为SELECT创建理想的索引

夏末蝉未鸣01

已于 2024-09-04 17:18:48 修改

阅读量899

点赞数 23

分类专栏：数据库文章标签：数据库

于 2024-07-25 15:39:35 首次发布

本文链接：https://blog.csdn.net/m0_62410482/article/details/140130817

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

根据三个最重要的需求为查询语句的索引指定星级

影响表及索引扫描性能的主要因素

当程序中所有的SQL都使用到了一个或多个索引时，有些DBA会认为一切看起来很正常。但是，使用一个不合适的索引有可能导致比全表扫描更差的性能。

磁盘及CPU时间的基础假设

I/O时间
随机读    10ms（4kb或8kb的页）
顺序读    40Mb/s

顺序扫描的CPU时间
检查一行记录   5μs
FETCH        100μs

不合适的索引

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

对于使用索引。DBMS会根据谓词条件LANME=:LNAME扫描索引片。然后对索引片上的每个索引行，都必须回到表中校验CITY的值。由于表中的行是根据CNO字段而非LNAME来聚簇的，所以这个操作需要一次磁盘随机读，获取完整的结果集需要比对10000个索引行（FF=1%）和10000个表行。假设索引大小是1000000×100byte≈100MB，读取宽度为1%的索引片需要10ms+100MB/40MB×1%≈35ms，但是比对此图字段时10000次随机读需要10000×10ms=100s，这太慢了。

对于全表扫描。只有第一个页需要随机读，假设表大小为1000000×600byte≈600MB，则花费的I/O时间为10ms+600MB/40MB≈15s，也太慢了。

在这个场景下，全表扫描的CPU时间要比使用索引长的多，因为DBMS要比对1000000行而不是20000行，并且要对这些行排序。但由于是顺序读，CPU时间可以与I/O时间交叠。全表扫描比在不合适索引上扫描要快，但还不够快，需要有个更好的索引。

三星级索引的设计——最理想的索引设计

如果使用了三星索引，一次查询通常只需要进行一次磁盘随机读及一次窄索引片的扫描，其响应时间通常会比使用一个普通索引的响应时间少几个数量级。即使返回的结果集有1000行，响应时间也不到1s。

假设结果集为1000行，那么组合谓词LNAME=:LNAME AND CITY=:CITY的过滤因子FF=0.1%，根据如图的三星索引，匹配列有两个（MC=2）。这种情况下，查询将花费10ms+1000×0.1ms≈0.1s来扫描这个索引片。在这个过程中，表根本没有本访问过，因为所需的列值都在索引中了。

根据三个最重要的需求为查询语句的索引指定星级

对于一个查询。最小化需要扫描的索引片的宽度，那么可以给索引标上第一颗星；省略排序操作，那么可以给索引标上第二颗星；避免表的访问，那么可以给索引标上第三颗星。

为了满足第一颗星

去除所有等值谓词的列（WHERE COL=...），把这些列作为索引最开头的列——以任意顺序排序都可以。

为了满足第二颗星

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

为了满足第三颗星

将查询语句中剩余的列加入到索引中去，列在索引中添加的顺序对查询语句的行难呢过没有影响，但是将易变的列放到最后能够降低更新的成本。

范围谓词与三星索引

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

“BETWEEN谓词”替换“=谓词”后，对索引设计会产生影响，下面以相反的顺序考虑三颗星。

为了满足第三颗星，将SELECT中的所有列放入索引中。

为了满足第二颗星，需将FNAME列放在BETWEEN谓词列LNAME之前才生效，因为CITY的值只有一个（=谓词）。

再考虑第一颗星，如果CITY是索引的第一个列，我们将会获得一个相对较窄的索引片，如果用索引（CITY, LNAME,......）的话，索引片会更窄，获得更好的收益。但这样其他列（如FNAME）就不能放在它们中间。因此，第一颗星和第二颗星只能二选一，无法同时满足。

宽索引

宽索引是指一个至少满足第三颗星的索引。该索引包含了SELECT语句所涉及的所有列，因此使得查询只需访问索引而无需访问表。

