Mysql的索引

铁锤学代码

已于 2024-11-30 14:56:45 修改

阅读量760

点赞数 23

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

于 2024-08-14 18:41:51 首次发布

本文链接：https://blog.youkuaiyun.com/Delete_wang/article/details/141196618

版权

Mysql 专栏收录该内容

6 篇文章

订阅专栏

文章目录

索引作用

提高数据检索效率：通过索引，可以大大加快数据的检索速度，降低数据库的I/O成本。
降低数据排序的成本：索引列对数据进行排序，可以降低数据排序的成本，减少CPU的消耗。

InnerDB的索引结构为B+树,B+树的特点为多路平衡树，叶子节点

索引的优缺点

优点
提高数据检索效率。
降低数据库的I/O成本。
通过对索引列进行排序，降低数据排序的成本。
缺点
索引列会占用额外的存储空间。
索引虽然提高了查询效率，但会降低更新表的速度，如INSERT、UPDATE、DELETE等操作，因为索引也需要被更新。

mysql索引的数据结构

MySQL索引主要使用B+树结构，但也支持哈希索引等其他结构。以下是几种常见的索引数据结构：

B+树索引
- B+树是一种多路平衡查找树，它被广泛用于数据库和文件系统的索引结构中。B+树索引具有以下特点：
  多路平衡性：每个节点可以包含多个关键字和子节点，这使得B+树具有较好的平衡性能。
- 有序性：B+树中的关键字按照升序排列，在进行范围查询时非常高效。
  磁盘友好性：B+树的节点大小通常与硬盘页的大小相匹配，这样可以最大程度地减少磁盘I/O操作。
- 内部节点不存储数据：B+树的内部节点只存储索引信息，而实际的数据记录则存储在叶子节点中，这样可以提高范围查询的效率。
- 叶子节点之间通过指针连接：B+树的叶子节点使用指针进行连接，形成一个有序链表，便于范围查询和顺序遍历。

MySQL的InnoDB存储引擎默认使用B+树作为索引结构，并对经典的B+树进行了优化，例如增加了一个指向相邻叶子节点的链表指针，以提高区间访问的性能。

哈希索引：

特点：哈希索引通过哈希函数将键值映射到对应的槽位上，存储在哈希表中。查询效率高，通常只需要一次检索。
限制：哈希索引只能用于等值比较（=、IN），不支持范围查询和排序操作。
支持情况：在MySQL中，支持哈希索引的是Memory引擎，而InnoDB存储引擎具有自适应哈希功能，可以自动构建哈希索引。

索引的分类

根据索引的存储形式，MySQL索引可以分为以下几种：

聚集索引：聚集索引决定了表中数据的物理存储顺序。一个表只能有一个聚集索引。
如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。如果没有主键或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
非聚集索引（也称为辅助索引或二级索引）：
非聚集索引的索引页面不直接指向数据页面，而是存储了索引键值和相应数据行的地址（即数据页的地址）。
一个表可以有多个非聚集索引，这些索引可以提供不同的数据检索路径。

设计索引的时候有哪些原则？

选择合适的列建立索引

高频查询列：选择经常用于查询条件的列建立索引，特别是那些在WHERE、JOIN、ORDER BY、GROUP BY等子句中出现的列。
唯一性列：对于包含唯一值的列，如主键列或唯一约束列，建立唯一索引。这不仅可以确保数据的唯一性，还可以加速基于唯一值的查询。
经常不发生变化又经常作为查询条件的:比如创建时间、订单号、物流单号等。

2.考虑数据分布和表大小

数据分布：如果列中的值分布较为均匀，那么在该列上创建索引可能更有效。相反，如果列中的值分布极不均匀，则可能需要谨慎考虑是否创建索引。
表大小：对于大型表，使用索引尤为重要，因为全表扫描的成本较高。而在小型表上，索引可能不是必需的，因为全表扫描的成本相对较低。

3.避免过度索引

索引数量：索引的数量并不是越多越好。每个索引都需要占用额外的存储空间，并会影响插入、更新和删除操作的性能。因此，需要权衡读写操作的频率，避免创建过多的索引。
索引长度：如果索引列的值很长，可以考虑使用前缀索引或缩短索引列的长度。这样可以减少索引占用的空间，并提高查询效率。

利用复合索引

复合索引：如果查询中经常涉及多个列，可以考虑创建复合索引。复合索引可以覆盖多个查询条件，从而避免使用多个单列索引时可能产生的回表操作。但是，复合索引的列顺序很重要，需要根据查询的需求和频率来选择合适的列顺序。

