解读MySQL 索引基础

写代码的SharkChili 2025-01-20

写在文章开头

在 MySQL 的浩瀚世界中，索引就如同指引数据宝藏的神秘地图，是提升数据库性能的关键要素之一。当我们踏上探索 MySQL 索引的旅程时，便开启了一扇通往高效数据管理和快速查询的大门。索引，看似微小却蕴含着巨大的力量，它能够在海量数据中精准地引导我们找到所需的信息，极大地缩短查询时间，优化数据库操作体验。在接下来的篇章中，我们将逐步揭开 MySQL 索引的神秘面纱，深入了解其基本原理、类型、创建与运用。

Hi，我是 sharkChili ，是个不断在硬核技术上作死的技术人，是 CSDN的博客专家 ，也是开源项目 Java Guide 的维护者之一，熟悉 Java 也会一点 Go ，偶尔也会在 C源码 边缘徘徊。写过很多有意思的技术博客，也还在研究并输出技术的路上，希望我的文章对你有帮助，非常欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

因为近期收到很多读者的私信，所以也专门创建了一个交流群，感兴趣的读者可以通过上方的公众号获取笔者的联系方式完成好友添加，点击备注 “加群” 即可和笔者和笔者的朋友们进行深入交流。

详解MySQL中的索引

什么是索引

我们完全可以将索引可以看作是书本的目录，通过书本的目录查找对应的章节就会快一些，索引底层数据结构有b树、b+树、红黑树、哈希表、甚至是链表等，对于MySQL而言，无论Innodb
还是MyIsam
，使用的都是b+树，原因很简单，红黑树和哈希表或链表只能单方面解决精准定位或者范围查询问题，故采用多叉有序层级的B+树来保证两种维度的查询以及一次查询的IO量：

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

优点：

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

缺点：

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

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

例如我们针对一张小表建立多个二级索引，若数据量不大的小表，这就意味着我们每次操作这些数据都需要维护一张聚簇索引和多棵索引B+树，不仅性能上没有太大提升，且每次操作都会带来各种非必要的索引维护的开销：

索引不同逻辑结构的实现

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

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

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

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

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

B树如下图所示

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

需要补充一点，如果存储引擎为MyISAM
，我们会根据索引算法查找数据，若找到对应的data
域，我们就可以从data
域中获取到数据的地址，从而获取数据，这就是我们说的非聚簇索引。相比之下InnoDB
的底层数据结构就比较特殊了，它将数据和索引都存放到一个文件中，所以文件本身就是按照B+树组织的一种索引结构，这棵树的叶子节点的data域保存的都是完整的数据记录。

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

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

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

详解聚簇索引和二级索引

主键索引

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

二级索引

二级索引是一级索引下一级的索引，它的data
域存放的是一级索引的值，我们可以通过二级节点的data
定位到聚簇索引，从而定位到聚簇索引树得到数据行的所有数据，这其中，二级索引可以是:

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

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

详解聚簇索引和非聚簇索引

聚簇索引

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

聚簇索引的优点：

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

聚簇索引的缺点：

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

使用聚簇索引在叶子节点稀疏(即id顺序但不密集的情况)可能导致全表扫描性能下降。

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

非聚簇索引

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

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

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

缺点:

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

详解覆盖索引和联合索引

覆盖索引

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

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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');

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

