mysql如何查看索引 mysql創(chuàng)建索引并驗(yàn)證效果步驟

查看索引使用show index和show create table；2. 創(chuàng)建索引用create index或alter table；3. 驗(yàn)證索引使用explain分析查詢計(jì)劃；4. 索引失效原因包括數(shù)據(jù)類型不匹配、函數(shù)操作、模糊查詢以%開(kāi)頭、or條件復(fù)雜、優(yōu)化器判斷選擇性低等；5. 常見(jiàn)索引類型有b-tree（默認(rèn)，適合等值和范圍查詢）、哈希（僅memory引擎，適合等值查詢）、全文索引（適合文本搜索）、空間索引（適合地理空間查詢）；6. 索引策略應(yīng)優(yōu)先高選擇性列、考慮查詢模式、合理使用復(fù)合索引并遵循最左前綴原則、避免過(guò)度索引、利用覆蓋索引減少回表；7. 索引維護(hù)包括optimize table整理碎片、analyze table更新統(tǒng)計(jì)信息、監(jiān)控索引使用情況并刪除冗余索引。

mysql中查看索引主要通過(guò)SHOW INDEX或SHOW CREATE TABLE命令，創(chuàng)建索引則使用CREATE INDEX或ALTER TABLE，驗(yàn)證效果最直接的方式是使用EXPLaiN語(yǔ)句分析查詢計(jì)劃。這幾個(gè)操作是數(shù)據(jù)庫(kù)性能優(yōu)化的基石，理解它們至關(guān)重要。

解決方案

要查看MySQL中的索引，最常用的方式是：

SHOW INDEX FROM your_table_name;

這條命令會(huì)列出指定表的所有索引信息，包括索引名、列名、索引類型（如BTREE）、是否唯一、基數(shù)（Cardinality）等。基數(shù)是一個(gè)很重要的指標(biāo)，它表示索引列中不重復(fù)值的數(shù)量，通常越高代表索引選擇性越好。

另一種查看方式是：

SHOW CREATE TABLE your_table_name;

這條命令會(huì)顯示創(chuàng)建表的完整sql語(yǔ)句，其中包含了所有索引的定義。這在需要快速?gòu)?fù)制表結(jié)構(gòu)時(shí)特別有用。

至于創(chuàng)建索引，我們通常有兩種主要方式：

-- 方式一：直接創(chuàng)建索引 CREATE INDEX idx_your_column ON your_table_name (your_column);  -- 方式二：通過(guò)ALTER TABLE添加索引 ALTER TABLE your_table_name ADD INDEX idx_another_column (another_column);

這里idx_your_column和idx_another_column是索引的名稱，你可以根據(jù)實(shí)際情況命名，通常建議以idx_開(kāi)頭，后面跟上表名和列名，方便識(shí)別。如果你需要?jiǎng)?chuàng)建唯一索引，只需將INDEX替換為UNIQUE INDEX。

驗(yàn)證索引效果的核心工具是EXPLAIN。在執(zhí)行任何你認(rèn)為可能受益于索引的查詢前加上EXPLAIN：

EXPLAIN SELECT * FROM your_table_name WHERE your_column = 'some_value';

EXPLAIN的輸出會(huì)告訴你MySQL在執(zhí)行查詢時(shí)是如何使用索引的。關(guān)注type、key、key_len、ref、rows和Extra這幾列。

• type列通常是const、eq_ref、ref、range這些值時(shí)表示索引被有效利用，而ALL則意味著全表掃描，通常是需要優(yōu)化的信號(hào)。
• key列會(huì)顯示實(shí)際使用的索引名稱。
• rows列預(yù)估了MySQL需要檢查的行數(shù)，這個(gè)數(shù)字越小越好。
• Extra列如果出現(xiàn)using index（覆蓋索引）或Using where（索引用于過(guò)濾），都表明索引發(fā)揮了作用。

這是一個(gè)迭代的過(guò)程：創(chuàng)建索引，然后用EXPLAIN驗(yàn)證，如果效果不佳，可能需要調(diào)整索引策略或查詢語(yǔ)句。

