{"id":120335,"date":"2026-10-31T13:40:55","date_gmt":"2026-10-31T05:40:55","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/120335.html"},"modified":"2026-10-31T13:40:55","modified_gmt":"2026-10-31T05:40:55","slug":"05_mysql-%e5%b8%b8%e7%94%a8-sql-%e5%a4%a7%e5%85%a8-%e8%af%8a%e6%96%ad%e3%80%81%e7%ae%a1%e7%90%86%e3%80%81%e4%bc%98%e5%8c%96%e8%be%85%e5%8a%a9-sql-%e4%b8%8e%e4%b8%89%e5%a4%a7","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/120335.html","title":{"rendered":"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5"},"content":{"rendered":"

05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5<\/h2>\n

\u7cfb\u5217\u5b9a\u4f4d：\u300aMySQL \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217\u300b\u7b2c 05 \u7bc7，\u4e0e\u300aOracle \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217（VMware \u7248）\u300b\u540c\u6784\u5bf9\u6807\u3002\u9002\u7528\u7248\u672c：MySQL 8.0（5.7 \u5dee\u5f02\u968f\u6587\u6807\u6ce8）\u3002\u5b98\u65b9\u6587\u6863：MySQL 8.0 Reference Manual\u3001information_schema \/ performance_schema \/ sys Schema\u3002<\/p>\n

\n

\u4e00\u3001\u4e09\u5927\u7cfb\u7edf\u5e93\u5168\u666f（\u7c7b\u6bd4 Oracle \u6570\u636e\u5b57\u5178）<\/h3>\n<\/p>\n

\"\u56fe<\/p>\n

\u56fe 1<\/p>\n<\/p>\n

\"\u56fe<\/p>\n

\u56fe 2<\/p>\n<\/p>\n\n\u7cfb\u7edf\u5e93\u4f5c\u7528\u6570\u636e\u6765\u6e90\u7c7b\u6bd4 Oracle<\/tr>\n\n\n\n\n
information_schema<\/td>\n\u5143\u6570\u636e：\u8868\u3001\u5217\u3001\u7d22\u5f15\u3001\u6743\u9650\u3001\u5b57\u7b26\u96c6<\/td>\n\u6570\u636e\u5b57\u5178 + \u5b9e\u65f6\u7edf\u8ba1<\/td>\ndba_\/all_<\/td>\n<\/tr>\n
performance_schema<\/td>\n\u8fd0\u884c\u65f6：\u8bed\u53e5\u3001\u9501\u3001\u4e8b\u52a1\u3001IO\u3001\u7b49\u5f85\u4e8b\u4ef6<\/td>\n\u8fd0\u884c\u65f6\u91c7\u96c6（\u9ed8\u8ba4\u5f00\u542f）<\/td>\nv$session \/ v$sql \/ ASH<\/td>\n<\/tr>\n
sys schema<\/td>\n\u5bf9 performance_schema \u7684\u53cb\u597d\u5c01\u88c5，\u4eba\u7c7b\u53ef\u8bfb<\/td>\n\u5e95\u5c42\u6765\u81ea performance_schema<\/td>\nDBA_HIST \/ \u8bca\u65ad\u89c6\u56fe<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u4f7f\u7528\u5efa\u8bae：- \u67e5\u5bf9\u8c61\/\u5143\u6570\u636e \u2192 information_schema- \u67e5\u6027\u80fd\/\u7b49\u5f85\/\u6162 SQL \u2192 performance_schema \u6216 sys- \u65e5\u5e38\u8fd0\u7ef4\u9996\u9009 sys schema：\u547d\u540d\u76f4\u89c2\u3001\u8f93\u51fa\u53cb\u597d，8.0 \u81ea\u5e26\u3002<\/p>\n

\n

\u4e8c\u3001information_schema \u5e38\u7528\u67e5\u8be2（\u5143\u6570\u636e）<\/h3>\n<\/p>\n

\"\u56fe<\/p>\n

\u56fe 3<\/p>\n<\/p>\n

2.1 \u5e93\u8868\u5927\u5c0f\u7edf\u8ba1<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u5404\u4e1a\u52a1\u5e93\u5927\u5c0f\u6392\u884c（\u6700\u5e38\u7528）
\nSELECT TABLE_SCHEMA AS db_name,
\n ROUND(SUM(DATA_LENGTH)\/1024\/1024\/1024, 2) AS data_gb,
\n ROUND(SUM(INDEX_LENGTH)\/1024\/1024\/1024, 2) AS index_gb,
\n ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)\/1024\/1024\/1024, 2) AS total_gb,
\n COUNT(*) AS table_count
\nFROM information_schema.TABLES
\nWHERE TABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema')
\nGROUP BY TABLE_SCHEMA
\nORDER BY total_gb DESC;<\/p>\n

