1.为表的字段创建索引

索引就像书的目录一样，如果在字段上建立索引，那么以索引为条件时可以加快查询数据的速度。

1.1创建主键索引

查询数据库的内容，按主键查询是最快的，每个表只能有一个主键，但是可以有多个普通索引列，主键列要求所有内容必须唯一，而索引列不要求内容唯一。

我们无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引也可以对多列创建索引

建立主键索方法：

(1)方法一：在创建表时，可以增加建立主键索引语句

操作演示

mysql> use linzhongniaoDatabase changedmysql> create table student( -> id int(4) not null AUTO_INCREMENT, -> name char(20) not null, -> age tinyint(2) not null default '0', -> dept varchar(16) default null, -> primary key(id), -> KEY index_name(name) -> );Query OK, 0 rows affected (0.01 sec)mysql> desc student; +-------+-------------+------+-----+---------+----------------+| Field |   Type       | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+| id    | int(4)       | NO   | PRI | NULL    | auto_increment || name  | char(20)     | NO   | MUL | NULL    |                || age   | tinyint(2)   | NO   |     | 0       |                || dept  | varchar(16)  | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+4 rows in set (0.01 sec)

提示：

1、PRI为主键的标识，MUL为普通索引的标识。

2、AUTO_INCREMENT 自增

3、Primary key(id) 主键

4、KEY index_name(name) name 字段普通索引

(2)方法二：建立表之后通过alter命令增加主键索引（不推荐这种做法)

模拟添加主键，主键列不能重复创建，必须先删除上面的配置，如果要删除的主键有auto_increment自增长，先删除自增长再删除主键

删除自增长：

mysql> alter table student change id id int;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0

删除主键：

mysql> alter table student drop primary key;Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student; +-------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+| id     | int(11)     | NO   |     | 0       |       || name   | char(20)    | NO   | MUL | NULL    |       || age    | tinyint(2)  | NO   |     | 0       |       || dept   | varchar(16) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

建表时忘记加主键了利用alter命令增加id列为自增主键列

mysql>alter table student change id id int primary key auto_increment;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql>desc student; +-------+-------------+------+-----+---------+----------------+ | Field |         Type| Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | id    | int(11)     | NO   | PRI | NULL    | auto_increment | | name  | char(20)    | NO   | MUL | NULL    |                | | age   | tinyint(2)  | NO   |     | 0       |                | | dept  | varchar(16) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

提示：只有int类型且为primary key才可以使用auto_increment。

1.2 创建普通索引

方法一：在建表时，可以增加建立普通索引列的语句如下：

操作演示：

mysql>drop table student;Query OK, 0 rows affected (0.00 sec)system@ceshi 04:2333->create table student( -> id int(4) not null AUTO_INCREMENT, -> name char(20) not null, -> age tinyint(2) NOT NULL default '0', -> dept varchar(16) default NULL, -> primary key(id), -> KEY index_name(name) -> );Query OK, 0 rows affected (0.00 sec)system@ceshi 04:2609->desc student; +-------+-------------+------+-----+---------+----------------+| Field  |         Type| Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+| id     | int(4)      | NO   | PRI | NULL    | auto_increment || name   | char(20)    | NO   | MUL | NULL    |                || age    | tinyint(2)  | NO   |     | 0       |                || dept   | varchar(16) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

提示：

KEY index_name(name) 表中的name字段普通索引

方法二：建表后利用alter增加普通索引

删除建表时创建的index_name索引

mysql> alter table student drop index index_name;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student; +-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+| id    | int(4)      | NO   | PRI | NULL    | auto_increment || name  | char(20)    | NO   |     | NULL    |                || age   | tinyint(2)  | NO   |     | 0       |                || dept  | varchar(16) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

在name列上添加索引，索引名为index_name

mysql> alter table student add index index_name(name);Query OK, 0 rows affected (0.14 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc student; +-------+-------------+------+-----+---------+----------------+| Field  | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+| id     | int(4)      | NO   | PRI | NULL    | auto_increment || name   | char(20)    | NO   | MUL | NULL    |                || age    | tinyint(2)  | NO   |     | 0       |                || dept   | varchar(16) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

1.3对字段的前n个字符创建普通索引

当遇到表中比较大的列时，列内容的前n个字符在所有内容中已经接近唯一时，这时可以对列的前n个字符建立索引而无需对整个列建立索引，这样可以节省创建索引占用的系统空间，以及降低和更新维护消耗的系统资源。

对条件列的前n个字符创建普通索引的语法：

create index index_name on student(name(8));

实战操作：

mysql>create index index_dept on student(dept(8));Query OK, 0 rows affected (0.19 sec)Records: 0  Duplicates: 0  Warnings: 0system@ceshi 06:3650->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | MUL | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | MUL | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)system@ceshi 06:3847->show index from student\G *************************** 1. row ***************************   Table: student  Non_unique: 0Key_name: PRIMARYSeq_in_index: 1 Column_name: id   Collation: A Cardinality: 0Sub_part: NULL  Packed: NULLNull:   Index_type: BTREE Comment:  *************************** 2. row ***************************   Table: student  Non_unique: 1Key_name: index_nameSeq_in_index: 1 Column_name: name   Collation: A Cardinality: NULLSub_part: NULL  Packed: NULLNull:   Index_type: BTREE Comment:  *************************** 3. row ***************************   Table: student  Non_unique: 1Key_name: index_deptSeq_in_index: 1 Column_name: dept   Collation: A Cardinality: NULLSub_part: 8  Packed: NULLNull: YES  Index_type: BTREE Comment: 3 rows in set (0.00 sec)

