[大数据]mysql数据库sql优化（七）看这里--其他优化策略

目录

exists和in的区分

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

关于select(*)

Limit 1 对优化的影响

多使用commit

exists和in的区分

问题：不太理解哪种情况下应该使用exists，哪种情况下使用in。选择的标准是看能否使用表的索引吗？

回答：

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

比如下面这样

select * from A where cc in( select cc from B)
select * from A where exists (select cc from B where A.cc=B.cc)

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

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

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

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

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

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

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

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

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

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

如果是innodb存储引擎，因为innodb支持事务，采用行级锁和mvcc机制，所以无法myisam一样，维护一个row_count变量，因此需要采用全表扫描，进行循环+计数的方式来完成统计。

环节三：在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中资源中的内部花费