資料庫深入學習筆記

來源:互聯網
上載者:User

------------------------------------------- 分割線 -------------------------------------------
資料庫深入學習筆記-SQL執行引擎

以下面的幾個問題,來開始今天的討論:
1,什麼是SQL引擎。
2,什麼是SQL儲存引擎。
3,SQL引擎的工作原理(工作流程)。
4,SQL語句的執行順序。

一,SQL語句的先行編譯
    先行編譯語句PreparedStatement是java.sql包中的一個介面,PreparedStatement繼承了Statement介面。通過Statement執行SQL時,應用
程式首先將SQL語句發送給DBMS,由DBMS進行編譯後執行。PreparedStatement與Statement的不同之處在於,PreparedStatement在建立
PreparedStatement對象時,就指定了SQL。該語句立即發送給DBMS進行編譯。當PreparedStatement執行時,DBMS直接運行編譯後的SQL。也就是
說,這個過程中省去了編譯的過程,所以PreparedStatement的執行速度要高於Statement。
    java.sql.PreparedStatement PreparedStatementSql = null;
    com.mysql.jdbc.PreparedStatement PreparedStatementMysql = null;

1,mysql引擎
    mysql引擎取決於mysql在安裝時是如何被編譯的。要添加一個新的引擎,就必須重新編譯mysql。預設情況下,mysql支援3個引擎:ISAM/MYISAM/
HEAP。另外2種類型是:INNODB/BERKLEY。下面介紹一下這幾種mysql引擎:

ISAM:ISAM是一個定義明確的,且經曆過時間考驗的資料表管理方法,在設計之初,ISAM就考慮到資料庫被查詢的次數
要遠大於修改的次數。因此,ISAM查詢速度快,且不佔用大量的記憶體和硬碟。ISAM的兩個不足之處在於:ISAM不支援
事務,且ISAM不能夠容錯。如果硬碟崩潰了,那麼資料檔案就無法恢複了。
MyISAM:MyISAM是MySQL的ISAM的擴充格式和預設的資料庫引擎。MyISAM提供了ISAM沒有的索引和欄位管理的大量
功能,MyISAM還具有表鎖定功能。MyISAM的表鎖定功能可以最佳化多個並發的讀寫操作,代價是需要運行OPTIMIZE TABLE
命令,來恢複被更新機制所浪費的空間。MyISAM的查詢速度快,這可能是MySQL收到web青睞的原因之一。MyISAM的一個
去缺點就是不能再損壞後不能進行資料恢複。
HEAP:HEAP允許只駐留在記憶體裡的暫存資料表。記憶體的速度遠遠高於硬碟,所以HEAP比ISAM/MyISAM都要快。但是HEAP不穩
定,容易遺失資料。
INNODB:INNODB引擎支援事務,支援外鍵

MyISAM與INNODB的區別:
  * MyISAM不支援事務,INNODB支援事務;
  * MyISAM不支援外鍵,INNODB支援外鍵;
  * MyISAM不支援行級鎖,INNODB支援行級鎖;
  * MyISAM的執行速度快於INNODB;
  * INNODB中不儲存表的具體行數,也就是說,執行SELECT COUNT(*) FROM TABLE時,INNODB要掃描全表。而MyISAM中
儲存有表的總行數,直接讀取即可。但是,如果包含WHERE語句,那麼INNODB和MyISAM都需要掃描全表。
  * 對於AUTO_INCREMENT類型的欄位,INNODB中必須包含只有該欄位的索引,但是在MyISAM表中,可以和其他欄位一起
建立聯合索引。
  * DELETE FROM TABLE時,INNODB不會重建立立表,而是一行一行的刪除。

概括來說,MyISAM適合以下情境:
  * 插入操作少,查詢操作多;
  * 沒有事務;

INNODB適合以下情境:
  * 可靠性要求比較高;
  * 要求事務;

    MySQL支援表層級的資料庫引擎設定,也就是說,對於MySQL中的每一個表格,都可以設定單獨的資料庫引擎。這也是MySQL靈活性的一種體現。
因為我們可以針對應用情境,為不同的表格設定不同的資料庫引擎。比如對於有事務操作的表格,我們需要配置INNODB,對於沒有事務的表格,我
們可以使用MyISAM來提高效能。

