实用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

  将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高的原因