那些年我們一起優(yōu)化的SQL

一、前言

在日常開發(fā)中，我們經(jīng)常遇到一些數(shù)據(jù)庫(kù)相關(guān)的問(wèn)題，比方說(shuō)：

• SQL已經(jīng)走了索引了，為什么還是會(huì)超時(shí)？
• 索引越建越多了，但是好像都是合理的，因?yàn)樾枨缶褪切枰鞣N查詢，但是索引過(guò)多又會(huì)降低寫入的效率，怎么更加合理的建立索引？
• 為某個(gè)業(yè)務(wù)場(chǎng)景建立了某個(gè)索引，想當(dāng)然的會(huì)生效，搞不清楚為啥沒有完全覆蓋？
• 索引包含了排序字段，為什么還是fileSort?
• 刷數(shù)據(jù)，批量處理大量數(shù)據(jù)如何優(yōu)化

二、分析SQL的手段

2.1 基于SQL執(zhí)行計(jì)劃

通過(guò)explain可以獲取SQL的執(zhí)行計(jì)劃

執(zhí)行返回結(jié)果

常見字段

查詢語(yǔ)句常出現(xiàn)的Extra類型

MySQL系統(tǒng)架構(gòu)圖

using where 和 using index condition的區(qū)別

比如：

索引:

index(field1,field2)

SQL:

select?*?from?table?where?field1=value1?and?field2?like?'%value2%'

如果沒有using index condtion，field1會(huì)走索引查詢，匹配到對(duì)應(yīng)的數(shù)據(jù)后，回表查出剩余字段信息，再去匹配。

如果通過(guò)field1匹配出了大量數(shù)據(jù)，會(huì)導(dǎo)致大量回表匹配。

于是就有了using index condtion，本質(zhì)上索引樹上是包含field2字段的，只是field2不能走索引搜索（因?yàn)槭褂昧饲澳：ヅ洌?，但是可以利用索引上現(xiàn)有的數(shù)據(jù)進(jìn)行遍歷，減少無(wú)效回表。

using index condtion就是使用了ICP（索引下推），在存儲(chǔ)引擎層進(jìn)行數(shù)據(jù)過(guò)濾，而不是在服務(wù)層過(guò)濾，利用索引現(xiàn)有的數(shù)據(jù)減少回表的數(shù)據(jù)。

比方說(shuō)field1過(guò)濾后剩下1000條數(shù)據(jù)，需要回表1000條，使用field2在索引過(guò)濾后剩下100條，那么即使select * 也只是回表100條。

2.2 基于追蹤優(yōu)化器分析

該功能可以查看優(yōu)化器生成執(zhí)行計(jì)劃的整個(gè)過(guò)程

• sql語(yǔ)句的優(yōu)化轉(zhuǎn)換
• 表的依賴關(guān)系
• 全表掃描、索引的開銷計(jì)算，及最終選擇
• filesort排序算法的選擇

怎么使用

執(zhí)行信息截取：

三、案例分析

案例數(shù)據(jù)表：

