前言:
眼前兄弟们对“mysql怎么查看索引是否失效”大致比较关注,你们都需要分析一些“mysql怎么查看索引是否失效”的相关内容。那么小编也在网上汇集了一些有关“mysql怎么查看索引是否失效””的相关文章,希望看官们能喜欢,我们一起来了解一下吧!1 前言
基础不牢,地动山摇!!!索引是Mysql提高查询效率的一大利器(针对innodb引擎,以下相同),对于多条件查询的情况,我们可以创建联合索引进一步提高查询效率,但如果使用不当,联合索引就会失效或者使用联合索引不充分,前者比较好理解不进行深入分析,本文主要分析后者的情况。
2 准备工作2.1 数据初始化
首先我们创建一个简单的表
sql复制代码CREATE TABLE `multiple_part_index_demo` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `c1` int(11) NOT NULL DEFAULT '0', `c2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_c1_c2` (`c1`,`c2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;;
这个表创建了一个c1和c2的联合索引,继续添加一些初始数据
sql复制代码insert into `multiple_part_index_demo` (`c1`, `c2`) values(1, 2);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(1, 2);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(2, 4);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(3, 1);insert into `multiple_part_index_demo` (`c1`, `c2`) values(3, 2);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(4, 1);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(6, 1);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(6, 2);insert into `multiple_part_index_demo` (`c1`, `c2`) values(6, 4);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 1);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 1);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 1);insert into `multiple_part_index_demo` (`c1`, `c2`) values(8, 2);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 2);INSERT INTO `multiple_part_index_demo` (`c1`, `c2`) VALUES(8, 4);
通过执行 select * from multiple_part_index_demo; 最终的表数据显示如下
data复制代码+----+----+----+| id | c1 | c2 |+----+----+----+| 1 | 1 | 2 || 2 | 1 | 2 || 3 | 2 | 1 || 4 | 3 | 1 || 5 | 3 | 3 || 6 | 4 | 1 || 7 | 6 | 2 || 8 | 6 | 3 || 9 | 6 | 4 || 10 | 8 | 1 || 11 | 8 | 1 || 12 | 8 | 1 || 13 | 8 | 2 || 14 | 8 | 2 || 15 | 8 | 4 |+----+----+----+2.2 构建B+树
我们根据初始化的数据构建一棵B+树,这里不讲解如何构造B+树,可查阅其他资料帮助消化。Myqsl实际页大小为16kb,这里假设每页只能存放3条用户数据,超过3条页就会分裂,图中不同颜色代表不同的涵义,注意右上角的颜色说明。
3 索引命中分析
我们都知道索引都是有序的,它会按字段值的大小升序排序,那如果是联合索引呢?比如给出的c1和c2列联合索引,首先会根据c1比较,如果比较c1就可以比出大小,那么c1小的就会排在前面,否则继续比较c2,c2比较小的排在前面,这个规律应该不难理解,但也非常非常重要,下面分析的索引使用列的情况就是依据这个规则。
3.1 等值查询
执行sql1
sql复制代码sql1: SELECT * FROM multiple_part_index_demo WHERE c1 = 6 AND c2 = 2;
查询结果如下,返回一条数据
diff复制代码+----+----+----+| id | c1 | c2 |+----+----+----+| 8 | 6 | 2 |+----+----+----+
我们再看下这条sql命中索引的情况
sql复制代码+----+-------------+--------------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | multiple_part_index_demo | NULL | ref | idx_c1_c2 | idx_c1_c2 | 8 | const,const | 1 | 100.00 | Using index |+----+-------------+--------------------------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
我们主要看看explain中的key_len,key_len表示执行sql命中索引使用列的字节数之和,这里的长度为8,因为c1和c2都是int类型,固定长度为4个字节,4 + 4 = 8,所以可以推出c1和c2都被使用到。
再根据B+树的结构来分析下,条件c1=6,在p1页里因为1 < 6 < 8,所以可以找到索引目录项(1, 2),而(8, 1)就被过滤掉了,下面的页过滤不符合条件的项原理相同,所以可以得到查找的路径和叶子节点的数据(p6页是叶子节点,括号内分别是c1和c2)
scss复制代码p1(1, 2) -> p2(6, 1) -> p6(6, 2)(6, 3)(6, 4)
因为最终的叶子节点的数据c1都是6,所以c2是有序的,此时可以根据c2=2条件来较少扫描行数(这里一条记录就是一行),我们分步骤来分析下c2的比较情况
(1) 首先与(6, 2)比较,2=2符合条件返回给客户端,因为2>=2,因此需要继续往下扫描比较
(2) 与(6, 3)比较,2!=3无需返回给客户端,并且2<3,c2是有序排序,因此后面的肯定比2大,因此可以直接推出扫描 因此,可以看到,用索引查找的过程中c1和c2都是有用到,所以explain出来的key_len=8。
3.2 无等号范围查询
执行下sql2
vbnet复制代码sql2: SELECT * FROM multiple_part_index_demo WHERE c1 < 3 AND c2 = 2;
查询结果如下,返回两条数据
diff复制代码+----+----+----+| id | c1 | c2 |+----+----+----+| 1 | 1 | 2 || 2 | 1 | 2 |+----+----+----+
看下这条sql命中索引的情况
sql复制代码+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | multiple_part_index_demo | NULL | range | idx_c1_c2 | idx_c1_c2 | 4 | NULL | 3 | 10.00 | Using where; Using index |+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
这种情况的key_len为4,说明索引只使用到了c1列。在这里我再把图贴过来,免得再去翻。
再来分析下这种情况索引命中的情况,根据条件c1 < 3,查找节点的原理和上面的一样不再分析,可以得到查找的路径和叶子节点的数据
scss复制代码p1(1, 2) -> p2(1, 2) -> p4(1, 2)(1, 3)(2, 1)
最后看下叶子节点是怎么过滤不符合条件的记录,c1包含了两个值1和2,导致c2并不是有序排列的,所以三条记录需要拿出来遍历判断c2是否等于2,具体步骤如下
(1) 与(1, 2)比较,2=2符合条件返回给客户端,因为2>=2,因此需要继续往下扫描比较
(2) 与(1, 3)比较,3!=2不符合条件,虽然2<3,但是c2是无序的,因此需要继续往下扫描比较
(3) 与(2, 1)比较,1!=2不符合条件,全部扫描完成,推出扫描
因此,范围查询条件后的列不会被索引使用。
3.3 有等号范围查询
上面给出了两种情况,别以为完了,还剩下一种。
vbnet复制代码sql3: SELECT * FROM multiple_part_index_demo WHERE c1 <= 3 AND c2 = 2;
看查询结果,此时仍然返回两条数据
diff复制代码+----+----+----+| id | c1 | c2 |+----+----+----+| 1 | 1 | 2 || 2 | 1 | 2 |+----+----+----+
继续看下执行计划
sql复制代码+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | multiple_part_index_demo | NULL | range | idx_c1_c2 | idx_c1_c2 | 8 | NULL | 4 | 10.00 | Using where; Using index |+----+-------------+--------------------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
key_len为8,很明显和sql2的情况不一样,我们再把图贴一下。
查找的路径和叶子节点的数据为
scss复制代码p1(1, 2) -> p2(1, 2)(3, 1) -> p4(1, 2)(1, 3)(2, 1), p5(3, 1)(3, 3)(4, 1)
查询条件c2=2,分析下叶子节点的数据过滤具体步骤
(1) 与(1, 2)比较,2=2符合条件返回给客户端,因为2>=2,因此需要继续往下扫描比较
(2) 与(1, 3)比较,3!=2不符合条件,虽然2<3,但是c2是无序的,因此需要继续往下扫描比较
(3) 与(2, 1)比较,1!=2不符合条件,虽然1<3,但是c2是无序的,因此需要继续往下扫描比较
(4) 与(3, 1)比较,1!=2不符合条件,并且1<2,因为c1等于3的记录c2是有序的,所以后面的记录要c1比较大,要么c2比较大,因此不需要再继续往下扫描比较
这里可以看成是等值查询和范围查询的结合,sql3等价于下面这条sql
ini复制代码SELECT * FROM multiple_part_index_demo WHERE (c1 < 3 AND c2 = 2) OR (c1 = 3 AND c2 = 2);4 总结
经过上面的分析,我们可以得出,当查询条件为范围查询时,索引后面的列就不会被用到,开发过程中应避免这种sql,把范围查询改成等值查询(=或者in),可能你的sql查询效率会更高。
作者:初心不改_1
链接:
标签: #mysql怎么查看索引是否失效