大表，查询速度慢

dwthyt8l 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(441)

这是一个灯具项目。为了便于说明，我将使用一个简化的问题：

create table table1 (
  id int unsigned primary key,
  mail_zip varchar(9),
  index (mail_zip(5))
);

create table table2 (
  name varchar(255),
  zip varchar(5)
);

select table1.id from table1
where substring(mail_zip, 1, 5) in
  (select zip from table2 where name = 'test');

表1包含超过500万条9位邮政编码的记录。对于一个特定的table2.name，表2通常只有不到10行，并且只使用5位的邮政编码。此查询花费了令人无法接受的长时间。在我的实际代码中，table1是一个包含100多列的国家数据库的副本。我想尽量保持该表和国家数据库之间的等价性，这样我就可以避免添加列或将zip压缩到5位；不过，我的默认计划是，在insert时只将邮件压缩到前5位，以避免使用substring（），我认为这是个问题，除非有人有更好的主意。
编辑：不幸的是，下面的大多数建议并没有导致任何明显的改善，除了粘性位的。我最初写的查询需要3分钟多一点。其他大多数建议也是如此。粘性钻头把时间降到了3.5秒。将表1中的mail\u zip字段截断为5位数，将查询时间减少到0.06秒。虽然我希望我的本地表与国家数据库完全匹配，但我很难通过删除邮政编码的最后4位来看到应用程序中的任何实际功能损失，所以我将这样做。

sql mysql

来源：https://stackoverflow.com/questions/51974973/large-table-slow-query

4条答案

按热度按时间

pu82cl6c1#

你可以试着用 INNER JOIN 用一个 LIKE .

SELECT DISTINCT
       table1.id
       FROM table1
            INNER JOIN table2
                       ON table1.mail_zip LIKE concat(table2.zip, '%')
            WHERE table2.name = 'test';

将函数的使用转移到较小表的列上。
为了支持这一点，还要在上创建一个复合索引 table1 (mail_zip, id) （不要限制 mail_zip ).

CREATE INDEX table1_mail_zip_id
             ON table1
                (mail_zip,
                 id);

也许还有另一个索引 table2 (name, zip) . 不过，我想如果只有10排的话，那一排也不会有多大区别 table2 .

CREATE INDEX table2_name_zip
             ON table2
                (name,
                 zip);

就像上面的索引一样 table1 可能会被发现（在我的测试中是这样的，但我没有数据，所以这并不能说明什么）。有一个惩罚 DISTINCT 尽管如此，但我预计指数的使用将大大超过这一点。

赞(0）回复(0）举报 2021-06-20

ajsxfq5m2#

试试这个

select table1.id from table1
INNER JOIN table2
ON table1.mail_zip LIKE CONCAT(table2.zip,'%')
WHERE name = 'test';

赞(0）回复(0）举报 2021-06-20

h22fl7wq3#

问题出在过滤器的“左侧表达式”中。
子字符串（邮件压缩，1，5）在。。。
一般来说，等式左侧的表达式可以/将不使用索引。典型的解决方案是重新表述查询，但在您的情况下不能。那个简单的解决办法还不在手边。
不过，如果您运行的是MySQL5.7或更高版本，有一种解决方法非常快：
将虚拟列添加到计算5位zip值的表中。
在虚拟列上创建索引。
修改查询以使用虚拟列而不是原始列。
举个例子：

alter table table1 add zip5 varchar(5) 
  generated always as (substring(mail_zip, 1, 5)) virtual;

create index ix1_table1 on table1 (zip5);

select table1.id from table1
where zip5 in
  (select zip from table2 where name = 'test');

赞(0）回复(0）举报 2021-06-20

zzoitvuj4#

你可以试着重写为

select table1.id 
  from table1 t1
 where exists
       ( SELECT 1
           FROM table2 t2
          WHERE substring(t1.mail_zip,1,5) = t2.zip
            AND t2.zip
       );

将其作为exists或join编写实际上可能会使用该索引。
一般来说，如果你必须在条件下做函数
例如，子字符串（t1.mail\u-zip，1,5）=t2.zip
这表明你的模型还有改进的余地。

赞(0）回复(0）举报 2021-06-20

我来回答

大表，查询速度慢

4条答案

相关问题

热门标签

最新问答