如下,这个表的设计肯定是有问题的,因为主键是id,但是查询的时候全是用的sys_key这一列,但是这表里总共就20多行数据,我感觉就算啥键都没有,直接全表扫描,来修改和读取,也不至于会需要1秒多吧?t_sys_conf | CREATE TABLE `t_sys_conf` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sys_key` varchar(50) NOT NULL,
`sys_value` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sys_key` (`sys_key`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 |______________________________________________________________________ 以下是慢查询统计结果 ___
Count : 1.95k (0.85%)
Time : 2973.410155 s total, 1.528746 s avg, 1.000199 s to 43.632868 s max (0.37%)
95% of Time : 2528.130352 s total, 1.368777 s avg, 1.000199 s to 2.465593 s max
Lock Time (s) : 461.62 ms total, 237 祍 avg, 68 祍 to 271.445 ms max (0.00%)
95% of Lock : 172.166 ms total, 93 祍 avg, 68 祍 to 148 祍 max
Rows sent : 0 avg, 0 to 0 max (0.00%)
Rows examined : 1 avg, 1 to 1 max (0.00%)
Database :
Users :
dian_user@ 221.174.25.210 : 100.00% (1945) of query, 61.88% (141073) of all usersQuery abstract:
SET timestamp=N; UPDATE t_sys_conf SET sys_value='S' WHERE sys_key='S' LIMIT N;Query sample:
SET timestamp=1361017126;
update t_sys_conf set sys_value='40959520' where sys_key='stat_cls_mid_ymd' limit 1;_____________________________________________________________________ 072 ___
Count : 198 (0.09%)
Time : 276.971112 s total, 1.398844 s avg, 1.053165 s to 3.129596 s max (0.03%)
95% of Time : 247.095108 s total, 1.314336 s avg, 1.053165 s to 2.635777 s max
Lock Time (s) : 10.499215 s total, 53.026 ms avg, 51.211 ms to 202.44 ms max (0.02%)
95% of Lock : 9.788412 s total, 52.066 ms avg, 51.211 ms to 52.856 ms max
Rows sent : 1 avg, 1 to 1 max (0.00%)
Rows examined : 1 avg, 1 to 1 max (0.00%)
Database :
Users :
dian_user@ 192.168.0.206 : 100.00% (198) of query, 38.02% (86690) of all usersQuery abstract:
SET timestamp=N; SELECT sys_value FROM t_sys_conf WHERE sys_key='S';Query sample:
SET timestamp=1361020320;
select sys_value from t_sys_conf where sys_key='push_start';
mysql
`id` int(11) NOT NULL AUTO_INCREMENT,
`sys_key` varchar(50) NOT NULL,
`sys_value` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sys_key` (`sys_key`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 |______________________________________________________________________ 以下是慢查询统计结果 ___
Count : 1.95k (0.85%)
Time : 2973.410155 s total, 1.528746 s avg, 1.000199 s to 43.632868 s max (0.37%)
95% of Time : 2528.130352 s total, 1.368777 s avg, 1.000199 s to 2.465593 s max
Lock Time (s) : 461.62 ms total, 237 祍 avg, 68 祍 to 271.445 ms max (0.00%)
95% of Lock : 172.166 ms total, 93 祍 avg, 68 祍 to 148 祍 max
Rows sent : 0 avg, 0 to 0 max (0.00%)
Rows examined : 1 avg, 1 to 1 max (0.00%)
Database :
Users :
dian_user@ 221.174.25.210 : 100.00% (1945) of query, 61.88% (141073) of all usersQuery abstract:
SET timestamp=N; UPDATE t_sys_conf SET sys_value='S' WHERE sys_key='S' LIMIT N;Query sample:
SET timestamp=1361017126;
update t_sys_conf set sys_value='40959520' where sys_key='stat_cls_mid_ymd' limit 1;_____________________________________________________________________ 072 ___
Count : 198 (0.09%)
Time : 276.971112 s total, 1.398844 s avg, 1.053165 s to 3.129596 s max (0.03%)
95% of Time : 247.095108 s total, 1.314336 s avg, 1.053165 s to 2.635777 s max
Lock Time (s) : 10.499215 s total, 53.026 ms avg, 51.211 ms to 202.44 ms max (0.02%)
95% of Lock : 9.788412 s total, 52.066 ms avg, 51.211 ms to 52.856 ms max
Rows sent : 1 avg, 1 to 1 max (0.00%)
Rows examined : 1 avg, 1 to 1 max (0.00%)
Database :
Users :
dian_user@ 192.168.0.206 : 100.00% (198) of query, 38.02% (86690) of all usersQuery abstract:
SET timestamp=N; SELECT sys_value FROM t_sys_conf WHERE sys_key='S';Query sample:
SET timestamp=1361020320;
select sys_value from t_sys_conf where sys_key='push_start';
mysql
主要是针对经常出现的慢语句进行优化。偶尔出现的可以先不做分析。
基本都慢,我查了一下,这表大概一小时被call 3W+次,update少,select多,不过慢查询里倒基本上是update,这个查询数算多还是少?
机器的CPU内存什么的我查了查,基本上应该算是负载比较低的状态。