读薄《高性能MySql》(三)索引优化

时间：2021-02-13 06:06:13

读薄《高性能MySql》(一)MySql基本知识
读薄《高性能MySql》(二)Schem与数据优化
读薄《高性能MySql》(三)索引优化

#1 基础知识

为了看懂这一篇博文，请先看懂 B+ 树。因为 MySql 中大多数的引擎都是用这个数据结构作为索引的，特别是 InnoDB，因为基本上绝大多数的应用都是这个引擎，所以如果你有 10 份时间，花 9 份时间在这个引擎上也是没错的，本文后面要讨论的内容也大多数是基于这个类型的索引的。

我们要注意这一个特性，就是 B+ 树只有叶子节点才存储数据（也就是在数据库中指向某一行的指针），知道这个特性对于理解后面的内容非常重要。比如说主键要尽量的小，这样可以一次性读入更多的索引来查找数据，

读薄《高性能MySql》(三)索引优化

BTW，这里有一个组成原理的知识需要大家掌握。对于机械硬盘来说，顺序的读取比随机读取快非常多。

机械硬盘长这样：

读薄《高性能MySql》(三)索引优化

读取数据的时候，需要把磁头移动到相应的柱面上然后通过磁盘旋转来读取这一个柱面的数据。

读取时间 = 移动磁头 + 磁盘旋转读取 + 传输时间

期中移动磁头占绝大部分时间，如果顺序的读取的话，就只需要移动一次磁头，速度自然就提上去了。

随机读取，每读一块都要重新移动磁头到相应的位置上去，速度就慢很多。

#2 明确目标

对于数据库查询优化，一个好的索引能让操作快上好几个数量级。索引的优点有

  1. 减少服务器需要扫描的数据量
  2. 帮助服务器避免排序和临时表
  3. 将随机 IO 变为 顺序 IO

一个好的索引应该具备以下三个优点。

  1. 索引将相关记录放在一起
  2. 索引中的顺序和查找顺序（比如 ORDER BY）操作一致
  3. 索引包含了需要查找的全部内容

书上把满足这几条需求的索引称作”三星索引“，在后面三星索引一章会介绍这几条原则。

#3 B+ 树索引

#3.1 如何最大化利用索引

B+ 树索引只能高效的使用最左前缀，我们拿下面的索引举例：

key(姓,名,生日)

只有以下的情况会用到索引：

全值匹配

全值匹配指的是匹配用到全部索引值，比如匹配姓为张，名为三，生日为 1998-11-11 的人，就可以用到索引。

WHERE 姓='张' AND 名='三' AND 生日='1998-11-11'

匹配最左前缀

可以匹配到姓为张的全部人，也就是用到索引的第一列

WHERE 姓='张'

匹配列前缀

比如里面有英文名字，可以匹配到所有姓是以 A 开头的人。

精确匹配到某一列并且范围匹配到另一列

可以使用从索引的左边到右边任意的数量的前缀，比如可以用一个半索引，查找用

WHERE 姓='张' AND like '三%'

在上面的情况除了按值查找能用到索引，ORDER BY 操作也能用到索引。

最左前缀匹配也就是匹配需要从最左边开始，无论 key 取多少位，一位，一位半，两位都行。上面的索引不能用到查找生日为某一天的人，因为没有用到前面的姓和名，也就是没有从最左边开始，直接从第三列开始了。

B-Tree 索引有如下几个限制

只能从索引的最左列开始查找

比如说上面的例子无法查找特定名的人，也不能查找特定生日的人

不能跳过前缀

上面的例子中，不能查找特定的姓并且生日为某一天的人，因为跳过了中间的索引名。

当前面的key用到范围查找后，后面的查找都不能使用索引查找了

比如说，不能查找这样的语句

WHERE 姓 LIKE 'A%' AND ...

在前面使用过范围查找后，后面的数据都不能通过索引找出了。

从上面的限制得出几个比较重要的优化点：

  • 尽量避免多个范围匹配在同一个查询中出现
  • 索引的顺序对于搜索至关重要

#4 聚簇索引

读薄《高性能MySql》(三)索引优化

