MySQL索引基础篇_二级索引

基础

什么是索引

我们完全可以将索引可以看作是书本的目录吧，通过书本的目录查找对应的章节就会快一些，索引底层数据结构有b树、b+树`、红黑树、哈希表等，对于MySQL而言，无论Innodb 还是MyIsam，使用的都是b+树。

使用索引会带来那些优缺点

优点

1. 索引的创建减少了服务器需要扫描的数据量。
2. 索引使得服务器可以避免排序和创建临时表。
3. 索引将随机IO变为顺序IO。

缺点

1. 存储索引自然是需要物理空间的，所以如果在数据量极大的情况下，大量创建索引也会占用大量存储空间。
2. 创建和维护索引也是有一定开销了，如果对数据进行频繁的增删改，会降低SQL的执行效率。

使用索引是否一定带来性能的提升？

不一定，若数据量不大的情况下，建立索引不一定会带来性能的提升。

几个索引常见的底层结构

上面说了几个底层数据结构，我们不妨来一个个进行介绍吧:

哈希结构

这就是一种键值对的结构，在保证哈希算法能够均摊散列的情况下，查询时间复杂度为O(1)，至于什么是均摊散列，说白了每一个数据算出来的key都是唯一的，若重复了我们只能通过链地址法解决冲突，这样查询数据的时间复杂度就不一定是O(1)了。
你可能会问为什么MySQL不适用哈希结构呢？原因很简单：

1. 哈希查询每次IO只能查询一条数据。
2. 哈希范围查询性能很差，如下图所示，每个数据经过哈希算法存到哈希表中的位置是随机的，你无法通过范围进行快速筛选，而是对范围每个值进行哈希并获取到对应的哈希值才能实现数据逐一获取。

B树和B+树

B树和B+树差不多，都是多路自平衡查找树，整体总结有以下几个区别:

1. B树每个节点存放的都是key和data，而B+树只有叶子节点存放key和data，非叶子节点存放的都是key。
2. B树不像B+树那样叶子节点有用引用链连接起来。
3. B树的查询相当于对范围内的节点做二分查找，可能还没到叶子节点就可以找到结果并直接返回了(上面说了B树每个节点存放的都是key和data)，而B+树必须到达叶子节点才能找到data,而且B+树叶子节点还存在引用链的原因，范围查询还是很有优势的。

B树如下图所示

再看看B+树，可以看到每个非叶子节点存储的都是子节点的指针，而叶子节指向被索引的数据，而且这个树是按照顺序进行排序的，所以b+树是非常适合范围查询的。

需要补充一点，如果存储引擎为MyISAM ，我们会根据索引算法查找数据，若找到对应的data域，我们就可以从data域中获取到数据的地址，从而获取数据，这就是我们说的非聚簇索引。

相比之下InnoDB的底层数据结构就比较特殊了，它将数据和索引都存放到一个文件中，所以文件本身就是按照B+树组织的一种索引结构，这棵树的叶子节点的data域保存的都是完整的数据记录。

而其余的索引则称为辅助索引，所以当我们使用辅助索引查找数据时，会根据辅助索引找到对应的data域，从data域中获取到主键的值，然后用这个值在文件中找到对应的数据，这也是我们说的非聚簇索引。

所以我们在建立主键时，建议建立的主键不要太长，也不要是那种非单调的字段作为主键。

如果没创建主键，MySQL如何创建主键树

实际上在我们数据表中有创建主键索引的情况下，那么这个索引结构就是基于我们自定义的主键索引字段。若没有的话，InnoDB 会从表中找到不为null的唯一索引字段作为主键索引。若还是没有那么InnoDB 就会自己生产一个6byte的字段作为自增主键。

索引的集中类型

主键索引(Primary Key)

在InnoDB 索引结构就是基于主键索引构成的，主键索引要求索引字段不为null且唯一，如果我们设计的表中没有主键字段，则InnoDB 会从表中找到唯一且不为null的唯一索引作为主键索引。若没有唯一且不为null的索引，那么InnoDB 就会自动生成一个6byte的自增主键作为主键索引。

二级索引

二级索引是一级索引下一级的索引，它的data域存放的是一级索引的值，我们可以通过二级节点的data找到一级索引的值，从而定位到一级索引进而获取到数据。
二级索引可以是:

1. 唯一索引(Unique Key):这种索引允许null的，它的主要作用是为了保证数据唯一性，一张表可以创建多个唯一索引。
2. 普通索引(Index):普通索引就是我们日常创建的索引，用于提高查询效率的，也可以创建多个,值也是允许重复的。
3. 前缀索引(Prefix):由字符串构成的索引，这种索引取字符串指定长度的前缀作为索引。
4. 全文索引(Full Text):为了检索大文本中的关键字的信息，常用于搜索引擎数据库使用的一种技术，MySQL5.6之前只有innodb支持，5.6之后MyISAM也支持了。

二级索引如下图所示，可以看到非叶子节点data存放的都是叶子节点的页地址，而叶子节点的data存放的都是主键的地址值。

进阶

聚簇索引

简介

聚簇索引说的其实一种将索引结构和数据放在一起的文件结构，用高性能MySQL的话说它就是将数据行和相邻的键值紧凑在一起。因为数据行不可以同时放在两个地方，所以一张表中只可能有一个聚簇索引。
它的文件后缀为.ibd，InnoDB 中的主键索引就属于聚簇索引。对于InnoDB而言，所有非叶子节点存放的都是索引，而叶子节点存放的则是索引以及索引对应的数据行。

优点

1. 查询数据速度较快，因为索引是一个多叉平衡树，我们可以快速定位到索引从而在叶子节点得到数据，相对于非聚簇索引少了一次IO。
2. 对于主键的排序以及范围查找速度非常快。

缺点

1. 依赖有序数据，对于非顺序的索引查询性能较差，因为叶子节点通过链表进行维护，若非顺序的索引进行插入或者查询性能是非常差的。
2. 维护困难，因为数据和索引都在同一个文件，若对索引字段频繁进行增删改操的话，我们就需要同步更新索引结构，这就会带来巨大的开销。
3. 更新聚簇索引代价高，因为更新聚簇索引InooDB会要求将更新列移动到新的位置。
4. 如果聚簇索引数据量很大的话，二级索引占用内存也会随之增大，因为二级索引的叶子节点存储的都是聚簇索引的值。
5. 如果数据插入是无序主键的方式插入，可能会导致性能问题，尤其是用UUID作为主键插入行，不仅花费的时间更长，而且索引占用的空间也更大。原因很简单，如果主键自增的话，我们顺序插入就无需维护叶子节点顺序直接追加即可，如果使用随机插入的话，很可能主键的地址和某个叶子节点地址产生冲突，由此导致为了插入一行数据移动大量原数据行记录。

6. 使用聚簇索引在叶子节点稀疏(即id很散的情况)可能导致全表扫描性能下降。

聚簇索引顺序插入注意事项

上面的说法仿佛告诉我们使用聚簇索引使用主键自增是最好的解决方案，实际上在高并发写的场景下，聚簇索引的顺序插入很可能导致间隙锁或者AUTO_INCREMENT锁。所以读者在使用聚簇索引时一定要考虑到当前业务场景是否会出现并发写的情况。

聚簇索引存储结构

如下图所示，我们的user表，有id和name两列，而id为主键，所以由主键生成的聚簇索引如下图所示，所有的非叶子节点指向叶子节点，叶子节点则存储着数据行。

而且非叶子节点11前面一列所指向的都是id小于11的数据行，需要注意的是数据行中不仅包含主键和其他列，还包含事务id和回滚指针，这些都是mvcc中的概念，笔者就不多做赘述了。
而且如果我们使用前缀索引作为主键列的话，MySQL的叶子节点不仅仅会存储主键，还会将完整的列放到叶子节点中。

非聚簇索引

在说说非聚簇索引吧，它也不是一种索引类型，是一种索引结构和数据分开的的索引。MyIsam使用的就是聚簇索引，而常用的InnoDB的二级索引用的也是非聚簇索引。非聚簇节点的叶子节点存放的不一定是数据的指针，以二级索引为例，它存放的就是索引以及索引对应主键的值。通过这个值我们可以通过回表查询到对应的数据。
和聚簇索引不同的是非聚簇索引数据和索引存放在不同的位置，它们分别存放在.myi和.myd中。
正是因为文件结构的特殊性，这就导致索引结构比较特殊，非聚簇索引会将建立的索引专门存放的索引以及数据的指针，我们之前所说的二级索引就是非聚簇索引，在非聚簇索引结构我们可以通过非叶子节点或者叶子节点定位到主键的指针，然后通过主键获取到真正的数据。

总的来说非聚簇索引有以下优点:

1. 维护开销小，因为索引和数据存放的不是一个文件中

缺点:

1. 依赖有序数据。
2. 查询可能会进行回表操作。

覆盖索引和联合索引

覆盖索引

首先是覆盖索引，通俗一点来说我们查询的值包含在我们建立的索引中，这种查询操作就无需通过回表就能直接获取到数据的情况就属于索引覆盖。而我们一直说的回表意思就是通过二级索引定位到主键的值，然后拿着主键的值去主键索引上获取数据的过程。

联合索引

这个就很简单了，说白了就是多个字段组合创建成一个索引，它使用原则是需要遵循最左匹配原则的。

索引覆盖示例

为了更好的演示我们不妨创建一张实验表，并插入数据，可以看到这张数据表id为主键，使用k作为普通索引

create table T (
  ID int primary key,
  k int NOT NULL DEFAULT 0,
  s varchar(16) NOT NULL DEFAULT '',
  index k(k)
)engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
1
2
3
4
5
6
7
8

先来看这段SQL，可以看到查询条件用的是索引k，他希望获取到k在3-5之间的数据。

select * from T where k between 3 and 5
1

结合上面数据表，我们可以得到下面这样一段步骤:

1. 通过k定位到3-5之间的id值有300，500。
2. 通过id值通过回表获取这两个id对应的数据。
3. 得到结果，响应给用户。

由此我们得出，因为数据表中存在范围内的k分别有3和5，所以触发两次回表，这样的查询性能是很差的。

再来看看这段SQL

select ID from T where k between 3 and 5
1

这段SQL要查询的刚刚好是id，而通过我们的索引k即可直接定位到id的值，无需进行回表，这就是我们所说的索引覆盖。

联合索引示例

同样的联合索引我们可以通过建立一张实验表来讲述一下:

数据表如下所示，可以看到我们使用name、age、ismale建立了一个联合索引，联合索引是遵循最左匹配原则的，在我们建立联合索引之后，相当于创建了name、(name、age)、(name、age、ismale)三个索引

create table `user` (
  ID int primary key,
  `name` varchar(32) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  `ismale` tinyint(1) DEFAULT NULL,
  KEY `name_age_ismale`(`name`,`age`,`ismale`)
)engine=InnoDB
1
2
3
4
5
6
7

所以，下面这三种情况都会走索引的

select * from user where name = 'Jack'
select * from user where name = 'Jack' and age = 22
--这种SQL优化器会自动优化为name在前，先匹配name然后再匹配age
select * from user where age > 18 and name = 'Jack'
1
2
3
4

这种就不会走索引了

select * from user where age = 18;
1

索引下推详解

答: 索引下推是MySQL5.6增加的一种特性，说白了就是为了更好的利用索引减少没必要的回表操作的一种方式。
对此我们不妨举个例子，首先我们创建一张数据表

create table `user` (
  ID int primary key,
  `name` varchar(32) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
   `sex` int(11) DEFAULT NULL, 
  KEY `name_age`(`name`,`age`)
)engine=InnoDB;
1
2
3
4
5
6
7

然后我们使用下面这条SQL进行查询

select * from user where name like '张%' and age=10;
1

假如我们的索引结构如下所示

这就意味着在没有索引下推这个机制前，因为name字段不是等值匹配，所以查询当我们看到了张三、张五、张a这几个name匹配name条件时，就会拿着对应的id去主键索引中判断age是否符合条件，如下图所示，这就意味着我们需要进行3次回表操作，没有一条数据是有效的。

MySQL5.6之后增加索引下推机制，它会对索引中包含的字段进行过滤，将没必要进行回表的数据直接过滤掉，从而减少回表次数，还是以上图为例，我们直接通过age过滤发现没有一条符合条件，直接不回表了。

最左匹配原则详解

最左匹配原则介绍

最左匹配原则算是MySQL的一个重点了，它会基于联合索引最左的字段进行排序，例如我们用字段a、b、c创建联合索引，他就会先通过a进行排序，如果a一样比较b，b再一样比较c这样，通过这样的比较机制生成一个有序的索引结构。
这也是为什么，我们直接直接用b就不走索引了，因为如果没有a，我们单单b这个查询条件得到的结果是散列的，根本无法快速定位数据。
为了更好的讲述这个技术点，我们不妨创建一张实验表,如下表所示，我们对这张学生表建立普通索引、联合索引、主键。

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_code` int(100) NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `height` double NULL DEFAULT NULL,
  `classid` int(11) NULL DEFAULT NULL,
  `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `普通索引`(`height`) USING BTREE,
  INDEX `联合索引`(`sname`, `s_code`, `address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6
