数据库索引设计与优化

一、概述

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语句所涉及的所有列的索引

原文发布于微信公众号 - 硬核项目经理(fullstackpm)

原文发表时间:2018-03-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券