数据库索引与优化 笔记

一、概述

1.索引误区：

索引层级不要超过5层

单表的索引数不要超过6个

不应该索引不稳定的列

2.在当前磁盘条件下，只有在更新频率多于10次/秒的情况下，不稳定列才可能成为问题

1.表和索引行都被存储在页中，页的大小一般为4KB，缓冲池和I/O活动都是基于页的

2.索引行在评估访问路径的时候是一个非常有用的概念，一个索引行等同于叶子页中的一个索引条目，字段的值从表中复制到索引上，并加上一个指向表中记录的指针

3.非叶子页通常包含着一个（可能被截断的）键值，以及一个指向下一层级页的指针，该键值是下一层级页中的最大键值，多个索引层级按照这一方式逐层建立，直到只剩下一个页，这种组织方式的索引称为B树索引

4.内存中的缓冲池进行最小化磁盘活动，如果一个索引或者表页在缓冲池中被找到，那么唯一的成本就是去处理这些索引或者表的记录，当一个索引或表页被请求时，它的理想位置是在数据库缓冲池中

5.辅助式随机读：自动跳跃式顺序读（DB2）、列表预读（DB2）、数据块预读（oracle）

6.聚簇索引是指定义了新插入的表行所在表页的索引

三、SQL处理过程

1.访问路径的成本很大程度上取决于索引片的厚度，即条件表达式确定的值域范围

四、为SELECT语句创建理想的索引

1.使用不合适的索引有可能会导致比全表扫描更差的性能

2.三星索引：

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

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

如果索引行包含查询语句中的所有列，可以标记为第三颗星，避免了访问表的操作，仅访问索引就可以了

3.ORDERBY和范围谓词同时存在不得不牺牲第一或第二颗星

五、前瞻性的索引设计

1.基本问题法（BQ）：是否有一个已存在的或者计划中的索引包含了WHERE子句所引用的所有列（一个半宽索引）？

2.BQ的目的只是确保我们至少可以通过索引过滤来最小化对表的访问

3.快递上限估算法（QUBE），在新方案的设计过程中使用

六、影响索引设计过程的因素

七、被动式索引设计

八、为表连接设置索引

1.嵌套循环连接，DBMS首先在外层表中找到一行满足本地谓词的记录，然后再从内层表中查找与这一行数据相关的记录，并检查其中哪些符合内层表的本地谓词条件，可以被两个单表的游标以及在程序中编写的嵌套循环代替。另外还有哈希连接和合并扫描连接

2.通过冗余数据优化连接查询

九、星型连接

十、多索引访问

十一、索引和索引重组

1.当在表中插入一行数据时，DBMS会尝试将索引行添加至其索引键所属的叶子页上，但是该索引页可能没有足够的空闲空间来存放这个索引行，在这种情况下，DBMS将会分裂该叶子页

2.分裂一个索引页只需要一次额外的同步读，约10ms，叶子页分裂会导致读一个索引片变得更慢

3.索引重组是为了恢复索引行正确的物理位置，它对于索引片扫描和全索引扫描的性能而言很重要

十二、数据库管理系统相关的索引限制

十三、数据库索引选项

十四、优化器不是完美的

1.完全避免由索引改进导致性能下降的唯一方法是，对每一个SQL调用都使用提示

十五、其他评估事项

十六、组织索引设计过程

半宽索引：一个包含WHERE子句中所有列的索引，使用半宽索引将使得访问路径仅在必要时才访问表

聚焦索引：在SQL Server中是指一个包含表行的索引，在DB2中是指任何一个索引行顺序与表行顺序相同或计划相同的索引

聚簇索引：使得DBMS在向表中添加记录时，将新记录添加至由聚簇索引键所定义的主页上。一张表上只能有一个聚簇索引

覆盖索引：指一个包含了SELECT语句所涉及的所有列的索引

 

 

参考博文：https://www.jianshu.com/p/56130d69731c