时间戳

首页 > 解决方案 > 为什么索引会使查询变慢？

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

问题描述

我有一张如下表：

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查询会有这么大的区别？谢谢。

标签： mysqlindexing

解决方案

没有好的方法可以优化这种类型的查询。

如果前 900K 行的 vdiamond<=0，则 usingPRIMARY必须至少跳过那么多行。

同样，uid = 1558347 and vdiamond > 0如果该 uid 的行数很多，但 vdiamond 为负数，则性能不佳。

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

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

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

推荐阅读