前言

Github:https://github.com/HealerJean

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

1、inexist 区别

结论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 用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值TrueFalse 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循环再对内表进行查询。一直以来认为existsin 效率高的说法是不准确的。exists()适合B表比A表数据大的情况,当A表数据与B表数据一样大时,inexists效率差不多,可任选一个使用.

1.4、not innot exists

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

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

2、varchar()char()

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

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

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

2.1、使用方面

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

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

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

2.2、区别

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

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

⬤ 在数据的索引和匹配上,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