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