MySQL引數調優範例探究講解

一、MySQL 引數調優

1、一些生產常用查詢命令

-- 檢視資料庫里正在執行的sql語句
show processlist;
-- 檢視正在執行的完整sql語句，完整顯示
show full processlist;
-- 檢視資料庫的設定引數資訊，例如：my.cnf裡引數的生效情況
show variables;
-- MySQL伺服器執行各種狀態值，查詢MySQL伺服器設定資訊語句
show variables like '%log_bin%';
-- 檢視當前對談的資料庫狀態資訊
show session status;
-- 檢視整個資料庫執行狀態資訊，分析並做好監控
show global status;
-- 顯示innodb 引擎的效能狀態
show engine innodb status;
-- 登陸資料庫現場抓(顯示完整的程序列表)
show full processlist;
-- explain語句檢查索引執行情況，將上邊抓到的慢語句，進行一個索引檢查
explain select * from test_table where ***

2、分析慢查詢

生產者中，一般設定如果執行時間超過0.2、0.5、1秒，就是慢查詢。一般可以分析緩慢的查詢紀錄檔，找出有問題的SQL語句。慢速查詢時間不應設定得太長，否則就沒有意義。最好在2秒內。

-- 檢視資料庫的設定引數資訊
show variables like '%slow%';
-- 檢視整個資料庫執行狀態資訊
show global status like '%slow%'; 

開啟慢速查詢紀錄檔可能會對系統效能產生輕微影響，如果您的MySQL是主從結構，您可以考慮開啟其中一個從屬伺服器的慢速查詢紀錄檔，這樣您就可以監視慢速查詢，對系統效能幾乎沒有影響。

3、分析連線數

-- 查詢MySQL伺服器最大連線數(可以理解為設定的最大連線數)
show variables like 'max_connections';
-- 查詢伺服器響應的最大連線數(可以理解為應用的最大連線數)
show global status like 'Max_used_connections';

過去，MySQL伺服器的最大連線數為 Max_used_connections，未達到與伺服器的最大連線數 max_connections ，沒有問題。

理想的設定是：Max_used_connections / max_connections * 100% ≈ 85%

最大連線數約佔最大連線數的85%。如果該比例小於10%，則MySQL伺服器的最大連線數設定得太高。

4、緩衝區調優 Key_buffer_size

Key_buffer_size 是對MyISAM表效能影響最大的引數。快取 MyISAM 表索引以提高 MyISAM 表索引的讀寫效率。

用於索引塊的緩衝區大小可以更好地處理的索引。

對MyISAM表的影響不大。MyISAM將使用系統快取來儲存資料，因此大量使用MyISAM表的計算機的記憶體將很快耗盡。但是，如果您將此值設定得太高（例如，大於總記憶體的50%），系統將轉換為頁面並變得非常緩慢。MySQL在讀取資料時依賴於作業系統來執行檔案系統快取，因此必須為檔案系統快取留出一些空間。

建議將其設定為25%的記憶體，並觀察效能變化。

-- 檢視分配了多少記憶體給key_buffer_size
show variables like 'key_buffer_size';
-- 檢視索引讀取請求、檢視請求在記憶體中沒有找到直接從硬碟讀取索引
show global status like 'key_read%'; 

計算索引未命中快取的概率：key_cache_miss_rate = Key_reads / Key_read_requests * 100%

-- Key_blocks_unused表示未使用的快取簇blocks數
-- Key_blocks_used表示曾經用到的最大的blocks數
show global status like 'key_blocks_u%'; 

比如伺服器的所有快取都用到了，要麼增加 key_buffer_size 。比較理想的設定：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

5、臨時表調優 Created_tmp_tables

-- 檢視在磁碟上建立臨時表
show global status like 'created_tmp'
-- 檢視MySQL伺服器對臨時表的設定
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 

每次建立臨時表時，都會新增 Created_tmp_tables。如果要在磁碟上建立臨時表，Created_tmp_disk_Tables也會增加，Created_tmp_files 表示MySQL服務建立的臨時檔案的數量。

理想的設定是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

6、分析 Open Table 使用情況

-- Open_tables表示開啟表的數量，如果Opened_tables數量過大，說明設定中 table_open_cache 值可能太小
show global status like 'open%tables%';
-- 查詢一下伺服器table_cache值
show variables like 'table_cache';

7、檢視 MySQL程序使用情況

thread_cache_size 快取可重用的執行緒數，此引數設定執行緒快取。執行緒建立和銷燬的成本可能很高，因為每個執行緒都需要連線/斷開連線。如果應用程式具有大量跳轉並行連線和更多執行緒，請增加其值。它旨在避免在正常操作中建立新執行緒。建議將其設定為至少16。

show global status like 'Thread%'; 
-- 查詢伺服器thread_cache_size設定
show variables like 'thread_cache_size'; 

如果我們在MySQL伺服器組態檔中設定 thread_cache_size。當用戶端斷開連線時，處理此使用者端的伺服器執行緒將被快取以響應下一個使用者端，而不是銷燬它（前提是快取數量未達到上限）。

Threads_created 表示建立的執行緒數。如果發現建立的值太大，則表明MySQL伺服器一直在建立執行緒，這也會消耗資源。您可以在組態檔中適當地新增 thread_cache_size 值。

