索引的创建与设计原则

最新推荐文章于 2024-04-25 12:02:52 发布

窗边的小豆豆丶

最新推荐文章于 2024-04-25 12:02:52 发布

阅读量2.2k

点赞数 1

分类专栏： MySQL高级文章标签：搜索引擎 mysql 数据库

本文链接：https://blog.csdn.net/Doudou_4174/article/details/129198154

版权

MySQL高级专栏收录该内容

17 篇文章 2 订阅

订阅专栏

1.索引的声明与使用

1.1索引的分类

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

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为 2 种: 聚族索引和非聚族索引。
按照作用字段个数进行划分，分成单列索引和联合索引。

1.普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在 任何数据类型 中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student 的字段 name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。

2.唯一性索引

使用UNIQUE参数 可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。

3.主键索引

主键索引就是一种 特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里 最多只有一个主键索引。

4.单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索单列索引还可以以是全文索引只要保证该索引只对应个字段即可，一个表可以有多个单列索引。

5.多列（组合、联合）索引

多列索引是在表的 多个字段组合 上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender上建立一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

6.全文索引

全文索引(也称全文检索)是目前 搜索引擎 使用的一种关键技术。它能够利用[分词技术] 等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

使用参数 FULLTEXT 可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在 CHAR 、VARCHAR 或 TEXT 类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表 student 的字段 information 是TEXT 类型该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。

全文索引典型的有两种类型: 自然语言的全文索引和布尔全文索引。

MySQL数据库从3.23.23版开始支持全文索引，MySQL5.6.4以前只有Myisam支持，5.6.4版本以后 innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。在5.7.6版本，MySQL内置了 ngram全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr、ElasticSearch 等专门的搜索引擎所替代。

7.补充:空间索引

使用参数SPATIAL 可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySOL中的空间数据类型包括 GEOMETRY、POINT、LINESTRING 和 POLYGON 等。目前只有MyISAM存储引擎支持空间检索，而目索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

小结: 不同的存储引擎支持的索引类型也不一样

InnoDB：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM :支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory :支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB :支持 Hash 索引，不支持 B-tree、 Full-text 等索引；
Archive :不支持 B-tree、 Hash、Full-text 等索引；

1.2创建索引

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

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 为同义词，两者的作用相同，用来指定创建索引；
index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
col_name为需要创建索的字段列，该列必须从数据表中定义的多个列中选择；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC或 DESC 指定升序或者降序的索引值存储；

1.创建普通索引

CREATE TABLE test1(
	id INT,
	name VARCHAR(25),
	age INT,
	sex CHAR,
	remark VARCHAR(255),
	INDEX(name)
);

2.创建唯一性索引

创建唯一索引的目的也是保证数据的唯一性，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的是，索引列的值必须唯一，但允许有空值。

CREATE TABLE test2(
	id INT NOT NULL,
	name VARCHAR(25),
	UNIQUE INDEX un_idx_id(id)
);

#查看表索引结构
SHOW INDEX FROM test2;

在这里插入图片描述

其中各个主要参数的含义为

Table表示创建索引的表；
Non_unique表示索引非唯一，1代表非唯一索引，0代表唯一索引；
Key_name表示索引的名称；
Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序；
Column_name表示定义索引的列字段；
Sub_part表示索引的长度；
Null表示该字段是否能为空值；
index_type表示索引类型；

3.主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引。

随表一起建索引

CREATE TABLE test3(
	id INT(10) UNSIGNED,
	name VARCHAR(25),
	PRIMARY KEY(id)
);

删除主键索引
```
ALTER TABLE test3 DROP PRIMARY KEY;
```
修改主键索引: 必须先删除掉(drop)原索引。再新建(add)索引

4.创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。

CREATE TABLE test4(
	id INT NOT NULL,
	name VARCHAR(255),
	INDEX single_idx_name(name(20))
);

5.创建组合索引

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

CREATE TABLE test5(
	id INT NOT NULL,
	name VARCHAR(25) NOT NULL,
	age INT(11) NOT NULL,
	INDEX multi_idx(id,name,age)
);

使用SHOW INDEX FROM test5;查看索引

在这里插入图片描述

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

组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”。例如，索引可以搜索的字段组合为: (id,name, age)、 (id,name) 或者id，而 (age) 或者 (name,age) 组合不能使用索引查询。

6.创建全文索引

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

CREATE TABLE test6(
	id INT NOT NULL,
	name VARCHAR(25),
	age INT(11),
	info VARCHAR(255),
	FULLTEXT INDEX futxt_idx_info(info)
);

不同于like方式的的查询

SELECT * FROM test6 WHERE info LIKE '%查询字符串%';

全文索引用match+against方式查询

SELECT * FROM test6 WHERE MATCH(info) AGAINST('查询字符串');

注意点

使用全文索引前，搞清楚版本支持情况；
全文索引比 like +%快N倍，但是可能存在精度问题；
如果需要全文索引的是大量数据，建议先添加数据，再创建索引；

7.创建空间索引

空间索引创建中，要求空间类型的字段必须为非空

