使用1 join优化查询，对具有10+数百万行的表进行优化

Question

我正在考虑使用两个表更快地提出请求。

我有以下两张表：

表“原木”

• id varchar(36) PK
• date timestamp(2)
• more varchar字段和一个文本字段

该表具有所称的与其他几个对象的“多态多到多”的关系，因此还有第二个表"logs_pivot“：

• id unsigned int PK
• log_id varchar(36) FOREIGN KEY (logs.id)
• model_id varchar(40)
• model_type varchar(50)

logs_pivot中的每个条目在logs中有一个或多个条目。它们分别拥有数百万行的20+和10+。

我们做这样的查询：

select * from logs 
join logs_pivot on logs.id = logs_pivot.log_id
where model_id = 'some_id' and model_type = 'My\Class'
order by date desc
limit 50;

显然，我们对model_id和model_type字段都有一个复合索引，但是请求仍然很慢:每次都有几次(几十秒)。

我们在date字段上也有一个索引，但是一个EXPLAIN显示这是使用的model_id_model_type索引。

解释声明：

+----+-------------+-------------+------------+--------+--------------------------------------------------------------------------------+-----------------------------------------------+---------+-------------------------------------------+------+----------+---------------------------------+
| id | select_type | table       | partitions | type   | possible_keys                                                                  | key                                           | key_len | ref                                       | rows | filtered | Extra                           |
+----+-------------+-------------+------------+--------+--------------------------------------------------------------------------------+-----------------------------------------------+---------+-------------------------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | logs_pivot  | NULL       | ref    | logs_pivot_model_id_model_type_index,logs_pivot_log_id_index | logs_pivot_model_id_model_type_index | 364     | const,const                               |    1 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | logs        | NULL       | eq_ref | PRIMARY                                                                        | PRIMARY                                       | 146     | the_db_name.logs_pivot.log_id |    1 |   100.00 | NULL                            |
+----+-------------+-------------+------------+--------+--------------------------------------------------------------------------------+-----------------------------------------------+---------+-------------------------------------------+------+----------+---------------------------------+

在其他表中，通过在索引中包含日期字段，我能够更快地提出类似的请求。但在这种情况下，他们是在一个单独的桌子。

当我们想要访问这些数据时，它们通常只有几个小时/天。

我们的InnoDB池太小，无法在内存中保存所有的数据(+所有其他表)，因此数据很可能总是在磁盘上查询。

我们有什么办法能更快地提出这个要求？

理想情况下，只使用另一个索引，或通过更改它的完成方式。

非常感谢！

编辑17h05：

到目前为止，谢谢大家的回答，我将尝试像O Jones建议的那样，并且在透视表中以某种方式包括日期字段，这样我就可以在索引中包含。

编辑14/ 10h。

解决办法：

因此，我最终改变了请求的实际处理方式，对put表的id字段进行了排序，这确实允许将其放入索引中。

此外，如果数据透视表没有按日期进行筛选，则只对数据透视表进行计数。

谢谢大家！

Answer 1

只是个建议。使用复合索引显然是一件好事。另一种方法可能是按日期预先限定ID，并根据logs_pivot表的索引(model_id、model_type、log_id )扩展索引。

如果您查询的数据，并且整个历史记录是20+百万记录，那么这些数据可以追溯到多远的地方，您只需要处理每个给定类型的id/type的50条记录的限制。比如说三个月？你说你有五年的记录？(没有在文章中列出，只是举个例子)。因此，如果您可以查询日期大于3个月前的最小日志ID，则该ID可以限制logs_pivot表中的其他操作。

有点像

select
      lp.*,
      l.date
   from
      logs_pivot lp
         JOIN Logs l
            on lp.log_id = l.id
   where
          model_id = 'some_id' 
      and model_type = 'My\Class'
      and log_id >= ( select min( id )
                         from logs
                        where date >= datesub( curdate(), interval 3 month ))
   order by 
      l.date desc
   limit  
      50;

