【51CTO.com快譯】為什么數(shù)據(jù)庫導(dǎo)致如此多的性能問題?

我們常常忘了每個(gè)請(qǐng)求與其他請(qǐng)求并非獨(dú)立。如果一個(gè)請(qǐng)求很慢，它不太可能影響其他請(qǐng)求，是這樣嗎?

[[355354]]

數(shù)據(jù)庫是由應(yīng)用程序中運(yùn)行的所有進(jìn)程使用的共享資源。即便只有一處設(shè)計(jì)不當(dāng)?shù)脑L問也可能拖累整個(gè)系統(tǒng)的性能。

本文討論了一些巧妙的SQL查詢方法，已完全改變了我們系統(tǒng)的一些部分，從而提升了應(yīng)用程序性能，最終改善客戶體驗(yàn)。

如果您處理龐大數(shù)據(jù)集、導(dǎo)入/導(dǎo)出過程、數(shù)據(jù)聚合算法以及類似問題，這些解決方案可以幫助您大幅減少應(yīng)用程序消耗的資源并大幅節(jié)省成本。

INSERT on Duplicate Key UPDATE

insert on duplicate key update是鮮為人知的MySQL子句之一，但它保證了在一些特定情況下可顯著提升性能，實(shí)際上確保了客戶體驗(yàn)。

由于這個(gè)子句，您可以指令MySQL運(yùn)行UPDATE語句，以防INSERT語句因表中可能存在重復(fù)鍵而出錯(cuò)。

不妨給出一個(gè)真實(shí)的例子。

CSV導(dǎo)入/導(dǎo)出

假設(shè)從CSV文件導(dǎo)入用戶列表的過程，其中每行都需要有唯一的電子郵件地址。如果電子郵件地址已經(jīng)存在，我們的腳本應(yīng)插入新用戶并更新特定用戶。

該腳本的第一個(gè)版本可能是： 

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(); 
} 

我們針對(duì)每一行驗(yàn)證數(shù)據(jù)庫中是否已存在有特定電子郵件的用戶。如果用戶存在，腳本更新其名稱后保存;如果用戶不存在，腳本將創(chuàng)建User的新實(shí)例，然后繼續(xù)插入。

在該示例中，我們使用Eloquent ORM與數(shù)據(jù)庫進(jìn)行交互;“save()”方法足夠聰明，可以更新記錄(如果不存在)，否則創(chuàng)建記錄。最后，我們運(yùn)行select從數(shù)據(jù)庫獲取用戶，然后執(zhí)行另一個(gè)查詢以INSERT或UPDATE記錄，因此對(duì)CSV文件中的每一行進(jìn)行了兩次查詢。

這意味著對(duì)于擁有500000行的CSV而言，我們需要運(yùn)行100萬次查詢(50萬次選擇，50萬次插入或更新)。

簡(jiǎn)化代碼

Eloquent以及其他所有不錯(cuò)的ORM都提供了某條捷徑來完成這種操作，因此我們可以使用updateOrCreate方法來減少行數(shù)，從而提高可讀性： 

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

該方法其實(shí)有很清楚的名稱，提供了實(shí)用功能，但這還不夠，因?yàn)樗嬖谕瑯拥膯栴}：每一CSV行運(yùn)行兩次查詢。

太多的查詢意味著太多的時(shí)間、CPU和內(nèi)存使用量。我們旨在減少數(shù)據(jù)庫語句的數(shù)量，從而優(yōu)化性能和消耗的資源。

如何使用“on duplicate key”?

該子句好比“try/catch”語句，但面向SQL。這里有個(gè)原始的例子： 

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

它的行為很簡(jiǎn)單：

• 試圖插入有特定信息的記錄;
• 如果沒有錯(cuò)誤，就按往常那樣執(zhí)行插入;
• 如果查詢出現(xiàn)“重復(fù)鍵”錯(cuò)誤，繼續(xù)執(zhí)行所提供的第二個(gè)查詢。

由于該子句，我們可以將“if”語句從PHP移到數(shù)據(jù)庫，把針對(duì)數(shù)據(jù)庫本身發(fā)出的請(qǐng)求數(shù)量減少一半。

不妨更進(jìn)一步

我們還可以使用該SQL語言用于批量操作，以獲得大幅提升性能的效果。我們可以添加多個(gè)INSERT，并使用VALUES函數(shù)來引用正確的字段，比如周期中的變量。 

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

從理論上來說，我們僅用一個(gè)查詢即可導(dǎo)入整個(gè)CSV。

在實(shí)際情況下，查詢有長(zhǎng)度限制，避免一次操作執(zhí)行全部任務(wù)來得比較謹(jǐn)慎，以免內(nèi)存不足錯(cuò)誤。我們可以將CSV分成有1000項(xiàng)的子組，并運(yùn)行內(nèi)有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只是個(gè)例子，基于您服務(wù)器的資源，您可以加大或調(diào)小這個(gè)數(shù)。最重要的是，我們已將查詢數(shù)量從500000次減少至500次。

Eloquent UPSERT 方法

Eloquent ORM提供的upsert方法可為您在底層實(shí)現(xiàn)這種策略。 

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

該方法的第一個(gè)參數(shù)由要插入或更新的值組成，第二個(gè)參數(shù)列出了唯一標(biāo)識(shí)關(guān)聯(lián)表中記錄的列。方法的第三個(gè)也是最后一個(gè)參數(shù)是這種列的數(shù)組：如果數(shù)據(jù)庫中已經(jīng)存在匹配的記錄，應(yīng)更新這些列。

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

結(jié)論

但愿這一個(gè)或多個(gè)技巧可以幫助您開發(fā)出可靠性和可擴(kuò)展性更高的軟件產(chǎn)品。

我已用Eloquent ORM編寫代碼示例，但您可以以同樣的方式對(duì)所有主要的ORM使用該策略。工具應(yīng)幫助我們實(shí)施有效的策略。 戰(zhàn)略性思維是從長(zhǎng)遠(yuǎn)角度看待我們產(chǎn)品的關(guān)鍵。

原文標(biāo)題：How to Accelerate Application Performance With Smart SQL Queries，作者：Valerio Barbera

【51CTO譯稿，合作站點(diǎn)轉(zhuǎn)載請(qǐng)注明原文譯者和出處為51CTO.com】