MySQL information_schema.columns表查詢慢原因分析
環境說明：
DB:MySQL 5.7.35 OS:Redhat 7.5
問題：
查詢資料庫內沒有主鍵/唯一鍵的錶速度慢，耗時12秒。
查詢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'));
問題重現：
建立測試庫
create database cjc; use cjc;
建立測試表
vi cjc_test.sh
#!/bin/bash
for i in {1..1000}
do
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)"
done
執行指令碼
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'))
and
(< 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);
其中：非關聯子查詢
select from A where A.x not in (select x from B where ...);
掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時表 C 中， 在掃描A表中的記錄，與臨時表C中的記錄進行比對。
而關聯子查詢就需要迴圈迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...);
掃描A表的每一條記錄rA：
掃描B表，找到其中的第一條滿足rA條件的記錄。
顯然，關聯子查詢的掃描成本會高於非關聯子查詢。
繼續分析執行計劃
檢查SQL執行計劃，分析子查詢結果是否進行了快取(物化，MATERIALIZATION)。
使用 optimizer trace工具，觀察MySQL對SQL的最佳化處理過程。
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”。
為什麼沒有物化，檢查統計資訊，錶行數為NULL。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【mysql】explain命令分析慢查詢MySqlAI
- 盤點MySQL慢查詢的12個原因MySql
- mysql伺服器查詢慢原因分析與解決方法小結MySql伺服器
- MySQL慢查詢分析工具之mysqldumpslowMySql
- MySQL慢查詢MySql
- MySQL 慢查詢MySql
- mysql慢查詢和錯誤日誌分析MySql
- mongodb慢查詢分析MongoDB
- 資料庫查詢慢的原因資料庫
- 關於MySQL 通用查詢日誌和慢查詢日誌分析MySql
- MySQL：慢查詢日誌MySql
- MySQL 慢查詢優化MySql優化
- 對 MySQL 慢查詢日誌的簡單分析MySql
- mysql 表資料量大量查詢慢如何優化MySql優化
- MySQL 慢查詢分析工具~pt-query-digest 詳解MySql
- [Mysql]慢查詢最佳化MySql
- MySQL 慢查詢那點事MySql
- 詳解MySQL--慢查詢MySql
- 遇到慢查詢怎麼辦？一文解讀MySQL 8.0查詢分析工具MySql
- Mysql 慢查詢優化實踐MySql優化
- 慢查詢日誌開啟分析
- 慢查詢分析調優工具～mysqldumpslowMySql
- 資料庫系列：MySQL慢查詢分析和效能最佳化資料庫MySql
- mysql鎖表查詢MySql
- MySQL 單表查詢MySql
- MySQL單表查詢MySql
- 慢查詢
- MySQL Slow Query log(慢查詢日誌)MySql
- mysql查詢效率慢的SQL語句MySql
- mysql慢查詢，死鎖解決方案MySql
- mysql之 slow log 慢查詢日誌MySql
- 慢查詢分析調優工具～show profile
- Mysql慢查詢日誌檔案轉ExcelMySqlExcel
- MySQL慢查詢日誌相關設定MySql
- MySQL索引原理及慢查詢最佳化MySql索引
- 深入mysql慢查詢設定的詳解MySql
- mysql5.7.10開啟慢查詢詳解MySql
- 在mysql查詢效率慢的SQL語句MySql