索引操作究竟发生了什么?

本文介绍了索引操作究竟发生了什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我今天正在索引一个巨大的表，其中包含20亿条记录.我以为MySQL会吞噬我的2TB驱动器...磁盘消耗一直增加到400GB，然后增加到500GB，最后下降到180GB，MySQL说成功添加了索引.为什么空间增加了，到底发生了什么?有人可以给我一些指示吗?

I was indexing a huge table today containing 2 billion records. I thought MySQL would eat away my 2TB drive... The disk consumption kept increasing to 400GB and then 500GB and then finally drops to 180GB and MySQL says successfully added the index. Why the space increase and what happened in the end? Can someone please give me some pointers?

推荐答案

昨天我偶然地回答了，下面是我的研究:

Incidentally yesterday I answered a question on how to make index creation faster in MySQL, and the following came out from my research:

CREATE INDEX和DROP INDEX命令通过创建一个新的空表来工作，该表定义了所请求的索引集.然后，它将现有行一张一张地复制到新表中，并随即更新索引.以这种方式将项插入索引中，即不对键值进行排序，这需要对索引节点进行随机访问，并且远非最佳.复制原始表中的所有行后，将删除旧表，并将副本重命名为原始表的名称.

The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes. Inserting entries into the indexes in this fashion, where the key values are not sorted, requires random access to the index nodes, and is far from optimal. After all rows from the original table are copied, the old table is dropped and the copy is renamed with the name of the original table.

来源:快速概述索引创建