MySQL在创建数据表的时候创建索引

桃扇骨 2023-09-24 10:06 56阅读 0赞

在MySQL中创建表的时候，可以直接创建索引。基本的语法格式如下：

CREATE TABLE 表名(字段名 数据类型 \[完整性约束条件\],

\[UNIQUE | FULLTEXT | SPATIAL\] INDEX | KEY

\[索引名\](字段名1 \[(长度)\] \[ASC | DESC\])

);

UNIQUE：可选。表示索引为唯一性索引。

FULLTEXT；可选。表示索引为全文索引。

SPATIAL：可选。表示索引为空间索引。

INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。

索引名：可选。给创建的索引取一个新名称。

字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。

长度：可选。指索引的长度，必须是字符串类型才可以使用。

ASC：可选。表示升序排列。

DESC：可选。表示降序排列。

创建一个普通索引时，不需要加任何UNIQUE、FULLTEXT或者SPATIAL参数。

实例：创建一个名为index1的数据表，在表内的id字段上建立一个普通索引。

1. 创建普通索引的SQL代码如下：

CREATE TABLE index1(id INT,

name VARCHAR(20),

sex BOOLEAN,

);

在DOS提示符窗口中查看MySQL创建普通索引的操作效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引][MySQL_MySQL]

从上图中可以看出，运行结果显示普通索引创建成功。

2. 使用SHOW CREATE TABLE语句查看表的结构。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_02][MySQL_MySQL_02]

从上图中可以看出，在id字段上已经建立了一个名为id的普通索引。

语句：

