第9章索引

淩亂°似流年 2022-06-14 01:18 245阅读 0赞

# 概述 #

这里的内容大部分是我学习书本上的内容的内容总结，加上自己的一些理解  
索引可以快速的找到一行记录。如果不使用索引，那么就需要从第一条开始逐条去找，最后找到需要的记录，而索引并不是逐条去查找，而是使用了某些算法，快速去定位。表越大，逐条查询的代价也就越高。这时候如果有索引，就可以快速的定位到位置了。

# 索引简介 #

我们知道要去查询一个sql，先要去数据库管理系统中去查询，然后数据库管理系统去查找文件中的记录，而索引是单独的一个文件，他是存储在磁盘上的文件，其中的内容是记录的数据库中数据的引用指针。使用索引可以快速的找到特定值的行，所以，mysql的列都可以被索引，使用索引是增加数据库查询效率的最简单高效的方式了。

例如：现在有30万条数据，在没有索引的情况下，执行sql语句：

SELECT * from mw_sms_send_record WHERE channel_id=1;

执行要遍历整个表，直到找到这条记录为止，最终的结果是0.453s。  
![这里写图片描述][SouthEast]

但是加上索引以后呢，查询需要的时间只需要0.022s  
![这里写图片描述][SouthEast 1]

效率直接提升了12倍，这个差距会随着数据量的增加而进一步扩大，可见，索引对于查询效率的提升是非常明显的。

索引是存储引擎实现的，所以不同的存储引擎的实现方式是不同的，并且，不同的存储引擎也不一定支持所有的索引类型。MySQL的索引类型有BETREE和HASH两种，MyISAM和InnoDB只支持BETREE索引，MEMORY/HEAP可以支持BETREE和HASH两种。

索引的优点：  
1. 创建唯一索引，可以保证数据表中的数据唯一性。  
2. 当然是最主要的，查询速度快了  
3. 在实现数据参考性方面，可以加快表的连接  
4. 分组和排序的效率提高。

有优点自然有缺点，下面就来说一下缺点：  
1. 创建和维护索引要耗费时间，因为我们在创建索引的时候要在磁盘中创建索引文件。  
2. 占用磁盘空间，这一点可能对于现在来说最无所谓了，因为现在磁盘空间相对人力成本来说还是太便宜的。  
3. 表中的数据增删改的时候，索引文件也要相应的做增删改查，所以虽然增加了查询效率，但是降低了增删改的效率。

### 9.1.2 索引的分类 ###

索引分为以下几类：

#### 1.普通索引和唯一索引 ####

普通索引是MySQL中最基本的一种索引，他允许空值和重复值  
唯一索引，索引列的值必须唯一，但是可以有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，但是不允许有空值。

#### 2.单列索引和组合索引 ####

单例索引就是这个索引中只有一列，一个表中可以有多个单例索引。  
组合索引就是多个列值组成的索引，在查询时遵循最左侧匹配的原则，也就是说，按左侧开始如果匹配的话，这个索引就是对这个查询生效的。

#### 3.全文索引 ####

全文索引类型为FULLTEXT，在定义全文索引的列上支持全文查找，允许列中的值为重复值和空值。全文索引可以在CHAR,VARCHAR,TEXT字段的类型上创建。MySQL只有MyISAM存储引擎支持全文检索。

#### 4.空间索引 ####

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是：GEOMETRY、JPOINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

### 9.1.3 索引的设计原则 ###

1.  并不是索引越多越好，因为索引会影响INSERT、UPDATE、DELETE的速度。
2.  应该避免为经常更新的表进行索引的操作，对于经常查询的字段应该增加索引，但要避免添加不必要的字段。
3.  数据量比较小的表不适宜增加索引，因为数据量比较小的表可能直接查库的速度会比较快。
4.  如果在不同项比较多的列中创建索引会比较适合，如果该列中的项比较少，比如性别这样的列，就没有必要加索引，因为增加索引，不但不会提高效率，反而会降低效率。
5.  在定义唯一性索引的时候，需要确认数据的完整性，这样可以提高查询速度。
6.  在频繁进行排序和分组的列上创建索引，可以加快速度。

## 9.2创建索引 ##

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引或者使用CREATE INDEX 语句在已存在的表上添加索引。本节将介绍这三种方法。

### 9.2.1 创建表的时候创建索引 ###

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

CREATE TABLE table_name[col_name data_type]
    [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]

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

#### 1.创建普通索引 ####

最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。  
在book表中的year\_publication字段上建立普通索引，SQL语句如下：

CREATE TABLE book( bookid INT NOT NULL, bookname VARCHAR(255) NOT NULL, authors VARCHAR(255) NOT NULL, info VARCHAR(255) NULL, comment VARCHAR(255) NULL, year_publication YEAR NOT NULL, INDEX(year_publication) );

查看创建之后的DDL如下：  
![这里写图片描述][SouthEast 2]

可以看到索引已经建立了，名称为year\_publication，为自动创建的，可以使用explain查看：  
![这里写图片描述][SouthEast 3]

EXPLAIN各行解释如下：  
select\_type:指定了查询的类型，可能的值有SIMPLE,PRIMARY,UNION,SUBQUERY等,这里为SIMPLE，表示是简单查询，不使用UNION或子查询。  
table：表示读取的数据库表名称，如果有多个表，则按照调用的先后顺序排序  
type：指定了本数据表和其他数据表之间的关联关系，可能的取值有system、const、eq\_ref、ref、range、index和All  
possible\_keys:给出了MySQL在搜索数据记录时可选用的各个索引。  
key行是MySQL实际选用的索引。  
key\_len行给出索引按字节计算的长度，key\_len数值越小，表示越快  
ref行给出了关联关系中另一个数据表里的数据列的名字。  
rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。  
extra行提供了与关联操作有关的信息。  
可以看到，possible\_keys和key的值都为year\_publication，查询时使用了索引。

