数据库如何优化

    数据库优化

    大表拆分小表（按照时间或者类型拆分），区分实时数据表和历史数据表，或者利用分区表

（业务背景，一张实时数据表，存储近百家电厂发电机组的负荷数据（还有其它指标数据），每台机组5分钟生成一条记录，到目前为止已有六百多万条记录。

需求：哪些机组跳机了？（满足条件：前面一次负荷＞＝10％，后面连续三次＜1％（满负荷是100％）

按照条件写出SQL语句，然后一执行---卡成翔了?！

第一次优化：加索引

在网上搜索各种优化方案，感觉加索引比较靠谱（索引类似于字典目录，按照字母顺序可以快速定位），于是就加上了，性能有明显改善，但是在查所有机组时，又卡成翔了?！

分析原因：把几百万条数据按照时间排一下序，光这一点就特别耗时，于是想到了下面的优化。

第二次优化：限定时间段

只查询出最近10分钟的数据（每5分钟一次，时间有延迟，适当放宽），这样一来按理说，数据量已经大大减少，应该快很快，一测试，果然快多了，基本可接受。

剧情往往要一点波折，否则就没有第三次优化了，周一一来，又卡成翔了。

分析原因：还是数据量太大，第二次虽然减少了排序时间，但依然没有减少过滤时间，但是为什么当时比较快的一下就出来，可能是那台机组数据量小，或者最近一直没有最新数据，取最近10分钟也不太精确。

第三次优化：空间换时间

问题的关键是数据量太大，加上算法复杂，需要很多次在六百多万数据中查找数据。回头看看问题，发现只需每个机组的最近4条记录。于是一种思路出来了，把每台机组的最近4条单独存在一个表中，粗算下来，不到1000条数据，于是数据量瞬间减少了6000倍。最后用触发器实现了存储最新记录功能，然后从不到1000条记录中查询，速度立马就上来了）

 

 

一、注释使用：在语句中多写注释，注释不影响SQL语句的执行效率。增加代码的可读性；

二、对于事务的使用：尽量使事务处理达到最短，如果事务太长最好按功能将事务分开执行(如：可以让用户在界面上多几步操作)。事务太长很容易造成数据库阻塞，用户操作速度变慢或死机情况；

三、对于与数据库的交互：尽量减少与数据库的交互次数。如果在前端程序写有循球访问数据库操作，最好写成将数据一次读到前端再进行处理或者写成存储过程在数据库端直接处理；

四、对于SELECT *这样的语句：不要使用SELECT *这样的语句，而应该使用SELECT table1.column1这样的语句，明确指出要查询的列减少数据的通讯量并且这样的代码可读性好，便于维护；

五、尽量避免使用游标：它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作；

六、尽量使用count(1)：count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率；

七、IN和EXISTS：EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询；

注意表之间连接的数据类型：避免不同类型数据之间的连接；

八、尽量少用视图：对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰；

没有必要时不要用DISTINCT和ORDER BY：这些动作可以改在客户端执行，它们增加了额外的开销；

九、避免相关子查询：一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行；

十、代码离数据越近越好：所以优先选择Default,依次为Rules,Triggers, Constraint(约束如外健主健CheckUNIQUE……,数据类型的最大长度等等都是约束),Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快；

十一、插入大的二进制值到Image列：使用存储过程，千万不要用内嵌Insert来插入。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作：方法：

Create procedure p_insert  as  insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善；

十二、Between在某些时候比IN 速度更快：Between能够更快地根据索引找到范围。用查询优化器可见到差别。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一样的。由于in会在比较多次，所以有时会慢些；

十三、对Where条件字段修饰字段移到右边：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边；

在海量查询时尽量少用格式转换；

十四、IS NULL 与 IS NOTNULL：不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的；

十五、建立临时表：如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度;如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。临时表是tempdb数据库实际的表，没有主键、索引，应该避免在临时表中存储大量的数据；

Where中索引的使用：WHERE条件顺序尽量把索引字段放在前面（主键的唯一性最高），复合索引字段顺序与where条件顺序保持一致。Sql自动查找使用那个索引；

十六、外键关联的列应该建立索引：(如子表id)主子表单据肯定要建视图，2个表的关联以2个表中的MainID为关系，所以，需要给子表的MainID单独建索引，这将很大地提高视图的速度。例如Gy_InOutSub中的InoutMainid增加索引；

十七、注意UNion和`UNion all 的区别：UNION all执行效率高；

十八、Insert：Insert into 表 values() 应该为 Insert into 表 (字段) values()；

十九、order by语句：ORDER BY语句决定了如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。