EXISTS 和 IN 的区分

问题

不太理解哪种情况应该使用EXISTS，哪种情况应该使用IN。选择的标准是看能否使用表的索引吗？

回答

索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下的效率是最高的。

比如下面这样：

1
2
3

select * from A where cc IN (select cc from B)

select * from A where EXISTS (select cc from B where B.cc = A.cc)

当A小于B时，用EXISTS。因为EXISTS的实现，相当于外表循环，实现的逻辑类似于：

1
2
3

for i in A
    for j in B
        if j.cc == i.cc then ...

当B小于A时用IN，因为实现的逻辑类似于：

1
2
3

for i in B
    for j in A
        if j.cc == i.cc then ...

哪个表小就用哪个表来驱动，A表小就用EXISTS，B表小就用IN。

in是把外表和内表作hash连接
而exists是对外表作loop循环，每次loop循环再对内表进行查询，关联子查询

小结

如果查询的两张表大小相当，那么用in和exists差别不大；
如果两个表中一个较小一个较大，则子查询表大（内表大）的用exists；
如果两个表中一个较小一个较大，自查询表小（内表小）的用in。

COUNT(*) 与 COUNT(具体字段) 效率

问题

在MySQL中统计数据表的行数，可以使用三种方式：select count(*)、select count(1)、select count(具体字段)，使用这三者之间的查询效率是怎样的？

回答

前提：如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。

环节1: count(*) 和 count(1) 都是对所有结果进行count，count(*) 和 count(1) 本质上并没有区别（二者执行时间可能略有差别，不过你还是可以把他俩的执行效率看成是相等的）。如果有where子句，则是对所有符合筛选条件的数据进行统计；如果没有where子句，则是对数据表的数据行数进行统计。

环节2: 如果是MyISAM存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则由表级锁来保证。

如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，是O(n)的复杂度，进行循环+计数的方式来完成统计。

环节3: 在InnoDB引擎中，如果采用count(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于count(*) 和 count(1) 来说，它们不需要查找具体的行，只统计行数，系统会自动采用占用空间更小的二级索引来进行统计。如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

关于SELECT *

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用select <字段列表> 查询。原因：

MySQL在解析的过程中，会通过查询数据字典将”*“按序转换成所有列明，这会大大的耗费资源和时间。
无法使用覆盖索引

LIMIT 1 对优化的影响

针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描，这样就会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会扫描全表的话，就不需要加上LIMIT 1了。

多使用COMMIT

只要有可能，在程序中尽量多使用commit，这样程序的性能得到提高，需求也会因为commit所释放的资源而减少。

commit所释放的资源：

回滚段上用于恢复数据的信息
被程序语句获得的锁
redo/undo log buffer中的空间
管理上述3种资源中的内部花费

Jlin's Blog

MySQL调优-8-其他查询优化策略

EXISTS 和 IN 的区分

问题

回答

小结

COUNT(*) 与 COUNT(具体字段) 效率

问题

回答

关于SELECT *

LIMIT 1 对优化的影响

多使用COMMIT