SQL優(yōu)化（二）

SQL優(yōu)化一： sql優(yōu)化（一）

文水ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！

上片文章已經(jīng)詳細(xì)介紹了explain各個字段的含義，以及什么情況應(yīng)該建立索引，什么情況不需要建立索引以及sql語句性能的判斷依據(jù)，接下來我介紹下如何合理的建立索引。

sql語句：select id，author_id from article where category_id = 1 and comments1 order by views desc limit 1;

分析：首先我們根據(jù)where后面的條件建立符合索引，然后根據(jù)order by后面的字段建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）數(shù)據(jù)列建立復(fù)合索引，但由于comments是一個范圍，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id則再排序comments，如果遇到相同的comments則再排序views，又因為comments字段在復(fù)合索引里處于中間位置，而comments1是一個條件（是一個范圍值），在復(fù)合索引的一個范圍值的數(shù)據(jù)列后面的索引全部失效，mysql無法利用索引再對后面的views部分進(jìn)行檢索，也就是說views無法按照索引排序，所以explain下此sql語句，type為range，extra使用的是Using filesort，這是比較糟糕的。所以我們放棄comments這個范圍字段，建立索引idx_article_cv，即以（category_id，views）數(shù)據(jù)列建立復(fù)合索引，explain 此sql，type變成了ref，extra的using?filesort也變成了using index，這就變得好多了。

索引：idx_article_cv，即以（category_id，views）數(shù)據(jù)列建立復(fù)合索引

前段時間做了一個銷售精細(xì)化項目，是公司crm項目的一個大模塊，大致就是為銷售人員制定指標(biāo)，實現(xiàn)銷售目標(biāo)從區(qū)域到團(tuán)到業(yè)務(wù)員到客戶，實時跟蹤業(yè)務(wù)員所負(fù)責(zé)客戶的下單量的情況。這就存在許多關(guān)聯(lián)關(guān)系，區(qū)域-團(tuán)，團(tuán)-業(yè)務(wù)員，業(yè)務(wù)員-客戶，這使得sql常常需要關(guān)聯(lián)多張表。

sql語句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and?tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左連接，左邊的表一定是全表查詢，所以要建立右邊表對應(yīng)關(guān)聯(lián)字段的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)字段建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）字段建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)字段建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)字段建立索引。建立索引后，sql查詢速度明顯快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、盡可能減少join語句中的NestedLoop的循環(huán)次數(shù)，永遠(yuǎn)用小結(jié)果集驅(qū)動大結(jié)果集

2、優(yōu)先優(yōu)化NestedLoop的內(nèi)層循環(huán)

3、保證join語句總被驅(qū)動表上的join字段已經(jīng)被索引

4、當(dāng)無法保證被驅(qū)動表join條件字段被索引，且內(nèi)存資源充足的前提下，不要太吝嗇joinBuffer的設(shè)置

1、全值匹配我最愛

2、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則，指的是查詢從索引的最左前列開始并且不跳過索引中的列

3、并在索引列上做任何操作（計算、函數(shù)、自動or手動類型轉(zhuǎn)換），這些會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

4、存儲引擎不能使用索引中范圍條件右邊的列，范圍之后的索引全失效

5、盡量使用覆蓋索引（之訪問索引的查詢（索引列和查詢的列一致）），減少select *

6、mysql在使用不等于（！=、、）的時候無法使用索引會導(dǎo)致全表掃描。

7、is null、is not null也無法使用索引。

8、like以通配符開頭（"%abc.."），mysql索引失效也會變成全表掃描的操作。

9、字符串不加單引號也會引起索引失效

10、少用or，用它來連接時會索引失效。

1、對于單值索引，盡量選擇針對當(dāng)前query過濾性更好的索引

2、在選擇組合索引的時候，當(dāng)前query中過濾性最好的字段在索引字段順序中，位置越靠前越好

3、在選擇組合索引的時候，盡量選擇盡可能包含當(dāng)前query中的where字句中更多字段的索引

