MySQL進(jìn)階篇：深入理解啟動項、系統(tǒng)變量、字符集

前言

Mysql數(shù)據(jù)庫是當(dāng)前應(yīng)用最為的廣泛的數(shù)據(jù)庫，在實際工作中也經(jīng)常接觸到。真正用好Mysql也不僅僅是會寫sql就行，更重要的是真正理解其內(nèi)部的工作原理。本文先從宏觀角度介紹一些mysql相關(guān)的知識點，目的是為了讓大家對mysql能有一個大體上的認(rèn)知，后續(xù)再逐一對每個知識點的進(jìn)行深入解讀。

通信方式

Mysql采用了典型的客戶端/服務(wù)器架構(gòu)(C/S架構(gòu))模式。對于計算機(jī)而言，數(shù)據(jù)庫客戶端程序和服務(wù)器程序分別運在不同的進(jìn)程中。所以客戶端進(jìn)程向服務(wù)器進(jìn)程發(fā)送sql請求并得到返回結(jié)果的過程本質(zhì)上就是進(jìn)程間通信。mysql支持的進(jìn)程間通信方式包括TCP/IP、命名管道、共享內(nèi)存、unix域套接字文件。

1.TCP/IP

如果服務(wù)端進(jìn)程和客戶端進(jìn)程運行在不同的主機(jī)中，只能通過TCP/IP網(wǎng)絡(luò)通信協(xié)議進(jìn)行通信。mysql服務(wù)器啟動時監(jiān)聽某個端口(默認(rèn)3306)，等待客戶端進(jìn)程來連接。當(dāng)然，服務(wù)端進(jìn)程和客戶端進(jìn)程在同一主機(jī)中，通過本機(jī)回環(huán)地址(127.0.0.1)也是可以使用TCP/IP進(jìn)行通信的。

2.命名管道或共享內(nèi)存

如果服務(wù)端進(jìn)程和客戶端進(jìn)程都運行在一臺windows主機(jī)上，可以通過命名管道或共享內(nèi)存方式進(jìn)行通信。

需要在啟動服務(wù)器程序的命令中加上–enable-named-pipe參數(shù)，然后在啟動客戶端程序的命令中加入–pipe或者–protocol=pipe參數(shù)。

需要在啟動服務(wù)器程序的命令中加上–shared-memory參數(shù)，在成功啟動服務(wù)器后，共享內(nèi)存便成為本地客戶端程序的默認(rèn)連接方式，不過我們也可以在啟動客戶端程序的命令中加入–protocol=memory參數(shù)來顯式的指定使用共享內(nèi)存進(jìn)行通信。

如果我們的服務(wù)器進(jìn)程和客戶端進(jìn)程都運行在同一臺操作系統(tǒng)為類Unix的機(jī)器上的話，我們可以使用Unix域套接字文件來進(jìn)行進(jìn)程間通信。

真實環(huán)境中，服務(wù)器和客戶端基本都是運行在不同主機(jī)中的，它們之間采用的通信方式就是TCP/IP。

一條查詢sql的基本處理過程

不論客戶端進(jìn)程和服務(wù)器進(jìn)程是采用哪種方式進(jìn)行通信，最后實現(xiàn)的效果都是：客戶端向服務(wù)器發(fā)送一段文本（sql語句），服務(wù)器進(jìn)程處理后再向客戶端進(jìn)程發(fā)送一段文本（處理結(jié)果）。下面以查詢sql為例，簡單說明一下服務(wù)器處理客戶端請求的大致處理過程。

從圖中我們可以看出，服務(wù)器程序處理來自客戶端的查詢請求大致需要經(jīng)過三個部分，分別是連接管理、解析與優(yōu)化、存儲引擎。

連接管理

每當(dāng)有一個客戶端連接到服務(wù)器時，服務(wù)器都會創(chuàng)建一個線程來專門處理與這個客戶端的交互。在客戶端程序發(fā)起連接的時候，需要攜帶主機(jī)信息、用戶名、密碼，服務(wù)器程序會對客戶端程序提供的這些信息進(jìn)行認(rèn)證，如果認(rèn)證失敗，服務(wù)器程序會拒絕連接。

當(dāng)連接建立后，與該客戶端關(guān)聯(lián)的服務(wù)器線程會一直等待客戶端發(fā)送請求，MySQL服務(wù)器接收到的請求只是一個文本消息，該文本消息還要經(jīng)過各種處理才能將最后的處理結(jié)果返回客戶端。

解析與優(yōu)化

到現(xiàn)在為止，MySQL服務(wù)器已經(jīng)獲得了文本形式的請求，接著還需要經(jīng)過查詢緩存、語法解析、查詢優(yōu)化等進(jìn)行處理。

查詢緩存

