使用MySQL进行千万级别数据查询的技巧分享,
目录
- 一般分页
- 这是什么原因呢?
- 1优化一:记录位置,避免使用 OFFSET
- 2优化二:计算边界值,转换为已知位置的查询
- 3优化三:使用索引覆盖+子查询优化
- 4优化四:使用索引覆盖+连接查询优化
一般分页
在系统中需要进行分页操作时,我们通常会使用 LIMIT 加上偏移量的方式实现,语法格式如下。
SELECT … FROM … WHERE … ORDER BY … LIMIT …
在有对应索引的情况下,这种方式一般效率还不错。但它存在一个让人头疼的问题,在偏移量非常大的时候,也就是翻页到很靠后的页面时,查询速度会变得越来越慢。
我们来演示一下。
先创建一个订单表 t_order。
CREATE TABLE t_order ( id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘自增主键', order_no varchar(32) NOT NULL COMMENT ‘订单号', user_id varchar(20) NOT NULL COMMENT ‘用户ID', amount decimal(18,2) NOT NULL COMMENT ‘订单金额', order_status tinyint(4) NOT NULL COMMENT ‘订单状态:0新建 1处理中 2成功 3失败', create_time datetime NOT NULL COMMENT ‘创建时间', PRIMARY KEY (id), UNIQUE KEY uniq_order_no (order_no) USING BTREE COMMENT ‘订单号唯一索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
往表中插入1100w 条数据。( t1 是一个有100条数据的表,这里我利用笛卡尔乘积的方式插入1100w条数据)
set @N=0; INSERT INTO t_order(order_no,user_id,amount,order_status,create_time) select CONCAT(“APP”, @N:=@N+1), CONCAT(“USER_ID_”, @N+1), @N%10000, @N%4, NOW() from t1 a, t1 b, t1 c, t1 d LIMIT 11000000;
我们看下,如下这些查询花费的时间。
select * from t_order order by id limit 0, 10; select * from t_order order by id limit 10000, 10; select * from t_order order by id limit 100000, 10; select * from t_order order by id limit 1000000, 10; select * from t_order order by id limit 10000000, 10;
执行时间如下:
– 0.002
– 0.045
– 0.069
– 0.517
– 4.134
同样是只查询10条数据,最开始的时候查询花费 0.002s,而到最后,查询花费了 4.134s。
这是什么原因呢?
这是因为查询时 MySQL 并不是跳过 OFFSET 行,而是取 OFFSET+N 行,然后放弃前 OFFSET 行,最后返回 N 行,当 OFFSET 特别大的时候,效率就非常的低下。
拿 limit 10000, 10 这条语句来说明一下, MySQL在执行这条查询的时候,需要查询 10010 (10000 + 10) 条记录,然后只返回最后 10 条,并将前面的 10000 条记录抛弃,这样当翻页越靠后时,代价就变得越来越高。
知道问题所在了,那有什么办法可以优化,解决这个问题呢?
1优化一:记录位置,避免使用 OFFSET
首先获取第一页的结果:
select * from t_order limit 10;
假如上边返回的是 id 为1 ~ 10的记录,我们将 10 这个值记住,下一页查询就可以直接从 10 这个值开始。
select * from t_order where id > 10 limit 10;
这样做,无论翻页到多少页,性能都会很好:
select * from t_order limit 10; select * from t_order where id > 10000 limit 10; select * from t_order where id > 100000 limit 10; select * from t_order where id > 1000000 limit 10; select * from t_order where id > 10000000 limit 10;
执行时间如下:
– 0.003
– 0.005
– 0.002
– 0.002
– 0.002
而如果我们当前记录的 id 值为 10000,我们想查上一页怎么办呢?返回去查一下即可:
select * from t_order where id <= 10000 order by id desc limit 10,10;
这种优化方式,可以实现上一页、下一页这种的分页。但如果想要实现跳转到指定页码的话,就需要保证 id 连续不中断,再通过计算找到准确的位置。
2优化二:计算边界值,转换为已知位置的查询
如果 id 连续不中断,我们就可以计算出每一页的边界值,让 MySQL 根据边界值进行范围扫描,查出数据。
select * from t_order where id between 0 and 10; select * from t_order where id between 10000 and 10010; select * from t_order where id between 100000 and 100010; select * from t_order where id between 1000000 and 1000010; select * from t_order where id between 10000000 and 10000010;
执行时间如下:
– 0.001
– 0.002
– 0.002
– 0.001
– 0.001
3优化三:使用索引覆盖+子查询优化
先在索引树中找到开始位置的 id 值,再根据找到的 id 值查询行数据。
select * from t_order where id >= (select id from t_order order by id limit 0, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 10000, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 100000, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 1000000, 1) order by id limit 10; select * from t_order where id >= (select id from t_order order by id limit 10000000, 1) order by id limit 10;
执行时间如下:
– 0.007
– 0.009
– 0.047
– 0.332
– 2.822
可以看到,这种优化方式也可以提升查询速度。这其实是利用了索引覆盖的如下好处:
索引文件不包含行数据的所有信息,故其大小远小于数据文件,因此可以减少大量的IO操作。
索引覆盖只需要扫描一次索引树,不需要回表扫描行数据,所以性能比回表查询要高。
4优化四:使用索引覆盖+连接查询优化
这种优化方式跟 优化三 原理一样。也是先在索引上进行分页查询,当找到 id 后,再统一通过 JOIN 关联查询得到最终需要的数据详情。
select * from t_order a Join (select id from t_order order by id limit 0, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 10000, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 100000, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 1000000, 10) b ON a.id = b.id; select * from t_order a Join (select id from t_order order by id limit 10000000, 10) b ON a.id = b.id;
执行时间如下:
– 0.001
– 0.023
– 0.028
– 0.348
– 2.955
以上就是使用MySQL进行千万级别数据查询的技巧分享的详细内容,更多关于MySQL千万级别数据查询的资料请关注PHP之友其它相关文章!
您可能感兴趣的文章:- MySQL 快速删除大量数据(千万级别)的几种实践方案详解
- MySQL中数据查询语句整理大全
- MySQL中存储的数据查询的时候如何区分大小写
- 详解MySQL数据库千万级数据查询和存储
- mysql聚合统计数据查询缓慢的优化方法