【2023】MySQL详细 ——SQL优化篇

索引

索引是一种数据结构

• 索引的优劣势：

索引结构：

My sql 索引主要指的是B+TREE索引

BTREE树结构：

如5叉数：一个叉代表一个指针，每一个节点最大能有4个元素（节点元素个数=M叉数少1）

B+TREE树

 

MySQL中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加了一个指向相邻元素的链表指针，就形成了带有顺序指针的B+Tree，提供区间的性能

索引分类

• 单值索引
• 复合索引
• 唯一索引

索引设计原则：

• 对查询频率较高的字段，数据量较大的表
• 索引字段的选择，最佳候选列应当从where子句中的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引会降低增删改的效率
• 使用短索引
• 利用最左前缀，使用复合索引时如果没有遵循最左前缀法则，索引则会失效

最左前缀：

表示如果索引了多列，查询需要从最左列开始，并且不跳过中间索引；

如果没有从最左列开始作为条件，索引失效；

如果中间有跳跃，后面的字段则不会使用索引

1. 只需要包含最左列就行，跟顺序没关系。

1. 范围查询时，右边的列不使用索引，（使用><不使用等于时）

1. 索引上不能进行运算，否则索引失效（）

1. 类型转换，如：string类型，做条件查询时，没加’ ‘，则会自动帮我们转为int

2. 不要使用* ，尽量使用覆盖索引（查询的字段在索引中已经包括了 ）

• 因为如果查询的字段通过索引已经查询出来了，在进行回表查询时，发现字段已经都有了，就不会回表查询了，如果是*，发现还有些没使用索引的字段没有查询出来，则还会回去回表查询

1. 用or 分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被引用到

1. 使用like进行模糊匹配，后面加%会走索引，但前面加%就不会走索引

1. 当mysql发现不走索引比索引快时，mysql会不走索引，如：查询的字段大部分都是你包含的值时。-查询北京市就不会走索引

1. is null ，is not null；有时会失效；当查询的字段大部分都是有数据时，使用is null就会走索引，而使用is not null就不会走索引，因为大部分字段都是非null的mysql就会觉得全表扫描更快，

2. in 走索引，not 不走索引，原理同上；

1. 查询日期时

需要将函数改为范围，要不然无法使用索引