MySQL分区还是删除旧数据？

Question

我最近有一个关于mySQL及其MyISAM的活动表，其中包含了大约4600万条记录。它已经被正确地索引并完成了所有的工作。但是，有些查询仍然需要几秒钟的时间来执行。

所以我想知道什么是提高这个数据库查询性能的最佳方法。是对表进行分区还是删除旧的数据？我主要是利用本月的数据计算查询，另一种用途是向用户显示他们最近的活动，以及我们必须不时手动监控他们的活动。

我一直在考虑删除所有早于今年的数据，这应该会删除大约2400万行数据。(自本月初以来已经4个月了，所以旧的数据不应该那么重要)。

或者我可以按月份进行分区，但我不知道如何在laravel中实现，我是否每次都要更改分区以获得比当前月份更早的数据呢？

编辑:这个1查询是一个重要的查询，它是在用户的特定活动之后执行的，这个特定的查询需要8s来执行，并使用user_id索引遍历了40,000行。帮助我改进这个查询的是如果它使用了多个索引，因为这样查询就会缩小到非常少的行。或者，如果我删除旧数据，那么它将有更少的行通过来完成计算。

select  *
    from  `recent_activites`
    where  `id` > 20443580
      and  `user_id` = 20580
      and  `updated_at` > '2020-04-01 00:00:00'
      and  `type` in (?, ?, ?, ?, ?, ?, ?, ?, ?) ```

编辑:查询:这个查询一次执行需要8.72ms，但由于减少了行，所以似乎总是需要超过1ms的时间。

​

​

这些是指标。

​

Answer 1

当删除大量表时，复制要保留的行会更快。您应该从InnoDB.

• PARTITIONing迁移到

• 将有助于将来清除旧数据。

• 添加PARTITIONing将花费时间。任何删除旧数据的技术也是如此。哪个更重要？尽量减少堵塞？或者使用task.

• PARTITIONing，本身的速度，都不会加快查询速度。但是，您似乎需要一个"2-D“索引。

上面的几个步骤可以同时完成--切换到InnoDB，添加分区，删除旧数据，更改索引。(汇总表是一个单独的任务。)我建议在处理你的直播系统之前先做实验。

参考文献：

切换到http://mysql.rjweb.org/doc.php/myisam2innodb

• Partitioning：
• -利弊、指南、时间序列代码：http://mysql.rjweb.org/doc.php/partitionmaint
• Big DELETEs --多项技术及其优缺点：http://mysql.rjweb.org/doc.php/deletebig
• Summary表：http://mysql.rjweb.org/doc.php/summarytables

Answer 2

您可以将一些数据移动到归档数据库，我的意思是将旧数据存储在单独的数据库(归档)中，并将实际数据存储在当前的数据库中。在这种情况下，对于实际(新鲜)数据仍然有很好的性能，同时访问旧数据的可能性也很大。

要处理这个问题，您需要在您的Laravel系统中设置2个连接--在config/database.php中创建新的连接。

现在您应该能够访问如下所示的存档数据：

$archive_data = DB::connection('name_of_archive_connection')->table('table_name')->select('*')...
$actual_data = DB::connection('name_of_main_connection')->table('table_name')->select('*')...

此外，您还可以通过插入/删除mysql函数来轻松地在数据库之间移动数据，如上面的示例所示。