MySQL存储过程

MySQL存储过程

-- //创建存储过程前先检查是否存在，存在就删除，这个可以忽略
DROP PROCEDURE IF EXISTS p_calc_production;
-- //存储过程
CREATE PROCEDURE p_calc_production()
BEGIN
-- 	//该变量用于标识是否还有数据需遍历
	DECLARE flag INT DEFAULT 0;
-- 	//创建一个变量用来存储遍历过程中的值
	DECLARE id BIGINT(40);
-- 	//查询出需要遍历的数据集合
	DECLARE idList CURSOR FOR (SELECT DISTINCT producer_id from class_order_config_history a1 where DATE_SUB(CURDATE(), INTERVAL 0 DAY) = date(crt));
-- 	//查询是否有下一个数据，没有将标识设为1，相当于hasNext
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
-- 	//打开游标
	OPEN idList;
-- 		//取值设置到临时变量中
		FETCH idList INTO id;
-- 		//遍历未结束就一直执行
		WHILE flag != 1 DO
				-- targetSQL //你想要执行的目标功能，这里可以写多个SQL
				
-- 				//  注意
-- 				//这里有一个坑，目标语句引用临时变量，实测发现不需要加@符号，但是搜索到的结果都是例如：@id ，这样来使用，实测发现无法取到数据
-- 				//  注意

				UPDATE test2.hs_data_watch a , (
SELECT a.producer_id,a.dev_group,last_class_prod,last2_class_prod from
(SELECT a.producer_id,a.dev_group,(case when a.producer_id=b.producer_id and a.class_order=b.class_order and a.class_order_alias=b.alias then output_add_kg else 0 end) last_class_prod
 from (
SELECT sum(case when a.dev_group='打包工序' then output_add_kg*25 else output_add_kg end) output_add_kg
,producer_id,dev_group,htime,class_order,class_order_alias
 from hs_machine_platforms a where a.producer_id=id and a.htime='2021-09-12'
GROUP BY producer_id,dev_group,htime,class_order,class_order_alias
ORDER BY producer_id,dev_group,htime,class_order,class_order_alias) a
left JOIN
(SELECT * from class_order_config_history a1 where a1.producer_id=id and DATE_SUB(CURDATE(), INTERVAL 2 DAY) <= date(crt) ORDER BY crt DESC limit 1,1) b
on a.producer_id=b.producer_id and a.class_order=b.class_order and a.class_order_alias=b.alias 
where b.producer_id is not null) a

LEFT JOIN
(
SELECT a.producer_id,a.dev_group,(case when a.producer_id=b.producer_id and a.class_order=b.class_order and a.class_order_alias=b.alias then output_add_kg else 0 end) last2_class_prod
 from (
SELECT sum(case when a.dev_group='打包工序' then output_add_kg*25 else output_add_kg end) output_add_kg
,producer_id,dev_group,htime,class_order,class_order_alias
 from hs_machine_platforms a where a.producer_id=id and a.htime='2021-09-12'
GROUP BY producer_id,dev_group,htime,class_order,class_order_alias
ORDER BY producer_id,dev_group,htime,class_order,class_order_alias) a
left JOIN
(SELECT * from class_order_config_history a1 where a1.producer_id=id and DATE_SUB(CURDATE(), INTERVAL 2 DAY) <= date(crt) ORDER BY crt DESC limit 2,1) b
on a.producer_id=b.producer_id and a.class_order=b.class_order and a.class_order_alias=b.alias 
where b.producer_id is not null
) b
on a.producer_id=b.producer_id and a.dev_group=b.dev_group
) b1
set a.last_class_prod=b1.last_class_prod,a.last2_class_prod=b1.last2_class_prod
where a.producer_id=id and a.producer_id=b1.producer_id and a.dev_group=b1.dev_group;
-- 				一定要记得把游标向后移一位，这个坑我替各位踩过了，不需要再踩了
				FETCH idList INTO id;
		END WHILE;
	CLOSE idList;
END;

MySQL processlist 常用

show full processlist;


show processlist;


select id, db, user, host, command, time, state, info
from information_schema.processlist
order by time desc ;


-- 查询非 Sleep 状态的链接，按消耗时间倒序展示，自己加条件过滤
select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc ;



-- 查询执行时间超过2分钟的线程，然后拼接成 kill 语句
select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 2*60
order by time desc ;

SELECT name,host_id,concat_ws("_",dev_group,class_order_alias) as aa,host_id,variety,max(output_add_kg),min(output_add_kg),sum(output_add_kg),avg(output_add_kg),VAR_POP(output_add_kg) 'VARIANCE-总体方差',FORMAT(STD(output_add_kg),2) '总体标准差',FORMAT(STDDEV_SAMP(output_add_kg),2) '样本标准偏差' from hs_output_data a where 
-- a.host_id='2164914694412828684' and
a.producer_id=2162304858206502921 and 
dev_group='粗纱工序' and
 a.htime BETWEEN '2022-07-24 04:00:00' and '2022-07-24 12:00:00'
--   and output_add_kg>34
GROUP BY host_id,variety
ORDER BY host_id;

MySQL 多范围读取

在探索数据库优化的广阔领域中，我们不可避免地会遇到一系列独特的概念和技术。其中之一就是MySQL的多范围读取(Multi-Range Read, MRR)。

这种技术为我们提供了在处理大量数据时提高查询效率的强大手段。它通过改变数据检索的顺序，并利用操作系统缓存进行预读，从而显著减少I/O操作数量，提高查询速度。本文将深入探讨MRR的内部工作原理，以及如何在日常数据库管理中有效地应用这种技术。

从explain结果中，我们可以看到Extra字段多了「Using MRR」，表示的是用上了MRR优化。而且，由于我们在read_rnd_buffer中按照id做了排序，所以最后得到的结果集也是按照主键id递增顺序的，也就是与图1结果集中行的顺序相反。

MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

简单来说：MRR 的核心思想就是通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

顺序读带来了两个好处：

磁盘和磁头不再需要来回做机械运动。

可以充分利用磁盘预读。

所谓的磁盘预读，比如说在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。

MRR 在本质上是一种用「空间换时间」的做法。

MySQL 不可能给你无限的内存来进行排序，这块内存的大小就由参数read_rnd_buffer_size来控制，如果read_rnd_buffer满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限，如此循环。

参考

https://www.cnblogs.com/booksea/p/17761501.html
https://www.cnblogs.com/booksea/p/17439651.html#总结