MySQL学习提升

分手后的思念是犯贱 2022-02-23 10:38 138阅读 0赞

# 一、深入MySQL B + Tree索引机制 #

链接: https://pan.baidu.com/s/1YjSI2jKChUAKFfjUJVoP8w 提取码: 900g

## 课程安排 ##

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

## 索引的定义 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 1]  
InnoDB 是 Mysql 的默认存储引擎(Mysql5.5.5之前是MyISAM，文档)。InnoDB 中的所使用的是 B+tree 的数据结构，上图中是 Hash 数据结构，不是 InnoDB 存储引擎。

## 为什么MySQL会选择 B + Tree 这种数据结构？ ##

计算机科学中的树有很多种，这里只讲几个常见的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 2]

数据结构模拟：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

来看看查询算法的数据结构的演进：

### 二叉查找树 ###

二叉排序树（Binary Sort Tree），又称二叉查找树（Binary Search Tree），亦称二叉搜索树。

> [二叉排序树][Link 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 3]

### 平衡二叉查找树 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 4]

平衡二叉查找树是为了解决二叉查询书数据不均匀而导致线性数据结构的问题而产生的。

平衡二叉搜索树（英语：Balanced Binary Tree）是一种结构平衡的二叉搜索树，即叶节点高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。它能在O(log n)内完成插入、查找和删除操作，最早被发明的平衡二叉搜索树为AVL树。

常见的平衡二叉搜索树有：

*  AVL树
 *  红黑树
 *  Treap
 *  节点大小平衡树

#### AVL树 ####

在计算机科学中，AVL树是最早被发明的自平衡二叉查找树。在AVL树中，任一节点对应的两棵子树的最大高度差为1，因此它也被称为高度平衡树。查找、插入和删除在平均和最坏情况下的时间复杂度都是 。增加和删除元素的操作则可能需要借由一次或多次树旋转，以实现树的重新平衡。AVL树得名于它的发明者G. M. Adelson-Velsky和Evgenii Landis，他们在1962年的论文《An algorithm for the organization of information》中公开了这一数据结构。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 5]

#### 红黑树 ####

红黑树（英语：Red–black tree）是一种自平衡二叉查找树，是在计算机科学中用到的一种数据结构，典型的用途是实现关联数组。它是在1972年由鲁道夫·贝尔发明的，他称之为"对称二叉B树"，它现代的名字是在Leo J. Guibas和Robert Sedgewick于1978年写的一篇论文中获得的。

> [平衡二叉查找树][Link 2]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 6]

### 平衡多路查找树（B-Tree） ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 7]  
多路故名思议就是有多个分叉，上图是以3叉为例，也就是每个节点最多分3叉，节点上面的关键字个数为：叉路数 - 1，这里是为2个关键字，那如果当关键字的个数超过2个时，关键字就会发生分裂，而后合并，使其保持2个，如下图：

![在这里插入图片描述][20190408152008477.gif]

### B+Tree ###

MySQL索引采用的是 B + Tree 数据结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 8]

#### B + Tree 的特点 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 9]

#### B + Tree 的优势 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 10]

> [二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树][AVLTree_B-Tree_B]

## MySQL B + Tree 具体落地形式 ##

MySQL数据库1个库允许有多个存储引擎的表，常见的就是InnoDB、Myisam。![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 11]

### Myisam ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 12]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 13]

### InnoDB ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 14]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 15]

### InnoDB VS Myisam ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 16]

## 索引的几大原则 ##

### 列的离散性 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 17]

### 最左匹配原则 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 18]

### 联合索引 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 19]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 20]

### 覆盖索引 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 21]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 22]

# 二、MySQL 优化 #

链接: https://pan.baidu.com/s/1kWW5Jymaob1ypRsoP2xfNg 提取码: 4wg6

>  *  [MySQL 千万级大表优化][MySQL]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70]: /images/20220223/96a39e08b1e0410990581216f5cda608.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 1]: /images/20220223/6da315619adf4334b4c5981e3ea64578.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 2]: /images/20220223/9c8b98092e034bf39c10ed08b2c83e08.png
[Link 1]: https://baike.baidu.com/item/%E4%BA%8C%E5%8F%89%E6%8E%92%E5%BA%8F%E6%A0%91/10905079?fr=aladdin
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 3]: /images/20220223/3388223d323244768c689fd71f2de340.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 4]: /images/20220223/560f3ff76eb14f078eeace81b30422ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 5]: /images/20220223/f1fd20eda8464302a4dc41da8fc648b6.png
[Link 2]: https://baike.baidu.com/item/%E5%B9%B3%E8%A1%A1%E4%BA%8C%E5%8F%89%E6%9F%A5%E6%89%BE%E6%A0%91
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 6]: /images/20220223/2c43e0d529e94158b3f2437c90de5e92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 7]: /images/20220223/f3f1ef78a995479a86d03cb7b56094c4.png
[20190408152008477.gif]: /images/20220223/9fc17d1ebe68489d8b63f8c2fa6fb9ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 8]: /images/20220223/a8cf3183e94548c4bb3dab323efbf4f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 9]: /images/20220223/40d7377c1ef14639b0be818d120f41a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 10]: /images/20220223/bcfbbda184f94123903be9446b424f9b.png
[AVLTree_B-Tree_B]: https://blog.csdn.net/qq_21993785/article/details/80576642
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 11]: /images/20220223/72aadbdf7a5a4466bcc5a4e33c066e9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 12]: /images/20220223/0d895657fa68452db0ab7e2f0e3b4233.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 13]: /images/20220223/46ad75275d864ef3b44c4fa9d15ee4f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 14]: /images/20220223/a6c2d9991a394b1fb1c9fc876f628abb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 15]: /images/20220223/51b07c4596db4d589ea9d5ee0fd142d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 16]: /images/20220223/2a009f462b2c41b4a0098fa78d487ebd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 17]: /images/20220223/742fa554dc834637aebd05dc8a5a1b4f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 18]: /images/20220223/17e8942d932f4bea9921dd7f2e77d0c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 19]: /images/20220223/0478281f82c946aab94397a3ea39d654.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 20]: /images/20220223/0f5f578ae1cc490a94c393574ee9eafc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 21]: /images/20220223/7d9a6da28054461891d57c1445cfc094.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0ODQ1Mzk0_size_16_color_FFFFFF_t_70 22]: /images/20220223/2c2e15cce49d48e49ea764c34cd35ae3.png
[MySQL]: https://www.cnblogs.com/LUO77/p/8574354.html