俞月
2015-04-24 12:03:11
Remysql多表关联查询分页如何优化?这里涉及到两部分的优化,多表关联和Limit分页。
SQL优化依赖于表中的数据分布,explain得到的执行计划中的rows可以大致反映出问题所在。由于现在只能看到您提供的SQL,没有具体的数据信息,我就说一下,拿到这条SQL后,我的优化思路:
多表关联的优化原则是小结果集驱动大结果集。这个阶段的优化非常重要,一般按照下面的步骤进行分析:
1. 确定驱动表,通过explain查看执行计划,可以看到优化器选择的驱动表。explain结果中,第一行出现的表就优化器选择的驱动表。
SELECT
c0.c_custnum AS cCustnum,
c0.c_username AS cUsername,
c10.cust_rank AS custRank,
jifen30.rank_name AS rankName,
c10.c_accdate AS cAccdate,
c10.c_custname AS cCustname
FROM
capf00 AS c0
LEFT JOIN capf10 AS c10 ON c0.c_custnum = c10.c_custnum
LEFT JOIN capf30 AS c30 ON (c10.c_custnum = c30.c_custnum AND c30.is_del = 'N')
LEFT JOIN capf120 ON capf120.cust_no = c10.c_custnum
LEFT JOIN jifen30 ON c10