1. Mysql 的發展歷史

因為MySQL是開源的（也有商業版本），所以在MySQL穩定版本的基礎上也發展出來了很多的分支，就像 Linux—樣，有 Ubuntu、RedHat、CentOSs Fedora、Debian

大家最熟悉MySQL分支的應該是MariaDB,因為CentOS 7里面自帶了一個MariaDBo，它是怎么來的呢？ Oracle收購MySQL之后’MySQL創始人之一Monty擔心MySQL資料庫發展的未來（開發緩慢，封閉，可能會被閉源），就創建了一個分支MariaDB （2009年），默認使用全新的Maria存盤引擎，它是原MylSAM存盤引擎的升級版本，

其他流行分支:
Percona Server是MySQL重要的分支之一，它基于InnoDB存盤引擎的基礎上,提升了性能和易管理性，最后形成了增強版的XtraDB引擎，可以用來更好地發揮服務器硬體上的性能，

國內也有一些MySQL的分支或者自研的存盤引擎，比如網易的InnoSQL,極數云舟的ArkDBo

我們操作資料庫有各種各樣的方式，比如Linux系統中的命令列，比如資料庫工具 Navicat,比如程式，例如Java語言的JDBC API或者ORM框架，

大家有沒有思考過，當我們的工具或者程式連接到資料庫之后，實際上發生了什么事情？它的內部是怎么作業的？

以一條査詢陳述句為例，我們來看下MySQL的作業流程是什么樣的，

2. 一條查詢Sql陳述句的執行流程

2.1 連接

我們的程式或者工具要操作資料庫，第一步要做什么事情？跟資料庫建立連接，

MySQL服務監聽的埠默認是3306,客戶端連接服務端的方式有很多，可以是同步的也可以是異步的，可以是長連接也可以是短連接，可以是TCP也可以是Unix Socket, MySQL有專門處理連接的模塊，連接的時候需要驗證權限，

我們怎么查看MySQL當前有多少個連接？

可以用show status命令，模糊匹配 Thread:

show global status like 'Thread%';

問題：為什么連接數是查看執行緒？客戶端的連接和服務端的執行緒有什么關系?

客戶端每產生一個連接或者一個會話，在服務端就會創建一個執行緒來處理，反過來， 如果要殺死會話，就是Kill執行緒，
既然是分配執行緒的話，保持連接肯定會消耗服務端的資源，MySQL會把那些長時間不活動的（SLEEP）連接自動斷開，

有兩個引數:

show global variables like 'wait timeout'; -- 非互動式超時時間，如 JDBC 程式
show global variables like 'interactive timeout'; -- 互動式超時時間，如資料庫工具

默認都是28800秒，8小時，
既然連接消耗資源，MySQL服務允許的最大連接數（也就是并發數）默認是多少呢？

在5.7版本中默認是151個，最大可以設定成10萬個

show variables like 'max connections';

引數級別說明：
MySQL中的引數（變數）分為session和global級別，分別是在當前會話中生效和全域生效,但是并不是每個引數都有兩個級別，比如max_connections就只有全域級別，

當沒有帶引數的時候，默認是session級別，包括查詢和修改，
比如修改了一個引數以后，在本視窗査詢已經生效，但是其他視窗不生效：

show variables like 'autocommit';
set autocommit = on;

所以，如果只是臨時修改，建議修改session級別， 如果需要在其他會話中生效，必須顯式地加上global引數，

執行一條查詢陳述句，客戶端跟服務端建立連接之后呢？下一步要做什么？

2.2 查詢快取

MySQL內部自帶了一個快取模塊，
思考一個問題：有一張500萬行資料的表，沒有索引，如果我兩次執行一模一樣的SQL陳述句，第二次會不會變得很快？

答：不會，因為mysql的快取也是有大小限制的，不可能一次快取500萬的資料，

再問：

select * from user u where u.name = 'xhc';

上面這條sql陳述句會用到快取嗎？
答案是：快取沒有生效，為什么？ MySQL的快取默認是關閉的，

show variables like 'query_cache%';

