MySQL5.6几种建索引方式比较

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.17    |
+-----------+
mysql> CREATE TABLE test AS SELECT * FROM information_schema.columns;
mysql> INSERT INTO test  SELECT * FROM test;
mysql> INSERT INTO test  SELECT * FROM test;
mysql> SELECT COUNT(1) FROM test;
+----------+
| COUNT(1) |
+----------+
|   312928 |
+----------+
1 row in set (0.17 sec)

ALGORITHM=inplace，就地进行，如果允许的话则修改操作可以直接在该表上执行。

mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=inplace;
Query OK, 0 rows affected (1.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test DROP INDEX ind_t_column_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> CREATE INDEX ind_t_column_name ON test(column_name);
Query OK, 0 rows affected (1.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALGORITHM=copy，用于标识改操作是否需要整个表。
mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=copy;
Query OK, 312928 rows affected (5.93 sec)

Records: 312928  Duplicates: 0  Warnings: 0

注：

SET old_alter_table=0; --ALGORITHM=inplace

SET old_alter_table=1; --ALGORITHM=copy