MySQL 資料庫

Linux下MySQL的安裝與使用

安裝前說明

查看是否安裝過MySQL

如果你是用rpm安裝, 檢查一下RPM PACKAGE：

rpm -qa | grep -i mysql # -i 忽略大小寫

檢查mysql service：

systemctl status mysqld.service

MySQL的卸載

1. 關閉 mysql 服務

systemctl stop mysqld.service

2. 查看當前 mysql 安裝狀況

rpm -qa | grep -i mysql
# 或
yum list installed | grep mysql

3. 卸載上述命令查詢出的已安裝程式

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx

務必卸載乾凈，反覆執行rpm -qa | grep -i mysql確認是否有卸載殘留

4. 刪除 mysql 相關文件

• 查找相關文件

find / -name mysql

• 刪除上述命令查找出的相關文件

rm -rf xxx

5.刪除 my.cnf

rm -rf /etc/my.cnf

MySQL的Linux版安裝

CentOS7下檢查MySQL依賴

1. 檢查/tmp臨時目錄許可權（必不可少）

由於mysql安裝過程中，會通過mysql用戶在/tmp目錄下新建tmp_db文件，所以請給/tmp較大的許可權。執行 ：

chmod -R 777 /tmp

2. 安裝前，檢查依賴

rpm -qa|grep libaio
rpm -qa|grep net-tools

CentOS7下MySQL安裝過程

1. 將安裝程式拷貝到/opt目錄下

在mysql的安裝文件目錄下執行：（必須按照順序執行）

rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

• rpm是Redhat Package Manage縮寫，通過RPM的管理，用戶可以把源代碼包裝成以rpm為擴展名的文件形式，易於安裝。
• -i, --install 安裝軟體包
• -v, --verbose 提供更多的詳細信息輸出
• -h, --hash 軟體包安裝的時候列出哈希標記 (和 -v 一起使用效果更好)，展示進度條

若存在mariadb-libs問題，則執行yum remove mysql-libs即可

查看MySQL版本

mysql --version 
#或
mysqladmin --version

服務的初始化

為了保證資料庫目錄與文件的所有者為 mysql 登錄用戶，如果你是以 root 身份運行 mysql 服務，需要執行下麵的命令初始化：

mysqld --initialize --user=mysql

說明： --initialize 選項預設以“安全”模式來初始化，則會為 root 用戶生成一個密碼並將該密碼標記為過期，登錄後你需要設置一個新的密碼。生成的臨時密碼會往日誌中記錄一份。

查看密碼：

cat /var/log/mysqld.log

root@localhost: 後面就是初始化的密碼

啟動MySQL，查看狀態

# 加不加.service尾碼都可以 
# 啟動
systemctl start mysqld.service 

# 關閉
systemctl stop mysqld.service 

# 重啟
systemctl restart mysqld.service 

# 查看狀態
systemctl status mysqld.service

查看MySQL服務是否自啟動

systemctl list-unit-files|grep mysqld.service

• 如不是enabled可以運行如下命令設置自啟動

systemctl enable mysqld.service

• 如果希望不進行自啟動，運行如下命令設置

systemctl disable mysqld.service

MySQL登錄

首次登錄

通過mysql -hlocalhost -P3306 -uroot -p進行登錄，在Enter password：錄入初始化密碼。

修改密碼

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

設置遠程登錄

1. 確認網路

1、在遠程機器上使用ping ip地址保證網路暢通。

2、在遠程機器上使用telnet命令保證埠號開放訪問。

2、關閉防火牆或開放埠

方式一：關閉防火牆

• CentOS6 ：

service iptables stop

• CentOS7：

# 開啟防火牆
systemctl start firewalld.service

# 查看防火牆狀態
systemctl status firewalld.service

# 關閉防火牆
systemctl stop firewalld.service

# 設置開機啟用防火牆 
systemctl enable firewalld.service

# 設置開機禁用防火牆 
systemctl disable firewalld.service

方式二：開放埠

• 查看開放的埠號

firewall-cmd --list-all

• 設置開放的埠號

firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent

• 重啟防火牆

firewall-cmd --reload

Linux下修改配置

修改允許遠程登陸：

use mysql;

select Host,User from user;

update user set host = '%' where user ='root';

flush privileges;

%是個 通配符 ，如果Host=192.168.1.%，那麼就表示只要是IP地址首碼為“192.168.1.”的客戶端都可以連接。如果Host=%，表示所有IP都有連接許可權。

註意：在生產環境下不能為了省事將host設置為%，這樣做會存在安全問題，具體的設置可以根據生產環境的IP進行設置。

配置新連接報錯：錯誤號碼 2058，分析是 mysql 密碼加密方法變了。

解決方法一：升級遠程連接工具版本。

解決方法二：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

字元集的相關操作

各級別的字元集

show variables like 'character%';

• character_set_server：伺服器級別的字元集。
• character_set_database：當前資料庫的字元集。
• character_set_client：伺服器解碼請求時使用的字元集。
• character_set_connection：伺服器處理請求時會把請求字元串從character_set_client轉為character_set_connection 。
• character_set_results：伺服器向客戶端返回數據時使用的字元集。

小結：

