mysql - 如何强制MySQL "Using index for group-by"

Question

我使用 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 使用索引进行分组？我试过 analyze table mytable 但没有用。

我知道当 foo_id 的数量只有一个时，查询 select min(bar_id)-1 from mytable where foo_id = 1000 很快，但它使分支到我的应用程序代码，所以我想避免这种情况。

Answer 1

这不是答案。这只是我评论中的一个示例

示例

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>