MySQL——数据库和表的基本操作（六）索引（1）创建索引①创建表的时候创建索引

Code repairman

已于 2024-08-29 17:13:22 修改

阅读量1.6k

点赞数 33

分类专栏： MySQL 文章标签：数据库 mysql

于 2024-08-08 09:08:53 首次发布

本文链接：https://blog.csdn.net/W_Fe5/article/details/141011734

版权

MySQL 专栏收录该内容

97 篇文章 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。