目錄
- 前言
- SQL語句優(yōu)化
- 記錄慢查詢SQL
- 查看SQL執(zhí)行計(jì)劃
- SQL編寫優(yōu)化
- 為何要對慢SQL進(jìn)行治理
- 總結(jié)
前言
影響一個(gè)系統(tǒng)的運(yùn)行速度的原因有很多,是多方面的,甚至可能是偶然性的,或前端,或后端,或數(shù)據(jù)庫,或中間件,或服務(wù)器,或網(wǎng)絡(luò)等等等等,真正的去定位一個(gè)問題需要對系統(tǒng)有一定的認(rèn)知,可以根據(jù)自身的判斷去縮小問題范圍。
今天不說其他的優(yōu)化,單獨(dú)把數(shù)據(jù)庫的優(yōu)化拿出來說幾個(gè)優(yōu)化方向。
跟系統(tǒng)的優(yōu)化方向一樣,數(shù)據(jù)庫的優(yōu)化,同樣也是多方面的,其中涵蓋著SQL語句的執(zhí)行情況,數(shù)據(jù)庫自身的情況等等,下面我們就來說一下MySQL數(shù)據(jù)庫中的慢SQL語句優(yōu)化方向,希望也能給到大家一些優(yōu)化思路。
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ù)庫后就會失效,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è)方法會重啟失效,所以還是建議使用第一種方式。
查看慢查詢?nèi)罩?/h4>
如何查詢慢查詢?nèi)罩灸兀绻亢苄〉那闆r下,其實(shí)是不需要使用工具的,完全可以直接打開即可。
如果量比較大,就需要mysqldumpslow工具查詢會更方便。
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è)測試庫單獨(dú)寫篇文章細(xì)說一下。
查看SQL執(zhí)行計(jì)劃
查看執(zhí)行計(jì)劃關(guān)鍵詞:EXPLAIN
如何使用
就是直接執(zhí)行 EXPLAIN SELECT * FROM TABLE_NAME;
這個(gè)一開始我是打算簡單說一下的,后來發(fā)現(xiàn)篇幅太長了,這個(gè)留待下篇文章里,感謝理解。
SQL編寫優(yōu)化
SQL的編寫優(yōu)化就很多了,我這里也整理出了一些,請大家自行查漏補(bǔ)缺。
- 查詢語句無論是使用哪種判斷條件 等于、小于、大于, where左側(cè)的條件查詢字段不要使用函數(shù)或者表達(dá)式。
- 不要直接使用select *,而應(yīng)該使用具體需要查詢的表字段;select * 使用的是全表掃描,不會走索引的。
- 避免在 WHERE 字句中對字段進(jìn)行 NULL 判斷。
- 避免在 WHERE 中使用 != 或 > 操作符。
- 使 用 BETWEEN AND 替代 IN。
- 為常用搜索條件創(chuàng)建索引
- 選擇正確的存儲引擎, InnoDB 、MyISAM 、MEMORY 等,不同的場景下使用不同的存儲引擎會有更好的效果。
- 使用 like %123% 不會走索引, 而使用 like 123% 會走索引。非常重要?。?!
- 選擇合適的字段類型。
- 設(shè)計(jì)字段時(shí),要盡量使用NOT NULL。
為何要對慢SQL進(jìn)行治理
從數(shù)據(jù)庫角度看:每個(gè)SQL執(zhí)行都需要消耗一定I/O資源,SQL執(zhí)行的快慢,決定資源被占用時(shí)間的長短。假設(shè)總資源是100,有一條慢SQL占用了30的資源共計(jì)1分鐘。那么在這1分鐘時(shí)間內(nèi),其他SQL能夠分配的資源總量就是70,如此循環(huán),當(dāng)資源分配完的時(shí)候,所有新的SQL執(zhí)行將會排隊(duì)等待。
從應(yīng)用的角度看:SQL執(zhí)行時(shí)間長意味著等待,在OLTP應(yīng)用當(dāng)中,用戶的體驗(yàn)較差
治理的優(yōu)先級上
- master數(shù)據(jù)庫->slave數(shù)據(jù)庫
- 目前數(shù)據(jù)庫基本上都是讀寫分離架構(gòu),讀在從庫(slave)上執(zhí)行,寫在主庫(master)上執(zhí)行。
- 由于從庫的數(shù)據(jù)都是從主庫上復(fù)制過去的,主庫等待較多的,會加大與從庫的復(fù)制時(shí)延。
- 執(zhí)行次數(shù)多的SQL優(yōu)先治理
- 如果有一類SQL高并發(fā)集中訪問某一張表,應(yīng)當(dāng)優(yōu)先治理。
總結(jié)
這里面遠(yuǎn)遠(yuǎn)還沒有講全,還有很多種編寫規(guī)則,同時(shí)還有索引的建立并沒有聊,留給大家一些自己看書的時(shí)間,希望大家有所進(jìn)步。
到此這篇關(guān)于MySQL中慢SQL優(yōu)化方向的文章就介紹到這了,更多相關(guān)MySQL慢SQL優(yōu)化方向內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
您可能感興趣的文章:- 淺談MySQL之select優(yōu)化方案
- Mysql縱表轉(zhuǎn)換為橫表的方法及優(yōu)化教程
- MySQL千萬級數(shù)據(jù)表的優(yōu)化實(shí)戰(zhàn)記錄
- MySql子查詢IN的執(zhí)行和優(yōu)化的實(shí)現(xiàn)
- 帶你快速搞定Mysql優(yōu)化
- mysql 數(shù)據(jù)插入優(yōu)化方法之concurrent_insert
- mysql優(yōu)化之query_cache_limit參數(shù)說明
- MySQL優(yōu)化之如何寫出高質(zhì)量sql語句
- mysql查詢優(yōu)化之100萬條數(shù)據(jù)的一張表優(yōu)化方案
- MYSQL 的10大經(jīng)典優(yōu)化案例場景實(shí)戰(zhàn)