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执行流程:
-
通过ID,到主键索引树,找到满足记录的行,然后取出展示的列(回表)
-
扫描满足条件的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快!还有他们在执行的过程中会用到哪些方法来提高自己的查询效率。