索引使用方式最佳实践

首先回顾一下InnoDB中B+树索引的结论：

每个索引都对应1棵B+树，最下面是叶子节点，保存用户记录，其余的是内节点，保存目录项记录。
InnoDB会自动为主键建立聚簇索引（如果没指定主键，会自动生成隐藏字段row_id作为主键），聚簇索引拥有最完整的用户记录。
二级索引的叶子节点只有索引列和主键值，想要获取完整用户记录，需要进行回表操作。
B+树中每层节点都按照索引列的值从小到大的顺序组成双向链表，而且每个节点内的记录都按照索引列的值从小到大组成单向链表。
通过索引查找记录时，是从B+树的根节点一层一层向下搜索的。

索引的代价

索引虽然提升了性能，但是任何事物都有两面性，尤其是计算机领域，不存在绝对的银弹。

空间上的代价
每建立一个索引，都需要在存储空间中针对这个索引列生成一棵B+树，每棵树的每一个节点都是一个数据页。一个数据页默认占用16KB的存储空间，而一棵树往往由大量的数据页组成，这将占用一大片的存储空间。
时间上的代价
每当对表中的数据进行增删改操作时，都需要修改各个B+树索引。而B+ 树中的每层节点都按照索引列的值从小到大的顺序排序组成了双向链表。无论是叶子节点中的记录还是内节点中的记录，都按照索引列的值从小到大的顺序形成了一个单向链表。而增删改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行页面分裂、页面回收等操作，以维护节点和记录的排序。如果建立了许多索引，每个索引对应的B+树都要进行相关的维护操作，这将严重影响性能！还有在执行查询语句前，首先要生成一个执行计划。一般情况下，一条查询语句在执行过程中最多使用一个二级索引，在生成执行计划时需要计算使用不同索引执行查询时所需的成本，最后选取成本最低的那个索引执行查询。此时如果建了太多索引，可能会导致成本分析过程耗时太多，从而影响查询语句的执行性能。

所以，在一个表中建立的索引越多，占用的存储空间也就越多，在增删改记录或者生成执行计划时性能也就越差。为了建立又好又少的索引，我们得先了解索引在查询执行期间到底是如何发挥作用的。

B+树索引实践

为了下述内容的讲解，这里我们建一张表：

CREATE TABLE single_table(
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY uk_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_part(key_part1, key_part2, key_part3)
);

扫描区间和边界条件

对于一个查询来说，最简单的方式就是遍历全表，这种方案称为全表扫描。这种方案肯定是最慢的方式了，但是它也是最万能的方式，任何查询都可以通过全表扫描获取到结果。

但是这里我们主要讲的是索引，通过索引可以明显减少需要扫描的记录数量。
Q1：扫描区间

1	SELECT * FROM single_table WHERE id >= 2 AND id <= 100;

这个语句需要查找id值在[2, 100]区间中的所有聚簇索引记录。可以通过聚簇索引对应的B+树快速定位到id为2的记录，然后沿着记录所在的单向链表向后扫描，直到不在[2, 100]区间内为止。

与扫描全部聚簇索引相比，[2, 100]区间的记录已经很大程度地减少了需要扫描的记录数量。

Q2：单点扫描区间和范围扫描区间

1	SELECT * FROM single_table WHERE KEY2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

我们发现该查询的搜索条件涉及key2列，并且存在唯一索引uk_key2。通过使用uk_key2索引，可以得到[1438, 1438]，[6328, 6328]，[38, 79]这三个扫描区间。[1438, 1438]和[6328, 6328]我们称为单点扫描区间，[38, 79]我们称为范围扫描区间。但是我们的查询列表是*，需要读取完整的用户记录，所以这里通过uk_key2索引查询到叶子节点后，需要根据叶子结点的主键值进行回表操作。

Q3：搜索条件多索引字段

1	SELECT * FROM single_table WHERE key1 < 'a' AND key3 > 'z' AND common_field = 'abc';

这里的搜索条件中有三个字段，key1和key3有索引，common_field无索引。

如果用idx_key1执行查询，那么扫描区间应该是(-∞, a)，而对于key3 > ‘z’ AND common_field = 'abc’就是普通的搜索条件，需要在获取到idx_key1的二级索引记录后，执行回表操作，再进行筛选。
如果用idx_key3执行查询，那么扫描区间应该是(z, +∞)，而对于key1 < ‘a’ AND common_field = 'abc’就算普通的搜索条件，需要在获取到idx_key3的二级索引记录后，执行回表操作，再进行筛选。

在使用某个索引执行查询时，关键的问题是通过搜索条件找出合适的扫描区间，然后在对应的B+树中扫描索引列值在这些区间的记录。对每个扫描区间来说，仅需要通过B+树定位到该扫描区间的第一条记录，然后就可以沿着记录所在的单向链表向后扫描，直到扫描区间的边界为止。有以下几点需要注意：

IN操作符的语义与若干个等值匹配操作符（=）之间用OR连接起来的语义是一样的，都会产生多个单点扫描区间。

# 扫描区间是[1438, 1438], [6328, 6328]
SELECT * FROM single_table WHERE key2 IN (1438, 6328);
# 扫描区间是[1438, 1438], [6328, 6328]
SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

!=产生的扫描区间容易被忽略，他会导致搜索区间分段。

1 2	# 扫描区间是(-∞, 'a'), (a, +∞) SELECT * FORM single_table WHERE key1 != 'a';

LIKE操作符比较特殊，只有匹配完整的字符串或者匹配字符串前缀才会产生合适的扫描区间。

# 扫描区间是['a', 'a']
SELECT * FROM single_table WHERE key1 LIKE 'a';
# 扫描区间是['a', 'b')
SELECT * FROM single_table WHERE key1 LIKE 'a%';
# 扫描区间是(-∞, +∞)
SELECT * FROM single_table WHERE key1 LIKE '%a';

AND和OR操作符需要注意，这两个操作符很常见。
cond1 AND cond2：只有cond1和cond2都为True，整个表达式才为True。
cond1 OR cond2：只要cond1或者cond2为True，整个表达式就为True。

但是搜索条件也不一定都可以生成对应的扫描区间，如下：

所有搜索条件都可以生成合适的扫描区间：

# AND操作符，扫描区间取交集(200, +∞)
SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

# OR操作符，扫描区间取并集(100, +∞)
SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;

有的搜索条件不能生成合适的扫描区间：
1
SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
这个查询语句中key2 > 100可以生成扫描区间（100，+∞），但是uk_key2的二级索引记录并不按照common_field列进行排序，所以common_field的扫描区间是（-∞，+∞），它们取交集最终的扫描区间就是（100，+∞）。这么来看的话，common_field在使用uk_key2执行查询时，没有起到任何作用，这里我们可以直接把common_field = 'abc’的搜索条件替换为TRUE，扫描区间为（-∞，+∞）。
1
2
# OR操作符，扫描区间取并集(100, +∞), (-∞, +∞)；最众扫描区间(-∞, +∞)
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
从复杂的搜索条件中寻找扫描区间：
1
2
3
4
SELECT * FROM single_table WHERE
(key1 > 'xyz' AND key2 = 748) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc'));
首先，这个搜索条件中索引列有key1和key2，我们无法判断会采用哪个索引列，这里我们运用上面的方法来分析一下：
- 假设用索引idx_key1执行查询
  - 首先将不能形成合适扫描区间的搜索条件进行替换TRUE，这里的key1 LIKE '%suf’的搜索区间也和TRUE一样
    (key1 > 'xyz' AND TRUE) OR (key1 < 'abc' AND key1 > 'lmn') OR (TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))
  - 其中key1 < ‘abc’ AND key1 > ‘lmn’，这个搜索条件互斥，无法产生任何区间。AND操作符中的TRUE也不会影响搜索区间，可以直接去除，我们对上述条件继续简化
    (key1 > 'xyz') OR (key1 > 'zzz')
  - 继续简化，这里的key1 > 'xyz’和key1 > 'zzz’之间是用OR操作符连接，取并集，所以最终的结果就是key1 > 'xyz'，搜索区间就是（‘xyz’，+∞）
- 假设使用索引uk_key2执行查询
  - 首先对不能形成合适扫描区间的搜索条记进行替换TRUE
    (TRUE AND key2 = 748) OR (TRUE AND TRUE) OR (TRUE AND TRUE AND (key2 < 8000 OR TRUE))
  - 继续进行简化，key2 < 8000 OR TRUE取并集，那么结果也为TRUE
    key2 = 748 OR TRUE OR (TRUE AND TRUE AND TRUE)
  - key2 = 748 OR TRUE取并集，那么结果也是TRUE
    TRUE
