主要介绍IBM DB2数据库,其实所有数据库大同小异,算是工作中的一些记录,一种总结,凑合看看吧。有时间再去整理一下。

Tips:以下SQL使用的表名,列名都经过本人修改,非真实生产环境定义,只是为了易于理解。

慎用 select * from 语句

这是初学者甚至很多老手都这么写的方法,但是对于column少的表来说,这么写,对于性能影响并不是很大,但有个问题在于,如果column数量比较多呢,超过50个column的话,这么写,检索速度会非常慢。
举个最简单的例子

1
2
3
/*例1*/
select * from company.product_price
where procduct_name like 'itemName%' order by price_amout

其实初学者这么写蛮规范的,但是对于一个数据量超过1000万条的表来说,这么写无异于增加了IO和CPU消耗,况且我最后还sort了一下,按照price_amout排序、因为这么写的话,DB2就需要把所有列都从硬盘上(物理介质)复制到 DB2的内存中来进行处理并且返回给用户。除非真的需要读取表中的所有列,否则基于提高查询性能的考虑,在写 SQL 语句的时候应该尽量避免使用select * from这样的情况。

1
2
3
4
5
6
/*例2*/
select * from company.product_price
where procduct_name like 'itemName%' order by price_amout
/*例3*/
select procduct_name,product_price,product_level,pruoduct_supplier from company.product_price
where procduct_name like 'itemName%' order by price_amout

我们看例2, 它是这么处理的,DB2在读取索引之后,必须再去对表中进行一次 Fetch 操作,读取那些索引中不存在的列数据了,但是我们并不需要这些数据,这样默认的去fetch一下,无疑会对性能产生很大影响,对比例3,它明确指出了在结果中希望得到的列procduct_name,product_price,product_level,pruoduct_supplier,而这些列已经全部包含在索引中,所以数据库采用了Index-Only的扫描方式,仅仅读取了索引,不再需要对表本身进行fetch操作,从而使得效率得到了很大的提升。

避免在索引列使用逻辑操作符以及函数

避免在使用NOT,<>,!= ,IS NULL和IS NOT NULL

尽量避免使用数据类型转换

避免在索引列上出现数据类型转换

查询时尽可能的用上主键

这个不多说了

让fetch效率更高

FETCH FIRST n ROWS ONLY 表示只取回结果集当中的前 n 条记录,相信大家都是这么写的,我如果只取10条,可能1秒就返回了,但是如果where条件很多,我需要fetch 10000条呢?这时候,optimze for 10000 rows 这个语句就非常重要了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*例4*/
select procduct_name,product_price,product_level,pruoduct_supplier from company.product_order
where order_stats = 'delivered'
and procduct_name like 'itemName%'
and product_price > '50'
and product_level = 'S'
and pruoduct_supplier = 'vendor'
and order_credt > '20180101'
fetch first 10000 rows only
/*例5*/
select procduct_name,product_price,product_level,pruoduct_supplier from company.product_order
where order_stats = 'delivered'
and procduct_name like 'itemName%'
and product_price > '50'
and product_level = 'S'
and pruoduct_supplier = 'vendor'
and order_credt > '20180101'
fetch first 10000 rows only
optimze for 10000 rows

而 optimze for 10000 rows 可能并不被一般用户所熟悉,它的作用是告诉DB2的优化器尽可能以最快方式来返回结果中的前10000条记录,但是需要注意的是,最终结果中的所有记录都会被返回,这是它与fetch first 10000 rows

避免非必要的排序

在很多时候,应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,可以避免排序的步骤,当以下的情况发生时,排序就不能省略:
1.)索引中不包括一个或几个待排序的列;
2.)group by或order by子句中列的次序与索引的次序不一样;
3.)排序的列来自不同的表。
为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表,尽管有时可能影响表的规范化,但相对于效率的提高是值得的。如果排序不可避免,那么应当试图简化它,如缩小排序列的范围等。

根据权值来优化查询条件

=在索引的情况下都会进行索引扫描,所以效率总是高的。
like当模糊查询为右模糊,比如’abc%’时,扫描索引,高效。当模糊查询含左模糊时,比如’%abc’,进行全表扫描,低效。
in的作用等同于or,也是进行索引扫描,高效,in还可以连接查询结果集,这时往往会和exists做比较。
权值