MySQL百万级数据大分页查询优化的实现

目录
  • 一、MySQL分页起点越大查询速度越慢
  • 二、 limit大分页问题的性能优化方法
    • (1)利用表的覆盖索引来加速分页查询
    • (2)用上次分页的最大id优化
  • 三、MySQL百万数据快速生成
    • 3.1、创建内存表及普通表
    • 3.2、创建函数
    • 3.3、创建插入内存表数据的存储过程
    • 3.4、创建内存表数据插入普通表的存储过程
    • 3.5、运行存储过程插入数据
  • 参考链接:

前言:在数据库开发过程中我们经常会使用分页,核心技术是使用用limit start, count分页语句进行数据的读取。

一、MySQL分页起点越大查询速度越慢

直接用limit start, count分页语句,表示从第start条记录开始选择count条记录 :

select * from product limit start, count

当起始页较小时,查询没有性能问题,我们分别看下从10, 1000, 10000, 100000开始分页的执行时间(每页取20条)。

select * from product limit 10, 20       0.002秒
select * from product limit 1000, 20      0.011秒
select * from product limit 10000, 20     0.027秒
select * from product limit 100000, 20    0.057秒

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为100w看下:

select * from product limit 1000000, 20   0.682秒

我们惊讶的发现MySQL在数据量大的情况下分页起点越大查询速度越慢,300万条起的查询速度已经需要1.368秒钟。这是为什么呢?因为limit 3000000,10的语法实际上是mysql扫描到前3000020条数据,之后丢弃前面的3000000行,这个步骤其实是浪费掉的。

select * from product limit 3000000, 20 1.368秒

从中我们也能总结出两件事情:

  • limit语句的查询时间与起始记录的位置成正比
  • mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

二、 limit大分页问题的性能优化方法

(1)利用表的覆盖索引来加速分页查询

MySQL的查询完全命中索引的时候,称为覆盖索引,是非常快的。因为查询只需要在索引上进行查找,之后可以直接返回,而不用再回表拿数据。在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何。

select id from product limit 1000000, 20 0.2秒

那么如果我们也要查询所有列,如何优化?

优化的关键是要做到让MySQL每次只扫描20条记录,我们可以使用limit n,这样性能就没有问题,因为MySQL只扫描n行。我们可以先通过子查询先获取起始记录的id,然后根据Id拿数据:

select * from vote_record where id>=(select id from vote_record limit 1000000,1) limit 20;

(2)用上次分页的最大id优化

先找到上次分页的最大ID,然后利用id上的索引来查询,类似于:

select * from user where id>1000000 limit 100

三、MySQL百万数据快速生成

利用mysql内存表插入速度快的特点,先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中

3.1、创建内存表及普通表

