Mysql索引原理

---

总所周知，Mysql的核心知识都是围绕索引展开的，如果没有索引，Mysql就和普通IO存数据没有任何区别了。了解索引，就能够在工作和面试中应对大部分数据库的问题，让我们开始吧。

存储引擎

最常见的两种存储引擎是InnoDB和MyIsam。

InnoDB

InnoDB是一种支持事务和外键的行级锁的存储引擎。它使用**多版本并发控制（MVCC）**来实现事务的隔离性。

MVCC（Multi-Version Concurrency Control）多版本并发控制。核心思想是为每个事务创建一个独立的版本，确保在多个并发事务修改数据库时数据的一致性和隔离性。

在MVCC中，当一个事务要修改一行数据时，它会复制一份原始数据，称为快照（Snapshot）。在事务中查询一行数据时，它只能查到它的快照版本。如果这个过程中其他事务对这行数据进行了修改，这些修改对当前事务是不可见的。这样可以确保事务之间互不干扰，保证数据的一致性和隔离性。

MVCC实现机制，是通过在每行记录上创建一个隐藏的“行版本号”和一个“系统版本号”来实现的。当一个事务启动时，它会获得一个特定的系统版本号，并只能看到已经存在于此版本号之前的行版本。这样可以避免读取到其他并发事务中修改的数据，保证数据的一致性和隔离性。

同时，MVCC使用锁机制来防止并发事务同时对同一行数据进行修改。对一条数据读写需要获得一个行级锁，分为读锁和写锁。其中写锁会与其他锁互斥，必须等待前一个写锁释放，后续获取锁的操作才会成功，这用于保证数据的一致性。

MyISAM

MyISAM是一种表级锁的存储引擎。

表级锁特点是：降低写的效率，提升读的效率。原因如下：

• 减少锁冲突：表级锁可以减少锁冲突的可能性，因为当多个事务同时访问同一张表时，只有一个事务可以获取到锁，其他事务则需要等待。这样可以避免多个事务同时读取或修改同一行数据，从而减少锁竞争和锁冲突，提高并发处理能力。

• 减少锁开销：相对于行级锁，表级锁的锁开销更小。在使用行级锁时，每个事务需要维护自己的锁，因此需要更多的内存和CPU资源来处理锁冲突和锁释放。而使用表级锁时，只需要维护一张表的锁，可以减少锁开销和消耗的系统资源。

• 优化查询计划：当一个事务锁定了一张表时，MySQL可以根据锁的信息来优化查询计划，从而提高查询效率。例如，如果一个查询需要扫描整个表，MySQL可以使用全表扫描来加快查询速度，因为此时不会有其他事务对该表进行修改操作。

索引的分类

逻辑上分为5类

普通索引（NORMAL）

字段值可以重复，也可以为null。

唯一索引（UNIQUE）

顾名思义，不允许有重复值，但允许有null值，并且可以有多个null值。

唯一索引与普通索引基本相同，唯一区别是如果插入的值重复了会报错

主键索引（PRIMARY）

只能用于主键字段的索引，必须是非空且唯一的。一个表只能有一个主键索引，一个主键索引可以包含多个字段。

如果不指定主键索引：

1. 自动给一个没有null值的唯一索引创建主键索引
2. 如果没有，将会给默认的RowId创建主键索引，他是一个int型，自动递增的隐藏字段

全文索引（FULLTEXT）

只能对字符串字段（char、varchar、text等）创建全文索引。
全文检索比较占空间，匹配效率也不是特别高。很少使用，一般用其他全文检索（如ElasticSearch）中间件代替。

空间索引（SPATAIL）

只能对地理类型字段（geometry）创建。使用的比较少。

也可以根据索引的字段数，分为单列索引和复合索引。上面的所有类别索引都可以创建为符合索引。

---

索引方法

索引方法分为2种。

B+树索引

一个表可以有多个索引，B+树索引会为每个索引创建一颗B+树。B+树主要具有以下特点：

• 多路平衡查找树
  多路：每个节点可以包含多个索引键，使得树很粗但是矮，减少查找路径长度，提高查找效率。
  平衡：B+树是高度平衡的，叶子节点的高度差不超过1，保证查询时间的稳定性。

• 叶子节点有序
  B+树的非叶子节点不存储数据（只存索引键），只有叶子节点存储数据。
  叶子节点按照关键字顺序排列，并通过指针链接在一起形成链表。这样可以方便地进行范围查询、关键字排序等操作。

B+树的这些特点使得它在数据库系统中被广泛应用，能够高效地支持数据的插入、删除和查询操作，尤其擅长支持范围查询和区间查找等操作。

回表

我们知道一个表可以有多个B+树索引，但并不是每一个B+树的索引都有数据，实际上只有主键索引有数据，非主键索引的叶子节点存储的是这条数据的主键。

包含数据的索引成为聚簇索引，不包含数据的索引成为非聚簇索引

当我们使用一个非主键索引（非聚簇索引）查找时，只能得到一个主键。必须把这个主键拿到主键索引（聚簇索引）进行二次查询，才能得到数据。这就是回表，回表会增加数据库的开销。

索引覆盖

但是有一种情况，我们是不需要回表的：即非聚簇索引的关键字，已经包含了需要查询的字段。这种情况根本不需要再走一次主键索引得到完整的数据。这就是索引覆盖，索引覆盖可以减少数据库开销。

Hash索引

哈希索引是一种基于哈希表实现的索引方式，它的原理如下：

• 哈希函数：使用哈希函数对索引键运算得到一个固定长度的哈希值。哈希函数需要满足以下条件：

  • 能够快速计算出对应的哈希值；
  • 对于不同的索引键，计算出的哈希值应该是不同的；
  • 对于相同的索引键，计算出的哈希值应该是相同的。

• 哈希表：哈希表存储索引键和数据地址之间的对应关系。哈希表是一个数组，每个元素称为一个桶（bucket），桶实际上是一个链表，链表中存储哈希值相同的索引键。

当执行查询操作时，先计算索引键的哈希值，然后在哈希表中查找对应的桶，查找桶中哈希值相同的索引键，在链表中遍历查找对应的数据地址。

需要注意的是，哈希索引只适用于等值查询，不支持范围查询和排序操作。此外，为了避免哈希冲突，需要合理选择哈希函数和桶的数量，并且需要定期重新构建哈希表来保证查询性能和效率。

索引的使用

1. 对于复合索引，如果查询字段不是最左边的值（1-n个都可以），将不会走索引
2. like同理，如果模糊匹配的字段，最左边不是确定的，将不会走索引
3. 复合索引的字段之间使用or会导致索引失效，但是在两个独立的索引字段之间使用or则不会
4. 类型不匹配不会走索引，如字符串111，即使不加单引号也可以执行，但mysql会把它当作int处理，无法走字符串的索引
5. 索引可以增加查询效率，但会降低增删改的效率，因为这些操作需要重新维护索引。
   是否对一个字段创建索引，需要根据具体场景来考量
6. not in、not exist、!=、<>、in (select语句)　会导致索引失效
7. 对字段进行运算或者函数，不会走索引
8. 查询条件是null，不会走索引