2,SQL引擎是如何編譯應用程式發送過來的SQL語句的
    當應用程式,把SQL語句發送給DBMS時,SQL引擎會對這個SQL進行加工處理,然後執行。這裡我們提一個問題:如果一個SQL需要執行多次,SQL引擎
是如何做的呢。好,我們先來看SQL引擎對SQL的處理過程,大致可以分為三步:
第一步:解析SQL語句;
第二步:檢查文法和語義;
第三步:產生DB可以執行的SQL;
這個過程還是有些抽象,我們以Oracle資料庫為例,來進行說明,Oracle資料庫的SQL引擎對SQL的處理過程如下:
第一步:語法檢查;
第二步:語義檢查,查看對象是否存在,判斷類型是否正確;
第三步:檢查使用者權限;
第四步:從許多可能的執行路徑中選擇一條作為執行計畫;
第五步:產生語句的編譯版本;
    從這裡我們可以看出,解析是一個相當耗費系統開銷的操作。尤其是對於資料庫而言。
    先行編譯的SQL,完成了前2步的操作,當需要執行時,SQL引擎只需要進行第三步操作即可。那麼,問題來了,先行編譯的SQL語句存放在哪裡,這個系統
開銷有多大。(Oracle有SQL緩衝,在執行SQL時,先將SQL語句的字串通過雜湊演算法得出一個值,然後檢查共用池中是否存在這個值。如果存在,則緩衝
命中,直接用已經緩衝的執行計畫來執行這個語句。如果不存在,則需要對SQL進行處理)

二,SQL語句的執行順序

    一般來說,SQL語句根據底層操作順序和操作類型,可以分為7大類:
  * SELECT
  * DISTINCT
  * FROM
  * WHERE
  * GROUP BY
  * HAVING
  * ORDER BY

    這7種類型的關鍵字的執行順序如下:
  * FROM
  * WHERE
  * GROUP BY
  * HAVING
  * SELECT
  * DISTINCT
  * ORDER BY

三,MySQL的文法分析過程詳解

    文法分析的作用是將一個輸入的字串,變換成一個描述這個字串的結構體。也就是把SQL語言翻譯成更加接近機器語言的過程。
文法分析包含三個過程:詞法分析、文法分析、輸出抽象文法樹。

1,詞法分析
    詞法分析階段,會用到詞法分析器。詞法分析器是一個確定有限自動機(DFA)。詞法分析器可以按照我們定義好的詞法,將輸入的字元
集轉換為“單詞”。如下:
abc     --> Identifier(標識符)
‘abc’   --> StringLiteral(字串)
123     --> Number(數字)
SELECT  --> Keyword(關鍵字)

例如,當輸入一個SQL語句在控制台,斷行符號後,詞法分析器會完成以下過程:
SELECT ID FROM PAYMENT;
--> (Keyword:SELECT)(Identifier:ID)(Keyword:FROM)(Identifier:PAYMENT)

2,文法分析
    詞法分析之後,進入文法分析階段。詞法分析的輸出結果,會作為文法分析的輸入。文法分析主要來判斷使用者輸入的單詞是否符合文法邏輯。

3,輸出抽象文法樹
    文法分析的最後一步,就是輸出抽象文法樹。抽象文法樹,全稱ADT,Abstract Syntax Tree。抽象文法樹以一種樹形結構來表示使用者輸入。
抽象文法樹上的每一個節點都是一個單詞。至此,使用者輸入的SQL語句,變成了一個“樹狀結構體”。

四,MySQL的語義分析過程詳解

    語義分析階段是SQL解析過程中,最複雜的一環。語義分析涉及SQL標準、SQL最佳化、MapReduce等相關理論。文法分析階段產生的抽象文法樹,
經過語義分析之後,會產生一個查詢計劃。查詢計劃會指導著物理執行運算元一步步的運行在我們的分布式系統上,去讀取表的內容。
    語義分析可以分為2個階段:邏輯分析和物理分析。

1,邏輯分析
    邏輯分析基本上是純代數的分析過程。邏輯分析與底層分布式環境無關。邏輯分析就是要分析一下使用者輸入的SQL語句到底要幹什麼,需要執行
哪些操作。一般來講,一個SQL語句總有一個輸入,一個輸出,輸入資料經過SQL加工後得到輸出資料。

