mysql索引失效的核心原因在于查詢方式或優化器未能正確識別意圖，常見問題包括：1.在索引列使用函數或運算；2.數據類型不匹配；3.模糊查詢前綴導致無法利用b-tree結構；4.or條件中部分無索引或類型不兼容；5.not in和!=等非等值查詢被優化器放棄；6.違反聯合索引的最左匹配原則；7.優化器誤判統計信息。解決方法依次為：將函數操作移至等號右側或改用范圍查詢、確保數據類型一致、避免前導模糊或使用全文索引、拆分or為union all或確保各條件均有索引、謹慎使用非等值查詢、設計聯合索引時遵循區分度高列優先且避免跳過左側列、定期更新統計信息并結合explain分析執行計劃。此外，創建索引需權衡查詢效率與寫入成本，選擇區分度高的列、避免冗余索引、合理使用前綴索引和覆蓋索引，并持續維護索引以適應數據變化。
mysql索引失效，往往不是索引本身“壞了”，而是我們寫查詢的方式或者優化器“沒看懂”我們的意圖，導致它放棄了走索引。這背后，常常是數據類型不匹配、函數運算、模糊查詢前綴、或者聯合索引的“最左匹配”原則沒被正確理解。而創建索引時，我們需要像雕刻師一樣精細，既要考慮查詢效率，又要權衡寫入成本和存儲空間，避免盲目堆砌。
解決方案
在我看來，避免mysql索引失效，核心在于理解索引的工作機制，并與查詢語句“步調一致”。這就像你有一本按拼音排序的字典，如果你想找一個字，卻只知道它的筆畫，那這本字典就幫不上忙了。
最常見的“坑”就是在索引列上使用函數或進行算術運算。比如，你的create_time列有索引，但你寫了WHERE YEAR(create_time) = 2023，Mysql優化器就懵了。它不知道YEAR()函數處理后的結果在索引里是什么位置，索性就放棄了索引。正確的做法應該是將函數操作放在等號的右邊，或者直接使用范圍查詢，例如WHERE create_time BETWEEN ‘2023-01-01 00:00:00’ AND ‘2023-12-31 23:59:59’。同理，WHERE price / 100 > 10這種運算也會讓索引“啞火”，應改為WHERE price > 10 * 100。
數據類型不一致也是個隱形殺手。如果你的id列是int類型，但你查詢時寫成了WHERE id = ‘123’，MySQL可能會嘗試進行隱式類型轉換。這個轉換操作就可能導致索引失效。所以，務必確保查詢條件中的數據類型與列的實際類型保持一致。
模糊查詢中的“前導模糊”，也就是LIKE ‘%關鍵字%’，這幾乎是索引的死敵。B-Tree索引是按照從左到右的順序進行排序的，你前面加個百分號，就相當于告訴索引：“我不知道開頭是什么，你從頭到尾給我找一遍吧！”這種情況下，索引形同虛設。如果業務上確實需要這樣的查詢，可以考慮使用全文索引（Full-Text Index）或者在某些特定場景下，通過反轉字符串并創建反轉索引來應對，但這通常更復雜。
OR連接的條件也需要警惕。如果OR連接的多個條件中，有一個條件沒有索引，或者不同條件的索引類型不兼容，那么整個查詢可能就會走全表掃描。最佳實踐是盡量避免OR，如果可以，嘗試將查詢拆分成多個UNION ALL語句，或者確保OR連接的每個條件都能夠有效利用索引。
NOT IN和!=這類非等值查詢，在某些數據量和分布情況下，優化器可能會認為全表掃描比走索引更劃算，從而導致索引失效。這并非絕對，但確實是需要注意的場景。
聯合索引的“最左匹配原則”是個老生常談的話題，但它卻是導致索引失效最常見的原因之一。如果你創建了INDEX (col1, col2, col3)，但查詢條件是WHERE col2 = ‘xxx’，那么這個索引就無法被利用。索引的利用必須從最左邊的列開始。即使你的查詢是WHERE col2 = ‘xxx’ AND col1 = ‘yyy’，MySQL優化器通常足夠智能，會幫你調整順序，但如果你只查詢col2，那就真的沒辦法了。
最后，別忘了MySQL優化器的“誤判”。有時候，即使你的SQL寫得再規范，如果表的數據量太小，或者統計信息不準確，優化器可能會覺得走全表掃描更快，而放棄使用索引。這時候，你可以嘗試使用FORCE INDEX強制指定索引，或者運行ANALYZE table table_name來更新表的統計信息。
聯合索引：它到底有多“左”？
聯合索引的“最左匹配原則”聽起來簡單，但實際應用中總有人掉坑。它說的其實是，MySQL在利用聯合索引時，會從索引的最左側列開始匹配，一旦遇到范圍查詢（>、
舉個例子，你有一個聯合索引idx_name_age_city (name, age, city)。
- select * FROM users WHERE name = ‘張三’：能用到name列的索引。
- SELECT * FROM users WHERE name = ‘張三’ AND age = 25：能用到name和age列的索引。
- SELECT * FROM users WHERE name = ‘張三’ AND age = 25 AND city = ‘北京’：能用到name、age和city列的索引。
- SELECT * FROM users WHERE age = 25：完全無法利用這個索引，因為它沒有從name開始。
- SELECT * FROM users WHERE name = ‘張三’ AND city = ‘北京’：能用到name列的索引，但city列無法利用索引進行過濾，因為age列被跳過了。
- SELECT * FROM users WHERE name = ‘張三’ AND age > 20 AND city = ‘北京’：能用到name和age列的索引。但由于age > 20是一個范圍查詢，city列就無法再利用索引進行過濾了。它可能會在索引中找到所有name=’張三’且age>20的記錄，然后對這些記錄進行回表操作，再在內存中過濾city=’北京’。
這告訴我們，在設計聯合索引時，需要將最常用于等值查詢、且區分度高的列放在聯合索引的前面。然后，將那些可能用于范圍查詢的列放在中間，最后是那些不常用于查詢或區分度不高的列。理解這一點，對于構建高效的復合查詢至關重要。
索引創建：哪些坑是你沒想到的？
創建索引并非一蹴而就，它是一門平衡的藝術。我們總想讓查詢更快，但盲目創建索引，反而可能適得其反。
首先，選擇合適的列至關重要。并非所有列都適合創建索引。那些經常出現在WHERE子句、JOIN條件、ORDER BY或GROUP BY子句中的列是首選。同時，要考慮列的區分度（Cardinality）。一個列如果只有“男”和“女”兩個值，那么為其創建索引的意義就不大，因為區分度太低，優化器很可能直接選擇全表掃描。相反，用戶ID、訂單號這類唯一性強的列，其區分度極高，非常適合做索引。
其次，索引并非越多越好。每個索引都需要占用磁盤空間，更重要的是，每次對表進行INSERT、UPDATE、delete操作時，MySQL不僅要修改數據本身，還要更新所有相關的索引。索引越多，寫入操作的性能開銷就越大。我見過一些系統，為了提升查詢速度，給一張表創建了十幾個甚至幾十個索引，結果導致寫入性能急劇下降，得不償失。
對于長字符串列的索引，直接為整個字符串創建索引可能會占用大量空間，并且降低索引效率。這時候，可以考慮使用前綴索引，例如CREATE INDEX idx_desc ON products(description(20))，只索引字符串的前20個字符。這能有效節省空間，但缺點是可能會降低索引的選擇性，因為前綴可能不唯一。
覆蓋索引（Covering Index）是一個高級技巧，它能極大提升查詢性能。如果你的查詢只需要從索引中獲取數據，而不需要回表（即訪問原始數據行），那么這個索引就是覆蓋索引。例如，SELECT name, age FROM users WHERE name = ‘張三’ AND age = 25，如果idx_name_age是一個聯合索引，那么這個查詢就可以直接從索引中獲取name和age，無需再去查數據行，性能自然飆升。設計查詢時，盡量讓查詢的列都在索引中，是一個值得追求的目標。
另外，別忘了主鍵和唯一索引。它們本身就是一種特殊的索引，MySQL會對其進行優化，并且主鍵更是聚簇索引，直接決定了數據在磁盤上的物理存儲順序，其重要性不言而喻。
最后，索引并非一勞永逸。數據分布會變化，查詢模式也會演進。定期使用ANALYZE TABLE更新表的統計信息，讓優化器能做出更準確的判斷。
索引的“雙刃劍”：性能與維護的平衡藝術
索引無疑是提升數據庫查詢性能的利器，但它也是一把“雙刃劍”。我在實際工作中深有體會，索引的價值體現在查詢的加速，而它的成本則體現在寫入性能的下降、磁盤空間的占用以及后續的維護復雜性上。
寫入性能的下降是索引最直接的負面影響。每一次數據修改，數據庫都需要額外的時間來更新所有相關的索引結構。當表上的索引數量過多，或者寫入操作非常頻繁時，這種開銷會變得非常顯著，甚至可能成為系統的瓶頸。這就要求我們在設計索引時，必須權衡查詢的頻率和寫入的頻率，避免為那些極少查詢但更新頻繁的列創建索引。
磁盤空間的占用也是一個不容忽視的問題。索引本身就是一種數據結構，需要存儲空間。對于數據量巨大的表，過多的索引可能會消耗大量的磁盤資源。這在一些存儲成本敏感的場景下，需要特別注意。
更深層次的挑戰在于索引的維護成本。隨著數據的不斷寫入和刪除，索引可能會出現碎片，影響查詢效率。雖然MySQL會自動管理一部分，但有時需要手動進行優化，例如通過OPTIMIZE TABLE來整理碎片。此外，當數據分布發生顯著變化時，舊的索引可能不再是最優選擇，甚至可能誤導優化器。這就要求我們持續監控慢查詢日志，并根據實際的業務負載和數據演進，對索引進行適時地調整和優化。
對于復雜查詢，尤其是那些涉及多個條件、多表連接、或者動態條件的查詢，想要通過單一或少數幾個索引來完全覆蓋所有場景，往往是不現實的。這時候，就需要結合EXPLaiN命令，深入分析查詢的執行計劃，看看哪些步驟是瓶頸，然后針對性地創建或調整索引。EXPLAIN的輸出，特別是type、rows、Extra這些字段，是理解索引使用情況和優化查詢的黃金指標。
在我看來，索引設計是一個持續迭代的過程，而不是一次性的任務。沒有“完美”的索引方案，只有“最適合當前業務和數據特點”的方案。我們需要像醫生診斷病情一樣，結合業務需求、數據特點和性能監控數據，不斷地調整和優化索引策略，才能真正發揮索引的最大效用，同時避免其負面影響。這其中，對業務的深刻理解和對數據庫原理的扎實掌握，缺一不可。
