《高性能MySQL》记录
《高性能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 消耗。 关于如何选择合适的前缀长度，可以通过计算选择性进行评估： 1 SELECT COUNT(DISTINCT LEFT(column, N)) / COUNT(*) FROM table; 选择接近完整列选择性的最短前缀（如对邮箱地址反转后建前缀索引）。 复合索引与最左前缀原则 最左前缀原则：查询必须从复合索引的最左列开始，且不能跳过中间列。示例：索引 (A,B,C) 支持 A=1 AND B=2 ，但不支持 B=2 或 B=2 AND C=3 。使用复合索引时要注意范围查询限制，若某一列使用范围查询，其右侧的列无法使用索引优化。...