mysql中索引如何在查询执行期间发挥作用

大家好，上篇文章我们聊了一下什么是索引，索引是如何形成的以及通过索引查找到用户记录的过程是什么。在讲今天的内容之前，我们先来总结一下索引的几个特点：

1. 每个索引都对应一棵B+树，所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
2. InnoDB 存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
3. 用户可以自己建立二级索引，二级索引的叶子节点包含的用户记录由索引列+主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作。
4. B+ 树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表。 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。

总结完后，我们想一个问题，既然创建索引可以快速定位到目标数据，那是不是一张表创建的索引越多越好呢？答案肯定是否定的，创建索引是有“代价”的。

空间上的代价

我们知道，每创建一个索引时都会产生一棵B+树，B+树的每个节点都是一个数据页，每个数据页默认都会占16KB的存储空间，建的索引越多，占的空间就越多。

时间上的代价

虽然创建索引能够减少对表中数据查询的时间，但是当对表中数据进行增删改时，要修改这个表创建的各个B+树索引，，B+树每层节点都 是按照索引列的值从小到大的顺序排序而组成了双向链表。增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们先了解一下索引在查询执行的过程中是如何发挥自己的作用的。

为了方便讲解，在这里我们先建立一个test_table表并创建了一个聚簇索引（id）、三个二级索引（c1、c2、（c4、c5）），其中c4和c5为联合索引。注意：c3列没有创建索引

CREATE TABLE `javaTuT`.`test_table`  (
  `id` int NOT NULL,
  `c1` varchar(100) NULL,
  `c2` int NULL,
  `c3` varchar(100) NULL,
  `c4` varchar(100) NULL,
  `c5` varchar(100) NULL,
  PRIMARY KEY (`id`),
  INDEX `idx1`(`c1`),
  INDEX `idx2`(`c2`),
  INDEX `idx3`(`c4`, `c5`)
);

我们知道B+树中叶子节点的记录是按照索引列从小到大排列的，所以在以索引列查找记录时，只扫描某个区间或者某些区间的记录即可，从而快速定位到目标记录，例如拿下面这个sql来说：

select * from test_table where id > 1 and id < 100 

这个语句其实是想找id在（1,100）区间中的所有聚簇索引记录，通过聚簇索引快速定位到id为1的那条聚簇索引记录，然后往后找，直到找到id不在（1,100）区间内的记录为止。所以只需查找1-100的聚簇索引记录就行，大大提升查询效率，这里我们称带扫描的id值所在区间（1,100）称之为扫描区间，把形成这个扫描区间的查询条件（id > 1and id < 100）称之为这个扫描区间的边界条件。

在确认扫描区间时，要注意以下几点：

1. in操作符的语义与若干个=操作符之间用or连接形成的扫描区间是一样的。

select * from test_table where c2 in（100，200） 
select * from test_table where c2 = 100 or c2 = 200

这两条sql的扫描区间都是[100,100]∪[200,200]

2. !=操作符形成的扫描区间是=操作符的相反区间

select * from test_table where c2 != 100 

这条sql的扫描区间是（-∞，100）∪（100，+∞）

3. like操作符只有在匹配完整字符或者匹配字符串前缀时才会产生合适的扫描区间。

select * from test_table where c1 like '%a' 

