MySQL索引优化

@琴酒**

已于 2023-11-13 16:06:49 修改

阅读量176

点赞数 1

文章标签： mysql 数据库

于 2023-11-13 16:03:23 首次发布

本文链接：https://blog.csdn.net/qq_40042416/article/details/134370367

版权

EXPLAIN详解

优先了解EXPLAIN，文章链接在下面。

EXPLAIN详解（MySQL）

索引数据结构

MySQL主要有两种结构：hash索引和B+Tree索引，InnoDB引擎默认是B+Tree索引。

索引分类

聚簇索引： 指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引。

非聚簇索引： 的逻辑顺序则与数据行的物理顺序不一致。

普通索引： MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建。

唯一索引： 索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建。

主键索引： 特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建。

组合索引： 组合表中多个字段创建的索引，遵守最左前缀匹配规则。

全文索引： 只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

示例表

employees表一个主键索引id字段，一个联合索引（idx_name_age_position）。

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

插入100000条数据

drop procedure if exists insert_emp; 
delimiter ;;
create procedure insert_emp()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100000)do                 
    insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_emp();

EXPLAIN索引分析

1、全职匹配

EXPLAIN SELECT * FROM employees WHERE `name` = 'HanMeimei'

如下图可以看出，type=ref、key=idx_name_age_position使用了联合索引，key_len=74（计算规则，name字段varchar(24)，3n+2 = 3*24+2=74，上面分享的文章有详述，通过key_len就可以分析出使用的那个字段的索引。）

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE `name` = 'HanMeimei' and age = 23

同上分析，使用了联合索引里的name、age字段。

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE `name` = 'HanMeimei' and age = 23 and position = 'dev'

同上分析，使用了联合索引里的name、age、position字段。

在这里插入图片描述

2、最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
EXPLAIN SELECT * FROM employees WHERE name = 'Bill' AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';

第一条SQL，符合最左前缀法则，使用了联合索引里的name、age字段。

在这里插入图片描述
第二条SQL，联合索引中间的age字段断了，所以只使用了name字段。

在这里插入图片描述
第三、四条SQL，不符合最左前缀法则，不会使用索引，走的的全表扫描。

在这里插入图片描述

3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

在这里插入图片描述

4、存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

age是范围查找，age后面的position字段就不会使用索引，只使用了name、age两个索引字段。
在这里插入图片描述

5、尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

Extra=null，没有使用覆盖索引。MySQL首先查询的是普通索引树，通过普通索引树里面的主键id，查询主键索引树找到剩余的字段。

在这里插入图片描述

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

Extra=Using index，使用覆盖索引。因为普通索引树有name、age字段，就不用再去查主键索引树了。
在这里插入图片描述

6、mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描，< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

在这里插入图片描述

7、is null,is not null 一般情况下也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is not null

在这里插入图片描述

8、like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

在这里插入图片描述
这里给大家补充一个概念，索引下推（Index Condition Pushdown，ICP）, like KK%其实就是用到了索引下推优化。

什么是索引下推了？

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’ 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。 使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

为什么范围查找Mysql没有用索引下推优化？

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

9、字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = 1000;

在这里插入图片描述

10、少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

在这里插入图片描述

11、范围查询优化

#给年龄添加单值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;

在这里插入图片描述

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引。

优化方法：可以将大的范围拆分成多个小范围。

explain select * from employees where age >=1001 and age <=2000;
#还原最初索引状态
ALTER TABLE `employees` DROP INDEX `idx_age`;

在这里插入图片描述

12、Order by与Group by优化

案例1：

EXPLAIN SELECT * FROM employees WHERE `name` = 'LiLei' ORDER BY age

在这里插入图片描述
利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort。

案例2：

EXPLAIN SELECT * FROM employees WHERE `name` = 'LiLei' ORDER BY position

在这里插入图片描述
从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。

案例3：

EXPLAIN SELECT * FROM employees WHERE `name` = 'LiLei' ORDER BY age,position

在这里插入图片描述
查找只用到索引name，age和position用于排序，无Using filesort。

案例4：

EXPLAIN SELECT * FROM employees WHERE `name` = 'LiLei' ORDER BY position,age

在这里插入图片描述
出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

总结：

1）MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2）order by满足两种情况会使用Using index。1) order by语句使用索引最左前列。2) 使用where子句与order by子句条件列组合满足索引最左前列。

3）尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4）如果order by的条件不在索引列上，就会产生Using filesort。

5）能用覆盖索引尽量用覆盖索引。

6）group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解

filesort文件排序方式

单路排序： 是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

双路排序（又叫回表排序模式）： 是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段。

13、分页查询优化

select * from employees limit 10000,10;

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

根据非主键字段排序的分页查询

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

14、Join关联查询优化

mysql的表关联常见有两种算法

1）嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

在这里插入图片描述

从执行计划中可以看到这些信息：

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表，用where条件过滤完驱动表，然后再跟被驱动表做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下：

从表 t2 中读取一行数据（如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据）；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行（扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行）。因此整个过程扫描了 200 行。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。

2）基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

在这里插入图片描述
Extra 中的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

上面sql的大致流程如下：

把 t2 的所有数据放入到 join_buffer 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。