4、盡可能通過分析統(tǒng)計信息和調(diào)整query的寫法來達(dá)到選擇合適索引的目的。

全值匹配我最愛，最左前綴要遵守

帶頭大哥不能死，中間兄弟不能斷

索引列上少計算，范圍之后全失效

like百分寫最右，覆蓋索引不寫里

不等空值還有or，索引失效要少用

var引號不可丟，sql高級也不難

mysql 避免索引失效

where條件==order by 條件==group by 條件 按順序遵守 最佳左前綴法則

假設(shè)創(chuàng)建了復(fù)合索引：a,b,c

不在索引列上做任何的操作（計算、函數(shù)、顯式或隱式的類型轉(zhuǎn)換），否則會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

1、字符不加單引號會導(dǎo)致索引失效

name字段為varchar類型

這條sql發(fā)生了隱式的類型轉(zhuǎn)換：數(shù)值==字符串。所以導(dǎo)致了全表掃描，索引失效

應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

mysql中的范圍條件有：in/not in、 like、 、BETWEEN AND ；

后面的索引失效

in會導(dǎo)致索引全部失效?。?！

BETWEEN AND 范圍條件不會導(dǎo)致索引失效！?。?/p>

盡量讓索引列和查詢列一致；減少select * 的使用

1、查詢表結(jié)構(gòu)

2、查詢表的索引結(jié)構(gòu)

聯(lián)合索引：name,age,post；說明add_time字段沒有添加索引

3、查看select * 的執(zhí)行計劃

4、查看 select name,age,pos的執(zhí)行計劃

5、如果select只用一部分索引

like以通配符開頭（’%abc…’）mysql索引失效會變成全表掃描的操作。

解決：可以使用 覆蓋索引 來解決這個問題！

1、先查看表上的索引

id、name、age、pos 四個字段上都有索引； 注意：name是聯(lián)合索引中的第一個，帶頭大哥！

2、查看表結(jié)構(gòu)

有個add_time字段沒有用到索引

3、查看執(zhí)行計劃

使用UNION ALL

假設(shè)創(chuàng)建了聯(lián)合索引 x(a,b,c)

ps：like雖然也是范圍查詢但是區(qū)別于、，%用在最前面就只用到索引a了；%用在最后面可以用到a+b+c！

下面的sql幾乎違背了上面的所有原則，索引依然全部生效。因為select是索引覆蓋的，select里不包含沒有建立索引的字段。因此總是用到索引的?？梢钥闯鰜硭饕采w在sql優(yōu)化中的作用性

為什么MySQL字符串不加引號索引會失效？這個答案是我見過最靠譜

作為一名程序員，在求職面試時，不知你有沒有遇到類似這樣的問題。

張工是一名java程序員，最近到一家軟件公司應(yīng)聘軟件開發(fā)崗位，面試官問了他關(guān)于MySql索引這樣的一個問題。

對于這個問題張工之前在做項目時也曾遇到，那時候字段明明是加了索引，可不明白為什么還是很慢。后加上引號就正常了，為了趕項目進(jìn)度，張工也沒有再去留意。

現(xiàn)在面試官突然這么一問，張工也說不出個所以然來。

面試官讓他回去等通知。

我們知道MySql索引可以加快數(shù)據(jù)檢索速度，這也是使用的索引的最主要原因。但有時候使用不當(dāng)就會遇到索引失效問題，譬如在MySQL字符串類型查詢時不加引號索引會失效，是因為MySQL內(nèi)部進(jìn)行了隱式轉(zhuǎn)換。

那為什么會發(fā)生隱式轉(zhuǎn)換？又是怎么轉(zhuǎn)換的呢？

今天我們來聊聊關(guān)于MySql索引失效的話題。

先來看看一般導(dǎo)致索引失效的有哪些？

如果一張表的索引有多個，要遵守最佳左前綴法則，即查詢從索引的最左前列開始并且不跳過索引中的列。

