DB哥MySQL高級教程-系統學習MySQL共149課時加我微信公眾號免費學：DB哥文末有MySQL高級課程目錄

MySQL是什麼？隨著時間的推移，開源數據庫在中低端應用中逐漸流行起來，占據瞭很大的市場份額。開源數據庫具有免費使用、配置簡單、穩定性好、性能優良等特點，而 MySQL 數據庫正是開源數據庫中的傑出代表。

• 開源全稱為“開放源代碼”。很多人認為開源軟件最明顯的特點是免費，但實際上並不是這樣的，開源軟件最大的特點應該是開放，也就是任何人都可以得到軟件的源代碼。可以在源代碼的基礎上加以修改學習，甚至重新發放，當然是在版權限制范圍之內。

MySQL 是最流行的數據庫之一，是一個免費開源的關系型數據庫管理系統，但也不意味著該數據庫是完全免費的。MySQL 由瑞典 MySQL AB 公司開發，目前屬於 Oracle 公司。MySQL 適合中小型軟件，被個人用戶以及中小企業青睞。MySQL的版本以及版本號針對不同的用戶，MySQL 分為兩個版本：

• MySQL Community Server（社區版）：該版本完全免費，但是官方不提供技術支持。
• MySQL Enterprise Server（企業版）：該版本能夠以很高的性價比為企業提供數據倉庫應用，支持 ACID 事物處理，提供完整的提交、回滾、崩潰恢復和行級鎖定功能，但是該版本需要付費使用，官方提供電話技術支持。

註意：MySQL Cluster 主要用於架設群服務器，需要在社區服務或企業版的基礎上使用。MySQL的版本命名機制MySQL 的命名機制由 3 個數字和 1 個後綴組成，例如 mysql-5.7.20：

• 第 1 個數字“5”是主版本號，用於描述文件的格式，所有版本 5 的發行版都有相同的文件夾格式。
• 第 2 個數字“7”是發行級別，主版本號和發行級別組合在一起便構成瞭發行序列號。
• 第 3 個數字“20”是在此發行系列的版本號，隨每次新發行的版本遞增。通常選擇已經發行的最新版本。

在 MySQL 開發過程中，同時存在多個發佈系列，每個發佈系列的成熟度處在不同階段。

• MySQL 5.7 是最新開發的穩定（GA）發佈系列，是將執行新功能的系列，目前已經可以正常使用。
• MySQL 5.6 是比較穩定的（GA）發佈系列，隻針對漏洞修復重新發佈，不增加會影響穩定性的新功能。
• MySQL 5.1 是一個穩定的（產品質量）發佈系列，隻針對嚴重漏洞修復和安全修復重新發佈，不增加影響該系列穩定性的重要功能。

mysql 的優勢由於 MySQL 數據庫體積小、速度快、總體擁有成本低、開放源代碼，其有著廣泛的應用，一般中小型網站的開發都選擇 MySQL 作為網站數據庫。由於其社區版的性能卓越，因此搭配 PHP 和 Apache 服務器可組成良好的開發環境。主要的優勢有以下幾點

1. MySQL 是開放源代碼的數據庫，任何人都可以獲取該數據庫的源代碼。這就使得任何人都可以修正 MySQL 的缺陷，並且任何人都能以任何目的來使用該數據庫。MySQL 是一款可以自由使用的數據庫。
2. MySQL 的跨平臺性：MySQL 不僅可以在 Windows 系列的操作系統上運行，還可以在 UNIX、Linux 和 Mac OS 等操作系統上運行。因為很多網站都選擇 UNIX、Linux 作為網站的服務器，所以 MySQL 的跨平臺性保證瞭其在 Web 應用方面的優勢。。
3. 價格優勢：MySQL 數據庫是一個自由軟件，任何人都可以從 MySQL 的官方網站上下載該軟件，這些社區版本的 MySQL 都是免費試用的，即使是需要付費的附加功能，其價格也是很便宜的。相對於 Oracle、DB2 和 SQL Server 這些價格昂貴的商業軟件，MySQL 具有絕對的價格優勢。
4. 功能強大且使用方便：MySQL 是一個真正的多用戶、 多線程 SQL 數據庫服務器。它能夠快速、有效和安全的處理大量的數據。相對於 Oracle 等數據庫來說，MySQL 的使用是非常簡單的。MySQL 主要目標是快速、健壯和易用。

MySQL 與常用的主流數據庫 Oracle、SQL Server 相比，主要特點就是免費，並且在任何平臺上都能使用，占用的空間相對較小。但是，MySQL 也有一些不足，比如對於大型項目來說，MySQL 的容量和安全性就略遜於 Oracle 數據庫mysql 的特性MySQL 數據庫管理系統具有以下系統特性：

• 使用 C 和 C++ 編寫，並使用多種編譯器進行測試，保證源代碼的可移植性。
• 支持 AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows 等多種操作系統。
• 為多種編程語言提供瞭 API。這些編程語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• 支持多線程，充分利用 CPU 資源。
• 優化的 SQL 查詢算法，有效地提高查詢速度。
• 既能夠作為一個單獨的應用程序應用在客戶端服務器網絡環境中，也能夠作為一個庫而嵌入其他的軟件中。
• 提供多語言支持，常見的編碼如中文的 GB 2312、BIG 5，日文的 Shift_JIS 等都可以用作數據表名和數據列名。
• 提供 TCP/IP、ODBC 和 JDBC 等多種數據庫連接途徑。
• 提供用於管理、檢查、優化數據庫操作的管理工具。
• 支持大型的數據庫。可以處理擁有上千萬條記錄的大型數據庫。
• 支持多種存儲引擎。