• 如果創建或修改列時沒有顯式的指定字元集和比較規則，則該列預設用表的字元集和比較規則
• 如果創建表時沒有顯式的指定字元集和比較規則，則該表預設用資料庫的字元集和比較規則
• 如果創建資料庫時沒有顯式的指定字元集和比較規則，則該資料庫預設用伺服器的字元集和比較規則

請求到響應過程中字元集的變化

MySQL的數據目錄

MySQL8的主要目錄結構

find / -name mysql

資料庫文件的存放路徑

show variables like 'datadir'; # /var/lib/mysql/

相關命令目錄

相關命令目錄：/usr/bin 和/usr/sbin。

配置文件目錄

配置文件目錄：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）。

資料庫和文件系統的關係

表在文件系統中的表示

InnoDB存儲引擎模式

1. 表結構

為了保存表結構，InnoDB在數據目錄下對應的資料庫子目錄下創建了一個專門用於描述表結構的文件。

表名.frm

2. 表中數據和索引

① 系統表空間（system tablespace）

預設情況下，InnoDB會在數據目錄下創建一個名為ibdata1、大小為12M的自拓展文件，這個文件就是對應的系統表空間在文件系統上的表示。

② 獨立表空間(file-per-table tablespace)

在MySQL5.6.6以及之後的版本中，InnoDB並不會預設的把各個表的數據存儲到系統表空間中，而是為每一個表建立一個獨立表空間，也就是說我們創建了多少個表，就有多少個獨立表空間。使用獨立表空間來存儲表數據的話，會在該表所屬資料庫對應的子目錄下創建一個表示該獨立表空間的文件，文件名和表名相同。

表名.ibd

MySQL8.0中不再單獨提供表名.frm，而是合併在表名.ibd文件中。

③ 系統表空間與獨立表空間的設置

我們可以自己指定使用系統表空間還是獨立表空間來存儲數據，這個功能由啟動參數innodb_file_per_table控制。

[server] 
innodb_file_per_table=0 # 0：代表使用系統表空間； 1：代表使用獨立表空間

④ 其他類型的表空間

隨著MySQL的發展，除了上述兩種老牌表空間之外，現在還新提出了一些不同類型的表空間，比如通用表空間（general tablespace）、臨時表空間（temporary tablespace）等。

MyISAM存儲引擎模式

1. 表結構

在存儲表結構方面， MyISAM 和 InnoDB 一樣，也是在數據目錄下對應的資料庫子目錄下創建了一個專門用於描述表結構的文件。

表名.frm

2. 表中數據和索引

在MyISAM中的索引全部都是二級索引，該存儲引擎的數據和索引是分開存放的。所以在文件系統中也是使用不同的文件來存儲數據文件和索引文件，同時表數據都存放在對應的資料庫子目錄下。

