MySQL学习笔记2（创建高性能的索引）

创建高性能的索引

索引基础

索引：是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能

索引对于良好的性能非常关键，当表中数据量越来越大的时候，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能不明显

索引优化重要的原因：索引经常被忽略，有时候甚至被误解，所以在实际案例中经常会遇到又糟糕索引导致的问题。

要理解索引是如何工作的，最简单的方法是去看看一本书的索引部分，如果想要在一本书中找到某个特定主题，一般会先去看书的索引，然后找到对应的页码

索引的类型

索引是存储在引擎层而不是服务器层实现的，所以没有统一的索引标准：不同存储引擎的索引的工作方式是不同的。

B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，那么多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。大多数MySQL引擎都支持这种索引。

我们使用术语“B-Tree”，是因为MySQL在CREATE TABLE 和其他语句中也使用该关键字。不过底层的存储引擎也可能使用不同的存储结构，例如：NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引，即使其名字是BTREE

InnoDB使用的是B+Tree

MySIAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。

MySIAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取重要的数据，取而代之的是从索引的根节点开始进行搜索的。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

索引对多个值进行排序的依据是CREATE TABLE 语句中定义索引实列的顺序。

可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围、或键前缀查找。其中键前缀查只适用于根据左前缀的查找。

B-Tree索引对如下类型的查询有效：

1. 全值匹配
2. 匹配最左前缀
3. 匹配范围值
4. 精准匹配某一列并范围匹配另外一列
5. 值访问索引的查询

B-Tree索引的限制：

• 如果不是按照索引的最左列开始寻找，则无法使用索引
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引查找优化

所以索引列的顺序很重要，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求

哈希索引

哈希索引基于哈希表实现，只有精准匹配所有列的查询才有效

哈希索引将所有的哈希码储在索引中，同时在哈希表中保存指向每个数据行的指针

只有Memory引擎显式支持哈希索引，且支持非唯一哈希索引

哈希索引查找步骤：

1. 先计算查找数据行得哈希值
2. 使用该值寻找对应得记录指针
3. 比较该行得值是否为对应要查找的数据，确保就是要查找的行

哈希索引查找快的原因：

• 自身只需要存储对应的哈希值，所以索引的结构十分紧凑

哈希索引的限制：

1. 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行
2. 哈希索引数据不是按照索引值顺序存储的，所以无法排序
3. 哈希索引不能利用部分索引键查询，因为哈希索引始终是使用索引列的全部内容计算哈希值
4. 哈希索引仅仅能够满足“=”，“IN”和“<=>”查询，不能使用范围查询
5. 当哈希冲突过多的时候，性能不一定会比B-Tree效率高，因为当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行（Hash索引在任何时候都不能避免表扫描）
6. 如果哈希冲突很多，索引维护操作代价也会很高

索引的优点

索引可以让服务器快速地定位到表的指定位置

B-Tree索引按照顺序存储结构，所以MySQL可以用来做order by和group by操作，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询

三大优点：

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

高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础

独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数

例如，下面这个查询无法使用actor_id列的索引：

select actor_id from sakila.actor where actor_id + 1 = 5;

其中的actor_id很容易看出是4，但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较引号的一侧、

下面是另一个常见的错误

select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10

前缀索引和索引选择性

有时候需要索引很长的字符串，这会让索引变得大且慢，一个策略是哈希索引，但还是不够的，那么还有什么办法呢？

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性

索引的选择性：

• 索引的选择性是指：不重复的索引值（也称为基数）和数据表的记录总数的比值
• 索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

方法：

一般情况下，某个列前缀的选择性也是足够高的，足以满足查询性能，但是要选择足够长的前缀以保证较高的选择性，又不能太长（以便节约空间）

1. 为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较
2. 还有另一个方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性，但是只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性，如果数据分布很不均匀，可能会有陷阱

前缀索引是一种能使索引更小、更快的有效方法，但另一方面也有其缺点：MySQL无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描

一个常见的场景就是针对很长的十六进制唯一ID使用前缀索引，有计算哈希值储存，取前缀等方法储存这类ID信息，但如果使用的是打包过的解决方案，因而无法修改存储结构，那该怎么办？例如使用vBulletin或者其它基于MySQL的应用在存储网站的会话时，需要在一个很长的十六进制字符串上创建索引。此时如果采用长度为8的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。

多列索引

一个常见的错误是，为每一个列创建独立的索引，或者按照错误的顺序创建多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0引入了一种叫“索引合并”的策略，一定程度上可以使用表中的多个单列索引来定位指定的行

索引合并

在MySQL5.0和更新的版本中，查询能够同时使用两个单列索引进行扫描，并将结果合并而避免了全表扫描（5.0之前版本）。这种算法有三个变种：or条件的联合（union），and条件的相交，组合前两种情况的联合及相交。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：

• 当出现服务器对多个索引做相交操作的时候（通常有多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
• 当服务器需要对多个索引做联合操作的时候（通常有多个or条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据的时候。
• 更重要的是，优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取，这样会使查询的成本被低估，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性

选择合适的索引列顺序

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列排序，其次是第二列，等等。所以索引可以按照升序或者降序进行扫描

经验法则

• 将选择性最高的列放到索引最前列

当查询某些特殊用户的时候，例如未在线的用户，可能会赋予它们相同的记录，这样会使索引不起作用，这时候这个案例的解决办法只能是修改应用程序代码，区分这类特殊的用户和组，禁止针对这类用户和组执行这个查询

所以经验法则和推论在多数情况下有用，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。

叶子页包含了行的全部数据，但是节点页只包含了索引列

InnoDB选择聚簇索引的方式

1. InnoDB将通过主键收集数据
2. 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替
3. 如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引

InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远

聚簇索引可能对性能有帮助，但也可能导致严重的性能问题

聚集的数据有一些重要的优点：

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

聚簇索引的缺点：

1. 如果数据全部放在内存中，则访问的顺序就没那么必要了

2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式

3. 更新聚簇索引列的代价很高

4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题

5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

6. 二级索引（非聚簇索引）可能比想象得要更大，因为在二级索引得叶子节点包含了引用行的主键列

7. 二级索引访问需要两次索引查找，而不是一次

为什么二级索引需要两次索引查找

• 答案在于二级索引保存的是主键的值，而不是行的物理位置，所以还要通过主键访问聚簇索引获得数据

InnoDB和MyISAM的数据分布对比

建立下表

MyISAM数据分布

MyISAM按照数据插入的顺序存储在磁盘上

col2列上的索引和其他索引没有什么区别

MyISAM中主键索引和其他索引在结构上没有什么不同。主键就是一个名为primary的唯一非空索引

InnoDB数据分布

改图其实显示了整个表，不仅仅只有索引，因为在InnoDB中，聚簇索引“就是”表，所以不像MyISAM那样需要独立的行存储

聚簇索引的每一个叶子节点都包含了主键值、事务ID，用于事务和MVCC的回滚指针以及所有的剩余列

还有一点和MyISAM不同的是，InnoDB的二级索引的叶子节点中存储的不是行指针，而是主键值，并以此作为指向行的”指针“

这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值做指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引的这个指针

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

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

最好避免随机的聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性

UUID主键插入行不仅花费的时间更长，而且索引占用的空间更大，这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的

主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改），一旦按照这种方式加载，主键页就会近似于被顺序的填满，这是我们所期望的结果

这是使用了UUID聚簇索引插入数据

因为新插入的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置并且分配空间，会导致增加很多额外的工作，并导致数据分布不够优化

缺点：

1. 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入前不得不先找到并从磁盘在读取目标页在内存中，这将导致大量的读写I/O
2. 因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页
3. 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片

这个案例可以看出，使用InnoDB时应该尽可能地按照主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行