默認關閉的意思就是不推薦使用，為什么MySQL不推薦使用它自帶的快取呢？ 主要是因為MySQL自帶的快取的應用場景有限，第一個是它要求SQL陳述句必須一模一樣，中間多一個空格，字母大小寫不同都被認為是不同的的SQL，

第二個是表里面任何一條資料發生變化的時候，這張表所有快取都會失效，所以對于有大量資料更新的應用，也不適合，
所以快取這一塊，我們還是交給0RM框架(比如MyBatis默認開啟了一級快取)， 或者獨立的快取服務，比如Redis來處理更合適，

MySQL 8.0中，查詢快取已經被移除了，

2.3 語法決議和預處理(Parser & Preprocessor)

這一步主要做的事情是對陳述句基于SQL語法進行詞法分析、語法分析和語意決議，

2.3.1 詞法分析

詞法分析就是把一個完整的SQL陳述句打碎成一個個的單詞，比如一個簡單的SQL陳述句：

select name from user where id = 1;

它會打碎成8個符號，每個符號是什么型別，從哪里開始到哪里結束，

2.3.2 語法分析

第二步就是語法分析，語法分析會對SQL做一些語法檢查，比如單引號有沒有閉合, 然后根據MySQL定義的語法規則，根據SQL陳述句生成一個資料結構，這個資料結構我們把它叫做決議樹(select lex)，

詞法語法分析是一個非常基礎的功能，Java的編譯器、百度搜索引擎如果要識別陳述句，必須也要有詞法語法分析功能，
任何資料庫的中間件，要決議SQL完成路由功能，也必須要有詞法和語法分析功能, 比如Mycat, Sharding-JDBC

問題：如果我寫了一個詞法和語法都正確的SQL,但是表名或者欄位不存在，會在哪里報錯？是在資料庫的執行器還是決議器？比如：

select * from xhc;

實際上還是在決議的時候報錯，決議SQL的環節里面有個前處理器，它會檢査生成的決議樹，解決決議器無法決議的語意，比如，它會檢査表和列名是否存在，檢査名字和別名，保證沒有歧義，
預處理之后得到一個新的決議樹，

2.4 查詢優化(Query Optimizer)與查詢執行計劃

2.4.1 什么是優化器？

得到決議樹之后，是不是執行SQL陳述句了呢？
這里我們有一個問題，一條SQL陳述句是不是只有一種執行方式？或者說資料庫最終執行的SQL是不是就是我們發送的SQL?

這個答案是否定的，一條SQL陳述句是可以有很多種執行方式的，最侄訓傳相同的結果，他們是等價的，但是如果有這么多種執行方式，這些執行方式怎么得到的？最終選擇哪一種去執行？根據什么判斷標準去選擇？

這個就是MySQL的査詢優化器的模塊(Optimizer)，

査詢優化器的目的就是根據決議樹生成不同的執行計劃(Execution Plan),然后選擇一種最優的執行計劃，MySQL里面使用的是基于開銷(cost)的優化器，那種執行計劃開銷最小，就用哪種，

可以使用這個命令査看査詢的開銷：

show status like 'Last query cost';

2.4.2 優化器可以做什么？

MySQL的優化器能處理哪些優化型別呢?

1. 當我們對多張表進行關聯查詢的時候，以哪個表的資料作為基準表，
2. 有多個索弓|可以使用的時候，選擇哪個索引，

實際上，對于每一種資料庫來說，優化器的模塊都是必不可少的，他們通過復雜的演算法實作盡可能優化查詢效率的目標，

但是優化器也不是萬能的，并不是再低效的SQL陳述句都能自動優化，也不是每次都能選擇到最優的執行計劃，大家在撰寫SQL陳述句的時候還是要注意，

優化完之后，得到一個什么東西呢？優化器最侄訓把決議樹變成一個査詢執行計劃,查詢執行計劃是一個資料結構

怎么査看MySQL的執行計劃呢？比如多張表關聯查詢，先査詢哪張表？在執行査詢的時候可能用到哪些索引，實際上用到了什么索引?

MySQL提供了一個執行計劃的工具，我們在SQL陳述句前面加上EXPLAIN,就可以看到執行計劃的資訊，

EXPLAIN select name from user where id=1;

如果要得到詳細的資訊，還可以用FORMAT=JSON 或者開啟optimizer traceo

