关于查询优化问题总结

1、

(1)背景：

最近在做项目，因为过滤规则以及代码问题，导致查询速度异常缓慢，根据账单号、摘要号查询对应账单分期几乎需要30s钟，然后点击上一页、下一页差不多需要50s，但是数据库中数据量不是很大，从视图取数据，视图为账单主表与分期表连接查询得到，（视图3000条数据，主表3000条，分期表6000条）

(2)优化方案：

1）代码优化，找寻哪块代码特别慢，慢的原因是什么，能够优化

2)sql优化

3)数据库表添加索引

4)索引执行情况(Explain/SQL执行计划)

(3)解决方案：一条条实践

1)代码优化方面：

根据断点调试一步步跳，发现主要代码问题在于页面内容从视图中取，但是分页查询出一页数据后，需要针对查询出的这页数据进行遍历赋值(页面需要展示一个结付标识，当分出公司账单已结算，则分入公司账单分期可结付标识为是)，每次赋值时需要再次查库根据数据库值来判断赋值情况，本来遍历赋值就慢，现在还需要查库后再赋值就更慢了。

解决方案：将可结付标识字段存入对应表中取代实时查库可以提升查询时间(后来证明该方案也很糟糕)

修改范围：在账单生成时设置指定默认值，在对账、结算保存时将结付标识存库；

但是当保存后点击修改按钮，做修改操作/删除操作时也需要对应更改库中存取可结付标识值；

当对账单、结算单点击注销按钮时同样要修改表中可结付标识字段值；

结果：从之前20s+时间成功变为10s+，查询速度快了一倍，但是依然缓慢；

2)SQL优化：

规则：对于每个业务账单存在不含税balance、保费VAT两项值进行结算，可以存在部分结算情况

在对应mapper.xml文件中存在存在子查询存在，比如：exists validFlag=‘0’ /结算单中已存在(accNo、serialNo=paymentId),连接查询修改为左连接查询

结果：尽心意，听天命

3)添加索引：

页面查询来源为视图，但是视图中不能添加索引，当时蒙了一阵，后来反应过来：

视图内容来自主账单表，分期表连表查询，当视图中不能添加视图，但是可以在主账单表、分期表中添加索引，添加索引位置为：

查询视图常用列：比如，查询视图常用：accNO、serialNo、accType、upperAccNo、accNo、claimNo、abstractNo添加索引

ALTER TABLE `acc_f_payment_info` ADD INDEX index_f_payment_info (accNo,serialNo);
ALTER TABLE `acc_f_main_info` ADD INDEX index_f_main_info (accType, upperAccNo,accNo,claimNo,abstractNo);

创建索引要求：常用但不会频繁修改的列添加索引，正常一张表中最多添加五个索引，每个索引最多五个属性；

对应每张表中主键默认为主键索引！

实践查询是否有效：执行Explain,SQL执行计划

如何执行：只需要在执行SQL之前添加Explain即可查看