MySQL深入思考索引在查询中的使用

时间：2025-03-11 07:06:18

索引在查询中的作用到底是什么？在我们的查询中发挥着什么样的作用呢？
请记住：

  1. 一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度。
  2. 一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引。

t_emp表中的索引：

mysql> show index from t_emp;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_emp |          0 | PRIMARY        |            1 | emp_no      | A         |      299246 |     NULL | NULL   |      | BTREE      |         |               |
| t_emp |          1 | idx_birth_date |            1 | birth_date  | A         |        4851 |     NULL | NULL   |      | BTREE      |         |               |
| t_emp |          1 | idx_hire_date  |            1 | hire_date   | A         |        5128 |     NULL | NULL   |      | BTREE      |         |               |
| t_emp |          1 | udx_union_test |            1 | hire_date   | A         |        4809 |     NULL | NULL   |      | BTREE      |         |               |
| t_emp |          1 | udx_union_test |            2 | birth_date  | A         |      297738 |     NULL | NULL   |      | BTREE      |         |               |
| t_emp |          1 | udx_union_test |            3 | first_name  | A         |      299246 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

扫描区间

对于某个查询来说，最简单粗暴的执行方案就是扫描表中的所有记录，判断每一条记录是否符合搜索条件。如果符合，就将其发送到客户端，否则就跳过该记录。这就是全表扫描。

对于使用InnoDB存储引擎的表来说，全表扫描意味着从聚簇索引第一个叶子节点的第一条记录开始，沿着记录所在的单向链表向后扫描，直到最后一个叶子节点的最后一条记录。虽然全表扫描是一种很笨的执行方案，但却是一种万能的执行方案，所有的查询都可以使用这种方案来执行，只是效率不高。

我们有了索引，利用B+树查找索引列值等于某个值的记录，这样可以明显减少需要扫描的记录数量。由于B+树叶子节点中的记录是按照索引列值由小到大的顺序排序的，所以即使只扫描某个区间或者某些区间中的记录也可以明显减少需要扫描的记录数量。比如下面这个查询语句：

SELECT * FROM t_emp WHERE emp_no >= 3 AND emp_no<= 99;

这个语句其实是想查找emp_no值在[3,99]区间中的所有聚簇索引记录。我们可以通过聚簇索引对应的B+树快速地定位到emp_no值为3的那条聚簇索引记录，然后沿着记录所在的单向链表向后扫描，直到某条聚簇索引记录的emp_no值不在[3,99]区间中为止。

与全表扫描相比，扫描emp_no值在[3,99]区间中的记录已经很大程度地减少了需要扫描的记录数量，所以提升了查询效率。其实所谓的全表扫描，我们可以理解为扫描的区间是[负无穷，正无穷]或者[第一条记录，最后一条记录]。

再看下面这个查询语句:

SELECT * FROM t_emp WHERE emp_no in(3,9) OR (emp_no>=23 AND emp_no<=99);

这里有几个扫描区间？三个，两个单独扫描区间[3,3]、[9,9]，一个范围扫描区间[23,99]。

再看下面这个查询语句:

SELECT * FROM t_emp WHERE birth_date < '2021-03-22' AND hire_date> '2021-03-22' AND first_name > 'Parto';

这个语句里，birth_date和hire_date都有索引，first_name没有索引，那会有两个扫描区间吗？并不会，请记住，一个Select查询语句在执行过程中一般最多能使用一个二级索引。那么也就是说：如果用idx_birth_date执行查询，那扫描区间就是[第一条记录，‘2021-03-22’)，hire_date> '2021-03-22' AND first_name > 'Parto'只能成为普通的搜索或者说判定条件。如果说用idx_hire_date执行查询，那扫描区间就是(‘2021-03-22’，最后一条记录]，birth_date < '2021-03-22' AND first_name > 'Parto'只能成为普通的搜索或者说判定条件。

无论用哪个索引执行查询，都需要获取到索引中的记录后，进行回表，获取到完整的用户记录后再根据判定条件判断这条记录是否满足SQL语句的要求。

范围区间扫描

其实对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个区间。

  1. IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的，也就是说会产生多个单点区间，比如下边这两个语句的效果是一样的：
SELECT * FROM t_emp WHERE hire_date IN ('2021-03-22', '2021-03-24');
SELECT * FROM t_emp WHERE hire_date='2021-03-22' OR hire_date='2021-03-24';
  1. !=产生的扫描区间呢？比如：
SELECT * FROM t_emp WHERE hire_date != '2021-03-22'