7
8
9
10
11
12
13

然后我们再插入一堆测试数据

INSERT INTO `student` VALUES (1, '学生1', 1, '上海', 170, 1, '2022-11-02 20:44:14');
INSERT INTO `student` VALUES (2, '学生2', 2, '北京', 180, 2, '2022-11-02 20:44:16');
INSERT INTO `student` VALUES (3, '学生3', 3, '京东', 185, 3, '2022-11-02 20:44:19');
INSERT INTO `student` VALUES (4, '学生4', 4, '联通', 190, 4, '2022-11-02 20:44:25');
1
2
3
4

我们不妨通过一个个SQL来了解最左匹配原则。

先来看看这条SQL，很明显它使用的条件是联合索引最左边的字段，所以它是走索引的

select create_time from student where sname = "学生3
1

通过执行计划我们也可以看到这个类型是ref，说明它使用到了非唯一性索引扫描。

再来看看这条SQL，明显这就是我们前面说的直接使用联合索引第二个字段的情况，由于其排序结果依赖第一列字段，这就导致这样的查询没法利用有序的索引，进而索引失效

select create_time from student where s_code = 1
1

查看执行计划，可以发现type用到了全表扫描，条件也只是using where

这两条SQL和上面同理，不多赘述

explain select create_time from student where address = "上海";

