MySQL怎么查瓶頸 mysql查詢性能瓶頸

超詳細(xì)MySQL數(shù)據(jù)庫優(yōu)化

數(shù)據(jù)庫優(yōu)化一方面是找出系統(tǒng)的瓶頸,提高M(jìn)ySQL數(shù)據(jù)庫的整體性能,而另一方面需要合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整,以提高用戶的相應(yīng)速度,同時(shí)還要盡可能的節(jié)約系統(tǒng)資源,以便讓系統(tǒng)提供更大的負(fù)荷.

創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括崇州網(wǎng)站建設(shè)、崇州網(wǎng)站制作、崇州網(wǎng)頁制作以及崇州網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，崇州網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到崇州省份的部分城市，未來相信會繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

1. 優(yōu)化一覽圖

2. 優(yōu)化

筆者將優(yōu)化分為了兩大類,軟優(yōu)化和硬優(yōu)化,軟優(yōu)化一般是操作數(shù)據(jù)庫即可,而硬優(yōu)化則是操作服務(wù)器硬件及參數(shù)設(shè)置.

2.1 軟優(yōu)化

2.1.1 查詢語句優(yōu)化

1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執(zhí)行信息.

2.例:

顯示:

其中會顯示索引和查詢數(shù)據(jù)讀取數(shù)據(jù)條數(shù)等信息.

2.1.2 優(yōu)化子查詢

在MySQL中,盡量使用JOIN來代替子查詢.因?yàn)樽硬樵冃枰短撞樵?嵌套查詢時(shí)會建立一張臨時(shí)表,臨時(shí)表的建立和刪除都會有較大的系統(tǒng)開銷,而連接查詢不會創(chuàng)建臨時(shí)表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高數(shù)據(jù)庫查詢速度最重要的方法之一,關(guān)于索引可以參高筆者M(jìn)ySQL數(shù)據(jù)庫索引一文,介紹比較詳細(xì),此處記錄使用索引的三大注意事項(xiàng):

2.1.4 分解表

對于字段較多的表,如果某些字段使用頻率較低,此時(shí)應(yīng)當(dāng),將其分離出來從而形成新的表,

2.1.5 中間表

對于將大量連接查詢的表可以創(chuàng)建中間表,從而減少在查詢時(shí)造成的連接耗時(shí).

2.1.6 增加冗余字段

類似于創(chuàng)建中間表,增加冗余也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優(yōu)化表

分析表主要是分析表中關(guān)鍵字的分布,檢查表主要是檢查表中是否存在錯誤,優(yōu)化表主要是消除刪除或更新造成的表空間浪費(fèi).

1. 分析表: 使用 ANALYZE 關(guān)鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關(guān)鍵字,如CHECK TABLE user [option]

option 只對MyISAM有效,共五個參數(shù)值:

3. 優(yōu)化表:使用OPTIMIZE關(guān)鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日志.,優(yōu)化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執(zhí)行過程中會加上只讀鎖.

2.2 硬優(yōu)化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執(zhí)行多個線程.

2.配置大內(nèi)存,提高內(nèi)存,即可提高緩存區(qū)容量,因此能減少磁盤I/O時(shí)間,從而提高響應(yīng)速度.

3.配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高并行操作的能力.

2.2.2 優(yōu)化數(shù)據(jù)庫參數(shù)

優(yōu)化數(shù)據(jù)庫參數(shù)可以提高資源利用率,從而提高M(jìn)ySQL服務(wù)器性能.MySQL服務(wù)的配置參數(shù)都在my.cnf或my.ini,下面列出性能影響較大的幾個參數(shù).

2.2.3 分庫分表

因?yàn)閿?shù)據(jù)庫壓力過大，首先一個問題就是高峰期系統(tǒng)性能可能會降低，因?yàn)閿?shù)據(jù)庫負(fù)載過高對性能會有影響。另外一個，壓力過大把你的數(shù)據(jù)庫給搞掛了怎么辦？所以此時(shí)你必須得對系統(tǒng)做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個數(shù)據(jù)庫服務(wù)上，這時(shí)作為主庫承載寫入請求。然后每個主庫都掛載至少一個從庫，由從庫來承載讀請求。

