首先，提前为文字墙道歉。我确实通读了我能找到的每一个类似的问题/答案，但这些答案似乎不适用于我的查询，或者我需要更清楚地理解潜在的问题和解决方案。

我有一个文件大小表以及相关的文件日期和观察时间戳。所有日期都是以秒为单位的 UNIX 纪元时间整数:

mysql> describe name_servers;
+-----------------------+------------------+------+-----+---------+----------------+
| Field                 | Type             | Null | Key | Default | Extra          |
+-----------------------+------------------+------+-----+---------+----------------+
| server_name           | varchar(255)     | YES  |     | NULL    |                |
| file_date             | int(10) unsigned | YES  |     | NULL    |                |
| file_size             | int(10) unsigned | YES  |     | NULL    |                |
| time                  | int(10) unsigned | YES  | MUL | NULL    |                |
| poll_id               | int(11)          | NO   | PRI | NULL    | auto_increment |
+-----------------------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)


mysql> show index from name_servers;
+--------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| name_servers |          0 | PRIMARY                  |            1 | poll_id     | A         |     3523218 |     NULL | NULL   |      | BTREE      |         |               |
| name_servers |          0 | index_time_servername    |            1 | time        | A         |      503316 |     NULL | NULL   | YES  | BTREE      |         |               |
| name_servers |          0 | index_time_servername    |            2 | server_name | A         |     3523218 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

我必须跟踪文件大小的变化，以检测文件是否在任何 48 小时内缩小了 > 20%。通常我会尝试使用 MySQL 窗口函数来执行此操作，但我的服务器上的 MySQL 版本不支持它们(5.6.37——我无法控制，因为服务器不由我的团队管理)。目前，我通过在当前行中查找文件大小的外部查询和在前 48 小时(172,800 秒)中查找最大文件大小的内部子查询来获取当前大小和最大大小(在过去 48 小时内) ) 行数:

mysql> select name_servers_outside.server_name,
    -> name_servers_outside.file_size,
    -> name_servers_outside.file_date,
    -> name_servers_outside.time,
    -> (select max(file_size) from name_servers where time > (name_servers_outside.time - 172800) and server_name = 'example_server') as max_file_size
    -> from name_servers as name_servers_outside
    -> where name_servers_outside.server_name = 'example_server'
    -> and name_servers_outside.time > (UNIX_TIMESTAMP() - 172800)
    -> limit 10;
+-------------------+-------------------+-------------------+------------+-----------------------+
| server_name       | file_size         | file_date         | time       | max_file_size         |
+-------------------+-------------------+-------------------+------------+-----------------------+
| example_server    |           1159544 |        1550382945 | 1550382985 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383195 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383255 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383316 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383376 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383435 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383496 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383555 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383616 |               1159580 |
| example_server    |           1159544 |        1550382945 | 1550383676 |               1159580 |
+-------------------+-------------------+-------------------+------------+-----------------------+
10 rows in set (16.11 sec)

仅检索这 10 行就花费了 16 秒，而在生产环境中，此查询将不得不检索超过 150 行。内部查询正在对所有超过 300 万个表行进行完整扫描，并显示消息 “检查每条记录的范围(索引映射:0x2)”:

mysql> explain
    -> select name_servers_outside.server_name,
    -> name_servers_outside.file_size,
    -> name_servers_outside.file_date,
    -> name_servers_outside.time,
    -> (select max(file_size) from name_servers where time > (name_servers_outside.time - 172800) and server_name = 'example_server') as max_file_size
    -> from name_servers as name_servers_outside
    -> where name_servers_outside.server_name = 'example_server'
    -> and name_servers_outside.time > (UNIX_TIMESTAMP() - 172800);
+----+--------------------+----------------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------------------------+
| id | select_type        | table                | type  | possible_keys            | key                      | key_len | ref  | rows    | Extra                                          |
+----+--------------------+----------------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------------------------+
|  1 | PRIMARY            | name_servers_outside | range | index_time_servername    | index_time_servername    | 5       | NULL |   47302 | Using index condition; Using MRR               |
|  2 | DEPENDENT SUBQUERY | name_servers         | ALL   | index_time_servername    | NULL                     | NULL    | NULL | 3533883 | Range checked for each record (index map: 0x2) |
+----+--------------------+----------------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------------------------+
2 rows in set (0.01 sec)