explain select create_time from student where address = "上海" and s_code = 1 ;

1
2
3
4

再来看看这条，这就比较有趣了，你会发现sname放在后面，可是观察执行计划还是走索引的，原因也很简单，SQL优化器会将name排到前面，因为最左匹配原则的定律，这个SQL虽然没有用到第2列，但是用到最左排序依赖项，所以它走索引了。

explain select create_time from student where address = "上海" and sname = "学生3";
1

这两条同理，走索引

select create_time from student where sname = "变成派大星" and address = "上海" 
select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海"
1
2

补充说一下，最左匹配原则依赖最左排序项，而且一旦条件中同时遇到范围查询(>、<、between、like)就会停止匹配，对此我们不妨举3个例子

先看看这条，很明显它是走索引的

EXPLAIN  select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海"

1
2

查看执行计划发现都是等值匹配所以，三个索引都用上了，type也是ref

再看看这条

EXPLAIN  select create_time from student where sname = "变成派大星" and s_code > 1 and address = "上海"

1
2

观察执行计划，如下图，是Using index condition，type也是范围查询，说明匹配到s_code就停止进行索引查询了。

再看看最后一个例子

EXPLAIN  select create_time from student where sname = "变成派大星" and s_code = 1 and address like "%上海"
1

查看其执行计划可以发现，由于第3个条件用了左边%的like导致索引匹配只用到了前两个条件，所以extra是using index condition