用戶表tb_user字段 id，name，age，sex

創(chuàng)建索引為idx_user_name

執(zhí)行語句：

這時候就會導(dǎo)致索引失效

在索引列上做加工操作，查詢時會導(dǎo)致索引失效，從而導(dǎo)致全表掃描。所以，建議不要在索引列上做任何操作。

舉個例子，例如訂單表tb_order有個索引是dt（日期）， 字段數(shù)據(jù)存放的格式是這樣的2021-12-10 這樣的，如果有個需求需要根據(jù)dt，格式是20220207這樣的來查詢，這時候就不要對dt進(jìn)行格式轉(zhuǎn)換了，

這樣索引就失效了。

而是應(yīng)該對 20220207做格式處理

這樣dt索引才不會失效。

例如我們在訂單表tb_order建立了索引idx_order_id，order_id字段類型為varchar

在查詢時如果使用where order_id= 20220207123654100，這樣的查詢方式會直接造成索引失效。

要讓索引生效，正確的用法為

假如有張用戶表tb_user，創(chuàng)建的索引為idx_user_name_age_sex_phone 其中name、age、sex都加了索引。

執(zhí)行語句

上面這條sql語句只會命中name和age索引，sex索引會失效，復(fù)合索引失效需要查看key_len的長度。

再來看一個例子：

從這兩條SQL執(zhí)行的結(jié)果我們可以看出，執(zhí)行第一條SQL沒有使用到索引，而執(zhí)行第二條SQL時使用到了索引。這是為什么呢？

我們需要先了解下mysql索引優(yōu)化器工作的原理。選擇索引是優(yōu)化器工作，優(yōu)化器工作有自己的一套規(guī)則，如果等號兩邊的數(shù)據(jù)類型不一致，則會發(fā)生隱式轉(zhuǎn)換。

基于這條規(guī)則，我們回過頭看看

這條SQL語句執(zhí)行時就會變?yōu)?/p>

由于對索引列進(jìn)行了函數(shù)操作，所以才導(dǎo)致索引失效，從而全表掃描了。

那么問題來了，細(xì)心的你不知有沒有留意到為什么是把左側(cè)的列轉(zhuǎn)為int類型，而不是把右側(cè)的值轉(zhuǎn)成字符串類型呢？

什么情況下把數(shù)字轉(zhuǎn)為字符串，什么情況下把字符串轉(zhuǎn)為數(shù)字，優(yōu)化器它是根據(jù)什么規(guī)則來進(jìn)行判斷的？其實規(guī)則也并不復(fù)雜。

根據(jù)這個規(guī)則，我們再回過頭看看之前的查詢語句

select '12345678936' = 12345678936

返回1 所以這時候就把左側(cè)的列值12345678936轉(zhuǎn)成數(shù)字。

關(guān)于MySql索引失效的問題先簡單寫到這，建議平時在做項目時還是要多了解下原理，如果你了解其背后的原理，求職面試時和面試官交流起來就會很舒服了，相信能為這次面試加分，提高被錄用的概率。

為什么MySQL字符串類型查詢時不加引號索引會失效？這是因為要查詢的字符串字段沒有加引號時，MySQL內(nèi)部進(jìn)行了隱式轉(zhuǎn)換，此次查詢會導(dǎo)致全表掃描，所以慢了。

總結(jié)：

在索引列上進(jìn)行了函數(shù)操作，MySQL內(nèi)部會進(jìn)行了隱式轉(zhuǎn)換，導(dǎo)致索引失效，從而產(chǎn)生全表掃描。

由于筆者知識及水平有限,文中錯漏之處在所難免,如有不足之處，歡迎交流。

拓展

索引創(chuàng)建

1、主鍵索引：

2、唯一索引：

3、普通索引：

4、全文索引：

alter table table_name add fulltext (column)

5、聯(lián)合索引：

索引刪除