CREATE?TABLE?`reserve`?(
??`id`?BIGINT?UNSIGNED?AUTO_INCREMENT?NOT?NULL?COMMENT?'數(shù)據(jù)庫(kù)自增id',
??`reserve_no`?VARCHAR(32)?NOT?NULL?COMMENT?'預(yù)約號(hào)?全局唯一',
??`shop_id`?BIGINT?UNSIGNED?NOT?NULL?COMMENT?'店鋪id',
??`uid`?BIGINT?UNSIGNED?NOT?NULL?COMMENT?'買家id',
??`technician_id`?BIGINT?UNSIGNED?NOT?NULL?DEFAULT?'0'?COMMENT?'技師id',
??`arrange_at`?DATETIME?NOT?NULL?COMMENT?'預(yù)約開始時(shí)間',
??`is_delete`?TINYINT?UNSIGNED?NOT?NULL?DEFAULT?'0'?COMMENT?'邏輯刪除字段?0-未刪除?1-已刪除',
??`created_at`?DATETIME?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'創(chuàng)建時(shí)間',
??`updated_at`?DATETIME?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
??`reserve_status`?INT?NOT?NULL?DEFAULT?'5'?COMMENT?'預(yù)約狀態(tài)',
??`guest_name`?VARCHAR(30)?NOT?NULL?DEFAULT?''?COMMENT?'預(yù)約人姓名/昵稱',
??`guest_mobile`?VARCHAR(32)?NOT?NULL?DEFAULT?''?COMMENT?'預(yù)約人手機(jī)號(hào)',
??`real_pay`?BIGINT?UNSIGNED?NOT?NULL?DEFAULT?'0'?COMMENT?'預(yù)約總價(jià)格',
??
??PRIMARY?KEY?(`id`),
??KEY?`idx_reserve_no`?(`shop_id`,`reserve_no`)?COMMENT?'預(yù)約號(hào)',
??KEY?`idx_uid_kdt`?(`shop_id`,`uid`)?COMMENT?'用戶?ID',
??KEY?`idx_shop_guest_name`?(`shop_id`,`guest_name`)?COMMENT?'預(yù)約人姓名',
??KEY?`idx_status_arrange`?(`shop_id`,`reserve_status`,`arrange_at`)?COMMENT?'狀態(tài)、到店時(shí)間',
??KEY?`idx_status_arrange`?(`shop_id`,`reserve_status`,`uid`)?COMMENT?'狀態(tài)、買家id',
??KEY?`idx_shop_create_status`?(`shop_id`,`created_at`,`reserve_status`)?COMMENT?'分店創(chuàng)建時(shí)間',
??KEY?`idx_shop_del`?(`shop_id`,`is_delete`)?COMMENT?'邏輯刪除',
??KEY?`idx_shop_dept_arrange`?(`shop_id`,`arrange_at`,`created_at`),
??KEY?`idx_guest_name`?(`guest_name`)?COMMENT?'預(yù)約人姓名',
)?ENGINE=InnoDB?CHARSET=utf8mb4?COMMENT='預(yù)約記錄表'

注意：

ICP只能減少回表，但是在索引上的搜索依舊沒有利用到查找樹的特性去提高搜索效率，所以這種場(chǎng)景該字段在本章中也會(huì)認(rèn)定索引失效。

比方說(shuō)表table 存在索引 index(field1,field2)

select?*?from?table?where?field1=value1?and?field2?like?'%value2%'

field2會(huì)基于從index上過(guò)濾后的數(shù)據(jù)進(jìn)行遍歷搜索。

那么出現(xiàn)using index condition 就說(shuō)明索引還不是最優(yōu)嗎？

并不一定, 當(dāng)索引使用類型type為range時(shí)，都會(huì)出現(xiàn)using index condition。

比方說(shuō)：

select?*?from?table?where?field1?>?value1

3.1 索引失效場(chǎng)景

3.1.1 最左匹配截?cái)?/strong>

索引：

KEY idx_reserve_no (shop_id,reserve_no) COMMENT '預(yù)約號(hào)'

SQL:

select?*?from?reserve?where?reserve_no=''

查詢匹配從左往右匹配，要使用reserve_no走索引，必須查詢條件攜帶shop_id

3.1.2 隱式轉(zhuǎn)換

索引：

KEY idx_shop_guest_name (shop_id,guest_name) COMMENT '預(yù)約人姓名' KEY idx_shop_create_status (shop_id,created_at,reserve_status) COMMENT '分店創(chuàng)建時(shí)間'

SQL:

select?*?from?reserve?where?shop_id=1?and?guest_name=1
select?*?from?reserve?where?created_at=1603296000000

guest_name是字符類型，使用了數(shù)字，以及date_time類型應(yīng)該使用字符串匹配。否則mysql會(huì)用到隱式替換，導(dǎo)致索引失效。

3.1.3 in + order by 導(dǎo)致排序失效

索引：

KEY idx_status_arrange (shop_id,reserve_status,arrange_at) COMMENT '狀態(tài)時(shí)間'

SQL:

select?*?from?reserve?where?shop_id=1?and?reserve_status?in?(1,2)?order?by?created_at

