SQL性能优化整合持续更新

 

    性能优化是面试、程序员之间交流的重要话题。数据到了一定量级后和用户达到一定数量频繁的请求某些数据查询等操作表，数据库是成为优化的必经之路。众说纷纭，打算从表设计到查询、业务操作等方面入手。数据库从设计到编码到使用这些过程中，及早发现问题比到后面使用后找到问题付出的代价要小，随着时间的发展，越晚发现问题付出的代价越大。

设计表结构时优化：

1)一般来说，数据库的三大范式可以解决一部分的性能问题

第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。
第2规范: 每个非关键字段必须依赖于主关键字，每张表必须有一个唯一的主键，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。
第3规范: 一个非关键字段不能依赖于另一个非关键字段，减少关联关系中的数据冗余。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

2)字段设计的时候，一定要注意字段选取的范围，能用int类型的就不要使用varchar、nvarchar。text和img这些应该在高版本没有了吧，慎重使用。就算要用，把他们用txt文件或者xml文件存到服务器，数据库存链接就好。尽量满足现在以后的数据长度。数据保存的时候尽量给默认值，因为查询的时候要排除null，少一个查询条件多一点速度。尽量减少自增字段，不利于数据迁移和并发量高等一些数据操作

 

3)尽量少使用表删除更新联动。表更新删除联动功能并不是一个线程安全的操作，如果用户操作并发量大，那么极大可能会操作出错而且这个错误系统是无法定位的，无法保证数据的完整性

4)合理使用触发器，触发器的使用完全是为了数据的完整性，用得合理事半功倍，而且还是个多线程操作。但是触发器增多了也会对系统有负担，尤其是那种操作频繁的表

5)合理使用索引，索引像个目录一样，其数据结构是树，查询速度也是很可观的。前期数据量少的时候感觉不出来，数据量一大起来，真的可以为所欲为。当然索引不是越多越好，合理就行

关于索引的选择，应该注意：
1.根据数据量决定哪些表需要增加索引，数据量小的可以只选择主键。
2.根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
3.把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
4.一个表不要加太多索引，因为索引影响插入和更新的速度。

 

6)如果考虑数据库与项目服务器分离的时候尽量考虑硬件的内存带宽和硬盘存储量

7)当然主外键尽量少使用联合主键或者外键，查询数据的时候少一个条件也是一种速度的提升，也听说过查询速度跟联合主键的位置也有一定的关系，这个正在考证。

8)在表设计的时候避免使用null，可以设置默认值，因为在做索引查询的时候会消耗更多的内存，减少查询时候的null排除操作

业务操作方面优化：

1)尽量少使用select *，勤快点用select a，b，c。。。，因为*是一个通配符，把所有的信息全部返回出来实行全表检索，这样增加不仅增加了内存消耗也会把一些不必要的字段暴露给客户端，查询的使用最好是把列名标记出来减少全表检索

2)避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序

3)查询的时候尽量减少时间转换、字符拼接、数据类型转换操作。当然必要合理不得已等情况下

4)统计select count（a）、select count（*）建议用select count（1）

5)where后面尽量少用 null判断和少用case when，前面讲过尽量给默认值。

6)where查询的时候尽量避免索引字段的类型转换、使用函数、数值计算或字符拼接。因为表频繁删除新增等操作索引有空缺，这些操作会给查询带来很重的负担

7)避免多级的嵌套查询：select （select count（1）from A）as a from （select * from （select a from a where））inner join ...

8)避免使用子查询，having、in。。能用exists代替的、能用join等连接查询代替。having和in真的是性能油老虎，全局反复查询

9)在连接查询条件里面能完成的，尽量不要到where里面去筛选，这样两次全局查询很吃性能

10)避免使用行转列操作，经常发现很多小伙伴做统计的时候脑袋都要炸了。。。我有个办法，sql最好保存统计信息或者后端保存xml格式的统计信息，定时执行统计操作，把数据保存起来，尤其是这个统计信息操作比较频繁的时候。很有效果

11)数据库操作时，尽量不用游标，能用循环或者CASE WHEN转换的尽量吧。

12)减少delete操作，原因很简单，delete操作会破坏索引结构留下索引碎片需要定时清理，而且容易出现误操作

13)适当的使用视图，视图本身就是一张虚拟表没有实际的数据存储，所以每次查询视图的时候系统会添加一个临时表把这个视图存入缓存中，一定时间后这个缓存被清理

14)尽量减少多表关联，多表关联非常影响查询速度

15)一些复杂的数据结构处理应该放在前端或者后端处理，减少数据库的开销

16)合理使用like查询，name like '%张三%'这样的查询执行不到索引，只有 name like '张%' 这样的查询才能执行到索引

17)查询中or 、<>、!= 不会执行到索引，可以使用in来代替他们，当然in的数据匹配条数尽量不要超过200条

     在查询阶段我们一般是观察这个查询的执行计划，看哪块执行影响速度，然后有针对性的去优化它。还可以通过一些sql查询监控获取一些比较消耗内存的查询和查询次数较多的sql语句，适当的做计划缓存，一把双刃剑。

数据库的优化，从设计就应该开始，找到问题减少以后发现问题执行的时间效率。优化应该要配合web、后端、数据库、硬件这些方面来实施，甚至nosql，数据库分布式，数据库缓存等一些有效的方法提高数据访问速度，提高用户的使用效率。