1.对于mysql,不推荐使用子查询和join是因为本身join的效率就是硬伤,一旦数据量很大效率就很难保证,强烈推荐分别根据索引单表取数据,然后在程序里面做join,merge数据。
2.子查询就更别用了,效率太差,执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,这里多了一个创建和销毁临时表的过程。
3.如果是JOIN的话,它是走嵌套查询的。小表驱动大表,且通过索引字段进行关联。如果表记录比较少的话,还是OK的。大的话业务逻辑中可以控制处理。
4.数据库是最底层的,瓶颈往往是数据库。建议数据库只是作为数据store的工具,而不要添加业务上去。
多表查询与join的区别:
select a.name,b.data_user from dev_table a,db_schema_group b where a.schema_group_id=b.id limit 100;
select a.name,b.data_user from dev_table a join db_schema_group b on a.schema_group_id=b.id limit 100;
两种方式相同,只是写法不同。多表查询相当于inner join。
select * from t1 straight_join t2 on (t1.a=t2.b); 可指定使用表t1作为驱动表。
能不能使用 join 语句?
1、如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;
2、如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。
判断要不要使用 join 语句时,就是看 explain 结果里面,Extra 字段里面有没有出现“Block Nested Loop”字样。
选择小表做驱动表!!!
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
Nested Loop Join:
Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与 Join,则再通过前两个表的 Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复。
Hash Join:
MySQL 8.0.18 版本增加了一个新的特性hash join,关于hash join,通常其执行过程如下,首先基于join操作的一个表,在内存中创建一个对应的hash表,然后再一行一行的读另外一张表,通过计算哈希值,查找内存中的哈希表。
hash join仅仅在join的字段上没有索引时才起作用,在此之前,我们不建议在没有索引的字段上做join操作,因为通常中这种操作会执行得很慢,但是有了hash join,它能够创建一个内存中的hash 表,代替之前的nested loop,使得没有索引的等值join性能提升很多。
Hash join散列连接是CBO(oracle) 做大数据集连接时的常用方式,优化器使用两个表中较小的表(通常是小一点的那个表或数据源)利用连接键(JOIN KEY)在内存中建立散列表,将列数据存储到hash列表中,然后扫描较大的表,同样对JOIN KEY进行HASH后探测散列表,找出与散列表匹配的行。需要注意的是:如果HASH表太大,无法一次构造在内存中,则分成若干个partition,写入磁盘的temporary segment,则会多一个写的代价,会降低效率。
hash join只能在没有索引的字段上有效;
hash join只在等值join条件中有效;
hash join不能用于left join和right join。
Sort Merge Join:(oracle,mysql不支持)
Merge Join 是先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配。
因为merge join需要做更多的排序,所以消耗的资源更多。 通常来讲,能够使用merge join的地方,hash join都可以发挥更好的性能,即散列连接的效果都比排序合并连接要好。然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序合并连接的性能会优于散列连接。