SQL優(yōu)化（二）

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

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

上片文章已經(jīng)詳細(xì)介紹了explain各個(gè)字段的含義，以及什么情況應(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是一個(gè)范圍，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id則再排序comments，如果遇到相同的comments則再排序views，又因?yàn)閏omments字段在復(fù)合索引里處于中間位置，而comments1是一個(gè)條件（是一個(gè)范圍值），在復(fù)合索引的一個(gè)范圍值的數(shù)據(jù)列后面的索引全部失效，mysql無法利用索引再對(duì)后面的views部分進(jìn)行檢索，也就是說views無法按照索引排序，所以explain下此sql語句，type為range，extra使用的是Using filesort，這是比較糟糕的。所以我們放棄comments這個(gè)范圍字段，建立索引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ù)合索引

前段時(shí)間做了一個(gè)銷售精細(xì)化項(xiàng)目，是公司crm項(xiàng)目的一個(gè)大模塊，大致就是為銷售人員制定指標(biāo)，實(shí)現(xiàn)銷售目標(biāo)從區(qū)域到團(tuán)到業(yè)務(wù)員到客戶，實(shí)時(shí)跟蹤業(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是左連接，左邊的表一定是全表查詢，所以要建立右邊表對(duì)應(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ū)動(dòng)大結(jié)果集

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

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

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

1、全值匹配我最愛

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

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

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

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

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

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

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

9、字符串不加單引號(hào)也會(huì)引起索引失效

10、少用or，用它來連接時(shí)會(huì)索引失效。

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

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

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

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

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

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

索引列上少計(jì)算，范圍之后全失效

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

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

var引號(hào)不可丟，sql高級(jí)也不難

mysql 避免索引失效

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

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

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

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

name字段為varchar類型

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

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

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

后面的索引失效

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

BETWEEN AND 范圍條件不會(huì)導(dǎo)致索引失效！！！

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

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

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

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

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

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

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

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

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

1、先查看表上的索引

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

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

有個(gè)add_time字段沒有用到索引

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

使用UNION ALL

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

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

下面的sql幾乎違背了上面的所有原則，索引依然全部生效。因?yàn)閟elect是索引覆蓋的，select里不包含沒有建立索引的字段。因此總是用到索引的。可以看出來索引覆蓋在sql優(yōu)化中的作用性

為什么MySQL字符串不加引號(hào)索引會(huì)失效？這個(gè)答案是我見過最靠譜

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

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

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

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

面試官讓他回去等通知。

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

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

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

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

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

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

創(chuàng)建索引為idx_user_name

執(zhí)行語句：

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

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

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

這樣索引就失效了。

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

這樣dt索引才不會(huì)失效。

例如我們?cè)谟唵伪韙b_order建立了索引idx_order_id，order_id字段類型為varchar

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

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

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

執(zhí)行語句

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

再來看一個(gè)例子：

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

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

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

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

由于對(duì)索引列進(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)行判斷的？其實(shí)規(guī)則也并不復(fù)雜。

根據(jù)這個(gè)規(guī)則，我們?cè)倩剡^頭看看之前的查詢語句

select '12345678936' = 12345678936

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

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

為什么MySQL字符串類型查詢時(shí)不加引號(hào)索引會(huì)失效？這是因?yàn)橐樵兊淖址侄螞]有加引號(hào)時(shí)，MySQL內(nèi)部進(jìn)行了隱式轉(zhuǎn)換，此次查詢會(huì)導(dǎo)致全表掃描，所以慢了。

總結(jié)：

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

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

拓展

索引創(chuàng)建

1、主鍵索引：

2、唯一索引：

3、普通索引：

4、全文索引：

alter table table_name add fulltext (column)

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

索引刪除

分享文章：mysql索引失效怎么優(yōu)化,mysql索引失效怎么優(yōu)化數(shù)據(jù)
網(wǎng)頁網(wǎng)址：http://chinadenli.net/article32/hsgssc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供ChatGPT、App開發(fā)、移動(dòng)網(wǎng)站建設(shè)、企業(yè)建站、網(wǎng)站營銷、域名注冊(cè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)