第五章:创建高性能索引(上)

索引是存储引擎用于快速找到记录的一种数据结构,这也是索引的基本功能.在MySQL中也叫"键key".良好的性能少不了索引.换句话说,索引优化能够将查询性能轻松提高几个数量级.

1. 索引基础

select first_name from actor where actor_id = 5;

运行上面的查询:如果在actor_id列上有索引,MySQL将使用该索引去查找actor_id为5的列,也就是说:MySQL先在索引上按值查找,然后返回包含该值的数据行.

• 索引可以包含一个或多个列,列的顺序很重要,MySQL只能高效的使用索引的最左前缀列

1.1 索引的类型

MySQL的索引由存储层实现,不同的存储引擎实现方式不同

MySQL支持的索引:

• B-Tree索引
  • B-Tree索引使用B-Tree数据结构来存储数据
  • InnoDB使用的B+Tree实现的索引
  • 存储引擎通过从索引的根节点开始搜索而不再是全表扫描以此提高了数据访问的速度
  • B-Tree对索引列是顺序组织存储的,所以适合查找范围数据
  • 可以使用B-Tree索引的查询类型:
    • 全值匹配:和索引中所有的列进行匹配
    • 匹配最左前缀:匹配索引的第一列
    • 匹配列前缀:匹配某一列值的开头部分
    • 匹配范围值
    • 精确匹配某一列并且范围匹配某一列:第一列全匹配,第二列范围匹配
    • 只访问索引列
  • B-Tree可以用于order by操作
  • InnoDB有个自适应哈希索引,可以在B-Tree的基础上建立一个哈希索引
  • B-Tree的限制:
    • 只能适用于从索引的最左列开始的查找
    • 不能跳过索引的列,比如索引是三列,只使用第一三列是无法查找的
    • 如果查询中有某个列的范围查询,则其右边所有的列都无法使用索引优化查找
• 哈希索引:基于哈希表实现,只有精确匹配所有索引列的查询才有效.
  • 在某些比较适合的场景下哈希索引性能提升较为明显
  • 哈希索引将所有哈希码存储在索引中,同时哈希表中保存指向数据行的指针.
  • 如果多个列的哈希值相同,索引会以链表的方式保存多个记录到同一个哈希条目中去
  • 只需保存对应的哈希值,节省空间,查找快
  • 哈希限制
    • 不能使用索引的值来避免读取行(对性能影响较小)
    • 不按照值的顺序索引,无法使用排序
    • 不支持部分索引列匹配
    • 只支持等值比较,无法范围查找
    • 哈希冲突过多造成索引维护成本增加
  • 自定义哈希索引:在B-Tree基础上建立伪哈希,查找时使用键的哈希值索引查找,仅需在where子句后使用自定义哈希函数
• 空间数据索引
• 全文索引
• 其他

2. 索引的优点

• 减少了服务器需要扫描的数据量
• 避免排序和临时表
• 将随机I/O变为顺序I/O

只有索引帮助存储引擎快速查找到记录的好处大于维护索引付出的成本时,索引才是有价值的

3. 高性能索引策略

3.1 独立的列

"独立的列":索引的列不能时表达式的一部分,也不能是函数的参数.

3.2 前缀索引和索引选择性

• 索引太长时可以只索引开始的部分字符,这样可以节省空间,提高效率. 但会降低索引的选择性.

• 索引选择性:不重复的索引值和数据表的总记录数T的比值:从1/T到1之间

  • 索引选择性越高,查询效率越高,选择性越高MySQL九年过滤掉更多的行.
  • 一般来说某个列的前缀选择性也是足够高的

3.3 多列索引

在多个列上建立独立的单列索引大部分情况下不能提升MySQL的查询性能.

索引合并策略有时候是一种优化的结果,当更多时候说明了表上的索引建的和糟糕:

• 当服务器出现多个索引做相交操作时(多个and条件), 这意味着需要一个包含所有相关列的多列索引
• 当服务器出现多个索引做联合操作时(多个or条件),通常需要耗费大量的CPU和内存在算法的缓存还需合并上.

如果在explain中看到有索引合并,应该检查一下查询和表结构,确定的hi否已经是最优的,或者在某些时候可以关闭或者忽略索引.

3.4 选择合适的索引列顺序

• 正确的顺序依赖于使用该索引的查询,并同时需要考虑如何满足排序和分组的需要,索引列的顺序总是从左到右一次查找.

• 在不考虑排序和分组时将选择性最高的放在最前列通常时比较好的做法,不过仅局限在where查询.

• 但性能不只是依赖于索引列的选择性,也和查询条件的具体值有关,也就是值的分布.