一般傳統(tǒng)互聯(lián)網(wǎng)公司很少接觸到 SQL 優(yōu)化問題,其原因是數(shù)據(jù)量小,大部分廠商的數(shù)據(jù)庫性能能夠滿足日常的業(yè)務(wù)需求荐吉,所以不需要進(jìn)行 SQL 優(yōu)化,但是隨著應(yīng)用程序的不斷變大口渔,數(shù)據(jù)量的激增样屠,數(shù)據(jù)庫自身的性能跟不上了,此時就需要從 SQL 自身角度來進(jìn)行優(yōu)化,這也是我們這篇文章所討論的痪欲。
SQL 優(yōu)化步驟
當(dāng)面對一個需要優(yōu)化的 SQL 時悦穿,我們有哪幾種排查思路呢?
1业踢、通過 show status 命令了解 SQL 執(zhí)行次數(shù)
首先栗柒,我們可以使用 show status 命令查看服務(wù)器狀態(tài)信息。show status 命令會顯示每個服務(wù)器變量 variable_name 和 value知举,狀態(tài)變量是只讀的傍衡。如果使用 SQL 命令,可以使用 like 或者 where 條件來限制結(jié)果负蠕。like 可以對變量名做標(biāo)準(zhǔn)模式匹配蛙埂。
圖我沒有截全,下面還有很多變量遮糖,讀者可以自己嘗試一下绣的。也可以在操作系統(tǒng)上使用 mysqladmin extended-status 命令來獲取這些消息。
但是我執(zhí)行 mysqladmin extended-status 后欲账,出現(xiàn)這個錯誤屡江。
應(yīng)該是我沒有輸入密碼的原因,使用 **mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status **后赛不,問題解決惩嘉。
這里需要注意一下 show status 命令中可以添加統(tǒng)計結(jié)果的級別,這個級別有兩個:
session 級:默認(rèn)當(dāng)前鏈接的統(tǒng)計結(jié)果
global 級:自數(shù)據(jù)庫上次啟動到現(xiàn)在的統(tǒng)計結(jié)果
如果不指定統(tǒng)計結(jié)果級別的話踢故,默認(rèn)使用 session 級別文黎。
對于 show status 查詢出來的統(tǒng)計結(jié)果,有兩類參數(shù)需要注意下殿较,一類是以 Com_ 為開頭的參數(shù)耸峭,一類是以 Innodb_ 為開頭的參數(shù)。
下面是 Com_ 為開頭的參數(shù)淋纲,參數(shù)很多劳闹,我同樣沒有截全。
Com_xxx 表示的是每個 xxx 語句執(zhí)行的次數(shù)洽瞬,我們通常關(guān)心的是 select 本涕、insert 、update伙窃、delete 語句的執(zhí)行次數(shù)菩颖,即
Com_select:執(zhí)行 select 操作的次數(shù),一次查詢會使結(jié)果 + 1对供。
Com_insert:執(zhí)行 INSERT 操作的次數(shù)位他,對于批量插入的 INSERT 操作氛濒,只累加一次。
Com_update:執(zhí)行 UPDATE 操作的次數(shù)鹅髓。
Com_delete:執(zhí)行 DELETE 操作的次數(shù)舞竿。
以 Innodb_ 為開頭的參數(shù)主要有:
Innodb_rows_read:執(zhí)行 select 查詢返回的行數(shù)。
Innodb_rows_inserted:執(zhí)行 INSERT 操作插入的行數(shù)窿冯。
Innodb_rows_updated:執(zhí)行 UPDATE 操作更新的行數(shù)骗奖。
Innodb_rows_deleted:執(zhí)行 DELETE 操作刪除的行數(shù)。
通過上面這些參數(shù)執(zhí)行結(jié)果的統(tǒng)計醒串,我們能夠大致了解到當(dāng)前數(shù)據(jù)庫是以更新(包括插入执桌、刪除)為主還是查詢?yōu)橹鳌?/p>
除此之外,還有一些其他參數(shù)用于了解數(shù)據(jù)庫的基本情況芜赌。
Connections:查詢 MySQL 數(shù)據(jù)庫的連接次數(shù)仰挣,這個次數(shù)是不管連接是否成功都算上。
Uptime:服務(wù)器的工作時間缠沈。
Slow_queries:滿查詢次數(shù)膘壶。
Threads_connected:查看當(dāng)前打開的連接的數(shù)量。
下面這個博客匯總了幾乎所有 show status 的參數(shù)洲愤,可以當(dāng)作參考手冊:https://blog.csdn.net/ayay_870621/article/details/88633092
2颓芭、定位執(zhí)行效率較低的 SQL
定位執(zhí)行效率比較慢的 SQL 語句,一般有兩種方式:
- 可以通過慢查詢?nèi)罩緛矶ㄎ荒男﹫?zhí)行效率較低的 SQL 語句柬赐。
MySQL 中提供了一個慢查詢的日志記錄功能亡问,可以把查詢 SQL 語句時間大于多少秒的語句寫入慢查詢?nèi)罩荆粘>S護(hù)中可以通過慢查詢?nèi)罩镜挠涗浶畔⒖焖贉?zhǔn)確地判斷問題所在肛宋。用 --log-slow-queries 選項啟動時州藕,mysqld 會寫一個包含所有執(zhí)行時間超過 long_query_time 秒的 SQL 語句的日志文件,通過查看這個日志文件定位效率較低的 SQL 悼吱。
比如我們可以在 my.cnf 中添加如下代碼慎框,然后退出重啟 MySQL。
log-slow-queries = /tmp/mysql-slow.log
通常我們設(shè)置最長的查詢時間是 2 秒后添,表示查詢時間超過 2 秒就記錄了,通常情況下 2 秒就夠了薪丁,然而對于很多 WEB 應(yīng)用來說遇西,2 秒時間還是比較長的。
也可以通過命令來開啟:
我們先查詢 MySQL 慢查詢?nèi)罩臼欠耖_啟
show variables like "%slow%";
啟用慢查詢?nèi)罩?/p>
set global slow_query_log='ON';
然后再次查詢慢查詢是否開啟
如圖所示严嗜,我們已經(jīng)開啟了慢查詢?nèi)罩尽?/p>
慢查詢?nèi)罩緯诓樵兘Y(jié)束以后才記錄粱檀,所以在應(yīng)用反應(yīng)執(zhí)行效率出現(xiàn)問題的時候慢查詢?nèi)罩静⒉荒芏ㄎ粏栴},此時應(yīng)該使用** show processlist 命令查看當(dāng)前 MySQL 正在進(jìn)行的線程漫玄。包括線程的狀態(tài)茄蚯、是否鎖表等压彭,可以實時的查看 SQL 執(zhí)行情況。同樣渗常,使用mysqladmin processlist**語句也能得到此信息壮不。
下面就來解釋一下各個字段對應(yīng)的概念:
Id :Id 就是一個標(biāo)示,在我們使用 kill 命令殺死進(jìn)程的時候很有用皱碘,比如 kill 進(jìn)程號询一。
User:顯示當(dāng)前的用戶,如果不是 root癌椿,這個命令就只顯示你權(quán)限范圍內(nèi)的 SQL 語句健蕊。
Host:顯示 IP ,用于追蹤問題
Db:顯示這個進(jìn)程目前連接的是哪個數(shù)據(jù)庫踢俄,為 null 是還沒有 select 數(shù)據(jù)庫缩功。
Command:顯示當(dāng)前連接鎖執(zhí)行的命令,一般有三種:查詢 query都办,休眠 sleep嫡锌,連接 connect。
Time:這個狀態(tài)持續(xù)的時間脆丁,單位是秒
State:顯示當(dāng)前 SQL 語句的狀態(tài)世舰,非常重要,下面會具體解釋槽卫。
Info:顯示這個 SQL 語句跟压。
State 列非常重要,關(guān)于這個列的內(nèi)容比較多歼培,讀者可以參考一下這篇文章:
https://blog.csdn.net/weixin_34357436/article/details/91768402
這里面涉及線程的狀態(tài)震蒋、是否鎖表等選項,可以實時的查看 SQL 的執(zhí)行情況躲庄,同時對一些鎖表進(jìn)行優(yōu)化查剖。
3、通過 EXPLAIN 命令分析 SQL 的執(zhí)行計劃
通過以上步驟查詢到效率低的 SQL 語句后噪窘,可以通過 EXPLAIN 或者 DESC 命令獲取 MySQL 如何執(zhí)行 SELECT 語句的信息笋庄,包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序。
比如我們使用下面這條 SQL 語句來分析一下執(zhí)行計劃
explain select * from test1;
上表中涉及內(nèi)容如下
- select_type:表示常見的 SELECT 類型倔监,常見的有 SIMPLE直砂,SIMPLE 表示的是簡單的 SQL 語句,不包括 UNION 或者子查詢操作浩习,比如下面這段就是 SIMPLE 類型静暂。
PRIMARY ,查詢中最外層的 SELECT(如兩表做 UNION 或者存在子查詢的外層的表操作為 PRIMARY谱秽,內(nèi)層的操作為 UNION)洽蛀,比如下面這段子查詢摹迷。
UNION,在 UNION 操作中郊供,查詢中處于內(nèi)層的 SELECT(內(nèi)層的 SELECT 語句與外層的 SELECT 語句沒有依賴關(guān)系時)峡碉。
SUBQUERY:子查詢中首個SELECT(如果有多個子查詢存在),如我們上面的查詢語句颂碘,子查詢第一個是 sr(sys_role)表异赫,所以它的 select_type 是 SUBQUERY。
table 头岔,這個選項表示輸出結(jié)果集的表塔拳。
type,這個選項表示表的連接類型峡竣,這個選項很有深入研究的價值靠抑,因為很多 SQL 的調(diào)優(yōu)都是圍繞 type 來講的,但是這篇文章我們主要圍繞優(yōu)化方式來展開的适掰,type 這個字段我們暫時作為了解颂碧,這篇文章不過多深入。
type 這個字段會牽扯到連接的性能类浪,它的不同類型的性能由好到差分別是
system :表中僅有一條數(shù)據(jù)時载城,該表的查詢就像查詢常量表一樣。
const :當(dāng)表中只有一條記錄匹配時费就,比如使用了表主鍵(primary key)或者表唯一索引(unique index)進(jìn)行查詢诉瓦。
eq-ref :表示多表連接時使用表主鍵或者表唯一索引,比如
select A.text, B.text where A.ID = B.ID
這個查詢語句力细,對于 A 表中的每一個 ID 行睬澡,B 表中都只能有唯一的 B.Id 來進(jìn)行匹配時。
ref :這個類型不如上面的 eq-ref 快眠蚂,因為它表示的是因為對于表 A 中掃描的每一行煞聪,表 C 中有幾個可能的行,C.ID 不是唯一的逝慧。
ref_or_null :與 ref 類似昔脯,只不過這個選項包含對 NULL 的查詢。
index_merge :查詢語句使用了兩個以上的索引笛臣,比如經(jīng)常在有 and 和 or 關(guān)鍵字出現(xiàn)的場景栅干,但是在由于讀取索引過多導(dǎo)致其性能有可能還不如 range(后面說)。
unique_subquery :這個選項經(jīng)常用在 in 關(guān)鍵字后面捐祠,子查詢帶有 where 關(guān)鍵字的子查詢中,用 sql 來表示就是這樣
value IN (SELECT primary_key FROM single_table WHERE some_expr)
range :索引范圍查詢桑李,常見于使用 =踱蛀,<>窿给,>,>=率拒,<崩泡,<=猬膨,IS NULL角撞,<=>,BETWEEN勃痴,IN() 或者 like 等運算符的查詢中谒所。
index :索引全表掃描,把索引從頭到尾掃一遍沛申。
all :這個我們接觸的最多了劣领,就是全表查詢,select * from xxx 铁材,性能最差尖淘。
上面就是 type 內(nèi)容的大致解釋,關(guān)于 type 我們經(jīng)常會在 SQL 調(diào)優(yōu)的環(huán)節(jié)使用 explain 分析其類型著觉,然后改進(jìn)查詢方式村生,越靠近 system 其查詢效率越高,越靠近 all 其查詢效率越低饼丘。
possible_keys :表示查詢時趁桃,可能使用的索引。
key :表示實際使用的索引葬毫。
key_len :索引字段的長度镇辉。
rows :掃描行的數(shù)量。
filtered :通過查詢條件查詢出來的 SQL 數(shù)量占用總行數(shù)的比例贴捡。
extra :執(zhí)行情況的描述忽肛。
通過上面的分析,我們可以大致確定 SQL 效率低的原因烂斋,一種非常有效的提升 SQL 查詢效率的方式就是使用索引屹逛,接下來我會講解一下如何使用索引提高查詢效率。
索引
索引是數(shù)據(jù)庫優(yōu)化中最常用也是最重要的手段汛骂,通過使用不同的索引可以解決大多數(shù) SQL 性能問題罕模,也是面試經(jīng)常會問到的優(yōu)化方式,圍繞著索引帘瞭,面試官能讓你造出火箭來淑掌,所以總結(jié)一點就是索引非常非常重!要蝶念!不只是使用抛腕,你還要懂其原芋绸!理!
1担敌、索引介紹
索引的目的就是用于快速查找某一列的數(shù)據(jù)摔敛,對相關(guān)數(shù)據(jù)列使用索引能夠大大提高查詢操作的性能。不使用索引全封,MySQL 必須從第一條記錄開始讀完整個表马昙,直到找出相關(guān)的行,表越大查詢數(shù)據(jù)所花費的時間就越多刹悴。如果表中查詢的列有索引行楞,MySQL 能夠快速到達(dá)一個位置去搜索數(shù)據(jù)文件,而不必查看所有數(shù)據(jù)颂跨,那么將會節(jié)省很大一部分時間敢伸。
2、索引分類
先來了解一下索引都有哪些分類恒削。
全局索引(FULLTEXT):全局索引池颈,目前只有 MyISAM 引擎支持全局索引,它的出現(xiàn)是為了解決針對文本的模糊查詢效率較低的問題钓丰,并且只限于 CHAR躯砰、VARCHAR 和 TEXT 列。
哈希索引(HASH):哈希索引是 MySQL 中用到的唯一 key-value 鍵值對的數(shù)據(jù)結(jié)構(gòu)携丁,很適合作為索引琢歇。HASH 索引具有一次定位的好處,不需要像樹那樣逐個節(jié)點查找梦鉴,但是這種查找適合應(yīng)用于查找單個鍵的情況李茫,對于范圍查找,HASH 索引的性能就會很低肥橙。默認(rèn)情況下魄宏,MEMORY 存儲引擎使用 HASH 索引,但也支持 BTREE 索引存筏。
B-Tree 索引:B 就是 Balance 的意思宠互,BTree 是一種平衡樹,它有很多變種椭坚,最常見的就是 B+ Tree予跌,它被 MySQL 廣泛使用。
R-Tree 索引:R-Tree 在 MySQL 很少使用善茎,僅支持 geometry 數(shù)據(jù)類型券册,支持該類型的存儲引擎只有MyISAM、BDb、InnoDb汁掠、NDb略吨、Archive幾種,相對于 B-Tree 來說考阱,R-Tree 的優(yōu)勢在于范圍查找。
從邏輯上來對 MySQL 進(jìn)行分類鞠苟,主要分為下面這幾種
- 普通索引:普通索引是最基礎(chǔ)的索引類型乞榨,它沒有任何限制 。創(chuàng)建方式如下
create index normal_index on cxuan003(id);
刪除方式
drop index normal_index on cxuan003;
- 唯一索引:唯一索引列的值必須唯一当娱,允許有空值吃既,如果是組合索引,則列值的組合必須唯一跨细,創(chuàng)建方式如下
create unique index normal_index on cxuan003(id);
- 主鍵索引:是一種特殊的索引鹦倚,一個表只能有一個主鍵,不允許有空值冀惭。一般是在建表的時候同時創(chuàng)建主鍵索引震叙。
CREATE TABLE
組合索引:指多個字段上創(chuàng)建的索引,只有在查詢條件中使用了創(chuàng)建索引時的第一個字段散休,索引才會被使用媒楼。使用組合索引時遵循最左前綴原則,下面我們就會創(chuàng)建組合索引戚丸。
全文索引:主要用來查找文本中的關(guān)鍵字划址,而不是直接與索引中的值相比較,目前只有 char限府、varchar夺颤,text 列上可以創(chuàng)建全文索引,創(chuàng)建表的適合添加全文索引
CREATE TABLE
當(dāng)然也可以直接創(chuàng)建全局索引
CREATE FULLTEXT INDEX index_content ON article(content)
3胁勺、索引使用
索引可以在創(chuàng)建表的時候進(jìn)行創(chuàng)建世澜,也可以單獨創(chuàng)建,下面我們采用單獨創(chuàng)建的方式姻几,我們在 cxuan004 上創(chuàng)建前綴索引
我們使用** explain **進(jìn)行分析宜狐,可以看到 cxuan004 使用索引的情況
如果不想使用索引,可以刪除索引蛇捌,索引的刪除語法是
索引使用細(xì)則:
我們在 cxuan005 上根據(jù) id 和 hash 創(chuàng)建一個復(fù)合索引抚恒,如下所示
create index id_hash_index on cxuan005(id,hash);
然后根據(jù) id 進(jìn)行執(zhí)行計劃的分析
explain select * from cxuan005 where id = '333';
可以發(fā)現(xiàn),即使 where 條件中使用的不是復(fù)合索引(Id 络拌、hash)俭驮,索引仍然能夠使用,這就是索引的前綴特性。但是如果只按照 hash 進(jìn)行查詢的話混萝,索引就不會用到遗遵。
explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';
如果 where 條件使用了 like 查詢,并且** % **不在第一個字符逸嘀,索引才可能被使用车要。
對于復(fù)合索引來說,只能使用 id 進(jìn)行 like 查詢崭倘,因為 hash 列不管怎么查詢都不會走索引翼岁。
explain select * from cxuan005 where id like '%1';
可以看到,如果第一個字符是 % 司光,則沒有使用索引琅坡。
explain select * from cxuan005 where id like '1%';
如果使用了 % 號,就會觸發(fā)索引残家。
如果列名是索引的話榆俺,那么對列名進(jìn)行 NULL 查詢,將會觸發(fā)索引坞淮。
explain select * from cxuan005 where id is null;
還有一些情況是存在索引但是 MySQL 并不會使用的情況茴晋。
最簡單的,如果使用索引后比不使用索引的效率還差碾盐,那么 MySQL 就不會使用索引晃跺。
如果 SQL 中使用了 OR 條件,OR 前的條件列有索引毫玖,而后面的列沒有索引的話掀虎,那么涉及到的索引都不會使用,比如 cxuan005 表中付枫,只有 id 和 hash 字段有索引烹玉,而 info 字段沒有索引,那么我們使用 or 進(jìn)行查詢阐滩。
explain select * from cxuan005 where id = 111 and info = 'cxuan';
我們從 explain 的執(zhí)行結(jié)果可以看到二打,雖然 possible_keys 選項上仍然有 id_hash_index 索引,但是從 key掂榔、key_len 可以得知继效,這條 SQL 語句并未使用索引。
在帶有復(fù)合索引的列上查詢不是第一列的數(shù)據(jù)装获,也不會使用索引瑞信。
explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';
如果 where 條件的列參與了計算,那么也不會使用索引
explain select * from cxuan005 where id + '111' = '666';
索引列使用函數(shù)穴豫,一樣也不會使用索引
explain select * from cxuan005 where concat(id,'111') = '666';
索引列使用了 like 凡简,并且 % 位于第一個字符逼友,則不會使用索引。
在 order by 操作中秤涩,排序的列同時也在 where 語句中帜乞,將不會使用索引。
當(dāng)數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換時筐眷,比如 varchar 不加單引號可能轉(zhuǎn)換為 int 類型時黎烈,會使索引無效,觸發(fā)全表掃描浊竟。比如下面這兩個例子能夠顯而易見的說明這一點
在索引列上使用 IS NOT NULL 操作
在索引字段上使用 <>怨喘,!=。不等于操作符是永遠(yuǎn)不會用到索引的振定,因此對它的處理只會產(chǎn)生全表掃描。
關(guān)于設(shè)置索引但是索引沒有生效的場景還有很多肉拓,這個需要小伙伴們工作中不斷總結(jié)和完善后频,不過我上面總結(jié)的這些索引失效的情景,能夠覆蓋大多數(shù)索引失效的場景了暖途。
4卑惜、查看索引的使用情況
在 MySQL 索引的使用過程中,有一個 Handler_read_key 值驻售,這個值表示了某一行被索引值讀的次數(shù)露久。Handler_read_key 的值比較低的話,則表明增加索引得到的性能改善不是很理想欺栗,可能索引使用的頻率不高毫痕。
還有一個值是 Handler_read_rnd_next,這個值高則意味著查詢運行效率不高迟几,應(yīng)該建立索引來進(jìn)行搶救消请。這個值的含義是在數(shù)據(jù)文件中讀下一行的請求數(shù)。如果正在進(jìn)行大量的表掃描类腮,Handler_read_rnd_next 的值比較高臊泰,就說明表索引不正確或?qū)懭氲牟樵儧]有利用索引。
MySQL 分析表蚜枢、檢查表和優(yōu)化表
對于大多數(shù)開發(fā)者來說缸逃,他們更傾向于解決簡單 SQL的優(yōu)化,而復(fù)雜 SQL 的優(yōu)化交給了公司的 DBA 來做厂抽。
下面就從普通程序員的角度和你聊幾個簡單的優(yōu)化方式需频。
1、MySQL 分析表
分析表用于分析和存儲表的關(guān)鍵字分布修肠,分析的結(jié)果可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計信息贺辰,使得 SQL 生成正確的執(zhí)行計劃。如果用于感覺實際執(zhí)行計劃與預(yù)期不符婴削,可以執(zhí)行分析表來解決問題涧偷,分析表語法如下
analyze table cxuan005;
分析結(jié)果涉及到的字段屬性如下
Table:表示表的名稱;
Op:表示執(zhí)行的操作刷后,analyze 表示進(jìn)行分析操作吃靠,check 表示進(jìn)行檢查查找硫眨,optimize 表示進(jìn)行優(yōu)化操作;
Msg_type:表示信息類型巢块,其顯示的值通常是狀態(tài)礁阁、警告、錯誤和信息這四者之一族奢;
Msg_text:顯示信息姥闭。
對表的定期分析可以改善性能,應(yīng)該成為日常工作的一部分越走。因為通過更新表的索引信息對表進(jìn)行分析棚品,可改善數(shù)據(jù)庫性能。
2廊敌、MySQL 檢查表
數(shù)據(jù)庫經(jīng)惩埽可能遇到錯誤,比如數(shù)據(jù)寫入磁盤時發(fā)生錯誤骡澈,或是索引沒有同步更新锅纺,或是數(shù)據(jù)庫未關(guān)閉 MySQL 就停止了。遇到這些情況肋殴,數(shù)據(jù)就可能發(fā)生錯誤:** Incorrect key file for table: ' '. Try to repair it.** 此時囤锉,我們可以使用 Check Table 語句來檢查表及其對應(yīng)的索引。
check table cxuan005;
檢查表的主要目的就是檢查一個或者多個表是否有錯誤疼电。Check Table 對 MyISAM 和 InnoDB 表有作用嚼锄。Check Table 也可以檢查視圖的錯誤。
3蔽豺、MySQL 優(yōu)化表
MySQL 優(yōu)化表適用于刪除了大量的表數(shù)據(jù)区丑,或者對包含 VARCHAR、BLOB 或則 TEXT 命令進(jìn)行大量修改的情況修陡。MySQL 優(yōu)化表可以將大量的空間碎片進(jìn)行合并沧侥,消除由于刪除或者更新造成的空間浪費情況。它的命令如下
optimize table cxuan005;
我的存儲引擎是 InnoDB 引擎魄鸦,但是從圖可以知道宴杀,InnoDB 不支持使用 optimize 優(yōu)化,建議使用 recreate + analyze 進(jìn)行優(yōu)化拾因。optimize 命令只對 MyISAM 旺罢、BDB 表起作用旷余。
作者丨cxuan
來源丨公眾號:程序員cxuan(ID:cxuangoodjob)