数据库——sql基本查询

一、基本结构

select 列

from 表

where 条件符合条件的列

group by 列

having 符合条件的分组

order by 列

二、执行顺序

select distinct 列

from t1

join t2 on 条件

where 条件符合条件的列

group by 列 having 符合条件的分组

order by 列

limit 行

（1）from t1 join t2 on 条件

（2）where 条件符合条件的列

（3）group by 列 having 符合条件的分组

（4）distinct

（5）order by 列

（6）limit

三、in 和 exists

IN 执行过程

        sql示例：select * from tabA where tabA.x in (select x from tabB where y>0 );
        其执行计划：
        （1）执行tabB表的子查询，得到结果集B，可以使用到tabB表的索引y；
        （2）执行tabA表的查询，查询条件是tabA.x在结果集B里面，可以使用到tabA表的索引x。

Exists执行过程

        sql示例：select from tabA where exists (select from tabB where y>0);
        其执行计划：
        （1）先将tabA表所有记录取到。
        （2）逐行针对tabA表的记录，去关联tabB表，判断tabB表的子查询是否有返回数据，5.5之后的版本使用Block Nested Loop(Block 嵌套循环)。
        （3）如果子查询有返回数据，则将tabA当前记录返回到结果集。
        tabA相当于取全表数据遍历，tabB可以使用到索引。

使用场景

• IN查询在内部表和外部表上都可以使用到索引；
• Exists查询仅在内部表上可以使用到索引；
• 当子查询结果集很大，而外部表较小的时候，Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于IN。
• 当子查询结果集较小，而外部表很大的时候，Exists的Block嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时IN的查询效率会优于Exists。

exists和in的使用方式：　　

1	#对B查询涉及id，使用索引，故B表效率高，可用大表 -->外小内大

2	select * from A where exists (select * from B where A.id=B.id);

3	#对A查询涉及id，使用索引，故A表效率高，可用大表 -->外大内小

4	select * from A where A.id in (select id from B);

　　1、exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；

　　2、in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

　　3、如果用not in ，则是内外表都全表扫描，无索引，效率低，可考虑使用not exists（仍然会使用内表索引），也可使用A left join B on A.id=B.id where B.id is null 进行优化。

　　此外，新近遇到的坑，mysql版本问题：

　　MySQL版本问题：5.6.5优化了子查询，引入物化子查询(针对where clause的subquery)，子查询物化将子查询结果存入临时表，确保子查询只执行一次，该表不记录重复数据且采用哈希索引查找;

        而之前的版本则会把非相关子查询转化为相关子查询，导致效率低下（尤其是子查询是小表，外表是大表的情况下，效率变慢许多）。　　

　　相关子查询：子查询依赖外层连接的返回值；

　　非相关子查询：子查询不依赖外层连接的返回值；

　　子查询分两种，from语句（派生表）和where语句（子查询），派生表的效率要高一些，5.6的优化就是把where语句变成from语句。

　　本来是内表小，用的in，但是据说5.6之前的版本会把非相关子查询改为相关子查询，就是把in的语句改成了exists的，结果效率超低。