برنامه ها مشکلات مختلفی می‌توانند داشته باشند که عملکرد آن ها را تحت تاثیر قرار دهد. یکی از رایج ترین این مشکلات برای برنامه هایی که با دیتابیس کار می‌کنند n+1 problem است. در واقع این مشکل یک آنتی-پترن (anti-pattern) در نحوه ی دسترسی به دیتابیس است. این مشکل مواقعی رخ می دهد که در یک رابطه ی پدر و فرزندی بعد از گرفتن پدر ها از دیتابیس نیاز داریم که فرزندان آن را نیز از دیتابیس فراخوانی کنیم. این موضوع را در ادامه شرح می دهیم.

فرض کنید که شما تعدادی ماشین دارید که در یک انبار هستند ولی چرخ های آن ها را در جای دیگری نگهداری می‌کنید! حال برای یک نمایشگاه اتوموبیل می‌خواهید ۵ ماشین از بین ماشین ها را انتخاب کنید و به نمایش بگذارید. ابتدا ماشین های بدون چرخ را از انبار خارج می‌کنید. اما الآن نیاز به چرخ های این ماشین ها دارید. در این مرحله دو راه مختلف را می‌توان در پیش گرفت.

در روش اول برای هر ماشین جداگانه چرخ ها را از انبار می‌گیریم و به هر ماشین متصل می‌کنیم. در این روش ۵ مرتبه باید به انبار برویم و چرخ هر ماشین را بیاوریم. اگر فاصله‌ی انبار چرخ‌ها یک ساعت باشد و هر یک ساعت چرخ های یک ماشین ارسال شود ما ۵ ساعت طول می‌کشد تا تمام چرخ ها را در اختیار داشته باشیم. یا می‌توانیم همزمان ۵ کامیون مختلف از انبار ارسال کنیم که هر کدام چرخ های یک ماشین را حمل می‌کنند. به نظر پر هزینه می‌رسد!

راه دومی که می‌توانیم طی کنیم این است که تمامی چرخ های این ۵ ماشین را در یک کامیون ارسال کنیم و در محل تحویل به هر ماشین چرخ های آن را متصل کنیم. منطقی به نظر می رسد، نه؟

این دو روش به صورت زیر در کد دیده می شوند.

روش اول:

cars = get_five_car()

for every car in cars {

    get_tiers_for_car(car)

}

و کوئری های دیتابیس به صورت زیر می باشند:

SELECT * FROM cars WHERE id IN (1,2,3,4,5)

SELECT * FROM tiers WHERE car_id = 1

SELECT * FROM tiers WHERE car_id = 2

SELECT * FROM tiers WHERE car_id = 3

SELECT * FROM tiers WHERE car_id = 4

SELECT * FROM tiers WHERE car_id = 5

همانطور که می‌بینید برای انجام این فرایند ۵ + ۱ کوئری به دیتابیس زده شده است. شاید این عدد کوچک باشد اما مواردی را فرض کنید که تعداد ماشین هایی که می خواهیم بسیار بیشتر از این اعداد باشد. تعداد کوئری ها با رابطه ی n + 1 زیاد می شود؛ یک کوئری برای گرفتن پدر (در اینجا ماشین) و n کوئری برای گرفتن فرزندان (در مثال ما چرخ ها).

روش دوم:

cars = get_five_cars()

tiers = get_tiers_for_all_cars_together(cars)

در این روش تعداد کوئری ها به دو عدد کاهش پیدا می کند.

SELECT * FROM cars WHERE id IN (1,2,3,4,5)

SELECT * FROM tiers WHERE car_id IN (1,2,3,4,5)

نکته مهم این است که در این مثال تعداد کوئری ها فارغ از تعداد ماشین ها همیشه ۲ باقی می ماند.

چند مثال واقعی از وجود n+1 problem و رفع آن

در این قسمت از مقاله چند مثال نشان می دهیم که این مشکل را دارند و روش درست پیاده سازی برای آن ها را نیز ذکر می کنیم.

لیست کردن مقالات موجود در سایت به همراه نویسنده های آن ها

فرض کنید در یک سایت می خواهیم لیست ده مقاله از مقالات سایت را به همراه نویسنده ی آن ها مشاهده کنیم. ساختار خلاصه شده ی دیتابیس پروژه را به صورت زیر در نظر بگیرید.

کد موجود برای این کار به صورت زیر است.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Get 10 articles
$sql = "SELECT id, title, author_id FROM articles LIMIT 10";
$result = mysqli_query($conn, $sql);
$articles = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Show articles' titles with their author name
foreach ($articles as $article) {
    echo $article['title'];

    // Get author of article
    $sql = "SELECT id, name FROM authors WHERE id = " . $article['author_id'];
    $result = mysqli_query($conn, $sql);
    $author =  mysqli_fetch_assoc($result);

    echo $author['name'];
}

mysqli_close($conn);
?>

با تغییر کد و استفاده از راه حل توضیح داده شده می توانیم کد را به صورت زیر بهبود دهیم و به جای ۱۱ کوئری (۱۰ + ۱) می توانیم اطلاعات را با دو کوئری دریافت کنیم.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Get 10 articles
$sql = "SELECT id, title, author_id FROM articles LIMIT 10";
$result = mysqli_query($conn, $sql);
$articles = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Get authors of articles
$articlesIds = array_map(function ($article) {
    return $article['id'];
}, $articles);
$sql = "SELECT id, name FROM authors WHERE id IN (" . implode(',', $articlesIds). ")";
$result = mysqli_query($conn, $sql);
$authors =  mysqli_fetch_all($result, MYSQLI_ASSOC);

