一、MySQL 參數(shù)調(diào)優(yōu)

1、一些生產(chǎn)常用查詢命令

-- 查看數(shù)據(jù)庫(kù)里正在執(zhí)行的sql語(yǔ)句
show processlist;
-- 查看正在執(zhí)行的完整sql語(yǔ)句，完整顯示
show full processlist;
-- 查看數(shù)據(jù)庫(kù)的配置參數(shù)信息，例如：my.cnf里參數(shù)的生效情況
show variables;
-- MySQL服務(wù)器運(yùn)行各種狀態(tài)值，查詢MySQL服務(wù)器配置信息語(yǔ)句
show variables like '%log_bin%';
-- 查看當(dāng)前會(huì)話的數(shù)據(jù)庫(kù)狀態(tài)信息
show session status;
-- 查看整個(gè)數(shù)據(jù)庫(kù)運(yùn)行狀態(tài)信息，分析并做好監(jiān)控
show global status;
-- 顯示innodb 引擎的性能狀態(tài)
show engine innodb status;
-- 登陸數(shù)據(jù)庫(kù)現(xiàn)場(chǎng)抓(顯示完整的進(jìn)程列表)
show full processlist;
-- explain語(yǔ)句檢查索引執(zhí)行情況，將上邊抓到的慢語(yǔ)句，進(jìn)行一個(gè)索引檢查
explain select * from test_table where ***

2、分析慢查詢

生產(chǎn)者中，一般設(shè)置如果執(zhí)行時(shí)間超過(guò)0.2、0.5、1秒，就是慢查詢。一般可以分析緩慢的查詢?nèi)罩?，找出有?wèn)題的SQL語(yǔ)句。慢速查詢時(shí)間不應(yīng)設(shè)置得太長(zhǎng)，否則就沒(méi)有意義。最好在2秒內(nèi)。

-- 查看數(shù)據(jù)庫(kù)的配置參數(shù)信息
show variables like '%slow%';
-- 查看整個(gè)數(shù)據(jù)庫(kù)運(yùn)行狀態(tài)信息
show global status like '%slow%'; 

打開(kāi)慢速查詢?nèi)罩究赡軙?huì)對(duì)系統(tǒng)性能產(chǎn)生輕微影響，如果您的MySQL是主從結(jié)構(gòu)，您可以考慮打開(kāi)其中一個(gè)從屬服務(wù)器的慢速查詢?nèi)罩?，這樣您就可以監(jiān)視慢速查詢，對(duì)系統(tǒng)性能幾乎沒(méi)有影響。

3、分析連接數(shù)

-- 查詢MySQL服務(wù)器最大連接數(shù)(可以理解為設(shè)置的最大連接數(shù))
show variables like 'max_connections';
-- 查詢服務(wù)器響應(yīng)的最大連接數(shù)(可以理解為應(yīng)用的最大連接數(shù))
show global status like 'Max_used_connections';

過(guò)去，MySQL服務(wù)器的最大連接數(shù)為 Max_used_connections，未達(dá)到與服務(wù)器的最大連接數(shù) max_connections ，沒(méi)有問(wèn)題。

理想的設(shè)置是：Max_used_connections / max_connections * 100% ≈ 85%

最大連接數(shù)約占最大連接數(shù)的85%。如果該比例小于10%，則MySQL服務(wù)器的最大連接數(shù)設(shè)置得太高。

4、緩沖區(qū)調(diào)優(yōu) Key_buffer_size

Key_buffer_size 是對(duì)MyISAM表性能影響最大的參數(shù)。緩存 MyISAM 表索引以提高 MyISAM 表索引的讀寫效率。

用于索引塊的緩沖區(qū)大小可以更好地處理的索引。

對(duì)MyISAM表的影響不大。MyISAM將使用系統(tǒng)緩存來(lái)存儲(chǔ)數(shù)據(jù)，因此大量使用MyISAM表的計(jì)算機(jī)的內(nèi)存將很快耗盡。但是，如果您將此值設(shè)置得太高（例如，大于總內(nèi)存的50%），系統(tǒng)將轉(zhuǎn)換為頁(yè)面并變得非常緩慢。MySQL在讀取數(shù)據(jù)時(shí)依賴于操作系統(tǒng)來(lái)執(zhí)行文件系統(tǒng)緩存，因此必須為文件系統(tǒng)緩存留出一些空間。

建議將其設(shè)置為25%的內(nèi)存，并觀察性能變化。

-- 查看分配了多少內(nèi)存給key_buffer_size
show variables like 'key_buffer_size';
-- 查看索引讀取請(qǐng)求、查看請(qǐng)求在內(nèi)存中沒(méi)有找到直接從硬盤讀取索引
show global status like 'key_read%'; 

計(jì)算索引未命中緩存的概率：key_cache_miss_rate = Key_reads / Key_read_requests * 100%