因此，log_id的where子句只执行一次，只返回一个来自3个月前的ID，而不是整个logs_pivot的历史记录。然后，使用模型id/type的优化的两部分键进行查询，但也跳到其索引的末尾，索引键中包含ID，以跳过所有历史记录。

另一件您可能想要包括的是一些预先汇总的表，比如每个给定的模型类型/id每个月/年有多少记录。使用它作为一个预查询呈现给用户，然后您可以使用它作为一个演练，以进一步了解更多的细节。一个预聚合表可以对所有历史事件进行一次操作，因为它是静态的，而不是变化的。您唯一需要不断更新的是当前的单个月期间，例如每晚进行更新。甚至可能更好，通过触发器，每次添加时插入一条记录，或者根据年/月聚合更新给定模型/类型的计数。同样，对于数据将如何/为什么会呈现给最终用户，只是一个没有其他上下文的建议。

Answer 2

我看到两个问题：

当表相对于内存大小很大时，

• UUID是昂贵的。

• LIMIT无法最优地处理，因为WHERE子句来自一个表，但ORDER BY列来自另一个表。也就是说，它将完成所有的JOIN，然后排序，最后剥离几行。

Answer 3

SELECT columns FROM big table ORDER BY something LIMIT small number是一个臭名昭著的查询性能反模式。为什么？服务器对一整排长行进行排序，然后丢弃几乎所有的行。您的columns之一是LOB --文本列，这一点也无济于事。

下面是一种减少开销的方法:通过查找所需的主键集，找出您想要的行，然后只获取这些行的内容。

你想要什么行？此子查询查找它们。

                  SELECT id
                    FROM logs
                    JOIN logs_pivot 
                            ON logs.id = logs_pivot.log_id
                   WHERE logs_pivot.model_id = 'some_id'
                     AND logs_pivot.model_type = 'My\Class'
                   ORDER BY logs.date DESC
                   LIMIT 50

这做了所有繁重的工作，计算出你想要的行。因此，这是您需要优化的查询。

这个索引可以在logs上加速

CREATE INDEX logs_date_desc ON logs (date DESC);

logs_pivot上的三列复合索引

CREATE INDEX logs_pivot_lookup ON logs_pivot (model_id, model_type, log_id);

这个索引可能会更好，因为优化器将看到logs_pivot上的过滤，而不是logs。因此，它将首先在logs_pivot中查看。

或者也许

CREATE INDEX logs_pivot_lookup ON logs_pivot (log_id, model_id, model_type);

尝试一个，然后另一个，看看哪一个产生更快的结果。(我不确定连接将如何使用复合索引。)(或者简单地将两者相加，并使用EXPLAIN查看它使用的是哪一个。)

然后，当您对子查询的性能感到满意时，使用它来获取所需的行，如下所示

SELECT * 
  FROM logs
  WHERE id IN (
                  SELECT id
                    FROM logs
                    JOIN logs_pivot 
                            ON logs.id = logs_pivot.log_id
                   WHERE logs_pivot.model_id = 'some_id'
                     AND model_type = 'My\Class'
                   ORDER BY logs.date DESC
                   LIMIT 50
              )
  ORDER BY date DESC

这是因为它分类的数据较少。覆盖logs_pivot上的三列索引也会有所帮助。

注意，子查询和主查询都有ORDER子句，以确保返回的详细结果集符合所需的顺序。

编辑 Darnit，在MariaDB 10+和MySQL 8+上呆了这么久，我忘记了以前的限制。试试这个吧。

SELECT * 
  FROM logs
  JOIN (
                  SELECT id
                    FROM logs
                    JOIN logs_pivot 
                            ON logs.id = logs_pivot.log_id
                   WHERE logs_pivot.model_id = 'some_id'
                     AND model_type = 'My\Class'
                   ORDER BY logs.date DESC
                   LIMIT 50
        ) id_set ON logs.id = id_set.id
  ORDER BY date DESC

最后，如果您知道您只关心比某一特定时间更新的行，则可以将类似的内容添加到您的子查询中。

                  AND logs.date >= NOW() - INTERVAL 5 DAY

如果您的表中有吨位的历史数据，这将非常有帮助。