最左匹配原则注意事项

MySQL8提供了一种非最左匹配原则的走索引策略，索引跳跃扫描:

对此我们不妨建立一张实验表

DROP TABLE IF EXISTS `user`;


create table user(
id int  not null auto_increment ,
name varchar(255) not null ,
gender tinyint not null ,
primary key (id),
key idx_gender_name (gender,name)
)ENGINE=InnoDB;
1
2
3
4
5
6
7
8
9
10
11

因为笔者的MySQL版本就是8.0，所以键入以下SQL查看执行计划

explain select * from user where name='一灯';

1
2

可以看到它还是走索引的

索引跳跃扫描出现于联合索引最左一列唯一值较少时，若用户直接跳过第一列索引使用第二列时，一样可以用到联合索引。

创建高性能索引技巧

创建实验表

为了更好的演示问题，我们不妨创建一张数据表，表中有主键、普通索引、普通列，并插入实验数据

DROP TABLE IF EXISTS `leftaffix`;
create table leftaffix(
a int(11) not null auto_increment,
b int(11) null default null,
c int(11) null default null,
d int(11) null default null,
e varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (a) USING BTREE,
INDEX `联合索引`(b, c, d) USING BTREE,
INDEX `idx_e`(e) USING BTREE
)ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 

INSERT INTO `leftaffix` VALUES (1, 1, 1, 1, '1');
INSERT INTO `leftaffix` VALUES (2, 2, 2, 2, '2');
INSERT INTO `leftaffix` VALUES (3, 3, 2, 2, '3');
INSERT INTO `leftaffix` VALUES (4, 3, 1, 1, '4');
INSERT INTO `leftaffix` VALUES (5, 2, 3, 5, '5');
INSERT INTO `leftaffix` VALUES (6, 6, 4, 4, '6');
INSERT INTO `leftaffix` VALUES (7, 8, 8, 8, '7');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

