MySQL索引创建内部

Question

现在有两种方法可以在MySQL中构建表的索引：

1. 首先创建表结构，然后导入数据，然后添加索引。
2. 创建带有索引的表结构，然后导入数据。

在第一个过程中，我们将有连续的数据(所有字段)页面，然后是索引页。因此，当我们使用索引进行查询时，MySQL必须首先加载索引页并找到匹配的键，并且必须在数据页上查找这些主键。为此，它必须再次加载数据页以获取数据。当我们有更大的索引扫描时，这是非常有用的，因为所有索引都是连续加载的。

在创建索引的第二种方法中，过滤后的索引页很可能包含接近它的数据页，因为它们是同时创建的。所以我想对一个小范围的扫描来说，向上看会更快。

我的理解正确吗？

更新：

我应该提到，在导入数据的第一种方式中，“主键已启用”(自动增量id列)。因此，内部rowid没有生成，并且保存了大量IO，因为我们不会添加主键。

正如您注意到的，当我们使用第二种方法导入数据时，会出现碎片。

考虑到我的要求是更大范围的扫描(扫描~100米行)，我想我将采用第一种导入数据的方式。

更新6月8日11:30

CREATE TABLE `table_dummy` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `column1` bigint(20) DEFAULT NULL,
  `column2` bigint(20) DEFAULT NULL,
  `column3` bigint(20) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `column4` tinyint(1) DEFAULT NULL,
  `column5` tinyint(4) DEFAULT NULL,
  `column6` bigint(20) DEFAULT NULL,
  `column6_created_at` datetime DEFAULT NULL,
  `column7` int(11) DEFAULT NULL,
  `column8` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `twtaccount_id_2` (`column1`,`column2`),
  KEY `twtaccount_id` (`column1`,`created_at`,`column5`),
  KEY `twt_user_id` (`column3`,`created_at`,`column5`),
  KEY `original_status_id` (`column6`,`column3`,`followers_count`)
)

引擎是INNODB

这张桌子有大约800米的记录，我用两种方式转储并导入它。

方法1:只使用主键创建表，并导入整个数据。然后，我给出了一个alter语句来添加其余的索引。

方法2:创建带有索引的表，并完成转储。这需要更长的时间。

与通过“方法2”转储的表的大小相比，通过“方法1”生成的表的大小要小30 by。方法1所用的时间比‘方法2’要短得多，几乎是速度的两倍。

我主要关心的是，当我选择扫描范围很广的索引(“索引只扫描”)时，表的性能。

Answer 1

在这里，我可以为你们澄清几点：

• 是的，将二级索引的创建推迟到导入数据之后(从MySQL 5.5开始--而不是之前)是一个很好的做法。默认情况下，迈赛尔泵会这样做。
• 当延迟二级索引创建时，内部MySQL将读取、排序并创建索引(减少碎片)。对于MySQL 5.7，这里有附加优化。
• 当您滴入加载索引时，它们可能有更多的分页和较低的页面填充效率(碎片)。这在一定程度上取决于数据，以及是否符合要求。您并没有给我很多关于column1，column2的线索，但是比如说，说column1是一个时间戳，它可能是合适的。
• 索引中的页面在逻辑上是按顺序排列的，不一定是物理上的。如果您不能适应内存中范围的工作集，并且正在使用旋转磁盘作为备份存储，这种区别可能很重要。这也有点难回答，因为对于旋转磁盘，您可能使用的RAID具有一定的条带大小，而且文件系统级别上的块也可能是不连续的。
• 还请注意，优化器可能不会考虑对表罐上的大范围进行范围扫描(因为在5.7之前，成本模型假定页面不在内存中)。如果您可以依赖于这种情况，您可能需要FORCE INDEX并比较实际运行时间。(更多关于新成本模式的信息。)

Answer 2

“接近”与此无关。这是因为buffer_pool中的数据块和索引块都是缓存的。缓存导致按逻辑不考虑的顺序进行写入/读取。

不要使用内部自动生成的PRIMARY KEY；即使以后添加辅助键，也要始终显式指定PRIMARY KEY。

