MySQL下使用Inplace和Online方式创建索引的教程

下面为你详细讲解“MySQL下使用Inplace和Online方式创建索引的教程”的完整攻略。

什么是Inplace和Online方式创建索引

在MySQL中，创建索引可以采用Inplace和Online方式进行，这两种方式都可以用于创建新的索引或者更新现有的索引。

Inplace方式创建索引：该方式是在表的数据区域中直接创建或修改索引，因此需要临时锁定整个表，在修改过程中，其他用户不能访问该表，属于阻塞操作。这种方式的好处是速度比Online方式快，同时也会占用较少的磁盘空间。但是如果数据量很大，创建或修改索引需要的时间也会很长，并且在创建或修改索引的过程中，表会被阻塞无法使用，可能会对业务产生影响。

Online方式创建索引：该方式是通过建立一张临时的索引表，并将新数据逐步写入临时表中，最终将索引从临时表合并到原表中，不需要锁定整个表，因此允许在创建索引的同时对表进行其他操作，适合于大表的索引重建，使用该方式创建索引不会阻塞表的正常读写操作，但是需要适当增加磁盘空间的使用。

在实际环境中，需要根据具体的需求，选择一个合适的创建方式来创建索引。

接下来，我们就分别介绍Inplace和Online方式在MySQL中创建索引的方法。

Inplace方式创建索引

Inplace方式创建索引的关键字是“ALTER TABLE ... ADD/CHANGE/DROP INDEX”，其中ADD/CHANGE/DROP是针对不同操作的，如果是新建索引则用ADD，如果是修改索引则用CHANGE。下面给出示例代码：

-- 添加一个新的索引
ALTER TABLE  table_name ADD INDEX index_name (column_name);

-- 修改现有索引
ALTER TABLE table_name CHANGE INDEX old_index_name new_index_name (column_name);

-- 删除现有索引
ALTER TABLE table_name DROP INDEX index_name;

需要注意的是，如果在Inplace方式下进行索引创建或修改操作，需要设置参数“InnoDB参数innodb_online_alter_log_max_size”的值，以避免在索引创建或修改过程中触发日志文件的切换。

Online方式创建索引

Online方式创建索引需要使用InnoDB的插件“innodb-online-alter-table”，启用该插件的方法是在MySQL启动命令行中添加参数“--plugin-load=innodb=ha_innodb.so;innodb_online_alter_log_sel=debug”或者在my.cnf中设置参数“[mysqld]innodb=FORCE;innodb_file_per_table;innodb_plugin_dir=/usr/local/mysql/lib/plugin;innodb_online_alter_log_sel=debug”。

在线创建索引可以用到的关键字是“ALTER TABLE ... ALGORITHM=INPLACE, ALGORITHM=INPLACE,COPY_TO_TMP_TABLE, SWITCH_TO_NEW_INDEX”, 其中ALGORITHM=INPLACE指定使用Inplace方式创建索引，ALCHEMY=COPY_TO_TMP_TABLE指定使用Online方式，将表的数据拷贝到临时表中，并在该表上创建索引，SWITCH_TO_NEW_INDEX则指定将临时表和原表进行合并操作。另外，对于Online方式，使用ALTER TABLE语句创建索引还需要注意一个限制条件，即在表中已经有UNIQUE索引或主键时，无法使用Online方式创建索引，只能用Inplace方式创建索引。下面给出示例代码：

-- 添加一个新索引
ALTER TABLE table_name ALGORITHM=INPLACE, ADD INDEX index_name(column_name);

-- 再添加索引时尽量用Online方式
ALTER TABLE table_name ALGORITHM=COPY_TO_TMP_TABLE, ADD INDEX index_name(column_name), ALGORITHM=INPLACE, DROP INDEX old_index_name, ALGORITHM=SWITCH_TO_NEW_INDEX;