【高性能MySQL】读书杂记：创建高性能的索引

内容全部来自《高性能MySQL》，纯属为个人学习习惯，方便复习

---

---

MySQL中，索引是存储在引擎层而不是服务器层实现的。

B-Tree索引，它使用B-Tree数据结构来存储数据。
而MySQL的B-Tree索引虽然名字是BTree，但是InnoDB使用的是B+Tree数据结构实现的。

索引的优点

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

索引是最好的解决方案么？

索引并不总是最好的解决方案。只有当索引帮助存储引擎快速查找到记录带来的好处大于其额外的工作时，索引才有效的。

1. 对于非常小的表，大部分情况下简单的全表扫描更高效
2. 对于中到大型的表，索引就非常有效
3. 但对于特大型的表，建立和使用索引的代价将随之增长，这种情况下，需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配。例如可以使用分区技术。对于TB级别的数据，定位单条记录意义不大，所以经常会使用块级别元数据技术来代替索引

https://juejin.im/post/6844903845554814983

高性能的索引策略

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

独立的列

如果查询中的列不是独立的，则MySQL不会使用索引。独立的列指索引列不能是表达式的一部分，也不能是函数的参数。我们应该简化WHERE条件，始终将索引列单独放在比较符号的一侧

多列索引

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

即在MySQL5.0后，MySQL并不是一次查询只能使用一个索引了，可以同时使用多个索引

MySQL会使用这类技术优化复杂查询，但实际上更多时候说明了表上的索引建得很糟糕：

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

选择合适的索引列顺序

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

在一个多列索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。。。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

InnoDB将通过主键聚集数据。
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
InnoDB只聚集在同一个页面中的记录。

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

• 可以把相关数据保存在一起，
• 数据访问更快。使用覆盖索引扫描的查询可以直接使用页节点中的主键值

同时，聚簇索引也有一些缺点：

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

InnoDB的二级索引和聚簇索引很不相同。
InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。
这样策略减少了当行移动或者数据页分裂时二级索引的维护工作。只用主键值当做指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”

在InnoDB表中不按主键顺序插入行的缺点

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

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

在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充

顺序的主键什么时候造成更坏的结果

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要重新考虑设计表或者应用，或者更改innodb_autoinc_lock_mode配置。

冗余和重复索引

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

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

如果索引类型不同，不算是重复索引。比如KEY和FULLTEXT KEY