如果服務(wù)器開啟了查詢緩存，在執(zhí)行查詢的時候會先從查詢緩存中獲取查詢結(jié)果。如果命中緩存則直接返回結(jié)果，否則接著執(zhí)行。mysql不推薦使用查詢緩存，并且在8.0版本已經(jīng)移除此功能。真實環(huán)境中也不會使用，因此不用詳細(xì)了解。

語法解析

這一步主要做的事情是對語句基于SQL語法進(jìn)行詞法和語法分析和語義的解析，將要查詢的表、各種查詢條件都提取出來放到MySQL服務(wù)器內(nèi)部使用的一些數(shù)據(jù)結(jié)構(gòu)上來。

查詢優(yōu)化

因為我們寫的MySQL語句執(zhí)行起來效率可能并不是很高，MySQL的優(yōu)化程序會對我們的語句做一些優(yōu)化，如外連接轉(zhuǎn)換為內(nèi)連接、表達(dá)式簡化、子查詢轉(zhuǎn)為連接等等。優(yōu)化的結(jié)果就是生成一個執(zhí)行計劃，這個執(zhí)行計劃表明了應(yīng)該使用哪些索引進(jìn)行查詢，表之間的連接順序等。我們可以使用EXPLAIN語句來查看某個語句的執(zhí)行計劃。

存儲引擎

mysql數(shù)據(jù)是保存在數(shù)據(jù)表里面，但表只是邏輯上的概念，數(shù)據(jù)真正是保存在物理磁盤上的。存儲引擎負(fù)責(zé)的就是物理上數(shù)據(jù)的保存和提取。為了實現(xiàn)不同的功能，MySQL提供了各式各樣的存儲引擎，不同存儲引擎在物理上的存儲結(jié)構(gòu)存在一些差異。但是不同的存儲引擎提供了統(tǒng)一的調(diào)用接口（也就是存儲引擎API）。

mysql支持多種存儲引擎，可以通過如下命令查看：

雖然支持的存儲引擎很多，但是我們需要重點關(guān)注InnoDB以及適當(dāng)了解MyISAM存儲引擎即可！

為了管理方便，人們把連接管理、查詢緩存、語法解析、查詢優(yōu)化這些并不涉及真實數(shù)據(jù)存儲的功能劃分為MySQL server的功能，把真實存取數(shù)據(jù)的功能劃分為存儲引擎的功能。

啟動選項和系統(tǒng)變量

mysql程序(包括服務(wù)器相關(guān)程序和客戶端相關(guān)程序)在啟動的時候可以指定啟動參數(shù)，來控制程序啟動后的行為。這些啟動參數(shù)可以放在命令行中指定，也可以把它們放在配置文件中指定。

在命令行上使用啟動選項

啟動mysql程序的命令行后邊指定啟動選項的通用格式如下：

--啟動選項1[=值1] --啟動選項2[=值2] ... --啟動選項n[=值n]

1

各個啟動選項之間使用空白字符隔開，在每一個啟動選項名稱前邊添加–。對于不需要值的啟動選項，比方說skip-networking，它們就不需要指定對應(yīng)的值。對于需要指定值的啟動選項，比如default-storage-engine我們在指定這個設(shè)置項的時候需要顯式的指定它的值，比方說InnoDB、MyISAM。

mysqld --default-storage-engine=MyISAM --skip-networking

比如上面的啟動項就表示默認(rèn)存儲引擎為MyISAM，并且禁止使用TCP/IP方式通信。

為了使用的方便，對于一些常用的選項提供了短形式，比如：

配置文件中使用選項

相比于使用命令行的方式設(shè)置啟動選項，mysql更推薦使用配置文件來設(shè)置啟動選項。我們把需要設(shè)置的啟動選項都寫在這個配置文件中，每次啟動服務(wù)器的時候都從這個文件里加載相應(yīng)的啟動選項。

MySQL程序在啟動時會尋找多個路徑下的配置文件，這些路徑有的是固定的，有的是可以在命令行指定的。根據(jù)操作系統(tǒng)的不同，配置文件的路徑也有所不同，并且越后面路徑下的配置優(yōu)先級越好。總之就是多個路徑下都可以存在配置文件，并且有個優(yōu)先級的關(guān)系。這里就不展開了。

配置文件的內(nèi)容

與在命令行中指定啟動選項不同的是，配置文件中的啟動選項被劃分為若干個組，每個組有一個組名，用中括號[]擴(kuò)起來，像這樣：

[server] (具體的啟動選項...) [mysqld] (具體的啟動選項...) [mysqld_safe] (具體的啟動選項...) [client] (具體的啟動選項...) [mysql] (具體的啟動選項...) [mysqladmin] (具體的啟動選項...)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

