如何强制 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>

Answer 2

尝试

select
foo_id,
min(bar_id)-1 
from
    mytable 
where
    foo_id LIKE 1000 
group by
    foo_id;