我使用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>

10-05 20:28
查看更多