447-MySQL(InnoDB的主键和二级索引树)

朴灿烈づ我的快乐病毒、 2022-09-06 10:13 157阅读 0赞

## InnoDB存储引擎 ##

**数据和索引存储在一起。**  
**一张表有2个文件**：student.frm，student.ibd（数据和索引文件）

## 场景1（主键索引树） ##

**uid是主键，其他字段没有添加任何索引**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]

select * from student;

**搜索的是整个索引树。**  
**主键索引树**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
**从叶子节点的链表上，从左到右遍历。**  
![在这里插入图片描述][85aa32ab76b04357b0d483008dfe73c0.png]

select * from student where uid<5;

**MySQL看到有过滤条件，uid有索引，如果是做等值查询，相当于从根节点开始，按照二分搜索的方式找到5，就找到5对应的数据data了。如果是做范围查询，直接在有序链表中搜索就可以了。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]

select * from student where name='linfeng';

**name没有索引。整张表搜索咯，直接搜有序链表，搜完！**  
![在这里插入图片描述][4dc0526ad8964d2a9ab1c47cba1d0e4b.png]  
**InnoDB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，如图：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]

## 场景2（回表，二级索引树） ##

**uid是主键，name创建了普通索引（二级索引）**  
**name为索引构建的索引树，叫辅助索引树，二级索引树**  
**key是name，外加uid的值。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]  
**key是辅助索引的值，data是所在记录行的主键值**

select name from student where name='linfeng';

**过滤字段是name，name有索引，从name的二级索引树上等值匹配‘linfeng’**

select uid,name from student where name='linfeng';

**搜索二级索引树就完事了。**

select * from student where name='linfeng';

**此句涉及到回表！**  
过滤字段是name，有索引，搜索name的二级索引树，找到linfeng了，但是这里的select是加\*，不仅仅需要uid,name（这2个在二级索引树上就找到了），还需要age,sex，二级索引树没有存age，sex。  
**所以MySQL的执行是：**  
1、搜索name的二级索引树，找到linfeng对应的主键uid：4  
2、再拿uid=4回表在主键索引树上搜索uid=4的那一行的记录  
**回表意味着更多的搜索，更多的磁盘I/O，效率就低了。**

如果业务只需要uid,name，就不要写出select \*了，避免回表。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]  
**删除name的索引，看看**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]

select * from student where age=20 order by name;

**如果只给age添加索引，行不行？还有什么没有考虑到？**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]  
**age命中索引了，但是有回表。**  
**一次SQL执行只能用到1个索引，不能给name加载索引来解决问题。**  
**解决方法：**

在二级索引树上的key:age+name，形成多列索引，联合索引，先按age排序再按name排序。age相同按name进行排序。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]  
**成功解决！**  
![在这里插入图片描述][041326bb75af41398f1ef2b8676ce46b.png]  
**如果只有age建索引，会有using filesort，需要创建name+age的多列索引**  
**多列索引一定要使用到第1个列！**

**InnoDB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，如图：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。从索引树上可以看到，InnoDB的索引关键字和数据都是在一起存放的，体现在磁盘存储上，例如创建一个user表，在磁盘上只存储两种文件，user.frm（存储表的结构），user.ibd（存储索引和数据）。  
InnoDB的索引树叶节点包含了完整的数据记录，这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（区别于MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动  
为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/8636c767aad5453c864fa76b11aed088.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/eb249b56d07e4a5fbb37957c369ce36c.png
[85aa32ab76b04357b0d483008dfe73c0.png]: /images/20220829/2a63758a74074f7b81de6d24c06b1131.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/6d2c989e70c44c9bb01e0c43d17a1bbc.png
[4dc0526ad8964d2a9ab1c47cba1d0e4b.png]: /images/20220829/c18911445be14362a72b2e3335b78daa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/47076f7131d54ff09ad8af34ba952d16.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/dd83b42cc48544cdb7c1d7c47b0ea12d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/74a0e593629d4c1399286d7116e3cc09.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/84ae66d52f344493b0e7ab77aefb3d52.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/279088f4ffa74119a1aa4d665e87fd57.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/6a00b549e67b410cb84763a55693e617.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/e34f11644a5d4d188143fe1caede6fde.png
[041326bb75af41398f1ef2b8676ce46b.png]: /images/20220829/03bc74932c9f46d4bdddc8fbd9838a52.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/207c7c868f6d4992bb98aed373b81ea7.png