mysql查詢瓶頸怎么辦,mysql性能測(cè)試瓶頸及調(diào)優(yōu)

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

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

專業(yè)成都網(wǎng)站建設(shè)公司，做排名好的好網(wǎng)站，排在同行前面，為您帶來(lái)客戶和效益!成都創(chuàng)新互聯(lián)公司為您提供成都網(wǎng)站建設(shè)，五站合一網(wǎng)站設(shè)計(jì)制作，服務(wù)好的網(wǎng)站設(shè)計(jì)公司，成都做網(wǎng)站、網(wǎng)站設(shè)計(jì)負(fù)責(zé)任的成都網(wǎng)站制作公司!

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

2. 優(yōu)化

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

2.1 軟優(yōu)化

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

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

2.例:

顯示:

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

2.1.2 優(yōu)化子查詢

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

2.1.3 使用索引

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

2.1.4 分解表

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

2.1.5 中間表

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

2.1.6 增加冗余字段

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

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

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

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

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

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

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

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

2.2 硬優(yōu)化

2.2.1 硬件三件套

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

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

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

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

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

2.2.3 分庫(kù)分表

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

2.2.4 緩存集群

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

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

mysql+springboot+jpa查詢幾十萬(wàn)條數(shù)據(jù)很慢 如何解決？

將查詢語(yǔ)句放到服務(wù)器命令行去跑，如果慢，則可以考慮通過(guò)添加索引來(lái)提高查詢速度。

如已有索引或添加索引后查詢速度仍未改善，查看語(yǔ)句執(zhí)行計(jì)劃中，是全表掃描還是走索引。如果走了索引，那就可能考慮是服務(wù)器性能瓶頸或數(shù)據(jù)庫(kù)設(shè)置問(wèn)題，涉及的設(shè)置項(xiàng)比較多，你沒(méi)有提供相關(guān)信息，無(wú)法繼續(xù)提供優(yōu)化建議。如果沒(méi)有走索引，檢查語(yǔ)法（查詢條件添加函數(shù)不走索引）和表屬性（關(guān)聯(lián)表字符集不統(tǒng)一不走索引）。

如果服務(wù)器本地快，但頁(yè)面查詢慢，那就排除了性能問(wèn)題，考慮網(wǎng)絡(luò)問(wèn)題與頁(yè)面查詢語(yǔ)句調(diào)用的驅(qū)動(dòng)模塊是否有問(wèn)題。檢測(cè)網(wǎng)絡(luò)連接速度，如慢嘗試更換網(wǎng)線。網(wǎng)絡(luò)連接速度正常，則嘗試更換調(diào)用的驅(qū)動(dòng)包，重新下一個(gè)或換一個(gè)版本。

mysql支持幾十萬(wàn)的數(shù)據(jù)，響應(yīng)速度應(yīng)該是毫秒級(jí)的。

看了下你的語(yǔ)句，不要用IN了，改INNER JOIN吧，套那么多層IN，肯定沒(méi)效率。

如果mysql里面的數(shù)據(jù)過(guò)多，查詢太慢怎么辦？

問(wèn)題

我們有一個(gè) SQL，用于找到?jīng)]有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運(yùn)行特別慢，怎么辦？

實(shí)驗(yàn)

我們搭建一個(gè) MySQL 5.7 的環(huán)境，此處省略搭建步驟。

寫個(gè)簡(jiǎn)單的腳本，制造一批帶主鍵和不帶主鍵的表：

執(zhí)行一下腳本：

現(xiàn)在執(zhí)行以下 SQL 看看效果：

...

執(zhí)行了 16.80s，感覺(jué)是非常慢了。

現(xiàn)在用一下 DBA 三板斧，看看執(zhí)行計(jì)劃：

感覺(jué)有點(diǎn)慘，由于 information_schema.columns 是元數(shù)據(jù)表，沒(méi)有必要的統(tǒng)計(jì)信息。

那我們來(lái) show warnings 看看 MySQL 改寫后的 SQL：

我們格式化一下 SQL：

可以看到 MySQL 將

select from A where A.x not in (select x from B) //非關(guān)聯(lián)子查詢

轉(zhuǎn)換成了

select from A where not exists (select 1 from B where B.x = a.x) //關(guān)聯(lián)子查詢

如果我們自己是 MySQL，在執(zhí)行非關(guān)聯(lián)子查詢時(shí)，可以使用很簡(jiǎn)單的策略：

select from A where A.x not in (select x from B where ...) //非關(guān)聯(lián)子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時(shí)表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時(shí)表 C 中的記錄進(jìn)行比對(duì)，直接在索引里比對(duì)，

而關(guān)聯(lián)子查詢就需要循環(huán)迭代：

select from A where not exists (select 1 from B where B.x = a.x and ...) //關(guān)聯(lián)子查詢掃描 A 表的每一條記錄 rA： ? ? 掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。

顯然，關(guān)聯(lián)子查詢的掃描成本會(huì)高于非關(guān)聯(lián)子查詢。

