SQL索引策略：给查询装上加速器

可视化演示

通过三种模式展示B+树结构原理、联合索引最左前缀原则、以及索引条件下推ICP优化机制。

索引类型

图书馆里找书，方法不止一种。管理员手里可能备着好几套目录：一套按书名字母排的卡片柜，一套按作者姓氏排的，还有一套记录书里关键词的“内容索引”。数据库的索引也分好几种形态，各有各的脾气，选错目录就像让管理员用作者卡去查书号，事倍功半。

最常见的是 B+树索引：键值有序排列，既支持“找某一本”，也支持“找某一区间”；哈希索引像一排带锁抽屉，钥匙对上立刻开箱，但抽屉之间毫无顺序；全文索引则给正文建关键词倒排表，用来回答“哪几本书提到过机器学习”。InnoDB 里还藏着一个“自适应哈希”，自动为热门的等值查询加速。

B+树把 N 条记录组织成树高只有几层的结构。主键索引的叶子直接挂整行数据，二级索引的叶子只存索引键加主键，查到后还要“回表”取其余列。整表数据涨到亿级，B+树高度通常也只到 4 层，因此查询成本几乎恒定——这正是数据库选它当主索引的原因。

所以选索引类型要先想清楚借书习惯：精确知道书号，用哈希抽屉最快；想借某一区间的书，B+树目录才能沿字母翻；搜主题词，只能靠全文索引。没有一把万能钥匙，管理员的目录柜从来是“各司其职”。

最左前缀

图书馆有一套“作者→书名→出版年”的三联目录卡。管理员查张三的《SQL 入门》，先翻到“张”姓区，再在里面翻“三”，最后比对年份，一路顺读，目录很好用。可你要是甩给他一句“帮我查 2020 年出版的书”，他就得把整套卡片从头翻到尾——因为目录的第一关键字是作者，不是年份。

这个“必须从最左边那栏开始顺读”的规矩，就是联合索引的“最左前缀”原则：MySQL 只能从左往右连续利用索引列。对 (a,b,c) 联合索引，条件覆盖 (a)、(a,b)、(a,b,c) 都能命中；只写 b，或者写 a 和 c 却跳过 b，后面的列就再也用不到索引。

更隐蔽的是“范围列中断”：WHERE a=1 AND b>10 AND c=5 时，b 用了范围比较后，c 无法继续在索引树里定位，只能把 b 范围里的行全部回表再过滤。因此设计时要把等值查询的列放前面，范围条件尽量放最后，才能把每一列索引都榨干。

记忆口诀是“顺读目录、跳栏作废”：设计联合索引前，先把业务查询里最常出现的等值列排到最左，范围列垫底，这样管理员每一次翻卡都能顺到底。

联合索引设计

三联目录卡的列顺序为什么不能乱排？假设作者姓氏只有一千种，书名却有十万个。若目录第一列是“出版年”，你想查“某作者某书”，管理员就得在上万个年份分区里反复横跳，目录优势荡然无存。列的顺序，决定了管理员翻卡的速度。

设计联合索引有三个核心考量：区分度、覆盖、写入代价。区分度等于列的不同取值数除以总行数，越接近 1 越能一刀切中目标，应当靠左；查询涉及的列若全部被索引包住，就是覆盖索引，连回表都省了；但每加一列索引，写入时就要多维护一棵排序树。

冗余也要清理：已有 (a,b) 索引再建 (a,b,c)，且 c 很少被查询使用，属于重复建设；而 (a) 和 (a,b) 不算完全冗余，前者能单独服务只查 a 的查询。实战中要结合慢查询日志统计高频条件，再决定“删哪建哪”。

设计联合索引就像整理衣柜：常穿的外套挂最外层，少用的塞进深处；每多一套目录，就多占一块书架。好索引不是“越多越好”，而是“刚好够用”。

索引失效场景

