浅谈数据库索引的结构设计与优化
原文 | http://t.cn/EaibpIz
阅读文本大概需要 5 分钟。
了解数据库索引的必要性
对于稍微数据量大一点的表，如果不适用索引，那么性能效率都会很低；如果绕开了索引，直接进行分区分表，数据库集群读写分离来解决性能问题的话，那么未免也太小题大做了。
对于大多数中小型系统，索引能够帮你解决 90% 的性能问题，所以索引是解决关系型数据库非常有利的武器。
表和索引结构
1.索引页和表页
表和索引都是存在页中。页的大小一般是 4KB。页的大小仅仅决定了一个页能存储多少个索引行，表行。
2.索引行
索引行是很有用的一个概念对于访问路径的时候。索引行的概念可以通过下图来了解:
每一个页上包含了很多索引行，每个索引行里存储着索引条目和指向下一层的页，这种数据结构为 B-tree 结构。
3.缓冲池和磁盘I/O
我们可以使用内存的缓冲池来减小到磁盘的访问。这一策略对 sql 性能表现至关重要。下图展示了磁盘读取到缓冲区的巨大成本：
当我们需要某一页的一行数据时，和需要这一页的数据时，所花费的时间是相等的。可以通过执行
show global status like 'innodb%read%';来判断缓存命中的情况，具体的参数可以自行在网上查找:
可以算出来缓存命中率为 = 260850/(64+260850+1927)=99.24%, 是很高的命中率了。
4.硬件特性
硬盘磁盘的图可以用下图简单表示:
我们的数据库表里的数据就保存在磁盘上，如果要读取数据，就要砖头磁盘，用磁头和磁盘的磁力来改变状态，来读取数据，所以，我们应该尽量少的转动磁盘，来优化数据库性能。
SQL处理过程
我们现在先讨论基础的处理过程，先来谈谈处理过程的一些基本概念。
1.关键字（谓词）
where 子句由一个或者多个谓词组成，比如说：
那么这个就有一个组合谓词，组合谓词是索引设计的主要入手点。
2.过滤因子
过滤因子是描述谓词的选择性，它主要依赖于列值的分布情况。它是一个计算值，公式为：
用来计算谓词结果集的返回大小估算。
3.物化结果集
是执行数据库访问来构建结果集。最好的情况下，是从数据库缓冲池返回一条记录，最坏的情况就是访问大量的磁盘读取数据。
物化结果集有 2 种方式：
- 一次 FETCH 物化返回一条数据
- 提前物化
为SELECT语句创建理想索引
1.三星索引
三星索引是指一条 sql 所能达到索引的最优设计。
第一颗星：如果与一个查询相关的索引行是相邻的，那么这个索引就为第一颗星。
第二颗星：如果索引行的顺序与查询语句一致，则为第二颗星。
第三颗星：查询的数据为索引的信息，不需要额外的磁盘随机读。这一颗星能大大改善性能。
假设有一条 sql 语句如下所示：
如果要满足一星索引：索引的顺序可以是 LNAME, CITY 或者 CITY, LNAME
如果要满足第二星索引：FNAME 加在 LNAME, CITY 或者 CITY, LNAME 后面
如果要满足第三索引：CNO 也要在索引里面
那么组合起来得三星索引就是：LNAME, CITY, FNAME, CNO 或者 CITY, LNAME, FNAME, CNO
前瞻性索引
1.发现不合适的索引
有两种基本的方法来发现不合适的索引：
1.基本问题法(BQ)
2.快速上线评估法(QUBE)
在这里我仅仅讨论一下快速上线评估法(QUBE)
2.快速上线评估法(QUBE)
QUBE 是悲观上限，它的目的是在早期发现程序设计的缺陷，并且及时更改。QUBE 忽略了排队时间，锁竞争时间等，把问题单一化来评估 sql 的性能问题。
下图就是 QUBE 计算评估 sql 时间公式：
可以发现，TR 和 TS 存在巨大的时间差距，随机访问会消耗大量的时间，那么来说说随机和顺序访问。
随机访问：
先说一说磁盘读和访问的区别。读是读取一页的信息，访问时访问一行的信息。所以单次随机访问的时间与一次磁盘随机读取的时间相同，都是 10ms。
顺序访问：
一次顺序读是指物理上读取连续的下一行，这一行要么在同一页中，要么在下一页中，估算出来时间是 0.01ms。
FETCH:
是 FETCH 调用次数来确定被接收行的数量。F的时间数量级要比 TS 大一级，但是要比 TR 小 2 级。
下面给出一个简单的事例来说明 QUBE 计算方法：
那么可以根据上面的公式得到
QUBE 算法其实可以多结合自己的项目事例来计算判断一下，因为这个公式是很多年前的了，现在磁盘读写能力肯定有了显著提升，但是判断 sql 性能的方式是一致的。