為什么索引有時(shí)會(huì)失效？

這個(gè)問(wèn)題，我想是很多數(shù)據(jù)庫(kù)優(yōu)化新手會(huì)遇到的“陷阱”。你明明建了索引，可查詢就是跑不快，這感覺(jué)挺挫敗的。索引失效的原因其實(shí)挺多的，有些是顯而易見(jiàn)的，有些則比較隱蔽。

一個(gè)常見(jiàn)的情況是數(shù)據(jù)類型不匹配。比如你的user_id列是int類型，但你在查詢時(shí)卻寫(xiě)成了WHERE user_id = ‘123’（字符串形式）。MySQL在比較時(shí)可能會(huì)進(jìn)行隱式類型轉(zhuǎn)換，導(dǎo)致無(wú)法使用索引。再比如，對(duì)索引列進(jìn)行函數(shù)操作，像WHERE date(create_time) = ‘2023-01-01’，create_time即使有索引，也會(huì)因?yàn)镈ATE()函數(shù)而失效，因?yàn)楹瘮?shù)會(huì)作用于每一行數(shù)據(jù)，使得索引無(wú)法直接定位。

另一個(gè)經(jīng)典場(chǎng)景是模糊查詢，特別是LIKE ‘%keyword’這種以通配符開(kāi)頭的模式。索引通常是按B-tree結(jié)構(gòu)存儲(chǔ)，它擅長(zhǎng)處理范圍查詢和前綴匹配，但對(duì)于任意位置的匹配就無(wú)能為力了。如果你的業(yè)務(wù)確實(shí)需要這種全模糊匹配，可能需要考慮使用全文索引（Full-Text Index）或其他搜索技術(shù)。

OR條件有時(shí)也會(huì)讓索引“犯迷糊”。當(dāng)OR連接的條件中，有一個(gè)條件沒(méi)有索引，或者優(yōu)化器認(rèn)為使用索引的成本高于全表掃描時(shí)，它就可能放棄使用索引。

還有就是優(yōu)化器自身的“判斷”。MySQL的查詢優(yōu)化器非常智能，它會(huì)根據(jù)統(tǒng)計(jì)信息、數(shù)據(jù)分布等因素來(lái)決定是否使用索引。如果它覺(jué)得某個(gè)索引的選擇性太低（比如在一個(gè)只有幾個(gè)不同值的列上建索引），或者查詢返回的結(jié)果集太大（比如要返回表中90%的數(shù)據(jù)），它可能會(huì)認(rèn)為全表掃描反而更快，從而放棄使用索引。我曾遇到過(guò)一個(gè)場(chǎng)景，一個(gè)status字段只有0和1兩個(gè)值，即使建了索引，查詢WHERE status = 1也經(jīng)常不走索引，因?yàn)閮?yōu)化器覺(jué)得掃半張表和掃全表沒(méi)多大區(qū)別，甚至全表掃描更直接。

此外，索引列上允許NULL值，且查詢條件是IS NULL或IS NOT NULL時(shí)，索引的行為可能會(huì)比較復(fù)雜，有時(shí)會(huì)走，有時(shí)不走，這取決于優(yōu)化器對(duì)數(shù)據(jù)分布的判斷。總之，索引失效并不是索引“壞了”，而是它在當(dāng)前查詢上下文中的“效用”不足以被優(yōu)化器采納。

索引類型與選擇策略

MySQL支持多種索引類型，每種都有其適用場(chǎng)景，理解這些差異是高效建索引的前提。

最常見(jiàn)的是B-Tree索引，這也是MySQL（尤其是InnoDB存儲(chǔ)引擎）默認(rèn)和最常用的索引類型。它適用于各種等值查詢、范圍查詢、排序和分組操作。幾乎所有你在數(shù)字、字符串、日期列上創(chuàng)建的索引，如果不特別指定，都是B-Tree索引。它的優(yōu)勢(shì)在于平衡樹(shù)結(jié)構(gòu)，能保證查詢效率相對(duì)穩(wěn)定，且支持前綴匹配。