CREATE TABLE test7(
	geo GEOMETRY NOT NULL,
	SPATIAL INDEX spa_idx_geo(geo)
);

2.在已经存在的表上创建索引

在已经存在的表中创建索引可以使用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表示向表中添加索引。

ALTER TABLE test1 ADD INDEX idx_age(age);
ALTER TABLE test2 ADD PRIMARY KEY(id);

2.使用CREATE INDEX创建索

CREATE INDEX语句可以在已经存在的表上添加索引，基本语法结构为：

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

CREATE UNIQUE INDEX uni_ind_name ON test3(name);

1.3.删除索引

MySOL中删除索引使用ALTER TABLE 或者 DROP INDEX 语句，两者可实现相同的功能，DROPINDEX语句在内部被映射到一个ALTER TABLE语句中。

1.使用ALTER TABLE删除索引

ALTER TABLE删除索引的基本语法格式如下

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE test3 DROP INDEX uni_ind_name;

提示：添加AUTO_INCREMENT约束字段的唯一索引不能被删除；

2.使用DROP INDEX语句删除索引

DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

DROP INDEX idx_age ON test1;

提示：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

2.MySQL8.0索引新特性

2.1支持降序索引

降序索引以降序存储键值。虽然在语法上，从MySOL 4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL8.x版本才开始真正支持降序索引 (仅限于InnoDB存储引擎)；

MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1，结果如下：

CREATE TABLE ts1(a int, b int, index inx_a_b(a,b desc));

在MySQL 5.7版本中查看数据表ts1的结构，结果如下：

在这里插入图片描述

从结果可以看出，索引仍然是默认的升序。

在MySQL 8.0版本中查看数据表ts1的结构，结果如下:

在这里插入图片描述

从结果可以看出，索引已经是降序了。

2.2隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持隐藏索引 (invisible indexes) ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引(即使使用force index (强制使用索引)，优化器也不会使用该索引)，确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 。

同时，如果你想验证某个索引删除之后的 查询性能影响，就可以暂时先隐藏该索引。

注意：主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。

索默认是可见的，在使用CREATE TABLE，CREATE INDEX或者ALTER TABLE等语句时可以通过 VISIBLF 或者INVISIBLE 关键词设置索引的可见性。

1.创建表时直接创建

在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：

CREATE TABLE table_name(
	propname1 type1[CONSTRAINT1],
    ...
    propnamen typen[CONSTRAINTn],
    INDEX [index_name](propname[length]) INVISIBLE
);

上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。

2.在已经创建的表上创建

可以为已经存在的表设置隐藏索引，其语法形式如下：

CREATE INDEX index_name
ON table_name(porpname(length)) INVISIBLE;

3.通过ALTER TABLE语句创建

语法形式如下：

ALTER TABLE table_name
ADD INDEX index_name (propname[length]) INVISIBLE;

4.切换索引可见状态

已存在的索引可通过如下语句切换可见状态：

ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;	#切换成隐藏索引
ALTER TABLE table_name ALTER INDEX index_name VISIBLE;		#切换成非隐藏索引

注意：当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

5.使隐藏索引对查询优化器可见

在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关(use_invisible_indexes)来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes 设置为of(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引

查看查询优化器的开关设置

select @@optimizer_switch;

在输出的结果信息中找到如下属性配置,此属性配置值为off，说明隐藏索引默认对查询优化器不可见
在这里插入图片描述

使隐藏索引对查询优化器可见

set session optimizer_switch='use_invisible_indexes=on';

3.索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则。

3.1哪些情况适合创建索引

1.字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中如果 某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。 (来源: Alibaba)

说明: 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2.频繁作为WHERE查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3.经常 GROUP BY和ORDER BY的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

4.UPDATE、DELETE的 WHERE条件列

对数据按照某个条件进行查询后再进行UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

同时显示出来的数据还是按照 递增的顺序进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

6.多表JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快严重影响查询的效率；

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE 条件过滤是非常可怕的；

最后，对用于连接的字段创建索引，并且该字段在多张表中的 类型必须一致；

7.使用列的类型小的创建索引

我们这里所说的 类型大小指的就是该类型表示的数据范围的大小。

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TINYINT、MEDIUMINT、INT、BIGINT 等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用BIGINT，能使用 MEDIUMINT 就不要使用 INT 。这是因为：

数据类型越小，在查询时进行的比较操作越快；
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘工/0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率；

这个建议对于表的 主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

8.使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题：

B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大；
如果B+树索引中索引列存储的字符串很长，那在做字符串 比较时会占用更多的时间；

我们可以通过截取字段的前面一部分内容建立索引，这个就叫 前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既 节约空间 ，又 减少了字符串的比较时间 ，还大体能解决排序的问题。

9.区分度高(散列性高)的列适合作为索引

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

可以使用公式 select count(distinct a)/count(*) from t1 计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

拓展:联合索引把区分度高(散列性高)的列放在前面；

10.使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则”，可以增加联合索引的使用率。

11.在多个字段都要创建索引的情况下，联合索引优于单值索引

3.2限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个 。原因:

每个索都需要占用磁盘空间，索引越多，需要的磁盘空间就越大；
索引会影响 INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担；
优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能；