1.优化背景
某信息查询涉及到8个头行表联合查询,逻辑为串行化执行。
表设计未设置相关索引,SQL查询并未使用分组筛选(历史问题)。
测试环境上线后信息出现响应慢的情况,条件查询超过3分钟。
严重影响用户操作体验,因此需要对此接口进行优化。
2.利用explain关键字查看sql性能
通过explain用详细项目编码查询,结果发现某头表sql的全局扫描达到了3个,rows=634*86*14861,这三个左联表字段并没有相关索引设置
explain sql语句;
后续发现a,b,c相关头行sql都有未经过索引的扫描,导致大量数据全盘扫描,严重减缓效率
3.优化过程
优化前sql查询速度为20s
根据对应的”ALL“的表,进行表设计索引添加,那么索引有这么多,我们应该怎么添加,添加什么类型呢?
当联查索引只有一个时,我们可以添加为普通索引,如果有多个则可以添加联合普通索引,如果这个字段是唯一的,我们可以添加为唯一索引
优化后,我们设置的索引都命中了,并且查询的rows目前=3*81,跟之前的查询行效率大大提高。
优化后查询速度为0.52s,提升了19.5s,完全满足我们的接口需求
4.建表、索引的原则、编写高效SQL的注意点
对于建表大家都不陌生,这里主要说明一下几点注意实现:
1、大数据字段最好剥离出单独的表,以便影响性能
2、使用varchar,代替char,这是因为varchar会动态分配长度,char指定为20,即时你存储字符“1”,它依然是20的长度
3、给表建立主键,看到好多表没主键,这在查询和索引定义上将有一定的影响
4、避免表字段运行为null,如果不知道添加什么值,建议设置默认值,特别int类型,比如默认值为0,在索引查询上,效率立显。
5、建立索引,聚集索引则意味着数据的物理存储顺序,最好在唯一的,非空的字段上建立,其它索引也不是越多越好,索引在查询上优势显著,在频繁更新数据的字段上建立聚集索引,后果很严重,插入更新相当忙。
6、组合索引和单索引的建立,要考虑查询实际和具体模式.
5.总结
可见,所谓优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。
1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
2.in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。
3.要善于使用存储过程,它使SQL变得更加灵活和高效