数据库索引设计与优化（笔记）：第4章 为SELECT语句创建理想的索引（三星索引（理想），最佳索引A/B/、索引代价）

1、并非SQL语句使用了索引就万事大吉。

关键还得看是否合适。一方面，不合适的索引可能导致更差的性能；另一方面，不同的索引表现差异巨大，是否还有更好的索引，是值得追寻的。

在评估索引前，对硬件资源做一些假设

目前的设备，比写这本书时候又有提升；虽然这本书在强调硬件系统的能力提升，一些因为原来硬件受限得到的数据库的概念已经过期

IO时间：  

随机读   10ms（4KB或8KB）

顺序读   40MB/s

顺序扫描CPU时间：

  检查一行记录  5us

  FETCH        100us

从一个不适合索引例子看分析思路

这里跟着记录一下，是展现一下量化计算的方法

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

 

1. 假设：可能的访问路径有两个：索引扫描（LNAME,FNAME）、全表扫描。
2. 考虑姓氏过滤因子为1%。总行数1,000,000。
3. 对于方案1：
   1. 根据谓词条件LNAME=:LNAME扫描索引片
   2. 然后回表校验CITY字段的值。以过滤因子为1%，则需要对比10000索引行和10000个表行。
   3. 假设索引行大小是100字节，则10000索引行是1MB。以顺序读40MB/s，则需要10ms+1MB/40，约35ms。这个倒问题不大，但10000次回表，随机读将花费10000*10ms，即100s。这就长了。
4. 对于方案2：这个场景下，全表扫描可以顺序读，比前面用索引还要快一些。

2、三星索引：查询语句的理想索引

第一颗星：与一个查询相关的索引行是相邻的，或者至少相距足够靠近。就可以标记第一颗星。它最小化了必须扫描的索引片的宽度。

做法：取出谓词的列（WHERE COL=），把这些列作为索引最开头的列，任意顺序都可以。

第二颗星：如果索引行的顺序与查询语句需求一致，则可以 标记第二颗星。这排除了排序操作。

做法：将ORDER BY列加入到索引中。不要改变这些列的顺序，但忽略那些在第一步已经加入索引的列。

第三颗星：如果索引行包含查询所需所有列，就可以被标记第三星。避免了表访问。

做法：将查询语句（不是WHERE谓词）中剩余的列加到索引中取，列在索引中的添加顺序对查询语句的性能更没有影响，但将易变的列放在最后能够降低更新的成本。

对照语句：

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

 

三星索引是（LNAME,CITY,FNAME,CNO）或（CITY,LNAME,FNAME,CNO）

这只是个简单的例子，还有更复杂的场景，在后面分析。

之所以说简单：

WHERE条件不包含范围谓词（本章简单讨论，第6章深入讨论）

FROM语句只涉及单表（第8章讨论）

所有谓词对优化器来说都足够简单（第6章讨论）

范围谓词和三星索引：

考虑这个例子

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

 

这种情况下，反着来考虑三星。

 

第三颗：确保查询语句中的列都在索引中。不考虑顺序，则有（CNO,FNAME,LNAME,CITY，不考虑顺序）

第二颗：满足ORDER BY，但必须放在BETWEEN谓词LNAME列之前才能成立。

比如：（CITY,FNAME,LNAME）：这个索引可以使结果集以FNAME的顺序排列，而不需要额外的排序。

如果放在之后，如（CITY,LNAME,FNAME），因为这个索引没有按照FNAME排序，就需要再次排序。

因此，FNAME必须在LNAME前，可以使（FNAME,….）或者（CITY,FNAME,….）

         第一颗：考虑比较谓词，这里有CITY和LNAME。用两个，比单用CITY扫描片更窄。但和第二颗冲突。

只能权衡：第三颗一定能有；在第一、第二里面取舍。要避免排序，就拥有第二颗；要最窄索引片，就要第三颗。

