mysql8新特性

->PERCENT_RANK() OVER w AS pr,

->id, category_id, category, NAME, price, stock

->FROM goods

->WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC)

| r | pr | id | category_id | category | NAME | price | stock |

| 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |

| 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |

| 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |

| 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |

| 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |

| 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |

6 rows in set (0.00 sec)

2．CUME_DIST()函数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

举例：查询goods数据表中小于或等于当前价格的比例。

mysql>SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,

->id, category, NAME, price

->FROM goods

| cd | id | category | NAME | price |

| 0.5 | 2 | 女装/女士精品 | 连衣裙 | 79.90 |

| 0.8333333333333334 | 3 | 女装/女士精品 | 卫衣 | 89.90 |

| 0.8333333333333334 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | | 1 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | | 0.16666666666666666 | 9 | 户外运动 | 登山杖 | 59.90 |

| 0.5 | 7 | 户外运动 | 自行车 | 399.90 |

| 0.5 | 10 | 户外运动 | 骑行装备 | 399.90 |

| 0.6666666666666666 | 12 | 户外运动 | 滑板 | 499.90 |

| 0.8333333333333334 | 11 | 户外运动 | 运动外套 | 799.90 |

| 1 | 8 | 户外运动 | 山地自行车 | 1399.90 |

总之，递归公用表表达式对于查询一个有共同的根节点的树形结构数据，非常有用。它可以不受层级的

限制，轻松查出所有节点的数据。如果用其他的查询方式，就比较复杂了。

3.3 小 结

公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根

节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。

MySQL从5.7一跃直接到8.0，这其中的缘由，咱就不关心那么多了，有兴趣的朋友自行百度，本次的版本更新，在功能上主要有以下6点：

账户与安全

优化器索引

通用表表达式

窗口函数

InnoDB 增强

JSON 增强

一、账户与安全

二、优化器索引

三、通用表表达式

四、窗口函数

五、InnoDB增强

六、JSON增强

mysql8以前 的 join 算法只有 nested loop 这一种，在 MySQL8 中推出了一种新的算法 hash join，比 nested loop 更加高效。mysql8中的部分NLJ算法已经取消，hash join 是它的的替代方案。像属于NLJ的BNLJ、SNLJ都会被Hash join替代！不过基于索引的INLJ算法还是存在的，所以实际使用中可以对比下INLJ和Hash Join的查询性能然后做出选择。

个人觉得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

---