like操作符在比较大小时，先比较第一个字符的大小，如果第一个字符一样，再比较第二个字符的大小，以此类推。这条sql就是先定位到c1值前缀是’a’的第一条记录，然后往后找，直到找到前缀为’b’的为止，所以这条sql的扫描区间是[‘a’，‘b’）

了解了扫描区间和边界条件后，我们看一些复杂的情况下该如何确定扫描区间。

1. 所有条件都能生成合适的扫描区间的情况

select * from test_table where c2 > 100 and c2 > 200 or c2 < 50

这条sql包含两个查询条件，且两个查询条件都能生成适合的扫描空间（100，+∞）、（200，+∞）和（-∞，50），因为前两个条件是用and连接，所以取这两个的交集（200，+∞），最后一个条件用or连接的，那么就要取前两个的交集（200，+∞）和最后一个（-∞，50）的并集，所以这条sql的扫描区间是（-∞，50）∪（200，+∞）。

2. 有的条件不能生成合适的扫描区间的情况

select * from test_table where c2 > 100 and c3 > 'aaa'

这条sql包含两个查询条件，其中c2 > 100 搜索条件可以形成扫描范围（100，+∞），但是c1列的二级索引记录中不会包含c3列信息，所以c3 > ‘aaa’ 搜索条件形成的扫描范围是（+∞，-∞），因为两个搜索条件用and连接，所以这条sql的扫描区间为（100，+∞）∩（+∞，-∞），也就是（100，+∞）。像c3 > ‘aaa’ 这种不起作用的搜索条件我们可以简化成true。

select * from test_table where c2 > 100 and true

3. 从复杂的搜索条件中找出扫描区间

select * from test_table where (c1 > 'aaa' and c2 > 100 ) and 
( c1 like '%ccc' or c2 < 999 ) or ( c1 < 'bbb' and c3 = 'aaa' )

这条sql一看查询条件太乱了，不过没关系，我们按照下面的方式来确认扫描区间。

假设使用c1列B+树索引执行查询

我们先把不能形成合适的扫描区间的条件变为true，由于使用c1列索引，所以c2列和c3列查询条件都无法形成合适的扫描区间，同时c1 like ‘%ccc’ 的扫描区间为（+∞，-∞），我们也变为true，所以简化后的sql为：

select * from test_table where (c1 > 'aaa' and true ) and 
( true or true ) or( c1 < 'bbb' and true )

最终简化为：

select * from test_table where (c1 > 'aaa') and ( c1 < 'bbb' )

所以扫描区间为（‘aaa’，+∞）∩ （-∞，‘bbb’）也就是（‘aaa’，‘bbb’）。

假设使用c2列B+树索引执行查询

同样道理，我们先把不能形成合适的扫描区间的条件变为true，简化后的sql为：

select * from test_table where ( true and c2 > 100 ) and 
( true or c2 < 999 ) or ( true  and true )

最终简化为：

select * from test_table where true

所以扫描区间为（-∞，+∞）。

相对于两者来说，肯定是c1列索引确定的扫描范围更小，所以这条sql的扫描范围为c1列B+树索引的（‘aaa’，‘bbb’）。

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

联合索引包含了多个列，所以在B+树中每层页面以及每个页面采用的排序规则也较为复杂。以test_table中的idx3联合索引为例，它的排序规则是先按照c4列从小到大进行排序，当c4列相同时，再按照c5列从小到大进行排序（这句话很重要）。下面通过几个sql来讲一下如何查找联合索引对应的扫描空间。

select * from test_table where c4 = 'a'

这条sql执行时，先定位到符合 c4 = ‘a’ 的第一条数据，然后往后找，直到找到不符合 c4 = ‘a’ 条件的第一条数据为止，所以这条sql对应的扫描区间就是[‘a’，‘a’]。

select * from test_table where c4 = 'a' and c5 = 'b'

这条sql执行时，先定位到符合 c4 = ‘a’ 和 c5 = ‘b’ 的第一条数据，然后往后找，直到找到不符合 c4 = ‘a’ 和 c5 = ‘b’ 条件的第一条数据为止，所以这条sql对应的扫描区间就是[（‘a’，‘b’），（‘a’，‘b’）]。

select * from test_table where c4 < 'a'

这条sql执行时，先定位到符合 c4 < ‘a’ 的第一条数据，然后往后找，直到找到不符合 c4 < ‘a’ 条件的第一条数据为止，所以这条sql对应的扫描区间就是（-∞，‘a’）。

select * from test_table where c4 = 'a' and c5 > 'a' and c5 <'b'

这条sql执行时，先定位到符合 c4 = ‘a’ 并且 c5 > ‘a’ 并且 c5 <'b’的第一条数据，然后往后找，直到找到不符合条件的第一条数据为止，所以这条sql对应的扫描区间就是[（‘a’，‘a’），（‘a’，‘b’）]。

select * from test_table where c5 = 'a'

由于联合索引是先按照c4列进行排序，当c4列的数据相同时，再按照c5列进行排序的，所以满足 c5 = ‘a’ 条件的数据可能并没有连续在一起，所以这条sql不会走idx3联合索引。

select * from test_table where c4 < 'a' and c5 = 'a'

这条sql中，首先满足 c4 < ‘a’ 条件的记录中，满足 c5 = ‘a’ 条件的数据可能并没有连续在一起，所以 c5 = ‘a’ 条件并不能用来确认扫描区间，所以这条sql对应的扫描区间就是（-∞，‘a’）。

select * from test_table where c4 <= 'a' and c5 = 'a'

这条sql和上一条sql很像，只是c4查询条件多了个 = ，但就是多的这个 = 让两条sql对应的扫描区间不同，这条sql在查询时，对于 c4 <='a’条件的二级索引记录来说，满足 c5 = ‘a’ 条件的数据可能并没有连续在一起，但是对于c4 ='a’条件的二级索引记录来说，满足 c5 = ‘a’ 条件的数据是连续在一起，所以只要扫描到不符合 c4 =‘a’ 并且 c5 = ‘a’ 条件的记录时，就可以停止扫描，所以这条sql对应的扫描区间就是[（-∞，+∞）,（‘a’,‘a’）]。

今天主要讲了索引是如何在查询执行期间发挥作用的，主要就是按照查询条件去明确sql对应的扫描区间，然后在扫描区间中去查找目标记录，从而增加查询的效率。

最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！