MySQL中慢SQL優(yōu)化的示例分析

這篇文章給大家分享的是有關(guān)MySQL中慢SQL優(yōu)化的示例分析的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。

SQL語句優(yōu)化

SQL語句的優(yōu)化，有很多文章說起，也有很多在SQL編寫上的指導(dǎo)；但是那種只能支持基本開發(fā)，如果要排查問題，那就不能單單的只是停留在SQL編寫上了，而是有一個(gè)整體的發(fā)現(xiàn)問題的流程。

本次優(yōu)化方向，大概分為發(fā)現(xiàn)慢查詢SQL，查看并解析SQL執(zhí)行計(jì)劃，SQL編寫上的優(yōu)化，索引優(yōu)化等幾個(gè)方面。

記錄慢查詢SQL

MySQL中記錄慢查詢SQL是可以利用MySQL內(nèi)部配置來實(shí)現(xiàn)的，這個(gè)配置就是slow_query_log配置。

可利用show variables like '%query%';查詢出以下三個(gè)相關(guān)結(jié)果。

long_query_time     | 1.00000
slow_query_log      | off
slow_query_log_file | /data/mysql/mysql_slow.log

解釋一下這三個(gè)參數(shù)，

• long_query_time：如何區(qū)分SQL查詢是慢查詢，就要規(guī)定一個(gè)查詢時(shí)間，超過這個(gè)時(shí)間的就歸類于慢查詢，此參數(shù)就是來設(shè)置時(shí)間范圍的；以秒為單位，可以設(shè)置小數(shù)。

• slow_query_log：此參數(shù)為是否開啟記錄慢查詢SQL的開關(guān)，兩個(gè)選擇，on或者off，默認(rèn)為off，所以在這里我們就知道如果要開啟慢查詢SQL記錄，需要手動(dòng)設(shè)置開啟。

• slow_query_log_file：慢查詢SQL日志的文件路徑，可以自行指定。
  

如何修改配置

有兩個(gè)方法。

其一：修改my.ini或者是my.cnf文件，將此三項(xiàng)配置進(jìn)行一個(gè)配置。

其二：直接在sqlplus中，使用set語法來修改參數(shù)，但是重啟mysql數(shù)據(jù)庫(kù)后就會(huì)失效，sql如下：

set global long_query_time = 10;

set global slow_query_log = on;

set global slow_query_log_file = /data/mysql/mysql_slow.log;

因?yàn)檫@個(gè)方法會(huì)重啟失效，所以還是建議使用第一種方式。

查看慢查詢?nèi)罩?/h5>

如何查詢慢查詢?nèi)罩灸兀绻亢苄〉那闆r下，其實(shí)是不需要使用工具的，完全可以直接打開即可。

如果量比較大，就需要mysqldumpslow工具查詢會(huì)更方便。

mysqldumpslow是和mysqld相同類型的執(zhí)行腳本，可以直接在命令行中執(zhí)行，具體的使用方法如下：

mysqldumpslow參數(shù)：

-s，是order的順序
-----al 平均鎖定時(shí)間
-----ar 平均返回記錄時(shí)間
-----at 平均查詢時(shí)間（默認(rèn)）
-----c 計(jì)數(shù)
-----l 鎖定時(shí)間
-----r 返回記錄
-----t 查詢時(shí)間

-t，top，即為返回前面多少條的數(shù)據(jù)
-g，自定義正則表達(dá)式

舉個(gè)例子，如下：

mysqldumpslow -s r -t 5 /data/mysql/mysql_slow.log

查詢出返回記錄集最多的5個(gè)慢查詢SQL。

更多用法之后我建個(gè)測(cè)試庫(kù)單獨(dú)寫篇文章細(xì)說一下。

查看SQL執(zhí)行計(jì)劃

查看執(zhí)行計(jì)劃關(guān)鍵詞：EXPLAIN

如何使用

就是直接執(zhí)行 EXPLAIN SELECT * FROM TABLE_NAME;

這個(gè)一開始我是打算簡(jiǎn)單說一下的，后來發(fā)現(xiàn)篇幅太長(zhǎng)了，這個(gè)留待下篇文章里，感謝理解。

SQL編寫優(yōu)化

SQL的編寫優(yōu)化就很多了，我這里也整理出了一些，請(qǐng)大家自行查漏補(bǔ)缺。

• 查詢語句無論是使用哪種判斷條件 等于、小于、大于, where左側(cè)的條件查詢字段不要使用函數(shù)或者表達(dá)式。

• 不要直接使用select *，而應(yīng)該使用具體需要查詢的表字段；select * 使用的是全表掃描，不會(huì)走索引的。

• 避免在 WHERE 字句中對(duì)字段進(jìn)行 NULL 判斷。

• 避免在 WHERE 中使用 != 或 <> 操作符。

• 使 用 BETWEEN AND 替代 IN。

• 為常用搜索條件創(chuàng)建索引

• 選擇正確的存儲(chǔ)引擎, InnoDB 、MyISAM 、MEMORY 等，不同的場(chǎng)景下使用不同的存儲(chǔ)引擎會(huì)有更好的效果。

• 使用 like %123% 不會(huì)走索引, 而使用 like 123% 會(huì)走索引。非常重要！??！

• 選擇合適的字段類型。

• 設(shè)計(jì)字段時(shí)，要盡量使用NOT NULL。

為何要對(duì)慢SQL進(jìn)行治理

從數(shù)據(jù)庫(kù)角度看：每個(gè)SQL執(zhí)行都需要消耗一定I/O資源，SQL執(zhí)行的快慢，決定資源被占用時(shí)間的長(zhǎng)短。假設(shè)總資源是100，有一條慢SQL占用了30的資源共計(jì)1分鐘。那么在這1分鐘時(shí)間內(nèi)，其他SQL能夠分配的資源總量就是70，如此循環(huán)，當(dāng)資源分配完的時(shí)候，所有新的SQL執(zhí)行將會(huì)排隊(duì)等待。

從應(yīng)用的角度看：SQL執(zhí)行時(shí)間長(zhǎng)意味著等待，在OLTP應(yīng)用當(dāng)中，用戶的體驗(yàn)較差

治理的優(yōu)先級(jí)上

• master數(shù)據(jù)庫(kù)->slave數(shù)據(jù)庫(kù)

• 目前數(shù)據(jù)庫(kù)基本上都是讀寫分離架構(gòu)，讀在從庫(kù)（slave）上執(zhí)行，寫在主庫(kù)（master）上執(zhí)行。

• 由于從庫(kù)的數(shù)據(jù)都是從主庫(kù)上復(fù)制過去的，主庫(kù)等待較多的，會(huì)加大與從庫(kù)的復(fù)制時(shí)延。

• 執(zhí)行次數(shù)多的SQL優(yōu)先治理

• 如果有一類SQL高并發(fā)集中訪問某一張表，應(yīng)當(dāng)優(yōu)先治理。

感謝各位的閱讀！關(guān)于“MySQL中慢SQL優(yōu)化的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！