mysql 秀出两个相关联的表中满足条件的内容_深入挖掘MySQL底层数据结构

最新推荐文章于 2022-11-18 15:35:40 发布

weixin_39900468

最新推荐文章于 2022-11-18 15:35:40 发布

阅读量267

点赞数

文章标签： mysql 秀出两个相关联的表中满足条件的内容

本文链接：https://blog.csdn.net/weixin_39900468/article/details/111694104

版权

“MySQL是全世界最流行的开源数据库，在业务中避免不了跟MySQL打交道，如果我们不了解它的底层原理，优化SQL就会变成一种假想猜测，MySQL常用的数据存储引擎分为InnoDB和MyISAM两种，这两种存储引擎的BTree索引的数据结构都是围绕BTree实现的，优化SQL的前提是参透BTree索引数据结构。”

—

什么是索引

索引是对数据库表中一个或多个列(例如，employee 表的姓名 (name) 列)的值进行排序的结构。

例如这样一个查询：select * from table employee where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。

从数据搜索实现的角度来看，索引也是另外一类文件/记录，它包含着可以指示出相关数据记录的各种记录。其中，每一索引都有一个相对应的搜索码，字符段的任意一个子集都能够形成一个搜索码。这样，索引就相当于所有数据目录项的一个集合，它能为既定的搜索码值的所有数据目录项提供定位所需的各种有效支持。

—

MySQL索引类型

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

优势：

检索效率非常高，索引的检索可以一次定位，检索效率非常高，因为Hash索引存储的数据的物理地址。

弊端：

Hash索引适用于"="，"IN"，"<=>"条件查询，无法实现范围查询。由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用；
Hash 索引在任何时候都不能避免全表扫描。前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键可能存在相同 Hash 值(Hash碰撞)，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果；
Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下；
Hash 索引无法被用来避免数据的排序操作。由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

BTree索引

基于B+Tree实现，非叶子节点存储索引值，叶子节点存储数据。

优势：

BTree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。这个数据结构一般用于数据库的索引，综合效率较高。

弊端：

单列索引的列不能包含null的记录，复合索引的各个列不能包含同时为null的记录，否则会全表扫描；将索引列值进行建树，其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null；这样的话，null值实际上是不能参与创建索引的过程。也就是说，null值不会像其他取值一样出现在索引树的叶子节点上；
不适合键值较少的列(重复数据较多的列)；假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。再加上访问索引块，一共要访问大于200个的数据块。如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块少一些，肯定就不会利用索引了；
前导模糊查询不能利用索引(like '%XX'或者like '%XX%')；假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件，由于前面是模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like 'A % '，就可以查找code中A开头的code的位置，当碰到B开头的数据时，就可以停止查找了，因为后面的数据一定不满足要求。这样就可以利用索引了。

—

MySQL的索引为什么使用B+Tree存储

我们引用Java8HashMap的数据结构作为分析的起始点，HashMap的KV以链表的形式存储，当链表的长度大于8时(8来源于泊松分布的理论)，则会将链表转换为红黑树，红黑树具有自动平衡的特性，相对来讲，在查询效率上占据效率优势，

场景：employee 业务表

我们在level字段上增加索引，加入表中数据如下:

id	level
id001	1
id002	2
id003	3
id004	4
id005	5
id006	6

查询需求：查询level字段等于6的数据

使用二叉树作为索引存储

定义：二叉树(英语：Binary tree)是每个节点最多只有两个分支(即不存在分支度大于2的节点)的树结构。通常分支被称作“左子树”或“右子树”。二叉树的分支具有左右次序，不能随意颠倒。如果使用二叉树作为索引存储，约定左子树小于中间节点，右子树大于中间节点。

使用二叉树我们查询level等于6的数据，需要6次IO操作才可以将结果查询出来，可以看到二叉树存储索引具有单边增长的态势，如果数据量过百万级别，二叉树的高度存在不确定性，并且这个数值并不会很小。

使用红黑树作为索引存储

定义：红黑树衍生于二叉树，具备自动平衡的能力，平衡指所有叶子的深度趋于平衡，更广义的是指在树上所有可能查找的均摊复杂度偏低。

使用红黑树数据结构的索引查询level等于6的数据，需要4次IO操作才可以将结果查询出来，相比于二叉树来讲，在IO次数上提升了，但是红黑树的高度依然过高，数据量过百万级别，高度存在不确定性，并且这个数值并不会很小。

使用B+Tree作为索引存储

定义：B+Tree是B树的变种，有着比B树更高的查询性能，来看下m阶B+Tree特征：

有m个子树的节点包含有m个元素(B-Tree中是m-1)根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中。
所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或者最小的元素。
叶子节点会包含所有的关键字，以及指向数据记录的指针，并且叶子节点本身是根据关键字的大小从小到大顺序链接。

使用B+Tree我们只需要查询3次即可查询到level等于6的数据，相比于红黑树来讲，B+Tree更具有效率优势，MySQL的度一般不超过100，从度的角度去控制，则我们的树的高度就会降低。

—

InnoDB存储引擎

InnoDB属于聚集索引，因为索引(主键索引)和数据是存储在一个文件中的；在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，树的叶子节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引；InnoDB要求表必须存在主键。

主键索引数据结构图

上图是InnoDB主索引(同时也是数据文件)的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

其他索引数据结构图

上图中叶子节点存储的是主键的内容。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

—

MyISAM存储引擎

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。(MyISAM存储引擎的数据和索引是分开存储的)

主键索引数据结构图

其他索引数据结构图

同样是B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM存储引擎的主键索引和其他索引的数据结构是一致的，data区域存储的都是数据地址，所以MyISAM存储引擎不要求表格具有主键。

—

MyISAM和InnoDB的区别

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快； 5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。

如何选择：