-- Key_blocks_unused表示未使用的緩存簇blocks數(shù)
-- Key_blocks_used表示曾經(jīng)用到的最大的blocks數(shù)
show global status like 'key_blocks_u%'; 

比如服務(wù)器的所有緩存都用到了，要么增加 key_buffer_size 。比較理想的設(shè)置：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

5、臨時(shí)表調(diào)優(yōu) Created_tmp_tables

-- 查看在磁盤上創(chuàng)建臨時(shí)表
show global status like 'created_tmp'
-- 查看MySQL服務(wù)器對(duì)臨時(shí)表的配置
show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 

每次創(chuàng)建臨時(shí)表時(shí)，都會(huì)添加 Created_tmp_tables。如果要在磁盤上創(chuàng)建臨時(shí)表，Created_tmp_disk_Tables也會(huì)增加，Created_tmp_files 表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件的數(shù)量。

理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

6、分析 Open Table 使用情況

-- Open_tables表示打開(kāi)表的數(shù)量，如果Opened_tables數(shù)量過(guò)大，說(shuō)明配置中 table_open_cache 值可能太小
show global status like 'open%tables%';
-- 查詢一下服務(wù)器table_cache值
show variables like 'table_cache';

7、查看 MySQL進(jìn)程使用情況

thread_cache_size 緩存可重用的線程數(shù)，此參數(shù)設(shè)置線程緩存。線程創(chuàng)建和銷毀的成本可能很高，因?yàn)槊總€(gè)線程都需要連接/斷開(kāi)連接。如果應(yīng)用程序具有大量跳轉(zhuǎn)并發(fā)連接和更多線程，請(qǐng)?jiān)黾悠渲?。它旨在避免在正常操作中?chuàng)建新線程。建議將其設(shè)置為至少16。

show global status like 'Thread%'; 
-- 查詢服務(wù)器thread_cache_size配置
show variables like 'thread_cache_size'; 

如果我們?cè)贛ySQL服務(wù)器配置文件中設(shè)置 thread_cache_size。當(dāng)客戶端斷開(kāi)連接時(shí)，處理此客戶端的服務(wù)器線程將被緩存以響應(yīng)下一個(gè)客戶端，而不是銷毀它（前提是緩存數(shù)量未達(dá)到上限）。

Threads_created 表示創(chuàng)建的線程數(shù)。如果發(fā)現(xiàn)創(chuàng)建的值太大，則表明MySQL服務(wù)器一直在創(chuàng)建線程，這也會(huì)消耗資源。您可以在配置文件中適當(dāng)?shù)靥砑?thread_cache_size 值。

8、查詢緩存 query cache

query_cache_size 指定MySQL查詢結(jié)果緩沖區(qū)的大小，如果應(yīng)用程序具有大量讀取且沒(méi)有應(yīng)用程序級(jí)緩存，則此選項(xiàng)非常有用。但是，不要將其設(shè)置得太大，因?yàn)榫S護(hù)它也需要大量開(kāi)銷，這會(huì)導(dǎo)致MySQL速度減慢。

-- 查詢 query_cache
show global status like 'qcache%'; 
-- 查詢服務(wù)器 query_cache 配置
show variables like 'query_cache%';

query_cache_limit：緩存上限，超過(guò)此大小的查詢將不會(huì)被緩存，緩存單條SQL的結(jié)果集上限。默認(rèn)4KB。當(dāng)一條SQL返回的結(jié)果集大于這個(gè)限制的時(shí)候，將不被MySQL緩存。

query_cache_min_res_unit：緩存塊的最小大小，緩存是每個(gè)數(shù)據(jù)集的最小內(nèi)存大小。默認(rèn)大小為4KB。如果它太小，MySQL將經(jīng)常訪問(wèn)內(nèi)存塊以獲取信息。如果設(shè)置得太大，內(nèi)存將被浪費(fèi)。如果SQL返回的結(jié)果集非常小，還可以減少參數(shù)以避免內(nèi)存浪費(fèi)。如果大多數(shù)結(jié)果集大于4KB，請(qǐng)考慮增加參數(shù)。

query_cache_size：查詢緩存大小 query_cache_type：緩存類型，它決定要緩存的查詢。在本例中，這意味著不緩存 select sql_no_cache 查詢 query_cache_wlock_invalidate：當(dāng)其他客戶端正在寫入MyISAM表時(shí)，如果查詢?cè)?query cache 中，則在讀取表以獲取結(jié)果之前，是返回緩存結(jié)果還是等待寫入操作完成。

計(jì)算緩存碎片率：Qcache_free_blocks / Qcache_total_blocks * 100%

計(jì)算緩存利用率：(query_cache_size - Qcache_free_memory) / query_cache_size * 100%

