我使用MySQL 5.6.19-log(根据select version()
)。
我有一个InnoDB表如下:
CREATE TABLE `mytable` (
`foo_id` bigint(20) NOT NULL,
`bar_id` bigint(20) NOT NULL,
`baz_id` bigint(20) NOT NULL,
PRIMARY KEY (`foo_id`,`bar_id`,`baz_id`)
)
此表适用于以下查询:
select
foo_id,
min(bar_id)-1
from
mytable
where
foo_id IN (
1000,2000
)
group by
foo_id;
+----+-------------+----------------------+-------+------------------------+---------+---------+------+-------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+------------------------+---------+---------+------+-------+---------------------------------------+
| 1 | SIMPLE | mytable | range | PRIMARY,bar_id_idx | PRIMARY | 8 | NULL | 58245 | Using where; Using index for group-by |
+----+-------------+----------------------+-------+------------------------+---------+---------+------+-------+---------------------------------------+
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00036575 | select foo_id, min(bar_id)-1 from mytable where foo_id IN (1000,2000) group by foo_id |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
但是,当where子句中
foo_id
的数目只有一个时,查询会变得相当慢,如下所示:select
foo_id,
min(bar_id)-1
from
mytable
where
foo_id = 1000
group by
foo_id;
+----+-------------+----------------------+------+------------------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+------------------------+---------+---------+-------+--------+-------------+
| 1 | SIMPLE | mytable | ref | PRIMARY,bar_id_idx | PRIMARY | 8 | const | 873664 | Using index |
+----+-------------+----------------------+------+------------------------+---------+---------+-------+--------+-------------+
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
| 1 | 0.07258075 | select foo_id, min(bar_id)-1 from mytable where foo_id = 1000 group by foo_id |
+----------+------------+-----------------------------------------------------------------------------------------------------------------+
我认为MySQL的查询计划器出了问题。当
foo_id
的数目只有一个时,是否有任何提示或其他东西强制MySQL使用index for group by?我试过了,但没用。我知道当
analyze table mytable
的数目只有一个时,查询select min(bar_id)-1 from mytable where foo_id = 1000
很快,但它会分支到我的应用程序的代码,所以我想避免这样做。 最佳答案
这不是答案。这只是我评论的一个样本
样品
mysql> EXPLAIN select
-> foo_id,
-> min(bar_id)-1
-> from
-> mytable
-> where
-> foo_id IN (
-> 1000,2000
-> )
-> group by
-> foo_id;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | mytable | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 2 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)
mysql>
mysql> EXPLAIN select
-> foo_id,
-> min(bar_id)-1
-> from
-> mytable
-> where
-> foo_id = 1000
-> group by
-> foo_id;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ref | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.14 |
+-----------+
1 row in set (0,00 sec)
mysql> SHOW CREATE TABLE mytable;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mytable | CREATE TABLE `mytable` (
`foo_id` bigint(20) NOT NULL AUTO_INCREMENT,
`bar_id` bigint(20) NOT NULL,
`baz_id` bigint(20) NOT NULL,
PRIMARY KEY (`foo_id`,`bar_id`,`baz_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12804 DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)
mysql>