mysql索引的创建、删除、修改、启用与禁用
时间:2013-10-05 10:10:37 作者:beebol 标签: 分类: Mysql
直接进行操作啦！！！
mysql> use test Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | food | | pet | +----------------+ 2 rows in set (0.00 sec) mysql> show index from pet; --这里查看表的索引Empty set (0.00 sec) Empty set (0.00 sec)
看上面的pet表，目前是还没有索引的，如何创建索引呢？如下
mysql> CREATE INDEX testINDEX ON pet (`name`);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from pet
-> ;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pet | 1 | testINDEX | 1 | name | A | 10 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)
1 row in set (0.00 sec)
上面成功创建了一个索引，使用方法：CREATE INDEX 索引名字 [index_type] 表名 （列表名）;
详细的创建方法：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_type]
index_col_name:
col_name [(length)] [ASC | DESC]
index_type: USING {BTREE | HASH | RTREE}
USING {BTREE | HASH | RTREE}
然后再创建一个Id列，在Id中创建一个主键，具体方法如下：
mysql> ALTER TABLE pet ADD Id INT (8); ---添加一列id，为pet表的ID Query OK, 10 rows affected (0.09 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> desc pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | MUL | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | | Id | int(8) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.01 sec) mysql> ALTER TABLE pet CHANGE Id Id INT (8) NOT NULL COMMENT 'pet ID'; ----修改Id列为not null并添加说明 Query OK, 10 rows affected, 10 warnings (0.08 sec)Records: 10 Duplicates: 0 Warnings: 10 Records: 10 Duplicates: 0 Warnings: 10
如下开始创建主键，但报错了，报ERROR 1062 （23000），错误的原因很简单，看一下select 数据查询结果就知道了。主键的数据是需要唯一的，但这里的数据全部是0，没有唯一，所以创建失败。那为什么是这样的，原因在添加这个数据列时，pet表已经有数据了。再添加一个int列时，默认就全部为0。需要按照我们想要的结果：一是update修改Id成不同的值;二是删除这一列重新创建一个带有主键自增的Id列。
mysql> ALTER TABLE pet ADD CONSTRAINT PK_PET PRIMARY KEY (`Id`); ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY' mysql> select * from pet; +----------+---------+---------+------+------------+------------+----+ | name | owner | species | sex | birth | death | Id | +----------+---------+---------+------+------------+------------+----+ | xixi | liuzhen | dog | m | 2013-10-04 | NULL | 0 | | Fluffy | Harold | cat | F | 1993-02-04 | NULL | 0 | | Claws | Gwen | cat | M | 1994-03-17 | NULL | 0 | | Buffy | Harold | dog | F | 1989-05-13 | NULL | 0 | | Chirpy | Gwen | bird | F | 1998-09-11 | NULL | 0 | | Fang | Benny | dog | M | 1990-08-27 | NULL | 0 | | Bowser | Diane | dog | M | 1990-08-31 | 1995-07-29 | 0 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | 0 | | Slim | Benny | snake | M | 1996-04-29 | NULL | 0 | | Puffball | Diane | hamster | F | 1999-03-30 | NULL | 0 | +----------+---------+---------+------+------------+------------+----+10 rows in set (0.00 sec) 10 rows in set (0.00 sec)
如果利用alter table直接添加一个auto_increment是不行的，因为它没有key。见如下提示：
mysql> ALTER TABLE pet CHANGE Id Id INT (8) NOT NULL AUTO_INCREMENT COMMENT 'pet ID'; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
那么我就先删除这一列，然后再添加这一列时就主动设置成主键和auto_increment属性。
mysql> ALTER TABLE pet DROP COLUMN Id; Query OK, 10 rows affected (0.04 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE pet ADD Id INT (8) NOT NULL AUTO_INCREMENT COMMENT 'pet ID'; ----这里报错的原因是自增列必须是一个key ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> desc pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | MUL | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> ALTER TABLE pet ADD Id INT (8) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'pet ID'; ----在这里添加primary key就创建成功了 Query OK, 10 rows affected (0.08 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from pet; +----------+---------+---------+------+------------+------------+----+ | name | owner | species | sex | birth | death | Id | +----------+---------+---------+------+------------+------------+----+ | xixi | liuzhen | dog | m | 2013-10-04 | NULL | 1 | | Fluffy | Harold | cat | F | 1993-02-04 | NULL | 2 | | Claws | Gwen | cat | M | 1994-03-17 | NULL | 3 | | Buffy | Harold | dog | F | 1989-05-13 | NULL | 4 | | Chirpy | Gwen | bird | F | 1998-09-11 | NULL | 5 | | Fang | Benny | dog | M | 1990-08-27 | NULL | 6 | | Bowser | Diane | dog | M | 1990-08-31 | 1995-07-29 | 7 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | 8 | | Slim | Benny | snake | M | 1996-04-29 | NULL | 9 | | Puffball | Diane | hamster | F | 1999-03-30 | NULL | 10 | +----------+---------+---------+------+------------+------------+----+10 rows in set (0.01 sec) 10 rows in set (0.01 sec)
如上显示已经成功将Id列添加主键并自动增长。然后再看一下索引信息:增加了一个primary key。这里需要说明一下，index_type这里默认是BTREE,如果需要变更可在创建时使用Using 其它类型。
mysql> show index from pet; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | pet | 0 | PRIMARY | 1 | Id | A | 2 | NULL | NULL | | BTREE | | | | pet | 1 | testINDEX | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec) 2 rows in set (0.00 sec)
那么如何删除索引呢？见下面例子
mysql> ALTER TABLE pet DROP INDEX testINDEX; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from pet; ----这里已经成功将索引testINDEX删除 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | pet | 0 | PRIMARY | 1 | Id | A | 10 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec) 1 row in set (0.00 sec)
如果我们在恢复数据的时候，（这里只说在myisam引擎中）在导入批量数据的时候会索引给暂时给失效，防止每插入一个数据需要刷新一次索引，这样会大大降低了插入性能。（当然，避免索引每次插入时刷新索引，可通过其它的方式避免，如事务、表写锁或者启动数据库时用--delay-key-write=ALL参数启动）。这里只介绍索引的启用和禁用（失效）。
方法如下：
mysql>ALTER TABLE pet DISABLE KEYS; mysql> ALTER TABLE pet ENABLE KEYS; mysql> ALTER TABLE pet ENABLE KEYS;