MySQL不適合用來建構索引及索引失效的情況有哪些

結論

具體案例下文有詳盡描述

不適合建立索引的場景：

• ##資料量比較小的表不建議建立索引

• 有大量重複資料的欄位上不建議建立索引（類似：性別欄位）

• 需要進行頻繁更新的表不建議建立索引

• where、group by、order by後面的沒有使用到的欄位不建立索引

• 不要定義冗餘索引

索引失效的場景：

• 篩選條件使用不等於(!=、)

• 過濾條件使用is not null

• 在索引欄位上使用函數或進行計算

• 在使用聯合索引的時候，需要滿足“最佳左前綴法則”，否則失效

• 當使用了類型轉換也會導致索引失效

• 在使用範圍查詢的時候，聯合索引的部分欄位失效(where age >18)

• 在like欄位中，如果是以%開頭，索引失效(where name like ‘�c’)

• 在使用or進行查詢的時候，or前後出現非索引字段，索引失效

• 表和庫的字元集不一致，回導致索引失效

知識點：

• 每張表的索引不建議超過6個（佔用空間、降低表更新速度）

• 最終到底是否使用索引還是優化器進行決定的

• #優化器會根據資料量、資料庫版本、資料選擇讀取進行查詢代價的比較，從而決定是否使用索引

• 建立索引的時候將需要範圍匹配的字段建立在索引的尾部，避免失效

• 在建立表格的時候將欄位設為not null同時設定預設值，當需要尋找沒有值的記錄的時候就可以使用where xxx = 預設值，放置使用is not null導致索引失效

• 在頁面搜尋時，請使用左側或全文模糊匹配（like '�c'）

• #對於過濾性較好的欄位建立在聯合索引的前面，這樣就可以優先過濾比較多的資料

不建議建立索引的場景

場景一：資料少的表

當資料比較少的時候，索引的優勢就不明顯了，因為資料庫的儲存引擎也是非常快的，相較於需要查詢索引在進行回表操作，可能直接查詢的效能會更高一些，所以資料相對較少的表不建議建立索引

場景二：有大量重複資料的字段

類似於性別字段，只有「男」和「女」兩個不同的值，所以索引一半的資料是「男」一半的資料是“女”，那麼建立索引並不能進行快速的查詢等，所以不建議在有大量重複資料的列上建立索引

場景三：頻繁更新的表（update /delete/insert）

因為表中更新資料的時候，索引也是需要進行對應的維護的，如果一個表近期需要頻繁的進行增刪改操作，那麼就需要耗費大量的時間去維護索引，不建議建立索引，可以在需要進行頻繁的更新操作的時候將索引刪除，更新完畢之後重建索引

場景四：沒有使用的字段（where/group by/order by）

不是where/group by/order by後面的欄位沒有必要建立索引，因為不會使用到該索引

場景五：不要定義冗餘索引

create index username_password_address on xiao(username,password,address);
-- 如果建立了第一个索引，那么就没有必要建立第二个索引
create index username on xiao (username);
--第二个索引就是冗余索引，因为第一个已经是先根据username排序的索引
--也就是第二个索引的功能完全可以由第一个索引实现

這裡因為username作為第一個聯合索引的第一個字段，所以索引就是按照username進行排序，在username相同的情況下按照password、address排序，所以也就是實現了單獨拿username列作為索引的功能，即第二個索引就是多餘的

索引失效的場景

場景一：在建立索引的欄位上進行運算(函數等)，導致索引失效

這裡首先是給age創建了索引，在第一次查詢過程中使用了age索引，但是第二次key值為null（索引失效），導致索引失效的原因在於第二次查詢的時候where後面對age進行了計算，計算機不知道執行的是什麼計算所以會將age 1計算後與1比較，索引失效

類似於在字段上使用函數concat()等都會導致索引失效

#場景二：使用不等於(where age != 18)

當使用等值運算，那麼是可以在索引中進行查找的，但是如果是不等於，那麼則需要遍歷所有數據，所以失效

explain select * from xiaoyuanhao where age = 18;
explain select * from xiaoyuanhao where age != 18;
--这里是在age字段上建立了普通索引，第二个查询时候索引失效