2.2 \u67e5\u627e\u5927\u8868<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u627e\u51fa\u5168\u5e93\u6700\u5927\u7684 20 \u5f20\u8868
\nSELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS,
\n ROUND((DATA_LENGTH+INDEX_LENGTH)\/1024\/1024\/1024, 2) AS total_gb,
\n ENGINE
\nFROM information_schema.TABLES
\nORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC
\nLIMIT 20;<\/p>\n

2.3 \u67e5\u627e\u65e0\u7d22\u5f15\u8868 \/ \u7f3a\u7d22\u5f15\u8868<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u627e\u51fa\u6709\u6570\u636e\u4f46\u6ca1\u6709\u4efb\u4f55\u7d22\u5f15\u7684\u8868（\u5168\u8868\u626b\u63cf\u98ce\u9669）
\nSELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS
\nFROM information_schema.TABLES t
\nLEFT JOIN information_schema.STATISTICS s
\n ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
\nWHERE t.TABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema')
\n AND s.TABLE_NAME IS NULL
\n AND t.TABLE_TYPE = 'BASE TABLE';<\/p>\n

2.4 \u67e5\u627e\u91cd\u590d\u7d22\u5f15 \/ \u5197\u4f59\u7d22\u5f15<\/h4>\n<\/p>\n

— \u89e3\u91ca：sys schema \u5c01\u88c5\u597d\u7684\u5197\u4f59\u7d22\u5f15\u89c6\u56fe
\nSELECT * FROM sys.schema_redundant_indexes;<\/p>\n

2.5 \u67e5\u770b\u5217\u4fe1\u606f<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u67d0\u8868\u7684\u6240\u6709\u5217\u5b9a\u4e49（\u66ff\u4ee3 DESC）
\nSELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA
\nFROM information_schema.COLUMNS
\nWHERE TABLE_SCHEMA = 'appdb' AND TABLE_NAME = 't_order'
\nORDER BY ORDINAL_POSITION;<\/p>\n

2.6 \u67e5\u770b\u8868\u788e\u7247\u7387<\/h4>\n<\/p>\n

— \u89e3\u91ca：Data_free \u5360\u6bd4\u9ad8\u8bf4\u660e\u788e\u7247\u591a，\u9700 OPTIMIZE TABLE
\nSELECT TABLE_SCHEMA, TABLE_NAME,
\n ROUND(DATA_LENGTH\/1024\/1024, 2) AS data_mb,
\n ROUND(DATA_FREE\/1024\/1024, 2) AS free_mb,
\n ROUND(DATA_FREE\/(DATA_LENGTH+1)*100, 2) AS frag_pct
\nFROM information_schema.TABLES
\nWHERE TABLE_SCHEMA NOT IN ('mysql','sys')
\n AND DATA_FREE > 10*1024*1024
\nORDER BY DATA_FREE DESC;<\/p>\n

\n

\u4e09\u3001performance_schema \u5e38\u7528\u67e5\u8be2（\u6027\u80fd\u8bca\u65ad）<\/h3>\n<\/p>\n

\"\u56fe<\/p>\n

\u56fe 4<\/p>\n<\/p>\n

3.1 \u6162 SQL Top N<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u6309\u603b\u8017\u65f6\u6392\u5e8f\u7684\u6162 SQL \u6c47\u603b（\u66ff\u4ee3\u8bfb slow.log）
\nSELECT DIGEST_TEXT,
\n COUNT_STAR AS exec_count,
\n ROUND(SUM_TIMER_WAIT\/1e12, 2) AS total_sec,
\n ROUND(AVG_TIMER_WAIT\/1e12, 2) AS avg_sec,
\n SUM_ROWS_EXAMINED AS rows_examined,
\n SUM_ROWS_SENT AS rows_sent
\nFROM performance_schema.events_statements_summary_by_digest
\nWHERE DIGEST_TEXT NOT LIKE 'CREATE TEMPORARY%'
\nORDER BY SUM_TIMER_WAIT DESC
\nLIMIT 10\\\\G<\/p>\n

