时间戳

首页 > 解决方案 > MySQL 查询只有在使用 ORDER BY 字段 DESC 和 LIMIT 时才慢

mysql - MySQL 查询只有在使用 ORDER BY 字段 DESC 和 LIMIT 时才慢

问题描述

概述

我正在运行 MySQL 5.7.30-33，并且遇到了一个问题，似乎 MySQL 在运行查询时使用了错误的索引。使用我现有的查询，我得到了 3 秒的查询时间。但是，只需更改 ORDER BY、删除 LIMIT 或强制使用 USE INDEX，我就可以获得 0.01 秒的查询时间。不幸的是，我需要坚持我的原始查询（它已融入应用程序），因此如果可以在架构/索引中解决这种差异，那就太好了。

设置/问题

我的表结构如下：

CREATE TABLE `referrals` (
  `__id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `systemcreated` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referrerid` mediumtext COLLATE utf8mb4_unicode_ci,
  `referrersiteid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  ... lots more mediumtext fields ...
  PRIMARY KEY (`__id`),
  KEY `systemcreated` (`systemcreated`,`referrersiteid`,`__id`)
) ENGINE=InnoDB AUTO_INCREMENT=53368 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED

该表只有约 55k 行，但非常宽，因为一些字段包含巨大的 BLOB：

mysql> show table status like 'referrals'\G;
*************************** 1. row ***************************
           Name: referrals
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 45641
 Avg_row_length: 767640
    Data_length: 35035897856
Max_data_length: 0
   Index_length: 3653632
      Data_free: 3670016
 Auto_increment: 54008
    Create_time: 2020-12-12 12:46:14
    Update_time: 2020-12-12 17:50:28
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)

我的客户的应用程序使用此查询表，不幸的是，这不能轻易更改：

SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

这导致查询时间约为 3 秒。

解释看起来像这样：

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

请注意，它使用 PRIMARY 键进行查询而不是systemcreated索引。

实验一

如果我将查询更改为使用 ASC 而不是 DESC：

SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id asc
    limit  16;

然后需要 0.01 秒，EXPLAIN 看起来是一样的：

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

实验二

如果我将查询更改为使用 ORDER BY __id DESC，但删除 LIMIT：

SELECT  *
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc;

然后它也需要 0.01 秒，解释如下：

+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | referrals   | NULL       | range | systemcreated | systemcreated | 406     | NULL | 2086 |    11.11 | Using index condition; Using filesort |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+

实验 3

或者，如果我强制原始查询使用systemcreated索引，那么它也会给出 0.01 秒的查询时间。这是解释：

mysql> explain     SELECT  *
    FROM  referrals USE INDEX (systemcreated)
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

+----+-------------+--------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+--------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | referrals    | NULL       | range | systemcreated | systemcreated | 406     | NULL | 2086 |    11.11 | Using index condition; Using filesort |
+----+-------------+--------------+------------+-------+---------------+---------------+---------+------+------+----------+---------------------------------------+

实验4

最后，如果我使用原始 ORDER BY __id DESC LIMIT 16 但选择较少的字段，那么它也会在 0.01 秒内返回！下面是解释：

mysql> explain     SELECT  field1, field2, field3, field4, field5
    FROM  referrals
    WHERE  `systemcreated` LIKE 'XXXXXX%'
      AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
    order by  __id desc
    limit  16;

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | referrals   | NULL       | index | systemcreated | PRIMARY | 4       | NULL |   32 |     5.56 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

概括

因此，似乎表现不佳的唯一组合是ORDER BY __id DESC LIMIT 16.

我想我的索引设置正确。我通过systemcreatedandreferrersiteid字段进行查询，并按 __id 排序，所以我有一个定义为 (systemcreated, referrersiteid, __id) 的索引，但 MySQL 似乎仍在使用 PRIMARY 键。

有什么建议么？

标签： mysqlindexinginnodb

解决方案

  • “Avg_row_length：767640”；很多MEDIUMTEXT。一行被限制在8KB左右；溢出进入“非记录”块。读取这些块需要额外的磁盘命中。

  • SELECT *将到达所有那些胖列。总数约为 50 次读取（每个 16KB）。这需要时间。

  • (Exp 4)SELECT a,b,c,d运行得更快，因为它不需要每行获取所有约 50 个块。

  • 您的二级索引 ( systemcreated, referrersiteid, __id) -- 只有第一列有用。这是因为systemcreated LIKE 'xxx%'. 这是一个“范围”。一旦达到一个范围，索引的其余部分就无效了。除了...

  • “索引提示”（USE INDEX(...)）今天可能会有所帮助，但明天当数据分布发生变化时可能会使情况变得更糟。

  • 如果你无法摆脱 中的通配符LIKE，我推荐这两个索引：

      INDEX(systemcreated)
  INDEX(referrersiteid)

  • 真正的加速可以通过翻转查询来实现。也就是说，先找到 16 个 id，然后再去寻找所有那些庞大的列：

      SELECT  r2...   -- whatever you want
      FROM  
      (
          SELECT  __id
              FROM  referrals
              WHERE  `systemcreated` LIKE 'XXXXXX%'
                AND  `referrersiteid` LIKE 'XXXXXXXXXXXX%'
              order by  __id desc
              limit  16 
      ) AS r1
      JOIN  referrals r2 USING(__id)
      ORDER BY  __id DESC   -- yes, this needs repeating

并保留您拥有的 3 列二级索引。即使它必须扫描超过 16 行才能找到所需的 16 行，但它的体积要小得多。这意味着子查询（“派生表”）会比较快。然后外部查询仍然有 16 次查找——可能需要读取 16*50 个块。读取的块总数仍然会少很多。

ASC和DESCon之间很少有明显的区别ORDER BY。

为什么优化器选择 PK 而不是看起来更好的二级索引？PK可能是最好的，特别是如果 16 行位于表的“末尾”（DESC）。但如果它必须扫描整个表而不找到 16 行，那将是一个糟糕的选择。

同时，通配符测试使二级索引仅部分有用。优化器根据不充分的统计数据做出决定。有时感觉就像抛硬币一样。

如果您使用我的由内而外的重新表述，那么我推荐以下两个复合索引——优化器可以在它们之间为派生表做出半智能、半正确的选择：

INDEX(systemcreated, referrersiteid, __id),
INDEX(referrersiteid, systemcreated, __id)

它会继续说“filesort”，但不用担心；它只对 16 行进行排序。

而且，请记住，SELECT *这会损害性能。（虽然也许你无法解决这个问题。）

推荐阅读