mysql - 哪种 PDO SQL 查询在长期和大量数据中更快？

Question

从一个表中有超过 百万条记录，当我从中提取数据时，

我想检查请求的数据是否存在，那么哪条路径比另一条路径更有效和更快？

$Query = '
    SELECT n.id 
    FROM names n 
    INNER JOIN ages a ON n.id = a.aid 
    INNER JOIN regions r ON n.id = r.rid 
    WHERE id = :id
';

---

$stmt->prepare($Query);
$stmt->execute(['id' => $id]);
if ($stmt->rowCount() == 1) {
    $row = $stmt->fetch();
    ......................
} else {
    exit();
}

或

$EXISTS = 'SELECT EXISTS (
    SELECT n.fname, n.lname, a.age, r.region 
    FROM names n 
    INNER JOIN ages a ON n.id = a.aid 
    INNER JOIN regions r ON n.id = r.rid 
    WHERE id = :id
    LIMIT 1
)
';
$stmt->prepare($EXISTS);
$stmt->execute(['id' => $id]);
if ($stmt->fetchColumn() == 1) {
    $stmt->prepare($Query);
    $stmt->execute(['id' => $id]);
    $row = $stmt->fetch();
    ......................
} else {
    exit();
}

记住 id 是 PRIMARY (INT) 而 aid, rid 是 INDEXED (INT)

Answer 1

您展示的这两种方法几乎可以肯定是等效的，在性能上几乎没有可衡量的差异。

SELECT n.id 
FROM names n 
INNER JOIN ages a ON n.id = a.aid 
INNER JOIN regions r ON n.id = r.rid 
WHERE id = :id

我假设 names.id 是该表的主键。主键查找非常快。

然后它将对其他两个表进行辅助键查找，这将是一个仅索引访问，因为没有对这些表的其他列的引用。

你应该学习如何使用 EXPLAIN 来分析 MySQL 的优化计划。每当您想提高 SQL 查询的性能时，您都应该练习这项技能。

参见 https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

mysql> explain SELECT n.id 
    ->     FROM names n 
    ->     INNER JOIN ages a ON n.id = a.aid 
    ->     INNER JOIN regions r ON n.id = r.rid 
    ->     WHERE id = 1;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | n     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | a     | NULL       | ref   | aid           | aid     | 5       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | r     | NULL       | ref   | rid           | rid     | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

我们看到每个表访问都使用索引(我假设索引，虽然你没有在你的问题中提供你的 SHOW CREATE TABLE)。

比较第二个解决方案SELECT EXISTS(...)

mysql> explain SELECT EXISTS (
    ->     SELECT n.id 
    ->     FROM names n 
    ->     INNER JOIN ages a ON n.id = a.aid 
    ->     INNER JOIN regions r ON n.id = r.rid 
    ->     WHERE id = 1 
    ->     LIMIT 1);

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra          |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+----------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | No tables used |
|  2 | SUBQUERY    | n     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index    |
|  2 | SUBQUERY    | a     | NULL       | ref   | aid           | aid     | 5       | const |    1 |   100.00 | Using index    |
|  2 | SUBQUERY    | r     | NULL       | ref   | rid           | rid     | 5       | const |    1 |   100.00 | Using index    |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+----------------+

子查询看起来与第一个查询优化计划相同；它仍然以相同的方式使用索引。但它被降级为子查询。可能差别不大，但又是一回事。

唯一的优点是 SELECT EXISTS... 查询保证只返回一行带有 true/false 值。第一个查询可能会返回包含零行、一行或多行的结果集，具体取决于查询中有多少行与 JOIN 匹配。区别不是性能差异(除非它返回的行太多以至于将结果集传输到客户端需要时间，或者使用大量内存来保存客户端中的结果集)，而只是为了方便你编码的方式。