// Save authors in an array and use their ids as index
$dictionaryOfAuthors = [];
foreach ($authors as $author) {
    $dictionaryOfAuthors[$author['id']] = $author;
}

// Show articles' titles with their author name
foreach ($articles as $article) {
    echo $article['title'];

    $author = $dictionaryOfAuthors[$article['author_id']];
    echo $author['name'];
}

mysqli_close($conn);
?>

پیاده سازی کامنت های تو در تو و حل مشکل n+1

قابلیت کامنت گذاری در بسیاری از وبسایت ها و اپلیکیشن ها وجود دارد. اگر بتوانیم روی کامنت ها، کامنت بگذاریم در هنگام نشان دادن کامنت های روی یک محتوا می توانیم با n+1 problem مواجه شویم. در مثال قبل فرض کنید که میتوانیم روی مقالات کامنت بگذاریم. همچنین می توانیم روی کامنت هایی که روی مقاله گذاشته شده نیز کامنت بگذاریم. به بیانی دیگر هر مقاله می تواند دو لایه کامنت داشته باشد. حال می خوایم مقاله ی شماره ۱ را به همراه کامنت های آن نشان دهیم. فرض کنید روی مقاله ۱ یک کامنت گذاشته شده و روی این کامنت ۱۰ کامنت دیگر گذاشته شده است. ساختار دیتابیس را به صورت زیر در نظر بگیرید.

ستون commentable_type می تواند دو مقدار ‘article’ و یا ‘comment’ را اختیار کند که نشان می دهد این کامنت روی یک مقاله گذاشته شده است یا روی یک کامنت دیگر. ممکن است در خواندن کامنت های روی کامنت ها با چالش n+1 problem مواجه شویم. کدی که دارای n+1 problem است به صورت زیر می باشد.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Get article number 1
$sql = "SELECT id, title FROM articles WHERE id = '1'";
$result = mysqli_query($conn, $sql);
$article = mysqli_fetch_assoc($result);

echo $article['title'];

// Get comments on article 1
$sql = "SELECT id, body "
    . "FROM comments "
    . " WHERE commentable_type = 'article' AND commentable_id = '1'";
$result = mysqli_query($conn, $sql);
$commentsOnArticle1 = mysqli_fetch_all($result, MYSQLI_ASSOC);

foreach ($commentsOnArticle1 as $commentOnArticle1) {
    echo $commentOnArticle1['body'];

    // Get comments on this comment
    $sql = "SELECT id, body "
        . "FROM comments "
        . "WHERE commentable_type = 'comment' AND commentable_id = " . $commentOnArticle1['id'];
    $result = mysqli_query($conn, $sql);
    $secondLayerComments = mysqli_fetch_all($result, MYSQLI_ASSOC);

    foreach ($secondLayerComments as $secondLayerComment) {
        echo $secondLayerComment['body'];
    }
}

mysqli_close($conn);
?>

با تغییر کد بالا به صورت زیر می توانیم تمام کامنت های لایه ی دوم را با یک کوئری دریافت کنیم.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Get article number 1
$sql = "SELECT id, title FROM articles WHERE id = '1'";
$result = mysqli_query($conn, $sql);
$article = mysqli_fetch_assoc($result);

echo $article['title'];

// Get comments on article 1
$sql = "SELECT id, body "
    . "FROM comments "
    . " WHERE commentable_type = 'article' AND commentable_id = '1'";
$result = mysqli_query($conn, $sql);
$commentsOnArticle1 = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Get all the second layer comments
$commentsIds = array_map(function ($comment) {
    return $comment['id'];
}, $commentsOnArticle1);
$sql = "SELECT id, body, commentable_id "
    . "FROM comments "
    . "WHERE commentable_type = 'comment' AND commentable_id IN " . implode(',', $commentsIds);
$result = mysqli_query($conn, $sql);
$secondLayerComments = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Save second layer comments in an 2D array and use their commentable_id as index for first dimension
$dictionaryOfSecondLayerComments = [];
foreach ($secondLayerComments as $secondLayerComment) {
    $dictionaryOfSecondLayerComments[$secondLayerComment['commentable_id']][] = $secondLayerComment;
}

foreach ($commentsOnArticle1 as $commentOnArticle1) {
    echo $commentOnArticle1['body'];

    foreach ($dictionaryOfSecondLayerComments[$commentOnArticle1['id']] as $secondLayerComment) {
        echo $secondLayerComment['body'];
    }
}

mysqli_close($conn);
?>

جمع بندی

با توجه به اینکه تعداد کوئری های زیاد باعث کند شدن روند پاسخ دهی برنامه ی ما و درگیر شدن دیتابیس با تعداد زیادی کوئری می شود، می بایست به این مشکل همیشه توجه داشت و از بروز دادن آن جلوگیری کنیم. عموما این مشکل زمانی به وجود می‌آید که داده ای را که از دیتابیس فراخوانی کرده ایم در یک حلقه استفاده می کنیم تا داده های دیگری را از دیتابیس دریافت کنیم. بنابراین این موارد را در کد خود بررسی کنید و با استفاده از راه حل ارائه شده از بروز این مشکل جلوگیری کنید.

امیدوارم این مطلب براتون مفید بوده باشد و با استفاده از آن عملکرد برنامه هایتان را بهبود دهید. در صورتی که سوالی در این مورد دارید خوشحال می شوم مطرح کنید و من سعی می کنم در اسرع وقت پاسخ بدهم.

مشکل n+1 چیست؟ و چطور این مشکل را حل کنیم با مثال های عملی