Mysql索引优化实战-1

Tacy0213

已于 2023-06-19 11:09:07 修改

阅读量37

点赞数

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

于 2023-06-17 23:51:59 首次发布

本文链接：https://blog.csdn.net/beautybug1126/article/details/131266250

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

示例表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `city` varchar(32) NOT NULL DEFAULT '' COMMENT '城市',
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_city` (`name`,`age`,`city`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

INSERT INTO user(name,age,city,created_time) VALUES('Libai',22,'shanghai',NOW());
INSERT INTO user(name,age,city,created_time) VALUES('Dufu',22,'shaoxing',NOW());
INSERT INTO user(name,age,city,created_time) VALUES('Wanganshi',22,'shenzhen',NOW());

-- 插入示例数据
drop procedure if exists insert_emp; 
delimiter ;;
create procedure insert_emp()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100000)do                 
    insert into user(name,age,city) values(CONCAT('tacy',i),i,'hangzhou'); 
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_emp();

索引失效的几种情况

where条件里用or
复合索引未引用左列字段
like以’%'开头
where条件里用or
字段类型转换
MySQL优化器的最终选择，不走索引
is not null 不走索引，is null 走索引
where中索引列进行了运算
where中索引列使用了函数

索引失效举例

where条件里用or

EXPLAIN SELECT * FROM user WHERE name = 'tacy1' or age = '18';

在这里插入图片描述

复合索引未引用左列字段

EXPLAIN SELECT * FROM user WHERE age > 18;

在这里插入图片描述

like以’%'开头

EXPLAIN SELECT * FROM user WHERE name like '%cy1';

在这里插入图片描述

字段类型转换

EXPLAIN SELECT * FROM user WHERE name = 1;

在这里插入图片描述

MySQL优化器的最终选择，不走索引

EXPLAIN SELECT * FROM `user` WHERE `name` LIKE 'Libai%' and age = 22 AND city = 'hangzhou';

在这里插入图片描述

-- 查出的数据量大，回表成本大，不走索引
EXPLAIN SELECT * FROM `user` WHERE `name` LIKE 'tacy%' and age = 22 AND city = 'hangzhou';

在这里插入图片描述

is not null 不走索引，is null 走索引

EXPLAIN SELECT * FROM `user` WHERE `name` IS NOT NULL;

在这里插入图片描述

索引下推

可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数

EXPLAIN SELECT * FROM `user` WHERE `name` LIKE 'Libai%' AND age = 22 AND city = 'hangzhou';

在这里插入图片描述
上面的查询在联合索引里匹配到名字是 ‘Libai’ 开头的索引之后，同时还会在索引里过滤age和city这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。
(注意：如果要查询的结果集较大，也不会用索引下推，mysql会考虑回表的成本)

trace工具用法

set session optimizer_trace="enabled=on",end_markers_in_json=on;  --开启trace
select * from user where name > 't' order by age;
select * from information_schema.OPTIMIZER_TRACE;
set session optimizer_trace="enabled=off"

常见sql深入优化

能用覆盖索引尽量用覆盖索引
MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
order by满足两种情况会使用Using index。
- order by语句使用索引最左前列。
- 使用where子句与order by子句条件列组合满足索引最左前列。
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果order by的条件不在索引列上，就会产生Using filesort。
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

sql优化举例

覆盖索引: 只需要遍历联合索引树就能拿到所有结果, 不需要回表

-- 优化前
EXPLAIN SELECT * FROM `user` WHERE `name` > 'T';

在这里插入图片描述

-- 优化后
EXPLAIN SELECT name,age,city FROM `user` WHERE `name` > 'T';

在这里插入图片描述

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

EXPLAIN SELECT * FROM `user` WHERE `name`= 'Libai' and city = 'hangzhou' order by age;

在这里插入图片描述
利用最左前缀法则：中间字段不能断，下面跳过了age索引列，用在排序过程中，Extra字段里出现了using filesort

EXPLAIN SELECT * FROM `user` WHERE `name`= 'Libai' order by city;

在这里插入图片描述

Using filesort文件排序原理详解

filesort文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields > （先取全部再排序）
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >（先取部分，排好序，再取需要的字段）
区别：单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。

索引设计原则

代码先行，索引后上
根据实际的业务情况，再建合适的索引
联合索引尽量覆盖条件
设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则
不要在小基数字段上建立索引
长字符串我们可以采用前缀索引
KEY idx_name_age_city (name(20),age,city)
where与order by冲突时优先where
基于慢sql查询做优化