精华内容
下载资源
问答
  • 在mysql大数据量的基础下,分页中随着页码的增加,查询时间也会响应的增加。所以到了百万级别的数据量时,我们就需要优化已有的查询代码进行合理有效的分页

    1. 常见问题

    在mysql大数据量的基础下,分页中随着页码的增加,查询时间也会响应的增加。所以到了百万级别的数据量时,我们就需要优化已有的查询代码进行合理有效的分页。

    一般情况下,页码与查询时间成正比

    SELECT count(*) FROM `sys_user`
    结果: 4344284
    > 时间: 1.132s
    
    SELECT * FROM `sys_user` LIMIT 10000,20
    > 时间: 0.095s
    
    SELECT * FROM `sys_user` LIMIT 100000,20
    > 时间: 0.181s
    
    SELECT * FROM `sys_user` LIMIT 1000000,20
    > 时间: 1.302s
    
    SELECT * FROM `sys_user` LIMIT 4000000,20
    > 时间: 5.498s
    

    2. 出现的原因

    我们先要了解到 limit 4000000,20的意思扫描满足条件的4000020行,扔掉前面的4000000行,返回最后的20行,问题就在这里,其实我们每次都只是需要20行的数据内容,那么到后面每次查询需要扫描超过400W+行,性能肯定大打折扣。

    3. 解决方式

    对超过特定阈值的页数进行 SQL改写:

    SELECT
    	a.* 
    FROM
    	`sys_user` a,
    	( SELECT id FROM `sys_user` LIMIT 4000000, 20 ) b 
    WHERE
    	b.id = a.id
    > 时间: 0.699s
    

    将语句抽离成一个可以复用的sql

    SELECT a.* FROM1 a, (select id from1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
    

    4. 原理

    利用覆盖索引来进行查询操作,避免回表

    说明:如果一本书需要知道第 11 章是什么标题,会翻开第 11 章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。我们语句中的覆盖索引就使用的就是

    SELECT id FROM `sys_user` LIMIT 4000000, 20
    

    这条sql直接拿到我们要查询的20条数据的id,再去表中查询它的具体数据

    展开全文
  • Mysql的分页查询十分简单,但是当数据量大的时候一般的分页就吃不消了。 传统分页查询:SELECT c1,c2,cn… FROM table LIMIT n,m MySQL的limit工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,...

    Mysql的分页查询十分简单,但是当数据量大的时候一般的分页就吃不消了。

    传统分页查询:SELECT c1,c2,cn… FROM table LIMIT n,m

    MySQL的limit工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,所以n越大,偏移量越大,性能就越差。

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

    推荐分页查询方法:

    1、尽量给出查询的大致范围

    利用表的覆盖索引来加速分页查询
    我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引:索引的数据覆盖了需要查询的所有数据),那么这种情况会查询很快。
    因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。
    在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:

    之前,我们取最后一页记录的时间

    select * from product limit 866613, 20   37.44秒

    这次我们查询最后一页的数据(利用覆盖索引,只包含id列),如下:

    select id from product limit 866613, 20 0.2秒

    相对于查询了所有列的37.44秒,提升了大概100多倍的速度
    那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

    SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
    查询时间为0.2秒,简直是一个质的飞跃

    另一种写法

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

    *统配符号在实际查询的中不应该使用,应该指明相应的属性,效率更好
    其实两者用的都是一个原理,所以效果也差不多.

    SELECT c1,c2,cn... FROM table WHERE id>=20000 LIMIT 10;

    2、子查询法(在方法1基础上改进的)

    SELECT c1,c2,cn... FROM table WHERE id>=
    (
        SELECT id FROM table LIMIT 20000,1
    )
    LIMIT 10;

    3、 第一步用用程序读取出ID,然后再用IN方法读取所需记录

    程序读ID:

    SELECT id FROM table LIMIT 20000, 10;
    SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)

    4.高性能MySQL一书中提到的只读索引方法(sql语句相比前三种复杂)

      一般表中经常作为条件查询的列都会建立索引,例如如下查询

        Sql代码  优化前

    SELECT id, content  FROM tb_chat ORDER BY create_time DESC LIMIT 24000, 20;

    Sql代码   优化后

    SELECT id, content FROM tb_chat   
    INNER JOIN (  
        SELECT id FROM tb_chat  
        ORDER BY create_time LIMIT 24000, 20  
        ) AS page USING(id);  

     这样当前查询页的内容就只会在索引中进行,当得到当前页的id再统一通过一个INNER JOIN得到最终要得到的数据详情,避免了对大量数据详情进行操作的消耗。当然JOIN操作也可以通过子查询实现,不过书中介绍5.6之前版本的mysql相比子查询还是优先使用JOIN。

    对上一个sql继续优化改进,当有查询条件分页时,一定要确保有数据是在limit后面的条件里,正常有输入条件检索查询应该是limit 0, 10   我写的是limit 15000,20只是为了测试,因为符合该条件的数据只有1万5千多个,不然超出这个数就查不到数据了,切记。

    SELECT id, content,c.z_type FROM tb_chat c
    INNER JOIN (  
        SELECT id,z_type FROM tb_chat WHERE z_type='1'
        ORDER BY create_time LIMIT 15000, 20  
    ) AS page USING(id);  

    等同于:

    SELECT c.id, c.content,c.z_type FROM tb_chat c
    INNER JOIN (  
        SELECT id,z_type FROM tb_chat WHERE z_type='1'
        ORDER BY create_time LIMIT 15000, 20  
    ) AS p ON c.id=p.id; 

    个人觉得此方法更为通用,而且经过我的测试,发现表中总数据只有3万条数据时两个sql语句的执行时间竟然相差4倍,优化前的sql执行需要120ms,而优化后的sql需要30ms。

    参考:https://www.cnblogs.com/shihaiming/p/6307018.html

    https://blog.csdn.net/u011687186/article/details/70213024

    展开全文
  • Mysql的分页查询十分简单,但是当数据量大的时候一般的分页就吃不消了。 传统分页查询:SELECT c1,c2,cn… FROM table LIMIT n,m MySQL的limit工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,...

    参考文章:https://www.dexcoder.com/selfly/article/293

     

    Mysql的分页查询十分简单,但是当数据量大的时候一般的分页就吃不消了。

    传统分页查询:SELECT c1,c2,cn… FROM table LIMIT n,m

    MySQL的limit工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,所以n越大,偏移量越大,性能就越差。

    推荐分页查询方法:

    1、尽量给出查询的大致范围

        

    SELECT c1,c2,cn... FROM table WHERE id>=20000 LIMIT 10;

     

    2、子查询法

     

    SELECT c1,c2,cn... FROM table WHERE id>=
    (
        SELECT id FROM table LIMIT 20000,1
    )
    LIMIT 10;

     

    3、高性能MySQL一书中提到的只读索引方法

      一般表中经常作为条件查询的列都会建立索引,例如如下查询

        
    Sql代码  优化前
    SELECT id, content  FROM tb_chat ORDER BY create_time DESC LIMIT 24000, 20;

     

    Sql代码   优化后
    SELECT id, content FROM tb_chat   
    INNER JOIN (  
        SELECT id FROM tb_chat  
        ORDER BY create_time LIMIT 24000, 20  
        ) AS page USING(id);  

     这样当前查询页的内容就只会在索引中进行,当得到当前页的id再统一通过一个INNER JOIN得到最终要得到的数据详情,避免了对大量数据详情进行操作的消耗。当然JOIN操作也可以通过子查询实现,不过书中介绍5.6之前版本的mysql相比子查询还是优先使用JOIN。

    对上一个sql继续优化改进,当有查询条件分页时,一定要确保有数据是在limit后面的条件里,正常有输入条件检索查询应该是limit 0, 10   我写的是limit 15000,20只是为了测试,因为符合该条件的数据只有1万5千多个,不然超出这个数就查不到数据了,切记

    SELECT id, content,c.z_type FROM tb_chat c
    INNER JOIN (  
        SELECT id,z_type FROM tb_chat WHERE z_type='1'
        ORDER BY create_time LIMIT 15000, 20  
    ) AS page USING(id);  

    等同于:

    SELECT c.id, c.content,c.z_type FROM tb_chat c
    INNER JOIN (  
        SELECT id,z_type FROM tb_chat WHERE z_type='1'
        ORDER BY create_time LIMIT 15000, 20  
    ) AS p ON c.id=p.id; 

     

    个人觉得此方法更为通用,而且经过我的测试,发现表中总数据只有3万条数据时两个sql语句的执行时间竟然相差4倍,优化前的sql执行需要120ms,而优化后的sql需要30ms。

     

    4、第一步用用程序读取出ID,然后再用IN方法读取所需记录

    程序读ID:

    SELECT id FROM table LIMIT 20000, 10;
    SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)

     

     
     

    转载于:https://www.cnblogs.com/shihaiming/p/6307018.html

    展开全文
  • 1. limit分页的原理 先来看一下分页的基本原理 mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20 ***************** 1. row ************** id: 1 select_type: SIMPLE table: message ...

    1. limit分页的原理

    先来看一下分页的基本原理

    mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20
    ***************** 1. row **************
    id: 1
    select_type: SIMPLE
    table: message
    type: index
    possible_keys: NULL
    key: PRIMARY
    key_len: 4
    ref: NULL
    rows: 10020
    Extra:
    1 row in set (0.00 sec)

    对上面的mysql语句说明:limit 10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里,如果是limit 100000,100,需要扫描100100行,在一个高并发的应用里,每次查询需要扫描超过10W行,性能肯定大打折扣。

    2. 优化方法

    2.1 最大ID法

    举个例子,我查询第一页的时候是limit 0,10 查询到的最后一条id是10,那么下一页的查询只需要查询id大于10的19条数据即可。

    explain select * from user where id > 4000000  limit 10;

    如果谁再问你“如何优化mysql分页查询”,请把这篇文章甩给他

    我们看到rows=1949780,这个表示可能扫描了这么多行,这个行数是因为扫描id>400w的记录,后面还有两百万,而这里表示可能扫描了1949780行,但是由于limit 10的存在,所以扫描了10行之后就停止扫描了,我们也可以对比一下使用这种方式和直接使用limit 4000000,10的效率,千差万别。但是这种比较局限,只能适用于自增组件,uuid生成的主键这种方式不适用。

    2.2 BETWEEN … AND

    select * from user where id BETWEEN 4000000 and 4000010

    如果谁再问你“如何优化mysql分页查询”,请把这篇文章甩给他

    如果谁再问你“如何优化mysql分页查询”,请把这篇文章甩给他

    这种方式也只能适用于自增主键,并且id没有断裂,否者不推荐这种方式,我们发现使用BETWEEN AND的时候查询出来11条记录,也就是说BETWEEN AND包含了两边的边间条件。使用的时候需要特别注意一下。

    2.3 利用覆盖索引优化

    我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

    2.3.1 只有主键id

    在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。

    select id from product limit 866613

    这次我们直接查询最后一页的数据(利用覆盖索引,只包含id列)耗时0.2秒,相对于查询了所有列的37.44秒,提升了大概100多倍的速度。

    那么如果要查询所有列有两种方法,

    • id>=的形式:SELECT * FROM product WHERE ID >= (select id from product limit 866613, 1) limit 20
    • 利用join:SELECT * FROM product a INNER JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

    2.3.2 利用复合索引

    利用主键id虽然查询效率很高,但是通常我们的sql是带有各种查询条件的。假设有个数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text,id 是主键,vtype是tinyint,vtype有索引。

    当有10万级以上的数据量时,执行 select id from collect where vtype=1 order by id limit 90000,10 时,虽然vtype字段有索引,大概耗时还是会在8~9秒。

    这里我们需要设计一个复合索引cmp_idx(vtype, id),再次执行select id from collect where vtype=1 limit 90000,10;耗时会降低到大概0.04秒左右!

    如果对于有where 条件,又想走索引用limit的,必须设计一个复合索引,将where条件的字段放第一位,limit用到的主键放第2位,而且只能select 主键!

    2.4 分表

    MySQL推荐一张表的存储不要超过500w数据。如果在100万以内的数据,利用2.3.2的复合索引方式,还可以支撑limit分页查询。300万以内就很勉强,需要进行分表设计。

    • 按时间分表:对于订单流水数据,可以考虑按时间分表,因为通常用户对最近一定时间内的数据比较感兴趣,可能经常查询使用,而超过一定时间的历史数据就很少查询。按时间分表,可以排除大量的历史数据,提升查询效率。
    • 垂直分表:对于表的字段超多,而且还有很多text类型的字段,这个时候我们可以将占用空间比较小的字段分在一张表,占用空间比较大的字段分在另一张表,两张表一一关联,这样,查询的时候就会快很多了。
    • 水平分表:按一定分表规则将数据拆分到多个表里,每个表数据量减少,自然查询效率就提升了。
    展开全文
  • mysql 大数据量分页查询的时候可以使用子查询方式优化 select * from table_a a join (select id from table_a limit 1000000, 20) b on a.id = b.id ;   转载于:...
  • mysql大数据量分页查询优化技巧

    万次阅读 2017-04-17 16:23:23
    PS:我们知道mysql使用Limit实现了分页查询,在我们平常的Web开发中常用一些通用的分页查询jar包,比如说有名的中国开发者实现的java Pagehelper(Github地址https://github.com/pagehelper/Mybatis-PageHelper),...
  • 其实对于大数据量分页,你给他一个几十页的数据浏览,已经够了,没必要每次分页都去源数据库,不停的找,前几页还好,愈往后查询效率肯定也会慢慢变差,所以来说,建立一个空间保存搜索结果比较合适,不过还是看你是否真的...
  • 分页的2种写法 SELECT keyword FROM keyword_rank WHERE advertiserid='59' order by keyword LIMIT 2 OFFSET 1; 比如这个SQL ,limit后面跟的是2条数据,offset后面是从第1条开始读取 SELECT keyword FROM ...
  • 适应场景:适用于数据量较少的情况(元组百/千级) 原因/缺点:全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃。 ...
  • 传统的mysql分页查询 ...特别是上线后数据量积累比较快,必须重视SQL优化,否则影响系统运行和用户使用体验 性能实验 直接用limit start, count分页语句, 也是我程序中用的方法: select * from table lim
  • MySQL大数据量分页查询方法及其优化
  • 当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询。对于数据库分页查询,也有很多种方法和优化的点。下面简单说一下我知道的一些...
  • 大数据量分页查询优化方案欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、...
  • MYSQL百万级数据分页查询优化实战

    万次阅读 2020-04-07 15:47:58
    MYSQL大数据量分页查询优化 一、记一次mysql分页查询优化 最近项目中,需要将公司老的订单日志数据迁移到新的ElasticSearch统一日志存储,我们老日志数据是分库分表存储在mysql数据库中(按天分表),单表数据量在500...
  • 当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询。对于数据库分页查询,也有很多种方法和优化的点。下面简单说一下我知道的一些...
  • 一般我们数据量大的时候,然后就需要进行分页,一般分页语句就是limit offset,rows。这种分页数据量小的时候是没啥影响的,一旦数据量越来越随着offset的变,性能...
  • 主要介绍了MySQL百万级数据量分页查询方法及其优化建议,帮助大家更好的处理MySQL数据库,感兴趣的朋友可以了解下
  • 我们平常写分页sql,一般都会分两次查询,一次查询结果,一次查询总行数。下面这条sql做了一些优化,只需要查询一次。代码如下: declare @nGotoPage int,@nPageSize int; set @nGotoPage = 1; set @nPageSize = ...
  • MySQL大数据量分页性能优化 mysql大数据量使用limit分页,随着页码的增大,查询效率越低下。 测试实验 1. 直接用limitstart,count分页语句,也是我程序中用的方法: select*fromproductlimitstart,count 当起始...
  • 点击上方Java后端,选择设为星标优质文章,及时送达作者|悠悠icnblogs.com/youyoui/p/7851007.html当需要从数据库查询的表有上万条记录的时候,一次性查...
  • MySQL大数据量分页查询方法及其优化 ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N ---适应场景: 适用于数据量较少的情况(元组百/千级) ---原因/缺点...
  • 大数据量实时统计排序分页查询(并发数较小时) 的瓶颈不是函数(count,sum等)执行, 不是having, 也不是order by,甚至不是表join, 导致慢的原因就在于“数据量本身” 化整为零 就是将表划分为M份相互独立的...
  • MySQL百万级数据量分页查询方法及其优化 MySQL分页查询优化 重点提一下: 在优化分页语句过程中,一定注意查询的语句添加排序字段,一定自己建表实践,有的可能不一定有效果哦 比如下面这两个语句,返回的结果就...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 68,744
精华内容 27,497
关键字:

大数据量分页查询优化