MySQL 之执行计划

yyangqqian

已于 2022-03-31 13:34:35 修改

阅读量1.5k

点赞数 1

分类专栏： MySQL 文章标签： java 开发语言后端

于 2020-06-16 14:08:01 首次发布

本文链接：https://blog.csdn.net/const_/article/details/106786068

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

文章目录

使用索引的原则
优化原则
执行计划参数解析
EXPLAIN简介
执行计划各字段含义
- type
- rows

使用索引的原则

最左前缀匹配原则：
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。所以要尽量把“=”条件放在前面，把这些条件放在最后。
不会用到b的索引： where a=1 and c>0 and b=2
会用到b的索引： where a=1 and b=2 and c>0

使用like时注意：
不使用索引： like ‘%L%’
使用索引： like ‘L%’

尽量将or 转换为 union all：
不使用索引： select * from user where name=’a’ or age=’20’
使用索引： select * from user where name=’a’ union all select * from user where age=’20’

字段加函数不会使用索引：
不使用索引： where truncate(price) = 1
使用索引： where price > 1 and price < 2

如果使用数字作为字符，则数字需要加引号，否则mysql会自动在列上加数据类型转换函数：
不使用索引： where mobile=18534874321
在这里插入图片描述
使用索引： where mobile=’18534874321’

使用组合索引时，必须要包括第一个列：

alter table test add index(a,b,c);

不使用索引： where b=1, c=2 、 where b=1、where c=2
使用索引： where a=1, b=1, c=2 、where a=1, b=1 、where a=1, c=2

尽量避免使用is null或is not null：
不使用索引： SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
使用索引：SELECT … FROM DEPARTMENT WHERE DEPT_CODE >0;

不等于（!=）不会使用索引：
不使用索引： SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0;
使用索引：SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;

索引不是越多越好。mysql需要资源来维护索引，任何数据的变更（增删改）都会连带修改索引的值。所以，需要平衡考虑索引带来的查询加速和增删改减速。

优化原则

1、将“=”号放在where条件的最前面，可以最大程度的缩小检索范围。
2、如果索引列作为条件的话，也尽量放在where条件的最前面。
3、使用union all替换or查询条件，因为or会使索引失效。
union 和 union all 区别：union会自带distinct操作，所以union 会清除重复的行，union all 将所有结果显示出来。
4、如果 where 条件后面用了函数，会使索引失效。
5、如果数字作为字符串来查询，将数字加上引号。不加引号会使索引失效，mysql会在数字列上加数据类型转换函数。
6、避免使用is null或is not null、!=，会使索引失效。
7、尽量用表连接(join)替代子查询，表连接性能好于子查询，子查询会创建临时表。

执行计划参数解析

在这里插入图片描述

select_type： 表示对应行是简单还是复杂的查询。
simple：简单查询。查询不包含子查询和union。
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
type： 访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
system：system是const的特例，表里只有一条元素为system。
const：通过一次索引就找到了。
req_ref：唯一索引扫描。
ref：非唯一索引扫描。
range：范围扫描索引。
index：全表扫描索引。
all：全表扫描，从硬盘读取，出现这个，就需要看下sql是否有问题了。
possible_keys： 可供使用的索引。
key： 实际使用的索引，NULL 表示未使用索引。
rows： 读取的行数。（重点查看）

怎么看执行计划？一个简单的优化原则：令sql读取尽可能少的行。

EXPLAIN简介

通过 EXPLAIN，可以分析出以下结果：

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

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

执行计划各字段含义

type

表示查询使用了哪种类型，类型如下：
在这里插入图片描述
从最好到最差依次是：system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref。

system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

id是主键，所以存在主键索引
all Full Table Scan 将遍历全表以找到匹配的行