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