mysql性能優(yōu)化-慢查詢分析、優(yōu)化索引和配置

《mysql性能優(yōu)化-慢查詢分析、優(yōu)化索引和配置》由會員上傳分享，免費在線閱讀，更多相關內(nèi)容在工程資料-天天文庫。

1、mysql性能優(yōu)化-慢查詢分析、優(yōu)化索引和配置目錄一、優(yōu)化概述二、查詢與索引優(yōu)化分析1性能瓶頸定位Show命令慢查詢?nèi)罩緀xplain分析查詢profiling分析查詢?2索引及查詢優(yōu)化三、配置優(yōu)化1)???max_connections2)???back_log3)???interactive_timeout4)???key_buffer_size5)???query_cache_size6)???record_buffer_size7)???read_rnd_buffer_size8)???sort_buffer_size9)???join_buf

2、fer_size10)??table_cache11)??max_heap_table_size12)??tmp_table_size13)??thread_cache_size14)??thread_concurrency15)??wait_timeout?一、?優(yōu)化概述MySQL數(shù)據(jù)庫是常見的兩個瓶頸是CPU和I/O的瓶頸，CPU在飽和的時候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時候。磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠大于內(nèi)存容量的時候，如果應用分布在網(wǎng)絡上，那么查詢量相當大的時候那么平瓶頸就會出現(xiàn)在網(wǎng)絡上，我們可以用mpstat,iostat,sa

3、r和vmstat來查看系統(tǒng)的性能狀態(tài)。除了服務器硬件的性能瓶頸，對于MySQL系統(tǒng)本身，我們可以使用工具來優(yōu)化數(shù)據(jù)庫的性能，通常有三種：使用索引，使用EXPLAIN分析查詢以及調(diào)整MySQL的內(nèi)部配置。二、查詢與索引優(yōu)化分析在優(yōu)化MySQL時，通常需要對數(shù)據(jù)庫進行分析，常見的分析手段有慢查詢?nèi)罩?，EXPLAIN?分析查詢，profiling分析以及show命令查詢系統(tǒng)狀態(tài)及系統(tǒng)變量，通過定位分析性能的瓶頸，才能更好的優(yōu)化數(shù)據(jù)庫系統(tǒng)的性能。1性能瓶頸定位Show命令我們可以通過show命令查看MySQL狀態(tài)及變量，找到系統(tǒng)的瓶頸：Mysql>showst

4、atus——顯示狀態(tài)信息（擴展showstatuslike‘XXX’）Mysql>showvariables——顯示系統(tǒng)變量（擴展showvariableslike‘XXX’）Mysql>showinnodbstatus——顯示InnoDB存儲引擎的狀態(tài)Mysql>showprocesslist——查看當前SQL執(zhí)行，包括執(zhí)行狀態(tài)、是否鎖表等Shell>mysqladminvariables-uusername-ppassword——顯示系統(tǒng)變量Shell>mysqladminextended-status-uusername-ppassword——顯

5、示狀態(tài)信息查看狀態(tài)變量及幫助：Shell>mysqld–verbose–help[

6、more#逐行顯示]?比較全的Show命令的使用可參考：http://blog.phpbean.com/a.cn/18/慢查詢?nèi)罩韭樵內(nèi)罩鹃_啟：在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個配置參數(shù)log-slow-queries=/data/mysqldata/slow-query.log???????????long_query_time=2?????????????????????????????????????????????????

7、???????????????注：log-slow-queries參數(shù)為慢查詢?nèi)罩敬娣诺奈恢茫话氵@個目錄要有mysql的運行帳號的可寫權(quán)限，一般都將這個目錄設置為mysql的數(shù)據(jù)存放目錄；long_query_time=2中的2表示查詢超過兩秒才記錄；在my.cnf或者my.ini中添加log-queries-not-using-indexes參數(shù)，表示記錄下沒有使用索引的查詢。log-slow-queries=/data/mysqldata/slow-query.log???????????long_query_time=10???????????

8、????????????????????????????????????????????????????log-queries-not-using-indexes?????????????????????????????????????????????慢查詢?nèi)罩鹃_啟方法二：我們可以通過命令行設置變量來即時啟動慢日志查詢。由下圖可知慢日志沒有打開，slow_launch_time=#?表示如果建立線程花費了比這個值更長的時間,slow_launch_threads計數(shù)器將增加設置慢日志開啟MySQL后可以查詢long_query_time的值。?為了方便

9、測試，可以將修改慢查詢時間為5秒。慢查詢分析mysqldumpslow我們可以通過打開log文