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 0.016秒

select * from order limit 1000, 20 0.047秒

select * from order limit 10000, 20 0.094秒

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

select * from order limit 400000, 20 3.229秒

再看我们取最后一页记录的时间

select * from order limit 800000, 20 37.44秒

显然这种时间是无法忍受的。

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

1）limit语句的查询时间与起始记录的位置成正比

2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

2.对limit分页问题的性能优化方法

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

我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：

这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：

select id from order limit 800000, 20 0.2秒

相对于查询了所有列的37.44秒，提升了大概100多倍的速度

那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

SELECT * FROM order WHERE ID > =(select id from order limit 800000, 1) limit 20

查询时间为0.2秒，简直是一个质的飞跃啊，哈哈

另一种写法

SELECT * FROM order a JOIN (select id from order limit 800000, 20) b ON a.ID = b.id

查询时间也很短

赞 (0)

相关推荐

  • 千万级记录的Discuz论坛导致MySQL CPU 100%的优化笔记

    发现此主机运行了几个 Discuz 的论坛程序, Discuz论坛的好几个表也存在着这个问题.于是顺手一并解决,cpu占用再次降下来了. 前几天,一位朋友通过这篇文章找到了我,说他就是运行最新的 discuz 版本,MySQL 占用 CPU 100%,导致系统假死,每天都要重启好几次,花了一个多月的时间一直没有解决,希望我帮忙一下.经过检查,他的这个论坛最重要的几个表中,目前 cdb_members 表,有记录 6.2 万:cdb_threads 表,有记录 11万:cdb_posts表,有记录

  • 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与MSSQl使用While语句循环生成测试数据的代码

    在MySQL中,使用While语句循环与SQL Server中有所不同,代码测试通过. MSSQL中使用while语句循环生成数据的方法: 示例代码: 复制代码 代码如下: declare @a int set @a = 1 while @a<25 begin INSERT INTO demotable (id,item1,item2) VALUES (@a,"abc","123") set @a = @a + 1 end MySQL中,使用while循环处理

  • mysql 动态生成测试数据

    一.问题 要生成两类数据: A类:两位的 01 02 03 ...09 10 11...19 20 21 ...98 99 另一类B类:三位的 100 101 102 ...110 111 112...998 999 二.解决办法 1.建表 复制代码 代码如下: CREATE TABLE `test`.`ta` ( `a` varchar(45) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2.创建存储过程 复制代码 代码如下: DELIMITE

  • MySQL如何快速的创建千万级测试数据

    MySQL如何快速的创建千万级测试数据

    备注: 此文章的数据量在100W,如果想要千万级,调大数量即可,但是不要大量使用rand() 或者uuid() 会导致性能下降 背景 在进行查询操作的性能测试或者sql优化时,我们经常需要在线下环境构建大量的基础数据供我们测试,模拟线上的真实环境. 废话,总不能让我去线上去测试吧,会被DBA砍死的 创建测试数据的方式 1. 编写代码,通过代码批量插库(本人使用过,步骤太繁琐,性能不高,不推荐) 2. 编写存储过程和函数执行(本文实现方式1) 3. 临时数据表方式执行 (本文实现方式2,强烈推荐该

  • mysql中迅速插入百万条测试数据的方法

    对比一下,首先是用 mysql 的存储过程弄的: 复制代码 代码如下: mysql>delimiter $ mysql>SET AUTOCOMMIT = 0$$ mysql> create procedure test() begin declare i decimal (10) default 0 ; dd:loop INSERT INTO `million` (`categ_id`, `categ_fid`, `SortPath`, `address`, `p_identifier`

  • 如何优化Mysql千万级快速分页

    看例子: 数 据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个基本的新闻系统的简单模型.现在往里面填充数据,填充10万篇新闻. 最后collect 为 10万条记录,数据库表占用硬盘1.6G. OK ,看下面这条sql语句: select id,title from collect limit 1000,10; 很快:基本上0.01秒就OK

  • MySQL 百万级分页优化(Mysql千万级快速分页)

    以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM

  • 30个mysql千万级大数据SQL查询优化技巧详解

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用

  • 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执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了. 能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低.代价小且行之有效的解决方法就是合理的加索引.索引使用得当,能使查询速度提升上千倍,效果惊人. (二)mysql的索引类型: mysql的索引有5种:主键索引.普通索引.唯一索引.全文索引.聚合索引(多列索引).

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

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

  • Oracle分页查询性能优化代码详解

    对于数据库中表的数据的 Web 显示,如果没有展示顺序的需要,而且因为满足条件的记录如此之多,就不得不对数据进行分页处理.常常用户并不是对所有数据都感兴趣的,或者大部分情况下,他们只看前几页. 通常有以下两种分页技术可供选择. Select * from ( Select rownum rn,t.* from table t) Where rn>&minnum and rn<=&maxnum 或者 Select * from ( Select rownum rn,t.* fro

  • MySQL查询性能优化索引下推

    MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

  • MySQL千万级数据表的优化实战记录

    MySQL千万级数据表的优化实战记录

    前言 这里先说明一下,网上很多人说阿里规定500w数据就要分库分表.实际上,这个500w并不是定义死的,而是与MySQL的配置以及机器的硬件有关.MySQL为了提升性能,会将表的索引装载到内存中.但是当表的数据到达一定的量的时候,会导致内存无法存储这些索引,无法存储索引,就只能进行磁盘IO,从而导致性能下降. 实战调优 我这里有张表,数据有1000w,目前只有一个主键索引 CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `u

  • 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 亿级数据分页的优化

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

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

  • MySQL Limit性能优化及分页数据性能优化详解

    MySQL Limit可以分段查询数据库数据,主要应用在分页上.虽然现在写的网站数据都是千条级别,一些小的的优化起的作用不大,但是开发就要做到极致,追求完美性能.下面记录一些limit性能优化方法. Limit语法: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT接受一个或两个数字参数.参数必须是一个整数常量. 如果给定两个参数,第一个参数指定

随机推荐