面试官问我MySQL索引

上周我在项目中遇到了优化 MySQL 性能的问题。
这主要得益于InnoDB引擎索引，它让我对B+树、表背、覆盖索引等概念有了更深入的理解。

首先，由于其数据存储的性质，选择B+树作为索引的底层数据结构。
在MySQL中，数据存储在硬盘上，不能立即加载到内存中。
B+树多路径搜索特性可以让单个节点存储更多信息，降低树高，从而减少磁盘I/O，提高检索速度。

其次，B+树的叶子节点通过链表连接，这使得遍历查询非常方便。
可以顺序访问后续记录，无需跨级搜索，特别是在执行范围查询时。

谈回表和覆盖索引，回表是指当索引不能直接提供所有需要的列时，必须根据主键ID重新查询数据。
覆盖索引避免了必须返回表，因为它包含查询所需的所有列，因此可以直接从索引检索所需的数据。

最左匹配原则是使用索引的规则之一。
这意味着索引最左侧的查询词必须一致匹配。
当遇到范围查询时（例如大于、小于、BETWEEN、LIKE左对齐匹配），后续列无法使用索引。

至于主键自增的重要性，主要是因为主键自增可以实现更高的插入效率。
与生成 UUID 等主键相比，自增主键通常是顺序插入的，不需要因磁盘空间问题而移动数据，从而提高了性能。

2 02 3 年，当我讨论这个问题时和同事提问时，我也想到了一个例子。
例如，表的主键是订单号。
如果我们使用UUID作为主键，那么在插入数据时可能会遇到性能问题，因为UUID可能会导致磁盘块移动。

我对这部分不太确定，但我认为在讨论性能优化时理解这些基本点很重要。
你会明白的，也许你也会在你的项目中使用这些知识。

mysql索引命中规则

说白了，MySQL索引的核心就是“最左匹配”加上“条件类型”，但实际的用法比这复杂得多。

扩展一下，匹配是最左边的依据，比如索引(A,B,C)，WHERE A=1 AND B=2 会完美匹配，但是WHERE B=2 就完全没用了——这就是为什么相等条件必须在左边。
还有一点就是范围条件（比如C>3 ）一定要放在最右边，因为B+树只能从左到右顺序读取。
一旦C过去了，之前的A和B就白搭了。
去年我们跑的3 000级项目中，仅仅因为我们把范围条件放在了前面，查询就减少了5 0%。
还有另一个关键细节。
例如，带有前导通配符的 LIKE '%X' 根本无法建立索引，但可以使用 'X%' - 这有点像在字典中搜索第一个字母。

一开始我以为索引越多越好，后来发现错了。
索引的维护成本非常高，尤其是在高并发场景下。
例如，如果单列索引的长度超过3 00字节，B+树的高度会大幅增加，查询效率会下降。

提醒：不带引号的字符串类型会触发隐式转换。
比如name='1 2 3 '写成name=1 2 3 ，索引就会直接失效。
许多人不注意这一点。

建议多运行EXPLAIN，看看SQL索引是如何使用的。
尤其是在复合索引中，将低选择性字段放在右侧会严重影响效率。

mysql索引命中规则讲解

等等，昨天在调试缓慢的电商订单表查询时，我发现了一个有趣的细节。

凌晨3 点，用户在后台查看“订单号以ABC开头，金额大于2 00”的SQL，执行计划显示已到达索引（A，order_id，金额）。
但看慢查询日志可以看到，实际只使用了A和order_id列上的索引，并且直接将金额范围条件扫描到表中。

当时我在看B+树图，发现虽然order_id不是一个连续范围，但是数据库以order_id=ABC将节点分组在一起。
这相当于先找到A列中以order_id开头的segment，然后在该segment内使用order_id进行二次过滤。

突然我想到，如果表中A列的值非常稀疏，比如9 9 %都是A=1 ，那么B+树最左边分支的节点有多大呢？可以存储1 6 KB块中的9 9 %（A=1 的记录），为B列留下1 KB。
这种“扭曲树”会影响查询效率吗？
等等，还有一件事。
之前的测试发现，当WHERE条件为“A as 'AB%' AND B in (1 ,2 )”时，MySQL实际上是分别处理A列的前缀匹配和B列的列族条件，最后合并结果。
这比顺序扫描表 A->B-> 要快得多。

所以索引规则可能更复杂，而不仅仅是查看最左边的列。
B+树分裂合并时，必须包含节点缓存命中率。
例如，如果order_id段在内存页中，则遍历速度比从磁盘读取快1 0倍。

我突然想到，如果A列是唯一索引，那么最左边的匹配实际上与精确查询相同。
但是B列是范围条件，为什么还能继续使用索引呢？这取决于B列在A列的分支中的分布密度。
如果密度太高，树会退化为链表；如果密度太高，树会退化为链表；如果密度太低，则范围扫描需要表扫描。

已经快早上1 0点了，咖啡杯里还装满了昨晚没喝完的水。
电商订单表A列的值其实并不稀疏，但是B列的范围条件特别集中。
例如，9 8 %的订单金额在2 00到5 00之间，那么B+树分支节点只能满足这两个范围条件。

如果B列的范围条件比较稀疏，例如金额分布到1 0个范围，那么树会被分成1 0个子树吗？这取决于数据库如何优化。
等等，还有一件事……