mysql - 为什么 MySQL 不为这个选择查询使用索引？

Question

为什么这个查询这里不使用索引呢？该表使用 InnoDB 引擎。

explain SELECT null as id,
    ->  up_time,
    ->  reg_date,
    ->  refer,
    ->  MAX(IFNULL(visits_count,0)) as visits_count,
    ->  MAX(IFNULL(register_count,0)) as register_count,
    ->  MAX(IFNULL(players_count,0)) as players_count,
    ->  MAX(IFNULL(activity_count,0)) as activity_count,
    ->  MAX(IFNULL(payment_users_count,0)) as payment_users_count,
    ->  MAX(IFNULL(payment_count,0)) as payment_count,
    ->  MAX(IFNULL(payment_sum,0)) as payment_sum FROM stats_refers
    ->
    ->  WHERE
    ->
    ->  stats_refers.reg_date < 1435006800
    ->  AND stats_refers.up_time < 1435006800
    ->
    ->  GROUP BY stats_refers.refer, stats_refers.reg_date;

和解释:

+----+-------------+--------------+------+----------------------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table        | type | possible_keys              | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+--------------+------+----------------------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | stats_refers | ALL  | reg_date,stat,up_reg_index | NULL | NULL    | NULL | 2983126 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+----------------------------+------+---------+------+---------+----------------------------------------------+

以及可以使用的键:

+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| stats_refers |          0 | PRIMARY      |            1 | id          | A         |     2983126 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          0 | reg_date     |            1 | reg_date    | A         |       13317 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          0 | reg_date     |            2 | up_time     | A         |     1491563 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          0 | reg_date     |            3 | refer       | A         |     2983126 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          1 | stat         |            1 | reg_date    | A         |       15142 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          1 | stat         |            2 | refer       | A         |       28683 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          1 | refer_uptime |            1 | refer       | A         |        2307 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          1 | refer_uptime |            2 | up_time     | A         |     1491563 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          1 | up_reg_index |            1 | reg_date    | A         |        2314 |     NULL | NULL   |      | BTREE      |         |               |
| stats_refers |          1 | up_reg_index |            2 | up_time     | A         |     1491563 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

这是表的描述:

CREATE TABLE `stats_refers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `reg_date` int(10) unsigned NOT NULL,
  `up_time` int(10) unsigned NOT NULL,
  `refer` varchar(16) NOT NULL DEFAULT '',
  `visits_count` int(10) unsigned NOT NULL DEFAULT '0',
  `register_count` int(10) unsigned NOT NULL DEFAULT '0',
  `players_count` int(10) unsigned NOT NULL DEFAULT '0',
  `activity_count` int(10) unsigned NOT NULL DEFAULT '0',
  `payment_users_count` int(10) unsigned NOT NULL DEFAULT '0',
  `payment_count` int(10) unsigned NOT NULL DEFAULT '0',
  `payment_sum` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `reg_date` (`reg_date`,`up_time`,`refer`),
  KEY `stat` (`reg_date`,`refer`),
  KEY `refer_uptime` (`refer`,`up_time`),
  KEY `up_reg_index` (`reg_date`,`up_time`)
) ENGINE=InnoDB AUTO_INCREMENT=4136504 DEFAULT CHARSET=utf8 |

+----+-------------+--------------+-------+-----------------------------------+-------------+---------+------+---------+-------------+
| id | select_type | table        | type  | possible_keys                     | key         | key_len | ref  | rows    | Extra       |
+----+-------------+--------------+-------+-----------------------------------+-------------+---------+------+---------+-------------+
|  1 | SIMPLE      | stats_refers | index | reg_date,up_time,reg,search_index | group_index | 54      | NULL | 3011896 | Using where |
+----+-------------+--------------+-------+-----------------------------------+-------------+---------+------+---------+-------------+

Answer 1

复合索引 stat，我假设它是为这个分组操作而创建的，它的顺序与您尝试执行的分组操作相反。要使索引可用于此分组操作，您需要执行以下操作之一:

• 反转 stat 索引的列顺序
• 在refer上添加一个单独的索引
• 在 refer, reg_date 添加另一个复合索引

你做出什么决定最终需要考虑对表的其他查询操作。

您可能需要更广泛地考虑此处的索引使用情况。虽然使用复合索引可以提高性能，而不是为可能需要在表的查询范围内使用的每个字段使用单独的索引，但在您的情况下，您正在以表描述中的不同组合复制相同字段的索引。在不了解所有查询用例的情况下，您可能很难查看整体索引建议，但我只是想指出您可能需要考虑一下。

例如，您的 up_reg_index 显然根本不需要，因为该索引已包含在 reg_date 唯一索引中。您可能最好使用这样的一组索引:

PRIMARY KEY (`id`),
UNIQUE KEY `regdate_uptime_refer` (`reg_date`,`up_time`,`refer`),
KEY `reg_date` (`reg_date`),
KEY `refer` (`refer`),
KEY `up_time` (`up_time`),

这肯定会比您当前拥有的索引需要更少的空间，并且在如何对这些列进行过滤/加入/分组方面会更加灵活，但不要将此作为坚定的建议。针对您的不同查询场景测试不同索引场景的性能(特别是插入性能，如果这是一个用例)。