有问题的部分似乎是这样的:

time > (name_servers_outside.time - 172800)

如果我在子查询中使用静态整数值而不是“name_servers_outside.time”列引用来运行类似的查询，索引将按预期使用并且查询速度很快:

time > (UNIX_TIMESTAMP() - 172800)

修改后的查询:

mysql> select name_servers_outside.server_name,
    -> name_servers_outside.file_size,
    -> name_servers_outside.file_date,
    -> name_servers_outside.time,
    -> (select max(file_size) from name_servers where time > (UNIX_TIMESTAMP() - 172800) and server_name = 'example_server') as max_file_size
    -> from name_servers as name_servers_outside
    -> where name_servers_outside.server_name = 'example_server'
    -> and name_servers_outside.time > (UNIX_TIMESTAMP() - 172800)
    -> limit 10;
+--------------------+-------------------+-------------------+------------+-----------------------+
| server_name        | file_size         | file_date         | time       | max_file_size         |
+--------------------+-------------------+-------------------+------------+-----------------------+
| example_server     |           1159544 |        1550382945 | 1550382985 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383195 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383255 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383316 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383376 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383435 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383496 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383555 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383616 |               1159580 |
| example_server     |           1159544 |        1550382945 | 1550383676 |               1159580 |
+--------------------+-------------------+-------------------+------------+-----------------------+
10 rows in set (0.01 sec)


mysql> explain
    -> select name_servers_outside.server_name,
    -> name_servers_outside.file_size,
    -> name_servers_outside.file_date,
    -> name_servers_outside.time,
    -> (select max(file_size) from name_servers where time > (UNIX_TIMESTAMP() - 172800) and server_name = 'example_server') as max_file_size
    -> from name_servers as name_servers_outside
    -> where name_servers_outside.server_name = 'example_server'
    -> and name_servers_outside.time > (UNIX_TIMESTAMP() - 172800)
    -> limit 10;
+----+-------------+----------------------+-------+--------------------------+--------------------------+---------+------+-------+----------------------------------+
| id | select_type | table                | type  | possible_keys            | key                      | key_len | ref  | rows  | Extra                            |
+----+-------------+----------------------+-------+--------------------------+--------------------------+---------+------+-------+----------------------------------+
|  1 | PRIMARY     | name_servers_outside | range | index_time_servername    | index_time_servername    | 5       | NULL | 49042 | Using index condition; Using MRR |
|  2 | SUBQUERY    | name_servers         | range | index_time_servername    | index_time_servername    | 5       | NULL | 49042 | Using index condition; Using MRR |
+----+-------------+----------------------+-------+--------------------------+--------------------------+---------+------+-------+----------------------------------+
2 rows in set (0.00 sec)

谢谢你陪我读到这里。我再次为巨大的文本墙道歉，但我想确保包含足够的解释性细节以清楚地定义问题。

现在，我要解决的问题是我需要检索每行之前 48 小时内 file_size 的最大值。因此，每一行都有自己独特的“max(file_size)”计算时间范围。然后，这将用于计算文件大小变化的百分比。如上所述，我通常想为此使用窗口函数，但我的 MySQL 版本 (5.6.37) 不支持它们，而且我没有能力更新到 8.0，因为我没有这个服务器。

一如既往，欢迎提出任何建议。感谢阅读!

最佳答案

我会首先尝试将 file_size 添加到您的 index_time_servername 索引，但我怀疑真正的问题是您必须在您的子查询中使用 name_servers_outside.time，它来自不同的别名可能会使查询规划器感到困惑。

那么，在时间介于 time 和 time-48-hours-ago 之间的情况下，丢失子查询并将表连接到自身怎么样？

有点像...

SELECT
  name_servers_outside.server_name,
  name_servers_outside.file_size,
  name_servers_outside.file_date,
  name_servers_outside.time,
  MAX(previous.file_size) AS max_file_size
FROM
   name_servers AS ns
INNER JOIN name_servers AS previous 
   ON previous.time BETWEEN (ns.time - 172800) AND (ns.time - 1)
WHERE 
   ns.server_name = 'example_server'
   AND ns.time > (UNIX_TIMESTAMP() - 172800)
GROUP BY
   ns.server_name,
   ns.file_size,
   ns.file_date,
   ns.time
LIMIT 10;

关于mysql - 这个子查询可以使用索引吗？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/54732049/