mysqlunionall無法走索引11 27
創(chuàng)新互聯(lián)建站專注于清江浦網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗(yàn)。 熱誠為您提供清江浦營銷型網(wǎng)站建設(shè)，清江浦網(wǎng)站制作、清江浦網(wǎng)頁設(shè)計(jì)、清江浦網(wǎng)站官網(wǎng)定制、小程序開發(fā)服務(wù)，打造清江浦網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供清江浦網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。
1. like %%失效。 方案:改為like %,只寫后面的%就能走索引。
2. 雖然有索引,但是查詢條件沒有索引列或者order by 排序沒有索引列。 方案:讓查詢條件有索引列
3. 索引列存在null值的情況。 方案:索引列如果沒有值,則給空字符串或者數(shù)字的0,總之就是不要設(shè)置null
MySQL索引失效的幾種情況
1.索引不存儲(chǔ)null值
更準(zhǔn)確的說，單列索引不存儲(chǔ)null值，復(fù)合索引不存儲(chǔ)全為null的值。索引不能存儲(chǔ)Null，所以對這列采用is null條件時(shí)，因?yàn)樗饕细?/p>
沒Null值，不能利用到索引，只能全表掃描。
為什么索引列不能存Null值？
將索引列值進(jìn)行建樹，其中必然涉及到諸多的比較操作。Null值的特殊性就在于參與的運(yùn)算大多取值為null。
這樣的話，null值實(shí)際上是不能參與進(jìn)建索引的過程。也就是說，null值不會(huì)像其他取值一樣出現(xiàn)在索引樹的葉子節(jié)點(diǎn)上。
2.不適合鍵值較少的列（重復(fù)數(shù)據(jù)較多的列）
假如索引列TYPE有5個(gè)鍵值，如果有1萬條數(shù)據(jù)，那么 WHERE TYPE = 1將訪問表中的2000個(gè)數(shù)據(jù)塊。
再加上訪問索引塊，一共要訪問大于200個(gè)的數(shù)據(jù)塊。
如果全表掃描，假設(shè)10條數(shù)據(jù)一個(gè)數(shù)據(jù)塊，那么只需訪問1000個(gè)數(shù)據(jù)塊，既然全表掃描訪問的數(shù)據(jù)塊
少一些，肯定就不會(huì)利用索引了。
3.前導(dǎo)模糊查詢不能利用索引(like '%XX'或者like '%XX%')
假如有這樣一列code的值為'AAA','AAB','BAA','BAB' ,如果where code like '%AB'條件，由于前面是
模糊的，所以不能利用索引的順序，必須一個(gè)個(gè)去找，看是否滿足條件。這樣會(huì)導(dǎo)致全索引掃描或者全表掃
描。如果是這樣的條件where code like 'A % '，就可以查找CODE中A開頭的CODE的位置，當(dāng)碰到B開頭的
數(shù)據(jù)時(shí)，就可以停止查找了，因?yàn)楹竺娴臄?shù)據(jù)一定不滿足要求。這樣就可以利用索引了。
4.索引失效的幾種情況
1.如果條件中有or，即使其中有條件帶索引也不會(huì)使用(這也是為什么盡量少用or的原因)
要想使用or，又想讓索引生效，只能將or條件中的每個(gè)列都加上索引
2.對于多列索引，不是使用的第一部分，則不會(huì)使用索引
3.like查詢以%開頭
4.如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來,否則不使用索引
5.如果mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引
5.MySQL主要提供2種方式的索引：B-Tree索引，Hash索引
B樹索引具有范圍查找和前綴查找的能力，對于有N節(jié)點(diǎn)的B樹，檢索一條記錄的復(fù)雜度為O(LogN)。相當(dāng)于二分查找。
哈希索引只能做等于查找，但是無論多大的Hash表，查找復(fù)雜度都是O(1)。
顯然，如果值的差異性大，并且以等值查找（=、 、、in）為主，Hash索引是更高效的選擇，它有O(1)的查找復(fù)雜度。
如果值的差異性相對較差，并且以范圍查找為主，B樹是更好的選擇，它支持范圍查找。
這個(gè)問題涉及到“覆蓋索引”這個(gè)概念。你第一個(gè)查詢是查count(*)，實(shí)際上值需要使用索引test，就能完全得到結(jié)果，不需要回聚簇索引查其他字段，因此InnoDB認(rèn)為用這個(gè)所以比全表掃描快。
而第二個(gè)查詢因?yàn)橐L問iMoney，需要“回表”，用不上覆蓋索引。
另外一個(gè)原因是索引字段的順序，如果你把 test定義為(iType, dtEventTime)， 這個(gè)查詢應(yīng)該就能用上test這個(gè)索引。而現(xiàn)在字段順序會(huì)導(dǎo)致你這個(gè)查詢，即使要用這個(gè)索引，也只能用到第一個(gè)字段。
作為一名程序員，在求職面試時(shí)，不知你有沒有遇到類似這樣的問題。
張工是一名java程序員，最近到一家軟件公司應(yīng)聘軟件開發(fā)崗位，面試官問了他關(guān)于MySql索引這樣的一個(gè)問題。
對于這個(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í)候就不要對dt進(jìn)行格式轉(zhuǎn)換了，
這樣索引就失效了。
而是應(yīng)該對 20220207做格式處理
這樣dt索引才不會(huì)失效。
例如我們在訂單表tb_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>
由于對索引列進(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ī)則，我們再回過頭看看之前的查詢語句
select '12345678936' = 12345678936
返回1 所以這時(shí)候就把左側(cè)的列值12345678936轉(zhuǎn)成數(shù)字。
關(guān)于MySql索引失效的問題先簡單寫到這，建議平時(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)合索引：
索引刪除
重慶分公司
重慶分公司
