SQL查询性能改进

Question

我想从两个带分页的表的联合中获得结果，目前我使用的是以下查询

SELECT * FROM 
  (SELECT Clients .id, Clients.Name 
      FROM Clients 
      INNER JOIN ClientPolicies ON ClientPolicies .client_id = Clients .id
    UNION
   SELECT c.id,c.Name 
      FROM ClientGroupPolicies cgp 
      JOIN `Groups` g ON g.id = cgp.client_group_id 
      JOIN GroupClients gc ON g.id = gc.groupId 
      JOIN Clients c ON c.id = gc.clientId) AS tb
    LIMIT 0,100

我得到了正确的结果，但可以在单个查询中完成，也可以提高它的性能。任何帮助都将不胜感激。

UPDATE这里是使用EXIST的查询

SELECT
    c2.id,
    c2.user
FROM
    Clients c2
WHERE
    EXISTS(
    SELECT
        ClientPolicies.client_id
    FROM
        ClientPolicies
    WHERE
        ClientPolicies .client_id = c2.id )
    or EXISTS (
    SELECT
        gc.clientId
    FROM
        ClientGroupPolicies cgp
    JOIN Groups g on
        g.id = cgp.client_group_id
    JOIN GroupClients gc on
        g.id = gc.groupId
    WHERE
        gc.clientId = c2.id )
LIMIT 0,
10

联合的结果

id|select_type |table         |partitions|type  |possible_keys   |key      |key_len|ref                         |rows|filtered|Extra                                             |
--|------------|--------------|----------|------|----------------|---------|-------|----------------------------|----|--------|--------------------------------------------------|
 1|PRIMARY     |<derived2>    |          |ALL   |                |         |       |                            |  14|   100.0|                                                  |
 2|DERIVED     |ClientPolicies|          |index |client_FK       |client_FK|5      |                            |   8|   100.0|Using where; Using index                          |
 2|DERIVED     |Clients       |          |eq_ref|PRIMARY         |PRIMARY  |4      |sdp.ClientPolicies.client_id|   1|   100.0|                                                  |
 3|UNION       |cgp           |          |ALL   |                |         |       |                            |   2|   100.0|Using where                                       |
 3|UNION       |g             |          |eq_ref|PRIMARY         |PRIMARY  |4      |sdp.cgp.client_group_id     |   1|   100.0|Using index                                       |
 3|UNION       |gc            |          |ALL   |clientId,groupId|         |       |                            |   3|   100.0|Using where; Using join buffer (Block Nested Loop)|
 3|UNION       |c             |          |eq_ref|PRIMARY         |PRIMARY  |4      |sdp.gc.clientId             |   1|   100.0|                                                  |
  |UNION RESULT|<union2,3>    |          |ALL   |                |         |       |                            |    |        |Using temporary                                   |

存在的结果

id|select_type       |table         |partitions|type  |possible_keys   |key      |key_len|ref           |rows|filtered|Extra                                             |
--|------------------|--------------|----------|------|----------------|---------|-------|--------------|----|--------|--------------------------------------------------|
 1|PRIMARY           |c2            |          |ALL   |                |         |       |              | 159|   100.0|Using where                                       |
 3|DEPENDENT SUBQUERY|gc            |          |ref   |clientId,groupId|clientId |4      |sdp.c2.id     |   1|   100.0|                                                  |
 3|DEPENDENT SUBQUERY|g             |          |eq_ref|PRIMARY         |PRIMARY  |4      |sdp.gc.groupId|   1|   100.0|Using index                                       |
 3|DEPENDENT SUBQUERY|cgp           |          |ALL   |                |         |       |              |   2|    50.0|Using where; Using join buffer (Block Nested Loop)|
 2|DEPENDENT SUBQUERY|ClientPolicies|          |ref   |client_FK       |client_FK|5      |sdp.c2.id     |   1|   100.0|Using index                                       |

Answer 1

联合一切都比联合快。另外，如果将来需要使用WHERE子句，则必须巧妙地使用它，因为WHERE子句对union的结果是一个昂贵的查询。相反，您可以在内部查询中使用它。