为何匹配行数相近的MySQL更新查询第二次运行速度大幅提升？

基于你给出的MySQL 5.7环境、无相关索引、排除查询缓存、服务器性能平稳的前提，核心原因可分为以下几类：

• 扫描行数与数据分布差异
  两条查询均无可用索引，都会走全表扫描逐行匹配过滤条件。若符合clientID = 123 and identifier like 'ABC.%'的行在表的物理存储中分布更靠后，查询需要扫描更多不满足条件的行才能凑够limit 16000的目标行数；而符合XYZ.%前缀的行分布更集中靠前，只需扫描更少的行数就能拿到limit 8000的目标行，二者的实际磁盘IO量差距远大于匹配行数的差距。
• Buffer Pool缓存命中差异
  第一条查询执行时需要将大量未加载到内存的表数据页从磁盘读取到Buffer Pool中，产生大量随机IO开销；第二条查询执行时，大部分需要扫描的表数据页已经被第一条查询加载到内存缓存中，无需再访问磁盘，IO成本骤降。
• 修改操作的写入开销差异
  第一条需要修改16000行数据，是第二条修改行数的2倍，同时如果第一条修改的行分散在更多不同的数据页中，产生的redo日志、undo日志量更大，刷脏页的开销也更高；如果第二条匹配的行集中在少数数据页中，写入开销会大幅降低。另外如果第一条查询中row1原值大部分不为1，所有匹配行都需要执行写入操作；若第二条中row1原值已经为1，部分行仅需修改row2甚至无实际修改，也会降低写入开销。
• 过滤条件的匹配效率差异
  同样是前缀匹配规则，若clientID=123的行中符合ABC.%前缀的占比更低，查询过程中需要过滤掉更多不满足条件的行，CPU计算开销也会更高；反之XYZ.%的匹配占比更高，过滤成本更低。

你可以通过查询慢查询日志中两条语句的Rows_examined字段，确认二者实际扫描的总行数差异，也可以通过检查两个前缀匹配行的物理分布进一步验证。

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