mysql 下 SQL 优化NOT IN （除了把NOT IN转化为LEFT JOIN外，可优化影响的数据行数）
Mysql 下 SQL 优化NOT IN （除了把NOT IN转化为LEFT JOIN外，可优化影响的数据行数）
2010-05-31 17:38:30| 分类： 默认分类|字号 订阅
Mysql 下 SQL 优化 NOT IN：
除了把NOT IN转化为LEFT JOIN外，可通过优化业务逻辑，减少了查询涉及到的返回的数据，从而达到优化查询的目的。
====================================================================
查询目的： 最新更新的10条博文信息 ：从博文表中取出200条，然后排除重复，按照发表时间顺序显示。
SELECT * FROM (
SELECT post_title, guid , path, post_author, post_date, tableid
FROM wp_posts, wp_blogs
WHERE post_r_site =1 /* post_r_site =1 表示网校下的博文 */
AND post_type = ‘post’ /* type=post 为博文 */
AND post_status = ‘publish’ /* 博文不是私有博文 */
AND wp_blogs.blog_id=wp_posts.tableid
AND wp_blogs.blog_id NOT IN (SELECT id FROM wp_blacklist WHERE type=’0′) /* 博客Id 不再黑名单中 */
AND wp_posts.tableid NOT IN ( SELECT blog_id FROM wp_blogs WHERE registered > ‘2010-05-14’ ) /* 用户注册日期大于05月14日 */
AND wp_posts.post_author IN (SELECT id FROM wp_users) /* 博主用户信息存在 */
ORDER BY wp_posts.post_date DESC /* 按照发表博文的时间做降序排列 */
LIMIT 200
)FF
GROUP BY tableid /* 排除重复用户 只显示用户的一条博文信息 */
ORDER BY max( post_date ) DESC /* 按照发表博文的时间做降序排列 */
LIMIT 0,10
这条查询语句，会从满足条件的wp_posts中取出200条记录。但是满足post_r_site =1等条件的数据达27万多条。27万远大于200条。
====================================================================
测试时，清空下Mysql 的 查询缓存，SQL语句有可能被内存缓存。
mysql> FLUSH QUERY CACHE;
mysql> RESET QUERY CACHE;
mysql> FLUSH TABLES;
关闭mysql 并释放mysql占用的内存
[root@mail ~]# more res.sh
killall memcached
memcached -d -m 500 -u root -l 192.168.190.11 -p 12000 -c 256 -P /tmp/memcached.pid
mysqladmin -u root –password=123qwe shutdown
./test
mysqld_safe –defaults-file=/home/linyang/database/my.cnf &
分配一个很大的内存，使得mysql的内存占用被释放。
[root@mail ~]# more test.cpp
#include <stdio.h>
#include <string.h>
int main()
{
long iSize = 1024*1024*2000;
char * pMem = new char[iSize];
if(pMem)
{
memset(pMem,0,iSize);
printf(“clear mem success/n”);
delete pMem;
}
else
printf(“clear mem error/n”);
return 0;
}
[root@mail ~]# g++ -o test test.cpp
====================================================================
explain 此sql语句，会发现wp_posts表涉及到的行 273098行。 远大于我们的需要200条。
mysql> explain SELECT * FROM (
-> SELECT post_title, guid , path, post_author, post_date, tableid
-> FROM wp_posts, wp_blogs
-> WHERE post_r_site =1 /* post_r_site =1 表示查询会计网校下的博文 */
-> AND post_type = ‘post’ /* type=post 为博文 */
-> AND post_status = ‘publish’ /* 博文不是私有博文 */
-> AND wp_blogs.blog_id=wp_posts.tableid
-> AND wp_blogs.blog_id NOT IN (SELECT id FROM wp_blacklist WHERE type=’0′) /* 博客Id 不再黑名单中 */
月14日 */D wp_posts.tableid NOT IN ( SELECT blog_id FROM wp_blogs WHERE registered > ‘2010-05-14’ ) /* 用户注册日期大于05
-> AND wp_posts.post_author IN (SELECT id FROM wp_users) /* 博主用户信息存在 */
-> ORDER BY wp_posts.post_date DESC /* 按照发表博文的时间做降序排列 */
-> LIMIT 200
-> )FF
-> GROUP BY tableid /* 排除重复用户 只显示用户的一条博文信息 */
-> ORDER BY max( post_date ) DESC /* 按照发表博文的时间做降序排列 */
-> LIMIT 0,10;
————+———————-+——-
| id | select_type | table | rows | Extra |
+—-+——————–+————–+——–+———————————+
| 1 | PRIMARY | <derived2> | 200 | Using temporary; Using filesort |
| 2 | DERIVED | wp_posts | 273098 | Using where |
| 2 | DERIVED | wp_blogs | 1 | |
| 5 | DEPENDENT SUBQUERY | wp_users | 1 | Using index |
| 4 | DEPENDENT SUBQUERY | wp_blogs | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | wp_blacklist | 1 | Using index; Using where |
+—-+——————–+————–+——–+———————————+
6 rows in set (0.03 sec)
====================================================================
NOT IN的（除了转化为LEFT JOIN外，可以通过业务逻辑）优化:
缩小了博文的数据范围，定义在10天内发表的博文，取出200条，然后排除重复，按照发表时间顺序显示。
如果不加 博文的时间范围限制，返回满足条件的全部的博文数据列表多达27万条记录，mysql会分配很大内存来存放数据。
加上博文的时间范围，则返回满足条件的全部的博文数据列减少了很多。
再次explain 此sql语句，会发现wp_posts表涉及到的行为248行。 执行sql语句 发现速度提升不少。
====================================================================
mysql> explain SELECT * FROM (
SELECT post_title, guid , path, post_author, post_date, tableid
FROM wp_posts, wp_blogs
WHERE post_r_site =1 /* post_r_site =1 表示查询会计网校下的博文 */
AND post_type = ‘post’ /* type=post 为博文 */
AND post_status = ‘publish’ /* 博文不是私有博文 */
AND post_date > ‘2010-05-04’ /* 定义在10天内发表的博文 */
AND wp_blogs.blog_id=wp_posts.tableid
AND wp_blogs.blog_id NOT IN (SELECT id FROM wp_blacklist WHERE type=’0′) /* 博客Id 不再黑名单中 */
AND wp_posts.tableid NOT IN ( SELECT blog_id FROM wp_blogs WHERE registered > ‘2010-05-14’ ) /* 用户注册日期大于05月14日 */
AND wp_posts.post_author IN (SELECT id FROM wp_users) /* 博主用户信息存在 */
ORDER BY wp_posts.post_date DESC /* 按照发表博文的时间做降序排列 */
LIMIT 200
)FF
GROUP BY tableid /* 排除重复用户 只显示用户的一条博文信息 */
ORDER BY max( post_date ) DESC /* 按照发表博文的时间做降序排列 */
LIMIT 0,10
+—-+——————–+————–+——+———————————+
| id | select_type | table | rows | Extra |
+—-+——————–+————–+——+———————————+
| 1 | PRIMARY | <derived2> | 62 | Using temporary; Using filesort |
| 2 | DERIVED | wp_posts | 248 | Using where |
| 2 | DERIVED | wp_blogs | 1 | |
| 5 | DEPENDENT SUBQUERY | wp_users | 1 | Using index |
| 4 | DEPENDENT SUBQUERY | wp_blogs | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | wp_blacklist | 1 | Using index; Using where |
+—-+——————–+————–+——+———————————+
6 rows in set (0.00 sec)
整理：
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：Mysql 上 SQL 优化NOT IN （除了把NOT IN转化为LEFT JOIN外，可优化影响的数据行数）
转载请注明原文链接：Mysql 上 SQL 优化NOT IN （除了把NOT IN转化为LEFT JOIN外，可优化影响的数据行数）
