mysql大数据量之limit优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql大数据量之limit优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5659字，纯文字阅读大概需要9分钟。

内容图文

背景：当数据库里面的数据达到几百万条上千万条的时候，如果要分页的时候（不过一般分页不会有这么多），如果业务要求这么做那我们需要如何解决呢？
我用的本地一个自己生产的一张表有五百多万的表，来进行测试，表名为big_data;
首先我们看如下几条sql语句：
在这之前我们开启profiling来监测sql语句执行的情况。
set profiling=1;
1.查询从第10w条数据开始分页10条
2.查询从第20w条数据分页10条
3.查询从第30w条数据分页10条

3.查询从第300w条数据分页10条

3.查询从第500w条数据分页10条

我们可以看出查询从200w开始分页的都还比较快，但从500w开始速度就变的很慢了，这个是不太让人满意的。

mysql> select id,my_name from big_data limit 5000000,10;

+---------+------------+

| id      | my_name    |

+---------+------------+

| 5000001 | kwCwziqhNu |

| 5000002 | NLpqMMwaJv |

| 5000003 | kskUTLXDbx |

| 5000004 | PtAvBtpubZ |

| 5000005 | whsuShiuvX |

| 5000006 | TcDLWzHNQT |

| 5000007 | qHmnEkjsmh |

| 5000008 | UQrmluqvgr |

| 5000009 | UzKeqpEbtQ |

| 5000010 | SkuvSePMpq |

+---------+------------+

10 rows in set (2.34 sec)

mysql> show profiles;

+----------+------------+--------------------------------------------------+

| Query_ID | Duration   | Query                                            |

+----------+------------+--------------------------------------------------+

|        1 | 0.02591075 | select id,my_name from big_data limit 100000,10  |

|        2 | 0.05773150 | select id,my_name from big_data limit 200000,10  |

|        3 | 0.08253525 | select id,my_name from big_data limit 300000,10  |

|        4 | 1.38455375 | select id,my_name from big_data limit 3000000,10 |

|        5 | 2.34040775 | select id,my_name from big_data limit 5000000,10 |

+----------+------------+--------------------------------------------------+

5 rows in set, 1 warning (0.00 sec)

show  profiles；

我们就如下两种解决方法：
（1）、通过判断id的范围来分页
select  id,my_sn from big_data where id>5000000 limit 10;
也得到了分页的数据，但是我们发现如果id不是顺序的，也就是如果有数据删除过的话，那么这样分页数据就会不正确，这个是有缺陷的。
（2）、通过连接查询来分页
我们可以先查询500w条数据开始分页的那10个id，然后通过连接查询显示数据
mysql> select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 4500000,10) as  tmp on tmp.id=b.id;

我们测试不同起始端的分页数据

 

mysql> select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 5000000,10) as  tmp on tmp.id=b.id;

+---------+------------+

| id      | my_name    |

+---------+------------+

| 5000001 | kwCwziqhNu |

| 5000002 | NLpqMMwaJv |

| 5000003 | kskUTLXDbx |

| 5000004 | PtAvBtpubZ |

| 5000005 | whsuShiuvX |

| 5000006 | TcDLWzHNQT |

| 5000007 | qHmnEkjsmh |

| 5000008 | UQrmluqvgr |

| 5000009 | UzKeqpEbtQ |

| 5000010 | SkuvSePMpq |

+---------+------------+

10 rows in set (2.15 sec)

 

mysql> show profiles;

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                                                              |

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------+

|        1 | 0.02591075 | select id,my_name from big_data limit 100000,10                                                                                    |

|        2 | 0.05773150 | select id,my_name from big_data limit 200000,10                                                                                    |

|        3 | 0.08253525 | select id,my_name from big_data limit 300000,10                                                                                    |

|        4 | 1.38455375 | select id,my_name from big_data limit 3000000,10                                                                                   |

|        5 | 2.34040775 | select id,my_name from big_data limit 5000000,10                                                                                   |

|        6 | 0.00004200 | reset query cache                                                                                                                  |

|        7 | 0.01999275 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 100000,10) as  tmp on tmp.id=b.id  |

|        8 | 0.03888825 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 200000,10) as  tmp on tmp.id=b.id  |

|        9 | 0.37394450 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 1000000,10) as  tmp on tmp.id=b.id |

|       10 | 1.33475700 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 3000000,10) as  tmp on tmp.id=b.id |

|       11 | 2.14759000 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 5000000,10) as  tmp on tmp.id=b.id |

如果怀疑有缓存的缘故我们可以清楚缓存后来查询

reset query cache;

?
show profile for query 3;//查看被记录的第三条sql语句的执行情况
可以看出两种方法查出来的数据都是一致的，但通过方法二的速度比之前单表查询的速度快了一些。

分析：因为mysql分页查询是先把分页之前数据都查询出来了，然后截取后把不是分页的数据给扔掉后得到的结果这样，所以数据量太大了后分页缓慢是可以理解的。
但是我们可以先把需要分页的id查询出来，因为id是主键id主键索引，查询起来还是快很多的，然后根据id连接查询对应的分页数据，可见并不是所有的连接查询都会比
单查询要慢，要依情况而定。

 

mysql大数据量之limit优化

标签：

本文系统来源：http://www.cnblogs.com/lisqiong/p/5635009.html

内容总结

以上是互联网集市为您收集整理的mysql大数据量之limit优化全部内容，希望文章能够帮你解决mysql大数据量之limit优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---