一、数据库执行计划概念¶
- 执行计划介绍：
在介绍数据库服务程序运行逻辑时，在SQL层处理SQL语句时，会根据解析器生成解析树（多种处理方案）；
然后在利用优化器生成最终的执行计划，然后在根据最优的执行计划进行执行SQL语句；
作为管理员，可以在某个语句执行前，将语句对应的执行计划提取出来进行分析，便可大体判断语句的执行行为，从而了解执行效果；
可以简单理解：执行计划就是最优的一种执行SQL语句的方案，表示相应SQL语句是如何完成的数据查询与过滤，以及获取；
二、数据库执行计划获取¶
可以利用命令进行获取执行计划信息：explain/desc
explain select * from zq.t100w where k2='VWlm';
或者
desc select * from zq.t100w where k2='VWlm';
命令执行输出信息：
输出信息解释说明：
| 序号 | 字段 | 解释说明 |
|---|---|---|
| 01列 | ID | 表示语句执行顺序，单表查询就是一行执行计划，多表查询就会多行执行计划； |
| 02列 | select_type | 表示语句查询类型，sipmle表示简单(普通)查询 |
| 03列 | table |
表示语句针对的表，单表查询就是一张表，多表查询显示多张表； |
| 04列 | partitions | 查看数据库表分区情况 |
| 05列 | type*** |
表示索引应用类型，通过类型可以判断有没有用索引，其次判断有没有更好的使用索引 |
| 06列 | possible_keys |
表示可能使用到的索引信息，因为列信息是可以属于多个索引的 |
| 07列 | key |
表示确认使用到的索引信息 |
| 08列 | key_len*** |
表示索引覆盖长度，对联合索引是否都应用做判断 |
| 10列 | rows |
表示查询扫描的数据行数（尽量越少越好），尽量和结果集行数匹配，从而使查询代价降低(百分比) |
| 11列 | filtered | 表示查询的匹配度(表示扫描后结果集与扫描行数比例关系) |
| 12列 | Extra*** |
表示额外的情况或额外的信息,表示索引应用过程是否进行了优化设置 |
三、数据库索引应用类型¶
利用类型信息，来判断确认索引的扫描方式，常见的索引扫描方式类型：
| 序号 | 类型 | 解释说明 |
|---|---|---|
| 01 | ALL - ok | 表示全表扫描方式，没用利用索引扫描类型； |
| 02 | index | 表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息（主键index=全表扫描） |
| 03 | range | 表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息； |
| 04 | ref | 表示辅助索引等值（常量）查询，精准定义辅助索引的查询条件 |
| 05 | eq_ref | 表示多表连接查询时，被驱动表的连接条件是主键或者唯一键时，获取的数据信息过程； |
| 06 | const/system | 表示主键或者唯一键等值（常量）查询，精准定义索引的查询条件 |
扫描类型执行计划展示效果：
3.1 扫描类型-ALL¶
此类型出现原因：查找条件没有索引；
mysql> explain select * from zq.t100w where k1='lm';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
此类型出现原因：查询条件不符合查询规律（like %%-只针对辅助索引，不影响主键索引-range）；
mysql> explain select * from zq.t100w where k2 like '%ma%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 997335 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
此类型出现原因：查询条件使用的了排除法（!=/not in-只针对辅助索引，不影响主键索引）；
mysql> explain select * from zq.t100w where k2 not in ('wwee','ccee');
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | idx_k2 | NULL | NULL | NULL | 997335 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3.2 扫描类型-index¶
此类型出现原因：扫描查询列设置了辅助索引信息，但是没有基于索引列设置查询条件
mysql> explain select k2 from zq.t100w;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | index | NULL | idx_k2 | 17 | NULL | 997335 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
3.3 扫描类型-range¶
此类型出现原因：查找条件是范围信息（> < >= <= between and in or）
mysql> explain select * from zq.t100w where k2 in ('abc','llee');
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t100w | NULL | range | idx_k2 | idx_k2 | 17 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
特殊说明：在利用in查询数据信息时，查询效果和逻辑语句or的查询效果是一致；
此类型出现原因：查找条件是模糊信息（like）
mysql> explain select * from zq.t100w where k2 like 'na%';
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t100w | NULL | range | idx_k2 | idx_k2 | 17 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
3.4 扫描类型-ref¶
此类型出现原因：查找条件是精确等值信息
mysql> explain select * from zq.t100w where k2='lmaa';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t100w | NULL | ref | idx_k2 | idx_k2 | 17 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
3.5 扫描类型-eq_ref¶
此类型出现原因：被驱动表的链表条件是主键或唯一键时
mysql> desc select city.name,country.name,city.population from country join city on city.countrycode=country.code;
当连接查询没有where条件时：
左连接查询时，前面的表是驱动表，后面的表是被驱动表，右连接查询时相反；
内连接查询时，哪张表的数据较少，哪张表就是驱动表
mysql> desc select city.name,country.name,city.population from city join country on city.countrycode=country.code where city.population<100;
当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表
说明：在没有设置比较合理索引情况下，默认选择结果集小的作为驱动表，即小表驱动大表；
但是，此时如果给city表中的population加上索引信息，查找数据的执行计划才是最优的，对应获取数据的性能是最好的；
-- city表中的population加上索引信息
mysql> alter table city add index index(population);
-- 了解这个查询的执行效率
mysql> desc select city.name,country.name,city.population from city join country on city.countrycode=country.code where city.population<100;
MySQL驱动表和被驱动表说明：https://www.cnblogs.com/oldboy666/p/16892774.html
3.6 扫描类型-const¶
此类型出现原因：查询的数据条件是主键或唯一键，并且是精确等值查询；
mysql> desc select * from world.city where id=10;
