程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 探討MySQL優化器對索引和JOIN次序的選擇

探討MySQL優化器對索引和JOIN次序的選擇

編輯:MySQL綜合教程

探討MySQL優化器對索引和JOIN次序的選擇。本站提示廣大學習愛好者:(探討MySQL優化器對索引和JOIN次序的選擇)文章只能為提供參考,不一定能成為您想要的結果。以下是探討MySQL優化器對索引和JOIN次序的選擇正文


本文經由過程一個案例來看看MySQL優化器若何選擇索引和JOIN次序。表構造和數據預備參考本文最初部門"測試情況"。這裡重要引見MySQL優化器的重要履行流程,而不是引見一個優化器的各個組件(這是另外一個話題)。

   我們曉得,MySQL優化器只要兩個自在度:次序選擇;單表拜訪方法;這裡將具體分析上面的SQL,看看MySQL優化器若何做出每步的選擇。

explain
select *
from
 employee as A,department as B
where
   A.LastName = 'zhou'
 and B.DepartmentID = A.DepartmentID
 and B.DepartmentName = 'TBX';

1. 能夠的選擇

   這裡看到JOIN的次序可所以A|B或許B|A,單表拜訪方法也有多種,關於A表可以選擇:全表掃描和索引`IND_L_D`(A.LastName = 'zhou')或許`IND_DID`(B.DepartmentID = A.DepartmentID)。關於B也有三個選擇:全表掃描、索引IND_D、IND_DN。
2. MySQL優化器若何做
2.1 概述

   MySQL優化器重要任務包含以下幾部門:Query Rewrite(包含Outer Join轉換等)、const table detection、range analysis、JOIN optimization(次序和拜訪方法選擇)、plan refinement。這個案例從range analysis開端。
2.2 range analysis

   這部門包含一切Range和index merge本錢評價(參考1 參考2)。這裡,等值表達式也是一個range,所以這裡會評價其本錢,盤算出found records(表現對應的等值表達式,年夜概會選擇出若干筆記錄)。

   本案例中,range analysis會針對A表的前提A.LastName = 'zhou'和B表的B.DepartmentName = 'TBX'分離做剖析。個中:

表A A.LastName = 'zhou' found records: 51
表B B.DepartmentName = 'TBX' found records: 1

   這兩個前提都不是range,然則這裡盤算的值依然會存儲,在前面的ref拜訪方法評價的時刻應用。這裡的值是依據records_in_range接口前往,而關於InnoDB每次挪用這個函數都邑停止一次索引頁的采樣,這是一個很消費機能的操作,關於許多其他的關系數據庫是應用"直方圖"的統計數據來防止此次操作(信任MariaDB後續版本也將完成直方圖統計信息)。
2.3 次序和拜訪方法的選擇:窮舉

   MySQL經由過程列舉一切的left-deep樹(也能夠說一切的left-deep樹就是全部MySQL優化器的搜刮空間),來找到最優的履行次序和拜訪方法。
2.3.1 排序

   優化器先依據found records對一切表停止一個排序,記載少的放後面。所以,這裡次序是B、A。
2.3.2 greedy search

   當表的數目較少(少於search_depth,默許是63)的時刻,這裡直接墮落為一個窮舉搜刮,優化器將窮舉一切的left-deep樹找到最優的履行籌劃。別的,優化器為了削減由於搜刮空間宏大帶來偉大的窮舉消費,所以應用了一個"偷懶"的參數prune_level(默許翻開),詳細若何"偷懶",可以參考JOIN次序選擇的龐雜度。不外至多須要有三個表以上的聯系關系才會有"偷懶",所以本案例不實用。
2.3.3 窮舉

   JOIN的第一個表可所以:A或許B;假如第一個表選擇了A,第二個表可以選擇B;假如第一個表選擇了B,第二個表可以選擇A;

   由於後面的排序,B表的found records更少,所以JOIN次序窮舉時的第一個表先選擇B(這個是有講求的)。

