索引的优化原则
- 联合索引尽量所有字段全值匹配。如果使用部分字段，注意最左前缀规则，按照顺序使用联合索引。
- 不在索引列上做计算、函数、类型转换等操作，会导致索引失效走全表扫描。
- 尽量使用索引覆盖，减少select * 带来的回表的成本。
- mysql使用in、not in、is null、is not null、<> 、 !=，mysql的优化器会根据扫描的条数进行成本分析，可能直接会全表扫描，所以注意查询条件命中记录的条数。
- like注意使用前缀通配匹配。like ‘abc%’。如果没办法可要优化到索引覆盖级别。
- 如果不符合预期，可以强制force index使用索引。
- 避免文件排序（explain extra中有Using filesort）。为排序字段建立索引，或者where条件字段和排序字段能使用联合索引。排序还要避免多个排序字段的升序、降序规则不同。
如果不能避免filesort，则可以调整mysql的参数max_length_for_sort_data，调小一点来触发双路排序。filesort是在内存维护一个sort buffer，当要排序字段总大小大于这个值就会在buffer中只加载主键和排序字段，回表拿最后的结果，而不是在buffer中加载所有符合条件的数据的所有字段排序后返回。
- 分页优化
1
select * from table where name ='xxx' limit 100000,10;
此语句的过程是，扫描二级索引name，查出符合条件的主键值，回表查询聚簇索引，扫描100010行，取最后的10行。
深度分页会造成回表扫描大量聚簇索引才能取到深度的那几条数据，造成效率低下。
解决办法：
- 能用主键先用一个标签来过滤的场景来用主键id过滤。
改写为1
select * from table where id > 100000 limit 10;
这样能减少扫描聚簇索引的数量。但是有局限性，因为要求能找到这样一个标签值，且结果是按照主键这样排序的。
- 非主键排序的深度分页可以用延迟关联的方法来连接查询。
1
select * from table order by name limit 100000,5;
可以改写为
1 | select * from table a where inner join (select id from table b order by name limit 100000, 5) b on a.id = b.id; |
这样的连接查询中，子查询先是索引覆盖去排序去筛选出对应的记录且去除分页记录，然后再作为驱动表连接查询该表，此时是根据主键id做关联查询，可以大量减少回表扫描记录数。
- 连接查询的优化
- 连接查询的字段建立索引，驱动表记录去和被驱动表连接查询可以使用索引。
- 小表驱动大表，本身连接查询也是这样去选择驱动表的。
- 连接字段没有索引，mysql本身会有一个基于块的嵌套循环查询算法，在内存中有join_buffer区域去加载一批驱动表的数据，去和被驱动表做关联查询，减少磁盘IO的次数。
- count查询
- count(字段) 不为统计null的记录数。count(id)、count(1)、count(*)会统计null的记录。
- 本身这几个效率差不多，在count(字段)中的字段有索引情况下，可以直接扫描二级索引来完成统计，效率更高。
索引的设计原则
代码先行，索引后上
因为代码中要开发sql查询，可以根据业务中的sql来确定具体的sql来建立合适的索引。联合索引进来覆盖条件
索引要能覆盖大量查询场景（where、order by、group by），顺序也要按照最左前缀原则来设计。
- 不要在小基数的字段上建立索引
越小的基数在等值或者范围查询场景下扫描更多的记录，可能造成优化器选择全表扫描。 不能发挥索引的优势。
- where 和order by 冲突 尽量先满足where
- 索引字段的长度尽量小一点，相同大小空间的B+树能承载更多的数据，查找效率也更高。