强制InnoDB构建辅助索引

Question

在INSERT中，InnoDB延迟了二级指标的建立。例如,

CREATE TABLE item_creators
(
item_id int(11) unsigned NOT NULL,
creator_id int(11) unsigned NOT NULL,
INDEX(creator_id),
PRIMARY KEY(item_id)
) ENGINE=InnoDB

在第一次查询之后，

INSERT INTO item_creators (item_id,creator_id)
    SELECT item_id,creator_id FROM t1;

SELECT a.item_id, b.creator FROM
    item_creators a JOIN creators ON a.creator_id=b.creator_id;

InnoDB开始重建次要索引，后台为INDEX(creator_id)。如果没有这个索引，第二个查询就会非常慢。

• 首先，如何在第一个查询之后强制InnoDB重建辅助索引？
• 其次，在运行第二个查询之前，我们如何检查所需的索引是否已经完成？

请注意，它大约是非常大的表(数亿行)，在这些表中，索引的构建需要时间。

Answer 1

我觉得你有些误会。当您插入一行时，InnoDB不会重建整个索引。它只是在现有索引中添加了一个条目。如果每次插入后必须在大型表上重新构建索引，对性能将是极其不利的。

另一个误解是关于延迟的索引更新。在这段时间内，索引仍然可用。InnoDB知道如何检查change缓冲区中的条目以及索引。如果查询从change缓冲区读取值，则会立即将其合并到索引中。如果查询读取已合并到索引中的其他值，则索引将帮助该查询。

你可能在试图解决一个不存在的问题。

至于你问的问题，如何判断索引是否完全合并，这是很难做到的。您可以查询到有多少缓冲池分配给变更缓冲区(这在手册页：https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html中有记录)。

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100))
       AS change_buffer_page_percentage;

+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                2064 |        8191 |                       25.1984 |
+---------------------+-------------+-------------------------------+

这显示，在我将几百万行插入测试表后，大约25%的缓冲池被等待合并的更改缓冲区内容占用。但它并没有告诉我哪个表或指数，所以可能是其他表(S)的变化才能解释这一点。

随着时间的推移，随着索引更改合并和其他INSERT/UPDATE/DELETE通信量的增加，更改缓冲区页将不断增长和缩小。

Answer 2

大插入后的I/O活动可能来自延迟的“更改缓冲区”活动。

这是更新非唯一辅助索引的地方。

这样想吧。在更新/插入/删除一行时，

• 最优先的(在事务处理之后，等等)是将数据放到表中。
• 对辅助索引的更新需要对索引BTree块进行读-更新-写入，但是，需要做什么的一个小记录存储在缓冲池的一部分，称为“变更缓冲区”。
• 在时间允许的情况下(也就是说，在后台)，对这些更新进行排序(它们可能保存在一个“优先级队列”中，以便可以访问和排序)，即所采取的读-更新-写入操作。
• 通过批处理更新，需要进行的读取更少。(也就是说，读取一个16 is的索引块；在写入之前插入/删除几行。)
• 结果块现在就像任何常规块一样，因此它作为一个“脏”块等待被刷新到磁盘。
• 甚至在以后，这个索引块实际上将被写入磁盘。

所以，是的，很多I/O活动。但它的存在是为了提高系统的效率，从而减少I/O。

我认为只有两个设置可以控制更改缓冲区：

• buffer_pool的百分比-- 96%的用户在默认为"25“时离开innodb_change_buffer_max_size。
• 处理哪些操作- 94%的用户拥有innodb_change_buffering = all (默认值)

没有必要“等待”它的完成；所有这些都是在后台透明地处理的。

Answer 3

按照OPTIMIZE TABLE TBL_NAME手册中的建议，在很少的间隔内执行MySQL。

一旦您的数据达到了稳定的大小，或者一个不断增长的表增加了几十兆或几百兆字节，请考虑使用优化表语句来重新组织表，并压缩任何浪费的空间。重新组织的表需要更少的磁盘I/O来执行完整的表扫描.当其他技术如改进索引使用或优化应用程序代码不实用时，这是一种简单的技术，可以提高性能。

另外，如果item_id和creator_id的组合是唯一的，那么创建一个复合主键。