sql - 如何优化这个 SQL 查询

Question

在大型实例(即非常强大)的 MySQL 5 (Amazon RDS) 上运行此查询，执行此查询需要 16 秒。这个InnoDB表大约有200万行。

DELETE FROM quiz_multichoice_user_answer_multi WHERE
user_answer_id IN( SELECT id FROM
quiz_multichoice_user_answers WHERE question_nid =
1001700 AND question_vid = 1002731 AND result_id =
64003 

Answer 1

重写它以使用 JOIN 而不是子查询，看看是否会有所改善。 MySQL 经常这样做；查询规划器更擅长优化连接，并在使用索引时选择良好的索引，无论出于何种原因。

DELETE 
  quiz_multichoice_user_answer_multi
FROM
  quiz_multichoice_user_answer_multi
INNER JOIN
  quiz_multichoice_user_answers
ON
  quiz_multichoice_user_answer_multi.user_answer_id = quiz_multichoice_user_answers.id
WHERE
  quiz_multichoice_user_answers.question_nid = 1001700
AND
  quiz_multichoice_user_answers.question_vid = 1002731
AND
  quiz_multichoice_user_answers.result_id = 64003