有时管理员手里明明有目录，却还是只能一本本翻——因为你的借书单把目录“写坏了”。比如你把书名写成“2024 年新版《SQL 入门》”，而目录按书名首字排序，开头变了，索引自然无从定位。数据库里，这类“写坏借书单”的操作每天都在发生。

最常见的三种失效写法：对索引列做函数运算（WHERE YEAR(created_at)=2024）、隐式转换（字符串列 phone 与数字比较，MySQL 会把列 cast 成数值，等于悄悄对列做了函数）、前导通配符 LIKE '%abc'。此外，索引列参与 OR 且另一侧无法走索引时，整体也常退化为全表扫。

判断是否失效有一把通用标尺：看条件是否“破坏列本身”或“无法确定起点”。LEFT(col,3)='abc' 用了函数，失效；col LIKE 'abc%' 起点明确，能走索引。隐式转换最阴险：varchar 列对数字比较，肉眼看着没问题，EXPLAIN 一查却是全表扫。

借书单要写得让目录读得懂：原样给列名、给确定前缀、别让数据库偷偷转型。目录再快，架不住单子写歪——这是排查慢查询时最先该检查的三处。

索引下推 ICP

三联目录的潜力还能再榨一层。老办法是：管理员按目录找出 100 本候选书，一本本搬到柜台核对“出版社对不对”，错的再搬回去；索引下推（ICP）彻底改变流程——他在目录格里翻片的瞬间，就直接核对其余条件，只把达标的那几本抱出来。

ICP 是 MySQL 5.6 起 InnoDB 默认开启的优化：联合索引扫到最左列命中候选后，存储引擎先用索引里已有的其他列继续过滤，过滤达标才回表取整行。关键是，它不需要你新建任何索引，纯粹是执行引擎的“顺手过滤”。

它恰好补上了“范围列中断”的一半遗憾：WHERE name LIKE '张%' AND age>18 配上 (name, age) 索引后，age 无法参与定位，但 ICP 能在索引层先把 age>18 过滤掉，回表行数大幅减少。注意 ICP 对主键索引没意义（本来就不用回表），对排序/分组也没帮助。

相当于把借书台的“人工初筛”挪进了目录卡片里：原本要抱 100 本书出来看，现在翻目录时就筛剩 20 本。ICP 不用建索引、不用改 SQL，是数据库白送的一笔性能红利。

前缀与唯一索引

有些书名长得离谱，管理员不想为每本书抄全名做目录，只抄前五个字：这就是前缀索引。目录瘦身了，但《机器学习实战》和《机器学习导论》前五个字相同，翻到候选区后还得回书架确认完整书名——省了空间，多了几次确认。

前缀索引用 INDEX idx_email(email(10)) 只索引前 10 个字符，特别适合 email 这类超长但前缀区分明显的列。核心指标是区分度：COUNT(DISTINCT LEFT(col,n)) 除以 COUNT(*)，尽量靠近完整列的区分度，一般取到 90% 以上即可，再长收益锐减。

唯一索引则相反，它强制列值全局不重复，像身份证号。它不只是约束，还给了优化器“最多返回一行”的强提示，等值查询可以提前终止。代价是每次写入都要做唯一性检查，写密集场景是实打实的开销。业务上本来唯一的列（订单号、手机号）就直接用唯一索引，一举两得。

前缀索引像“简写目录”，省纸但偶尔要回架确认；唯一索引像“身份证目录”，绝不重号但要办证维护。长文本选前缀、天然唯一选唯一——这是索引策略里最容易被忽略的两张牌。

索引维护策略

再好的目录，用久了也会乱：书被移架、卡片被撕角、新书没插回原位。数据库索引同样如此——频繁插入删除会产生页碎片，统计信息过期后优化器会“看走眼”，该走索引的查询偏偏选了全表扫。

