本文介绍了mysql查询语句中有关limit与count的优化方法，优化limit的原则是尽量不要使用偏移量m，将limit m,n转换为limit n的形式，对于count有两种查找方法，一种是使用count，一种是使用sum函数。
mysql-limit/ target=_blank class=infotextkey>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语句的优化 （脚本学堂 编辑整理 www.jb200.com）
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函数。
sql语句：
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)
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)
