数据库和SQL

维护优化SQL数据库来提升工作效率

起因:客户查询报表特别慢,以前是快的

某客户反馈查询报表特别慢,经过远程看现场确实是非常慢,并分析了客户主要业务表的索引,发现跟其他客户的索引是一模一样的,就是慢的离谱。

分析:SQL命令种数量最大的表是?

我们通过获取客户查询的SQL命令,见下

Select A.fOptDateStr As fOptDateStr17/* 业务日期 */,
A.fFilialeName As fFilialeName29/* 分店名称 */,
A.fModeName As fModeName26/* 收款方式 */,
Sum(A.fMoney) As fMoney24/* 收款金额 */
From (select a.fOptDateStr,a.fInnerBillNo,b.fPosBillNo,a.fGatheringNo,a.fPayCredNo,
a.fCertificateNo,a.fModeID,a.fOrderID,(a.fMoney * 1) as fMoney,
a.fTotalSign,a.fAssistSign,a.fUsableFillSum,a.fUsableScore,a.fCurrencyID,a.fCurrencyValue,
a.fExchangeRate,a.fCardTypeId,a.fPoundage,a.fConsumeSum,
fOptTimeStr,fOrderNo,fOrderName,fModeNo,fModeName,fUserName,f.fMachineNo,
f.fMachineName,fFilialeName, f.fDeptID,b.fManID,b.fMachineID,
ISNULL(ct.fCurrencyName,’本币’) as fCurrencyName
from Pos_RetailPayDetail(nolock) a
Join Pos_RetailBill(nolock)b on a.fOptDateStr = b.fOptDateStr and a.fInnerBillNo=b.fInnerBillNo
Left Join Pos_ReceiverOrder(nolock)c on a.fOrderID = c.fOrderID
Left Join Pos_PayMode(nolock)d on a.fModeID = d.fModeID
Left Join Right_User(nolock)e on b.fManID = e.fUserID
Left Join Pos_CashRegister(nolock)f on b.fMachineID = f.fMachineID
Left Join Pos_Filiale(nolock)g on f.fDeptID = g.fFilialeID
Left Join (
select fCurrencyID,fCurrencyName from POS_CurrencyType(nolock)
union all
select 0 as fCurrencyID,fItemValue as fCurrencyName from Opt_SysInfo(nolock)
where fOptionName = ‘TDM_FIOPTION’ and fItemName = ‘MONEYNAME’
) ct on a.fCurrencyID = ct.fCurrencyID
) As A /*查询.POS前台付款方式明细*/
Where 1=1 and A.fOptDateStr Between ‘20200620’ and ‘20200624’ and A.fDeptID IN (7,8,9,10,11,)
GROUP BY A.fOptDateStr,A.fFilialeName,A.fModeName

检测和修复索引

我们分析了上面的SQL命令,发现记录数最多数据量最大的表是 (Pos_RetailPayDetail)和(Pos_RetailBill),对这2个表去检测索引健康是非常有必要的,检测方法参见检测数据库的索引健康程度

修复SQL表的索引其实是一个比较复杂的技术性操作,为此我准备了一个非常简单方案,让维护工作变成一个很容易的事情,参见——修复SQL表索引

发表评论

电子邮件地址不会被公开。 必填项已用*标注