mysql的limit使用不当导致了全表扫描

digdeep126 · 发表于 2014-6-22 13:38

mysql> SELECT COUNT(*) FROM w_bet WHERE
->  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';
+----------+
| COUNT(*) |
+----------+
|    66 |
+----------+
1 row in set (0.05 sec)

结果有66行记录，但是：

SELECT * FROM w_bet WHERE
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

返回了 66 行

执行耗时 : 0.493 sec
传送时间 : 11.773 sec
总耗时    : 12.266 sec
（SQLyog的输出）

再看：
SELECT * FROM w_bet WHERE
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

返回了 66 行

执行耗时 : 0.482 sec
传送时间 : 0.114 sec
总耗时    : 0.597 sec
（SQLyog的输出）

limit 0,66 和 limit 0,67执行结果相差极大！！
传送时间 : 11.773 sec 也就是 sending data 花的时间，这么长的时间，明显用在了全表扫描！

所以：limit 超出了 count(*) 值 66 ，之后导致了全表扫描。

幕南风 · 发表于 2014-6-22 21:38

有个比例的，检索的数据量达到该比例优化器就会认为走全表扫成本比较低

digdeep126 · 发表于 2014-6-22 23:52

幕南风发表于 2014-6-22 21:38
有个比例的，检索的数据量达到该比例优化器就会认为走全表扫成本比较低

我觉得不是这个原因，我把时间范围稍微修改下：
mysql> SELECT COUNT(*) FROM w_bet FORCE INDEX(bs) WHERE
-> start_time>='2013-06-16 00:00:00'
-> AND start_time<='2014-06-16 20:59:59' AND BET_MONEY>=1000
-> AND is_cash='T'
-> AND vip=9 AND is_group='F'
-> AND type_code='Soccer.Predict';
+----------+
| COUNT(*) |
+----------+
|    114 |
+----------+
1 row in set (1.39 sec)

请看：
SELECT * FROM w_bet FORCE INDEX(bs) WHERE
start_time>='2013-06-16 00:00:00'
AND start_time<='2014-06-16 20: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,114

返回了 114 行

执行耗时 : 0.579 sec
传送时间 : 0.470 sec
总耗时    : 1.049 sec

再看：
SELECT * FROM w_bet FORCE INDEX(bs) WHERE
start_time>='2013-06-16 00:00:00'
AND start_time<='2014-06-16 20: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,115

返回了 114 行

执行耗时 : 0.570 sec
传送时间 : 12.383 sec
总耗时    : 12.954 sec

不可能两次都这么巧，遇到了那个优化器选择走全部扫描的比例值吧。

lujinke · 发表于 2014-6-23 10:48

幕南风发表于 2014-6-22 21:38
有个比例的，检索的数据量达到该比例优化器就会认为走全表扫成本比较低

以前是10%,但是之后版本的优化器有更复杂的判断逻辑

lujinke · 发表于 2014-6-23 10:49

本帖最后由 lujinke 于 2014-6-23 10:50 编辑

digdeep126 发表于 2014-6-22 23:52
我觉得不是这个原因，我把时间范围稍微修改下：
mysql> SELECT COUNT(*) FROM w_bet FORCE INDEX(bs) WH ...

explain一下，看看，两次的执行计划有什么不同，而且最好把MySQL版本，表结构列一下

digdeep126 · 发表于 2014-6-23 11:21

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)

digdeep126 · 发表于 2014-6-23 11:22

本帖最后由 digdeep126 于 2014-6-23 11:23 编辑

mysql> select version();
+------------+
| version() |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.11 sec)

5.6和5.5都有这个问题。

我也是帮别人优化，他这个表的索引建的不是很合理

幕南风 · 发表于 2014-6-24 01:06

digdeep126 发表于 2014-6-23 11:21
执行计划是一样的，但是我怀疑真正执行时，并没有按照执行计划执行。耗时长的应该进行了全表扫描。
mysq ...

1. 耗时长的没有进行全表扫，你也看了，2者的执行计划几乎一致，除了rows
2. rows之所以不同，个人认为，rows本身便是个平均值，如果你再验证 LIMIT 0,69 ; LIMIT 0，70 ....结果应该也是会不同，但相差不会太大。

digdeep126 · 发表于 2014-6-24 11:25

幕南风发表于 2014-6-24 01:06
1. 耗时长的没有进行全表扫，你也看了，2者的执行计划几乎一致，除了rows
2. rows之所以不同，个人认为， ...

谢谢帮主的回复。

但是两者执行时间相差很远，那么真正的执行计划很定是不一样的。explain的结果并不一定是执行时真正的执行计划。

另外 12秒的时间，除了全部扫描，和锁之外，很难有其他的操作会话这么长的时间。
下面是profile的结果：
mysql> show profile for query 3;
+----------------------+----------+
| Status             | Duration |
+----------------------+----------+
| starting          | 0.000095 |
| checking permissions | 0.000007 |
| Opening tables    | 0.000025 |
| init                | 0.000099 |
| System lock       | 0.000012 |
| optimizing          | 0.000021 |
| statistics          | 0.000225 |
| preparing          | 0.000031 |
| Sorting result    | 0.000004 |
| executing          | 0.000002 |
| Sending data       | 1.199665 |
| end                | 0.000013 |
| query end          | 0.000011 |
| closing tables    | 0.000014 |
| freeing items       | 0.000072 |
| cleaning up       | 0.000020 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 4;
+----------------------+-----------+
| Status             | Duration  |
+----------------------+-----------+
| starting          |  0.000104 |
| checking permissions |  0.000009 |
| Opening tables    |  0.000031 |
| init                |  0.000094 |
| System lock       |  0.000025 |
| optimizing          |  0.000023 |
| statistics          |  0.000253 |
| preparing          |  0.000033 |
| Sorting result    |  0.000007 |
| executing          |  0.000003 |
| Sending data       | 18.246401 |
| end                |  0.000018 |
| query end          |  0.000013 |
| closing tables    |  0.000017 |
| freeing items       |  0.000162 |
| logging slow query |  0.000081 |
| cleaning up       |  0.000020 |
+----------------------+-----------+

两者的差别就在 sending data上，而最常见的导致sending data时间很长的原因就是全表扫描。

digdeep126 · 发表于 2014-6-24 12:10

本帖最后由 digdeep126 于 2014-6-24 12:19 编辑

幕南风发表于 2014-6-24 01:06
1. 耗时长的没有进行全表扫，你也看了，2者的执行计划几乎一致，除了rows
2. rows之所以不同，个人认为， ...

我使用
mysql> show status like 'select_scan';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Select_scan | 14 |
+---------------+-------+
1 row in set (0.00 sec)

进一步证实了我的想法，limit 0,67时，导致 select_scan 增1，limit 0,66时，select_scan 没有增加。

而 select_scan 这个status会在 full table scan时自增。

mysql中实在不知道如何获得真正的执行计划...
只能通过select_scan来判断是否全表扫描了

我怀疑这算不算是mysql 的一个bug?