mysql - 使用 "ORDER BY"进行简单的 SQL 查询需要花费 10-20 倍的时间

Question

使用“ORDER BY”，简单的 SQL 查询需要花费 10 到 20 倍的时间。我怎样才能加快速度？

我的第一个查询是:

SELECT * 
FROM wp_usermeta 
WHERE meta_key = 'partner' 
AND meta_value = 1 
ORDER BY user_id DESC
LIMIT 5

需要 0.2601 秒。经过一些研究，我可以将其优化为:

SELECT user_id 
FROM wp_usermeta 
WHERE meta_key = 'partner' 
AND meta_value = '1' 
ORDER BY umeta_id DESC
LIMIT 5

这个查询只花了 0.1491 秒，但仍然太多了。如果我删除 ORDER BY，则只需要 0.0075 秒。

我在 Stackoverflow 和其他论坛上阅读了很多内容，但我无法获得更好的输出。有人有想法吗？

这是一个标准的 WordPress 用户元表。

Answer 1

WordPress 中的 wp_usermeta 表众所周知，它在 meta_key 上有一个单列索引。

但这会选择具有指定键的所有行，这并不会缩小搜索范围。而且它无助于排序，因此查询必须做额外的工作来进行排序:

mysql> explain SELECT *  FROM wp_usermeta WHERE meta_key = 'partner'  AND meta_value = 1  ORDER BY user_id DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_usermeta
         type: ref
possible_keys: meta_key
          key: meta_key
      key_len: 767
          ref: const
         rows: 1
        Extra: Using where; Using filesort

添加新索引应该会有所帮助:

mysql> alter table wp_usermeta add key (meta_key(191), meta_value(191), user_id);

mysql> explain SELECT *  FROM wp_usermeta WHERE meta_key = 'partner'  AND meta_value = 1  ORDER BY user_id DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_usermeta
         type: ref
possible_keys: meta_key_2,meta_key
          key: meta_key_2
      key_len: 767
          ref: const
         rows: 1
        Extra: Using where; Using filesort

尽管这表明它正在使用新索引 (meta_key_2)，但这没有帮助。 key_len 和 ref 指示它仅使用索引的第一列。为什么它不能同时使用两列？

因为您的查询将整数值 1 与字符串列 meta_value 进行比较。您必须比较相似的类型，即字符串 '1' 与字符串列:

mysql> explain SELECT *  FROM wp_usermeta WHERE meta_key = 'partner'  AND meta_value = '1'  ORDER BY user_id DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_usermeta
         type: ref
possible_keys: meta_key_2,meta_key
          key: meta_key_2
      key_len: 1534
          ref: const,const
         rows: 1
        Extra: Using where

现在它可以使用索引中的第二列来搜索值 '1'，您可以看出，因为 key_len: 1534 和 ref: const ,const 表示它使用索引的两列而不是一列。

然后优化器意识到它已经按 user_id 的顺序读取数据，因此不需要排序。 “使用文件排序”消失了。