> # 索引失效的場景、索引優化方案 > ### 索引失效的場景 - 字段類型不同(隱式類型轉換): 給一個字符串字段加索引, where 字段=123456 時候, 字段是 varchar(255), 而輸入的參數是整型,需要類型轉換導致全表掃描(類型符合轉換規則還是可以使用的 不同位數的類型互相轉換) - 表字符集不同(隱式字符編碼轉換): 通過字段關聯兩種表時, 一張表是utf8, 另一張表是utf8mb4也會導致全表掃描 - 字段使用了函數或表達式 where ABS(字段) = 7 , where 字段+1 > 9 - !=,<>,is null,is not null不會走索引 - 條件中帶有or, or中的每個列都有索引時才有用（要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引 ）（每一個or單處建索引，查詢的使用索引合并，用到多列索引） - 不符合最左匹配原則, 建立了組合索引, where 時沒出現前面的索引前面的列 - 在進行范圍查詢（比如>、=、<=、in等條件）時，當Mysql發現通過索引掃描的行記錄數超過全表的10%-30%時，優化器可能會放棄走索引，自動變成全表掃描 - like (like 'XXX%' 符合最左匹配原則可以用到索引， like '%XXX', '%XXX%' 無法使用索引) - 查詢數據量大的時候，導致優化器認為走全表查詢時間效率更佳 - NOT條件 - 我們知道建立索引時，給每一個索引列建立一個條目，如果查詢條件為等值或范圍查詢時，索引可以根據查詢條件去找對應的條目。反過來當查詢條件為非時，索引定位就困難了，執行計劃此時可能更傾向于全表掃描，這類的查詢條件有：<>、NOT、in、not exists > ### 索引優化方案 - 盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一直）），減少select \* - 索引列字符很長,會讓索引變大和慢, 這時可以通過前綴或后綴去優化(有時也會用到字段倒序存儲, Hash字段存儲) - 創建組合索引時, 把第一時間過濾完后數據量少的字段放在前面 - 組合索引a,b, 在建立a索引是冗余, 建立b索引不是, 組合索引a,b, 中的b是一個個比對 - 唯一索引比普通索引差, 如果業務在保存數據時能確保唯一就采用普通索引 - 查詢全表字段時, select 字段名 會比 select * 快 - 優化器選擇錯誤索引時, 強制優化器使用某個索引 : select * from table force index(索引名稱) - 聚簇索引的主鍵長度小, 有序自增, 避免修改和刪除 - 最左匹配原則,mysql會一直向右匹配直到遇到范圍查詢(>、<、like、between),就停止匹 - 主鍵不能太長而且插入時的主鍵最好是按順序遞增，否則對性能有很大影響 - 分頁數據如何優化? - 取出來數據的ID是有序自增, 可以通過代碼算出每一頁的ID范圍, 然后 select * from table where id bwteen 范圍1, 范圍2 - 取出來的數據是 ID無序, 可以先 select id from table limit xxx,xxx 然后 select * from table where id in (id值), 這樣兩次查詢都能用到索引 - [MySql千萬級limit優化方案](https://blog.csdn.net/sunct/article/details/90441965) - order by 優化 - 沒有order by時, 執行SQL返回的數據順序, 會按照使用到的索引排序返回, 需要回表的話會用主鍵排序返回 - [MySQL中order by 原理](https://www.szhulian.com/new/1646.html) - [Mysql學習之order by的工作原理](https://www.cnblogs.com/lamp01/p/10770172.html) - 按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count(\*) - count(主鍵 id) 來說，InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。 - 對于 count(1) 來說，InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個數字“1”進去，判斷是不可能為空的，按行累加。