MySQL中使用UNION进行两表合并,去重导致效率低下,请问如何优化?

夜雨正狂
  • 13

如下SQL语句在MySQL中执行需要2秒左右的时间,如果使用UNION ALL进行不去重合并只需要0.4秒,UNION ALL配合DISTINCT来去重速度又变成2秒了,请问如何进行优化?

SELECT
    a. KEY,
    a. DATA,
    a.date_added
FROM
    (
        (
            SELECT
                CONCAT('customer_', ca. KEY) AS `key`,
                ca. DATA,
                ca.date_added
            FROM
                `cf_customer_activity` ca
        )
        UNION
            (
                SELECT
                    CONCAT('affiliate_', aa. KEY) AS `key`,
                    aa. DATA,
                    aa.date_added
                FROM
                    `cf_affiliate_activity` aa
            )
    ) a
ORDER BY
    a.date_added DESC
LIMIT 0,
 5;

回复
阅读 13.7k
4 个回答
✓ 已被采纳

看你写的语句key前缀不一样永远不会有重复啊,直接union all

@prolifes 是对的。也许题主没表述清楚。

我补充另外一个思路,因为你最后只需要5条数据,从ca中取5条,再从aa中取5条,总共10条排序,这个会很快。

SELECT
    a. KEY,
    a. DATA,
    a.date_added
FROM
    (
        (
            SELECT
                CONCAT('customer_', ca. KEY) AS `key`,
                ca. DATA,
                ca.date_added
            FROM
                `cf_customer_activity` ca
            ORDER BY 
                a.date_added DESC /* 如果date_added有索引的话,这个会比较快 */ 
            LIMIT 0,5
        )
        UNION ALL
            (
                SELECT
                    CONCAT('affiliate_', aa. KEY) AS `key`,
                    aa. DATA,
                    aa.date_added
                FROM
                    `cf_affiliate_activity` aa
                ORDER BY 
                    a.date_added DESC /* 如果date_added有索引的话,这个会比较快 */ 
                LIMIT 0,5
            )
    ) a
ORDER BY
    a.date_added DESC
LIMIT 0, 5;

当然先是使用QEP

mysql> explain select * from wp_options limit 1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_options
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 136
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

mysql> show session status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |
| Handler_read_key      | 3     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 209   |
+-----------------------+-------+
7 rows in set (0.01 sec)

至于每个字段的具体含义,自己查资料。

SELECT
        CONCAT('customer_', ca. KEY) AS `key`,
        ca. DATA,
        ca.date_added
    FROM
        `cf_customer_activity` ca
UNION
        SELECT
            CONCAT('affiliate_', aa. KEY) AS `key`,
            aa. DATA,
            aa.date_added
        FROM
            `cf_affiliate_activity` aa
        ORDER BY
            a.date_added DESC
        LIMIT 0, 5;

这个语句不是这样写就可以了吗?要distinct就去相应的子句distinct。order by 会给整个结果集排序,union 会对结果集自动去重,只是在这条语句里没有意义

宣传栏