mysql - 关于select ... in的sql性能

Question

Mysql 5.7.21

我使用池连接数据库并运行 SQL

let mysql = require('mysql');
let pool = mysql.createPool(db);
pool.getConnection((err, conn) => {
      if(err){
        ...
      }else{
        console.log('allConnections:' + pool._allConnections.length);
        let q = conn.query(sql, val, (err, rows,fields) => {
        ...

我有一个包含大约 1,000,000 条记录的表。我写了一个选择来影响记录。

select * from tableA where trackingNo in (?)

我将通过数组参数发送trackingNo。 TrackingNo的数量在20000左右，这意味着数组的长度在20000左右。

我为trackingNo 列创建了索引。 (trackingNo列是varchar类型，不唯一，可以为null、空白以及所有可能的值)

问题是，我发现大约需要 5 分钟才能得到结果!这里的5分钟意味着纯粹的后端sql处理时间。我认为在1,000,000条记录中匹配20000条记录太慢了。您对 select.. in 有什么建议吗？

解释SQL:

id  select_type table   partitions  type    possible_keys          key    key_len   ref   rows   filtered   Extra
1   SIMPLE      tableA  null        ALL     table_tracking_no_idx  null   null      null  999507    50      Using where

Answer 1

您可以考虑使用您想要匹配的跟踪号码填充表格。然后，您可以使用内部联接来代替当前的 WHERE IN 方法:

SELECT *
FROM tableA a
INNER JOIN tbl b
    ON a.trackingNo = b.trackingNo;

这样做的好处是，您可以在 trackingNo 列上对新的 tbl 表建立索引，从而使联接查找速度极快。

这假设 tbl 将有一个列 trackingNo，其中包含您需要考虑的 20K+ 值。