mysql优化——1.索引底层原理

阳光穿透心脏的1/2处 2022-03-06 12:24 183阅读 0赞

# 索引底层原理个人相关理解 #

每天多学一点点~  
话不多说，这就开始吧…

### 文章目录 ###

*  索引底层原理个人相关理解
 *   *  1.索引到底是什么
     *  2.B-Tree
     *  3.B+Tree(B-Tree变种)
     *  4.MyISAM索引实现(非聚集)
     *  5.INNodb索引实现(聚集)
     *  6.联合索引
     *  7.一些面试题
     *  8.结语

## 1.索引到底是什么 ##

索引是帮助MySQL高效获取数据的排好序的数据结构  
索引存储在文件里  
索引结构

1.  二叉树
2.  红黑树
3.  HASH
4.  BTREE  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70]  
    这里推荐一个国外的数据结构教学网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

## 2.B-Tree ##

度(Degree)-节点的数据存储个数  
叶节点具有相同的深度  
叶节点的指针为空  
节点中的数据key从左到右递增排列  
![在这里插入图片描述][20190318224124985.png]

## 3.B+Tree(B-Tree变种) ##

非叶子节点不存储data，只存储key，**可以增大度**  
叶子节点不存储指针  
顺序访问指针，提高区间访问的性能  
![在这里插入图片描述][20190318224222464.png]  
**mysql的MyISAM和innodb底层都是b+tree**

## 4.MyISAM索引实现(非聚集) ##

MyISAM索引文件和数据文件是分离的  
找到mysql的安装目下data文件夹里  
![在这里插入图片描述][20190318224710278.png]  
如上图 MyISAM是非聚集的，frm是表结构文件，MYD是数据，MYI是索引文件，**其索引底层b+tree叶子节点放的是 数据文件 的地址**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 1]  
**主键索引与非主键索引结构是一样的**

## 5.INNodb索引实现(聚集) ##

如上图，innodb的idb文件，索引与数据是 放在一起的，所以是聚集

1.  数据文件本身就是索引文件
2.  表数据文件本身就是按B+Tree组织的一个索引结构文件
3.  聚集索引-叶节点包含了完整的数据记录  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 2]

**注意：innodb的主键索引结构，其叶子节点存储的是那一行的记录；其非主键索引，其叶子节点存储的是主键id**

**默认情况下，就算你子见表时没有选择主键，innodb会自动选择不重复的那一列作为主键；若没有不重复的，则会默认创建主键**

\***面试题：**

1.  为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？\*
2.  为什么非主键索引结构叶子节点存储的是主键值？

相信小伙伴们看了innodb底层b+tree后，应该有了自己的理解，我这里就不赘述了

## 6.联合索引 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 3]  
如上图，联合索引即把几个字段放在一起，要遵循最左前缀原理  
至于什么是最左前缀原理，等下一篇  
[mysql优化——2.explain详解与实践][mysql_2.explain]再讲解

## 7.一些面试题 ##

1.  mysql 的innodb引擎一页大小为何是16kb？  
    show variables like ‘innodb\_page\_size’;看默认mysql一页大小
    
    result = 叶子节点数 \* 一个叶子节点里能存放的数据条数
    
    叶子节点数 = 根节点的指针数 = \{索引值+ 指针\}组合数 = 索引值个数
    
    **假设**一行数据的大小-=1KB（合理的值）  
    一个叶子节点能够存放的数据条数？16KB / 1KB = 16条
    
    索引值个数？16KB / \{int类型索引值8B + 指针6B\} (14B) = 16KB / 14B = 1170  
    result = 1170\*16=18720
    
    B+树高度为2 ： 1170\*16=18720
    
    B+树高度为3： 1170 \* 1170 \* 16 = 21902400
    
    总结：若h=3，第一层 有1170个节点，第二层一个 节点里面有1170节点，一个节点里面又有1170个节点，所以是1170x1170  
    16k可以存取2000w数据，足够了~
2.  为何不用hash索引  
    因为范围查询不好用，比如> < 。hash碰撞也有，但概率小  
    因为hash索引是根据hash算法算出来的，没法保证排列顺序
3.  为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？  
    **为何必须要有主键**：  
    因为底层是B+TREE,就算你建表时候不设主键，若你表中有一列数据具有唯一性，mysql会默认将这个作为主键。  
    若表中没有，mysql会默认给你建主键，rowId，是整型数值
    
    **推荐使用整型的自增主键（为何不用uuid字符串）**：  
    uuid不能保证顺序（b+tree底层是排序的）  
    第一，整型比大小快  
    第二，b+tree底层从左到右是递增
4.  为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)  
    一致性:插入和修改数据时候需要维护索引，就不需要又维护主键索引又维护非主键索引  
    节省存储空间:就不用说了吧

## 8.结语 ##

**世上无难事，只怕有心人，每天积累一点点，fighting！！！**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70]: /images/20220303/0ca0a1a09de1412db886a1aa3cb41b2e.png
[20190318224124985.png]: /images/20220303/20290a3d5d384f02a296ffa4937b5aad.png
[20190318224222464.png]: /images/20220303/376f743aee80442ebfe1d8fc57e573f1.png
[20190318224710278.png]: /images/20220303/5a406c152f734042a2e5d0664ecf9bdb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 1]: /images/20220303/e0af4fea747346f7966e585a7e029236.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 2]: /images/20220303/2d5d0414c8c741d9b1037813fed06c78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQzNzYzMw_size_16_color_FFFFFF_t_70 3]: /images/20220303/73371c906e3e425d82d96f9db0a9369c.png
[mysql_2.explain]: https://blog.csdn.net/weixin_42437633/article/details/88652790