当前位置：首页 > news >正文

MySQL——索引（二）创建索引（1）创建表的时候创建索引

news 来源：原创 2024/9/20 12:01:25

要想使用索引提高数据表的访问速度，首先要创建一个常引。创建索引的方式有三种，具体如下。

创建表的时候可以直接创建索引，这种方式最简单、方便，其基本的语法格式如下所示:

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: 1select_type: SIMPLEtable: t1partitions: NULLtype: ref
possible_keys: idkey: idkey_len: 5ref: constrows: 1filtered: 100.00Extra: 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: 1select_type: SIMPLEtable: t5partitions: NULLtype: ref
possible_keys: multikey: multikey_len: 4ref: constrows: 1filtered: 100.00Extra: 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: 1select_type: SIMPLEtable: t5partitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra: 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。