一、什么时候该建：三个条件都满足

条件一：这一列经常出现在 WHERE、JOIN、ORDER BY 里。 条件二：选择性高——不重复的值多，查一个值只返回很少的行。 条件三：表够大——几百行的表，全表扫比走索引还快。 「选择性」怎么算：不同值个数 / 总行数。 主键是 1（最好），性别是 0.00001（最差）。 经验上低于 0.01 就不太值得单独建索引了。 但「选择性」这个词有个陷阱：它说的是【整列】的选择性，实际查询用的是【某个值】。 一列有 100 万个不同值，但如果你查的那个值占了 90% 的行，走索引照样慢。 这就是「数据倾斜」——只有直方图才能让优化器知道这件事。

二、什么时候不该建

最后一条听着像废话，但现实里很常见。 原因通常是「当年为了某个需求建的，需求下线了索引留着」。 一个系统跑了五年，索引数量翻倍是常态，性能就是被这么一点点拖下去的。 判断「到底有没有人用」，Oracle 提供了两个办法： ① ALTER INDEX ... MONITORING USAGE 打开监视，看 V$OBJECT_USAGE.USED； ② 直接从 V$SQL_PLAN 里搜索引名，看哪些游标用过它。 后者更准，因为它看的是真实执行过的计划。

三、索引列上套函数或做运算：直接失效

索引里存的是「列的原值」，不是「substr(列) 的值」。 所以 WHERE substr(name,1,3) = 'abc' 用不上 name 上的索引。 同样，WHERE amt + 100 > 500 也用不上 amt 上的索引。 改写方向只有两个： ① 把运算挪到等号右边——amt + 100 > 500 改成 amt > 400。 ② 建函数索引——CREATE INDEX i ON t(substr(name,1,3))。 Oracle 上还有个更隐蔽的坑：隐式类型转换。 如果 empno 是 NUMBER 类型，你写 WHERE empno = '7369'，Oracle 会把字符串转成数字， 索引照样能用。 但如果 phone 是 VARCHAR2，你写 WHERE phone = 13800138000，Oracle 会把【列】转成数字—— 相当于 WHERE to_number(phone) = 13800138000，索引直接失效。 方向记住一句：转换「常量」是安全的，转换「列」就废索引。 所以列是什么类型，就传什么类型的值。

四、复合索引：主列决定生死

复合索引按「先第一列、再第二列」排序，像电话簿按「姓、名」排。 所以查「姓张、名三」能用，只查「名三」用不上。 规矩是：把最常用、选择性最高的列放最前面。 如果两个列总是一起用，顺序其实无所谓（都能用上）。 如果一个常用一个不常用，常用的必须在前。 现代优化器有个补丁叫【索引跳跃扫描】（INDEX SKIP SCAN）。 它的做法是「把主列的每个不同值都试一遍」，相当于把复合索引拆成几段分别查。 所以「不带主列」不一定完全用不上。 但它有硬前提：主列的不同值必须【很少】。 主列有几十个值，跳跃扫描很划算；主列有几十万个值，它等于扫几十万次，优化器根本不会选。 所以跳跃扫描是个补丁，不是「主列随便放」的许可证。

五、避免大表全扫：三种手段

手段一：覆盖索引。 查询要的列全在索引里，就不用回表。 所以「别写 SELECT *」和这一条是配套的。 手段二：分区裁剪。 按时间分区的表，查「最近三天」时优化器只扫那一个分区，其他分区直接跳过。 11.1 讲过这件事，它是「大表全扫」最有效的解法—— 因为分区裁剪减的是「扫多少块」，不是「怎么扫」。 手段三：并行。 /*+ PARALLEL(t 4) */ 让四个进程一起扫。 但并行只适合「反正要扫很多」的报表查询——OLTP 里一条小查询开并行，只会把 CPU 抢光。 注意「避免大表全扫」不等于「绝对不能全扫」。 报表要统计全表，那就该全扫，而且该并行扫。 真正要避免的是「明明只要几行，却扫了全表」。

六、动手 Demo：三种白建的索引，三种都能量出来

这一篇的 Demo 把三种「建了等于没建」的情况挨个跑一遍。 第一种看耗时（建了反而慢），第二种看计划（SCAN 还是 SEARCH）， 第三种看计划里有没有 ANY(...) 这个跳跃扫描的标志。 另外还会实测「白养的索引有多贵」——同样插两万行，带一个没人用的索引和删掉它，写入差多少。

# 三种「白建」的索引
import os, sqlite3, tempfile, time
p = os.path.join(tempfile.gettempdir(), "t153.db")
if os.path.exists(p):
    os.remove(p)
