MySQL执行计划分析及案例_mysql 执行计划 key_len

大家好，我是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，表示未使用索引

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

0. 查看type是否为ALL,ALL代表全表扫描
1. 查看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编码的数据表

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

数据类型最大存储长度最大索引长度int(k)kk+1char(m)m*3m*3+1varchar(n)n*3m*3+3

对于utf8mb4编码的数据表

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

数据类型最大存储长度最大索引长度int(k)kk+1char(m)m*4m*4+1varchar(n)n*4m*4+3

测试联合索引长度

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

?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应用的长度

执行计划使用场景

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

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

?索引应用规范

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

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

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

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

简历案例

zabbix监控系统架构整改

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

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

问题：

0. zabbix版本
1. 数据库版本
2. zabbix数据库500G数据，存储在一个文件里

优化建议：

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

优化结果：

监控状态良好

原因分析：

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