考虑索引的维护成本

索引重构：在修改表结构或数据分布发生变化时，可能需要重新构建索引以确保其有效性。这可能会消耗大量的时间和资源。
索引优化：定期分析和评估索引的效果，并根据实际查询模式进行调整。可以使用EXPLAIN等工具来查看查询的执行计划和索引的使用情况。

遵循最佳实践

NOT NULL约束：如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束。这有助于优化器更好地确定哪个索引最有效地用于查询。
前缀索引：对于长字符串类型的字段，如果字段的前缀具有足够的区分度，可以考虑使用前缀索引以减少索引的大小并提高查询效率。

索引失效的场景？

1. 索引列参与运算或函数处理

当索引列在查询条件中参与了运算（如加减乘除）或函数处理（如使用UPPER(), LOWER(), CONCAT()等函数）时，会导致索引失效，数据库会进行全表扫描来查找数据。

2. 违反最左前缀法则（针对联合索引）

在使用联合索引时，如果查询条件没有遵循最左前缀法则，即没有从索引的最左列开始匹配，那么索引可能会失效。联合索引相当于同时创建了多个索引，但使用时必须从最左边的索引列开始。

3. 使用LIKE进行模糊查询时占位符位于条件开头

在使用LIKE进行模糊查询时，如果通配符%位于条件的开头（如LIKE ‘%value’），则索引会失效，因为数据库无法利用索引来快速定位数据。但如果通配符位于条件的尾部（如LIKE ‘value%’），且查询列是索引列，则索引仍然有效。

4. 数据类型不匹配导致隐式转换

当查询条件中的数据类型与索引列的数据类型不匹配时，数据库会进行隐式类型转换，这可能导致索引失效。例如，如果索引列是VARCHAR类型，但在查询时使用了没有引号的数字与之比较，MySQL会尝试将数字转换为字符串，从而导致索引失效。

5. 使用OR连接条件时涉及不同索引列

当查询条件使用OR连接时，如果OR前后的条件分别涉及不同的索引列，那么这些索引可能都无法被有效利用，导致索引失效。此外，如果OR条件中一边使用了范围查询（如>、<），另一边使用了等值查询，也可能导致索引失效。

6. 使用SELECT ，尽量使用索引覆盖

当查询语句使用SELECT *时，如果查询的表中有大量的列，而索引只是针对部分列建立的，那么即使索引有效，数据库在获取到索引列的数据后仍然需要回表查询其他列的数据，这可能会降低查询效率。在某些情况下，如果查询列不是索引列的一部分，那么索引可能会被视为无效。

7. 使用IS NULL或IS NOT NULL

在某些情况下，对索引列使用IS NULL或IS NOT NULL条件可能会导致索引失效。这通常取决于数据库的优化器和索引的具体实现。

8. 索引列参与复杂的JOIN操作

在复杂的JOIN操作中，如果索引列的使用方式不当（如JOIN条件没有直接使用索引列），或者JOIN操作导致的数据量过大，都可能导致索引失效。

9. 查询条件使用NOT IN或<>

当查询条件使用NOT IN或<>时，如果查询的数据量较大，可能会导致索引失效。因为NOT IN和<>通常会导致数据库进行全表扫描来查找不满足条件的数据。

10. 索引列包含大量重复值

如果索引列包含大量重复值，那么索引的区分度就会降低，这可能导致在某些查询中索引无法被有效利用。

11. 数据库配置或优化器决策

在某些情况下，即使查询语句和索引都设计得很好，但由于数据库的配置问题或优化器的决策问题，也可能导致索引失效。例如，如果数据库认为全表扫描比使用索引更快，那么它可能会选择不使用索引。

11. 增加冗余字段减少Join

12. 深分页优化

最左前缀匹配

最左前缀匹配，在MySQL中特指在查询时利用索引的最左边部分进行匹配的原则。这一原则主要适用于组合索引（联合索引），即一个索引包含多个字段的情况。

定义

当执行查询时，如果查询条件涉及到组合索引的前几个列，MySQL就能够利用该复合索引来进行匹配。例如，假设有一个组合索引(col1, col2, col3)，如果查询条件是针对col1、(col1, col2)或(col1, col2, col3)的，那么MySQL就能利用该复合索引进行最左前缀匹配。然而，如果查询条件只涉及到col2、只涉及到col3或者只涉及到col2和col3（即没有包含col1），那么通常情况下（不考虑索引跳跃扫描等其他优化），就无法利用该索引进行最左前缀匹配。

