檢視mysql執行狀態的一些sql
宣告：本文內容是筆者閱讀《千金良方 MySQL效能最佳化金字塔法則》後整理的筆記，只整理了筆者感興趣的內容，其他內容詳見該書。
檢視最近的top sql
透過performance_schema.events_statements_history表，檢視資料庫最近執行的一些sql語句：
(root@localhost)[(none)]> SELECT thread_id ,event_name ,source ,sys.format_time(timer_wait) ,sys.format_time(lock_time) ,sql_text ,current_schema ,message_text ,rows_affected ,rows_sent ,rows_examined FROM performance_schema.events_statements_history WHERE current_schema != 'performance_schema' ORDER BY timer_wait DESC limit 10 \G *************************** 1. row *************************** thread_id: 561166 event_name: statement/sql/select source: socket_connection.cc:101 sys.format_time(timer_wait): 53.64 ms sys.format_time(lock_time): 429.00 us sql_text: select * from oa_v_position_list current_schema: oa_2016 message_text: NULL rows_affected: 0 rows_sent: 4 rows_examined: 18051 *************************** 2. row *************************** thread_id: 153896 event_name: statement/sql/select source: socket_connection.cc:101 sys.format_time(timer_wait): 51.76 ms sys.format_time(lock_time): 96.00 us sql_text: select count(id) as num from formmain_2477 where (field0003 = 'SJCL-201911008' and ifnull(field0003, '0') != '0') current_schema: oa_2016 message_text: NULL rows_affected: 0 rows_sent: 1 rows_examined: 222 *************************** 3. row *************************** ....
我們可以使用performance_schema.events_statements_summary_by_digest表查詢經過統計之後的top sql語句：
(root@localhost)[(none)]> SELECT schema_name ,digest_text ,count_star ,sys.format_time(sum_timer_wait) AS sum_time ,sys.format_time(min_timer_wait) AS min_time ,sys.format_time(avg_timer_wait) AS avg_time ,sys.format_time(max_timer_wait) AS min_time ,sys.format_time(sum_lock_time) AS sum_lock_time ,sum_rows_affected ,sum_rows_sent ,sum_rows_examined FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL ORDER BY count_star DESC limit 10 \G *************************** 1. row *************************** schema_name: oa_2016 digest_text: SET `autocommit` = ? count_star: 1604399319 sum_time: 13.57 h min_time: 2.00 ns avg_time: 30.46 us min_time: 39.87 s sum_lock_time: 0 ps sum_rows_affected: 0 sum_rows_sent: 0 sum_rows_examined: 0 *************************** 2. row *************************** schema_name: oa_2016 digest_text: COMMIT count_star: 368723348 sum_time: 4.15 h min_time: 10.07 us avg_time: 40.52 us min_time: 21.54 s sum_lock_time: 40.27 s sum_rows_affected: 0 sum_rows_sent: 0 sum_rows_examined: 0 *************************** 3. row *************************** schema_name: oa_2016 digest_text: SELECT * FROM `jk_JOB_DETAILS` WHERE `SCHED_NAME` = ? AND `JOB_NAME` = ? AND `JOB_GROUP` = ? count_star: 361183117 sum_time: 19.88 h min_time: 5.00 ns avg_time: 198.10 us min_time: 4.97 s sum_lock_time: 4.93 h sum_rows_affected: 0 sum_rows_sent: 361182289 sum_rows_examined: 361182289 ......
提示：performance_schema.events_statements_summary_by_digest記錄的sql並不完整，預設情況下只擷取了1024位元組，所以該表提供的資料只能算作慢日誌分析的一個補充。如果需要完整sql文字，還得依賴慢日誌分析。
檢視最近失敗的SQL
mysql> SELECT thread_id ,event_name ,source ,sys.format_time(timer_wait) ,sys.format_time(lock_time) ,sql_text ,current_schema ,message_text ,rows_affected ,rows_sent ,rows_examined FROM performance_schema.events_statements_history WHERE errors>0 \G *************************** 1. row *************************** thread_id: 6172541 event_name: statement/sql/select source: socket_connection.cc:101 sys.format_time(timer_wait): 135.89 us sys.format_time(lock_time): 0 ps sql_text: SELECT id,waybill_num,oms_order_status FROM store_order WHERE is_sync_css_status_end IS NULL OR is_sync_css_status_end!='01' ORDER BY create_time ASC, css_search_time ASC LIMIT 0,200 current_schema: oms message_text: Table 'oms.store_order' doesn't exist rows_affected: 0 rows_sent: 0 rows_examined: 0 1 row in set (0.02 sec)
檢視是MDL鎖在等什麼
# 檢視MDL鎖等待事件的instrments（採集器）是否開啟 mysql> SELECT * FROM performance_schema.setup_instruments WHERE name LIKE '%metadata/sql/mdl%'; +----------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------+---------+-------+ | wait/lock/metadata/sql/mdl | NO | NO | +----------------------------+---------+-------+ 1 row in set (0.00 sec) # 啟用與MDL鎖等待事件相關的instruments（採集器） mysql> update performance_schema.setup_instruments set ENABLED='YES' where name like '%metadata/sql/mdl%'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update performance_schema.setup_instruments set TIMED='YES' where name like '%metadata/sql/mdl%'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from performance_schema.setup_instruments where name like '%metadata/sql/mdl%'; +----------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------+---------+-------+ | wait/lock/metadata/sql/mdl | YES | YES | +----------------------------+---------+-------+ 1 row in set (0.01 sec) # 然後使用sys.schema_table_lock_wait檢視進行查詢（注意：請自行模擬一個會話事務不提交， 另外一個會話發生DDL的操作，就可以檢視到MDL鎖等待的內容)。 mysql> select * from sys.schema_table_lock_waits \G
檢視innodb_buffer_pool中熱點資料有哪些
mysql> select * from sys.innodb_buffer_stats_by_table order by allocated desc limit 10 ; +---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+ | mysql | help_keyword | 96.00 KiB | 43.99 KiB | 6 | 6 | 6 | 283 | | InnoDB System | SYS_COLUMNS | 80.00 KiB | 44.50 KiB | 5 | 5 | 5 | 696 | | mdm | employee_jz | 64.00 KiB | 19.77 KiB | 4 | 4 | 4 | 177 | | mysql | innodb_index_stats | 64.00 KiB | 28.20 KiB | 4 | 4 | 4 | 288 |
檢視資料庫中是否有表使用了外來鍵
mysql> SELECT * FROM information_schema.key_column_usage WHERE constraint_schema = 'oms' AND referenced_table_schema IS NOT NULL \G; *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: oms CONSTRAINT_NAME: qrtz_blob_triggers_ibfk_1 TABLE_CATALOG: def TABLE_SCHEMA: oms TABLE_NAME: qrtz_blob_triggers COLUMN_NAME: SCHED_NAME ORDINAL_POSITION: 1 POSITION_IN_UNIQUE_CONSTRAINT: 1 REFERENCED_TABLE_SCHEMA: oms REFERENCED_TABLE_NAME: qrtz_triggers REFERENCED_COLUMN_NAME: SCHED_NAME *************************** 2. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: oms
通常在開發規範中禁止使用外來鍵。
檢視每張表都有哪些索引
mysql> SELECT TABLE_SCHEMA ,TABLE_NAME ,INDEX_NAME ,COLUMN_NAME ,CARDINALITY FROM information_schema.STATISTICS GROUP BY TABLE_SCHEMA ,TABLE_NAME limit 100; +--------------+---------------------------+-------------------------+------------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +--------------+---------------------------+-------------------------+------------------+-------------+ | mdm | department | index_unique_department | deptid | 381 | | mdm | employee | PRIMARY | sn | 1544 | | mdm | employee_jz | index_unique_employee | sn | 1626 | .......
檢視資料庫是否有分割槽表
mysql> select * from information_schema.partitions where partition_name is not null; Empty set (0.02 sec)
mysql系統庫之統計資訊表
# 將表和索引的統計資訊資料儲存到磁碟中，預設是開啟的 (root@localhost)[mysql]> show variables like 'innodb_stats_persistent'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_stats_persistent | ON | +-------------------------+-------+ 1 row in set (0.00 sec) # 持久化資訊被儲存在mysql資料庫的如下兩張表中： (root@localhost)[mysql]> show tables from mysql like '%stats%'; +---------------------------+ | Tables_in_mysql (%stats%) | +---------------------------+ | innodb_index_stats | | innodb_table_stats | +---------------------------+ 2 rows in set (0.00 sec) # innodb_stats_auto_recalc變數控制是否啟用統計資訊的自動重新計算功能，預設是開啟的。如果啟用，當表中的資料量超過10%時會 觸發統計資訊自動重新計算功能 (root@localhost)[mysql]> show variables like 'innodb_stats_auto_recalc' -> ; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_auto_recalc | ON | +--------------------------+-------+ 1 row in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28916011/viewspace-2663649/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 檢視BW執行狀態
- 使用 telescope 檢視 schedule 執行狀態
- Linux如何檢視系統和程式的執行狀態?Linux
- 檢視spark程式執行狀態以及安裝sparkSpark
- 使用jstack檢視當前程序全部執行緒的狀態JS執行緒
- 檢視使用 MySQL Shell 的連線狀態MySql
- mysql檢視主從同步狀態的方法MySql主從同步
- 如何檢視SQL的執行計劃SQL
- Jtti：linux怎麼檢視oracle資料庫的執行狀態JttiLinuxOracle資料庫
- MySQL執行緒狀態詳解MySql執行緒
- MySQL MHA 執行狀態監控MySql
- GitLab 的元件狀態檢視Gitlab元件
- 請求更改狀態介面後，執行 sql 查出來狀態不對SQL
- 結合作業系統執行緒 檢視mysql中的sql資源 消耗作業系統執行緒MySql
- mysql 鎖狀態的一些狀態資訊記錄MySql
- MySQL 變數及效能狀態檢視知識技巧MySql變數
- MYSQL sql執行過程的一些跟蹤分析(一)MySql
- 執行緒狀態執行緒
- Java執行緒的狀態Java執行緒
- 檢視SQL執行計劃的幾種常用方法YQSQL
- Oracle檢視sql_id 的歷史執行計劃OracleSQL
- 檢視一個正在執行的sql的執行計劃(explain for connection processlist_id)SQLAI
- JAR衝突問題的解決以及執行狀態下如何檢視載入的類JAR
- 監控 SQL Server 的執行狀況SQLServer
- ThreadPollExcutor執行緒池的狀態thread執行緒
- Kylin系統檢視firewalld狀態
- firewalld:檢視版本/幫助/狀態
- 3.4.4 檢視例項的靜默狀態
- 通過Python檢視Azure VM的狀態Python
- Java多執行緒-執行緒狀態Java執行緒
- mysql的sql語句執行流程MySql
- 達夢資料庫SQL執行計劃檢視方法資料庫SQL
- 【Java】執行緒的 6 種狀態Java執行緒
- Java 執行緒的5種狀態Java執行緒
- java執行緒的狀態+鎖分析Java執行緒
- 執行緒狀態和鎖執行緒
- java執行緒的五大狀態，阻塞狀態詳解Java執行緒
- 系統狀態統計和檢視