【高性能MySQL】第5章 读书笔记

第5章 创建高性能索引

5.1 索引基础

5.1.1 索引的类型

B-Tree 索引

使用 B-Tree 数据结构来存储数据。

哈希索引

基于哈希表实现

只有 Memory 引擎支持哈希索引

自定义哈希索引

在 B-Tree 索引基础上创建一个伪哈希索引，可以享受一些哈希索引的便利。

R-Tree 索引（空间数据索引）

MyISAM 表支持，用作地理数据存储。

全文索引

其他类别索引

树形索引 — TokuDB

聚簇索引

覆盖索引

Patricia tries — ScaleDB

5.2 索引的优点

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

使用场景：

对于非常小的表：全文扫描。

对于TB级别的数据：块级别元数据技术。

5.3 高性能索引策略

5.3.1 独立的列

5.3.2 前缀索引和索引选择性

索引选择性

5.3.3 多列索引

出现 AND，OR 说明表上的索引建得很糟糕。

5.3.4 选择合适的索引列顺序

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

5.3.5 聚簇索引

结合散列表的索引？

优点：

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

缺点：

• 提高了 IO 密集型应用的性能，但如果数据全部放在内存中，则访问顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。
• 更新聚簇索引代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
• 面临“页分裂”问题。
• 聚簇索引可能会导致全表扫描变慢。
• 二级索引可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

InnoDB 和 MyISAM 的数据分布对比

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

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

在高并发工作负载中：

• 并发插入会导致间隙锁竞争。

• AUTO_INCREMENT：更改 innodb_autoinc_lock_mode 配置

5.3.6 覆盖索引

如果一个索引包含所有需要查询字段的值，我们就称之为覆盖索引。

只能使用 B-Tree 索引做覆盖索引。

延迟关联。

5.3.7 使用索引扫描来做排序

5.3.8 压缩（前缀压缩）索引

MyISAM 中使用

5.3.9 冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。

5.3.10 未使用的索引

找出未使用的索引：

1. Percona Server 工具
2. Percona Toolkit 中的 pt-index-usage

5.3.11 索引和锁

关于 InooDB，锁，索引有一个鲜为人知的细节：InooDB 在二级索引需要共享锁，但访问主键索引需要排他锁。这消除了使用覆盖索引的可能性。

5.4 索引案例学习

5.4.1 支持多种过滤条件

5.4.2 避免多个范围条件

5.4.3 优化排序

5.5 维护索引和表

5.5.1 找到并修复损坏的表

5.5.2 更新索引统计信息

5.5.3 减少索引和数据碎片

B-Tree 索引可能会碎片化。

三种数据碎片：

• 行碎片
• 行间碎片
• 剩余空间碎片

MyISAM 三类碎片都有可能产生

InnoDB 不会出现短小的碎片；InnoDB 会移动短小的行并重写到一个片段中。