我們希望 MySQL 能先"緩存"子查詢的結(jié)果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認(rèn)為不緩存更快，我們就需要給予 MySQL 一定指導(dǎo)。

...

可以看到執(zhí)行時(shí)間變成了 0.67s。

整理

我們?cè)\斷的關(guān)鍵點(diǎn)如下：

\1. 對(duì)于 information_schema 中的元數(shù)據(jù)表，執(zhí)行計(jì)劃不能提供有效信息。

\2. 通過(guò)查看 MySQL 改寫后的 SQL，我們猜測(cè)了優(yōu)化器發(fā)生了誤判。

\3. 我們?cè)黾恿?hint，指導(dǎo) MySQL 正確進(jìn)行優(yōu)化判斷。

但目前我們的實(shí)驗(yàn)僅限于猜測(cè)，猜中了萬(wàn)事大吉，猜不中就無(wú)法做出好的診斷。

mysql在并發(fā)測(cè)試中遇到性能瓶頸，在線求幫助

1、使用行級(jí)別鎖，避免表級(jí)別或頁(yè)級(jí)別鎖

盡量使用支持行級(jí)別鎖的存儲(chǔ)引擎，如InnoDB;只在讀操作顯著多于寫作的場(chǎng)景中(如數(shù)據(jù)倉(cāng)庫(kù)類的應(yīng)用)使用表級(jí)別鎖的存儲(chǔ)引擎，如MyISAM;。

2、降低熱巨鎖(hot gaint lock)出現(xiàn)的可能性以盡可能避免全局互斥量

臨界區(qū)(僅允許單一線程訪問(wèn)的資源)會(huì)嚴(yán)重降低MySQL系統(tǒng)并發(fā)性;InnoDB緩沖池(buffer pool)、數(shù)據(jù)字典等都是常見(jiàn)的臨界區(qū);幸運(yùn)的是，新版本的InnoDB已經(jīng)能夠較好的運(yùn)行于多核處理器，支持使用 innodb_buffer_pool_instances服務(wù)器變量建立多個(gè)緩沖池實(shí)例，每個(gè)緩沖池實(shí)例分別自我管理空閑列表、列表刷寫、LRU以及其它跟緩沖池相關(guān)的數(shù)據(jù)結(jié)構(gòu)，并通過(guò)各自的互斥鎖進(jìn)行保護(hù)。

3、并行運(yùn)行多個(gè)I/O線程

通過(guò)innodb_io_capacity服務(wù)器變量等增加磁盤I/O線程的數(shù)量可以提高前端操作(如SELECT)的性能，不過(guò)，磁盤I/O線程的數(shù)量不應(yīng)該超過(guò)磁盤的IOPS(7200RPM的單塊硬件的IOPS數(shù)量一般為100個(gè)左右)。

此外，異步I/O也可以在一定程度上提高系統(tǒng)的并發(fā)能力，在Linux系統(tǒng)上，可以通過(guò)將MySQL的服務(wù)器變量innodb_use_native_aio的值設(shè)定為ON設(shè)定InnoDB可以使用Linux的異步I/O子系統(tǒng)。

4、并行后端任務(wù)

默認(rèn)情況下，MySQL的清寫(purge)操作(用于移除帶刪除標(biāo)記的記錄)由InnoDB的主線程完成，這可以降低內(nèi)部資源競(jìng)爭(zhēng)發(fā)生的概率，進(jìn)而增強(qiáng)MySQL服務(wù)伸縮能力。不過(guò)，隨著InnoDB內(nèi)部各式各樣的競(jìng)爭(zhēng)越來(lái)越多，這種設(shè)置帶來(lái)的性能優(yōu)勢(shì)已幾乎不值一提，因此，生產(chǎn)環(huán)境中應(yīng)該通過(guò)為innodb_purge_threads服務(wù)器變量設(shè)定為ON將主線程與清寫線程分開(kāi)運(yùn)行。

5、單線程復(fù)制模型中的SQL線程是一個(gè)熱區(qū)

在從服務(wù)器上并行運(yùn)行多個(gè)SQL線程可有效提高M(jìn)ySQL從服務(wù)器性能，MySQL 5.6支持多線程復(fù)制(每庫(kù)一個(gè)復(fù)制線程);

如何查看 mysql 性能瓶頸

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

環(huán)境介紹

硬件配置

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

軟件環(huán)境

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

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

優(yōu)化層級(jí)

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

SQL優(yōu)化

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

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

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

指導(dǎo)思想

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

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

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

當(dāng)前題目：mysql查詢瓶頸怎么辦,mysql性能測(cè)試瓶頸及調(diào)優(yōu)
本文地址：http://muchs.cn/article14/hcghde.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、網(wǎng)站維護(hù)、全網(wǎng)營(yíng)銷推廣、App開(kāi)發(fā)、網(wǎng)站內(nèi)鏈、微信公眾號(hào)

廣告

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