2,物理分析
    物理分析是將邏輯分析的結果做變換。物理分析與底層的執行環境密切相關。一般來說,物理分析時需要確定在MapReduce時如何將資料分區、
排序、讀取資料量的大小、啟動多少個進程來執行任務等等。

五,MySQL的邏輯運算元

   下面的SQL操作,其實都對應了一種邏輯運算元,邏輯運算元可以理解為MySQL內部的一些操作。這些操作時原子性的,不可拆分的。邏輯運算元可以
看做MySQL內部操作的最小執行單元。
  * SELECT
  * DISTINCT
  * FROM
  * WHERE
  * GROUP BY
  * HAVING
  * ORDER BY

    這些SQL操作對應的MySQL內部操作的邏輯運算元如下:
  * SELECT               -->  SelectOperator(SEL)
  * DISTINCT / GROUP BY  -->  GBYOperator(GBY)
  * FROM                 -->  TableScanOperator(TS)
  * WHERE / HAVING       -->  FilterOperator(FIL)
  * ORDER BY / LIMIT     -->  OrderByOperator(ORDER)
  * JOIN                 -->  JoinOperator(JOIN)
  * UNION / UNION ALL    -->  UnionAllOperator(UNION)

1,邏輯運算元的作用
    邏輯查詢計劃是由邏輯運算元組成的。邏輯計劃是一個有向非循環圖(DAG)。一般的邏輯運算元都有一個輸入資料集和輸出資料集。JoinOperator
和UnionAllOperator比較特殊,擁有兩個或者兩個以上的輸入資料集,因為這兩個邏輯運算元的功能就是關聯多個資料集。
    我們將邏輯運算元的輸入資料集和輸出資料集稱為虛表。使用者是看不到虛表的,虛表是用來做內部分析的,是邏輯運算元之間的橋樑。

六,產生邏輯查詢計劃

    有了邏輯運算元的概念以後,我們就可以產生我們的查詢計劃了。根據使用者輸入的SQL語句的執行順序,遍曆編譯階段產生的抽象文法樹,用
邏輯運算元替換樹的節點對應的“單詞”。
    產生邏輯查詢計劃以後,需要進行一次最佳化。這些最佳化包括以下內容:
  * 計算常量運算式的結果
  * 列裁剪
  * Predict Push Down

    這裡我們一下列裁剪和Predict Push Down。產生查詢計劃時,預設會把表中的所有列都讀出來,但是之後可能只需要對其中的幾個列進行
計算,其他的列就沒有必要查詢出來了。這就是列裁剪。Predict Push Down指的是先過濾再串連,還是先串連再過濾的問題。Predict Push Down
會進行邏輯判斷,如果過濾操作(FilterOperator)可以放在串連操作(JoinOperator)之前,那麼先過濾再串連。Predict Push Down最佳化對於提
升效能很有協助。

七,產生物理查詢計劃

    邏輯查詢計劃是物理查詢計劃的輸入。我們按照拓撲序去遍曆邏輯查詢計劃上的每一個邏輯運算元,產生對應的物理運算元。產生物理查詢計劃時
也需要進行最佳化。


上面的文法分析、語義分析參考了博文《SQL解析過程詳解》






------------------------------------------- 分割線 -------------------------------------------
資料庫深入學習筆記-應用程式與資料庫如何通訊

一,應用程式伺服器與資料庫伺服器是如何通訊的

    個人認為,應用程式伺服器與資料庫伺服器是通過Socket進行通訊的。


------------------------------------------- 分割線 -------------------------------------------
資料庫深入學習筆記-MySQL體繫結構

    MySQL是一個可移植的資料庫,幾乎能在當前所有的作業系統上運行。各種系統在底層實現方面各有不同,但是MySQL基本上能夠保證在各個作業系統
上的物理體繫結構的一致性。

一,MySQL的組成部分

1,串連池組件(Connections Pools)
2,管理服務和工具組件(Management Services and Utilities)
3,SQL介面組件(SQL Interface)
4,查詢分析器組件(Parse)
5,最佳化器組件(Optimize)
6,緩衝(Cache)組件/緩衝組件(Cache and Buffer)
7,外掛程式式儲存引擎(Pluggable Storage Engine)
8,物理檔案

    MySQL區別於其他關聯式資料庫的一個重要特點就是:MySQL支援外掛程式式的儲存引擎。MySQL5.6版本預設儲存引擎為INNODB。


