2000 万的表，索引怎么建 ?

建索引，别看是 CREATE INDEX 那么简单。建好了，可真不是简单的事儿。

你看，为建索引，还有作者专门写了一本书。唯一一本能从索引本质说起来的书，从磁盘随机读，顺序读说起，从数据库索引结构剖析，从浅入深，从局部入手，全盘考量，最终选择合适的索引，说它是系统工程，可真一点没错。

当然，数据库设计与开发中，类似的系统工程思维，真有不少，看我前面一篇帖子，就是最好的证明:

作为面试官，我最常用的两类SQL数据库面试题

今天，我要分享的，是针对千万级别的表，应该慎重考虑的一种索引及其原理。

举个例子：

DECLARE @ORDER_ID VARCHAR(15) = 'A-14RX7',
        @ATTR NVARCHAR(128) = 'PRODUCT_NAME'

SELECT A.EVENT_ID,
    A.ORDER_ID,
    A.ATTR,
    A.VAL1,
    A.VAL2
FROM EVENT B 
    INNER JOIN EVENT_LOG A 
        ON A.EVENT_ID = B.ID 
WHERE A.ORDER_ID = @ORDER_ID 
    AND A.ATTR = @ATTR
ORDER BY B.BEGIN_DATE ASC 

其中 EVENT 的数据量有上百万，EVENT_LOG 的数据量有2000多万

此时的索引情况是 EVENT 在 ID 上有唯一索引，而 EVENT_LOG 上有 针对 EVENT_ID 做了非唯一索引。

就上面这段脚本，运行时间超过了20秒。

很可能大家看到这样一个 Join, 两边都建立了索引，认为查询效率应该没有问题。你看，索引都建了，也都按照索引去走了。那么为什么效率还那么慢呢？其实，就是索引无效。

比如我第一次建索引的时候，就犯了这么个错误：

CREATE INDEX IDX_EVT_ATTR_VAL 
ON EVENT_LOG(ORDER_ID,ATTR) 
INCLUDE(EVENT_ID,VAL1,VAL2)

众所周知，索引呈树结构组织，最底层的数据页，存储索引真实的数据，还有指向堆表的地址或者主键值。

上图就是典型的索引结构图。索引的键值，都存储在 Page3, Page4（当然实际的索引页复杂的多），俗称叶子页，而中间的那些数据页，存储的数据，都是到达最终叶子页的路径，俗称中间页。最顶层的那个页，称为顶点(root)页。

本次查询中，从 EVENT_LOG 查询的字段不多，因此把这些字段都存到索引叶子结点，明显能够减少回表的成本。我真就这么干了。

潇洒的回车执行后，去泡了杯茶回来，结果一看，我楞眼了，索引还没建完。于是赶紧停掉，好险。都已经 2 分钟过去了。

仔细看了下 EVENT_LOG 表结构，才发现 VAL1,VAL2 居然是 2 个超长字符串，将其放在叶子页，明显加大了索引的叶子页的空间，原本1000个页搞定的事情，说不定就长成了10万个页，使得索引无效。

再看下表统计，有2000多万条数据。而真实逻辑是，1 个 EVENT_ID 在 EVENT_LOG 中对应的数据，可能有上百条，但在 EVENT_ID 加 ATTR 的限定下，数据条数不会超过 10 条。

于是，果断放弃覆盖索引，改成一般索引：

CREATE INDEX IDX_EVT_ATTR_VAL 
ON EVENT_LOG(ORDER_ID,ATTR) 

果然，不到 40 秒，索引建立完毕，最开始的查询，也秒出。

所以，大家千万别以为，索引建了就一定 有用，建错索引，带来的后果很严重。

PS: 看到这里的朋友，顺便说下最近我的研究情况。近半月全身心在看 TPC 的论文，模拟 TPC 各类数据库测压标准。能够将数据库支撑的QPS( Query Per Second,即每秒查询数) 准确估算出来，才能防范于未然。相关的文章也在整理当中。回复“TPCH”可拿到数据仓库的测试指南，一起研究。