慢查询日志里有几条sql没有用到索引或扫描行数太多,不知道该怎么优化,请各位帮忙,谢谢了.
表结构--表里有140多万条记录
CREATE TABLE `t_toll_universal` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`invokeId` varchar(50) DEFAULT NULL,
`customName` varchar(200) DEFAULT NULL,
`customID` varchar(200) DEFAULT NULL,
`callDirection` int(11) DEFAULT NULL,
`agentID` varchar(50) DEFAULT NULL,
`corpName` varchar(50) DEFAULT NULL,
`firstNo` varchar(50) DEFAULT NULL,
`firstTrunkDn` varchar(50) DEFAULT NULL,
`secondNo` varchar(50) DEFAULT NULL,
`secondTrunkDn` varchar(50) DEFAULT NULL,
`wanIp` varchar(50) DEFAULT NULL,
`lanIp` varchar(50) DEFAULT NULL,
`tollType1` int(11) DEFAULT NULL,
`beginTime` datetime DEFAULT NULL,
`endTime` datetime DEFAULT NULL,
`Exmoney` float(8,2) DEFAULT NULL,
`PresideTrunkDn` varchar(50) DEFAULT NULL,
`HashAccountid` varchar(50) DEFAULT NULL,
`tollType2` int(11) DEFAULT NULL,
`managerId` varchar(50) DEFAULT NULL,
`presidePhoneNo` varchar(50) DEFAULT NULL,
`ComputerSymbol` varchar(10) DEFAULT NULL,
`GatewayName` varchar(50) DEFAULT NULL,
`Exmoney_6s` float(8,2) DEFAULT NULL,
`Costmoney` float(8,2) DEFAULT NULL,
`Costmoney_6s` float(8,2) DEFAULT NULL,
`Charge_CurPerAmount` float(8,2) DEFAULT NULL,
`Cost_CurPerAmount` float(8,2) DEFAULT NULL,
`WorkingPloyName` varchar(50) DEFAULT NULL,
`ExMinute` float(8,2) DEFAULT NULL,
`LeftMinute` float(8,2) DEFAULT NULL,
`ServerSymbol` varchar(100) DEFAULT NULL,
`Charge_IpPerAmount` float(8,0) DEFAULT NULL,
`Cost_IpPerAmount` float(8,0) DEFAULT NULL,
`AnswerCallTime` datetime DEFAULT NULL,
`IsLocalCall` int(4) DEFAULT NULL,
`IsRealNoCall` int(4) DEFAULT NULL,
`TransInvokeId` varchar(50) DEFAULT NULL,
`TransCallTime` datetime DEFAULT NULL,
`IsTransferred` int(4) DEFAULT NULL,
`SessionID` varchar(50) DEFAULT NULL,
`ServerSymbol2` varchar(50) DEFAULT NULL,
`SubsidiariesSignNo` varchar(50) DEFAULT NULL,
`IsWebCall` int(4) DEFAULT NULL,
`IsMasterPhone` int(4) DEFAULT NULL,
`IVRBottonNum` varchar(10) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `agentID` (`agentID`),
KEY `123` (`firstNo`,`secondNo`),
KEY `invokeId` (`invokeId`) USING BTREE,
KEY `idx_managerId_TransCallTime` (`managerId`,`TransCallTime`) USING BTREE,
KEY `idx_managerId_endTime` (`managerId`,`endTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2849809 DEFAULT CHARSET=utf8;-- 表里有3条记录
CREATE TABLE `t_manager` (
`managerid` varchar(50) NOT NULL,
`Corpname` varchar(50) NOT NULL,
PRIMARY KEY (`managerid`),
KEY `idx_managerid` (`managerid`),
KEY `idx_corpname` (`Corpname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
mysql> EXPLAIN SELECT a.corpname,b.exmoney,b.minute,b.gatewayname from t_manager as a right join (select sum(exmoney) as exmoney,managerid,SUM(ceil(timestampdiff(second, beginTime, endTime)/60)) AS minute ,gatewayname from t_toll_universal where begintime >= '2012-04-01' and begintime < '2012-05-01' and calldirection = 1 and islocalcall=0 group by managerid,gatewayname) as b on b.managerid=a.managerid;+----+-------------+------------------+--------+-----------------------+---------+---------+-------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+--------+-----------------------+---------+---------+-------------+---------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | a | eq_ref | PRIMARY,idx_managerid | PRIMARY | 152 | b.managerid | 1 | |
| 2 | DERIVED | t_toll_universal | ALL | NULL | NULL | NULL | NULL | 1404650 | Using where; Using temporary; Using filesort |
+----+-------------+------------------+--------+-----------------------+---------+---------+-------------+---------+----------------------------------------------+
3 rows in set
mysql> EXPLAIN SELECT a.corpname,b.exmoney,b.minute,b.gatewayname from t_manager as a right join (select sum(exmoney) as exmoney,managerid,presidephoneno,SUM(ceil(timestampdiff(second, beginTime, endTime)/60)) AS minute ,gatewayname from t_toll_universal where gatewayname='北京网关二' and begintime >= '2012-04-01' and begintime < '2012-05-01' and calldirection = 1 and islocalcall=0 group by managerid,gatewayname) as b on b.managerid=a.managerid;+----+-------------+------------------+-------+---------------+-----------------------------+---------+------+---------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+-----------------------------+---------+------+---------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | t_toll_universal | index | NULL | idx_managerId_TransCallTime | 162 | NULL | 1404650 | Using where |
+----+-------------+------------------+-------+---------------+-----------------------------+---------+------+---------+-----------------------------------------------------+
表结构--表里有140多万条记录
CREATE TABLE `t_toll_universal` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`invokeId` varchar(50) DEFAULT NULL,
`customName` varchar(200) DEFAULT NULL,
`customID` varchar(200) DEFAULT NULL,
`callDirection` int(11) DEFAULT NULL,
`agentID` varchar(50) DEFAULT NULL,
`corpName` varchar(50) DEFAULT NULL,
`firstNo` varchar(50) DEFAULT NULL,
`firstTrunkDn` varchar(50) DEFAULT NULL,
`secondNo` varchar(50) DEFAULT NULL,
`secondTrunkDn` varchar(50) DEFAULT NULL,
`wanIp` varchar(50) DEFAULT NULL,
`lanIp` varchar(50) DEFAULT NULL,
`tollType1` int(11) DEFAULT NULL,
`beginTime` datetime DEFAULT NULL,
`endTime` datetime DEFAULT NULL,
`Exmoney` float(8,2) DEFAULT NULL,
`PresideTrunkDn` varchar(50) DEFAULT NULL,
`HashAccountid` varchar(50) DEFAULT NULL,
`tollType2` int(11) DEFAULT NULL,
`managerId` varchar(50) DEFAULT NULL,
`presidePhoneNo` varchar(50) DEFAULT NULL,
`ComputerSymbol` varchar(10) DEFAULT NULL,
`GatewayName` varchar(50) DEFAULT NULL,
`Exmoney_6s` float(8,2) DEFAULT NULL,
`Costmoney` float(8,2) DEFAULT NULL,
`Costmoney_6s` float(8,2) DEFAULT NULL,
`Charge_CurPerAmount` float(8,2) DEFAULT NULL,
`Cost_CurPerAmount` float(8,2) DEFAULT NULL,
`WorkingPloyName` varchar(50) DEFAULT NULL,
`ExMinute` float(8,2) DEFAULT NULL,
`LeftMinute` float(8,2) DEFAULT NULL,
`ServerSymbol` varchar(100) DEFAULT NULL,
`Charge_IpPerAmount` float(8,0) DEFAULT NULL,
`Cost_IpPerAmount` float(8,0) DEFAULT NULL,
`AnswerCallTime` datetime DEFAULT NULL,
`IsLocalCall` int(4) DEFAULT NULL,
`IsRealNoCall` int(4) DEFAULT NULL,
`TransInvokeId` varchar(50) DEFAULT NULL,
`TransCallTime` datetime DEFAULT NULL,
`IsTransferred` int(4) DEFAULT NULL,
`SessionID` varchar(50) DEFAULT NULL,
`ServerSymbol2` varchar(50) DEFAULT NULL,
`SubsidiariesSignNo` varchar(50) DEFAULT NULL,
`IsWebCall` int(4) DEFAULT NULL,
`IsMasterPhone` int(4) DEFAULT NULL,
`IVRBottonNum` varchar(10) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `agentID` (`agentID`),
KEY `123` (`firstNo`,`secondNo`),
KEY `invokeId` (`invokeId`) USING BTREE,
KEY `idx_managerId_TransCallTime` (`managerId`,`TransCallTime`) USING BTREE,
KEY `idx_managerId_endTime` (`managerId`,`endTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2849809 DEFAULT CHARSET=utf8;-- 表里有3条记录
CREATE TABLE `t_manager` (
`managerid` varchar(50) NOT NULL,
`Corpname` varchar(50) NOT NULL,
PRIMARY KEY (`managerid`),
KEY `idx_managerid` (`managerid`),
KEY `idx_corpname` (`Corpname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
mysql> EXPLAIN SELECT a.corpname,b.exmoney,b.minute,b.gatewayname from t_manager as a right join (select sum(exmoney) as exmoney,managerid,SUM(ceil(timestampdiff(second, beginTime, endTime)/60)) AS minute ,gatewayname from t_toll_universal where begintime >= '2012-04-01' and begintime < '2012-05-01' and calldirection = 1 and islocalcall=0 group by managerid,gatewayname) as b on b.managerid=a.managerid;+----+-------------+------------------+--------+-----------------------+---------+---------+-------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+--------+-----------------------+---------+---------+-------------+---------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | a | eq_ref | PRIMARY,idx_managerid | PRIMARY | 152 | b.managerid | 1 | |
| 2 | DERIVED | t_toll_universal | ALL | NULL | NULL | NULL | NULL | 1404650 | Using where; Using temporary; Using filesort |
+----+-------------+------------------+--------+-----------------------+---------+---------+-------------+---------+----------------------------------------------+
3 rows in set
mysql> EXPLAIN SELECT a.corpname,b.exmoney,b.minute,b.gatewayname from t_manager as a right join (select sum(exmoney) as exmoney,managerid,presidephoneno,SUM(ceil(timestampdiff(second, beginTime, endTime)/60)) AS minute ,gatewayname from t_toll_universal where gatewayname='北京网关二' and begintime >= '2012-04-01' and begintime < '2012-05-01' and calldirection = 1 and islocalcall=0 group by managerid,gatewayname) as b on b.managerid=a.managerid;+----+-------------+------------------+-------+---------------+-----------------------------+---------+------+---------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+-------+---------------+-----------------------------+---------+------+---------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | t_toll_universal | index | NULL | idx_managerId_TransCallTime | 162 | NULL | 1404650 | Using where |
+----+-------------+------------------+-------+---------------+-----------------------------+---------+------+---------+-----------------------------------------------------+
解决方案 »
- 研究mysql源代码 能对数据库 架构深入了解吗?
- 请教一个mysql排序问题
- php类论坛数据库中的时间:1178287713 怎么得到的?
- 如果将ORACLE中含BLOB和CLOB字段的表的数据导到MYSQL中?
- mysql 中有像oracle中的 nvl 函数吗?
- mysql字符集问题
- sql有几种分类呀?有时候sql server,有时候又看到Mysql,有什么区别呀?
- 如何获得运行时的错误代号将此代号写入log中?(java+postgresql)
- 请教大神关于增加字段取值范围的SQL语句,不是更新是增加
- 如何导出指定编码格式的数据
- 用mysqldump导出整个数据库时,能不能不导某些表数据?(顶有分)
- 创建数据库
mysql> EXPLAIN SELECT b.managerid, b.corpname,presidephoneno,a.exmoney,a.minute from t_manager as b right join (select sum(exmoney) as exmoney,managerid,presidephoneno,SUM(ceil(timestampdiff(second, beginTime, endTime)/60)) AS minute from t_toll_universal where begintime >= 'S' and begintime < 'S' and calldirection = 1 group by managerid, presidephoneNo) as a on a.managerid = b.managerid
;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | t_toll_universal | ALL | NULL | NULL | NULL | NULL | 1404650 | Using where; Using temporary; Using filesort |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------------------------------+
mysql> EXPLAIN SELECT b.corpname,a.exmoney,a.minute from t_manager as b right join (select sum(exmoney) as exmoney,managerid,SUM(ceil(timestampdiff(second, beginTime, endTime)/60)) AS minute from t_toll_universal where begintime >= '2012-04-01' and begintime < '2012-05-01' and calldirection = 0 group by managerid) as a on a.managerid = b.managerid;+----+-------------+------------------+--------+-----------------------+-----------------------------+---------+-------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+--------+-----------------------+-----------------------------+---------+-------------+---------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | PRIMARY | b | eq_ref | PRIMARY,idx_managerid | PRIMARY | 152 | a.managerid | 1 | |
| 2 | DERIVED | t_toll_universal | index | NULL | idx_managerId_TransCallTime | 162 | NULL | 1404650 | Using where |
+----+-------------+------------------+--------+-----------------------+-----------------------------+---------
mysql> EXPLAIN SELECT a.corpname,b.exmoney,b.minute,b.gatewayname from t_manager as a right join (select sum(exmoney) as exmoney,managerid,presidephoneno,SUM(ceil(timestampdiff(second, beginTime, endTime)/60)) AS minute ,gatewayname from t_toll_universal where begintime >= '2012-04-01' and begintime < '2012-05-01' and calldirection= 1 and islocalcall=1 group by managerid,gatewayname) as b on b.managerid=a.managerid;+----+-------------+------------------+--------+-----------------------+---------+---------+-------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+--------+-----------------------+---------+---------+-------+---------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | a | const | PRIMARY,idx_managerid | PRIMARY | 152 | const | 1 | |
| 2 | DERIVED | t_toll_universal | ALL | NULL | NULL | NULL | NULL | 1404650 | Using where; Using temporary; Using filesort |
+----+-------------+------------------+--------+-----------------------+---------+---------+-------+---------+-