近期刚拿到这本书《数据库索引设计与优化》,当然是中文版
美国TApio Lahdenmaki和Michael Leach著,“中国工信出版集团”和“电子工业出版社”一起出版的,曹怡倩和赵建伟译的。
书不厚,但感觉讲的挺好,对梳理数据库索引相关的思路挺有帮助,边学边做做笔记。
用word做的笔记,贴上来格式都不对,大概调了调,乱了的地方就乱看吧。
本书的2个目标
- 站在优化器的位置,思考它如何决定应该执行什么样的表和索引扫描,以尽可能高效地执行SQL语句
- 展示如何来量化系统运行所涉及的工作。
一些误区或误解
误区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的性能需求仍然决定了表上索引的上限。现在这个上限是随着硬件能力的提升扩大了,但不代表没有了。
索引设计思想的变迁,逐步进化
- 矩阵模型(20世纪60年代):用于预测每个字段读取和更新的频率,以及包含这些字段的记录插入和删除频率。通常索引被假定只包含一个列,以减少IO量。这些只适用于普通的事务。
- 面向响应时间的索引设计方法:直指索引的目的:在硬件容量限制的前提下保证所有数据库调用运行的足够快。一些工具开始支持自动化工作:采集工作负载样本,为SELECT语句生成一组候选索引;然后基于一些简单的评估公式或一个基于成本的优化器CBO来决定哪些索引最有价值。
- 系统化索引设计:
- 第一步:找到当前索引条件下运行的非常慢或将会非常慢的查询,至少找到在最差输入条件下(如:最大的客户、最老的日期)运行非常慢的查询
- 检测不适合的索引,期初是基于务必复杂的 预测公式进行、也有基于CBO的简化版公式。后来一些极其简单的公式出现(如:QUBE公式和一个估算随机IO数量的简易算法。。。。)
- 20世纪90年代,监控软件成为一个有效的手段
2.第二步:然后必须设计索引使其快起来,并不导致其他SQL调用明显变慢。
- 设计就比较难。经验告诉我们:即便是看起来无害的SELECT,尤其是表连接,通常也会有大量合理的索引设计方式。估算每一种方式太复杂。
- 需要找到一个好的方法,后续会探讨。