Mysql学习order by查询效率提高500倍

概述介绍《Mysql学习order by查询效率提高500倍》开发教程，希望对您有用。

《MysqL学习order by查询效率提高500倍》要点：
本文介绍了MysqL学习order by查询效率提高500倍，希望对您有用。如果有疑问，可以联系我们。

导读：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',
`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',
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,
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`,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`,`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,const | 1 | Using index |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+------+-------------+
2 rows in set (0.00 sec)

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





那取第二页呢：

MysqL> explain SELECT `ID`,`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,const | 1 | Using index |
+----+-------------+----------------+--------+----------------------+--------------+---------+-----------------------------+-------+-------------+
2 rows in set (0.00 sec)

总结

以上是内存溢出为你收集整理的Mysql学习order by查询效率提高500倍全部内容，希望文章能够帮你解决Mysql学习order by查询效率提高500倍所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。