前言

MySQL性能優(yōu)化是一個(gè)老生常談的問題，無論是在實(shí)際工作中還是面試中，都不可避免遇到相應(yīng)的場(chǎng)景，下面博主就總結(jié)一些能夠幫助大家解決這個(gè)問題的小技巧。

SQL優(yōu)化之前需要確認(rèn)哪些SQL需要優(yōu)化，這時(shí)就需要引起SQL性能分析工具，主要優(yōu)化的是查詢語句。

SQL的執(zhí)行頻率

SQL性能優(yōu)化一般是針對(duì)查詢語句，所以在定位是否需要優(yōu)化之前，可以先確認(rèn)表的更刪查改的一個(gè)執(zhí)行頻率對(duì)比，如果是查詢占主導(dǎo)地位，則可以一步排查。

MySQL支持客戶端通過show [session|global] status命令對(duì)服務(wù)器狀態(tài)進(jìn)行查詢。

查看執(zhí)行頻率方式：

show global status like ‘com_______’(7個(gè)下劃線,表示后面會(huì)有7個(gè)字符)

慢查詢?nèi)罩?/h2>

確認(rèn)了SQL的執(zhí)行頻率，則需要通過慢查詢?nèi)罩具M(jìn)行進(jìn)一步定位哪些SQL語句執(zhí)行時(shí)間占用較長(zhǎng)。

慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過指定參數(shù)(long_query_time，單位:秒，默認(rèn)是10s)的所有SQL語句的日志。

默認(rèn)情況下，慢查詢?nèi)罩臼菦]有開啟的，需要在MySQL的配置文件(linux下為:/etc/my.cnf)中配置如下指令：

• 查詢服務(wù)端是否開啟慢查詢?nèi)罩荆簊how variables like 'slow_query_log';
• 在mysql的配置文件中添加如下配置啟動(dòng)：
• slow_query_log=1;開啟mysql慢日志查詢開關(guān)
• long_query_time=xx;設(shè)置慢日志時(shí)間，只要SQL執(zhí)行時(shí)間查過該值，則視為慢查詢，記錄在慢日志中。
• 配置完成后重啟mysql服務(wù)端
• linux中mysql的慢日志文件在: /var/lib/mysql/localhost-slow.log

window可以在my.ini文件中配置具體的地址

Query_time SQL執(zhí)行的時(shí)間,越長(zhǎng)則越慢

Lock_time 在MySQL服務(wù)器階段(不是在存儲(chǔ)引擎階段)等待表鎖時(shí)間

Rows_sent 查詢返回的行數(shù)

Rows_examined 查詢檢查的行數(shù)

show profiles詳情分析

通過慢查詢?nèi)罩?，我們可以定位到超過設(shè)置閾值的慢SQL，但是實(shí)際業(yè)務(wù)中，這并不能完全具有代表性，因?yàn)殚撝凳侵饔^設(shè)置的，可能有大量執(zhí)行時(shí)間低于閾值的SQL也存在問題，因此慢日志SQL并不能完全定位出所有的慢SQL。

show profiles 能夠讓我們了解到SQL執(zhí)行時(shí)時(shí)間都耗費(fèi)到哪里了。 通過have_profiling參數(shù)，可以查看mysql是否支持該profile操作。

格式: select @@have_profiling;

默認(rèn)情況下，profiling是關(guān)閉的，可以同set指令開啟session|global級(jí)別的profiling。

格式: set global | session profiling = 1;

優(yōu)化方案：

• show profiles; 查看每一條SQL的耗時(shí)基本情況
• show profile for query query_id; 查詢指定query_id的SQL語句各個(gè)階段的耗時(shí)情況
• show profile cpu for query query_id; 查詢指定query_id的SQL語句cpu使用情況

explain執(zhí)行計(jì)劃

前面介紹的幾種方式都是通過執(zhí)行時(shí)間長(zhǎng)短來判斷SQL語句執(zhí)行的性能好壞，但是這個(gè)相對(duì)來說是比較片面的，想要更全面地評(píng)判SQL語句好壞，則需要使用explain查看SQL的執(zhí)行計(jì)劃。

Explain或者DESC命令獲取MySQL如何執(zhí)行SELECT語句的信息，包括在SELECT語句過程中表如何連接和連接的順序。

語法：explain | desc select xxxx...

1、ID參數(shù)

select中的查詢序號(hào)，表示的是查詢中執(zhí)行select子句或者是操作表的順序(id相同，執(zhí)行順序從上往下，id不同，值越大，越先執(zhí)行)

2、select_type參數(shù)

表示select查詢類型，常見的有SIMPLE(簡(jiǎn)單表，即不使用表連接或者子查詢)、primary(主查詢，即外層查詢)、UNION(UNION中的第二個(gè)或者后面的查詢語句)、SUBQUERY(SELECT/WHERE之后包含了子查詢)

3、type參數(shù)

表示連接/訪問類型，性能由好到差的連接類型為：null、system、const、eq_ref、ref、range、index、all

在優(yōu)化的時(shí)候，盡量將type往前優(yōu)化，最差也要為index

• null：查詢的時(shí)候不訪問任何表，如:select "1"
• system：當(dāng)訪問一些系統(tǒng)表的時(shí)候會(huì)出現(xiàn)
• const：根據(jù)主鍵或者唯一索引訪問時(shí)，會(huì)出現(xiàn)const
• eq_ref：待確認(rèn)
• ref：使用非唯一性索引進(jìn)行訪問時(shí)，可能出現(xiàn)ref
• range：
• index：使用到了索引，但是對(duì)整個(gè)索引都進(jìn)行了遍歷，性能也比較差
• all：全表掃描，性能最差

4、possible_key參數(shù)：顯示在執(zhí)行查詢時(shí)，表中可能被使用到的索引，一個(gè)或者多個(gè)、

5、key參數(shù)：在執(zhí)行查詢時(shí)，實(shí)際上會(huì)命中的索引

6、key_len參數(shù)：表示使用到的索引的字節(jié)數(shù)，該值為索引字段最大可能長(zhǎng)度，在不損失精確性的前提下，長(zhǎng)度越短越好。

7、rows參數(shù)：MySQL認(rèn)為必須要執(zhí)行查詢的行數(shù)，在idb引擎表中，是一個(gè)估計(jì)值，可能并不總是準(zhǔn)確的

8、ref參數(shù)：待確認(rèn)？

9、filtered參數(shù)：表示查詢返回的行數(shù)占總讀取行數(shù)的百分比，值越大越好

10、extra參數(shù)：額外的一些執(zhí)行信息如排序

以上就是MySQL數(shù)據(jù)庫查詢性能優(yōu)化的4個(gè)技巧干貨的詳細(xì)內(nèi)容，更多關(guān)于MySQL查詢性能優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論