mysql 的適用場景MySQL 是目前世界上最流行的開源關系數據庫，大多應用於互聯網行業。比如，在國內，大傢所熟知的百度、騰訊、淘寶、京東、網易、新浪等，國外的 Google、Facebook、Twitter、GitHub 等都在使用 MySQL。社交、電商、遊戲的核心存儲往往也是 MySQL。

1. Web 網站系統：Web 網站開發者是 MySQL 最大的客戶群，也是 MySQL 發展史上最為重要的支撐力量。

• MySQL 之所以能成為 Web 網站開發者們最青睞的數據庫管理系統，是因為 MySQL 數據庫的安裝配置都非常簡單，使用過程中的維護也不像很多大型商業數據庫管理系統那麼復雜，而且性能出色。還有一個非常重要的原因就是 MySQL 是開放源代碼的，完全可以免費使用。

1. 日志記錄系統：MySQL 數據庫的插入和查詢性能都非常的高效，設計的好，在使用 MyISAM 存儲引擎的時候，兩者可以做到互不鎖定，達到很高的並發性能。所以，對需要大量的插入和查詢日志記錄的系統來說，MySQL 是非常不錯的選擇。比如處理用戶的登錄日志，操作日志等，都是非常適合的應用場景。
2. 數據倉庫系統：隨著現在數據倉庫數據量的飛速增長，我們需要的存儲空間越來越大。數據量的不斷增長，使數據的統計分析變得越來越低效，越來越困難。

• 下面是幾個主要的解決思路。

1. 采用昂貴的高性能主機以提高計算性能，用高端存儲設備提高 I/O 性能，效果理想，但是成本非常高；
2. 通過將數據復制到多臺使用大容量硬盤的廉價 PC Server 上，以提高整體計算性能和 I/O 能力，效果尚可，存儲空間有一定限制，成本低廉；
3. 通過將數據水平拆分，使用多臺廉價的 PC Server 和本地磁盤來存放數據，每臺機器上面都隻有所有數據的一部分，解決瞭數據量的問題，所有 PC Server 一起並行計算，也解決瞭計算能力問題，通過中間代理程序調配各臺機器的運算任務，既可以解決計算性能問題又可以解決 I/O 性能問題，成本也很低廉。

• 在上面的三個方案中，第二和第三個的實現，MySQL 都有較大的優勢。通過 MySQL 的簡單復制功能，可以很好的將數據從一臺主機復制到另外一臺 ，不僅僅在局域網內可以復制，在廣域網同樣可以。
• 當然，很多人可能會說，其他的數據庫同樣也可以做到，不是隻有 MySQL 有這樣的功能。確實，很多數據庫同樣能做到，但是 MySQL 是免費的，其他數據庫大多都是按照主機數量或者 cpu 數量來收費，當我們使用大量的 PC Server 的時候，License 費用相當驚人。所以第一個方案，基本上所有數據庫系統都能夠實現，但是其高昂的成本不是每一個公司都能夠承擔的。

1. 嵌入式系統：嵌入式環境對軟件系統最大的限制是硬件資源非常有限，在嵌入式環境下運行的軟件系統，必須是輕量級低消耗的軟件。
1. MySQL 在資源的使用方面的伸縮性非常大，可以在資源非常充裕的環境下運行，也可以在資源非常少的環境下正常運行。它對於嵌入式環境來說，是一種非常合適的數據庫系統，而且 MySQL 有專門針對於嵌入式環境的版本。
2. 並且，MySQL 的定位是通用數據庫，各種類型的應用一般都能利用到 MySQL 存取數據的優勢。業內生產實踐證明，MySQL 更適合中小型企業。以目前的軟硬件產品水平來看，如果數據超過幾個 TB 將難以高效利用 MySQL。
3. MySQL 可以作為傳統的關系型數據庫產品使用，也可以當作一個 key-value 產品來使用。由於它具有優秀的災難恢復功能，因此相對於目前市場上的一些 key-value 產品會更有優勢。