test.frm 存儲表結構 #MySQL8.0 改為了 b.xxx.sdi
test.MYD 存儲數據 (MYData) 
test.MYI 存儲索引 (MYIndex

用戶與許可權管理

用戶管理

登錄MySQL伺服器

啟動MySQL服務後，可以通過mysql命令來登錄MySQL伺服器，命令如下：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL語句"

• -h參數後面接主機名或者主機IP，hostname為主機，hostIP為主機IP。
• -P參數後面接MySQL服務的埠，通過該參數連接到指定的埠。MySQL服務的預設埠是3306，不使用該參數時自動連接到3306埠，port為連接的埠號。
• -u參數後面接用戶名，username為用戶名。
• -p參數會提示輸入密碼。
• DatabaseName參數指明登錄到哪一個資料庫中。如果沒有該參數，就會直接登錄到MySQL資料庫中，然後可以使用USE命令來選擇資料庫。
• -e參數後面可以直接加SQL語句。登錄MySQL伺服器以後即可執行這個SQL語句，然後退出MySQL伺服器。

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

創建用戶

CREATE USER 用戶名 [IDENTIFIED BY '密碼'][,用戶名 [IDENTIFIED BY '密碼']];


# 示例
CREATE USER zhang3 IDENTIFIED BY '123123'; # 預設host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

修改用戶

UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; 
FLUSH PRIVILEGES;

刪除用戶

方式1：使用DROP方式刪除（推薦）

DROP USER user[,user]…;


# 示例
DROP USER li4 ; # 預設刪除host為%的用戶
DROP USER 'kangshifu'@'localhost';

方式2：使用DELETE方式刪除（不推薦，有殘留信息）

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;

設置當前用戶密碼

1. 使用ALTER USER命令來修改當前用戶密碼

ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用SET語句來修改當前用戶密碼

SET PASSWORD='new_password';

修改其它用戶密碼

1. 使用ALTER語句來修改普通用戶的密碼

ALTER USER user [IDENTIFIED BY '新密碼'] 
[,user[IDENTIFIED BY '新密碼']]…;

2. 使用SET命令來修改普通用戶的密碼

SET PASSWORD FOR 'username'@'hostname'='new_password';

許可權管理

許可權列表

show privileges;

• CREATE和DROP許可權，可以創建新的資料庫和表，或刪除（移掉）已有的資料庫和表。如果將MySQL資料庫中的DROP許可權授予某用戶，用戶就可以刪除MySQL訪問許可權保存的資料庫。
• SELECT、INSERT、UPDATE和DELETE許可權允許在一個資料庫現有的表上實施操作。
• SELECT許可權只有在它們真正從一個表中檢索行時才被用到。
• INDEX許可權允許創建或刪除索引，INDEX適用於已有的表。如果具有某個表的CREATE許可權，就可以在CREATE TABLE語句中包括索引定義。
• ALTER許可權可以使用ALTER TABLE來更改表的結構和重新命名錶。
• CREATE ROUTINE許可權用來創建保存的程式（函數和程式），ALTER ROUTINE許可權用來更改和刪除保存的程式，EXECUTE許可權用來執行保存的程式。
• GRANT許可權允許授權給其他用戶，可用於資料庫、表和保存的程式。
• FILE許可權使用戶可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE語句讀或寫伺服器上的文件，任何被授予FILE許可權的用戶都能讀或寫MySQL伺服器上的任何文件（說明用戶可以讀任何資料庫目錄下的文件，因為伺服器可以訪問這些文件）。

授予許可權的原則

許可權控制主要是出於安全因素，因此需要遵循以下幾個經驗原則：

1、只授予能滿足需要的最小許可權，防止用戶幹壞事。比如用戶只是需要查詢，那就只給select許可權就可以了，不要給用戶賦予update、insert或者delete許可權。

2、創建用戶的時候限制用戶的登錄主機，一般是限製成指定IP或者內網IP段。

3、為每個用戶設置滿足密碼複雜度的密碼。

4、定期清理不需要的用戶，回收許可權或者刪除用戶。

授予許可權

GRANT 許可權1,許可權2,…許可權n ON 資料庫名稱.表名稱 TO 用戶名@用戶地址 [IDENTIFIED BY ‘密碼口令’];

• 該許可權如果發現沒有該用戶，則會直接新建一個用戶。
• 給li4用戶用本地命令行方式，授予demodb這個庫下的所有表的插刪改查的許可權。

GRANT SELECT,INSERT,DELETE,UPDATE ON demodb.* TO li4@localhost;

• 授予通過網路方式登錄的joe用戶 ，對所有庫所有表的全部許可權，密碼設為123。註意這裡唯獨不包括grant的許可權

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

查看許可權

查看當前用戶許可權

SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();

查看某用戶的全局許可權

SHOW GRANTS FOR 'user'@'主機地址';

收回許可權

註意：在將用戶賬戶從user表刪除之前，應該收回相應用戶的所有許可權。

• 收回許可權命令

REVOKE 許可權1,許可權2,…許可權n ON 資料庫名稱.表名稱 FROM 用戶名@用戶地址;


# 示例

# 收回全庫全表的所有許可權 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';

# 收回mysql庫下的所有表的插刪改查許可權 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

註意：須用戶重新登錄後才能生效

角色管理

創建角色

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

角色名稱的命名規則和用戶名類似。如果host_name省略，預設為%，role_name不可省略，不可為空。

給角色賦予許可權

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述語句中privileges代表許可權的名稱，多個許可權以逗號隔開。可使用SHOW語句查詢許可權名稱

SHOW PRIVILEGES\G

查看角色的許可權

SHOW GRANTS FOR 'role_name';

只要你創建了一個角色，系統就會自動給你一個“USAGE”許可權，意思是連接登錄資料庫的許可權。

回收角色的許可權

REVOKE privileges ON tablename FROM 'rolename';

刪除角色

DROP ROLE role [,role2]...

註意，如果你刪除了角色，那麼用戶也就失去了通過這個角色所獲得的所有許可權。

給用戶賦予角色

角色創建並授權後，要賦給用戶並處於激活狀態才能發揮作用。

GRANT role [,role2,...] TO user [,user2,...];

查詢當前已激活的角色

SELECT CURRENT_ROLE();

激活角色

方式1：使用set default role 命令激活角色

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

方式2：將activate_all_roles_on_login設置為ON

SET GLOBAL activate_all_roles_on_login=ON;

這條 SQL 語句的意思是，對所有角色永久激活。

撤銷用戶的角色

REVOKE role FROM user;

設置強制角色(mandatory role)

方式1：服務啟動前設置

[mysqld] 
mandatory_roles='role1,role2@localhost,r3@%.demo.com'

方式2：運行時設置

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系統重啟後仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系統重啟後失效

邏輯架構

邏輯架構剖析

第1層：連接層

系統（客戶端）訪問MySQL伺服器前，做的第一件事就是建立TCP連接。

經過三次握手建立連接成功後，MySQL伺服器對TCP傳輸過來的賬號密碼做身份認證、許可權獲取。

• 用戶名或密碼不對，會收到一個Access denied for user錯誤，客戶端程式結束執行
• 用戶名密碼認證通過，會從許可權表查出賬號擁有的許可權與連接關聯，之後的許可權判斷邏輯，都將依賴於此時讀到的許可權

TCP連接收到請求後，必須要分配給一個線程專門與這個客戶端的交互。所以還會有個線程池，去走後面的流程。每一個連接從線程池中獲取線程，省去了創建和銷毀線程的開銷。

第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中刪除。

第3層：引擎層

插件式存儲引擎層（ Storage Engines），真正的負責了MySQL中數據的存儲和提取，對物理伺服器級別維護的底層數據執行操作，服務層通過API與存儲引擎進行通信。

小結

簡化為三層結構：

1. 連接層：客戶端和伺服器端建立連接，客戶端發送 SQL 至伺服器端；

2. SQL 層（服務層）：對 SQL 語句進行查詢處理；與資料庫文件的存儲方式無關；

3. 存儲引擎層：與資料庫文件打交道，負責數據的存儲和讀取。

SQL執行流程

MySQL 中的 SQL執行流程

MySQL的查詢流程：

1. 查詢緩存：Server 如果在查詢緩存中發現了這條 SQL 語句，就會直接將結果返回給客戶端；如果沒有，就進入到解析器階段。需要說明的是，因為查詢緩存往往效率不高，所以在 MySQL8.0 之後就拋棄了這個功能。

查詢緩存是提前把查詢結果緩存起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在MySQL 中的查詢緩存，不是緩存查詢計劃，而是查詢對應的結果。這就意味著查詢匹配的魯棒性大大降低，只有相同的查詢操作才會命中查詢緩存。兩個查詢請求在任何字元上的不同（例如：空格、註釋、大小寫），都會導致緩存不會命中。因此 MySQL 的 查詢緩存命中率不高 。

同時，如果查詢請求中包含某些系統函數、用戶自定義變數和函數、一些系統表，如 mysql、 information_schema、 performance_schema 資料庫中的表，那這個請求就不會被緩存。

此外，既然是緩存，那就有它緩存失效的時候。MySQL的緩存系統會監測涉及到的每張表，只要該表的結構或者數據被修改，如對該表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE語句，那使用該表的所有高速緩存查詢都將變為無效並從高速緩存中刪除！對於更新壓力大的資料庫來說，查詢緩存的命中率會非常低。

2. 解析器：在解析器中對 SQL 語句進行語法分析、語義分析。

分析器先做“詞法分析”。你輸入的是由多個字元串和空格組成的一條 SQL 語句，MySQL 需要識別出裡面的字元串分別是什麼，代表什麼。 MySQL 從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字元串“T”識別成“表名 T”，把字元串“ID”識別成“列 ID”。

接著，要做“語法分析”。根據詞法分析的結果，語法分析器（比如：Bison）會根據語法規則，判斷你輸入的這個 SQL 語句是否滿足 MySQL 語法。如果SQL語句正確，則會生成一個語法樹。

3. 優化器：在優化器中會確定 SQL 語句的執行路徑，比如是根據全表檢索，還是根據索引檢索等。在查詢優化器中，可以分為邏輯查詢優化階段和物理查詢優化階段。

4. 執行器：在執行之前需要判斷該用戶是否具備許可權。如果沒有，就會返回許可權錯誤。如果具備許可權，就執行 SQL查詢並返回結果。在 MySQL8.0 以下的版本，如果設置了查詢緩存，這時會將查詢結果進行緩存。

SQL 語句在 MySQL 中的流程是： SQL語句→查詢緩存→解析器→優化器→執行器 。

資料庫緩衝池(buffer pool)

InnoDB存儲引擎是以頁為單位來管理存儲空間的，我們進行的增刪改查操作其實本質上都是在訪問頁面（包括讀頁面、寫頁面、創建新頁面等操作）。而磁碟 I/O 需要消耗的時間很多，而在記憶體中進行操作，效率則會高很多，為了能讓數據表或者索引中的數據隨時被我們所用，DBMS 會申請占用記憶體來作為數據緩衝池，在真正訪問頁面之前，需要把在磁碟上的頁緩存到記憶體中的Buffer Pool之後才可以訪問。

這樣做的好處是可以讓磁碟活動最小化，從而減少與磁碟直接進行 I/O 的時間。要知道，這種策略對提升 SQL 語句的查詢性能來說至關重要。如果索引的數據在緩衝池裡，那麼訪問的成本就會降低很多。

緩衝池 vs 查詢緩存

1、緩衝池（Buffer Pool）

從圖中，你能看到 InnoDB 緩衝池包括了數據頁、索引頁、插入緩衝、鎖信息、自適應 Hash 和數據字典信息等。

緩存原則：

“位置 * 頻次”這個原則，可以幫我們對 I/O 訪問效率進行優化。

首先，位置決定效率，提供緩衝池就是為了在記憶體中可以直接訪問數據。

其次，頻次決定優先順序順序。因為緩衝池的大小是有限的，比如磁碟有 200G，但是記憶體只有 16G，緩衝池大小隻有 1G，就無法將所有數據都載入到緩衝池裡，這時就涉及到優先順序順序，會優先對使用頻次高的熱數據進行載入。

2、查詢緩存

查詢緩存是提前把查詢結果緩存起來，這樣下次不需要執行就可以直接拿到結果。需要說明的是，在MySQL 中的查詢緩存，不是緩存查詢計劃，而是查詢對應的結果。因為命中條件苛刻，而且只要數據表發生變化，查詢緩存就會失效，因此命中率低。

緩衝池如何讀取數據

緩衝池管理器會儘量將經常使用的數據保存起來，在資料庫進行頁面讀操作的時候，首先會判斷該頁面是否在緩衝池中，如果存在就直接讀取，如果不存在，就會通過記憶體或磁碟將頁面存放到緩衝池中再進行讀取。

查看/設置緩衝池的大小

查看緩衝池的大小。

show variables like 'innodb_buffer_pool_size';

設置緩衝池的大小。

set global innodb_buffer_pool_size = 268435456;

或者

[server] 
innodb_buffer_pool_size = 268435456

多個Buffer Pool實例

[server] 
innodb_buffer_pool_instances = 2

何查看緩衝池的個數。

show variables like 'innodb_buffer_pool_instances';

每個Buffer Pool實例實際占記憶體空間。

innodb_buffer_pool_size/innodb_buffer_pool_instances

存儲引擎

查看存儲引擎

show engines;
# 或
show engines\G

設置系統預設的存儲引擎

• 查看預設的存儲引擎：

show variables like '%storage_engine%'; 
#或
SELECT @@default_storage_engine;

• 修改預設的存儲引擎

如果在創建表的語句中沒有顯式指定表的存儲引擎的話，那就會預設使用InnoDB作為表的存儲引擎。

SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改my.cnf文件：

default-storage-engine=MyISAM 

# 重啟服務 
systemctl restart mysqld.service

設置表的存儲引擎

存儲引擎是負責對錶中的數據進行提取和寫入工作的，我們可以為不同的表設置不同的存儲引擎，也就是說不同的表可以有不同的物理存儲結構，不同的提取和寫入方式。

創建表時指定存儲引擎

CREATE TABLE 表名(
    建表語句; 
) ENGINE = 存儲引擎名稱;

修改表的存儲引擎

ALTER TABLE 表名 ENGINE = 存儲引擎名稱;

引擎介紹

InnoDB 引擎：具備外鍵支持功能的事務存儲引擎

MySQL從3.23.34a開始就包含InnoDB存儲引擎。大於等於5.5之後，預設採用InnoDB引擎。

InnoDB是MySQL的預設事務型引擎，它被設計用來處理大量的短期(short-lived)事務。可以確保事務的完整提交(Commit)和回滾(Rollback)。

除了增加和查詢外，還需要更新、刪除操作，那麼，應優先選擇InnoDB存儲引擎。

除非有非常特別的原因需要使用其他的存儲引擎，否則應該優先考慮InnoDB引擎。

數據文件結構：

• 表名.frm 存儲表結構（MySQL8.0時，合併在表名.ibd中）。
• 表名.ibd 存儲數據和索引。

InnoDB是為處理巨大數據量的最大性能設計。

• 在以前的版本中，字典數據以元數據文件、非事務表等來存儲。現在這些元數據文件被刪除了。比如：.frm，.par，.trn，.isl，.db.opt等都在MySQL8.0中不存在了。

• 對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，並且會占用更多的磁碟空間以保存數據和索引。

• MyISAM只緩存索引，不緩存真實數據；InnoDB不僅緩存索引還要緩存真實數據，對記憶體要求較高，而且記憶體大小對性能有決定性的影響。

MyISAM 引擎：主要的非事務處理存儲引擎

5.5之前預設的存儲引擎。

MyISAM提供了大量的特性，包括全文索引、壓縮、空間函數(GIS)等，但MyISAM不支持事務、行級鎖、外鍵，有一個毫無疑問的缺陷就是崩潰後無法安全恢復。

優勢是訪問的速度快，對事務完整性沒有要求或者以SELECT、INSERT為主的應用。

針對數據統計有額外的常數存儲。故而 count(*) 的查詢效率很高。

數據文件結構：

• 表名.frm 存儲表結構

• 表名.MYD 存儲數據 (MYData)

• 表名.MYI 存儲索引 (MYIndex)

• 應用場景：只讀應用或者以讀為主的業務

Archive 引擎：用於數據存檔

下表展示了 ARCHIVE 存儲引擎功能：

Blackhole 引擎：丟棄寫操作，讀操作會返回空內容

CSV 引擎：存儲數據時，以逗號分隔各個數據項

mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV; 
Query OK, 0 rows affected (0.06 sec) 

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); 
Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 