3.2 \u9501\u7b49\u5f85\u5206\u6790<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u5f53\u524d\u6240\u6709\u9501
\nSELECT * FROM performance_schema.data_locks\\\\G<\/p>\n

— \u89e3\u91ca：\u9501\u7b49\u5f85\u94fe（\u8c01\u7b49\u8c01）
\nSELECT r.ENGINE_LOCK_ID AS waiting_lock,
\n b.ENGINE_LOCK_ID AS blocking_lock,
\n r.REQUESTED_ENGINE_LOCK_ID
\nFROM performance_schema.data_lock_waits w
\nJOIN performance_schema.data_locks b
\n ON w.BLOCKING_ENGINE_LOCK_ID = b.ENGINE_LOCK_ID
\nJOIN performance_schema.data_locks r
\n ON w.REQUESTING_ENGINE_LOCK_ID = r.ENGINE_LOCK_ID;<\/p>\n

3.3 \u8868 IO \u7edf\u8ba1<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u54ea\u4e9b\u8868\u8bfb\u5199\u6700\u9891\u7e41（\u5b9a\u4f4d\u70ed\u70b9\u8868）
\nSELECT OBJECT_SCHEMA, OBJECT_NAME,
\n COUNT_READ, COUNT_WRITE,
\n SUM_TIMER_READ\/1e12 AS read_sec,
\n SUM_TIMER_WRITE\/1e12 AS write_sec
\nFROM performance_schema.table_io_waits_summary_by_table
\nWHERE OBJECT_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema')
\nORDER BY SUM_TIMER_WAIT DESC
\nLIMIT 10;<\/p>\n

3.4 \u7d22\u5f15\u4f7f\u7528\u7edf\u8ba1<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u54ea\u4e9b\u7d22\u5f15\u6ca1\u88ab\u7528\u8fc7（\u53ef\u8003\u8651\u5220\u9664）
\nSELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
\nFROM performance_schema.table_io_waits_summary_by_index_usage
\nWHERE INDEX_NAME IS NOT NULL
\n AND COUNT_STAR = 0
\n AND OBJECT_SCHEMA NOT IN ('mysql','sys')
\nORDER BY OBJECT_SCHEMA, OBJECT_NAME;<\/p>\n

\n

\u56db\u3001sys schema \u5e38\u7528\u67e5\u8be2（\u8fd0\u7ef4\u9996\u9009）<\/h3>\n

4.1 \u4f1a\u8bdd\u4e0e\u8fde\u63a5<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u6bd4 SHOW PROCESSLIST \u66f4\u53cb\u597d\u7684\u89c6\u56fe
\nSELECT * FROM sys.processlist
\nWHERE COMMAND != 'Sleep'
\nORDER BY TIMER_WAIT DESC
\nLIMIT 20\\\\G<\/p>\n

— \u89e3\u91ca：\u54ea\u4e2a\u7528\u6237\u8fde\u63a5\u6700\u591a
\nSELECT user, count, active FROM sys.host_summary;<\/p>\n

4.2 \u6162\u67e5\u8be2\u6c47\u603b<\/h4>\n<\/p>\n

— \u89e3\u91ca：sys \u5c01\u88c5\u7684\u6162 SQL Top 10（\u6700\u5e38\u7528）
\nSELECT * FROM sys.statement_summary_by_digest
\nORDER BY SUM_TIMER_WAIT DESC
\nLIMIT 10\\\\G<\/p>\n

— \u89e3\u91ca：\u626b\u63cf\u884c\u6570\u8fdc\u5927\u4e8e\u8fd4\u56de\u884c\u6570\u7684 SQL（\u4f4e\u6548 SQL）
\nSELECT * FROM sys.statement_analysis
\nWHERE rows_examined > rows_sent * 10
\nORDER BY rows_examined DESC
\nLIMIT 10;<\/p>\n

