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)
整理：
分享到：
相关推荐
NOT IN、JOIN、IS NULL、NOT EXISTS效率对比 语句一：select count(*) from A where A.a not in (select a from B) 语句二：select count(*) from A left join B on A.a = B.a where B.a is null 语句三：select ...
最近项目上用select查询时使用到了not in来排除用不到的主键id一开始使用的sql如下： select s.SORT_ID， ...表中的数据较多时这个sql的执行时间较长、执行效率低，在网上找资料说可以用 left join进
SQL（MySQL） LEFT JOIN 会取得左表（table1）全部记录，即使右表（table2）并无对应匹配记录。LEFT JOIN 基本语法如下： ... FROM table1 LEFT JOIN table2 ON condition ... MySQL LEFT JOIN 用法实例 下面是两...
本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法。分享给大家供大家参考，具体如下： MySQL 多表关联一对多查询取最新的一条数据 遇到的问题 多表关联一对多查询取最新的一条数据，数据出现重复 ...
mysql left join 语句格式 A LEFT JOIN B ON 条件表达式 left join 是以A表为基础，A表即左表，B表即右表。 左表(A)的记录会全部显示，而右表(B)只会显示符合条件表达式的记录，如果在右表(B)中没有符合条件的记录，...
本文实例讲述了mysql多个left join连接查询用法。分享给大家供大家参考，具体如下： mysql查询时需要连接多个表时，比如查询订单的商品表，需要查询商品的其他信息，其他信息不在订单的商品表，需要连接其他库的表，...
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。 如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据 在匹配阶段 WHERE 子句的条件都不会被使用...
内容概要：本文介绍了MySQL中的JOIN操作，以及通过具体的表结构示例演示了JOIN的应用。其中包括LEFT JOIN、ON条件过滤、笛卡尔积等概念和操作。本文适合对MySQL数据库和JOIN操作感兴趣的读者进行学习和参考。 使用...
mysql中有种可以通过join相关操作进行表与表之间的方式查询不同结果集,但是在一对多的情况下,关键查询的结果是多条的.例如:班级和学习的关系,我想很直观的看到班级和学生的情况,列表显示出班级的信息和班级的男生...
1 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN(MySQL 不支持)、CROSS JOIN 这是在网上找到的非常好的一篇博文，图解 join 语句： CODING HORROR-A Visual Explanation of SQL Joins 下图可以很清楚的明白，join ...
我们有时会有这样的应用，需要在sql的left join时，需要使值为NULL的列不返回NULL而时某个特定的值，比如0。这个时候，用is_null(field,0)是行不通的，会报错的，可以用ifnull实现，但是COALESE似乎更符合标准
SELECT DISTINCT g.*， cp.name AS cp_name， c.name AS category_name， t.name AS type_name FROMgm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g....
Hash Join算法是把一张小表数据存储到内存中的哈希表里，并逐行去匹配大表中的数据，计算哈希值并把符合条件的数据，从内存中返回客户端。 用sysbench生成4张表，并删除默认的k字段索引。 我们用explain format=...
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。 LEFT JOIN 关键字语法 SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1....
这里所说的主表是指在连接查询里MySQL以哪个表为主进行查询。比如说在LEFT JOIN查询里，一般来说左表就是主表，但这只是经验之谈，很多时候经验主义是靠不住的，为了说明问题，先来个例子，建两个演示用的表...
最近遇到一个查询比较慢的sql语句，用了子查询，大概需要0.8秒左右，这个消耗时间比较长，严重影响了性能，所以需要进行优化。单独查询单表或者子查询记录都很快，下面来看看详细的介绍。 开启profile mysql> show...
优先级 ...在left join下，两者的区别： on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。 where则是在生成临时表之后使用的条件，此时已经不管是否使
例如，往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99，编号为3 的名为Wang 的成绩为96.5. mysql> insert into MyClass values(1,'Tom',96.45),(2,...