如何使用智能SQL查询提升应用程序性能？-编程学习网

我们常常忘了每个请求与其他请求并非独立。如果一个请求很慢，它不太可能影响其他请求，是这样吗?

[[355354]]

数据库是由应用程序中运行的所有进程使用的共享资源。即便只有一处设计不当的访问也可能拖累整个系统的性能。

本文讨论了一些巧妙的SQL查询方法，已完全改变了我们系统的一些部分，从而提升了应用程序性能，最终改善客户体验。

如果您处理庞大数据集、导入/导出过程、数据聚合算法以及类似问题，这些解决方案可以帮助您大幅减少应用程序消耗的资源并大幅节省成本。

INSERT on Duplicate Key UPDATE

insert on duplicate key update是鲜为人知的MySQL子句之一，但它保证了在一些特定情况下可显著提升性能，实际上确保了客户体验。

由于这个子句，您可以指令MySQL运行UPDATE语句，以防INSERT语句因表中可能存在重复键而出错。

不妨给出一个真实的例子。

CSV导入/导出

假设从CSV文件导入用户列表的过程，其中每行都需要有唯一的电子邮件地址。如果电子邮件地址已经存在，我们的脚本应插入新用户并更新特定用户。

该脚本的第一个版本可能是：

PHP 
// CSV file content 
$csv = [...]; 
foreach($csv as $row) 
{ 
    $user = User::firstWhere('email', $row['email']); 
    if(!$user) { 
        $user = new User() 
    } 
    $user->name = $row['name']; 
    // "save" method is smart enough to update a record if it does not exists, otherwise create. 
    $user->save(); 
}

我们针对每一行验证数据库中是否已存在有特定电子邮件的用户。如果用户存在，脚本更新其名称后保存;如果用户不存在，脚本将创建User的新实例，然后继续插入。

在该示例中，我们使用Eloquent ORM与数据库进行交互;“save()”方法足够聪明，可以更新记录(如果不存在)，否则创建记录。最后，我们运行select从数据库获取用户，然后执行另一个查询以INSERT或UPDATE记录，因此对CSV文件中的每一行进行了两次查询。

这意味着对于拥有500000行的CSV而言，我们需要运行100万次查询(50万次选择，50万次插入或更新)。

简化代码

Eloquent以及其他所有不错的ORM都提供了某条捷径来完成这种操作，因此我们可以使用updateOrCreate方法来减少行数，从而提高可读性：

// CSV file content 
$csv = [...]; 
foreach($csv as $row) 
 { 
    User::updateOrCreate( 
        // Identify record by this columns 
        [ 
            'email' => $row['email'] 
        ], 
        // Other fields to fill 
        [ 
            'name' => $row['email'] 
        ] 
    ]);

该方法其实有很清楚的名称，提供了实用功能，但这还不够，因为它存在同样的问题：每一CSV行运行两次查询。

太多的查询意味着太多的时间、CPU和内存使用量。我们旨在减少数据库语句的数量，从而优化性能和消耗的资源。

如何使用“on duplicate key”?

该子句好比“try/catch”语句，但面向SQL。这里有个原始的例子：

INSERT INTO users (email, name)  
    VALUES ('support@inspector.dev', 'Valerio') 
ON DUPLICATE KEY  
    UPDATE users SET name = 'Valerio';

它的行为很简单：

试图插入有特定信息的记录;
如果没有错误，就按往常那样执行插入;
如果查询出现“重复键”错误，继续执行所提供的第二个查询。

由于该子句，我们可以将“if”语句从PHP移到数据库，把针对数据库本身发出的请求数量减少一半。

不妨更进一步

我们还可以使用该SQL语言用于批量操作，以获得大幅提升性能的效果。我们可以添加多个INSERT，并使用VALUES函数来引用正确的字段，比如周期中的变量。

INSERT INTO users (email, name)  
    VALUES 
        ('support@inspector.dev', 'Valerio'), 
        ('support@inspector.dev', 'Valerio Barbera'), 
        ('frank@gmail.com', 'Frank'), 
        ('seb@gmail.com', 'Sebastian') 
ON DUPLICATE KEY  
    UPDATE users SET name = VALUES(name);

从理论上来说，我们仅用一个查询即可导入整个CSV。

在实际情况下，查询有长度限制，避免一次操作执行全部任务来得比较谨慎，以免内存不足错误。我们可以将CSV分成有1000项的子组，并运行内有1000次INSERT的查询：

// CSV file content 
$csv = [...]; 
$chunks = array_chunk($csv, 1000); 
foreach($chunks as $chunk) { 
    foreach($chunk as $row) { 
        User::updateOrCreate( 
            // Identify record by this columns 
            [ 
                'email' => $row['email'] 
            ], 
            // Other fields to fill 
            [ 
                'name' => $row['email'] 
            ] 
        ]); 
    } 
}

1000只是个例子，基于您服务器的资源，您可以加大或调小这个数。最重要的是，我们已将查询数量从500000次减少至500次。

Eloquent UPSERT 方法

Eloquent ORM提供的upsert方法可为您在底层实现这种策略。

User::upsert([ 
    ['email' => 'support@inspector.dev', 'name' => 'Valerio', 'age' => 25], 
    ['email' => 'support@inspector.dev', 'name' => 'Valerio Barbera', 'age' => 35] 
], ['email'], ['name', 'age']);