mysql5.6的索引有哪些_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