Mysql 索引原理和优化方式

一、索引原理

1，什么是索引

索引是存储引擎用于快速找到记录的一种数据结构。可以联想到字典中的目录。

2，索引的分类

按数据结构分

（1） Hash 索引

Hash 索引是比较常见的一种索引，他的单条记录查询的效率很高，时间复杂度为1。但是，Hash索引并不是最常用的数据库索引类型，尤其是我们常用的Mysql Innodb引擎就是不支持hash索引的。主要有以下原因：

Hash索引适合精确查找，但是范围查找不适合，因为存储引擎都会为每一行计算一个hash码，hash码都是比较小的，并且不同键值行的hash码通常是不一样的，hash索引中存储的就是Hash码，hash 码彼此之间是没有规律的；
且 Hash 操作并不能保证顺序性，所以值相近的两个数据，Hash值相差很远，被分到不同的桶中。这就是为什么hash索引只能进行全职匹配的查询，因为只有这样，hash码才能够匹配到数据。

（2） 二叉树

先来介绍下最经典的二叉树的特点：

• 二叉树的时间复杂度为 O(n)
• 一个节点只能有两个子节点。即度不超过2
• 左子节点 小于 本节点，右子节点 大于 本节点

但是在极端情况下会出现链化的情况，即节点一直在某一边增加。

平衡二叉树(Balanced Binary Tree,简称 ABT)是一种特殊的二叉树，其中每个节点的左右子树的高度之差的绝对值不超过1,并且它的左子树和右子树都是平衡二叉树。平衡二叉树的特点：

• 根节点会随着数据的改变而变更
• 数据量越多，遍历次数越多，IO次数就越多，就越慢（磁盘的IO由树高决定）

（3）B-树

B-树是一棵多路平衡查找树,对于一棵M阶的B-树有以下的性质:

• 根节点至少有两个子女.
• 每个节点包含k-1个元素和k个孩子,其中m/2 <= k <= m.
• 每一个叶子节点都包含k-1个元素,其中m/2 <= k <= m.
• 所有的叶子节点位于同一层.
• 每个节点中的元素从小到大排列,那么k-1个元素正好是k个孩子包含的值域的划分.

可以将B-树理解为一棵更加矮胖的二叉搜索树.
二叉搜索树(Binary Search Tree,简称 BST)，是一种特殊的二叉树，其中每个节点的左子树的值都小于该节点的值，而每个节点的右子树的值都大于该节点的值。

（4）B+树

MySQL 中最常用的索引的数据结构是 B+ 树。B+树是B-树的进阶版本，在B-树的基础上又做了如下的限制:

• 每个中间节点不保存数据,只用来索引,也就意味着所有非叶子节点的值都被保存了一份在叶子节点中.
• 叶子节点之间根据自身的顺序进行了链接.

这样可以带来什么好处呢?

• 中间节点不保存数据，那么就可以保存更多的索引，树的层级更少，减少数据库磁盘IO的次数.
• 因为中间节点不保存数据，所以每一次的查找都会命中到叶子节点，而叶子节点是处在同一层的，因此查询的性能更加的稳定.
• 所有的叶子节点按顺序链接成了链表，因此可以方便的话进行范围查询.

按实现方式分

按存储数据的方式可以分为：

• 聚簇索引：将索引和表数据放到同一个节点中，索引结构的叶子节点存放数据，找到了索引，即找到了数据。一个表只能有一个聚簇索引。

• 非聚簇索引：索引存储和数据存储分离，索引结构的叶子节点指向数据的位置。通过索引找到位置，再通过位置找到数据，这个过程叫做回表查询。一个表可以有多个非聚簇索引。非聚簇索引也成为辅助索引。

按实现方式：

• 覆盖索引：要求查询涉及到的列都在索引中出现，可以避免访问数据表本身，提高查询性能，如

select name from color where name = '张三'。name为索引字

• 联合索引：涵盖多个列，适用于需要对多个列进行过滤或排序的查询，但不要求所有查询涉及到的列都在索引中出现，因此仍然可能需要进行回表操作，根据主键跑到主键的聚簇索引里去找。

CREATE INDEX idx_orders ON orders (customer_id, order_date, order_number);
SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

因此，SQL里最好是直接走覆盖索引的方式，不要去回表到聚簇索引。
即使真的要回表到聚簇索引，那也尽可能用limit、where之类的语句从联合索引里筛选少数数据，然后再回表到聚簇索引里去，这样性能也会好一些。

另外，一个表可以有多个非聚簇索引,但只能有一个聚簇索引。 因为非聚簇索引不会改变表中数据的物理排序,而聚簇索引会根据索引顺序来存储数据行。

MySQL中，主键索引页+数据页组成的B+树就是聚簇索引。聚簇索引中数据页记录的是一条记录的完整的记录。

MySQL 在存储数据的时候是以数据页为最小单位的，且数据在数据页中的存储是连续的，数据页中的数据是按照主键排序的（没有主键是由 MySQL自己维护的 ROW_ID 来排序的），数据页和数据页之间是通过双向链表来关联的，数据与数据时间是通过单向链表来关联的。

也就是说有一个在每个数据页中，他必然就有一个最小的主键，然后每个数据页的页号和最小的