4.3 \u9501\u7b49\u5f85<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u4e00\u952e\u770b\u8c01\u963b\u585e\u8c01（\u751f\u4ea7\u6392\u969c\u5fc5\u5907）
\nSELECT * FROM sys.innodb_lock_waits\\\\G<\/p>\n

4.4 \u5e93\u8868\u5927\u5c0f<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u5404\u5e93\u5927\u5c0f（\u6bd4 information_schema \u66f4\u76f4\u89c2）
\nSELECT * FROM sys.innodb_buffer_stats_by_schema;<\/p>\n

— \u89e3\u91ca：\u54ea\u4e9b\u8868\u5360\u7528 Buffer Pool \u6700\u591a
\nSELECT * FROM sys.innodb_buffer_stats_by_table
\nORDER BY allocated DESC
\nLIMIT 10;<\/p>\n

4.5 \u914d\u7f6e\u53c2\u6570\u68c0\u67e5<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u4e0d\u662f\u9ed8\u8ba4\u503c\u7684\u53c2\u6570（\u627e\u88ab\u6539\u8fc7\u7684\u914d\u7f6e）
\nSELECT * FROM sys.config;<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u81ea\u52a8\u589e\u957f\u5217\u5f53\u524d\u503c
\nSELECT * FROM sys.schema_auto_increment_columns;<\/p>\n

\n

\u4e94\u3001\u65e5\u5e38\u8fd0\u7ef4 SQL \u901f\u67e5<\/h3>\n<\/p>\n

\"\u56fe<\/p>\n

\u56fe 5<\/p>\n<\/p>\n

5.1 \u7528\u6237\u4e0e\u6743\u9650<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u67d0\u7528\u6237\u7684\u6743\u9650
\nSHOW GRANTS FOR 'appuser'@'192.168.10.%';<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u6240\u6709\u7528\u6237
\nSELECT user, host, account_locked, password_last_changed
\nFROM mysql.user;<\/p>\n

5.2 \u4e3b\u4ece\u590d\u5236<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u4e3b\u5e93\u5f53\u524d binlog \u4f4d\u70b9
\nSHOW MASTER STATUS;<\/p>\n

— \u89e3\u91ca：\u4ece\u5e93\u590d\u5236\u72b6\u6001
\nSHOW REPLICA STATUS\\\\G — 8.0；5.7 \u7528 SHOW SLAVE STATUS\\\\G<\/p>\n

— \u89e3\u91ca：\u4ece\u5e93\u5ef6\u8fdf（\u5173\u952e\u6307\u6807）
\nSHOW REPLICA STATUS\\\\G
\n— \u770b：Seconds_Behind_Source<\/p>\n

5.3 binlog \u7ba1\u7406<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u6240\u6709 binlog \u6587\u4ef6
\nSHOW BINARY LOGS;<\/p>\n

— \u89e3\u91ca：\u6e05\u7406\u6307\u5b9a\u65e5\u671f\u4e4b\u524d\u7684 binlog
\nPURGE BINARY LOGS BEFORE '2026-10-01 00:00:00';<\/p>\n

— \u89e3\u91ca：\u67e5\u770b binlog \u4e8b\u4ef6\u5185\u5bb9（\u9700 mysqlbinlog \u547d\u4ee4\u884c）
\n— mysqlbinlog –start-position=154 –stop-position=500 \/data\/mysql\/binlog\/mysql-bin.000001<\/p>\n

5.4 \u6570\u636e\u5e93\u53c2\u6570<\/h4>\n<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u6240\u6709\u53c2\u6570
\nSHOW VARIABLES;<\/p>\n

— \u89e3\u91ca：\u6a21\u7cca\u67e5\u627e\u53c2\u6570
\nSHOW VARIABLES LIKE 'innodb_buffer%';<\/p>\n

— \u89e3\u91ca：\u8fd0\u884c\u65f6\u52a8\u6001\u4fee\u6539\u53c2\u6570（\u65e0\u9700\u91cd\u542f）
\nSET GLOBAL max_connections = 1000;<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u8fd0\u884c\u72b6\u6001\u8ba1\u6570\u5668
\nSHOW GLOBAL STATUS LIKE 'Threads%';<\/p>\n