维护三板斧：第一，碎片整理，OPTIMIZE TABLE 或 ALTER TABLE ENGINE=InnoDB 重建表，碎片率高的索引顺序读性能明显回升；第二，保持统计信息新鲜，定期 ANALYZE TABLE 更新基数，让优化器重新“看得准”；第三，盯紧慢查询日志，揪出“应该走索引却没走”的 SQL。

巡检时还要删冗余：两个索引前缀相同、一个完全覆盖另一个，可以合并成一个；写多读少的表要控索引个数，每个索引都是写入路径上额外的一次排序。

图书馆都有“闭馆盘库”：核对卡片、修整书架、下架旧书。数据库也该有一份索引保养日历——周更表查碎片、月更表刷统计、按慢查询反馈调整联合索引。目录常新，查询才常快。

AI 应用场景

Python 演示

# -*- coding: utf-8 -*-"""SQL索引策略演示：全表扫描 vs 索引查找 vs 最左前缀 vs 索引下推"""import bisectimport randomimport time  def build_books(n):    """模拟图书馆书架：随机生成 n 条 (书号, 分类号) 记录"""    random.seed(42)    return [(random.randint(0, 10 ** 6), random.randint(0, 999)) for _ in range(n)]  def build_index(books):    """建立简化版B+树索引：按书号排序的键数组与原始行号映射"""    ordered = sorted(range(len(books)), key=lambda i: books[i][0])    keys = [books[i][0] for i in ordered]    return keys, ordered  def full_scan(books, target):    """全表扫描：一格一格翻书架"""    for i, (key, _) in enumerate(books):        if key == target:            return i    return -1  def index_lookup(keys, rows, target):    """索引查找：二分跳转，只比较约 log2(n) 次"""    pos = bisect.bisect_left(keys, target)    if pos < len(keys) and keys[pos] == target:        return rows[pos]    return -1  def leftmost_prefix(index_cols, query_cols):    """模拟联合索引(a,b,c)的最左前缀：返回命中前缀列数"""    hit = 0    for _, qc in zip(index_cols, query_cols):        if qc is None:            break        hit += 1    return hit  def icp_count(keys, rows, books, lo, hi, low, high):    """索引下推：索引层先过滤分类号区间，只统计回表行数"""    hit = 0    for pos in range(lo, hi + 1):        row = rows[pos]        if low <= books[row][1] <= high:            hit += 1    return hit  def main():    n = 200000    books = build_books(n)    keys, rows = build_index(books)    target = keys[len(keys) // 2]     t0 = time.perf_counter()    full_scan(books, target)    t1 = time.perf_counter()    index_lookup(keys, rows, target)    t2 = time.perf_counter()    print("full scan: %.4fs" % (t1 - t0))    print("index search: %.4fs" % (t2 - t1))     idx = ("author", "title", "year")    cases = [("张三", "SQL", 2020), ("张三", None, 2020), (None, "SQL", 2020)]    for query in cases:        print("query", query, "-> prefix columns:", leftmost_prefix(idx, query))     total = len(keys)    pushed = icp_count(keys, rows, books, 0, total - 1, 300, 700)    print("candidate rows: %d, rows to fetch after ICP: %d" % (total, pushed))  if __name__ == "__main__":    main()

1. ordered = sorted(range(len(books)), key=lambda i: books[i][0])按书号给行号排序，模拟建索引时对键值排序，相当于给图书馆贴上索书号。
2. keys = [books[i][0] for i in ordered]抽出有序键数组，简化版 B+树目录。
3. pos = bisect.bisect_left(keys, target)二分查找，像目录跳转，一次排除一半候选。
4. for _, qc in zip(index_cols, query_cols):逐列检查查询条件，遇到缺列即停。
5. if qc is None: break条件里跳过中间列，正是最左前缀“跳列即断”。
6. if low <= books[row][1] <= high:在索引层先用分类号过滤，模拟 ICP 下推，只有命中者才回表。
7. if __name__ == "__main__":入口守卫，避免 import 时误执行演示逻辑。

常见误区

面试题