delete语句能不能改一下? delete from student where student.cid in (select id from class where gid=2) 你看这样还会锁整个class表吗?
这种子查询的方式,不说锁表的问题,光是这样的方式,SQL就很慢, 而且用in去组合条件,更慢
是这样的,因为这个数据量会很大,而且还是多线程并发的,所以,对SQL的要求比较高一些。
刚才我试了一下 delete from student where student.cid in (select id from class where class.gid=2); 这种方式不会锁表。 有没有速度更快的方法呢? 还有delete from student where student.cid in (select id from class where class.gid=2);delete student from student inner join class on student.cid=class.id where class.gid=2;以上两种方法,执行时的内部机制有何不同?那个速度更快呢?
我试了一下 对种方式进行了测试,测试结果如下: "Query_ID" "Duration" "Query" "148" "0.00193525" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "149" "0.00035400" "select count(1) from student where student.cid in (select id from class )" "150" "0.00036700" "select count(1) from student inner join class on student.cid=class.id" "151" "0.00032275" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "152" "0.00044425" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "153" "0.00045450" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "154" "0.00006125" "#----" "155" "0.00027450" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "156" "0.00061000" "select count(1) from student where student.cid in (select id from class )" "157" "0.00024175" "select count(1) from student inner join class on student.cid=class.id" "158" "0.00047975" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "159" "0.00031250" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "160" "0.00028850" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "161" "0.00015900" "#----" "162" "0.00088050" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "163" "0.00031225" "select count(1) from student where student.cid in (select id from class )" "164" "0.00026275" "select count(1) from student inner join class on student.cid=class.id" "165" "0.00050000" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "166" "0.00032600" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "167" "0.00028650" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "168" "0.00004675" "#----" "169" "0.00037700" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "170" "0.00027625" "select count(1) from student where student.cid in (select id from class )" "171" "0.00035200" "select count(1) from student inner join class on student.cid=class.id" "172" "0.00076700" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "173" "0.00029550" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "174" "0.00029725" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "175" "0.00004675" "#----" "176" "0.00028400" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "177" "0.00041200" "select count(1) from student where student.cid in (select id from class )" "178" "0.00036325" "select count(1) from student inner join class on student.cid=class.id" "179" "0.00032850" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "180" "0.00031400" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "181" "0.00029900" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "182" "0.00005600" "#----" "183" "0.00027425" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "184" "0.00041625" "select count(1) from student where student.cid in (select id from class )" "185" "0.00026125" "select count(1) from student inner join class on student.cid=class.id" "186" "0.00032500" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "187" "0.00032275" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "188" "0.00029825" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "189" "0.00007475" "#----" "190" "0.00027275" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "191" "0.00028850" "select count(1) from student where student.cid in (select id from class )" "192" "0.00025525" "select count(1) from student inner join class on student.cid=class.id" "193" "0.00032300" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "194" "0.00029925" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "195" "0.00027675" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "196" "0.00005625" "#----" "197" "0.00035900" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "198" "0.00031475" "select count(1) from student where student.cid in (select id from class )" "199" "0.00028700" "select count(1) from student inner join class on student.cid=class.id" "200" "0.00036350" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "201" "0.00036800" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "202" "0.00049050" "select count(1) from student inner join class on student.cid=class.id and class.gid=2" "203" "0.00007950" "#----" "204" "0.00043250" "select count(1) from student where exists (select 1 from class where class.id=student.cid )" "205" "0.00032025" "select count(1) from student where student.cid in (select id from class )" "206" "0.00040925" "select count(1) from student inner join class on student.cid=class.id" "207" "0.00038525" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)" "208" "0.00030850" "select count(1) from student where student.cid in (select id from class where class.gid=2)" "209" "0.00046725" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
Mysql 用的是InnoDB引擎。
版本是5.1.X
delete from student where student.cid in (select id from class where gid=2)
你看这样还会锁整个class表吗?
这种子查询的方式,不说锁表的问题,光是这样的方式,SQL就很慢, 而且用in去组合条件,更慢
delete from student where student.cid in (select id from class where class.gid=2);
这种方式不会锁表。
有没有速度更快的方法呢?
还有delete from student where student.cid in (select id from class where class.gid=2);delete student from student inner join class on student.cid=class.id where class.gid=2;以上两种方法,执行时的内部机制有何不同?那个速度更快呢?
后者是先join, 再where, 所以会锁住。
只要in条件带的子句产生的集合不太大,效率应该还可以。
另外,效率是相对于你的基本性能需求。
对种方式进行了测试,测试结果如下:
"Query_ID" "Duration" "Query"
"148" "0.00193525" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"149" "0.00035400" "select count(1) from student where student.cid in (select id from class )"
"150" "0.00036700" "select count(1) from student inner join class on student.cid=class.id"
"151" "0.00032275" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"152" "0.00044425" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"153" "0.00045450" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"154" "0.00006125" "#----"
"155" "0.00027450" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"156" "0.00061000" "select count(1) from student where student.cid in (select id from class )"
"157" "0.00024175" "select count(1) from student inner join class on student.cid=class.id"
"158" "0.00047975" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"159" "0.00031250" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"160" "0.00028850" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"161" "0.00015900" "#----"
"162" "0.00088050" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"163" "0.00031225" "select count(1) from student where student.cid in (select id from class )"
"164" "0.00026275" "select count(1) from student inner join class on student.cid=class.id"
"165" "0.00050000" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"166" "0.00032600" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"167" "0.00028650" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"168" "0.00004675" "#----"
"169" "0.00037700" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"170" "0.00027625" "select count(1) from student where student.cid in (select id from class )"
"171" "0.00035200" "select count(1) from student inner join class on student.cid=class.id"
"172" "0.00076700" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"173" "0.00029550" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"174" "0.00029725" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"175" "0.00004675" "#----"
"176" "0.00028400" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"177" "0.00041200" "select count(1) from student where student.cid in (select id from class )"
"178" "0.00036325" "select count(1) from student inner join class on student.cid=class.id"
"179" "0.00032850" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"180" "0.00031400" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"181" "0.00029900" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"182" "0.00005600" "#----"
"183" "0.00027425" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"184" "0.00041625" "select count(1) from student where student.cid in (select id from class )"
"185" "0.00026125" "select count(1) from student inner join class on student.cid=class.id"
"186" "0.00032500" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"187" "0.00032275" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"188" "0.00029825" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"189" "0.00007475" "#----"
"190" "0.00027275" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"191" "0.00028850" "select count(1) from student where student.cid in (select id from class )"
"192" "0.00025525" "select count(1) from student inner join class on student.cid=class.id"
"193" "0.00032300" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"194" "0.00029925" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"195" "0.00027675" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"196" "0.00005625" "#----"
"197" "0.00035900" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"198" "0.00031475" "select count(1) from student where student.cid in (select id from class )"
"199" "0.00028700" "select count(1) from student inner join class on student.cid=class.id"
"200" "0.00036350" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"201" "0.00036800" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"202" "0.00049050" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
"203" "0.00007950" "#----"
"204" "0.00043250" "select count(1) from student where exists (select 1 from class where class.id=student.cid )"
"205" "0.00032025" "select count(1) from student where student.cid in (select id from class )"
"206" "0.00040925" "select count(1) from student inner join class on student.cid=class.id"
"207" "0.00038525" "select count(1) from student where exists (select 1 from class where class.id=student.cid and class.gid=2)"
"208" "0.00030850" "select count(1) from student where student.cid in (select id from class where class.gid=2)"
"209" "0.00046725" "select count(1) from student inner join class on student.cid=class.id and class.gid=2"
http://www.itpub.net/thread-1191621-1-1.html
同时感谢各位朋友的建议,十分感谢