然后是哈希索引（HASH），主要在Memory存儲(chǔ)引擎中用到，InnoDB中只能用于自適應(yīng)哈希索引（由MySQL內(nèi)部自動(dòng)創(chuàng)建和管理）。哈希索引的特點(diǎn)是查詢速度極快，因?yàn)樗苯油ㄟ^(guò)哈希算法定位數(shù)據(jù)，時(shí)間復(fù)雜度接近O(1)。但它的缺點(diǎn)也很明顯：不支持范圍查詢，不支持排序，也不支持模糊匹配。它只適用于等值查詢。所以，如果你看到Memory表，哈希索引可能是一個(gè)好選擇，但對(duì)于InnoDB，通常我們不直接創(chuàng)建哈希索引。

全文索引（FULLTEXT），顧名思義，是為文本內(nèi)容搜索而設(shè)計(jì)的。當(dāng)你需要在文章標(biāo)題、內(nèi)容等大文本字段中進(jìn)行關(guān)鍵詞搜索時(shí)，B-Tree索引往往效率低下，全文索引就能派上用場(chǎng)了。MySQL的全文索引支持自然語(yǔ)言模式、布爾模式等，但它的功能和效果與專業(yè)的搜索引擎（如elasticsearch、solr）相比還是有差距的，通常只適用于一些簡(jiǎn)單的文本搜索場(chǎng)景。

空間索引（SPATIAL），用于地理空間數(shù)據(jù)類型，如POINT、LINESTRING、POLYGON。如果你在做地圖應(yīng)用、LBS服務(wù)，需要查詢某個(gè)區(qū)域內(nèi)的點(diǎn)，或者計(jì)算距離，那么空間索引就是你的利器。它通常結(jié)合R-tree算法實(shí)現(xiàn)，能夠高效處理空間關(guān)系查詢。

在選擇索引策略時(shí)，有幾個(gè)點(diǎn)我個(gè)人覺(jué)得特別重要：

1. 高選擇性列優(yōu)先：選擇性高的列（即不重復(fù)值多的列）更適合創(chuàng)建索引，因?yàn)樗鼈兡芨斓乜s小查詢范圍。比如用戶ID、訂單號(hào)，它們的唯一性很高，非常適合做索引。
2. 考慮查詢模式：你的大部分查詢是等值查詢、范圍查詢，還是排序、分組？針對(duì)性地創(chuàng)建索引。如果經(jīng)常需要按某個(gè)字段排序，可以考慮在該字段上創(chuàng)建索引。
3. 復(fù)合索引（聯(lián)合索引）：當(dāng)查詢條件涉及多個(gè)列時(shí)，可以考慮創(chuàng)建復(fù)合索引。例如INDEX (col1, col2, col3)。復(fù)合索引遵循“最左前綴原則”，即只有當(dāng)查詢條件包含索引的最左邊列時(shí)，索引才可能被使用。比如WHERE col1 = ‘A’ AND col2 = ‘B’會(huì)使用，但WHERE col2 = ‘B’則不會(huì)。合理設(shè)計(jì)復(fù)合索引能顯著減少索引數(shù)量，提高查詢效率。
4. 避免過(guò)度索引：索引不是越多越好。每個(gè)索引都會(huì)占用磁盤(pán)空間，并且在插入、更新、刪除數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)需要維護(hù)這些索引，這會(huì)帶來(lái)額外的開(kāi)銷(xiāo)。我見(jiàn)過(guò)一些系統(tǒng)，為了所謂的“優(yōu)化”，給每個(gè)字段都加了索引，結(jié)果寫(xiě)操作性能急劇下降。
5. 覆蓋索引（Covering Index）：如果一個(gè)查詢需要的所有列都在索引中，那么MySQL可以直接從索引中獲取數(shù)據(jù)，而無(wú)需回表（即不再去數(shù)據(jù)行中查找數(shù)據(jù)），這會(huì)大大提高查詢效率。EXPLAIN結(jié)果中Extra列顯示Using index就是覆蓋索引的標(biāo)志。

選擇合適的索引類型和策略，需要對(duì)業(yè)務(wù)場(chǎng)景和數(shù)據(jù)特性有深入的理解，并不是一蹴而就的事情。