//内存表
CREATE TABLE `vote_record_memory` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_id` VARCHAR (20) NOT NULL,
	`vote_id` INT (11) NOT NULL,
	`group_id` INT (11) NOT NULL,
	`create_time` datetime NOT NULL,
	PRIMARY KEY (`id`),
	KEY `index_id` (`user_id`)
) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

//普通表
CREATE TABLE `vote_record` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_id` VARCHAR (20) NOT NULL,
	`vote_id` INT (11) NOT NULL,
	`group_id` INT (11) NOT NULL,
	`create_time` datetime NOT NULL,
	PRIMARY KEY (`id`),
	KEY `index_user_id` (`user_id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

3.2、创建函数

//创建函数
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '' ;
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END

3.3、创建插入内存表数据的存储过程

#创建插入内存表数据存储过程,入参n是多少就插入多少条数据
CREATE  PROCEDURE `add_vote_memory`(IN n int)
BEGIN
 DECLARE i INT DEFAULT 1;
 WHILE (i <= n) DO
   INSERT into vote_record_memory  (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
	 set i=i+1;
 END WHILE;
 END

3.4、创建内存表数据插入普通表的存储过程

此处利用对内存表的循环插入和删除来实现批量生成数据,这样可以不需要更改mysql默认的max_heap_table_size值也照样可以生成百万或者千万的数据。

  • max_heap_table_size默认值是16M。
  • max_heap_table_size的作用是配置用户创建内存临时表的大小,配置的值越大,能存进内存表的数据就越多。
#循环从内存表获取数据插入普通表
#参数描述 n表示循环调用几次;count表示每次插入内存表和普通表的数据量
 CREATE PROCEDURE `add_vote_memory_to_common`(IN n int, IN count int)
 BEGIN
 DECLARE i INT DEFAULT 1;
 WHILE (i <= n) DO
  CALL add_vote_memory(count);
	INSERT INTO vote_record SELECT * FROM vote_record_memory;
	delete from vote_record_memory;
	SET i = i + 1;
 END WHILE;
 END 

3.5、运行存储过程插入数据

#循环调用100次,每次插入1W条数据
add_vote_memory_to_vote(100,10000);

插入一百万条数据,花了2分半钟:

我执行了两次,查询vote_record表的行记录总数为两百万条:

参考链接:

MySQL的limit使用及解决超大分页问题

MySQL优化之limit分页

mysql 快速生成百万条测试数据

mysql 如何快速生成百万测试数据

到此这篇关于MySQL百万级数据大分页查询优化的实现 的文章就介绍到这了,更多相关MySQL 分页查询优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql Limit 分页查询优化详解

    select * from table LIMIT 5,10; #返回第6-15行数据 select * from table LIMIT 5; #返回前5行 select * from table LIMIT 0,5; #返回前5行 我们来写分页 物理分页 select * from table LIMIT (当前页-1)*每页显示条数,每页显示条数; MySQL之Limit简单优化.md 同样是取90000条后100条记录,传统方式还是改造方式? 传统方式是先取了前90001条记录,取其中最

  • MySQL 分页查询的优化技巧

    在有分页查询的应用中,包括 LIMIT 和 OFFSET 的查询十分常见,而且几乎每个都会有一个 ORDER BY 子句.如果使用索引排序的话将对性能优化十分有帮助,否则服务端需要做很多文件排序. 一个高频的问题是 offset 的值过大.如果查询类似 LIMIT 10000, 20,将会产生10020行,并将之前的10000行丢弃,这样的代价很高.假设所有的页使用相同的频次访问,这样的查询将平均扫描一半数据表.为了优化他们,你可以在分页视图中限制最多可访问的页数,或者让大便宜的查询更有效. 一

  • MySQL优化教程之超大分页查询

    背景 基本上只要是做后台开发,都会接触到分页这个需求或者功能吧.基本上大家都是会用MySQL的LIMIT来处理,而且我现在负责的项目也是这样写的.但是一旦数据量起来了,其实LIMIT的效率会极其的低,这一篇文章就来讲一下LIMIT子句优化的. LIMIT优化 很多业务场景都需要用到分页这个功能,基本上都是用LIMIT来实现. 建表并且插入200万条数据: # 新建一张t5表 CREATE TABLE `t5` ( `id` int NOT NULL AUTO_INCREMENT, `name`

  • mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

  • MySQL百万级数据量分页查询方法及其优化建议

    数据库SQL优化是老生常谈的问题,在面对百万级数据量的分页查询,又有什么好的优化建议呢?下面将列举了一些常用的方法,供大家参考学习! 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N 适应场景: 适用于数据量较少的情况(元组百/千级) 原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余

  • MySQL百万级数据分页查询优化方案

    当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询.对于数据库分页查询,也有很多种方法和优化的点.下面简单说一下我知道的一些方法. 准备工作 为了对下面列举的一些优化进行测试,下面针对已有的一张表进行说明. 表名:order_history 描述:某个业务的订单历史表 主要字段:unsigned int id,tinyint(4) int type 字段情况:该表一共37个字段,不包含text等大型数组,最大为varcha

  • MySQL百万级数据大分页查询优化的实现

    目录 一.MySQL分页起点越大查询速度越慢 二. limit大分页问题的性能优化方法 (1)利用表的覆盖索引来加速分页查询 (2)用上次分页的最大id优化 三.MySQL百万数据快速生成 3.1.创建内存表及普通表 3.2.创建函数 3.3.创建插入内存表数据的存储过程 3.4.创建内存表数据插入普通表的存储过程 3.5.运行存储过程插入数据 参考链接: 前言:在数据库开发过程中我们经常会使用分页,核心技术是使用用limit start, count分页语句进行数据的读取. 一.MySQL分页

  • mysql千万级数据大表该如何优化?

    1.数据的容量:1-3年内会大概多少条数据,每条数据大概多少字节: 2.数据项:是否有大字段,那些字段的值是否经常被更新: 3.数据查询SQL条件:哪些数据项的列名称经常出现在WHERE.GROUP BY.ORDER BY子句中等: 4.数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中: 5.SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少? 6.预计大表及相关联的SQL,每天总的执行量在何数量级? 7.表中的数据:更新为主的

  • MySQL 百万级数据的4种查询优化方式

    一.limit越往后越慢的原因 当我们使用limit来对数据进行分页操作的时,会发现:查看前几页的时候,发现速度非常快,比如 limit 200,25,瞬间就出来了.但是越往后,速度就越慢,特别是百万条之后,卡到不行,那这个是什么原理呢.先看一下我们翻页翻到后面时,查询的sql是怎样的: select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25; 这种查询的慢,其实是因为limit后面的偏移量太大导致的.

  • 浅谈MySQL 亿级数据分页的优化

    背景 下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排. 突然电话响了起来,一看是我们的一个开发同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了. 果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的MySql集群被拖慢了. 好吧,这问题算是严重了,下了地铁匆匆赶到家,开电脑,跟同事把Pinpoint上的慢查询日志捞出来.看到一个很奇怪的查询,如下 POST domain/v1.0/module/method?ord

  • MySQL 千万级数据量如何快速分页

    前言 后端开发中为了防止一次性加载太多数据导致内存.磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字.但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页. 案例 这里我以显示电商订单详情为背景举个例子,新建表如下: CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned NOT NULL AUTO_I

  • MySQL千万级数据的大表优化解决方案

    目录 1.数据库设计和表创建时就要考虑性能 设计表时要注意: 索引 简言之就是使用合适的数据类型,选择合适的索引 引擎 2.sql的编写需要注意优化 3.分区 分区的好处是: 分区的限制和缺点: 分区的类型: 4.分表 5.分库 mysql数据库中的表数据量几千万后,查询速度会很慢,日常各种卡慢,严重影响使用体验.在考虑升级数据库或者换用大数据解决方案前,必须优化现有mysql数据库表设计和sql语句. 1.数据库设计和表创建时就要考虑性能 mysql数据库本身高度灵活,造成性能不足,严重依赖开

  • mysql大数据查询优化经验分享(推荐)

    正儿八经mysql优化! mysql数据量少,优化没必要,数据量大,优化少不了,不优化一个查询10秒,优化得当,同样查询10毫秒. 这是多么痛的领悟! mysql优化,说程序员的话就是:索引优化和where条件优化. 实验环境:MacBook Pro MJLQ2CH/A,mysql5.7,数据量:212万+ ONE: select * from article INNER JOIN ( SELECT id FROM article WHERE length(content_url) > 0 an

随机推荐