高性能Mysql-索引

概述

索引在大数量量时对性能尤为重要,创建合适的索引和使用成了其中关键,本文介绍如何创建高性能的索引。

索引基础

类型

mysql中,有不同的索引类型,不同的存储引擎对索引的支持也不同,在不同的场景各有优缺点。

B-Tree索引

B-Tree索引意味着所有的值都是按顺序存储的,每一个叶子节点到根的距离相同。不同的存储引擎实现不一样,InnoDB使用B+Tree。具体数据结构和算法不做细说,希望后面又机会好好研究。B-tree索引适用于全建值,键值范围或者键前缀查找。除了可以用于查询,还可以按排序查找(ORDER BY)。但这种索引类型也有限制:

  • 组合索引只匹配最左前缀
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询,右边所有列无法使用索引。

哈希索引

基于哈希表实现,只支持精确匹配索引所有列的查询才有效。其原理就是对索引列进行hash计算,使用hash值寻找对应的记录指针,最后比对指针对应的数据。如下: 表:

+-------+---------+
| fname | lname   |
+-------+---------+
| henry | thoreau |
| steve | thoreau |
| bob   | thoreau |
+-------+---------+

对fname插件hash索引,加上hash值对应如下:

f('henry`) = 3434
f('steve') = 4323
f('bob')   = 2311

二索引结构如下:

slot value
2311 指向第3行指针
3434 指向第1行指针
4323 指向第2行指针

从它的存储方式和结构,也能快速判断其特性,存储紧凑,查找速度快。但也有其限制:

  • 索引数据不是按照索引值顺序存储,无法排序
  • 不支持部分索引列匹配
  • 只支持等值比较(=,in ,<=>),不支持范围查找,如where price > 100
  • 如果列选择性不高,hash冲突,对链表处理代价大 可以看到,hash索引限制比较多,但是我们可以对特殊场景,在innoDB的B-tree上添加自定义的哈希索引,从而利用它的优势(如:为很长的列添加索引)。方法是新增一个hash字段,可以通过设置触发器,使用crc32()对其进行插件和更新的hash计算。如果数据量大,我们也可以自实现一些hash函数。
+----------------------------------+
| md5('http://www.mysql.com')      |
+----------------------------------+
| 634a39601ce4bd4a5d32fd526d4c630d |
+----------------------------------+
+---------------------------------------------------+
| CONV(RIGHT(md5('http://www.mysql.com'),16),16,10) |
+---------------------------------------------------+
| 6715708524825699085                               |
+---------------------------------------------------+

就算对hash函数处理,还是可能出现冲突,索引查询时,需要在where语句中添加查询列的值。

还有其他索引类型如空间数据索引,全文索引。

优点

  1. 减少服务器扫描数据量
  2. 避免排序和创建临时表
  3. 将随机IO变成有序IO。

索引策略

  • 索引列不能是表达式的一部分,也不能是函数的参数。
  • 索引很长很大的字符串时,会让索引变得大且慢,可以通过截取前n个字符的方式进行索引,即前缀索引。创建索引:ALTER TABLE t ADD KEY (rowName(n)),其中,n成了关键,既要提高选择性,又不能太长。创建时,通过下列计算前缀选择性:
SELECT
  COUNT(DISTINCT LEFT(last_name, 3)) / COUNT(*) AS nam3,
  COUNT(DISTINCT LEFT(last_name, 4)) / COUNT(*) AS nam4,
  COUNT(DISTINCT LEFT(last_name, 5)) / COUNT(*) AS nam5,
  COUNT(DISTINCT LEFT(last_name, 6)) / COUNT(*) AS nam6,
  COUNT(DISTINCT LEFT(last_name, 7)) / COUNT(*) AS nam7
FROM employees;
  • where多条件查询时,使用组合索引比多个单列索引,是更好的选择。
  • 对多列索引,一般把选择性最高的列放在前
  • 顺序重要,需要注意,不能把平均选择性作为唯一指标,特殊情况有可能摧毁整个系统。
  • inoDB使用主键作为作为聚族索引,如果没有,会子选唯一非空索引代替,如果还没有,会自建隐式主键,所以,使用InnoDb时,一定要有主键。
  • 插入数据时按照主键顺序插件性能最好,所以,主键最好有序递增。避免随机的主键插入,比如UUID。
  • B-tree的二级索引的叶子节点包含索引列数据,如果查询直接通过获取所有不需要回表,效率会很高。也即覆盖查询。
  • 索引列最好能既满足排序,又满足查询。
  • 创建索引时,注意冗余和重复索引。对于未使用的索引,考虑删除。

总结

总的来说,编写查询语句尽可能选择合适的索引避免单行查找,尽可能使用数据原生顺序避免额外排序操作,尽可能使用索引覆盖查询。


1.《高性能Mysql》

CONTENTS