什么是聚簇索引？对于这棵 B+ 树来说，如果不是采用的聚簇索引（比如在 MyISAM 引擎中），那些 Data 保存的是索引对应的列的指针，也就是说，如果你想要访问列的数据还需要根据指针查找到那一列然后才能获取数据。而对于聚簇索引来说，保存的是列的数据，在读入索引的时候可以直接将这一列的值读入。

聚簇指的是数据和 B+ 树的叶子节点紧凑的存储在一起。因为无法把数据放在两个地方，所以一张表只有一个聚簇索引。一般来说被索引的列是主键，如果没有主键，会隐式的生成一个主键作为索引。

聚簇索引的实现是在引擎层面上的，这里讨论的是 InnoDB，原理对于其他的同样引擎适用。

#4.1 聚簇索引优点

可以把相关数据保存在一起，比如按照用户 id 取邮件，因为邮件都根据 id 保存在一个地方，所以只需要比较少次 I/O就可以把同一个用户的全部邮件读取完。

#4.2 聚簇索引缺点

  • 聚簇数据查询顺序依赖于插入顺序，按照主键顺序插入是最快的，如果不是按照主键顺序插入，在插入后最好用 OPTIMIZE TABLE 重新组织一下表。

  • 更新聚簇索引列的代价很高，因为每个更新的行都要移动到新位置。

  • 需要插入行或者移动行的时候可能会产生碎片，会占用更多空间。

  • 聚簇索引会导致全表扫描变慢，由于页分裂会导致数据存储不连续

#4.3 避免随机插入

最好避免随机的聚簇索引，这种情况下还不如使用一个和业务无关的自增列。特别是对于 I/O 密集操作，因为随机索引插入，这会导致页分裂并且需要移动列的位置。

#5 高性能索引

#5.1 查询中索引不要带表达式

查询条件中，索引不能是表达式的一部分，也不能是函数的参数，MySql 并不会懂得去优化它，比如说下面的选择。

SELECT id from singer where id + 1 = 5

我们很容易看出来这个表达式等价于查找 id 为 4 的歌手，但是 MySql 不懂得自己去优化它，不会使用到索引，而是采用全表搜索。

#5.2 选择合适索引前缀长度

我们首先引入个概念

索引的选择性：通俗地说就是索引对列的区分度，比如说如果每一个列有唯一的 id，那么选择性就是 1，如果有 100 个相同的 id，那么选择性就是 1 / 100，索引的选择性越高性能越好。

有时候索引需要很长的字符串，这些索引会让查询变得很慢，一个解决策略就是前面说到的手动创建哈希索引。另外一种方法就是只使用前面几个字符来作为索引，但是这样会降低索引的选择性。诀窍在于要使用刚刚好长度的字符串前缀作为索引，索引太长会导致一次性读入内存的索引减少，太短会导致区分度不高，B+ 树查找完索引还要线性查找很长一段距离。

为了更直观的解释如何选择合适的长度，我们拿一张表来举个例子

song(id, title)

歌曲表，里面有 id 和 标题，我们的目标是在 title 上创建索引。

为了直观的看出索引的区分度，我们可以用一些 SQL 语句来测试数据，逐渐增加前缀的长度，选择最适合的长度。

SELECT COUNT(*) AS cnt , LEFT(title,3) AS pref FROM song GROUP BY pref ORDER BY cnt DESC LIMIT 10;

选择结果如下

读薄《高性能MySql》(三)索引优化

我们只看数值最大的那个，因为如果第一个数值很大，那么查询这个前缀的时候效率会变得很低，我们可以慢慢增加前缀长度直到数字达到可以接受的数值。

当然也可以计算一个查询的前缀长度的选择性

SELECT COUNT(DISTINCT LEFT(title, 3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(title, 4))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(title, 5))/COUNT(*) AS sel5
FROM song;

读薄《高性能MySql》(三)索引优化

这个值越大查询效率越高，一般来说，可以慢慢增加前缀直到这个数值增加的不明显为止，然后根据具体需求选择出合适的长度。

#5.3 避免多列索引

在上一节的例子中，有人可能会创建这样的索引。

key(名),key(姓),key(生日)

这样运行下面这个查询的时候，可能用不到这个索引。

