SQL Optimizations:

Today we are looking for data, time relationships, and write a rough SQL statement:

#2s587ms
#直接将所有表关联, more rough.

Select Go.businessid,dd.dict_namefrom Fn_xte.gte_order go,fn_config.t_dictionary_type dt,fn_config.t_dictionary_ Dict DD

where go.appid = dt.app_id and Dt.data_key = Dd.dict_type and Dict_code = Go.xingzhenquyu and dt.data_key_name = ' XING_ZHE Ng_qu_yu '

This statement associates three tables with a deeper recursive hierarchy, resulting in a large amount of computation, averaging x^3 levels. The associated result set is then filtered based on the where statement. A time-consuming operation is primarily a union operation of three tables in the FROM clause.

So it is optimized with subqueries as:

#487ms
#首先fn_xte. Gte_order,fn_config.t_dictionary_type the joint, Fn_config.t_dictionary_type at the rear, as the driving table, according to APP_ID, in Fn_xte.gte_ Orde the query, using the index
Select Temp.businessid,dd.dict_name
From Fn_config.t_dictionary_dict DD, (select Go.businessid,go.xingzhenquyu,dt.data_key
From Fn_xte.gte_order Go,fn_config.t_dictionary_type DT
     where dt.app_id = go.appid and dt.data_key_name = ' xing_zheng_qu_yu ') as Temp

Further optimization is about the order of execution of the from,where clauses, and there are still questions to be demonstrated:

#405ms
#首先是fn_config. T_dictionary_type dt,fn_xte.gte_order Go Union, Fn_xte.gte_order go behind as driver, its field appid indexed, here I think the index is not useful
#因为是根据appId查询其他表的, but the result shows that time consumption is low,
#可能原因是, table Fn_config.t_dictionary_type diluted the benefits of the Fn_xte.gte_order index due to too few data
Explain select Temp.businessid,dd.dict_name
From (select Go.businessid,go.xingzhenquyu,dt.data_key
From Fn_config.t_dictionary_type Dt,fn_xte.gte_order Go
      where dt.data_key_name = ' Xing_zheng_qu_yu ' and dt.app_id = go.appid) as temp,fn_config.t_dictionary_dict DD
where Dd.dict_code = Temp.xingzhenquyu and dd.dict_type = Temp.data_key;

SQL Optimizations: