性能优化-SQL优化(索引层面)

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变得更加灵活和高效