mysql - 简单查询优化(WHERE + ORDER + LIMIT)

Question

我的查询运行速度慢得令人难以置信(4 分钟):

SELECT * FROM `ad` WHERE `ad`.`user_id` = USER_ID ORDER BY `ad`.`id` desc LIMIT 20;

广告表大约有 1000 万行。

SELECT COUNT(*) FROM `ad` WHERE `ad`.`user_id` = USER_ID;

返回 10k 行。

表有以下索引:

  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`,`status`,`sorttime`),

EXPLAIN 给出了这个:

           id: 1
  select_type: SIMPLE
        table: ad
         type: index
possible_keys: idx_user_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4249
        Extra: Using where

我不明白为什么要花这么长时间？此外，此查询是由 ORM(分页)生成的，因此最好从外部对其进行优化(可能添加一些额外的索引)。

顺便说一句，这个查询工作得很快:

select aa.*
from (select id from ad where user_id=USER_ID order by id desc limit 20) as a
join ad as aa on a.id = aa.id ;

编辑: 我尝试了另一个用户，行数比原始用户少得多(几十个)。我想知道为什么原始查询不使用 idx_user_id:

EXPLAIN SELECT * FROM `ad` WHERE `ad`.`user_id` = ANOTHER_ID ORDER BY `ad`.`id` desc LIMIT 20;

           id: 1
  select_type: SIMPLE
        table: ad
         type: ref
possible_keys: idx_user_id
          **key: idx_user_id**
      key_len: 3
          ref: const
         rows: 84
        Extra: Using where; Using filesort

Edit2:在 Alexander 的帮助下，我决定尝试强制 MySQL 使用我想要的索引，并且以下查询要快得多(1 秒而不是 4 分钟):

SELECT * 
FROM `ad` USE INDEX (idx_user_id)
WHERE `ad`.`user_id` = 1884774
ORDER BY `ad`.`id` desc LIMIT 20; 

Answer 1

在 EXPLAIN 输出中，您可以看到 key 值为 PRIMARY。这意味着 MySQL 优化器决定扫描所有表记录(已按 id 排序)并搜索具有特定 user_id 值的前 20 条记录比使用idx_user_id 键，优化器将其视为可能的键，然后拒绝。

在您的第二个查询中，优化器发现子查询中只需要 id 值，因此决定改用 idx_user_id 索引，因为该索引允许计算列表必要的 id 而无需触及表本身。然后通过主键值直接查找只检索到20条记录，对于那么少的记录，这是非常快的操作。

当您使用 ANOTHER_ID 进行查询时，MySQL 的错误决定是基于先前 USER_ID 值的行数。这个数字太大了，优化器猜测它会更快地找到具有这个特定 user_id 的前 20 条记录，只需查看表记录本身并跳过带有错误 user_id 的记录值(value)观。

如果通过索引访问表行，则需要随机访问操作。对于典型的 HDD 随机访问操作比顺序扫描慢大约 100 倍。因此，为了使索引有用，它必须将行数减少到总行数的 1% 以下。如果特定USER_ID值的行占总行数的1%以上，如果我们想检索所有，做全表扫描可能比使用索引更高效这些行。但是 MySQL 优化器没有考虑到只会检索其中 20 行的事实。所以它错误地决定不使用索引，而是进行全表扫描。

为了使您对任何 user_id 值的查询更快，您可以添加一个索引，这将允许以最快的方式执行查询:

create index idx_user_id_2 on ad(user_id, id);

该索引允许 MySQL 进行过滤和排序。为此，应首先放置用于过滤的列，然后放置用于排序的列。 MySQL 应该足够聪明以使用该索引，因为该索引允许搜索所有必要的记录而不跳过任何记录。