都是一些MySQL的基础知识

分组排序解决方案
MySQL 函数、存储过程
SQL执行顺序（牢记）
MySQL 8.0 窗口函数

以下所有SQL语句在 (Mysql5.8 , Navicat 12) 测试成功执行

需求

Retrieving the last record in each group，类似Oracle 中 Top 函数。

分组Top N问题。在商品中取相同分类下最新创建的一个。

先来建表：

CREATE TABLE `goods`(
	`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	`category_id` bigint(20) UNSIGNED NOT NULL COMMENT '分类ID',
	`unit_price` decimal(11,4) UNSIGNED NOT NULL COMMENT '商品单价/元',
	`create_time` datetime(0) NOT NULL COMMENT '创建时间',
	`update_time` datetime(0) NOT NULL COMMENT '更新时间',
	PRIMARY KEY(`id`),
	KEY `idx_category_id_create_time` (`category_id`,`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

分组排序

Step 1.分析

相同分类：可以用到group by
最新一行：可以考虑取id或者create_time较大值，也可以尝试使用order by

Step 2.编写

尝试写了以下几条SQL：

❌ SELECT * 
   FROM goods 
   GROUP BY category_id 
   ORDER BY create_time DESC

然而MYSQL的执行顺序并没有那么理想化。并不会对组内排序，而是先分组，后排序。并且这条SQL在5.7下也无法执行。

针对最大值考虑使用聚合函数：

1
2
3

❌ SELECT *,category_id,max(create_time) 
   FROM goods 
   GROUP BY category_id

看上去没什么大问题，但它无法执行，因为（SELECT *）违反了MySQL only_full_group_by的规则。这里先不做解释，我们来试着改造下这个sql。

上一条sql中我们已经有了最大时间和分类id，我们可以考虑使用联表查询：

✅ SELECT a.unit_price, a.category_id, a.create_time 
   FROM goods a INNER JOIN (
     SELECT category_id,max(create_time) AS max_time 
     FROM goods 
     GROUP BY category_id
   ) AS b 
   ON a.category_id = b.category_id 
   AND a.create_time = b.max_time

执行成功，是我们想要的结果。

INNER JOIN 可以考虑换成LEFT JOIN 加上 IS NULL，作为参照对比性能：

✅ SELECT a.unit_price, a.category_id, a.create_time 
    FROM goods a LEFT JOIN (
     SELECT category_id,max(create_time) AS max_time 
     FROM goods GROUP BY category_id
    ) AS b 
   ON a.category_id = b.category_id 
   AND a.create_time = b.max_time
   WHERE b.category_id IS NOT NULL

也可以写在WHERE后的子查询这里取巧的按ID大小来排序：

✅ SELECT unit_price,category_id,create_time 
   FROM goods WHERE id IN (
    SELECT MAX(id) 
    FROM goods 
    GROUP BY category_id
   )

在网上看到这种方法，MYSQL5.7之前默认取第一条，可以利用这个特性来做。
5.7之后，会对组内排序优化，从而忽视组内排序。加上limit max_int 就可以无视：

❓ SELECT * FROM (
    SELECT * FROM test 
    ORDER BY seq DESC limit 10000000000
    ) tmp 
   GROUP BY NAME

个人并不是很喜欢这种解法。首先逻辑上并不成立，排序完再分组取的不一定是第一条，同样在5.7下依然因为only_full_group_by无法运行。

至此，根据所学写了三条可用的SQL。但还不够，我们接下来造数据调试一下。

Step 3.优化

mysql 创建存储过程：

-- 以下是在 Mac/Navicat/12.0下执行的 可以不加DEFINER 命令行执行需要加上DELIMITER $$ 声明开始结束
CREATE DEFINER=`root`@`localhost` PROCEDURE `RandomInsert`(IN time_len INT,IN category_len INT,IN len INT) -- 三个参数分别是时间戳增加值，分类数，插入条数
BEGIN
  DECLARE i INT;
	DECLARE ts INT;
	SET i = 0; 
	START TRANSACTION;
	WHILE i <= len DO
	SET ts = UNIX_TIMESTAMP( NOW()) + CEIL(RAND() * i); -- 随机时间戳
	INSERT goods(category_id, unit_price, create_time, update_time) VALUES(FLOOR(
	RAND() * category_len), FLOOR( RAND() * 1000) , FROM_UNIXTIME(ts),FROM_UNIXTIME(ts) );
	SET i = i+1;
END WHILE;
COMMIT;
END

-- 新建另一个函数去调用我们刚刚创建的方法
CALL RandomInsert(1000,1000,10000000) -- 这里我插入了一千万条，只分了1000个类（本机最终耗时3326秒。i7-4700MQ)

以下是平均查询耗时

INNER JOIN : 0.014s
LEFT JOIN : 0.028s
WHERE IN : 6.5s

利用EXPLAIN && SHOW WARNINGS可以看到WHERE IN 的查询方式多了一次全表扫描

结论：相同需求时LEFT JOIN需要多出 n*n/2 + n/2 次的 IS NULL 操作，故最好选择INNER JOIN

SQL执行顺序

基础知识，不过多赘述。（图片转自CSND）

MySQL8.0的窗口函数

MYSQL从8.0开始支持窗口函数。经过短暂的学习后我们可以写出分组排序的另一种解决方案：

✅ SELECT * FROM (
    SELECT 
    category_id, 
    unit_price,
    create_time,
    ROW_NUMBER() OVER (
        PARTITION BY category_id 
        ORDER BY create_time DESC
    ) as rn
    FROM
   goods) t
   WHERE rn <= 1;

ROW_NUMBER() 就是一个窗口函数

调用窗口函数的格式是：函数名（[expr]） OVER子句

其中，PARTITION BY 是分区的意思，我暂时理解为GROUP BY 不做深究

最关键的就是 WHERE 子句后面的内容：row_number <= 1 感觉是不是回到了ORACLE？

优点就是真的太方便了，缺点就是执行效率感人（与联表查询相同索引的情况下，执行了两次全表扫描）

Coding Life

MySQL分组排序引发的问题

需求