我想要删除大量数据。这个表现在大约有11207333个
然而,我有几个方法来删除它。
将要删除的数据约为300k。我有两种方法可以做到这一点,但不确定哪一个执行得更快。
我的第一个选项:
$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): %d'n", $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);
}
}
这个表的数据是显示给客户端看的,我担心如果删除出错,会影响到我的客户端。
我想知道有没有比我的方法更好的方法。
如果你们需要我提供更多的信息,请告诉我。
谢谢
在我看来,第一种选择更快。
第二个选项包含循环,我认为它会更慢,因为它会循环几次寻找你的表id。
如果你没有提供错误的开始和结束日期,我认为两个选项都是安全的,但我认为选项1更快。
,是的,我没有看到任何删除选项2,但我假设你已经记住了,但使用循环方法。
选项一是你最好的选择。
如果您担心某些事情会"出错",您可以通过先备份数据、导出您计划删除的行或实现逻辑删除标志来保护自己。
假设其中确实有一个DELETE查询,第二种方法不仅速度较慢,如果另一个连接删除了您打算在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): %d'n", $deletedRecords);
哪种方法更好取决于您正在使用的存储引擎。
如果你正在使用InnoDB,这是推荐的方法。原因是DELETE语句在事务中运行(即使在自动提交模式下,每个sql语句都在事务中运行,以便原子化…如果它在中间失败,整个删除将被回滚,并且您不会以半数据结束)。这意味着您将有一个长时间运行的事务,并且在事务期间将有许多锁定的行,这将阻止任何想要更新此类数据的人(如果涉及到唯一索引,它可能会阻止insert),并且读取将通过回滚日志完成。换句话说,对于InnoDB来说,如果在块中执行大的删除操作,则速度更快。
在MyISAM中,delete会锁定整个表。如果您在很多小块中这样做,您将执行太多的LOCK/UNLOCK命令,这实际上会减慢进程。我也会在MyISAM中创建一个循环,以便给其他进程使用表的机会,但要比InnoDB大一些。对于基于MyISAM的表,由于LOCK/UNLOCK的开销,我永远不会逐行执行。