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

 现在主库的MySQL的QPS一直在3K/s左右,想知道其到底执行了那些SQL,或者是那些SQL执行的次数比较多:

  腾讯云的后台监控:

实用QPS和TPS高的高效分析方法_第1张图片

    开启腾讯云的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和TPS高的高效分析方法_第2张图片

 

 初步得到执行排序,初步计算其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高的原因

 

你可能感兴趣的