mysql order by 耗时,加个order by使得查询效率增高500倍_mysql

加个order by使得查询效率提高500倍

很简单的三个表：

p248_user记录用户信息

CREATE TABLE `p248_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`list_ids` varchar(4000) NOT NULL DEFAULT '',

`email` varchar(255) NOT NULL,

`mobile` varchar(20) NOT NULL,

`_created` datetime NOT NULL,

`_updated` datetime NOT NULL,

`hb_status` tinyint(4) DEFAULT '0',

`sb_status` tinyint(4) DEFAULT '0',

`unsubscribe_email_status` tinyint(4) DEFAULT '0',

`unsubscribe_sms_status` tinyint(4) DEFAULT '0',

`hb_time` datetime DEFAULT NULL,

`unsubscribe_email_time` datetime DEFAULT NULL,

`unsubscribe_sms_time` datetime DEFAULT NULL,

`_create_operator_name` varchar(100) DEFAULT NULL,

`_update_operator_name` varchar(100) DEFAULT NULL,

`_create_operator_email` varchar(100) DEFAULT NULL,

`_update_operator_email` varchar(100) DEFAULT NULL,

`name` varchar(255) NOT NULL DEFAULT '',

`time` varchar(255) NOT NULL DEFAULT '',

`year` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `u1` (`email`,`mobile`) USING BTREE,

KEY `_updated` (`_updated`),

KEY `mobile` (`mobile`)

) ENGINE=InnoDB AUTO_INCREMENT=5596286 DEFAULT CHARSET=utf8

p248_list记录组信息

CREATE TABLE `p248_list` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) NOT NULL,

`status` enum('active','delete') DEFAULT 'active',

`_created` datetime NOT NULL,

`_updated` datetime NOT NULL,

`user_count` int(11) DEFAULT '0',

`lock_status` int(11) NOT NULL DEFAULT '0',

`lock_reason` varchar(100) DEFAULT NULL,

`lock_time` datetime DEFAULT NULL,

`import_percent` int(11) DEFAULT NULL,

`hb_count` int(11) DEFAULT '0',

`sb_count` int(11) DEFAULT '0',

`unsubscribe_email_count` int(11) DEFAULT '0',

`unsubscribe_sms_count` int(11) DEFAULT '0',

`_create_operator_name` varchar(100) DEFAULT NULL,

`_update_operator_name` varchar(100) DEFAULT NULL,

`_create_operator_email` varchar(100) DEFAULT NULL,

`_update_operator_email` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `_updated` (`_updated`)

) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8

p248_user_list是个多对多的表，记录用户属于哪些组

CREATE TABLE `p248_user_list` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`list_id` int(11) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `user_list_id` (`user_id`,`list_id`),

KEY `list_id` (`list_id`)

) ENGINE=InnoDB AUTO_INCREMENT=5646298 DEFAULT CHARSET=utf8

p248_user有200万条记录, p248_user_list有1000万条记录。

现在要找出属于29分组，并且手机号码不为空，并且没有退订的用户。这样的用户大约有100万个。现在要把这些用户按照4000个一批放到一群临时的记录集里。

这个要用到分页了，一开始的想法：

第一页:

SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' LIMIT 0, 4000;

第二页就LIMIT 4000, 4000。第三页就LIMIT 8000, 4000。依次类推。

结果这个SQL查询耗时用了整整5秒。

分析一下这个查询：

mysql> explain SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' LIMIT 0, 4000;

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+--------+------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+--------+------------------------------------+

| 1 | SIMPLE | p248_user | range | PRIMARY,mobile | mobile | 62 | NULL | 934446 | Using index condition; Using where |

| 1 | SIMPLE | p248_user_list | eq_ref | user_list_id,list_id | user_list_id | 8 | contacts.p248_user.id,const | 1 | Using index |

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+--------+------------------------------------+

2 rows in set (0.00 sec)

可以看到用户表扫描了93万行，几乎是全表扫描了。也就是把所有符合条件的结果都取了出来然后再取前4000条。

把上面的查询加上了ORDER BY `id`，结果查询耗时仅0.01秒，查询速度足足提高了500倍。

为什么会这样呢？

分析一下新的查询：

mysql> explain SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' ORDER BY `id` LIMIT 0, 4000;

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+------+-------------+

| 1 | SIMPLE | p248_user | index | PRIMARY,mobile | PRIMARY | 4 | NULL | 7999 | Using where |

| 1 | SIMPLE | p248_user_list | eq_ref | user_list_id,list_id | user_list_id | 8 | contacts.p248_user.id,const | 1 | Using index |

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+------+-------------+

2 rows in set (0.00 sec)

这次用户表仅扫描了8000行。也就是查询先使用了主键索引，扫描完前4000条符合条件的记录就直接结束了。

那取第二页呢：

mysql> explain SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE 1 = 1 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' ORDER BY `id` LIMIT 4000, 4000;

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+-------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+-------+-------------+

| 1 | SIMPLE | p248_user | index | PRIMARY,mobile | PRIMARY | 4 | NULL | 15999 | Using where |

| 1 | SIMPLE | p248_user_list | eq_ref | user_list_id,list_id | user_list_id | 8 | contacts.p248_user.id,const | 1 | Using index |

+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+-------+-------------+

2 rows in set (0.00 sec)

这次就要扫描16000行了，因为前4000条是第一页的没用扔掉了。

这样的话页数越大查询就会越耗时。

但实际上可以换个方法：

第一次查询结束时，得到最后一条记录的user id, 比如是6500。

第二次查询的时候用这个user_id作为条件去匹配

SELECT `id`, `email`, `mobile`, `_created`, `_updated`, `_create_operator_name`, `_update_operator_name`, `name`, `time`, `year` FROM `p248_user` WHERE id > 6500 AND id IN (SELECT DISTINCT user_id FROM `p248_user_list` WHERE list_id IN (29)) AND unsubscribe_sms_status = 0 AND mobile <> '' ORDER BY `id` LIMIT 0, 4000;

这样扫描的行数和第一页依然是一样的。

直到最后一页也是如此，耗时不会有任何明显的下降。

欢迎大家阅读《加个order by使得查询效率增高500倍_mysql》,跪求各位点评，by 搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~