MySQL · 特性分析 · Statement Digest · 數據庫內核月報

## 背景在對數據庫進行性能調優的時候，除了參數、配置的調整以外，SQL調優也是重要的手段，同時也是收益最大的一環。當DBA對業務庫進行sql調優的時候，如何做到有的放矢，投入產出受益最大？足夠詳細的SQL性能統計無疑是最重要的信息。下面我們先來看下不同數據庫提供的sql性能統計信息: ## Oracle的sql性能統計 Oracle可以通過直接查詢v$表得到，下面的columns列表是我們常用的一些統計: ~~~ select sql_id, sql_text, sql_fulltext, sharable_mem, persistent_mem, runtime_mem, sorts, fetches, executions, parse_calls, disk_reads, direct_writes, buffer_gets, application_wait_time, concurrency_wait_time, cluster_wait_time, user_io_wait_time, rows_processed, cpu_time, elapsed_time from v$sql_area ~~~ 這里邊包括了幾類統計，SQL內存使用的統計、parse的統計、物理/邏輯IO的統計、cpu時間、等待時間等時間統計。 DBA可以根據這些統計信息進行有針對性的調優: 1. CPU調優: 如果當前數據庫性能CPU是瓶頸，可以通過order by cpu_time，查詢出來top CPU的SQL進行調優； 2. IO調優: 可以根據buffer_gets, disk_reads，user_io_wait_time 查詢top IO的SQL進行調優； 3. 鎖爭用: 可以根據concurrency_wait_time，cluster_wait_time查詢top lock的SQL進行調優。 ## MySQL的SQL性能統計 1\. 通過show profiles來查詢統計信息在MySQL 5.6版本之前，還保留著show profiles的方式，后續版本逐步被performance_schema來替換了。使用方法如下: ~~~ mysql> SET profiling=1; mysql> select 1, sleep(1); mysql> show profile cpu, block io for query 1; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000100 | NULL | NULL | NULL | NULL | | checking permissions | 0.000014 | NULL | NULL | NULL | NULL | | Opening tables | 0.000024 | NULL | NULL | NULL | NULL | | init | 0.000020 | NULL | NULL | NULL | NULL | | optimizing | 0.000008 | NULL | NULL | NULL | NULL | | executing | 0.000022 | NULL | NULL | NULL | NULL | | User sleep | 1.000090 | NULL | NULL | NULL | NULL | | end | 0.000021 | NULL | NULL | NULL | NULL | | query end | 0.000009 | NULL | NULL | NULL | NULL | | closing tables | 0.000010 | NULL | NULL | NULL | NULL | | freeing items | 0.000055 | NULL | NULL | NULL | NULL | | logging slow query | 0.000008 | NULL | NULL | NULL | NULL | | cleaning up | 0.000012 | NULL | NULL | NULL | NULL | +----------------------+----------+----------+------------+--------------+---------------+ 13 rows in set (0.00 sec) ~~~ show profile的語法如下： ~~~ SHOW PROFILE [type [, type] … ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS ~~~ 從結果集可以看到每一塊操作的CPU時間，block IO情況。但這種適合拿單個SQL進行分析，使用上的便捷性比較差。 2\. 通過performance_schema的digest統計 MySQL之前的版本不支持綁定變量，導致SQL語句太多，相同業務的SQL匯總統計比較麻煩。從MySQL 5.6開始，在performance_schema中支持了對SQL statement的digest進行統計。 performance_schema.events_statements_summary_by_digest表根據digest進行匯總統計，DBA可以直接訪問這個內存表得到SQL的統計信息。首先，需要打開performance_schema，然后系統就會自動為SQL statement生成digest，并記錄統計信息。例如: ~~~ mysql> select 1, sleep(1); +---+----------+ | 1 | sleep(1) | +---+----------+ | 1 | 0 | +---+----------+ 1 row in set (1.00 sec) mysql> select * from events_statements_summary_by_digest\G; *************************** 1\. row *************************** SCHEMA_NAME: performance_schema DIGEST: bb80cc862a205b471ce0f0ff2605a9a0 DIGEST_TEXT: SELECT ? , `sleep` (?) COUNT_STAR: 1 SUM_TIMER_WAIT: 1000577972000 MIN_TIMER_WAIT: 1000577972000 AVG_TIMER_WAIT: 1000577972000 MAX_TIMER_WAIT: 1000577972000 SUM_LOCK_TIME: 0 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 1 SUM_ROWS_EXAMINED: 0 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2015-11-06 11:15:52 LAST_SEEN: 2015-11-06 11:15:52 2 rows in set (0.00 sec) ~~~ DBA可用通過這個表的統計信息，來有的放矢的進行SQL調優。然而performance_schema中digest的限制: 1. 必須打開performance_schema的所有功能才行，但performance_schema全部打開會對性能產生一些影響； 2. events_statements_summary_by_digest 表默認有200個的最大限制，但從MySQL 5.5開始，可以通過調整performance_schema_digests_size來修改。但如果表滿了的話，新來的digest的統計信息，會被全部匯總到一個digest=NULL的記錄中； 3. 對于每一個SQL statement，performance_schema會生成一個最大1024bytes的digest_text，超過的會被截斷。針對以上的一些限制，MySQL5.7最新GA的版本，進行了一些改進。另外，針對限制2 Percona有一些工具pt-query-digest, 并建立一些digest歷史表，進行分析，有興趣的可以使用嘗試一下。可以參考:?[mysql-query-digest-with-performance-schema](https://www.percona.com/blog/2015/10/13/mysql-query-digest-with-performance-schema/) ## MySQL 5.7 performance_schema digest的改進 1. SQL statement digest生成功能不必和performance_schema綁定，digest的功能的源代碼主要是這兩個文件：PFS_digest.cc和PFS_digest.h。這兩個文件從存儲引擎目錄storage/perfschema/ 移到了server目錄sql/下； 2. 從MySQL 5.7.6開始，digest的最大長度由固定的1024bytes，變成了可變大小，由參數performance_schema_max_sql_text_length在系統啟動的時候初始化。 ## 總結用戶可以針對performance_schema提供的digest的功能，根據需求進行一些開發和擴展，比如定期歷史保存、建立SQL性能基線、或者更進一步如果能修改源碼，可以為digest增加更多的merics等。