MySQL客戶端和服務器架構（C/S架構）客戶端-服務器（Client/Server）結構簡稱 C/S 結構，是一種網絡架構，通常在該網絡架構下的軟件分為客戶端和服務器。服務器是整個應用系統資源的存儲和管理中心，多個客戶端分別各自處理相應的功能，共同實現完整的應用。在客戶/服務器結構中，客戶端用戶的請求被傳送到數據庫服務器，數據庫服務器進行處理後，將結果返回給用戶，從而減少網絡數據的傳輸量。用戶在使用應用程序時，首先啟動客戶端，然後通過相關命令告知服務器進行連接以完成各種操作，而服務器則按照此請示提供相應的服務。每一個客戶端軟件的實例都可以向一個服務器或應用程序服務器發出請求。客戶端和服務器程序通常不在同一臺計算機上運行，比如，我們平時在當當網上買書的時候，所使用的電腦和網頁瀏覽器就被當做瞭一個客戶端，同時，組成當當網的電腦、數據庫和應用程序就被當做服務器。數據庫管理系統可分為兩類：一類是基於共享文件系統的數據庫管理系統，例如 Microsoft Access 和 FileMaker，主要用於桌面用途，不適合用於高端或更關鍵的應用；另一類是基於客戶端-服務器的數據庫管理系統，例如 MySQL、Oracle 和 SQL Server 等數據庫。服務器軟件負責訪問和處理所有數據的一個軟件，這個軟件運行在稱為數據庫服務器的計算機上，並且與數據文件打交道的隻有服務器軟件。關於數據的添加、刪除和更新等所有的請求都由服務器完成。這些請求來自於運行客戶端的計算機。客戶端用來和用戶打交道。例如，如果你請求一個按字母順序列出的產品表，則客戶端會通過網絡提交該請求給服務器，服務器處理這個請求，然後根據需要對數據進行過濾、丟棄和排序，最後把結果返回到客戶端。註意：客戶端和服務器可以安裝在兩臺計算機或一臺計算機上，不管它們在不在相同的計算機上，客戶端都要與服務器進行通信。以上這些過程對用戶都是透明的，你不需要直接訪問數據文件。為瞭使用MySQL，需要訪問運行 MySQL 服務器的計算機和發佈命令到 MySQL 客戶端的計算機。對於 MySQL 數據庫管理系統，服務器為MySQL DBMS。你可以在本地安裝的副本上運行，也可以連接到運行在你具有訪問權的遠程服務器上的一個副本。客戶端可以是 MySQL 提供的工具（如 MySQL Workbench、SQLyog）、腳本語言（如Perl）、Web 應用開發語言和程序設計語言等。MySQL體系結構詳解麻雀雖小，五臟俱全。MySQL 雖然以簡單著稱，但其內部結構並不簡單。mysql的體系結構MySQL 就好比蓋房子，如果想把房子蓋的特別高，地基一定要穩，基礎一定要牢固。MySQL 由連接池、SQL 接口、解析器、優化器、緩存、存儲引擎等組成，可以分為三層，即 MySQL Server 層、存儲引擎層和文件系統層。MySQL Server 層又包括連接層和 SQL 層。如下是官方文檔中 MySQL 的基礎架構圖：

MySQL體系結構圖上圖中，Connection pool 為連接層，Management Services & Utilities …Caches & Buffers 為 SQL 層，Pluggable Storage Engines 為存儲引擎層，File system、Files & Logs 為文件系統層。Connectors 不屬於以上任何一層，可以將 Connectors 理解為各種客戶端、應用服務，主要指的是不同語言與 SQL 的交互。連接層應用程序通過接口（如 ODBC、JDBC）來連接 MySQL，最先連接處理的是連接層。連接層包括通信協議、線程處理、用戶名密碼認證 3 部分。

• 通信協議負責檢測客戶端版本是否兼容 MySQL 服務端。
• 線程處理是指每一個連接請求都會分配一個對應的線程，相當於一條 SQL 對應一個線程，一個線程對應一個邏輯 CPU，在多個邏輯 CPU 之間進行切換。
• 密碼認證用來驗證用戶創建的賬號、密碼，以及 host 主機授權是否可以連接到 MySQL 服務器。

Connection Pool（連接池）屬於連接層。由於每次建立連接都需要消耗很多時間，連接池的作用就是將用戶連接、用戶名、密碼、權限校驗、線程處理等需要緩存的需求緩存下來，下次可以直接用已經建立好的連接，提升服務器性能。SQL層SQL 層是 MySQL 的核心，MySQL 的核心服務都是在這層實現的。主要包含權限判斷、查詢緩存、解析器、預處理、查詢優化器、緩存和執行計劃。

• 權限判斷可以審核用戶有沒有訪問某個庫、某個表，或者表裡某行數據的權限。
• 查詢緩存通過 Query Cache 進行操作，如果數據在 Query Cache 中，則直接返回結果給客戶端，不必再進行查詢解析、優化和執行等過程。
• 查詢解析器針對 SQL 語句進行解析，判斷語法是否正確。
• 預處理器對解析器無法解析的語義進行處理。
• 查詢優化器對 SQL 進行改寫和相應的優化，並生成最優的執行計劃，就可以調用程序的 API 接口，通過存儲引擎層訪問數據。

Management Services & Utilities、SQL Interface、Parser、Optimizer 和 Caches & Buffers 屬於 SQL 層，詳細說明如下表所示。

• Management Services & Utilities： MySQL 的系統管理和控制工具，包括備份恢復、MySQL 復制、集群等。
• SQL Interface（SQL 接口）：用來接收用戶的 SQL 命令，返回用戶需要查詢的結果。例如 SELECT FROM 就是調用 SQL Interface。
• Parser（查詢解析器）：在 SQL 命令傳遞到解析器的時候會被解析器驗證和解析，以便 MySQL 優化器可以識別的數據結構或返回 SQL 語句的錯誤。
• Optimizer（查詢優化器）：SQL 語句在查詢之前會使用查詢優化器對查詢進行優化，同時驗證用戶是否有權限進行查詢，緩存中是否有可用的最新數據。它使用“選取-投影-連接”策略進行查詢。
• 例如 SELECT id, name FROM student WHERE gender = "女";語句中，SELECT 查詢先根據 WHERE 語句進行選取，不是將表全部查詢出來以後再進行 gender 過濾。SELECT 查詢先根據 id 和 name 進行屬性投影，不是將屬性全部取出以後再進行過濾，將這兩個查詢條件連接起來生成最終查詢結果。

