MySQL讲义第16讲——索引（INDEX）的创建

创建索引分两种情况：（1）在创建表时同时创建索引。（2）创建表之后有需要时再创建索引。

一、创建表的同时创建索引

CREATE TABLE 表名( <字段名> <数据类型> [完整性约束], .... , [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (字段名[(长度)] [ASC|DESC]) ); 说明： （1）UNIQUE：表示索引为唯一索引。 （2）FULLTEXT：表示索引为全文索引。 （3）SPATIAL：表示索引为空间索引。 （4）INDEX 和 KEY：用于指定字段为索引，两者选择其一，作用相同。 （5）索引名：给创建的索引取一个新名称。如果不指定则采用字段名作为索引名。 （6）字段名：指定索引对应的字段的名称。 （7）长度：指索引的长度，字符串类型才可以使用。 （8）ASC：表示升序排列。 （9）DESC：表示降序排列。

举例：创建一张表 employee，并且为相应的字段创建索引。

create table dept ( dept_id int primary key, dept_name char(20) ); create table emp ( e_id int primary key auto_increment, e_name char(20), birth datetime, salary decimal(10,2), phone char(20), addr varchar(100), dept_id int, foreign key(dept_id) references dept(dept_id), index idx_ename(e_name), unique key uq_idx_phone(phone), index idx_addr(addr(10)) );

查看 dept 表的索引：

mysql> show index from dept\G *************************** 1. row *************************** Table: dept Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: dept_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)

查看 emp 表的索引：

mysql> show index from emp\G --- 主键索引 *************************** 1. row *************************** Table: emp Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: e_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: -- 唯一索引 *************************** 2. row *************************** Table: emp Non_unique: 0 Key_name: uq_idx_phone Seq_in_index: 1 Column_name: phone Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: -- 普通索引（创建外键约束时自动创建的索引） *************************** 3. row *************************** Table: emp Non_unique: 1 Key_name: dept_id Seq_in_index: 1 Column_name: dept_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: -- 普通索引 *************************** 4. row *************************** Table: emp Non_unique: 1 Key_name: idx_ename Seq_in_index: 1 Column_name: e_name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: -- 指定长度的索引，根据 addr 的前 10 个字符创建索引 *************************** 5. row *************************** Table: emp Non_unique: 1 Key_name: idx_addr Seq_in_index: 1 Column_name: addr Collation: A Cardinality: 0 Sub_part: 10 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 5 rows in set (0.01 sec)

二、在已经存在的表上创建索引

如果一个表已经创建好，当需要为某个字段创建索引时可以采用两种方法，一是使用 alter table 命令，二是使用 create index 命令。

1、使用 alter table 命令添加索引

格式如下：

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名](字段名[(长度)] [ASC | DESC]);

举例：创建一张表 stu，然后添加索引。

create table stu ( s_id int primary key auto_increment, s_name char(20), gender char(2), birth datetime, phone char(20), weight decimal(4,1), class char(10) ); mysql> alter table stu add index idx_sname (s_name); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table stu add unique index uq_idx_phone (phone); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table stu add index idx_class_sname (class,s_name); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0

查看 stu 表的索引：

mysql> show index from stu\G *************************** 1. row *************************** Table: stu Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: s_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: stu Non_unique: 0 Key_name: uq_idx_phone Seq_in_index: 1 Column_name: phone Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: stu Non_unique: 1 Key_name: idx_sname Seq_in_index: 1 Column_name: s_name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: stu Non_unique: 1 Key_name: idx_class_sname Seq_in_index: 1 Column_name: class Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: stu Non_unique: 1 Key_name: idx_class_sname Seq_in_index: 2 Column_name: s_name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 5 rows in set (0.00 sec)

2、使用 create index 命令添加索引

格式如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名);

举例：创建一张 book 表，并添加索引。

create table book ( id int primary key auto_increment, name char(20), isbn char(20), author char(20), publisher char(100), price decimal(10,2) ); mysql> create index idx_name on book(name); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create unique index uq_idx_isbn on book(isbn); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index idx_publisher_price on book(publisher,price); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

查看 book 表中的索引：

mysql> show index from book\G *************************** 1. row *************************** Table: book Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: book Non_unique: 0 Key_name: uq_idx_isbn Seq_in_index: 1 Column_name: isbn Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: book Non_unique: 1 Key_name: idx_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: book Non_unique: 1 Key_name: idx_publisher_price Seq_in_index: 1 Column_name: publisher Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: book Non_unique: 1 Key_name: idx_publisher_price Seq_in_index: 2 Column_name: price Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 5 rows in set (0.00 sec)