MySQL——数据库和表的基本操作（六）索引（1）创建索引①创建表的时候创建索引
要想使用索引提高数据表的访问速度，首先要创建一个常引。创建索引的方式有三种，具体如下。
创建表的时候可以直接创建索引，这种方式最简单、方便，其基本的语法格式如下所示:
CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件],
字段名 数据类型[完整性约束条件],
...
字段名 数据类型
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名](字段名1[(长度)][ASC|DESC]
);
关于上述语法的相关解释具体如下。
(1)UNIQUE:可选参数，表示唯一索引。
(2)FULLTEXT:可选参数，表示全文索引。
(3)SPATIAL:可选参数，表示空间索引。
(4)INDEX和 KEY:用来表示字段的索引，二者选一即可。
(5)别名:可选参数，表示创建的索引的名称。
(6)字段名1:指定索引对应字段的名称。
(7)长度:可选参数，用于表示索引的长度。
(8)ASC 和 DESC:可选参数，其中，ASC 表示升序排列，DESC 表示降序排列。
为了帮助读者更好地了解如何在创建表的时候创建索引，接下来，通过具体的案例分别对 MySQL 中的6种索引类型进行讲解，具体如下:
1)创建普通索引
例如，在 t1 表中 id 字段上建立索引，SQL 语句如下:
mysql> create table t1(id INT,
-> name VARCHAR(20),
-> score FLOAT,
-> INDEX (id)
-> );
Query OK, 0 rows affected (0.03 sec)
上述 SQL语句执行后，使用 SHOW.CREATE TABLE 语句查看表的结构，结果如下所示:
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`score` float DEFAULT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
从上述结果可以看出，id字段上已经创建了一个名称为id 的索引。为了查看索引是否被使用，可以使用 EXPLAIN 语句进行查看，SQL代码如下:
EXPLAIN SELECT * FROM t1 WHERE id=1 \G
执行结果如下所示:
mysql> EXPLAIN SELECT * FROM t1 WHERE id=1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: id
key: id
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
从上述执行结果可以看出，possible_keys 和 key 的值都为 id，说明id 索引已经存在并且已经开始被使用了。
2)创建唯一性索引
例如，创建一个表名为 t2 的表，在表中的id 字段上建立索引名为 unique_id 的唯一性索引，并且按照升序排列，SQL语句如下:
mysql> create table t2(id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> UNIQUE INDEX unique_id(id ASC)
-> );
Query OK, 0 rows affected (0.02 sec)
上述 SQL 语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
UNIQUE KEY `unique_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
从上述结果可以看出，id 字段上已经建立了一个名称为 unique_id 的唯一性索引。
3)创建全文索引
例如，创建一个表名为 t3 的表，在表中的 name 字段上建立索引名为 fulltextname 的全文索引，SQL语句如下:
mysql> create table t3(id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> FULLTEXT INDEX fulltext_name(name)
-> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
上述 SQL语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:
mysql> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
FULLTEXT KEY `fulltext_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
从上述结果可以看出，name字段上已经建立了一个名为 fulltext_name 的全文索引.需要注意的是，由于目前只有 MyISAM 存储引擎支持全文索引，InnoDB 存储引擎还不支持全文索引，因此，在建立全文索引时，一定要注意表存储引擎的类型，对于经常需要索引的字符串、文字数据等信息，可以考虑存储到 MyISAM 存储引擎的表中。
4)创建单列索引
例如，创建一个表名为 t4 的表，在表中的 name 字段上建立索引名为 single.name的单列索引，SQL语句如下:
mysql> create table t4(id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> INDEX single_name(name(20))
-> );
Query OK, 0 rows affected (0.01 sec)
上述 SQL语甸执行后，使用SHOW CREATE TABLE 语句登有衣的结构，结果如下所示:
mysql> show create table t4\G
*************************** 1. row ***************************
Table: t4
Create Table: CREATE TABLE `t4` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
KEY `single_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
从上述结果可以看出，name字段上已经建立了一个名称为 single_name 的单列索引，并且索引的长度为 20。
5)创建多列索引
例如，创建一个表名为 t5 的表，在表中的 id 和 name 字段上建立索引名为multi 的多列索引，SQL语句如下:
mysql> create table t5(id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> score FLOAT,
-> INDEX multi(id,name(20))
-> );
Query OK, 0 rows affected (0.02 sec)
上述 SQL语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:
mysql> show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`score` float DEFAULT NULL,
KEY `multi` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
从上述结果可以看出，id 和 name 字段上已经建立了一个名为 multi 的多列索引需要注意的是，在多列索引中，只有査询条件中使用了这些字段中的第一个字段时，多列索引才会被使用。为了验证这个说法是否正确，将id字段作为查询条件，通过 EXPLAD语句查看索引的使用情况，SQL执行结果如下所示:
mysql> EXPLAIN SELECT * FROM t5 WHERE id=1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
partitions: NULL
type: ref
possible_keys: multi
key: multi
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
从上述执行结果可以看出，possible_keys 和 key 的值都为 multi，说明 multi 索引已经存在，并且已经开始被使用了。但是，如果只使用 name 字段作为查询条件，SQL 执行结果如下所示:
mysql> EXPLAIN SELECT * FROM t5 WHERE name='Mike' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
从上述执行结果可以看出，possible_keys 和 key 的值都为 NULL，说明 multi 索引还没有被使用。
6)创建空间索引
例如，创建一个表名为 t6 的表，在空间类型为 GEOMETRY 的字段上创建空间索引，SQL 语句如下:
mysql> create table t6(id INT,
-> space GEOMETRY NOT NULL,
-> SPATIAL INDEX sp(space)
-> )ENGINE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.01 sec)
上述 SQL语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:
mysql> show create table t6\G
*************************** 1. row ***************************
Table: t6
Create Table: CREATE TABLE `t6` (
`id` int DEFAULT NULL,
`space` geometry NOT NULL,
SPATIAL KEY `sp` (`space`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
从上述结果可以看出，t6 表中的 space 字段上已经建立了一个名称为 sp 的空间索引。需要注意的是，创建空间索引时，所在字段的值不能为空值，并且表的存储引擎为MyISAM。