优化原则：小表驱动大表，即小的数据集驱动大的数据集。
############# 原理 (RBO) #####################
1 2 3 4 | select * from A where id in (select id from B)等价于:for select id from Bfor select * from A where A.id = B.id |
当B表的数据集必须小于A表的数据集时，用in优于exists。
1 2 3 4 | select * from A where exists (select 1 from B where B.id = A.id)等价于for select * from Afor select * from B where B.id = A.id |
当A表的数据集系小于B表的数据集时，用exists优于in。
注意：A表与B表的ID字段应建立索引。
例如：
1 2 3 4 | /** 执行时间：0.313s **/SELECT SQL_NO_CACHE * FROM rocky_member m WHERE EXISTS (SELECT 1 FROM rocky_vip_appro a WHERE m.ID = a.user_id AND a.passed = 1);/** 执行时间：0.160s **/SELECT SQL_NO_CACHE * FROM rocky_member m WHERE m.ID in(SELECT ID FROM rocky_ |
mysql存储
创建一个存储p1()
1 2 3 4 5 6 7 8 9 10 | mysql> \d //mysql> create procedure p1()-> begin-> set @i=0;-> while @i<10 do-> select @i;-> set @i=@i+1;-> end while;-> end;-> // |
执行存储p1()
1 2 3 4 5 6 | mysql> \d ;mysql> call p1();--查看procedure p1()的status信息mysql> show procedure status\G--查看procedure p1()的具体信息:mysql> show create procedure p1\G |
mysql触发器
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 修改delimiter为//mysql> \d //创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据 mysql> create trigger tg1 before insert on t1 for each ro>begin>insert into t2(id) values(new.id); >end//--准备两个空表t1和t2mysql> select * from t1;mysql> select * from t2;--向t1表中插入多条数据:mysql> insert into t1 values(1),(2),(3),(4);如何制作删除表t1后t2表中的记录也会跟着删除呢mysql>\d //mysql> create trigger tg2 beforedelete on t1 for each row >begin delete from t2 where id=old.id;>end//mysql>\d ;如何制作更改表t1后t2表中的记录跟着个性呢mysql>\d //mysql> create trigger tg3 beforeupdate on t1 for each row >begin update t2 set id=new.id where id=old.id; >end//mysql>\d ;查看触发器mysql> show triggers; |
本文出自 “cruisezhao” 博客，请务必保留此出处http://132408.blog.51cto.com/122408/1738665
原文：http://132408.blog.51cto.com/122408/1738665