Как оптимизировать большой MySQL в цикле foreach в PHP?

Question

Как оптимизировать большой MySQL в цикле foreach в PHP?

У меня есть функция, которая получает информацию о пользователе и возвращает ассоциативный массив, состоящий из массивов каждого пользователя с соответствующими данными. Моя функция работает, за исключением того, что она не работает так же хорошо, когда приходится извлекать большое количество строк из mySQL.

function function_name($DB, $id)
{
//Prepare, bind and execute statements
//Returns one value or an array
}

function main_function($DB, $id_list)
{
foreach($id_list as $user_id)
{
//Calls function_name
$data = function_name($DB, $user_id);
}

//Returns a nested associative array
}

Мне сказали, что в моем случае я должен переместить оператор bind param за пределы цикла foreach, но я пытался и продолжаю получать сообщение об ошибке «MySQL ушёл». Как я могу оптимизировать запросы от mysql, когда потенциально могу запросить 10,0000 идентификаторов одновременно?

Пожалуйста, обратитесь к фрагменту кода ниже для подробного объяснения.

function getUserEvent($DB_3308, $user_id)
{
$user_event = array ();

$sql_get_user_event = "SELECT * FROM user_event WHERE user_id = ?";

$statement_user_event = $DB_PUMA_3306->link->prepare ( $sql_get_user_event);
$statement_user_event ->bind_param ( "s", $user_id );
$statement_user_event ->execute ();

if ($rs_user_event = $statement_user_event->get_result ())
{
while ( $row = $rs_user_event->fetch_assoc () )
{
$user_event [] = $row;
}
}

return $user_event;
}

function getUserDetails($DB_3306, $DB_3308, $user_list)
{
$user_details = array ();

foreach ( $user_list as $user_id )
{
$temp = array ();
$user_personal = null;
$user_event = null;

$user_personal = getUserContact ( $DB_3306, $user_id );
$user_event = getUserEvent( $DB_3308, $userid );

$temp ['user_id'] = $user_id;
$temp ['full_name'] = $user_personal ['full_name'];
$temp ['tel_no'] = $user_personal ['tel_no'];
$temp ['email'] = $user_personal ['email'];
$temp ['events'] = $user_event ;$user_details [] = $temp;
}

return $user_details;
}

0

foreach mysql php query-optimization

Решение

Другие решения

Почему вы не можете получить 50 или 100 userID в массиве, прежде чем извлекать его из базы данных и извлекать его навалом, чтобы уменьшить нагрузку на запрос?

$implodedUserIDs = implode(',', $userIDs);
$query = "SELECT * FROM user_event WHERE user_id IN ($implodedUserIDs)";

Это уменьшит некоторую нагрузку. Также вы можете немного поспать в каждой нагрузке. Просто постарайтесь максимально оптимизировать ваш код. 🙂

1

Источник

Accepted Answer

Похоже, вы зацикливаетесь на (потенциально) 10000 пользователей, и каждый из них выполняет не менее 2 запросов. Каждый запрос имеет небольшую нагрузку для его анализа и т. Д., И, следовательно, с большим количеством запросов это может быстро сложиться.

Я бы посоветовал вам объединить два запроса вместе, выполнив объединение, чтобы получить как контактные данные пользователей, так и сведения о пользовательских событиях.

Я также хотел бы предложить, чтобы вы выполняли этот единственный запрос один раз для всех идентификаторов пользователей, а не один раз для идентификатора пользователя. Обычно это было бы легко сделать, используя IN со списком идентификаторов пользователей, но с 10000 это не реально. Для этого создайте временную таблицу, содержащую ваш список идентификаторов пользователей.

Очень грубо (и делая предположения о классе вашей базы данных и о ваших реальных данных) что-то вроде этого:

function getUserDetails($DB_3306, $DB_3308, $user_list)
{

$sql = 'CREATE TEMPORARY TABLE user_list_tmp
(
user_id INT
)';

$DB_3306->execute($sql);

$user_list_split = array_chunk($user_list, 250);

foreach($user_list_split as $user_list_split_chunk);
{
$sql = 'INSERT INTO user_list_tmp (user_id) VALUES ('.implode('),(', $user_list_split_chunk).')';
$DB_3306->execute($sql);
}

$sql = "SELECT a.user_id, b.full_name, b.tel_no, b.email, c.event_id
FROM user_list_tmp a
INNER JOIN user_contact b
ON a.user_id = b.user_id
LEFT OUTER JOIN user_event c
ON a.user_id = c.userid
WHERE user_id = ?
ORDER BY a.user_id, c.event_id";

$statement_user_event = $DB_3306->link->prepare ( $sql);
$statement_user_event ->execute ();

$user_details = array();

if ($rs_details = $statement_user_event->get_result ())
{
while ( $row = $rs_details->fetch_assoc () )
{
$user_details[$row['user_id']]['user_id'] = $row['user_id'];
$user_details[$row['user_id']]['full_name'] = $row['full_name'];
$user_details[$row['user_id']]['tel_no'] = $row['tel_no'];
$user_details[$row['user_id']]['email'] = $row['email'];
$user_details[$row['user_id']]['events'][] = $row['event_id'];
}
}
return $user_details;
}

Это берет вам массив переданных пользовательских идентификаторов, разбивает его на массивы 250 и вставляет их во временную таблицу (я склонен вставлять в пакеты 250 как разумный баланс между читаемым и быстрым оператором вставки и выполнением минимального количества отдельных операторов — вы можете разделить его на большие или меньшие куски).

Затем он выполняет один запрос, который присоединяет временную таблицу к таблице user_contact, а левый присоединяет ее к таблице user_event. Каждый пользователь будет возвращать несколько строк, по одной на каждый чёт (но все равно одну строку, если нет событий). Он помещает их в массив, и я немного обманул здесь, используя user_id в качестве ключа массива. Таким образом, для первой строки для идентификатора пользователя сохраняются подробности для пользователя, а в любых последующих строках для пользователя (для дальнейших событий) данные пользователя просто перезаписываются. Детали событий просто помещаются в следующий элемент массива массива событий для этого пользователя.

1