复杂sql语句执行计划分析

一.创建表db_tb_filter

CREATE TABLE `db_tb_filter` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `schema_name` varchar(32) NOT NULL COMMENT '逻辑库名称',
  `database_name` varchar(32) NOT NULL COMMENT '数据库名称',
  `table_name` varchar(32) NOT NULL COMMENT '表名称',
  `table_type` smallint(1) NOT NULL COMMENT '水平表1,垂直表2',
  `divided_column` varchar(32) DEFAULT NULL COMMENT '拆分列',
  `is_exist` smallint(1) NOT NULL COMMENT '是否已经录入系统',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `database_name` (`database_name`,`table_name`) USING BTREE,
  KEY `table_name_idx` (`table_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=245 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

二.在表中要找到重复表的所有的表信息：

select id,schema_name,database_name,table_name,if(table_type=1,"水平表","垂直表")as table_type,divided_column,is_exist from db_tb_filter where table_name in (select table_name from (select table_name,count(*)c from db_tb_filter group by table_name)as a where c>1)order by table_name;```

查看执行计划：

desc select id,schema_name,database_name,table_name,if(table_type=1,"水平表","垂直表")as table_type,divided_column,is_exist from db_tb_filter where table_name in (select table_name from (select table_name,count(*)c from db_tb_filter group by table_name)as a where c>1)order by table_name;

三.执行计划分析：

1.执行顺序：
由于执行顺序满足：
id相同，从上往下一次执行。
id不同，id越大优先级越高，越先被执行。
所以执行顺序为先4>3>1>2(1,2,3,4为从上往下的四条语句）。

2.先执行第4项：

1）类型为derived，说明这条语句为派生表的查询，即：

select table_name,count(*)c from db_tb_filter group by table_name

这条语句。
2）table=db_tb_filter,说明这条语句在db_tb_filter表上执行。表上没有分区所以partition是null。
3）type，possible_keys，key分别说明了使用了索引，可能使用的索引和实际使用的索引。这里使用了索引table_name的索引。有key_len可以知道索引长度为98：
由于table_name类型为变长varchar(32)NOTNULL，默认为utf8:
key_len=32*3+2(变长字段)=98
4）因为没有关联其他表所以ref为null。
5）rows估算出大概要找到的行数，这里这张表一共101行。
6）filtered选取的行数占所有行的比例，这里为100说明所有行都被选出来了。
7）extra表明表示select操作使用了索引覆盖，避免回表访问数据行。

3.再执行第3项：

1）类型为materialized，说明将子查询结果保存为了临时表，后续对子查询结果集的访问将直接通过临时表获得：

select table_name from (select table_name,count(*)c from db_tb_filter group by table_name)as a where c>1)order by table_name

select table_name,count(*)c from db_tb_filter group by table_name 保存为临时表a。
2）table=derived3，derived表示在派生表上进行查询，3表示id号为3的语句生成的表。
3）type=all说明是对临时表全盘扫描。并未使用索引。也无关联其他表（ref=null）。
4）估计扫描101行，filtered=33.33在这里并不准确，实际全部都没被选中，应该为0。
5）use where：表示 SQL 操作使用了 where 过滤条件。

4.然后执行第1项：

1）类型为primary，此语句有子句：

(select table_name from (select table_name,count(*)c from db_tb_filter group by table_name)as a where c>1)order by table_name;

2）table=subquery2，subquery和2说明id=2的查询是他的子句。
3）type=all,全表扫描。
4）extra=Using temporary; Using filesort。是因为使用order by和group by会形成临时表，并在外部文件排序查找（order by table_name）。

5.最后执行第2项：

1）类型为primary，有子句，为第1项：
2）table表示了在db_tb_filter表上查找。
3）type=ref：查找条件列使用了索引而且不为主键和unique。possible_keys，key表明了使用的索引，和第一项一样长度也是98。
查看ref=<//subquery2>.table_name说明关联了子句中的table_name（因为order by table_name）。