但是感觉大家还是一知半解的吧，我们就一项一项的详细的举例说明好了，首先是select\_type，这里只有SIMPLE的例子，我们构造一个PRIMARY的例子：

EXPLAIN SELECT article, dealer, price FROM shop WHERE price = (SELECT MAX(price) FROM shop);

其结果如图：  
![这里写图片描述][SouthEast 4]

可以看到，外面的查询对应着PRIMARY这条记录，而where中的子查询则对应着SUBQUERY

#### 2.创建唯一索引 ####

创建索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。  
　　【例9.2】创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引。

CREATE TABLE t1( id INT NOT NULL, name CHAR(30) NOT NULL, UNIQUE INDEX Uniqidx(id) );

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：  
　　![这里写图片描述][SouthEast 5]

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

#### 3.创建单例索引 ####

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。前面两个例子中创建的索引都为单列索引。  
　　【例9.3】创建一个表t2，在表中的name字段上创建单列索引。  
　　表结构如下：

CREATE TABLE t2( id INT NOT NULL, name CHAR(50) NULL, INDEX SingleIdx(name(20)) );

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：  
![这里写图片描述][SouthEast 6]

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

#### 4.创建组合索引 ####

组合索引是在多个字段上创建一个索引。  
　　【例9.4】创建表t3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

CREATE TABLE t3( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), INDEX MultiIdx(id,name(20),age) );

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

![这里写图片描述][SouthEast 7]

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

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

在t3表中，查询id和name字段，使用EXPLAIN语句查看索引的使用情况：  
　　![这里写图片描述][SouthEast 8]

可以看到，查询id和那么字段时，使用了名称MultiIdx的索引，如果查询（name，age）组合或者单独查询name和age字段，结果如下：  
　　![这里写图片描述][SouthEast 9]

此时，possible\_keys和key为NULL，并没有使用在t3表中创建的索引进行查询。

#### 5.创建全文索引 ####

FULLTEXT全文检索可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部（前缀）索引。  
　　【例 9.5】创建表t4，在表中的info字段上建立全文索引，SQL语句如下：

CREATE TABLE t4( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX FullTextIdx(info) )ENGINE=MyISAM;

> 提示：因为MySQL5.5中默认存储引擎为InnoDB，在这里创建表时需要修改表的存储引擎为MyISAM，不然创建索引会出错。

语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：  
　　![这里写图片描述][SouthEast 10]

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

#### 6. 创建空间索引 ####

空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。  
　　【例 9.6】创建表t5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

CREATE TABLE t5( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：  
　　![这里写图片描述][SouthEast 11]

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

### 9.2.2 在已经存在的表上创建索引 ###

在已经存在的表中创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句，本节将介绍如何使用ALTER TABLE和CREATE INDEX语句在已知表字段上创建索引。

#### 1. 使用ALTER TABLE语句创建索引 ####

ALTER TABLE创建索引的基本语法如下：

ALTER TABLE table_name ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]
    [index_name] (col_name[length],...)[ASC|DESC]

与创建表时创建索引的语法不同的是，在这里使用了ALTER TABLE和ADD关键字，ADD表示向表中添加索引。  
　　【例 9.7】在book表中的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：

ALTER TABLE book ADD INDEX BkNameIdx (bookname(30));

添加索引之前，使用SHOW INDEX语句查看指定表中创建的索引：  
　　![这里写图片描述][SouthEast 12]  
　　其中各个主要参数的含义为：  
　　（1）Table表示创建索引的表。  
　　（2）Non\_unique表示索引非唯一，1代表非唯一索引，0代表唯一索引。  
　　（3）Key\_name表示索引的名称。  
　　（4）Seq\_in\_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段  
　　（5）  
　　（6）  
　　（7）  
　　（8）  
　　（9）

[SouthEast]: /images/20220614/7de58adca1ea41d5925e6bccba37996a.png
[SouthEast 1]: /images/20220614/73b56eda77a74a3ba3c404662394ae25.png
[SouthEast 2]: /images/20220614/f8af13e2602843419b10e10c174dfc54.png
[SouthEast 3]: /images/20220614/2203de0af88d42109610fcc7e72662ce.png
[SouthEast 4]: /images/20220614/3de4d6875e0f4b6f81aa670ab2174f9e.png
[SouthEast 5]: /images/20220614/4a7c769af2a44f2aa3ba63d2d9530760.png
[SouthEast 6]: /images/20220614/26d692c591ab4de9ba4b2cbda030553b.png
[SouthEast 7]: /images/20220614/f06e431309f04f00a275bc50043b12d9.png
[SouthEast 8]: /images/20220614/af3ac6ac27cd49f2a339bcdb7c1e9800.png
[SouthEast 9]: /images/20220614/5d6e7b70411e4abb9fefb48c93e744b7.png
[SouthEast 10]: /images/20220614/af0033c61b81459596b3d0432001e4bb.png
[SouthEast 11]: /images/20220614/5f59ddbd72324bb6902bb241ff4a7337.png
[SouthEast 12]: /images/20220614/7140873a6c2a44ada11abef43f2af5dd.png