数据库索引设计与优化（笔记）：第1章 概述

近期刚拿到这本书《数据库索引设计与优化》，当然是中文版

美国TApio Lahdenmaki和Michael Leach著，“中国工信出版集团”和“电子工业出版社”一起出版的，曹怡倩和赵建伟译的。

书不厚，但感觉讲的挺好，对梳理数据库索引相关的思路挺有帮助，边学边做做笔记。

用word做的笔记，贴上来格式都不对，大概调了调，乱了的地方就乱看吧。

 

 

 

本书的2个目标

1. 站在优化器的位置，思考它如何决定应该执行什么样的表和索引扫描，以尽可能高效地执行SQL语句
2. 展示如何来量化系统运行所涉及的工作。

一些误区或误解

误区1：索引层级不超过5层。

• 其基于的假设就是：只有根页是留在内存中的。
• 在当前处理器条件下，所有非叶子节点都在缓冲池中，每个索引层级向索引扫描过程贡献50us
• 如果非叶子页不在缓冲池中，而在存储服务器的读缓存中，则读取索引页花费1ms
• 一次磁盘随机读写是10ms。

基于上面的信息，来算一下5层索引的性能（量化

假设：1亿行的索引，平均长度100字节。按照4KB一页，每个叶子页放35个索引行。假设非叶子页不截断（XXX）索引建，页按35来算。所有数值不是精确计算，有一定灰度。

倒着网上排，从1亿开始算，看多少能放下，一层一层向上算。

 

索引层级	索引行	页
根	2	1
2	70	2
3	2400	70
4	83000	2400
5	2900000	83000
叶子页	100,000,000	2,900,000叶子页

 

所以：该索引占用空间：2900000+83000+….，以3000000计算。每个4KB，则是12GB

叶子页占大头，2900000*4，约12GB。比较合理的假设是这些页通常会从磁盘读取（10ms）

上面层级的索引，占用该空间就很小了，最大的第五层也就300多MB。如果该索引被频繁使用，则不在数据库缓冲池，也在存储缓存中。余下的更小，基本就在缓冲池中。

 

访问这个6层索引的任意一个索引行，将花费10~20ms（主要是磁盘IO）。这里想说的是，对索引的层数做限制是没有太大意义的。

思考：但是到了7/8/9层呢？不过这是一个很大的数字了。按照这个数据模型预估，到7层就是35亿的行了。

误区2：单表的索引数不超过6个。

误区3：不应该索引不稳定的列

原来考虑的原因：索引行是按照索引键顺序存储的，当索引键中有一列被更新时，DBMS可能不得不把相应的行从旧的索引位置移到新的位置来保持顺序性。如果新位置与旧位置在同一个叶子页上，还好说，只有一个页受影响。然而如果被修改的是第一列或唯一列，有可能被移动到不同的叶子页。以一个4层索引为例，假设只有根页在内存中，则需要6次磁盘随机操作（取旧的，整新的）。按照以前的IO性能，每次30ms，则180ms，是够慢的。

 

现在新形势：

不过现在内存很大，比如有3层非叶子节点都保留在内存中，一次IO需要10ms，则响应时间变为20ms。

此外，对于多列组合索引，能使得索引键值唯一。当不稳定的列为组合索引的尾列时，不会导致索引迁移到新的叶子。所以，更新一个不稳定列，只会对更新操作增加10ms的响应时间。

 

结论：不稳定的列，如果一定是查询必备条件，则需要加列。如果它与其他AND，最好构建组合索引，把更新频繁的列放在尾列。

IO决定了索引数目的上限

索引的变化，最终还是需要写到磁盘的。磁盘的负载及INSERT、UPDATE、DELETE的性能需求仍然决定了表上索引的上限。现在这个上限是随着硬件能力的提升扩大了，但不代表没有了。

索引设计思想的变迁，逐步进化

1. 矩阵模型（20世纪60年代）：用于预测每个字段读取和更新的频率，以及包含这些字段的记录插入和删除频率。通常索引被假定只包含一个列，以减少IO量。这些只适用于普通的事务。
2. 面向响应时间的索引设计方法：直指索引的目的：在硬件容量限制的前提下保证所有数据库调用运行的足够快。一些工具开始支持自动化工作：采集工作负载样本，为SELECT语句生成一组候选索引；然后基于一些简单的评估公式或一个基于成本的优化器CBO来决定哪些索引最有价值。
3. 系统化索引设计：

1. 第一步：找到当前索引条件下运行的非常慢或将会非常慢的查询，至少找到在最差输入条件下（如：最大的客户、最老的日期）运行非常慢的查询

• 检测不适合的索引，期初是基于务必复杂的 预测公式进行、也有基于CBO的简化版公式。后来一些极其简单的公式出现（如：QUBE公式和一个估算随机IO数量的简易算法。。。。）
• 20世纪90年代，监控软件成为一个有效的手段

      2.第二步：然后必须设计索引使其快起来，并不导致其他SQL调用明显变慢。

1. 设计就比较难。经验告诉我们：即便是看起来无害的SELECT，尤其是表连接，通常也会有大量合理的索引设计方式。估算每一种方式太复杂。
2. 需要找到一个好的方法，后续会探讨。