mysql> SELECT * FROM test; 
+---+------------+
| i | c | 
+---+------------+
| 1 | record one | 
| 2 | record two | 
+---+------------+
2 rows in set (0.00 sec)

創建CSV表還會創建相應的 元文件 ，用於 存儲表的狀態 和 表中存在的行數 。此文件的名稱與表的名稱相同，尾碼為 CSM 。如圖所示

如果檢查 test.CSV 通過執行上述語句創建的資料庫目錄中的文件，其內容使用Notepad++打開如下：

"1","record one" 
"2","record two"

這種格式可以被 Microsoft Excel 等電子錶格應用程式讀取，甚至寫入。

Memory 引擎：置於記憶體的表

Memory採用的邏輯介質是 記憶體 ， 響應速度很快 ，但是當mysqld守護進程崩潰的時候 數據會丟失 。另外，要求存儲的數據是數據長度不變的格式，比如，Blob和Text類型的數據不可用(長度不固定的)。

主要特征：

• Memory同時 支持哈希（HASH）索引 和 B+樹索引 。
• Memory表至少比MyISAM表要 快一個數量級 。
• MEMORY 表的大小是受到限制 的。表的大小主要取決於兩個參數，分別是 max_rows 和 max_heap_table_size 。其中，max_rows可以在創建表時指定；max_heap_table_size的大小預設為16MB，可以按需要進行擴大。
• 數據文件與索引文件分開存儲。