EXPLAIN FORMAT=JSON select name from user where id=1;

2.5 存盤引擎

我們知道，mysql 有很多種存盤引擎，比如myisam ，memor，innodb等，一個表型別是myisam 的表，和一個表型別是innodb表型別的表，他們到底是如何存盤資料的呢？

show variables like 'datadir';

默認情況下，每個資料庫有一個自己檔案夾，以test資料庫為例，任何一個存盤引擎都有一個frm檔案，這個是表結構定義檔案，

不同的存盤引擎存放資料的方式不一樣，產生的檔案也不一樣， memory沒有，innodb是1個，myisam 是2個，

這里我們有幾個問題：

1. 表型別是怎么選擇的？可以修改嗎？
2. MySQL為什么支持這么多存盤引擎呢？一種還不夠用嗎？
3. 這些不同的存盤引擎，到底有什么區別？

2.5.1 存盤引擎選擇

一張表的存盤引擎，是在創建表的時候指定的，使用ENGINE關鍵字，

CREATE TABLE user_innodb' (
id int(11) NOT NULL AUTOINCREMENT,
name varchar(255) DEFAULT NULL,
gender tinyint(1) DEFAULT NULL,
phone varchar(11) DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'comidx_name_phone' ( name ,'phone')
)ENGINE=InnoDB AUTO_INCREMENT= 1 DEFAULT CHARSET=utf8mb4;

很多時候我們自己寫的建表陳述句是沒有指定存盤引擎的，

沒有指定的時候，資料庫就會使用默認的存盤引擎，5.5.5之前，默認的存儲引擎是MylSAM, 5.5.5之后，默認的存盤引擎是InnoDB，

這么多的存盤引擎，區別到底在哪里?

試想一下: 如果我有一張表，需要很高的訪問速度，而不需要考慮持久化的問題，是不是要把資料放在記憶體?
如果一張表，是用來做歷史資料存檔的，不需要修改，也不需要索引，那它是不是要支持資料的壓縮?
如果一張表用在讀寫并發很多的業務中，是不是要支持讀寫不干擾，而且要保證比較高的資料一致性呢?

說到這里大家應該明白了，為什么要支持這么多的存盤引擎，就是因為我們有不同的業務需求，一種存盤引擎不能提供所有的特性，

2.5.2 常見的存盤引擎介紹

• MylSAM（3個檔案）
  應用范圍比較小，表級鎖定限制了讀/寫的性能，因此在Web和資料倉庫配置中，它通常用于只讀或以讀為主的作業，
  特點： 支持表級別的鎖（插入和更新會鎖表）
  優點： 擁有較高的插入（insert）和查詢（select）速度，存盤了表的行數（count速度更快）（怎么快速向資料庫插入100萬條資料？我們有一種先用MylSAM插入資料,然后修改存盤引擎為InnoDB的操作，）
  缺點： 不支持事務
  適合： 只讀之類的資料分析的專案，
• InnoDB（2個檔案）
  mysql 5.7中的默認存盤引擎，InnoDB是一個事務安全（與ACID兼容）的MySQL 存盤引擎，它具有提交、回滾和崩潰恢復功能來保護用戶資料，InnoDB行級鎖提高了多用戶并發性和性能，InnoDB將用戶資料存盤在聚集索引中，以減少基于主鍵的常見查詢的I/O，為了保持資料完整性， InnoDB還支持外鍵參考完整性約束，
  特點： 1.支持事務，支持外鍵，因此資料的完整性、一致性更高，
  2.支持行級別的鎖和表級別的鎖，
  3.支持讀寫并發，寫不阻塞讀（MVCC），
  4.特殊的索引存放方式，可以減少IO，，提升査詢效率，
  適合： 經常更新的表，存在并發讀寫或者有事務處理的業務系統，

小故事：

InnoDB本來是InnobaseOy公司開發的，它和MySQL AB公司合作開源了 InnoDB的代碼，但是沒想到MySQL的競爭對手Oracle把InnobaseOy收購了，后來08年Sun公司（開發Java語言的Sun）收購了 MySQL AB, 09年Sun公司又被Oracle收購了，所以MySQL, InnoDB又是一家了，有人覺得MySQL越來越像Oracle,其實也是這個原因，

• Memory（1個檔案）
  將所有資料存盤在RAM中，以便在需要快速查找非關鍵資料的環境中快速訪問，這個引擎以前被稱為堆引擎，其使用案例正在減少；InnoDB及其緩沖池記憶體區域提供了一種通用、持久的方法來將大部分或所有資料保存在記憶體中，而ndbduster為大型分布式資料集提供了快速的鍵值查找，
  特點：
  把資料放在記憶體里面，讀寫的速度很快，但是資料庫重啟或者崩潰，資料會全部消失，只適合做臨時表，將表中的資料存盤到記憶體中，
• CSV （3個檔案）
  它的表實際上是帶有逗號分隔值的文本檔案，csv表允許以CSV格式匯入或轉儲資料, 以便與讀寫相同格式的腳本和應用程式交換資料，因為CSV表沒有索引，所以通常在正常操作期間將資料保存在innodb表中，并且只在匯入或匯出階段使用csv表，
  特點： 不允許空行，不支持索引，格式通用，可以直接編輯，適合在不同資料庫之間匯入匯出，
• Archive （2 個檔案）
  這些緊湊的沒有索引的表用于存盤和檢索大量很少參考的歷史、存檔或安全審計資訊，
  特點： 不支持索引，不支持update delete

這是MySQL里面常見的一些存盤引擎，我們看到了，不同的存盤引擎提供的特性都不一樣，它們有不同的存盤機制、索引方式、鎖定水平等功能，

我們在不同的業務場景中對資料操作的要求不同，就可以選擇不同的存盤引擎來滿足我們的需求，這個就是MySQL支持這么多存盤引擎的原因，

2.5.3 如何選擇存盤引擎？

• 如果對資料一致性要求比較高，需要事務支持，可以選擇InnoDB，
• 如果資料查詢多更新少，對查詢性能要求比較高，可以選擇MyISAM，
• 如果需要一個用于查詢的臨時表，可以選擇Memory，
• 如果所有的存盤引擎都不能滿足你的需求，并且技術能力足夠，可以根據官網內部手冊用C語言開發一個存盤引擎：
  https://dev.mvsql.com/doc/internals/en/custom-engine.html
  按照這個開發規范，實作相應的介面，給執行器操作，

也就是說，為什么能支持這么多存盤引擎，還能自定義存盤引擎，表的存盤引擎改了對Server訪問沒有任何影響，就是因為大家都遵循了一定了規范，提供了相同的操作介面，
每個存盤引擎都有自己的服務，

show engine innodb status;

這些存盤引擎用不同的方式管理資料檔案，提供不同的特性，但是為上層提供相同的介面，

2.6 執行引擎(Query Execution Engine)

存盤引擎分析完了，它是我們存盤資料的形式，那么，是誰使用執行計劃去操作存盤引擎呢？

這就是我們的執行引擎，它利用存盤引擎提供的相應的API來完成操作，
為什么我們修改了表的存盤引擎，操作方式不需要做任何改變？因為不同功能的存盤引擎實作的API是相同的，最后把資料回傳給客戶端，

總結

總體上，我們可以把MySQL分成三層，

• 跟客戶端對接的連接層，
• 真正執行操作的服務層，
• 和跟硬體打交道的存盤引擎層，

1. 連接層
   我們的客戶端要連接到MySQL服務器3306埠，必須要跟服務端建立連接，那么管理所有的連接，驗證客戶端的身份和權限，這些功能就在連接層完成，
2. 服務層
   連接層會把SQL陳述句交給服務層，這里面又包含一系列的流程：
   比如查詢快取的判斷、根據SQL呼叫相應的介面，對我們的SQL陳述句進行詞法和語法的決議（比如關鍵字怎么識別，別名怎么識別，語法有沒有錯誤等等），
   然后就是優化器，MySQL底層會根據一定的規則（最小成本原則）對我們的SQL陳述句進行優化，最后再交給執行器去執行，
3. 存盤引擎
   存盤引擎就是我們的資料真正存放的地方，在MySQL里面支持不同的存盤引擎，
   再往下就是記憶體或者磁盤，