此时，对搜索区间进行比对，我们发现这里用索引idx_key1的搜索区间明显要小于uk_key2的搜索区间。
在使用索引idx_key1进行上述查询时，搜索条件key1 LIKE '%suf’比较特殊。虽然它不能作为形成扫描区间的边界条件，但是idx_key1的二级索引记录是包含key1列的.因此，我们可以先判断获取到的二级索引记录是否符合这个条件。如果符合再执行回表操作，如呆不符合就不执行回表操作了。这样可能减少因回表操作而带来的性能损耗，这种优化方式称为索引条件下推。
使用联合索引执行查询时对应的扫描区间：
联合索引的索引列包含多个列，B+树中的每一层页面以及每个页面中的记录采用的排序方式较为复杂。以single_table表中的idx_key_part联合索引威力，它采用的排序规则如下：
- 先按照key_part1列的值进行排序；
- 在key_part1列的值相同的情况下，再按照key_part2列的值进行排序；
- 在key_part1和key_part2列的值都相同的情况下，再按照key_part3列的值进行排序。
了解了排序规则后，我们来判断下列哪些语句会执行联合索引，哪些不会，为什么？
S1：SELECT * FROM single_table WHERE key_part1 = 'a';
- 使用索引：是，因为条件直接涉及到索引的第一列。
- 搜索区间：[‘a’，‘a’]，即精确匹配 key_part1 = ‘a’。
S2：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b';
- 使用索引：是，涵盖了索引的前两列。
- 搜索区间：[[‘a’， ‘b’]，[‘a’，‘b’]]，即精确匹配 key_part1 = ‘a’ 和 key_part2 = ‘b’。
S3：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
- 使用索引：是，完全匹配了索引的所有列。
- 搜索区间：[[‘a’，‘b’，‘c’]，[‘a’，‘b’，‘c’]]，即精确匹配所有三个索引列。
S4：SELECT * FROM single_table WHERE key_part1 < 'a';
- 使用索引：是，条件使用了索引的第一列的范围查询。
- 搜索区间：(-∞，‘a’)，即小于 ‘a’ 的所有值。
S5：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 > 'a' AND key_part3 < 'd';
- 使用索引：是，虽然条件是范围查询，但使用了联合索引的前三列。
- 搜索区间：[[‘a’，(‘a’，-∞))，[‘a’，(‘+∞’，‘d’))]，包括 key_part2 > ‘a’ 和 key_part3 < ‘d’ 的范围。
S6：SELECT * FROM single_table WHERE key_part2 = 'a';
- 使用索引：不会，因为条件只包含中间列 key_part2，无法根据key_part1获取到最初的搜索区间，只能用全表扫描。
- 搜索区间：无，因为索引无法应用。
S7：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part3 = 'c';
- 使用索引：部分使用，但不高效，因为跳过了 key_part2，相当于只有key_part1字段生效。之后再遍历二级索引过滤key_part3='c’的记录。
- 搜索区间：[‘a’，‘a’]，虽然定义了范围，但效率不高。
S8：SELECT * FROM single_table WHERE key_part1 <= 'a' AND key_part2 = 'a';
- 使用索引：是，使用了索引的前两列，尽管第一列是范围查询。
- 搜索区间：[(‘-∞’，‘a’]，[‘a’，‘a’]]，包括 key_part1 <= ‘a’ 和精确匹配 key_part2 = ‘a’。
S9：SELECT * FROM single_table WHERE key_part1 < 'a' AND key_part2 = 'a';
- 使用索引：是，使用了索引的前两列，尽管第一列是范围查询。
- 搜索区间：[(‘-∞’，‘a’]，(‘a’，‘a’]]，不过，因为 key_part1 的范围限制，这可能导致不连续的索引使用。

总体来说，理解这些查询的关键在于：

索引的最左前缀原则：只有从左到右连续使用的索引列才能被高效利用。
范围查询（<, >, <=, >=）之后的索引列通常无法被高效利用。
等值查询（=）通常能更好地利用索引。
当无法使用索引的所有列时，查询可能会变得低效，特别是当需要在大范围内过滤少量数据时。

索引用于排序

我们经常用ORDER BY子句对查询结果按照某种规则进行排序，一般情况下，我们将查询结果加载到内存中，然后用一些排序算法进行排序。但有时查询结果太大，无法在内存中进行排序，此时需要借助磁盘空间存放中间结果，在排序完所有记录后再返回客户端。这种在内存或者磁盘中进行排序的方式称为文件排序(filesort)。但是如果ORDER BY子句中使用了索引列，就有可能省去在内存或磁盘中排序的步骤。

例如：SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3 LIMIT 10;

因为联合索引的存在，我们的key_part1，key_part2，key_part3字段已经是排好序的，只需要联合索引的B+树从最左边拿出10条记录，然后每条记录进行回表即可。

使用联合索引进行排序时注意事项
- 列顺序一致性: ORDER BY 子句中的列顺序必须与联合索引中的列顺序一致，才能有效利用 B+ 树索引进行排序。例如：
  - ORDER BY key_part1, key_part2, key_part3 可以利用索引。
  - 但 ORDER BY key_part3, key_part2, key_part1 不能利用索引。
- 部分使用索引: 可以只使用索引中从左到右连续的部分列进行排序。例如：
  - ORDER BY key_part1 或 ORDER BY key_part1, key_part2 可以利用索引。