缺點：其數據易丟失，生命周期短。基於這個缺陷，選擇MEMORY存儲引擎時需要特別小心。

使用 Memory 存儲引擎的場景：

1. 目標數據比較小 ，而且非常 頻繁的進行訪問 ，在記憶體中存放數據，如果太大的數據會造成 記憶體溢 出 。可以通過參數 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大小。
2. 如果數據是臨時的 ，而且 必須立即可用 得到，那麼就可以放在記憶體中。
3. 存儲在Memory表中的數據如果突然間 丟失的話也沒有太大的關係 。

Federated 引擎：訪問遠程表

Federated引擎是訪問其他MySQL伺服器的一個 代理 ，儘管該引擎看起來提供了一種很好的 跨伺服器 的靈活性 ，但也經常帶來問題，因此 預設是禁用的 。

Merge引擎：管理多個MyISAM表構成的表集合

NDB引擎：MySQL集群專用存儲引擎

也叫做 NDB Cluster 存儲引擎，主要用於 MySQL Cluster 分散式集群 環境，類似於 Oracle 的 RAC 集群。

MyISAM 和 InnoDB

索引的數據結構

索引及其優缺點

1. 索引概述

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。

索引的本質：索引是數據結構。你可以簡單理解為“排好序的快速查找數據結構”，滿足特定查找演算法。這些數據結構以某種方式指向數據， 這樣就可以在這些數據結構的基礎上實現高級查找演算法。