避免使用select *

虽然说select *会走索引，例如下面这段SQL

explain select * from leftaffix where b=100;
1

但是，它会导致:

1. 因为很多我们可能用不上的字段进而去进行回表查询，尤其是无用字段还会增加网络消耗，尤其是text这种类型的字段。
2. 增加SQL的解析成本。
3. 结合Mybatis使用时，resultMap映射处理也很耗时。

所以我们建议非必要情况下不要使用select *。

缩小范围查询

如下这两句SQL，第二句因为缩小了检索范围就走了索引(using index)

explain select * from leftaffix where b>1;
explain select * from leftaffix where b>7;

1
2
3

尽可能where条件后不用函数

如下面这两句，第二句就不失效，工作原理很简单，因为第二句保留的索引的原值，我们的索引结构是基于字段原值建立，如果使用函数产生的计算结果可能就和原值不一样，进而导致索引失效。

当然MySQL8之后出现了函数索引，如果你的字段需要用到函数就可以为其创建函数索引。

-- 失效
explain select * from leftaffix l where length (b) =10;
-- 不失效
explain select length(b) from leftaffix l where b =10;
1
2
3
4

避免对字段进行计算

如下所示，工作原理也很上相同，不破坏索引原值的情况下是走索引的。

-- 失效
explain select * from leftaffix l where b-1=10;
-- 不失效
explain select * from leftaffix l where b=10-1;
1
2
3
4

正确的使用like

如下所示，前者就走索引了，因为前者我们可以知晓一定范围的索引，不像后者那样，匹配的索引范围可能性几乎是全表，导致索引失效了。

-- like %要在左边
-- 走索引，但是级别比较低就是了
explain select * from leftaffix l where e like 'aa%';
-- 左边% 要匹配索引范围很大就没有走索引的必要了
explain select * from leftaffix l2 where e like '%aa';
1
2
3
4
5

正确的使用or

这个例子我们不妨重建一张表格

DROP TABLE IF EXISTS `leftaffix`;
create table leftaffix(
a int(11) not null auto_increment,
b int(11) null default null,
c int(11) null default null,
d int(11) null default null,
e varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (a) USING BTREE,
INDEX `联合索引`(b, c, d) USING BTREE
)ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 

INSERT INTO `leftaffix` VALUES (1, 1, 1, 1, '1');
INSERT INTO `leftaffix` VALUES (2, 2, 2, 2, '2');
INSERT INTO `leftaffix` VALUES (3, 3, 2, 2, '3');
INSERT INTO `leftaffix` VALUES (4, 3, 1, 1, '4');
INSERT INTO `leftaffix` VALUES (5, 2, 3, 5, '5');
INSERT INTO `leftaffix` VALUES (6, 6, 4, 4, '6');
INSERT INTO `leftaffix` VALUES (7, 8, 8, 8, '7');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

看看这条SQL，索引和普通列用or，索引失效

-- 用or左索引右普通裂，导致失效
explain select * from leftaffix l where a=1 or e='4';
1
2

从执行计划我们就能看出来，它走了全表扫描

正确的使用in

如下所示，第一条SQL是走索引的，一旦查询结果集范围大于表中结果的30%，就会走全表扫描

explain select * from leftaffix l where b in(1);
-- in 在结果集大于百分之30时索引失效
explain select * from leftaffix l where b in(1,2,3,4,5,6);
1
2
3

补充:MySQL自己的优化

如下所示，我们都知道这张表我们对b创建了索引，但是查看执行计划还是走了全表扫描

explain select e  from leftaffix l order by b ;

1
2

如下图所示，原因很简单，使用索引进行order by因为有序自然效率高一些，但因为获取的字段要通过回表进行获取，如果回表次数过多性能也会受到影响，所以MySQL优化器直接走了全表扫描。

子查询

正常情况下合理使用是会走索引的。

总结