不可以使用索引迸行排序的几种情况
- ASC、DESC 混用: 当 ORDER BY 子句中同时使用 ASC 和 DESC 排序时，索引可能无法完全利用。例如，ORDER BY key_part1 ASC, key_part2 DESC 不能高效使用索引。
  MySQL 8.0引入了一种称为Descending Index的特性，可以支持ORDERBY子句中ASC、DESC混用的情况.具体情况可以参考文档
- 排序列不在同一索引中: 如果 ORDER BY 子句中的列来自不同的索引，MySQL 无法使用任何一个索引进行排序。
- 非连续索引列排序: 如果 ORDER BY 中的列在联合索引中并不连续，MySQL 无法使用索引进行排序。例如，ORDER BY key_part1, key_part3 会跳过 key_part2，因此不能利用索引。
- 不同的扫描区间和排序列: 当扫描区间和排序列不同时，MySQL 也无法利用索引进行排序。例如，WHERE key1='a' ORDER BY key2。
- 修饰过的列: 如果 ORDER BY 子句中的列经过函数修饰（如 UPPER(key1)），MySQL 无法使用索引进行排序。
索引用于分组
- 在分组查询中（GROUP BY），如果分组列顺序与联合索引的顺序一致，MySQL 可以直接利用索引进行分组，避免使用临时表，提升查询性能。
- 分组时，索引列的顺序也必须与 GROUP BY 子句中列的顺序一致，或者仅使用联合索引的左边连续部分列进行分组。

回表的代价

在使用 MySQL 的 InnoDB 存储引擎时，二级索引（非聚簇索引）的叶子节点中仅包含索引列和主键 ID。当查询需要返回的不仅是索引列，还包括其他非索引列时，必须通过主键 ID 回到聚簇索引中获取完整的记录，这个过程称为“回表”。回表操作通常会导致额外的随机 I/O 开销，尤其是在查询需要回表的记录非常多时，会显著影响查询性能。

B+树的每层节点会使用双向链表连接，上一个节点和下一个节点页号可以不必相邻。但是在实际场景中，InnoDB还是尽量让同一个索引的叶子节点的页号按照顺序排列。所以扫描区间中的二级索引记录所在页面的页号会尽可能相邻。即便不相邻，一个页面也可以存放很多记录，总之执行一次页面I/O操作后，可以把很多二级索引记录加载到内存，此时付出的代价相对是较小的。但是这些二级索引记录对应的ID值是无序的，每读取一条二级索引记录，就需要回表，而且很可能这条记录所在的聚簇索引不在内存中，需要从磁盘加载，并且这些聚簇索引页的页号分散的几率很大，需要从磁盘中加载大量的聚簇索引页，可能会造成大量的随机I/O。所以需要回表的记录越多，那么二级索引进行查询的性能也就越低，某些查询宁愿用全表扫描也不使用二级索引。

什么时候用全表扫描，什么时候用二级索引+回表？这是查询优化器的工作。而查询优化器会针对表中的一些记录计算统计数据，然后根据统计数据或者访问表中少量数据的方式来计算需要进行回表的记录数。如果回表的记录越多，就更倾向使用全表扫描，反之使用二级索引+回表的方式。一般情况下，可以给查询语句增加LIMIT子句来限制查询返回的记录数，这可能会让查询优化器偏向使用二级索引+回表的方式。

更好的创建和使用索引

只为用于搜索、排序或分组的列创建索引

只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。仅出现在查询列表中的列就没必要建立索引了。比如有这样一个查询语句：

SELECT common_field, key_part3 FROM single_table WHERE key1 = 'a';

查询列表中的common_ field，key_part3这两个列就没有必要创建索引。

考虑索引列中不重复值的个数

在回表的代价中通过二级索引+回表的方式执行查询时，回表的次数越多，查询优化器越有可能选择全表扫描。所以，当为某个列创建索引时，还需要考虑列中不重复值的个数占全部条数的比例。如果比例太低，说明重复值过多，那么执行查询时，就更有可能走全表扫描。

索引列的类型尽量小

整数类型及其存储空间：

常见的整数类型包括：TINYINT、MEDIUMINT、INT、BIGINT，它们占用的存储空间依次递增。
这些类型能够表示的整数范围也随着类型大小的增大而增大。

索引列的类型选择：

当建立索引时，在能满足表示范围的情况下，建议尽量使用较小的整数类型。
例如，如果 TINYINT 足够用，就不要使用 BIGINT；如果 MEDIUMINT 足够用，就不要使用 INT。
原因：数据类型越小，索引占用的存储空间就越少，因此在同一个数据页内可以存储更多的记录，这样可以减少磁盘I/O操作，提高读写效率。

主键列类型的选择：

对于主键列，这个建议尤其重要。因为聚簇索引存储了主键值，而所有的二级索引也都会存储主键值。
使用较小的数据类型作为主键，可以节省更多的存储空间，提升整体性能。