Post Jobs

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有一千万条记下。

 

现行反革命要寻找属于二十八分组,并且手提式有线电话机号码不为空,并且未有退订的用户。那样的用户大概有十0万个。未来要把那么些用户根据四千个一群放权一批近年来的记录集里。

 

其一要用到分页了,一最先的主见:

第一页:

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 陆仟, 6000。第二页就LIMIT 九千, 5000。依次类推。

结果这么些SQL查询耗费时间用了全部伍秒。

 

浅析一下以此查询:

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)

能够观看用户表扫描了九三万行,差不离是全表扫描了。也正是把全体符合条件的结果都取了出来然后再取前五千条。

 

 

把上面包车型大巴询问加上了O昂CoraDE本田CR-V BY
`id`,结果查询耗费时间仅0.0一秒,查询速度至少加强了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)

此番用户表仅扫描了九千行。也等于查询先接纳了主键索引,扫描完前伍仟条符合条件的笔录就平昔截止了。

 

 

那取第二页呢:

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)

此次将在扫描15000行了,因为前伍仟条是第贰页的没用扔掉了。

 

 

那样的话页数越大查询就能够越耗费时间。

 

 

但实在能够换个法子:

率先次询问结束时,获得终极一条记下的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;

如此扫描的行数和第2页照旧是千篇一律的。

甘休最终1页也是那样,耗费时间不会有其余显明的降落。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图