分析所有该表相关的统计分析... 优化所有的相关SQL---我们单位的表大,..大约3年数据有1200W,因为有优化,效率尚可... 由分析结果建立常用的几个符合索引。。例如: select sku_code sku_code, sum(qty_in) as sum_in, sum(qty_out) as sum_out from t_tx_log t1,t_warehouse t2 where t1.warehouse_id=t2.id and t2.code='111' and t1.op_type not in (6,7) and t1.operation_time>='08/10/2005' and t1.operation_time<'10/01/2005' 优化为 select sku_code sku_code, sum(qty_in) as sum_in, sum(qty_out) as sum_out from t_tx_log t1,t_warehouse t2 where t1.warehouse_id=t2.id and t2.code='111' and t1.op_type in (-,-,...) ----not不会使用op_type索引 and t1.operation_time>='08/10/2005' and t1.operation_time<'10/01/2005' 该SQL如过复合索引(operation_time,op_type)的话,效率应该是提高不少的 再删掉一个没用的单键索引...一个合适的复合索引效率将大幅度的提高.建议结合该贴上的资料进行分析:(SQL的东西不少哦,多看看) http://community.csdn.net/Expert/topic/4273/4273349.xml?temp=.6357996
优化所有的相关SQL---我们单位的表大,..大约3年数据有1200W,因为有优化,效率尚可...
由分析结果建立常用的几个符合索引。。例如:
select sku_code sku_code,
sum(qty_in) as sum_in,
sum(qty_out) as sum_out
from t_tx_log t1,t_warehouse t2
where t1.warehouse_id=t2.id and t2.code='111'
and t1.op_type not in (6,7)
and t1.operation_time>='08/10/2005' and t1.operation_time<'10/01/2005'
优化为
select sku_code sku_code,
sum(qty_in) as sum_in,
sum(qty_out) as sum_out
from t_tx_log t1,t_warehouse t2
where t1.warehouse_id=t2.id and t2.code='111'
and t1.op_type in (-,-,...) ----not不会使用op_type索引
and t1.operation_time>='08/10/2005' and t1.operation_time<'10/01/2005'
该SQL如过复合索引(operation_time,op_type)的话,效率应该是提高不少的
再删掉一个没用的单键索引...一个合适的复合索引效率将大幅度的提高.建议结合该贴上的资料进行分析:(SQL的东西不少哦,多看看)
http://community.csdn.net/Expert/topic/4273/4273349.xml?temp=.6357996