当前位置:Gxlcms > mysql > 一个实例讲解MySQL查询优化(三)_MySQL

一个实例讲解MySQL查询优化(三)_MySQL

时间:2021-07-01 10:21:17 帮助过:27人阅读

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

人气教程排行