KEY \`id\` (\`id\`)

圆括号内的id是字段名称，圆括号左侧外面的id是索引名称。

3. 使用EXPLAIN语句查看索引是否被使用。SQL代码如下：

EXPLAIN SELECT \* FROM index1 where id=1 \\G

在DOS提示符窗口中查看使用EXPLAIN语句查看索引是否被使用的操作效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_03][MySQL_MySQL_03]

上图中的结果显示，possible\_keys和key的值都为id。说明id索引已经存在，并且查询时已经使用了索引。

如果使用UNIQUE参数进行约束，则可以创建唯一性索引。

实例：创建一个名为index2的数据表，在表内的id字段上建立一个唯一性索引，并且设置id字段以升序的形式排列。

1. 创建一个唯一性索引的SQL代码如下：

CREATE TABLE index2(id INT UNIQUE,

name VARCHAR(20),

);

index2\_id是为唯一性索引起的一个新名字。

在DOS提示符窗口中查看MySQL创建唯一性索引的操作效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_04][MySQL_MySQL_04]

从上图中可以看出，运行结果显示创建成功。

2. 使用SHOW CREATE TABLE语句查看表的结构。SQL代码如下：

SHOW CREATE TABLE index2 \\G

在DOS提示符窗口中查看使用SHOW CREATE TABLE语句查看表的结构的效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_05][MySQL_MySQL_05]

从上图中可以看出，在id字段上建立了名为id和index2\_id的两个唯一性索引。这样做，可以提高数据的查询速度。

如果在创建index2表时，id字段没有进行唯一性结束。如下所示：

CREATE TABLE index2(id INT,

name VARCHAR(20),

);

则也可以在id字段上成功创建名为index2\_id的唯一性索引。但是，这样可能达不到提高查询速度的目的。

全文索引使用FULLTEXT参数，并且只能在CHAR、VARCHAR或TEXT类型的字段上创建。

全文索引可以用于全文搜索。

现在，MyISAM存储引擎和InnoDB存储引擎都支持全文索引。

实例：创建一个名为index3的数据表，在表中的info字段上建立名为index3\_info的全文索引。

1. 创建全文索引的SQL代码如下：

CREATE TABLE index3(id INT,

info VARCHAR(20),

)ENGINE=MyISAM;

如果设置ENGINE=InnoDB，则可以在InnoDB存储引擎上创建全文索引。

在DOS提示符窗口中查看MySQL创建全文索引的操作效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_06][MySQL_MySQL_06]

从上图中可以看出，代码的执行结果显示创建成功。

2. 使用SHOW CREATE TABLE语句查看index3数据表的结构。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_07][MySQL_MySQL_07]

从上图中可以看出，在info字段上已经建立了一个名为index3\_info的全文索引。

我使用的是MySQL 5.6.19版本，已经可以在InnoDB存储引擎中创建全文索引了。

全文索引非常适合于大型数据集，对于小的数据集，它的用处可能比较小。

单列索引是在数据表的单个字段上创建的索引。一个表中可以创建多个单列索引。唯一性索引和普通索引等都为单列索引。

实例：创建一个名为index4的数据表，在表中的subject字段上建立名为index4\_st的单列索引。

1. 创建单列索引的SQL代码如下：

CREATE TABLE index4(id INT,

subject VARCHAR(30),

);

在DOS提示符窗口中查看MySQL创建单列索引的操作效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_08][MySQL_MySQL_08]

从上图中可以看出，代码执行的结果显示创建成功。

2. 使用SHOW CREATE TABLE语句查看index4数据表的结构。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_09][MySQL_MySQL_09]

从上图中可以看出，在subject字段上已经建立了一个名为index4\_st的单列索引。

注意：subject字段长度为30，而index4\_st设置的索引长度只有10，这样做是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，而只查询它前面的若干字符信息。

创建多列索引是在表的多个字段上创建一个索引。

实例：创建一个名为index5的数据表，在表中的name和sex字段上建立名为index5\_ns的多列索引。

1. 创建多列索引的SQL代码如下：

CREATE TABLE index5(id INT,

name VARCHAR(20),

sex CHAR(4),

);

在DOS提示符窗口中查看MySQL创建多列索引的操作效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_10][MySQL_MySQL_10]

从上图中可以看出，代码的执行结果显示index5\_ns索引创建成功。

2. 使用SHOW CREATE TABLE语句查看index5数据表的结构。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_11][MySQL_MySQL_11]

从上图中可以看出，name和sex字段上已经建立了一个名为index5\_ns的多列索引。

3. 多列索引中，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

先在index5数据表中添加一些数据记录，然后使用EXPLAIN语句可以查看索引的使用情况。如果只是使用name字段作为查询条件进行查询。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_12][MySQL_MySQL_12]

从上图中可以看出，possible\_keys和key的值都是index5\_ns。Extra(额外信息)显示正在使用索引。这说明使用name字段进行索引时，索引index5\_ns已经被使用。

4. 如果只使用sex字段作为查询条件进行查询。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_13][MySQL_MySQL_13]

从上图中可以看出，possible\_keys和key的值都是NULL。Extra(额外信息)显示正在使用where条件查询，而未使用索引。

使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。因此，在优化查询速度时，可以考虑优化多列索引。

使用SPATIAL参数能够创建空间索引。创建空间索引时，表的存储引擎必须是MyISAM类型。而且，索引字段必须有非空约束。

实例：创建一个名为index6的数据表，在表中的space字段上建立名为index6\_sp的空间索引。

1. 创建空间索引的SQL代码如下：

CREATE TABLE index6(id INT,

space GEOMETRY NOT NULL,

)ENGINE=MyISAM;

在DOS提示符窗口中查看MySQL创建空间索引的操作效果。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_14][MySQL_MySQL_14]

从上图可以看出，代码执行的结果显示空间索引创建成功。

2. 使用SHOW CREATE TABLE语句可看index6数据表的结构。如下图所示：

![MySQL在创建数据表的时候创建索引\_MySQL在创建数据表的时候创建索引\_15][MySQL_MySQL_15]

从上图中可以看出，在space字段上已经建立了一个名为index6\_sp的空间索引。

注意，space字段是非空的，而且数据类型是GEOMETRY类型。这个类型是空间数据类型。

空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到

[MySQL_MySQL]: http://www.baike369.com/content/images/201406/4631.gif
[MySQL_MySQL_02]: http://www.baike369.com/content/images/201406/4630.gif
[MySQL_MySQL_03]: http://www.baike369.com/content/images/201406/4629.gif
[MySQL_MySQL_04]: http://www.baike369.com/content/images/201406/4628.gif
[MySQL_MySQL_05]: http://www.baike369.com/content/images/201406/4627.gif
[MySQL_MySQL_06]: http://www.baike369.com/content/images/201406/4626.gif
[MySQL_MySQL_07]: http://www.baike369.com/content/images/201406/4625.gif
[MySQL_MySQL_08]: http://www.baike369.com/content/images/201406/4624.gif
[MySQL_MySQL_09]: http://www.baike369.com/content/images/201406/4623.gif
[MySQL_MySQL_10]: http://www.baike369.com/content/images/201406/4622.gif
[MySQL_MySQL_11]: http://www.baike369.com/content/images/201406/4621.gif
[MySQL_MySQL_12]: http://www.baike369.com/content/images/201406/4620.gif
[MySQL_MySQL_13]: http://www.baike369.com/content/images/201406/4619.gif
[MySQL_MySQL_14]: http://www.baike369.com/content/images/201406/4618.gif
[MySQL_MySQL_15]: http://www.baike369.com/content/images/201406/4617.gif