vlambda博客
学习文章列表

mysql大量数据分页优化


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

  1. 准备数据

    1. 建一个测试表引擎为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;
  1. 写一个批量插入的存储过程

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;ENDCALL sp_test_batch(10000000,10000);
 

插入1000w数据

  1. 测试性能                                                                                                     下面我们分别针对于offset等于不同的值来进行实:offset等于10000时耗时


  • offset等于100000时耗时


mysql大量数据分页优化


  • offset等于1000000时耗时


mysql大量数据分页优化

  • offset等于5000000时耗时


mysql大量数据分页优化


  • offset等于10000000时耗时


mysql大量数据分页优化


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

4 .进行优化

   子查询的分页方式:


mysql大量数据分页优化


mysql大量数据分页优化

SELECT * FROM user WHERE id >= (SELECT id FROM user ORDER BY id LIMIT 9000000, 1) LIMIT 10

从图可以得出子查询确实速度快了一倍。

JOIN分页方式:

SELECT * FROM user t1 INNER join
(SELECT id FROM user ORDER BY id LIMIT 9000000, 10) t2 on t2.id =t1.id

mysql大量数据分页优化

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

终极优化:

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

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


近期文章




        
          
          
        
 扫描以下二维码

回复“Linux”获取博主1.4万字Linux实战手册(新)

回复“面试”获取博主15万字Java面试题