MySQL參數(shù)調(diào)優(yōu)實(shí)例探究講解
一、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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL安裝與創(chuàng)建用戶操作(新手入門指南)
這篇文章主要為大家介紹了MySQL安裝與創(chuàng)建用戶的使用講解是非常適合小白新手的入門學(xué)習(xí),有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-05-05MySQL中l(wèi)imit語(yǔ)法及用法小結(jié)
LIMIT 是 MySQL 中的一個(gè)特殊關(guān)鍵字,用于指定查詢結(jié)果從哪條記錄開(kāi)始顯示,一共顯示多少條記錄,本文重點(diǎn)介紹MySQL中l(wèi)imit語(yǔ)法及用法小結(jié),感興趣的朋友一起看看吧2023-10-10解析MySQL8.0新特性——事務(wù)性數(shù)據(jù)字典與原子DDL
這篇文章主要介紹了MySQL8.0新特性——事務(wù)性數(shù)據(jù)字典與原子DDL的相關(guān)資料,幫助大家更好的理解和學(xué)習(xí)MySQL8.0感興趣的朋友可以了解下2020-08-08CentOS系統(tǒng)中安裝MySQL和開(kāi)啟MySQL遠(yuǎn)程訪問(wèn)的方法
這篇文章主要介紹了CentOS系統(tǒng)中安裝MySQL和開(kāi)啟MySQL遠(yuǎn)程訪問(wèn)的方法,包括MySQL的隨機(jī)啟動(dòng)等操作的介紹,需要的朋友可以參考下2016-02-02mysql截取的字符串函數(shù)substring_index的用法
這篇文章主要介紹了mysql截取的字符串函數(shù)substring_index的用法,需要的朋友可以參考下2014-08-08MySQL5.6 GTID模式下同步復(fù)制報(bào)錯(cuò)不能跳過(guò)的解決方法
搭建虛擬機(jī)centos6.0, mysql5.6.10主從復(fù)制,死活不同步,搞了一整天找到這篇文章終于OK了,特分享一下,需要的朋友可以參考下2020-04-04MySQL中TINYINT、INT 和 BIGINT的具體使用
MySQL提供了多種整數(shù)類型來(lái)滿足不同的數(shù)據(jù)存儲(chǔ)需求,本文主要介紹了MySQL中TINYINT、INT 和 BIGINT的具體使用,具有一定的參考價(jià)值,感興趣的可以了解一下2024-07-07Mysql中JSON字段的值的實(shí)現(xiàn)示例
本文中介紹了如何通過(guò)SQL語(yǔ)句查詢JSON字段中的特定數(shù)據(jù),如查詢數(shù)組中的元素,提取映射中的值,以及使用不同的JSON函數(shù)來(lái)處理數(shù)據(jù),感興趣的可以了解一下2024-09-09