• Caches & Buffers（查詢緩存）：如果查詢緩存有命中的查詢結果，查詢語句就可以直接去查詢緩存中取數據。這個緩存機制是由一系列小緩存組成的，比如表緩存、記錄緩存、key 緩存、權限緩存等。

存儲引擎層Pluggable Storage Engines 屬於存儲引擎層。存儲引擎層是 MySQL 數據庫區別於其他數據庫最核心的一點，也是 MySQL 最具特色的一個地方。主要負責 MySQL 中數據的存儲和提取。因為在關系數據庫中，數據的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型（即存儲和操作此表的類型）。文件系統層文件系統層主要是將數據庫的數據存儲在操作系統的文件系統之上，並完成與存儲引擎的交互。MySQL 5.7的新特性（新功能）與 MySQL 5.6 相比，MySQL 5.7 具有以下幾個方面的新功能。

1. 隨機 root 密碼：MySQL 5.7 數據庫初始化完成後，會自動生成一個 root@localhost 用戶，root 用戶的密碼不為空，而是隨機產生一個密碼。
2. 自定義 test 數據庫：MySQL 5.7 默認安裝完成後沒有 test 數據庫。用戶可以自行創建 test 數據庫並對其進行權限控制。
3. 默認 SSL 加密：MySQL 5.7 采用瞭更加簡單的 SSL 安全訪問機制，默認連接使用 SSL 的加密方式。
4. 密碼過期策略：MySQL 5.7 支持用戶設置密碼過期策略，要求用戶在一定時間過後必須修改密碼。
5. 用戶鎖：MySQL 5.7 為管理員提供瞭暫時禁用某個用戶的功能，使被鎖定的用戶無法訪問和使用數據庫。
6. 全面支持JSON：MySQL 5.7 也提供瞭對 JSON 的支持，在服務器端提供瞭一組便於操作 JSON 的函數。存儲的方法是將 JSON 編碼成 BLOB 後再由存儲引擎進行處理。這樣，MySQL 就同時擁有瞭關系型數據庫和非關系型數據庫的優點，並且可以提供完整的事務支持。
7. 支持兩類生成列（generated column）：生成列是通過數據庫中的其他列計算得到的一列。當為生成列創建索引時，可以便捷地加快查詢速度。MySQL 5.7 支持虛擬生成列和存儲生成列。虛擬生成列僅將數據保存在表的元數據中，作為缺省的生成列類型；存儲生成列則是將數據永久保存在磁盤上，需要更多的磁盤空間。
8. 引入系統庫（sys schema）：系統庫中包含一系列視圖、函數和存儲過程，通過多線程、多進程、組合事務提交和基於行的優化方式將復制功能提高 5 倍以上，用戶向外擴充其跨商品系統的工作負載時，得以大幅提升復制的效能和效率。

MySQL安裝後的目錄結構介紹MySQL 的安裝目錄包含一些子目錄以及一些後綴名為.ini的配置文件。MySQL 5.7 版本的安裝配置後，目錄結構可能如下圖所示：mysql安裝教程

MySQL目錄結構

MySQL目錄結構註意：MySQL 5.7 的 Data 目錄和 my.ini 文件有時並不放在 MySQL 的安裝目錄下，而是在 C:ProgramDataMySQLMySQL Server 5.7 下（如上圖所示）。一般情況下，C 盤下的 ProgramData 目錄是隱藏的，需要取消隱藏。MySQL 安裝目錄的作用。

1. bin 目錄：用於放置一些可執行文件，如 mysql.exe、mysqld.exe、mysqlshow.exe 等。
2. docs 目錄：存放一些文檔
3. Data 目錄：登錄數據庫後，可使用 SHOW GLOBAL VARIABLES LIKE "%Datadir%"; 命令查看 Data 目錄位置。Data 目錄中用於放置一些日志文件以及數據庫。我們創建和保存的數據都存在這個目錄裡。打開 Data 目錄，以上數據庫都是安裝 MySQL 時，系統自動創建的
4. include 目錄：用於放置一些頭文件，如：mysql.h、mysql_ername.h 等。
5. lib 目錄：用於放置一系列庫文件
6. share 目錄：用於存放字符集、語言等信息
7. my.ini 文件：my.ini 是 MySQL 默認使用的配置文件，一般情況下，隻要修改 my.ini 配置文件中的內容就可以對 MySQL 進行配置。
8. 除瞭上述介紹的目錄，MySQL 安裝目錄下可能還有幾個後綴名為.ini的配置文件，不同的配置文件代表不同的含義。my.ini 是 MySQL 默認使用的配置文件，其它的配置文件都是適合不同數據庫的配置文件的模板，在文件名中就說明瞭適合的數據庫類型

