关系型数据库索引设计与优化
本文基于mysql innodb引擎
请注意数据库DB2和MYSQL的聚簇索引和非聚簇索引的定义不同。
1、三星索引：
1、如果一个查询相关的索引行是相邻的，或者至少相距足够靠近，则索引可以被标记上第一颗星。（最小化了扫描的索引片的宽度） 2、如果索引行的顺序与查询语句的需求一致，则索引可以被标记上第二颗星。（避免了排序操作） 3、如果索引行包含查询语句中的所有列，则索引可以被标记第三颗星。（避免了回表） 简单理解: 1、where语句中的简单谓词和索引的顺序一致或相邻。 2、order by的最终结果和索引结果集的顺序一致。 3、索引行包含所有查询字段。 案例分析：假设cno为主键，所以二级索引(非聚簇索引)叶子页键值中，值包含了cno不需要建立cno列为索引 select cno, fname from cust where lname between :lanme1 and :lname2 and city = :city order by fname
建立索引从第三颗星到第一颗星。
1、建立第三颗星（lname,city,fname）顺序不必在意，只需包含所有查询字段就可以。 2、建立第二颗星。如果fname在lname之后，那么索引结果集的顺序和需要的顺序不一致（例：记录1的个别字段为lname=b,fname=b。记录2的个别字段为lname=a,fname=a。则索引集顺序为1，2，但需要的顺序是2，1），所以fname必须在lname之前。 3、建立第一颗星。找到简单谓词city。所以第一个索引一定为city，fname不能为第一个因为这样的话，不能使用索引（最左匹配）。但要想满足第一颗星必须city和lname字段相邻，以满足最小的索引片。但city已经是第一个，并且fname必须在lname之前，
因此不能实现三星索引。那就只能在第一颗星和第二颗星之间做取舍。 候选A:（city, lname, fname）舍弃第二颗星，实现最窄的索引片，但无序。 1、取出等值谓词列，将这些列作为索引的最前列。(city) 2、如果有范围谓词，将选择性最好的范围谓词作为索引的下一个列，其他范围谓词加入其后。 (city, lname) 3、按照查询order by顺序添加order by（如果有desc，加上desc）列，忽略1.2步已添加的列。 (city, lname, fname) 4、将查询语句中剩余列，加入索引之后（以不易变的列开始）。(city, lname, fname) 候选B:（city, fname, lname）舍弃第一颗星，实现自排序，但索引片宽。
1、取出等值谓词列，将这些列作为索引的最前列。 (city)
2、按照查询order by顺序添加order by（如果有desc，加上desc）列，忽略1.2步已添加的列。 (city, fname)
3、将查询语句中剩余列，加入索引之后（以不易变的列开始）。 (city, fname, lname)
具体使用A还是B，需要根据实际项目确定（一般第一颗星比第二颗星重要），或者bq/qube算法。
2、索引设计与评估：
个人理解：《数据库索引设计与优化》一书中，其案例中的索引存储结构类似于mysql的myisam，索引和数据分开存储。索引叶子页只保存数据在表中的指针。
所以如果根据innodb的结构，就很难理解其第五章的qube案例。
一旦一个新的查询语句出现，那么就要考虑现有的索引对新的语句来说是否合适。那么如何判断呢？步骤如下：
对现有索引评估方法BQ：
基本问题法bq: 判断是否有一个已存在的索引包含了where子句中的所有列？ 1：如果没有，首先考虑将缺少的谓词列添加到一个已有的索引上。这将产生一个半宽索引。 2：如果还没有达到足够的性能，那么将所有涉及的列都添加到索引上（除主键外），以避免回表。这将产生一个宽索引。 3：如果还没有达到性能要求，那么就要设计一个新的索引。 如何判断半宽索引和宽索引的性能呢？ 1：使用测试库进行测试 2：使用qube算法
对索引的评估方法QUBE：
快速上限估算法qube:
qube假设所有表、索引都是以最理想的顺序组织的。结果为最差输入情况下的响应时间。 响应时间对比公式 LRT = TR * 10mx + TS * 0.01ms + F * 0.1ms 不同访问路劲对比公式 LRT = TR * 10mx + TS * 0.01ms LRT:本地响应时间 TR:随机访问的数量 TS:顺序访问的数量 F:有效fetch的数量
案例分析：假设姓名不重复
select cno, lname, fname, city from cust where lname = "张" and fname = ”飞"
索引A（lname, fname）TR = 2（多一次回表）, TS = 0, F = 1
LRT = TR * 10ms + TS * 0.01ms + F * 0.1ms = 20.1ms
索引B（lname, fname, city）最左匹配 TR = 1（覆盖索引）, TS = 0, F = 1
LRT = TR * 10ms + TS * 0.01ms + F * 0.1ms = 10.1ms
明显索引B更好。
