MySQL索引，看这一篇就够了！

索引篇

B+树结构

B+树记录数据存储在叶子节点页上，记录与记录之间形成一条单向链表，页与页之间形成双向链表（方便范围扫描） ；非叶子节点页中存储的记录可以看成下层节点中某页最小记录以及对应页号（因为指向了每页最小记录，记录中还有分组维护了对应的槽，维护成一个顺序列表，这样查找时能够使用二分法）

【图中的目录项记录页就是非叶子节点、用户记录页就是叶子节点】

B+树索引

• 聚簇索引

  • innodb的主键索引的实现是聚簇索引，叶子节点页中的记录以主键来升序排序，同时记录了整行记录

• 二级索引

  • 普通索引的（以某列生成的索引）实现是二级索引，叶子节点页中记录以指定的列来升序排序，当指定的列值相同时再按照记录的主键值来升序排序（非唯一索引情况下）
  • 与聚簇索引不同的是，二级索引除了记录指定的索引列就只记录主键值而不记录其他列，如果使用二级索引且需要查询其他列的情况下，那么就要重新查一遍聚簇索引取到完整记录，这叫回表

• 联合索引

  • 联合索引是指定多个索引列的二级索引，比如指定索引列为a,b的联合索引，就是先以索引列a升序排序，当a相同时再对索引列b进行升序排序，当b相同时再对主键值进行升序排序
  • 当知道这个后就能够知道为什么联合索引要满足最左匹配原则

使用索引

索引代价

• 空间上索引会占用空间
• 时间上优化器会根据可能用到的多个索引来计算执行计划，增加了计算成本；在进行增删改操作时可能改动索引，增加维护成本

优化措施

• 覆盖索引：因为回表操作可能造成大量随机IO，如果使用二级索引时不用回表就叫覆盖索引；覆盖索引优化随机IO并且在使用count函数的情况下如果使用count(*)、count(常量)的情况会使用二级索引减少IO开销（二级索引占用空间比聚簇索引小）
• Multi Range Read 多范围读取MRR：回表时的主键值不是有序的，直接回表会造成随机IO，MRR优化使用缓冲对主键值排序再回表
• index condition pushdown 索引条件下推ICP：当查询语句where条件有多个且使用二级索引时，如果二级索引存在的列正好匹配where条件的列（大多是联合索引情况下），就会使用ICP优化，不回表查询聚簇索引放回server层再比较条件，以此来减少回表

• 索引合并：使用两个索引将结果集合并

  • intersection（and 合并）：where k1 = 'a' and k2 = 'b' (二级索引：k1,k2) 使用二级索引主键排序的情况下，使用k1,k2索引分别查出结果集再作交集过滤，减少回表开销
  • union（or 合并）：where k1 = 'a' or k2 = 'b' (二级索引：k1,k2)使用二级索引主键排序的情况下，使用k1,k2索引分别查出结果集再作并集过滤，减少回表开销
  • sort_union（or + sort合并）：where k1 < 'a' or k2 > 'b' (二级索引：k1,k2) 不需要主键排序，使用k1,k2索引分别查出结果集后对主键值排序再作并集过滤，减少回表开销【不适用查询数据量大的情况】

索引失效

1. where 条件使用表达式导致索引失效

2. where 条件使用函数导致索引使用

   • 可能隐式使用函数 ，当联表查询时a.c(utf8mb4) == b.c(utf8) 因为字符集不同，低字符集自动向高字符集转换导致索引失效 【注意是使用索引的一方用函数才失效，也就是b.c；如果a.c使用函数不失效；因为函数（b.c）索引不认识】
   • 隐式的使用字符串转数字比较
   • 使用函数或者表达式能够使用索引，但是携带表达式或者函数在索引上不能快递定位记录，从而导致索引失效

1. 回表数量太多导致不使用索引

   • 优化器认为使用索引时回表数量太多，就直接使用全表扫描

• 回表数量多不用索引的场景

  • 深分页：limit 10W,10 会先找出前100010条记录放到server层再取最后10条；可以使用联表查询、子查询或者携带上次查询终点参数来解决（先定位到起点）
  • 查询条件携带 is not null 会觉得重复值太多，导致可能回表太多，不偏向使用（默认情况下，null被当作重复值，null太多可能导致优化器不偏向使用索引）
  • cardinality 表示索引中唯一值的估计数量，值越小表示索引重复值越多（重复值太多可能导致回表太多），优化器越不偏向使用索引

索引使用

1. 为常用order、group by，where 的子句的列或者被驱动表的列建立索引；索引并不是越多越好，维护索引需要开销，避免冗余索引
2. 优化器使用错索引，可以force index强制使用；如果是cardinality的缘故则可以在空闲时重新统计analyze table
3. 字符串长的列可以根据场景需要为列前缀建立索引

良好习惯

1. 索引列和主键值尽量小，每页存储的记录多，查询相同记录数量时IO开销可能会小
2. 为了维护排序的索引，插入时最好升序插入，否则维护还需要页分裂开销
3. 查询语句尽量不要用 * ，可能需要的列在二级索引上就有，就可以使用覆盖索引，不需要回表
4. 索引列在查询条件中不要使用表达式、函数等，注意字符串索引列的字符编码是否一致

索引优化

• explain SQL ：查看最低成本执行计划

• explain format = json SQL : 最低成本执行计划JSON格式

• optimizer trace ：查看所有执行方案 （默认不开启）

  1. 开启set optimizer_trace = 'enabled=on';
  2. 执行查询语句
  3. 查看 select * from information_schema.OPTIMIZER_TRACE\G;
  4. 关闭set optimizer_trace = 'enabled=off';

总结

使用好索引可以让查询语句变得更快，但是索引不是越多越好，索引的维护有空间、时间上的开销

innodb的索引有覆盖索引、MRR、ICP、索引合并等等优化措施，了解优化措施方便我们更好的使用索引

使用索引要有良好的习惯，这样能从开发时就注意性能问题，而不是等到出现了性能问题再去优化

熟悉索引后，当发生性能问题，能够更好的去排查索引失效或者能否建立索引去进行性能优化