2. 優點

（1）類似大學圖書館建書目索引，提高數據檢索的效率，降低資料庫的IO成本，這也是創建索引最主要的原因。

（2）通過創建唯一索引，可以保證資料庫表中每一行數據的唯一性。

（3）在實現數據的參考完整性方面，可以加速表和表之間的連接。換句話說，對於有依賴關係的子表和父表聯合查詢時，可以提高查詢速度。

（4）在使用分組和排序子句進行數據查詢時，可以顯著減少查詢中分組和排序的時間，降低了CPU的消耗。

3. 缺點

（1）創建索引和維護索引要耗費時間，並且隨著數據量的增加，所耗費的時間也會增加。

（2）索引需要占磁碟空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間存儲在磁碟上，如果有大量的索引，索引文件就可能比數據文件更快達到最大文件尺寸。

（3）雖然索引大大提高了查詢速度，同時卻會降低更新表的速度。當對錶中的數據進行增加、刪除和修改的時候，索引也要動態地維護，這樣就降低了數據的維護速度。

InnoDB中索引的推演

索引之前的查找

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

1. 在一個頁中的查找

假設目前表中的記錄比較少，所有的記錄都可以被存放在一個頁中，在查找記錄的時候可以根據搜索條件的不同分為兩種情況：

• 以主鍵為搜索條件：可以在頁目錄中使用二分法快速定位到對應的槽，然後再遍歷該槽對應分組中的記錄即可快速找到指定的記錄。
• 以其他列作為搜索條件：因為在數據頁中並沒有對非主鍵建立所謂的頁目錄，所以我們無法通過二分法快速定位相應的槽。這種情況下只能從最小記錄開始依次遍歷單鏈表中的每條記錄，然後對比每條記錄是不是符合搜索條件。很顯然，這種查找的效率是非常低的。

2. 在很多頁中查找

大部分情況下我們表中存放的記錄都是非常多的，需要好多的數據頁來存儲這些記錄。在很多頁中查找記錄的話可以分為兩個步驟：

1）、定位到記錄所在的頁。

2）、從所在的頁內查找相應的記錄。

在沒有索引的情況下，不論是根據主鍵列或者其他列的值進行查找，由於我們並不能快速的定位到記錄所在的頁，所以只能從第一個頁沿著雙向鏈表一直往下找，在每一個頁中根據我們上面的查找方式去查找指定的記錄。因為要遍歷所有的數據頁，所以這種方式顯然是超級耗時的。

設計索引

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;

這個新建的index_demo表中有2個INT類型的列，1個CHAR(1)類型的列，而且我們規定了c1列為主鍵，這個表使用Compact行格式來實際存儲記錄的。這裡我們簡化了index_demo表的行格式示意圖：

• record_type：記錄頭信息的一項屬性，表示記錄的類型，0表示普通記錄、1表示目錄項記錄、2表示最小記錄、3表示最大記錄。
• next_record：記錄頭信息的一項屬性，表示下一條地址相對於本條記錄的地址偏移量，我們用箭頭來表明下一條記錄是誰。
• 各個列的值：這裡只記錄在index_demo表中的三個列，分別是c1、c2和c3。
• 其他信息：除了上述3種信息以外的所有信息，包括其他隱藏列的值以及記錄的額外信息。

將記錄格式示意圖的其他信息項暫時去掉並把它豎起來的效果就是這樣：

把一些記錄放到頁里的示意圖就是：

