mysql - View 上的 MYSQL SELECT 性能问题

Question

我遇到了 MYSQL 的 SELECT 性能问题。

我有两个表“域”和“电子邮件”，其中包含重复项，这些表经常由不同的源更新(插入/删除)(大约每十分钟一次)。

我的主要目标是从这些表中创建两个没有任何重复的 View 。我知道 View 是一个存储的查询，但这是我保持其动态的唯一方法，每几十分钟创建一个不重复的新表会很疯狂(也许不是？)。

这两个 View 都被另一个线程(后缀)用来检查收件人是否是允许的。当我尝试做一个简单的查询时

SELECT email FROM emailview WHERE email = 'john@google.com'` 

查询需要 3-4 秒。相反，如果我直接在电子邮件表上执行 SELECT(包含重复项)，则需要 0.01 秒。

如何提高系统上的 SELECT 性能，以获得与 View 几乎相似的结果，而不是直接在表上？

以下是架构的详细信息(INNODB 引擎，值 1 是随机的，并不重要):

域名表:

| field        | type         | null | key  |
|--------------|--------------|------|------|
| domain       | varchar(255) | NO   | NULL |
| creationdate | datetime     | NO   | NULL |
| value 1      | varchar(255) | NO   | NULL |
| source_fkey  | varchar(255) | MUL  | NULL |

| domain     | creationdate        | value 1 | source_fkey |
|------------|---------------------|-----------------------|
| google.com | 2013-05-28 15:35:01 | john    | Y           |
| google.com | 2013-04-30 12:10:10 | patrick | X           |
| yahoo.com  | 2011-04-02 13:10:10 | britney | Z           |
| ebay.com   | 2012-02-12 10:48:10 | harry   | Y           |
| ebay.com   | 2013-04-15 07:15:23 | bill    | X           |

域 View (使用最早的创建日期删除重复的域):

CREATE VIEW domainview AS 
  SELECT domain.domain, creationdate, value1, source_fkey 
    FROM domain 
    WHERE (domain, creationdate) IN (SELECT domain, MIN(creationdate) 
                                       FROM domain GROUP BY domain);

| domain     | creationdate        | value 1 | source_fkey |
|------------|---------------------|-----------------------|
| google.com | 2013-04-30 12:10:10 | patrick | X           |
| yahoo.com  | 2011-04-02 13:10:10 | britney | Z           |
| ebay.com   | 2012-02-12 10:48:10 | harry   | Y           |

电子邮件表:

| field        | type         | null | key  |
|--------------|--------------|------|------|
| email        | varchar(255) | NO   | NULL |
| source_fkey  | varchar(255) | MUL  | NULL |


| email              | foreign_key |
|--------------------|-------------|
| john@google.com    | X           |
| john@google.com    | Y           |   <-- duplicate from wrong foreign/domain
| harry@google.com   | X           |
| mickael@google.com | X           |
| david@ebay.com     | Y           |
| alice@yahoo.com    | Z           |

电子邮件 View (合法电子邮件和来自域 View 的domain/foreign_key的电子邮件):

CREATE VIEW emailview AS 
  SELECT email.email, email.foreign_key 
    FROM email, domainview 
    WHERE email.foreign_key = domainview.foreign_key 
      AND SUBSTRING_INDEX(email.email,'@',-1) = domainview.domain;

| email              | foreign_key |
|--------------------|-------------|
| john@google.com    | X           |
| harry@google.com   | X           |
| mickael@google.com | X           |
| david@ebay.com     | Y           |
| alice@yahoo.com    | Z           |

没有唯一性，没有索引，唯一的主键在foreign_key所在的表中。

感谢您的帮助。

之前的讨论:Select without duplicate from a specific string/key

Answer 1

两个查询都很慢 - 首先是因为 IN 子句中的子查询 - 直到 MySQL 5.6 才对其进行优化；第二个是因为在 where 子句中使用了函数。

在第一个查询中，您可以用联接替换子选择在第二种情况下，最好将域存储在单独的列中并使用它进行比较

确保在连接、where 和 group by 子句中使用的字段上有复合索引