一个实例讲解MySQL查询优化

　2007-11-11 15:11:03　来源：WEB开发网　　　

核心提示： 实例分析下面我们举一个制造公司的例子来说明如何进行查询优化，制造公司数据库中包括3个表，一个实例讲解MySQL查询优化(3)，模式如下所示:1.part表零件号　　　　　零件描述　　　　　　　　其他列(part_num)　(part_desc)　　　　　　(other column)102，032　　　Seage

　实例分析

下面我们举一个制造公司的例子来说明如何进行查询优化。制造公司数据库中包括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