• my-huge.ini：適合超大型數據庫的配置文件。
• my-large.ini：適合大型數據庫的配置文件。
• my-medium.ini：適合中型數據庫的配置文件。
• my-small.ini：適合小型數據庫的配置文件。
• my-template.ini：是配置文件的模板，MySQL 配置向導將該配置文件中選擇項寫入到 my.ini 文件。
• my-innodb-heavy-4G.ini：表示該配置文件隻對於 InnoDB 存儲引擎有效，而且服務器的內存不能小於 4GB。

MySQL數據目錄在 MySQL 中，物理文件存放在數據目錄中。數據目錄與安裝目錄不同，安裝目錄用來存儲控制服務器和客戶端程序的命令，數據目錄用來存儲 MySQL 服務器在運行過程中產生的數據。MySQL 中任何一項邏輯性或者物理性文件都具有可配置性，另外由於開源的原因，每個版本都有一些改進，所以不要死記硬背。可以通過SHOW VARIABLES LIKE 'datadir';命令查看MySQL 的數據目錄路徑下面分別講解 MySQL 數據目錄裡存放的目錄和文件。數據目錄下圖是 MySQL（5.7.29）在 Windows 系統下安裝的數據文件目錄，可以看到有如下幾類文件。

img

• Data 目錄用來存放數據庫相關的數據信息，包括數據庫信息，表信息等。
• MySQL 5.7 及之後的版本開始支持集群模式，installer_config.xml 配置文件主要用於配置單節點或集群模式。
• my.ini 文件是 MySQL 服務端和客戶端主要的配置文件，包括編碼集、默認引擎、最大連接數等設置。MySQL 服務器啟動時會默認加載此文件。

Data目錄Data 目錄中存放的文件如下圖所示：

img由圖中可以看出，系統數據庫和用戶自定義數據庫的存放路徑相同。數據庫目錄中主要存放相應的數據庫對象對 Data 目錄中的文件說明如下：

• mysql、performance_schema、sakila、sys 和 world 是系統數據庫，information_schema 數據庫比較特殊，這裡沒有相應的數據庫目錄。
• test 是用戶自定義的數據庫，也就是用戶自己創建的數據庫。
• auto.cnf：MySQL 服務器的選項文件，用於存儲 server-uuid 的值。server-uuid 與 server-id 一樣，用於標識 MySQL 實例在集群中的唯一性。
• ib_logfile0、ib_logfile1 是支持事務性引擎的 redo 日志文件
• ibdata1 為共享表空間（系統表空間）。如果采用 InnoDB 引擎，默認大小為 10M 。
• ibtmp1 為存儲臨時對象的空間，比如臨時表對象等。

數據目錄裡可能還有：

• MySQL 服務器的進程 ID（PID）文件。
• MySQL 服務器所生成的狀態和日志文件。
• DES 密鑰文件或服務器的 SSL 證書。

數據庫目錄數據庫實際是一個目錄，每個目錄都保存著相應數據庫中的表以及表數據。對數據庫目錄中的文件說明如下：

1. db.opt：用來保存數據庫的配置信息，比如該庫的默認字符集編碼和字符集排序規則。如果你創建數據庫時指定瞭字符集和排序規則，後續創建的表沒有指定字符集和排序規則，那麼該表將采用 db.opt 文件中指定的屬性。

• 對於 InnoDB 表，如果是獨立的表空間，數據庫中的表結構以及數據都存儲在數據庫的路徑下（而不是在共享表空間 ibdata1 文件中）。但是數據中的其他對象，包括數據被修改之後，事務提交之間的版本信息，仍然存儲在共享表空間的 ibdata1 文件中。

1. .frm：在 MySQL 中建立任何一張數據表，其對應的數據庫目錄下都會有該表的 .frm 文件。.frm文件用來保存每個數據表的元數據（meta）和表結構等信息。數據庫崩潰時，可以用 .frm 文件恢復表結構。.frm 文件跟存儲引擎無關，任何存儲引擎的數據表都有 .frm 文件，命名方式為表名.frm，如 users.frm。

• MySQL 8.0 版本開始，frm 文件被取消，MySQL 把文件中的數據都寫到瞭系統表空間。通過利用 InnoDB 存儲引擎來實現表 DDL 語句操作的原子性（在之前版本中是無法實現表 DDL 語句操作的原子性的，如 TRUNCATE 無法回滾）。

1. .MYD和.MYI

• .MYD 理解為 My Data，用於存放 MyISAM 表的數據
• .MYI 理解為 My Index，主要存放 MyISAM 表的索引及相關信息。

1. .ibd：對於 InnoDB 存儲引擎的數據表，一個表對應兩個文件，一個是 *.frm，存儲表結構信息；一個是*.ibd，存儲表中數據。
2. .ibd和.ibdata：都是專屬於 InnoDB 存儲引擎的數據庫文件。當采用共享表空間時，所有 InnoDB 表的數據均存放在 .ibdata 中。所以當表越來越多時，這個文件會變得很大。相對應的 .ibd 就是采用獨享表空間時 InnoDB 表的數據文件。

• 當然，就算開啟瞭獨享表空間，ibdata 文件也會越來越大，因為這個文件裡還存儲瞭：
• 變更緩沖區
• 雙寫緩沖區
• 撤銷日志

MySQL配置文件（my.ini）詳解my.ini 是 MySQL 數據庫中使用的配置文件，MySQL 服務器啟動時會讀取這個配置文件，我們可以通過修改這個文件，達到更新配置的目的。

