데이터 베이스 MySQL 튜토리얼 一个实例讲解MySQL查询优化(三)_MySQL

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

Jun 01, 2016 pm 01:59 PM
회사 어떻게 질문

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

 

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

뜨거운 기사 태그

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

Xuexin.com에서 학업 자격을 확인하는 방법 Xuexin.com에서 학업 자격을 확인하는 방법 Mar 28, 2024 pm 04:31 PM

Xuexin.com에서 학업 자격을 확인하는 방법

12306 항공권 구매 내역 확인 방법 항공권 구매 내역 확인 방법 12306 항공권 구매 내역 확인 방법 항공권 구매 내역 확인 방법 Mar 28, 2024 pm 03:11 PM

12306 항공권 구매 내역 확인 방법 항공권 구매 내역 확인 방법

Hands 앱은 어느 회사의 제품인가요? Hands 앱은 어느 회사의 제품인가요? Mar 13, 2024 am 11:10 AM

Hands 앱은 어느 회사의 제품인가요?

Blue Star Travel Yao는 어느 회사에 속해 있나요? Blue Star Travel Yao는 어느 회사에 속해 있나요? Mar 22, 2024 pm 03:41 PM

Blue Star Travel Yao는 어느 회사에 속해 있나요?

Apple 휴대폰의 활성화 날짜를 확인하는 방법 Apple 휴대폰의 활성화 날짜를 확인하는 방법 Mar 08, 2024 pm 04:07 PM

Apple 휴대폰의 활성화 날짜를 확인하는 방법

Oracle을 사용하여 테이블이 잠겨 있는지 쿼리하는 방법은 무엇입니까? Oracle을 사용하여 테이블이 잠겨 있는지 쿼리하는 방법은 무엇입니까? Mar 06, 2024 am 11:54 AM

Oracle을 사용하여 테이블이 잠겨 있는지 쿼리하는 방법은 무엇입니까?

MySQL과 PL/SQL의 유사점과 차이점 비교 MySQL과 PL/SQL의 유사점과 차이점 비교 Mar 16, 2024 am 11:15 AM

MySQL과 PL/SQL의 유사점과 차이점 비교

Tongshen Coin의 최신 가격을 확인하는 방법은 무엇입니까? Tongshen Coin의 최신 가격을 확인하는 방법은 무엇입니까? Mar 21, 2024 pm 02:46 PM

Tongshen Coin의 최신 가격을 확인하는 방법은 무엇입니까?

See all articles