(*) 選擇第一個JOIN的表為B
  (**) 肯定B表的拜訪方法
    由於B表為第一個表,所以沒法應用索引IND_D(B.DepartmentID = A.DepartmentID),而只能應用IND_DN(B.DepartmentName = 'TBX')
      應用IND_DN索引的本錢盤算:1.2;個中IO本錢為1。
      能否應用全表掃描:這裡會比擬應用索引的IO本錢和全表掃描的IO本錢,前者為1,後者為2;所以疏忽全表掃描
    所以,B表的拜訪方法ref,應用索引IND_D

  (**) 從殘剩的表中窮舉選出第二個JOIN的表,這裡殘剩的表為:A
  (**) 將A表參加JOIN,並肯定其拜訪方法
    可使用的索引為:`IND_L_D`(A.LastName = 'zhou')或許`IND_DID`(B.DepartmentID = A.DepartmentID)
    順次盤算應用索引IND_L_D、IND_DID的本錢:
    (***) IND_L_D A.LastName = 'zhou'
          在range analysis階段給出了A.LastName = 'zhou'對應的記載約為:51。
          所以,盤算IO本錢為:51;ref做IO本錢盤算時會做一次修改,將其修改為worst_seek(參考)
          修改後IO本錢為:15,總本錢為:25.2
    (***) IND_DID B.DepartmentID = A.DepartmentID
          這是一個須要曉得後面表的成果,能力盤算的本錢。所以range analysis是沒法剖析的
          這裡,我們看到後面表為B,found_record是1,所以A.DepartmentID只須要對應一筆記錄便可以了
          由於詳細取值不曉得,也沒有直方圖,所以只能簡略根據索引統計信息來盤算:
            索引IND_DID的列A.DepartmentID的Cardinality為1349,全表記載數為1349
            所以,每個值對應一筆記錄,而後面表B只要一筆記錄,所以這裡的found_record盤算為1*1 = 1
            所以IO本錢為:1,總本錢為1.2
    (***) IND_L_D本錢為25.2;IND_DID本錢為1.2,所以選擇後者為以後表的拜訪方法
  (**) 肯定A應用索引IND_DID,拜訪方法為ref
  (**) JOIN次序B|A,總本錢為:1.2+1.2 = 2.4

(*) 選擇第一個JOIN的表為A
  (**) 肯定A表的拜訪方法
       由於A表是第一個表,所以沒法應用索引`IND_DID`(B.DepartmentID = A.DepartmentID)
       那末只能應用索引`IND_L_D`(A.LastName = 'zhou')
         應用IND_L_D索引的本錢盤算,總本錢為25.2;參考後面盤算;
  (**) 這裡拜訪A表的本錢曾經是25.2,比之前的最優本錢2.4要年夜,疏忽該次序
       所以,此次窮舉搜刮到此停止

   把下面的進程簡化以下:

(*) 選擇第一個JOIN的表為B
  (**) 肯定B表的拜訪方法
  (**) 從殘剩的表中窮舉選出第二個JOIN的表,這裡殘剩的表為:A
  (**) 將A表參加JOIN,並肯定其拜訪方法
    (***) IND_L_D A.LastName = 'zhou'
    (***) IND_DID B.DepartmentID = A.DepartmentID
    (***) IND_L_D本錢為25.2;IND_DID本錢為1.2,所以選擇後者為以後表的拜訪方法
  (**) 肯定A應用索引IND_DID,拜訪方法為ref
  (**) JOIN次序B|A,總本錢為:1.2+1.2 = 2.4

(*) 選擇第一個JOIN的表為A
  (**) 肯定A表的拜訪方法
  (**) 這裡拜訪A表的本錢曾經是25.2,比之前的最優本錢2.4要年夜,疏忽該次序

   至此,MySQL優化器就肯定了一切表的最好JOIN次序和拜訪方法。
