1. 索引是怎么发挥作用的

[toc]

Background

索引好不好, 还是看你平时怎么查, 有哪些常用的关键字段, 那些喜欢用等号, 那些喜欢用范围查找. 明白索引的工作方式, 你就能知道怎样避免磁盘IO, 从而降低查询耗时, 一次查询过程分成以下几步:

1. 尝试缓冲池: 这一步是Server层做的, 也就是说这一步都还没到存储引擎层里, 先看看原语句能不能命中, 如果不能, 做一下查询优化看看能不能命中. 通常情况下缓存刷新非常快, 而且数据库一更新这边就刷没了, 所以也别太指望了

2. 尝试Buffer Pool : 从这一步开始我们就已经到引擎层了, 因为磁盘非常慢所以有时候也会把磁盘里的一些东西加载到Buffer Pool里, 尝试一下能不能命中

3. 开始磁盘IO. 读磁盘的过程分成三个步骤: 寻道/旋转/传送, 就是你的索引指向了磁盘上的一块, 然后你要让磁盘转过去才能开始读, 转磁盘这种事非常浪费时间

   1. 如果你尝试 随机读取: 一次随机读取, 上面的三个步骤就要走一下, n次随机读取, 就是走n下

   2. 如果你尝试 顺序读取: 无论你读多少次, 磁盘都只转一下, 然后顺着这个位置往下读就行了, 因为是顺序的, 所以后面不会再转了, 因此哪怕你读n次, 磁盘都只转一次

主键/聚簇索引

思考一个问题, 同样都是索引, 主键搞出来的索引, 跟普通索引有区别吗? 那个快? 答案是主键搞出来的聚簇索引更快, 原因思考一下B+树的结构 => [所有实际的数据都存放在叶子节点所构成的数组上, 我们的数据就是这么存的], 我们之所以叫它 聚簇/Clustered , 也正是因为他们按照主键的顺序聚在一起.

• 在主键/聚簇的情况下: 在B+这棵N叉树上, 我们在加载最后一个页节点的时候就直接把实际数据给加载出来了.

• 在非主键(非聚簇/二级)的索引下, 我们会生成一个冗余数据结构, key是你自定义的单键/复合键, val不是实际数据, 而是主键. 然后我们通过主键再查到实际值, 等于又回到主键的情况下重查, 这个过程我们叫回表

以上这两种表现能给我们一些启示:

• 为什么主键最好用自增ID: 你也看到了聚簇索引的页节点上存的是连续数组, 因此自增ID, 能让你每次插入新数据的时候直接往后面追加一行即可, 如果是随意ID, 或者是字符串当主键, 那么一个新的值在排序的情况下可能就是在数组中间插入了

• 为什么任何键都最好别用复杂的结构: 主要是排序不方便

• 二级索引并非一定要回表: 假设我们今天令(name, age)成为一个二级索引, 查询内容就是 [ select age where name=liangxiaohan], 那你直接根据叶节点的值就可以找到, 你都不用定位出主键的值, 这种情况我们叫它覆盖索引

左起匹配

在上面的覆盖索引里, 索引明明是 (name, age) 这样的pair, 但实际上我们之给了name=liangxiaohan, 二元条件我们只给了其中一元, 这样也能算用上索引了吗?

超过一元的索引我们都叫它复合索引(我天数据库里可真喜欢起一大堆alias), 复合索引是怎么存的? 在下面这张图里, 我们给出了 (location, age, name) 这样的三元组复合索引, 存放方式是: 先给第一个键排序, 然后第一个相同的情况下比较第二个, 在这种情况下:

我们判断一个语句能不能走这个复合索引要:

• 判断复合索引的第一个键, 在不在where条件中, 这样我们能走第一层排序, 比如上图中的 [where location = anhui] 这样的查询条件

• 判断复合索引的第二个键, 在不在where条件中, 这样我们能走第二层排序, 比如像是 [where location = anhui and age =20]

• 在这种结构下如果你的筛选条件只有where age=20, 很容易想象出这个复合索引你是用不上的

我们从复合索引的第一个键, 也就是最左边的第一个键开始. 看where中是否有对应的条件, 然后到第二个键, 看看where中是否有对应的条件, 这种过程我们叫它左起匹配. 到什么时候算是匹配结束呢?

• 你的where条件里没有这个key了

• 你的where条件是like, 小于五这样的范围查询

• 你的where条件是age+10=20这样的计算公式

[练习] 复合索引的列选择性

如果我们现在避免不了必须使用 WHERE col1 < val1 AND col2 < val2的模式, 怎么安排才能尽可能的达到最大效率? 既然这两者都是范围查询, 那么复合索引显然就只能用上其中一个

CREATE INDEX index_name ON table_name -> 
                   (col1, col2)
                   (col2, col1)

这个问题的答案取决于col1/2那个更有选择性, 显然如果我们选择了其中一个, 另一个就只能顺序的往后遍历了, 那么我们就把 更有选择性的列 放在前面

举例说明: 如果col1 < val1 能过滤掉90%的数据, 而col2 < val2 只能过滤掉20%的数据, 那么我们把col1放在前面, 去高效的过滤掉90%, 然后自己只用遍历剩下来的10%就好了, 下面我们做个小实验:

mysql> select * from compound_index_test limit 1;
+------+----------+----------+
| id   | integer1 | integer2 |
+------+----------+----------+
| 2829 |       55 |        3 |
+------+----------+----------+

以上, integer1是0~100的随机数, 而integer2是0~10的随机数, 那么对于integer1 < 50 and integer2 < 5 这样的条件, 显然是把integer1放在前面会更有选择性:

// 如果把更有选择性的, 放在前面
           id: 1
  select_type: SIMPLE
        table: compound_index_test
possible_keys: more_selective
          key: more_selective
         rows: 1116

// 如果把没什么选择性的, 放在前面
           id: 1
  select_type: SIMPLE
        table: compound_index_test
possible_keys: less_selective
          key: less_selective
         rows: 2159