mysql创建每天执行计划_mysql执行计划-CSDN博客

本文链接：https://blog.csdn.net/weixin_33098963/article/details/113683438

执行计划分析

什么是执行计划?

select * from city where sname='张飞';

分析的是优化器按照内置的代价计算模型算法，

最终选择后的执行计划。

cost ? 代价，成本

对于计算机来讲，代价是什么?

io,cpu,mem

查看执行计划

mysql> explain select * from world.city;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

执行计划显示结果的分析

table :此次查询涉及到的表

type :查询类型：全表扫，索引扫

possible_keys :可能会用到的索引

key :最后选择的索引

key_len :索引的覆盖长度

rows :此次查询需要扫描的行数

Extra :额外的信息

详细分析

table:此次分析所涉及到的表，多对查询时，定位到有问题的表

type 查询类型

全表扫描：不会利用到所有的索引 ALL

例如:select * from city;

select * from city where sname = like '%y%';

select * from city where sname not in '张飞';

索引扫描 : index < range < ref < eq_ref < const(system)

index :全索引扫描

desc select contrycode from world ;contrycode有索引

range:索引范围查询: > < >= <= like in or between and

desc select countrycode from world.city where id > 10;

desc select countrycode from world.city where countrycode like 'F%';

desc select * from city where countrycode in ('USA','CHN');

特殊情况：查询条件为主键是可以走range 范围查询

!= 和 not in 会自动拆分为范围查询

ref : 辅助索引的等值查询

desc select * from city where countrycode = ('USA');

eq_ref :多表链接中，非驱动表的链接条件是主键或唯一键.

a join b on a.xx = b.yy

desc select c.name,l.name from city c join country l

on c.`CountryCode` = l.`Code` where c.`Name`='Shanghai';

const(system):聚簇索引等值查询。

desc select * from city where id=1;

possible_keys 可能用到的索引，所有和此次查询有关的索引情况

key:当次查询选择的索引

key_len :联合索引的覆盖长度

例如:index(a,b,c) 我们希望我们的查询，对联合索引，应用的越充分越好,

key_len可以帮我判断，此次查询走了联合索引的几部分.

假设，某条查询可以完全覆盖三列联合索引

select * from t1 where a= and b = and c =

ken_len = a长度 ? + b长度? + c长度?

长度受到数据类型字符集影响

长度指的是，列的最大的字节存储长度

数字:

not null 没有not null

tinyint 1字节 1+ 1

int 4字节 4+ 1

bigint 8字节 8 + 1

key_len:

a int not null --->4

a int ---->5

字符: utf8------->一个字符最大占3个字节

not null 没有not null

char(10) 3* 10 3*10 +1

varchar(10) 3* 10 + 2(最多占用2个字节存储字符的长度) 3*10 + 2+ 1

b char(10) not null 30

b char(10) 31

c varchar(10) not null 32

c varchar(10) 33

字符: utf8mb4------->一个字符最大占4个字节

not null 没有not null

char(10) 4* 10 4*10 +1

varchar(10) 4* 10 + 2(最多占用2个字节存储字符的长度) 4*10 + 2+ 1

b char(10) not null 40

b char(10) 41

c varchar(10) not null 42

c varchar(10) 43

create table t1 (

a int not null ,

b int,

c char(10) not null,

d varchar(10) ) engine = innodb charset=utf8mb4;

alter table t1 add index idx_abcd(a,b,c,d);

desc select a,b,c,d from t1 where a= 1 and b = 3 and c = 'd' and d = '4';

1SIMPLEt1NULLrefidx_abcdidx_abcd92const,const,const,const1100.00Using index

Extra

using filesort:表示此次查询使用到了文件排序操作，

说明此次查询的排序操作 order by group by distinct ...(可能有这些语句)

列子;

desc select * from city where countrycode='CHN' order by population ;

4ab340b72ec3

可以看到，extra出现了using filesort ，这时我们通过添加where条件字段和population字段的联合索引看是否能解决此问题；

alter table city add index idx_c_p(countrycode,population);

desc select * from city where countrycode='CHN' order by population ;

4ab340b72ec3

可以看到此问题已经解决。

索引应用规范

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

1.建表时一定要有主键，一般是个无关列

2.选择唯一性索引

3.在联合索引中，让唯一索引(重复值较少的列)作为最左边的键，可以更快速的来查询。

4.如果字段过长最好使用前缀索引.

5.索引数据不是更多更好

可能产生的问题：

1).磁盘空间增大

