避免索引失效原則(二)

注：繼上一篇文章繼續(xù)講解：

避免索引失效原則(一)https://www.cnblogs.com/StanleyBlogs/p/10482048.html#4195062

作者 ： Stanley 羅昊

【轉(zhuǎn)載請(qǐng)注明出處和署名，謝謝！】

體驗(yàn)SQL優(yōu)化中的概率情況

在上一篇文章結(jié)尾處，我們?cè)趫?zhí)行查詢(xún)計(jì)劃的時(shí)候，卻發(fā)現(xiàn)我明明加了索引，并且也滿(mǎn)足了使用索引的條件，但是，給我的優(yōu)化結(jié)果卻是失敗，從而，得出一個(gè)結(jié)論便是，優(yōu)化是概率的，也就跟彩票一樣，不可能百分之百優(yōu)化成功的，但是彩票我們都知道，全憑運(yùn)氣，但是這里就不一樣了，我們需要了解SQL優(yōu)化概率背后到底是誰(shuí)導(dǎo)致它優(yōu)化失敗的；

首先，我們來(lái)了解下，出現(xiàn)概率優(yōu)化的原因：因?yàn)樵赟QL底層中，有一個(gè)服務(wù)層，服務(wù)層有一個(gè)SQL優(yōu)化器，當(dāng)我們寫(xiě)一條語(yǔ)句，雖然我們手動(dòng)優(yōu)化了，但是，優(yōu)化器覺(jué)得你優(yōu)化的不太合適，它可能會(huì)進(jìn)行一些自己的干擾，干擾完畢之后就執(zhí)行結(jié)果就不再是你理想中的那樣了，所以這個(gè)優(yōu)化器有的時(shí)候會(huì)阻擾我們的優(yōu)化工作；

接下來(lái)，我們就通過(guò)幾個(gè)例子來(lái)體驗(yàn)一下我們?cè)O(shè)想的優(yōu)化和實(shí)際不一樣的一些操作；

首先，我們需要建立一個(gè)復(fù)合索引：

alter table book add index idx_book_at(authorid,typeid);

建立完索引后，我們進(jìn)行一個(gè)簡(jiǎn)單的查詢(xún)：

explain select * from book where authorid = 1 and typeid = 2;

通過(guò)結(jié)果我們可以發(fā)現(xiàn)，復(fù)合索引全部生效了；

那么接下來(lái)，我們將體驗(yàn)一下讓它產(chǎn)生概率問(wèn)題，我把上面的SQL語(yǔ)句拿過(guò)來(lái)改改：

explain select * from book where authorid > 1 and typeid = 2;

我們查看執(zhí)行結(jié)果：

結(jié)果很明顯，給authorid 添加了一個(gè)大于號(hào)，這樣則導(dǎo)致了右側(cè)索引全部失效，包括自身，從而得出一個(gè)結(jié)論，復(fù)合索引中如果有>，則自身已經(jīng)后面的索引都將會(huì)失效；

但是，這次我SQL語(yǔ)句再次改變，奇怪的事情將會(huì)發(fā)生：

explain select * from book where authorid = 1 and typeid > 2;

這次我把這個(gè)大于號(hào)加給了typeid字段，顯然它也是索引，剛才我說(shuō)了，添加大于號(hào)會(huì)導(dǎo)致自身并且右側(cè)索引全部失效，但是接下來(lái)：

現(xiàn)在我們又發(fā)現(xiàn)，結(jié)論又不對(duì)了，我明明自身肯定失效啊，為啥這次偏偏卻兩個(gè)都生效了？

原因就是概率情況，咱們?cè)趯?shí)際執(zhí)行時(shí)，復(fù)合索引全部使用了，并不是剛才我們說(shuō)的那個(gè)結(jié)論，自身失效及右側(cè)全部失效，當(dāng)然，這個(gè)情況是大部分情況下都是有用了，僅有小部分情況會(huì)出現(xiàn)；

明顯的概率問(wèn)題

剛才我寫(xiě)了幾個(gè)例子看起來(lái)不是特別的明顯，下面我將寫(xiě)幾個(gè)比較明顯的例子來(lái)體驗(yàn)一下概率問(wèn)題；

首先，我們編寫(xiě)一條SQL語(yǔ)句：

explain select * from book where authorid < 1 and typeid = 2;

此時(shí)，我把a(bǔ)uthorid改成了小于號(hào)，我們看結(jié)果：

我們看到了，此時(shí)，我們換層了小于號(hào)，發(fā)現(xiàn)沒(méi)有全部失效，此條語(yǔ)句得出結(jié)論，兩個(gè)索引，僅生效了一個(gè)因?yàn)榉秶樵?xún)僅對(duì)自身生效，對(duì)后面的不會(huì)生效；

接下來(lái)，我再改變一下SQL語(yǔ)句：

explain select * from book where authorid < 4 and typeid = 2;

首先看清楚，我現(xiàn)在沒(méi)有更改任何符號(hào)，僅把a(bǔ)uthorid小于號(hào)后面的數(shù)字條件寫(xiě)成了4，再來(lái)看看執(zhí)行結(jié)果：

我們驚奇的發(fā)現(xiàn)，竟然全部失效了，我明明就光改了一個(gè)數(shù)字而已，就全部失效了，剛才還有一個(gè)生效，現(xiàn)在一個(gè)都沒(méi)有了，這到底是為什么呢？

