数据库索引设计与优化
一、概述
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语句所涉及的所有列的索引