MySQL索引与SQL优化_sql 不创建主键不匹配最左匹配原则也会走索引-CSDN博客

本文链接：https://blog.csdn.net/oxygen3000/article/details/142083737

索引与约束

索引概念

索引，是一种有序的存储结构，按单个或多个列的值进行排序，使用索引可提高搜索效率。

索引分类

索引按数据结构可分为B+树索引（磁盘）、hash索引（内存）以及全文索引；
按存储方式可分为聚集索引和辅助索引；
按列属性可分为主键索引（非空唯一索引）、唯一索引（允许出现一个null）、普通索引（可重复）和前缀索引；
按列属性可分为单列索引和组合索引。

如果使用InnoDB存储引擎，就必须要设置主键。如果没有显示设置，就从非空唯一索引中选择第一个声明为主键，如果非空唯一索引也没有，系统会自动生成6bytes的主键。所以就算我们不创建索引，系统也会自动创建主键索引。

索引的代价

索引既然作为一种存储结构，作为查询的中间商，索引本身是不是也会占用空间，如果我们使用过多索引，空间占比也会上升。

同样，如果我们修改的内容创建了索引，是不是也要修改相应的索引值，维护成本也会上升。

索引实现：B+树

B+树是一种自平衡的树数据结构，又称多路平衡搜索树，它能够保持数据排序，并且允许搜索、顺序访问、插入和删除数据。B+树是B树的一种变体，它在数据库和文件系统中广泛用于索引。（详细解释还需搜索，这里不多赘述）。

我们从B+树的设计开始讨论。B+树的设计使得它在读写大量数据时非常高效，因为它的叶子节点包含所有的数据，并且这些叶子节点是相互链接构成双向链表的，这使得范围查询和顺序访问变得更加简单。此外，所有的叶子节点大小都是固定的16k（默认），每个叶子节点至少存储两行数据。非叶子节点只会记录索引信息。多路平衡查找树以绝对的数据结构优势（矮胖）保证减少磁盘IO。

我们再回到索引分类，讨论一下辅助索引与聚集索引，辅助索引其实存储的是聚集索引的索引，也就是说副主索引并不存储实际数据。从辅助索引查找到id，再回到聚集索引查找数据的过程称为回表查询。

索引使用场景

索引越多，对空间占用就越高，维护成本就越大。那么就要谨慎选择使用索引的情况。

where
group by
order by

这三种场景会先判断是否创建索引，再去使用索引对应的B+树，这样会起到高效查询的作用，防止无效IO。

约束

在数据库设计中，约束（Constraints）是用来规定存储在表中的数据必须满足的条件。约束确保数据库中的数据是有效和准确的，它们可以强制实施业务规则和数据完整性。使用约束可以帮助数据库管理员和开发者维护数据的准确性和一致性，同时也可以减少数据冗余和错误。在设计数据库时，合理地使用约束是非常重要的。

以下是一些常见的数据库约束类型：

主键约束（PRIMARY KEY）：确保列（或列组合）中的每个值都是唯一的。保证表中每一行都可以被唯一标识。一个表可以有一个主键，该主键可以由一个或多个列组成（复合主键）。主键列不能包含 NULL 值。
外键约束（FOREIGN KEY）：用于建立两个表之间的关系，确保引用完整性。一个表中的外键列必须与另一个表的主键列或唯一键列的值相匹配，或者为 NULL。可以定义在父表中的主键或唯一键上的引用操作，如级联更新和级联删除。
唯一约束（UNIQUE）：确保列中的所有值都是唯一的，但允许 NULL 值出现多次。可以有多个 NULL 值，因为 NULL 不等于 NULL。
非空约束（NOT NULL）：确保列中的每个记录必须含有值，不能为 NULL。如果没有指定 NOT NULL 约束，列可以包含 NULL 值。
检查约束（CHECK）：确保列中的值符合一个指定的条件。例如，可以限制某个列的值必须在特定的范围内。在 MySQL 中，CHECK 约束在创建或修改表时被解析，但在实际应用中并不强制执行，直到 MySQL 8.0.16 版本之后才开始支持。
默认值约束（DEFAULT）：当没有为列提供值时，将自动使用默认值。如果插入记录时没有指定列的值，数据库将使用默认值。
级联约束（CASCADE）：通常与外键约束一起使用，定义了在父表中进行更新或删除操作时，子表中相应记录应该如何更新或删除。例如，如果父表中的记录被删除，可以设置级联删除，使得子表中相关的记录也被删除。
主键约束（PRIMARY KEY）：这是主键约束的另一种称呼，用于标识表中每一行的唯一性。
唯一约束（UNIQUE TABLE CONSTRAINT）：与 UNIQUE 约束类似，但通常在创建表时作为表级别的约束定义。

覆盖索引

覆盖索引不是索引，而是针对辅助索引的一种数据查询方式。直接通过辅助索引的B+树就能找到目标值，而不是回表查询。它指的是一个或多个索引包含所有查询所需的数据，这样查询处理时不需要访问表的行数据，直接从索引中获取所需的数据。覆盖索引可以显著提高查询性能，因为它减少了数据库需要做的磁盘I/O操作。

