MySQL select 特定列比 select * 慢

Question

我的MySQL不强，所以请原谅任何菜鸟错误。简短版本：

SELECT locId,count,avg FROM destAgg_geo 显着慢于 SELECT * from destAgg_geo

prtt.destAgg 是一个以 dst_ip 为键的表（主）

mysql> describe prtt.destAgg;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| dst_ip  | int(10) unsigned | NO   | PRI | 0       |       |
| total   | float unsigned   | YES  |     | NULL    |       |
| avg     | float unsigned   | YES  |     | NULL    |       |
| sqtotal | float unsigned   | YES  |     | NULL    |       |
| sqavg   | float unsigned   | YES  |     | NULL    |       |
| count   | int(10) unsigned | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+

geoip.blocks 是一个以 startIpNum 和 endIpNum 为键的表（主要）

mysql> describe geoip.blocks;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| startIpNum | int(10) unsigned | NO   | MUL | NULL    |       |
| endIpNum   | int(10) unsigned | NO   |     | NULL    |       |
| locId      | int(10) unsigned | NO   |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

destAgg_geo 是一个视图：

CREATE VIEW destAgg_geo AS SELECT * FROM destAgg JOIN geoip.blocks 
  ON destAgg.dst_ip BETWEEN geoip.blocks.startIpNum AND geoip.blocks.endIpNum;

这是 select * 的优化计划：

mysql> explain select * from destAgg_geo;
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                          |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | blocks  | ALL  | start_end     | NULL | NULL    | NULL | 3486646 |                                                |
|  1 | SIMPLE      | destAgg | ALL  | PRIMARY       | NULL | NULL    | NULL |  101893 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+

这是带有特定列的 select 的优化计划：

mysql> explain select locId,count,avg from destAgg_geo;
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                          |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | destAgg | ALL  | PRIMARY       | NULL | NULL    | NULL |  101893 |                                                |
|  1 | SIMPLE      | blocks  | ALL  | start_end     | NULL | NULL    | NULL | 3486646 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+

这是 destAgg 中每一列的优化计划，仅是 geoip.blocks 中 locId 列的优化计划：

mysql> explain select dst_ip,total,avg,sqtotal,sqavg,count,locId from destAgg_geo;
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                          |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | blocks  | ALL  | start_end     | NULL | NULL    | NULL | 3486646 |                                                |
|  1 | SIMPLE      | destAgg | ALL  | PRIMARY       | NULL | NULL    | NULL |  101893 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+

删除除 dst_ip 之外的任何列并且范围检查会翻转到块：

mysql> explain select dst_ip,avg,sqtotal,sqavg,count,locId from destAgg_geo;
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                          |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | destAgg | ALL  | PRIMARY       | NULL | NULL    | NULL |  101893 |                                                |
|  1 | SIMPLE      | blocks  | ALL  | start_end     | NULL | NULL    | NULL | 3486646 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+

这会慢得多。这是怎么回事？

（是的，我可以只使用 * 查询结果和过程，但我想知道发生了什么以及为什么）

编辑 - 对 VIEW 查询进行解释：

mysql> explain SELECT * FROM destAgg JOIN geoip.blocks ON destAgg.dst_ip BETWEEN geoip.blocks.startIpNum AND geoip.blocks.endIpNum;
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                          |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | blocks  | ALL  | start_end     | NULL | NULL    | NULL | 3486646 |                                                |
|  1 | SIMPLE      | destAgg | ALL  | PRIMARY       | NULL | NULL    | NULL |  101893 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+

Answer 1

MySQL 可以告诉您是否对两个查询都运行 EXPLAIN PLAN。

第一个包含列的查询不包含任何键列，所以我的猜测是它必须执行表扫描。

第二个带有“SELECT *”的查询包含主键，因此可以使用索引。

Answer 2

最后应用范围过滤器，因此问题在于查询优化器在一种情况下选择首先连接较大的表，而在另一种情况下选择首先连接较小的表。也许对优化器有更多了解的人可以告诉我们为什么它以不同的顺序连接每个表。

我认为这里真正的目标应该是尝试让 JOIN 使用索引，因此连接的顺序并不那么重要。

Answer 3

我会尝试在 locId、count、avg 上放置一个复合索引，看看这是否不会提高速度。