mysql 索引

最新推荐文章于 2025-08-03 21:59:57 发布

ChanYipFan

最新推荐文章于 2025-08-03 21:59:57 发布

阅读量230

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43530726/article/details/92424098

MySQL 专栏收录该内容

2 篇文章

订阅专栏

添加链接描述

索引优化分析

索引是什么

官方定义：索引是帮助 mysql 高效获取数据的数据结构

可简单理解为排好序的快速查找数据结构

索引会影响到 where 后面的查找和 order by 后面的排序

在这里插入图片描述
索引往往以索引文件的形式存储在磁盘上

索引没有特指的话就是B树索引

索引优缺点

优点：

提高查询效率（降低IO使用率）
降低CPU使用率（…order by age desc，因为 B树索引本身就是一个好排序的结构，因此在排序时可以直接使用）

缺点：

索引本身很大，实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的；
索引不是所有情况均适用： a. 少量数据，b.频繁更新的字段，c.很少使用的字段
索引会降低增删改的效率；MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引的创建

	方式一(创建)：
        	create 索引类型  索引名  on 表(字段)
        	
            单值(普通索引)：
            create index dept_index on tb(dept);
            复合索引
            create index dept_index on tb(dept,name);
            唯一：
            create unique index name_index on tb(name) ;


	方式二(添加)：
			alter table 表名 add 索引类型 索引名（字段）
			
            主键索引:
            ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
            单值：
            alter table tb add index dept_index(dept) ;
            唯一：
            alter table tb add unique index name_index(name);
            复合索引
            alter table tb add index dept_name_index(dept,name);
            全文索引
            ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
            
    注意：如果一个字段是primary key，则改字段默认就是 主键索引

索引的删除

drop index 索引名 on 表名 ;

drop index name_index on tb ;

索引的查询

show index from 表名 ;

show index from 表名 \G

创建索引的情况

需要创建索引：

主键自动建立唯一索引(primary key)；
频繁作为查询条件的字段应该创建索引(where 后面的语句)；
查询中与其它表关联的字段，外键关系建立索引；
单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)；
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
查询中统计或者分组字段；(group by…)

不需要创建索引:

表记录太少；
经常增删改的表；（更新表时不仅保存数据还要保存索引文件）
Where条件里用不到的字段不创建索引；
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果(有一个比值，不同的个数和总个数的比值越大越好)；

Explain

概念:

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

作用:

表的读取顺序；
哪些索引可以使用；
哪些索引被实际使用；
数据读取操作的操作类型；
表之间的引用；
每张表有多少行被优化器查询；

执行计划包含的信息：
在这里插入图片描述

其中 id、type、key、rows、Extra 最重要

信息字段解释：

id

表示：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

分为三种情况:

第一种情况：id相同

执行顺序由上至下。
在这里插入图片描述

第二种情况：id不同

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

在这里插入图片描述
第三种情况：id相同不同，同时存在。

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
在这里插入图片描述
注：衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。）

select_type

在这里插入图片描述

type

该字段一共有8种值：
在这里插入图片描述

type 显示的是访问类型，是较为重要的一个指标

以下是常用的结果值：
在这里插入图片描述

possible_keys、key、key_len

possible_keys：

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：

实际使用的索引。如果为NULL，则没有使用索引；查询中若使用了覆盖索引，则该索引和查询的select字段重叠；

覆盖索引:

如果一个索引包含 (或者说覆盖) 所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存情引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要"回表"，也就是要通过主键再查找一次。这样就会比较慢。

覆盖索引就是把要查询出的列和索引是对应的，不做回表操作!

现在我创建了索引(username,age)，在查询数据的时候: select username , age fromuser where username = Java’ and age = 22。要查词出的列在叶子节点都存在! 所以就不要回表。

简单说就是查询的字段和建的索引的字段，个数和顺序一一对应

例子：
在这里插入图片描述

key_len：

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度。
key_len字段能够帮你检查是否充分的利用上了索引。
具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

ref、rows

