一、索引基礎說明

InnoDB 支持：主鍵索引、普通索引、唯一索引、聯(lián)合復合索引、前綴索引、全文索引；
索引核心作用：加速 WHERE / JOIN / ORDER BY / GROUP BY 查詢；
代價：插入、更新、刪除時需要維護 B+ 樹，索引越多寫入性能越差。

二、創(chuàng)建索引三種方式

方式1：建表時直接定義索引（推薦規(guī)范寫法）

CREATE TABLE `openapi_apilog` (
  id BIGINT AUTO_INCREMENT COMMENT '主鍵',
  user_id VARCHAR(32) NOT NULL,
  date DATE NOT NULL,
  path VARCHAR(500) NOT NULL,
  login_ip VARCHAR(50),
  price DECIMAL(10,2),
  creat_time DATETIME,
  -- 主鍵索引
  PRIMARY KEY (`id`),
  -- 普通聯(lián)合索引
  INDEX idx_user_date (user_id, date),
  -- 唯一索引
  UNIQUE INDEX uk_path_uid (path, user_id),
  -- 字符串前綴索引（path只截取前40字符建索引，節(jié)省空間）
  INDEX idx_path_prefix (path(40))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '接口日志表';

各類索引在建表時關鍵字區(qū)分

1. PRIMARY KEY：主鍵索引，一張表只能一個，非空且唯一；
2. INDEX / KEY：普通索引，無唯一性限制；
3. UNIQUE INDEX：唯一索引，字段值不能重復，允許一條 NULL；
4. path(N)：前綴索引，長字符串專用。

方式2：已有表追加創(chuàng)建索引（線上最常用）

語法通用：

CREATE [UNIQUE] INDEX 索引名 ON 表名(字段1, 字段2...);

1）普通單列索引

CREATE INDEX idx_user_id ON openapi_apilog(user_id);

2）聯(lián)合復合索引（多字段組合）

CREATE INDEX idx_user_date_path ON openapi_apilog(user_id, date, path);

3）唯一索引

CREATE UNIQUE INDEX uk_verify_id ON openapi_apilog(verify_idf_id);

4）前綴索引（長URL、地址字段）

CREATE INDEX idx_path_prefix ON openapi_apilog(path(40));

5）覆蓋索引（查詢字段全部放進索引，消除回表）

CREATE INDEX idx_cover ON openapi_apilog(user_id, date, path, login_ip, price, creat_time);

方式3：ALTER TABLE 語句創(chuàng)建索引

底層和 CREATE INDEX 效果一致，兼容老版本：

-- 普通索引
ALTER TABLE openapi_apilog ADD INDEX idx_date (date);

-- 唯一索引
ALTER TABLE openapi_apilog ADD UNIQUE INDEX uk_ip (login_ip);

-- 主鍵索引（表無主鍵時添加）
ALTER TABLE openapi_apilog ADD PRIMARY KEY (`id`);

三、查看索引（管理必備命令）

1. SHOW INDEX FROM 表名（最常用）

SHOW INDEX FROM openapi_apilog;

關鍵字段解讀：

• Key_name：索引名稱；
• Seq_in_index：聯(lián)合索引內(nèi)字段順序；
• Column_name：索引字段；
• Non_unique：0=唯一索引/主鍵，1=普通索引；
• Cardinality：基數(shù)，代表區(qū)分度，數(shù)值越大索引效率越高。

2. DESCRIBE / DESC 查看表結構附帶索引

DESC openapi_apilog;

3. 查詢系統(tǒng)表，查看全庫索引

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

4. 查詢從未使用過的閑置索引（清理冗余用）

SELECT * FROM sys.schema_unused_indexes;

5. EXPLAIN 驗證索引是否生效

EXPLAIN 
SELECT login_ip,price FROM openapi_apilog 
WHERE user_id='10001' AND date='2026-07-02';

• type = ALL：全表掃描，未走索引；
• key 列有索引名：成功命中索引；
• Extra 出現(xiàn) Using index：命中覆蓋索引，無回表。

四、修改索引

MySQL 不支持直接修改索引字段，只能先刪除舊索引，再重建新索引。

示例：原有 idx_user_date，需要改成 user_id + date + creat_time

-- 1. 刪除舊索引
DROP INDEX idx_user_date ON openapi_apilog;
-- 2. 創(chuàng)建新索引
CREATE INDEX idx_user_date_time ON openapi_apilog(user_id, date, creat_time);

五、刪除索引

方式1：DROP INDEX（推薦）

DROP INDEX idx_path_prefix ON openapi_apilog;

方式2：ALTER TABLE 刪除索引

ALTER TABLE openapi_apilog DROP INDEX idx_user_date_path;

刪除主鍵特殊寫法

ALTER TABLE openapi_apilog DROP PRIMARY KEY;

注意：如果主鍵是自增字段，刪除前必須先去掉 AUTO_INCREMENT。

六、索引日常管理規(guī)范與運維操作

1. 建索引線上注意事項

1）大表千萬不要直接在線執(zhí)行 CREATE INDEX
500萬行以上表新建索引會鎖表阻塞讀寫，解決方案：

• MySQL5.6+ 支持在線無鎖創(chuàng)建：ALTER TABLE ... ADD INDEX LOCK=NONE;
• 使用 pt-online-schema-change 工具在線加索引，避免鎖表；
• 業(yè)務低峰期凌晨執(zhí)行。

2. 清理冗余索引規(guī)則

已有聯(lián)合索引 (a,b,c)，無需單獨創(chuàng)建 (a)、(a,b) 單列索引，聯(lián)合索引天然支持最左前綴查詢，多余索引只會加重寫入壓力。

3. 索引碎片整理

大量 DELETE / UPDATE 會產(chǎn)生索引碎片，降低查詢效率：

OPTIMIZE TABLE openapi_apilog;

InnoDB 會重建表和索引，釋放碎片空間。

4. 索引數(shù)量控制

單表索引建議不超過 5 個，INSERT / UPDATE / DELETE 時每條索引都要同步更新。

5. 區(qū)分度判斷（建索引前校驗）

-- 區(qū)分度越接近1，索引效果越好
SELECT COUNT(DISTINCT user_id)/COUNT(*) FROM openapi_apilog;

區(qū)分度低于0.1（如status 0/1狀態(tài)）不建議單獨建索引。

七、常見索引管理踩坑

1. 索引字段加函數(shù)、后置模糊匹配 %xxx、隱式類型轉(zhuǎn)換 → 索引失效；
2. 聯(lián)合索引順序錯誤，范圍字段放前面，后面字段無法利用索引；
3. 長字符串不加前綴索引，索引文件體積過大，緩存命中率低；
4. 線上大表直接創(chuàng)建索引，長時間鎖表引發(fā)業(yè)務超時；
5. 大量冗余索引，寫入接口TPS持續(xù)下跌。

八、完整操作流程總結

1. 建表階段：按需定義主鍵、聯(lián)合索引；
2. 后期新增：CREATE INDEX / ALTER TABLE ADD INDEX；
3. 查看校驗：SHOW INDEX + EXPLAIN 確認是否命中；
4. 調(diào)整索引：先 DROP 再 CREATE；
5. 清理維護：刪除無用索引、定期 OPTIMIZE 整理碎片；
6. 線上大表操作：使用在線DDL工具避免鎖表。

以上就是MySQL索引的完整教程(創(chuàng)建、查看、修改、刪除與日常管理)的詳細內(nèi)容，更多關于MySQL索引完整教程的資料請關注腳本之家其它相關文章！

最新評論