資料庫核心月報 － 2015 / 11-MySQL · 特性分析 · Statement Digest

在對資料庫進行性能調優的時候，除了參數、配置的調整以外，sql調優也是重要的手段，同時也是收益最大的一環。

當dba對業務庫進行sql調優的時候，如何做到有的放矢，投入産出受益最大？足夠詳細的sql性能統計無疑是最重要的資訊。

下面我們先來看下不同資料庫提供的sql性能統計資訊:

oracle可以通過直接查詢v$表得到，下面的columns清單是我們常用的一些統計:

這裡邊包括了幾類統計，sql記憶體使用的統計、parse的統計、實體/邏輯io的統計、cpu時間、等待時間等時間統計。

dba可以根據這些統計資訊進行有針對性的調優:

cpu調優: 如果目前資料庫性能cpu是瓶頸，可以通過order by cpu_time，查詢出來top cpu的sql進行調優；

io調優: 可以根據buffer_gets, disk_reads，user_io_wait_time 查詢top io的sql進行調優；

鎖争用: 可以根據concurrency_wait_time，cluster_wait_time查詢top lock的sql進行調優。

1. 通過show profiles來查詢統計資訊

在mysql 5.6版本之前，還保留着show profiles的方式，後續版本逐漸被performance_schema來替換了。

使用方法如下:

show profile的文法如下：

從結果集可以看到每一塊操作的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，并記錄統計資訊。

例如:

dba可用通過這個表的統計資訊，來有的放矢的進行sql調優。

然而performance_schema中digest的限制:

必須打開performance_schema的所有功能才行，但performance_schema全部打開會對性能産生一些影響；

events_statements_summary_by_digest 表預設有200個的最大限制，但從mysql 5.5開始，可以通過調整performance_schema_digests_size來修改。但如果表滿了的話，新來的digest的統計資訊，會被全部彙總到一個digest=null的記錄中；

對于每一個sql statement，performance_schema會生成一個最大1024bytes的digest_text，超過的會被截斷。

針對以上的一些限制，mysql5.7最新ga的版本，進行了一些改進。

sql statement digest生成功能不必和performance_schema綁定，digest的功能的源代碼主要是這兩個檔案：pfs_digest.cc和pfs_digest.h。這兩個檔案從存儲引擎目錄storage/perfschema/ 移到了server目錄sql/下；

從mysql 5.7.6開始，digest的最大長度由固定的1024bytes，變成了可變大小，由參數performance_schema_max_sql_text_length在系統啟動的時候初始化。

使用者可以針對performance_schema提供的digest的功能，根據需求進行一些開發和擴充，比如定期曆史儲存、建立sql性能基線、或者更進一步如果能修改源碼，可以為digest增加更多的merics等。