mysql - 每天在大表上添加和删除索引是一个好习惯吗？

Question

我正在构建一个连接到 MySQL 数据库的 Web 应用程序。目前我有两个巨大的表，每个表包含大约 4000 万行，并且它们每天都在接收新行(每天增加 ~ 500 000-1000 000 行)。

添加新行的过程在夜间运行，此时没有人可以使用该应用程序，新行的内容取决于对当前数据库的一些基本 SELECT 查询的结果。为了足够快地获得那些 SELECT 语句的结果，我在 WHERE 中至少出现一次的每一列上使用简单的索引(每个索引一列)条款。

事情是，白天，一些完全不同的查询针对这些表运行，包括一些“范围 WHERE 子句”(SELECT * FROM t1 WHERE a = a1 AND b = b1 AND(date BETWEEN d1 AND d2))。我在堆栈上找到了这本非常有用的迷你指南，它根据数据库的查询方式建议您应该使用哪些索引:http://mysql.rjweb.org/doc.php/index_cookbook_mysql他们建议使用复合索引:在我上面的示例查询中，它将给出 INDEX(a, b, date)。

它确实提高了白天运行查询的速度(从 1 分钟到 8 秒，所以我真的很高兴)。

但是，使用这些复合索引，在夜间添加新行所需的时间完全爆炸(添加每日内容需要一天以上)。

这是我的问题:每天晚上删除所有索引、添加新内容并重新设置每日索引是否可以？或者这会不会很危险，因为索引并不是每天都要重建的，尤其是在这么大的表上？我知道这样的操作总共需要大约两个小时(删除并重新创建索引)。

我知道 ALTER TABLE table_name DISABLE KEYS; 的存在，但我正在使用 InnoDB，我相信它不适用于 InnoDB 表。

Answer 1

我相信您已经回答了自己的问题:您白天需要索引，但晚上不需要。根据您的描述，您应该在晚上删除批量插入的索引，然后重新创建它们。为数据加载删除索引并非闻所未闻，并且在您的情况下似乎是合适的。

我想问一下您如何插入新数据。一种方法是一次插入一行值。另一种是将值放入临时表(没有索引)并进行批量插入:

insert into bigtable( . . .)
    select . . .
    from smalltable;

它们具有不同的性能特征。您可能会发现使用单个 insert(如果您尚未这样做)就足以满足您的目的。