• 一般情況下，my.ini 在 MySql 安裝的根目錄下，也有可能在隱藏文件夾“ProgramData”下面。

文件內容如下：[client] port=3306 [mysql] default-character-set=gbk上面顯示的是客戶端的參數，[client] 和 [mysql] 都是客戶端，參數說明如下：

• port：表示 MySQL 客戶端連接服務器端時使用的端口號，默認的端口號為 3306。如果需要更改端口號的話，可以直接在這裡修改。
• default-character-set：表示 MySQL 客戶端默認的字符集。

服務器的參數，參數說明如下：

• port=3306 :表示 MySQL 服務器的端口號
• basedir=C:/Program Files/MySQL/MySQL Server 5.7/ :表示 MySQL 的安裝路徑
• datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data :表示 MySQL 數據文件的存儲位置，也是數據表的存放位置
• character-set-server=gb2312 :表示服務器端默認的字符集
• default-storage-engine=INNODB:創建數據表時，默認使用的存儲引擎
• sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION":表示 SQL 模式的參數，通過這個參數可以設置檢驗 SQL 語句的嚴格程度
• max_connections=100:表示允許同時訪問 MySQL 服務器的最大連接數。其中一個連接是保留的，留給管理員專用的
• query_cache_size=0:查詢時的緩存大小，緩存中可以存儲以前通過 SELECT 語句查詢過的信息，再次查詢時就可以直接從緩存中拿出信息，可以改善查詢效率
• table_cache=256:表示所有進程打開表的總數
• tmp_table_size=35M:表示內存中每個臨時表允許的最大大小
• thread_cache_size=8:表示緩存的最大線程數
• myisam_max_sort_file_size=100G:表示 MySQL 重建索引時所允許的最大臨時文件的大小
• myisam_sort_buffer_size=69M;表示重建索引時的緩存大小
• key_buffer_size=55M:表示關鍵詞的緩存大小
• read_buffer_size=64K:表示 MyISAM 表全表掃描的緩存大小
• read_rnd_buffer_size=256K:表示將排序好的數據存入該緩存中
• sort_buffer_size=256K:表示用於排序的緩存大小

InnoDB 存儲引擎使用的參數，參數說明如下：

• innodb_additional_mem_pool_size=3M：表示附加的內存池，用來存儲 InnoDB 表的內容。
• innodb_flush_log_at_trx_commit=1：是設置提交日志的時機，若設置為 1，InnoDB 會在每次提交後將事務日志寫到磁盤上。
• innodb_log_buffer_size=2M：表示用來存儲日志數據的緩存區的大小。
• innodb_buffer_pool_size=107M：表示緩存的大小，InnoDB 使用一個緩沖池類保存索引和原始數據。
• innodb_log_file_size=54M：表示日志文件的大小。
• innodb_thread_concurrency=18：表示在 InnoDB 存儲引擎允許的線程最大數。

註意：每次修改 my.ini 文件中的參數後，必須重新啟動 MySQL 服務才會有效。MySQL服務器的SQL模式（sql_mode變量）MySQL 服務器可以在不同的 SQL 模式下運行，並且可以針對不同的客戶端以不同的方式應用這些模式，具體取決於 sql_mode 系統變量的值。SQL 模式定義瞭 MySQL 數據庫所支持的 SQL 語法和數據校驗（數據驗證檢查），這樣可以更容易的在不同環境下使用 MySQL。在 MySQL 中，SQL 模式常用來解決下面幾類問題：

• 通過設置 SQL Mode，可以完成不同嚴格程度的數據校驗，有效地保障瞭數據的準確性。
• 通過設置 SQL Mode 為 ANSI 模式，可以保證大多數 SQL 符合標準的 SQL 語法，使不同數據庫之間進行遷移時，不需要進行較大的修改。
• 在不同數據庫之間進行數據遷移之前，設置 SQL Mode 可以使 MySQL 中的數據更方便地遷移到目標數據庫中。

sql_mode 系統變量的常用值下面列出瞭幾種 SQL 模式常用的值。

1. TRICT_ ALL_TABLES 和 STRICT_ TRANS_TABLES：如果將 sql_mode 的值設置為 TRICT_ALL_TABLES 和 STRICT_TRANS_TABLES，那麼 MySQL將啟用“嚴格”模式。在嚴格模式下，MySQL 服務器會更加嚴格地對待接收到的不合格數據，它不會把這些不合格的數據轉換為最為接近的有效值，而是會拒絕接收它們。簡單來說 MySQL 的嚴格模式就是 MySQL 自身對數據進行的嚴格校驗，例如格式、長度和類型等。
2. TRADITIONAL：類似於嚴格模式，但是對於插入的不合格值會給出錯誤而不是警告。可以應用在事務表和非事務表

• 用於事務表隻要出現錯誤就會立即回滾。
• 如果是非事務存儲引擎，建議不要把 SQL Mode 值設置為 TRADITIONAL，因為出現錯誤前進行的操作不會回滾，這樣會導致操作隻進行瞭一部分。

1. ANSI_QUOTES：MySQL 服務器會把雙引號識別為一個標識符引用字符，不是字符串的引號字符。在啟用 ANSI_QUOTES 時，不能用雙引號來引用字符串。
2. PIPES_ AS_ CONCAT：會讓 MySQL 服務器把||當成一個標準的 SQL 字符串連接運算符，而不會把它當成是 OR 運算符的同義詞。