一個簡單的索引設計方案

我們在根據某個搜索條件查找一些記錄時為什麼要遍歷所有的數據頁呢？因為各個頁中的記錄並沒有規律，我們並不知道我們的搜索條件匹配哪些頁中的記錄，所以不得不依次遍歷所有的數據頁。所以如果我們想快速的定位到需要查找的記錄在哪些數據頁中該咋辦？我們可以為快速定位記錄所在的數據頁而建立一個目錄，建這個目錄必須完成下邊這些事：

• 下一個數據頁中用戶記錄的主鍵值必須大於上一個頁中用戶記錄的主鍵值。
• 給所有的頁建立一個目錄項。

以頁28為例，它對應目錄項2，這個目錄項中包含著該頁的頁號28以及該頁中用戶記錄的最小主鍵值5。我們只需要把幾個目錄項在物理存儲器上連續存儲（比如：數組），就可以實現根據主鍵值快速查找某條記錄的功能了。比如：查找主鍵值為20的記錄，具體查找過程分兩步：

1）、先從目錄項中根據二分法快速確定出主鍵值為20的記錄在目錄項3中（因為 12 < 20 < 209 ），它對應的頁是頁9。

2）、再根據前邊說的在頁中查找記錄的方式去頁9中定位具體的記錄。

至此，針對數據頁做的簡易目錄就搞定了。這個目錄有一個別名，稱為索引。

迭代1次：目錄項紀錄的頁

我們把前邊使用到的目錄項放到數據頁中的樣子就是這樣：

從圖中可以看出來，我們新分配了一個編號為30的頁來專門存儲目錄項記錄。這裡再次強調目錄項記錄和普通的用戶記錄的不同點：

• 目錄項記錄的record_type值是1，而普通用戶記錄的record_type值是0。
• 目錄項記錄只有主鍵值和頁的編號兩個列，而普通的用戶記錄的列是用戶自己定義的，可能包含很多列，另外還有InnoDB自己添加的隱藏列。
• 瞭解：記錄頭信息里還有一個叫min_rec_mask的屬性，只有在存儲目錄項記錄的頁中的主鍵值最小的目錄項記錄的min_rec_mask值為1，其他別的記錄的min_rec_mask值都是0。

相同點：兩者用的是一樣的數據頁，都會為主鍵值生成Page Directory（頁目錄），從而在按照主鍵值進行查找時可以使用二分法來加快查詢速度。

現在以查找主鍵為20的記錄為例，根據某個主鍵值去查找記錄的步驟就可以大致拆分成下邊兩步：

1）、先到存儲目錄項記錄的頁，也就是頁30中通過二分法快速定位到對應目錄項，因為 12 < 20 < 209 ，所以定位到對應的記錄所在的頁就是頁9。

2）、再到存儲用戶記錄的頁9中根據二分法快速定位到主鍵值為20的用戶記錄。

迭代2次：多個目錄項紀錄的頁

從圖中可以看出，我們插入了一條主鍵值為320的用戶記錄之後需要兩個新的數據頁：

• 為存儲該用戶記錄而新生成了頁31。
• 因為原先存儲目錄項記錄的頁30的容量已滿（我們前邊假設只能存儲4條目錄項記錄），所以不得不需要一個新的頁32來存放頁31對應的目錄項。

現在因為存儲目錄項記錄的頁不止一個，所以如果我們想根據主鍵值查找一條用戶記錄大致需要3個步驟，以查找主鍵值為20的記錄為例：

1）、確定目錄項記錄頁我們現在的存儲目錄項記錄的頁有兩個，即頁30和頁32，又因為頁30表示的目錄項的主鍵值的範圍是 [1, 320) ，頁32表示的目錄項的主鍵值不小於 320 ，所以主鍵值為20的記錄對應的目錄項記錄在頁30中。

2）、通過目錄項記錄頁確定用戶記錄真實所在的頁。在一個存儲目錄項記錄的頁中通過主鍵值定位一條目錄項記錄的方式說過了。

3）、在真實存儲用戶記錄的頁中定位到具體的記錄。

迭代3次：目錄項記錄頁的目錄頁

如圖，我們生成了一個存儲更高級目錄項的頁33，這個頁中的兩條記錄分別代表頁30和頁32，如果用戶記錄的主鍵值在[1, 320)之間，則到頁30中查找更詳細的目錄項記錄，如果主鍵值不小於320的話，就到頁32中查找更詳細的目錄項記錄。

我們可以用下邊這個圖來描述它：

這個數據結構，它的名稱是B+樹。

④ B+Tree

一個B+樹的節點其實可以分成好多層，規定最下邊的那層，也就是存放我們用戶記錄的那層為第0層，之後依次往上加。之前我們做了一個非常極端的假設：存放用戶記錄的頁最多存放3條記錄，存放目錄項記錄的頁最多存放4條記錄。其實真實環境中一個頁存放的記錄數量是非常大的，假設所有存放用戶記錄的葉子節點代表的數據頁可以存放100條用戶記錄，所有存放目錄項記錄的內節點代表的數據頁可以存放1000條目錄項記錄，那麼：

