学完这篇，你能分清 B+树、哈希、全文这三类索引各自适合什么查询，并且会用 EXPLAIN 判断自己那条 SQL 到底该建哪种索引。
第一步：先看查询长什么样
索引选型由查询决定，不是由表决定。动手前先确认三件事：WHERE 里是等值还是范围、有没有 ORDER BY、有没有 LIKE '%xxx%'。
-- 找慢查询
SHOW VARIABLES LIKE 'slow_query_log';
-- 看执行计划
EXPLAIN SELECT id, title FROM bbs_thread
WHERE fid = 3 AND status = 1 ORDER BY dateline DESC LIMIT 20;
重点看 type、key、rows 三列。type 是 ALL 说明全表扫，key 是 NULL 说明没走索引，rows 越大越该优化。
注意：EXPLAIN 只是估算，别拿 rows 当精确值。数据分布倾斜时，优化器选错索引很常见，必要时用 ANALYZE TABLE 更新统计信息，或 FORCE INDEX 验证。
第二步：B+树索引——90% 的场景用它
InnoDB 的默认索引就是 B+树，值是按顺序排好的，所以它能干三件事：
- 等值查询：
WHERE uid = 123
- 范围查询：
WHERE dateline > 1700000000、BETWEEN、<
- 排序与分组：
ORDER BY dateline DESC 直接借索引顺序，省掉 filesort
还有两个衍生能力：最左前缀匹配（联合索引 (fid, status, dateline) 能支撑 fid= 或 fid= AND status=，但跳过 fid 直接查 status 就用不上），以及覆盖索引（查询列全在索引里，Extra 显示 Using index，不回表，这是提速最明显的一招）。
-- 版块页按状态筛选 + 时间倒序，一条联合索引全吃下
ALTER TABLE bbs_thread ADD INDEX idx_fid_status_time (fid, status, dateline);
注意：LIKE '%关键词%' 前面的 % 会直接废掉 B+树索引，因为它破坏有序性。需要这类模糊搜索时，别硬加 B+树索引，看第四步。
第三步：哈希索引——只认等值，不认范围
哈希索引按 key 算桶位，定位一条记录是 O(1)，但它不排序，所以只支持 = 和 IN，遇到 >、<、ORDER BY、LIKE、范围条件全部失效。
MySQL 里你能碰到的哈希索引有两种：
- MEMORY 引擎：默认就是哈希索引，可以显式指定
USING HASH。适合做临时表、缓存表、会话表这类纯 KV 读取。
- InnoDB 自适应哈希索引（AHI）：你建不了也删不掉，由 InnoDB 自己监控热点等值访问后自动建。查看状态：
SHOW ENGINE INNODB STATUS\G
-- 关注 INSERT BUFFER AND ADAPTIVE HASH INDEX 段
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
一般的 InnoDB 业务表不需要也不应该手写哈希索引，写了也只会被忽略。
注意：AHI 在高并发下可能成为争用点。如果压测时发现 btr_search_latch 相关等待偏高，可以临时 SET GLOBAL innodb_adaptive_hash_index = OFF; 对比验证，但别一上来就关。
第四步：全文索引——给"搜正文"用
帖子标题、正文这类长文本的模糊匹配，B+树无能为力。MySQL 5.6 起 InnoDB 支持 FULLTEXT，配 MATCH ... AGAINST 使用。
ALTER TABLE bbs_thread ADD FULLTEXT INDEX ft_title_message (subject, message);
-- 自然语言模式（按相关度打分）
SELECT id, subject, MATCH(subject, message) AGAINST('索引优化') AS score
FROM bbs_thread
WHERE MATCH(subject, message) AGAINST('索引优化')
ORDER BY score DESC;
中文必须先装 ngram 解析器，否则整段中文会被当成一个词，搜不到东西：
SHOW VARIABLES LIKE 'ngram_token_size'; -- 默认 2，即二字分词
-- my.cnf 里配置：ngram_token_size=2，重启生效
英文场景下 innodb_ft_min_token_size 默认 3，意味着两个字母的词不会被索引。
注意：全文索引对短文本（几个字的标题）收益不如对长正文明显；数据量很大时，专用搜索引擎（如 Elasticsearch）仍是更稳的选择。另外 IN BOOLEAN MODE 支持 +必须 -排除 "短语" 语法，做多关键词组合搜索比自然语言模式更可控。
第五步：验证，别凭感觉
改完索引回到第一步，重跑同一条 EXPLAIN，看三件事有没有变化：key 是否命中你新建的索引、rows 是否下降、Extra 是否出现 Using index（覆盖）或 Using index condition（索引下推）。
注意：索引不是越多越好。每个额外索引都会拖慢 INSERT/UPDATE/DELETE，并占空间。上线前用 SHOW INDEX FROM 表名 盘一遍，把从没被 key 命中过的冗余索引删掉。
小结
- B+树：等值、范围、排序、最左前缀、覆盖索引，InnoDB 默认选择，绝大多数场景用它。
- 哈希：只吃等值和 IN；MEMORY 引擎显式用，InnoDB 交给自适应哈希，不要手建。
- 全文：长文本关键词搜索；中文先配
ngram_token_size，短标题用 B+树前缀匹配可能更划算。
- 选型依据是查询形态：先
EXPLAIN，再建索引，再 EXPLAIN 验证，不要凭直觉。
- 联合索引把等值列放前、范围列放后，能同时吃到过滤和排序。
见习用户
正式会员
认证极客