通常第一颗比第二颗更重要，但不总是这样。

公式化：

当存在范围谓词时，就无法实现三星索引，需要降级。（也许）不得不牺牲第二颗星来满足一个更窄的索引片（第一颗星），这样最佳索引就只拥有两颗星。

首先设计一个索引片尽可能窄（第一）的宽索引（第三）。如果查询使用这个索引不需要排序（第二），则这个索引就是三星索引。否则只能是一个二星索引。看情况是牺牲第一颗，还是牺牲第二颗。

候选A算法（牺牲第二颗，需要排序）

1. 取出对优化器来说不过分复杂的等值谓词列，将这些列作为索引的前导列---顺序任意。
2. 将选择性最好的范围谓词作为索引的下一个列，如果存在的话。最好的选择性是指对于最差的输入值有最低的过滤 因子。只考虑对优化器来说不过分复杂的谓词即可。
3. 以正确的顺序添加ORDER BY列（如果ORDER BY有DESC，加上DESC）。忽略前面已经加的索引列。
4. 以任意顺序将SELECT中其余列添加到索引中（以不易变的列开始）

候选B算法（牺牲第一颗，不需要排序）

1. 取出对优化器来说不过分复杂的等值谓词列，将这些列作为索引的前导列---顺序任意。
2. 以正确的顺序添加ORDER BY列（如果ORDER BY有DESC，加上DESC）。忽略前面已经加的索引列。
3. 以任意顺序将SELECT中其余列添加到索引中（以不易变的列开始）。

 

到底哪个好，在后面有QUBE方法来估算。

 

近年来排序速度已经提升很多。每排序一行消耗10us。50000行需要0.5s。由于现在硬件的排序速度快，所以如果一个程序要取出结果集的所有行，A可能和B一样快，甚至比B更快。

然而，如果一个程序只获取一部分数据量，则B可能会比A快很多。

需要为每个查询都设计理想索引吗？

1. 在为查询语句设计一个最佳索引后，去看一下已经存在的索引使很有必要的。有可能某个在使用的索引几乎和理想索引差不多好，尤其是打算在这个已有索引最后增加一个列的情况下
2. 完全多余的索引：比如，没有范围谓词的情况下，(A,B)=(B,A)，不需要2个
3. 近乎多余的索引：假设（A,B,C,D）已经存在，为一个新的查询语句设计的理想索引包含了以这个索引的4列为开头的14列。那么旧的要不要删掉？

基于性能假设，分别计算在两种场景下的CPU和IO的时间影响。得出结果是主要受限还是CPU时间，两个差不多。所以4列的可以删掉了。

下面抄一遍，省的以后查书，介绍了如何计算的具体例子

假设索引行由50字节->200字节、扫描1000行索引片，并取出处理，计算CPU时间和IO CPU时间=1000*0.1ms+10000*0.005=150ms（1000次FETCH，10000次检查） 4列时4KB页数量：1.5*10000*50/4000，约200（1.5是空闲系数） 14列4KB页数量：1.5*10000*200/4000，约800 顺序读取时间（4列）=200*0.1ms=20ms 顺序读取时间（14列）=800*0.1ms=80ms 大部分时间是在CPU处理上，而时间基本一样。所以4列的无用。

 

可能多余的索引

如果已有索引是（A,B,F,C），新考虑的理想索引是（A,B,C,D,E,F）。如果把旧的替换为（A,B,F,C,D,E），是否就不需要新的了？

要考虑理想索引的优势：

1. 可能使得查询能有更多的匹配列
2. 可能可以避免排序

可以通过类似上面的算法进行估算，或者是后面的QUBE进行估计。

 

3、新增一个索引的代价

如果一个表上有100个查询，为每个设计了最佳索引的话，即时没有重复，也会显得索引有些多，会导致数据变更操作会变慢。

响应时间：

