长期使用大量数据时，哪种PDO SQL查询速度更快? [英] Which PDO SQL Query is faster in the long run and heavy data?

问题描述

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

From a Table has over a million record, When i pull the data from it,

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

I want to check if the requested data exists or not, So which path is more efficient and faster then the other?

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

推荐答案

您展示的两种方法几乎可以肯定是等效的，而性能几乎没有可测量的差异.

The two methods you show are almost certainly equivalent, with almost no measurable difference in performance.

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是该表的主键.主键查找非常快.

I assume names.id is the primary key of that table. A primary key lookup is very fast.

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

Then it will do a secondary key lookup to the other two tables, and it will be an index-only access because there's no reference to other columns of those tables.

您应该学习如何使用EXPLAIN分析MySQL的优化计划.要想提高SQL查询的性能，您应该在任何时候都应该练习这项技能.

You should learn how to use EXPLAIN to analyze MySQL's optimization plan. This is a skill you should practice any time you want to improve the performance of an SQL query.

请参见 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.)

We see that each table access is using an index (I'm assuming indexes though you did not provide your SHOW CREATE TABLE in your question).

与使用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    |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+----------------+

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

The subquery looks identical to the first query optimization plan; it still uses indexes in the same way. But it's relegated to a subquery. Probably not a big difference, but it's one more thing.

唯一的优点是，确保SELECT EXISTS...查询仅返回具有true/false值的一行.第一个查询可能返回一个包含零行，一行或多行的结果集，具体取决于查询中与JOIN匹配的行数.差异不是性能差异(除非返回的行太多，以至于需要花费时间将结果集传输到客户端，或者使用大量内存将结果集保存在客户端中)，但这仅仅是为了方便编码方式.

The only advantage is that the SELECT EXISTS... query is guaranteed to return just one row with a true/false value. The first query might return a result set with zero, one, or many rows, depending how many matched the JOINs in the query. The difference is not a performance difference (unless it returns so many rows that it takes time to transfer the result set to the client, or uses a lot of memory to hold the result set in the client), but just a matter of convenience for the way you code it.

这篇关于长期使用大量数据时，哪种PDO SQL查询速度更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！