啟動mysql程序時，會使用對應(yīng)的一個或多個組下的啟動選項。每個組下邊可以定義若干個啟動選項，我們以[server]組為例來看一下填寫啟動選項的形式（其他組中啟動選項的形式是一樣的）：

[server] option1 #這是option1，該選項不需要選項值 option2 = value2 #這是option2，該選項需要選項值

1

2

3

系統(tǒng)變量

Mysql系統(tǒng)變量是指能夠影響服務(wù)器程序運行行為的變量。比如允許同時連入的客戶端數(shù)量由系統(tǒng)變量max_connections控制，表的默認(rèn)存儲引擎由系統(tǒng)變量default_storage_engine控制。每個系統(tǒng)變量都有一個默認(rèn)值，我們可以使用命令行或者配置文件中的選項在啟動服務(wù)器時改變一些系統(tǒng)變量的值，或者在運行時動態(tài)修改(大多數(shù)系統(tǒng)變量支持動態(tài)修改)。

作用范圍

多個客戶端程序可以同時連接到一個服務(wù)器程序。對于同一個系統(tǒng)變量，我們有時想讓不同的客戶端有不同的值，mysql通過系統(tǒng)變量的作用范圍來解決上述問題。具體來說作用范圍分為下面兩種：

1.GLOBAL：全局變量，影響服務(wù)器的整體操作。

2.SESSION：會話變量，影響某個客戶端連接的操作。（注：SESSION有個別名叫LOCAL）

很顯然，通過啟動選項設(shè)置的系統(tǒng)變量的作用范圍都是GLOBAL的，也就是對所有客戶端都有效的。通過客戶端動態(tài)修改系統(tǒng)變量語法如下：

SET [GLOBAL|SESSION] 系統(tǒng)變量名 = 值;

1

如果在設(shè)置系統(tǒng)變量的語句中省略了作用范圍，默認(rèn)的作用范圍就是SESSION。同理，我們可以使用下列命令查看MySQL服務(wù)器程序支持的系統(tǒng)變量以及它們的當(dāng)前值：

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

狀態(tài)變量

Mysql狀態(tài)變量是指描述服務(wù)器運行狀態(tài)的變量，比方說Threads_connected表示當(dāng)前有多少客戶端與服務(wù)器建立了連接。

由于狀態(tài)變量是用來顯示服務(wù)器程序運行狀況的，所以它們的值只能由服務(wù)器程序自己來設(shè)置（對客戶端而言是只讀的）。與系統(tǒng)變量類似，狀態(tài)變量也有GLOBAL和SESSION兩個作用范圍的，所以查看狀態(tài)變量的語句可以這么寫：

SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

mysql支持的字符集和比較規(guī)則

在計算機(jī)中，數(shù)據(jù)最終都是以二進(jìn)制的形式保存的。因此，如果我們要保存字符串，首先就先得確定字符串中的每個字符對應(yīng)的二進(jìn)制數(shù)據(jù)是什么，然后再將這些二進(jìn)制數(shù)據(jù)保存到計算機(jī)中。將一個字符映射成一個二進(jìn)制數(shù)據(jù)的過程也叫做編碼，將一個二進(jìn)制數(shù)據(jù)映射到一個字符的過程叫做解碼。

使用字符集可以解決數(shù)據(jù)存儲的問題，但是無法完全解決字符之間相互比較的問題。簡單場景下，我們可以直接通過比較字符的二進(jìn)制數(shù)據(jù)來判斷大小，這種方式其實就是二進(jìn)制比較規(guī)則。而有些場景下，二進(jìn)制比較規(guī)則并不適用，比如忽略大小寫的時候。因此為了應(yīng)對不同的場景，同一種字符集可以有多種比較規(guī)則。

字符集

Mysql中支持很多種字符集，可以通過以下語句查看：

SHOW CHARSET [LIKE 匹配的模式];

1

mysql> SHOW CHARSET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | ... | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | ... +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.01 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

1.Charset: 字符集名稱

2.Description: 字符集描述

3.Default collation: 默認(rèn)的比較規(guī)則

4.Maxlen: 一個字符最大占用的字節(jié)數(shù)。對于采用變長編碼方式的字符集而言，一個字符占用的字節(jié)數(shù)不是固定的。比如在GB2312字符集中，一個字母只占用1個字節(jié)，而一個漢字占用了2個字節(jié)。

在Mysql中，utf8和utf8mb4的區(qū)別就在于1個字符占用的最大字節(jié)數(shù)不同。utf8一個字符占用1-3個字節(jié)，而utf8mb4一個字符占用1-4個字節(jié)。實際上，Mysql的utf8是utf8mb3的別名。如果需要保存一些占用4個字節(jié)的特殊字符(比如emoji表情)，建議使用utf8mb4字符集。