------------------------------------------- 分割線 -------------------------------------------


資料庫深入學習筆記-資料庫索引底層實現原理

一,背景知識

1,B-Tree和B+Tree

2,Binary Search(折半尋找)

3,資料庫的效能問題
  * 磁碟IO效能非常低,嚴重的影響了資料庫的效能;
  * 磁碟順序讀寫比隨機讀寫的效能高很多;

4,資料的基本存放裝置結構
  * 磁碟空間被劃分為許多大小相同的塊(Block)或者頁(Page)
  * 一個表的資料區塊以鏈表的方式串連在一起
  * 資料是以行為單位存放在磁碟上的塊(Block)中
  * 在訪問資料時,一次從磁碟中讀出或者寫入至少一個完整的塊(Block)

5,如何定位元據
    對資料操作之前,需要先進行定位。Mysql是如何定位元據的呢。簡單來說,就是表掃描。表掃描就是從磁碟中依次讀出所有的資料區塊,逐行
進行匹配。表掃描的時間複雜度是O(n)。

6,如何提高資料定位的效率
    主要有以下幾種方式:減少資料佔用的磁碟空間,也就是需要對資料進行壓縮,最佳化資料存放區結構;減少資料訪問總量,減少無效資料的訪問。
索引的出現,極大的提高了資料定位的效率。

>>>>>思考一個問題:聯合索引和單列索引的區別。

7,索引產生的背景
    如果我們只匹配一行記錄中的一個欄位,那麼效率會得到很大的提升,這樣做有效減少了無效資料的訪問;如果我們把一個欄位單獨放到一個
塊(Block)中,那麼效率也會得到很大的提升,這樣做有效減少了佔用的磁碟空間。索引就是在這種情況下產生的。索引是以犧牲空間為代價來換
時間的。凡事都有兩面性,所以合理使用索引就顯得尤為重要了。MySQL的索引以B樹格式儲存。

8,Binary Search(折半尋找)
    折半尋找,是對索引進行排序,使用一個數組順序儲存索引塊地址。這個數組也存放在磁碟上。折半尋找的時間複雜度為O(log(n))。在此基礎
上,繼續進行抽象,直到最上層塊中只有一個記錄,這樣就成了一棵B+Tree。樹的高度就是需要尋找的次數,索引樹越高,效率也就越低。

二,MySQL索引的分類

1,普通索引:最基本的索引類型,沒有唯一性限制。

2,唯一索引:唯一索引和普通索引的區別就是唯一索引必須保證索引列的值都是不重複的,也就是索引列的值都是唯一的。

3,主鍵:主鍵是一種索引,並且是唯一性索引。主鍵相當於彙總索引,是查詢最快的索引。

4,全文索引:一種特殊的索引。全文索引會把某個資料表的某個欄位出現過的所有單詞產生一份清單。全文索引只能在MyISAM資料表中建立。少於
3個字元的單詞通常會被忽略。可以通過修改MySQL設定檔my.cnf來修改。ft_min_word_len = 3。

5,單列索引:單列索引就是一個欄位的索引。

6,多列索引:多列索引就是多個欄位的索引,多列索引也叫聯合索引。多列索引使用時需要注意,只有在where條件中含有索引中的首欄欄位時,才
有效。舉例說明如下:
首先建立一個索引:ALTER TABLE PAYMENT ADD INDEX UNION_INDEX(ID,AMOUNT,DATE);
然後看以下幾個查詢操作:
SELECT * FROM PAYMENT WHERE ID = '10000001' AND AMOUNT = '100.00';    --聯合索引有效
SELECT * FROM PAYMENT WHERE ID = '10000001' AND DATE = '20161208';    --聯合索引有效
SELECT * FROM PAYMENT WHERE ID = '10000001';                          --聯合索引有效
SELECT * FROM PAYMENT WHERE AMOUNT = '100.00' AND DATE = '20161208';  --聯合索引無效

7,最左索引:可以這樣理解嗎,MySQL的複合式索引必須符合最左索引的規則。也就是上面所說的,只有在where條件中含有索引中的首欄欄位時,
索引才有效。

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.