查看索引使用show index和show create table；2. 創建索引用create index或alter table；3. 驗證索引使用explain分析查詢計劃；4. 索引失效原因包括數據類型不匹配、函數操作、模糊查詢以%開頭、or條件復雜、優化器判斷選擇性低等；5. 常見索引類型有b-tree（默認，適合等值和范圍查詢）、哈希（僅memory引擎，適合等值查詢）、全文索引（適合文本搜索）、空間索引（適合地理空間查詢）；6. 索引策略應優先高選擇性列、考慮查詢模式、合理使用復合索引并遵循最左前綴原則、避免過度索引、利用覆蓋索引減少回表；7. 索引維護包括optimize table整理碎片、analyze table更新統計信息、監控索引使用情況并刪除冗余索引。
mysql中查看索引主要通過SHOW INDEX或SHOW CREATE TABLE命令，創建索引則使用CREATE INDEX或ALTER TABLE，驗證效果最直接的方式是使用EXPLaiN語句分析查詢計劃。這幾個操作是數據庫性能優化的基石，理解它們至關重要。
解決方案
要查看MySQL中的索引，最常用的方式是：
SHOW INDEX FROM your_table_name;
這條命令會列出指定表的所有索引信息，包括索引名、列名、索引類型（如BTREE）、是否唯一、基數（Cardinality）等。基數是一個很重要的指標，它表示索引列中不重復值的數量，通常越高代表索引選擇性越好。
另一種查看方式是：
SHOW CREATE TABLE your_table_name;
這條命令會顯示創建表的完整sql語句，其中包含了所有索引的定義。這在需要快速復制表結構時特別有用。
至于創建索引，我們通常有兩種主要方式：
-- 方式一：直接創建索引 CREATE INDEX idx_your_column ON your_table_name (your_column); -- 方式二：通過ALTER TABLE添加索引 ALTER TABLE your_table_name ADD INDEX idx_another_column (another_column);
這里idx_your_column和idx_another_column是索引的名稱，你可以根據實際情況命名，通常建議以idx_開頭，后面跟上表名和列名，方便識別。如果你需要創建唯一索引，只需將INDEX替換為UNIQUE INDEX。
驗證索引效果的核心工具是EXPLAIN。在執行任何你認為可能受益于索引的查詢前加上EXPLAIN：
EXPLAIN SELECT * FROM your_table_name WHERE your_column = 'some_value';
EXPLAIN的輸出會告訴你MySQL在執行查詢時是如何使用索引的。關注type、key、key_len、ref、rows和Extra這幾列。
- type列通常是const、eq_ref、ref、range這些值時表示索引被有效利用，而ALL則意味著全表掃描，通常是需要優化的信號。
- key列會顯示實際使用的索引名稱。
- rows列預估了MySQL需要檢查的行數，這個數字越小越好。
- Extra列如果出現using index（覆蓋索引）或Using where（索引用于過濾），都表明索引發揮了作用。
這是一個迭代的過程：創建索引，然后用EXPLAIN驗證，如果效果不佳，可能需要調整索引策略或查詢語句。
為什么索引有時會失效？
這個問題，我想是很多數據庫優化新手會遇到的“陷阱”。你明明建了索引，可查詢就是跑不快，這感覺挺挫敗的。索引失效的原因其實挺多的，有些是顯而易見的，有些則比較隱蔽。
一個常見的情況是數據類型不匹配。比如你的user_id列是int類型，但你在查詢時卻寫成了WHERE user_id = ‘123’（字符串形式）。MySQL在比較時可能會進行隱式類型轉換，導致無法使用索引。再比如，對索引列進行函數操作，像WHERE date(create_time) = ‘2023-01-01’，create_time即使有索引，也會因為DATE()函數而失效，因為函數會作用于每一行數據，使得索引無法直接定位。
另一個經典場景是模糊查詢，特別是LIKE ‘%keyword’這種以通配符開頭的模式。索引通常是按B-tree結構存儲，它擅長處理范圍查詢和前綴匹配，但對于任意位置的匹配就無能為力了。如果你的業務確實需要這種全模糊匹配，可能需要考慮使用全文索引（Full-Text Index）或其他搜索技術。
OR條件有時也會讓索引“犯迷糊”。當OR連接的條件中，有一個條件沒有索引，或者優化器認為使用索引的成本高于全表掃描時，它就可能放棄使用索引。
還有就是優化器自身的“判斷”。MySQL的查詢優化器非常智能，它會根據統計信息、數據分布等因素來決定是否使用索引。如果它覺得某個索引的選擇性太低（比如在一個只有幾個不同值的列上建索引），或者查詢返回的結果集太大（比如要返回表中90%的數據），它可能會認為全表掃描反而更快，從而放棄使用索引。我曾遇到過一個場景，一個status字段只有0和1兩個值，即使建了索引，查詢WHERE status = 1也經常不走索引，因為優化器覺得掃半張表和掃全表沒多大區別，甚至全表掃描更直接。
此外，索引列上允許NULL值，且查詢條件是IS NULL或IS NOT NULL時，索引的行為可能會比較復雜，有時會走，有時不走，這取決于優化器對數據分布的判斷。總之，索引失效并不是索引“壞了”，而是它在當前查詢上下文中的“效用”不足以被優化器采納。
索引類型與選擇策略
MySQL支持多種索引類型，每種都有其適用場景，理解這些差異是高效建索引的前提。
最常見的是B-Tree索引，這也是MySQL（尤其是InnoDB存儲引擎）默認和最常用的索引類型。它適用于各種等值查詢、范圍查詢、排序和分組操作。幾乎所有你在數字、字符串、日期列上創建的索引，如果不特別指定，都是B-Tree索引。它的優勢在于平衡樹結構，能保證查詢效率相對穩定，且支持前綴匹配。
然后是哈希索引（HASH），主要在Memory存儲引擎中用到，InnoDB中只能用于自適應哈希索引（由MySQL內部自動創建和管理）。哈希索引的特點是查詢速度極快，因為它直接通過哈希算法定位數據，時間復雜度接近O(1)。但它的缺點也很明顯：不支持范圍查詢，不支持排序，也不支持模糊匹配。它只適用于等值查詢。所以，如果你看到Memory表，哈希索引可能是一個好選擇，但對于InnoDB，通常我們不直接創建哈希索引。
全文索引（FULLTEXT），顧名思義，是為文本內容搜索而設計的。當你需要在文章標題、內容等大文本字段中進行關鍵詞搜索時，B-Tree索引往往效率低下，全文索引就能派上用場了。MySQL的全文索引支持自然語言模式、布爾模式等，但它的功能和效果與專業的搜索引擎（如elasticsearch、solr）相比還是有差距的，通常只適用于一些簡單的文本搜索場景。
空間索引（SPATIAL），用于地理空間數據類型，如POINT、LINESTRING、POLYGON。如果你在做地圖應用、LBS服務，需要查詢某個區域內的點，或者計算距離，那么空間索引就是你的利器。它通常結合R-tree算法實現，能夠高效處理空間關系查詢。
在選擇索引策略時，有幾個點我個人覺得特別重要：
- 高選擇性列優先：選擇性高的列（即不重復值多的列）更適合創建索引，因為它們能更快地縮小查詢范圍。比如用戶ID、訂單號，它們的唯一性很高，非常適合做索引。
- 考慮查詢模式：你的大部分查詢是等值查詢、范圍查詢，還是排序、分組？針對性地創建索引。如果經常需要按某個字段排序，可以考慮在該字段上創建索引。
- 復合索引（聯合索引）：當查詢條件涉及多個列時，可以考慮創建復合索引。例如INDEX (col1, col2, col3)。復合索引遵循“最左前綴原則”，即只有當查詢條件包含索引的最左邊列時，索引才可能被使用。比如WHERE col1 = ‘A’ AND col2 = ‘B’會使用，但WHERE col2 = ‘B’則不會。合理設計復合索引能顯著減少索引數量，提高查詢效率。
- 避免過度索引：索引不是越多越好。每個索引都會占用磁盤空間，并且在插入、更新、刪除數據時，數據庫需要維護這些索引，這會帶來額外的開銷。我見過一些系統，為了所謂的“優化”，給每個字段都加了索引，結果寫操作性能急劇下降。
- 覆蓋索引（Covering Index）：如果一個查詢需要的所有列都在索引中，那么MySQL可以直接從索引中獲取數據，而無需回表（即不再去數據行中查找數據），這會大大提高查詢效率。EXPLAIN結果中Extra列顯示Using index就是覆蓋索引的標志。
選擇合適的索引類型和策略，需要對業務場景和數據特性有深入的理解，并不是一蹴而就的事情。
索引維護與優化
索引建好了，也跑得不錯，是不是就萬事大吉了？不，索引也需要“保養”。就像汽車需要定期維護一樣，數據庫索引也可能因為數據變化而變得不那么高效，甚至需要調整。
一個比較常見的維護操作是重建或優化索引。當表中的數據經過大量的插入、刪除、更新操作后，索引可能會出現碎片化，導致其物理存儲不再連續，從而降低查詢效率。這時，你可以使用OPTIMIZE TABLE命令來整理表和索引：
OPTIMIZE TABLE your_table_name;
OPTIMIZE TABLE會重建表和索引，消除碎片，并更新統計信息。但要注意，這個操作可能會鎖定表，對于大表來說，執行時間會比較長，可能會影響線上業務。所以，通常建議在業務低峰期執行，或者考慮使用在線DDL工具（如Percona Toolkit的pt-online-schema-change）來避免長時間鎖定。
另一個重要的方面是監控索引使用情況。你創建的索引真的被用到了嗎？還是只是躺在那里占用空間？MySQL的performance_schema和information_schema提供了一些視圖，可以幫助你了解索引的使用情況。例如，你可以查看information_schema.STATISTICS表中的Index_used列（如果你的MySQL版本支持），或者更細致地通過performance_schema.table_io_waits_summary_by_index_usage等視圖來分析。
如果發現某個索引長期沒有被使用，或者使用頻率極低，那么它可能就是一個冗余索引。冗余索引不僅占用存儲空間，還會增加寫操作的開銷。對于這類索引，果斷地將其刪除通常是一個明智的選擇：
DROP INDEX idx_unused_column ON your_table_name;
刪除索引前，務必仔細分析，確保它不是被某個不常運行但至關重要的查詢所依賴。
我個人在做索引優化時，還會特別關注索引的基數（Cardinality）。SHOW INDEX FROM your_table_name命令的輸出中就有這一列。基數表示索引列中不重復值的數量。理論上，基數越高，索引的選擇性越好，查詢效率越高。如果一個索引的基數很低（比如在一個只有“男”、“女”兩個值的性別字段上），那么這個索引的作用就非常有限，甚至可能不如全表掃描。MySQL的優化器會定期更新索引的統計信息，但如果數據變化非常頻繁，有時也需要手動ANALYZE TABLE your_table_name;來強制更新統計信息，確保優化器能做出最準確的判斷。
最后，要記住，索引優化是一個持續的過程，它不是一次性工作。隨著業務的發展、數據量的增長和查詢模式的變化，原有的索引策略可能不再是最優的。定期回顧和調整索引，是保持數據庫高性能的關鍵。
