《高性能MySQL》记录
创建高性能的索引
索引基础
B-Tree 索引
InnoDB 引擎的默认索引类型是 B+ Tree，按照原数据格式进行存储。
B-Tree 意味着所有的值都是按顺序存储的，并且每一个叶子到根的距离相同。B-Tree 索引能够加快访问数据的速度，通过根节点的指针不断向下比较查找，最终存储引擎要么找到对应的值，要么记录不存在。B-Tree 对索引列的顺序存储结构适合查找范围数据，如全值匹配、匹配最左前缀、匹配范围值等。除了按值查找，索引还可以用于 ORDER BY 操作。
B-Tree 索引存在以下限制：
- 如果不是按照索引的最左列开始查找，则无法使用索引。对于复合索引(A,B,C)，查询条件必须包含A列才能有效使用该索引。如果查询只使用B或C列，索引通常不会被使用。
- 不能跳过索引中的列，必须按照索引定义的列顺序使用。
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。
哈希索引
哈希索引是基于哈希表实现的索引类型，通过哈希函数将索引列值转换为哈希码来快速定位数据行。在MySQL中，只有MEMORY存储引擎显式支持哈希索引，并且这是MEMORY引擎的默认索引类型。
哈希索引是无序存储，而且只支持精确匹配。哈希索引的限制如下：
- 不支持范围查询，如
>、<、BETWEEN等。 - 不支持部分列匹配，必须使用索引的所有列才能生效，因为哈希索引始终是使用索引项的全部内容来计算哈希值的。
- 哈希冲突问题，不同值可能产生相同哈希值，需要额外处理。
InnoDB 有一个自适应哈希索引，InnoDB 引擎会自动监控查询模式，对频繁访问的索引值创建自适应哈希索引。这实际上是 B-Tree 索引上的优化层，使用哈希值而非键本身进行查找。
空间数据索引（R-Tree）
空间数据索引是一种专门用于处理多维空间数据(如地理坐标、几何图形等)的索引技术，主要解决传统 B-Tree 索引无法有效处理空间数据的问题。
全文索引
全文索引是专门为文本内容搜索设计的索引技术，通过建立倒排索引实现高效文本检索。
高性能的索引策略
独立的列
索引列必须作为独立的表达式存在，不能参与计算或作为函数参数，否则索引会失效。MySQL 无法解析包含运算或函数的表达式，必须将列单独提取为查询条件，才能触发索引优化。
前缀索引与索引选择性
针对长字符串或文本类型，通过截取前 N 个字符建立索引，减少存储空间和 I/O 消耗。
关于如何选择合适的前缀长度，可以通过计算选择性进行评估：
| |
选择接近完整列选择性的最短前缀（如对邮箱地址反转后建前缀索引）。
复合索引与最左前缀原则
最左前缀原则：查询必须从复合索引的最左列开始，且不能跳过中间列。示例：索引 (A,B,C) 支持 A=1 AND B=2 ，但不支持 B=2 或 B=2 AND C=3 。使用复合索引时要注意范围查询限制，若某一列使用范围查询，其右侧的列无法使用索引优化。
关于复合索引的列选择，选择性高的列放左侧、优先筛选更多数据。若需 ORDER BY A,B ，索引顺序应为 (A,B) 。
聚簇索引
聚簇索引并非独立的索引类型，而是 InnoDB 的数据存储方式，将 B-Tree 索引和数据行合并存储在同一个结构中。数据行按主键顺序物理存储，叶子节点包含完整数据。聚簇索引优先选择自增主键，若无主键，选择首个唯一非空索引。两者均无时，InnoDB 隐式创建包含 rowid 的聚簇索引。
聚簇索引有以下优点：
- 数据局部性：相关数据（如用户ID关联的邮件）物理相邻存储，减少磁盘 I/O 。
- 查询性能：索引与数据共存于 B-Tree，避免二次查找。覆盖索引扫描可直接利用节点中的主键值。
聚簇索引的缺点：
同时也导致了插入和更新的代价比较高：
主键顺序插入最快，乱序插入易引发页分裂，需
OPTIMIZE TABLE优化。更新聚簇索引列会强制移动行位置，增加 I/O 开销。
存储问题：
- 页分裂导致空间碎片化，全表扫描效率降低。
- 二级索引体积较大，需回表查询。
覆盖索引
索引包含查询所需的所有字段，无需回表，显著减少I/O和锁竞争。高频查询字段较少时，可将这些字段加入索引。InnoDB的二级索引会隐式包含主键值，因此若查询包含主键和索引列，也可能触发覆盖索引。通过 EXPLAIN 的 Extra 列显示 Using index 时即表示使用了覆盖索引。
使用索引扫描排序
MySQL可通过两种方式排序：
- 文件排序（Filesort）：临时磁盘排序，性能较差。
- 索引扫描排序：按索引顺序直接返回结果，
EXPLAIN的type列为index。
要使用索引扫描排序，必须满足以下所有条件：
- 列顺序一样：
ORDER BY子句的列顺序与索引定义完全一致。 - 排序方向一样：所有列均为升序（ASC）或降序（DESC），混合方向需文件排序。
- 最左匹配原则。
当出现以下情况时会失效：
- 范围查询。
- 包含非索引列。
- 多表关联：仅当
ORDER BY列全部来自关联的第一个表时才生效。
把索引压缩
通过压缩算法减少索引占用的存储空间，主要应用于MyISAM引擎（InnoDB的压缩机制不同）：
- 压缩前缀索引：默认仅压缩字符串，通过截取列值的前缀减少空间。
- 整数压缩：需手动启用，对整数值进行差分编码或位压缩。
压缩索引可以节省存储同时提高 I/O 吞吐量，但是解压会增加计算负担（不适合 CPU 计算密集型场景），同时还会增加索引的更新代价。
冗余索引和重复索引
概念：
重复索引：在相同列上按相同顺序创建的相同类型索引（如对列
A同时创建两个 B-Tree 索引）。MySQL 需单独维护每个重复索引，优化器需逐个评估，影响性能。冗余索引：若已有联合索引
(A,B)，再创建(A)则为冗余索引（因(A,B)可替代(A)的功能）。但(B)或(B,A)不冗余。
这两种索引增加存储占用，且额外增加更新代价，降低性能。
若扩展已有索引会导致索引过大（如从 INT 列扩展为 INT+VARCHAR ），可保留冗余索引以兼顾不同查询性能。
索引和锁
InnoDB 行锁依赖索引：锁定的行通过索引定位。若查询使用索引，仅锁定符合条件的行；否则退化为全表扫描并锁定更多行。
锁类型：
- 记录锁（Record Lock）：锁定索引中的具体记录。
- 间隙锁（Gap Lock）：在可重复读（RR）隔离级别下，防止其他事务在索引间隙插入数据（解决幻读）。
性能影响：
- 索引优化锁粒度：有效索引减少锁定行数，降低锁竞争，提高并发性。
- 无索引的代价：若
WHERE条件无法利用索引，InnoDB 会锁定全表或大量行，导致阻塞。