高性能MySQL-索引

创建索引-高效索引

1.1 索引初体验

1.1.1 介绍

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引的作用是做数据的快速检索，而快速检索的实现的本质是数据结构。通过不同数据结构的选择，实现各种数据快速检索。在数据库中，高效的查找算法是非常重要的，因为数据库中存储了大量数据，一个高效的索引能节省巨大的时间。

1.1.2 索引类型

B-Tree索引

对于MyISAM引擎，其采用了非聚集索引的方式来实现

对于InnoDB引擎，其使用聚集索引的方式来建表

注意：InnoDB只在主键索引树的叶子节点存储了具体数据，但是其他索引树却不存具体数据，而要先找到主键，再在主键索引树找到对应的数据。 别问，问就是节省空间，拿时间换空间。这也证明了选择合适的主键的重要性。

B+Tree索引查询类型：

• 全值匹配

  ​和索引中的所有列进行匹配,例如key(A,B,C),匹配(A,B,C)

• 匹配最左前缀

  ​例如key(A,B,C),可以匹配(A) (A,B) (A,B,C)

• 匹配列前缀

  ​例如数据changtong，加上索引后可以匹配(c*),这也就是为什么我们对某一添加了索引的列使用模糊查询时like a% 就会使用索引，如果是%a就只能够完全检索了

• 匹配范围值

  ​B-tree索引是顺序存储数据的，因此能够使用索引进行范围匹配

• 精确匹配某一列并范围匹配另外一列

  ​例如key(A,B)可以匹配(A,B*)

• 只访问索引的查询

  ​这里主要是覆盖索引，只访问索引，不访问数据行

Hash索引

InnoDB一般不使用Hash索引，但是其有一项功能叫“自适应哈希索引”，当它发现某些数据访问非常频繁，可能会基于B-Tree的基础上再创建一个Hash索引，该过程自发且不可控，可以选择关闭。

综上所述，对于使用InnoDB引擎的我们来说，Hash索引了解一下就好了，不过这也给我们提供了一个思路，例如我们存储网络链接这样的无序长字符串，是不是可以使用上Hash算法？我们可以新建一列url_hash，存放该链接对应的Hash值，然后我们对该Hash值建立索引，就能得到更好的查询体验了，在查询时使用以下查询语句即可

select id from url where url="http://changtong1819.top"
and url_hash = CR32("http://changtong1819.top")

当然，缺点是我们可能需要使用触发器等工具维护我们的Hash值

空间索引

全文索引

1.2 索引的优点

最常见的B-Tree索引，由于其按照顺序存储数据，所以 MySQL可以用来做ORDER BY和GROUPBY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。

最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下三个优点:

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

注意：索引并非总是好的解决方案

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如可以使用分区技术。

1.3 高性能索引

1.3.1 独立的列

例如：

select id from user where id + 1 = 10

尽管上面的写法看着很智障，但貌似我以前好像写过这样的查询语句，靠！总而言之，能在业务层简化就尽量简化，直接写id = 9就会使用索引了。

1.3.2 前缀索引和索引选择性

我们如果存储了一列全是类似"changtong1819hahahahahahahahhhehehehehhehehexixixiixixii "这样的长数据列的话，我们可以增加一列，这一列只保存上面数据列中的前10个字符，对该列添加索引，在查询时匹配这两列能够极大的提高查询速度。

那么问题来了，具体截取多少个字符呢？什么是索引选择性?
对于我们上面的例子来说，截取太长，匹配效果好，但是既然这个索引还是长，那我要它干嘛？截取太短的话，就会导致我们该索引的相同值太多了，我们知道B-Tree好就好在其实顺序存储的，当相同值越多，就导致索引效果会越差，即选择性差。因此，我们要权衡两种效果来选择合适的长度，这肯定是和实际数据库的存放数据相关了。

1.3.3 多列索引

MySQL引入了索引合并策略，在一定程度上可以帮助我们通过多个单列索引来定位到指定的数据，当然，尽管有该策略，我们应该也能想到其效率也不会有多高。这是MySQL对我们的查询操作进行的优化，但是我们要尽量创建合适的索引。

1.3.4 索引列顺序

通常情况下我们将选择性最高的列放在索引的最前列，这有利于我们的where语句。但考虑到其他情况的话，这样做就可能不是最好的选择了。我们使用where语句时，要充分考虑到联合索引的执行与否。

1.3.5 聚簇索引

我们平时手动创建的索引称为二级索引，而通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值（二级索引没有行数据），然后根据这个值去聚簇索引中查找到对应的行。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

在InnoDB表中按主键顺序插入行

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键(surrogate key)作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于IО密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

1.3.6 覆盖索引

MySQL不能在索引中执行LIKE操作。这是底层存储引擎API的限制，MySQL 5.5和更早的版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于)。

MySQL 能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE查询，存储引擎就无法做比较匹配。这种情况下，MySQL服务器只能提取数据行的值而不是索引值来做比较。

1.3.7 使用索引扫描

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

注意：只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求。

1.3.8 压缩索引

1.3.9 冗余和重复索引

通常情况下，我们不会写出两个KEY(A)这样的索引。但是像主键、外键MySQL会自动创建索引，我们再去添加索引就会导致重复了。至于冗余索引，我们创建了KEY(A,B)之后在创建KEY(A)就很明显冗余了。

1.3.10 未使用的索引

1.3.11 索引和锁

MySQL 5.1之后，InnoDB可以在服务端过滤掉行之后就释放锁，此前版本需要事务提交后才释放锁

如果不能使用索引查找和锁定行的话，MySQL会做全表扫描并锁住所有的行，而不管是不是需要。

InnoDB在二级索引上使用共享(读）锁，但访问主键索引需要排他（写）锁。

1.4 合理使用索引

1.4.1 支持多种过滤条件

创建索引时我们需要考虑哪些列拥有很多不同的取值，哪些列在WHERE子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的，因为可以让MySQL更有效地过滤掉不需要的行。

像性别这样的选择性就两个的字段，单独添加个索引毫无必要，索引的最主要目的就是快速查找、定位数据。而性别这样的数据在表中的定位性比较差，即便加上索引，优化器也是会认为此索引使用的成本过高，而不会使用索引。

但并不是性别就一定不能成为索引字段，如果该表经常出现性别和其他字段往往同时出现在where后面，那么你可以将性别和其他字段作为联合索引。即能够帮助我们多过滤一些数据行还是好的。

1.4.2 避免多范围条件

前面我们提到了，InnoDB使用B+Tree作为索引存储结构，而这是一种顺序存储结构，因此InnoDB是支持索引的范围匹配的，但是这并不意味着我们可以随意在where后面添加多个范围匹配。

不过我尝试了几种多范围条件的查询，发现都使用了索引

1.4.3 优化排序

对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建(sex,x)索引用于对x字段和性别字段的查询。