概念：
优化策略：字段选择性
选择性较低索引 可能带来的性能问题
索引选择性=索引列唯一值/表记录数；
选择性越高索引检索价值越高，消耗系统资源越少；选择性越低索引检索价值越低，消耗系统资源越多；
查询条件含有多个字段时，不要在选择性很低字段上创建索引
可通过创建组合索引来增强低字段选择性和避免选择性很低字段创建索引带来副作用；
尽量减少possible_keys，正确索引会提高sql查询速度，过多索引会增加优化器选择索引的代价，不要滥用索引；
现象：
昨天数据库一个SQL在读库上占用很高的CPU，CPU一直在100%，CPU wait达到100多，通EM的SQL监视 可以看到，有一条SQl在多次执行时一直没有完成，有的运行20多分钟还在执行。
SQL如下：
Select DistinctFppolicyin0_.Id AsId53_,
Fppolicyin0_.Carrier_Code As Carrier2_53_,
Fppolicyin0_.Policy_Id As Policy3_53_,
Fppolicyin0_.Carrier_Policyid As Carrier4_53_,
Fppolicyin0_.Agent_Id As Agent5_53_,
Fppolicyin0_.Airline As Airline53_,
Fppolicyin0_.Airline_Type As Airline7_53_,
Fppolicyin0_.Apply_Flightnos As Apply8_53_,
Fppolicyin0_.Exc_Flightnos As Exc9_53_,
Fppolicyin0_.Class_Codes As Class10_53_,
Fppolicyin0_.Shortstay_Time As Shortstay11_53_,
Fppolicyin0_.Useweek As Useweek53_,
Fppolicyin0_.Tkt_Type As Tkt13_53_,
Fppolicyin0_.Psg_Type As Psg14_53_,
Fppolicyin0_.Is_Special As Is15_53_,
Fppolicyin0_.Is_Commend As Is16_53_,
Fppolicyin0_.Limit_Code As Limit17_53_,
Fppolicyin0_.Pre_Outtkt_Day As Pre18_53_,
Fppolicyin0_.Last_Outtkt_Day As Last19_53_,
Fppolicyin0_.Flight_Price As Flight20_53_,
Fppolicyin0_.Rebate As Rebate53_,
Fppolicyin0_.Flight_Price_Chd As Flight22_53_,
Fppolicyin0_.Rebate_Chd As Rebate23_53_,
Fppolicyin0_.Flight_Price_Um As Flight24_53_,
Fppolicyin0_.Rebate_Um As Rebate25_53_,
Fppolicyin0_.Flight_Price_Inf As Flight26_53_,
Fppolicyin0_.Rebate_Inf As Rebate27_53_,
Fppolicyin0_.Out_Tktcity As Out28_53_,
Fppolicyin0_.Ei As Ei53_,
Fppolicyin0_.Signticket As Signticket53_,
Fppolicyin0_.Endorsement As Endorse31_53_,
Fppolicyin0_.Refundmemo As Refundmemo53_,
Fppolicyin0_.Enei As Enei53_,
Fppolicyin0_.Enendorsement As Enendor34_53_,
Fppolicyin0_.Enrefundmemo As Enrefun35_53_,
Fppolicyin0_.Tkt_Startdate AsTkt36_53_,
Fppolicyin0_.Tkt_Enddate As Tkt37_53_,
Fppolicyin0_.Tkt_Useweek As Tkt38_53_,
Fppolicyin0_.Fp_Startdate As Fp39_53_,
Fppolicyin0_.Fp_Enddate As Fp40_53_,
Fppolicyin0_.Fp_Useweek As Fp41_53_,
Fppolicyin0_.Exc_Startdate As Exc42_53_,
Fppolicyin0_.Exc_Enddate As Exc43_53_,
Fppolicyin0_.Is_Open As Is44_53_,
Fppolicyin0_.Ret_Startdates As Ret45_53_,
Fppolicyin0_.Ret_Enddates As Ret46_53_,
Fppolicyin0_.Mf_Remark As Mf47_53_,
Fppolicyin0_.Remark As Remark53_,
Fppolicyin0_.Create_Date As Create49_53_,
Fppolicyin0_.Longstay_Time As Longstay50_53_,
Fppolicyin0_.Levcity As Levcity53_,
Fppolicyin0_.Arvcity As Arvcity53_,
Fppolicyin0_.Levdrome As Levdrome53_,
Fppolicyin0_.Arvdrome As Arvdrome53_,
Fppolicyin0_.Is_To_Pata As Is55_53_,
Fppolicyin0_.Commend_Rmk As Commend56_53_,
Fppolicyin0_.Isgroup As Isgroup53_,
Fppolicyin0_.Policy_Source As Policy58_53_,
Fppolicyin0_.Tkt_Price As Tkt59_53_,
Fppolicyin0_.Other_Segment As Other60_53_,
Fppolicyin0_.Spe_Display_Name As Spe61_53_,
Fppolicyin0_.Fp_Startdate_Back As Fp62_53_,
Fppolicyin0_.Fp_Enddate_Back As Fp63_53_,
Fppolicyin0_.Exc_Startdate_BackAs Exc64_53_,
Fppolicyin0_.Exc_Enddate_Back As Exc65_53_,
Fppolicyin0_.Fp_Starttime As Fp66_53_,
Fppolicyin0_.Fp_Endtime As Fp67_53_,
Fppolicyin0_.Fp_Starttime_Back As Fp68_53_,
Fppolicyin0_.Fp_Endtime_Back As Fp69_53_
From Fp_Policyinfo_Dt Fppolicyin0_,
Code_Airways Tkcodeairw1_,
Fp_Policy_Issue_Dt Fppolicyis2_,
Code_Airdrome Tkcodeaird3_,
Code_Airdrome Tkcodeaird4_,
Fp_Policy_Issue_Dt Fppolicyis5_
Where Fppolicyin0_.Policy_Id =Fppolicyis5_.Id
And 1 = 1
And Fppolicyis5_.Status = '1'
And Fppolicyin0_.Carrier_Code =Tkcodeairw1_.Id
And Fppolicyin0_.Policy_Id =Fppolicyis2_.Id
And (Fppolicyin0_.Levdrome =Tkcodeaird3_.Airdromeid Or
Fppolicyin0_.Levdrome = '*')
And (Fppolicyin0_.Arvdrome =Tkcodeaird4_.Airdromeid Or
Fppolicyin0_.Arvdrome = '*')
And ((Fppolicyin0_.Levcity = 'PEK' Or --变量1
Fppolicyin0_.Levcity =
(Select Tkcodeaird6_.Cityid
FromCode_Airdrome Tkcodeaird6_
WhereTkcodeaird6_.Airdromeid = 'PEK')) And --变量2
(Fppolicyin0_.Arvcity = 'SHA' Or --变量3
Fppolicyin0_.Arvcity =
(Select Tkcodeaird7_.Cityid
FromCode_Airdrome Tkcodeaird7_
Where Tkcodeaird7_.Airdromeid = 'SHA')) Or --变量4
Fppolicyin0_.Airline = '*-*')
And (Fppolicyin0_.Policy_Source IsNull Or
Fppolicyin0_.Policy_Source <> 'P')
And (Fppolicyin0_.Tkt_Startdate IsNull Or
Fppolicyin0_.Tkt_Startdate <= '2016-03-17') --变量5
And (Fppolicyin0_.Tkt_Enddate IsNull Or
Fppolicyin0_.Tkt_Enddate >= '2016-03-17') --变量6
分析解决：
通过和开发人员沟通，此SQL是航班查询的功能。
查看SQL的执行计划，发现cost值非常大：
查看SQL中相关的的5个表，其中Fp_Policyinfo_Dt 、FP_POLICY_ISSUE_DT表数据量最大，记录数达900多万行，应该是没使用正确的索引导致的SQL查询慢。
首先考虑是否有索引碎片，尝试重建几个比较大的索引无效
接着尝试新建某些索引也无效
再次研究发现Fp_Policy_Issue_Dt表中Status字段值(1)在SQL中是写死的，于是查看Status字段在表中的分布
--查看字段值分布
SQL> Select status,Count(1) FromFP_POLICY_ISSUE_DT group by cube(status);
STATUS COUNT(1)
---------- ----------
9663424 --总记录数
0 1200
1 586632
2 13730
3 9061862
--查看status值为1的占总记录数的比例
SQL> select round(586632/9663424,2) fromdual;
ROUND(586632/9663424,2)
-----------------------
0.06
SQL> select round(586632/9663424,2) fromdual;
ROUND(586632/9663424,2)
-----------------------
0.06
可以看到STATUS=3的为绝大多数，status=1只占6%，所以在status上字段创建索引，在SQL查询status=1时，可以提高SQL索引的选择性，使SQL查询效率会更高。
--下面我们尝试创建索引
SQL> reate index ind_IDX_ FP_POLICY_ISSUE_STATUSon FP_POLICY_ISSUE_DT (status) online;
--再查看执行计划
可以看到SQl使用了我们新建的索引IDX_ FP_POLICY_ISSUE_STATUS，SQL的执行时间也由原来的30分钟以上，优化后6秒就能出来了。
总结：
按常规来说，像状态类型等这类选择性比较低的字段是不适合建立索引的，但在实际情况中，我们要充分了所查字段值的分布情况，在SQL查询中要根据字段值实际分布建立合适的索引，灵活使用索引的选择性，也提高SQL的查询性能。
内容参考：http://www.cnblogs.com/zhengyun_ustc/p/slowquery2.html
扫一扫
265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
