Mysql底层索引详解

向右看齐 2023-06-03 04:55 47阅读 0赞

## 索引是帮助MySQL高效获取数据的排好序的数据结构。 ##

索引本质以及索引类型：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70]  
MySQL底层索引的数据结构是B+Tree（B-Tree变种）

1.  非叶子节点不存储data，只存储索引，可以放更多的索引；
2.  顺序访问指针，提高区间访问的性能。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 1]  
    B+Tree树节点的大小为16KB，每个树的的结点会被load到内存，每次和磁盘进行一次IO操作（比较耗时）。
3.  一个索引的内存大小为8B，指针的内存大小为6B，所以一个索引的实际大小为14B(字节）。
4.  16KB / 14B 约等于1170，所以一个B+Tree树节点可以存放1170个索引。那么第二层的索引个数大约为1170 \* 1170个。
5.  毛估第三层一个索引+对应的数据的大小为1KB，那么第三层一个叶子节点可以包含16KB / 1KB = 16个索引。那么这样下来\*\*B+Tree的三层可以存放1170 x 1170 x 16 = 2千多万个索引。\*\*  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 2]

1，为什么不使用Hash作为索引方法？  
答：不支撑范围查询。select \* from student where id > 10;  
**B+Tree可以支撑范围查找，因为他有一个指针指向下一个索引**。如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 3]

## 数据库的表结构，数据，索引的存放位置： ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 4]  
MySQL表的存储引擎为MyISAM:

1.  frm文件存放表结构；
2.  MYD文件存放表中的数据；
3.  MYI文件存放表的索引。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 5]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 6]  
    MyISAM存储引擎的查找过程：  
    1，将B+Tree的索引节点load到内存，进行比较，依次往树的下面寻找；  
    2，最后在树的叶子节点找到与之对应的索引值，而**索引的data存放的是：索引所在数据行的磁盘文件指针（如上图：Ox90)；**  
    3，根据该指针，把数据从数据表中查询出来即可。  
    **该过程查找了2个文件，一个MYI文件，一个MYD文件；相比InnoDB多了一个磁盘IO。**

MySQL表的存储引擎为InnoDB:

1，frm文件存放表结构；  
2，ibd文件存放索引和数据。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 7]  
InnoDB存储引擎的查找过程：  
1，将B+Tree的索引节点load到内存，进行比较，依次往树的下面寻找；  
2，最后在树的叶子节点找到与之对应的索引值，而**索引的data存放的是：索引对应行的数据；**  
该过程只查找了1个文件，ibd文件，相比MyISAM存储引擎少了一次磁盘IO。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 8]

**1，什么是聚集索引？**

*  聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。
 *  聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
 *  InnoDB的主键索引就是聚集索引，叶子节点包含了完整的数据记录，数据和索引存储在同一个文件；MyISAM的主键索引就是非聚集索引，索引存储在MYI文件，数据存储在MYD文件。

**2，InnoDB表必须有主键，并且推荐使用整型的自增主键？**

*  如果InnoDB表没有设置主键，后台会自动生成一个主键，所以必须自己建，没必要MySQL帮我们再加一个字段，占用内存；
 *  如果使用UUID作为主键，在进行比较时（abc3f，abcdf 转成ASCII码进行比较) 没有整型数据大小比较快；
 *  如果不是自增，假如叶子节点达到16个满了，再生成一个索引时，恰好索引值大小处于该叶子节点的中间，导致叶子节点要拆分，增加了B+Tree的层级，减慢了索引查找的性能。如下图：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 9]

## Innodb的非主键索引（负索引）查找逻辑： ##

先根据name找到ID，然后根据ID找到对应的数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 10]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 11]

b树和b+树的区别：[b树和b+树的区别][b_b]，[聚集索引和非聚集索引的区别][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70]: /images/20230601/d305020c229b4500a1effb0814a68a14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 1]: /images/20230601/66b4a07965a84e7fbc36c9ea930cbbc8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 2]: /images/20230601/31ca06408fe248939aca15dc1060203a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 3]: /images/20230601/b2d967e5e81e459c9bf91faeec6e85bf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 4]: /images/20230601/9bcacfc92f2847da8f14334e4dec200f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 5]: /images/20230601/cfcd3c13051146d2a3f0f5a1316cb7ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 6]: /images/20230601/5b24cb7dad5844259328a49376ed3847.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 7]: /images/20230601/eaebdf5261dc43d5baf6020905ff394b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 8]: /images/20230601/2ef9aca6ef834dd99919337b8a815865.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 9]: /images/20230601/3ae7546372a446fa869d2cf7fad1c466.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 10]: /images/20230601/e28227e567f04c979ea573f43452f4f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RocXR6cQ_size_16_color_FFFFFF_t_70 11]: /images/20230601/feb6c13b9cbe4446b9d682266fc78550.png
[b_b]: https://www.cnblogs.com/ivictor/p/5849061.html
[Link 1]: https://www.cnblogs.com/jiawen010/p/11805241.html