sql慢查询以及删除备份

1.删除重复数据,保留最早的一条。
步骤:(1)查看是否存在重复的数据
select order_id, product_id, count(*) 
from  product_comment
group by order_id, product_id having count(*) > 1
(2)备份表
CREATE TABLE bak_t_order_180417
AS
SELECT * FROM product_comment;
或者
CREATE TABLE  bak_t_order_180417 LIKE t_order;
INSERT INTO bak_t_order_180417 SELECT * FROM product_comment;
(3)删除数据
delete a from product_comment a
join (
)b on a.order_id = b.order_id and a.product_id = b.product_id
and a.comment_id > b.comment_id
2.如何进行分区间统计
统计消费总金额大于1000元的,800-1000元的,500到800元的,以及500元以下的人数
select count(case when ifnull(total_money,0) >= 1000 then a.customer_id end) as '大于1000',
count(case when ifnull(total_money,0) >= 800 and ifnull(total_money,0) < 1000 then a.customer_id end) as '800-1000',
count(case when ifnull(total_money,0) >= 500 and ifnull(total_money,0) < 800 then a.customer_id end) as '500-800',
count(case when ifnull(total_money,0) < 500 then a.customer_id end) '小于500'
from mc_userdb.`customer_login` a
left join 
(
select customer_id, sum(order_money) as total_money from mc_orderdb.`order_master` group by customer_id) b
)
on a.`customer_id` = b.`customer_id`
3.启用mysql慢查询日志
#慢查询日志存储位置
set global slow_query_log_file = /sql_log/slow_log.log
#未使用索引的sql记录日志
set global log_queries_not_using_indexs = on;
#设置慢查询时间1s 
set global long_query_time = 1;
#开启慢查询日志记录
set global low_query_log = on;


4.使用mysqldumpslow解析慢查询日志
mysqldumpslow slow-mysql.log
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u014172271/article/details/79979954
个人分类: 数据库
所属专栏: 数据库总结
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