mysql 执行计划分析_MySQL执行计划分析及案例

大家好，我是anyux。本文介绍MySQL执行计划分析。

分析执行计划

possible-keys：可能会用的索引

演示1：possible-keys未被使用

use world;desc select * from city where countrycode!="CHN";

下面图中type值为ALL，意味着select * from city where countrycode!="CHN";这条语句执行计划是全表扫描，然后看下possible-keys，对应的值为CountryCode,说明它有可能走，但不一定走。再查看key值为NULL，表示未使用索引

最终确定是否通过索引的两点

查看type是否为ALL,ALL代表全表扫描查看key是否为NULL，NULL代表没走任何索引，为全表扫描演示2：possible-keys未被使用

use world;alter table city add index idx_c_p(countrycode,population);desc select * from city where countrycode!="CHN";

下面图中type值为ALL，意味着select * from city where countrycode!="CHN";这条语句执行计划是全表扫描，然后看下possible-keys，对应的值为CountryCode,idx_c_p,说明它有可能走，但不一定走。再查看key值为NULL，表示未使用索引

演示3：possible-keys被使用

use world;alter table city add index idx_c_p(countrycode,population);desc select * from city where countrycode="CHN";

下面图中type值为ALL，意味着select * from city where countrycode="CHN";这条语句执行计划是全表扫描，然后看下possible-keys，对应的值为CountryCode,idx_c_p,说明它有可能走，但不一定走。再查看key值为CountryCode，表示使用了索引

演示4：possible-keys被使用

use world;alter table city add index idx_c_p(countrycode,population);desc select * from city where countrycode="CHN" and population<1000;

下面图中type值为ALL，意味着desc select * from city where countrycode="CHN" and population<1000;这条语句执行计划是全表扫描，然后看下possible-keys，对应的值为CountryCode,idx_c_p,说明它有可能走，但不一定走。再查看key值为idx_c_p，表示使用了索引

key_len：索引覆盖长度

如果name字段字utf8mb4格式定义为 varchar(10)，即，可以分别存储10个中文，10个英文，10个数字。varchar(10)可能存储的最大字节长度是40。最小的字节长度为10。所以在存储索引时，都是预留40个索引长度

对于utf8而言，varchar(10)可能存储的最大字节长度是30.最小的字节长度为10。

use test;show create table t100w;

此时k2列的类型为char(4),即可存储的最大字节长度为16。

desc select * from t100w where k2='rsEF';

key_len索引键长度为17，和t100w表中的k2键长度不一致

多出来的1个字节，是来用标是否非空的。表中k2是支持值为NULL的

修改k2列为not null

alter table t100w change k2 k2 char(4) not null;

在线DDL修改表，锁表时间12.57秒

再次查看key_len

desc select * from t100w where k2='rsEF';

key_len值为16

测试单列索引长度

创建utf8测试表,查看字段对应的索引长度

use test;create table tmp_index(id int ,k1 char(2),k2 varchar(4),k3 char(4),k4 varchar(8) ) engine=innodb charset=utf8;alter table tmp_index add index ind_id(id);alter table tmp_index add index ind_k1(k1);alter table tmp_index add index ind_k2(k2);alter table tmp_index add index ind_k3(k3);alter table tmp_index add index ind_k4(k4);insert into tmp_index values(1,'张三','平湖秋月','天上人间','xxx'),(2,'李四','春雷滚滚','星火燎原','yyy');desc select * from tmp_index where id=1;

下图中key_len为5，id的列为int类型，默认4个存储长度，加上一个标识非空，所以索引长度为5

desc select * from tmp_index where k1='张三';

下图中key_len为7，k1的列为char(2)类型，默认2个存储长度，2*3+1(标识非空)，所以索引长度为7

desc select * from tmp_index where k2='平湖秋月';

下图中key_len为15，k2的列为varchar(4)类型，默认2个存储长度，4*3+1(1个开始标识)+1(1个结尾标识)+1(标识非空)，所以索引长度为15

desc select * from tmp_index where k3='天上人间';

下图中key_len为13，k3的列为varchar(4)类型，默认12个存储长度，4*3+1(标识非空)，所以索引长度为13

desc select * from tmp_index where k4='xxx';

下图中key_len为27，k3的列为varchar(8)类型，默认24个存储长度，8*3+1(1个开始标识)+1(1个结尾标识)+1(标识非空)，所以索引长度为27

得出结论,对于utf8编码的数据表

注意，以下数据列设置为空

