【51CTO.com快譯】為什么數據庫導致如此多的性能問題?

我們常常忘了每個請求與其他請求并非獨立。如果一個請求很慢，它不太可能影響其他請求，是這樣嗎?

[[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']); 

該方法的第一個參數由要插入或更新的值組成，第二個參數列出了唯一標識關聯表中記錄的列。方法的第三個也是最后一個參數是這種列的數組：如果數據庫中已經存在匹配的記錄，應更新這些列。

為了使該方法發揮作用，要求upsert方法的第二個參數中的列具有“主”或“唯一”索引。

結論

但愿這一個或多個技巧可以幫助您開發出可靠性和可擴展性更高的軟件產品。

我已用Eloquent ORM編寫代碼示例，但您可以以同樣的方式對所有主要的ORM使用該策略。工具應幫助我們實施有效的策略。 戰略性思維是從長遠角度看待我們產品的關鍵。

原文標題：How to Accelerate Application Performance With Smart SQL Queries，作者：Valerio Barbera

【51CTO譯稿，合作站點轉載請注明原文譯者和出處為51CTO.com】