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慢查詢的12個原因MySql
- mysql伺服器查詢慢原因分析與解決方法小結MySql伺服器
- mysqlsla 分析mysql慢查詢日誌MySql
- MySQL 慢查詢MySql
- MySQL慢查詢MySql
- MySQL慢查詢分析工具之mysqldumpslowMySql
- mysql慢查詢日誌分析工具使用MySql
- Mysql慢查詢日誌分析工具mysqlslaMySql
- linux下開啟mysql慢查詢，分析查詢語句LinuxMySql
- 資料庫查詢慢的原因資料庫
- mongodb慢查詢分析MongoDB
- mysql慢查詢和錯誤日誌分析MySql
- Mysql優化_慢查詢開啟說明及Mysql慢查詢分析工具mysqldumpslow用法講解MySql優化
- 關於MySQL 通用查詢日誌和慢查詢日誌分析MySql
- 總結mysql伺服器查詢慢原因與解決方法MySql伺服器
- MySQL 慢查詢優化MySql優化
- MySQL：慢查詢日誌MySql
- Mysql慢查詢操作梳理MySql
- MySQL開啟慢查詢MySql
- mysql慢查詢日誌MySql
- 對 MySQL 慢查詢日誌的簡單分析MySql
- mysql分析慢查詢日誌工具mysqlsla安裝MySql
- 分析mysql慢查詢日誌的好工具--mysqlslaMySql
- mysql 表資料量大量查詢慢如何優化MySql優化
- MySQL 慢查詢那點事MySql
- 詳解MySQL--慢查詢MySql
- MySQL 清空慢查詢檔案MySql
- MySQL 慢查詢分析工具~pt-query-digest 詳解MySql
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- MySQL 慢查詢日誌——讓“慢”無所遁形MySql
- Mysql 慢查詢優化實踐MySql優化
- mysql慢查詢的奇異事件MySql事件
- 【MySQL】慢查詢日誌不列印MySql
- 慢查詢日誌開啟分析
- 慢查詢最佳化及分析
- MySQL優化 - 開啟MySQL慢查詢日誌及分析工具mysqldumpslowMySql優化
- 資料庫系列：MySQL慢查詢分析和效能最佳化資料庫MySql
- MySQL單表查詢MySql