数据库索引设计及优化

1.表的索引数量不应有上限，只要所有的SQL都能够流畅的运行。

一个页从磁盘读取到数据缓冲池中，一个页包含多条记录，我们可能需要该页上的所有行或一部分行或一行，但花费的成本都相同，约10ms. 在计算领域中， 10ms是一个很长的时间。
10ms怎么计算出来的：排队时间 + 寻道时间 + 半圈旋转时间 + 传输时间
Q:平均排队时间
U: 平均驱动器繁忙度
S：平均服务时间
Q = (u/(1-u))/S

2.SQL处理过程

select * from user where sex = ‘m’ and (weight > 90 or height > 190)
如果一个索引能满足select查询语句所有谓词表达式，那么优化器就可能建立一个高效的访问路径。

过滤因子描述了谓词的选择性，即表中满足谓词条件的记录行数所占的比例，它主要依赖于列值的分布情况。
组合谓词的过滤因子可能比两个谓词过滤因子的乘积要低的多。
对结果集排序要意味着，即使只需要提取一条记录，也必须物化整个结果集。

磁盘及cpu时间的基础假设：
随机读 10ms
顺序读 40Mb/s

顺序扫描的cpu时间
检查一行记录 5us
fetch 100us

select cno, fname from cust where lname = ‘jack’ and city = ‘beijing’ order by fname

如果结果集有1000行，组合谓词lname 和 city做索引，查询花费的时间 1 * 10ms + 1000 * 0.1 ms = 0.1s 。表没有访问，所需的列值都已经复制到索引中了。

星级给定：
一星 ： 减少了扫描的范围；
二星：索引的顺序和查询条件的需求一致， 不需要排序操作
三星：索引行包含查询语句的所有列，避免访问表操作

5.索引设计
发现不合适的索引：
1）基本问题法；是否有一个已存在的或者计划中的索引包含where字句所引用的所有列，如果是否，可以考虑将缺少的谓词列加到索引中(Basic Question, BQ)
2）快速上限估算法；(Quick Upper-Bound Estimate, QUBE)
本地响应时间(LRT)
随机访问的数量：TR
顺序访问的数量：TS
有效FETCH的数量：F
F的成本比TS大一个数量级
比较值：
LRT = TR * 10ms + TS ** 0.01ms
绝对值：
LRT = TR * 10ms + TS * 0.01ms + F * 0.1ms

扫描索引或表的一个片段，那么第一行的读取即为一次随机访问。后续行的读取，每行都是一次顺序访问。顺序访问的成本比随机访问低得多。一次索引访问的成本与一次表访问的成本基本是相同的。
一次顺序读所消耗的时间是0.01ms。顺序访问的时间是指数据库在判断是否接受一行时所做的必要的处理的时间，不管是在索引中还是表中，所有的行都必须进行这一步检测，被拒绝的行将不需要进一步处理。

通过主键索引读取一个表行需要随机访问一次表和随机访问一次索引。
LRT = 2 * 10ms + 0.01ms = 20ms

扫描索引片的过程：需要进行一次随机访问来找到索引片上的第一条符合条件的索引行，直到找到不匹配的行为止，包括访问的最后一个不匹配的行。
1 * 10ms + 1000 * 0.01ms = 20ms

select cno, lname, fname from cust where zip = :zip and lname =: lname order by fname
存在索引(zip,lname, fname)
LRT = 2 * 10ms + 2000 * 0.01ms + 1000 * 0.1ms = 140ms

表记录不是通过聚簇索引访问的，表的1000次访问就会变成随机访问
LRT = 1001 * 10ms + 1000 * 0.01ms + 1000 * 0.1ms = 10s
这种情况下，将列cno加到索引中让索引成为三星索引。可以使本地响应时间从10s降低到0.1s。

BQ方法所提出的问题：是否有一个现有的或者计划中的索引包含了where字句中所涉及的所有列。

设计索引是为了设法让所有 程序都运行得足够快，同时还能够做到不使用过量的磁盘存储、内存和读缓存，且不使磁盘超载。

半宽索引：最大化索引过滤（所有的谓词都在索引列中）
宽索引：只需访问索引

6.影响索引设计的因素
or操作符和布尔谓词
谓词和其他谓词之间为or操作，那么对于优化器而言，他们也可能是困难的。可行的替代方案有全索引扫描，全表扫描，以及多索引扫描。
好的谓词（BT谓词），只要任何一个简单谓词被判定为不满足条件，那么这一行就可以被排除。
不好的谓词（非BT谓词）：仅仅不满足谓词A或仅仅不满足谓词B无法排除一行记录

8、为表连接设置索引
连接查询中有两类谓词：本地谓词和连接谓词。
只用于访问一张表的谓词称为本地谓词，定义了表和表之间的连接关系的谓词称为连接谓词。
当谓词具有很高的过滤因子时，全表扫描会很快，当过滤因子较低时，索引扫描会更快。

select cname, ctype, ino, ieur from idate =: idate and cust.cno = invoice.cno
只取前20张发票数据
索引 idate TR = 1 TS = 19
表 invoice TR = 20

索引CNO TR = 20
表 CUST TR = 20
提取 20 * 0.1ms
LRT TR = 61 TS = 19
61 * 10ms + 19 * 0.01ms + 2 ms = 612ms

9、星型连接
星型连接与普通连接的差别：
1）位于星型结构中心位置的表称为事实表，它的数据量远大于它周围的表------维度表
2）最佳的访问路径通常是包含维度表的笛卡儿积。

索引统统比表占用的空间大，原因：
1）表进行了压缩处理，索引没有
2）新增一条记录，表不需要空闲空间，索引需要留足够的空闲时间