ounter(line
select * from T where k between 3 and 5

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

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

由此我们得出，因为数据表中存在范围内的k分别有3和5，基于范围查询所以触发一次回表。

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

ounter(line
select ID from T where k between 3 and 5

对应的执行情况如下图所示：

联合索引

联合索引即多个字段组合创建成一个索引，它使用原则是需要遵循最左匹配原则的。

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

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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

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

ounter(lineounter(lineounter(lineounter(line
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'

这种就不会走索引了：

ounter(line
select * from user where age = 18;

详解索引下推

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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;

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

ounter(line
select * from user where name like '张%' and age=10;

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

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

MySQL5.6
之后增加索引下推机制，它会对索引中包含的字段进行过滤，将没必要进行回表的数据直接过滤掉，只得到唯一匹配的张三，从而减少回表次数：

详解索引最左匹配原则

最左匹配原则介绍

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

为了更好的讲述这个技术点，我们不妨创建一张实验表，并插入一些测试数据，如下表所示，我们对这张学生表建立普通索引、联合索引、主键。

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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;


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');

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

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

ounter(line
select create_time from student where sname = '学生3'

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

ounter(lineounter(lineounter(line
id|select_type|table  |partitions|type|possible_keys|key      |key_len|ref  |rows|filtered|Extra|
--+-----------+-------+----------+----+-------------+---------+-------+-----+----+--------+-----+
 1|SIMPLE     |student|          |ref |union_idx    |union_idx|62     |const|   1|   100.0|     |

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

ounter(line
select create_time from student where s_code = 1

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

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

ounter(lineounter(lineounter(lineounter(line
explain select create_time from student where address = "上海";


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

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

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

这两条同理，走索引

ounter(lineounter(line
select create_time from student where sname = "变成派大星" and address = "上海" 
select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海"

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

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

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

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

再看看这条

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

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

再看看最后一个例子

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

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

最左匹配原则注意事项

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

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line


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;

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

ounter(lineounter(line
explain select * from user where name='一灯';

可以看到它还是走索引的

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

创建高性能索引技巧

创建实验表

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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');

避免使用select *

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

ounter(line
explain select * from leftaffix where b=100;

但是，它会导致:

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

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

缩小范围查询

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

ounter(lineounter(lineounter(line
explain select * from leftaffix where b>1;
explain select * from leftaffix where b>7;

尽可能where条件后不用函数

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

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

ounter(lineounter(lineounter(lineounter(line
-- 失效
explain select * from leftaffix l where length (b) =10;
-- 不失效
explain select length(b) from leftaffix l where b =10;

避免对字段进行计算

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

ounter(lineounter(lineounter(lineounter(line
-- 失效
explain select * from leftaffix l where b-1=10;
-- 不失效
explain select * from leftaffix l where b=10-1;

正确的使用like

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

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

正确的使用or

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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');

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

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

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

正确的使用in

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

ounter(lineounter(lineounter(line
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);

MySQL自己的优化

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

ounter(lineounter(line
explain select e  from leftaffix l order by b ;

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

子查询

正常情况下合理使用是会走索引的，就像下面这句SQL，s1表和s2表结构一致，key1和key3都是索引，SQL优化器经过分析发现，这段SQL可以优化为连接查询select * from s1 inner join s2 on s1.key1 =s2.key3 ;
这就是的s2成为被驱动驱动表，走了key3索引进行扫描连接：

ounter(lineounter(line
explain select * FROM s1  where key1 in (select key3 from s2 );

阶段小结

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

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

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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;

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

ounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(lineounter(line
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();

然后我们进行如下查询

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

ounter(lineounter(line
explain SELECT * FROM `test1` WHERE num1 = 10000;

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

ounter(lineounter(line
explain SELECT * FROM `test1` WHERE num1 = '10000';

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

ounter(line
explain SELECT * FROM `test1` WHERE num2 = '10000';

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

ounter(lineounter(line
explain SELECT * FROM `test1` WHERE num2 = 10000;

MySQL隐式转换

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

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

ounter(lineounter(line
SELECT * FROM `test1` WHERE num1 = '10000';

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

ounter(line
explain SELECT * FROM `test1` WHERE num2 = 10000;

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

ounter(lineounter(lineounter(lineounter(line
-- 隐式转换比较
select 123='123abc';
select 12='012abc';
select 5.3='5.3a666';

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

MySQL创建索引的时候需要注意什么

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

小结

MySQL索引作为数据库性能优化的关键要素，在提升查询效率方面扮演着举足轻重的角色。本文从索引的基本概念出发，深入探讨了索引的原理，它如同书籍的目录，通过特定的数据结构（如 B 树、哈希表等），帮助数据库快速定位所需数据，大大减少了磁盘 I/O 操作，从而显著提升查询速度。在索引的类型方面，我们详细介绍了多种常见类型。主键索引作为表中数据的唯一标识，确保了数据的完整性与唯一性；唯一索引能防止字段出现重复值，在维护数据一致性上发挥重要作用；普通索引则是最基本的索引类型，适用于各种需要提升查询性能的场景；全文索引为文本数据的高效搜索提供了可能，尤其在处理大量文本信息时优势明显；组合索引则允许在多个字段上创建索引，进一步优化复杂查询的性能。同时，文章还着重强调了索引的使用场景与注意事项。合理创建和使用索引能够极大地提升数据库性能，但不当的索引策略也可能带来负面影响。例如，过多的索引会增加磁盘空间占用、降低数据插入和更新的速度，因此需要根据实际业务需求和数据特点，谨慎选择索引字段和索引类型。在编写查询语句时，遵循索引的使用规则，如避免在索引字段上进行函数操作、尽量使用覆盖索引等，能够充分发挥索引的优势。总之，MySQL 索引是一个复杂而强大的工具，深入理解和掌握索引的知识，能够帮助我们构建高效、稳定的数据库应用程序，为业务的发展提供坚实的数据支持。希望本文的内容能够帮助读者对 MySQL 索引基础有更清晰的认识，并在实际工作中灵活运用索引优化数据库性能。

我是 sharkchili ，CSDN Java 领域博客专家，mini-redis的作者，我想写一些有意思的东西，希望对你有帮助，如果你想实时收到我写的硬核的文章也欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

参考文献

MySQL索引详解:https://javaguide.cn/database/mysql/mysql-index.html

MySQL 索引之覆盖索引，联合索引与索引下推:https://juejin.cn/post/6991734559754682405

我说MySQL联合索引遵循最左前缀匹配原则，面试官让我回去等通知:https://juejin.cn/post/7127656601044910094

MySQl 索引之道:https://juejin.cn/post/7161964571853815822#heading-5

接口响应慢用户总抱怨？后端必会知识点 EXPLAIN SQL 分析整理:https://juejin.cn/post/7161254854571065375

一文彻底搞懂MySQL基础：B树和B+树的区别:https://blog.csdn.net/a519640026/article/details/106940115

面渣逆袭（MySQL面试题八股文）必看:https://tobebetterjavaer.com/sidebar/sanfene/mysql.html#_29-创建索引有哪些注意点

高性能MySQL（第4版）:https://book.douban.com/subject/36096578/

MySQL - MySQL没有主键怎么办?会自动生成主键么？自动生成的主键有什么问题？:https://www.cnblogs.com/frankcui/p/15226301.html

五分钟搞懂MySQL索引下推 :https://juejin.cn/post/7005794550862053412

文章转载自写代码的SharkChili，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

解读MySQL 索引基础

写在文章开头

详解MySQL中的索引

什么是索引

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

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

索引不同逻辑结构的实现

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

详解聚簇索引和二级索引

主键索引

二级索引

详解聚簇索引和非聚簇索引

聚簇索引

非聚簇索引

详解覆盖索引和联合索引

覆盖索引

联合索引

详解索引下推

详解索引最左匹配原则

最左匹配原则介绍

最左匹配原则注意事项

创建高性能索引技巧

创建实验表

避免使用select *

缩小范围查询

尽可能where条件后不用函数

避免对字段进行计算

正确的使用like

正确的使用or

正确的使用in

MySQL自己的优化

子查询

阶段小结

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

MySQL创建索引的时候需要注意什么

小结

参考文献

评论