大家好，我是閑水，每天更新java最新最熱技術，對java感興趣的朋友記得關注一下哦。

注意 ：這是SQL性能優化第三章，點擊關注查看前置內容。

上篇文章我們主要了解了索引優化的標尺"Explain"怎么用，這一張，我們就要將索引怎么建立，以及索引失效的例子了。廢話不多說我們開始。

一 單表查詢建立索引

系統中經常出現的sql語句如下： ( SQL_NO_CACHE 是不查找緩存，以免對我們的結果造成影響)

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

未建索引，0.15s

我們先運行一下單表查詢都用了0.15s，這個效率已經很低了(這里我們模擬了一百萬條數據)

索引應該如何建立 ？

單表查詢建立索引的規則是where條件后，最好都建立索引

創建索引：CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)

讓我們看一下索引的效果

建立索引后小數后兩位已經無法統計

大家可以看到，索引的效果還是不錯的。

第二個規則 最佳左前綴法則

如果系統經常出現的sql如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'

或者

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'

那原來的idx_age_deptid_name 還能否正常使用？

用到了一半

沒用到

可以看到，第一個只有部分被使用到了，圖二沒有用到索引

這是為什么呢，這就要說到我們的最佳左前綴法則，如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。

三 不能在索引列上做任何操作(計算、函數、(自動or手動)類型轉換)，會導致索引失效而轉向全表掃描

這兩條sql哪種寫法更好

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%'

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc'

第一個

第二個

切記不要用計算、函數、(自動or手動)類型轉換。

四.mysql 在使用不等于(!= 或者<>)的時候無法使用索引會導致全表掃描

五.is not null 也無法使用索引,但是is null是可以使用索引的

六.like以通配符開頭('%abc...')mysql索引失效會變成全表掃描的操作

七.字符串不加單引號索引失效

八.存儲引擎不能使用索引中范圍條件右邊的列

大家切記在建索引的時候不要違反這些規定

下面是我自己總結的一些建議送給大家：

1.對于單鍵索引，盡量選擇針對當前query過濾性更好的索引

2.在選擇組合索引的時候，當前Query中過濾性最好的字段在索引字段順序中，位置越靠前越好。

3.在選擇組合索引的時候，盡量選擇可以能夠包含當前query中的where字句中更多字段的索引

4.在選擇組合索引的時候，如果某個字段可能出現范圍查詢時，盡量把這個字段放在索引次序的最后面

5.書寫sql語句時，盡量避免造成索引失效的情況。

最后給大家一個練習題，看一下自己能答對幾道題

Where語句 索引是否被使用

where a = 3 Y,使用到a

where a = 3 and b = 5 Y,使用到a，b

where a = 3 and b = 5 and c = 4 Y,使用到a,b,c

where b = 3 或者 where b = 3 and c = 4 或者 where c = 4N

where a = 3 and c = 5 使用到a， 但是c不可以，b中間斷了

where a = 3 and b > 4 and c = 5 使用到a和b， c不能用在范圍之后，b斷了

where a is null and b is not null is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用

where a <> 3 不能使用索引

where abs(a) =3 不能使用 索引

where a = 3 and b like 'kk%' and c = 4Y, 使用到a,b,c

where a = 3 and b like '%kk' and c = 4Y, 只用到a

where a = 3 and b like '%kk%' and c = 4Y, 只用到a

where a = 3 and b like 'k%kk%' and c = 4Y,使用到a,b,c

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的怎么看我装的sql能不能用_深入浅出sql优化（三）之单表索引优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。