mysql - 为什么索引会使查询变慢？

Question

我有一个表格如下:

CREATE TABLE `fp_user_vcurrency_account_75` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) unsigned NOT NULL COMMENT 'SPLIT_KEY',
  `vdiamond` bigint(20) unsigned NOT NULL DEFAULT '0',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uid_id` (`uid`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=958434 DEFAULT CHARSET=utf8;

我需要执行以下查询:

select UNIX_TIMESTAMP(mtime), vdiamond, id 
from fp_user_vcurrency_account_75 
where uid = 1558347 and vdiamond > 0 
order by id desc limit 20;

我创建了索引“uid_id”来加速查询。然而，尽管解释输出看起来没问题:

mysql> explain select UNIX_TIMESTAMP(mtime), vdiamond, id from fp_user_vcurrency_account_75 use index(uid_id) where uid = 1558347 and vdiamond > 0 order by id desc limit 20;
+----+-------------+------------------------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table                        | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------------------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fp_user_vcurrency_account_75 | NULL       | index | uid_id        | uid_id | 16      | NULL |   20 |    33.33 | Using where |
+----+-------------+------------------------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+

查询大约需要 1 秒。如果我根本不指定索引，查询只需要几毫秒。后面sql的解释输出如下:

mysql> explain select UNIX_TIMESTAMP(mtime), vdiamond, id from fp_user_vcurrency_account_75  where uid = 1558347 and vdiamond > 0 order by id desc limit 20;
+----+-------------+------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table                        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fp_user_vcurrency_account_75 | NULL       | index | uid_id        | PRIMARY | 8       | NULL |   40 |    16.66 | Using where |
+----+-------------+------------------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

为什么这两个sql查询有这么大的差异？谢谢。

Answer 1

没有好的方法来优化此类查询。

如果前 900K 行有 vdiamond<=0，则使用 PRIMARY 将必须跳过至少那么多行。

类似地，如果有很多行具有该 uid，但 vdiamond 为负值，则 uid = 1558347 且 vdiamond > 0 的性能会很差。

我希望这是最好的:INDEX(uid, vdiamond)。这将让它在索引的 BTree 内完成所有 WHERE 工作。

该索引需要获取大量行，然后对它们进行排序，然后才查找 20 行。但请注意，您的两次尝试都避免了文件排序。

有时优化器有很好的统计数据，有时它可以猜测正确。