mysql - 一条多表联查SQL语句优化的问题

Question

SELECT *,
    (SELECT name FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as name,
    (SELECT detail FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as detail
FROM tbl_a
WHERE id1='1';

暂且无视那个*号。请问这个语句怎么优化到最佳

Answer 1

首先建議增加tbl_a表增加id1欄位的索引,tbl_b表增加id1和id2欄位的索引,然後看一下處理是否滿足要求。

create index idx_a on tbl_a(id1);
create index idx_b on tbl_b(id1,id2);

初步看可以把取name和detail的語句合併到一起查詢處理，如：

SELECT *,
    (SELECT name, detail FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1)
FROM tbl_a
WHERE id1='1';