此时使用hire_date执行查询时对应的扫描区间就是[第一条记录,‘2021-03-22’)和(‘2021-03-22’,最后一条记录]。

  1. LIKE操作符比较特殊，只有在匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间。对于某个索引列来说，字符串前缀相同的记录在由记录组成的单向链表中肯定是相邻的。比如：
 select * from employees where last_name like 'b%'

我们有一个搜索条件是last_name like 'b%'，对于二级索引idx_last_name来说，所有字符串前缀为b的二级索引记录肯定是相邻的。这也就意味着我们只要定位到值的字符串前缀为b的第一条记录，就可以沿着记录所在的单向链表向后扫描，直到某条二级索引记录的字符串前缀不为b为止。很显然，last_name LIKE' b%'形成的扫描区间相当于[‘b’, ‘c’)。

不过在日常的工作中，一个查询的WHERE子句可能有很多个小的搜索条件，这些搜索条件需要使用AND或者OR操作符连接起来，我们来看看怎么从由AND或OR组成的复杂搜索条件中提取出正确的范围区间。

所有搜索条件都可以使用某个索引的情况

有时候每个搜索条件都可以使用到某个索引，比如下边这个查询语句：

SELECT * FROM t_emp WHERE hire_date > '2021-03-22' and hire_date > '2021-06-22'

这个查询中的搜索条件都可以使用到idx_hire_date，也就是说每个搜索条件都对应着一个idx_hire_date的范围区间。这两个小的搜索条件使用AND连接起来，也就是要取两个范围区间的交集，两者交集当然就是hire_date > '2021-06-22'了，也就是说上边这个查询使用idx_hire_date的范围区间就是(‘2021-06-22’, 最后一条记录]。

再看一下使用OR将多个搜索条件连接在一起的情况：

SELECT * FROM t_emp WHERE hire_date > '2021-03-22' or hire_date > '2021-06-22'

OR意味着需要取各个范围区间的并集，所以上边这个查询使用idx_hire_date的范围区间就是( ‘2021-03-22’ ,最后一条记录]。

有的搜索条件无法使用索引的情况

比如下边这个查询：

SELECT * FROM t_emp WHERE hire_date> '2021-03-22' AND first_name = 'abc'

请注意，这个查询语句中能利用的索引只有idx_hire_date一个，而idx_hire_date这个二级索引的记录中又不包含first_name这个字段，所以在使用二级索引idx_hire_date定位记录的阶段用不到first_name='abc'这个条件，这个条件是在回表获取了完整的用户记录后才使用的，而范围区间是为了到索引中取记录中提出的概念，所以在确定范围区间的时候不需要考虑first_name='abc'这个条件。

我们把上边的查询中用不到idx_hire_date的搜索条件化简之后就是这样：

SELECT * FROM t_emp WHERE hire_date> '2021-03-22'

也就是说最上边那个查询使用idx_hire_date的范围区间就是：(‘2021-03-22’,最后一条记录]。

再来看一下使用OR的情况：

SELECT * FROM t_emp WHERE hire_date> '2021-03-22' OR first_name = 'abc'

这条语句在搜索时可以化简为：

SELECT * FROM t_emp

这也就说如果我们使用idx_hire_date执行查询的话，对应的范围区间就是[第一条记录,最后一条记录]，也就是需要将全部二级索引的记录进行回表，这个代价肯定比直接全表扫描都大了。也就是说一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。为什么？道理很简单，idx_hire_date这个二级索引的记录中不包含first_name这个字段，也就是说，即使二级索引idx_hire_date中找到了满足hire_date> '2021-03-22'的记录，是无法判定first_name是否满足first_name = 'abc'的，又因为是OR条件，所以必须要在主键索引中从第一条记录到最后一条记录逐条判定first_name是否等于’abc’。

复杂搜索条件下找出范围匹配的区间

有的查询的搜索条件可能特别复杂，比方说下边这个：

SELECT * FROM t_emp WHERE (hire_date > '2021-01-01' AND birth_date = '2021-03-22' ) 
OR (hire_date < '2021-06-01' AND hire_date > '2021-09-01') 
OR (hire_date LIKE '%01' AND hire_date > '2021-10-01' AND (birth_date < '2021-03-22' OR first_name = 'abc'))

分析一下：
首先查看WHERE 子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引。这个查询的搜索条件涉及到了hire_date、birth_date、first_name这3个列，然后hire_date列有二级索引idx_hire_date，birth_date列有二级索引
idx_birth_date。
对于那些可能用到的索引，分析它们的范围区间。

  1. 使用idx_hire_date执行查询