使用了in+order by會(huì)導(dǎo)致order by不能走索引，可以通過(guò)將order by字段前置，將in過(guò)濾的字段移動(dòng)到組合索引后端，只用于避免回表。

一半建議減少排序的字段，所以可以調(diào)整下SQL只查id，再根據(jù)id去查對(duì)應(yīng)的數(shù)據(jù)。（可以讓sql走覆蓋索引、非索引排序的情況下也可以促使Mysql使用更優(yōu)的排序算法）

select?id?from?reserve?where?shop_id=1?and?reserve_status?in?(1,2)?order?by?created_at

可以調(diào)整索引為

KEY idx_status_arrange (shop_id,arrange_at,reserve_status) COMMENT '狀態(tài)時(shí)間'

讓排序走索引，并且利用reserve_status在索引上進(jìn)行數(shù)據(jù)過(guò)濾，避免回表

同時(shí)存在 reserve_status、created_at索引和 created_at、reserve_status索引時(shí) ，則要注意mysql在進(jìn)行索引選擇時(shí)，針對(duì)in是有特殊的代價(jià)計(jì)算規(guī)則的。

in代價(jià)計(jì)算

in查詢?cè)贛ysql底層是通過(guò)n*m的方式去搜索，類似union，但是效率比union高。

in查詢?cè)谶M(jìn)行cost代價(jià)計(jì)算時(shí)（代價(jià) = 元組數(shù) * IO平均值），是通過(guò)將in包含的數(shù)值，一條條去查詢獲取元組數(shù)的，因此這個(gè)計(jì)算過(guò)程會(huì)比較的慢，所以Mysql設(shè)置了個(gè)臨界值，5.6之后超過(guò)這個(gè)臨界值后該列的cost就不參與計(jì)算了。因此會(huì)導(dǎo)致執(zhí)行計(jì)劃選擇不準(zhǔn)確。

eq_range_index_dive_limit

默認(rèn)是200，即in條件超過(guò)了200個(gè)數(shù)據(jù)，會(huì)導(dǎo)致in的代價(jià)計(jì)算存在問(wèn)題，可能會(huì)導(dǎo)致Mysql選擇的索引不準(zhǔn)確。

3.1.4 范圍查詢阻斷組合索引

索引：

KEY idx_shop_create_status (shop_id,created_at,reserve_status) COMMENT '分店創(chuàng)建時(shí)間',

SQL:

select?*?from?reserve?where?shop_id=1?and??created_at>'2020-10-22'?and?reserve_status=10

注意：>和< 判斷會(huì)阻斷組合索引，created_at用了>查詢，因此reserve_status不能走索引。

可以調(diào)整下索引順序，created_at放在最后面。

KEY idx_shop_create (shop_id,reserve_status,created_at) COMMENT '分店創(chuàng)建時(shí)間排序'

注意：>=和<=雖然也會(huì)阻斷后續(xù)字段，但是后續(xù)字段可能會(huì)通過(guò)icp基于索引獲取數(shù)據(jù)，所以explain可能可以看到key_len是包含后續(xù)字段的。

3.1.5 前模糊匹配不能走索引

組合索引時(shí)，在非最左字段的情況下，可以用到ICP

索引：

KEY idx_shop_guest_name (shop_id,guest_name) COMMENT '預(yù)約人姓名',

SQL:

select?*?from?reserve?where?shop_id=1?and?guest_name?like?'%name%'

3.1.6 or查詢導(dǎo)致失效

索引：

KEY idx_guest_name (guest_name) COMMENT '預(yù)約人姓名' ?KEY uid (uid) COMMENT '買家id'

SQL:

select?*?from?reserve?where?uid=1?or?guest_name=''

or查詢會(huì)導(dǎo)致索引失效，可以將uid和guest_name分別建立索引，利用Mysql的索引合并進(jìn)行優(yōu)化。

3.1.7 使用函數(shù)查詢

包括使用查詢字段進(jìn)行四則運(yùn)算 field+1 > value

索引：

KEY idx_shop_create_status (shop_id,created_at,reserve_status) COMMENT '分店創(chuàng)建時(shí)間' KEY idx_shop_real_pay (shop_id,real_pay) USING BTREE,

