Блог

Я хотел бы удалить большую часть данных. в этой таблице сейчас примерно 11207333

Однако у меня есть несколько способов его удаления.

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

Мой первый вариант:

 $start_date     = "2011-05-01 00:00:00";
$end_date       = "2011-05-31 23:59:59";
$sql = "DELETE FROM table WHERE date>='$start_date' and date <='$end_date'";
$mysqli->query($sql);
printf("Affected rows (DELETE): %dn", $mysqli->affected_rows);
  

второй вариант:

 $query = "SELECT count(*) as count FROM table WHERE date>='$start_date' and date <='$end_date'";
$result = $mysqli->query($query);
$row = $result->fetch_array(MYSQLI_ASSOC);
$total = $row['count'];

if ($total > 0) {
    $query = "SELECT * FROM table WHERE date>='$start_date' and date <='$end_date' LIMIT 0,$total";
    $result = $mysqli->query($query);
      while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $table_id = $row['table_id']; // primary key
            $query = "DELETE FROM table where table_id = $table_id LIMIT 0,$total";
            $mysqli->query($query);

    }
}
  

Данные этой таблицы отображаются клиенту для просмотра, я боюсь, что если удаление пойдет не так, и это повлияет на моего клиента.

Мне было интересно, есть ли какой-нибудь метод лучше моего.

Если вам, ребята, нужна дополнительная информация от меня, просто дайте мне знать.

Спасибо

На мой взгляд, первый вариант быстрее.

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

Если вы не указали неправильную начальную и конечную дату, я думаю, вы в безопасности в любом варианте, но вариант 1, на мой взгляд, быстрее.

и да, я не вижу никакого удаления в варианте 2, но я предполагаю, что вы имеете это в виду, но используете метод цикла.

Первый вариант — ваш лучший выбор.

Если вы боитесь, что что-то «пойдет не так», вы можете защитить себя, сначала создав резервную копию данных, экспортировав строки, которые вы планируете удалить, или установив логический флаг удаления.

Предполагая, что в нем действительно есть запрос на удаление, второй метод не только медленнее, он может прерваться, если другое соединение удалит одну из строк, которые вы собираетесь удалить в вашем while цикле, прежде чем у него будет возможность это сделать. Чтобы это сработало, вам нужно обернуть это в транзакцию:

 mysqli_query("START TRANSACTION;");
# your series of queries...
mysql_query("COMMIT;");
  

Это позволит корректно обрабатывать ваши запросы в изоляции от остальных событий, происходящих в БД.

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

Без этого индекса mysql будет в основном обрабатывать запрос более или менее так же, как в методе 2, но без:

• Интерпретация PHP,
• сетевое взаимодействие и
• накладные расходы на анализ запросов.

Вам не нужно ничего SELECTS , чтобы выполнить удаление в цикле. Просто используйте LIMIT в своем запросе на удаление и проверьте, есть ли затронутые строки:

 $start_date     = "2011-05-01 00:00:00";
$end_date       = "2011-05-31 23:59:59";
$deletedRecords = 0;
$sql = "DELETE FROM table WHERE date>='$start_date' and date <='$end_date' LIMIT 100";
do {
$mysqli->query($sql);
$deletedRecords  = $mysqli->affected_rows;
while ($mysqli->affected_rows > 0);
}

printf("Affected rows (DELETE): %dn", $deletedRecords);
  

Какой метод лучше, зависит от используемого вами механизма хранения.

Если вы используете InnoDB, это рекомендуемый способ. Причина в том, что оператор DELETE выполняется в транзакции (даже в режиме автоматической фиксации каждый оператор sql выполняется в транзакции, чтобы быть атомарным … если он завершится неудачей в середине, все удаление будет отменено, и вы не закончите с половиной данных). Это означает, что у вас будет длительная транзакция, и у вас будет много заблокированных строк во время транзакции, что заблокирует любого, кто захочет обновить такие данные (он может блокировать insterts, если задействованы уникальные индексы), и чтение будет выполняться через журнал отката. Другими словами, для InnoDB большие удаления выполняются быстрее, если выполняются порциями.

Однако в MyISAM удаление блокирует всю таблицу. Если вы выполняете много небольших фрагментов, у вас будет выполняться слишком много команд БЛОКИРОВКИ / РАЗБЛОКИРОВКИ, что фактически замедлит процесс. Я бы сделал это в цикле и для MyISAM, чтобы дать возможность другим процессам использовать таблицу, но в больших кусках по сравнению с InnoDB. Я бы никогда не стал делать это строка за строкой для таблицы на основе MyISAM из-за накладных расходов на БЛОКИРОВКУ / РАЗБЛОКИРОВКУ.