Sql语句优化之explan分析案例

① extra =  Using temporary; Using filesort;

sql : EXPLAIN SELECT vtiger_account.accountname, IF(protected = 1, '是', '否') AS protected, vtiger_account.servicetype, IF(sign = 1, '是', '否') AS sign, vtiger_account.advancesmoney , IFNULL(( SELECT CONCAT(last_name, '[', IFNULL(( SELECT departmentname FROM vtiger_departments WHERE departmentid = ( SELECT departmentid FROM vtiger_user2department WHERE userid = vtiger_users.id LIMIT 1 ) ), ''), ']', IF(`status` = 'Active', '', '[离职]')) AS last_name FROM vtiger_users WHERE vtiger_crmentity.smownerid = vtiger_users.id ), '--') AS smownerid, vtiger_crmentity.smownerid AS smownerid_owner, vtiger_account.accountrank, vtiger_account.linkname, vtiger_account.mobile , vtiger_account.phone, vtiger_account.website, vtiger_account.fax, vtiger_account.email1, vtiger_account.industry , vtiger_account.annual_revenue, vtiger_account.address, vtiger_account.makedecision, vtiger_account.gender, vtiger_account.country , vtiger_account.business, vtiger_account.regionalpartition, IFNULL(( SELECT CONCAT(last_name, '[', IFNULL(( SELECT departmentname FROM vtiger_departments WHERE departmentid = ( SELECT departmentid FROM vtiger_user2department WHERE userid = vtiger_users.id LIMIT 1 ) ), ''), ']', IF(`status` = 'Active', '', '[离职]')) AS last_name FROM vtiger_users WHERE vtiger_crmentity.modifiedby = vtiger_users.id ), '--') AS modifiedby, vtiger_crmentity.modifiedby AS modifiedby_reference, vtiger_account.title , vtiger_account.leadsource, vtiger_account.businessarea, ( SELECT createdtime FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_account.accountid AND vtiger_crmentity.deleted = 0 ) AS createdtime, ( SELECT modifiedtime FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_account.accountid AND vtiger_crmentity.deleted = 0 ) AS modifiedtime, vtiger_servicecomments.serviceid AS serviceid , vtiger_servicecomments.serviceid AS serviceid_reference, ( SELECT description FROM vtiger_crmentity WHERE vtiger_crmentity.crmid = vtiger_account.accountid AND vtiger_crmentity.deleted = 0 ) AS description, IFNULL(( SELECT label FROM vtiger_crmentity WHERE crmid = vtiger_account.parentid ), '--') AS parentid, vtiger_account.customerproperty, vtiger_account.account_no , vtiger_account.lastfollowuptime, vtiger_account.saleorderlastdealtime, vtiger_account.protectday, vtiger_account.accountcategory, vtiger_account.visitingtimes , IF(frommarketing = 1, '是', '否') AS frommarketing, vtiger_account.accountid FROM vtiger_account LEFT JOIN vtiger_crmentity ON vtiger_account.accountid = vtiger_crmentity.crmid LEFT JOIN vtiger_servicecomments ON vtiger_account.accountid = vtiger_servicecomments.related_to AND vtiger_servicecomments.assigntype = 'accountby' WHERE 1 = 1 AND vtiger_crmentity.deleted = 0 AND vtiger_account.accountname LIKE '%上海%' AND vtiger_account.accountname IS NOT NULL AND vtiger_account.accountcategory = 2 ORDER BY vtiger_crmentity.crmid DESC LIMIT 33140, 20;

上面的红色部分表示，排序使用了临时表。原因分析： 由于进行了 ORDER BY vtiger_crmentity.crmid DESC 排序。但是这里的排序并没有走crmid 对应的索引，这里走了 第一张表的 protectday 索引。

所以当几张表都关联获取数据后，这些数据在 根据crmid字段排序，这时候本来数据就比较多，所以就出现了 Using temporary; Using filesort; 来进行排序。 

解决方案： ①排序的字段换成第一张表的主键或者 使用 sql语句执行中第一个张表使用的索引字段排序。 ②前面where得到的数据排序必须很少，这样直接排序就很轻松

sql :

EXPLAIN

select

