分析执行计划

10.1 是什么?

优化器(算法)最终得出的,代价最低的,SQL语句的执行方案.

10.2 为什么要分析执行计划?

场景一: 分析比较慢的语句.
场景二: 上线新业务,可能会包含很多select update delete...,提前发现问题.

10.3 如何抓取执行计划

a. 抓取目标: select update delete

b. 抓取执行计划的方法 :

mysql> desc   select * from world.city where countrycode='CHN';


+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

10.4 执行计划的分析:

table  :  操作的表
type   :  操作类型(全表\索引) ,ALL  index   range  ref   eq_ref  const(system)
possible_keys : 有可能用的索引
key    : 真正要用是哪索引
key_len: 索引覆盖长度(联合索引)
rows   : 预估需要扫描的行数
Extra  : using where  using index  using index condition  using  filesort sort   using temp

对type(操作类型)的详解

状态的分为:
第一种:ALL: 全表的扫描(不走索引)
可能会用到全表扫描的案例:
mysql> explain   select * from world.city ;                                    #进行全表扫描
mysql> explain   select * from world.city where countrycode !='chn';             #进行不相等的查找
mysql> explain   select * from world.city where countrycode like '%hn%';          #进行模糊查找
mysql> explain   select * from world.city where countrycode not in ('chn','usa'); #使用in语句进行查找

第一部分:index 全索引进行扫描

需要扫描整颗索引树,才能得到想要的结果. 
desc select id ,countrycode from world.city;

第二部分:range 索引范围

使我们优化索引的底线,也是应用最多的
mysql> desc select * from city where id<10;
mysql> desc select * from city where countrycode like 'ch%';
mysql> desc select * from city where countrycode in ('CHN','USA');

第三部分:ref : 辅助索引等值查询

mysql> desc  select * from city where countrycode='CHN';

第四部分:eq_ref : 非驱动表,连接条件是主键或唯一键.

mysql> desc select a.name, b.name,a.countrycode,a.population  
from city  as a 
join country as b 
on a.countrycode=b.code where a.population<100;


在多表连接时,小结果集的表驱动大表
优化会自动判断查询语句中的谁做为驱动表更合适.有可能会出现选择错误.
我们可以通过left join 强制驱动表干预执行计划.

如何判断一条连接语句中,谁是驱动表

第一种:优化器的选择

mysql> desc  select * from city  join country on city.countrycode=country.code where city.population<100000   and country.SurfaceArea>10000000;

第二种:人为的选择:

mysql> select count(*) from  country where country.SurfaceArea>10000000;  
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select celect count(*) from city where city.population<1
+----------+
| count(*) |
+----------+
|      517 |
+----------+
1 row in set (0.01 sec)
补充: 如果 where后的列中都有索引,会选择结果集小的作为驱动表.

第五部分:const(system) 索引等值查询

mysql>  desc select * from city where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
##完全利用索引进行查询  就是索引的等值的查询

key_len: 索引覆盖长度(联合索引)的详解

a. 介绍

(联合)索引覆盖长度
idx(a,b,c) ----> a (10) b(20) c(30)

b. 如何计算索引列的key_len
key_len 和每个列的最大预留长度(字节)有关.

数据类型    utf8mb4       没有 not null (yes就加一)
tinyint      1              1    
int          4              1
char(10)     4*10           1
varchar(10)  4*10+2         1 




mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |  字符集为:utf8mb4 
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |  ####索引长度:4
| Name        | char(35) | NO   | MUL |         |                |  ####索引长度:35X4
| CountryCode | char(3)  | NO   | MUL |         |                |  ####索引长度:3X4
| District    | char(20) | NO   | MUL |         |                |  ####索引长度:20X4
| Population  | int      | NO   |     | 0       |                |  ###索引长度:4
+-------------+----------+------+-----+---------+----------------+

c. 联合索引应用细节

idx(a,b,c)    ---->   a  ab   abc 
完全覆盖 : 
    a=  and b=   and c=
    a=  and c=  and  b=  等值打乱顺序的
    a=  and b=  and  c范围 
    a=  anb b字符范围 and c=
        
