## **字符集** 在MysQL8.0版本之前，默認字符集為 latin1 （ISO-8859-1）, utf8字符集指向的是utf8mb3。網站開發人員在數據庫設計的時候往往會將編碼修改為utf8字符集。如果遺忘修改默認的編碼,就會出現亂碼的問題。從MySQL 8.0開始,數據庫的默認編碼將改為utf8mb4 ,從而避免上述亂碼的問題。 **MySQL 的 utf8 編碼是 3 字節，無法存儲 一些表情符號，要用 utf8mb4才行， 4字節的。** ``` 查看默認使用的字符集 show variables like 'character%' # 或者 show variables like 'char%' ``` 設置默認字符集為 utf8mb4 Windows 是 my.ini 文件 Linux 是 /etc/my.cnf `character_set_server = utf8mb4` 修改已創建數據庫的字符集 ``` alter database 數據庫名稱 character set 'utf8mb4' ``` 修改已創建數據表的字符集 ``` alter table 表名 convert to character set 'utf8mb4' ``` ## 字母大小寫 在 SQL 中，關鍵字和函數名是不用區分字母大小寫的，比如 SELECT、WHERE、ORDER、GROUP BY 等關 鍵字，以及 ABS、MOD、ROUND、MAX 等函數名。 **但是數據庫名、表名、別名、字段名等數據 在 windows系統默認大小寫不敏感 1 ， linux系統是大小寫敏感的 0。** 命令查看： ``` SHOW VARIABLES LIKE '%lower_case_table_names%' ``` lower\_case\_table\_names參數值的設置： * 默認為0，大小寫敏感 。 * 設置1，大小寫不敏感。創建的表，數據庫都是以小寫形式存放在磁盤上，對于sql語句都是轉 換為小寫對表和數據庫進行查找。 * 設置2，創建的表和數據庫依據語句上格式存放，凡是查找都是轉換為小寫進行 ## SQL編寫建議 1. 關鍵字和函數名稱全部大寫； 2. 數據庫名、表名、表別名、字段名、字段別名等全部小寫； 3. SQL 語句必須以分號結尾。 雖然關鍵字和函數名稱在 SQL 中不區分大小寫，也就是如果小寫的話同樣可以執行。但是同時將關鍵詞 和函數名稱全部大寫，以便于區分數據庫名、表名、字段名。 ## MySQL的數據目錄 ### 查看默認數據庫 * **mysql ** MySQL 系統自帶的核心數據庫，它存儲了MySQL的用戶賬戶和權限信息，一些存儲過程、事件的定 義信息，一些運行過程中產生的日志信息，一些幫助信息以及時區信息等。 * **information_schema** 庫保存著MySQL服務器 維護的所有其他數據庫的信息 ，比如有 哪些表、哪些視圖、哪些觸發器、哪些列、哪些索引。 * **performance_schema **這個數據庫里主要保存MySQL服務器運行過程中的一些狀態信息，可以 用來 監控 MySQL 服務的各類性能指標 。包括統計最近執行了哪些語句，在執行過程的每個階段都 花費了多長時間，內存的使用情況等信息。 * **sys **這個數據庫主要是通過 視圖 的形式把 information_schema 和 performance_schema 結合起來，幫助系統管理員和開發人員監控 MySQL 的技術性能。 ## InnoDB存儲引擎模式 ### **表結構** 為了保存表結構， InnoDB 在 數據目錄 下對應的數據庫子目錄下創建了一個專門用于描述表結構的文件 ，文件名是這樣：**表名.frm** ### 表中數據和索引 **系統表空間（system tablespace）**：默認情況下，InnoDB會在數據目錄下創建一個名為 ibdata1 、大小為 12M 的文件，這個文件就是對應 的 系統表空間 在文件系統上的表示。怎么才12M？注意這個文件是 自擴展文件 ，當不夠用的時候它會自 己增加文件大小。 **獨立表空間(file-per-table tablespace)**： 每 一個表建立一個獨立表空間 ，也就是說我們創建了多少個表，就有多少個獨立表空間。使用 獨立表空間 來 存儲表數據的話，會在該表所屬數據庫對應的子目錄下創建一個表示該獨立表空間的文件，文件名和表 名相同，只不過添加了一個 .ibd 的擴展名而已，所以完整的文件名稱長這樣：**表名.ibd** ## 1 邏輯架構剖析 ### 1.1 服務器處理客戶端請求  ### 1.3 第1層：連接層 1. 系統（客戶端）訪問 MySQL 服務器前，做的第一件事就是建立 **TCP 連接。** 2. 經過**三次握手**建立連接成功后， MySQL 服務器對 TCP 傳輸過來的賬號密碼做**身份認證**、**權限獲取**。 * 用戶名或密碼不對，會收到一個Access denied for user錯誤，客戶端程序結束執行 * 用戶名密碼認證通過，會從權限表查出賬號擁有的權限與連接關聯，之后的權限判斷邏輯，都將依 賴于此時讀到的權限 3. TCP 連接收到請求后，必須要分配給一個線程專門與這個客戶端的交互。所以還會有個線程池，去走后面的流程。每一個連接從線程池中獲取線程，省去了創建和銷毀線程的開銷 ### 1.4 第2層：服務層 * SQL Interface: SQL接口 * 接收用戶的SQL命令，并且返回用戶需要查詢的結果。比如SELECT ... FROM就是調用SQL Interface * MySQL支持DML（數據操作語言）、DDL（數據定義語言）、存儲過程、視圖、觸發器、自定 義函數等多種SQL語言接口。 * Parser: 解析器 * 在解析器中對 SQL 語句進行語法分析、語義分析。將SQL語句分解成數據結構，并將這個結構 傳遞到后續步驟，以后SQL語句的傳遞和處理就是基于這個結構的。如果在分解構成中遇到錯 誤，那么就說明這個SQL語句是不合理的。 * 在SQL命令傳遞到解析器的時候會被解析器驗證和解析，并為其創建 語法樹 ，并根據數據字 典豐富查詢語法樹，會 驗證該客戶端是否具有執行該查詢的權限 。創建好語法樹后，MySQL還 會對SQl查詢進行語法上的優化，進行查詢重寫。 * Optimizer: 查詢優化器 * SQL語句在語法解析之后、查詢之前會使用查詢優化器確定 SQL 語句的執行路徑，生成一個 執行計劃 。 * 這個執行計劃表明應該 使用哪些索引 進行查詢（全表檢索還是使用索引檢索），表之間的連 接順序如何，最后會按照執行計劃中的步驟調用存儲引擎提供的方法來真正的執行查詢，并將 查詢結果返回給用戶。 * 它使用“ 選取-投影-連接 ”策略進行查詢。例如：SELECT id,name FROM student WHERE gender = '女'; 這個SELECT查詢先根據WHERE語句進行 選取 ，而不是將表全部查詢出來以后再進行gender過 濾。 這個SELECT查詢先根據id和name進行屬性 投影 ，而不是將屬性全部取出以后再進行過 濾，將這兩個查詢條件 連接 起來生成最終查詢結果。 * Caches & Buffers： 查詢緩存組件 * MySQL內部維持著一些Cache和Buffer，比如Query Cache用來緩存一條SELECT語句的執行結 果，如果能夠在其中找到對應的查詢結果，那么就不必再進行查詢解析、優化和執行的整個過 程了，直接將結果反饋給客戶端。 * 這個緩存機制是由一系列小緩存組成的。比如表緩存，記錄緩存，key緩存，權限緩存等 * 這個查詢緩存可以在 不同客戶端之間共享 。 * 從MySQL 5.7.20開始，不推薦使用查詢緩存，并在 MySQL 8.0中刪除 ## 1.5 第3層：引擎層 插件式存儲引擎層（ Storage Engines），真正的負責了MySQL中數據的存儲和提取，對物理服務器級別 維護的底層數據執行操作，服務器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，這樣 我們可以根據自己的實際需要進行選取。 MySQL 8.0.25默認支持的存儲引擎如下：  1.6 存儲層 所有的數據，數據庫、表的定義，表的每一行的內容，索引，都是存在 文件系統 上，以 文件 的方式存 在的，并完成與存儲引擎的交互。當然有些存儲引擎比如InnoDB，也支持不使用文件系統直接管理裸設 備，但現代文件系統的實現使得這樣做沒有必要了。在文件系統之下，可以使用本地磁盤，可以使用 DAS、NAS、SAN等各種存儲系統。 ## 2. SQL執行流程 ### 2.1 MySQL 的SQL執行流程  MySQL的查詢流程： 1. **查詢緩存：不推薦使用**，因為查詢緩存往往效率不高，所以在 MySQL8.0 之后就拋棄 了這個功能。查詢緩存是提前把查詢結果緩存起來，只要該表的 結構或者數據被修改，緩存就會刪除 * 如果在查詢緩存中發現了這條 SQL 語句的執行結果，就會直接將結果返回給客戶端。 * 如果沒 有，就進入到解析器階段。 **2. 解析器：在解析器中對 SQL 語句進行語法分析、語義分析。** * 分析器先做“ 詞法分析 ”，識別出輸入 的字符串分別是什么，代表什么。 * 然后做“ 語法分析 ”。語法分析器（比如：Bison）會根據語法規則，判斷輸 入的這個 SQL 語句是否 滿足 MySQL 語法，如果正確就生成語法樹。  3. **優化器**：在優化器中會確定 SQL 語句的執行路徑，比如是根據 全表檢索 ，還是根據 索引檢索 等。在查詢優化器中，可以分為 邏輯查詢 優化階段和 物理查詢 優化階段。 4.** 執行器：** 在執行之前需要判斷該用戶是否具備權限 。如果沒有，就會返回權限錯誤。如果具備權限，就執行 SQL 查詢并返回結果。在 MySQL8.0 以前的版本，如果設置了查詢緩存，這時會將查詢結果進行緩存。 至此，這個語句就執行完成了。對于有索引的表，執行的邏輯也差不多。 SQL 語句在 MySQL 中的流程是： SQL語句→查詢緩存→解析器→優化器→執行器  ## 3. 數據庫緩沖池 ### 1. 緩沖池（Buffer Pool） 在 InnoDB 存儲引擎中有一部分數據會放到內存中，緩沖池則占了這部分內存的大部分，它用來存儲各種 數據的緩存，如下圖所示：  從圖中，能看到** InnoDB 緩沖池包括了數據頁、索引頁、插入緩沖、鎖信息、自適應 Hash 和數據字典 信息等。** ## 4. 存儲引擎 ### 4.1. 查看存儲引擎 ``` show engines; show engines \G; ``` ### 4.2. 設置系統默認的存儲引擎 查看默認的存儲引擎 ``` show variables like '%storage_engine%'; #或 SELECT @@default_storage_engine; ``` 修改默認的存儲引擎 ``` SET DEFAULT_STORAGE_ENGINE=MyISAM; ``` 或者修改 my.cnf 文件 Windows 是 my.ini ``` default-storage-engine=MyISAM # 重啟服務 systemctl restart mysqld.service ``` ### 4.3 設置表的存儲引擎 用默認的存儲引擎 InnoDB 存儲引擎是負責對表中的數據進行提取和寫入工作的，我們可以為 不同的表設置不同的存儲引擎 ，也就是 說不同的表可以有不同的物理存儲結構，不同的提取和寫入方式。 #### 4.3.1 創建表時顯示指定存儲引擎 ``` CREATE TABLE 表名( 建表語句; ) ENGINE = 存儲引擎名稱; ``` 4.3.2 修改表的存儲引擎 ``` ALTER TABLE 表名 ENGINE = 存儲引擎名稱; ``` ### 4.4 引擎介紹 InnoDB 引擎：具備外鍵支持功能的事務存儲引擎 * InnoDB是MySQL的 默認事務型引擎 ，它被設計用來處理大量的短期(short-lived)事務。可以確保事務 * 的完整提交(Commit)和回滾(Rollback)。 * 除了增加和查詢外，還需要更新、刪除操作，那么，應優先選擇InnoDB存儲引擎。 * 除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮InnoDB引擎。 * InnoDB是 為處理巨大數據量的最大性能設計 。 * MyISAM只緩存索引，不緩存真實數據；InnoDB不僅緩存索引還要緩存真實數據， 對內存要求較 高 ，而且內存大小對性能有決定性的影響。 MyISAM 引擎：主要的非事務處理存儲引擎 * MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(GIS)等，但MyISAM 不支持事務、行級鎖、外鍵 ，有一個毫無疑問的缺陷就是 崩潰后無法安全恢復 。 * 應用場景：只讀應用或者以讀為主的業務