深入学习MySQL——如何创建索引（一），躬身实战出真知

前言

昨天咱们简单介绍了MySQL索引的整体概述，让大家有一个初步的认识。今天，咱们就开始具体实战：创建MySQL索引。

 

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句CREATETABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。后续咱们将详细介绍这3种方法。

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束， 而不论创建那种约束，在定义约束的同时相当于在指定列上创建了一个索引。创建表时创建索引的基本语法格式如下：

 

UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、 全文索引和空间索引； INDEX与KEY为同义词，两者作用相同，用来指定创建索引； col_ name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择； index_ name指定索引的名称，为可选参数，如果不指定，MySQL默认col name为索引值； length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度； ASC 或DESC指定升序或者降序的索引值存储。

创建普通索引

最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。

在book表中的year_ publication 字段上建立普通索引，SQL语句如下：

 

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

 

 

由结果可以看到，book 表的year_ publication 字段上成功建立索引，其索引名称year_ publication 为MySQL自动添加。使用EXPLAIN语句查看索引是否正在使用：

 

EXPLAIN语句输出结果的各个行解释如下：

(1 )select_ type 行指定所使用的SELECT查询类型，这里值为SIMPLE,表示简单的SELECT, 不使用UNION或子查询。其他可能的取值有： PRIMARY、UNION、SUBQUERY等。

(2) table 行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。

(3) type行指定了本数据表与其他数据表之间的关联关系，可能的取值有system、const、eq_ref、ref、range、index和All。

(4) possible_ keys 行给出了MySQL在搜索数据记录时可选用的各个索引。

(5) key行是MySQL实际选用的索引。

(6) key_ len 行给出索引按字节计算的长度，key_ len 数值越小，表示越快。

(7) ref行给出了关联关系中另一个数据表里的数据列的名字。

(8)rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。

(9) extra 行提供了与关联操作有关的信息。

可以看到，possible_ keys 和key的值都为year_ publication, 查询时使用了索引。

创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的就是：索引列的值必须唯一, 但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引。

 

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构:

 

由结果可以看到，id 字段上已经成功建立了一个名为UniqIdx的唯一索引。

创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。前面两个例子中创建的索引都为单列索引。

创建一个表t2,在表中的name字段上创建单列索引。

表结构如下:

 

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构:

 

由结果可以看到，id 字段上已经成功建立了一个名为SingleIdx的单列索引，索引长度为20。

创建组合索引

组合索引是在多个字段上创建一个索引。

创建表t3,在表中的id、name和age字段上建立组合索引，SQL语句如下:

 

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构:

 

由结果可以看到，id. name 和age字段上已经成功建立了一个名为Multildx的组合索引。

组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”:利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如这里由id、name 和age 3个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合: (id, name, age)、(id, name)或者id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如(age) 或者(name,age) 组合则不能使用索引查询。

在t3表中，查询id和name字段，使用EXPLAIN语句查看索引的使用情况:

 

 

可以看到，查询id和name字段时，使用了名称Multildx的索引，如果查询(name,age)组合或者单独查询name和age字段，结果如下:

 

此时，possible_ keys 和key值为NULL,并没有使用在t3表中创建的索引进行查询。

创建全文索引

FULLTEXT (全文索引)可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR 和TEXT列创建索引。索引总是对整个列进行，不支持局部(前缀)索引。

创建表t4，在表中的info字段上建立全文索引，SQL语句如下:

 

因为MySQL 5.7 中默认存储引擎为InnoDB, 在这里创建表时需要修改表的存储引擎为MyISAM,不然创建索引会出错。

语句执行完毕之后，使用SHOW CREATE TABLE查看表结构:

 

 

由结果可以看到，info 字段上已经成功建立了一个名为FulTxtldx的FULLTEXT索引。全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。

创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。

创建表t5,在空间类型为GEOMETRY的字段上创建空间索引，SQL 语句如下:

 

可以看到，t5 表的g字段上创建了名称为spatIdx的空间索引。注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。

今天只是讲解了其中一种MySQL索引方式：创建表的时候创建索引，就是希望大家能够深入理解，每天进步一点点，留给大家充分的自我思考时间，只有这样，学习思考，才能更好的理解。

喜欢请多多点赞评论分享，关注小编，你们的支持就是小编最大的动力~~~