MySQL:子查询或联合? [英] MySQL: subquerys OR unions?
问题描述
查询的逻辑我就不解释了，因为不简单.如果我详细解释 - 没有人愿意阅读并深入研究查询的本质.
I do not explain the logic of the query, because it is not simple. If I explain in detail - no one will want to read and delve into the essence of the query.
有两个查询.他们做得很好.结果:相同.在手动模式下检查，用铅笔在纸上(和数据库).
There are two querys. They do their job well. Result: identical. Checked in manual mode, with a pencil on paper (and on a data base).
哪个查询会减少服务器的负载?还是只有在真正的(生产)服务器上工作一段时间后才会被发现?
Which query will less load the server? Or will it only be found out some time after working on a real (production) server?
操作UNION很难加载服务器吗?我应该在查询的解释中寻找什么?
The operation UNION veryhard load the server? What should I look for in the explanations of the querys?
1 q 与 + + +
1 q whith + + +
select (select count(comid) from coms join posts on pid=pid_coms where uid_posts=8888 and uid_coms=8888)
+
(select count(comid) from frends join posts on sl_frend=uid_posts join coms on pid=pid_coms
where uid_coms=8888 and m_frend=8888 and ((postacc=1 and postcomacc=2) or (postacc=2 and postcomacc=2) or (postacc=2 and postcomacc=1)))
+
(select count(comid) from frends join posts on m_frend=uid_posts join coms on pid=pid_coms
where uid_coms=8888 and sl_frend=8888 and ((postacc=1 and postcomacc=2) or (postacc=2 and postcomacc=2) or (postacc=2 and postcomacc=1)))
+
(select count(comid) from coms join posts on pid_coms=pid where uid_posts != 8888 and uid_coms=8888 and postacc=1 and postcomacc=1) CountMyComms;
EXPLAIN
+----+-------------+--------+--------+-----------------------+----------+---------+---------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+-----------------------+----------+---------+---------------------+------+----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 5 | SUBQUERY | coms | ref | uid_coms,pid_coms | uid_coms | 4 | const | 7 | |
| 5 | SUBQUERY | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
| 4 | SUBQUERY | frends | ref | m_frend,sl_frend | sl_frend | 4 | const | 1 | |
| 4 | SUBQUERY | coms | ref | uid_coms,pid_coms | uid_coms | 4 | const | 7 | |
| 4 | SUBQUERY | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
| 3 | SUBQUERY | coms | ref | uid_coms,pid_coms | uid_coms | 4 | const | 7 | |
| 3 | SUBQUERY | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
| 3 | SUBQUERY | frends | ref | m_frend,sl_frend | sl_frend | 4 | mbs.posts.uid_posts | 1 | Using where |
| 2 | SUBQUERY | coms | ref | uid_coms,pid_coms | uid_coms | 4 | const | 7 | |
| 2 | SUBQUERY | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
+----+-------------+--------+--------+-----------------------+----------+---------+---------------------+------+----------------+
11 rows in set (0.00 sec)
2 q 与工会
select count(comid) from (select comid from coms join posts on pid=pid_coms where uid_posts=8888 and uid_coms=8888
union
select comid from frends join posts on sl_frend=uid_posts join coms on pid=pid_coms
where uid_coms=8888 and m_frend=8888 and ((postacc=1 and postcomacc=2) or (postacc=2 and postcomacc=2) or (postacc=2 and postcomacc=1))
union
select comid from frends join posts on m_frend=uid_posts join coms on pid=pid_coms
where uid_coms=8888 and sl_frend=8888 and ((postacc=1 and postcomacc=2) or (postacc=2 and postcomacc=2) or (postacc=2 and postcomacc=1))
union
select comid from coms join posts on pid_coms=pid
where uid_posts != 8888 and uid_coms=8888 and postacc=1 and postcomacc=1) a;
EXPLAIN
+----+--------------+----------------+--------+-----------------------+----------+---------+---------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+--------+-----------------------+----------+---------+---------------------+------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | coms | ref | uid_coms,pid_coms | uid_coms | 4 | | 7 | |
| 2 | DERIVED | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
| 3 | UNION | coms | ref | uid_coms,pid_coms | uid_coms | 4 | | 7 | |
| 3 | UNION | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
| 3 | UNION | frends | ref | m_frend,sl_frend | sl_frend | 4 | mbs.posts.uid_posts | 1 | Using where |
| 4 | UNION | frends | ref | m_frend,sl_frend | sl_frend | 4 | | 1 | |
| 4 | UNION | coms | ref | uid_coms,pid_coms | uid_coms | 4 | | 7 | |
| 4 | UNION | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
| 5 | UNION | coms | ref | uid_coms,pid_coms | uid_coms | 4 | | 7 | |
| 5 | UNION | posts | eq_ref | PRIMARY,pid,uid_posts | PRIMARY | 4 | mbs.coms.pid_coms | 1 | Using where |
| NULL | UNION RESULT | <union2,3,4,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+----------------+--------+-----------------------+----------+---------+---------------------+------+------------------------------+
12 rows in set (0.00 sec)
推荐答案
看起来您正在寻找表的一系列不同查询的记录计数总和.
It looks like you're looking for the sum of record counts for a series of different queries of your tables.
第一个选择......计算每个查询的结果，然后添加它们......会更快.为什么?它要做的工作更少.您的第二个选择必须处理一组 comid 值，然后计算它们.这需要时间.
The first alternative ... count each query's results, then add them ... will be faster. Why? It has less work to do. Your second alternative has to wrangle a set of comid values, and then count them. That takes time.
如果可以，请使用 COUNT(*).这个更便宜.尽可能使用 UNION ALL 而不是 UNION ；UNION 删除重复项，而 UNION ALL 不会.删除重复项需要时间.
Use COUNT(*) if you can. It's cheaper. Use UNION ALL instead of UNION when you can; UNION removes duplicates and UNION ALL doesn't. Removing duplicates takes time.
任一替代方案的性能取决于每个子查询的索引的良好选择.
The performance of either alternative depends on good choices for indexes for each subquery.
这篇关于MySQL:子查询或联合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
