可以尝试以下几种方法来优化更新查询:
示例代码:
ALTER TABLE table_name ADD INDEX index_name (column_name);
示例代码:
CREATE TEMPORARY TABLE temp_table_name LIKE table_name;
INSERT INTO temp_table_name SELECT * FROM table_name WHERE ...;
UPDATE temp_table_name SET column_name = ... WHERE ...;
INSERT INTO table_name SELECT * FROM temp_table_name;
示例代码:
START TRANSACTION;
UPDATE table_name SET column_name = ... WHERE ...;
COMMIT;
示例代码:
UPDATE table_name SET column_name = ... WHERE id BETWEEN 1 AND 10000;
UPDATE table_name SET column_name = ... WHERE id BETWEEN 10001 AND 20000;
...
示例代码:
UPDATE table_name SET column_name = ... WHERE id IN (SELECT id FROM other_table WHERE ...);
改为:
UPDATE table_name JOIN other_table ON table_name.id = other_table.id SET column_name = ... WHERE ...;