1、什么是索引？

  • 索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
  • 索引的优点是可以提高检索数据的速度
  • 索引的缺点是创建和维护索引需要耗费时间
  • 索引可以提高查询速度，会减慢写入速度

2、索引的分类

  • 普通索引
  • 唯一索引
  • 全文索引
  • 单列索引
  • 多列索引
  • 空间索引

3、如何创建索引

1、创建表的时候创建索引 

1
2
3
4
5
CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
…,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名称](字段名称[(长度)] [ASC|DESC])
);
  • 普通索引
1
2
3
4
5
6
CREATE TABLE test4(
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);
  • 唯一索引
1
2
3
4
5
6
CREATE TABLE test5(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) NOT NULL,
UNIQUE KEY uni_card(card)
);
  • 全文索引
1
2
3
4
5
6
CREATE TABLE test6(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
userDesc VARCHAR(20) NOT NULL,
FULLTEXT INDEX full_userDesc(userDesc)
);
  • 单列索引
1
2
3
4
5
6
7
8
CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);
  • 多列索引

　1、联合索引或符合索引 

1
2
3
4
5
6
7
8
CREATE TABLE test8(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2_t3(test1,test2,test3)
);

　2、唯一索引

1
2
3
4
5
6
7
8
CREATE TABLE test9(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)
);
  • 空间索引
1
2
3
4
5
CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;

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

1
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名 {字段名称[(长度)] [ASC|DESC]}
1
ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称(字段名称[(长度)] [ASC|DESC]);
  • 普通索引
    以test4为例，先删除所有索引：
1
2
DROP INDEX in_id ON test4;
DROP INDEX in_username ON test4;

然后分别用两种方式添加索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> CREATE INDEX in_id ON test4(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test4 ADD INDEX in_username(username);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test4;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test4 | CREATE TABLE `test4` (
  `id` tinyint(3) unsigned DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  KEY `in_id` (`id`),
  KEY `in_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 唯一索引
    先删除test5中的索引再创建：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> ALTER TABLE test5 DROP INDEX uni_card;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DROP INDEX username ON test5;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE UNIQUE INDEX uni_username ON test5(username);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test5;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test5 | CREATE TABLE `test5` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `card` char(18) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_username` (`username`),
  UNIQUE KEY `uni_card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 全文索引：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> DROP INDEX full_userDesc ON test6;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE FULLTEXT INDEX full_userDesc ON test6(userDesc);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test6;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test6 | CREATE TABLE `test6` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `userDesc` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  FULLTEXT KEY `full_userDesc` (`userDesc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 多列索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test8;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test8 | CREATE TABLE `test8` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `test1` varchar(20) NOT NULL,
  `test2` varchar(20) NOT NULL,
  `test3` varchar(20) NOT NULL,
  `test4` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mul_ti_t2_t3` (`test1`,`test2`,`test3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 空间索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> DROP INDEX spa_test ON test10;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE SPATIAL INDEX spa_test ON test10(test);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test10;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                         |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test10 | CREATE TABLE `test10` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `test` geometry NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `spa_test` (`test`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4、如何删除索引

1
DROP INDEX 索引名称 ON tbl_name

首先创建一个带索引的数据表：

1
2
3
4
5
6
7
mysql> CREATE TABLE test100(
    -> id TINYINT UNSIGNED,
    -> username VARCHAR(20),
    -> INDEX in_id(id),
    -> KEY in_username(username)
    -> );
Query OK, 0 rows affected (0.02 sec)

然后查看一下表结构：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW CREATE TABLE test100;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                         |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test100 | CREATE TABLE `test100` (
  `id` tinyint(3) unsigned DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  KEY `in_id` (`id`),
  KEY `in_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除索引：

1
2
3
4
5
6
7
mysql> DROP INDEX in_id ON test100;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DROP INDEX in_username ON test100;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

再看下表结构：

1
2
3
4
5
6
7
8
9
10
mysql> SHOW CREATE TABLE test100;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------+
| test100 | CREATE TABLE `test100` (
  `id` tinyint(3) unsigned DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)