部分覆盖 
    a=  and b= 
    a= 
    a=  and c=
    a=  anb b数字范围 and c=
完全不覆盖  bc  --> bc  b   
    b  
    c
    bc
    cb 

优化案例:
idx(k1,num,k2)
1. mysql> desc select * from t100w where k1='Vs' and num<27779 and k2='mnij'

优化方案: 修改索引为idx(k1,k2,num)

extra :索引的使用率

1.1出现using where问题的解决思路:

using index  ##最优质的的索引状态 使用了索引覆盖扫描 
using where  ###部分用到索引,部分么有用到索引    使用where回表扫描数据行,说明目标表的索引没有设计好 
解决问题的思路:
第一步:回到出问题的表  table----->获取到出问题的表
第二步:看原始查询语句中的wherer条件列
第三步:查询列的索引情况: ---->  show index from tl  
第四步:按需求进行优化索引

1.2出现using filesort (使用额外排序)问题的解决思路

第一步:  ##table---->获取到出问题的表
第二步: 查看原始语句中的  order by group by distinct  等
第三步: 查看列的索引情况
第四步: 按需进行优化索引

优化案例

mysql> desc select *from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
第一步:查看列的索引情况:
mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+------
第二步:按照所需的条件进行创建索引:创建联合索引
mysql> alter table city add index idx_c_p(countrycode,population);    ###创建联合索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select *from city where countrycode='CHN' order by population;  ###查看索引执行计划
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode,idx_c_p | idx_c_p | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-------+    

1.3出现using temp 的情况分析

a. 条件范围是不是过大. 
b. having  order by   ##这些条件必然出现using temp 进行额外排序
c. 子查询
大几率开发需要改写语句了.

扩展项: 关于索引的优化器算法:ICP MRR

第一部分: ICP : Index Condition Pushdown(优化器的选择)

优化器算法:

    a. 查询优化器算法:
    mysql> select @@optimizer_switch;
    b. 设置优化器算法: 
    mysql> set global optimizer_switch='index_condition_pushdown=off';
    hits方式: 
    https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
    配置文件: 
    my.cnf

例子 : 
mysql> set global optimizer_switch='index_condition_pushdown=off';

mysql> desc select * from t100w where k1='Vs'  and num<27779 and k2='mnij';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | range | idx           | idx  | 14      | NULL |   29 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set global optimizer_switch='index_condition_pushdown=on';

mysql> desc select * from t100w where k1='Vs'  and num<27779 and k2='mnij';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t100w | NULL       | range | idx           | idx  | 14      | NULL |   29 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

压测:a.  开ICP 2000次语句压测  索引顺序不调整
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query=" select * from t100w where k1='Vs'  and num<27779 and k2='mnij'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

4.580 seconds
4.569 seconds
4.431 seconds
4.433 seconds
4.391 seconds

b.  关 ICP 2000次语句压测  索引顺序不调整
5.327 
5.516
5.267
5.330
5.293 seconds

c. 索引顺序优化 压测 
4.251
4.143 

11.2 MRR  
https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html

12. 索引应用规范

12.1 建立索引的原则（DBA运维规范）
(1) 必须要有主键,业务无关列。
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引列,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
mysql> select count(distinct left(name,19)) from city;
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期，建议用pt-osc。 
(7) 联合索引最左原则



12.2 不走索引的情况（开发规范）
11.2.1 没有查询条件，或者查询条件没有建立索引
select * from t1  ;
select * from t1 where id=1001 or 1=1;

作业: 
SQL审核和审计. yearning.io  github, inception



12.2.2 查询结果集是原表中的大部分数据，应该是15-25%以上。
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。

可以通过精确查找范围，达到优化的效果。
1000000

>500000 and 


12.2.3 索引本身失效，统计信息不真实（过旧）

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效,统计数据不真实

innodb_index_stats  
innodb_table_stats  

mysql> ANALYZE TABLE world.city;


12.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

12.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
mysql> desc select * from b where telnum=110;
mysql> desc select * from b where telnum='110';

12.2.6 <> ，not in 不走索引（辅助索引）


12.2.7 like "%_" 百分号在最前面不走