SQL优化

作者 knight 日期 2018-01-22
SQL优化

知事故而不世故

优化指引

  • 每个表都要在关键字段(频繁用于查询的字段)建立索引。
    查询字段不用*号,写出具体的字段名;只查询出需要用到的字段,查询字段越少查询速度就会越快。
  • 多表查询,尽量给每个表取别名,并在字段前加别名,减轻数据库用来解析每个字段归属表与歧义的时间。(经常有多个表有相同名的字段名)
  • 数据库一般按照从右到左的顺序处理from子句中的表名,from子句中写在最后的表(基础表)将被最先处理。在from子句中包含2个表的情况下,必须选择记录条数较少的表作为基础表;如果有3个以上的表连接查询,那就需要选择交叉表作为基础表。(交叉表是指那个被其他表所引用的表)。
  • where子句中查询条件:用于表与表之间的连接部分,写在where子句靠前位置,能过滤掉越多记录数的查询条件,越写在WHERE子句的末尾。
  • 在where子句里,有索引的字段不要进行数据转换后再进行比较(比如用函数把数字转成字符,再比较),这样会失掉索引的作用。
  • 用到group by的sql,select后面的字段顺序和数量与group by后面的字段顺序和数量一致。
  • 能在on条件过滤掉的(如关联查询时)数据,不留到where;能在where条件中过滤掉的数据,不留到having过滤;having一般与一些聚合函数一起使用。
  • 避免在索引列判断is null 或is not null,否则索引将失效。
  • 比较运算,“>=”符号比“>”效率要高。
  • 能不用like 或 in ,就尽量不用。
  • 通常情况下,用union替换where子句中的or将会起到较好的效果。对索引列使用or将造成全表扫描,即索引失效,可考虑用union 把or分成两个sql,这样保证其中一个或者两个都可以用到索引。
  • 如果可用in与or,尽量用in,如:
    高效:select … from tableA where A in ( 10,20,30)
    低效:select … from tableA where A=10 or A=20 or A=30
  • 用EXISTS替代IN、用NOT EXISTS替代NOT IN
    高效:select … from tableA where exists(select 1 from tableB where tableA.A=tableB.A and tableB.B=’X’))
    低效:select … from tableA where tableA.A in(select tableB.A from tableB where tableB.B=’X’)
  • 子查询里使用group by会加快查询速度,尽量少用或不用distinct,如下边情况:
    select top 100 t1.* from table1 t1 where t1.FId in (
    高效:select t2.FId from table2 t2 where t2.FName=‘xxx’ group by t2.FId
    低效:select distinct t2.FId from table2 t2 where t2.FName=‘xxx’
    )。
  • 不要写过于复杂的SQL语句。有限度的在from里使用嵌套子查询,就是from (select f1,f2,f3 from table1 where f4=‘xx’)p 的方式。遇到需要用到多个这样的嵌套查询时,建议使用临时表处理。使用临时表会提高查询速度,大数据量查询效果更明显。注意:临时表使用完后应该drop掉。
  • 多表查询时,尽量用表连接替换exists子句、子查询;能用inner join 的,尽量不用left/right join。
  • count()快于conut(1),如果列有索引,count(列)快于count()
  • 如果是全表数据删除,truncate比delete效率高,truncate 删除数据是不作日志记录的。

编程规范

应用程序里写SQL:

  • 禁止直接使用select * from Table1而不加条件限制;需要什么字段应写明字段名称。
  • 多表查询,查询条件里必须包含所有的表,禁止在where 部分仅写个where 1=1 而不加其它条件,因为这样相当做了一个笛卡尔积,如果两个表的记录都是1万,则查询的结果将是1亿行,这样就容易把数据库的tempdb爆满,引发故障并导致系统不能用。
  • 在代码里编写SQL语句,在进行多表关联查询时,表的后面必须加上别名;在字段名前也要加上表别名,明确字段归属的表。 别名的命名按次序a,b,c,d…,或者用简写,如tbMm1Traffic tfc, tbNeIp ip。
  • 为考虑数据库兼容与移植问题:
    • 编写SQL时,字段名、表名必须遵循大小写敏感的规则,就算当前所用的数据库可以忽略大小写(例如MYSQL,oracle,SqlServer,DB2)。
    • 要求对字段起别名,并且别名也强制区分大小写,从而保证界面元素名称与字段的对应。如:Select iStaffId “iStffId”, sStaffName “sStaffName” from tbOsStaff。
    • 要将一个完整的SQL实例做为注释写在代码里(编程时,查询条件部分多为动态拼装,所以源码里的SQL一般不是完整的SQL实例,可以做一次完整查询,然后从控制台copy一个完整实例贴在注释里)。–此点为曹雄所提。
  • 编写比较复杂的查询语句时,需要格式化(加上回车换行符)和加上详细的注释说明。
  • 一些枚举、基础配置表的翻译,不要使用数据库表关联的方式翻译;两个以上字段是这种情况,就应该单独查询出来,然后在程序中转换。
  • 不要将所有的应用压力都推向数据库。需要做比较复杂的运算时,不要企图用一个SQL语句实现或通过编写一批十分复杂的SQL实现,例如:数据的行转列,完全可以使用程序语言的灵活性处理。
  • 开发时要关注运行速度慢的SQL(标准:超过1秒的SQL),着情进行优化处理,对于不能优化的,用程序处理。
  • 查询字段的选择,尽量使用ID或有索引的字段,或者说对频繁查询的字段,应该加索引,这样可以大大加快查询的速度。
  • 尽量用短事务。在事务里尽量只写新增、删除、修改的语句,少写查询SQL。
  • 如果要判断某一字符字段是否为空,应该使用以下的判断条件:rtrim(fieldName) is null。如:rtrim(' ') is null返回的是true; ' ' is null 返回的是false。
  • 不要在in 条件里放过多的条件(in 里是子查询除外),这样有可能使查询失败,例如Sybase里in的条件不能超过300个。其他的数据库具体数目不清楚,不过在编写代码的时候必须要留意是否会有这样的情况出现。
  • 表里的时间字段,如果是取当前时间的,使用数据库服务器的时间。
  • 手工键入的字符串查询条件都要求模糊匹配。如果还要求不区分大小写,可用upper(fieldName) like upper(‘%+查询条件+%’),查询字段和条件都用upper进行转换,注意索引失效的情况。
  • 尽量写标准的SQL,如: *=,= null 这些sybase 特有的语法,而标准SQL: a left join b on …,is null。