教程集 > 数据库 > mysql > 正文 mysql查询优化之limit和count的优化

mysql查询优化之limit和count的优化

发布时间：2015-12-15 编辑：jiaochengji.com

本文介绍下，对mysql进行查询优化的方法，这里介绍针对limit与count方面的优化，多实例探讨，相信对您一定有所帮助。建议参考学习下。

本节对mysql优化方法中的limit和count语句进行探讨，欢迎大家的关注与参与。

1，limit语句的优化。
常见的limit语句的形式为:LIMIT m,n;随之偏移量m的增大，limit语句的执行效率也跟着下降。
所以，优化limit的原则是尽量不要使用偏移量m，将limit m,n转换为limit n的形式，万一非要使用偏移量m，也要m尽可能的小。
现在，从表items表中，找出10000之后的10条记录。

一般的查找方法如下:

复制代码代码示例:

mysql> explain select ItemID,ItemName,Code from items  limit 10000,10;

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

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |

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

|  1 | SImpLE      | items | ALL  | NULL          | NULL | NULL    | NULL | 22116 |       |

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

1 row in set (0.00 sec)

索引类型为ALL,而且是全表扫描，影响的行数整张表的记录总数。可见是对全行数据进行偏移。
一个简单的技巧是对索引数据进行偏移，然后将索引数据与全行数据内联，得到所需的列。

语句如下：

复制代码代码示例:

mysql> explain select ItemID from items INNER JOIN (select ItemID from items order by ItemID LIMIT 10000,10) AS items2 USING(ItemID);

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

| id | select_type | table      | type   | possible_keys | key     | key_len | ref           | rows  | Extra       |

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

|  1 | PRIMARY     |  | ALL    | NULL          | NULL    | NULL    | NULL          |    10 |             |

|  1 | PRIMARY     | items      | eq_ref | PRIMARY       | PRIMARY | 4       | items2.ItemID |     1 | Using index |

|  2 | DERIVED     | items      | index  | NULL          | PRIMARY | 4       | NULL          | 10010 | Using index |

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

3 rows in set (0.00 sec)

上述查询影响的rows书只有10010，查询中对索引ItemID进行了排序。
还有一种查找方法，使用子查询，转换为limit n形式。

如下：

复制代码代码示例:

mysql> explain select ItemID,ItemName,Code from items where ItemID >= 10000  order by ItemID limit 10;

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

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |

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

|  1 | SImpLE      | items | range | PRIMARY       | PRIMARY | 4       | NULL | 13563 | Using where |

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

1 row in set (0.00 sec)

如果可以确定记录的具体位置，也可以使用between….and…来达到效果：

复制代码代码示例:

mysql> explain select ItemID,ItemName,Code FROM items where ItemID between 10000 and 10010 order by ItemID;

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

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

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

|  1 | SImpLE      | items | range | PRIMARY       | PRIMARY | 4       | NULL |    8 | Using where |

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

2 count语句的优化
count语句不好做优化，只有写特例，在没有使用where语句的count查询，非常快。也就是select count(*) from items
现在，查询items表中，ItemID大于2的所有记录。

sql语句如下：

复制代码代码示例:

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

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |

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

|  1 | SImpLE      | items | index | PRIMARY       | PRIMARY | 4       | NULL | 22114 | Using where; Using index |

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

1 row in set (0.00 sec)

对比如下查询：

复制代码代码示例:

mysql> explain  select (select count(*) from items) - count(*) from items where ItemID <= 2;

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

| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |

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

|  1 | PRIMARY     | items | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index     |

|  2 | SUBQUERY    | NULL  | NULL  | NULL          | NULL    | NULL    | NULL | NULL | Select tables optimized away |

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

2 rows in set (0.00 sec)

第一句查询影响了>2的所以行，第二句查询影响了<=2的所以记录。关键区别在(select count(*) from items) - count(*)来计算行数和where条件的不同。
还有一个有趣的地方，如果要统计同一列中不同值的记录数。如items表里产品上架(1)和下架(0)的不同数量。
有两种查找方法，一种是使用count，一种是使用sum函数。

如下：
1，

复制代码代码示例:

mysql> explain select count(isActive = '1' or null) as up,count(isActive = '0' or null) as down from items;

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

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |

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

|  1 | SImpLE      | items | ALL  | NULL          | NULL | NULL    | NULL | 22116 |       |

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

1 row in set (0.00 sec)

2，

复制代码代码示例:

mysql> explain select sum(if(isActive='1',1,0)) as up,sum(if(isActive='0',1,0)) as down from items;

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

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |

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

|  1 | SImpLE      | items | ALL  | NULL          | NULL | NULL    | NULL | 22116 |       |

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

1 row in set (0.00 sec)

有关mysql查询优化中，针对limit与count进行优化的分析，就介绍到这里了。
不太容易掌握吗？建议朋友们，好好琢磨下，最好是动手演练一番，会进步很快的。

您可能感兴趣的文章：
mysql limit用法及优化详解
学习MySQL数据分页查询(limit用法)
mysql优化之LIMIT,OFFSET分页
优化mysql的limit offset的例子
学习mysql中的limit分页优化
mysql分页limit速度太慢
mysql的limit offset,n的奥秘

您可能感兴趣的文章：
学习MySQL数据分页查询(limit用法)
MySQL limit查询优化的实例详解
 学习mysql中的limit分页优化
 实例详解mysql limit大数据量分页优化的方法
 mysql中limit、order by和group by的用法实例解析
 mysql分页limit速度太慢
 mysql limit用法及优化详解
 优化mysql的limit offset的例子
 mysql优化之LIMIT,OFFSET分页
 mysql分页limit优化方法

关键词： limit优化 mysql limit mysql查询优化 查询优化 　

上一篇：使用mysql二进制日志恢复mysql数据库的方法下一篇：linux下mysql的安装与配置过程详解

[关闭]

mysql查询优化之limit和count的优化

最近更新

浏览排行