假设有一个employees表，其中包含id, name, department_id, 和 salary 列。如果我们经常根据department_id查询员工的name和salary，我们可以创建一个覆盖索引：

CREATE INDEX idx_department_name_salary ON employees(department_id, name, salary);

这样，当执行如下查询时：

SELECT name, salary FROM employees WHERE department_id = 10;

数据库可以直接使用idx_department_name_salary索引来获取name和salary的数据，而不需要访问表中的实际数据行。

注意事项：

索引维护成本：虽然覆盖索引可以提高查询性能，但它们也会增加索引的维护成本，因为每次对表的INSERT、UPDATE或DELETE操作都需要更新索引。
空间消耗：覆盖索引可能会占用更多的磁盘空间，因为它们包含了更多的列。
适用性：并非所有的查询都能通过覆盖索引来优化，它适用于那些查询条件和返回列都可以通过索引覆盖的情况。

最左匹配原则

最左匹配原则（Leftmost Prefix Matching）是数据库索引使用中的一个概念，特别是在使用B-Tree或B+Tree这类索引结构时。这个原则描述了索引在处理查询条件时的行为。在最左匹配原则中，数据库查询优化器会从索引的最左列开始检查匹配条件，然后根据需要向右移动到下一列。这意味着索引的效率和效果很大程度上依赖于查询条件中列的顺序和索引定义中的列顺序是否匹配。

假设有一个索引定义在表的两列上，如(idx_col1, col2)，这意味着索引首先按照col1的值排序，当col1的值相同时，再按照col2的值排序。在查询时：

完全匹配：如果查询条件中包含col1和col2，并且顺序与索引定义一致，那么索引可以被完全利用。
部分匹配：如果查询条件只包含col1，那么索引也可以被利用，但可能不如完全匹配高效，因为数据库需要在col1匹配后，继续扫描col2列。
不匹配：如果查询条件直接是col2，而没有col1，那么这个索引通常不会被使用，因为索引的效率是基于最左列开始的。

考虑一个表employees，其中有一个索引idx_lastname_firstname定义在lastname和firstname两列上：

CREATE INDEX idx_lastname_firstname ON employees(lastname, firstname);

有效使用索引：查询SELECT * FROM employees WHERE lastname = 'Smith' AND firstname = 'John';将有效利用索引，因为它从lastname开始匹配，然后是firstname。
部分有效使用：查询SELECT * FROM employees WHERE lastname = 'Smith';也能利用索引，但只利用了索引的最左列。
不使用索引：查询SELECT * FROM employees WHERE firstname = 'John';通常不会利用这个索引，因为没有提供lastname的条件。

在设计索引时，应该考虑查询模式，将最常用的搜索列放在索引的最左侧。

索引下推

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，它允许数据库引擎在执行查询时，将部分过滤条件下推到索引中去处理，而不是在索引检索数据之后在表数据上进行过滤。这种技术可以减少数据库需要处理的数据量，从而提高查询性能。

在没有索引下推的情况下，数据库引擎通常会先通过索引找到所有可能匹配的行，然后将这些行加载到内存中，最后应用过滤条件来确定哪些行真正满足查询条件。这种方式可能会导致不必要的数据加载和处理，增加I/O和CPU的开销。索引下推技术通过将查询的一部分或全部过滤条件下推到索引操作中，使得数据库引擎在访问表数据之前就能过滤掉不符合条件的行。这样，只有满足索引过滤条件的行才会被加载到内存中进行进一步的处理。

假设有一个表employees，其中有一个索引idx_department定义在department_id列上。一个没有使用索引下推的查询可能如下：

SELECT * FROM employees WHERE department_id = 10 AND salary > 50000;

在这种情况下，数据库引擎可能会先通过索引找到所有department_id为10的行，然后将这些行加载到内存中，最后检查salary是否大于50000。如果使用索引下推，数据库引擎可能会将salary > 50000的条件也下推到索引操作中，这样只有同时满足department_id为10且salary大于50000的行才会被加载到内存中。

使用索引下推（Index Condition Pushdown, ICP）通常不需要开发者直接干预，因为这是数据库查询优化器的工作。查询优化器会自动分析查询并决定是否使用索引下推来优化查询性能。然而，开发者可以通过编写高效的查询和设计合适的索引来间接促进索引下推的使用。实际工作中用得少。

索引存储

索引数据存储在Buffer Pool（128M）中，也就是缓冲池。B+树的每一个页通过mmap映射磁盘数据块。在访问数据时先通过自适应hash判断某个页是否在缓存中，在就直接取，返回；不在就mmap到缓存。继续寻找页，以此循环直到找到数据。数据会不断加载到缓冲池，但缓冲池又只有128M。所以使用LRU机制进行数据更新与淘汰。如果涉及增删改操作，只更改缓存肯定是不够的，所以要组织一个链表保存修改的数据，然后进行刷盘操作更新磁盘。所以下图三个链表实现上述操作。free_list组织空闲页，flush_list组织脏页，lru_list组织冷热数据。