前言面试题来自：社招一年半及分享（含阿里美团头条京东滴滴）MySQL常见的优化方式分为以下几个方面：SQL优化、设计优化、硬件优化等.,其中每个大方向还包括多个小的优化点。让我们来看看SQL优化。该优化方案是指通过优化SQL语句和索引来提高MySQL数据库的运行效率。具体内容如下：分页优化例如：select*fromtablewheretype=2andlevel=9orderbyidasclimit190289,10;优化方案：延迟关联先通过where条件提取主键，然后将表与原始数据表进行关联，通过主键id而不是原来的二级索引提取数据行。例如：selecta.*fromtablea,(selectidfromtablewheretype=2andlevel=9orderbyidasclimit190289,10)bwherea.id=b.id书签法书签法就是找到limit第一个参数对应的主键值，然后根据主键值和限制例如：select*fromtablewhereid>(select*fromtablewheretype=2andlevel=9orderbyidasclimit190289,1)limit10;索引优化正确使用索引如果不加索引，那么查询的时候会触发全表扫描，查询的数据会很多，查询效率会很低。为了提高查询的性能，我们需要对最常用的查询字段添加相应的索引，从而提高查询的性能。建立覆盖索引InnoDB在使用辅助索引查询数据时会返回表，但是如果索引的叶子节点已经包含要查询的字段，那么就不需要回表查询了。这称为覆盖索引。这样就可以直接从索引中获取查询结果altertabletestaddindexidx_city_name(city,name);在MySQL5.0之前的版本中，尽量避免使用or查询。在MySQL5.0之前的版本中，尽量避免使用or查询，可以使用union或subqueries来代替，因为在早期的MySQL版本中使用或查询可能会导致索引失败。MySQL5.0以后的版本，引入了索引合并。索引合并简单来说就是多条件查询，比如or或and查询，对多个索引进行条件扫描，然后Merge它们各自的结果，所以不会出现索引失效的问题。如果Explain执行计划的type列的值为index_merge，可以看出MySQL是使用索引merge的方式对该表执行查询。Explain的使用可以参考我之前的文章：最全的Explain总结，SQL优化不再难放弃索引索引导致全表扫描，即使被比较的字段上有索引解决方法：将不等于运算符改为or，可以使用索引避免全表扫描。比如把column<>'aaa'改成column>'aaa'或者column<'aaa'，就可以使用索引了。正确使用前缀索引MySQL支持前缀索引，也就是说我们可以定义字符串的一部分作为索引。我们知道索引越长，它占用的磁盘空间就越多，所以在同一个数据页中，可以放入索引的索引值越少，也就是说搜索索引所需要的查询时间为较长，查询效率会降低，所以我们可以选择适当使用前缀索引来减少空间占用，提高查询效率，比如邮箱的后缀是固定的“@xxx.com”，那么一个字段像这样最后几位固定值的很适合定义为前缀索引altertabletestaddindexindex2(email(6));使用前缀索引，如果你定义好长度，你可以节省空间而不会增加太多额外的查询成本。需要注意的是，前缀索引也有缺点。MySQL不能使用前缀索引进行orderby和groupby操作，也不能作为覆盖索引。查询特定字段而不是所有字段，尽量避免使用select*，而是查询必填字段，这样可以提高速度，减少网络传输的带宽压力。优化子查询，尽量使用Join语句代替子查询，因为子查询是嵌套查询，嵌套查询会创建新的临时表，临时表的创建和销毁会占用一定的系统资源，占用一定的量时间的，同时，对于返回比较大的结果集的子查询，对查询性能的影响更大。关于Join语句的使用，可以参考我之前的文章：写好Join语句，必须明白小表带动大表。我们尝试用小表带动大表进行查询，即如果B表的数据小于A表的数据，那么执行的顺序是先查B表，再查A表。具体查询语句如下：selectnamefromAwhereidin(selectidfromB);不要对列进行操作不要对列字段进行算术运算或其他表达式操作，否则查询引擎可能无法正确使用索引，从而影响查询效率select*fromtestwhereid+1=50;select*fromtestwheremonth(updateTime)=7;一个容易踩的坑：隐式类型转换：select*fromtestwhereskuId=123456skuId字段上有索引，但是explain的结果显示这条语句会扫描全表，因为skuId的字符类型是varchar(32)，比较值是整数，需要进行类型转换，适当增加冗余字段。增加冗余字段可以减少大量的表连接查询，因为多表之间的表连接查询性能很低，所以可以适当增加冗余字段来减少多表的关联查询。这是一种以空间换取时间的优化策略。正确使用联合索引。使用B+树的MySQL数据库引擎，例如InnoDB引擎，每次从左到右查询复合字段。匹配数据，所以在创建联合索引的时候需要注意索引的创建顺序。比如我们创建了一个联合索引是idx(name,age,sex)，那么当我们使用，name+age+gender,name+age,name等最左前缀查询条件时，联合索引会被触发查询；但是，如果查询条件不是最左匹配，例如性别+姓名等查询条件，则不会触发联合索引Join，优化MySQLjoin语句连接表使用nested-loopjoin算法。这个过程类似于嵌套循环。简单来说，就是遍历驱动表（outertable），读出每一行数据，取出连接字段到驱动表（innertable）），找到满足条件的行，形成结果行。要提高join语句的性能，就需要尽可能减少嵌套循环的循环次数。一个显着的优化方法是在被驱动表的join字段上建立索引，利用索引快速匹配到对应的行，避免与内表的每一行进行比较，大大减少了总循环次数另一个优化点是在连接时用小结果集驱动大结果集。在索引优化的基础上，可以进一步减少嵌套循环的次数。如果难以判断哪个是大表哪个是小表，可以使用innerjoin来连接。MySQL会自动选择小表来驱动大表。Join语句的使用可以参考我之前的文章：写好Join语句。前提是你了解这些避免使用JOIN过多关联的表是和MySQL相关的。对于缓存来说，缓存的大小可以通过join_buffer_size参数来设置。在MySQL中，同一条SQL连接多张表，会多分配一个关联缓存。一条SQL关联的表越多，占用的内存越大，如果程序中大量使用多表关联操作，并且join_buffer_size设置不合理，很容易造成服务器内存溢出，会影响服务器数据库性能的稳定性排序优化MySQL使用索引扫描排序生成有序结果有两种方式：一种是对结果集进行排序，另一种是按照索引的顺序扫描.结果自然是有序的，但是如果索引不能覆盖查询要求的列，就得扫描一条记录回表查询一次。这种读取操作是随机IO，通常比顺序全表扫描慢。因此，在设计索引时，尽量使用同一个索引进行排序和查找。行示例：--buildindex(date,staff_id,customer_id)selectstaff_id,customer_idfromtestwheredate='2010-01-01'orderbystaff_id,customer_id;只有当索引中列的顺序与ORDERBY子句的顺序完全相同，并且所有列的排序方向相同时，才能使用索引对结果进行排序。UNION优化MySQL。处理union的策略是先创建一个临时表，然后将每条查询结果填充到临时表中，然后执行查询。联合查询中会用到很多优化策略。无效，因为它不能使用索引。最好将where、limit等子句手动下推到并集的每个子查询上，这样优化器就可以充分利用这些条件进行优化。另外，除非服务器真的需要去重，否则必须使用unionall，如果不加all关键字，MySQL会在临时表中加上distinct选项，这会导致对整个临时表进行唯一性检查，即非常昂贵。慢查询日志中慢查询通常的排查方法是先使用慢查询日志功能，查询较慢的SQL语句，然后通过通过Explain查询SQL语句的执行计划，最终分析定位问题根源，再对慢查询日志进行处理。慢查询日志是指MySQL中可以通过配置开启慢查询日志记录功能。超过long_query_time值的SQL将被记录在日志中。我们可以通过设置“slow_query_log=1”来启用慢查询。需要注意的是，开启慢日志功能后，会对MySQL的性能产生一定的影响，所以在生产环境中慎用该功能设计优化尽量避免使用NULLNULL不好处理在MySQL中，存储需要额外的空间，操作也需要特殊的操作符。包含NULL的列很难进行查询优化。应该指定列为非空，使用0、空字符串或其他特殊的A值来代替空值，比如定义为int非空默认0最小数据长度数据类型长度越小通常需要的空间越小disk,memoryandCPUcache，使用最简单的数据类型处理速度更快Simple数据类型的运行成本更低，例如：可以使用int类型，就不要使用varchar类型，因为int类型比varchar类型查询效率高，尽量少定义text类型，text类型查询效率很低，如果一定要用text定义字段，可以把这个字段拆分成子表，使用需要查询该字段时联合查询，可以提高主表的查询效率。将大表拆分成多个子表，将使用频率较高的主要信息放到主表中，其他的放到子表中，这样我们的大部分查询只需要查询主表就可以完成更少的字段，从而有效地提高了查询的效率。分库是指将一个数据库划分为多个数据库。比如我们把一个数据库拆分成多个数据库，一个主库用来写入和修改数据，另一个用来同步主数据提供给客户端查询，这样一个的读写压力数据库是共享的。提供多个库，提高了数据库的整体运行效率。常用类型选择整数类型宽度设置MySQL可以为整数类型指定一个宽度，比如int(11)，其实是没有意义的，它不限制取值范围。对于存储和计算，int(1)和int(20)是相同的VARCHAR和CHAR类型。char类型是定长的，varchar存储可变字符串，比定长更节省空间，但是varchar需要额外的1或2个字节来记录字符串的长度，而且在使用时容易产生碎片更新。需要结合使用场景选择：如果string列的最大长度远大于平均长度，或者列的更新很少，选择varchar比较合适；如果要保存很短的字符串，或者长度相同的字符串值，比如MD5值，或者经常变化的列数据，选择使用char类型的DATETIME和TIMESTAMP类型的datetime范围更大，可以表示从1001到9999，而timestamp只能表示从1970到2038。datetime与时区无关，timestamp显示的值取决于时区。大多数场景下，这两种都可以很好的工作，但是推荐使用timestamp，因为datetime占8个字节，timestamp只占4个字节，timestamp更节省空间。BLOB和TEXT类型blob和text都是字符串数据类型，旨在存储大量数据。它们在实际使用中以二进制和字符格式存储。这两种类型要谨慎使用，因为它们的查询效率很低。如果字段必须使用这两种类型，可以把这个字段分离成子表，需要查询这个字段的时候使用联合查询，可以提高主表的查询效率小，可以在内存中缓存更多的数据，所以操作会执行得更快缺点是查询时需要更多的关联第一范式：字段不可分割，数据库默认支持第二范式：消除对主键的部分依赖，可以添加一个什么都没有的字段将业务逻辑作为表中的主键，例如使用自增id的第三范式：消除对主键的传递依赖，可以拆分表，减少数据冗余硬件优化MySQL硬件要求主要体现在三个方面：磁盘、网络、内存盘。尽量使用具有高性能读写能力的磁盘，比如固态硬盘，这样可以减少I/O运行时间，从而提高MySQL的整体运行效率。磁盘你也可以尝试使用多个小磁盘而不是一个大磁盘，因为磁盘的速度是固定的。拥有多个小磁盘相当于拥有多个并行运行的磁盘。网络保证网络带宽通畅（低延迟），足够大的网络带宽是MySQL正常运行的基本条件。如果条件允许，还可以设置多块网卡，以提高MySQL服务器在网络高峰期的运行效率。内存MySQL服务器的内存越大，存储和缓存的信息就越多。很多，而且内存的性能非常高，提高了整个MySQL的运行效率转载本文请联系粤版飞鱼公众号。