SQL:

select?*?from?reserve??where?shop_id=1?and?DATE_SUB(CURDATE(),?INTERVAL?7?DAY)?<=?date(created_at);
select?*?from?reserve?where?shop_id=1?and?real_pay+1=10

在列上進(jìn)行使用函數(shù)查詢和使用查詢字段進(jìn)行四則運(yùn)算，會(huì)導(dǎo)致不能走索引

3.1.8 不等于、不包含只能用到ICP

索引：

KEY idx_status_arrange (shop_id,reserve_status,arrange_at) COMMENT '狀態(tài)時(shí)間'

SQL:

select?*?from?reserve?where?shop_id=1?and?reserve_status?not?in?(1,2)
select?*?from?reserve?where?shop_id=1?and?reserve_status?!=?1

3.1.9 選擇性過(guò)低，直接走全表

選擇性過(guò)低，曾經(jīng)以選擇性是否低于30%為界限決定是否走索引，現(xiàn)在還會(huì)根據(jù)表大小、IO塊大小、行數(shù)等決定。

索引：

KEY idx_shop_create_status (shop_id,created_at,reserve_status) COMMENT '分店創(chuàng)建時(shí)間'

SQL:

select?*?from?reserve?where?shop_id=1?and?created_at>'2017-10-22'

3.1.10 索引碎片化

索引碎片化是較為特殊的場(chǎng)景，查詢字段均符合索引，但是索引執(zhí)行計(jì)劃不符合預(yù)期。考慮如果是大量執(zhí)行DML的表，有可能是索引碎片化嚴(yán)重，導(dǎo)致索引失效。

(11) 排序使用了索引

排序的字段不一定是要在最后，但是一定不能前一個(gè)字段不在查詢條件中

索引：

KEY idx_shop_create_status (shop_id,created_at,reserve_status) COMMENT '分店創(chuàng)建時(shí)間'

SQL:

select?*?from?reserve?where?shop_id=1?and?created_at>'2017-10-22'?order?by

小結(jié)

根據(jù)以上例子，總結(jié)幾個(gè)索引失效的場(chǎng)景

1. 組合索引左匹配原則
2. 發(fā)生隱式轉(zhuǎn)換
3. 組合索引，in + order by in會(huì)阻斷排序用索引
4. 范圍查詢會(huì)阻斷組合索引,索引涉及到范圍查詢的索引字段要放在組合索引的最后面。
5. 前模糊匹配導(dǎo)致索引失效
6. or查詢，查詢條件部分有索引，部分無(wú)索引，導(dǎo)致索引失效。
7. 查詢條件使用了函數(shù)運(yùn)算、四則運(yùn)算等。
8. 使用了!=、not in
9. 選擇性過(guò)低

3.2 索引可優(yōu)化場(chǎng)景

3.2.1 索引包含選擇性過(guò)低字段

SQL:

select?*?from?reserve?where?shop_id=1?and?is_delete=0

索引：

KEY idx_shop_del (shop_id,is_delete) COMMENT '邏輯刪除',

is_delete 表示邏輯刪除，包含0未刪除和1已刪除，數(shù)據(jù)庫(kù)中的值只有很少量部分是邏輯刪除的，但是在業(yè)務(wù)中我們一般都只查未刪除的，那么這種情況is_delete是完全不必要在索引中的?？梢园裪s_delete在組合索引中去掉

3.2.2 唯一性索引

SQL:

select?*?from?reserve?where?shop_id=1?and?reserve_no=''

索引：

KEY idx_reserve_no (shop_id,reserve_no) COMMENT '預(yù)約號(hào)',

具有唯一性的字段不用跟別的字段建立組合索引，可以只保留reserve_no。

3.2.3 字段前置

SQL:

select?*?from?reserve?where?shop_id=1?and?uid=1?and?reserve_status=1

索引：

KEY idx_status_arrange (shop_id,reserve_status,uid) COMMENT '狀態(tài)、買家id' uid的區(qū)分度明顯比reserve_status更高，單條sql來(lái)看，將uid和reserve_status的位置交換會(huì)更加合適

