mysql中刪除和創建索引主要通過drop index、create index或alter table語句實現，推薦使用alter table以增強語義清晰度。1. 刪除索引可使用drop index index_name on table_name; 或alter table table_name drop index index_name; 2. 創建普通索引可用create index idx_column_name on table_name (column_name); 或alter table table_name add index idx_column_name (column_name); 3. 創建唯一索引用create unique index udx_column_name on table_name (column_name); 或alter table table_name add unique index udx_column_name (column_name); 4. 添加主鍵索引通常通過alter table table_name add primary key (column_name); 實現；5. 創建復合索引使用create index idx_col1_col2 on table_name (column1, column2); 或alter table添加；6. 全文索引通過create fulltext index或alter table add fulltext實現。索引提升查詢性能但也影響寫入效率和存儲，應基于實際查詢模式分析explain執行計劃，避免過度索引，合理設置復合索引順序，關注列基數，并利用在線ddl減少鎖表風險。定期清理未使用或冗余索引，避免在生產環境直接操作，應在測試環境中驗證后再上線。
mysql中刪除和創建索引，主要通過DROP INDEX和CREATE INDEX（或更常用的ALTER TABLE語句）來完成，這是數據庫性能調優的核心操作之一。理解它們的用法和背后的邏輯，對于提升數據庫查詢效率至關重要。
解決方案
刪除mysql索引主要有兩種方式，雖然效果一樣，但在語法上略有不同。我個人更傾向于使用ALTER TABLE的形式，因為它更明確地指出了操作對象是表。
刪除索引：
-
使用 DROP INDEX 語句：
DROP INDEX index_name ON table_name;
- index_name: 你要刪除的索引的名稱。
- table_name: 索引所在的表的名稱。
-
使用 ALTER TABLE 語句：
ALTER TABLE table_name DROP INDEX index_name;
- 這種方式在語義上更清晰，因為它明確表示正在修改一個表。
創建MySQL索引：
創建索引同樣可以通過CREATE INDEX或ALTER TABLE來實現，后者也是我日常工作中更常使用的。
-
創建普通索引 (Non-Unique Index)：
CREATE INDEX idx_column_name ON table_name (column_name); -- 或者 ALTER TABLE table_name ADD INDEX idx_column_name (column_name);
- idx_column_name: 索引的名稱，通常以idx_開頭是個不錯的習慣。
- table_name: 要創建索引的表名。
- column_name: 要創建索引的列名。
-
創建唯一索引 (Unique Index)： 確保列中的所有值都是唯一的。如果嘗試插入重復值，將拋出錯誤。
CREATE UNIQUE INDEX udx_column_name ON table_name (column_name); -- 或者 ALTER TABLE table_name ADD UNIQUE INDEX udx_column_name (column_name);
-
創建主鍵索引 (Primary Key Index)： 主鍵是一種特殊的唯一索引，它還強制了非空約束。一個表只能有一個主鍵。
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
- 通常在創建表時就指定主鍵，如 id int PRIMARY KEY AUTO_INCREMENT。
-
創建復合索引 (Composite Index)： 在多個列上創建索引，查詢時如果條件包含這些列，可能會用到。列的順序很重要。
CREATE INDEX idx_col1_col2 ON table_name (column1, column2); -- 或者 ALTER TABLE table_name ADD INDEX idx_col1_col2 (column1, column2);
-
創建全文索引 (Full-Text Index)： 用于在文本列中進行高效的關鍵詞搜索，通常用于VARCHAR, TEXT等類型。需要存儲引擎支持（如InnoDB從MySQL 5.6開始支持）。
CREATE FULLTEXT INDEX fdx_content ON table_name (content_column); -- 或者 ALTER TABLE table_name ADD FULLTEXT INDEX fdx_content ON table_name (content_column);
索引的生與死：何時創建，何時刪除，以及為什么？
我個人經驗里，很多時候一個查詢慢下來，第一反應就是看索引是不是沒建對，或者壓根就沒建。索引，說白了就是數據庫為了加快數據檢索速度而建立的“目錄”。它能讓數據庫系統迅速定位到需要的數據，而不是掃描整個表。
為什么創建索引？ 最直接的原因就是提升查詢性能。當你需要根據某個或某幾個字段頻繁地進行查找、排序或連接操作時，沒有索引，數據庫就得一行一行地去對比，這在數據量大的時候簡直是災難。比如，一個用戶表，如果按user_id或username查找，沒有索引，每次都得全表掃描。有了索引，就像翻字典一樣，直接定位。
為什么刪除索引？ 這可能聽起來有點反直覺，既然索引這么好，為什么還要刪除呢？其實，索引并非沒有代價。
- 寫入性能損耗： 每次對表進行INSERT、UPDATE、delete操作時，數據庫不僅要修改數據本身，還要同步更新相關的索引。索引越多，更新的開銷越大，寫入性能自然就下降。
- 存儲空間占用： 索引本身也是數據，需要占用磁盤空間。雖然單個索引可能不大，但大量索引累積起來，尤其是在大型表上，也會消耗可觀的存儲資源。
- 查詢優化器決策： 有時候，過多的索引反而會“迷惑”查詢優化器，讓它選擇了一個并非最優的執行計劃。或者，有些索引根本就沒被用到，成了“僵尸索引”，白白消耗資源。我見過不少系統，為了所謂的“優化”，給每個字段都建索引，結果寫入性能一塌糊涂，這就是典型的過度優化。
何時創建索引？
- 當你的WHERE子句、JOIN條件或ORDER BY子句中經常使用某個列或列組合時。
- 當你在進行聚合操作（如GROUP BY）時，涉及的列也可以考慮。
- 對于經常被查詢的表，尤其是有大量讀操作的表。
何時刪除索引？
- 當一個索引長期不被使用時（可以通過performance_schema或SHOW STATUS查看索引使用情況）。
- 當表的寫入操作遠多于讀取操作，且索引成為寫入瓶頸時。
- 當你發現某個索引導致了查詢優化器選擇了錯誤的執行計劃時。
- 當業務需求變化，某個舊的查詢模式不再頻繁出現，相應的索引變得冗余。
我通常會建議，先跑一段時間的業務，收集真實的查詢模式，再來決定索引的去留，而不是拍腦袋。
高效索引管理：從分析到實踐的最佳策略
高效的索引管理不僅僅是會敲幾行SQL那么簡單，它更像是一門藝術，需要結合對業務的理解和對數據庫內部機制的洞察。
1. 深入分析查詢模式：EXPLaiN是你的眼睛 這是我每次進行索引優化時必不可少的第一步。使用EXPLAIN語句可以分析SQL查詢的執行計劃，它會告訴你查詢是否使用了索引，使用了哪個索引，掃描了多少行，以及連接類型等等。
EXPLAIN SELECT * FROM users WHERE username = 'someuser';
通過分析EXPLAIN的輸出，特別是type（連接類型，如ALL表示全表掃描，ref或eq_ref表示使用了索引）和rows（掃描的行數），你能清楚地看到當前查詢的痛點在哪里，以及是否需要新的索引。如果看到type: ALL，那基本就是沒有用到索引，或者索引不合適。
2. 避免過度索引：少即是多 我曾經就掉進過這個坑，覺得索引越多越好。但實際情況是，每個額外的索引都會增加寫入操作的負擔，并且占用存儲空間。你需要找到一個平衡點。一個經驗法則是：只為那些真正能帶來性能提升的查詢創建索引。
3. 復合索引的列順序：左前綴原則 如果你的查詢經常同時使用多個列，那么復合索引是個好選擇。但這些列在索引中的順序至關重要。MySQL的復合索引遵循“左前綴原則”，這意味著如果你有一個(col1, col2, col3)的復合索引，那么它可以用于col1的查詢，也可以用于(col1, col2)的查詢，但不能單獨用于col2或col3的查詢。因此，將最常用于WHERE子句或JOIN條件的列放在復合索引的最前面，并且選擇性（唯一性）高的列優先。
4. 考慮列的基數（Cardinality）： 基數是指列中不重復值的數量。對于那些基數很低的列（比如性別，只有男/女），創建索引的效果往往不佳，因為即使有索引，數據庫也可能發現掃描少量數據比走索引更劃算。索引最適合那些基數高的列，例如用戶ID、訂單號等。
5. 利用在線DDL（Online DDL）： 在MySQL 5.6及更高版本中，ALTER TABLE操作（包括添加和刪除索引）通常支持在線DDL。這意味著在執行這些操作時，表不會被完全鎖定，應用程序可以繼續讀寫數據。這對于生產環境中的大型表尤為重要。你可以通過ALGORITHM=INPLACE和LOCK=NONE來指定操作模式，雖然通常MySQL會默認選擇最優的。了解這一點能讓你在生產環境做索引變更時更有底氣，不至于一操作就導致業務中斷。
索引操作的那些坑：常見誤區與避雷指南
即使是經驗豐富的dba，在處理索引時也可能踩到一些意想不到的坑。這些坑往往不是語法錯誤，而是對數據庫行為或業務場景理解不足導致的。
1. 忽略ALTER TABLE的鎖定影響： 在MySQL 5.5及以前版本，或者在某些特殊情況下（如添加主鍵、修改列類型），ALTER TABLE操作可能會對表進行長時間的寫鎖定，甚至讀鎖定。我曾經就犯過這樣的錯誤，在一個幾十億行的大表上直接DROP INDEX，結果把整個庫都拖慢了幾個小時，那次教訓記憶猶新。現在雖然有在線DDL，但了解其局限性（例如，某些操作仍然會短暫鎖定）并做好預案（如在業務低峰期執行，使用pt-online-schema-change等工具）仍然非常重要。
2. 索引并非總是被使用： 你創建了索引，不代表查詢就一定會用它。查詢優化器會根據成本模型來決定是否使用索引。
- 數據量太小： 對于小表，全表掃描可能比走索引更快。
- 條件不精確： 如果查詢條件使用了函數、類型轉換，或者LIKE ‘%keyword’（以通配符開頭），索引可能失效。
- 優化器選擇： 優化器可能認為其他索引或全表掃描更優。 這時候，EXPLAIN就成了你的救命稻草，它會告訴你查詢計劃的真實情況。
3. 冗余索引與重復索引：
- 冗余索引： 一個索引是另一個索引的左前綴。例如，你有一個(col1, col2)的復合索引，又單獨建了一個col1的索引。那么col1的索引就是冗余的，因為復合索引已經包含了col1的功能。
- 重復索引： 在同一個列上創建了多個完全相同的索引。這純粹是浪費資源，并且會增加寫入開銷。
- 定期檢查并清理這些無用的索引是很有必要的。你可以通過information_schema.STATISTICS表來查看表的索引信息。
4. OPTIMIZE TABLE的誤解： 有人認為刪除索引后需要OPTIMIZE TABLE來釋放空間。確實，OPTIMIZE TABLE可以整理碎片、回收空間，但它是一個重量級操作，會鎖定表。對于索引的刪除，空間通常會由數據庫內部管理，并不總是需要立即OPTIMIZE TABLE。它更多用于在大量數據刪除或更新后，回收表的物理空間。
5. 生產環境直接操作： 這是最大的忌諱。任何索引的創建或刪除，都應該先在開發環境、測試環境進行充分的驗證和性能測試。模擬生產環境的數據量和并發請求，觀察操作對系統性能的影響，確保一切符合預期，再考慮在生產環境執行。我見過太多因為在生產環境“藝高人膽大”直接操作，最終導致系統崩潰的案例。
總結一下，索引管理是一個持續的過程，需要不斷地分析、調整和優化。沒有一勞永逸的方案，只有不斷適應業務變化的策略。
