Mysql 优化limit查询【实战可用】

LIMIT 优化查询

​最近在开发中遇到一个问题，当一个表中的数据量非常大的时候。
然后用limit查询时 效率会随着数据量的增长而变慢，他们的关系成正比。

​下面介绍几种优化方式，让你的分页查询快起来！且不仅仅是分页查询。

下面是一张通过OCR识别水表信息的表，每天都有成千上万张图片进行识别。

表结构:

CREATE TABLE `ocr_image_recognition`  (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `STUBNumber` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '销根号',
  `sn` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '识别序列号',
  `code` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '识别读数',
  `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文件名',
  `is_identify` int(2) NULL DEFAULT 0 COMMENT '是否识别 0--识别  1--未进行识别',
  `pt` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '时间',
  `CREATETIME` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATETIME` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `scan_num` int(11) NULL DEFAULT 1 COMMENT '识别次数',
  `first_sn` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `first_code` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '识别算法',
  `interface` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `filename`(`filename`) USING BTREE,
  INDEX `pt`(`pt`) USING BTREE,
  INDEX `STUBNumber`(`STUBNumber`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5722690 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

数据量有五百多万：

首先我们看下按照正常的方式去实现 需要花费多少时间？

SELECT * FROM `ocr_image_recognition` limit 500000,10

​ 分析一下我们常用的limit 在分页的过程中 是怎样去进行查询的？我们来看下这个Sql执行流程：

1. 通过ID，到主键索引树，找到满足记录的行，然后取出展示的列（回表）

2. 扫描满足条件的500010行，然后扔掉前500000行，返回。

   如果我们把回表这个操作给省略，查询效率会不会快很多？

1.子查询

​通过子查询，把需要的列查出来 然后通过主键id去关联，减少回表的操作。它这里用到的是B+树 + 主键索引。

 select * from ocr_image_recognition where id >= 
(select id from  ocr_image_recognition ORDER BY id limit 500000,1) limit 10

2.关联查询

跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是，关联使用了right join代替子查询。

SELECT * FROM
	ocr_image_recognition o1
	RIGHT JOIN ( SELECT Id FROM `ocr_image_recognition` ORDER BY id LIMIT 500000, 10 ) o2 ON o1.id = o2.id

3.标签记录法

​ 其实在数据量比较大的情况下进行分页，主要就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。

其实我们可以采用标签记录法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

假设上次查询到id为：540465的这条记录（为了对比上面）:

SELECT * FROM ocr_image_recognition where id >= 540464 ORDER BY id LIMIT 10

4.between…and…

​ 很多时候，可以将limit查询转换为已知位置的查询，这样MySQL通过范围扫描between...and，就能获得到对应的结果，这样也就可以使用**between…and…**进行优化

例如：540464 - 540473

SELECT * FROM ocr_image_recognition where id between 540464 and 540473

以上就是在分页查询的时候常用的四种优化方案！在面试中也可能被问到，这个时候就可以畅所欲言了。

只不过要想在面试官面前装逼，你需要了解这四种方式查询的时候 mysql的执行流程，为什么会比普通的limit快！还有他们在执行的过程中会用到哪些方法来提高自己的查询效率。

所欲言了。

只不过要想在面试官面前装逼，你需要了解这四种方式查询的时候 mysql的执行流程，为什么会比普通的limit快！还有他们在执行的过程中会用到哪些方法来提高自己的查询效率。