mysql之索引优化

Author Avatar
Sean Yu 12月 02, 2020
  • 在其它设备中阅读本文章

索引的优点

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

B+ 树索引

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 ...

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

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

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

hash索引

就是哈希表，只包含哈希值和行指针，而不存储字段值。

优点是快，缺点是只能匹配精确等值查找，范围查找和排序都用不上。同时哈希冲突很多也不适用。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”，当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于BTree索引之上再创建一个哈希索引，这样就让BTree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置。

可以模拟像InnoDB一样创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。思路很简单：在BTree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用BTree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。

下面是一个实例，例如需要存储大量的URL，并需要根据URL进行搜索查找。如果使用BTree来存储URL，存储的内容就会很大，因为URL本身都很长。正常情况下会有如下查询：

mysql> SELECT id FROM url WHERE url="http://www.mysql.com";

若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

mysql> SELECT id FROM url WHERE url="http://www.mysql.com" AND url_crc=CRC32("http://www.mysql.com");

这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。即使有多个记录有相同的索引值，查找仍然很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回对应的行。这样实现的缺陷是需要维护哈希值。可以手动维护，也可以使用触发器实现。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。在相同的列上同时创建全文索引和基于值的BTree索引不会有冲突，全文索引适用于MATCHAGAINST操作，而不是普通的WHERE条件操作。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

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

image.png

每个InnoDB表具有一个特殊的索引称为聚簇索引（也叫聚集索引，聚类索引，簇集索引）。如果表上定义有主键，该主键索引就是聚簇索引。如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

聚簇索引优点：

  • 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
  • 数据访问更快。聚簇索引将索引和数据保存在同一个B+Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引缺点：

  • 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZETABLE命令重新组织一下表。
  • 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂（pagesplit）”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
  • 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B+Tree查找而不是一次。这个过程被称为回表。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

避免聚簇索引随机插入

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

高性能索引

查询中索引不要带表达式

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

SELECT id from singer where id + 1 = 5

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

选择合适索引前缀长度

我们首先引入个概念

索引的选择性：通俗地说就是索引对列的区分度，比如说如果每一个列有唯一的 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;

选择结果如下

image.png

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

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

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;

image.png

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

避免多列索引

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

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

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

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

【为啥？因为mysql发现key(名),key(姓)都是二级索引，都要回表，所以就干脆扫全表了】

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

key(姓,名)

会更合适

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

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

key(姓, 名)

选择合适的索引列顺序

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

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

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

索引覆盖

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

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

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

也就是说，尽量只在select子句里查找建立过索引的列，这样可以避免回表。

使索引与排序顺序一致

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

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

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

当需要关联查询多个表的时候，只有 ORDER BY 子句引用的字段全为第一个表的时候，才能用索引做排序。

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

索引和锁

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

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

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

清除冗余和重复索引

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

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

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

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

三星索引

  • 第一颗星需要取出所有等值谓词中的列，作为索引开头的最开始的列（任意顺序）；
  • 第二颗星需要将 ORDER BY 列加入索引中；

  • 第三颗星需要将查询语句剩余的列全部加入到索引中；

  • 第一颗星不只是将等值谓词的列加入索引，它的作用是减少索引片的大小以减少需要扫描的数据行；

  • 第二颗星用于避免排序，减少磁盘 IO 和内存的使用；
  • 第三颗星用于避免每一个索引对应的数据行都需要进行一次随机 IO 从聚集索引中读取剩余的数据；

当一个 SQL 查询中同时拥有范围谓词和 ORDER BY 时，无论如何我们都是没有办法获得一个三星索引的，我们能够做的就是在这两者之间做出选择，是牺牲第一颗星还是第二颗星。实际上大多数时候，我们更偏爱第一颗星。我们希望减少需要扫描的数据行。

过滤因子

非唯一索引要考虑过滤因子，过滤因子大的过滤条件通常放在最前面。