索引維護(hù)與優(yōu)化

索引建好了，也跑得不錯(cuò)，是不是就萬(wàn)事大吉了？不，索引也需要“保養(yǎng)”。就像汽車(chē)需要定期維護(hù)一樣，數(shù)據(jù)庫(kù)索引也可能因?yàn)閿?shù)據(jù)變化而變得不那么高效，甚至需要調(diào)整。

一個(gè)比較常見(jiàn)的維護(hù)操作是重建或優(yōu)化索引。當(dāng)表中的數(shù)據(jù)經(jīng)過(guò)大量的插入、刪除、更新操作后，索引可能會(huì)出現(xiàn)碎片化，導(dǎo)致其物理存儲(chǔ)不再連續(xù)，從而降低查詢效率。這時(shí)，你可以使用OPTIMIZE TABLE命令來(lái)整理表和索引：

OPTIMIZE TABLE your_table_name;

OPTIMIZE TABLE會(huì)重建表和索引，消除碎片，并更新統(tǒng)計(jì)信息。但要注意，這個(gè)操作可能會(huì)鎖定表，對(duì)于大表來(lái)說(shuō)，執(zhí)行時(shí)間會(huì)比較長(zhǎng)，可能會(huì)影響線上業(yè)務(wù)。所以，通常建議在業(yè)務(wù)低峰期執(zhí)行，或者考慮使用在線DDL工具（如Percona Toolkit的pt-online-schema-change）來(lái)避免長(zhǎng)時(shí)間鎖定。

另一個(gè)重要的方面是監(jiān)控索引使用情況。你創(chuàng)建的索引真的被用到了嗎？還是只是躺在那里占用空間？MySQL的performance_schema和information_schema提供了一些視圖，可以幫助你了解索引的使用情況。例如，你可以查看information_schema.STATISTICS表中的Index_used列（如果你的MySQL版本支持），或者更細(xì)致地通過(guò)performance_schema.table_io_waits_summary_by_index_usage等視圖來(lái)分析。

如果發(fā)現(xiàn)某個(gè)索引長(zhǎng)期沒(méi)有被使用，或者使用頻率極低，那么它可能就是一個(gè)冗余索引。冗余索引不僅占用存儲(chǔ)空間，還會(huì)增加寫(xiě)操作的開(kāi)銷(xiāo)。對(duì)于這類索引，果斷地將其刪除通常是一個(gè)明智的選擇：

DROP INDEX idx_unused_column ON your_table_name;

刪除索引前，務(wù)必仔細(xì)分析，確保它不是被某個(gè)不常運(yùn)行但至關(guān)重要的查詢所依賴。

我個(gè)人在做索引優(yōu)化時(shí)，還會(huì)特別關(guān)注索引的基數(shù)（Cardinality）。SHOW INDEX FROM your_table_name命令的輸出中就有這一列。基數(shù)表示索引列中不重復(fù)值的數(shù)量。理論上，基數(shù)越高，索引的選擇性越好，查詢效率越高。如果一個(gè)索引的基數(shù)很低（比如在一個(gè)只有“男”、“女”兩個(gè)值的性別字段上），那么這個(gè)索引的作用就非常有限，甚至可能不如全表掃描。MySQL的優(yōu)化器會(huì)定期更新索引的統(tǒng)計(jì)信息，但如果數(shù)據(jù)變化非常頻繁，有時(shí)也需要手動(dòng)ANALYZE TABLE your_table_name;來(lái)強(qiáng)制更新統(tǒng)計(jì)信息，確保優(yōu)化器能做出最準(zhǔn)確的判斷。

最后，要記住，索引優(yōu)化是一個(gè)持續(xù)的過(guò)程，它不是一次性工作。隨著業(yè)務(wù)的發(fā)展、數(shù)據(jù)量的增長(zhǎng)和查詢模式的變化，原有的索引策略可能不再是最優(yōu)的。定期回顧和調(diào)整索引，是保持?jǐn)?shù)據(jù)庫(kù)高性能的關(guān)鍵。