qianqianling 发表于 2018-10-4 10:35:50

mysql 慢查询说明

# mysqlsla -lt slow slow.log  
Report for slow logs: slow.log
  
200.98k queries total, 896 unique
  
Sorted by 't_sum'#按照总时间排序
  
Grand Totals: Time 9.11k s, Lock 11 s, Rows sent 322.59M, Rows Examined 819.93M
  
______________________________________________________________________ 001 ___
  
Count         : 1(0.00%)#sql执行次数占总slow log的百分比0.00%
  
Time          : 2862.478802 s total, 2862.478802 s avg, 2862.478802 s to 2862.478802 s max(31.43%) #执行时间占总慢sql的31.43%
  
Lock Time (s) : 267 otal, 267 vg, 267 o 267 ax(0.00%)#去除最快和最慢的sql, 覆盖率占0%的sql的执行时间.
  
Rows sent   : 951.83k avg, 951.83k to 951.83k max(0.30%)#结果行统计数量, 包括平均, 最小, 最大数量.
  
Rows examined : 18.08k avg, 18.08k to 18.08k max(0.00%)   #扫描行数量
  
Database      : uupay   #使用的数据库
  
Users         :
  
      dawagame@ 112.64.173.78 : 100.00% (1) of query, 0.30% (603) of all users#用户、IP、占到执行sql的0.30%
  

  
Query abstract:
  
SET timestamp=N; SELECT * FROM (SELECT * FROM uu_paylog WHERE pay_time BETWEEN N AND N) AS pa,(SELECT * FROM uu_paylog_history WHERE pay_time BETWEEN N AND N) AS ps;#查询语句
  
Query sample:
  
SET timestamp=1395808846;
  
SELECT * FROM (SELECT * FROM `uu_paylog` WHERE pay_time between 1392393600 AND 1395802751) as pa,(SELECT * FROM `uu_paylog_history` WHERE pay_time between 1392393600 AND 1395802751) as ps;#查询语句
  

  
______________________________________________________________________ 002 ___
  
Count         : 23(0.01%)
  
Time          : 1744.759876 s total, 75.859125 s avg, 68.960445 s to 90.404704 s max(19.16%)
  
95% of Time : 1570.735116 s total, 74.79691 s avg, 68.960445 s to 80.307138 s max
  
Lock Time (s) : 995 otal, 43 vg, 36 o 86 ax(0.01%)
  
95% of Lock : 845 otal, 40 vg, 36 o 59 ax
  
Rows sent   : 1.54M avg, 1.54M to 1.54M max(10.99%)
  
Rows examined : 1.54M avg, 1.54M to 1.54M max(4.32%)
  
Database      : datacenter
  
Users         :
  
      root@localhost: 100.00% (23) of query, 7.40% (14880) of all users
  
Query abstract:
  
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM droleselect;
  

  
Query sample:
  
SET timestamp=1395781352;
  
SELECT /*!40001 SQL_NO_CACHE */ * FROM `dRoleSelect`;
  

  
______________________________________________________________________ 003 ___
  
Count         : 23(0.01%)
  
Time          : 1141.456968 s total, 49.628564 s avg, 41.787475 s to 54.329336 s max(12.53%)
  
95% of Time : 1032.950757 s total, 49.188131 s avg, 41.787475 s to 54.003767 s max
  
Lock Time (s) : 946 otal, 41 vg, 28 o 104 ax(0.01%)
  
95% of Lock : 752 otal, 36 vg, 28 o 53 ax
  
Rows sent   : 926.18k avg, 926.18k to 926.18k max(6.60%)
  
Rows examined : 926.18k avg, 926.18k to 926.18k max(2.60%)
  
Database      : datacenter
  
Users         :
  
      root@localhost: 100.00% (23) of query, 7.40% (14880) of all users
  
Query abstract:
  
SET timestamp=N;
  

  
Query sample:
  
SET timestamp=1395770401;
  

  
______________________________________________________________________ 005 ___
  
Count         : 627(0.31%)
  
Time          : 820.093928 s total, 1.307965 s avg, 1.129821 s to 2.762843 s max(9.00%)
  
95% of Time : 752.770582 s total, 1.265161 s avg, 1.129821 s to 1.676759 s max
  
Lock Time (s) : 93.863 ms total, 150 vg, 93 o 1.228 ms max(0.84%)
  
95% of Lock : 83.639 ms total, 141 vg, 93 o 225 ax
  
Rows sent   : 0 avg, 0 to 0 max(0.00%)
  
Rows examined : 754.64k avg, 754.64k to 754.64k max(57.71%)
  
Database      : iyingke
  
Users         :
  
      iyingke_db@localhost: 100.00% (627) of query, 0.59% (1193) of all users
  
Query abstract:
  
SET timestamp=N; SELECT cid,mid,COUNT(*) FROM cinemamovie WHERE playtime BETWEEN 'S' AND 'S' GROUP BY cid,mid;
  

  
Query sample:
  
SET timestamp=1395775395;
  
select cid,mid,count(*) FROM cinemamovie where playtime between '2014-03-26 04:03:14' AND '2014-03-26 23:59:59' GROUP BY cid,mid;
  

  
______________________________________________________________________ 006 ___
  
