MySQL效能調優

標籤：允許   線程   慢查詢日誌   mysql日誌   requests   效能   讀取   file   variable   

MySQL效能調優
提高MySQL系統的效能、影響速度
-替換硬體（CPU/磁碟/記憶體等）
-服務程式的運行參數調整
-對SQL查詢進行最佳化

並發及串連控制
-串連數、連線逾時
max_connections //允許的最大並發串連數
connect_timeout //等待建立串連的逾時秒數，預設10秒，只在登入時有效
wait_timeout //等待關閉串連的不活動逾時秒數，預設28800秒（8小時）

-產看當前已使用的串連數
mysql>flush status;
mysql>show globale status like "max_used_connections";

-查看預設的最大串連數
mysql>show variables like "max_connections";

-緩衝參數控制
緩衝區、線程數量、開表數量
key_buffer_size //用於MyISAM引擎的關鍵索引緩衝大小
sort_buffer_size //為每個要排序的線程分配此大小的緩衝空間
read_buffer_size //為順序讀取表記錄保留的緩衝大小
read_rnd_buffer_size //為按排序結果讀取表記錄保留的緩衝大小
thread_cache_size //允許儲存在緩衝中被重用的線程數量
table_open_cache //為所有線程緩衝的開啟的表的數量

key_buffer_size=8M
當key_reads/key_read_requests較低時可適當加大此緩衝值
mysql>show global status like "key_read%";
mysql>show variables like "key_buffer_size";

sort_buffer_size=256K
增大此值可提高ORDER和GROUP的速度
mysql>show variables like "sort_buffer_size";

查看錶記錄讀取緩衝
-此緩衝值影響SQL查詢的響應速度
mysql>show variables like "read_%_size";

查看可重用線程
mysql>show variables like "thread_%_size";

查看當前的線程重用狀態
mysql>show global status like "threads_%";

查看已開啟、開啟過多少個表
mysql>show global status like "open%tables";

查看可緩衝多少個開啟的表
mysql>show variables like "table_open_cache";

SQL查詢最佳化
MySQL日誌類型，常用日誌種類及選項：
錯誤記錄檔 log_error[=name] //記錄啟用/運行/停止過程的錯誤訊息
查詢日誌 general_log,general_log_file= //記錄用戶端串連和查詢操作
慢查詢日誌 slow_query_log,slow_query_log_file=,long_query_time= //記錄耗時較長或不使用索引的查詢操作

記錄慢查詢：
slow-query-log //啟用慢查詢
slow-query-log-file //指定慢查詢記錄檔
long-query-time //超過指定秒數（預設10秒）的查詢才被記錄
log-queries-not-using-indexes //記錄未使用索引的查詢

調整服務配置：
vim /etc/my.cnf
[mysqld]
...
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=5
log_queries_not_using_indexes=1

systemctl restart mysqld

查看慢查詢日誌，使用mysqldumpslow工具
mysqldumpslow /var/lib/mysql/mysql-slow.log

查看緩衝的大小
mysql>show variables like "query_cache%";

查看當前的查詢快取統計
mysql>show global status like "qcache%";

MySQL效能調優