2.2.4 緩存集群

如果用戶量越來越大，此時(shí)你可以不停的加機(jī)器，比如說系統(tǒng)層面不停加機(jī)器，就可以承載更高的并發(fā)請求。然后數(shù)據(jù)庫層面如果寫入并發(fā)越來越高，就擴(kuò)容加數(shù)據(jù)庫服務(wù)器，通過分庫分表是可以支持?jǐn)U容機(jī)器的，如果數(shù)據(jù)庫層面的讀并發(fā)越來越高，就擴(kuò)容加更多的從庫。但是這里有一個很大的問題：數(shù)據(jù)庫其實(shí)本身不是用來承載高并發(fā)請求的，所以通常來說，數(shù)據(jù)庫單機(jī)每秒承載的并發(fā)就在幾千的數(shù)量級，而且數(shù)據(jù)庫使用的機(jī)器都是比較高配置，比較昂貴的機(jī)器，成本很高。如果你就是簡單的不停的加機(jī)器，其實(shí)是不對的。所以在高并發(fā)架構(gòu)里通常都有緩存這個環(huán)節(jié)，緩存系統(tǒng)的設(shè)計(jì)就是為了承載高并發(fā)而生。所以單機(jī)承載的并發(fā)量都在每秒幾萬，甚至每秒數(shù)十萬，對高并發(fā)的承載能力比數(shù)據(jù)庫系統(tǒng)要高出一到兩個數(shù)量級。所以你完全可以根據(jù)系統(tǒng)的業(yè)務(wù)特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫數(shù)據(jù)庫的時(shí)候同時(shí)寫一份數(shù)據(jù)到緩存集群里，然后用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機(jī)器資源承載更高的并發(fā)。

一個完整而復(fù)雜的高并發(fā)系統(tǒng)架構(gòu)中，一定會包含：各種復(fù)雜的自研基礎(chǔ)架構(gòu)系統(tǒng)。各種精妙的架構(gòu)設(shè)計(jì).因此一篇小文頂多具有拋磚引玉的效果,但是數(shù)據(jù)庫優(yōu)化的思想差不多就這些了.

如何判斷MSSQL數(shù)據(jù)庫磁盤出現(xiàn)了瓶頸

具體問題具體分析，舉例來說明為什么磁盤IO成瓶頸數(shù)據(jù)庫的性能急速下降了。

為什么當(dāng)磁盤IO成瓶頸之后, 數(shù)據(jù)庫的性能不是達(dá)到飽和的平衡狀態(tài)，而是急劇下降。為什么數(shù)據(jù)庫的性能有非常明顯的分界點(diǎn)，原因是什么？

相信大部分做數(shù)據(jù)庫運(yùn)維的朋友，都遇到這種情況。 數(shù)據(jù)庫在前一天性能表現(xiàn)的相當(dāng)穩(wěn)定，數(shù)據(jù)庫的響應(yīng)時(shí)間也很正常，但就在今天，在業(yè)務(wù)人員反饋業(yè)務(wù)流量沒有任何上升的情況下，數(shù)據(jù)庫的變得不穩(wěn)定了，有時(shí)候一個最簡單的insert操作， 需要幾十秒，但99%的insert卻又可以在幾毫秒完成，這又是為什么了？

dba此時(shí)心中有無限的疑惑，到底是什么原因呢? 磁盤IO性能變差了？還是業(yè)務(wù)運(yùn)維人員反饋的流量壓根就不對？ 還是數(shù)據(jù)庫內(nèi)部出問題？昨天不是還好好的嗎？

