mysql高级-5-索引的创建及设计原则

最新推荐文章于 2023-08-30 20:18:07 发布

鑫！

最新推荐文章于 2023-08-30 20:18:07 发布

阅读量691

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44227063/article/details/127816906

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

mysql高级

前言
1、索引的声明与使用
- 1.1 索引的分类
- 1.2 创建和删除索引
2、MySQL8.0索引新特性
- 2.1 支持降序索引
- 2.2 隐藏索引
3、索引的设计原则
- 3.1 哪些情况适合添加索引（11条）
- 3.2 哪些情况不适合添加索引（7条）
end...

前言

前面，我们学习了一些底层的东西，看似枯燥，其实是为了我们后面更加深入的理解mysql调优做铺垫，本篇文章我们将学习期待已久的索引相关知识，对于面试和工作来讲都是重中之重的部分。文章课程链接：MySQL数据库教程天花板，mysql安装到…

1、索引的声明与使用

1.1 索引的分类

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

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

不同的存储引擎支持的索引类型不同，如表

索引名	支持	不支持
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、Full-text、Hash

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

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

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

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

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

6.全文索引
类似于Elasticsearch和Solr的功能，使用参数FULLTEXT可以设置索引为全文索引，在定义的索引的列上支持值的全文查找，允许这些索引列中插入重复值和空值，全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上。一般都不会使用这个索引，因为有Elasticsearch或Solr这样更专业的产品。

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

1.2 创建和删除索引

创建索引分为

1、创建表的时候创建索引
2、在已经存在的表上创建索引

删除索引

1、使用ALTER TABLE删除索引
2、使用DROP INDEX删除索引
提示：添加自增约束字段的唯一索引不能被删除
可以删除联合索引中的某个字段

这部分不是我们的重点，这里不再记录

2、MySQL8.0索引新特性

2.1 支持降序索引

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

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

通过 a,b 两个字段排序，一个升序，一个降序，如：SELECT *** ORDER BY a,b DESC LIMIT 5;这时，我们的索引应该对应着：CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

2.2 隐藏索引

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

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

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

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

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

3、索引的设计原则

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

3.1 哪些情况适合添加索引（11条）

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

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。因为该值只有一个，找到后就可以直接返回了（无需向下查找），这样可以更快速地通过该索引来确定某条记录。

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

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

3.经常 GROUP BY 和 ORDER BY 的列

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

4.UPDATE、DELETE 的 WHERE 条件列

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

5.DISTINCT 字段需要创建索引

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

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

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致，类型不一致会使用函数转换，索引就不会用，从而失效。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

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

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

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

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树创建时花费的空间大且时间更长。
因此，我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。
但我们取多少作为索引呐，可以通过这个公式进行参考
select count(distinct left(列名, 索引长度)) / count(*) from table;
因为二级索引中不包含完整的索引列信息，所以无法对索引字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。
最后，阿里规范也对此做了说明
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

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

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引
注意：联合索引应把区分度高(散列性高)的列放在前面

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