MySQL索引优化

        MySQL版本：5.7.31。

---

1 B-Tree索引

        假设有如下数据表：

CREATE TABLE People (
last_name VARCHAR ( 50 ) NOT NULL,
first_name VARCHAR ( 50 ) NOT NULL,
dob date NOT NULL,
gender enum ( 'm', 'f' ) NOT NULL,
KEY ( last_name, first_name, dob ) 
);

        B-Tree对索引列是顺序组织存储的，所其对如下类型的查询有效：

• 全值匹配
  • 全值匹配指的是和索引中的所有列进行匹配。
• 匹配最左前缀
  • 可用于查找所有姓为Allen的人，即指使用索引的第一列。
• 匹配列前缀
  • 也可以只匹配某一列的值的开头部分。例如可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。
• 匹配范围值
  • 例如可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另外一列
  • 可用于查找所有姓为Allen，并且名字是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。
• 只访问索引的查询
  • B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无需访问数据行，即“覆盖索引”。

        因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。但是使用B-Tree索引也会有一些限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询如下：

  SELECT
      * 
  FROM
      people 
  WHERE
      last_name = 'Hou' 
      AND first_name LIKE 'R%' 
      AND dob = '1995-02-15'

  这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。这里描述的基本原则是：尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

---

2 哈希索引

        哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A，B)上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
• 哈希索引只支持等值比较查询，包括=、IN()、<=>。但不支持任何范围查询，例如WHERE price > 100。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

---

3 索引策略

3.1 独立的列

        我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。如下所示：

SELECT
    actor_id 
FROM
    sakila.actor 
WHERE
    actor_id + 1 = 5;

SELECT
    ...
WHERE
    TO_DAYS( CURRENT_DATE ) - TO_DAYS( date_col ) <= 10;

        另一个例子如下，这里hire_time字段已经有了索引：

EXPLAIN select * from employees where date(hire_time)='2018-09-30';

        转化为日期范围查询，会走索引：

EXPLAIN select * from employees where hire_time>='2018-09-30 00:00:00' and hire_time<='2018-09-30 23:59:59'

3.2 使用索引扫描来做排序

        只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

        有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。例如下面的索引和对应的SQL：

UNIQUE KEY rental_date ( rental_date, inventory_id, customer_id ),

SELECT
    rental_id,
    staff_id 
FROM
    sakila.rental 
WHERE
    rental_date = '2029-02-29' 
ORDER BY
    inventory_id,
    customer_id

        另外需要说明的是，如果order by的条件不在索引列上，就会产生Using filesort，即文件排序。文件排序分为单路排序和双路排序两种：

• 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
• 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，即主键，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段

        MySQL 通过比较系统变量 max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式。

• 如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 单路排序模式
• 如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 双路排序模式

        group by与order by的优化思路是类似的，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。如果不需要排序，对于group by的优化可以加上order by null禁止排序。还有一点，where的优先级是要高于having的，所以能写在where中的限定条件就不要去having中进行限定了。

3.3 索引的取舍

        一些列的选择性通常不高，但可能很多查询都会用到，所以考虑到使用的频率，还是建议在创建不同组合索引的时候将该列作为前缀。但这违背了不应该在选择性低的列上创建索引的经验，但是可以通过下面的“诀窍”绕过：假如说该列是性别列SEX，如果某个查询不限制性别，那么可以通过在查询条件中新增AND SEX IN ( 'm', 'f' )来让MySQL选择该索引。这样写并不会过滤任何行，和没有这个条件时的结果相同。但是必须加上这个列的条件，MySQL才能够匹配索引的最左前缀。这个“诀窍”在这类场景中非常有效，但如果列有太多不同的值，就会让IN ()列表太长，这样做就不行了。

3.4 存储引擎不能使用索引中范围条件右边的列

        这里建立了一个联合索引：(name，age，position)

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

        从key_len列可以看到，联合索引没有用到范围索引右边的列。

3.5 != 或者 <> 无法使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

3.6 is null,is not null 无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null;

        所以一般情况下建议设置字段为非空，给一个默认值。

3.7 like以通配符开头，mysql索引会失效变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei';

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';

        假设有索引(a，b，c)：

Where语句	索引是否被使用
where a = 3	Y，使用到a
where a = 3 and b = 5	Y，使用到a，b
where a = 3 and b = 5 and c = 4	Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	Y，只用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	Y，使用到a和b，c不能在范围之后，b断了
where a = 3 and b like 'kk%' and c = 4	Y，使用到a，b，c
where a = 3 and b like '%kk' and c = 4	Y，只用到a
where a = 3 and b like '%kk%' and c = 4	Y，只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y，使用到a，b，c

        like KK%相当于常量，%KK和%KK%相当于范围。

        如果实在是要用双百分号的模糊查询，可以考虑使用覆盖索引：

EXPLAIN SELECT name,age,position FROM employees WHERE name LIKE '%Lei'

        如果覆盖索引不能满足要求的话，可以考虑使用ES。

3.8 字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';

EXPLAIN SELECT * FROM employees WHERE name = 1000;