c = sqlite3.connect(p)
c.execute("create table sk (id int primary key, flag text, name text, amt int)")
c.executemany("insert into sk values (?,?,?,?)",
              [(i, "Y" if i % 2 else "N", "n%05d" % (i % 5000), i % 1000)
               for i in range(1, 50001)])
c.commit()

def t(fn, n=3):
    best = 9e9
    for _ in range(n):
        a = time.perf_counter()
        fn()
        best = min(best, time.perf_counter() - a)
    return best

def plan(sql):
    return " | ".join(r[3] for r in
                      c.execute("explain query plan " + sql).fetchall())

# 1 低选择性：flag 只有两个值，查一半的行
q = "select sum(amt) from sk where flag = 'Y'"
print("没索引  %.4f 秒  %s" % (t(lambda: c.execute(q).fetchone()), plan(q)))
c.execute("create index i_flag on sk(flag)")
c.execute("analyze")
print("有索引  %.4f 秒  %s" % (t(lambda: c.execute(q).fetchone()), plan(q)))

# 2 索引列上套函数：用不上索引
c.execute("create index i_name on sk(name)")
c.execute("analyze")
for s in ("select count(*) from sk where name = 'n00001'",
          "select count(*) from sk where substr(name,1,5) = 'n00001'"):
    print("%.4f 秒  %s" % (t(lambda x=s: c.execute(x).fetchone()), plan(s)))

实跑输出：

--------------------------------------------------------------------
第六十六课：索引——什么时候有用，什么时候白建
--------------------------------------------------------------------

  **索引不是「建了就好」。**
  **这一课测三种「建了等于没建」的情况，再看两类「索引到底有没有人用」的证据。**

  一、低选择性的列：优化器自己就不用
  --------------------------------------------------------------------
  **没索引时的计划：**SCAN big
  **建了索引之后的计划：**SEARCH big USING INDEX i_flag (flag=?)

  FLAG 上有索引吗 耗时
  -------------------------
  没有            0.0335 秒
  有              0.0504 秒
  已选择 2 行
  **看清这两份计划的区别：没索引是 `SCAN`，建了索引就变成 `SEARCH`——它【真的用了】这个索引。**
  **但耗时反而从 0.0335 秒涨到 0.0504 秒。**
  **为什么？`flag` 只有两个值，查 `'Y'` 要取一半的行。**
  **走索引要先读索引、再按行号回表十万次，比直接顺扫一遍表更贵。**
  **优化器算不出这一层的时候（比如没有直方图、数据分布不均），它就会选错。**
  **所以「选择性低（不同值少）的列不建索引」这条规矩，不是迷信，是能测出来的。**
  **典型的反例：性别、状态位（只有三五个值）、删除标记。**
  **这些列如果一定要用，做法是建【复合索引】把它放后面，前面放一个选择性高的列。**

  二、建了不用的索引，还在拖慢写入
  --------------------------------------------------------------------

  插入 20000 行时    耗时
  ----------------------------
  带着那个没用的索引 0.1629 秒
  把索引删掉         0.1476 秒
  已选择 2 行
  **差 1.10 倍。**  **这就是「白养的索引」的真实成本：查询一条都不用它，写入却要为它维护索引项。**
  **所以索引不是「免费的加速器」，它是「用写换读」的买卖。**
  **一张表上五六个索引，写入慢一倍是常事。**

  三、索引列上做运算或换函数：索引直接失效
  --------------------------------------------------------------------

  写法                       能不能走索引 耗时
  -------------------------------------------------
  `= 'n00001'`               SEARCH       0.0001 秒
  `like 'n0001%'`            SCAN         0.0262 秒
  `glob 'n0001*'`            SEARCH       0.0001 秒
  `substr(name,1,5)='n0001'` SCAN         0.0353 秒
  已选择 4 行
  **第一行能走索引，第三行也能，第二行和第四行走不了。**
  **第四行好理解：索引里存的是 `name`，不是 `substr(name,1,5)`。**
  **要让这种写法也走索引，得建【函数索引】：`CREATE INDEX i ON big(substr(name,1,5));`**
  **第二行 `like` 走不了索引，这一条是 SQLite 的特性，不是通用规律：**
  **SQLite 的 `like` 默认不区分大小写，所以没法用索引做范围查找。**
  **Oracle 里 `like 'n0001%'` 是能走索引的**——
  **两边在这一点上不一样，别拿这里的结论去套 Oracle。**
  **第四行那类写法在 Oracle 上同样失效，这条是通用的：**
  **`where amt + 100 > 500`，改成 `amt > 400`。**
  **`where to_char(dt,'YYYY')='2026'`，改成范围比较。**
  **一句话：索引列要「裸着」出现在条件左边。**

  四、复合索引：主列管不管用，看优化器有多大本事
  --------------------------------------------------------------------
  **带主列（`deptno AND amt`）：**SEARCH big USING COVERING INDEX i_da (deptno=? AND amt=?)
  **不带主列（只有 `amt`）：**SEARCH big USING COVERING INDEX i_da (ANY(deptno) AND amt=?)
  **第二个计划里出现了 `ANY(deptno)` 这个词——这叫【索引跳跃扫描】。**
  **它的意思是「把 deptno 的每个不同值都试一遍」，相当于把复合索引拆成好几段分别查。**
  **Oracle 里也有这个能力，叫 `INDEX SKIP SCAN`。**
  **但它有前提：主列的不同值必须很少（几十个以内）。**
  **如果 `deptno` 有十万个不同值，跳跃扫描就等于扫十万次，优化器不会选它。**
  **于是这个索引就真的白建了。**
  **所以「复合索引把最常用的列放最前面」这条规矩仍然成立**——  **跳跃扫描是个补丁，不是替代品。**

  五、监视索引：它到底有没有人用
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | ALTER INDEX i_da MONITORING USAGE;
  | -- 跑一段时间业务
  | SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'I_DA';
  | -- INDEX_NAME  USED  START_MONITORING  END_MONITORING

    select count(*) from big where deptno = 3 and amt = 100 用了 i_da
    select count(*) from big where deptno = 5              没用
    select count(*) from big where flag = 'Y'              没用
  **三条查询里只有 1 条用到了这个索引。**
  **Oracle 里查 `V$OBJECT_USAGE` 的 `USED` 列就是这个意思：`NO` 说明这个索引白养着。**
  **所以「定期清理没用的索引」和「建该建的索引」一样重要**——  **前者还不用担风险。**