1. 正确创建索引:使用那经常被查询的字段、不经常修改、不为null的字段、要作为where条件，频繁作为排序条件的字段、频繁用于连接查询的字段为其创建索引。
2. 不要为经常被修改的字段创建索引。
3. 尽可能创建联合索引而非单列索引。
4. 避免索引冗余。
5. 考虑为字符串字段创建前缀索引。
6. 及时删除那些基本不用的索引。
7. 避免写的SQL不走索引(索引失效)。

explain对应关键字详解

我们不妨对explain每一个字段进行概括一下吧:

select_type(查看sql语句是什么类型的查询)

先说说select_type吧，这个字段决定了你的SQL涉及的查询类型，常见的有:

1. SIMPLE:简单查询，如下所示，可以看到简单的SQL语句就属于这种查询类型

explain select * from `user` u ;

1
2

2. PRIMARY:SQL语句如下所示

explain select * from `user` u where id =(select id from `user` where id=1);

1
2

我们查看执行计划的截图，可以看到涉及这种嵌套查询的SQL左边的SQL就是PRIMARY

3. UNION:如下所示，从执行计划的截图就可以看出union后面的SQL就属于union

explain select * from `user` u union select * from `user` ;

1
2

4. DEPENDENT UNION:如下SQL所示，子查询中的union后面的SQL就属于DEPENDENT UNION
5. UNION RESULT:包含union的结果集，在union和union all语句中

explain select * from `user` u  where id in (select id from `user` u1  union select id from `user` u2)
1

6. DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生);MySQL会递归执行这些子查询, 把结果放在临时表里。

type(重点-查看查询是否走索引)

效率从高到低如下图所示，这里就不多赘述了

extra(重点-查看sql排序规则)

这个字段也很重要，它表示当前SQL进行排序时使用的规则:

1. Using filesort:使用了外部的索引进行排序，并没有用到我们自己定义的索引，性能较差。
2. using index:这种方式性能就不错了，使用了索引并且不需要回表就得到了我们需要的数据，即用到了索引覆盖。
3. Using temporary:MySQL查询排队时使用了临时表。
4. using where：排序时用的只是普通字段，没有走索引
5. Using join buffer:大量使用了表连接，所以会将某些结果放到缓存中进行排序，这种情况下我们建议加大joinbuffer缓存空间大小。
6. impossible where:说明where条件基本得不到需要的结果，筛选数据时一直处于false的状态。

possible_keys

表示当前查询可能用到的索引。如下这个执行计划，它就以为着可能用到了主键

key(用到的索引名称)

表示用到的索引名称，如下所示下面这条sql可能就用到了这两个索引。

key_len(用到的索引字节长度)

在MySQL的EXPLAIN语句中，key_len列表示使用索引的键部分的字节数。它是一个估计值，根据查询中使用的索引类型和数据类型来计算。通常，key_len越小，性能就越好，因为它意味着需要读取更少的数据块。
例如，如果你有一个使用VARCHAR(100)数据类型的列作为索引，并且查询中只使用了前10个字符作为搜索条件，则key_len将是10。如果你使用的是INT(10)数据类型的列作为索引，则key_len将是4，因为INT类型占用4个字节。
在优化查询时，理解key_len可以帮助你确定哪些索引可以更有效地支持查询，以及如何进一步优化索引设计。
以笔者的sql为例这里的长度为5,意为着本次索引用到了5字节。

ref(用到的索引)

表示查询时那一列被用到了。如下所示，这就意味着笔者rental_date中的三个索引列rental_date、inventory_id、customer_id 都被用到了。

rows(查询用到的行数)

当前查询查到的行数。如这个执行计划就意为着查到了1行。

filter(读取和过滤占比)

表示选取的行和读取的行占比，例如100就代表选取的所有行就是读取的所有行。

MySQL隐式转换导致索引失效问题

这个问题我们不妨看个例子吧，首先我们创建一张表，如下所示，可以看到num1、num2都是key，一个是int，一个是varchar类型。