向DB添加一行，必须为每个索引都添加相应的行。在写书时候的硬件条件下，在一个索引上添加行，插入操作花费时间以10ms计（主要还是读叶子页进来）。

这样，为10个索引的表插入一行，则需要10*10ms=100ms。

如果为这个表再插入20行，则粗略估计是10*20*10=2s。书上说1.8，是假设有些事更新到同一个叶子页的。

从时间上来看，响应时间会严重上升。

磁盘负载：

• 修改过的叶子需要写到磁盘上。由于采用异步写，不会影响事务的响应时间。但是会增加磁盘负载。下面把详细计算再抄一下。

 

• RAID5机制决定它会写放大：因为RAID5需要读取写回，每次访问消耗12ms（寻到4ms，读、写旋转各4ms）;而且同时需要修改2个磁盘的数据，所以带来的磁盘繁忙度为24ms。
• RAID10：直接写，涉及2个盘，所以增加磁盘繁忙度为：（4+4）*2=16。书上是12，不知怎么算的。
• 如果插入频率较高，磁盘负载会成为瓶颈 ，限制了表上索引数量。删除和插入负载相同；更新只影响列值被修改的索引。

 

在一个包含4个索引的表上分析：

表行压力不大：在插入时，数据保存在表的末尾，在页被写到磁盘之前，许多行写在这个页上，操作不会造成大量磁盘写。

4个索引有问题：因为都是随机插入，每秒更新20行，则一共是每秒80次随机读、

 

假设采用的磁阵：RAID5,128盘（112活动，16空闲）。读缓存64GB，写缓存2GB。

 

先不考虑缓存问题：

80次随机写。考虑磁盘平均排队时间是3ms

这4个索引更新带来的磁盘繁忙度为：4（索引）*20（行）*（2（次）*3（排队）+24（一次RAID5读写繁忙度））=80*（6+24）=2400ms=2.4s。先对1秒，磁盘负载为240%。

磁盘平均负载240%/112，约为2%。通常认为平均磁盘负载不应该超过25%。

 

考虑读写缓存

这里是假设完全随机，实际中数据访问，与业务相关，可能不一定这样。但这里用完全随机展示一种思路。

 

假设75000个叶子中任何一个叶子页，对同一个叶子页的相邻两次访问时间是平均的。

每秒更新20个，则平均每个是50ms。则75000*50约为1小时。

 

如果读缓存平均保留时间是30分钟，则无法在读缓存命中。

 

如果写缓存比读缓存时间更短，如10分钟，则读缓存作用更小：一个叶子在10分钟内更新一次以上，才能通过写缓存省一次磁盘写。

 

所以，磁盘的繁忙度还是随机的，读写缓存没起到有效作用。

 

RAID5的平均负载贡献：

 

L = N*I/D

N：随机插入涉及的索引数量

I：插入频率（每秒插入的行数）

D：磁盘数量（空闲盘除外）

L<1：负载很低

L在1~10之间，负载增加可能明显

L>10，很可能有问题。

按照刚才例子：L=4*20/112=0.7，负载很小。

以上数字是经验数字。在新硬件系统，需要还需要参考着看 。这里是给的方法。

磁盘空间

索引会占用额外的空间。不排除有索引大于数据本身。

通常计算索引，会考虑空闲系数，如1.5倍。

索引会占用空间：如：1千万记录，一个索引占400字节，则索引开销：1.5*1千万）400=6GB。

索引变大，缓冲空间也增大，因为非叶子节点要缓冲。

5、总结建议

1. 在磁盘空间成本较低的情况下，机械地为每个查询设计最佳索引也是不明智的。因为索引的维护会使得一些程序速度太慢或者使磁盘超负荷。
2. 最佳索引是一个好的开端，但在决定为一个新的查询创建索引前需要先考虑一下三种多余的索引。
3. 即使有可能为每一个新查询都设计最佳索引，但在实际中更常见的情况是，只对那些由于不合适的索引而导致速度太慢的查询进行索引设计。