MySQL笔记-08 索引-CSDN博客

本文链接：https://blog.csdn.net/qui910/article/details/128231165

文章目录

1 索引概述
- 1.1 MySQL索引分类
2 创建索引
3 删除索引
4 索引设计原则
5 HASH索引
6 BTREE索引

索引是一种特殊的数据库结构，是提高数据库性能的重要方式，可以用来快速查询数据库表中的特定记录，MySQL中所有的数据类型都可以被索引。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。本章将介绍索引的概念、作用、不同类别，用不同的方法创建索引以及删除索引的方法等。

1 索引概述

在MySQL中，索引由数据表中一列或多列组合而成，创建索引的目的是为了优化数据库的查询速度。其中，用户创建的索引指向数据库中具体数据所在位置。当用户通过索引查询数据库中的数据时，不需要遍历所有数据库中的所有数据，大幅度提高了查询效率。

凡事都有双面性，使用索引可以提高检索数据的速度，对于依赖关系的子表和父表之间的联合查询时，可以提高查询速度，并且可以提高整体的系统性能。但是，创建索引和维护需要耗费时间，并且该耗费时间与数据量的大小成正比；另外，索引需要占用物理空间，给数据的维护造成很多麻烦。

整体来说，索引可以提高查询的速度，但是会影响用户操作数据库的插入操作。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序。所以，用户可以将索引删除后，插入数据，当数据插入操作完成后，用户可以重新创建索引。

不同的存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引。总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。索引有两种存储类型，包括B树（BTREE）索引和哈希（HASH）索引。其中，B树为系统默认索引方法。

MySQL8.0之前不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引。从MySQL8.0开始，才支持函数索引。

1.1 MySQL索引分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

1.1.1 普通索引

普通索引，即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。创建该类型索引后，用户在查询时，便可以通过索引进行查询。在某数据表的某一字段中，建立普通索引后。用户需要查询数据时，只需根据该索引进行查询即可。

1.1.2 唯一性索引

使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录，主键是一种特殊唯一索引。

1.1.3 全文索引

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，查询带有文章回复内容的字段，可以应用全文索引方式。需要注意的是，在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序后，可以执行大小写敏感的全文索引。

1.1.4 单列索引

顾名思义，单列索引即只对应一个字段的索引。其可以包括上述叙述的3种索引方式。应用该索引的条件只需要保证该索引值对应一个字段即可。

1.1.5 多列索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段中的第一个字段。

1.1.6 空间索引

使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

2 创建索引

创建索引是指在某个表中至少一列中建立索引，以便提高数据库性能。其中，建立索引可以提高表的访问速度。介绍几种不同的方式创建索引：

在建立数据库时创建索引。
在已经建立的数据表中创建索引。
修改数据表结构创建索引。

2.1 在建立数据表时创建索引

在建立数据表时可以直接创建索引，这种方式比较直接，且方便、易用。在建立数据表时创建索引的基本语法结构如下：

create table table_name(
属性名 数据类型[约束条件]，
属性名 数据类型[约束条件]
……
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL ] INDEX }KEY
[别名]( 属性名1 [(长度)] [ASC | DESC])
);

属性名后的属性值，其含义如下:

（1）UNIQUE：可选项，表明索引为唯一性索引。
（2）FULLTEXT：可选项，表明索引为全文搜索。
（3）SPATIAL：可选项，表明索引为空间索引。

INDEX和KEY参数用于指定字段索引，用户在选择时，只需要选择其中的一种即可；

别名为可选项，其作用是给创建的索引取新名称；别名的参数如下:

（1）属性名1：指索引对应的字段名称，该字段必须被预先定义。
（2）长度：可选项，指索引的长度，必须是字符串类型才可以使用。
（3）ASC/DESC：可选项，ASC表示升序排列，DESC参数表示降序排列。

2.1.1 普通索引创建

创建普通索引，即不添加UNIQUE、FULLTEXT等任何参数。

create table score(
id int(11) auto_increment primary key not null，
name varchar(50) not null，
math int(5) not null，
english int(5) not null，
chinese int(5) not null，
index(id));

使用show create table score可以查看建表语句。

2.1.2 创建唯一性索引

创建唯一性索引与创建一般索引的语法结构大体相同，但是在创建唯一索引的时候，需要使用UNIQUE参数进行约束。

create table address(
id int(11) auto_increment primary key not null，
name varchar(50)，
address varchar(200)，
UNIQUE INDEX address(id ASC));

2.1.3 创建全文索引

与创建普通索引和唯一索引不同，全文索引的创建只能作用在CHAR、VARCHAR、TEXT类型的字段上。创建全文索引需要使用FULLTEXT参数进行约束。

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.

在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.

在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词.

2.1.3.1 使用全文索引

MySQL的全文索引查询有多种模式

自然语言搜索

