代码之家 › 专栏 › 技术社区 › Toleo

哪种PDO SQL查询从长远来看更快,数据量更大?

query-optimization pdo mysql

Toleo · 技术社区 · 7 年前

从一张桌子上 百万记录 ,当我从中提取数据时,

我想检查请求的数据存在或者不是,那么哪条路更 有效率的 和更快那么另一个呢?

$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)

2 回复 | 直到 7 年前

Bill Karwin 7 年前

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

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... 查询保证只返回一行的真/假值。第一个查询可能返回一个包含零行、一行或多行的结果集,具体取决于查询中匹配联接的行数。这种差异不是性能差异(除非它返回的行太多,以至于需要花费时间才能将结果集传输到客户端,或者使用大量内存在客户端中保存结果集),而是为了方便您对其进行编码。

Rick James diyism 7 年前

不要使年龄正常化;这只是浪费空间和时间。 age (假设是“年”)可以放入1字节 TINYINT UNSIGNED (范围:0到255)并避免 JOIN 查找。请注意 aid 似乎是4字节 INT ,它可以容纳数十亿个不同的值--你有数十亿个不同的年龄吗?

可能会改变 regions 也是值得的。

在第一个查询中,两个 JOINs 只需验证年龄和地区中是否有行。那就是可能浪费。

EXISTS 找到一行时停止。所以 LIMIT 1 是非常不必要的。