索引本质

索引是存储引擎用于快速定位记录的数据结构，相当于书的目录。无索引则全表扫描，性能随数据量线性下降。

索引类型全景

B-Tree 索引（默认）

• 支持 =、<>、IN、BETWEEN、>、< 等范围查询。
• 最左前缀匹配：LIKE 'abc%' 走索引，LIKE '%abc' 不走。
• 覆盖索引：查询列全在索引中，避免回表。
• 排序优化：索引天然有序，ORDER BY/GROUP BY 可直接利用。

哈希索引

• 仅 Memory 引擎显式支持，InnoDB 自适应哈希索引（AHI）内部自动管理。
• 仅支持 精确等值查询，不支持范围、排序、最左前缀。
• 优势：单次定位，O(1) 级查找速度。

全文索引

• 倒排索引结构，MyISAM 早期独有，InnoDB 5.6+ 支持。
• 适用 CHAR/VARCHAR/TEXT，中文需分词器（ngram/MeCab）。

空间索引

• 针对几何类型，MyISAM/InnoDB 均支持，SPATIAL INDEX。

聚集索引 vs 非聚集索引

InnoDB 聚集索引

• 主键即聚集索引，数据行与主键同文件存储（.ibd）。
• 二级索引叶子存主键值 → 查询需“回表”两次 B+Tree 搜索。
• 主键选型原则：单调递增、短小、不更新（自增 BIGINT 最佳）。

MyISAM 非聚集索引

• .MYI 存索引，.MYD 存数据，叶子节点存物理行指针。
• 主键与辅助索引结构无本质差异，仅主键要求唯一非空。

索引 DDL 操作

-- 建表时创建
CREATE TABLE t (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    INDEX idx_name (name) USING BTREE
);

-- 单独创建
CREATE INDEX idx_name ON t(name);
CREATE UNIQUE INDEX uk_email ON t(email);
ALTER TABLE t ADD INDEX idx_name (name);

-- 删除
DROP INDEX idx_name ON t;
ALTER TABLE t DROP INDEX idx_name;

-- 查看
SHOW INDEX FROM t;

高效索引策略五原则

1. 必要性原则：仅为 WHERE/JOIN/ORDER BY/GROUP BY 高频列建索引。
2. 区分度原则：选择性 > 0.2，否则优化器放弃索引；长字符串用前缀索引。
3. 最左前缀原则：组合索引列顺序 = 高区分度在前 + 符合查询顺序。
4. 覆盖索引原则：高频查询尽量只取索引列，避免回表。
5. 最小化维护成本：删除冗余/重复索引，每个索引增加写放大与锁竞争。

索引维护与监控

• ANALYZE TABLE 更新统计信息（InnoDB 采样估算，MyISAM 全量精确）。
• OPTIMIZE TABLE 整理碎片（InnoDB 等同重建表）。
• 监控 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率。
• 慢查询日志 + EXPLAIN 定期巡检执行计划。