• 在 Oracle 等數據庫中，||被視為字符串的連接操作符，所以在其它數據庫中含有||操作符的 SQL 在 MySQL 中將無法執行，為瞭解決這個問題，MySQL 提供瞭這個值。

1. ANSI：會同時啟用 ANSI_QUOTES、PIPES_ AS_CONCAT 和其它的幾個模式值，使 MySQL 服務器的行為比它的默認運行狀態更接近於標準 SQL。

如何設置 sql_mode在設置 SQL 模式時，需要指定一個由單個模式值或多個模式值（多個模式值用逗號分隔）構成的值，或者指定一個空字符串，用以清除該值。值不區分大小寫。如果想在啟動服務器時設置 SQL 模式，那麼可以在 mysqld 命令行，或者在某個選項文件裡設置系統變量 sql_mode。可以使用下面語句：sql_mode= "TRADITIONAL " sql_mode= "ANSI_ QUOTES, PIPES_ AS_ CONCAT"如果隻是想在運行時更改 SQL 模式，那麼可以使用 SET 語句來設置 sql_mode 系統變量。SET sql_mode = ' TRADITIONAL' ;如果想設置全局性的 SQL 模式，則需要加上 GLOBAL 關鍵字：SET GLOBAL sql_mode = ' TRADITIONAL';設置全局變量需要具備 SUPER 管理權限。新設置的全局變量值將成為此後連入客戶端的默認 SQL 模式。如果想獲取當前會話或全局的 SQL 模式值，則可以使用如下語句:SELECT @@SESSION.sql_mode; SELECT @@GLOBAL. sql_mode;其返回值由當前啟用的所有模式構成，兩個模式之間以逗號隔開。如果當前沒有啟用任何模式，則返回一個空值。

