MySql查询优化--索引介绍

目的

记录mysql数据库在日常开发过程中，索引相关的原理性知识及注意事项。

原理篇

索引类型

• mysql常见的索引类型：B+树、B树、Hash。
• Hash通过hash算法定位key的数据存储位置，性能较好，但不支持范围查询。
• B树，叶子节点和非叶子节点都存储数据，导致树的高度相对较高，也就是会影响二分查找的次数，查询效率相对较低。
• B+树，非叶子节点只存放冗余索引，全量的索引都在叶子节点，降低输的高度，查询效率高很多。

引擎类型

• mysql常用的引擎类型：InnoDB、myisam。
• myisam，底层使用B+树，索引文件和数据文件分离，叶子节点存储数据文件地址。
• InnoDB，底层使用B+树，数据在叶子节点。其中聚集索引的叶子节点存放完整的数据记录，非聚集索引的叶子节点存放主键id（节省空间，不需要每棵树都存放完整数据，但是会有回表查询的效率问题）。
• 建议使用整型自增，也是因为B+树的数据结构，减少主键插入引起的索引树的分裂平衡。

使用篇

执行计划

explain（执行计划）可以辅助查看一条sql的索引使用情况，进而进行相应的优化手段。下面列举部分explain重要字段：

• id：select的序列号，理论上id的数量和select数量是一致的，也是select出现的顺序号。id越大越先执行，id相同由上往下执行，id为null最后执行。
• select_type：字面意思，查询类型。常见：simple（简单查询）、primary（复杂查询中的外层查询）、subquery（select中的子查询）、derived（from中的子查询）、union（union联合查询）。
• type：表示关联类型或访问类型，效率顺序（system > const > eq_ref > ref > range > index > ALL）。
  1、其中system、const为常量查询，system只有在表内只有唯一条数据时才存在，const一般为主键或者唯一索引条件直接查询；
  2、eq_ref通常是把主键或者唯一索引作为关联条件的查询；
  3、ref普通索引的简单查询或关联查询；
  4、range索引的范围查询；
  5、index二级索引的全扫；
  6、ALL聚集索引的全表扫描。
• possible_keys：可能使用的索引，不代表最终就会用这个索引。
• key：执行sql实际使用到的索引。
• key_len：使用的索引的字节数，通常可以用来计算使用联合索引里面的字段个数。（注：只计算where条件下的索引长度，不包含order by和group by中用到的索引长度，order by是否用到索引通过Extra字段是否Using filesort判断）
• ref：索引列查询的值，通常有const（常量）和关联字段名称。
• Extra：枚举如下
  Using index：覆盖索引；
  Using where：没有命中索引的普通查询（全表扫索引树）；
  Using index condition：命中索引，没有被索引覆盖；
  Using temporary：创建了临时表，一般效率比较低；
  Using filesort：排序的场景下，文件排序，非索引排序，效率较差；
  Select tables optimized away：聚合函数

使用建议

• 尽量减少select *,尽量让查询字段被覆盖索引覆盖；
• 当where和order索引设计冲突时，优先给where，快速得到少量的结果集后进行排序；
• 小基数字段不要建索引，不能通过B+树的二分查找快速定位出少量的结果集；