一、理论基础

本章讲述的方法基本上适用于所有的数据库软件。笔者按自己当前的认知总结了一下。大家都知道索引的创建是由业务查询决定的，所以在创建索引前需要先分析下业务sql，即select语句。

本章会围绕一个例子说明单表查询时索引的创建规则，也可用于优化线上语句。多表索引的创建会在下一章节描述。

1.1、一个例子

比如以下例子，得出的结论是不合适的索引不如全表扫描的速度快。比如一张用户user表有如下几个字段：CNO（用户编号）、FNAME（姓）、LNAME（名）、CITY（居住城市），如下：

CREATE TABLE `t_user`  (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `CNO` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `LNAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `FNAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CITY` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ADDRESS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

其中一条业务查询语句如下，同时假设两个前提：

• 随机读一次的时间为：10ms；
• 磁盘I/O速率：40M/s；

select cno, fname from t_user where LNAME=? AND CITY=? ORDER BY FNAME；

1.1.1在设计索引情况下（索引扫描）

索引为（LNAME, FNAME）。其查询过程分为以下两步，1、根据LNAME扫描索引片；2、回表查询CITY。则

花费的时间=（比对索引片+回表扫描）*数据条数，如果索引大小为1M，则按上述公式得出的总查询时间为：
 T = （10ms+1M/40M/S）*N = 35ms*N

因为返回结果里有个cno字段，这会引起一次随机读。结论就是，在此种场景下性能的影响主要受数据库条数和磁盘的IO速率；

1.1.2、不设计索引情况下（全表扫描）：

其查询过程只有一步，1、全表扫描。则

• 花费的时间=数据表大小/40M/s + 10*1

同样是cno字段，因为是全表扫描，所以只会引起一次随机读，其它数据全是顺序读，因为表的存储一般是连续的。结论就是，这里节省了随机读的时间，显然在数据库表数据同比增加的情况下，全表扫描的速度要优于索引。

这里注意回表和随机读是两个概念，可以简单认为回表是针对的where后的语句，随机读是针对的select后的返回字段。

1.2三星原则

上述无论采用哪种方案，查询速度都会比较差，所以下面介绍下优化的原理。同时建议回顾下上一章节提到的sql执行过程。可能会理解的更深一些。下面的例子都以用户表为例进行说明。

1.2.1、一星（索引加入查询条件，减小索引片宽度）

最小化要扫描的索引片的宽度。取出所有等值谓词（非范围连接符）的列（比如CITY=?）。把这些列作为索引最开头的列，任意顺序就可以。所以优化三星索引可以是（LNAME，CITY）或（CITY，LNAME）。在这两种情况下，必须扫描的索引片宽度将被缩减至最窄。

1.2.2、二星（索引加入排序字段，避免排序）

将ORDER BY列加入到索引中。不要改变列的顺序，但是忽略那些在第一步中已经加入索引的列。例如，在ORDER BY中有重复的列，如ORDER BY LNAME，FNAME或者是ORDER BY FNAME，CITY，只有FNAME需要在这步中被加入到索引中去。当FNAME是索引的第三列时，结果集中的记录无须排序就已经是以正确的顺序排列的了。第一次读取操作将返回FNAME值最小的那一行。这时优化后的索引变成（LNAME，CITY，FNAME）或（CITY，LNAME，FNAME）。

1.2.3、三星（索引加入返回字段，避免访问表）

将查询语句中剩余的列加到索引中去，列在索引中添加的顺序对查询语句的性能没有影响，但是将易变的列放在最后能够降低更新的成本。最终三星索引将会是：(LNAME，CITY，FNAME，CNO)或(CITY，LNAME，FNAME，CNO）,同时需要注意以下几点：

• WHERE条件不包含范围谓词（BETWEEN、>、<、in等）。
• FROM语句只涉及单表。
• 所有谓词对于优化器来说都足够简单。

--附上测试数据：mysql8.0.23, 记录条数：共100W
--查询语名：select cno, fname from t_user where lname="lname" AND CITY="city" ORDER BY FNAME;

上述语句总数据量：68W，测试结果如下：
1、无索引,执行时间约：1.774S
2、索引(LNAME，CITY)：执行时间约：2.5S
3、索引(LNAME，CITY，FNAME)：执行时间约：1.4S
4、索引(LNAME，CITY，FNAME，CNO)：执行时间约：0.2S


//可以看出最多会有10倍的性能差距

1.3、影响因素

本节只讲一个与开发关系最大的一个影响因素，对于硬件的影响大家可自行找些资料评估。

1.3.1、范围谓词

如果查询语句中查询条件LNAME换成between，如下：

select cno, fname from t_user where LNAME BETWEEN ? AND ? AND CITY=?  ORDER BY FNAME；

这样在存在范围谓词，就不能同时拥有三星索引，这时理想的选择是选择1星和3星。如果不需要排序的话也是一个变相的三星索引。优化方案如下：

//方案A：1+3星，由于FNAME在范围谓词列LNAME的后面，引起了一次排序操作
(CITY,LNAME,FNAME,CNO)：

//方案B：2+3星，会有多次回表
(CITY,FNAME,LNAME,CNO)

如果数据量足够多那么方案A和B性能没太大区别，通常情况下会选择方案B。方案A会比较占CPU资源，因为排序多数是在内存中进行的；方案B虽然要回表多次（物化过程因为是每次都回表），但省去了排序过程（结果集大的话，会花费比较长的时间）。如果考虑到一般业务上会有limit的限制，回表的时间可以忽略不计。所以虽然方案A性能比方案B好，所以在资源占用和时间性能上综合后，一般会选择方案B。

二、索引评估方法

2.1、基本问题法(BQ)

这种方法只能用于初步评估，测试时需要先预热查询，保证数据已尽可能的加入到了缓冲池中。是否有一个已存在的或者计划中的索引包含了WHERE子句所引用的所有列（一个半宽索引）？

• 如果答案是否，那么我们应当首先考虑将缺少的谓词列加到一个现有的索引上去。这将产生一个半宽索引，达到一星水平，这样保证索引过滤可以确保回表访问只发生在所有查询条件都满足的时候；
• 如果这还没有达到足够的性能，那么下一个选择就是将所有涉及的列都加到索引上，以使访问路径只需访问索引。这将产生一个避免所有表访问的宽索引；
• 如果SELECT仍然很慢，就应当使用谓词影响因素一节中所介绍的两个候选索引算法来设计一个新的索引；

半宽索引作用是最大化索引过滤；

宽索引作用只访问索引作用是防止回表；

2.2、快速上限估算法（QUBE）

可以看做BQ方法的补充，它不会像BQ那样漏掉发现某些问题。同时QUBE比BQ在实施时也会更耗时，但它能揭示所有与索引或者表设计相关的性能问题一假设对每个谓词所用的最差过滤因子都非常接近于实际的最差情况过滤因子值。QUBE方法是悲观的（评估的是上限），它有时会有告警误报，但QUBE的目的是将潜在的慢访问路径问题暴露出来。为了能在实际项目中使用，任何语句级别的预测公式都必须足够简单，这样才能将评估过程的额外开销保持在一个可以接受的程度。这种方法用于加上数据量以后的详细评估。

这个快速估算法的输出结果是本地响应时间（LRT），即一个传统事务的LRT是指用户和数据库服务器之间一次交互的响应时间，不包括所有的网络延迟。LRT=服务时间+排队时间（这个是完整的公式）。

• 服务时间：服务时间等于CPU时间加上排除了磁盘驱动排队的随机读时间。如果没有资源竞争，则本地响应时间等于服务时间。
• 排队时间：在一个常规的多用户环境中，程序的并发会导致对所需资源的各种竞争，因此这些并发的程序不得不排队来获取这些资源。

比较值：LRT=TR×10ms+TS×0.01ms
绝对值：LRT=TR×10ms+TS×0.01ms+TF×0.1ms
//LRT=本地响应时间
//TR=随机访问的数量
//TS=顺序访问的数量
//TF=有效FETCH的数量

2.2.1、访问

DBMS读取一个索引行或一个表行的成本称为一次访问，包括索引访问或表访问。如果DBMS扫描索引或表的一个片段（被读取的行在物理上是彼此相邻的，实际上现在的数据库设计也全是相邻的），那么第一行的读取即为一次随机访问。对于后续行的读取，每行都是一次顺序访问，顺序访问的成本比随机访问要低得多，大概性能差100倍左右。一次索引访问的成本与一次表访问的成本基本上是相同的。

物理上彼此相邻意思是索引中的所有行都通过指针链接在一起，链接的先后顺序由索引的键值严格定义。当几个索引行的键值相同时，就根据索引行存储的指针值进行链接。在传统的索引设计中，链表从LP1（叶子页1）开始，随后链接LP2，以此类推。这样叶子页就组成了一个连续的文件，LP1至LP12存储在磁盘柱面的第一个磁道，LP13至LP24存储在下一个磁道，如此继续。这样读取一组连续的索引行（即一个索引片，或者包含了单个键值或者一个范围的键值所对应的索引行）就非常快了。一次磁盘旋转会将多个叶子页读取进内存中，而且只有在磁盘指针移到下一个柱面时才需要进行一次短暂的寻址。​

• 全表扫描：从TP1（表页1）开始，读取该页上所有的记录，然后再访问TP2，以此类推。按照记录在表页中存储的顺序进行读取，没有其他特殊的顺序；​
• 聚簇索引：扫描读取索引片上的第一个索引行，然后获取相应的表行，再访问第二个索引行，以此类推。如果索引行与对应的表行记录顺序完全一致（聚簇率为100%），那么除了第一次之外的所有表访问就都是顺序访问。这个顺序也可以由于特殊数据太大的原因导致不连续，但可以通过组重组可以保证这个顺序是完全一致的；

2.2.2、访问次数

磁盘读与访问的区别，读所访问的对象是页，而写访问对象则是行。一次随机磁盘读会将一整页（通常包含很多行）读取至数据库的缓冲池中，前后两次连续的随机读不太可能会访问到同一个页。因此，QUBE中单次随机访问所消耗的时间与一次磁盘随机写的平均耗时是一样的。另外随机读是同步的；

一次顺序访问是指读取物理上连续的下一行，这一行要么存储在同一页中，要么在下一页中。由于顺序读的CPU时间与I/O时间是重叠的因此顺序访问的消耗时间就是两者中较大的那个。在QUBE中，如果一次随机读的耗时为10ms，那么一次顺序读所消耗的时间差不多是0.01s。

2.2.3、FETCH处理

被接受的行的数量可以通过FETCH调用的次数来确定，比如一次返回100条数据，那么这个值就是100，在估计时间时比较有用，在比较时此值没有用处；

2.3、几个例子

以上述公式为背景，介绍以下两个例子：

例子1

//主键访问，需要1次随机索和1次随机表， LRT = 2*10=20ms
SELECT CNO,LNAME,FNAME FROM CUST WHERE CNO=:CNO
//假设ZIP，LNAME, FNAME全是索引，这行语句返回1000条数据，则LRT = （索引）TR=1, TS=1000， （表)TR=1，TS=1000 ，TF=100，=140ms
SELECT CNO,LNAME,FNAME FROM CUST WHERE ZIP =ZIP AND LNAME =LNAME ORDER BY FNAME

例子2

SELECT CNO,FNAME FROM CUST WHERE LNAME =LNAME AND CITY=:CITY ORDER BY FNAME

假定以下条件成立：

• 表中有100万条记录；
• LNAME，FNAME）是唯一合适的索引；
• 渭词LNAME=：LNAME的最大过滤因子是1%；
• 渭词CITY=：CITY的最大过滤因子是10%；
• 结果集最多包含1000行记录（1000，000×1%×10%）；
• 表中的记录按照CNO列的大小顺序存储【主键索引（CNO）是聚簇索引，或者表经常按照CNO进行排序重组

性能数据大概如下(非实测，只是为了说明其性能差)：

SELECT CNO,FNAME FROM CUST WHERE CITY=CITY AND LNAMEBETWEEN LNAME1 AND LNAME2 ORDER BY FNAME

三、创建索引的建议

3.1、没用的索引

正常来讲创建索引的工作比较简单，一般来讲也没啥限制，但道理上来说索引越多表的写性能会越差，同时也会占用更多的磁盘空间，所以如果业务满足以下几类条件，就可以把无用的索引去掉了。

• 索引(A+B) =（B+A）：同时适应where a=? and b=?和where b=? and a=?，所以没必要创建两个索引；
• 原来有索引（A+B) 和（A+B+C)：那么是否要删除(A+B)呢：答案是不行，因为各适应各的场景，虽然CPU时间没啥影响，但需要考虑磁盘I/O时间；
• （A+B+C+D+E+F）和（A+B+F+C+D+E）：这两个索引是否可互换，答案是不一定，要看数据量而定，因为这里F的顺序非常重要，参考上述范围谓词一节中的描述；

3.2、索引的代价

如果一个表上有100个不同的查询，且为每一个查询语句都设计了最佳索引的话，那么即使没有重复的索引，该表上最终也可能有非常多的索引。这样一来表的插入、更新和删除操作就会变得很慢。

3.2.1、响应时间

当数据库管理系统向表中添加一行时，它必须在每一个索引上都添加相应的行。当一个事务向一张有10个索引的表里插入1行数据时，会串行写10次。

3.2.2、磁盘负载

被修改过的叶子页是迟早会被写到磁盘上去的。由于数据库的写是异步的，所以这些写不会影响到事务的响应时间。但是，这些写会增加磁盘负载。RAD5会放大这种影响，因为每一次页的随机更新都会引来两个磁盘的访问。整个Raid条带都需要被读取和写回：一次寻道（4ms）和两次旋转（2×4ms）。如果一张表的插入频率较高的话，磁盘负载可能会变成主要的问题，删除操作和插入操作所带来的磁盘负载是相同的，所以大量的删除任务是另外一个重要的考虑事项。更新操作只会影响列值被修改了的索引。如果磁盘负载是一个问题，较明显的解决办法是尝试合并索引。一个有10个列的索引比两个各有6个列的索引所引起的磁盘负载要小。

3.2.3、磁盘空间

举个例子，在表上创建一个每行包含400字节用户数据的索引，我们是否需要考虑磁盘空间？这个索引（去除RAID带来的额外开销需要1.5×10000000×400byte≈6GB左右的磁盘空间。随着索引变大，缓冲池或磁盘缓存也应该随之增大，否则非叶子页的I/O量会增加。宽索引的额外开销系数主要取决于分布的空闲空间的量。对于每个叶子页保留25%的空闲空间已经足够大了。对于一个8KB的叶子页来说，这意味着在分裂前，还可以向叶子页上添加5个400字节的索引行。

四、影响索引设计的因素

4.1、I/O时间估算

这个没啥好说的，建一张表，该表有100万行记录，每行记录的平均长度约为400字节。根据假定的顺序I/O时间计算，以顺序I/O读取400字节长度的行所花费的时间为400byte/40MB/s=0.01ms。再做以下两种测试，测试出的时间就做为基数来做后面的评估。

• 由一个所有行都不满足条件的SELECT查询引起的一次全表扫描(1×10ms+1000000×0.01ms≈10s)
• 一次涉及1000次FETCH调用的索引扫描，使用如（LNAME，FNAME）这样的索引，导致1000行的索引片扫描和1000次对表的同步读（1000×0.01ms+1000×10ms=10ms+10s≈10s）。

4.2、困难谓词

如果一个谓词字段不能参与定义索引片，即它无法成为一个匹配胃词，即称为困难谓词。

4.2.1、like

%xx左匹配时走全索引扫描，xx%右匹配时会索引片扫描

4.2.2、or操作符和bool谓词

即便是简单的谓词，如果它们与其他谓词之间为OR操作，那么对于优化器而言它们也可能是困难的。bool谓词也会走索引，但如果用or相关联就基本不走索引了。

select a,b,c from T where a>? and b>?
//单个索引片将会被扫描，由谓词A定义，因为它是个范围谓词，它是唯一的一个匹配字段。
//B字段因为索引筛选将会在此次扫描中被检查，则索引条目将会被忽略。
select a,b,c from T where a>? or b>?
//这个语名就会走全表扫描

4.2.3、in

select a, b, c, d, e from Tx where a=? and b in(b1,b2,b3) and c>1 and e in (e1, e2, e3)

这个例子中可选的三个索引方案的性能是一样的，所以真正可行的方案可以拆分为多个查询语句，因为如果in是一个状态值，一般来讲列的状态值都是有限的几个，如果in条件过多导致返回的结果过多，就相当于全表扫描了。另外有时也会发现in的性能忽高忽低，不要奇怪这是由于数据量引导起的，如果回表太多多次随机查，性能当然不会好到哪。

• (A,E,C,D,B)：这种方案会读三个索引片，(a,e1,c)(a,e2,c)(a,e3,c)，b会做为过滤谓词用
• (E,A,C,B,D)：这种方案会读三个索引片，
• (E.A.C,D,B)：这种方案会读三个索引片，

五、被动式索引设计

在产品投入大规模使用后才对应用的大部分索引进行调做强工作。主要是用EXPLAIN进行线上语句的分析，然后再优化。一般来讲会发现以下几类问题：

• 全表扫描或全索引扫描；
• 对结果集进行排序，这条着重看两方面内容（a、没有可使查询语句避免排序的索引；b、优化器所选择的访问路径包含了一次多余的排序。）；
• 成本估算，主要是cpu和I/O时间的估计，这一点需要注意估算成本，因为评估出来的数据偏差可能会比较大。

以上三类问题通常是需要运行数据加以分析才能发现在的，所以在系统上线后，一般要监控以下两个大类数据：

5.1、LRT级别的异常监控

5.1.1、程序粒度的均值不够

对于单个事务的监视应该尽早开始，最好在测试阶段就开始，用以捕捉最差输入下的响应时间。而且，分析单个事务的概况比分析同一个程序下许多不同事务的平均概况要简单得多，后者的复杂度就好比让我们比较一些卡车和许多自行车的平均特征。监控的结构包括：程序名、主要模块（对LRT贡献最大）的名称、主要模块对LRT的贡献占比、日期、结束时间；可监控的项有：本地响应时间（s）LRT、SQL调用的总时间（s）SQL、SQL调用的CPU时间（s）CPU时间、同步读时间（s）同步读、等待预读取的时间（s）预读等待、锁等待时间（s）锁等待、其他等待时间（s）其他等待、平均同步读时间（ms）、同步读次数（表页）、同步读次数（索引页）、SQL调用次数、访问的表页数量、访问的索引页数量、顺序预读取请求数、提交次数；

5.1.2、优化问题制造者

需要着手解决问题制造者，而非受害者。同时也要考虑到紧急线上问题修复，但这就是另一个问题了；在有足够的监控数据后，需要有针对性的修复，比如除了sql语句外，还要关注网络和机器情况。解决思路如下，有优化空间的问题制造者有两个特征：1、磁盘服务时间长；2、磁盘读大多是对表页的读取而非索引的读取。解决思路是有优化空间的改进索引，无优化空间的改进程序，但有时很难进行优化，所以调做强的空间一般是能够去除的SQL调用次数乘以每次SQL调用的基本CPU时间。

读表页有以下几种方式：

• 事务中的随机读（同步读，SR）：不与CPU时间重叠，应用程序会停下来，等待所请求的页从磁盘服务器返回。调优潜在空间等于同步读取的表页数和同步读的平均时长的乘积。如果通过使用宽索引避免全部表页的读取，那么本地响应时间就能减少这个量；
• 跳跃式顺序读：响应时间可能还是取决于所花费的I/O时间。相比同步读，这种方式所节省的时间是非常不确定的，具体取决于读取的页之间的平均距离以及条带的实现方式。这种方式的调优潜在空间即为当前预读等待的值；
• 顺序读（顺序预读）：预读取，它的I/O时间与程序所花费的CPU时间是重叠的。一般问题是由于CPU时间引起的，主要的调做强方向是读取更窄的索引段或表段。

5.1.3、优化问题受害者

这类一般都是由于锁等待引起的。所以首先要做的是找出热点资源，找出造成锁等待的行或页。可以利用监控程序或是去判断这个受长时间锁等待影响的组件访问了哪些表。假设索引页或索引行没有被数据库管理系统锁住，那么热点资源必定是这些表中某一个表的页或行，可能是个小表中访问最频繁的行，或者是一个大表中最后的表页（新行一般都插入到表的末尾），一个简单的衡量平均排队时间公式如下：

Q=u/(1-u)S
//Q=平均排队时间
//u=利用率（资源繁忙度）
//S=平均服务时长（每次请求引起的资源繁忙度）

经验显示，资源繁忙度（u）的告警阀值不应大于0.1，因为这意味着在高峰期对象有10%的时间是被锁住的。这样一来，平均排队时间将会是资源锁定时间的≈11%。

5.2、调用级别的异常监视

监视时间段内慢SQL调用的报告。这些报告对于在一个有大量不同SQL语句的慢程中找出较慢的SQL调用很有帮助。假设我们执行了一次高峰时段的跟踪，且基于该时间段内耗费时间的长，生成了最差SQL调用列表，这些慢SQL就是优化的重点。

拿到慢SQL后，优化的优先级建议以调用次数和LRT时间综合来看。因为有些SQL比较耗时但调用次数比较少，这些CASE很容易被忽略掉。