MySQL带userId索引的表同用户连续与非连续id行查询速度差异大吗？

从理论层面分析，基于主流的InnoDB存储引擎，方案1的查询性能通常会优于方案2，但优势是否“明显”取决于实际运行场景，并非所有场景下都有显著差异。

1. 回表IO类型差异带来的性能差距 #

InnoDB的非聚簇索引（你创建的userId索引属于这类）叶子节点存储的是对应行的主键id值。执行select * from UserRecord where userId=x时的流程为：

• 第一步：从userId二级索引中匹配到所有符合条件的id集合
• 第二步：拿着id到聚簇索引（主键索引，数据本身按id有序物理存储）中读取完整行数据，该过程称为回表。

两种方案的回表效率差异极大：

• 方案1中同一用户的id完全连续，回表时属于顺序IO操作，操作系统的预读机制可以一次性加载相邻的所有数据页，通常1次IO就能拿到该用户的全部10条数据。
• 方案2中同一用户的id高度离散，回表时属于随机IO操作，即便只有3条数据，也可能分布在3个甚至更多不相邻的磁盘页上，需要多次IO才能读取完成。机械硬盘的随机IO性能仅为顺序IO的几百分之一，固态硬盘也有几倍到几十倍的差距，这种场景下方案1的优势会非常明显。

2. 数据量差异带来的反向影响 #

方案1的总数据量是方案2的3倍左右，对应的表空间、二级索引占用的磁盘空间都会大3倍：

• 这会导致MySQL缓冲池（Buffer Pool）能缓存的索引页、数据页数量更少，缓存命中率下降。
• 当查询的用户分散度极高、冷数据查询占比大时，缓存命中率下降带来的性能损失可能抵消甚至超过顺序IO带来的优势，此时两种方案的性能差异不大甚至方案2表现更好。

• 如果你的场景中大部分查询的是活跃用户，对应的数据已经加载到缓冲池中，那么两种方案的性能差异会非常小，因为内存读取不存在顺序/随机IO的差异。
• 方案1单次查询返回固定10条数据，方案2返回不超过3条数据，网络传输、结果集解析的开销方案1会略高，但相比IO层面的差异几乎可以忽略。
• 如果你后续有权限新增索引（不需要修改原有表字段结构），可以创建(userId, recordType, recordCount)的联合覆盖索引，查询时不需要回表，两种方案的性能差异会完全消失。
• 如果你使用的是MyISAM这类堆表存储引擎，数据不按主键有序存储，那么连续ID的优势完全不存在，两种方案性能差异不大。

内容的提问来源于stack exchange，提问作者cr001