當(dāng)數(shù)據(jù)庫出現(xiàn)響應(yīng)時(shí)間不穩(wěn)定的時(shí)候，我們在操作系統(tǒng)上會看到磁盤的利用率會比較高，如果觀察仔細(xì)一點(diǎn)，還可以看到，存在一些讀的IO. 數(shù)據(jù)庫服務(wù)器如果存在大量的寫IO,性能一般都是正常跟穩(wěn)定的，但只要存在少量的讀IO,則性能開始出現(xiàn)抖動，存在大量的讀IO時(shí)（排除配備非常高速磁盤的機(jī)器），對于在線交易的數(shù)據(jù)庫系統(tǒng)來說，大概性能就雪崩了。為什么操作系統(tǒng)上看到的磁盤讀IO跟寫IO所帶來的性能差距這么大呢？

如果親之前沒有注意到上述的現(xiàn)象，親對上述的結(jié)論也是懷疑。但請看下面的分解。

在寫這個文章之前，作者閱讀了大量跟的IO相關(guān)的代碼，如異步IO線程的相關(guān)的，innodb_buffer池相關(guān)的，以及跟讀數(shù)據(jù)塊最相關(guān)的核心函數(shù)buf_page_get_gen函數(shù)以及其調(diào)用的相關(guān)子函數(shù)。為了將文章寫得通俗點(diǎn)，看起來不那么累，因此不再一行一行的將代碼解析寫出來。

咱們先來提問題。?buf_page_get_gen函數(shù)的作用是從Buffer bool里面讀數(shù)據(jù)頁，可能存在以下幾種情況。

提問. 數(shù)據(jù)頁不在buffer bool 里面該怎么辦？

回答：去讀文件，將文件中的數(shù)據(jù)頁加載到buffer pool里面。下面是函數(shù)buffer_read_page的函數(shù)，作用是將物理數(shù)據(jù)頁加載到buffer pool, 圖片中顯示

buffer_read_page函數(shù)棧的頂層是pread64(),調(diào)用了操作系統(tǒng)的讀函數(shù)。

buf_read_page的代碼

如果去讀文件，則需要等待物理讀IO的完成，如果此時(shí)IO沒有及時(shí)響應(yīng)，則存在堵塞。這是一個同步讀的操作，如果不完成該線程無法繼續(xù)后續(xù)的步驟。因?yàn)樾枰臄?shù)據(jù)頁不再buffer 中，無法直接使用該數(shù)據(jù)頁，必須等待操作系統(tǒng)完成IO .

再接著上面的回答提問：

當(dāng)?shù)诙捑€程執(zhí)行sql的時(shí)候，也需要去訪問相同的數(shù)據(jù)頁，它是等待上面的線程將這個數(shù)據(jù)頁讀入到緩存中，還是自己再發(fā)起一個讀磁盤的然后加載到buffer的請求呢？?? 代碼告訴我們，是前者，等待第一個請求該數(shù)據(jù)頁的線程讀入buffer pool。

試想一下，如果第一個請求該數(shù)據(jù)頁的線程因?yàn)榇疟PIO瓶頸，遲遲沒有將物理數(shù)據(jù)頁讀入buffer pool, 這個時(shí)間區(qū)間拖得越長，則造成等待該數(shù)據(jù)塊的用戶線程就越多。對高并發(fā)的系統(tǒng)來說，將造成大量的等待。 等待數(shù)據(jù)頁讀入的函數(shù)是buf_wait_for_read，下面是該函數(shù)相關(guān)的棧。

通過解析buf_wait_for_read函數(shù)的下層函數(shù)，我們知道其實(shí)通過首先自旋加鎖pin的方式，超過設(shè)定的自旋次數(shù)之后，進(jìn)入等待，等待IO完成被喚醒。這樣節(jié)省不停自旋pin時(shí)消耗的cpu,但需要付出被喚起時(shí)的開銷。

再繼續(xù)擴(kuò)展問題： 如果會話線程A 經(jīng)過物理IO將數(shù)據(jù)頁1001讀入buffer之后，他需要修改這個頁，而在會話線程A之后的其他的同樣需要訪問數(shù)據(jù)頁1001的會話線程，即使在數(shù)據(jù)頁1001被入讀buffer pool之后，將仍然處于等待中。因?yàn)樵跀?shù)據(jù)頁上讀取或者更新的時(shí)候，同樣需要上鎖，這樣才能保證數(shù)據(jù)頁并發(fā)讀取/更新的一致性。

