详解MySQL索引

目录

数据库底层原理

索引

---

数据库底层原理

局部性原理：空间局部性、时间局部性。操作系统页面大小4KB，MySQL页的大小16KB。页是逻辑单位；每次取出一页的数据，取一次进行一次磁盘IO。利用页减少磁盘IO。

插入的时候会按照主键进行升序排序。如果没有主键，怎么办？存储的是链表。

页目录将页面数据进行分组存放该目录下最小的主键值。

分页情况：

页面多了后，就可以对页面进行分组。

没有主键索引使用唯一索引，如果没有唯一索引，使用自增的隐藏字段row_id。索引是排好序的数据结构。

索引

聚集索引（聚族索引）（主键索引）索引的排序方式和数据行的物理存储的顺序是一样的，例如：字典的字母ABC..查询和对应的字；反之，非聚集索引，例如：偏旁查询和汉字。

select * from t1 where a > 1 ; 使用了主键索引。先找到a = 1的索引后，找a > 1;

执行下面语句：

select * from t1 where a = 1 and b =1 and d = 1 ;

需要全表扫描，这样就需要创建联合索引。创建联合索引bcd，按照bcd排序，如图：

 

create index_t1_bcd on t1(b,c,d) ;

可以看出每建一个索引就需要复制全表的数据，MySQL显然不是这样做的，而是如下：

回表过程：回表是为了避免联合索引存放数据，叶子存放的是主键，这样找数据就必须再通过主键索引进行查找，这就是回表。

select  * from t1 where b =1 and c =1 and d=1 ;

查询bcd会通过联合索引，找到主键，通过主键索引找到数据，即回表。

select  * from t1 where c =1 and d=1 ; 不走联合索引，即最左前缀原则：要走那个索引一定要提供最左边的哪一个字段。

select  * from t1 where b=1 ; 走bcd联合索引；

select  * from t1 where b like ’%124‘； 不走联合索引；

区分下面：

select  * from t1 where b =1 and d=1 ； 走，如图只用了一个字段。

假设叶子节点如图：

5.7之前都会将这两个记录取出来（没有考虑d=1），根据主键回表，对两个记录回表，回表两次，通过完整的记录来比较。

5.7之后到页后，先进行d字段的匹配，找到具体的主键索引，不需要经过两次回表，只需要经过一次回表，即索引条件下推。

select  * from t1 where b =1 and e=1； 

select  * from t1 where b >1 ； 没走bcd索引，走的是全表扫描。如图，b>1七条数据都要进行回表，如果全表扫描只要扫描四次。

select  * from t1 where b >6；不走全表扫描。查询优化器会判断是否走bcd索引。

select  * from t1 where b =1 and c =1 and d=1 and a = 1; 走主键索引。 

select  * from t1 where b =1 and c =1 and d=1 or a = 1;两个都走。

select  * from t1 where b =1 and c =1 and d=1 and a > 1;走联合索引

说明利用了a字段。

下面创建e字段的索引。

select  * from t1 where a =1; // 主键索引

select  * from t1 where a ='1';   // 主键索引

select  * from t1 where e =1; // 全表，为什么？主要是根据排序规则（转换规则）；

select  * from t1 where e ='1'; // 走e字段索引

 

selset 'a' = 0 ; // 返回1；

select '1' = 1 ; //返回1 ；

等式两边类型不一样会进行类型的转换，字符串是数字直接转，不是数字转成数字0 ；

下面两种索引建立形式一样：

上面是升序索引

上面desc表示降序索引。

例：selet * ...... 。 查询c=1 and d =1，不会走联合索引，因此要求最左前置原则。例如：查询b = 1，会走bcd索引。查询 like %123，不走。查询b=1 and d =1 ，走，但是和 b = 1 and c =1 and d = 1，查询使用的字段key_len字段长度不一样，前者key_len = 5(一个字段)，后者key_len = 15(三个字段)。这里有一个问题，值用了一个字段怎么确定最终要找的数据呢？这里就要用到索引条件using index condition，减少回表的次数。

select * .... ，查询 b > 1, 全表扫描。查询 b > 6，走bcd索引。因为，优化器会评估走bcd索引还是全表扫描的速度，进行选择。 

使用select b from t1 where b > 1 ;走bcd索引，这就是覆盖索引。

字符串的排序规则和转换规则的问题，导致用不到索引。

参考连接：

https://www.bilibili.com/video/BV1XZ4y1s7wV?p=6