一个实例讲解MySQL查询优化（三）

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

实例分析

下面我们举一个制造公司的例子来说明如何进行查询优化。制造公司数据库中包括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的执行效率。查询优化的重点环节是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。