记录一次千万数据优化过程

大家好我是陈思源 好久没有更新文章了今天带来一个数据优化的案例

最近公司让我做了一个比较有意思的项目，这个项目是一个大型文旅集团的报表系统项目，我们主要做原来系统报表的二次开发和整个报表系统数据查询很慢的情况。我就被负责到了数据优化这块模块，文旅集团他们那边复制了一个备份库让我们使用这个备份库去测试，我去连接了一下这个库，数据大概都是千万级别数据 ，表大概一千多张表

大概需要优化30多种报表模版 文旅集团他们那边是自带SQL的，已经把SQL语句发过来了我也是第一时间去测试了一张表，不测不知道，一测试发现查询就一个月数据需要花费58s 左右，这种查询速度已经不能使用了，这里贴出这张报表的SQL语句，这个主表数据大概是5千万数据。

-- 各售票员销售日报表【结算】
   
   select
	temp.SELLER,
	temp.PAY_NAME,
	temp.CLIENT_TYPE,
	temp.TICKET_GROUP_NAME,
	temp.TICKET_NAME,
	temp.TICKET_KIND_NAME,
	temp.TICKET_MODEL_PRICE,
		temp.TRADE_TYPE,
	sum(temp.AMOUNT) as AMOUNT,
	sum(temp.PAY_SUM) as PAY_SUM
from
	(
	 select
		   case  when TM.BILL_SOURCE = '2' then '电子商务'
		   else 	ti.gateway
		END AS gateway ,
		bi.SELLER ,
       case
		          when TM.BILL_SOURCE = '2' then '电子商务'
		       else  ( case when ti.GATEWAY in('101') and ti.REPORT_INFO='alipay' then '支付宝'
					when ti.GATEWAY in('101') and ti.REPORT_INFO='weixin' then '微信'	else ti.PAY_AWAY  end 
						)
		END AS PAY_NAME ,
		case
		          when TM.CLIENT_TYPE = '4' then '散客'
		          when TM.CLIENT_TYPE = '3' then '团队'
		          else '其他'
		end as CLIENT_TYPE,
		bi.TICKET_GROUP_NAME ,
		bi.TICKET_NAME,
		bi.TICKET_KIND_NAME,
		bi.TICKET_MODEL_PRICE,
	                 (case when bi.TRADE_TYPE = 2 then -bi.AMOUNT else bi.AMOUNT end) as AMOUNT,
		(case when bi.TRADE_TYPE = 2 then -bi.AMOUNT else bi.AMOUNT end)*bi.TICKET_MODEL_PRICE as PAY_SUM,
	bi.TRADE_TYPE
	from
	tkt_balance_info bi

	inner join tkt_balance_main tbm  on tbm.ID = bi.BALANCE_ID  and tbm.DELETED = 'F'
	left join trans_info ti on  ti.TRADE_CODE = bi.TRADE_NO  and ti.deleted = 'F'   and ti.pay_status = '3'
	left join tkt_trademain tm on  bi.TRADE_ID = tm.id
	left join tkt_ticket_model model  on  bi.TICKET_CODE = model.ticket_code  and model.deleted = 'F'
	left join tkt_park_zone tp on bi.PARK_ID=tp.id and tp.deleted='F'
	where  bi.deleted = 'F'   and bi.TRADE_TYPE in(1,2)
	and model.TICKET_GROUP_CODE not in ('1017','9001','1006','1019','1101','1102','1103','1104','1105')	
				and bi.CREATE_TIME >= '2024-02-01'
						and bi.CREATE_TIME <= DATE_ADD('2024-03-01',INTERVAL 1 DAY)
						                                  and  substring(tp.PARK_CODE, 1, 4) in ( '0002'    )
                                  								  
union all
		
	select
	'电子商务'  as gateway,
	                 bi.SELLER ,
		'电子商务'  as PAY_NAME ,
		'电子商务'  CLIENT_TYPE,
		model.TICKET_GROUP_NAME ,
		bi.TICKET_NAME,
		bi.TICKET_KIND_NAME,
	    bi.TICKET_MODEL_PRICE,
	    (case when bi.TRADE_TYPE = 8 then -bi.AMOUNT else bi.AMOUNT end) as AMOUNT,
		(case when bi.TRADE_TYPE = 8 then -bi.AMOUNT else bi.AMOUNT end)*bi.TICKET_MODEL_PRICE as PAY_SUM,
	bi.TRADE_TYPE
	from  tkt_balance_info bi

	inner join tkt_balance_main tbm on  tbm.ID = bi.BALANCE_ID  and tbm.DELETED = 'F'
	left join tkt_ticket_model model  on  bi.TICKET_CODE = model.ticket_code  and model.deleted = 'F'
	left join tkt_park_zone tp on bi.PARK_ID=tp.id and tp.deleted='F'
	where
	bi.deleted = 'F' and bi.TRADE_TYPE in(3,4,8) 
                 -- and ifnull(bi.CHECK_ACCOUNTS_ID,0) > 0
	and model.TICKET_GROUP_CODE not in ('1017','9001','1006','1019','1101','1102','1103','1104','1105')	
	                 	                 and bi.CREATE_TIME >= '2024-02-01'
	                 	                 		and bi.CREATE_TIME <= DATE_ADD('2024-03-01',INTERVAL 1 DAY)
						
		    and left(tp.PARK_CODE,4) in (
    	    	   '0002'  
	    	          )
                     )
                     temp where 1=1
				 	group by
	temp.SELLER,
	temp.PAY_NAME,
	temp.CLIENT_TYPE,
	temp.TICKET_GROUP_NAME,
	temp.TICKET_NAME,
	temp.TICKET_KIND_NAME,
	temp.TICKET_MODEL_PRICE,
	temp.TRADE_TYPE
