《数据库索引设计优化》读书笔记（六）

第10章 多索引访问

练习

10.1 假设多索引访问一节中所描述的拥有位图索引的 CIA 表包含 200000000 行数据。请评估（a）位图索引和（b）半宽 B 树索引所需的磁盘空间。假设一个字节占 8 位，请将磁盘空间的差异转化为每月需要支付的美元金额。

        书中关于拥有位图索引的 CIA 表的描述如下：

        位图索引的比较优势在于能够很容易地使用多个位图索引来满足单个查询。考虑一个有多个谓词条件的查询，每个谓词上都有一个索引。虽然有些系统可能尝试对多个索引的记录标识进行交集操作，但是传统的数据库可能会只使用其中一个索引。位图索引在此种情况下工作得更好，因为它们更紧凑，而且计算几个位图的交集比计算几个记录集合的交集更快。在最好的情况下，性能的提升与机器的字长成比例，因为同一时间两个位图能够进行一个字长的位的交集计算。最佳的使用场景是，每一个单独谓词的选择性不好，但是所有谓词一起进行索引与后的选择性很好。使用位图索引考虑如下查询，“找出有棕色头发，戴眼镜，年龄在 30 岁至 40 岁之间，蓝眼睛，从事计算机行业并居住在加利福利亚的人”。这意味着对棕色头发位图、佩戴眼镜的位图、年龄在 30 岁至 40 岁间的位图等进行交集计算。

        在当前的磁盘条件下，只要查询中没有太多的范围谓词，使用一个半宽 B 树索引是性能最佳的方案，即便对于像 CIA 那样的应用来说也是如此。对于上文中的例子，一个用 HAIRCOLOUR、
GLASSES、EYECOLOUR、INDUSTRY 和 STATE 的任意排序序列作为开头，并以 DATE OF BIRTH 作为第 6 列的索引将提供非常出色的性能，因为这使得访问路径将会有 6 个匹配列：包含目标结果集的索引片将会非常窄。

分析：

        位图索引的使用空间主要跟表的记录数和索引列的键值数有关，题目中只给了表的记录数，所以需要根据实际情况可以确定 6 个位图索引的键值数如下：

• 头发颜色 键值数为 5
• 是否戴眼镜 键值数为 2
• 年龄段 键值数为 10
• 眼睛颜色 键值数为 10
• 行业 键值数为 100
• 州 键值数为 50

（a）6 个位图索引需要的磁盘空间为
(5+2+10+10+100+50) * 200000000 /8/1024/1024/1024 = 4.12G

        B 树索引的空间跟索引字段的长度有关，假设半宽索引的 6 个字段的总长为 50 字节

（b）半宽 B 树索引所需的磁盘空间为
1.5 * 50 * 200000000 /1024/1024/1024 = 13.97G

        其中 1.5 是一个经验性的空间放大因子（Overhead Factor），用于考虑索引的存储开销，包括 B 树的结构开销、填充因子（Fill Factor）、分裂和碎片化等。通常认为 B 树索引的实际空间占用大约是纯键值所占空间的 1.3~1.5 倍（取决于具体实现和负载类型），使用 1.5 是一个保守估计，确保预留足够的空间。

        书中给出的磁盘空间价格为每 G 每月 50$，所以每月需要支付的美元金额差异为 50 * (13.97 - 4.12) = 492.50$，即半宽 B 树索引比位图索引每月需多付 492.50 美元。

补充：

        位图索引会带来“位图段级锁”的原因是：使用位图索引时，一个键指向多行（成百上千），如果更新一个位图索引键，会同时将其它行对应位图索引字段进行锁定！

• 较之 B-Tree 索引的优点：位图以一种压缩格式存放，因此占用的磁盘空间比 B-Tree 索引要小得多。
• 较之 B-Tree 索引的缺点：锁定的代价很高，会导致一些 DML 语句出现“锁等待”，严重影响插入、更新和删除的效率，对于高并发的系统不适用。

位图索引使用原则：

        位图索引主要用于决策支持系统或静态数据，不支持索引行级锁定。在 OLTP 环境中，如果一个表更新比较频繁，千万不要使用位图索引。如果数据仓库环境中，使用了位图索引，也最好在加载数据的时候将其删除，等数据加载完成以后重新创建。

        位图索引最好用于低 cardinality 列（即列的唯一值除以行数为一个很小的值，接近零），例如“性别”列，列值有“M”，“F”两种。在这个基本原则的基础上，要认真考虑包含位图索引的表的操作特点，如果是并发操作高的系统，不适合使用位图索引！