3. 測試情況

MySQL: 5.1.48-debug-log innodb plugin 1.0.9

CREATE TABLE `department` (
 `DepartmentID` int(11) DEFAULT NULL,
 `DepartmentName` varchar(20) DEFAULT NULL,
 KEY `IND_D` (`DepartmentID`),
 KEY `IND_DN` (`DepartmentName`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

CREATE TABLE `employee` (
 `LastName` varchar(20) DEFAULT NULL,
 `DepartmentID` int(11) DEFAULT NULL,
 KEY `IND_L_D` (`LastName`),
 KEY `IND_DID` (`DepartmentID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

for i in `seq 1 1000` ; do mysql -vvv -uroot test -e 'insert into department values (600000*rand(),repeat(char(65+rand()*58),rand()*20))'; done
for i in `seq 1 1000` ; do mysql -vvv -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),600000*rand())'; done

for i in `seq 1 50` ; do mysql -vvv -uroot test -e 'insert into employee values ("zhou",27760)'; done
for i in `seq 1 200` ; do mysql -vvv -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),27760)'; done
for i in `seq 1 1` ; do mysql -vvv -uroot test -e 'insert into department values (27760,"TBX")'; done

show index from employee;
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| employee |     1 | IND_L_D |      1 | LastName   | A     |    1349 |   NULL | NULL  | YES | BTREE   |     |
| employee |     1 | IND_DID |      1 | DepartmentID | A     |    1349 |   NULL | NULL  | YES | BTREE   |     |
+----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

show index from department;
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| department |     1 | IND_D  |      1 | DepartmentID  | A     |    1001 |   NULL | NULL  | YES | BTREE   |     |
| department |     1 | IND_DN  |      1 | DepartmentName | A     |    1001 |   NULL | NULL  | YES | BTREE   |     |
+------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

4. 結構一個Bad case

   由於聯系關系前提中MySQL應用索引統計信息做本錢預估,所以數據散布不平均的時刻,就輕易做失足誤的斷定。簡略的我們結構上面的案例:

   表和索引構造不變,依照上面的方法結構數據:

for i in `seq 1 10000` ; do mysql -uroot test -e 'insert into department values (600000*rand(),repeat(char(65+rand()*58),rand()*20))'; done
for i in `seq 1 10000` ; do mysql -uroot test -e 'insert into employee values (repeat(char(65+rand()*58),rand()*20),600000*rand())'; done

for i in `seq 1 1` ; do mysql -uroot test -e 'insert into employee values ("zhou",27760)'; done
for i in `seq 1 10` ; do mysql -uroot test -e 'insert into department values (27760,"TBX")'; done
for i in `seq 1 1000` ; do mysql -uroot test -e 'insert into department values (27760,repeat(char(65+rand()*58),rand()*20))';
done

explain
select *
from
 employee as A,department as B
where
   A.LastName = 'zhou'
 and B.DepartmentID = A.DepartmentID
 and B.DepartmentName = 'TBX';
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys  | key   | key_len | ref         | rows | Extra    |
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+
| 1 | SIMPLE   | A   | ref | IND_L_D,IND_DID | IND_L_D | 43   | const        |  1 | Using where |
| 1 | SIMPLE   | B   | ref | IND_D,IND_DN  | IND_D  | 5    | test.A.DepartmentID |  1 | Using where |
+----+-------------+-------+------+-----------------+---------+---------+---------------------+------+-------------+

   可以看到這裡,MySQL履行籌劃對表department應用了索引IND_D,那末A表射中一筆記錄為(zhou,27760);依據B.DepartmentID=27760將前往1010筆記錄,然後依據前提DepartmentName = 'TBX'停止過濾。

   這裡可以看到假如B表選擇索引IND_DN,後果要更好,由於DepartmentName = 'TBX'僅僅前往10筆記錄,再依據前提A.DepartmentID=B.DepartmentID過濾之。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved