性能调优学习日记

最新推荐文章于 2024-04-19 19:23:54 发布

咘咘咘咘咘咘

最新推荐文章于 2024-04-19 19:23:54 发布

阅读量177

点赞数

文章标签： Mysql索引

本文链接：https://blog.csdn.net/weixin_43895644/article/details/101606821

版权

Mysql调优学习笔记-1

#大家好，这是我个人的JAVA学习，以及相关知识的一个日记，其中会分享一些我自己的学习心得，以及算是对自己的一个监督，若有不足请大家指正，我们共同学习，共同进步！

首先对于Mysql的调优学习，我主要对sql语句以及对表索引的做一个优化，这篇内容这是关于索引数据结构的本人的小看法。

索引是什么

索引是帮助MySql高效获取数据的排好序的数据结构。
假设一下，如果一个表有一个类似这样子的表

col1	col2
000 1	34
0002	77
0003	5
0004	83
0005	3
0006	22
0007	59

如果我们执行这个sql语句

select * from t where col2 = '22';

如果没有索引，mysql将会全表扫描，一行行查找，从上往下要查找6次，每次查找就是磁盘作一次IO操作，多次的磁盘io操作这样就将会大大的耗费性能。有没有方法尽量减少磁盘io操作呢，如果达到这个效果就是添加一个合理的索引。

索引的数据结构

在添加索引之前，上面说到索引是一个数据结构，以下这边我就先罗列一些数据结构：

二叉树
红黑树
B-Tree

B+Tree ( B-Tree 的变种)

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

首先对于学习数据结构，我这里分享大家一个数据结构学习以及解析其原理的一个网站。操作很简便，先点击想要了解的数据，再通过一个个insert 以及 delete元素就可以观察结构的变化可以很好辅助我们了解上面的这些结构。

一、二叉树，
我们把上面表格col2 列的数据放进 ,二叉树结构就是这个样子：
在这里插入图片描述

二叉树的数据插入顺序是从根节点开始，比根节点小的数据插到左边，比根节点大的数据插到右边，按照这种顺序排列后，这个二叉树的高度（深度）就是3，我们要查询的22 这个数据就在第三层，那么我们的索引如果是这个二叉树的数据结构，那只需要三次io的磁盘操作就能得到这个数据。
但是二叉树有个问题，如果我们把索引建在col1上面，然后查询sql语句改为

 select * from t where col1 = '0006';

那么此时二叉树的结构图变成这个亚子：
在这里插入图片描述
此时二叉树为了满足右边的元素必须大于父节点的特性，看起来像是一种单边的链表结构，如果查询0006这个数据，从根节点开始，还是要进行6次的磁盘IO操作，相当于还是全表扫描，所以二叉树面对着自增列的数据情况下，这种索引并没有效用的。

二、红黑树

同样的我们把表格col1的数据放进红黑树里面去，此时的数据结构是这样：

在这里插入图片描述
红黑树又名为二叉平衡树，也是一种二叉树，但是当红黑树有一个特性，当一个节点下的两个叶节点的高度不相等并且大于2倍的时候，二叉树就会自动平衡，平衡操作有变色或者自旋。当我们查找0006这个数据时，所在的树的第三层，磁盘也是进行了3次IO操作，所以红黑树性能在这种自增列的数据是比二叉树要高。

但是问题来了如果我这个表是有非常大量的数据，假设我们的表有100万条的数据，此时红黑树的高度为n，那么2^n = 1000000, 当我们恰好查询后面第一百万条数据的时候，此时磁盘需要进行20次IO操作，我们也是无法接受的，所以红黑树作为索引的数据结构在面对大数据量的表查询没法达到我们要求的性能。

三、B-Trees

当我们再把表col1的数据放进B-Trees,此时的数据结构

在这里插入图片描述
先说一下B-Tree的一些特性

叶节点具有相同的深度，叶节点指针为空
所有索引元素不重复
节点中的数据索引是从左往右递增排列

可以看出B-Tree的根节点处会有多个索引，多个索引下又可以查询多个数据，如果此时查询0006，那么此时磁盘IO操作只需要两次即可查询。

不过我们先分析一下B-Tree数据结构的索引是这样的
在这里插入图片描述
每一个节点都是一种key-value数据存储，Mysql的每个表的存储引擎都可以是不一样的，我们常用的存储引擎myisam以及innodb，其中myisam里面索引的节点key-value存储是索引元素以及一个指向磁盘物理地址的一个信息，innodb里面索引的节点key-value存储的是索引元素以及索引所在行的所有列的数据。

那么问题来，当如果这个表的字段有好几十个，因为我们实际生产环境中，一个表几十个字段很正常，如果这个表的存储引擎是innodb，那么在这个B-Tree数据结构中节点里的data将会相当的大。

Mysql对索引的节点的大小一般是设置在16kb，如果在节点大小固定的情况下，节点下的data数据很大，那将会导致一个节点里的索引变少，那么这会导致B-Tree的高度会变高，虽然B-Tree性能比红黑树要高，但是在更大数据量的情况下，B-Tree高度仍是不可控，所以B-Tree的数据结构的索引还是无法满足我们超大数据量下的性能查询。

四、B+Tree

我们再把表col1数据放进B+Tree的结构

在这里插入图片描述
Mysql索引底层用的正是B+Tree的数据结构，先列一下B+Tree的特性：

非叶子节点不存储data,只存储索引（冗余），那非叶子节点就能存储更多索引
叶子节点包含所有索引字段
叶子节点用指针连接，提高区间访问的性能

通过B+Tree的特性可以看出，非叶子节点能存储相当多的索引，例如还是0006的数据,执行第一次磁盘IO操作，把根节点索引加载内存读取，0006位于0005与0007之间，然后执行第二次磁盘IO操作，根据0005-0007之间的指针就获取到0006的索引，再执行第三次磁盘IO操作把0006的data拿到。这样的一个B+Tree结构在节点大小相同的情况下通过比 B-Tree能存更多的索引，更多的索引能存到更多的数据。就算用在千万级别数据的表中也能快速查找。以下是举例估算：

假设有一颗高度为3的B+Tree，一个索引大小值是 8byte ，一个指针占 6byte，那么一整个节点是16kb ，那么第一层根节点能存储 16x1024/(8+6) 约等于 1170个索引元素，那么第二层就会有1170x1170 个索引元素。到了第三层叶子节点，假设每个叶子节点的里面每一个data元素占用1kb，那第三层每一个叶子节点能存16个data，所以一颗高度为3的B+Tree 可以轻松存取1170x1170x16 = 超过2000万行，每行1kb的数据。而且只需要3次IO磁盘操作就能提取其中任何一行的数据。

所以Mysql索引底层就会采用B+Tree的数据结构，一旦设置好，该索引能支撑千万级别表的快速查询。