普通的判断是否包含

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神' IN NATURAL LANGUAGE MODE);
+----+-----------------+-------------------------+
| id | title           | body                    |
+----+-----------------+-------------------------+
|  1 | 弘扬正能量      | 贯彻党的18大精神        |
+----+-----------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神');
+----+-----------------+-------------------------+
| id | title           | body                    |
+----+-----------------+-------------------------+
|  1 | 弘扬正能量      | 贯彻党的18大精神        |
+----+-----------------+-------------------------+
1 row in set (0.00 sec)

可以看到,搜索结果命中了一条,且在不指定搜索模式的情况下,默认模式为自然语言搜索.

BOOLEAN MODE

这个模式和lucene中的BooleanQuery很像,可以通过一些操作符,来指定搜索词在结果中的包含情况.比如 + 表示必须包含 , -表示必须不包含,默认为误操作符,代表可以出现可以不出现,但是出现时在查询结果集中的排名较高一些.也就是该结果和搜索词的相关性高一些.

具体包含的所有操作符可以通过MySQL查询来查看:

show variables like '%ft_boolean_syntax%'

+-------------------+----------------+
| Variable_name     | Value          |
+-------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
+-------------------+----------------+
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神' IN BOOLEAN MODE);
+----+-----------------+-------------------------+
| id | title           | body                    |
+----+-----------------+-------------------------+
|  1 | 弘扬正能量      | 贯彻党的18大精神        |
+----+-----------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神 -贯彻' IN BOOLEAN MODE);
Empty set (0.01 sec)

当搜索必须命中精神时,命中了一条数据,当在加上不能包含贯彻的时候,无命中结果.

2.1.4 创建单列索引

创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需指定单列字段名，即可创建单列索引。

create table telephone(
id int(11) primary key auto_increment not null，
name varchar(50) not null，
tel varchar(50) not null，
index tel_num(tel(20))
);

数据表中的字段长度为50，而创建的索引的字段长度为20，这样做的目的是为了提高查询效率，优化查询速度。

2.1.5 创建多列索引

与创建单列索引相仿，创建多列索引即指定表的多个字段即可实现。

create table information(
id int(11) auto_increment primary key not null，
name varchar(50) not null，
sex varchar(5) not null，
birthday varchar(50) not null，
INDEX info(name，sex)
);

需要注意的是，在多列索引中，只有查询条件中使用了这些字段中的第一个字段（即上面示例中的name字段）时，索引才会被使用。

触发多列索引的条件是用户必须使用索引的第一字段，如果没有用到第一字段，则索引不起任何作用，用户想要优化查询速度，可以应用该类索引形式。

2.1.6 创建空间索引

创建空间索引时，需要设置SPATIAL参数。同样，必须说明的是，只有MyISAM类型表支持该类型索引。而且，索引字段必须有非空约束。

create table list(
id int(11) primary key auto_increment not null，
goods geometry not null，
SPATIAL INDEX listinfo(goods)
)engine=MyISAM;

空间类型除了上述示例中提到的GEOMETRY类型外，还包括如POINT、LINESTRING、POLYGON等类型。这些空间数据类型在平常的操作中很少被用到。

2.2 在已建立的数据表中创建索引

在MySQL中，不但可以在用户创建数据表时创建索引，用户也可以直接在已经创建的表中，在已经存在的一个或几个字段上创建索引。其基本的命令结构如下所示：

CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name
ON table_name(属性 [(length)] [ ASC | DESC]);

命令的参数说明如下:

（1）index_name为索引名称，该参数作用是给用户创建的索引赋予新的名称。
（2）table_name为表名，即指定创建索引的表名称。
（3）可选参数，指定索引类型，包括UNIQUE（唯一索引）、FULLTEXT（全文索引）、SPATIAL（空间索引）。
（4）属性参数，指定索引对应的字段名称。该字段必须已经预存在用户想要操作的数据表中，如果该数据表中不存在用户指定的字段，则系统会提示异常。
（5）length为可选参数，用于指定索引长度。
（6）ASC和DESC参数，指定数据表的排序顺序。

与建立数据表时创建索引相同，在已建立的数据表中创建索引同样包含6种索引方式。

2.2.1 创建普通索引

create INDEX stu_info ON studentinfo(sid);

2.2.2 创建唯一索引

在已经存在的数据表中建立唯一索引的命令如下:

CREATE UNIQUE INDEX 索引名 ON 数据表名称(字段名称);

其中，UNIQUE是用来设置索引唯一性的参数，该表中的字段名称既可以存在唯一性约束，也可以不存在唯一性约束。

2.2.3 创建全文索引

在MySQL中，为已经存在的数据表创建全文索引的命令如下:

CREATE FULLTEXT INDEX 索引名 ON 数据表名称(字段名称);

其中，FULLTEXT用来设置索引为全文索引。字段类型必须为VARCHAR、CHAR、TEXT等类型。

示例：

CREATE FULLTEXT INDEX index2_info ON index2(info);

2.2.4 创建单列索引

与建立数据表时创建单列索引相同，用户可以设置单列索引。其命令结构如下。

CREATE INDEX 索引名 ON 数据表名称(字段名称(长度));

设置字段名称长度，可以优化查询速度，提高查询效率。

