mysql建表并为索引设置createtime(int)，但是select没有使用，为什么呢？

Question

mysql建表并为索引设置createtime(int)，但是select没有使用，为什么？

mysql> EXPLAIN SELECT
    -> player_id,
    -> COUNT(*) AS count_num,
    -> SUM( add_gold ) AS sum_add_gold
    -> FROM
    -> cloud_data_player_gold_log
    -> WHERE
    -> 1 = 1
    -> AND create_time >= 1561046400
    -> GROUP BY
    -> player_id
    -> ORDER BY
    -> sum_add_gold ASC
    -> LIMIT 0, 10;
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table                      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                        |
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | cloud_data_player_gold_log | NULL       | ALL  | create_time   | NULL | NULL    | NULL | 555659 |    44.47 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show index from cloud_data_player_gold_log;
+----------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| cloud_data_player_gold_log |          0 | PRIMARY     |            1 | log_id      | A         |      555659 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | channel_id  |            1 | channel_id  | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | game_id     |            1 | game_id     | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | game_id_2   |            1 | game_id     | A         |          12 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | game_id_2   |            2 | room_id     | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| cloud_data_player_gold_log |          1 | create_time |            1 | create_time | A         |       15356 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.04 sec)

表总数据行560000，sql运行时间超过0.6s

Answer 1

MySQL 假定大量行 (44%) 在 create_time >= 1561046400 之后。如果它使用索引，这意味着 MySQL 必须通过在表中来回跳转来读取表的一半。由于数据存储在 block 中(由多于 1 行组成)，它实际上会多次读取表大小，因为对于每个有用的行，它还会读取几个不需要的行(尽管其中很多可以通过缓存缓解)。

在这种情况下，从头到尾读取整个表一次并丢弃不需要的行会更快，MySQL 决定这样做。

您可以通过覆盖索引 (create_time, player_id, add_gold) 在索引中提供它需要的所有数据来防止 MySQL 读取实际的表数据。 。然后它就可以从 create_time >= 1561046400 读取索引一次性结束，无需费时在表格内跳转。

如果 MySQL 估计不正确，例如实际上，您的时间范围内可能只有少数行，或者如果您只想使用索引测试执行时间，您可以 force MySQL 与例如使用它

... FROM cloud_data_player_gold_log FORCE INDEX (create_time) WHERE ...

这样做的普遍缺点是MySQL无法适应变化的数据，不同create_time - 参数或附加过滤器，例如如果使用不同的索引实际上会更快。

您可以尝试的替代索引是 (player_id, create_time) (或者覆盖 (player_id, create_time, add_gold) )，它支持 group by 。

这将取决于您的数据分布，哪一个会更快:以 create_time 开头的索引必须读取更少的行，索引以 player_id 开头得少排序一次。根据行数，一行将抵消另一行。