3.2.4 覆蓋索引

SQL:

SELECT?sum(real_pay)?FROM?reserve?where?shop_id=1?and?reserve_status=1

索引：

KEY idx_status_arrange (shop_id,reserve_status,uid) COMMENT '狀態(tài)、買家id',

看起來(lái)查詢條件已經(jīng)走索引了，但是如果數(shù)據(jù)量很大的情況下，會(huì)造成大量回表，也會(huì)導(dǎo)致慢查。

可以通過(guò)建一個(gè)索引解決

KEY idx_status_real_pay (shop_id,reserve_status,real_pay) COMMENT '狀態(tài)-預(yù)約實(shí)付金額',

小結(jié)

1. 建立索引要選擇性低的字段不用建立索引
2. 具有唯一識(shí)別的字段無(wú)需與其他字段建立組合索引
3. 除了業(yè)務(wù)需求上的考慮，盡量選擇性高的索引字段前置
4. 不止需要考慮查詢條件是否走索引，在經(jīng)過(guò)索引過(guò)濾后，數(shù)據(jù)量依舊很大的情況下要考慮使用覆蓋索引。

3.3 sql寫法可優(yōu)化場(chǎng)景

3.3.1 深分頁(yè)

在刷數(shù)據(jù)經(jīng)常涉及到深分頁(yè)，而深分頁(yè)的時(shí)候查詢效率會(huì)很慢。

比如以下sql：

select?*?from?reserve?where?field=1?limit?1000,10

由于limit不會(huì)傳遞到引擎層，所以如果查1000行數(shù)據(jù)，如果where有條件不走索引，會(huì)導(dǎo)致需要回表1000行數(shù)據(jù)。

Mysql查詢數(shù)據(jù)時(shí)，先由引擎層通過(guò)索引過(guò)濾出一批數(shù)據(jù)（索引過(guò)濾），然后服務(wù)層進(jìn)行二次過(guò)濾（非索引過(guò)濾）。引擎層過(guò)濾后會(huì)將獲取的數(shù)據(jù)暫存，服務(wù)層一條一條數(shù)據(jù)獲取，獲取時(shí)引擎層回表獲得完成數(shù)據(jù)交給服務(wù)層，服務(wù)層判斷是否匹配查詢條件（非索引過(guò)濾），如果匹配會(huì)繼續(xù)判斷是否滿足limit限制的條數(shù)。

所以如果深分頁(yè)，會(huì)導(dǎo)致大量的無(wú)效回表，因此優(yōu)化的方式就是避免深分頁(yè)帶來(lái)的無(wú)效回表。

我們可以改成id過(guò)濾，每次都只查詢大于上次查詢的數(shù)據(jù)id。這樣每次只查詢100條，回表也只需要回表100條

#?避免深分頁(yè)
select?*?from?reserve?where?id>上次查詢的數(shù)據(jù)id值?limit?100

#?延遲關(guān)聯(lián)?避免大量回表
SELECT?*?FROM?shop?t1,(select?id?from?reserve?limit?1000,100)?t2?where?t1.id=t2.id

另外，涉及到大數(shù)據(jù)量的處理，可以按照時(shí)間分割查詢，每個(gè)線程負(fù)責(zé)一個(gè)時(shí)間段的數(shù)據(jù)處理，提高整體的效率。

3.3.2 asc和desc混用

索引：

KEY ide_shop_dept_arrage(sho_id,arrage_at,created_at)

SQL:

select?*?from?t1?where?shop_id=1?order?by?arrage_at?desc,created_at?asc

desc 和asc混用時(shí)會(huì)導(dǎo)致索引失效

另外排序建議只select必須的字段，通過(guò)覆蓋索引提高效率。排序字段一定要走索引，不走索引數(shù)據(jù)量大的情況即使select的字段很少也會(huì)很慢。

select比較少的字段不僅可以利用到覆蓋索引提高速度。在排序沒有命中索引時(shí)，還涉及到Mysql如果選擇排序策略的問(wèn)題，本文不擴(kuò)展說(shuō)明。