如果您的PRIMARY KEY是一个AUTO_INCREMENT，并且不是由正在加载的数据提供的，那么加载数据将本质上是“仅附加”--非常高效。每一行进入“最后”数据块，直到它几乎满为止，然后启动一个新块。(稍后将更多地介绍辅助键。)

如果您的PRIMARY KEY是“自然的”(表中的一个或多个列)，那么在加载之前对传入的数据进行排序是非常有益的。然后，再一次，你得到“只附加”。

插入每一行时必须检查UNIQUE辅助键。如果此列的值(S)是随机的，那么查找可能会击中磁盘，在这种情况下，当该索引变得大到需要缓存在buffer_pool中时，代价会很高。太可惜了。(稍等片刻。)

一个非唯一的辅助键(通常)最好是在事实发生后创建.在一个ALTER TABLE tbl ADD INDEX(...), ADD INDEX(...), ...;中创建所有这样的键

如果您正在创建AUTO_INCREMENT值(而不是从其他地方加载它们)，那么将“先排序”技巧应用于一些次要索引，如果存在这种情况，则为UNIQUE索引。这将有效地构建“仅附加”索引。它避免了上述关于UNIQUE索引的警告。

好的，这为您提供了加载大型InnoDB表的最有效方法。但你问的是内部..。

“数据”和PRIMARY KEY共存在同一个BTree中，每个“记录”都包含表的所有列。每个辅助键都存在于一个单独的BTree中，每个“记录”都由辅助键的列和主键的列组成。

BTrees由16 of块组成。或包含16 Or块的1MB“区段”。“范围”的存在是为了实现块体的“接近”。但是这有点愚蠢，因为当操作系统要求一个范围时，它不一定会给MySQL一个连续的1MB。

还有一个皱纹..。如果您有TEXT或BLOB列，它们可能(取决于大小和ROW_FORMAT)存储在其他地方。

又一个皱纹..。如果使用COMPRESSED，事情会变得更加复杂。

如果您想向我们展示您的SHOW CREATE TABLE，我们可能有更多的提示。

编辑(在OP编辑之后)

方法1(加载表后添加辅助索引)显然更好(更快、更小)。我怀疑这一点，但没有令人信服的证据表明它总是“更好”。

方法1为每个索引写出所有数据，对其进行排序，然后从排序列表构建BTree。这会减少碎片，并且不会跳到索引中插入“行”。

方法2一次在索引中随机插入“行”。缓存(buffer_pool)阻止了大多数I/O，因此它并不太慢。然而，BTree中的块可能不得不被分割很多次，并且大部分最终部分满了。(理论平均为69%。)

在这两种方法中，BTree的“级别”的数量可能是相同的。百万行索引需要大约3个级别的BTree。每一个100的因素导致另一个水平。

因为方法1导致索引块较少，所以它更容易缓存。这只会是一个微小的差别。

“点查询”(SELECT ... WHERE primary_key = constant)需要向下钻取数据级别(基于PRIMARY KEY结构)以找到底层节点。通常，除了底层之外，所有级别都将在RAM中缓存，因此I/O不是问题。这两种方法可能击中相同数目的块，缓存或不缓存。

使用辅助索引的点查询将向下钻取辅助索引的BTree，找到主键，然后向下钻取数据BTree。同样，这些方法可能具有类似的性能。

例如，“索引扫描”就是SELECT ... WHERE secondary_key BETWEEN 1000 AND 2000。这是通过钻下BTree找到1000，然后扫描到2000年。这将(平均)达到大约10个块--方法1少几个；方法2多几个。所以现在我们发现了一个潜在的明显的性能差异，特别是当~10个块没有全部缓存时。

这两种差别够重要吗？如果你从来没有插入更多的行，也许它是有价值的。然而，方法1的INSERTing很快就会导致分块，从而使性能更接近于方法2。方法2不会像方法2那样快速地进行分块，因为块往往有更多的空闲空间。

正如您所看到的，如果没有对SELECT的了解，我就不能谈论INSERT。

底线:使用方法1装载(而不是“倾倒”)。