读书笔记二之索引策略

索引策略

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

独立的列

避免查询不当地使用索引，或者使得 MySQL无法使用已有的索引

如果查询中的列不是 独立的(索引列不能是表达式的一部分，也不能是函数的参数)，那么 MySQL就不会去使用索引

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢：一个策略是使用哈希索引，但有时这还不够，还可以做些什么呢？

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

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

因此，选择一个合适的索引长度也非常重要：为了决定列前缀的合适长度，需要 找到最常见的值的列表，然后和最常见的前缀列表进行比较(增加前缀长度，直到这个前缀的选择性接近完整列的选择性.)：

多列索引

很多人对多列索引的理解都不够，一个常见的错误就是：为每个列创建独立的索引，或者按照错误的顺序创建多列索引.

在多个列上建立单独的索引大部分情况下并不能提高 MySQL的性能.

MySQL 5.0中引入了一种叫 "索引合并"的概念，一定程度上可以使用表上的多个单列索引来定位指定的行

MySQL会使用这类技术来优化复杂查询，所以在某些语句的 Extra列中还可以看到嵌套操作

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

• 
• 如果有在 EXPLAIN中看到索引的合并，应该好好检查一下查询和表的结构，看一看是不是最优的
• 也可以通过参数 optimizer_switch来关闭索引合并功能，也可以使用 IGNORE INDEX提示让优化器忽略掉某些索引

合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要

在一个多列 B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所有，索引可以按照升序或者降序进行排序，以来满足精确符合列顺序的 ORDER BY、GROUP BY和 DISTINCT等子句的查询需求.

因此，多列索引的顺序至关重要，其决定了一个索引是否能够成为一个真正的 "三星索引".

• 经验法则：将选择性最高的列放到索引最前列
• 经验法则通常不如随机 I/O和排序重要，考虑的问题需要更加全面一些

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

聚簇索引

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

将数据和索引放到了一块，找到索引也就找到了数据.

InnoDB的聚簇索引实际上在 同一个结构 中保存了 B-Tree索引和数据行

并不是所有的存储引擎都支持聚簇索引

聚簇索引具有唯一性，一张表只有一个聚簇索引

InnoDB 通过主键来聚集数据

• 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替；
• 如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引
• InnoDB只聚集在 同一个页面中的记录
• 包含相邻键值的页面可能会相距甚远

聚簇索引的优点：

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

聚簇索引的缺点：

• 聚簇数据最大限度的提高了 I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了

• 插入速度严重依赖于插入顺序：按照主键的顺序插入是加载数据到 InnoDB表中速度最快的方式，如果不是按照主键的顺序来加载数据，那么在加载完成之后最好使用 OPTIMIZE TABLE命令来重新组织下表

• 更新聚簇索引的列代价很高：会强制 InnnoDB将被更新的行移动到新的位置

• 基于聚簇索引的表 在插入新行，或者主键被更新导致需要移动行时，可能面临 "页分裂(page split)"的问题：当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂的动作，页分裂会导致表占用更多的存储空间

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

• 二级索引(非聚簇索引)可能比想象的要更大，二级索引的叶子节点包含了引用行的主键列

• 二级索引访问需要两次索引查找，而不是一次(二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值)

  

InnoDB和 MyISAM的数据分布对比

InnoDB是聚簇索引，使用 B+Tree作为索引结构，数据文件和(主键)索引绑在一起的，必须要有主键，通过主键索引效率高。但辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据

MyISAM是非聚簇索引，也是使用 B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是指向数据文件的指针

即：InnoDB的 B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而 MyISAM的主键索引和辅助索引都是数据文件的地址指针.

MyISAM:

InnoDB:

在 InnoDB中，聚簇索引就是 "表",所以不需要像 MyISAM那样需要独立的行进行存储

聚簇索引的每一个叶子节点都包含了主键值、事务 ID、MVCC的回滚指针以及所有的剩余列；如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列

聚簇索引和非聚簇索引对比图：

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

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

最好避免随机的(不连续且值的分布范围非常大)的聚簇索引，特别是对于 I/O密集型的应用

• 从性能的角度进行考虑，使用 UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，这样会使得数据没有任何聚集特性

向聚簇索引插入顺序的索引值

• 因为主键的值是顺序的，InnoDB会把每一条记录存储在上一条记录的后面
• 当达到页的最大填充因子时(InnoDB默认的最大填充因子是页的 15/16，留出部分空间用于以后修改)，下一条记录就会写入新的叶中
• 一旦数据按照这种方式加载，主键页就会近似于被顺序的记录填满，这也是所期待的结果(然而，二级索引页可能是不一样的)

向聚簇索引中插入无序的值

因为新行的主键值不一定比之前插入的大，所以 InnoDB无法简单地总是把新行插入到索引的最后，常需要 为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间

