SELECT DISTINCT table_name,table_schema
FROM information_schema.columns
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select DISTINCT table_name FROM information_schema.columns
WHERE column_key in ('PRI','UNI'));
问题重现：
创建测试库
create database cjc;
use cjc;
创建测试表
vi cjc_test.sh
#!/bin/bash
for i in {1..1000}
mysql -uroot -p1 cjc -e "create table with_pk_${i}(pk int primary key)"
mysql -uroot -p1 cjc -e "create table whthout_pk_${i}(col int)"
sh cjc_test.sh
执行SQL，耗时12秒。
SELECT DISTINCT table_name,table_schema
FROM information_schema.columns
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select DISTINCT table_name FROM information_schema.columns
WHERE column_key in ('PRI','UNI'));
.......
1004 rows in set (12.29 sec)
问题分析：
查看执行计划
explain SELECT DISTINCT table_name,table_schema
FROM information_schema.columns
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select DISTINCT table_name FROM information_schema.columns
WHERE column_key in ('PRI','UNI'));
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
| 1 | PRIMARY | columns | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases; Using temporary |
| 2 | DEPENDENT SUBQUERY | columns | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
查看SQL改写后的语句
MySQL [cjc]> show warnings\G;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select distinct `information_schema`.`columns`.`TABLE_NAME` AS `table_name`,`information_schema`.`columns`.`TABLE_SCHEMA` AS `table_schema` from `information_schema`.`columns` where ((`information_schema`.`columns`.`TABLE_SCHEMA` not in ('sys','information_schema','mysql','performance_schema')) and (not(<in_optimizer>(`information_schema`.`columns`.`TABLE_NAME`,<exists>(/* select#2 */ select 1 from `information_schema`.`columns` where ((`information_schema`.`columns`.`COLUMN_KEY` in ('PRI','UNI')) and (<cache>(`information_schema`.`columns`.`TABLE_NAME`) = `information_schema`.`columns`.`TABLE_NAME`)))))))
1 row in set (0.00 sec)
ERROR: No query specified
将SQL格式化：
/* select#1 */
select distinct information_schema.columns.TABLE_NAME AS table_name,
information_schema.columns.TABLE_SCHEMA AS table_schema
from information_schema.columns
where ((information_schema.columns.TABLE_SCHEMA not in
('sys', 'information_schema', 'mysql', 'performance_schema')) and
not (
< in_optimizer > (information_schema.columns.TABLE_NAME, < exists >
/* select#2 */
select 1
from information_schema.columns
where ((information_schema.columns.COLUMN_KEY in ('PRI', 'UNI'))
(< cache > (information_schema.columns.TABLE_NAME) = information_schema.columns.TABLE_NAME)))))));
SQL自动改写：
可以看到MySQL将非关联子查询:select from A where A.x not in (select x from B);
关联子查询:select from A where not exists (select 1 from B where B.x = a.x);
MySQL [cjc]> show variables like '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace | enabled=off,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
+------------------------------+----------------------------------------------------------------------------+
调大 optimizer trace 的内存容量（否则 trace 的输出会被截断），然后开启了 optimizer trace功能。
MySQL [cjc]> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
MySQL [cjc]> set optimizer_trace_max_mem_size=104856;
Query OK, 0 rows affected (0.00 sec)
执行SQL
SELECT DISTINCT table_name,table_schema
FROM information_schema.columns
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select DISTINCT table_name FROM information_schema.columns
WHERE column_key in ('PRI','UNI'));
1005 rows in set (12.70 sec)
跑完 SQL 后，可以在 INFORMATION_SCHEMA.OPTIMIZER_TRACE 看到 SQL 的优化处理过程：
结果较多，JSON格式，需要输出到文件。
MySQL [(none)]> SELECT TRACE INTO DUMPFILE "/home/mysql/test01.log" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
Query OK, 1 row affected (0.00 sec)
[mysql@mysql02 ~]$ vi test01.log
"steps": [
"creating_tmp_table": {
"tmp_table_info": {
"row_length": 6015,
"key_length": 0,
"unique_constraint": false,
"location": "disk (InnoDB)",
"record_format": "packed"
"creating_tmp_table": {
"tmp_table_info": {
"row_length": 6015,
"key_length": 0,
"unique_constraint": false,
"location": "disk (InnoDB)",
"record_format": "packed"
......
[mysql@mysql02 ~]$ cat test01.log |wc -l
28732
[mysql@mysql02 ~]$ ll -rth test*
-rwxrw-r-- 1 mysql mysql 175 Feb 5 11:41 test.sh
-rw-rw-rw- 1 mysql mysql 103K Feb 5 13:21 test01.log
-rw-rw-rw- 1 mysql mysql 660K Feb 5 13:53 test02.log
详细执行计划，可以通过在线json编辑器查看
在线json编辑器
MySQL引入了Materialization（物化）这一关键特性用于子查询（比如在IN/NOT IN子查询以及 FROM 子查询）优化。
具体实现方式是：在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。
与此同时，优化器还具有延迟物化子查询的能力，先通过其它条件判断子查询是否真的需要执行。
物化子查询优化SQL执行的关键点在于对子查询只需要执行一次。
与之相对的执行方式是对外表的每一行都对子查询进行调用，其执行计划中的查询类型为“DEPENDENT SUBQUERY”。
MySQL [(none)]> select * from information_schema.tables where table_name='COLUMNS'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: information_schema
TABLE_NAME: COLUMNS
TABLE_TYPE: SYSTEM VIEW
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: NULL
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 8388608
AUTO_INCREMENT: NULL
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: max_rows=2789
TABLE_COMMENT:
1 row in set (0.00 sec)
ERROR: No query specified
检查columns创建语句
MySQL [information_schema]> show create table columns\G;
*************************** 1. row ***************************
Table: COLUMNS
Create Table: CREATE TEMPORARY TABLE `COLUMNS` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
`COLUMN_DEFAULT` longtext,
`IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` longtext NOT NULL,
`COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
`EXTRA` varchar(30) NOT NULL DEFAULT '',
`PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
`COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT '',
`GENERATION_EXPRESSION` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR: No query specified
无法收集统计信息
MySQL [information_schema]> analyze table information_schema.COLUMNS;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
尝试创建相同数据量的t1表
MySQL [cjc]> create table t1 like information_schema.columns;
Query OK, 0 rows affected (0.04 sec)
MySQL [cjc]> insert into t1 select * from information_schema.columns;
Query OK, 5102 rows affected (1.97 sec)
Records: 5102 Duplicates: 0 Warnings: 0
执行相同语句只需要0.03秒
SELECT DISTINCT table_name,table_schema
FROM t1
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select DISTINCT table_name FROM t1
WHERE column_key in ('PRI','UNI'));
......
1005 rows in set (0.03 sec)
查看执行计划
explain SELECT DISTINCT table_name,table_schema
FROM t1
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select DISTINCT table_name FROM t1
WHERE column_key in ('PRI','UNI'));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5005 | 60.00 | Using where; Using temporary |
| 2 | SUBQUERY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5005 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
查看t1表统计信息
MySQL [(none)]> select * from information_schema.tables where table_name='t1'\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: cjc
TABLE_NAME: t1
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 5005
AVG_ROW_LENGTH: 317
DATA_LENGTH: 1589248
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 4194304
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-02-05 11:55:22
UPDATE_TIME: 2022-02-05 11:55:40
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
尝试强制物化
查看参数：
MySQL [cjc]> show variables like '%optimizer_switch%'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: 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,prefer_ordering_index=on
1 row in set (0.00 sec)
ERROR: No query specified
速度没有改变，强制物化没成功？
SELECT DISTINCT table_name,table_schema
FROM information_schema.columns
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select /*+ SUBQUERY(MATERIALIZATION) */ DISTINCT table_name FROM information_schema.columns
WHERE column_key in ('PRI','UNI'));
1005 rows in set (12.23 sec)
explain SELECT DISTINCT table_name,table_schema
FROM information_schema.columns
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
AND table_name NOT IN (select /*+ SUBQUERY(MATERIALIZATION) */ DISTINCT table_name FROM information_schema.columns
WHERE column_key in ('PRI','UNI'));
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
| 1 | PRIMARY | columns | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases; Using temporary |
| 2 | DEPENDENT SUBQUERY | columns | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
---参考：《MySQL大智小技》
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2854704/，如需转载，请注明出处，否则将追究法律责任。
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
