减少MySQL查询时间（目前运行24小时，但仍在继续）

Question

我有一个带有三个表的数据库，我需要将第一个表与另外两个表交叉引用以创建第四个合并信息表。所有表格都有一个通用的字段，这是MSISDN（手机/手机号码），至少有10位数字。

表1 - 819248行

表2 - 75308813行

表3 - 17701196行

我想从表1中返回的所有行，并从表2附加某些字段和表3中有一个匹配的MSISDN。 我的查询现在已经运行了24小时以上，我无法知道这样的事情需要多长时间。

这种类型的查询可能是一个常规项目 - 有没有一种方法来显着减少查询时间？

我有索引表2和3与MSISDN和我需要返回的字段。

我的查询是这样的：

create TABLE FinishedData 
select 
    Table1.ADDRESS, table1.POSTAL, table1.MOBILE, 
    table1.FIRST, table1.LAST, table1.MID, table1.CARRIER, 
    table1.TOWN, table1.ID, table2.status as 'status1', 
    table2.CurrentNetworkName as 'currentnetwork1', 
    table2.DateChecked as 'datechecked1', table3.Status as 'status2', 
    table3.CurrentNetworkName 'currentnetwork2', 
    table3.DateChecked as 'datechecked2' 
from 
    table1 left join (table2, table3) 
     on (right(table1.MOBILE, 10) = right(table2.MSISDN, 10) 
     AND right(table1.MOBILE,10) = right(table3.MSISDN,10)) 

MySQL是一个64位Windows机器12GB内存和8个逻辑核心3GHz的@上运行。运行查询时，MySQLd仅使用10％cpu和600MB资源。

任何帮助表示赞赏。

Answer 1

终止性能问题与正确功能当你使用这个函数时，MySQL不能使用索引。

我的建议是：

1. 与reverse内容MSISDN
2. 的创建表2和表3中的新领域让由左功能的加入替换正确的函数。

有了这个小小的改变，MySQL可以通过索引让你的连接成为可能。

解释步骤：

1）创建新的列：

Alter table table2 add column r_MSISDN varchar(200); 
update table2 set r_MSISDN = reverse(MSISDN); 

Alter table table3 add column r_MSISDN varchar(200); 
update table3 set r_MSISDN = reverse(MSISDN); 

2）新加盟：

... 
from 
    table1 left join (table2, table3) 
     on (right(table1.MOBILE, 10) = left(table2.r_MSISDN, 10) 
     AND right(table1.MOBILE,10) = left(table3.r_MSISDN,10)) 

Answer 2

RIGHT是一个函数。在where子句中使用函数意味着MySQL（也可能是任何数据库）不能使用索引，因为它必须在比较之前计算每行的函数返回的值。

如果您希望更快地进行此查询，请考虑将MSISDN存储为规范化格式，并使用=运算符进行比较。

现在我不知道什么MSISDN号码看起来像。如果它是一个固定宽度的数字，那么你的工作很容易。如果它包含分隔符（空格/连字符）并且分隔符仅用于提供可读性，则应在将它们存储到数据库之前将其删除。如果前10个字符很重要，剩余字符可选，则可以考虑将前10个字符和剩余字符存储在不同的列中。

Answer 3

正如其他人已经提到的，问题是right函数不允许使用任何索引。

简而言之，您对table1中每行的当前查询都会对table2进行全面扫描，并且对于每个匹配，都会对table3进行全面扫描。考虑到你在table2和table3中有多少行，你有很大的机会在查询完成之前看到世界。

另一个问题是查询启动了一个巨大的事务，它应该能够像MySQL认为的那样回滚，并且您可能会考虑隔离级别。

虽然我不会改变当前表格。我将创建具有所需列的table2和table3的子表，并将table2.MSISDN（10）作为table2 copy（右（table3.MSISDN，10）中的单独索引列）添加到table3副本中。

然后，您可以对副本执行LEFT JOIN，或者将副本减少到与table1中的任何内容匹配的行，然后执行LEFT JOIN。