通過(guò)后兩個(gè)例子我們發(fā)現(xiàn)，就改了一個(gè)數(shù)，索引都不一樣了，所以，這就是SQL優(yōu)化的一個(gè)概率；

因此得出結(jié)論，我們學(xué)習(xí)的索引優(yōu)化，是一個(gè)大部分情況都適用的結(jié)論，但由于SQL優(yōu)化器等原因，該結(jié)論不是100%正確，因?yàn)镾QL的底層把我們寫(xiě)的語(yǔ)句給干擾了；

一般而言，范圍查詢(xún)(> < in)，之后的索引失效，僅對(duì)自身生效；

補(bǔ)救

那么，如果這樣一直干擾下去，我們到底還優(yōu)不優(yōu)化了？就沒(méi)有辦法來(lái)補(bǔ)救這個(gè)概率問(wèn)題嗎？答案是有的；

盡量使用索引覆蓋 (using index)在Extra里面出現(xiàn)這個(gè)，就表示你的SQL語(yǔ)句不會(huì)出錯(cuò)，如果你怕在優(yōu)化中出現(xiàn)概率問(wèn)題，那么你就朝著using index這個(gè)方向去優(yōu)化，因?yàn)?#xff0c;出現(xiàn)這個(gè)就代表你這條SQL100%生效，不會(huì)出現(xiàn)概率問(wèn)題；

比如我現(xiàn)在有 a b c三張表;

現(xiàn)在我編寫(xiě)一條SQL，select a,b,c from 表名 where a = ... and b = ...;

在select后面我們用到了abc 并且查詢(xún)條件也是a b 沒(méi)有跨列，滿(mǎn)足最佳做前綴，最主要的是查詢(xún)條件也是索引，所有的索引你都按照規(guī)則全部用上了，這樣就會(huì)出現(xiàn)索引覆蓋，大大的提高了系統(tǒng)性能；

like盡量以“常量”開(kāi)頭，不要以'%'開(kāi)頭，否則索引失效

我現(xiàn)在編寫(xiě)一條SQL語(yǔ)句;

select * from 表名 where name like '%x%';

首先，這條sql語(yǔ)句是查詢(xún)表名中name 帶有x的數(shù)據(jù)，如果你這樣寫(xiě)了，如果name是索引，那么name將會(huì)失效！

接下來(lái)，我結(jié)合數(shù)據(jù)庫(kù)進(jìn)行證實(shí)一下；

explain select tname from teacher where tname like '%x%'

首先，tname我是加了一個(gè)索引的，但是看一下看一下執(zhí)行結(jié)果：

沒(méi)有失效，因?yàn)槌霈F(xiàn)了覆蓋索引，因?yàn)閠name是索引，我剛好去查tname，所以出現(xiàn)了覆蓋索引，導(dǎo)致本次查詢(xún)沒(méi)有失效，下面我把它換成“*”；

值得注意的是，在開(kāi)發(fā)過(guò)程中，嚴(yán)禁出現(xiàn)“*”！本次為了說(shuō)明問(wèn)題，所以換成“*”；

執(zhí)行結(jié)果：

索引全部失效！原因我剛才也說(shuō)過(guò)了，在模糊查詢(xún)是，不要以百分號(hào)開(kāi)頭；

如果想避免失效，可以變成以下這種寫(xiě)法：

explain select tname from teacher where tname like 'x%'

這樣雖然可以保證索引不會(huì)失效，但是，我們?cè)陧?xiàng)目開(kāi)發(fā)中，難免遇到模糊查詢(xún)，所以也是有解決方案的；

剛才我不小心也試出來(lái)了，因?yàn)槲沂褂昧怂饕采w，你想用模糊查詢(xún)可以，但是你需要有索引覆蓋，剛才我查詢(xún)tname，tname本身就在索引里面，所以出現(xiàn)了索引覆蓋；

如果必須使用模糊查詢(xún)，那么就把查詢(xún)條件以及需要查詢(xún)的字段全部聲明成索引即可；

盡量不要使用類(lèi)型轉(zhuǎn)換(顯示、隱式)，否則索引失效

這里我就簡(jiǎn)單的舉個(gè)例子：

select * from teacher where tname = 'abc';

此時(shí)，tname是varchar類(lèi)型，這個(gè)時(shí)候你你卻寫(xiě)成int類(lèi)型：

select * from teacher where tname = 123;

人家本來(lái)需要單引號(hào)的字符串類(lèi)型，結(jié)果你給人家弄了一個(gè)去掉引號(hào)的int類(lèi)型，所以索引就會(huì)失效；

盡量不要使用or，否則索引失效

select * from teacher where tname = " " or tcid>1;

這條sql語(yǔ)句就會(huì)導(dǎo)致索失效，所以要避免使用or這個(gè)關(guān)鍵字！

經(jīng)過(guò)測(cè)試發(fā)現(xiàn)，or回導(dǎo)致以左的索引失效，也就是tname這個(gè)字段的索引失效了；

今日感悟：

努力就一定會(huì)有收獲，心無(wú)旁騖

總結(jié)

以上是生活随笔為你收集整理的mysql 大于号 优化_SQL优化 MySQL版 - 避免索引失效原则（二）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。