mysql> create index index_id on info(id); #info是表名,id是表中的字段
mysql> show index from info;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| info |0| PRIMARY |1| id | A |5|NULL|NULL|| BTREE |||| info |1| index_id |1| id | A |5|NULL|NULL|| BTREE |||+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9
修改表结构的方式添加索引:
mysql> use student
mysql> alter table info add index index_name(name);
mysql> show index from info;+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| info |0| PRIMARY |1| id | A |5|NULL|NULL|| BTREE |||| info |1| index_id |1| id | A |5|NULL|NULL|| BTREE |||| info |1| index_name |1| name | A |5|NULL|NULL|| BTREE |||+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
1
2
3
4
5
6
7
8
9
10
11
创建表时创建:
create table hobby(id int(2) not null primary key, con varchar(20), index index_id (id));
mysql> show index from hobby;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| hobby |0| PRIMARY |1| id | A |0|NULL|NULL|| BTREE |||| hobby |1| index_id |1| id | A |0|NULL|NULL|| BTREE |||+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)
mysql> create table test4(id int(2) not null,name char(20),index index_test4(id,name));
Query OK,0 rows affected (0.02 sec)
mysql> show index from test4;+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment | Index_comment |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test4 |1| index_test4 |1| id | A |0|NULL|NULL|| BTREE |||| test4 |1| index_test4 |2| name | A |0|NULL|NULL| YES | BTREE |||+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10
5、全文索引
创建表时创建全文索引(必须为字符型字段)
mysql> create table test5(id int(2) not null,name char(20),fulltext(name));
Query OK,0 rows affected (0.04 sec)
mysql> show index from test5;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test5 |1| name |1| name |NULL|0|NULL|NULL| YES | FULLTEXT |||+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
1
2
3
4
5
6
7
8
9
在已有的表添加全文索引
mysql> create fulltext index index_name on test4(name);
Query OK,0 rows affected,1 warning (0.05 sec)
mysql> show index from test4;+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test4 |1| index_test4 |1| id | A |0|NULL|NULL|| BTREE |||| test4 |1| index_test4 |2| name | A |0|NULL|NULL| YES | BTREE |||| test4 |1| index_name |1| name |NULL|0|NULL|NULL| YES | FULLTEXT |||+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
1
2
3
4
5
6
7
8
9
10
11
修改表的方式添加全文索引
mysql> alter table info add fulltext index_city(address);
mysql> show index from info;+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| info |0| PRIMARY |1| id | A |5|NULL|NULL|| BTREE |||| info |1| index_id |1| id | A |5|NULL|NULL|| BTREE |||| info |1| index_name |1| name | A |5|NULL|NULL|| BTREE |||| info |1| index_city |1|
address |NULL|5|NULL|NULL| YES | FULLTEXT |||+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
1.5 删除索引
方法一:drop
mysql> drop index index_city on info;#drop index 索引名 on 表名;
1
1
方法二:alter
mysql> alter table info drop index index_name;#alter table 表名 drop index 索引名