ref:

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值；

例子：
在这里插入图片描述

rows:

rows列显示MySQL认为它执行查询时必须检查的行数。
越少越好；

例子：
在这里插入图片描述

Extra

包含不适合在其他列中显示但十分重要的额外信息：
在这里插入图片描述
前三个是最重要的。出现了前两个是坏事，出现了第三个是好事

Using filesort：

索引尽量跟着 key 的索引的个数和顺序走，不然很容易出现 Using filesort1

例子（有了这个就说明你的sql写得不好）：
在这里插入图片描述
上图的第二条语句性能高于第一条语句，因为查出来的 Extra 结果中没有 Using filesort

Using index：

例子：
在这里插入图片描述
上面的第二条语句用到了覆盖索引

覆盖索引：
在这里插入图片描述
覆盖索引使用事项：

impossible where：

例子：
在这里插入图片描述

Explain 小测试

由下图的查询结果推测，sql语句的执行顺序是怎样的？
在这里插入图片描述

答案：
在这里插入图片描述

索引优化案例

单表

准备工作，建表与插入数据：

CREATE TABLE IF NOT EXISTS `article`(

`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

INSERT INTO `article` (author_id,category_id,views,comments,title,content) VALUES
(1,1,1,1,1,1),
(2,2,2,2,2,2),
(1,1,3,3,3,3);

问题：查询 categoryid 为1 且 comments 大于 1 的情况下，views 最多的文章

操作过程：

explain select id, author_id from article where category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

结果：
在这里插入图片描述
现在查是查出来了，但性能不好，必须要优化

那哪些字段需要建索引？

where 条件后面需要被查询到的要建索引：

create index idx_article_ccv on article(category_id, comments, views);

结果：
在这里插入图片描述
现在虽然 type 变成了 range，但 extra 里还有 Using filesort，也还是不行

那么我们已经建立了索引，为什么没用？
在这里插入图片描述

简单地说就是，范围以后的索引会导致失效
(comments > 1，大于1是一个范围)

删除上面那个不是很好的索引，然后只建立(category_id, views)之间的索引，而没有comments:

drop index idx_article_ccv on article;

create index article_cv on article(category_id, views);

explain select id, author_id from article where category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

结果：
在这里插入图片描述
现在可以看到 type变成了ref，Extra 中的 Using fileSort 也消失了，结果非常理想

双表

有两个表，class 和 book 表，class 表里有字段 id 和 card，book 表里有字段 bookid 和 card

现在让 class 左连接 book，
class 为左表，book 为右表

结果：
在这里插入图片描述
type 有 All，不是很好

下面我们为右表的 book 建立索引：
在这里插入图片描述

再看一下：
在这里插入图片描述
可以看到 book 表的 type 变为了 ref，rows 也从20变到了1

再为左表的 class 建立索引：
在这里插入图片描述
再看一下：

可以看到虽然 class 表的 type 变为了 index，但 rows 还是20

从以上结果可以看出，在左连接的情况下，给右表建索引的优化效果比给左表建索引的优化效果要好

这是为什么？

这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引

所以，

左连接，索引建在右表；
右连接，索引建在左表。

三表

有三张表，book、class、phone，它们都有 card 字段

先把它们 inner join 连接起来，结果：
在这里插入图片描述
出现了 Using join buffer，这说明 Using join buffer 这个字段出现在多表连接中

再将它们依次左连接起来，结果：
在这里插入图片描述
性能不好。

现在为 phone 与 book 表建索引：
在这里插入图片描述
再查一次，结果：

可以看出后2行的 type 都是 ref 且总 rows 优化很好，效果不错。

因此索引最好设置在需要经常查询的字段中。

索引优化总结：

1、保证被驱动表的 join 字段已经被索引。（join 后的表为被驱动表，因为它需要被查询)
2、left join 时，选择小表作为驱动表，大表作为被驱动表(建立索引的表)。但是 left join 时一定是左边是驱动表，右边是被驱动表。
3、inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。
4、子查询尽量不要放在被驱动表，有可能使用不到索引。
5、当无法保证被驱动表的 join 条件字段被索引且内存资源充足的前提下，不要太吝啬 JoinBuffer 的设置。

索引失效(应该避免)

准备工作，建表，插入数据，建立索引：

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;

INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

索引失效的案例：

1、非全值匹配
2、在索引列上做操作
3、索引的中间字段用到了范围条件
4、很少使用覆盖索引
5、使用了不等于（!= 或 <>）
6、使用了 is (not) null
7、使用了 like 后以通配符 % 开头
8、字符串不加单引号
9、经常用 or

以下逐一讲解

1、非全值匹配

索引 idx_staffs_nameAgePos 建立时是以 name，age，pos 的顺序建立的

全值匹配的意思是我们要按索引建立的顺序进行匹配

现在分情况讨论：

当我们只筛选 name 时，结果：
在这里插入图片描述
key 显示用到了索引

当我们筛选 name 和 age 时，结果：
在这里插入图片描述
key 显示用到了索引，并从 key_len 和 ref 中与上一个对比发现用到了两个

当我们筛选 name、age 和 pos 时，结果：
在这里插入图片描述
key 显示用到了索引，并从 key_len 和 ref 中与上一个对比发现用到了三个

以上说明，对 idx_staffs_nameAgePos 索引来说，能用到的值越多越好

下面来点不一样的：

当我们只筛选 age 和 pos 时，结果：
在这里插入图片描述
key 显示没用到索引

当我们只筛选 pos 时，结果：
在这里插入图片描述
key 依旧显示没用到索引

当我们只筛选 name 时，结果：
在这里插入图片描述
key 显示用到了索引

当我们只筛选 name 和 pos 时，结果：
在这里插入图片描述
key 显示用到了索引，但和只筛选 name 时没区别

那么以上四个索引为什么有的全失效有的部分失效了呢？

这是因为它们违背了最佳左前缀法则（查询从索引的最左前列开始并且不跳过索引中的列）

如果索引了多列，就要遵守最佳左前缀法则

我们现在的索引是 idx_staffs_nameAgePos

如果我们直接跳过了 name，那么就会导致索引全失效

如果筛选了 name 后跳过 age 筛选 pos，那么就会导致索引只利用到了 name 的那一部分，并没有完全利用

结论：在写筛选条件时要遵循最佳左前缀法则。

2、在索引列上做操作

例子：

同样的语句，一个没有函数，一个用了函数，它们查询出来的结果相同：
在这里插入图片描述
但它们的性能却完全不同：

上图显示用了函数后，索引失效了

结论：不要在索引列上做任何操作，计算、函数、类型转换(自动or手动) 等等，这会导致索引失效而转向全表扫描。

3、索引的中间字段用到了范围条件

性能好的查询：
在这里插入图片描述
性能不好的查询：

以上结果显示，范围条件之后的全失效了，索引 idx_staffs_nameAgePos 中只用到了 name 和 age，而后面的 pos 没用到

结论：筛选时不要在中间字段用到范围条件。

4、很少使用覆盖索引

例子：

先查一下表里有哪些字段：

在这里插入图片描述
除了索引 idx_staffs_nameAgePos 中的 name、age、pos 之外，还有 add_time

接下来开始查询，结果如下：
在这里插入图片描述
因为我们的筛选条件中只有 name、age、pos，而 select * 中包含了 add_time，所以查询的性能没有直接查 name、age、pos 要好

同样的例子：

在这里插入图片描述

可以看到在直接筛选之后 key_len 由78变成了74

结论：尽量使用覆盖索引，即不用select * ，而是 select 具体的字段，减少select * 的使用量

5、使用了不等于（!= 或 <>）

例子：
在这里插入图片描述

结论：尽量不使用不等于符号，能不写就不写。

因为若是业务功能需要的话，还是要写的，毕竟先实现功能再考虑性能。

6、使用了 is (not) null

例子：

在这里插入图片描述

结论：避免使用 is (not) null。若是关键字段实在不行也要写个 default 值。

7、使用了 like 后以通配符 % 开头

例子：

like 后以%开头：
在这里插入图片描述
like 后没有以%开头：

注意：like 也是 range 类型的，但和 >、<号的 range 不同。
like 是在前面有定值情况下后面跟的范围，比如说July%，那么 July 是定值，%是范围。
所以 like 可以在索引的中间条件下使用，与（“3、索引的中间字段用到了范围条件”的索引失效条件不冲突）

以上说明了%最好不要写在开头，否则索引失效

那么现在问题来了，我就是想在开头写%怎么办？

即：怎样解决 like ‘%字符串%’ 时索引不被使用？

答: 使用覆盖索引。

我们先创建一个索引，只有 name 和 age：
在这里插入图片描述
覆盖索引查看一下 select name, age：

发现索引没有失效

再查看一下 select id：
在这里插入图片描述
发现索引也没有失效

这是因为 id 是主键（我建表的时候设的 id 为主键），所以它可以从主键索引上去取

再查看 id 与其他字段混合的：
在这里插入图片描述
发现索引依然有效

现在来看两个不使用覆盖索引的：
在这里插入图片描述
发现索引失效了

索引也失效了

总结：

以下是可以在 like 后用%开头的：
在这里插入图片描述
以下是不可以在 like 后用%开头的：

结论：

1、like 后最好不要加通配符 % 开头
2、若硬是要开头加 % 则要用覆盖索引

8、字符串不加单引号

例子：

首先先查下表：
在这里插入图片描述
发现 name 里有2000这个 varchar 类型的数据（我建表的时候把 name 的类型设成了 varchar）

对字符串加单引号与不加单引号查询：
在这里插入图片描述
发现都能查出正常的结果

为什么字符串不加单引号也能查出来呢？

因为 mysql 能自动地进行类型转换。

这也算是在索引列上做操作了（详情见上面 “2、在索引列上做操作”），所以这会导致索引失效
在这里插入图片描述

结论：字符串一定记得加单引号

9、经常用 or

例子：

在这里插入图片描述

结论：少用 or

总结与练习

练习一，看左边语句推断索引是否被使用：
在这里插入图片描述

练习二：

准备工作，建表与插入数据：
在这里插入图片描述
然后建索引：

现在来看不同的场景的索引使用的情况：

一、用了4个的
在这里插入图片描述

二、用了3个的

三、用了2个的

以上两个的 order by c3 实际上也用到了索引，但是它是用来排序而不是用来查找，所以没有统计到 key 里面，导致了 key_len = 62而不是93
（索引的功能就是查找和排序）

在这里插入图片描述
因为索引是按照c1c2c3c4建的，用了c1c2后直接用了c4，那么为了完成查询任务，系统只能自己根据自己的规则重新索引过

在这里插入图片描述

这里 order by 乱了顺序也没出现 filesort 的原因是因为在 order by 之前c2已经被索引查询过了，已经是一个常量了（c2=‘a2’）。可以对比下面一个来看：

在这里插入图片描述

四、用了1个的
在这里插入图片描述
只用了c1一个字段索引，c2、c3用于排序，无 filesort

因为索引是根据c1c2c3c4建的，就算 order by 没有在 key 里显示用到了索引，但它也算是用到了索引的，所以如果你在 order by 里弄乱了顺序也不行

在这里插入图片描述

group by 表面上是分组，但分组之前必排序。所以它和 order by 的排序法则和索引优化的原则几乎是一致的，与 order by不同的是它还有 having。如果 group by 顺序乱了会有临时表产生

对以上的情况小总结就是：分析索引时，定值为常量，范围后会失效，最终看排序

索引优化的建议总结:

对于单键索引，尽量选择针对当前查询过滤性更好的索引；
在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)；
在选择组合索引的时候，尽量选择可以能够包含当前查询中的 where 字句中更多字段的索引；
尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的；