示例：

CREATE INDEX index3_addr ON index3(address(4));

2.2.5 创建多列索引

建立多列索引与建立单列索引类似。其主要命令结构如下:

CREATE INDEX 索引名 ON 数据表名称(字段名称1，字段名称2，…);

与建立数据表时创建多列索引相同，当创建多列索引时，用户必须使用第一字段作为查询条件，否则索引不能生效。

示例：

CREATE INDEX index4_na ON index4(name，address);

2.2.6 创建空间索引

建立空间索引，用户需要应用SPATIAL参数作为约束条件。其命令结构如下：

CREATE SPATIAL INDEX 索引名 ON 数据表名称(字段名称);

其中，SPATIAL用来设置索引为空间索引。用户要操作的数据表类型必须为MyISAM类型。并且字段名称必须存在非空约束，否则将不能正常创建空间索引。

2.3 修改数据表结构添加索引

修改已经存在表上的索引，可以通过ALTER TABLE语句为数据表添加索引，其基本结构如下:

ALTER TABLE table_name ADD [ UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name(属性名 [(length)]
[ASC | DESC]);

该参数与2.1节和2.2节中所介绍的参数相同。

2.3.1 添加普通索引

alter table studentinfo ADD INDEX timer (time(20));

2.3.2 添加唯一索引

与已存在的数据表中添加索引的过程类似，在数据表中添加唯一索引的命令结构如下所示:

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称*(字段名称);

其中，ALTER语句一般是用来修改数据表结构的语句，ADD为添加索引的关键字；UNIQUE是用来设置索引唯一性的参数，该表中的字段名称既可以存在唯一性约束，也可以不存在唯一性约束。

2.3.3 添加全文索引

创建全文索引与创建普通索引和唯一索引不同，全文索引创建只能作用在CHAR、VARCHAR、TEXT类型的字段上。创建全文索引需要使用FULLTEXT参数进行约束。

在MySQL中，为已经存在的数据表添加全文索引的命令如下。

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名称(字段名称);

其中，ADD是添加的关键字，FULLTEXT用来设置索引为全文索引。字段类型同样必须为VARCHAR、CHAR、TEXT等类型。

示例：

ALTER TABLE workinfo ADD FULLTEXT INDEX index_ext(address);

2.3.4 添加单列索引

与建立数据表时创建单列索引相同，用户可以设置单列索引。其命令结构如下:

ALTER TABLE 表名 ADD INDEX 索引名称(字段名称(长度));

同样，用户可以设置字段名称长度，以便优化查询速度，提高执行效率。

2.3.5 添加多列索引

添加多列索引与建立单列索引类似。其主要命令结构如下:

ALTER TABLE 表名 ADD INDEX 索引名称(字段名称1，字段名称2，…);

使用ALTER修改数据表结构同样可以添加多列索引。与建立数据表时创建多列索引相同，当创建多列索引时，用户必须使用第一字段作为查询条件，否则索引不能生效。

2.3.6 添加空间索引

添加空间索引，用户需要应用SPATIAL参数作为约束条件。其命令结构如下:

ALTER TABLE 表名 ADD SPATIAL INDEX 索引名称(字段名称);

其中，SPATIAL用来设置索引为空间索引。用户要操作的数据表类型必须为MyISAM类型，并且字段名称必须存在非空约束，否则将不能正常创建空间索引。该类别索引并不常用，初学者只需要了解该索引类型即可。

3 删除索引

在MySQL中，创建索引后，如果用户不再需要该索引，则可以删除指定表的索引。因为这些已经被建立且不常使用的索引，一方面可能会占用系统资源，另一方面也可能导致更新速度下降，这极大地影响了数据表的性能。所以，在用户不需要该表的索引时，可以手动删除指定索引。其中，删除索引可以通过DROP语句来实现。其基本的命令如下:

DROP INDEX index_name ON table_name;

其中，参数index_name是用户需要删除的索引名称，参数table_name指定数据表名称

4 索引设计原则

在创建索引时要尽量遵循一些原则，便于提升索引的使用效率。

搜索的索引列，不一定是所要选择的列。即，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
使用唯一索引。考虑某列中值的分部。索引的列的基数越大，索引的效果越好。即索引能确定的行数越小越好，最好的情况就是唯一索引，能唯一确定一行数据。
使用短索引。如果对字符串列进行索引，只要有可能应该指定一个前缀长度。尽量不要对整个列进行索引，如CHAR(200)，如果在前10~20字符内，多数值是唯一的，那么就使用前缀索引，这样能减少IO。方便MySQL在内存中容纳更多的值。
利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因此可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
不要过度索引。索引不是越多越好，索引过多时会导致数据写表的效率降低，因此可以将一些的很少用到或者从不使用的索引删除。
对应InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就按照唯一索引的顺序保存。如果即没有主键又没有唯一索引，那么表中会自动生成一个内部列，并且按照这个列保存。按照主键或内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择短的数据类型，可以有效的减少索引的磁盘占用，提高索引的缓存效果。