Menu

Subscribe

高性能Mysql-索引

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