(本文主要讲解SQL的优化,不涉及数据库配置方面的调整,关于数据库如何配置,等以后有时间再写,此内容结合工作经验和网上内容简要总结,不足之处还请多多指正)
1.问什么需要对SQL进行优化?
程序上线使用的初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也无法判断SQL对程序的运行效率有多大,故很少针对SQL进行专门优化(注释1)。而随着时间的积累,业务数据量的增多,SQL的执行效率对程序运行效率的影响逐渐增大,此时对SQL的优化就很有必要。
2.SQL优化的原则
SQL优化的原则就是尽一切可能提高SQL的执行效率
3.SQL优化的方法
1)设计数据库表结构时,要对表做数量级和性能影响预测和评估,表的字段尽量都设置default值,尽量避免default为null,主要防止在执行SQL查询时直接将查询条件设置为null或者not null而导致数据库放弃索引,直接全表扫描;
2)SQL条件中允许出现库函数和左模糊查询,sql条件中库函数会导致数据库执行时放弃索引,直接全表扫描,而左模糊也是,直接就全表扫描了;
3)原则上,SQL条件中避免出现<>,in,not in,exists,not exists等操作符;
4)子查询中的实际查询结果要设置上限要求,且子查询必须要有索引支持,否则子查询也去扫描全表就悲剧了;
5)单个事务的SQL语句数量要有上限要求,不能前台一个提交操作,后台要去插入几十张表的数据,那如果是千万级用户数,基本上就光去插入数据了;
6)同上一条类似,单条SQL语句的数据影响量也要有上限要求,不能一个update操作更新了上千条数据;
7)尽量减少多表关联的SQL,如果必须使用多表关联,也尽量减少关联的表数量,且多表关联时,关联字段必须包含在查询索引中。多表关联SQL中尽量不要使用视图和代理表。
8)充分利用索引,严禁出现表扫描。同时,创建表时也注意索引的字段顺序。
注释1:一般情况下,不同的行业数据量水平相对而言是比较固定的,比如电信行业的数据主要以用户数为基准,按照省级行政单位划分,数量级在千万到亿级之间。而法院的数据主要以案件数为基准,按照市级行政单位划分,数量级在百万到千万之间。(这里只是简要描述一下,实际数据量比这个大得多啊~)一般情况下,系统上线前都会针对不同行业不同地区的数据量做一个估算,然后再通过超大数据量对系统进行性能测试。但是如果遇到技术升级更新或者部署方式发生改变(比如数据集中存放到云上或者分布式部署改为大集中部署),那数据量几乎是十倍百倍的增长,这时候前期SQL执行效率的问题就会暴露出来。
注释2:发现一个博客,写SQL优化描述的也比较到位,可以参考下:http://database.51cto.com/art/201407/445934.htm