\n

\u516d\u3001EXPLAIN \u6267\u884c\u8ba1\u5212（\u4f18\u5316\u8f85\u52a9）<\/h3>\n<\/p>\n

\"\u56fe<\/p>\n

\u56fe 6<\/p>\n<\/p>\n

— \u89e3\u91ca：\u67e5\u770b\u6267\u884c\u8ba1\u5212（\u6700\u5e38\u7528\u7684\u4f18\u5316\u5de5\u5177）
\nEXPLAIN SELECT * FROM t_order WHERE user_id = 100;<\/p>\n

— \u89e3\u91ca：8.0 \u589e\u5f3a\u7248 EXPLAIN ANALYZE（\u5b9e\u9645\u6267\u884c\u5e76\u6253\u5370\u6bcf\u884c\u8017\u65f6）
\nEXPLAIN ANALYZE SELECT * FROM t_order WHERE user_id = 100;<\/p>\n\nEXPLAIN \u5217\u8bf4\u660e\u4f18\u5316\u65b9\u5411<\/tr>\n\n\n\n\n\n\n
type<\/td>\n\u8bbf\u95ee\u7c7b\u578b：const > ref > range > index > ALL<\/td>\nALL = \u5168\u8868\u626b\u63cf，\u5fc5\u987b\u4f18\u5316<\/td>\n<\/tr>\n
key<\/td>\n\u5b9e\u9645\u4f7f\u7528\u7684\u7d22\u5f15<\/td>\nNULL = \u6ca1\u8d70\u7d22\u5f15<\/td>\n<\/tr>\n
rows<\/td>\n\u9884\u4f30\u626b\u63cf\u884c\u6570<\/td>\n\u8d8a\u5927\u8d8a\u6162<\/td>\n<\/tr>\n
Extra<\/td>\nUsing where \/ Using filesort \/ Using temporary<\/td>\nfilesort\/temporary \u9700\u4f18\u5316<\/td>\n<\/tr>\n
possible_keys<\/td>\n\u53ef\u80fd\u7528\u5230\u7684\u7d22\u5f15<\/td>\n\u4e3a\u7a7a\u8bf4\u660e\u65e0\u7d22\u5f15\u53ef\u7528<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

— \u89e3\u91ca：\u67e5\u770b\u67d0\u8868\u7684\u7d22\u5f15
\nSHOW INDEX FROM appdb.t_order;<\/p>\n

\n

\u4e03\u3001\u8bca\u65ad SQL \u901f\u67e5\u8868（\u6309\u573a\u666f）<\/h3>\n\n\u573a\u666fSQL<\/tr>\n\n\n\n\n\n\n\n\n\n\n\n
\u5f53\u524d\u8fde\u63a5\u6570<\/td>\nSHOW STATUS LIKE 'Threads_connected';<\/td>\n<\/tr>\n
\u6700\u5927\u8fde\u63a5\u6570<\/td>\nSHOW VARIABLES LIKE 'max_connections';<\/td>\n<\/tr>\n
\u5f53\u524d\u9501\u7b49\u5f85<\/td>\nSELECT * FROM sys.innodb_lock_waits;<\/td>\n<\/tr>\n
\u957f\u4e8b\u52a1<\/td>\nSELECT * FROM information_schema.innodb_trx WHERE …<\/td>\n<\/tr>\n
\u6162 SQL Top 10<\/td>\nSELECT * FROM sys.statement_analysis ORDER BY …<\/td>\n<\/tr>\n
\u5927\u8868\u6392\u884c<\/td>\nSELECT * FROM information_schema.TABLES ORDER BY …<\/td>\n<\/tr>\n
\u5e93\u5927\u5c0f<\/td>\nSELECT TABLE_SCHEMA, SUM(DATA_LENGTH) FROM information_schema.TABLES …<\/td>\n<\/tr>\n
\u590d\u5236\u5ef6\u8fdf<\/td>\nSHOW REPLICA STATUS\\\\G \u770b Seconds_Behind_Source<\/td>\n<\/tr>\n
Buffer Pool \u547d\u4e2d\u7387<\/td>\nSHOW ENGINE INNODB STATUS\\\\G<\/td>\n<\/tr>\n
binlog \u4f4d\u70b9<\/td>\nSHOW MASTER STATUS;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

