MySQL数据库相关索引的使用

娃哈哈哈❚❚

已于 2024-06-10 09:38:50 修改

阅读量960

点赞数 37

文章标签：数据库

于 2024-05-22 16:47:21 首次发布

本文链接：https://blog.csdn.net/m0_73756512/article/details/139102028

版权

基础索引：

索引介绍：索引是一种能够提高检索（查询）效率的，提前排好序的数据结构。对于索引来说，当索引的唯一性越好，性能就越高。

索引的优缺点：

索引的优点：

1、提高查询性能：通过创建索引，可以大大减少数据路查询的数据量，从而提高查询的速度。

2、加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率

3、减少磁盘的IO：索引可以减少磁盘IO的次数，这对磁盘读写速度较低的场景，尤为重要。

索引的缺点：

1、占据额外的存储空间：索引需要占据额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。

2、增删改查操作的性能损耗：每次对数据表仅从插入，更新，删除等操作时，需要更新索引，会导致操作的性能降低。

3、资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。

什么时候使用索引：

在以下情况下建议使用索引：

1、频繁执行查询的操作字段：如果这些字段经常被查询，使用索引可以提高查询的性能，减少查询的时间。

2、大表：当表的数据量较大时，使用索引可以快速定位到所需要的数据，提高查询的效率

3、需要排序或分组的时候：在对字段进行排序或分组的操作时，索引可以减少排序或者分组的时间。

4、外键关联的字段：在进行表之间关联查询时，使用素偶因可以加快关联查询的速度

在以下情况不建议使用索引：

1、频繁的执行更新操作的表：如果表经常被更新数据，使用索引可能会降低更新操作的性能，因为每次更新都需要维护索引。

2、小表：对于数据量较小的表，使用索引可能并不会带来明显的性能提升，反而会占用额外的存储空间。

3、对于唯一性很差的字段，一般不建议添加索引。当一个字段的唯一性很差时，查询操作基本上需要扫描整个表的大部分数据，如果为这样的字段创建索引，索引的大小可能会表数据本身还大，导致索引存储空间占用过高，同时也会导致查询操作的性能下降。

总之：索引需要根据具体情况进行使用和权衡，需要考虑到表的大小，查询频率，更新频率以及业务需求等因素。

索引的创建与删除

给表添加索引：

方式一：建表的时候直接加（给t_user2表的name添加user_name_index的索引）

方式二：建完表在添加索引（给t_user3的表中的id，name添加索引）

注意：

主键会自动添加索引：主键字段会自动添加索引，不需要程序员的干涉，主键字段上的索引被称为主键索引

unique约束的字段自动添加索引：unique约束的字段也会自动添加索引，不需要程序员干涉，这种字段上添加的索引称为唯一索引。

索引的删除：

索引的分类：

不同的存储引擎，有不同的索引类型的实现：

按照数据结构分类:

B+树索引（MySQL的InnoDB存储引擎采用的就是这种索引）采用B+树的数据结构

Hash索引（仅memory存储引擎支持）：采用哈希表的数据结构

按照物理存储分类：

聚集索引：索引和表中数据在一起，数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引

非聚集索引：索引和表中数据是分开的，索引是独立与空间的，一张表可以有多个非聚集索引。

按照字段特性分类：

主键索引（primary key）

唯一索引（unique）

普通索引（index）

全文索引（fulltext：仅InnDB和MyISAM存储引擎支持）：要求字段的类型都是文本内容采用可以使用全文索引

按照字段个数分类：

单列索引，联合索引（也叫复合索引，组合索引）

树

二叉树

二叉树是一种树形结构，其中每个阶段最多有两个子节点，分别成为左子节点和右子节点。每个节点都包含一个值，用于存储数据。

特点：

1、每个节点最多有两个子节点，分别被称为左子节点和右子节点。没有子节点的节点被称为叶子节点

2、二叉树可以是空的（没有任何节点），或者是一个根节点连接着左子树和右子树的非空树。

红黑树（平衡二叉树）

红黑树是一种自平衡的二叉查找树。他在普通的二叉搜索树的基础上添加了额外的规则来保持树的平衡性，苍耳保证了在最坏情况下的搜索，插入和删除的时间复杂度为O(log n).

特点：

1、每个节点都是红色或黑色的，其中根节点和叶子节点（NIL）是黑色的，且红色节点只能有黑色节点作为父节点和子节点。

