mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录
字体：[ ] 类型：转载 时间：
mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录，需要的朋友可以参考下。
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 count(*) from A where not exists (select a from B where A.a = B.a) 知道以上三条语句的实际效果是相同的已经很久了，但是一直没有深究其间的效率对比。一直感觉上语句二是最快的。 今天工作上因为要对一个数千万行数据的库进行数据清除，需要删掉两千多万行数据。大量的用到了以上三条语句所要实现的功能。本来用的是语句一，但是结果是执行速度1个小时32分，日志文件占用21GB。时间上虽然可以接受，但是对硬盘空间的占用确是个问题。因此将所有的语句一都换成语句二。本以为会更快。没想到执行40多分钟后，第一批50000行都没有删掉，反而让SQL SERVER崩溃掉了，结果令人诧异。试了试单独执行这条语句，查询近一千万行的表，语句一用了4秒，语句二却用了18秒，差距很大。语句三的效率与语句一接近。 第二种写法是大忌，应该尽量避免。第一种和第三种写法本质上几乎一样。 假设buffer pool足够大，写法二相对于写法一来说存在以下几点不足： （1）left join本身更耗资源（需要更多资源来处理产生的中间结果集） （2）left join的中间结果集的规模不会比表A小 （3）写法二还需要对left join产生的中间结果做is null的条件筛选，而写法一则在两个集合join的同时完成了筛选，这部分开销是额外的 这三点综合起来，在处理海量数据时就会产生比较明显的区别（主要是内存和CPU上的开销）。我怀疑楼主在测试时buffer pool可能已经处于饱和状态，这样的话，写法二的那些额外开销不得不借助磁盘上的虚拟内存，在SQL Server做换页时，由于涉及到较慢的I/O操作因此这种差距会更加明显。 关于日志文件过大，这也是正常的，因为删除的记录多嘛。可以根据数据库的用途考虑将恢复模型设为simple，或者在删除结束后将日志truncate掉并把文件shrink下来。 因为以前曾经作过一个对这个库进行无条件删除的脚本，就是要删除数据量较大的表中的所有数据，但是因为客户要求，不能使用truncate table，怕破坏已有的库结构。所以只能用delete删，当时也遇到了日志文件过大的问题，当时采用的方法是分批删除，在SQL2K中用set rowcount @chunk，在SQL2K5中用delete top @chunk。这样的操作不仅使删除时间大大减少，而且让日志量大大减少，只增长了1G左右。 但是这次清除数据的工作需要加上条件，就是delete A from A where ....后面有条件的。再次使用分批删除的方法，却已经没效果了。 不知您知不知道这是为什么。 mysql not in 和 left join 效率问题记录 首先说明该条sql的功能是查询集合a不在集合b的数据。 not in的写法
代码如下: select add_tb.RUID from (select distinct RUID from UserMsg where SubjectID =12 and CreateTime&' 15:30:00' and CreateTime&=' 16:00:00' ) add_tb where add_tb.RUID not in (select distinct RUID from UserMsg where SubjectID =12 and CreateTime&' 15:30:00' )
返回444行记录用时 0.07sec explain 结果 +----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+-- ----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+-- ----------------------------+ | 1 | PRIMARY | &derived2& | ALL | NULL | NULL | NULL | NULL | 452 | Using where | | 3 | DEPENDENT SUBQUERY | UserMsg | index_subquery | RUID,SubjectID,CreateTime | RUID | 96 | func | 2 | U Using where | | 2 | DERIVED | UserMsg | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1857 | U Using temporary | +----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+-- ----------------------------+ 分析:该条查询速度快原因为id=2的sql查询出来的结果比较少，所以id=1sql所以运行速度比较快，id=2的使用了临时表，不知道这个时候是否使用索引？ 其中一种left join
代码如下: select a.ruid,b.ruid from(select distinct RUID from UserMsg where SubjectID =12 and CreateTime &= ' 15:30:00' and CreateTime&=' 16:00:00' ) a left join ( select distinct RUID from UserMsg where SubjectID =12 and CreateTime& ' 15:30:00' ) b on a.ruid = b.ruid where b.ruid is null
返回444行记录用时 0.39sec explain 结果 +----+-------------+------------+-------+----------------------+------------+---------+------+------+----------------------- -------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+----------------------+------------+---------+------+------+----------------------- -------+ | 1 | PRIMARY | &derived2& | ALL | NULL | NULL | NULL | NULL | 452 | | | 1 | PRIMARY | &derived3& | ALL | NULL | NULL | NULL | NULL | 1112 | U Not exists | | 3 | DERIVED | UserMsg | ref | SubjectID,CreateTime | SubjectID | 5 | | 6667 | U Using temporary | | 2 | DERIVED | UserMsg | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1838 | U Using temporary | +----+-------------+------------+-------+----------------------+------------+---------+------+------+----------------------- -------+ 分析:使用了两个临时表，并且两个临时表做了笛卡尔积，导致不能使用索引并且数据量很大 另外一种left join
代码如下: select distinct a.RUID from UserMsg a left join UserMsg b on a.ruid = b.ruid and b.subjectID =12 and b.createTime & ' 15:30:00' where a.subjectID =12 and a.createTime &= ' 15:30:00' and a.createtime &=' 16:00:00' and b.
返回444行记录用时 0.07sec explain 结果 +----+-------------+-------+-------+---------------------------+------------+---------+--------------+------+--------------- --------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------------------+------------+---------+--------------+------+--------------- --------------------+ | 1 | SIMPLE | a | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1839 | U Using temporary | | 1 | SIMPLE | b | ref | RUID,SubjectID,CreateTime | RUID | 96 | dream.a.RUID | 2 | U N Distinct | +----+-------------+-------+-------+---------------------------+------------+---------+--------------+------+--------------- --------------------+ 分析：两次查询都是用上了索引，并且查询时同时进行的，所以查询效率应该很高 使用not exists的sql
代码如下: select distinct a.ruid from UserMsg a where a.subjectID =12 and a.createTime &= ' 15:30:00' and a.createTime &=' 16:00:00' and not exists ( select distinct RUID from UserMsg where subjectID =12 and createTime & ' 15:30:00' and ruid=a.ruid )
返回444行记录用时 0.08sec explain 结果 +----+--------------------+---------+-------+---------------------------+------------+---------+--------------+------+------ ------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------+-------+---------------------------+------------+---------+--------------+------+------ ------------------------+ | 1 | PRIMARY | a | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1839 | Using
Using temporary | | 2 | DEPENDENT SUBQUERY | UserMsg | ref | RUID,SubjectID,CreateTime | RUID | 96 | dream.a.RUID | 2 | Using where | +----+--------------------+---------+-------+---------------------------+------------+---------+--------------+------+------ ------------------------+ 分析：同上基本上是一样的，只是分解了2个查询顺序执行，查询效率低于第3个 为了验证数据查询效率，将上述查询中的subjectID =12的限制条件去掉，结果统计查询时间如下 0.20s 21.31s 0.25s 0.43s laserhe帮忙分析问题总结
代码如下: select a.ruid,b.ruid from( select distinct RUID from UserMsg where CreateTime &= ' 15:30:00' and CreateTime&=' 16:00:00' ) a left join UserMsg b on a.ruid = b.ruid and b.createTime & ' 15:30:00' where b.
执行时间0.13s +----+-------------+------------+-------+-----------------+------------+---------+--------+------+-------------------------- ----+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-----------------+------------+---------+--------+------+-------------------------- ----+ | 1 | PRIMARY | &derived2& | ALL | NULL | NULL | NULL | NULL | 1248 | | | 1 | PRIMARY | b | ref | RUID,CreateTime | RUID | 96 | a.RUID | 2 | U Not exists | | 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3553 | U Using temporary | +----+-------------+------------+-------+-----------------+------------+---------+--------+------+-------------------------- ----+ 执行效率类似与not in的效率 数据库优化的基本原则：让笛卡尔积发生在尽可能小的集合之间，mysql在join的时候可以直接通过索引来扫描，而嵌入到子查询里头，查询规 划器就不晓得用合适的索引了。 一个SQL在数据库里是这么优化的：首先SQL会分析成一堆分析树，一个树状数据结构，然后在这个数据结构里，查询规划器会查找有没有合适 的索引，然后根据具体情况做一个排列组合，然后计算这个排列组合中的每一种的开销（类似explain的输出的计算机可读版本），然后比较里 面开销最小的，选取并执行之。那么： explain select a.ruid,b.ruid from(select distinct RUID from UserMsg where CreateTime &= ' 15:30:00' and CreateTime&=' 16:00:00' ) a left join UserMsg b on a.ruid = b.ruid and b.createTime & ' 15:30:00' where b. 和 explain select add_tb.RUID -& from (select distinct RUID -& from UserMsg -& where CreateTime&' 15:30:00' -& and CreateTime&=' 16:00:00' -& ) add_tb -& where add_tb.RUID -& not in (select distinct RUID -& from UserMsg -& where CreateTime&' 15:30:00' -& ); explain +----+--------------------+------------+----------------+-----------------+------------+---------+------+------+------------ ------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+----------------+-----------------+------------+---------+------+------+------------ ------------------+ | 1 | PRIMARY | &derived2& | ALL | NULL | NULL | NULL | NULL | 1248 | Using where | | 3 | DEPENDENT SUBQUERY | UserMsg | index_subquery | RUID,CreateTime | RUID | 96 | func | 2 | U Using where | | 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3509 | U Using temporary | +----+--------------------+------------+----------------+-----------------+------------+---------+------+------+------------ ------------------+ 开销是完全一样的，开销可以从 rows 那个字段得出（基本上是rows那个字段各个行的数值的乘积，也就是笛卡尔积） 但是呢：下面这个： explain select a.ruid,b.ruid from(select distinct RUID from UserMsg where CreateTime &= ' 15:30:00' and CreateTime&=' 16:00:00' ) a left join ( select distinct RUID from UserMsg where createTime & ' 15:30:00' ) b on a.ruid = b.ruid where b. 执行时间21.31s +----+-------------+------------+-------+---------------+------------+---------+------+-------+----------------------------- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------------+---------+------+-------+----------------------------- -+ | 1 | PRIMARY | &derived2& | ALL | NULL | NULL | NULL | NULL | 1248 | | | 1 | PRIMARY | &derived3& | ALL | NULL | NULL | NULL | NULL | 30308 | U Not exists | | 3 | DERIVED | UserMsg | ALL | CreateTime | NULL | NULL | NULL | 69366 | U Using temporary | | 2 | DERIVED | UserMsg | range | CreateTime | CreateTime | 9 | NULL | 3510 | U Using temporary | +----+-------------+------------+-------+---------------+------------+---------+------+-------+----------------------------- -+ 我就有些不明白 为何是四行 并且中间两行巨大无比 按理说 查询规划器应该能把这个查询优化得跟前面的两个一样的 （至少在我熟悉的pgsql数据库里我有信心是一样的） 但mysql里头不是 所以我感觉查询规划器里头可能还是糙了点 我前面说过优化的基本原则就是，让笛卡尔积发生在尽可能小的集合之间 那么上面最后一种写法至少没有违反这个原则 虽然b 表因为符合条件的非常多，基本上不会用索引 但是并不应该妨碍查询优化器看到外面的join on条件，从而和前面两个SQL一样，选取主键进行join 不过我前面说过查询规划器的作用 理论上来讲 遍历一遍所有可能，计算一下开销 是合理的 我感觉这里最后一种写法没有遍历完整所有可能 可能的原因是子查询的实现还是比较简单？ 子查询对数据库的确是个挑战 因为基本都是递归的东西 所以在这个环节有点毛病并不奇怪 其实你仔细想想，最后一种写法无非是我们第一种写法的一个变种，关键在表b的where 条件放在哪里 放在里面，就不会用索引去join 放在外面就会 这个本身就是排列组合的一个可能
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具查看: 807|回复: 10|关注: 0
求助 fmincon优化最小值 这个我实在搞不定了
<h1 style="color:# 麦片财富积分
新手, 积分 11, 距离下一级还需 39 积分
求大神帮助 我弄了几天没弄出来:Q& &
<h1 style="color:# 麦片财富积分
另附我的QQ 有大神愿意帮我的话
<h1 style="color:# 麦片财富积分
希望版主和高手可以帮助一下
<h1 style="color:#7 麦片财富积分
关注者： 12
直接敲进去就可以计算。
<h1 style="color:# 麦片财富积分
直接敲进去就可以计算。
我对目标函数用for循环累加 约束条件我看fmincon例子里面都是用的矩阵。。。。
<h1 style="color:# 麦片财富积分
我对目标函数用for循环累加 约束条件我看fmincon例子里面都是用的矩阵。。。。 ...
能否用fminbnd函数
<h1 style="color:#7 麦片财富积分
关注者： 12
|此回复为最佳答案
能否用fminbnd函数
打开最优化工具箱的GUI，把你的目标函数和约束都定义成函数，按照帮助文档的例子，分分钟就弄清楚了。
<h1 style="color:# 麦片财富积分
打开最优化工具箱的GUI，把你的目标函数和约束都定义成函数，按照帮助文档的例子，分分钟就弄清楚了。 ...
哦哦 太感谢了:D
<h1 style="color:# 麦片财富积分
打开最优化工具箱的GUI，把你的目标函数和约束都定义成函数，按照帮助文档的例子，分分钟就弄清楚了。 ...
对了 还有优化中的初始值应该怎么取 感觉对计算结果没有影响啊
<h1 style="color:#7 麦片财富积分
关注者： 12
对了 还有优化中的初始值应该怎么取 感觉对计算结果没有影响啊
初始值你大概选一个，比较靠近解的最好，这样迭代次数会少些，你可以画图看看。
如果是凸函数，初始值可以随便选个了。
站长推荐 /1
Powered by&&&&&&&&&&&&&&&&&&
posts - 6,comments - 3,trackbacks - 0
阅读排行榜
评论排行榜
同时找出最大值和最小值的一种优化算法-MaxAndMin&&&& 在一个有n个元素的集合中，单独求出最大值（或最小值）的算法，很容易实现，只需按序扫描整个序列，记录最大值（或最小值），其上限为n-1次。&&&& 但在很多应用中，需同时找到最大值和最小值，一般情况大家较容易想到用上面的算法独立的找到最大值和最小值，各用n-1次，共有2n-2次比较。这在大容量数据库中（n很大），效率不是很高。&&&& 在这里，我将给出一种新的算法代码，以大幅提高其效率（n很大时）。具体做法是：每次成对的处理数据，先将一对元素进行比较，然后把较大者与当前最大值比较，较小者与当前最小者比较，因此每两个元素需要3次比较。具体实现时需考虑n的奇偶，n为奇数，3【n/2】次；n为偶数，3n/2-2次。因此总的比较次数至多为3【n-2】。（注：【n】表示不大于n的整数）。具体C++源代码如下：
#include&&iostream.h&#include&&limits.h&&&&//包含INT_MAX，INT_MIN的头文件&int&nMax&=&INT_MIN;&&&//将INT_MIN设为当前最大值的初始值int&nMin&=&INT_MAX;/**//////记录比较最大值函数int&Max(int&nNum){&&&&&if&(nMax&nNum)&&&&{&&&&&&&&nMax&=&nN&&&&}&&&&return&nM}/**//////记录比较最小值函数int&Min(int&nNum){&&&&if&(nMin&nNum)&&&&{&&&&&&&&nMin&=&nN&&&&}&&&&return&nM}void&main(){&&&&//测试序列&&&&int&nData[]&=&{<span style="color: #,<span style="color: #,<span style="color: #,<span style="color: #,<span style="color: #,<span style="color: #,<span style="color: #,<span style="color: #,<span style="color: #,-<span style="color: #,<span style="color: #80};&&&&int&nLen&=&sizeof(nData)/sizeof(nData[<span style="color: #]);&&&&if&(nLen%<span style="color: #&==&<span style="color: #)&&&//待测数据为奇数&&&&{&&&&&&&&//待测数据为奇数，最值初始值均设为nData[0]&&&&&&&&Max(nData[<span style="color: #]);&&&&&&&&&&Min(nData[<span style="color: #]);&&&&&&&&for&(int&i=<span style="color: #;i&=(nLen-<span style="color: #)/<span style="color: #;i++)&&&&&&&&{&&&&&&&&&&&&if&(nData[i]&nData[nLen-i])&&&&&&&&&&&&{&&&&&&&&&&&&&&&&Max(nData[i]);&&&&&&&&&&&&&&&&Min(nData[nLen-i]);&&&&&&&&&&&&}&&&&&&&&&&&&&else&&&&&&&&&&&&{&&&&&&&&&&&&&&&&Max(nData[nLen-i]);&&&&&&&&&&&&&&&&Min(nData[i]);&&&&&&&&&&&&}&&&&&&&&}&&&&}&&&&&else&&&&&&&&&&&&&&&//待测序列为偶数&&&&{&&&&&&&&for&(int&i=<span style="color: #;i&nLen/<span style="color: #;i++)&&&&&&&&{&&&&&&&&&&&&if&(nData[i]&nData[nLen-i-<span style="color: #])&&&&&&&&&&&&{&&&&&&&&&&&&&&&&Max(nData[i]);&&&&&&&&&&&&&&&&Min(nData[nLen-i-<span style="color: #]);&&&&&&&&&&&&}&&&&&&&&&&&&&else&&&&&&&&&&&&{&&&&&&&&&&&&&&&&Max(nData[nLen-i-<span style="color: #]);&&&&&&&&&&&&&&&&Min(nData[i]);&&&&&&&&&&&&}&&&&&&&&}&&&&}&&&&cout&&"nMax&=&"&&nMax&&endl&&"nMin&=&"&&nMin&&}
阅读(4610)
&re: 同时找出最大值和最小值的一种优化算法（比较次数至多为3【n/2】）小木虫 --- 500万硕博科研人员喜爱的学术科研平台
&&查看话题
优化后体系能量的最小值
请教：优化后体系能量的最小值可以在那个文件里找到？在.castep中，给出的很模糊
360截图41295.jpg
可以找到最近的Final energy，不过没有Final free energy&&和
NB est. 0K energy 。就是我给出的那个图片的样子。
请问，这是什么原因？
你在Task中选的是energy还是 Geometry&&optimization
Geometry&&optimization，不是energy
BFGS : Geometry optimization completed successfully.在Task为Energy的时候，不出现吧？
在文件里没有搜到，不管Task是 Geometry&&optimization，还是energy。
那不清楚了，按理说无论你是结构优化还是单点能，是都有这个的。
奥，我的没有，不知什么情况。
可本人计算后的文件。
请问一下，这三个能量值各代表什么？
出现三个值的原因：体系的电子结构在费米面附近出现多个能级简并时，自洽过程变得缓慢甚至不收敛，因此在自洽迭代过程中引入热微扰，使费米面附近一定能量范围的电子可以分数占据轨道，从而可以使体系收敛
如果只有第一个值呢？
原因是：？体系的电子结构在费米面附近没有出现多个能级简并，自洽过程正常，在自洽迭代过程中未引入热微扰。？？？？
请教，我的解释合理吗？
好的，谢谢了。
研究生必备与500万研究生在线互动！
扫描下载送金币
浏览器进程
打开微信扫一扫
随时随地聊科研梁山夫妻大办离婚庆典，两人亲自把喜字剪开。
让人意想不到的是，其中还有产妇和6个月孕妇。
声明：本文由入驻搜狐公众平台的作者撰写，除搜狐官方账号外，观点仅代表作者本人，不代表搜狐立场。
　　我们在找一个数学问题的最优解时是在一定的约束条件下进行的，在Maple中进行时此时用到的就是优化工具。在Maple中优化工具有两种，一种是局部优化，还有一种全局优化。利用局部优化无法找出最优解时，就需要用到工具了。
　　更多更详细的Maple基本操作及功能介绍请访问
　　下面是一个例子，使用线性方法不能发现全局最小值，但全局求解器可以发现最优值。
　　考虑一个非线性方程组：
　　最小二乘误差函数有多个极值。
　　为了求最小二乘误差的全局最小值，定义要优化的目标函数和约束。
　　首先，使用Maple内置的局部优化函数包（Optimization）得到局部优化解。由于方程组的复杂性，线性优化求解器并不能发现可行的解。
　　而全局优化算法可以发现全局最小值。
　　代入约束函数，结果显示最小二乘逼近是相当精确的解。也就是说，发生在该最小点上的最小二乘逼近的误差非常小。
　　以上内容向大家介绍了Maple全局优化工具一个使用过程，在线性优化即局部优化无法满足时使用的情况。Maple函数包和工具包非常丰富，可以满 足各种不同的简单或者复杂的计算过程，这也是它成为工程计算软件的原因之一。如果需要了解有关Maple矩阵计算的问题，可以参考教程：
欢迎举报抄袭、转载、暴力色情及含有欺诈和虚假信息的不良文章。
请先登录再操作
请先登录再操作
微信扫一扫分享至朋友圈
搜狐公众平台官方账号
生活时尚&搭配博主 /生活时尚自媒体 /时尚类书籍作者
搜狐网教育频道官方账号
全球最大华文占星网站-专业研究星座命理及测算服务机构
理工男是大学里被认为宅而木讷的群体。指学理工科出身又拥有高...
1047文章数
主演：黄晓明/陈乔恩/乔任梁/谢君豪/吕佳容/戚迹
主演：陈晓/陈妍希/张馨予/杨明娜/毛晓彤/孙耀琦
主演：陈键锋/李依晓/张迪/郑亦桐/张明明/何彦霓
主演：尚格?云顿/乔?弗拉尼甘/Bianca Bree
主演：艾斯?库珀/ 查宁?塔图姆/ 乔纳?希尔
baby14岁写真曝光
李冰冰向成龙撒娇争宠
李湘遭闺蜜曝光旧爱
美女模特教老板走秀
曝搬砖男神奇葩择偶观
柳岩被迫成赚钱工具
大屁小P虐心恋
匆匆那年大结局
乔杉遭粉丝骚扰
男闺蜜的尴尬初夜
客服热线：86-10-
客服邮箱：}