2).修改表时，对索引的重构和更新和麻烦，越多的索引，会使更新表的时间变慢

3).优化器的负担很重，会影响优化器的选择

6.删除不使用的或者很少使用的索引

(percona toolkit).py-duplicate-key-checker 工具可以查询到重复的索引

7.大表加索引，要在业务不繁忙的期间操作(8.0以前要锁表)

8.尽量少在经常更新值的列上加索引(update的列，这样容易索引失效)

9.建索引的原则：

1)必须要有主键，如果没有可以作为主键条件的列，创建无关列。

2)经常做为where条件列，order by group by join distinct的条件(业务：产品功能+用户行为)

3)最好使用唯一值多的列作为索引条件，如果索引列重复值较多，可以考虑使用联合索引。

4)列值长度较长的索引，我们建议使用前缀索引

5)降低索引条目，一方面不要创建没有用的索引，不常使用的索引清理，percona toolkit的工具.

6)索引维护要避开业务繁忙期

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

1 没有查询条件，或者查询条件没有建立索引

select * from tab; 全表扫描

select * from tab where 1=1 ;

在业务库中，特别是数据量比较大的表，

是没有全表扫描的需求。

对用户查看是非常痛苦的。

对服务器来讲是毁灭性的。

2.查询结果集是原表中的大部分数据，应该是15-30%,

查询的结果集，超过了总行数的25%，优化器就觉得没有必要

走索引。

原因是？？？跟数据库的预读能力有关，以及一些参数有关。彩蛋？？

解决办法，如果业务允许，进行分批次查询，或者进行分页查询。

limit 查询。

如果不允许，尽量不要把这部分数据存储在mysql，可以放到redis这种类似的

内存数据库中.

3。索引本身失效，统计数据不真实.

索引和表都有自我维护的能力。

对于表内容的变化比较频繁的情况下，统计信息不准确，过旧，有可能出现索引失效的情况。

解决办法：重建索引.

现象：有一条sql语句平时查询很快，突然很慢，会是什么原因。

表跟新频繁，统计信息过旧，导致索引失效.

查看表和索引的统计信息:

select * from innodb_table_stats; 表的统计信息

select * from innodb_index_stats; 索引的统计信息

不是实时跟新。

表的实时跟新解决办法:多删除点才可以跟新。

所以解决办法是，重建索引。

4.如果函数作用在索引上，或者对索引列进行运算，运算包括(+,-,* ,/,!等)

5.隐式转换导致索引失效，这一点应当引起重视

这里我测试字符串转数字不会失效，只有数字转字符串就会失效。

6.<>,not in 不走索引(辅助索引)(聚簇会走)

单独的> < in 又可能走索引，也可能不走.

7.like '%_' 百分号在前面不走索引。

如有这种大量的需求可以用mongodb数据库

扩展：优化器针对索引的算法

1mysql索引的自优化-AHI(索引页的索引,自适应hash索引)

mysql的innodb引擎，能够创建的只有btree。

ahi作用：自动评估热的内存索引page，生成一个hash索引表，加速

索引读取的速度。

2.mysql索引的自优化 -Change buffer

insert update delete 数据

对于聚簇索引会立即更新

对于辅助索引，不是实时更新

在innodb内存结构中，加入了insert buffer(会话),现版本叫change buffer

change buffer的功能是临时缓冲辅助索引需要的数据更新。

当我们需要查询新insert 的数据，会在内存中进行mege 合并，此时辅助索引就是最新的。

3 ICP 索引下推

在引擎层再次根据索引进行过滤，减少磁盘的io。

例如 index(a,b,c) select * from t1 where a= and c=

在sql层只走部分索引a，到引擎层再次走索引过滤掉部分c的数据，

在进行磁盘数据读取，减少了磁盘的io.

所涉及的算法调优的参数:

select@@optimizer_switch;

|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |

如何修改算法参数？

1).my.cnf

2).set global optimizer_switch='batched_key_access=on'

3).hints 在语句里加入 /*+ */ 也就是oracle里面的hint

4.MRR muti range read

mrr = on mrr_cost_based = on(开启mrr需要关闭 mrr_cost_based)

set global optimizer_switch='mrr_cost_based=off';

默认的辅助索引查询，会多次回表，产生随机io。

mrr 缓存一部辅助索引的id，然后进行排序，利用rowid buffer，然后进行一次或者

少次数的回表，减少回表次数以及随机IO；提高服务器的性能。

辅助索引回表 >> 聚簇索引

转换为