2014年小结之sql语句优化

之前接手一个数据统计处理的小程序,本来逻辑上并没什么,只是数据量略大,某些表的数据达到了千万级别..因为是统计,所以免不了各种连接各种查询,结果这个小程序写完后运行一次要1个小时..这的确有点出乎意料,所以着手优化一下..

1.添加索引

平时不注意或是数据量比较小的时候可能会忽略这个,加或是不加基本没差别,但是当数据量很大的时候差别就非常明显,没有索引的情况下,在1万条以内和10万条以上的数据中查询所用的时间差别已经能分辨出了.这里有2点需要特别注意:

1)默认情况下我们创建表,数据库会自动给我们添加一个默认索引,但是当采用create table 表名 as select 语句创建表时,数据库是不会自动创建索引的,此时我们需要手动添加索引.

2)哪些字段需要添加索引?那些在我们查询语句中起到桥梁(连接)作用的字段需要添加索引.因为只有在查询条件中包含有索引字段才能享用索引带来的速度提升.

可以说索引是提高查询速度最明显最简单的方法,它可以令一个数分钟的查询在毫秒内完成,效率提升不是一点半点..

2.缩小查询集合

既然是查询,那一定有要查询的集合,也就是 from 后面的集合.如果能缩小这个集合,那么查询遍历一遍的时间也会缩减不少,整体的查询时间自然会减少.主要有几个手段:

1)创建临时查询表.如果一个表的数据量很大,而我们只在其中满足某些特定条件的数据集合中查询,那么我们完全可以首先从这个表中查询出所有满足特定条件的数据集合并创建成一个表,典型应用就是create table xx as select XX where xx ,这样我们就得到一个比原始表小很多的临时表,之后的所有查询工作都从这张新表中进行即可..

2)优化查询语句顺序.where后的条件语句是从右向左执行的,所以我们可以把能排除掉更多数据的条件放到最右面,这样执行后余下的数据集合会更小,接下来的条件查询也就更快.同一思想,当我们采用子查询的时候,让某些子查询排除掉更多的数据也会让整体效率提高.

3)去重,去重本来是一个比较耗时的操作,但是如果某个集合是反复被使用的,那么对这个集合进行去重处理也会带来效率的提升.

 

经过优化后那个小程序在5分钟内就执行完毕..可见sql语句的优化带来的提升还是很可观的..当然由于我这个小程序一开始就处理的不是太好,所以优化后提升很多,但是这也说明,如果注意sql语句的优化,在程序的处理时候就多加注意的话,一定程度上能避免不少性能问题..