由此可見，當(dāng)一個高并發(fā)的系統(tǒng)，出現(xiàn)了熱點(diǎn)數(shù)據(jù)頁需要從磁盤上加載到buffer pool中時(shí)，造成的延遲，是難以想象的。因此排在等待熱點(diǎn)頁隊(duì)列最后的會話線程最后才得到需要的頁，響應(yīng)時(shí)間也就越長，這就是造成了一個簡單的sql需要執(zhí)行幾十秒的原因。

再回頭來看上面的問題，mysql數(shù)據(jù)庫出現(xiàn)性能下降時(shí)，可以看到操作系統(tǒng)有讀IO。 原因是，在數(shù)據(jù)庫對數(shù)據(jù)頁的更改，是在內(nèi)存中的，然后通過檢查點(diǎn)線程進(jìn)行異步寫盤，這個異步的寫操作是不堵塞執(zhí)行sql的會話線程的。所以，即使看到操作系統(tǒng)上有大量的寫IO，數(shù)據(jù)庫的性能也是很平穩(wěn)的。但當(dāng)用戶線程需要查找的數(shù)據(jù)頁不在buffer pool中時(shí)，則會從磁盤上讀取，在一個熱點(diǎn)數(shù)據(jù)頁不是非常多的情況下，我們設(shè)置足夠大的innodb_buffer_pool的size, 基本可以緩存所有的數(shù)據(jù)頁，因此一般都不會出現(xiàn)缺頁的情況，也就是在操作系統(tǒng)上基本看不到讀的IO。 ?當(dāng)出現(xiàn)讀的IO時(shí)，原因時(shí)在執(zhí)行buf_read_page_low函數(shù)，從磁盤上讀取數(shù)據(jù)頁到buffer pool, 則數(shù)據(jù)庫的性能則開始下降，當(dāng)出現(xiàn)大量的讀IO，數(shù)據(jù)庫的性能會非常差。

(16)mysql瓶頸 & MGR和一致性讀 性能測試

容量： 看硬件

InnoDB?最大容量64TB ，存儲引擎將 InnoDB 表 保存在一個 表空間內(nèi)（ 原始磁盤分區(qū)，由數(shù)個文件創(chuàng)建）。這樣， 表大小 能超過 單獨(dú)文件最大容量 。

MySQL 3.22( MyISAM )限制表大小 4GB ，最大表尺寸增加到65536TB（2567 – 1字節(jié)）。最大有效表尺寸通常是由 操作系統(tǒng) 對 文件大小限制 決定的， 不是 由MySQL內(nèi)部限制決定。

最多 20億個表 ，一個表允許定義1024列，每行的最大長度為8092字節(jié)（不包括文本和圖像類型的長度）；

阿里《Java 開發(fā)手冊》提出 單表行 500w 容量2GB ，才分庫分表

與 MySQL 配置及硬件 有關(guān)，實(shí)際記錄的條數(shù)無關(guān)。因?yàn)楸?索引 裝載 到內(nèi)存，InnoDB buffer size 足夠 ，才能全加載進(jìn)內(nèi)存，查沒問題。達(dá)量級限時(shí)，導(dǎo)致 內(nèi)存無法存儲索引 ，產(chǎn)生磁盤 IO，性能下降。增加硬件配置解決。500w算折中

QPS在8400左右 ：400個線程并發(fā)，插入100萬條記錄（4核2.33G、3G內(nèi)存、SATA硬盤）

寫： 90-100M/S（機(jī)械硬盤，7200轉(zhuǎn)）預(yù)計(jì)kB_wrtn/s在90M左右

show variables like 'max_connections' ?mysql當(dāng)前最大連接數(shù)

set global max_connections=1000; ?設(shè)置當(dāng)前最大連接數(shù)為1000；mysql重啟時(shí)失效,需要長期生效在my.ini 添加 max_connections=1000

