Mysql索引记录之最左前缀

一、where条件验证

创建数据表test。

CREATE TABLE `test` (   `id` bigint NOT NULL,   `a` int DEFAULT NULL,   `b` int DEFAULT NULL,   `c` int DEFAULT NULL,   `d` varchar(45) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `index_a` (`a`,`b`,`c`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

测试sql。

explain SELECT * FROM test  where a =3; -- 5    explain SELECT * FROM test  where a =3 and b=3; -- 10    explain SELECT * FROM test  where a =3 and b=3 and c=4; -- 15   explain SELECT * FROM test  where b =3; -- 用不了 explain SELECT * FROM test  where b =3 and c=4;-- 用不了 explain SELECT * FROM test  where c =4; -- 用不了   explain SELECT * FROM test  where a =3 and c=4;  -- 5   explain SELECT * FROM test  where a =3 and b>4 and c=5; -- 10   explain SELECT * FROM test  where  a=3 and b like 'kk%' and c=4; -- 5   explain SELECT * FROM test  where  a=3 and b like '%kk' and c=4; -- 5   explain SELECT * FROM test  where  a=3 and b like '%kk%' and c=4; -- 5   explain SELECT * FROM test  where  a=3 and b like 'k%kk%' and c=4; -- 5

 

 

总结（select是*）

where 条件	索引使用情况	type	key_len	Extra
a =3	Yes，使用到了a	ref	5
a =3 and b=3	Yes，使用到了a，b	ref	10
a =3 and b=3 and c=4	Yes，使用到了a，b，c	ref	15
b =3	No
b =3 and c=4	No
c =4	No
a =3 and c=4	Yes，使用到了a，没使用到c，因为b中间断了。	ref	5	Using index condition
a =3 and b>4 and c=5;	Yes，使用到了a、b，c不能用在范围之后，b断了。	range	10	Using index condition
a=3 and b like 'kk%' and c=4;	Yes,使用到了a。	ref	5	Using index condition
a=3 and b like '%kk' and c=4	Yes,使用到了a。	ref	5	Using index condition
a=3 and b like '%kk%' and c=4	Yes,使用到了a。	ref	5	Using index condition
a=3 and b like 'k%kk%' and c=4	Yes,使用到了a。	ref	5	Using index condition

 

    总结一下什么最左前缀原则:查询从索引的最左前列开始并且不跳过索引中的列,通俗易懂的来说就是:带头大哥不能死、中间兄弟不能断。

   0)  key_len，可以看出用到了几个索引。

   1） range范围包括本身，比如b>4，后面的字段不使用索引;

   2） like 不包括本身，后面的字段不使用索引；

   3）对于Extra中using index condition的解释：

     查询的列不全在索引中，where条件中是一个前导列的范围；

  或者查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）。

     意味着查询列的某一部分无法直接使用索引

 4）对于Extra中空的解释：

     查询的列未被索引覆盖，并且where筛选条件是索引的前导列，

     意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

   5）type使用了ref的解释：如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。

 

二、select、where、group验证

index_a(a,b,c)，d无索引。
select	where	group by	order by	type	key_ len	Extra	说明
a	a=1	a		ref	5	Using index	查询的列被索引覆盖，并且where筛选条件是索引的是前导列
*	a=1	a		ref	5	空	查询的列未被索引覆盖，并且where筛选条件是索引的前导列，     意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)
a	a=1	b		ref	5	Using index
*	a=1	b		ref	5	空	需回表
a	b=1	a		index	15	Using where; Using index	1）查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的不是前导列，Extra中为Using where; Using index， 　　意味着无法直接通过索引查找来查询到符合条件的数据。 [也是说group用到了索引，而where没有用到索引（b=1不符合最左前缀）]    2）查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据
*	b=1	a		index	15	Using where	1）查询的列未被索引覆盖，where筛选条件非索引的前导列；     2） 查询的列未被索引覆盖，where筛选条件非索引列；    意味着通过索引或者表扫描的方式进程where条件的过滤
b	a=1	b		ref	5	Using index
总结：where条件需要是group的一个基础。按where、group的顺序去匹配索引顺序，反过来，则不能使用索引，或者不能完全使用索引。

 

三、mysql explain extra解释以及勘正

创建数据表user。

create table user ( id int primary key, name varchar(20), sex varchar(5), index(name) )engine=innodb;   insert into user values(1, 'shenjian','no'); insert into user values(2, 'zhangsan','no'); insert into user values(3, 'lisi', 'yes'); insert into user values(4, 'lisi', 'no');

 

1、Using where

实验语句：

explain select * from user where sex='no';

​

结果说明：

Extra为Using where说明，SQL使用了where条件过滤数据。

需要注意的是：

（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；

（2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；

　　本例虽然Extra字段说明使用了where条件过滤，但type属性是ALL，表示需要扫描全部数据，仍有优化空间。

　　常见的优化方法为，在where过滤属性上添加索引。

画外音：本例中，sex字段区分度不高，添加索引对性能提升有限。

2、Using index

实验语句：

explain select id,name from user where name='shenjian';

​

结果说明：

Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

　　这类SQL语句往往性能较好。

3、空

实验语句：

explain select id,name,sex from user where name='shenjian';

​

　画外音：该SQL语句与上一个SQL语句不同的地方在于，被查询的列，多了一个sex字段。

结果说明：

Extra为空，应该近似于 Extra为Using index condition的情况，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录，即需要回表，这类SQL语句性能也较高，但不如Using index。

4、Using filesort

实验语句：

explain select * from user order by sex;

​

结果说明：

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。

　　这类SQL语句性能极差，需要进行优化。

　　典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

5、Using temporary

实验语句：

explain select * from user group by name order by sex;

​

结果说明：

Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。 

　　这类SQL语句性能较低，往往也需要进行优化。

　　典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

6、Using join buffer (Block Nested Loop)

实验语句：

explain select * from user where id in(select id from user where sex='no');

结果说明：

Extra为Using join buffer (Block Nested Loop)说明，需要进行嵌套循环计算。

　　画外音：内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。

　　这类SQL语句性能往往也较低，需要进行优化。

　　典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

   然而，这条语句并没有出现，Using join buffer (Block Nested Loop)，出现的是如下：

​

可能是因为mysql 8.0的不同。子查询sex上没有索引，所以是全表扫描。需要优化。