MySQL之优化“N板斧”

MySQL之优化“N板斧”

一、索引优化

1、最左前缀匹配原则

根据Alibaba开发手册，索引文件具有B-Tree的最左匹配特性，如果左边的值未确定，那么将无法使用此索引。

如测试表student表有5千万数据，在name字段上添加索引，使用做模糊匹配执行计划如下：

explain select sql_no_cache * from student where student.name like '%abc';

确定左边的值，使用右查询模糊执行计划如下：

explain select sql_no_cache * from student where student.name like 'abc%';

2、主键顺序插入，尽量避免跳值插入

假设B-Tree一个页数据中主键值为1，3，7，10，…，100。此时如果再插入一条主键为9的记录，那么就需要插入到7和10之间，这样就有可能造成记录移动和页分裂,页分裂带来直接影响就是性能损耗。所以如果想尽量避免这种情况，最好让插入记录的主键依次递增。建议主键AUTO_INCREMENT，让存储引擎自动生成主键，而不是手动插入。

3、计算，函数，类型转换（自动/手动）导致索引失效

• 函数导致失效

  以测试表student为例，查询name是abc开头的所有记录。使用like和函数生成的计划分别如下：

explain select sql_no_cache * from student where student.name like 'abc%';

explain select sql_no_cache * from student where left(student.name,3) = 'abc';

• 计算导致失效

  explain select sql_no_cache id,stuno,name from student where stuno+1 = 800001;
  

• 类型转换导致失效

  explain select id, stuno, name from student where name = 123;
  

4、不等于索引失效

explain select sql_no_cache * from student where age <> 18;

5、is not null索引失效

explain select sql_no_cache * from student where age is not null;

is null可以使用索引

6、like以%开头索引失效

此规则同第一条。

7、or前后存在非索引的列，索引失效

explain select sql_no_cache * from student where age = 10 or classid = 888;

在age和name上分别创建单列索引，但是where条件or后的classid为非索引列，导致索引失效。

使用到索引情况：

explain select sql_no_cache * from student where age = 10 or name = 'abc';

8、数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4（5.5…3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同字符集进行比较前需要继续转换可能造成索引失效场景。

二、查询优化

1、关联查询优化

• 在外连接查询中，尽量让连接条件中最为被驱动表的字段加上索引（如果两张表的连接条件都有索引最好），这样在连接查询时就会加快被驱动表的查询效率，从而加快整个查询语句的执行效率。

• 在内连接查询中，查询优化器根据花费的成本计算可以决定谁作为驱动表，谁作为被驱动表的。如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被当做被驱动表；同时在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，也就是通常说的小表驱动大表。

• MySQL默认开启了block_nested_loop，故在实际应用时可是根据实际情况增大join_buffer_size的值（默认256k）。

• 减少驱动表不必要的字段查询，字段越少，join buffer所缓存的数据就越多。

  MySQL从8.0.20版本开始废弃BNLJ，从8.0.18版本开始加入hash join并且默认会使用hash join

2、子查询优化

子查询时MySQL的一项重要功能，可以通过一个SQL语句实现比较复杂的查询。但是子查询的执行效率不高，原因有以下几点：

• 执行子查询时，MySQL需要为内层查询语句的结果建立一个临时表，然后外层查询从临时表中查询记录，查询完毕后，在销毁临时表，这样会消耗过多的CPU和IO资源，产生大量慢查询。
• 子查询的结果集是临时表，不论是内存临时表还是磁盘临时表都不会存在索引，因此查询性能会受到影响。
• 对于返回结果集比较大的子查询，其对查询性能的影响也越大。

因此，在实际开发中，尽量使用join替代子查询。连接查询速度快还能使用到索引；其次，尽量不要用NOT IN或者NOT EXISTS，用LEFT JOIN 表1 ON 表2 WHERE 字段 IS NULL来代替。

3、排序优化

• 由于MySQL采用B-TREE存储数据，所以如果能在order by后的字段上也加上索引的话，那天然就是有序的，并且在MySQL8.0之后还支持降序索引，这就是的索引在建立的时候就能保证数据的有序性，效率自然就更高。
• 尽量避免在order by后的字段使用FileSort排序，这是一种效率底下的体现。
• 尽量使用index完成order by排序，如果where和order by后面的字段相同就使用单列索引，如果不同就使用联合索引。

4、分组优化

• group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
• group by 先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• where效率高于having，能写在where限定的条件就不要写在having中了
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行 以内，否则SQL会很慢。

5、分页优化

一般分页查询时，通过创建覆盖索引能较好的提高查询性能。

• 在索引上完成排序分页操作，然后通过主键关联回表查询的方式提高查询效率。
• 可以把limit查询转换成某个位置的查询，如select * from tbl limit 30000,10转换为select * from tbl where id>30000 limit 10。这种情况适用于主键自增的情况。

6、优先覆盖索引

覆盖索引就是在查询的条件中需要返回的字段和条件字段都在索引中，所以存储引擎在查询数据时只需查询B-TREE的索引数据就可满足查询结果，而不用进行回表操作。如给表的name、age都建立索引，而且查询时select name, age from stu where age >20，这种类似的语句就使用到覆盖索引，而不用回表查询没有建立索引的原字段。

07、索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

索引下推主要针对的是联合索引，通俗来讲就是在查询时如果使用到的联合索引中某些字段索引失效或有为索引的字段，通过已有索引进一步过滤数据的一种手段。索引下推在MySQL中是默认开启的。

使用索引下推执行结果如下：

不使用索引下推执行结果如下：

ICP的使用条件如下：

• 只能用于二级索引(secondary index)
• explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
• 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
• ICP可以用于MyISAM和InnnoDB存储引擎
• MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
  ange 、 ref 、 eq_ref 或者 ref_or_null 。
• 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
• ICP可以用于MyISAM和InnnoDB存储引擎
• MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
• 当SQL使用覆盖索引时，不支持ICP优化方法。

----------------------------------------------⭐⭐MySQL系列文章⭐⭐------------------------------------------------

1、MySQL之存储引擎
2、MySQL之索引创建原则
3、MySQL之优化“N板斧”