比較規(guī)則

可以通過以下語句查看mysql中支持的比較規(guī)則：

SHOW COLLATION [LIKE 匹配的模式];

1

mysql> SHOW COLLATION LIKE 'utf8\_%'; +--------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | ... +--------------------------+---------+-----+---------+----------+---------+ 27 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

1.Collation: 比較規(guī)則名稱，基本符合字符集名稱_語言_后綴模式。第一部分字符集名稱就是與其關(guān)聯(lián)的字符集的名稱開頭，第二部分表示該比較規(guī)則作用的語言，比如utf8_spanish_ci是以西班牙語的規(guī)則比較，utf8_general_ci是一種通用的比較規(guī)則。第三部分后綴主要用來表示要不要區(qū)分大小寫和重音之類的。

2.Charset: 關(guān)聯(lián)的字符集的名稱。

3.Default: yes表示是字符集默認(rèn)的比較規(guī)則。

字符集和比較規(guī)則作用域級別

Mysql中字符集和比較規(guī)則有4種作用域級別：

1.服務(wù)器級別

2.數(shù)據(jù)庫級別

3.表級別

4.列級別

實際上，字符集和比較較規(guī)則最后肯定是作用在列級別字段上的。可以簡單的認(rèn)為，如果列級別沒有指定字符集和比較較規(guī)則，就使用表級別的；如果表級別沒有指定字符集和比較較規(guī)則，就使用數(shù)據(jù)庫級別的；以此類推。

服務(wù)器級別

MySQL提供了兩個系統(tǒng)變量來表示服務(wù)器級別的字符集和比較規(guī)則：

1.character_set_server: 服務(wù)器級別的字符集

2.collation_server: 服務(wù)器級別的比較規(guī)則

服務(wù)器級別默認(rèn)的字符集是utf8，默認(rèn)的比較規(guī)則是utf8_general_ci。

數(shù)據(jù)庫級別

我們在創(chuàng)建和修改數(shù)據(jù)庫的時候可以指定該數(shù)據(jù)庫的字符集和比較規(guī)則，具體語法如下：

CREATE DATABASE 數(shù)據(jù)庫名 CHARACTER SET 字符集名稱 COLLATE 比較規(guī)則名稱; ALTER DATABASE 數(shù)據(jù)庫名 CHARACTER SET 字符集名稱 COLLATE 比較規(guī)則名稱;

1

2

3

4

5

6

7

比如：

mysql> CREATE DATABASE charset_demo_db -> CHARACTER SET gb2312 -> COLLATE gb2312_chinese_ci; Query OK, 1 row affected (0.01 sec)

1

2

3

4

如果想查看當(dāng)前數(shù)據(jù)庫使用的字符集和比較規(guī)則，可以查看下面兩個系統(tǒng)變量的值:

1.character_set_database: 當(dāng)前數(shù)據(jù)庫的字符集

2.collation_database: 當(dāng)前數(shù)據(jù)庫的比較規(guī)則

表級別

我們可以在創(chuàng)建和修改表的時候指定表的字符集和比較規(guī)則，語法如下：

CREATE TABLE 表名 (列的信息) CHARACTER SET 字符集名稱 COLLATE 比較規(guī)則名稱 ALTER TABLE 表名 CHARACTER SET 字符集名稱 COLLATE 比較規(guī)則名稱

1

2

3

4

5

6

7

8

比如：

mysql> CREATE TABLE t( -> col VARCHAR(10) -> ) CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0.03 sec)

1

2

3

4

列級別

需要注意的是，對于存儲字符串的列，同一個表中的不同的列也可以有不同的字符集和比較規(guī)則。

我們在創(chuàng)建和修改列定義的時候可以指定該列的字符集和比較規(guī)則，語法如下：

CREATE TABLE 表名( 列名 字符串類型 CHARACTER SET 字符集名稱 COLLATE 比較規(guī)則名稱, 其他列... ); ALTER TABLE 表名 MODIFY 列名 字符串類型 CHARACTER SET 字符集名稱 COLLATE 比較規(guī)則名稱;

1

2

3

4

5

6

比如我們修改一下表t中列col的字符集和比較規(guī)則可以這么寫：

mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0

1

2

3

還需要注意的一點是：由于字符集和比較規(guī)則是相互聯(lián)系的，如果我們只修改了字符集和比較規(guī)則，都可能引起關(guān)聯(lián)的字符集和比較規(guī)則發(fā)生變化。

最后

我這邊整理了一份：Mysql相關(guān)資料文檔以及知識圖譜、Java系統(tǒng)化資料，（包括Java核心知識點、面試專題和20年最新的互聯(lián)網(wǎng)真題、電子書等）

MySQL 任務(wù)調(diào)度

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。