• 如果B+樹只有1層，也就是只有1個用於存放用戶記錄的節點，最多能存放100條記錄。
• 如果B+樹有2層，最多能存放1000×100=10,0000條記錄。
• 如果B+樹有3層，最多能存放1000×1000×100=1,0000,0000條記錄。
• 如果B+樹有4層，最多能存放1000×1000×1000×100=1000,0000,0000條記錄。相當多的記錄！！！

你的表裡能存放100000000000條記錄嗎？所以一般情況下，我們用到的B+樹都不會超過4層，那我們通過主鍵值去查找某條記錄最多只需要做4個頁面內的查找（查找3個目錄項頁和一個用戶記錄頁），又因為在每個頁面內有所謂的Page Directory（頁目錄），所以在頁面內也可以通過二分法實現快速定位記錄。

常見索引概念

聚簇索引

特點：

1. 使用記錄主鍵值的大小進行記錄和頁的排序，這包括三個方面的含義：

   • 頁內的記錄是按照主鍵的大小順序排成一個單向鏈表。

   • 各個存放用戶記錄的頁也是根據頁中用戶記錄的主鍵大小順序排成一個雙向鏈表。

   • 存放目錄項記錄的頁分為不同的層次，在同一層次中的頁也是根據頁中目錄項記錄的主鍵大小順序排成一個雙向鏈表。

2. B+樹的葉子節點存儲的是完整的用戶記錄。

所謂完整的用戶記錄，就是指這個記錄中存儲了所有列的值（包括隱藏列）。

優點：

• 數據訪問更快，因為聚簇索引將索引和數據保存在同一個B+樹中，因此從聚簇索引中獲取數據比非聚簇索引更快
• 聚簇索引對於主鍵的排序查找和範圍查找速度非常快
• 按照聚簇索引排列順序，查詢顯示一定範圍數據的時候，由於數據都是緊密相連，資料庫不用從多個數據塊中提取數據，所以節省了大量的io操作。

缺點：

• 插入速度嚴重依賴於插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響性能。因此，對於InnoDB表，我們一般都會定義一個自增ID列為主鍵
• 更新主鍵的代價很高，因為將會導致被更新的行移動。因此，對於InnoDB表，我們一般定義主鍵為不可更新
• 二級索引訪問需要兩次索引查找，第一次找到主鍵值，第二次根據主鍵值找到行數據

二級索引（輔助索引、非聚簇索引）

概念：回表 我們根據這個以c2列大小排序的B+樹只能確定我們要查找記錄的主鍵值，所以如果我們想根據c2列的值查找到完整的用戶記錄的話，仍然需要到聚簇索引中再查一遍，這個過程稱為回表。也就是根據c2列的值查詢一條完整的用戶記錄需要使用到2棵B+樹！

聯合索引

我們也可以同時以多個列的大小作為排序規則，也就是同時為多個列建立索引，比方說我們想讓B+樹按照c2和c3列的大小進行排序，這個包含兩層含義：

• 先把各個記錄和頁按照c2列進行排序。

• 在記錄的c2列相同的情況下，採用c3列進行排序

註意一點，以c2和c3列的大小為排序規則建立的B+樹稱為聯合索引，本質上也是一個二級索引。它的意思與分別為c2和c3列分別建立索引的表述是不同的，不同點如下：

• 建立聯合索引只會建立如上圖一樣的1棵B+樹。

• 為c2和c3列分別建立索引會分別以c2和c3列的大小為排序規則建立2棵B+樹。

InnoDB的B+樹索引的註意事項

根頁面位置萬年不動

我們前邊介紹B+索引的時候，為了大家理解上的方便，先把存儲用戶記錄的葉子節點都畫出來，然後接著畫存儲目錄項記錄的內節點，實際上B+樹的形成過程是這樣的：

• 每當為某個表創建一個B+樹索引（聚簇索引不是人為創建的，預設就有）的時候，都會為這個索引創建一個根節點頁面。最開始表中沒有數據的時候，每個B+樹索引對應的根節點中既沒有用戶記錄，也沒有目錄項記錄。
• 隨後向表中插入用戶記錄時，先把用戶記錄存儲到這個根節點中。
• 當根節點中的可用空間用完時繼續插入記錄，此時會將根節點中的所有記錄複製到一個新分配的頁，比如頁a中，然後對這個新頁進行頁分裂的操作，得到另一個新頁，比如頁b。這時新插入的記錄根據鍵值（也就是聚簇索引中的主鍵值，二級索引中對應的索引列的值）的大小就會被分配到頁a或者頁b中，而根節點便升級為存儲目錄項記錄的頁。

這個過程特別註意的是：一個B+樹索引的根節點自誕生之日起，便不會再移動。這樣只要我們對某個表建立一個索引，那麼它的根節點的頁號便會被記錄到某個地方，然後凡是InnoDB存儲引擎需要用到這個索引的時候，都會從那個固定的地方取出根節點的頁號，從而來訪問這個索引。

內節點中目錄項記錄的唯一性

我們知道B+樹索引的內節點中目錄項記錄的內容是索引列+頁號的搭配，但是這個搭配對於二級索引來說有點不嚴謹。還拿index_demo表為例，假設這個表中的數據是這樣的：

您的分享是我們最大的動力!

-Advertisement-