《Mysql學(xué)習(xí)order by查詢(xún)效率提高500倍》要點(diǎn)：
本文介紹了Mysql學(xué)習(xí)order by查詢(xún)效率提高500倍，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

很簡(jiǎn)單的三個(gè)表：

p248_user記錄用戶(hù)信息

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是個(gè)多對(duì)多的表,記錄用戶(hù)屬于哪些組

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萬(wàn)條記錄, p248_user_list有1000萬(wàn)條記錄.



現(xiàn)在要找出屬于29分組,并且手機(jī)號(hào)碼不為空,并且沒(méi)有退訂的用戶(hù).這樣的用戶(hù)大約有100萬(wàn)個(gè).現(xiàn)在要把這些用戶(hù)按照4000個(gè)一批放到一群臨時(shí)的記錄集里.



這個(gè)要用到分頁(yè)了,一開(kāi)始的想法：

第一頁(yè):

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;

第二頁(yè)就LIMIT 4000, 4000.第三頁(yè)就LIMIT 8000, 4000.依次類(lèi)推.

結(jié)果這個(gè)SQL查詢(xún)耗時(shí)用了整整5秒.



分析一下這個(gè)查詢(xún)：

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)

可以看到用戶(hù)表掃描了93萬(wàn)行,幾乎是全表掃描了.也就是把所有符合條件的結(jié)果都取了出來(lái)然后再取前4000條.





把上面的查詢(xún)加上了ORDER BY `id`,結(jié)果查詢(xún)耗時(shí)僅0.01秒,查詢(xún)速度足足提高了500倍.

為什么會(huì)這樣呢?

分析一下新的查詢(xún)：

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)

這次用戶(hù)表僅掃描了8000行.也就是查詢(xún)先使用了主鍵索引,掃描完前4000條符合條件的記錄就直接結(jié)束了.





那取第二頁(yè)呢：

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)

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/5746.html