Q:我用mysql关联查询,limit进行分页 ,但是当数据过万时翻页就特别慢,请问有什么好的优化办法啊? 我的代码:       
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.cust_rank = jifen30.cust_rank      
LEFT JOIN capf101 ON capf101.cust_no = c0.c_custnum      


WHERE       
capf120.create_time IS NULL    AND c0.is_del = ‘N’    AND c10.is_del = ‘N’


LIMIT 9500
OFFSET 10



A:这里涉及到两部分的优化,多表关联和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.cust_rank = jifen30.cust_rank      
LEFT JOIN capf101 ON capf101.cust_no = c0.c_custnum      


WHERE    
capf120.create_time IS NULL    
AND c0.is_del = ‘N’    
AND c10.is_del = ‘N’


一般,习惯性认为left join驱动顺序是固定的,left join左边的表为驱动表,右边为被驱动表。
其实这也不是绝对的,当left join跟inner join等价的时候,MySQL优化器就会自己选择驱动表。
MySQL对表连接至今只支持nested loop join,通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果集。
优化的目标是尽可能减少关联查询中nested loop的循环次数,也就是说尽量缩小驱动表的结果集。


2. 理清各表之间的关联关系,注意被驱动表的关联字段上需要建立索引 假设在第1步中,最后选取的驱动表是c0, 那么根据表关联关系,可以理出驱动顺序为
c0 -> c10 ->c30 ;  c10->capf120;  c10 ->jfen30 ;c0 ->capf101;
那么在被驱动表的关联字段上,是需要建立索引的,这里包括:
c10.c_custnum
c30.c_custnum
capf120.cust_no
jifen30.cust_rank
capf101.cust_no


3. 综合考虑join字段和where字段,那么:
c0建立索引 (is_del)
c10建立组合索引 (c_custnum,is_del)
c30建立组合索引 (c_custnum,is_del)
capf120建立组合索引 (cust_no,create_time)
jifen30建立索引 (cust_rank)
capf101建立索引 (cust_no)


limit 分页的优化: 可以利用到覆盖索引,使得在查询时,直接从二级索引中获取结果,避免返回数据行查找;


4. 所以结合到 select 字段,进一步优化索引:
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
结合上面的几个步骤,最后给出的索引建议是:(这里依赖了前文中的一个假设条件,具体情况还是要依赖实际数据)
c0 建立组合索引(is_del,c_custnum,c_username)
c10 建立组合索引(c_custnum,is_del,cust_rank,c_accdate,c_custname)
c30 建立组合索引 (c_custnum,is_del)
capf120 建立组合索引(cust_no,create_time)
jifen30 建立组合索引(cust_rank,rank_name)
capf101 建立索引(cust_no)

发表评论

电子邮件地址不会被公开。 必填项已用*标注