\u516b\u3001VMware \u865a\u673a\u6ce8\u610f\u4e8b\u9879<\/h3>\n\n\u9879\u5efa\u8bae<\/tr>\n\n\n\n\n\n
performance_schema \u5f00\u9500<\/td>\n\u7ea6 5~10% \u6027\u80fd\u5f00\u9500，8.0 \u9ed8\u8ba4\u5f00\u542f，\u751f\u4ea7\u4fdd\u6301\u5f00\u542f<\/td>\n<\/tr>\n
sys schema \u4f9d\u8d56<\/td>\n\u4f9d\u8d56 performance_schema，\u4e0d\u8981\u5173\u95ed<\/td>\n<\/tr>\n
\u5927\u7ed3\u679c\u96c6\u67e5\u8be2<\/td>\n\u4e0d\u8981\u5728\u751f\u4ea7\u76f4\u63a5\u8dd1\u65e0 LIMIT \u7684\u5927\u67e5\u8be2，VMware \u5185\u5b58\u6709\u9650<\/td>\n<\/tr>\n
EXPLAIN ANALYZE<\/td>\n\u4f1a\u771f\u5b9e\u6267\u884c SQL，\u5927\u8868\u614e\u7528<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

\u4e5d\u3001\u8e29\u5751\u8bb0\u5f55（\u5b9e\u6d4b\u6c47\u603b）<\/h3>\n\n#\u5751\u73b0\u8c61\u89e3\u51b3<\/tr>\n\n\n\n\n\n\n\n\n\n\n\n
1<\/td>\n\u7528 information_schema \u770b\u884c\u6570\u4e0d\u51c6<\/td>\nTABLE_ROWS \u662f\u4f30\u7b97\u503c<\/td>\nInnoDB \u884c\u6570\u662f\u91c7\u6837\u4f30\u7b97，\u7cbe\u786e\u884c\u6570\u7528 COUNT(*)<\/td>\n<\/tr>\n
2<\/td>\nperformance_schema \u6ca1\u5f00<\/td>\n\u9501\/\u6162 SQL \u67e5\u4e0d\u4e86<\/td>\n\u786e\u8ba4 performance_schema=ON（8.0 \u9ed8\u8ba4\u5f00）<\/td>\n<\/tr>\n
3<\/td>\nsys schema \u6ca1\u88c5<\/td>\n\u67e5 sys.processlist \u62a5\u9519<\/td>\n8.0 \u81ea\u5e26，5.7 \u9700\u624b\u52a8\u5b89\u88c5<\/td>\n<\/tr>\n
4<\/td>\nSHOW PROCESSLIST \u53ea\u770b\u5230\u5f53\u524d\u7528\u6237<\/td>\n\u666e\u901a\u7528\u6237\u770b\u4e0d\u5230\u5176\u4ed6\u8fde\u63a5<\/td>\n\u7528 root \u6216 PROCESS \u6743\u9650\u8d26\u53f7\u67e5\u8be2<\/td>\n<\/tr>\n
5<\/td>\nEXPLAIN type=ALL<\/td>\n\u5168\u8868\u626b\u63cf\u6027\u80fd\u5dee<\/td>\n\u52a0\u7d22\u5f15\u6216\u6539\u5199 SQL<\/td>\n<\/tr>\n
6<\/td>\ndata_locks \u770b\u4e0d\u61c2<\/td>\n\u9501\u4fe1\u606f\u592a\u539f\u59cb<\/td>\n\u7528 sys.innodb_lock_waits \u5c01\u88c5\u89c6\u56fe<\/td>\n<\/tr>\n
7<\/td>\n\u957f\u4e8b\u52a1\u5bfc\u81f4 undo \u81a8\u80c0<\/td>\n\u78c1\u76d8\u7a7a\u95f4\u4e0d\u591f<\/td>\n\u53ca\u65f6 KILL \u957f\u4e8b\u52a1，\u5f00\u76d1\u63a7\u544a\u8b66<\/td>\n<\/tr>\n
8<\/td>\n\u7d22\u5f15\u7edf\u8ba1\u4fe1\u606f\u8fc7\u671f<\/td>\n\u4f18\u5316\u5668\u9009\u9519\u7d22\u5f15<\/td>\nANALYZE TABLE \u66f4\u65b0\u7edf\u8ba1\u4fe1\u606f<\/td>\n<\/tr>\n
9<\/td>\nSHOW STATUS \u6570\u636e\u592a\u5927<\/td>\n\u8f93\u51fa\u51e0\u5343\u884c<\/td>\n\u7528 LIKE \u8fc7\u6ee4，\u6216\u67e5 performance_schema<\/td>\n<\/tr>\n
10<\/td>\nVMware \u5feb\u7167\u65f6\u8dd1\u5927\u67e5\u8be2<\/td>\nIO \u5ef6\u8fdf\u98d9\u5347<\/td>\n\u5feb\u7167\u524d\u505c\u4e1a\u52a1\u6216\u4f4e\u5cf0\u64cd\u4f5c<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

