现在主库的MySQL的QPS一直在3K/s左右,想知道其到底执行了那些SQL,或者是那些SQL执行的次数比较多:
腾讯云的后台监控:
开启腾讯云的SQL审计后,下载几分钟SQL日志文件, 下列语句在MySQL建表,如我们下载了6分钟的单实例审计日志:
CREATE TABLE `ex` (
`AffectRows` varchar(255) DEFAULT NULL,
`ErrCode` varchar(255) DEFAULT NULL,
`SqlType` varchar(255) DEFAULT NULL,
`TableName` varchar(255) DEFAULT NULL,
`PolicyName` varchar(255) DEFAULT NULL,
`DBName` varchar(255) DEFAULT NULL,
`Sql` text CHARACTER SET utf8mb4,
`Host` varchar(255) DEFAULT NULL,
`User` varchar(255) DEFAULT NULL,
`ExecTime` varchar(255) DEFAULT NULL,
`CpuTime` varchar(255) DEFAULT NULL,
`LockWaitTime` varchar(255) DEFAULT NULL,
`CheckRows` varchar(255) DEFAULT NULL,
`SentRows` varchar(255) DEFAULT NULL,
`ThreadId` varchar(255) DEFAULT NULL,
`NsTime` varchar(255) DEFAULT NULL,
`IoWaitTime` varchar(255) DEFAULT NULL,
`TrxLivingTime` varchar(255) DEFAULT NULL,
`Timestamp` varchar(255) DEFAULT NULL,
`Result` varchar(255) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_sql` (`Sql`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
将sql文件导入到表中,执行下列SQL:
select tab,op,count(*) num from (
select case when `sql` like 'insert%' then 'insert'
when `sql` like 'replace%' then 'replace'
when `sql` like 'update%' then 'update'
when `sql` like 'delete%' then 'delete'
when `sql` like 'select%' then 'select' else '' end op,
case when `sql` like 'insert%' then substring_index(substring_index(LOWER(`sql`), '(', 1),'into',-1)
when `sql` like 'replace%' then substring_index(substring_index(LOWER(`sql`), '(', 1),'into',-1)
when `sql` like 'update%' then substring_index(substring_index(LOWER(`sql`), 'set', 1),'update',-1)
when `sql` like 'delete%' then substring_index(substring_index(LOWER(`sql`), 'where', 1),'from',-1)
when `sql` like 'select%' then substring_index(substring_index(LOWER(`sql`), 'where', 1),'from',-1)
else '' end tab,`sql` from ex where `sql` like 'insert%' or
`sql` like 'update%' or `sql` like 'delete%' or `sql` like 'select%' or `sql` like 'replace%' ) t
GROUP BY tab,op order by 3 desc
执行结果:
初步得到执行排序,初步计算其QPS:
前2个查询命令,QPS =550000/60/6=1527次/秒,其每秒3000多次/秒,这2个查询占用几乎一半左右,大体知道其SQL调用情况。
如果要查某个表可以使用全文索引:
--查询sys_user表,没有join关联字段,有"select"查询关键字的SQL
select * from ex where MATCH(`sql`) AGAINST ('+sys_user -join +select' IN BOOLEAN MODE);
如果id参数是一样的,就有多少重复的id(1060),后面就可以直接加redis缓存:
select * from ex where MATCH(`sql`) AGAINST ('+sys_user -join +select' IN BOOLEAN MODE)
and MATCH(`sql`) AGAINST ('1060')
立马就可以查询到明细情况。
这里查询了增删改查,如果只查TPS,可以把SQL查询语句的 " or `sql` like 'select%' " 去掉,就可以查DML的调用次数
总结:
1,该方法在百万级别(千万级也可以)的SQL审计中,能快速分析出哪个表的调用表次数多
2,结合mysql的fullindex全文索引,快速定位到具体的SQL
3,主要用来分析QPS高的原因