從業(yè)務(wù)使用場景出發(fā)，根據(jù)RDS套餐類型和線上實(shí)際訪問流量，來衡量性能指標(biāo)，以便方便對標(biāo)實(shí)際業(yè)務(wù)場景。

MySQL 5.7.21 Group Replication

MySQL 5.7.21 Group Replication with Consistent Read

同機(jī)房3節(jié)點(diǎn)、跨機(jī)房3節(jié)點(diǎn)

網(wǎng)絡(luò)異常：長時(shí)間延時(shí)0.5ms，長時(shí)間延時(shí)2ms，丟包0.01%

場景1、2的差異可以衡量 跨機(jī)房網(wǎng)絡(luò) 帶來的 性能損耗

場景3關(guān)注在 網(wǎng)絡(luò)質(zhì)量變化 時(shí)帶來的 性能變化

同機(jī)房3節(jié)點(diǎn)為 05 06 03????跨機(jī)房3節(jié)點(diǎn)為 05 06 01

機(jī)器部署：同IDC3臺（永順ys 03 05 06），跨IDC1臺（廣州gz 01）

同IDC RTT（06-05）：RTT min/avg/max/mdev =?0.051/0.059/0.070/0.010?ms

跨IDC RTT（01-05）：RTT min/avg/max/mdev =?0.739/0.749/0.810/0.027

跨IDC的網(wǎng)絡(luò)耗時(shí)是同 IDC的1.3倍 ，在設(shè)置 延遲0.5ms后 的網(wǎng)絡(luò)質(zhì)量：

同IDC RTT（06-05）：RTT min/avg/max/mdev =?0.507/0.564/0.617/0.037

跨IDC RTT（01-05）：RTT min/avg/max/mdev =?1.199/1.248/1.315/0.046

跨IDC的網(wǎng)絡(luò)耗時(shí)是 同IDC的2.2倍 ，在設(shè)置 延遲2ms后 的網(wǎng)絡(luò)質(zhì)量：

同IDC RTT（06-05）：RTT min/avg/max/mdev = 1.963/2.054/2.161/0.064 ms

跨IDC RTT（01-05）：RTT?min/avg/max/mdev = 2.642/2.732/2.835/0.076 ms

參考：;aliyun

如何查看 mysql 性能瓶頸

通過sysbench的oltp_read_write測試來模擬業(yè)務(wù)壓力、以此來給指定的硬件環(huán)境配置一份比較合理的MySQL配置文件。

環(huán)境介紹

硬件配置

請點(diǎn)擊輸入圖片描述

軟件環(huán)境

請點(diǎn)擊輸入圖片描述

優(yōu)化層級與指導(dǎo)思想

優(yōu)化層級

MySQL數(shù)據(jù)庫優(yōu)化可以在多個不同的層級進(jìn)行，常見的有：

SQL優(yōu)化

參數(shù)優(yōu)化

架構(gòu)優(yōu)化

本文重點(diǎn)關(guān)注：參數(shù)優(yōu)化

指導(dǎo)思想

日志先行 -- 一個事務(wù)能否成功提交的關(guān)鍵是日志是否成功落盤，與數(shù)據(jù)沒有太大的關(guān)系；也就是說對寫的優(yōu)化可以表述為各方面的資源向?qū)懖僮鲀A斜。

瓶頸分析 -- 通過show global status 的各個計(jì)數(shù)器的值基本上就能分析出當(dāng)前瓶頸所在，再結(jié)合一些簡單的系統(tǒng)層面的監(jiān)控工具如top iostat 就能明確瓶頸。

整體性能是“讀”“寫”之間的再平衡。

網(wǎng)站欄目：MySQL怎么查瓶頸 mysql查詢性能瓶頸
分享鏈接：http://www.muchs.cn/article36/dohchsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供響應(yīng)式網(wǎng)站、ChatGPT、虛擬主機(jī)、網(wǎng)站設(shè)計(jì)、關(guān)鍵詞優(yōu)化、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)