2、从任一节点到其每个叶子节点的所有路径都包含相同数目的黑色节点。

3、任意节点到其子树中每个叶子节点的路径长度，最长不超过最短路径的两倍。

B Trees

B树是一种自平衡的梳妆数据结构，被广泛应用于数据库和文件系统中。

特点：

1、每个节点都包含多个键值对（通常称为节点的阶）。

2、节点中的键值对按顺序排列，并且分布在节点子节点之间。

3、所有叶子几点位于相同的层级，并且不包含任何数据。

4、所有非叶子节点（内部节点）都包含只想子节点的指针

B+Trees

b+Trees是在B树的基础上进行了改进的一种树状数据结构。

与B Trees的区别：

1、所有数据都存储在叶子节点中，而非内部节点。

2、叶子节点之间通常通过指针连接成一个有序链表，这样可以很快地进行范围查询

3、B+Trees的节点使得他在范围查询和顺序访问时效率更高。他仍然报纸了B树的平衡性和高效的插入，删除和查找操作，但通过将数据存储在叶子节点上，减少了书的高度，从而提高了查询的性能。

Hash索引：

支持Hash索引的存储引擎有：

InnDB（不知处手动创建Hash索引，系统会自动维护一个自适应的Hash索引）

对于InnDB来说，即使手动指定某个字段的采用Hash索引，最中show index from 表名的时候，还是BTREE。

Hash索引的优缺点：

优点：值能用在等值比较重，效率很高。

缺点：不支持排序，不支持范围查找。

聚集索引和非聚集索引

聚集索引：索引值和数据在一起（存储引擎InnDB）

非聚集索引：索引值和数据不在一起（存储引擎为MyISAM，且任意字段都是非搜索引擎，索引值下面为行指针）

InnDB的物理存储方式：当创建一张表t_user并使用InnDB存储引擎时，会在硬盘上生成以下文件：

t_user.ibd（InnDB data 表索引+数据）

t_user.frm（存储表结构信息）

MyISAM的物理存储方式：当创建一张表t_user，并使用MyISAM存储引擎时，会在磁盘上生成以下文件：

t_user.MYD（表数据）

t_user.MYI（表索引）

t_user.frm（表结构）

注意：从MySQL8开始，不再生成frm文件，引入了数据字典，用数据字典来统一存储结构信息，例如：

information_schema.TABES：表示含了数据库中所有表的信息，例如表名、数据库名、引擎类型等

information_schema.COLUMNS：表示含了数据库中所有表的列信息，例如列名，数据类型，默认值

聚集索引的优点和缺点

优点：聚集索引将数据存储在索引树的叶子节点上，可以减少一次查询，因为查询索引树的同时可以获取数据

缺点：对数据进行修改或删除时需要更新索引树，会增加系统的开销

二级索引

二级索引：属于非聚集索引，被称为辅助索引；

二级索引的数据结构：

二级索引的查询原理：

覆盖索引：

简介：指某个查询语句可以通过索引来覆盖，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中的提取到，而不需要再去查寻实际数据行获取查询所需数据。当使用覆盖索引时，MySQL可以直接通过索引，也就是索引上的数据来获取所需的结果，而不必再去查找表中的数据。这样可以显著提高查询性能。

假设有一个用户表（user）包含以下列：Id，username，email，age

常见的查询根据用户名查询用户的邮箱，如果为了体哦高这个查询的性能，可以创建一个覆盖索引，包含（username，email）这两列。

创建覆盖索引的SQL语句可以如下：

CREATE INDEX inx_user_username_email ON USER (username，email)

当执行以下查询时：

SELECT email FROM user WHERE username = ‘Lucy’;

MysSQL可以直接使用覆盖索引（Idx_username_email）来获取查询结果，而不必再去查找用户表中的数据，这样可以减少磁盘I/O并提高查询效率，而如果没有覆盖索引，MySQL会使用索引（username）来找到匹配的行，然后再回表查询获取邮箱，这个过程会增加更多的磁盘I/O和查询时间。值得注意的是，覆盖索引的创建考虑查询字段选择，如果查询需要的字段较多，可能需要创建包含更多列的覆盖索引，以满足完全覆盖查询的需要。

覆盖索引的优缺点：

优点