为查询语句设计最好索引的算法

理想的索引是一个三星索引，然而，当存在范围谓词时，这是不可能实现的。不得不牺牲第二颗星来满足一个更窄的索引片（第一颗星），这样最佳索引就只拥有两颗星。

候选索引A（CITY, LNAME, FNAME, CNO），需进行一次排序。

候选索引B（CITY, FNAME, LNAME, CNO），无需排序，但索引片更窄。

导目前为止，我们所做的只是设计理想索引或是最佳索引。但是这是否是实际可行的，在这个阶段还不好说。

现今排序速度很快，为什么我们还需要候选B

今年来，排序速度已提升很多。现在大多数的排序过程都在内存中进行，每排序一行大约10μs左右，5000行就是0.5s，这对于一次事务操作来说也许是可接受的，但对于CPU时间来说是一个比较大的开销。

由于在现在的硬件条件下排序速度很快，所以如果一个程序取出结果集的所有行，那么候选A、B可能一样快，甚至候选A比B更快。

然而，如果一个程序只需获取一段数据量（如LIMIT 0, 50），那么候选不可能会比A快很多。

需要为所有查询语句设计理想索引吗

为每一个查询设计最佳索引是很简单的，只要给出下面内容即可：

1、查询语句

2、数据库统计信息（行数，页数，列值分布等）

3、对于每一个简单谓词或组合谓词最差情况下的过滤因子

4、已经存在的索引

在为查询语句设计了一个最佳索引后，去看一下已经存在的索引是很有必要的。有可能某一个已经存在的索引几乎和理想索引差不多好用，特别是打算在这个索引的最后添加一些列的情况下。

完全多余的索引

如索引(A, B)和索引(B, A)，如果没有查询包含A列或B列的范围谓词（如WHERE B BETWEEN :B1 AND :B2）的话，其中一个索引是完全多余的。

近乎多余的索引

假设索引(A, B, C, D)已经存在，为一个新的查询语句设计的理想索引包含了以这个索引为开头的14个列，新索引创建后，原索引是否要删除？

假设索引行大小分别为50字节（4列）和200字节（14列），计算扫描10000行索引片并从中取出1000行需花费多少时间。

CPU时间=1000×0.1ms+10000×0.005ms=150ms
（两种情况都是1000次FETCH调用和10000个索引行）

4KB大小的叶子页的数量
4列：1.5×10000×50÷4096≈200页
14列：1.5×10000×200÷4096≈800页
（1.5为空间系数）

顺序读时间
4列：200×0.1ms=20ms
14列：800×0.1ms=80ms

由于顺序读的处理过程使得响应时间还是受CPU时间的限制，所以使用这两个索引的响应时间并无明显不同，在新的14列索引创建之后，旧的索引就多余了。

可能多余的索引

假如有这样一个场景：一个新的查询语句的理想索引是(A, B, C, D, E, F)，而表上已经存在的索引是(A, B, F, C)。那么把已经存在的索引替换成(A, B, F, C, D, E)的话，索引(A, B, C, D, E, F)和(A, B, F, C)可能就多余了。

理想索引可能在两方面比索引(A, B, F, C, D, E)要好。

1、可能使得查询有更多的匹配列；

2、可能可以避免排序。

这两个优势都受到需要再索引片上扫描的行数的影响。

新增一个索引的代价

如果一个表上有100个不同的索引，表的插入、更新和删除操作就会变得很慢。

响应时间

当DBMS向表中添加一行时，它必须在每一个索引上都添加相应的行。在当前的硬件条件下，在一个索引上添加一行，插入操作所花费的时间就增加10ms，因为必须从磁盘上读取一个叶子页。当一个事务向一张有10个索引的表里插入1行数据时，索引的维护就会使响应时间增加10×10=100ms，当插入的数据量变多或索引变多时，耗时之长可能让人无法接受。另外，从磁盘负载的角度来看，要在一个大表上进行每秒多余10行的插入操作可能不容许表上有10个以上的索引。