自己本人常用的或实用的SQL或mysql技巧

大表回收空间SQL

使用场景

• 多线程插入导致的碎片化
• 阿里云的 数据传输DTS 在大数据量的情况下会出现表空间增大，记得提工单防止传输失败

命令

# 此过程会锁表请谨慎操作
optimize table t_user;

查询表占用的存储大小

使用场景

• 分库分表查看

命令

SELECT file_name,concat(TOTAL_EXTENTS,'MB') as 'FileSize' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC;

查询慢日志记录

使用场景

• 数据库CPU飙升
• IOPS飙升
• 索引优化

命令

select * from mysql.slow_log where start_time>='2020-05-08 00:00:00';

单表分时间段求和营业额或其他信息

# 获取每天的交易总额 假设 id 为 13位毫秒级时间戳
# 如果基于时区问题 那么直接根据当前数据库时区与客户端返回的时区处理加减 2020-06-24 13:48:46
select sum(fAmount) totalAmount,DATE_FORMAT(from_unixtime((id)/1000), '%Y-%m-%d') stime from t_productdoc where storeID = 235366 group by stime;

查询一个表的记录，在另一个表中需要有一条以上记录

# 这个场景适用于 比如 某个商户在某段时间内 至少有一笔交易记录 或者多笔交易记录 等类似的场景
SELECT *
FROM `t_merchant`
WHERE `conditon` = 0
  and (
          SELECT COUNT(`id`) as num
          FROM `t_doc`
          WHERE `t_doc`.`shopID` = `t_merchant`.`shopID`
            and `t_doc`.`nTimestamp` < 1598284800
            and `t_doc`.`done` = 1) > 0;