背景

去年下半年，我在微信书架里加入了许多技术书籍，各种类别的都有，断断续续的读了一部分。

没有计划的阅读，收效甚微。

新年伊始，我准备尝试一下其他方式，比如阅读周。每月抽出1~2个非连续周，完整阅读一本书籍。

这个“玩法”虽然常见且板正，但是有效，已经坚持阅读十一个月。

已读完书籍：《架构简洁之道》、《深入浅出的Node.js》、《你不知道的JavaScript（上卷）》、《你不知道的JavaScript（中卷）》、《你不知道的JavaScript（下卷）》、《数据结构与算法JavaScript描述》、《WebKit技术内幕》、《前端架构：从入门到微前端》、《秒懂算法：用常识解读数据结构与算法》、《JavaScript权威指南》、《JavaScript异步编程设计快速响应的网络应用》、《编写可测试的JavaScript代码》。

当前阅读周书籍：《MySQL数据库入门》。

创建表的时候创建索引

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

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-1】　在t1表中id字段上建立索引，SQL语句如下：

CREATE TABLE t1(id INT,
                name VARCHAR(20),
                score FLOAT,
                INDEX (id)
               );

上述SQL语句执行后，使用SHOW CREATE TABLE语句查看表的结构，结果如下所示：

mysql＞SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
      Table: t1
Create Table: CREATE TABLE 't1' (
  'id' int(11) DEFAULT NULL,
  'name' varchar(20) COLLATE utf8_bin DEFAULT NULL,
  'score' float DEFAULT NULL,
  KEY 'id' ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
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
         type: ref
possible_keys: id
      key: id
    key_len: 5
      ref: const
      rows: 1
    Extra: Using where
1 row in set (0.03 sec)

从上述执行结果可以看出，possible_keys和key的值都为id，说明id索引已经存在，并且已经开始被使用了。

创建唯一性索引

【例1-2】　创建一个表名为t2的表，在表中的id字段上建立索引名为unique_id的唯一性索引，并且按照升序排列，SQL语句如下：

CREATE TABLE t2(id INT NOT NULL,
                name VARCHAR(20) NOT NULL,
                score FLOAT,
                UNIQUE INDEX unique_id(id ASC)
               );

上述SQL语句执行后，使用SHOW CREATE TABLE语句查看表的结构，结果如下所示：

mysql＞SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
        Table: t2
Create Table: CREATE TABLE 't2' (
  'id' int(11) NOT NULL,
  'name' varchar(20) COLLATE utf8_bin NOT NULL,
'score' float DEFAULT NULL,
  UNIQUE KEY 'unique_id' ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

从上述结果可以看出，id字段上已经建立了一个名称为unique_id的唯一性索引。

创建全文索引

【例1-3】　创建一个表名为t3的表，在表中的name字段上建立索引名为fulltext_name的全文索引，SQL语句如下：

CREATE TABLE t3(id INT NOT NULL,
                name VARCHAR(20) NOT NULL,
                score FLOAT,
                FULLTEXT INDEX fulltext_name(name)
                )ENGINE=MyISAM;

上述SQL语句执行后，使用SHOW CREATE TABLE语句查看表的结构，结果如下所示：

mysql＞SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
        Table: t3
Create Table: CREATE TABLE 't3' (
  'id' int(11) NOT NULL,
  'name' varchar(20) COLLATE utf8_bin NOT NULL,
  'score' float DEFAULT NULL,
  FULLTEXT KEY 'fulltext_name' ('name')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

从上述结果可以看出，name字段上已经建立了一个名为fulltext_name的全文索引。需要注意的是，由于目前只有MyISAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引，因此，在建立全文索引时，一定要注意表存储引擎的类型，对于经常需要索引的字符串、文字数据等信息，可以考虑存储到MyISAM存储引擎的表中。

创建单列索引

【例1-4】　创建一个表名为t4的表，在表中的name字段上建立索引名为single_name的单列索引， SQL语句如下：

CREATE TABLE t4(id INT NOT NULL,
                name VARCHAR(20) NOT NULL,
                score FLOAT,
                INDEX single_name(name(20))
                );

上述SQL语句执行后，使用SHOW CREATE TABLE语句查看表的结构，结果如下所示：

mysql＞SHOW CREATE TABLE t4\G
*************************** 1. row ***************************
        Table: t4
Create Table: CREATE TABLE 't4' (
  'id' int(11) NOT NULL,
  'name' varchar(20) COLLATE utf8_bin NOT NULL,
  'score' float DEFAULT NULL,
  KEY 'single_name' ('name')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

从上述结果可以看出，name字段上已经建立了一个名称为single_name的单列索引，并且索引的长度为20。

创建多列索引

【例1-5】　创建一个表名为t5的表，在表中的id和name字段上建立索引名为multi的多列索引，SQL语句如下：

CREATE TABLE t5(id INT NOT NULL,
                name VARCHAR(20) NOT NULL,
                score FLOAT,
                INDEX multi(id,name(20))
                );

上述SQL语句执行后，使用SHOW CREATE TABLE语句查看表的结构，结果如下所示：

mysql＞SHOW CREATE TABLE t5\G
*************************** 1. row ***************************
        Table: t5
Create Table: CREATE TABLE 't5' (
  'id' int(11) NOT NULL,
  'name' varchar(20) COLLATE utf8_bin NOT NULL,
  'score' float DEFAULT NULL,
  KEY 'multi' ('id','name')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

从上述结果可以看出，id和name字段上已经建立了一个名为multi的多列索引。需要注意的是，在多列索引中，只有查询条件中使用了这些字段中的第一个字段时，多列索引才会被使用。为了验证这个说法是否正确，将id字段作为查询条件，通过EXPLAIN语句查看索引的使用情况，SQL执行结果如下所示：

mysql＞EXPLAIN SELECT * FROM t5 WHERE id=1 \G
*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
      table: t5
           type: ref
possible_keys: multi
          key: multi
    key_len: 4
          ref: const
        rows: 1
      Extra:
1 row in set (0.09 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
           type: ALL
possible_keys: NULL
          key: NULL
    key_len: NULL
          ref: NULL
        rows: 1
      Extra: Using where
1 row in set (0.01 sec)

从上述执行结果可以看出，possible_keys和key的值都为NULL，说明multi索引还没有被使用。

创建空间索引

【例1-6】　创建一个表名为t6的表，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

CREATE TABLE t6(id INT,
                space GEOMETRY NOT NULL,
                SPATIAL INDEX sp(space)
                )ENGINE=MyISAM;
上述SQL语句执行后，使用SHOW CREATE TABLE语句查看表的结构，结果如下所示：mysql＞SHOW CREATE TABLE t6\G
*************************** 1. row ***************************
        Table: t6
Create Table: CREATE TABLE 't6' (
  'id' int(11) DEFAULT NULL,
  'space' geometry NOT NULL,
  SPATIAL KEY 'sp' ('space')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

从上述结果可以看出，t6表中的space字段上已经建立了一个名称为sp的空间索引。需要注意的是，创建空间索引时，所在字段的值不能为空值，并且表的存储引擎为MyISAM。

总结

创建表的时候可以直接创建索引，这种方式最简单、方便。

作者介绍
非职业「传道授业解惑」的开发者叶一一。
《趣学前端》、《CSS畅想》等系列作者。华夏美食、国漫、古风重度爱好者，刑侦、无限流小说初级玩家。
如果看完文章有所收获，欢迎点赞👍 | 收藏⭐️ | 留言📝。