order by
	temp.SELLER,
	temp.PAY_NAME,
	temp.CLIENT_TYPE,
	temp.TICKET_GROUP_NAME,
	temp.TICKET_NAME,
	temp.TICKET_KIND_NAME,
	temp.TICKET_MODEL_PRICE,
	temp.TRADE_TYPE 
	

可以看到一个月数据居然需要58s

explain 一下发现

查询条件比较慢的还是在bi这张表也就是tkt_balance_info 这张主表

因为数据量不是太大这个数据查询这么慢，我就先想到了两种可能

• 没有建立索引
• 索引失效或者没有走预想的索引

我们先考虑第一种情况也就是看里面关于tkt_balance_info这张表的查询条件是否有建立索引

通过观察发现这几个查询条件 和表的索引情况是建立的

索引是带的，那我就想的是索引失效情况

通过explain发现sql 优化器走的索引是tkt_balance_info_TICKET_CODE_IDX这个索引，通过上面的查询条件逻辑发现重要的几个查询条件 IDX_CREATE_TIME，deleted，TRADE_TYPE 都没有走，不太清楚为什么SQL优化器要走这个索引可能是SQL语句写的子查询的原因。

1. 为了效率考虑我还是想到在不改SQL情况去尽量优化，如果不行了再去改SQL语句，我就试试看这几个条件的索引，既然查询器不走我就只能用自带的强制走指定索引的一个语法 ，force index 【感兴趣的可以具体看一下这个怎么用的】

他的作用简单说就是指定走一个索引这里我看CREATE_TIME这个在SQL比较重要我就先指定他试试看。

看到结果，果然是没有走这个索引导致的 现在是2.5s

这个是之前查询也是192行是58S(前面也有这个图片)

既然是没有走索引导致的那我可以建立一个复合索引指定优化器走这个索引

这里注意细节，建立复合索引优先走最左边的字段也就是CREATE_TIME其次才走其他两个

可以看到speeds 复合索引已经建立我们强制走这个复合索引试试看，有没有更好的提升

可以看到用了复合索引已经控制在2秒以内了，好的这期就分享到这里了我们下期见！