这会 增加很多额外工作，并且导致数据分布的不够优化

覆盖索引

设计优秀的索引应该要考虑整个查询，而不仅仅是 Where条件部分

MySQL同样可以使用普通索引来直接获取列的数据，这样便可以不用再通过回表的方式去读取数据行

如果一个索引包含了所有需要查询字段的值，称为 索引覆盖

在实际查询中应尽量去 避免回表查询

使用覆盖索引的好处：

• 索引条目通常远小于数据行大小，如果只需要读取索引，MySQL会 极大地减少数据的访问量
  • 这对 缓存的负载非常重要，因为这种情况下响应时间大部分都花费在了数据的拷贝上
  • 覆盖索引对 IO密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中(这对于 MyISAM尤其正确，因为 MyISAM能压缩索引变得更小)
• 索引是按照列值的顺序存储的(至少单个页内是如此)，所以对于 IO密集型的范围查询会比随机从磁盘读取每一行数据的 IO要少得多(从避免回表上来看，这也是减少了磁盘 IO的次数)
• 一些存储引擎如 MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据时需要一次系统调用，这会造成严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景.
• 由于 InnoDB的聚簇索引，覆盖索引对 InnoDB表特别有用：InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖索引，这边能够避免对主键索引的二次查询

在索引中满足查询的成本一般比查询行要小得多

不同的存储引擎实现覆盖索引的方式不同，且并不是所有的存储引擎都支持覆盖索引(Memory存储引擎就不支持覆盖索引)

当发起一个被索引覆盖的查询时，在 EXPLAIN的 EXTRA列可以看到 "Using index"的信息，如果没有匹配到索引，实际上看到的便会是 "Using Where"

MySQL的查询优化器会在执行查询前判断一个索引能否进行覆盖

覆盖索引使用时的一些限制

MySQL不能在索引中执行 LIKE操作，这是底层存储引擎 API的限制

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

未来 MySQL的优化 --> 索引下推的由来

使用索引扫描来做排序

p175

MySQL有两种方式可以生成有序的结果：① 通过排序操作 Using filesort(文件排序：MySQL中无法利用索引来完成的排序操作 --> MySQL会对数据使用一个外部的索引来进行排序，而不是按照表内的索引顺序进行读取) ② 按索引顺序扫描

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录；但是如果索引不能覆盖到查询的所有列时，那就不得不没扫毛一条索引记录就去回表查询一次对应的行，而这基本上是随机的 IO，因此 按索引顺序读取数据的速度通常比顺序地全表扫描慢，尤其是 IO密集型的工作负载时

MySQL可以使用同一个索引既满足排序，又用于查找行

只有当 索引的顺序和 ORDER BY子句的顺序完全一致，并且所有列的排序方向(倒序或正序)都一样时，MySQL才可以使用索引来对结果进行排序

• 如果查询需要关联多张表，则只有当 ORDER BY字句引用的字段全部为第一个表时，才能使用索引来进行排序
• ORDER BY字句和查找型查询的限制是一样的：需要去满足索引的最左前缀的要求，否则，MySQL就会去进行文件排序(Using filesort)，而无法利用索引排序
  • 一种情况例外，就是 索引的前导列为常量时，如果 Where字句或者 JOIN字句中对这些列指定了常量，就可以弥补索引的不足

压缩索引

前缀压缩

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能

压缩块能使用更少的空间，代价是某些操作可能变慢

• 每个值的压缩前缀都依赖于前面的值，所以 MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描，正序扫描速度还不错，倒序扫描就不是很好了 --> 在索引块中查找某一行的平均操作都需要去扫描半个索引块
• 对于 CPU密集型应用，因为扫描需要随机查找，压缩索引使得 MyISAM在索引查找上要慢好几倍

冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的，MySQL需要单独维护重复的索引，并且优化器在优化时也需要逐个进行考虑，这会影响性能

重复索引：指在相同的列上按照相同的顺序创建的相同类型的索引；应该避免重复索引的创建，发现以后也应该立即移除.

区分冗余索引和重复索引

• 冗余索引通常发生在添加新索引时

在大多数情况下都不需要冗余索引，应该尽量去 扩展已有的索引而不是创建新索引；有时候处于性能考虑会去考虑创建新索引：扩展已有的索引会使其变得很大，从而影响使用该索引来查询的性能.

索引和锁

索引可以让查询锁定更少的行，如果你的查询从不去访问那些不需要的行，那么就会锁定更少的行

虽然 InnoDB的行锁效率高，内存使用少，但是锁定行时仍然会带来而外开销

其次，锁定 超过需要的行会增加锁争用并减少并发性

InnoDB只有在访问行时才会对其加锁，而索引能减少 InnoDB访问的行数，从而减少锁数量

• InnoDB在 二级索引上使用共享锁，但 访问主键索引时需要 排它锁

vv