Mysql 搜索高行数

Question

我尝试在 mysql 数据库中导入相对较多的数据(来自文本文件的大约 600 万个条目)。

我必须通过将每个条目与两个文本字段进行比较来检查数据库中是否还没有类似的记录:

`ref` varchar(30) COLLATE utf8_unicode_ci NOT NULL
`labelCanonical` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL

文件按 N 个条目的批处理进行处理(在本例中为 10 个)，我执行一个查询来检查批处理中的所有重复项，如下所示:

SELECT p.`ref`, p.`labelCanonical` 
FROM `rtd_piece` p 
WHERE (p.`ref` = "6569GX" AND p.`labelCanonical` = "fsc-principal") 
  OR (p.`ref` = "6569GY" AND p.`labelCanonical` = "fsc-principal") 
  OR (p.`ref` = "6569GZ" AND p.`labelCanonical` = "fsc-principal") 
  OR (p.`ref` = "6569H0" AND p.`labelCanonical` = "fsc-habitacle") 
  OR (p.`ref` = "6569H1" AND p.`labelCanonical` = "support-fsc") 
  OR (p.`ref` = "6569H2" AND p.`labelCanonical` = "fsc-injection") 
  OR (p.`ref` = "6569H4" AND p.`labelCanonical` = "fsc-injection") 
  OR (p.`ref` = "6569H8" AND p.`labelCanonical` = "faisceau-mot") 
  OR (p.`ref` = "6569H9" AND p.`labelCanonical` = "faisceau-mot") 
  OR (p.`ref` = "6569HA" AND p.`labelCanonical` = "fsc-principal")

我使用 Doctrine 2(没有 Symfony)，并且使用“NativeQuery”执行此查询。

这个问题是，即使数据库中有 600k 条目，此查询也需要 730 毫秒(对于一批 100 条记录需要 6.7 秒)来执行，并且它会增加随着记录添加到数据库中，情况会显着变化。

我目前在“ref”或“labelCanonical”字段上没有索引，并且我不确定添加索引是否会对我所做的请求类型产生任何好处。

我这个方法哪里错了，所以这么慢？

编辑以添加有关流程的更多信息。

我对每个批处理进行 ajax 查询，也是为了向用户提供反馈。在服务器端(PHP)时，我执行以下过程:

1) 我在处理时查找当前文件并提取接下来的 N 条记录

2)我解析每一行并将引用和slugified标签添加到两个不同的数组

3)我尝试从数据库中获取这些记录以避免重复:

$existing = array();
$results = getRepository('Piece')->findExistingPieces($refs, $labels);
for ($i = 0, $c = count($results); $i < $c; ++$i) {
    $existing[] = $results[$i]['ref'].'|'.$results[$i]['labelCanonical'];
}

public function findExistingPieces(array $refs, array $labels)
{
    $sql = '';
    $where = array();
    $params = array();

    for ($i = 0, $c = count($refs); $i < $c; ++$i) {
        $params[] = $refs[$i];
        $params[] = $labels[$i];
        $where[] = '(p.`ref` = ? AND p.`labelCanonical` = ?)';
    }

    $sql = 'SELECT p.`ref`, p.`labelCanonical` '.
           'FROM `rtd_piece` p '.
           'WHERE '.implode(' OR ', $where);

    $rsm = new ResultSetMapping;
    $rsm->addScalarResult('ref', 'ref');
    $rsm->addScalarResult('labelCanonical', 'labelCanonical');

    $query = $this->getEntityManager()
                  ->createNativeQuery($sql, $rsm)
                  ->setParameters($params);
    return $query->getScalarResult();
}

4)我迭代以前解析的数据并检查重复项:

for ($i = 0; $i < $nbParsed; ++$i) {
    $data = $parsed[$i];

    if (in_array($data['ref'].'|'.$data['labelCanonical'], $existing)) {
        // ...
        continue ;
    }
    // Add record
    $piece = new PieceEntity;
    $piece->setRef($data['ref']);
    //...

    $em->persist($piece);
}

5)我在批处理结束时冲洗

我添加了一些“分析”代码来跟踪每个步骤所花费的时间，结果如下:

0.00024509429931641 (0.245 ms) : Initialized
0.00028896331787109 (0.289 ms) : Start doProcess
0.00033092498779297 (0.331 ms) : Read and parse lines
0.0054769515991211 (5.477 ms) : Check existence in database
6.9432899951935 (6,943.290 ms) : Process parsed data
6.9459540843964 (6,945.954 ms) : Finilize
6.9461529254913 (6,946.153 ms) : End of process
6.9464020729065 (6,946.402 ms) : End doProcess
6.9464418888092 (6,946.442 ms) : Return result

第一个数字显示自请求开始以来经过的微秒数，然后是同一时间(以毫秒为单位)，然后是正在执行的操作。

Answer 1

经过一些重构，这就是我得到的:

我使用名为“hash”的新字段检查重复项，如下所示:

$existing = array();
$results = getRepository('Piece')->findExistingPiecesByHashes($hashes);
for ($i = 0, $c = count($results); $i < $c; ++$i) {
    $existing[] = $results[$i]['hash'];
}

public function findExistingPiecesByHashes(array $hashes)
{
    $sql = 'SELECT p.`ref`, p.`labelCanonical`, p.`hash` '.
           'FROM `rtd_piece` p '.
           'WHERE (p.`hash`) IN (?)';

    $rsm = new ResultSetMapping;
    $rsm->addScalarResult('ref', 'ref');
    $rsm->addScalarResult('hash', 'hash');
    $rsm->addScalarResult('labelCanonical', 'labelCanonical');

    $query = $this->getEntityManager()
                  ->createNativeQuery($sql, $rsm)
                  ->setParameters(array($hashes));
    return $query->getScalarResult();
}

哈希值会在模型中自动更新，如下所示:

// Entities/Piece.class.php

private function _updateHash()
{
    $this->hash = md5($this->ref.'|'.$this->labelCanonical);
}

我的哈希字段没有 FULLTEXT 索引，因为我使用 InnoDB 引擎和 MySQL 5.5 版本，据我所知，自 MySQL 5.6 以来，InnoDB 只支持 FULLTEXT 索引。

我现在没有更新MySQL的感觉，太多数据库和网站运行在它上面，如果更新出错那将是灾难性的。

但是，即使没有对字段建立索引，性能增益也是令人难以置信的:

0.00024199485778809 (0.242) : Initialized
0.00028181076049805 (0.282) : Start doProcess
0.0003199577331543 (0.320) : Read and parse lines
0.088779926300049 (88.780) : Check existence in database
0.8656108379364 (865.611) : Process parsed data
0.94273900985718 (942.739) : Finilize
1.3771109580994 (1,377.111) : End of process
1.3795168399811 (1,379.517) : End doProcess
1.3795938491821 (1,379.594) : Return result

这是针对 1000 条批处理，表上有 65 万条记录。

优化前，检查100条记录需要6.7秒，速度提升了9倍左右!

按照这个速度，我应该能够在 1 小时 30 到 2 小时内导入所有数据。

非常感谢您的帮助。