MySQL 索引创建和优化实践
2023-03-14 22:39:58 时间
本文以 employees
表为例子,结合具体的索引运用实践案例,通过分析 EXPLAIN
关键字获取执行计划,来验证我们这些索引实践。如果是执行计划相关的详细信息,大家可以参考 mysql 官网 explain 介绍。
mysql 版本: 5.7.23
使用的表
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 USING BTREE (name, age, position)
) ENGINE = InnoDB AUTO_INCREMENT = 4 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 ('WaKen', 23, 'dev', NOW());
INSERT INTO employees (name, age, position, hire_time) VALUES ('Lucy', 23, 'dev', NOW());
复合索引数据结构
下面是一个复合索引的是示意图
索引最佳实践
1. 全值匹配
对于全值匹配,就是查询条件能够命中索引的全部列,或者复合索引需要命中左边的数据
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
image.png
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
image.png
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
image.png
2.最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';
image.png
EXPLAIN SELECT * FROM employees WHERE position = 'manager';
image.png
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
image.png
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 使用全值匹配能够正常使用索引
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
image.png
- 使用 left 或者其他的数据库函数导致索引未命中。会走一个全表扫描。
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
image.png
4.存储引擎不能使用索引中范围条件右边的列
- 全值匹配如下。
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
image.png
- 如果我们中间增加一个范围查找,会导致右边的查询条件无法使用索引。我们对比一下,上下两条 sql 的差别。
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
image.png
5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句
对于 select 指定字段查询,如果查询的所有列都是索引上的数据,那么可以减少 “回表”。查询的效率高于 select *
explain select name,age from employees where name = 'Lilei' and age = 23 and position= 'manage';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
image.png
6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
image.png
7.is null,is not null 也无法使用索引
因为在 mysql 变长的数据类型中,如 varchar 。null 不会存储,它需要一个额外的标志位来存储。所以如果我们使用 is null , is not null 是无法使用索引的。所以我们在 DDL 语句中尽量让每个字段都有默认值。
EXPLAIN SELECT * FROM employees WHERE name is null;
image.png
8.like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作
EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
image.png
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';
问题:解决like'%字符串%'索引不被使用的方法?a)使用覆盖索引,查询字段必须是建立覆盖索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
b)当覆盖索引指向的字段是varchar(380)及380以上的字段时,覆盖索引会失效!
9. 如果查询条件导致类型转换会导致索引失效
字符串不加单引号索引失效
- 加上单引号,无需数据转换 name 存储数据也是 varchar 类型。
EXPLAIN SELECT * FROM employees WHERE name = '1000';
image.png
- 如果不加单引号,发生数据类型转换导致本次查询索引失效。
EXPLAIN SELECT * FROM employees WHERE name = 1000;
image.png
10.少用or,用它连接时很多情况下索引会失效
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
image.png
小总结
like KK%相当于=常量,%KK和%KK% 相当于范围 下面是常见的几种索引查询失效或者,可用的判定
总结 1
总结 2
01索引优化案例.png
参考文档
- https://blog.csdn.net/qq_38138069/article/details/82998658
- https://www.processon.com/u/5e26625de4b00fbcc45e576d
- https://zhuanlan.zhihu.com/p/94190700
- https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
相关文章
- 数据孤岛是业务效率的无声杀手
- 2023展望:新的一年将给大数据分析领域带来什么?
- 阿里云ADB基于Hudi构建Lakehouse的实践
- 大数据在医疗保健领域的使用案例
- 微软增加说明:KB5021751 更新扫描已经 / 即将过时 Office 过程中不会触碰用户隐私
- 2022 Gartner全球云数据库管理系统魔力象限发布 腾讯云数据库入选
- 场景化、重实操,分享一个实时数仓实践案例
- Arctic的湖仓一体践行之路
- 分布式计算MapReduce究竟是怎么一回事?
- 淘系数据模型治理优秀实践
- 大数据分析对医疗保健的影响
- 当我们说大数据Hadoop,究竟在说什么?
- 2022年及以后大数据的五个发展趋势
- 网易严选离线数仓治理实践
- 2023 年数据治理趋势
- 一份“靠谱”的年度经营计划,你学会了吗?
- 漫谈对大数据的思考
- 测试一下,读懂数据的能力,你有吗?
- 用艺术的眼光探索数据之美
- 聊聊数据分析成果如何落地