( SELECT GROUP_CONCAT(productsearchid, '##') FROM vtiger_servicecontracts WHERE sc_related_to = related_to LIMIT 2 ORDER BY servicecontractid DESC ) AS 'productlist',
vtiger_servicecomments.assigntype,
IFNULL((select vtiger_modcomments.addtime from vtiger_modcomments
where vtiger_modcomments.modulename='Accounts'
and vtiger_modcomments.moduleid=vtiger_servicecomments.related_to
and vtiger_modcomments.creatorid=vtiger_servicecomments.serviceid
ORDER BY vtiger_modcomments.addtime desc LIMIT 1),vtiger_servicecomments.addtime) as lastfollowtime,
vtiger_servicecomments.allocatetime,
vtiger_servicecomments.servicecommentsid,
vtiger_servicecomments.salesorderproductsrelid,
vtiger_servicecomments.related_to as accountid,
(vtiger_account.accountname) as related_to,
vtiger_servicecomments.addtime,
(select leadsource from vtiger_account where vtiger_account.accountid=vtiger_servicecomments.related_to ) as leadsource,
'--' as productid,
vtiger_servicecomments.starttime,
vtiger_servicecomments.endtime,
'--' as serviceamount,
IFNULL((select CONCAT(last_name,'[',IFNULL((select departmentname from vtiger_departments where departmentid = (select departmentid FROM vtiger_user2department where userid=vtiger_users.id LIMIT 1)),''),']',(if(`status`='Active','','[离职]'))) as last_name from vtiger_users where id=vtiger_servicecomments.serviceid),'--') as serviceid,

(select last_name from vtiger_users where id=vtiger_servicecomments.assignerid) as assignerid,
(select accountrank from vtiger_account where vtiger_account.accountid=vtiger_servicecomments.related_to) as accountrank,
(select IFNULL((select CONCAT(last_name,'[',IFNULL((select departmentname from vtiger_departments where departmentid = (select departmentid FROM vtiger_user2department where userid=vtiger_users.id LIMIT 1)),''),']',(if(`status`='Active','','[离职]'))) as last_name from vtiger_users where vtiger_crmentity.smownerid=vtiger_users.id),'--') as smownerid from vtiger_crmentity where vtiger_crmentity.crmid=vtiger_servicecomments.related_to and vtiger_crmentity.deleted=0) as ownerid,
(select departmentname from vtiger_departments where vtiger_departments.departmentid=(select departmentid from vtiger_user2department where vtiger_user2department.userid=(select vtiger_crmentity.smownerid from vtiger_crmentity where vtiger_crmentity.crmid=vtiger_servicecomments.related_to and vtiger_crmentity.deleted=0))) as departmentid,
'--' as schedule,
vtiger_servicecomments.nofollowday,
vtiger_servicecomments.remark
FROM vtiger_servicecomments
LEFT join vtiger_account ON vtiger_account.accountid = vtiger_servicecomments.related_to
LEFT JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_servicecomments.related_to
LEFT JOIN vtiger_servicecomments_returnplan ON vtiger_servicecomments_returnplan.commentsid = vtiger_servicecomments.servicecommentsid

where 1=1 and vtiger_servicecomments.assigntype='accountby' and SYSDATE() BETWEEN updatetime AND lowertime and isfollow !=1 
GROUP BY vtiger_servicecomments.servicecommentsid 
order by vtiger_servicecomments.servicecommentsid DESC LIMIT 0,20;

   这个sql的explan与我以前像结果有些不一样。根据sql中的

FROM vtiger_servicecomments
LEFT join vtiger_account ON vtiger_account.accountid = vtiger_servicecomments.related_to
LEFT JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_servicecomments.related_to
LEFT JOIN vtiger_servicecomments_returnplan ON vtiger_servicecomments_returnplan.commentsid = vtiger_servicecomments.servicecommentsid

可以推测 explan的结果第一条应该是 vtiger_servicecomments 表的查询，这个是我以前的认知，但是现在却是 vtiger_servicecomments_returnplan 这个表。根据上面的分析，出现Using temporary; Using filesort;就能接受了。
可以通过改变 order by 这个排序为  ORDER BY vtiger_servicecomments_returnplan.commentreturnplanid DESC 排序就不会出现 Using temporary;（使用临时表）。
但是为什么会从 vtiger_servicecomments_returnplan 表开始运行sql了？
经过查找资料，sql中关联表的查询的顺序不一定是sql中表的顺序，mysql的关联分析器会根据where 查询条件，索引，找到最优的 表来作为执行的第一个表，上面的sql走的第一个表，主要原因是

SYSDATE() BETWEEN updatetime AND lowertime and isfollow !=1        lowertime和isfollow都是在vtiger_servicecomments_returnplan中的字段。 而vtiger_servicecomments表中的 assigntype字段不是索引，最关键的是唯一性不高。所以选择了

vtiger_servicecomments_returnplan表，这样查询的数据相对于vtiger_servicecomments表assigntype='accountby'的数据少。