我们需要把那些用不到该索引的搜索条件暂时移除掉。上边的查询中除了有关birth_date和first_name列不能使用到idx_hire_date索引外，hire_date LIKE '%01'也使用不到索引。

如果条件太复杂，我们可以把所有用不到的搜索条件视为True来进行中间替换，所以把这些搜索条件替换为True之后的样子就是这样：

SELECT * FROM t_emp WHERE (hire_date > '2021-01-01' AND true ) 
OR (hire_date < '2021-06-01' AND hire_date > '2021-09-01') 
OR (true AND hire_date > '2021-10-01' AND (true OR true))

再化简：

SELECT * FROM t_emp WHERE (hire_date > '2021-01-01' AND true ) 
OR (hire_date < '2021-06-01' AND hire_date > '2021-09-01') 
OR (true AND hire_date > '2021-10-01' AND (true OR true))

接下来替换掉永远为TRUE或FALSE的条件，因为符合hire_date < '2021-06-01' AND hire_date > '2021-09-01'永远为FALSE，所以上边的搜索条件可以被写成这样：

SELECT * FROM t_emp WHERE (hire_date > '2021-01-01' ) OR (hire_date > '2021-10-01')

很明显，两者使用OR操作符连接起来的，意味着要取并集，所以最终的结果化简的到的区间就是：(‘2021-01-01’, 最后一条记录]，也就是说：上边那个复杂搜索条件的查询语句如果使用idx_hire_date索引执行查询的话，需要把满足hire_date > '2021-01-01'的二级索引记录都取出来，然后拿着这些记录的id再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。记住，我们说的是如果使用idx_hire_date索引执行查询，不代表MySQL一定会使用，因为MySQL需要做整体评估，才能确定是否使用这个索引还是别的索引，或者是干脆全表扫描。

  1. 使用idx_birth_date执行查询

我们需要把那些用不到该索引的搜索条件暂时使用TRUE条件替换掉，其中有关hire_date和first_name的搜索条件都需要被替换掉，替换结果就是：

SELECT * FROM t_emp WHERE (true AND birth_date = '2021-03-22' ) 
OR (true AND true) 
OR (true AND true AND (birth_date < '2021-03-22' OR true))

按照布尔运算的规则，birth_date < '2021-03-22' OR true的结果肯定是TRUE，也就是说化简之后的搜索条件成这样了：

SELECT * FROM t_emp WHERE (birth_date = '2021-03-22' ) 
OR (true)

这个化简之后的结果就更简单了：

SELECT * FROM t_emp WHERE true

这个结果也就意味着如果我们要使用idx_birth_date索引执行查询语句的话，需要扫描idx_birth_date二级索引的所有记录，然后再回表，这种情况下为啥MySQL不直接全表扫描呢？所以一定不会使用idx_birth_date索引的。

使用联合索引执行查询时对应的扫描区间

联合索引的索引列包含多个列，B+树每一层页面以及每个页面中的记录采用的排序规则较为复杂，以t_emp表的udx_union_test(hire_date, birth_date, first_name)联合索引为例，它采用的排序规则如下所示：

  1. 先按照hire_date列的值进行排序。
  2. 在hire_date列的值相同的情况下，再按照birth_date列的值进行排序。
  3. 在hire_date和birth_date列的值都相同的情况下，再按照first_name列的值进行排序。

对于下边这个查询Q1来说：

SELECT * FROM t_emp WHERE hire_date = '2021-03-22';

由于二级索引记录是先按照hire_date列的值进行排序的，所以所有符合hire_date = '2021-03-22'条件的记录肯定是相邻的，我们可以定位到第一条符合hire_date = '2021-03-22'条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合hire_date = '2021-03-22'条件为止(当然，对于获取到的每一条二级索引记录都要执行回表操作)。

也就是说，如果我们使用udx_union_test索引执行查询Q1时，对应的扫描区间就是[‘2021-03-22’, ‘2021-03-22’]，形成这个扫描区间的条件就是hire_date = '2021-03-22'。

对于下边这个查询Q2来说：

SELECT * FROM t_emp WHERE hire_date = '2021-03-22' AND birth_date = '2021-06-22';

由于二级索引记录是先按照hire_date列的值进行排序的；在hire_date列的值相等的情况下，再按照birth_date列进行排序。所以符合hire_date = '2021-03-22' AND birth_date = '2021-06-22'条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合hire_date = '2021-03-22' AND birth_date = '2021-06-22'条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合hire_date = '2021-03-22'条件或者birth_date = '2021-06-22'条件为止。