場景三：使用is not null索引失效

與不等於一樣，如果使用的是is not null，那麼就需要進行全部數據的遍歷操作，索引失效，但是如果使用的是is null那麼依舊是可以使用索引的

--这里是在age字段上建立了普通索引，第二个查询时候索引失效
explain select * from xiaoyuanhao where age is null;
--可以正常使用索引
explain select * from xiaoyuanhao where age is not null;
--索引失效

場景四：在使用聯合索引的時候沒有遵循最佳左前綴法則

CREATE INDEX age_classid_name ON student(age,classId,NAME);
EXPLAIN SELECT * FROM student WHERE classId = 30 AND NAME = 'xiaoyuanhao';
-- 因为没有使用age字段，所以没有准许最佳左前缀原则，索引失效

从这里可以看出是没有使用索引的(key = null)，因为创建的索引是先按照age进行排序，在age相同的情况下按照classId和name排序，如果在查询的时候需要直接按照classId进行排序查找，那么就无法使用该索引，即索引失效。

如果需要使用使用索引，那么就一定需要到联合索引的第一个字段age，案例如下

EXPLAIN SELECT * FROM student WHERE age = 10 AND NAME = 'xiaoyuanhao';
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 33 AND NAME = 'xiaoyuanhao';
--两者都是使用age字段索引，所以索引有效

场景五：类型转换导致索引失效

CREATE INDEX NAME ON student(NAME);
-- 这里的name字段是varchar类型
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 本次查询是可以使用索引的，因为类型都是一致的，都是字符串
EXPLAIN SELECT * FROM student WHERE NAME = 123;
-- 本次查询则无法使用索引，因为是将数字类型123转换为字符类型

没有发生类型转换，使用索引key = name

发生了类型转换，无法使用索引kye = null，索引失效

使用索引的时候一定需要保证数据类型是一致的，否则系统就需要进行转换，那么就无法使用索引

场景六：使用范围查询导致联合索引其他字段失效

create index age_classId_name on student (age,classId,name);
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId > 20 AND NAME = 'xiaoyuanhao';
-- 这里只能使用age,classId，索引的前两个字段
EXPLAIN SELECT * FROM student WHERE age = 10 AND classId = 20 AND NAME = 'xiaoyuanhao';
-- 这里可以使用完整的索引，因为都是等值连接

在classId字段上使用范围查询，导致name字段失效，有效索引长度为63

使用的都是等值匹配，整个索引皆可用，有效索引长度为73

也就是在对于联合索引来说，如果在使用的时候是等值匹配，那么就可以重复的利用索引，如果不是等值匹配，那么该字段也是可以使用索引的，但是该字段右边的字段就将失效

建议在建立索引的时候将需要范围匹配的字段建立在索引的最后面

场景七：在使用like的时候，如果以%开头导致索引失效

EXPLAIN SELECT * FROM student WHERE NAME LIKE 'abc%';
-- 可以正常使用索引
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';
-- 这里在like中，%在前面无法使用索引

key = name，使用了该索引，索引有效

key = null，索引失效

因为建立的索引实际上是按照整个字符串的从第一个开始进行比较排序的，所以在使用like的时候，也只能够重现进行比较，如果使用的是’%abc’，那么查询的就是以abc结尾的数据，无法使用索引

场景八：or前后出现非索引字段，索引失效

-- 该表中只有name字段上的索引
CREATE INDEX NAME ON student(NAME);
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao';
-- 这里是可以使用name索引的
EXPLAIN SELECT * FROM student WHERE NAME = 'xiao' OR classId = 1001;
-- 这个则无法使用索引，进行的是全表扫描

key = null，无法使用索引，or条件中出现非索引字段

因为如果name不等于’xiao’的时候那么就会继续判断classId是否等于1001，那么实际上还是会进行全表扫描，所以索引失效（也就是进行name判断的时候可以使用索引，但是在判断classId的时候又要全表扫描，那么优化器就直接进行全表扫描），但是如果or前后的字段都有索引了，那么就就会使用索引

以上是MySQL不適合用來建構索引及索引失效的情況有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章！