实用QPS和TPS高的高效分析方法实用QPS和TPS高的高效分析方法

现在主库的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

View Code 将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调用情况。
如果要查某个表可以使用全文索引：

--查询user表，没有join关联字段,有"select"的查询 select * from ex where MATCH(`sql`) AGAINST ('+sys_user -join +select' IN BOOLEAN MODE);

立马就可以查询到明细情况。
这里查询了增删改查，如果只差TPS，可以把SQL查询语句的 " or `sql` like 'select%' "去掉就可以查DML的调用次数
总结：
1，该方法在百万级别的SQL审计中，能快速分析出哪个表的调用表次数多
2，结合mysql的fullindex全文索引，快速定位到具体的SQL
3，主要用来分析QPS高的原因
【实用QPS和TPS高的高效分析方法】