Mysql优化

　　1.子查询很慢，改成全连接 方式：

SELECT SUM(l.RegisterNum) AS RegisterNum, 
                            SUM(l.AgentRequirementNum) AS AgentRequirementNum,
                            SUM(l.FreeRequirementNum) AS FreeRequirementNum,
                            SUM(l.AgentInquiryNum) AS AgentInquiryNum,
                            SUM(l.FreeInquiryNum) AS FreeInquiryNum,
                            (
                                SELECT count(1)
                                FROM stat_purchaser_active p
                                JOIN admin_purchaser a ON p.PurchaserId = a.PurchaserId
                                JOIN sda_crm.admin_user_info as u on {4}
                            ) AS ActiveNum                             
                            FROM stat_purchaser_pullnew

　　以上要100多秒，优化如下：

　　　　

SELECT * FROM ( SELECT l.UserId,l.RealName,SUM(l.RegisterNum) AS RegisterNum, 
                                            SUM(l.AgentRequirementNum) AS AgentRequirementNum,
                                            SUM(l.FreeRequirementNum) AS FreeRequirementNum,
                                            SUM(l.AgentInquiryNum) AS AgentInquiryNum,
                                            SUM(l.FreeInquiryNum) AS FreeInquiryNum,
                                            ActiveTb.ActiveNum
                                        FROM stat_purchaser_pullnew l
                                        LEFT JOIN
                                        (
                                            select ap.AdminUserId,count(*) as ActiveNum
                                            from admin_purchaser as ap
                                            left JOIN stat_purchaser_active as spa on spa.PurchaserId = ap.PurchaserId
                                            where 1 = 1 {2}{3}
                                            GROUP BY ap.AdminUserId,ap.PurchaserId
                                        ) as ActiveTb on ActiveTb.AdminUserId = l.UserId

　　优化之后不到2秒就出来了。

　　2.全连接方式很慢，改成Exists方式：

select AVG(Adst.Score) as FdScore, Adst.ProductTypeId, Adst.ConfigProductScoreItemId
                                                from
                                                (
                                                    SELECT psr.Score, psr.ConfigProductScoreItemId, p.ProductTypeId
                                                    from product_score_record as psr
                                                    join product as p on p.Id = psr.ProductId
                                                    JOIN user_info as ui on ui.Id = psr.UserId
                                                    join purchaser_info as pi on pi.CompanyId = ui.CompanyId and pi.IsTest = 0JOIN supplier_info as si on si.IsTest = 0 and si.CompanyId = p.CompanyId
                                                    where IsPlatformScore = 0 
                                                ) as Adst
                                                where Adst.ConfigProductScoreItemId = 'FunctionalityParameter'
                                                GROUP BY Adst.ProductTypeId, Adst.ConfigProductScoreItemId

　　这样要50多秒，优化之后如下：

                                                select AVG(Adst.Score) as FdScore, Adst.ProductTypeId, Adst.ConfigProductScoreItemId
                                                from
                                                (
                                                    SELECT psr.Score, psr.ConfigProductScoreItemId, p.ProductTypeId
                                                    from product_score_record as psr
                                                    join product as p on p.Id = psr.ProductId
                                                    JOIN user_info as ui on ui.Id = psr.UserId
                                                    join purchaser_info as pi on pi.CompanyId = ui.CompanyId and pi.IsTest = 0where IsPlatformScore = 0 and not EXISTS(SELECT si.CompanyId from supplier_info as si where si.IsTest = 0 and si.CompanyId = ui.CompanyId) 
                                                ) as Adst
                                                where Adst.ConfigProductScoreItemId = 'FunctionalityParameter'
                                                GROUP BY Adst.ProductTypeId, Adst.ConfigProductScoreItemId

　　这样优化之后，只需要2秒多就可以了。

　　其实对于数据库优化，除了像DBA那样专业的调优师，懂得数据库的各种原理就比较快优化，想我这种后台编程人员，只能一个一个试，看哪一部分比较慢，再针对这部分和业务逻辑进行优化。