原文 | 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 种方式:
为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 调用次数来确定被接收行的数量。F的时间数量级要比 TS 大一级,但是要比 TR 小 2 级。
下面给出一个简单的事例来说明 QUBE 计算方法:
那么可以根据上面的公式得到
QUBE 算法其实可以多结合自己的项目事例来计算判断一下,因为这个公式是很多年前的了,现在磁盘读写能力肯定有了显著提升,但是判断 sql 性能的方式是一致的。