為什麼那麼多人學MySQL，最後放棄瞭。因為你走彎路瞭，沒有系統的規劃，碎片化的學習，讓你對MySQL沒有一個整體的認知，而且沒有人給你引路指導、糾錯，讓你很難有進步。【MySQL課程大綱】備戰加薪季，硬核技能來助力！關註我的微信公眾號【DB哥】免費學MySQL高級課程第1章 互聯網公司MySQL安裝部署 1-1互聯網公司安裝MySQL規范[09:13]1-2手把手帶你安裝MySQL5.7.26[18:20]1-3手把手帶你安裝MySQL8.0.20[11:52]1-4.MySQL多實例的二三事[16:10] 第2章 MySQL啟動時遇到的那些坑 2-1生產中的MySQL啟動及MySQL啟動順序[17:54]2-2常見MySQL啟動失敗案例(1)[22:15]2-3常見MySQL啟動失敗案例(2)[21:09]2-4MySQL數據庫用戶安全策略[14:44]2-5找回丟失的用戶密碼[06:36] 第3章 MySQL數據庫亂碼之字符集 3-1MySQL字符集[32:27]3-2插入中文亂碼解決[05:54]3-3數據庫常見字符集及如何選擇字符集[03:48]3-4生產中如何徹底避免出現亂碼[04:54] 第4章 MySQL用戶權限管理 4-1MySQL用戶權限管理及註意事項[25:53]4-2你必須知道的MySQL用戶知識[14:10]4-3MySQL用戶權限案例(1)[12:19]4-4MySQL用戶權限案例(2)[20:03] 第5章 SQL編程與性能 5-1SQL編程入門也精彩(1)[26:57]5-2SQL編程入門也精彩(2)[24:51]5-3MySQL數據類型與性能(1)[35:42]5-4MySQL數據類型與性能(2)[21:00]5-5MySQL約束[32:54]5-6SQL編程高級之導入測試庫及表結構分析[06:56]5-7SQL編程高級之查詢列[14:40]5-8SQL編程高級之where子句[30:09]5-9SQL編程高級之group by子句[15:07]5-10SQL編程高級之order by子句[10:38]5-11SQL編程高級之limit子句及聚合函數[12:43]5-12SQL編程高級之多表查詢[11:02]5-13SQL編程高級之子查詢[18:56]5-14SQL編程高級之MySQL巡檢案例[13:50] 第6章 MySQL索引性能優化 6-1MySQL索引與二分查找法[38:23]6-2剖析b+tree數據結構[34:14]6-3相輔相成的聚集索引和輔助索引[29:08]6-4覆蓋索引與回表查詢[13:22]6-5創建高性能的主鍵索引[14:57]6-6唯一索引與普通索引的性能差距[18:06]6-7前綴索引帶來的性能影響[13:04]6-8你真的會使用聯合索引嗎[18:50]6-9Online DDL影響數據庫的性能和並發[24:21]6-10pt-osc原理與應用[22:15]6-11生產中索引的管理[19:59]6-12SQL語句無法使用索引的情況[22:10] 第7章 information_schema和sys中索引應用 7-1大有用處的STATISTICS[15:22]7-2判斷索引創建是否合理[13:41]7-3檢查聯合索引創建是否合理[09:32]7-4有瞭聯合索引(a,b),還要創建a索引嗎[06:50]7-5如何查找冗餘索引[05:52]7-6如何查找產生額外排序的sql語句[09:48]7-7如何查找產生臨時表的sql語句[08:36]7-8全表掃描及統計產生全表掃描的sql語句[08:12]7-9統計無用的索引[02:33]7-10索引統計信息管理[07:26] 第8章 MySQL優化器算法與執行計劃 8-1簡單嵌套查詢算法-SNLJ[08:01]8-2基於索引的嵌套查詢算法-INLJ[08:10]8-3基於塊的嵌套查詢算法- BNLJ[09:30]8-4Multi-Range Read算法[06:39]8-5聯表查詢算法BKA[05:19]8-6mysql三層體系結構和ICP索引條件下推[10:49]8-7一條查詢SQL語句是怎樣運行的[11:38]8-8一條更新SQL語句是怎樣運行的[05:53]8-9MySQL長連接與短連接的選擇[07:10]8-10執行計劃explain詳解[24:20] 第9章 MySQL核心之InnoDB存儲引擎 9-1innodb存儲引擎體系結構[13:02]9-2提升數據庫性能-緩沖池[34:30]9-3刷寫臟頁check point[10:39]9-4重做日志-redo log[23:39]9-5回滾日志-undo log[10:04]9-6.DML語句性能提升技術-插入緩沖[14:44]9-7數據寫入可靠性提升技術-兩次寫[06:58]9-8自適應哈希索引-AHI[06:51]9-9innodb預讀預寫技術[08:17]9-10參數配置文件[09:04]9-11錯誤日志文件[05:46]9-12慢查詢日志文件[11:42]9-13MySQL二進制日志(1)[31:28]9-14MySQL二進制日志(2)[24:57]9-15表空間文件[12:05]9-16InnoDB存儲引擎表空間結構[17:05]9-17表碎片清理[14:22]9-18表空間文件遷移[16:39] 第10章 MySQL查詢優化 10-1MySQL查詢優化技術[08:34]10-2子查詢優化[11:31]10-3外連接消除[04:43]10-4生產中可不可以使用join聯表查詢[11:41]10-5group by分組優化[10:16]10-6order by排序優化[09:02]10-7MySQL性能抖動問題[04:26]10-8count(*)優化[08:22]10-9磁盤性能基準測試[11:11]10-10MySQL基準測試[07:42] 第11章 事務 11-1認識事務[12:30]11-2事務控制語句[20:40]11-3事務的實現方式[13:40]11-4purge thread線程[06:09]11-5事務統計QPS與TPS[13:02]11-6事務隔離級別[28:19]11-7事務組提交group commit[08:03]11-8事務兩階段提交[07:51] 第12章 鎖優化 12-1認識鎖[10:57]12-2innodb行鎖[19:21]12-3索引對行鎖粒度的影響[26:16]12-4FTWRL全局讀鎖[06:19]12-5innodb表鎖[12:04]12-6innodb意向鎖與MDL鎖[15:45]12-7自增鎖[11:27]12-8插入意向鎖[07:13]12-9死鎖[16:51]12-10MySQL兩階段鎖協議[09:55] 第13章 MVCC多版本並發控制 13-1MVCC多版本並發控制[13:01] 第14章 備份恢復 14-1生產中備份恢復的方式[08:00]14-2mysqldump備份實戰及原理[24:28]14-3mysqldump全備及binlog恢復數據[13:43]14-4xtrabackup備份工具安裝[04:03]14-5xtrabackup備份恢復實戰及原理[14:08]14-6binlog備份[10:07] 第15章 MySQL主從復制架構 15-1認識主從復制[11:16]15-2主從復制架構部署[09:26]15-3主從復制原理深入講解[16:29]15-4從庫狀態詳解[26:21]15-5過濾復制[18:42]15-6主從復制案例主庫刪除[09:31]15-7主從復制中斷解決方案[12:52]15-8延遲復制[07:40]15-9主庫drop誤操作利用延遲復制恢復案例[15:27]15-10並行復制[15:47]15-11增強半同步復制[11:11]15-12主從數據一致性校驗[07:35]15-13gtid復制[13:17]15-14主從復制架構[10:33] 第16章 MySQL高可用和讀寫分離架構優化 16-1MHA高可用架構部署[20:39]16-2主庫宕機故障模擬及處理[05:03]16-3MHA VIP自動切換[09:54]16-4MHA主從數據自動補足[07:42]16-5Atlas讀寫分離高性能架構[15:09]16-6讀寫分離架構應用[08:52]16-7Atlas在線管理[04:18]16-8讀寫分離避坑指南[07:11] 第17章 MySQL分庫分表優化 17-1MyCAT分佈式架構入門及雙主架構[11:48]17-2MyCAT架構部署[20:39]17-3MyCAT安裝[08:39]17-4MyCAT讀寫分離架構[13:52]17-5MyCAT高可用讀寫分離架構[07:21]17-6schema.xml配置文件詳解[07:07]17-7MyCAT垂直分表[10:55]17-8MyCAT水平分表-范圍分片[10:18]17-9MyCAT水平分表-取模分片[06:38]17-10MyCAT水平分表-枚舉分片[08:10]17-11MyCAT全局表與ER表開始學習

關註我的微信公眾號【DB哥】，免費學MySQL高級課程，快學起來吧