MySQL 常用查询优化

qq_29662633

已于 2022-11-11 15:17:37 修改

阅读量200

点赞数

文章标签： sql 数据库

于 2022-11-10 18:36:50 首次发布

本文链接：https://blog.csdn.net/qq_29662633/article/details/127793935

版权

例如:获取不同班级用的前10名

SELECT
    a.paypal_acount,
    a.id 
FROM
    orders a
    LEFT JOIN ( SELECT ROW_NUMBER () OVER ( PARTITION BY paypal_acount ORDER BY id DESC ) AS C ,id  FROM orders where  paypal_acount IN('xxxx','yyyy')) AS b ON a.id = b.id 
WHERE
    ( b.C BETWEEN 1 AND 9 )
ORDER BY
    a.paypal_acount DESC,a.id DESC;

mysql随机查询一条数据

SELECT
	* 
FROM
	goods t1
	JOIN (
	SELECT
		RAND() * (
		SELECT
			MAX( id ) 
		FROM
			goods 
		WHERE
			`cate_id` IN ( 4982, 867, 905, 4, 384, 869, 871 ) 
-- 			AND `price` = 44.43 
		) AS nid 
	) t2 ON t1.id >= t2.nid 
	LIMIT 1

mysql sum统计查询优化

#常规sql使用
SELECT
	SUM(amount) AS tp_sum 
FROM
	`orders` 
	WHERE
    createtime`>1546387200 
	AND createtime <1636473599 
    AND `status` = '1' 
	AND `pay_account_id` = 109821
	LIMIT 1
#优化sql使用
SELECT
	SUM( CASE WHEN `createtime` BETWEEN 1546387200 
	AND 1636473599 
	  THEN amount ELSE 0 END ) AS tp_sum 
FROM
	`orders` 
	WHERE
	 `status` = '1' 
	AND `pay_account_id` = 109821
	LIMIT 1

1:清理数据库日志(建议至少保留7天)

show binary logs; //查看日志
#mysql 5
show variables like 'expire_logs_days'; 
set global expire_logs_days = 7; 
#mysql 8 废弃expire_logs_days 改用binlog_expire_logs_seconds
show variables like 'binlog_expire_logs_seconds';  //查看日志保留时间
set global binlog_expire_logs_seconds=60*60*24*7 //设置日志保留时间

RESET MASTER;删除所有binlog日志，新日志编号从头开始
PURGE MASTER LOGS TO 'mysql-bin.010'; ||删除mysql-bin.010之前所有日志
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'; ||删除2003-04-02 22:46:26之前产生的所有日志