sql-嵌套查询的计数速度还快吗?

cngwdvgl  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(348)

假设我有一个问题如下:

SELECT message.mid
FROM message
WHERE message.mid <= 100

据我所知,如果将查询更改为以下内容,它将执行得更快,因为列没有展开。

SELECT COUNT(message.mid)
FROM message
WHERE message.mid <= 100

但是下面的查询也有同样的好处吗?还会像现在这么快吗?

SELECT COUNT(*)
FROM (
    SELECT message.mid,
           message.something,
           message.something2,
           message.something3,
    FROM message
    WHERE message.mid <= 100
) AS A
olmpazwi

olmpazwi1#

COUNT(*) 说数数行数。 COUNT(x) 说要数一数 x IS NOT NULL . 所以稍微慢一点,可能有不同的答案。 SELECT mid (与 SELECT COUNT(...) )——更慢更笨重。它正在返回 mid ,而不仅仅是一个数字。 SELECT COUNT(..) FROM ( SELECT ... ) --要慢得多(在较旧的mysql版本中),因为它必须用子查询的结果生成一个temp表。也, COUNT 只是一个简单的数字;子查询正在收集大量行。
如果 mid 索引(包括 PRIMARY KEY ,那么 WHERE mid <= 100 是索引(或表)的“范围”扫描。也就是说,它只触及部分行。
如果 mid 如果没有索引,则将扫描整个表—因此速度较慢。

wztqucjr

wztqucjr2#

我们可以问mysql它会做什么。这是5.7。

mysql> explain SELECT COUNT(*) FROM (     SELECT message.mid     FROM message     WHERE message.mid <= 100 
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | message | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |  100 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT count(message.mid)     FROM message     WHERE message.mid <= 100;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | message | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |  100 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

看起来是一样的。mysql优化了子查询。
下面是一个mysql没有优化子查询的例子。

mysql> explain SELECT * FROM (     SELECT message.mid     FROM message where mid < 100 group by mid) m;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   99 |   100.00 | NULL                     |
|  2 | DERIVED     | message    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   99 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

“优化派生表和视图引用”中有一些例子说明了这种优化是如何工作的。
例1:

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

通过合并,该查询的执行方式类似于:

SELECT * FROM t1;

该页面概述了mysql用来提高子查询效率的许多其他优化技巧。

相关问题