MySQL为了提高查询效率，设计思路是怎样的

最新推荐文章于 2024-09-26 14:19:34 发布

让你三行代码QAQ

最新推荐文章于 2024-09-26 14:19:34 发布

阅读量865

点赞数 13

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_43676797/article/details/141176410

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

MySQL为了提高查询效率，设计思路是怎样的

1. IO

MySQL真实行记录和索引数据都是存储在硬盘中的
在查询数据的时候会将磁盘中的数据加载到内存中
这样必然会有IO问题，所以要尽量减少IO次数，减少IO量（能一次将查询的数据从磁盘取出，绝对不取第二次）

2.分而治之的思想

当数量量够大的时候，不能够一次将磁盘的数据加载到内存中，因此采用分而治之的思想
分块读取磁盘的数据，那么如何确定’块’的大小呢？这里涉及操作系统中的一个概念叫做磁盘预读

磁盘预读

内存跟磁盘进行交互时候，有一个最基本的逻辑单位，称之为页，也叫做datapage,一般页大小是4KB的整数倍，在MySQL的InnoDB存储引擎下，默认一次读取16KB数据。

查看MySQL每次读取数据的大小

通过show variables like 'innodb_page_size’可以查看每次读取数据页的大小，这个参数一般不会调整。

3.存储的数据格式

例如：select * from table where id = 1;

根据ID查询整行数据内容。
将id作为Key,将整行数据（或要查询的列信息）作为Value，采用K-V的存储格式

为了满足查询的需要，应该如何设计索引呢？

MySQL持久化数据是通过磁盘上的文件实现的，当磁盘文件太多时，如何定位数据是一个棘手的问题。

第一种： key存储索引列的值，value存储文件名+offset(偏移量)+length（文件长度）。这种设计在查询数据的时候，首先通过key(索引列的值)找到文件名，打开文件，再根据偏移量和长度定位要查询的数据。在这个过程中读取索引文件+读取数据文件要进行两次IO操作。
第二种： key存储索引列的值，value存储行记录，这种方式只需要读取一次IO就可以定位数据。但是它也有它的弊端，如果行列数量过大，会导致建立索引占用空间大。
首先MySQL的InnoDB存储引擎采用第二种方式存储，只有在聚簇索引中存储整行记录，非聚簇索引存储索引列+聚簇索引。也正是因为这种存储方式，导致在查询的时候会出现回表操作。
而MySQL的MyISAM存储引擎中采用第一种存储方式。
从文件结构就可以发现两种存储引擎的实现方式不同：
在MyISAM存储引擎下：.frm文件存储表结构，.MYD文件存储数据，.MYI文件存储索引，其中索引和数据是分开存储的。
在InnoDB存储引擎下：.frm文件存储表结构，.ibd文件存储索引+数据，其中索引和数据是存在一个文件中的。

小结：索引和数据放到一起可以减少IO操作，这也是MySQL普遍使用InnoDB存储引擎的原因。

4.存储的数据结构

存储K-V格式，可以采用Hash表、二叉树、BST、AVL树、红黑树等等，那么为什么MySQL最终采用B+树存储呢？

分析一下：

hash表：无序散列表，它进行精确查找时候，效率较高，但是在进行范围查找的时候，需要挨个匹配，效率及低。另外为了防止哈希碰撞，需要设计优良的算法，但无法避免碰撞。MySQL中的Memory存储引擎只支持Hash索引，InnoDB支持自适应hash。
树：①二叉树、②BST树、③AVL树、④红黑树，其中①②③④都满足至多只有两个分支，②③④满足有序，③④满足平衡。至多有两个分支就决定了它们不能作为MySQL的数据结构，因为树越高IO次数越多。
B树：为了解决树高度的问题，可以采用多叉树，树的分叉多了，树的高度自然就低了。引入了B树，每个数据块存储了多个键值对和多个键值对之间指向下一个块儿的指针。
B+树：非叶子节点只存储索引key，叶子节点存储所有的数据，并且叶子节点每个节点存储指向下一个节点的指针。

B树和B+树都是三层存满数据的情况下，假设每块16KB，每条数据1KB，存储数据条数对比：

B树：因为每个节点还要存储指向下个节点的指针，因此一个节点至多存储15条数据。15 + 1615 + 1615*15 = 4095
B+树：假设非也只节点，每个节点10KB数据。第一、二层每个节点有161024/10 = 1638个子节点，第三次有 16381638 = 2683044个子节点，假设每个节点存储16条数据，共计2683044*16 = 42928704条数据。
对比可以发现，同样层数的树，B+ 树可以存储的数据是B树的10000倍，不是一个量级的。
另外B+树可以存储多少数据跟索引列的大小相关，索引列越大，非叶子节点越少，叶子节点也就更少，存储的数据就越少。