\u5341\u3001\u8981\u70b9\u901f\u8bb0<\/h3>\n
  • \u4e09\u5927\u7cfb\u7edf\u5e93\u5206\u5de5：information_schema \u67e5\u5143\u6570\u636e\u3001performance_schema \u67e5\u6027\u80fd\u3001sys schema \u662f\u53cb\u597d\u5c01\u88c5\u3002<\/li>\n
  • \u65e5\u5e38\u8fd0\u7ef4\u9996\u9009 sys schema：sys.processlist\u3001sys.innodb_lock_waits\u3001sys.statement_analysis \u4e09\u4ef6\u5957\u3002<\/li>\n
  • \u6162 SQL \u4f18\u5316\u5165\u53e3：sys.statement_analysis + EXPLAIN + EXPLAIN ANALYZE\u3002<\/li>\n
  • \u9501\u6392\u67e5\u6d41\u7a0b：sys.innodb_lock_waits \u2192 \u627e\u5230 blocking_thread_id \u2192 KILL\u3002<\/li>\n
  • EXPLAIN \u770b type \u548c key：type=ALL \u6216 key=NULL \u662f\u91cd\u70b9\u4f18\u5316\u5bf9\u8c61\u3002<\/li>\n
    \n

    \u5341\u4e00\u3001\u53c2\u8003\u8d44\u6599（\u5b98\u65b9\u6b63\u5f0f\u6587\u6863）<\/h3>\n\n\u4e3b\u9898\u5b98\u65b9\u94fe\u63a5<\/tr>\n\n\n\n\n\n\n\n\n
    MySQL 8.0 Reference Manual<\/td>\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/<\/td>\n<\/tr>\n
    The INFORMATION_SCHEMA Tables<\/td>\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/information-schema.html<\/td>\n<\/tr>\n
    MySQL Performance Schema<\/td>\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/performance-schema.html<\/td>\n<\/tr>\n
    MySQL sys Schema<\/td>\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/sys-schema.html<\/td>\n<\/tr>\n
    EXPLAIN Output Format<\/td>\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/explain-output.html<\/td>\n<\/tr>\n
    EXPLAIN ANALYZE<\/td>\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/explain-analyze.html<\/td>\n<\/tr>\n
    SHOW STATUS<\/td>\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/show-status.html<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \n

    \u672c\u6587\u7ae0\u5c5e\u4e8e\u300aMySQL \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217\u300b\u65e5\u5e38\u8fd0\u7ef4\u7bc7\u3002\u6b22\u8fce\u6536\u85cf\u3001\u4ea4\u6d41\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"

    05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5
    \n\u7cfb\u5217\u5b9a\u4f4d：\u300aMySQL \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217\u300b\u7b2c 05 \u7bc7，\u4e0e\u300aOracle \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217（VMware \u7248）\u300b\u540c\u6784\u5bf9\u6807\u3002\u9002\u7528\u7248\u672c：MySQL 8.0（5.7 \u5dee\u5f02\u968f\u6587\u6807\u6ce8）\u3002\u5b98\u65b9\u6587\u6863：MySQL 8.0 Reference Manual\u3001information_<\/p>\n","protected":false},"author":2,"featured_media":120329,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[114,768,100,188,44],"topic":[],"class_list":["post-120335","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-server","tag-mysql","tag-sql","tag-100","tag-188","tag-44"],"yoast_head":"\n05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wsisp.com\/helps\/120335.html\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"og:description\" content=\"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 \u7cfb\u5217\u5b9a\u4f4d：\u300aMySQL \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217\u300b\u7b2c 05 \u7bc7，\u4e0e\u300aOracle \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217（VMware \u7248）\u300b\u540c\u6784\u5bf9\u6807\u3002\u9002\u7528\u7248\u672c：MySQL 8.0（5.7 \u5dee\u5f02\u968f\u6587\u6807\u6ce8）\u3002\u5b98\u65b9\u6587\u6863：MySQL 8.0 Reference Manual\u3001information_\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wsisp.com\/helps\/120335.html\" \/>\n<meta property=\"og:site_name\" content=\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-31T05:40:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/10\/20261031054052-6ae57f64b194a.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/120335.html\",\"url\":\"https:\/\/www.wsisp.com\/helps\/120335.html\",\"name\":\"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"isPartOf\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\"},\"datePublished\":\"2026-10-31T05:40:55+00:00\",\"dateModified\":\"2026-10-31T05:40:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/120335.html#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.wsisp.com\/helps\/120335.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/120335.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.wsisp.com\/helps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\",\"url\":\"https:\/\/www.wsisp.com\/helps\/\",\"name\":\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"description\":\"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"contentUrl\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"caption\":\"admin\"},\"sameAs\":[\"http:\/\/wp.wsisp.com\"],\"url\":\"https:\/\/www.wsisp.com\/helps\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wsisp.com\/helps\/120335.html","og_locale":"zh_CN","og_type":"article","og_title":"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","og_description":"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 \u7cfb\u5217\u5b9a\u4f4d：\u300aMySQL \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217\u300b\u7b2c 05 \u7bc7，\u4e0e\u300aOracle \u6570\u636e\u5e93\u5168\u6808\u8fd0\u7ef4\u5b9e\u6218\u7cfb\u5217（VMware \u7248）\u300b\u540c\u6784\u5bf9\u6807\u3002\u9002\u7528\u7248\u672c：MySQL 8.0（5.7 \u5dee\u5f02\u968f\u6587\u6807\u6ce8）\u3002\u5b98\u65b9\u6587\u6863：MySQL 8.0 Reference Manual\u3001information_","og_url":"https:\/\/www.wsisp.com\/helps\/120335.html","og_site_name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","article_published_time":"2026-10-31T05:40:55+00:00","og_image":[{"url":"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/10\/20261031054052-6ae57f64b194a.png"}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"admin","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"6 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.wsisp.com\/helps\/120335.html","url":"https:\/\/www.wsisp.com\/helps\/120335.html","name":"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","isPartOf":{"@id":"https:\/\/www.wsisp.com\/helps\/#website"},"datePublished":"2026-10-31T05:40:55+00:00","dateModified":"2026-10-31T05:40:55+00:00","author":{"@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41"},"breadcrumb":{"@id":"https:\/\/www.wsisp.com\/helps\/120335.html#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wsisp.com\/helps\/120335.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.wsisp.com\/helps\/120335.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.wsisp.com\/helps"},{"@type":"ListItem","position":2,"name":"05_MySQL \u5e38\u7528 SQL \u5927\u5168 \u2014\u2014 \u8bca\u65ad\u3001\u7ba1\u7406\u3001\u4f18\u5316\u8f85\u52a9 SQL \u4e0e\u4e09\u5927\u7cfb\u7edf\u5e93\u901f\u67e5"}]},{"@type":"WebSite","@id":"https:\/\/www.wsisp.com\/helps\/#website","url":"https:\/\/www.wsisp.com\/helps\/","name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","description":"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41","name":"admin","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/","url":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","contentUrl":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","caption":"admin"},"sameAs":["http:\/\/wp.wsisp.com"],"url":"https:\/\/www.wsisp.com\/helps\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/120335","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/comments?post=120335"}],"version-history":[{"count":0,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/120335\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media\/120329"}],"wp:attachment":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media?parent=120335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/categories?post=120335"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/tags?post=120335"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/topic?post=120335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}