計(jì)算緩存命中率：(Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

query_cache_min_res_unit，默認(rèn)值為4KB。大的設(shè)置對(duì)于大數(shù)據(jù)查詢很好，但如果您的查詢都是小數(shù)據(jù)查詢，則很容易導(dǎo)致內(nèi)存碎片和浪費(fèi)。如果查詢緩存碎片率超過(guò)20%，則可以使用 FLUSH QUERY CACHE 對(duì)緩存碎片進(jìn)行碎片整理，或者如果查詢都是小數(shù)據(jù)，則可以嘗試減少 query_cache_min_res_unit 。如果查詢緩存利用率低于25%，query_cache_size 如果大小設(shè)置得太大，可以適當(dāng)減小。

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

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

sort_buffer_size，為每個(gè)需要排序的線程分配此大小的緩沖區(qū)，并增加此值以加快ORDER BY或GROUP BY操作。

但是，與此參數(shù)相對(duì)應(yīng)的分配內(nèi)存對(duì)于每個(gè)連接是互斥的。如果有100個(gè)連接，則實(shí)際分配的排序緩沖區(qū)的總大小為100×sort_buffer_size，通常設(shè)置為2M以觀察更改，然后進(jìn)行調(diào)整。

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

表掃描率 = Handler_read_rnd_next / Com_select，如果表掃描速率超過(guò)4000，則表明執(zhí)行了太多的表掃描。很可能索引尚未構(gòu)建，并且已添加讀取緩沖區(qū)（read_buffer_size）大小值有一些優(yōu)勢(shì)，但最好不要超過(guò)8MB。

-- 查看文件打開(kāi)數(shù)
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服務(wù)器完成的查詢請(qǐng)求次數(shù)：
show global status like 'com_select';

二、MySQL優(yōu)化參數(shù)實(shí)戰(zhàn)

互聯(lián)網(wǎng)上有很多文章介紹如何配置 MySQL 服務(wù)器。然而，考慮到服務(wù)器硬件配置和特定應(yīng)用程序的差異，首先使用MySQL默認(rèn)值，我們需要根據(jù)自己的情況優(yōu)化配置。

好的做法是，首先使用MySQL默認(rèn)值，在 MySQL 服務(wù)器穩(wěn)定運(yùn)行一段時(shí)間后運(yùn)行，并根據(jù)服務(wù)器的狀態(tài)，以及上述的調(diào)優(yōu)參數(shù)的知識(shí)對(duì)其進(jìn)行優(yōu)化。

每個(gè)連接都需要申請(qǐng)相應(yīng)的內(nèi)存，根據(jù)默認(rèn)參數(shù)值，每個(gè)連接線程使用的最大內(nèi)存大小為25MB。線程級(jí)別參數(shù)不應(yīng)設(shè)置得太大。

read_buffer_size：用于順序讀的緩沖區(qū)大小，提高順序讀效率，默認(rèn)131072

read_rnd_buffer_size：用于隨機(jī)讀的緩沖大小，提高隨機(jī)讀效率，默認(rèn)262144

sort_buffer_size：排序緩沖大小，提高排序效率，默認(rèn)262144

join_buffer_size：用于普通索引掃描，范圍索引掃描和不使用索引并因此執(zhí)行全表掃描連接的緩沖區(qū)的最小值，提高表連接效率，默認(rèn)262144

binlog_cache_size：二進(jìn)制日志緩沖大小，用于緩存事務(wù)執(zhí)行期間binlog，提高binlog寫入效率，默認(rèn)32768

tmp_table_size：臨時(shí)表內(nèi)存大小，提高臨時(shí)表存儲(chǔ)效率，默認(rèn)16777216

thread_stack：線程堆棧大小，暫存復(fù)雜SQL語(yǔ)句、控制存儲(chǔ)過(guò)程遞歸深度等，默認(rèn)262144

thread_cache_size：線程緩存大小，減少多次打開(kāi)線程開(kāi)銷，默認(rèn)8+(max_connections/100)，上限100

net_buffer_length：線程池連接緩沖以及讀取結(jié)果緩沖大小，默認(rèn)16384

bulk_insert_buffer_size：MyISAM表批量寫入數(shù)據(jù)緩存大小，默認(rèn)8388608

總結(jié)

本文主要講解MySQL參數(shù)調(diào)優(yōu)，包括SQL調(diào)優(yōu)的參數(shù)解析，如何在生產(chǎn)環(huán)境調(diào)優(yōu)，文中根據(jù)不同的數(shù)據(jù)庫(kù)參數(shù)給出調(diào)優(yōu)建議與應(yīng)用場(chǎng)景。

到此這篇關(guān)于MySQL參數(shù)調(diào)優(yōu)實(shí)例探究講解的文章就介紹到這了,更多相關(guān)MySQL參數(shù)調(diào)優(yōu)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論