分类： Mysql/postgreSQL
2005-08-18 11:23:04
下面我们举一个制造公司的例子来说明如何进行查询优化。制造公司数据库中包括3个表，模式如下所示:
1.part表
零件号?????零件描述????????其他列
(part_num)?(part_desc)??????(other column)
102，032???Seageat 30G disk?????……
500，049???Novel 10M network card??……
……
实例分析
下面我们举一个制造公司的例子来说明如何进行查询优化。制造公司数据库中包括3个表，模式如下所示:
1.part表
零件号?????零件描述????????其他列
(part_num)?(part_desc)??????(other column)
102，032???Seageat 30G disk?????……
500，049???Novel 10M network card??……
……
2.vendor表
厂商号??????厂商名??????其他列
(vendor _num)?(vendor_name) (other column)
910，257?????Seageat Corp???……
523，045?????IBM Corp?????……
……
3.parven表
零件号?????厂商号?????零件数量
(part_num)?(vendor_num)?(part_amount)
102，032????910，257????3,450,000
234，423????321，001????4，000，000
……
下面的查询将在这些表上定期运行，并产生关于所有零件数量的报表:
SELECT part_desc，vendor_name，part_amount
FROM part，vendor，parven
WHERE part.part_num=parven.part_num
AND parven.vendor_num = vendor.vendor_num
ORDER BY part.part_num
如果不建立索引，上述查询代码的开销将十分巨大。为此，我们在零件号和厂商号上建立索引。索引的建立避免了在嵌套中反复扫描。关于表与索引的统计信息如下:
表?????行尺寸???行数量?????每页行数量???数据页数量
(table)?(row size)?(Row count)?(Rows/Pages)?(Data Pages)
part????150?????10，000????25???????400
Vendor???150?????1，000???? 25???????40
Parven???13????? 15，000????300?????? 50
索引?????键尺寸???每页键数量???页面数量
(Indexes)?(Key Size)?(Keys/Page)???(Leaf Pages)
part?????4??????500???????20
Vendor????4??????500???????2
Parven????8??????250???????60
看起来是个相对简单的3表连接，但是其查询开销是很大的。通过查看系统表可以看到，在part_num上和vendor_num上有簇索引，因此索引是按照物理顺序存放的。parven表没有特定的存放次序。这些表的大小说明从缓冲页中非顺序存取的成功率很小。此语句的优化查询规划是:首先从part中顺序读取400页，然后再对parven表非顺序存取1万次，每次2页(一个索引页、一个数据页)，总计2万个磁盘页，最后对vendor表非顺序存取1.5万次，合3万个磁盘页。可以看出在这个索引好的连接上花费的磁盘存取为5.04万次。
实际上，我们可以通过使用临时表分3个步骤来提高查询效率:
1.从parven表中按vendor_num的次序读数据:
SELECT part_num，vendor_num，price
FROM parven
ORDER BY vendor_num
INTO temp pv_by_vn
这个语句顺序读parven(50页)，写一个临时表(50页)，并排序。假定排序的开销为200页，总共是300页。
2.把临时表和vendor表连接，把结果输出到一个临时表，并按part_num排序:
SELECT pv_by_vn，* vendor.vendor_num
FROM pv_by_vn，vendor
WHERE pv_by_vn.vendor_num=vendor.vendor_num
ORDER BY pv_by_vn.part_num
INTO TMP pvvn_by_pn
DROP TABLE pv_by_vn
这个查询读取pv_by_vn(50页)，它通过索引存取vendor表1.5万次，但由于按vendor_num次序排列，实际上只是通过索引顺序地读vendor表(40+2=42页)，输出的表每页约95行，共160页。写并存取这些页引发5*160=800次的读写，索引共读写892页。
3.把输出和part连接得到最后的结果:
SELECT pvvn_by_pn.*，part.part_desc
FROM pvvn_by_pn，part
WHERE pvvn_by_pn.part_num=part.part_num
DROP TABLE pvvn_by_pn
这样，查询顺序地读pvvn_by_pn(160页)，通过索引读part表1.5万次，由于建有索引，所以实际上进行1772次磁盘读写，优化比例为30∶1。笔者在Informix Dynamic
Sever上做同样的实验，发现在时间耗费上的优化比例为5∶1(如果增加数据量，比例可能会更大)。
小结
20%的代码用去了80%的时间，这是程序设计中的一个著名定律，在数据库应用程序中也同样如此。我们的优化要抓住关键问题，对于数据库应用程序来说，重点在于SQL的执行效率。查询优化的重点环节是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。
