Mysql中分页查询两个方法比较

mysql中分页查询有两种方式, 一种是使用COUNT(*)的方式,具体代码如下


1

2

3

SELECT COUNT(*) FROM foo WHERE b = 1;

 

SELECT a FROM foo WHERE b = 1 LIMIT 100,10;

1

  

另外一种是使用SQL_CALC_FOUND_ROWS


1

2

SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10;

SELECT FOUND_ROWS();

 

第二种方式调用SQL_CALC_FOUND_ROWS之后会将WHERE语句查询的行数放在FOUND_ROWS()之中，第二次只需要查询FOUND_ROWS()就可以查出有多少行了。

 

讨论这两种方法的优缺点：

首先原子性讲，第二种肯定比第一种好。第二种能保证查询语句的原子性，第一种当两个请求之间有额外的操作修改了表的时候，结果就自然是不准确的了。而第二种则不会。但是非常可惜，一般页面需要进行分页显示的时候，往往并不要求分页的结果非常准确。即分页返回的total总数大1或者小1都是无所谓的。所以其实原子性不是我们分页关注的重点。

 

下面看效率。这个非常重要，分页操作在每个网站上的使用都是非常大的，查询量自然也很大。由于无论哪种，分页操作必然会有两次sql查询，于是就有很多很多关于两种查询性能的比较：

SQL_CALC_FOUND_ROWS真的很慢么？

http://hi.baidu.com/thinkinginlamp/item/b122fdaea5ba23f614329b14

To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

老王这篇文章里面有提到一个covering index的概念，简单来说就是怎样才能只让查询根据索引返回结果，而不进行表查询

具体看他的另外一篇文章：

MySQL之Covering Index

http://hi.baidu.com/thinkinginlamp/item/1b9aaf09014acce0f45ba6d3

 

实验

结合这几篇文章，做的实验：

表：


1

2

3

4

5

6

7

CREATE TABLE IF NOT EXISTS `foo` (

`a` int(10) unsigned NOT NULL AUTO_INCREMENT,

`b` int(10) unsigned NOT NULL,

`c` varchar(100) NOT NULL,

PRIMARY KEY (`a`),

KEY `bar` (`b`,`a`)

) ENGINE=MyISAM;

 

 

注意下这里是使用b,a做了一个索引，所以查询select * 的时候是不会用到covering index的，select a才会使用到covering index


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

<?php

 

$host = '192.168.100.166';

$dbName = 'test';

$user = 'root';

$password = '';

 

$db = mysql_connect($host, $user, $password) or die('DB connect failed');

mysql_select_db($dbName, $db);

 

 

echo '==========================================' . "\r\n";

 

$start = microtime(true);

for ($i =0; $i<1000; $i++) {

    mysql_query("SELECT SQL_NO_CACHE COUNT(*) FROM foo WHERE b = 1");

    mysql_query("SELECT SQL_NO_CACHE a FROM foo WHERE b = 1 LIMIT 100,10");

}

$end = microtime(true);

echo $end - $start . "\r\n";

 

echo '==========================================' . "\r\n";

 

$start = microtime(true);

for ($i =0; $i<1000; $i++) {

    mysql_query("SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10");

    mysql_query("SELECT FOUND_ROWS()");

}

$end = microtime(true);

echo $end - $start . "\r\n";

 

echo '==========================================' . "\r\n";

 

$start = microtime(true);

for ($i =0; $i<1000; $i++) {

    mysql_query("SELECT SQL_NO_CACHE COUNT(*) FROM foo WHERE b = 1");

    mysql_query("SELECT SQL_NO_CACHE * FROM foo WHERE b = 1 LIMIT 100,10");

}

$end = microtime(true);

echo $end - $start . "\r\n";

 

echo '==========================================' . "\r\n";

 

$start = microtime(true);

for ($i =0; $i<1000; $i++) {

    mysql_query("SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM foo WHERE b = 1 LIMIT 100, 10");

    mysql_query("SELECT FOUND_ROWS()");

}

$end = microtime(true);

echo $end - $start . "\r\n";

返回的结果：

和老王里面文章说的是一样的。第四次查询SQL_CALC_FOUND_ROWS由于不仅是没有使用到covering index，也需要进行全表查询，而第三次查询COUNT(*)，且select * 有使用到index，并没进行全表查询，所以有这么大的差别。

 

总结

PS： 另外提醒下，这里是使用MyISAM会出现三和四的查询差别这么大，但是如果是使用InnoDB的话，就不会有这么大差别了。

所以我得出的结论是如果数据库是InnoDB的话，我还是倾向于使用SQL_CALC_FOUND_ROWS

 