对于utf8mb4编码的数据表

注意，以下数据列设置为空

测试联合索引长度

创建联合索引时，唯一值多的，放在最左侧

show index from tmp_index;

删除之前的索引

alter table tmp_index drop index ind_id;alter table tmp_index drop index ind_k1;alter table tmp_index drop index ind_k2;alter table tmp_index drop index ind_k3;alter table tmp_index drop index ind_k4;

建立联合索引

alter table tmp_index add index indx(k1,k2,k3,k4);alter table tmp_index drop index indx;

desc select * from tmp_index where k1='张天' and k2='平湖秋月' and k3="天上人间" and k4='xxx'\G

下图中type为ref，是因为我们建立是辅助索引，而ref为4个const，是因为4个值都是唯一的，而key_len是62

如下图，通过公式计算得出结果62,正好与key_len一样

在有联合索引的时候，执行计划查看的时候，一定是key_len值越大越好，对于单列，key_len值越小越好

varchar(20)

1.能存20个任意字符

2.不管存储的是什么字符，数字，中文，都是1个字符最大预留长度是4个字节

3.对于中文，1个字符占4个字节

4.对于数字和字母，1个字符实际占用大小是1个字节

对于查看列的长度，可以使用length函数

use test;select length(id),length(k1),length(k2),length(k3),length(k4) from tmp_index;

在where查询中如果出现> < >= <= like

desc select * from tmp_index where k1='张三' and k3="天上人间" and k4='xxx' and k2>'平湖秋月';

此时需要调整联合索引的结构

alter table tmp_index add index idx1(k1,k3,k4,k2);

多子句查询，应用联合索引

desc select * from tmp_index where k1='张三' order by k2;

此时需要调整联合索引的结构

alter table tmp_index add index indx3(k1,k2);

Extra:Using filesort

如果出现Using filesort ,表明有额外排序出现，说明索引设计或应用不合理

检查SQL语句中是否出现过order by ,group by ,distinct,union等关键字,关注key_len应用的长度

执行计划使用场景

分析数据库性能降低的原因

数据库突然卡住，消耗大量资源。处理过程如下登录数据库，show processlist,查看阻塞原因对找到的SQL语句，执行计划解析，查看使用索引情况依据分析情况，在业务低谷期间，修改索引常规性的卡顿开启slowlog，分析slowlog执行计划分析slowlog中的语句，查看索引使用情况依据分析情况，在业务低谷期间，修改索引

索引应用规范

建立索引的原则(运维规范)

必须要有主键，一般是无关列，自增长为常在where,order by ,group by,join on,distinct条件后的列做索引最好使用唯一值多的列做为联合索引的前导列，就是将唯一值多的列，优先使用，其他的按照联合索引的优化细节来做列值长度较长的索引列，使用前缀索引降低索引条目，不要创建无效的索引，删除无效索引，使用pt-duplicate-key-checker,percona tookit工具排查索引维护要避开业务高峰期小表(万条记录以下)不要建立索引

不走索引的情况(开发规范)

没有查询条件，或者查询条件没有建立索引查询结果集是原表中的大部分数据，在1/4以上，可能不会使用索引索引本身失败，统计数据不真实。(实际情况，同一个SQL语句，昨天运行很快，今天很慢。原因可能就是索引失效了)查询条件使用函数操作索引列，或对索引列进行运算，包括+,-,*,/隐式转换导致索引失效<>,not in 不走索引(辅助索引)like '%aa' 百分号在最前面不走联合索引

简历案例

zabbix监控系统架构整改

环境：zabbix3.2 mariaDB5.5 centos7.3

现象：zabbix很卡，每隔3-4个月，都需要重新搭建一回zabbix，存储空间经常爆满

问题：

zabbix版本数据库版本zabbix数据库500G数据，存储在一个文件里优化建议：

数据库版本升级到10版本，zabbix升级到更高版本，mariaDB版本与原mysql版本号存在差异存储引擎改为TokuDB监控数据按月份进行切割(二次开发zabbix,数据保留机制功能重写，数据库表分离)关闭binlog和双1参数调整优化结果：

监控状态良好

原因分析：

mariaDB原生态支持TokuDB,另外经过测试环境，10.0比5.5性能高出2-3倍TokuDB:insert数据比InnoDB快，数据压缩比也比InnoDB高监控数据按月份切割，为了能够truncate每个分区表，立即释放空间关闭binlog,减少无关日志的记录参数调整，安全性参数关闭，提高性能