个人觉得mysql8这个hash join也只能算是一个锦上添花的功能，顶多是代替了没有加索引时默认走的BNLJ算法，提高了join的性能下限。说白了就是给不懂加索引的mysql新用户提高下join性能。其实也不绝对，不过我有做 INLJ和Hash Join 对比实验，Hash Join 很有可能比需要在内部表建立索引的INLJ算法性能要好！毕竟当INLJ需要回表查的时候性能会大幅度下降，这时候Hash Join绝对值得一试的，当然具体两者之间的选择还请自己实际测试下。
创建user和book表
可以看看下列语句的执行计划，Extra 出现了 Using join buffer (hash join) 说明该语句使用到了hash join。这里还使用了 IGNORE index(index_user_id)禁用索引，不然使用的是INLJ。
那么，使用Hash Join会分为下面2个阶段：
1、build 构建阶段：从参与join的2个表中选一个，选择占空间小的那个表，不是行数少的，这里假设选择了 user 表。对 user表中每行的 join 字段值进行 hash(a.id ) 计算后放入内存中 hash table 的相应位置。所有行都存放到 hash table 之后，构建阶段完成。
溢出到磁盘在构建阶段过程中，如果内存满了，会把表中剩余数据写到磁盘上。不会只写入一个文件，会分成多个块文件。
2、probe 探测阶段：对 book 表中每行中的 join 字段的值进行 hash 计算：hash(b.user_id) 拿着计算结果到内存 hash table 中进行查找匹配，找到一行就发给 client。这样就完成了整个 join *** 作，每个表只扫描一次就可以了，扫描匹配时间也是恒定的，非常高效。
散列连接的内存使用可以使用join_buffer_size系统变量来控制；散列连接使用的内存不能超过这个数量。当散列连接所需的内存超过可用的数量时，MySQL通过使用磁盘上的文件来处理这个问题(溢出到磁盘)。
如果发生这种情况，您应该知道，如果散列连接无法容纳在内存中，并且它创建的文件超过了为open_files_limit设置的数量，则连接可能不会成功。
为避免此类问题，请执行以下任一更改:
1、增加join_buffer_size，以便哈希连接不会溢出到磁盘。
在MySQL 8.0.19及更高版本中， 设置 optimizer_switch 变量值 hash_join=on or hash_join=off 的方式已经失效了
2、增加open_files_limit。若数据量实在太大内存无法申请更大的join_buffer，就只能溢出到磁盘上了。我们可以增加open_files_limit，防止创建的文件超过了为open_files_limit设置的数量而join失败。
必须使用format=tree（8.0.16的新特性）才能查看hash join的执行计划：
创建几张测试表
从MySQL 8.0.18开始，MySQL对每个连接都有一个等连接条件的任何查询都使用散列连接，并且没有可应用于任何连接条件的索引，例如:
在MySQL 8.0.20之前，如果任何一对连接的表没有至少一个等连接条件，就不能使用Hash Join，并且使用了较慢的BNLJ。而 在MySQL 8.0.20和更高版本中，hash join可以用于未包含等值连接条件的查询
甚至是笛卡尔积的join
Semijoin也行
还有 antijoin
首先说说索引的 优点 ：最大的好处无疑就是提高查询效率。有的索引还能保证数据的唯一性，比如唯一索引。
而它的 坏处 也很明显：索引也是文件，我们在创建索引时，也会创建额外的文件，所以会占用一些硬盘空间。其次，索引也需要维护，我们在增加删除数据的时候，索引也需要去变化维护。当一个表的索引多了以后，资源消耗是很大的，所以必须结合实际业务再去确定给哪些列加索引。
再说说索引的基本结构。一说到这里肯定会脱口而出：B+树！了解B+树前先要了解二叉查找树和二叉平衡树。 二叉查找树 ：左节点比父节点小，右节点比父节点大，所以二叉查找树的中序遍历就是树的各个节点从小到大的排序。 二叉平衡树 ：左右子树高度差不能大于1。B+树就是结合了它们的特点，当然，不一定是二叉树。
为什么要有二叉查找树的特点？？ 因为查找效率快，二分查找在这种结构下，查找效率是很快的。 那为什么要有平衡树的特点呢？ 试想，如果不维护一颗树的平衡性，当插入一些数据后，树的形态有可能变得很极端，比如左子树一个数据没有，而全在右子树上，这种情况下，二分查找和遍历有什么区别呢？而就是因为这些特点需要去维护，所以就有了上面提到的缺点，当索引很多后，反而增加了系统的负担。
接着说B+树。 它的结构如下 ：
可以发现，叶子节点其实是一个 双向循环链表 ，这种结构的好处就是，在范围查询的时候，我只用找到一个数据，就可以直接返回剩余的数据了。比如找小于30的，只用找到30，其余的直接通过叶子节点间的指针就可以找到。再说说其他特点： 数据只存在于叶子节点 。当叶子节点满了，如果再添加数据，就会拆分叶子节点，父节点就多了个子节点。如果父节点的位置也满了，就会扩充高度，就是拆分父节点，如25 50 75拆分成：25为左子树，75为右子树，50变成新的头节点，此时B+树的高度变成了3。它们的扩充的规律如下表，Leaf Page是叶子节点，index Page是非叶子节点。
再说说B树 ，B树相比较B+树，它所有节点都存放数据，所以在查找数据时，B树有可能没到达叶子节点就结束了。再者，B树的叶子节点间不存在指针。
最后说说Hash索引 ，相较于B+树，Hash索引最大的优点就是查找数据快。但是Hash索引最大的问题就是不支持范围查询。试想，如果查询小于30的数据，hash函数是根据数据的值找到其对应的位置，谁又知道小于30的有哪几个数据。而B+树正好相反，范围查询是它的强项。
附录： Hash到底是啥？？ 哈希中文名散列，哈希只是它的音译。 为啥都说Hash快？？ 首先有一块哈希表（散列表），它的数据结构是个数组，一个任意长度的数据通过hash函数都可以变成一个固定长度的数据，叫hash值。然后通过hash值确定在数组中的位置，相同数据的hash值是相同的，所以我们存储一个数据以后，只需O(1)的时间复杂度就可以找到数据。 那hash函数又是啥？？ 算术运算或位运算，很多应用里都有hash函数，但实际运算过程大不一样。这是Java里String的hashCode方法：
publicint hashCode() {
}
还有一个问题，hash函数计算出来的hash值有可能存在碰撞，即两个不同的数据可能存在相同的hash值，在MySQL或其他的应用中，如Java的HashMap等，如果存在碰撞就会以当前数组位置为头节点，转变成一个链表。
说到这里也清楚了为啥Java中引用类型要同时重写hashCode和equals了。两个对象，实例就算一模一样，它们的hash值也不相等， 为啥不相等？？ 默认的Object的hashCode方法会根据对象来计算hash值的，实例相同，但它们还是两个不同的对象啊，所以我们重写hashCode时，最简单的方法就是调用Object的hashCode方法，然后传入该引用类型的属性，让hashCode方法只根据这几个属性来计算，那么实例相同的话，它们的hash值也会相等。等hashCode比较完后，如果相等再比较实例内容，也就是equals，确保不是hash碰撞。
索引的分类
如果我们指定了一个主键，那么这个主键就是主键索引。如果我们没有指定，Mysql就会自动找一个非空的唯一索引当主键。如果没有这种字段，Mysql就会创建一个大小为6字节的自增主键。如果有多个非空的唯一索引，那么就让第一个定义为唯一索引的字段当主键，注意，是第一个定义，而不是建表时出现在前面的。
对于辅助索引来说，它们的B+树结构稍微有点特殊，它们的叶子节点存储的是主键，而不是整个数据。所以在大部分情况下，使用辅助索引查找数据，需要二次查找。但并不是所有情况都需要二次查找。比如查找的数据正好就是当前索引字段的值，那么直接返回就行。这里提一句，B+树的key就是对应索引字段的内容。
而辅助索引又有一些分类：唯一索引：不能出现重复的值，也算一种约束。普通索引：可以重复、可以为空，一般就是查询时用到。前缀索引：只适用于字符串类型数据，对字符串前几个字符创建索引。全文索引：作用是检测大文本数据中某个关键字，这也是搜索引擎的一种技术。
注意，聚集索引、非聚集索引和前面几个索引的分类并不是一个层面上的。上面的几个分类是从索引的作用来分析的。聚集、非聚集索引是从索引文件上区分的。主键索引就属于聚集索引，即索引和数据存放在一起，叶子节点存放的就是数据。数据表的.idb文件就是存放该表的索引和数据。
辅助索引属于非聚集索引，说到这也就明白了。索引和数据不存放在一起的就是非聚集索引。在MYISAM引擎中，数据表的.MYI文件包含了表的索引， 该表的 叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。
索引的几点使用经验
经常被查询的字段；经常作为条件查询的字段；经常用于外键连接或普通的连表查询时进行相等比较字段；不为null的字段；如果是多条件查询，最好创建联合索引，因为联合索引只有一个索引文件。
经常被更新的字段、不经常被查询的字段、存在相同功能的字段
*nix系系统：ES(Unix)
例子: IvS7aeT4NzQPM
说明：Linux或者其他linux内核系统中
长度: 13 个字符
描述：第1、2位为salt，例子中的'Iv'位salt，后面的为hash值
系统：MD5(Unix)
例子：$1$12345678$XM4P3PrKBgKNnTaqG9P0T/
说明：Linux或者其他linux内核系统中
长度：34个字符
描述：开始的$1$位为加密标志，后面8位12345678为加密使用的salt,后面的为hash
加密算法：2000次循环调用MD5加密
系统：SHA-512(Unix)
例子：$6$12345678$U6Yv5E1lWn6mEESzKen42o6rbEm
说明：Linux或者其他linux内核系统中
长度: 13 个字符
描述：开始的$6$位为加密标志，后面8位为salt，后面的为hash
加密算法：5000次的SHA-512加密
系统：SHA-256(Unix)
例子：$5$12345678$jBWLgeYZbSvREnuBr5s3gp13vqi
说明：Linux或者其他linux内核系统中
长度: 55 个字符
描述：开始的$5$位为加密标志，后面8位为salt，后面的为hash
加密算法：5000次的SHA-256加密
系统：MD5(APR)
例子：$apr1$12345678$auQSX8Mvzt.tdBi4y6Xgj.
说明：Linux或者其他linux内核系统中
长度：37个字符
描述：开始的$apr1$位为加密标志，后面8位为salt，后面的为hash
加密算法：2000次循环调用MD5加密
windows系统：
windows
例子：Admin:b474d48cdfc4974d86ef4d24904cdd91
长度：98个字符
加密算法：MD4(MD4(Unicode($pass)).Unicode(strtolower($username)))
mysql
系统：mysql
例子：606717496665bcba
说明：老版本的MySql中
长度：8字节（16个字符）
说明：包括两个字节，且每个字的值不超过0x7fffffff
系统：MySQL5
例子：*E6CC90B878B948C35E92B003C792C46C58C4AF40
说明：较新版本的MySQL
长度：20字节（40位）
加密算法：SHA-1(SHA-1($pass))
其他系统：
系统：MD5(WordPress)
例子：$P$B123456780BhGFYSlUqGyE6ErKErL01
说明：WordPress使用的md5
长度：34个字符
描述：$P$表示加密类型，然后跟着一位字符，经常是字符‘B’，后面是8位salt，后面是就是hash
加密算法：8192次md5循环加密
系统：MD5(phpBB3)
说明：phpBB 3.x.x.使用
例子：$H$9123456785DAERgALpsri.D9z3ht120
长度：34个字符
描述：开始的$H$为加密标志，后面跟着一个字符，一般的都是字符‘9’，然后是8位salt，然后是hash 值
加密算法：2048次循环调用MD5加密
系统：RAdmin v2.x
说明：Remote Administrator v2.x版本中
例子：5e32cceaafed5cc80866737dfb212d7f
长度：16字节（32个字符）
加密算法：字符用0填充到100字节后，将填充过后的字符经过md5加密得到（32位值）
md5加密
标准MD5
例子：c4ca4238a0b923820dcc509a6f75849b
使用范围：phpBB v2.x, Joomla 的 1.0.13版本前，及其他cmd
长度：16个字符
其他的加salt及变形类似：
md5($salt.$pass)
例子:f190ce9ac8445d249747cab7be43f7d5:12
md5(md5($pass))
例子:28c8edde3d61a0411511d3b1866f0636
md5(md5($pass).$salt)
例子:6011527690eddca23580955c216b1fd2:wQ6
md5(md5($salt).md5($pass))
例子: 81f87275dd805aa018df8befe09fe9f8:wH6_S
md5(md5($salt).$pass)
例子: 816a14db44578f516cbaef25bd8d8296:1234
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）