MySQL-Index

索引篇

为什么Mysql InnoDB选择B+树作为索引数据结构？

1、B+ Tree VS B Tree

B+树只有在叶子节点才存储数据，非叶子节点只存储索引，B树的非叶子节点也要存储数据，所以B+树的单个节点的数据量更小，在相同I/O次数下，能查询更多的节点。
B+树是双链表连接，适合MySQL中基于范围的顺序查找，但是B树不行。

2、B+ Tree VS 二叉树

B+Tree的搜索复杂度是O(logdN)，d表示节点的最大子节点个数，一般大于100，这样可以保证在千万级别的数据量下，B+Tree高度依然保持在3~~4层左右，即一次数据查询操作一般只需要3~~4次I/O操作即可查询到目标。
二叉树节点的子节点最多两个，所以搜索复杂度是O(logN)，因此搜索一条数据在二叉树里面的I/O操作次数远高于B+Tree。

3、B+Tree vs Hash

Hash在进行等值查询的时候效率是O(1)，但是Hash不适合做范围查询。

主键索引，唯一索引，普通索引，前缀索引，联合索引

主键索引
- 一张表只有一个主键索引
- 不能为空，NOT NULL
唯一索引
- 一张表可以有多个唯一索引，但是索引值必须唯一，允许有空值
普通索引
- 不要求是主键，也不要求unique
前缀索引
- 针对字符型字段的前几个字符创建索引，可以建立在char、varchar、binary、varbinary的列上。
- 为了减少索引占用的存储空间，提升查询效率

联合索引

举例如下：创建商品表的product_no 和 name 字段组成联合索引(product_no,name)，创建联合索引的方式如下:

```Java
CREATE INDEX index_product_no_name ON product(product_no,name);


B+Tree的示意图如下：

![1692498640322](https://pengzihao166.oss-cn-beijing.aliyuncs.com/blog/1692498640322.jpg)

联合索引查询数据时，先按照product_no字段比较，在product_no相同的情况下再按name字段比较，也就是说联合索引查询的时候，先将product_no进行排序，再按照product_no相同的情况下将name字段进行排序。

此时联合索引存在**最左匹配原则，**也就是按照最左优先的方式进行索引匹配，使用联合索引的时候如果不遵循“最左匹配原则”索引此时就会失效。

上述创建了product_no，name的联合索引即`(product_no,name)`如果是以下的情况即可匹配联合索引:

- where product_no = 1 and name = 'ali';
- where name = 'bytedance' and product_no = 2;

因为查询优化器的存在，product_no和name字段在where子句中并不重要。

但是下列例子中，因为不符合最左匹配原则，所以无法匹配以上联合索引，联合索引会失效：

-  where name = 'ali';

以上语句会导致索引失效，因为`(product_no,name)`索引是按照product_no先进行排序，在product_no相同的情况下再按name排序，因此name是**全局无序的，局部相对有序的**，这样在没有遵循最左匹配原则的情况下，无法利用索引。

  **联合索引****范围查询**

**查询过程中不一定使用了****联合索引****查询，就代表联合索引中得所有字段都用到了联合索引进行索引查询。**可能是部分索引用到了。

select * from t_table where a > 1 and b = 2

1
2
3


因为a > 1 and b = 2无法通过范围查询定位到从哪个具体的索引开始查询（可能会有a = 2 and b = 2，a = 3 and b = 2的情况），此时意味着b=2的条件并不能通过索引进一步减少扫描的记录数量，即b字段无法利用联合索引进行查询。

select * from t_table where a >= 1 and b = 2

1
2
3


此时因为a >= 1 and b = 2可以直接定位到具体从哪个索引开始扫描查询（即a = 1 and b = 2），此时b = 2是相对有序全局无序的（即先按照a字段进行排序，再在a字段相等的情况下按照b字段进行排序）。

SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2


因为a >= 2 and a <= 8 and b = 2可以直接定位到具体从哪个索引开始扫描（即 a = 2 and b = 2），此时b = 2是在a = 2开始排序的前提下，在a相等的情况下再对b进行排序，即b字段是局部有序，全局无序的状态。

SELECT * FROM t_user WHERE name like 'j%' and age = 22

二级索引先根据name字段进行排序，在name字段相等的前提下，再对age进行排序，此时name前缀为‘j’的二级索引是相邻的

#### 什么时候需要创建索引？

- 字段有唯一性限制
- 经常用于`WHERE`查询的字段
- 经常用于`GROUP BY`和`ORDER BY`字段

#### 什么时候不需要索引？

- `where` 条件、`GROUP BY` 、`ORDER BY` 里面用不到的字段。
- 字段中存在大量重复数据，不需要创建索引，如性别字段，查询优化器发现某个值出现在表的数据行中得百分比很高的时候，就会忽略索引，进行全表扫描。
- 表的数据太少的时候，就不需要创建索引。
- 经常更新的字段不需要创建索引。比如电商的余额

#### 索引优化的办法？

- 前缀索引优化
 - ORDER BY无法使用前缀索引
 - 无法把前缀索引用作覆盖索引
- 覆盖索引优化
- 主键索引最好自增
 - 如果不是自增化，就可能会导致插入的数据，插入到现有数据页中得某个位置，这将不得不移动其他数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，这时候就会产生页分裂，页分裂可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
- 防止索引失效
- 索引最好设置成NOT NULL
 - 会占用物理空间
 - 会导致优化器在做索引选择的时候更加复杂

#### 索引失效有哪些？

- 对索引使用左或左右模糊匹配

因为索引查询的根本目的是为了通过索引能够缩小查询范围，从而提高查询的速率，但是左模糊或左右模糊查询无法直接通过索引直接定位到符合要求的字段而减小查询范围，所以会导致索引失效。

- 对索引使用函数
 - 因为索引保存的是索引字段的原始值，而不是经过函数计算之后的值，索引无法走索引了。
 - 不过在MySQL8.0之后可以针对函数计算之后的值建立索引，即该索引的值是函数计算后的值，可以通过扫描该索引进行查询数据了。

- 对索引进行表达式计算
 因为索引保存的是索引字段的原始值，而不是id+1表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

- 对索引隐式类型转换
 
 id定义的是int，phone定义的是varchar
 
 ```sql
 select * from t_user where id = "1";//字符串转整数类型
 select * from t_user where id = CAST("1" AS signed int);//此时id字段并没有用到CAST函数，索引能够走到索引字段。
 
 
 select * from t_user where phone = 1300000001;//整数转字符串
 select * from t_user where CAST(phone AS signed int) = 1300000001;//此时phone索引字段使用了CAST函数，而对索引使用函数会导致索引失效。

联合索引非最左匹配
- SELECT * FROM y_table where a = 1 and b = 1 and c = 1;
- 联合索引查询的时候B+树是先按a的顺序进行排序，然后在a相同的情况下对b进行排序，在b相同的情况下，最后对c进行排序。
WHERE子句中得OR查询
- 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。