高性能MySQL 第五章: 高效索引

第五章整体来讲是非常有价值的, 读几遍都不过分, 珠玉非常多, 先记下部分深有感触的部分.

排序类查询优化

我个人近期对这个感触是比较深的, 如果filesort的对象是一个比较小的集合, 那是非常快的, 但如果对象集合到达上百万, 这样就显得不是很高效. 对于选择性非常低的的列, 可以增加一些特殊索引来方便排序, 比如一个游侠玩家信息表格(profiles), 里面有一列是sex, 那么sex的选择性肯定非常低, 最多三种结果: 男, 女, 人妖. 这种情况, 我们可以添加一个索引(sex, rank) 用于下面的查询: 获取排名前10的男性玩家信息:

select <col> from profiles where sex = 'M' order by rank limit 10

这个查询同时使用了order by 和limit, 没有的话, 会非常的慢.

MySQL 里面翻页关于limit的翻页问题也是非常出名的, 比如

select <col> from profiles where sex = 'M' order by rank limit 100000, 10

当limit的值很大时, 随着偏移量的增加, 由于MySQL查询也会变得相当的慢, 这时候, denormlization, precomputing, cache 会是解决这些问题的三把斧.

当然我个人认为, 后端能做的技术优化是有限的, 前段稍微改下访问策略胜过后端无数优化. 比如限制用户只能访问前10万条数据, 或者只提供用户往前,往后翻页选项, 因为很少有用户想直接跳到搜索结果的第10000页, 这样对用户的访问影响不大, 而且随着用户不断的翻页, 数据都得到有效缓存, 对之后的页面访问也是有着效率提升.

另外一个不错的查询策略是延迟关联, 可以通过覆盖索引返回主键, 再根据主键返回原表的行, 这样可以MySQL扫描需要丢弃的行数.

比如下面这个例子.

SELECT <cols> FROM profiles INNER JOIN (
   SELECT <primary key cols> FROM profiles
   WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
) AS x USING(<primary key cols>);

聚簇索引

每个表都有一个唯一的聚簇索引: * 默认情况下, 聚簇索引是表内定义的primary key. * 没有primay key 被定义时, DB engine会选取第一个unique index作为聚簇索引. * 当没有unique index 被定义时, 系统会自动生成一个GEN_CLUST_INDEX 与row id 进行匹配. (6 byte, 单向增长)

聚簇索引作用: 访问聚簇索引是非常快的, 这里引用 Carl_ 这文对聚簇索引的比喻,

最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说，你翻到新华字典的汉字“爬”那一页就是P开头的部分，这就是物理存储顺序（聚簇索引）；而不用你到目录，找到汉字“爬”所在的页码，然后根据页码找到这个字（非聚簇索引）。

二级索引或者说非聚簇索引, 实际上都会讲聚簇索引作为索引的一部分进行保存. 就像上述比喻一样, 二级索引相当于目录, 保存一个指针, 指向聚簇索引位置.

因此如果查询通过聚簇索引效率是比二级索引快的.

由于二级索引都会引用聚簇索引, 关于聚簇索引设计的几个细节

尽量短, 最好是整型: 不然二级索引会变得非常长, 用二级索引查询时效率会降低很明显.
将自增字段ID设为聚簇索引不一定最好: 由于聚簇索引的唯一性, 最好了解该报最常使用的查询语句, 并对应的建立聚簇索引, 也就是说.

覆盖索引

我们知道索引实际上是储存了一部分列表数据的, 索引通常的使用方式是, 通过索引字段, 找到对应行所在的页面, 再获取查询所序行的相关信息. 可是如果要查询的所有字段本身就是索引一部分的话, 那就不需要去查找相关页面, 直接可以通过索引返回查询信息. 这种索引我们称之为覆盖索引.

覆盖索引的优势: 1. 索引条目一般远小于数据行数大小, 只需要读取索引, 那么将极大程度减少I/O操作. 2. 索引是按列 值顺序进行存储, 对于I/O 密集的范围操作, 会比随机从磁盘读取每一行数据的I/O少的多. 3. InnoDB 存储引擎二级索引有在leaf node 保存了主键值, 所依如果二级索引覆盖了主键值的相关查询, 可以避免对主键的二次查询.

如何判断query是否使用了覆盖索引?

书中原例子

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4673
        Extra: Using index

使用explain，如果Extra column包含 using index 那么表示这个query采用了覆盖索引。

第五章总结

Single-row access(单行访问) 是非常慢的, 特别是机械硬盘, 虽然固态硬盘随机I/O读写速度快很多, 但这点还是成立. 使用索引最好帮助查询返回尽可能多所需要的行.
与随机I/O访问相对比, 顺序访问是非常快的: 1. 顺序I/O 不需要多次磁盘寻道, 尤其是机械硬盘顺序读写更快. 2. 顺序读写不需要额外的排序操作, 对于group by相关操作更是避免了按组进行排序跟聚合的操作.
覆盖索引是非常快的, 如果一个索引包含了查询所需要的所有列, 那么引擎就不需要再回表查询, 这样避免了大量的单行访问.

说白了, 避免单行查询, 遵循取得数据的原生顺序, 避免额外排序, 尽可能的使用覆盖索引.

InnoDB 对于count(*)操作是非常慢的, 适当情况下的汇总表非常关键.

理解索引的工作原理是非常关键的, 不能盲目的将复合索引中选择性最高的放在第一列, 或者说在where中所有出现的列都应该添加索引

充分的利用explain, 检查: * 查询是否查询了太多行, 太多的单行访问? * 是否做了额外的排序? * 是否使用了临时表? * 是否有太多的回表查询不在索引中的列?

Contents

排序类查询优化

聚簇索引

覆盖索引

第五章总结