mysql海量数据分页查询优化


背景

一般我们数据量大的时候,然后就需要进行分页,一般分页语句就是limit offset,rows。这种分页数据量小的时候是没啥影响的,一旦数据量越来越大随着offset的变大,性能就会越来越差。下面我们就来实验下:

准备数据

  • 建一个测试表引擎为MyISAM(插入数据没有事务提交,插入速度快)的表。
CREATE TABLE USER (
id INT ( 20 ) NOT NULL auto_increment,
NAME VARCHAR ( 20 ) NOT NULL,
address VARCHAR ( 20 ) NOT NULL,
PRIMARY KEY ( id ) 
) ENGINE = MyISAM;
  • 写一个批量插入的存储过程
delimiter //
# 删除表数据
TRUNCATE TABLE t;
# 如果已经有sp_test_batch存储过程,将其删除,后面重新创建
DROP PROCEDURE IF EXISTS sp_test_batch;
# 创建存储过程,包含num和batch输入,num表示插入的总行数,batch表示每次插入的行数
CREATE PROCEDURE sp_test_batch(IN num INT,IN batch INT)
BEGIN
    SET @insert_value = '';
    # 已经插入的记录总行数
  SET @count = 0;
    # 
    SET @batch_count = 0;
    WHILE @count < num DO
        # 内while循环用于拼接INSERT INTO t VALUES (),(),(),...语句中VALUES后面部分
        WHILE (@batch_count < batch AND @count < num) DO
            IF @batch_count>0
            THEN 
                SET @insert_value = concat(@insert_value,',');
            END IF;
            SET @insert_value = concat(@insert_value,"('name", @count, "','address", @count, "')");
            SET @batch_count = @batch_count+1;    
        END WHILE;

        SET @count = @count + @batch_count;
        # 拼接SQL语句并执行
        SET @exesql = concat("insert into user(name,address) values ", @insert_value);    
        PREPARE stmt FROM @exesql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        # 重置变量值
        SET @insert_value = '';
        SET @batch_count=0;
    END WHILE;
    # 数据插入完成后,查看表中总记录数
    SELECT COUNT(id) FROM user;
END
  • 插入100w数据

    测试性能

    下面我们分别针对于offset等于不同的值来进行实验:

    • offset等于10000时耗时
      在这里插入图片描述
      • offset等于100000时耗时:
        在这里插入图片描述
      • offset等于1000000时耗时
        在这里插入图片描述
  • offset等于5000000时耗时
    在这里插入图片描述

  • offset等于10000000时耗时
    在这里插入图片描述
    从上图可以得出随着offset的值越大耗时就越来越多。这还只是1000w数据,如果我们上亿数据呢,可想而知这时候查询的效率有多差。下面我们来进行优化。

    性能优化

    子查询的分页方式:

    在这里插入图片描述
    在这里插入图片描述

    SELECT * FROM user WHERE  id >=  
    (SELECT id FROM user  ORDER BY id LIMIT 9000000, 1) LIMIT 10
    从图可以得出子查询确实速度快了一倍。
    
#### JOIN分页方式:
```sql
SELECT * FROM user t1 INNER join
(SELECT id FROM user  ORDER BY id LIMIT 9000000, 10) t2 on t2.id =t1.id

在这里插入图片描述

  • join的方式比子查询性能在稍微好点。

依赖当前页ID优化:

这个时间性能是最好的。这种优化必须要依赖前一次的查询的最大ID,如果是那种分页直接可以指定多少页的是不行的,必须是只能后一页,后一页这么点击。

SELECT id FROM user  where id > 9000000 ORDER BY id  LIMIT 10;

在这里插入图片描述

终极优化

  • 通过伪列对ID进行分页,可以多线程同时查询,这个适合分页把全量数据加载到缓存。
  • 得到ID的范围
    ​​在这里插入图片描述
select id from(
SELECT @rownum:=@rownum+1 AS rownum, id FROM   user as t1 ,(SELECT @rownum:=0) t2 order
by t1.id asc
) t3 where t3.rownum%5000=0

多个线程执行不同的sql

select * from user where id >0 and id<=5000 一直到最大的id

结束

  • 由于自己才疏学浅,难免会有纰漏,假如你发现了错误的地方,还望留言给我指出来,我会对其加以修正。
  • 如果你觉得文章还不错,你的转发、分享、赞赏、点赞、留言就是对我最大的鼓励。
  • 感谢您的阅读,十分欢迎并感谢您的关注。
    在这里插入图片描述

​​​​


文章作者: java金融
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 java金融 !
 上一篇
阿里四面你都知道吗? 阿里四面你都知道吗?
最近一个朋友跟我说,现在面试太难了,再也不是以前那种随便背几个面试题然后就能拿到offer的时候了。最近朋友准备换工作面试了阿里,然后和我交流了下他遇到的一些面试题,然后我整理了一下,然后就分享给有需要的朋友们顺便也查漏补缺一下。 一面1.
2020-03-09
下一篇 
10分钟带你入门git到github 10分钟带你入门git到github
git的产生背景开局先来一个故事吧,故事看完如果不想看枯燥无味的指令,没关系我已经把这篇文章的内容录制成了一个视频,点击**视频地址就可以观看。或者说你已经熟练掌握git的使用了,可以直接跳到总结部分(一个好玩的游戏**)去检验下你掌握的熟
2020-02-07