8、查詢快取 query cache

query_cache_size 指定MySQL查詢結果緩衝區的大小，如果應用程式具有大量讀取且沒有應用程式級快取，則此選項非常有用。但是，不要將其設定得太大，因為維護它也需要大量開銷，這會導致MySQL速度減慢。

-- 查詢 query_cache
show global status like 'qcache%'; 
-- 查詢伺服器 query_cache 設定
show variables like 'query_cache%';

query_cache_limit：快取上限，超過此大小的查詢將不會被快取，快取單條SQL的結果集上限。預設4KB。當一條SQL返回的結果集大於這個限制的時候，將不被MySQL快取。

query_cache_min_res_unit：快取塊的最小大小，快取是每個資料集的最小記憶體大小。預設大小為4KB。如果它太小，MySQL將經常存取記憶體塊以獲取資訊。如果設定得太大，記憶體將被浪費。如果SQL返回的結果集非常小，還可以減少引數以避免記憶體浪費。如果大多數結果集大於4KB，請考慮增加引數。

query_cache_size：查詢快取大小 query_cache_type：快取型別，它決定要快取的查詢。在本例中，這意味著不快取 select sql_no_cache 查詢 query_cache_wlock_invalidate：當其他使用者端正在寫入MyISAM表時，如果查詢在 query cache 中，則在讀取表以獲取結果之前，是返回快取結果還是等待寫入操作完成。

計算快取碎片率：Qcache_free_blocks / Qcache_total_blocks * 100%

計算快取利用率：(query_cache_size - Qcache_free_memory) / query_cache_size * 100%

計算快取命中率：(Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

query_cache_min_res_unit，預設值為4KB。大的設定對於巨量資料查詢很好，但如果您的查詢都是小資料查詢，則很容易導致記憶體碎片和浪費。如果查詢快取碎片率超過20%，則可以使用 FLUSH QUERY CACHE 對快取碎片進行碎片整理，或者如果查詢都是小資料，則可以嘗試減少 query_cache_min_res_unit 。如果查詢快取利用率低於25%，query_cache_size 如果大小設定得太大，可以適當減小。

9、分析SQL排序與鎖及檔案使用與表掃描情況

-- 分析SQL排序使用情況
show global status like 'sort%'; 

sort_buffer_size，為每個需要排序的執行緒分配此大小的緩衝區，並增加此值以加快ORDER BY或GROUP BY操作。

但是，與此引數相對應的分配記憶體對於每個連線是互斥的。如果有100個連線，則實際分配的排序緩衝區的總大小為100×sort_buffer_size，通常設定為2M以觀察更改，然後進行調整。

-- 分析SQL表掃描情況
show global status like 'handler_read%'; 

表掃描率 = Handler_read_rnd_next / Com_select，如果表掃描速率超過4000，則表明執行了太多的表掃描。很可能索引尚未構建，並且已新增讀取緩衝區（read_buffer_size）大小值有一些優勢，但最好不要超過8MB。

-- 檢視檔案開啟數
show global status like 'open_files';
-- 檢視設定 Open_files / open_files_limit * 100% <= 75%
show variables like 'open_files_limit';
-- 檢視表鎖
show global status like 'table_locks%';
-- 查詢MySQL伺服器完成的查詢請求次數：
show global status like 'com_select';

二、MySQL優化引數實戰

網際網路上有很多文章介紹如何設定 MySQL 伺服器。然而，考慮到伺服器硬體設定和特定應用程式的差異，首先使用MySQL預設值，我們需要根據自己的情況優化設定。

好的做法是，首先使用MySQL預設值，在 MySQL 伺服器穩定執行一段時間後執行，並根據伺服器的狀態，以及上述的調優引數的知識對其進行優化。

每個連線都需要申請相應的記憶體，根據預設引數值，每個連線執行緒使用的最大記憶體大小為25MB。執行緒級別引數不應設定得太大。

read_buffer_size：用於順序讀的緩衝區大小，提高順序讀效率，預設131072

read_rnd_buffer_size：用於隨機讀的緩衝大小，提高隨機讀效率，預設262144

sort_buffer_size：排序緩衝大小，提高排序效率，預設262144

join_buffer_size：用於普通索引掃描，範圍索引掃描和不使用索引並因此執行全表掃描連線的緩衝區的最小值，提高表連線效率，預設262144

binlog_cache_size：二進位制紀錄檔緩衝大小，用於快取事務執行期間binlog，提高binlog寫入效率，預設32768

tmp_table_size：臨時表記憶體大小，提高臨時表儲存效率，預設16777216

thread_stack：執行緒堆疊大小，暫存複雜SQL語句、控制儲存過程遞迴深度等，預設262144

thread_cache_size：執行緒快取大小，減少多次開啟執行緒開銷，預設8+(max_connections/100)，上限100

net_buffer_length：執行緒池連線緩衝以及讀取結果緩衝大小，預設16384

bulk_insert_buffer_size：MyISAM表批次寫入資料快取大小，預設8388608

總結

本文主要講解MySQL引數調優，包括SQL調優的引數解析，如何在生產環境調優，文中根據不同的資料庫引數給出調優建議與應用場景。

到此這篇關於MySQL引數調優範例探究講解的文章就介紹到這了,更多相關MySQL引數調優內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！