提高查询性能：覆盖索引能够满足查询的所有需求，同时不需要访问表中的实际数据行，从而可以提高查询性能。这是因为DBMS可以直接使用索引来执行查询，而不需要从磁盘读取实际的数据行。
减少磁盘和内存访问次数：当使用覆盖索引时，DBMS不需要访问实际的数据行。这样可以减少磁盘和内存访问次数，从而提高查询性能。
减少网络传输：由于在覆盖索引中可以存储所有查询所需的列，因此可以减少数据的网络传输次数，从而提高查询的性能。
可以降低系统开销：在高压力的数据库系统中，使用覆盖索引可以减少系统开销，从而提高系统的可靠性和可维护性。

缺点：

需要更多的内存：覆盖索引需要存储查询所需的所有列，因此需要更多的内存来存储索引。在大型数据库系统中，这可能会成为一项挑战。
会使索引变得庞大：当索引中包含了许多列时，它们可能会使索引变得非常庞大，从而影响查询性能，并且可能会占用大量的磁盘空间。
只有在查询中包含了索引列时才能使用：只有当查询中包含了所有的索引列时才能使用覆盖索引。如果查询中包含了其他列，DBMS仍然需要访问实际的数据行，并且无法使用覆盖索引提高查询性能。

索引下推

注意：索引吓推是MySQL优化器自动处理的，并不需要程序员进行干预。

没有使用索引下推

使用索引下推：

综上所得：索引吓退的使用减少了回标查询的次数，提高查询的效率。

单列索引和复合索引

单列索引

单列索引是指对数据库表中的某一列或属性进行索引创建，对该列进行快速查找和排序操作。单列索引可以加快查询速度，提高数据库的性能。

举个例子，假设我们有一个学生表（student），其中有以下几个列：学生编号（student_id）、姓名（name）、年龄（age）和性别（gender）。

如果我们针对学生表的学生编号（student_id）列创建了单列索引，那么可以快速地根据学生编号进行查询或排序操作。例如，我们可以使用以下SQL语句查询学生编号为123456的学生信息：

SELECT * FROM student WHERE student_id = 123456;

由于我们对学生编号列建立了单列索引，所以数据库可以直接通过索引快速定位到具有学生编号123456的那一行记录，从而加快查询速度。

复合索引

复合索引（Compound Index）也称为多列索引（Multi-Column Index），是指对数据库表中多个列进行索引创建。与单列索引不同，复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键，以提高多列条件查询的效率。

举个例子，假设我们有一个订单表（Order），其中包含以下几个列：订单编号（OrderID）、客户编号（CustomerID）、订单日期（OrderDate）和订单金额（OrderAmount）。

如果我们为订单表的客户编号和订单日期这两列创建复合索引（CustomerID, OrderDate），那么可以在查询时同时根据客户编号和订单日期来快速定位到匹配的记录。

例如，我们可以使用以下SQL语句查询客户编号为123456且订单日期为2021-01-01的订单信息：

SELECT * FROM Order WHERE CustomerID = 123456 AND OrderDate = '2021-01-01';

由于我们为客户编号和订单日期创建了复合索引，数据库可以使用这个索引来快速定位到符合条件的记录，从而加快查询速度。复合索引的使用能够提高多列条件查询的效率，但需要注意的是，复合索引的创建和维护可能会增加索引的存储空间和对于写操作的影响。

相对于单列索引，复合索引有以下优势：

1、减少索引的数量：复合索引可以包含多个列，因此可以减少索引的数量，减少索引的存储空间和维护成本。

2、提高查询性能：当查询条件中涉及到复合索引的多个列时，数据库可以使用复合索引进行快速定位和过滤，从而提高查询性能。

3、覆盖查询：如果复合索引包含了所有查询需要的列，那么数据库可以直接使用索引中的数据，而不需要再进行表的读取，从而提高查询性能。

4、排序和分组：由于复合索引包含多个列，因此可以用于排序和分组操作，从而提高排序和分组的性能。

娃哈哈哈❚❚

关注

37
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库相关索引的使用

指某个查询语句可以通过索引来覆盖，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中的提取到，而不需要再去查寻实际数据行获取查询所需数据。当使用覆盖索引时，MySQL可以直接通过索引，也就是索引上的数据来获取所需的结果，而不必再去查找表中的数据。这样可以显著提高查询性能。假设有一个用户表（user）包含以下列：Id，username，email，age。
复制链接

扫一扫