也就是说，如果我们使用udx_union_test索引执行查询Q2时，可以形成扫描区间[(‘2021-03-22’, ‘2021-06-22’), (‘2021-03-22’, ‘2021-06-22’)]，形成这个扫描区间的条件就是hire_date = '2021-03-22' AND birth_date = '2021-06-22'。

对于下边这个查询Q3来说：

SELECT * FROM t_emp WHERE hire_date = '2021-03-22' AND birth_date = '2021-06-22' AND first_name = 'morris';

由于二级索引记录是先按照hire_date列的值进行排序的；在hire_date列的值相等的情况下，再按照birth_date列进行排序；在hire_date和birth_date 列的值都相等的情况下，再按照first_name列进行排序。所以符合hire_date = '2021-03-22' AND birth_date = '2021-06-22' AND first_name = 'morris'条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合hire_date = '2021-03-22' AND birth_date = '2021-06-22' AND first_name = 'morris'条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合hire_date = '2021-03-22'条件或者birth_date = '2021-06-22'条件或者first_name = 'morris'条件为止。如果我们使用udx_union_test索引执行查询Q3时，可以形成扫描区间[(‘2021-03-22’, ‘2021-06-22’, ‘morris’), (‘2021-03-22’, ‘2021-06-22’, ‘morris’)]，形成这个扫描区间的条件就是hire_date = '2021-03-22' AND birth_date = '2021-06-22' AND first_name = 'morris'。

对于下边这个查询Q4来说：

SELECT * FROM t_emp WHERE hire_date < '2021-03-22';

由于二级索引记录是先按照hire_date列的值进行排序的，所以所有符合hire_date < '2021-03-22'条件的记录肯定是相邻的，我们可以定位到第一条符合hire_date < '2021-03-22'条件的记录，然后沿着记录所在的链表向前扫描，直到某条记录不符合hire_date < '2021-03-22'为止。

也就是说，如果我们使用udx_union_test索引执行查询Q4时，可以形成扫描区间[第一条记录, ‘2021-03-22’)，形成这个扫描区间的条件就是hire_date < '2021-03-22'。

对于下边这个查询Q5来说：

SELECT * FROM t_emp WHERE hire_date = '2021-03-22' AND birth_date >= '2021-06-22';

由于二级索引记录是先按照hire_date列的值进行排序的；在hire_date列的值相等的情况下，再按照birth_date列进行排序。也就是说在符合hire_date = '2021-03-22'条件的二级索引记录中，是按照birth_date列的值进行排序的，那么此时符合hire_date = '2021-03-22' AND birth_date >= '2021-06-22'条件的二级索引记录肯定是相邻的。我们可以定位到第一条符合hire_date = '2021-03-22' AND birth_date >= '2021-06-22'条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合hire_date = '2021-03-22'条件或者birth_date >= '2021-06-22'条件为止。

也就是说，如果我们使用udx_union_test索引执行查询Q5时，可以形成扫描区间，条件就是hire_date = '2021-03-22' AND birth_date >= '2021-06-22'。

对于下边这个查询Q6来说：

SELECT * FROM t_emp WHERE birth_date = '2021-06-22';

由于二级索引记录不是直接按照birth_date列的值排序的，所以符合birth_date = '2021-06-22'的二级索引记录可能并不相邻，也就意味着我们不能通过这个birth_date = '2021-06-22'搜索条件来减少需要扫描的记录数量。在这种情况下，我们是不会使用udx_union_test索引执行查询的。

对于下边这个查询Q7来说：

SELECT * FROM t_emp WHERE hire_date = '2021-03-22' and first_name = 'morris'

由于二级索引记录是先按照hire_date列的值进行排序的，所以符合hire_date = '2021-03-22'条件的二级索引记录肯定是相邻的，但是对于符合hire_date = '2021-03-22'条件的二级索引记录来说，并不是直接按照first_name列进行排序的，也就是说我们不能根据搜索条件first_name = 'morris'来进一步减少需要扫描的记录数量。那么如果我们使用udx_union_test索引执行查询的话，可以定位到第一条符合hire_date = '2021-03-22'条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合hire_date = '2021-03-22'条件为止。所以在使用udx_union_test索引执行查询Q7的过程中，对应的扫描区间其实是[‘2021-03-22’, ‘2021-03-22’]，形成该扫描区间的搜索条件是hire_date = '2021-03-22'，与first_name = 'morris'无关。

相关文章