Count         : 23(0.01%)
  
Time          : 161.369311 s total, 7.016057 s avg, 5.696684 s to 12.72534 s max(1.77%)
  
95% of Time : 138.779739 s total, 6.608559 s avg, 5.696684 s to 9.190991 s max
  
Lock Time (s) : 905 otal, 39 vg, 31 o 52 ax(0.01%)
  
95% of Lock : 802 otal, 38 vg, 31 o 49 ax
  
Rows sent   : 1.36M avg, 1.36M to 1.36M max(9.67%)
  
Rows examined : 1.36M avg, 1.36M to 1.36M max(3.80%)
  
Database      :
  
Users         :
  
      root@localhost: 100.00% (23) of query, 7.40% (14880) of all users
  
Query abstract:
  
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM showlog;
  

  
Query sample:
  
SET timestamp=1395781210;
  
SELECT /*!40001 SQL_NO_CACHE */ * FROM `showLog`;
  

  
______________________________________________________________________ 007 ___
  
Count         : 23(0.01%)
  
Time          : 158.490591 s total, 6.890895 s avg, 6.073215 s to 8.692146 s max(1.74%)
  
95% of Time : 141.553816 s total, 6.740658 s avg, 6.073215 s to 8.028118 s max
  
Lock Time (s) : 1.155 ms total, 50 vg, 37 o 138 ax(0.01%)
  
95% of Lock : 947 otal, 45 vg, 37 o 63 ax
  
Rows sent   : 133.52k avg, 133.52k to 133.52k max(0.95%)
  
Rows examined : 133.52k avg, 133.52k to 133.52k max(0.37%)
  
Database      :
  
Users         :
  
      root@localhost: 100.00% (23) of query, 7.40% (14880) of all users
  

  
Query abstract:
  
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM dreg;
  
Query sample:
  
SET timestamp=1395781275;
  
SELECT /*!40001 SQL_NO_CACHE */ * FROM `dReg`;
  

  
______________________________________________________________________ 008 ___
  
Count         : 23(0.01%)
  
Time          : 127.179138 s total, 5.529528 s avg, 5.053959 s to 6.666312 s max(1.40%)
  
95% of Time : 114.415055 s total, 5.448336 s avg, 5.053959 s to 6.033416 s max
  
Lock Time (s) : 1.171 ms total, 51 vg, 39 o 87 ax(0.01%)
  
95% of Lock : 999 otal, 48 vg, 39 o 69 ax
  
Rows sent   : 3.73M avg, 3.73M to 3.73M max(26.60%)
  
Rows examined : 3.73M avg, 3.73M to 3.73M max(10.46%)
  
Database      :
  
Users         :
  
      root@localhost: 100.00% (23) of query, 7.40% (14880) of all users
  

  
Query abstract:
  
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM osspayuser;
  
Query sample:
  
SET timestamp=1395781502;
  
SELECT /*!40001 SQL_NO_CACHE */ * FROM `ossPayUser`;
  

  
______________________________________________________________________ 009 ___
  
Count         : 823(0.41%)
  
Time          : 109.827912 s total, 133.448 ms avg, 100.121 ms to 292.359 ms max(1.21%)
  
95% of Time : 100.6995 s total, 128.937 ms avg, 100.121 ms to 186.794 ms max
  
Lock Time (s) : 0 total, 0 avg, 0 to 0 max(0.00%)
  
95% of Lock : 0 total, 0 avg, 0 to 0 max
  
Rows sent   : 0 avg, 0 to 0 max(0.00%)
  
Rows examined : 0 avg, 0 to 0 max(0.00%)
  
Database      :
  
Users         :
  
      root@localhost: 100.00% (823) of query, 7.40% (14880) of all users
  

  
Query abstract:
  
SET timestamp=N; PURGE BINARY logs TO 'S';
  
Query sample:
  
SET timestamp=1395771601;
  
purge binary logs to 'mysql-bin.016583';
  

  
______________________________________________________________________ 010 ___
  
Count         : 23(0.01%)
  
Time          : 73.43714 s total, 3.192919 s avg, 3.015631 s to 3.397369 s max(0.81%)
  
95% of Time : 66.686371 s total, 3.175541 s avg, 3.015631 s to 3.306015 s max
  
Lock Time (s) : 868 otal, 38 vg, 30 o 67 ax(0.01%)
  
95% of Lock : 740 otal, 35 vg, 30 o 51 ax
  
Rows sent   : 362.86k avg, 362.86k to 362.86k max(2.59%)
  
Rows examined : 362.86k avg, 362.86k to 362.86k max(1.02%)
  

  
Database      :
  
Users         :
  
      root@localhost: 100.00% (23) of query, 7.40% (14880) of all users
  

  
Query abstract:
  
SET timestamp=N; SELECT /*!N SQL_NO_CACHE */ * FROM uu_logs;
  

  
Query sample:
  
SET timestamp=1395781531;
  
SELECT /*!40001 SQL_NO_CACHE */ * FROM `uu_logs`;


页: [1]
查看完整版本: mysql 慢查询说明