mysql慢查詢

標籤：

mysql 開啟慢查詢日誌 MySQL加速查詢速度的獨門武器：查詢快取對mysql查詢快取從五個角度進行詳細的分析：Query Cache的工作原理、如何配置、如何維護、如何判斷查詢快取的效能、適合的業務情境分析。n  工作原理查詢快取的工作原理，基本上可以用二句話概括：l  緩衝SELECT操作或預先處理查詢（注釋：5.1.17開始支援）的結果集和SQL語句；l  新的SELECT語句或預先處理查詢語句，先去查詢快取，判斷是否存在可用的記錄集，判斷標準：與緩衝的SQL語句，是否完全一樣，區分大小寫；查詢快取對什麼樣的查詢語句，無法緩衝其記錄集，大致有以下幾類：l  查詢語句中加了SQL_NO_CACHE參數；l  查詢語句中含有獲得值的函數，包涵自訂函數，如：CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等；l  對系統資料庫的查詢：mysql、information_schemal  查詢語句中使用SESSION層級變數或預存程序中的局部變數；l  查詢語句中使用了LOCK  IN SHARE MODE、FOR UPDATE的語句l  查詢語句中類似SELECT …INTO 匯出資料的語句；l  交易隔離等級為：Serializable情況下，所有查詢語句都不能緩衝；l  對暫存資料表的查詢操作；l  存在警告資訊的查詢語句；l  不涉及任何錶或視圖的查詢語句；l  某使用者只有列層級許可權的查詢語句；查詢快取的優缺點：l  不需要對SQL語句做任何解析和執行，當然文法解析必須通過在先，直接從Query  Cache中獲得查詢結果；l  查詢快取的判斷規則，不夠智能，也即提高了查詢快取的使用門檻，降低其效率；l  Query Cache的起用，會增加檢查和清理Query Cache中記錄集的開銷，而且存在SQL語句緩衝的表，每一張表都只有一個對應的全域鎖；n  配置        是否啟用mysql查詢快取，可以通過2個參數：query_cache_type和query_cache_size，其中任何一個參數設定為0都意味著關閉查詢快取功能，但是正確的設定推薦query_cache_type=0。l  query_cache_type範圍為：0 -– 不啟用查詢快取；範圍為：1 -– 啟用查詢快取，只要符合查詢快取的要求，用戶端的查詢語句和記錄集鬥可以緩衝起來，共其他用戶端使用；範圍為：2 -– 啟用查詢快取，只要查詢語句中添加了參數：SQL_CACHE，且符合查詢快取的要求，用戶端的查詢語句和記錄集，則可以緩衝起來，共其他用戶端使用；l  query_cache_size允許設定query_cache_size的值最小為40K，對於最大值則可以幾乎認為無限制，實際生產環境的應用經驗告訴我們，該值並不是越大，查詢快取的命中率就越高，也不是對伺服器負載下降貢獻大，反而可能抵消其帶來的好處，甚至增加伺服器的負載，至於該如何設定，下面的章節講述，推薦設定為：64M；l  query_cache_limit限制查詢快取區最大能緩衝的查詢記錄集，可以避免一個大的查詢記錄集佔去大量的記憶體地區，而且往往小查詢記錄集是最有效緩衝記錄集，預設設定為1M，建議修改為16k~1024k之間的範圍，不過最重要的是根據自己應用的實際情況進行分析、預估來設定；l  query_cache_min_res_unit設定查詢快取分配記憶體的最小單位，要適當地設定此參數，可以做到為減少記憶體塊的申請和分配次數，但是設定過大可能導致記憶體片段數值上升。預設值為4K，建議設定為1k~16Kl  query_cache_wlock_invalidate該參數主要涉及MyISAM引擎，若一個用戶端對某表加了寫鎖，其他用戶端發起的查詢請求，且查詢語句有對應的查詢快取記錄，是否允許直接讀取查詢快取的記錄集資訊，還是等待寫鎖的釋放。預設設定為0，也即允許；n  維護l  查詢快取區的磁碟重組    查詢快取使用一段時間之後，一般都會出現記憶體片段，為此需要監控相關狀態值，並且定期進行記憶體片段的整理，磁碟重組的動作陳述式：FLUSH QUERY CACHE；l  清空查詢快取的資料那些操作操作可能觸發查詢快取，把所有緩衝的資訊清空，以避免觸發或需要的時候，知道如何做，二類可觸發查詢快取資料全部清空的命令：(1).     RESET QUERY CACHE；(2).     FLUSH TABLES；n  效能監控l  片段率查詢快取記憶體片段率=Qcache_free_blocks / Qcache_total_blocks * 100%l  命中率查詢快取命中率=Qcache_hits/(Qcache_hits + Qcache_inserts)  * 100%l  記憶體使用量率查詢快取記憶體使用量率=(query_cache_size – Qcache_free_memory) / query_cache_size * 100%l  Qcache_lowmem_prunes該參數值對於檢測查詢快取區的記憶體大小設定是否，有非常關鍵性的作用，其代表的意義為：查詢快取去因記憶體不足而不得不從查詢快取區刪除的查詢快取資訊，刪除演算法為LRU；l  query_cache_min_res_unit    記憶體塊分配的最小單元非常重要，設定過大可能增加記憶體片段的機率發生，太小又可能增加記憶體配置的消耗，為此在系統平穩運行一個階段性後，可參考公式的計算值：查詢快取最小記憶體塊 = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cachel  query_cache_size我們如何判斷query_cache_size是否設定過小，依然也只有先預設定一個值，推薦為：32M~128M之間的地區，待系統平穩運行一個時間段（至少1周），並且觀察這周內的相關狀態值：(1).     Qcache_lowmem_prunes；(2).     命中率；(3).     記憶體使用量率；    若整個平穩運行期監控獲得的資訊，為命中率高於80%，記憶體使用量率超過80%，並且Qcache_lowmem_prunes的值不停地增加，而且增加的數值還較大，則說明我們為查詢緩衝區分配的記憶體過小，可以適當地增加查詢快取區的記憶體大小；    若是整個平穩運行期監控獲得的資訊，為命中率低於40%，Qcache_lowmem_prunes的值也保持一個平穩狀態，則說明我們的查詢緩衝區的記憶體設定過大，或者說業務情境重複執行一樣查詢語句的機率低，同時若還監測到一定量的freeing items，那麼必須考慮把查詢快取的記憶體條小，甚至關閉查詢快取功能；n  業務情境通過上述的知識梳理和分析，我們至少知道查詢快取的以下幾點：l  查詢快取能夠加速已經存在緩衝的查詢語句的速度，可以不用重新解析和執行而獲得正確得記錄集；l  查詢快取中涉及的表，每一個表對象都有一個屬於自己的全域性質的鎖；l  表若是做DDL、FLUSH TABLES 等類似操作，觸發相關表的查詢快取資訊清空；l  表對象的DML操作，必須優先判斷是否需要清理相關查詢快取的記錄資訊，將不可避免地出現鎖等待事件；l  查詢快取的記憶體配置問題，不可避免地產生一些記憶體片段；l  查詢快取對是否是一樣的查詢語句，要求非常苛刻，而且還不智能；    我們再重新回到本節的重點上，查詢快取適合什麼樣的業務情境呢？只要是清楚了查詢快取的上述優缺點，就不難羅列出來，業務情境要求：l  整個系統以讀為主的業務，比如門戶型、新聞類、報表型、論壇等網站；l  查詢語句操作的表對象，非頻繁地進行DML操作，可以使用query_cache_type=2模式，然後SQL語句加SQL_CACHE參數指定；/*    SHOW VARIABLES LIKE ‘%SLOW%‘;    SET GLOBAL LOG_SLOW_QUERIES=ON    SET SLOW_QUERY_LOG=ON    SET long_query_TIME=1  --單位是秒*/最佳化MySQL最重要的一部分工作是先確定”有問題”的查詢語句。只有先找出這些查詢較慢的sql查詢(執行時間較長)，我們才能進一步分析原因並且最佳化它。MySQL為我們提供了Slow Query Log記錄功能，它能記錄執行時間超過了特定時間長度的查詢。分析Slow Query Log有助於幫我們找到”問題”查詢。記錄slow queries 首先，我們需要查看mysql server版本號碼，以及是否配置啟用了slow query log。 #開啟服務 log_slow_queries = ON 當log_slow_queries是ON時，才表示已經啟用了記錄slow query功能。預設是不記錄slow query的。 啟用slow query日誌 #//將下列配置放到my.cnf中 [mysqld] log-slow-queries = /var/lib/mysql/slow-queries.log //新增加的參數 long_query_time = 3 log-queries-not-using-indexes log-slow-admin-statements 上面的配置開啟了slow query日誌,將會捕獲了執行時間超過了3秒的查詢，包括執行速度較慢的管理命令(比如OPTIMEZE TABLE),並且記錄了沒有使用索引的查詢。這些SQL，都會被記錄到log-slow-queries指定的檔案/var/lib/mysql/slow-queries.log檔案中。 log-slow-queries <slow_query_log_file> 存放slow query日誌的檔案。你必須保證mysql server進程mysqld_safe進程使用者對該檔案有w許可權。 long_query_time 如果query time超過了該值，則認為是較慢查詢，並被記錄下來。單位是秒，最小值是1,預設值是10秒。10秒對於大多數應用來講，太長了。我們推薦從3秒開始， 依次減少，每次都找出最”昂貴”的10條SQL語句並且最佳化他們。日複一日，一步一步最佳化。一次性找出很多條SQL語句，對於最佳化來講，意義並不大。 log-queries-not-using-indexes MySQL會將沒有使用索引的查詢記錄到slow query日誌中。無論它執行有多快，查詢語句沒有使用索引，都會被記錄。有的時候，有些沒有使用引索的查詢非常快(例如掃描很小的表)，但也有可能導致伺服器變慢，甚至還會使用大量的磁碟空間。 log-slow-admin-statements 一些管理指令，也會被記錄。比如OPTIMEZE TABLE, ALTER TABLE等等。 記錄檔 MySQL5.1中，提供了全域變數slow_query_log、slow_query_log_file可以靈活地控制enable/disable慢查詢。同時可以通過long_query_time設定時間 #//停用slow query記錄 #注意:設定了slow_query_log全域變數, log_slow_queries也會隱性地跟著改變 mysql>set global slow_query_log=OFF 我們可以通過tail -f查看記錄檔。 $tail -f /var/lib/mysql/slow-queries.log # Time: 110107 16:22:11 # User@Host: root[root] @ localhost [] # Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774 SET timestamp=1294388531; select count(*) from ep_friends; 第一行,SQL查詢執行的時間 第二行,執行SQL查詢的串連資訊 第三行記錄了一些我們比較有用的資訊 Query_time SQL執行的時間,越長則越慢 Lock_time 在MySQL伺服器階段(不是在儲存引擎階段)等待表鎖時間 Rows_sent 查詢返回的行數 Rows_examined 查詢檢查的行數 Slow Query日誌，雖然協助你記錄了那些執行過了的SQL語句。但它不是萬能的，意義可能沒有你想象的那麼大。它只告訴了你哪些語句慢，但是為什麼慢?具體 原因，還是需要你自己去分析，不斷的調試。也許，你只需要換一條更有效sql語句，也許你只需簡單地增加一個索引，但也有可能你需要調整你應用程式的設 計方案。比如，上面那條語句是很明顯，它檢查了600多萬行資料。不幸的是，並不是每條語句都這麼明顯。也許還有別的原因，比如: *鎖表了，導致查詢處於等態狀態。lock_time顯示了查詢等待鎖被翻譯的時間 *資料或索引沒有被緩衝。常見於第一次啟動伺服器或者伺服器沒有調優 *備份資料庫，I/O變慢 *也許同時運行了其它的查詢，減少了當前查詢MySQL5.1可以在運行時改變日記行為，將日誌記錄到資料庫表中。只要將mysql全域變數log_output設定為 TABLE即可。MySQL會將日誌分別記錄到表mysql.gengera_log和mysql.slow_log二張表中。但是，我們推薦將日誌記錄 在日記檔案中。 mysql> show variables like ‘log_output’\G Variable_name: log_output Value: FILE mysql>set global log_output=’table’; 缺陷與審記 雖然記錄了slow query能夠協助你最佳化產品。但是MySQL目前版本，還有幾大蹩足的地方。 1.MySQL5.0版本, long_query_time時間粒紋不夠細,最小值為1秒。對於高並發效能的網頁指令碼而言，1秒出現的意義不大。即出現1秒的查詢比較少。直到mysql5.1.21才提供更細粒度的long_query_time設定. 2.不能將伺服器執行的所有查詢記錄到慢速日誌中。雖然MySQL普通日誌記錄了所有查詢，但是它們是解析查詢之前就記錄下來了。這意味著普通日誌沒辦法包含諸如執行時間，鎖表時間，檢查行數等資訊。 3.如果開啟了log_queries_not_using_indexes選項，slow query日誌會充滿過多的垃圾日誌記錄，這些快且高效的全表掃描查詢(表小)會衝掉真正有用的slow queries記錄。比如select * from category這樣的查詢也會被記錄下來。 通過microslow-patch補丁可使用更細的時間粒紋，和記錄所有執行過的sql語句。不過，使用這個補訂不得不自己編譯MySQL，出於穩定性考濾，我們推薦在開發測試環境，可以打上這個補丁，享受這個補丁帶來的便利。在運營環境盡量不要這麼做… MySQL內建了mysqldumpslow工具用來分析slow query日誌，除此之外，還有一些好用的開源工具。比如MyProfi、mysql-log-filter，當然還有mysqlsla

 

mysql慢查詢