文章目錄
- 可能導致索引失效的場景
- 1. 索引列不獨立. 獨立是指: 列不能是運算式的一部分, 也不能是函式的引數
- 解決方案:
- 2. 使用了左模糊
- 3. 使用or查詢的部分欄位沒有索引
- 解決方案:
- 4. 字串條件為使用 ' ' 引起來
- 解決方案: 添加 ' ' , 規范的撰寫 sql
- 5. 不符合最左前綴原則的查詢
- 解決方案:
- 6. 索引欄位建議添加 NOT NULL 約束
- 解決方案:
- 7. 隱式轉換導致索引失效
- 索引型別(常用的6種):
- 1. all
- 2. index
- 3. range
- 4. ref
- 5. ref_eq
- 6. const
可能導致索引失效的場景
1. 索引列不獨立. 獨立是指: 列不能是運算式的一部分, 也不能是函式的引數
例如:
where條件的左側有計算
explain
select * from employees where emp_no + 1 = 10003;
此時的 explain 為 all
解決方案:
將 條件改到where條件的右面 :
explain
select * from exployee where emp_no = 10003 -1;
此時的explain 為 const
2. 使用了左模糊
例如:
explain * from employees where first_name like '%Geo%'
解決方案:
盡量避免使用左連接, 如果避免不了, 可以考慮使用搜索引擎去解決;
explain * from employees where first_name like 'Geo%'
3. 使用or查詢的部分欄位沒有索引
例如: first_name 有索引, last_name 沒有索引, 則如下陳述句不能使用索引
explain
select * from employees where first_name = 'Georgi'
or last_name = 'Georgi';
解決方案:
額外添加 or 條件的索引, 這時候資料庫會默認的將兩個索引合并, 避免了全表掃描.
4. 字串條件為使用 ’ ’ 引起來
例: 字串條件未使用 ’ ’ 引起來 (dept_no 為字串)
explain
select * from dept_emp
where dept_no = 3;
結果type 為 all
解決方案: 添加 ’ ’ , 規范的撰寫 sql
5. 不符合最左前綴原則的查詢
解決方案:
調整索引的順序, 變成index(first_name, last_name)
6. 索引欄位建議添加 NOT NULL 約束
單列索引無法存盤null值, 復合索引無法存盤全為null的值
查詢時, 采用 is null 條件時, 不能利用到索引, 只能全表掃描
mysql 官方建議盡量把欄位定義為 NOT NULL
解決方案:
把索引欄位設定成 NOT NULL, 甚至可以把所有欄位都設定為 NOT NULL, 并且為欄位設定默認值
7. 隱式轉換導致索引失效
在創建表的時候要盡量規范: 比如使用int 或者 bigint
索引型別(常用的6種):
索引失效則為 all (全表掃描)
覆寫索引: 從索引中直接獲取查詢結果，要使用覆寫索引需要注意select查詢列中包含在索引列中；where條件包含索引列或者復合索引的前導列；查詢結果的欄位長度盡可能少，
1. all
“全表掃描”，通常意味著你的sql陳述句處于一種最原生的狀態，有很大的優化空間, 我們都要避免這樣型別的查找，除非你不得不這樣做，
2. index
這種連接型別只是另外一種形式的全表掃描，只不過它的掃描順序是按照索引的順序，這種掃描根據索引然后回表取資料，和all相比，他們都是取得了全表的資料，而且index要先讀索引而且要回表隨機取資料.
3. range
range指的是有范圍的索引掃描，相對于index的全索引掃描，它有范圍限制，因此要優于index，關于range比較容易理解，需要記住的是出現了range，則一定是基于索引的，同時除了顯而易見的between，and以及’>’,’<'外，in和or也是索引范圍掃描，
4. ref
出現該連接型別的條件是： 查找條件列使用了索引而且不為主鍵和unique，
其實，意思就是雖然使用了索引，但該索引列的值并不唯一，會有重復，這樣即使使用索引快速查找到了第一條資料，仍然不能停止，要進行目標值附近的小范圍掃描，
但它的好處是它并不需要掃描全表，因為索引是有序的，即便有重復值，也是在一個非常小的范圍內掃描，
5. ref_eq
ref_eq 與 ref相比牛的地方是，它知道這種型別的查找結果集只有一個？什么情況下結果集只有一個呢！那便是使用了主鍵或者唯一性索引進行查找的情況，比如根據學號查找某一學校的一名同學，在沒有查找前我們就知道結果一定只有一個，所以當我們首次查找到這個學號，便立即停止了查詢，這種連接型別每次都進行著精確查詢，無需過多的掃描，因此查找效率更高，當然列的唯一性是需要根據實際情況決定的，
6. const
通常情況下，如果將一個主鍵放置到where后面作為條件查詢，mysql優化器就能把這次查詢優化轉化為一個常量，至于如何轉化以及何時轉化，這個取決于優化器，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/265467.html
標籤：其他
上一篇：資料庫-事務