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

发布时间:2020-06-18编辑:脚本学堂
本文介绍下,对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的奥秘