SELECT * FROM student WHERE 名='宁' AND 姓='宁'

对于这样的查询，这个索引

key(姓,名)

会更合适

在 MySql 5.0 和更新版本中，查询能同时使用两个索引进行扫描，并将结果合并（这就用到了这 key(姓)和 key(名) 这两个索引）。索引合并是一项优化策略，但是更可能说明了索引创建的很糟糕。

当出现了多个 AND 关联的查询，应该采用包含所有类的索引而不是独立的单个索引。

key(姓, 名)

#5.4 选择合适的索引列顺序

从前面讲到的索引限制条件，我们可以知道，一个好的索引顺序能极大的提高索引的利用率。

当不需要排序和分组的时候，将选择性最高的列放在前面通常是很好的选择，这是一种经验法则。但是这和值的分布有关，也和业务有关，没有一个四海皆准的法则。

一个比较有效的方法就是提取出比较差的查询，然后按照选择率来创建索引，将选择性高的列放在索引左边创建索引。

#5.5 索引覆盖

当使用到的索引就是需要的值，那么就能减少磁盘的 IO，查询效率大大提升。

比如说有这个索引 key(sex, age)，当查询性别男，生日为某一天的时候，匹配到索引的时候就直接返回结果就行了 ，不需要再进行磁盘 IO 来拿出这一整列。

而且因为索引是按照列值顺序存储的，所以对于 I/O 密集的范围查询，会比随机从磁盘读取每一行数据的 I/O 少得多。

#5.6 使索引与排序顺序一致

MySql 有两种方式来生成排序，一种是直接利用索引扫描顺序作为排序，一种是通过排序操作。当 EXPLAIN 出来的 type 的值为 index 时说明使用了索引进行排序。

设计索引的时候要尽量使得索引的列顺序和 ORDER BY 顺序一致。

只有当索引的顺序和 ORDER BY 的子句一致，并且所有的列排序方向都一样的时候，MYSQL 才能用索引来对结果进行排序。如果需要不同的方向进行排序，一个好的方法就是在保存的时候翻转字符串或者保存相反数。当需要关联查询多个表的时候，只有 ORDER BY 子句引用的字段全为第一个表的时候，才能用索引做排序。

索引在 Order By 中的使用限制也和 SELECT 中的限制一样

#6 索引和锁

InnoDB 访问行的时候才会锁定这行，索引可以让查询访问更少行，从而锁定更少行。

然而索引是按照顺序自增的时候，并发插入会导致行锁的竞争。

在旧版中，需要提交事务才能释放行锁，在 MySql 5. 1 后的版本，InnoDB 可以在服务端过滤掉行后就释放锁。

#7 清除冗余和重复索引

MySql 允许重复索引的存在，并且需要单独维护重复的索引。有时候我们需要查看未使用到的索引。

可以用 Percona Toolkit 来查看索引情况和查询索引的日志来找出重复和多余的索引。

接下来讨论一下冗余索引，冗余索引一般是增加索引（A,B）的时候，而不是去扩展已有的索引（A）。

大多数情况下都不需要冗余索引。除非当需要额外增加一个非常长的 VARCHAR 索引的时候，增加这个列会导致性能急剧下降，这时候就需要冗余索引。

#8 一些其他的索引讨论

顺带一提，如果有特殊需要，可以使用其他的索引。

R-Tree：可以用任意维度进行索引，MySql 在这方面做得不好，可以考虑使用 PostgreSQL

全文索引：用于搜索，一般可以将整张表导入 elasticsearch 中。

#8.1 哈希索引

在 MySql 中，只有 Memory 引擎支持显式哈希索引，它是支持非唯一哈希索引的。也就是说类似于 Java 中的 HashMap（JDK 1.8 前），散列到同一个位置的列会被保存在链表中。

如果数据库不支持哈希索引，可以在 B-Tree 中创建一个伪哈希索引，比如需要保存大量的 URL，可以在 WHERE 语句中利用特定的方法来把 URL 散列开来，可以借助 MySql 插件来做到这一点。这样的缺点就是需要使用触发器在列表更新的时候更改哈希值。

标签：

相关文章