DROP TABLE IF EXISTS test1;
CREATE TABLE `test1` (
    `id` int(11) NOT NULL,
    `num1` int(11) NOT NULL DEFAULT '0',
    `num2` varchar(11) NOT NULL DEFAULT '',
    `type1` int(4) NOT NULL DEFAULT '0',
    `type2` int(4) NOT NULL DEFAULT '0',
    `str1` varchar(100) NOT NULL DEFAULT '',
    `str2` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `num1` (`num1`),
    KEY `num2` (`num2`),
    KEY `type1` (`type1`),
    KEY `str1` (`str1`),
    KEY `str2` (`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

然后我们创建一个存储过程进行数据插入，如下所示，可以看出num1和num2值是一样的，但是类型不同的。

DROP PROCEDURE IF EXISTS pre_test1;
DELIMITER //
CREATE PROCEDURE `pre_test1`()
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    WHILE i < 10000000 DO
        SET i = i + 1;
        SET @str1 = SUBSTRING(MD5(RAND()),1,20);
        -- 每100条数据str2产生一个null值
        IF i % 100 = 0 THEN
            SET @str2 = NULL;
        ELSE
            SET @str2 = @str1;
        END IF;
        INSERT INTO test1 (`id`, `num1`, `num2`,
        `type1`, `type2`, `str1`, `str2`)
        VALUES (CONCAT('', i), CONCAT('', i),
        CONCAT('', i), i%5, i%5, @str1, @str2);
        -- 事务优化，每一万条数据提交一次事务
        IF i % 10000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;
END;
// DELIMITER ;
-- 执行存储过程
CALL pre_test1();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

然后我们进行如下查询

先来看看这句，num1为key，查询条件右边为int类型。从执行计划可以看出查到了4条数据，并且走了索引

explain SELECT * FROM `test1` WHERE num1 = 10000;

1
2

再来看看这句，左边为int，右边为字符串，从执行计划看出他也是走索引查到的数据也是4条

explain SELECT * FROM `test1` WHERE num1 = '10000';

1
2

接下来看看这条，左右都是num类型，走了索引，不多赘述

explain SELECT * FROM `test1` WHERE num2 = '10000';
1

最后看看神奇的一条，左边为字符类型，右边为int类型，走了全表扫描，查出来的数据有好几条，这正是我们说的索引失效问题

explain SELECT * FROM `test1` WHERE num2 = 10000;

1
2

MySQL隐式转换

1. 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1(即结果匹配，是我们要的数据)这两种情况都不需要做类型转换。
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换。
3. 两个参数都是整数，按照整数来比较，不做类型转换十六进制的值和非数字做比较时，会被当做二进制串。
4. 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp。
5. 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较。
6. 如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较。
7. 所有其他情况下， 两个参数都会被转换为浮点数再进行比较。

由此，我们上面所说就是第7种情况，我们先来分析这句SQL，左边是int，右边varchar，按照情况7都被转为浮点数，左右条件情况唯一，所以走索引。

SELECT * FROM `test1` WHERE num1 = '10000';

1
2

再看看这条，左边字符型右边int类型，都被转为浮点类型。

explain SELECT * FROM `test1` WHERE num2 = 10000;
1

这时候情况就不一样的，因为MySQL会将字符串类型转为数字的方式有很多种，情况不唯一，如下所示，这些SQL比较结果都为1(true)，这就意味着字符串类型转浮点数会将英文抹去，高位的0也被抹去，字母后面的数字也被抹去。

-- 隐式转换比较
select 123='123abc';
select 12='012abc';
select 5.3='5.3a666';
1
2
3
4

所以上面那条SQL中的num2在和’10000’比较时，隐式转换的情况就特别多，左边条件不唯一确定，故不走索引。

MySQL创建索引的时候需要注意那些？

答:

1. 经常作为查询条件作为索引。
2. 建立的索引尽量区分度要高，像性别这种就不要建立索引了。
3. 建立了组合索引时，区分度高的放在前面。
4. 建立组合索引，就不要建立单列索引。
5. 如果是长的字符串作为索引尽可能使用前缀索引。
6. 不要建立过多的索引，避免占用过多的空间。
7. 更新频繁的值不要作为索引。
8. 无需的值不要作为索引例如uuid，这种数据无序，会造成叶子节点频繁分裂，出现磁盘碎片化。

参考文献

MySQL索引详解

MySQL 索引之覆盖索引，联合索引与索引下推

我说MySQL联合索引遵循最左前缀匹配原则，面试官让我回去等通知

MySQl 索引之道

接口响应慢 用户总抱怨？后端必会知识点 EXPLAIN SQL 分析整理

一文彻底搞懂MySQL基础：B树和B+树的区别

面渣逆袭（MySQL面试题八股文）必看

高性能MySQL（第4版）