遵守最左前缀法则好处:

提高查询性能：

遵守最左前缀匹配原则可以确保查询能够充分利用索引的优势，加快查询速度，降低数据库的IO成本。
通过减少需要扫描的数据量，索引可以显著提高查询性能，特别是在处理大量数据时。

优化索引使用：

联合索引的创建是为了优化多列查询的性能。如果查询不遵循最左前缀匹配原则，那么索引的利用率将大大降低，甚至可能导致索引失效。
在设计查询语句时，将经常用于查询条件的列放在联合索引的左侧，可以更好地利用索引，提高查询效率。

3.减少资源消耗：

不遵循最左前缀匹配原则的查询可能需要更多的CPU和内存资源来执行全表扫描或复杂的索引扫描，从而增加数据库的负载。
遵守原则可以减少对系统资源的消耗，提高数据库的整体性能。

索引结构限制：

索引的底层结构（如B+树）决定了索引的使用方式。在B+树中，数据是按照索引列的顺序进行排序的。因此，只有从索引的最左边开始匹配，才能有效地利用索引的排序特性。

注意事项

查询条件顺序无关：虽然最左前缀匹配原则要求查询条件包含索引的最左边部分，但查询条件的顺序并不影响匹配结果。即，WHERE col1 = ‘value1’ AND col2 = 'value2’和WHERE col2 = ‘value2’ AND col1 = 'value1’在索引利用上是等价的。
索引跳跃扫描：在MySQL 8.0及更高版本中，引入了索引跳跃扫描的特性。这使得在某些情况下，即使查询条件没有严格遵循最左前缀匹配原则，也能通过索引跳跃扫描的方式利用索引。然而，这并不改变最左前缀匹配原则在大多数情况下的重要性。

综上所述，最左前缀匹配原则是MySQL中联合索引使用的基本原则之一。遵守这一原则可以显著提高查询性能、优化索引使用、减少资源消耗，并充分利用索引的底层结构优势。

什么是回表查询，怎么尽量减少回表查询

回表是数据库查询优化中的一个重要概念，特别是在使用非聚集索引（Secondary Index 或 Non-Clustered Index）进行查询时。回表指的是数据库引擎首先通过非聚集索引找到数据行的位置（通常是主键或行标识符），然后再根据这些位置从聚集索引或数据文件中读取完整的数据行的过程。

具体来说，当一个查询使用了非聚集索引时，数据库会首先通过非聚集索引找到符合条件的记录。非聚集索引的叶子节点包含的是索引键值和数据行的位置标识符（如主键值或行指针）。找到索引键值对应的行标识符后，数据库引擎再根据这些标识符回到表中，查找完整的数据行，以获取查询所需的其他列的数据。

如何减少回表的次数
减少回表的次数是数据库优化中的一个重要目标，因为它可以减少数据库的I/O操作，提高查询性能。以下是一些减少回表次数的方法：
1. 使用覆盖索引：
覆盖索引是指一个非聚集索引包含了查询所需的所有列，从而使查询不需要回表即可获取所有数据。通过创建覆盖索引，可以避免在查询过程中进行回表操作，直接通过索引获取所需数据。
2.查询条件使用聚集索引：
聚集索引反映了记录在磁盘上存储的物理结构，其叶子节点包含了表中的所有数据。因此，当查询条件能够直接使用聚集索引时，数据库可以直接通过聚集索引获取完整的数据行，无需进行回表操作。
3. 使用索引下推:
索引下推是MySQL 5.6及以上版本引入的一种优化技术。在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，从而减少需要回表查询的记录数。这可以在一定程度上减少回表的次数，提高查询效率。
4. 优化查询语句：
通过优化查询语句，如避免使用SELECT *，只查询需要的列；合理使用WHERE子句，减少不必要的条件判断等，都可以在一定程度上减少回表的次数。
5. 调整数据库和表的设计：
在设计数据库和表时，合理规划索引，避免过多的非聚集索引和不必要的表连接等操作，也可以减少回表的次数。
6. 利用缓存机制:
数据库中的缓存机制（如InnoDB的Buffer Pool）可以缓存经常访问的数据行和索引页。通过利用缓存机制，可以减少对磁盘的访问次数，从而间接减少回表的次数。

索引覆盖与索引下推

索引覆盖
定义:索引覆盖是指在一个查询语句中，某个索引已经“覆盖了”需要被查询出来的列，此时就不需要进行回表查询了，这就叫做索引覆盖。简单来说，就是查询的列被所建的索引覆盖，可以直接通过索引获取数据，而无需访问表中的数据行。
特点：

