By

前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

1、in 和 exist 区别

结论1：IN() 适合 B 表比 A 表数据小的情况

结论2：EXISTS() 适合 B 表比 A 表数据大的情况

1、适用表的类型不同。

in 是子查询为驱动表，外面的表为被驱动表，故适用于子查询结果集小而外面的表结果集大的情况。

exists 是外面的表位驱动表，子查询里面的表为被驱动表，故适用于外面的表结果集小而子查询结果集大的情况。

2、子查询关联不同。

exists 一般都是关联子查询。对于关联子查询，必须先执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。

in 则一般都是非关联子查询，非关联子查询则必须先完成内层查询之后，外层查询才能介入。

3、执行次数不同。

IN 语句：只执行一次，确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

EXISTS语句：执行次数根据表的长度而定。指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

1.1、数据准备

查询中涉及到的两个表，一个 books 和一个 borrow 表，具体表的内容如下：

书单( books )表：

image-20210801163550082

借书表borrow

image-20210801163537096

1.2、IN

1.1.1、sql

SELECT
    *
FROM
    `books`
WHERE
    `books`.bno IN (SELECT `borrow`.bno   FROM   `borrow` )

1.1.2、执行流程

IN() 适合 B 表比 A 表数据小的情况

1、首先查询子查询的表

2、然后将内表和外表做一个笛卡尔积

3、然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

1、　首先，在数据库内部，查询子查询，执行如下代码：

 SELECT  `borrow`.bno  FROM   `borrow`

image-20210801164020292

2、此时，将查询到的结果和原有的 books 表做一个笛卡尔积，结果如下：

image-20210801164107834

3、此时，再根据我们的 books.bno IN borrow.bno 的条件，将结果进行筛选（既比较 bno列和 bno1 列的值是否相等，将不相等的删除）。最后，得到两条符合条件的数据。

image-20210801164142353

1.1.3、结论

以下 SQL 使用了in语句, in() 只执行一次,它查出 B 表中的所有 bno 字段并缓存起来.之后,检查 A 表的 id 是否与B 表中的bno 相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录. 它的查询过程类似于以下过程

当B表数据较大时不适合使用in()

select * from A where id in(select id from B)

List resultSet=[]; 
Array A=(select * from A); 
Array B=(select id from B);
for(int i=0;i<A.length;i++) {    
    for(int j=0;j<B.length;j++) {  
        if(A[i].bno == B[j].bno)     
            resultSet.add(A[i]);          
    }    
} 
return resultSet;

1.3、exist

1.1.1、sql

SELECT a.cno from borrow a, books b 
where a.bno = b.bno and b.bname = '计算方法' 
and 
not EXISTS(
    select * from borrow aa, 
  								books bb 
    where aa.bno = bb.bno and bb.bname = '计算方法习题集' and  aa.cno = a.cno)

1.1.2、执行流程

EXISTS() 适合B 表比 A 表数据大的情况 ，EXISTS 用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False EXISTS 指定一个子查询，检测行 的存在。

1、遍历循环外表

2、然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

1、使用 exists 关键字进行查询的时候，首先，我们先查询的不是子查询的内容，而是查我们的主查询的表，也就是说，我们先执行的sql语句是：

SELECT a.cno from borrow a, books b  where a.bno = b.bno and b.bname = '计算方法'

image-20210801165122474

2、然后，根据表的每一条记录，执行以下语句，依次去判断 where后面的条件是否成立：如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回。

not EXISTS(
        select * from borrow aa, books bb 
        where aa.bno = bb.bno and bb.bname = '计算方法习题集' and            aa.cno = a.cno
            )

1.1.3、结论

以下查询使用 了exists 语 句, exists() 会执行 A.length 次,它并不缓存 exists()结果集,因为 exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false. 它的查询过程类似于以下过程

select a.* from A a where exists(select 1 from B b where a.id=b.id)

List resultSet=[]; 
Array A=(select * from A)
for(int i=0;i<A.length;i++) {    
//执行select 1 from B b where b.id=a.id是否有记录返回  
    if(!exists(A[i].id) {    
        resultSet.add(A[i]);    
    } 
}
return resultSet;

当 B 表比 A 表数据大时适合使用 exists() ,因为它没有那么遍历操作，只需要再执行一次查询就行.

如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.

如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次

因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.

再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次，还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较（个人理解：需要先查出来，然后比较）,而exists()需要查询数据库（个人理解： 每次需要查询一遍数据）,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

结论: in 是把外表和内表作hash 连接，而 exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比 in 效率高的说法是不准确的。exists()适合B表比A表数据大的情况,当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.

1.4、not in 和not exists

⬤ 如果查询语句使用了 not in 那么内外表都进行全表扫描，没有用到索引；

⬤ not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

2、varchar()和 char()

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：

char(M)：每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足

varchar(M)：每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）

​

2.1、使用方面

字符型字段是数据库表中最常见的字段，而字符型字段又分为定长和变长两种。

⬤ VARCHAR类型用于存储内容长度变化较大的数据

⬤ CHAR 类型用于存储内容长度没有变化或变化不大的数据。

2.2、区别

⬤ 在数据的内部存储上，一般VARCHAR型字段会使用1或2个字节作为数据的长度描述，数据的后面不含无意义的空格；而CHAR型字段会将数据的结尾以空格填充，直至填满定义的长度。

⬤ 在数据的操作效率上，UPDATE含有变长字段的变长记录会花费较多时间，而对INSERT、DELETE和SELECT操作则没有明显差别。

⬤ 在数据的索引和匹配上，VARCHAR型字段会将’张三’和’张三 ‘认为是两项不同的数据，而CHAR则认为它们是等同的。

2.3、使用推荐

使用char

1、字段值长度不变或变化不大；

2、所在记录会被频繁修改

1、是考虑其长度的是否相近。

如果某个字段其长度虽然比较长，但是其长度总是近似的，如一般在90个到100个字符之间，甚至是相同的长度。此时比较适合采用CHAR字符类型。比较典型的应用就是MD5哈希值。当利用MD5哈希值来存储用户密码时，就非常使用采用CHAR字符类型。因为其长度是相同的。另外，像用来存储用户的身份证号码等等，一般也建议使用CHAR类型的数据

2、从碎片角度进行考虑

使用CHAR字符型时，由于存储空间都是一次性分配的。为此某个字段的内容，其都是存储在一起的。单从这个角度来讲，其不存在碎片的困扰。而可变长度的字符数据类型，其存储的长度是可变的。当其更改前后数据长度不一致时，就不可避免的会出现碎片的问题。故使用可变长度的字符型数据时，数据库管理员要时不时的对碎片进行整理。如执行数据库导出导入作业，来消除碎片

3、即使使用varchar数据类型，也不能够太过于慷慨。

这是什么意思呢?如现在用户需要存储一个地址信息。根据评估，只要使用100个字符就可以了。但是有些数据库管理员会认为，反正Varchar数据类型是根据实际的需要来分配长度的。还不如给其大一点的呢。为此他们可能会为这个字段一次性分配200个字符的存储空间。

这VARCHAR(100)与VARCHAR(200)真的相同吗?结果是否定的。虽然他们用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，则不是。其时使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。所以如果某些字段会涉及到文件排序或者基于磁盘的临时表时，分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。如果为了考虑冗余，可以留10%左右的字符长度。千万不能认为其为根据实际长度来分配存储空间，而随意的分配长度，或者说干脆使用最大的字符长度。

ContactAuthor