|
lujinke 发表于 2014-6-23 10:49 ![]()
explain一下,看看,两次的执行计划有什么不同,而且最好把MySQL版本,表结构列一下
执行计划是一样的,但是我怀疑真正执行时,并没有按照执行计划执行。耗时长的应该进行了全表扫描。
mysql> EXPLAIN SELECT * FROM w_bet WHERE
-> is_group_item='F'
-> AND start_time>='2014-06-16 00:00:00'
-> AND start_time<='2014-06-16 23:59:59' AND BET_MONEY>=1000
-> AND is_cash='T'
-> AND vip=9 AND is_group='F'
-> AND type_code='Soccer.Predict'
-> ORDER BY bet_time DESC LIMIT 0,66;
+----+-------------+-------+-------+------------------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | w_bet | index | START_TIME,s_b,IS_GROUP,IS_GROUP_2 | bs | 9 | NULL | 2607 | Using where |
+----+-------------+-------+-------+------------------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM w_bet WHERE
-> is_group_item='F'
-> AND start_time>='2014-06-16 00:00:00'
-> AND start_time<='2014-06-16 23:59:59' AND BET_MONEY>=1000
-> AND is_cash='T'
-> AND vip=9 AND is_group='F'
-> AND type_code='Soccer.Predict'
-> ORDER BY bet_time DESC LIMIT 0,67;
+----+-------------+-------+-------+------------------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | w_bet | index | START_TIME,s_b,IS_GROUP,IS_GROUP_2 | bs | 9 | NULL | 2646 | Using where |
+----+-------------+-------+-------+------------------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
bs是(bet_time,start_time)复合索引
mysql> show create table w_bet\G;
*************************** 1. row ***************************
Table: w_bet
Create Table: CREATE TABLE `w_bet` (
`BET_ID` varchar(64) NOT NULL,
`SPORT_CODE` varchar(64) NOT NULL,
`TYPE_CODE` varchar(64) NOT NULL,
`MATCH_ID` varchar(64) DEFAULT NULL,
`GAME_ID` varchar(64) NOT NULL,
`SELECT_UUID` varchar(64) DEFAULT NULL,
`SELECT_ID` varchar(128) NOT NULL,
`PLAY_TIME` varchar(30) DEFAULT NULL,
`BET_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`TIME_SCOPE` char(1) DEFAULT NULL,
`ODDS_DISPLAY_KIND` char(1) NOT NULL,
`BET_ODD_KIND` char(1) NOT NULL,
`BET_ODD` int(11) DEFAULT NULL,
`BET_ODD2` int(11) DEFAULT NULL,
`GIVEN` char(1) DEFAULT NULL,
`BET_ODDS` float DEFAULT NULL,
`BET_MONEY` int(11) NOT NULL,
`SOLID_MONEY` float DEFAULT NULL,
`CAN_WIN` float DEFAULT NULL,
`U_ID` varchar(30) NOT NULL,
`SPARTNER_ID` varchar(30) DEFAULT NULL,
`PARTNER_ID` varchar(30) DEFAULT NULL,
`SAGENT_ID` varchar(30) DEFAULT NULL,
`AGENT_ID` varchar(30) DEFAULT NULL,
`BET_STAT` char(2) NOT NULL,
`IS_GROUP` char(1) NOT NULL,
`IS_GROUP_ITEM` char(1) NOT NULL,
`PARENT_ID` varchar(64) DEFAULT NULL,
`IS_CASH` char(1) NOT NULL,
`CURRENCY_ID` int(11) DEFAULT NULL,
`CURRENCY_RATE` float DEFAULT NULL,
`RETURN_USER` float(11,2) DEFAULT NULL,
`RETURN_SAGENT` float(11,2) DEFAULT NULL,
`RETURN_PARTNER` float(11,2) DEFAULT NULL,
`RETURN_SPARTNER` float(11,2) DEFAULT NULL,
`RETURN_AGENT` float(11,2) DEFAULT NULL,
`PERCENT_SPARTNER` int(11) DEFAULT NULL,
`PERCENT_PARTNER` int(11) DEFAULT NULL,
`PERCENT_SAGENT` int(11) DEFAULT NULL,
`PERCENT_AGENT` int(11) DEFAULT NULL,
`WIN_USER` float(11,2) DEFAULT NULL,
`WIN_SPARTNER` float(11,2) DEFAULT NULL,
`WIN_PARTNER` float(11,2) DEFAULT NULL,
`WIN_SAGENT` float(11,2) DEFAULT NULL,
`WIN_AGENT` float(11,2) DEFAULT NULL,
`WIN_ADMIN` float(11,2) DEFAULT NULL,
`BET_DESC` text,
`BET_DESCZH` text,
`CANCEL_FOR` varchar(30) DEFAULT NULL,
`START_TIME` timestamp NULL DEFAULT NULL,
`HOST_GOAL` smallint(5) unsigned DEFAULT NULL,
`VISITOR_GOAL` smallint(5) unsigned DEFAULT NULL,
`HOST_HALF_GOAL` smallint(5) unsigned DEFAULT NULL,
`VISITOR_HALF_GOAL` smallint(5) unsigned DEFAULT NULL,
`HV_TURN` char(1) DEFAULT NULL,
`MATCH_IDS` varchar(128) DEFAULT NULL,
`VIP` int(11) DEFAULT NULL,
`NET_NAME` varchar(90) DEFAULT NULL,
`LOGINNAME` varchar(90) DEFAULT NULL,
`drag_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`balance_time` timestamp NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`BET_ID`),
KEY `SELECT_UUID` (`SELECT_UUID`),
KEY `START_TIME` (`START_TIME`),
KEY `MATCH_ID` (`MATCH_ID`),
KEY `ix_bet_dragtime` (`drag_time`),
KEY `U_ID` (`U_ID`),
KEY `PARENT_ID` (`PARENT_ID`),
KEY `BET_STAT` (`BET_STAT`),
KEY `balance_time` (`balance_time`),
KEY `U_ID_balance_time` (`U_ID`,`balance_time`),
KEY `AGENT_ID_balance_time` (`AGENT_ID`,`balance_time`),
KEY `SPARTNER_ID` (`SPARTNER_ID`),
KEY `PARTNER_ID` (`PARTNER_ID`),
KEY `SAGENT_ID` (`SAGENT_ID`),
KEY `bs` (`BET_TIME`,`START_TIME`),
KEY `s_b` (`START_TIME`,`BET_MONEY`),
KEY `IS_GROUP` (`IS_GROUP`),
KEY `IS_GROUP_2` (`IS_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec) |
|