索引覆盖是非聚集复合索引的一种特殊形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。
索引覆盖可以减少对磁盘的访问次数，因为不需要读取完整的数据行，只需要读取索引中的数据即可。
在使用InnoDB存储引擎时，辅助索引的叶子节点包含主键列，因此主键列总是被索引覆盖的。

索引下推
定义：
索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，它利用了数据库引擎中的索引和过滤条件，将部分过滤工作下推到存储引擎层面进行处理，从而减少不必要的数据读取和传输。
特点：

索引下推通过减少全行记录读取来降低IO操作，从而提高查询性能。
它适用于复杂查询条件、多列条件的查询，能够有效地减少不必要的数据读取和传输。
在不使用索引下推的情况下，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些行读取到内存中，然后再进行进一步的过滤操作。而索引下推则尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中。
索引下推适用于MySQL的InnoDB和MyISAM存储引擎，但具体实现可能因存储引擎而异。
实现方式：
索引下推可以通过存储引擎提供的接口或钩子函数来实现，让存储引擎在读取索引页时就进行额外的过滤操作。这样，在索引层面就可以提前过滤掉不符合条件的数据，减少需要传递给查询引擎的数据量和内存消耗。

走了索引但是不快

索引未使用：查询语句可能没有按照索引的方式执行。比如，查询中使用了函数、计算或者连接条件不正确，导致数据库无法利用已有的索引。
索引选择不当：

创建的索引可能不是最优的。例如，对于经常需要范围查询的列，使用哈希索引可能不如B树索引有效。
对于多列索引（联合索引或复合索引），如果查询条件没有遵循最左前缀原则，也可能导致索引失效。

索引覆盖不足：
- 查询的列没有全部被索引覆盖，数据库可能需要额外的查找来获取其他列的数据，从而增加查询时间。
索引过多：

虽然索引可以提高查询速度，但过多的索引会降低写操作的性能，因为每次数据变动都需要更新索引。
过多的索引还会增加存储空间的需求，并可能导致查询优化器选择错误的执行计划。

数据分布不均：

如果索引列的数据分布非常不均匀，比如某个值占据了大部分的行，那么索引的效果会大打折扣。

表扫描更优：

在某些情况下，如查询需要检索大部分数据的表或者是小表，全表扫描可能会比使用索引更快。

锁争用：
在高并发场景下，索引的使用可能会导致大量的锁争用，从而影响查询性能。
系统配置问题：
MySQL的配置参数可能没有优化，比如内存分配、连接池大小、查询缓存等设置不当。
硬件资源限制：
服务器的CPU、内存、I/O等资源可能成为瓶颈，限制了MySQL的性能。
查询语句优化不足：
查询语句本身可能不够优化，比如存在不必要的子查询、连接操作复杂、使用了SELECT *等。

Mysql执行计划Explain

在MySQL中，EXPLAIN是一个用于获取查询语句执行计划的命令。当你对一个查询使用EXPLAIN，MySQL会返回这个查询的执行计划，包括如何执行这个查询以及如何使用索引等信息。这对于优化查询和理解查询的执行方式非常有用。
以下是EXPLAIN的一些关键输出列：
- id：查询的序列号，对于每个查询，都有一个唯一的序列号。
- select_type：查询的类型，比如SIMPLE（简单查询）、PRIMARY（主查询）、UNION（并集查询的一部分）等。
- table：输出结果集的表。
- partitions：匹配的分区信息。
- type：连接类型，常见的有ALL（全表扫描）、index（全索引扫描）、range（范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）等。
- possible_keys：可能用到的索引。
- key：实际使用的索引。
- key_len：实际使用的索引的长度。
- ref：索引的前导列被哪些字段或常量引用。
- rows：估计需要扫描的行数。
- filtered：根据表的条件过滤后剩余的行的百分比。
- Extra：额外的信息，比如是否使用了索引、是否进行了文件排序等。

例如，如果你有一个查询：

SELECT * FROM users WHERE a = 1 AND b > 10 AND c = 2;

使用EXPLAIN来查看这个查询的执行计划：

EXPLAIN SELECT * FROM users WHERE a = 1 AND b > 10 AND c = 2;

在这个例子中，type列显示为range，表示使用了范围扫描，key列显示实际使用的索引是idx_abc（假设这是a, b, c列的联合索引），rows列显示估计需要扫描的行数是5，Extra列提供了额外的信息，比如使用了索引条件。