Mysql 下 SQL 优化NOT IN （除了把NOT IN转化为LEFT JOIN外，可优化影响的数据行数）

gmdzxx

　　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占用的内存
[iyunv@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的内存占用被释放。
[iyunv@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;
}
 
[iyunv@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)




 
 
 
整理：