第一段：给只有两个值的列建索引，查询反而变慢。 计划从 SCAN 变成 SEARCH，说明优化器【真的用了】这个索引，但它用错了。 因为它估不出「flag='Y' 占一半的行」这件事，以为走索引更便宜。 这也是 Oracle 上要建直方图的原因： 只有直方图能让优化器知道「这个值占了 90%」，从而放弃索引。 没有直方图，它只能按「平均每个值占多少」来估，一遇到倾斜就错。 第二段：白养的索引对写入的拖累，这里量到约 1.1 倍。 一倍看着不多，但这是在「只有两个索引」的表上量的。 真实业务表上五六个索引，写入慢一倍是常事。 第三段：= 'n00001' 走 SEARCH，substr(name,1,5)='n00001' 只能 SCAN。 两者差了两个数量级——一个是直接定位，一个是扫十万行。 这就是「索引列上套函数」的真实代价。 还有一个细节要说明：这一篇里 like 'n0001%' 也没走上索引。 这是 SQLite 的特性（like 默认不区分大小写）， 不是通用规律——Oracle 里 like 'abc%' 是能走索引的。 这类差异在正文和 Demo 里都标了出来，别混着记。

30 秒实验

1. 把第一种情况的查询改成查 1% 的行（比如 where amt = 500），再看有索引和没索引的耗时。 这次索引该赢了——选择性决定一切。
2. 给 big(name) 建一个函数索引 substr(name,1,5)，再跑第三段那条查询。 看计划会不会变成 SEARCH——这就是函数索引的用处。
3. 在复合索引 (deptno, amt) 上跑 where amt = 100，看计划里有没有 ANY(deptno)。 有，就是跳跃扫描；没有，就是真用不上。

常见误区

1. 「索引越多查询越快」。 读可能快，写一定慢。每个索引都要在插入时维护。
2. 「选择性低的列也能靠索引加速」。 查一半的行，走索引比全扫更贵。 这种列要么不建，要么放进复合索引的后面。
3. 「LIKE 'abc%' 一定用不上索引」。 Oracle 上能用。 真正用不上的是 LIKE '%abc'（前面带通配符）。
4. 「复合索引的列顺序无所谓」。 只查后面那列时，靠跳跃扫描兜底。 主列不同值一多，兜底就没了。

下一篇讲优化器。这一篇反复提到「优化器会不会选这个索引」，它凭什么选？ 下一篇讲它靠什么算成本、怎么看它的决定（EXPLAIN PLAN）、以及「计划突然变了」怎么固定住。

• ☐ （本篇完，下一篇 15.4 优化器的使用）

相关笔记

• 索引对象
• 表连接优化
• 分区技术简介
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引