|
|
|
|
公众号矩阵

MySQL 索引创建和优化实践

本文以 employees 表为例子,结合具体的索引运用实践案例,通过分析 EXPLAIN 关键字获取执行计划,来验证我们这些索引实践。如果是执行计划相关的详细信息,大家可以参考 mysql 官网 explain 介绍。

作者:老郑 来源:运维开发故事 |2021-11-09 07:59

本文转载自微信公众号「运维开发故事」,作者老郑。转载本文请联系运维开发故事公众号。

本文以 employees 表为例子,结合具体的索引运用实践案例,通过分析 EXPLAIN 关键字获取执行计划,来验证我们这些索引实践。如果是执行计划相关的详细信息,大家可以参考 mysql 官网 explain 介绍。

mysql 版本: 5.7.23

使用的表

  1. CREATE TABLE employees ( 
  2.  id int(11) NOT NULL AUTO_INCREMENT, 
  3.  name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名'
  4.  age int(11) NOT NULL DEFAULT '0' COMMENT '年龄'
  5.  position varchar(20) NOT NULL DEFAULT '' COMMENT '职位'
  6.  hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
  7.  PRIMARY KEY (id), 
  8.  KEY idx_name_age_position USING BTREE (name, age, position) 
  9. ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARSET = utf8 COMMENT '员工记录表'
  10.  
  11. INSERT INTO employees (name, age, position, hire_time) VALUES ('LiLei', 22, 'manager', NOW()); 
  12.  
  13. INSERT INTO employees (name, age, position, hire_time) VALUES ('WaKen', 23, 'dev', NOW()); 
  14.  
  15. INSERT INTO employees (name, age, position, hire_time) VALUES ('Lucy', 23, 'dev', NOW()); 

复合索引数据结构

下面是一个复合索引的是示意图

索引最佳实践

1. 全值匹配

对于全值匹配,就是查询条件能够命中索引的全部列,或者复合索引需要命中左边的数据

  1. EXPLAIN SELECT * FROM employees WHERE name'LiLei'

  1. EXPLAIN SELECT * FROM employees WHERE name'LiLei' AND age = 22; 

  1. EXPLAIN SELECT * FROM employees WHERE name'LiLei' AND age = 22 AND position ='manager'

2.最佳左前缀法则

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

  1. EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager'

  1. EXPLAIN SELECT * FROM employees WHERE position = 'manager'

  1. EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'

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

使用全值匹配能够正常使用索引

  1. EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'

使用 left 或者其他的数据库函数导致索引未命中。会走一个全表扫描。

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

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

全值匹配如下。

  1. EXPLAIN SELECT * FROM employees WHERE name'LiLei' AND age = 22 AND position ='manager'

如果我们中间增加一个范围查找,会导致右边的查询条件无法使用索引。我们对比一下,上下两条 sql 的差别。

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

5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句

对于 select 指定字段查询,如果查询的所有列都是索引上的数据,那么可以减少 “回表”。查询的效率高于 select *

  1. explain select name,age from employees where name = 'Lilei' and age = 23 and position= 'manage'

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

6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

  1. EXPLAIN SELECT * FROM employees WHERE name != 'LiLei'

7.is null,is not null 也无法使用索引

因为在 mysql 变长的数据类型中,如 varchar 。null 不会存储,它需要一个额外的标志位来存储。所以如果我们使用 is null , is not null 是无法使用索引的。所以我们在 DDL 语句中尽量让每个字段都有默认值。

  1. EXPLAIN SELECT * FROM employees WHERE name is null

8.like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作

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

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

问题:解决like'%字符串%'索引不被使用的方法?a)使用覆盖索引,查询字段必须是建立覆盖索引字段

  1. EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%'

b)当覆盖索引指向的字段是varchar(380)及380以上的字段时,覆盖索引会失效!

9. 如果查询条件导致类型转换会导致索引失效

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

加上单引号,无需数据转换 name 存储数据也是 varchar 类型。

  1. EXPLAIN SELECT * FROM employees WHERE name = '1000'

如果不加单引号,发生数据类型转换导致本次查询索引失效。

  1. EXPLAIN SELECT * FROM employees WHERE name = 1000; 

10.少用or,用它连接时很多情况下索引会失效

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

小总结

like KK%相当于=常量,%KK和%KK% 相当于范围 下面是常见的几种索引查询失效或者,可用的判定

总结 1

总结 2

参考文档

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

鸿蒙官方战略合作共建——HarmonyOS技术社区

【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

带你轻松入门 RabbitMQ

带你轻松入门 RabbitMQ

轻松入门RabbitMQ
共4章 | loong576

53人订阅学习

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

14人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

42人订阅学习

视频课程+更多

计算机网络精讲教程

计算机网络精讲教程

讲师:王艳华84288人学习过

企业级运维监控平台Zabbix深度实践【2021年录制基于zabbix5】

企业级运维监控平台Zabbix深度实践【2021年录

讲师:高俊峰6746人学习过

汇编语言教程

汇编语言教程

讲师:杨大毛698人学习过

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微