1.4 为表的多个字段创建联合索引

如果查询的条件是多列时，我们可以为多个查询的列创建联合索引，甚至可以为多列的前n个字符创建联合索引，实战演示如下：

(1)为多个列创建索引

mysql> create index index_name_dept on student(name,dept);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from student\G *************************** 1. row ***************************Table: student   Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1  Column_name: idCollation: A  Cardinality: 0 Sub_part: NULL   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 2. row ***************************Table: student   Non_unique: 1 Key_name: index_name Seq_in_index: 1  Column_name: nameCollation: A  Cardinality: 0 Sub_part: NULL   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 3. row ***************************Table: student   Non_unique: 1 Key_name: index_dept Seq_in_index: 1  Column_name: nameCollation: A  Cardinality: 0 Sub_part: 8   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 4. row ***************************Table: student   Non_unique: 1 Key_name: index_name_dept Seq_in_index: 1  Column_name: nameCollation: A  Cardinality: 0 Sub_part: NULL   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 5. row ***************************Table: student   Non_unique: 1 Key_name: index_name_dept Seq_in_index: 2  Column_name: deptCollation: A  Cardinality: 0 Sub_part: NULL   Packed: NULL Null: YES   Index_type: BTREE  Comment: Index_comment: 5 rows in set (0.01 sec)

(2)多个列的前n个字符创建联合索引

首先删除普通索引，方法一：

system@ceshi 06:5144->drop index ind_name_dept on student; 删除索引的另一种方法。Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

方法二：

mysql> alter table student drop index index_name_dept;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0

演示：为name和dept字段的前n个字符设置索引

mysql> create index index_name_dept on student(name(8),dept(10));Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from student\G  *************************** 1. row ***************************Table: student   Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1  Column_name: idCollation: A  Cardinality: 0 Sub_part: NULL   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 2. row ***************************Table: student   Non_unique: 1 Key_name: index_name Seq_in_index: 1  Column_name: nameCollation: A  Cardinality: 0 Sub_part: NULL   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 3. row ***************************Table: student   Non_unique: 1 Key_name: index_dept Seq_in_index: 1  Column_name: nameCollation: A  Cardinality: 0 Sub_part: 8   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 4. row ***************************Table: student   Non_unique: 1 Key_name: index_name_dept Seq_in_index: 1  Column_name: nameCollation: A  Cardinality: 0 Sub_part: 8   Packed: NULL Null:    Index_type: BTREE  Comment: Index_comment:  *************************** 5. row ***************************Table: student   Non_unique: 1 Key_name: index_name_dept Seq_in_index: 2  Column_name: deptCollation: A  Cardinality: 0 Sub_part: 10   Packed: NULL Null: YES   Index_type: BTREE  Comment: Index_comment: 5 rows in set (0.00 sec)

提示：

1、按条件列查询数据时，联合索引是有前缀生效特性的

2、index(a,b,c)仅a、ab、abc三个查询条件可以走索引。b,bc,ac,c不能走索引

1.5 创建唯一索引（非主键索引）

语法：create unique index index_age on student(age);

唯一索引是用来约束表的内容的，不能重复。

system@ceshi 07:0828->create unique index uni_ind_name on student(name);Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0system@ceshi 07:1010->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | UNI | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | MUL | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

2.索引表的创建及生效条件

问题1：既然索引可以加快查询速度，那么是不是就要给所有的列创建索引呢？

解答：不对因为创建索引不但占用系统空间，更新数据库时还需要维护索引数据。因此索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需创建索引，更新频繁，读取少的业务要少建立索引。

问题2：需要在哪些列上创建索引呢？

解答：Select user,host from mysql.user where host= ….索引一定要创建在where后的条件列上，而不是select后的选择数据的列上，另外我们要尽量选择在唯一值多的大表上建立索引。

3.创建索引命令集合小结

添加主键索引

alter table student change id id int primary key auto_increment;

删除主键索引，如果删除主键有自增长先删除自增长

alter table student change id id int;alter table student drop primary key

创建普通索引

alter table student add index index_dept(dept)

根据列的前n个字符创建索引

create index index_dept on student(dept(8))

根据多个列创建联合索引

create index index_name_dept on student(name,dept);

根据多个列的前n个字符创建索引

create index index_name_dept on student(name(8),dept(10));

删除普通索引,两种方法：

alter table student drop index index_deptdrop index ind_name_dept on student

创建唯一索引

create unique index uni_ind_name on student(name);

创建索引总结：

a.要在表的列上创建索引。

b.索引会加快查询速度，但是会影响更新的速度，因为要维护索引。

c.索引不是越多越好，要在频繁查询的where后的条件列上创建索引。

d.小表或唯一值极少的列上不建索引，要在大表以及不同内容多的列上创建索引。