索引的类型(数据结构)

常见的索引类型 有哈希索引 和 b+tree 索引

  • 哈希索引：存放列的哈希值，只能用于全值匹配
  • b+tree索引：将列的值按照树的结构有序的组织在一起。因为 存储原值、有序 等特点，b+树索引可以用于 范围查询、排序、前缀匹配（ where col like 'xxx%'）

下文的内容主要针对 B+树 索引。

建立索引

单列索引、多列索引、列前缀索引（add index INDEX_NAME (COL_NAME(5))）

如何确定索引的选择性

如果列的选择性高，那么在该列上建立索引 可以减少扫描的行数，优化效果就比较好。

如何用索引优化查询

在where子句中使用

单列索引不必多说。
多列索引 必须遵循 最左前缀原则。例如add index INDEX_NAME (status、create_time、price)在4个列上建立了一个索引，要使索引生效，具备2个条件

  1. 对左侧的列进行筛选，如 where status =1或where status =1 and create_time='2019-01-01'
  2. 对左侧列进行等值查询，如过对某个列进行了范围查询，则右侧列不会被用于索引数据。例如where status =1 and create_time > '2019-01-01' and price =1,则 price 不会 作为索引列 用来检索数据
用索引优化连表查询

可以对关联条件 用到的字段添加索引，规则与在where子句中相同。

有一点特殊的地方是，如果在 join on 子句中使用范围查询，则必须是常量，而不能是一个字段或 对字段的计算表达式。例如a join b on b.col > '2019'与a join b on b.col> a.col，前者可以用上col字段上的索引而后者不能(这条规则是实验结果，我没有找到官方的相关文档，读者可以自行测试。)。

用索引优化order by、group by
优化order by

之所以索引可以用来排序，是因为索引自身是将列值以有序的结构组织在一起，数据查询出来就是一个有序的集合，因此不用额外的排序操作。

用索引排序的条件

  1. order by 子句包含了左侧索引列（最左前缀原则），且排序方向一致；
  2. 左边的列使用了常量筛选，那么 这个列在order by 子句中可以不用出现（in操作被认为是范围查找，则该列页必须出现在order by子句中）

沿用上一节的例子,以下是可以使用索引排序的情况

  • select * from table order by status,create_time，满足最左前缀原则，且排序顺序一致
  • select * from table where status >1 order by status，create_time
  • select * from table where status =1 order by create_time，不满足最左前缀原则，但左边第一列为常量。
    以下是不可以使用索引排序的情况
  • select * from table order by status asc,create_time desc，字段排序方向不一致
  • select * from table order by create_time，不满足最左前缀原则
  • select * from table order by status,create_time,other_col，用索引外的字段排序
  • select * from table where status >1 order by create_time，最左列使用了范围查询，且没有出现在order by子句中
  • select * from table where status in(1,2) order by create_time,同上条一样 使用了范围查询，in也被认为是范围查询。
优化group by

group by子句 的优化与order by类似，因为group by 操作依赖于排序

理解优化规则的原理（*）

多列索引的组织结构：多个列的值拼接在一起，以B+tree的结构有序地组织在一起。排序规则按照从左到右逐个比较，按相同的方向排序，用第一个值不同的列的排序结果作为整体的排序结果，不会考虑右侧列的排序。

由上我们可以推理出，用索引排序时个字段方向需要一致，且最左侧列必须出现。我们讨论和下左侧列在order by子句中缺失的情况。

  1. 左侧列没有出现在where子句中，则无法按照索引检索数据，自然无法排序。
  2. 左侧列 采用了范围查询。按索引查询出的数据顺序是 是对左侧多个列 的综合排序，不能忽略左侧列对数据顺序的影响，因此无法保证索引中的数据顺序就是 按照第二个列排序的。当然也有列外，就是左侧列用常量查询，那么这时候可以忽略第一列对排序的影响。
前缀索引可以用于优化排序吗？

答案是否定的。因为前缀索引中只存储了列的前缀部分，索引的顺序不能代表原数据的顺序。因此利用前缀索引查找出的数据也就不具备有序性，需要额外的内存排序操作。

注: 如何优化排序还可以参考这篇博文