结论：SQL_CALC_FOUND_ROWS和COUNT(*)的性能在都使用covering index的情况下前者高，在没使用covering index情况下后者性能高。所以使用的时候要注意这个。

时间： 2024-09-18 12:21:34

Mysql中分页查询两个方法比较的相关文章

Mysql中分页查询的两个解决方法比较_php实例

mysql中分页查询有两种方式, 一种是使用COUNT(*)的方式,具体代码如下 复制代码 代码如下: SELECT COUNT(*) FROM foo WHERE b = 1; SELECT a FROM foo WHERE b = 1 LIMIT 100,10; 另外一种是使用SQL_CALC_FOUND_ROWS 复制代码 代码如下: SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10; SELECT FOUND_

mysql-MySQL中关联查询两个表的问题，在线等！！！急急急

问题描述 MySQL中关联查询两个表的问题,在线等!!!急急急 两个表的关联查询,却出现的笛卡尔积的问题,所以查出来的数据并不是我想要的 该怎么办呀 解决方案 你怎么查询的,关联的一侧不是主键或者唯一值,那么就是笛卡尔集. 解决方案二: 换言之,如果是N:N查询,那么所有匹配的组合都会被查询出来.确保你的查询是1:N N:1或者1:1 如果你一侧不是1,那么可以用distinct或者group by先变成唯一的. 解决方案三: 关联条件是如何做的 是不是有正确的join起来 解决方案四: 使用f

MySQL删除数据库的两种方法_Mysql

本文为大家分享了两种MySQL删除数据库的方法,供大家参考,具体内容如下 第一种方法:使用 mysqladmin 删除数据库使用普通用户登陆mysql服务器,你可能需要特定的权限来创建或者删除 MySQL 数据库. 所以我们这边使用root用户登录,root用户拥有最高权限,可以使用 mysql mysqladmin 命令来创建数据库. 在删除数据库过程中,务必要十分谨慎,因为在执行删除命令后,所有数据将会消失. 以下实例删除数据库TUTORIALS(该数据库在前一章节已创建): [root@h

mysql中模糊查询的四种用法介绍_Mysql

下面介绍mysql中模糊查询的四种用法: 1,%:表示任意0个或多个字符.可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示. 比如 SELECT * FROM [user] WHERE u_name LIKE '%三%' 将会把u_name为"张三","张猫三"."三脚猫","唐三藏"等等有"三"的记录全找出来. 另外,如果需要找出u_name中既有"三"又有

mongoDB分页的两种方法(图例)_MongoDB

mongoDB分页的两种方法mongoDB的分页查询是通过limit(),skip(),sort()这三个函数组合进行分页查询的下面这个是我的测试数据db.test.find().sort({"age":1}); 第一种方法查询第一页的数据:db.test.find().sort({"age":1}).limit(2); 查询第二页的数据:db.test.find().sort({"age":1}).skip(2).limit(2); 查询其他页

mysql中模糊查询的四种用法介绍

下面介绍mysql中模糊查询的四种用法: 1,%:表示任意0个或多个字符.可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示. 比如 SELECT * FROM [user] WHERE u_name LIKE '%三%' 将会把u_name为"张三","张猫三"."三脚猫","唐三藏"等等有"三"的记录全找出来. 另外,如果需要找出u_name中既有"三"又有

MySQL的分页查询语句limit

问题描述 MySQL的分页查询语句limit 假设,一个表中有一百条数据,我要查询第5页,每页10条数据,SQL语句怎么写?是用limit么? 不用涉及到Java语言,就SQL语句 解决方案 SELECT * FROM table LIMIT 51,10; 解决方案二: 查询第m页,每页n条数据 SELECT * FROM tablename LIMIT (m-1)*n,n 解决方案三: SELECT * FROM tablename LIMIT 5,10 解决方案四: SELECT * FRO

分页查询 索引-分页查询两种方式结果却不同

问题描述 分页查询两种方式结果却不同 select * from emp where rowid in (select rid from (select rownum rn ,rid from (select rowid rid from emp ) where rownum where rn>4) order by deptno; select * from (select t.*,rownum rn from (select * from emp ) t where rownum )wher

mysql中同时查两个表中的前2000条

问题描述 mysql中同时查两个表中的前2000条 比方说我有两个结构一样的表,但是由于各种原因并不能合并. 其中有一个create_time 字段记录了时间 我想同时查出两个表中时间大于某个时间(例:2016-04-01 00:00:00)的前2000条数据 要求是查出来一共2000条,而不是每个表查2000条出来. 求大神!谢谢! 解决方案 SELECT CREATE_TIME FROM (SELECT 字段1,字段2 from 表1 where create_time>某个时间 union