1.问题现象
顾名思义,慢查询就是query 类型SQL执行慢,本次问题产生是在生产环境的数据库里,一个特定关键字查询耗时180+秒,换关键字查询则不会出现这样的问题。
2.初步定位
- 查看数据量,10W级,随着业务增加,日增5W,从20W到100W只过了2周
- 查看表结构,存在大字段,单表多个大字段(这数据设计问题)
- 业务相关:多个节点,并发操作,存在多节点同时写的可能
3.坑——此时。。。数据挂了,生产的数据库挂了,具体原因虽然和这个无关,但是足足block问题排查3周时间
4.三周里
a. 这个问题在开发环境没法验证,测试环境也好,query还都挺快的,那么咋整,在数据坏之前,我还申请导出那批数据(经过允许的),试图本地恢复
坑——单表数据3+GB
这也是问题哦,3G数据的文本SQL文件(应该直接导出数据,这样文件可以小点),到了开发用的L480,用sublime打开需要很久,用VScode需要一会儿,提示说增加内存,加!加到4GB可以打开了,挺快,但是要执行SQL导入几乎不可能!
我试图用图形界面来执行的时候,结果,VScode突然就挂了,然后再看数据,3GB成了60+MB,居然自己给我截断了——教训,重要数据千万备份好,不然那个开源、免费的或者盗版的soft会让你后悔!
b. 大字段转储,文件、对象存储等转移大字段
c. MQ缓存,缓解存储压力
5.最终
一通SQL大法好
# 数据库磁盘占用(全)
select concat(truncate(sum(data_length)/1024/1024/1024,2),' GB') as data_size from information_schema.tables;
# 各库大小
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_size desc;
-- 慢查询日志开关、查询
show variables like 'slow_query_log';
show variables like 'long_query_time';
show variables like 'slow_query_log_file';
sql mysqldumpslow -s t -t 10 -g 'select' /data/mysql/data/dcbi-3306/log/slow.log
-- 字段长度
SELECT length(result_uuid) from **
-- sql分析,打开SQL轮廓
show VARIABLES like 'profiling';
show profiles;
-- 根据上一步过程记录id,查询详情
show profile cpu, block io for query **;
大字段造成过多IO
60+MB的大字段,一个敢设计,一个敢存储
奉劝大家设计数据库要善良啊,数据虽好,可不要偷懒!