Innodb的随机PRIMARY KEY [英] Random PRIMARY KEY for Innodb

问题描述

文章缓慢插入到带有随机PRIMARY KEY列值的InnoDB表中描述如果使用随机PRIMARY KEY列，插入将很慢.并且 MySQL文档说:

The article Slow INSERT into InnoDB table with random PRIMARY KEY column's value describes that if you use random PRIMARY KEY columns, insertions will be slow. And MySQL docs says:

如果按顺序插入索引记录(升序或降序)，则结果索引页将占满15/16.如果按随机顺序插入记录，则页面的容量为1/2到15/16.

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full.

为什么插入会导致很多页面拆分(1/2满)，并且随机出现PRIMARY KEY列?

Why will insertions cause a lot of page splits (1/2 full) with random PRIMARY KEY column ?

非常感谢您的帮助.

推荐答案

MySQL不会重建"每个插入的索引.

MySQL doesn't "rebuild" the index on each insert.

MySQL的默认页面大小为16K.它将以1MB的增量(称为扩展数据块)分配这些页面.

MySQL's default page size is 16K. It allocates these pages in 1MB increments (called extents).

第一次创建表(重建索引)时，页面将被填满15/16，为一些随机插入(1k的空间)留出了空间.如果您的索引条目每个为500字节(主键大小+聚集索引的行数据)，则在拆分页面之前会留出空间来插入2个新行.

When a table is first created (indexes are rebuilt), pages are filled up 15/16th full, leaving room for some random inserts (1k of room). If your index entries are 500 bytes each (primary key size + row data for a clustered index), that leaves room for 2 new rows to be inserted before having to split the page.

MySQL将最高和最低记录的值保留在页面页眉中，因此，一定范围内的记录将位于同一页面上.

MySQL keeps the value of the highest and lowest record in the page header, so records within a certain range go on the same page.

当MySQL需要在整页上插入一行时，必须拆分该页. MySQL将添加一个新页面，并将页面数据的一半移至新页面.

When MySQL needs to insert a row on a full page, the page must be split. MySQL will add a new page, and move half of the page data to the new page.

在一个页面内，记录实际上可能不是按物理顺序排列的.它们将按照插入的顺序排列.它们通过链接列表的形式按顺序链接.因此，即使是随机插入，也不需要拆分页面，也不会导致数据在物理上四处移动.

Within a page, records may not actually be in physical order. They'll be in the order they were inserted. They're linked in order via a form of linked list. So, even a random insert, outside of the need to split the page, doesn't cause data to be physically moved around.

多次随机插入后，您的页面将从1/2满到满.具有许多半满页面的索引会对读取性能产生负面影响(您必须读取两个半满页面才能读取与一个15/16个完整页面相同的记录数).

After many random inserts, your pages will be from 1/2 full to full. An index with many half full pages will negatively affect read performance (you have to read two half-full pages to read the same number of records as one 15/16th full page).

现在，如果要按索引顺序插入行，则MySQL只会继续添加到页面的末尾，将它们填满15/16，然后在页面的某个时间添加范围.由于不存在页面拆分，因此性能损失要少得多，因此不涉及数据移动，更不用说几乎整页的读取性能优势.

Now, if you're inserting rows in index order, then MySQL simply keeps adding to the end of the pages, filling them up 15/16 full, and adding an extent at a time of pages. Much less performance penalty since there is no splitting of pages, hence no moving of data is involved, not to mention the read performance benefit of nearly full pages.

随机插入还会增加页面的碎片，如果您经常读取大量顺序记录(稀有)，则这可能会影响读取性能.

Random inserts also increase fragmentation of the pages, which may affect read performance if you often read large numbers of sequential records (rare).

此外，更改缓冲可能会影响你.

这篇关于Innodb的随机PRIMARY KEY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！