MySQL大翻页查询优化

背景

• 前两天做项目需要讲数据从MySQL 同步到ES中，由于索引结构经常发生变化，需要时不时就跑一个全量的索引。
• 数据库MySQL5.7
• 开始方案没有选好，图省事，用的select * from table limit x,10000进行全量读取(现在已经改成用id扫描方式了。。)，开始速度还可以，越到后面越慢。

自己测试

• 在自己的电脑上面做了一个一个测试，主要是优化一下limit查询，有一定提升，但是这个尽量还是别用的好，全量还是用id扫描最快。

• 配置如下：

  • 16款MacPro，16G内存 4Core i7 2.2G，SSD硬盘
  • MySQL 5.7.22
  • 表很简单一列主键id，一列guid,guid没有加索引，InnoDB引擎
  • 数据量24589792（自己select insert 进去的）
  • 没有做任何特殊优化，只是标准安装(- -自己水平有限)

• 测试结果如下：

  • 语句1：SELECT * FROM token LIMIT 24589790,1
  • 结果：
    • OK, Time: 5.920000s
    • OK, Time: 5.872000s
    • OK, Time: 5.821000s
    • OK, Time: 5.819000s
    • OK, Time: 5.880000s
  • 语句2：SELECT * FROM token INNER JOIN (SELECT id FROM token LIMIT 24589790,1) t USING (id)
  • 结果：
    • OK, Time: 4.897000s
    • OK, Time: 4.962000s
    • OK, Time: 4.897000s
    • OK, Time: 4.889000s
    • OK, Time: 4.910000s

• 其实还是挺明显的，接近1秒的差别

原理

• 从《高性能MySQL》中看到的，摘抄一下
• LIMIT和OFFSET，尤其是OFFSET 会导致MySQL扫描大量的不需要的行，然后抛弃掉，这个也是查询慢的根本原因。
• 语句2提升查询效率是因为他可以让MySQL扫描尽可能少的页面，获取到需要访问的记录后再根据关联列去原表找到所需要的列。

高效的最终方案

• 就是上面提到的通过Id扫描的方式进行查询，速度杠杠的
• SELECT * FROM token WHERE id>4589790 LIMIT 1
• 花费时间(hmmm……显示是OK, Time: 0.000000s)