mysql执行计划

执行计划分析

什么是执行计划?

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

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

最终选择后的执行计划。

cost ? 代价，成本

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

io,cpu,mem 

查看执行计划 

mysql> explain select * from world.city;

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

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |

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

|  1 | SIMPLE      | city  | NULL      | ALL  | NULL          | NULL | NULL    | NULL | 4046 |  100.00 | NULL  |

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

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';

1 SIMPLE t1 NULL ref idx_abcd idx_abcd 92 const,const,const,const 1 100.00 Using index

Extra 

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

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

列子;

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

可以看到，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  ;

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

索引应用规范

建立索引的原则（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；提高服务器的性能。

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

转换为

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

5.SNLJ  多表关联优化的算法 

a join b on a.xx = b.y y where a.c = b.c 

for each row  in b {

for each row in b{

 a.xx = b.xx send to  client }

以上例子，可以通过left join 强制驱动表

6.BNLJ  多表关联优化算法 

a join b on a.xx = b.y y where a.c = b.c

将a表满足条件的数据一次性（使用块）跟b表比对，并返回，减少了

循环的次数。提高了效率。a为驱动表.

when

7 BKA 

BNLJ的基础上，也就是把a表的数据放到一个块中，然后在进行排序，然后在跟

b表进行关联，可以减少io次数，和随机io次数。优化非驱动表的关联辅助索引。

开启的方式  开启：MRR ，在开启BKA。