MySQL架構(gòu)組成——存儲引擎

防偽碼：尊重現(xiàn)在，往事不記，后事不提。

在前一節(jié)中我們學(xué)習(xí)了 mysql 的物理文件組成，接下來我們來學(xué)習(xí) mysql 的邏輯模塊組成。

邏輯模塊組成:

MySQL 邏輯結(jié)構(gòu)可以看成是二層架構(gòu)，第一層我們通常叫做 SQL Layer，在 MySQL 數(shù)據(jù)庫系

統(tǒng)處理底層數(shù)據(jù)之前的所有工作都是在這一層完成的，包括權(quán)限判斷，sql 解析，執(zhí)行計劃

優(yōu)化，query cache 的處理等等；第二層就是存儲引擎層，我們通常叫做 StorageEngine Layer，

也就是底層數(shù)據(jù)存取操作實現(xiàn)部分，由多種存儲引擎共同組成。所以，可以用如下一張最簡

單的架構(gòu)示意圖來表示 MySQL 的基本架構(gòu)，如圖所示：

從上圖看起來 MySQL 邏輯架構(gòu)非常的簡單，但實際上每一層中都含有各自的很多小模塊，

尤其是第一層 SQL Layer，結(jié)構(gòu)相當(dāng)復(fù)雜的。

接下來我們來學(xué)習(xí) mysql 存儲引擎。

一、mysql 存儲引擎介紹：

MySQL在5.1 （不包括）之前的版本中，存儲引擎是需要在MySQL 安裝的時候就必須和MySQL

一起被編譯并同時被安裝的。

但是從 MySQL5.1 開始，MySQL AB 對其結(jié)構(gòu)體系做了較大的改造，并引入了一個新的概念：

插件式存儲引擎體系結(jié)構(gòu)。MySQL AB 在架構(gòu)改造的時候，讓存儲引擎層和 sqllayer 各自更

為獨立，耦合更小，甚至可以做到在線加載新的存儲引擎，也就是完全可以將一個新的存儲

引擎加載到一個正在運行的 MySQL 中，而不影響 MySQL 的正常運行。插件式存儲引擎的

架構(gòu)，為存儲引擎的加載和移出更為靈活方便，也使自行開發(fā)存儲引擎更為方便簡單。

MySQL 的插件式存儲引擎主要包括 MyISAM，Innodb，NDB Cluster，Maria，F(xiàn)alcon，Memory，

Archive 等，其中最著名而且使用最為廣泛的 MyISAM 和 Innodb 兩種存儲引擎。MyISAM 是

MySQL 最早的 ISAM 存儲引擎的升級版本，也是 MySQL 默認(rèn)的存儲引擎。而 Innodb 實際

上并不是 MySQ 公司的，而是第三方軟件公司 Innobase（在 2005 年被 Oracle 公司所收購）

所開發(fā)，其最大的特點是提供了事務(wù)控制等特性，所以使用者也非常廣泛。

其他的一些存儲引擎相對來說使用場景要稍微少一些，都是應(yīng)用于某些特定的場景，如 NDB

Cluster 雖然也支持事務(wù)，但是主要是用于分布式高可用集群環(huán)境。Maria 是 MySQL 最新開

發(fā)的對 MyISAM 的升級版存儲引擎，F(xiàn)alcon 是 MySQL 公司自行研發(fā)的為了替代當(dāng)前的

Innodb 存儲引擎的一款帶有事務(wù)等高級特性的數(shù)據(jù)庫存儲引擎。Memory 存儲引擎所有數(shù)

據(jù)和索引均存儲于內(nèi)存中，僅保存.frm 文件在硬盤，所以主要是用于一些臨時表，或者對性

能要求極高，但是允許在 Crash 的時候丟失數(shù)據(jù)的特定場景下，會消耗內(nèi)存較大。Archive 是

一個數(shù)據(jù)經(jīng)過高比例壓縮存放的存儲引擎，僅支持 insert，select，不支持 update 和 delete，

主要用于存放過期而且很少訪問的歷史信息，不支持索引。

MyISAM  存儲引擎簡介 ：

1、mysql5.1 之前默認(rèn)存儲引擎。

2、MyISAM 存儲引擎的表在數(shù)據(jù)庫中，每一個表都被存放為三個以表名命名的物理文件。

首先肯定會有任何存儲引擎都不可缺少的存放表結(jié)構(gòu)定義信息的.frm 文件，另外還有.MYD

和.MYI 文件，分別存放了表的數(shù)據(jù)（.MYD）和索引數(shù)據(jù)（.MYI）。每個表都有且僅有這樣三

個文件做為 MyISAM 存儲類型的表的存儲，也就是說不管這個表有多少個索引，都是存放在

同一個.MYI 文件中。

3、MyISAM 支持以下三種類型的索引：

B-Tree 索引

B-Tree 索引，就是所有的索引節(jié)點都按照 balance tree 的數(shù)據(jù)結(jié)構(gòu)來存儲，所有的索引數(shù)據(jù)

節(jié)點都在葉節(jié)點。

R-Tree 索引

R-Tree 索引的存儲方式和 b-tree 索引有一些區(qū)別，主要設(shè)計用于為存儲空間和多維數(shù)據(jù)的

字段做索引，所以目前的 MySQL 版本來說，也僅支持 geometry 類型的字段作索引。

Full-text 索引

Full-text 索引就是全文索引，他的存儲結(jié)構(gòu)也是b-tree。主要是為了解決在我們需要用like 查

詢的低效問題。

MyISAM 上面三種索引類型中，最經(jīng)常使用的就是 B-Tree 索引了，偶爾會使用到 Fulltext，

但是 R-Tree 索引一般系統(tǒng)中都是很少用到的。另外 MyISAM 的 B-Tree 索引有一個較大的限

制，那就是參與一個索引的所有字段的長度之和不能超過 1000 字節(jié)。

3、不支持事務(wù)

4、只有表鎖

5、如下情況會造成表損壞：

Mysqld 正在寫入該表時，被 kill 掉

主機宕機（Crash）

磁盤硬件故障

MyISAM 存儲引擎的 bug

6、雖然每一個 MyISAM 的表都是存放在一個相同后綴名的.MYD 文件中，但是每個文件的

存放格式實際上可能并不是完全一樣的，因為 MyISAM 的數(shù)據(jù)存放格式是分為靜態(tài)（FIXED）

固定長度、動態(tài)（DYNAMIC）可變長度以及壓縮（COMPRESSED）這三種格式。當(dāng)然三種格

式中是否壓縮是完全可以任由自己選擇的，可以在創(chuàng)建表的時候通過 ROW_FORMAT 來指定

{COMPRESSED | DEFAULT}，也可以通過 myisampack 工具來進(jìn)行壓縮，默認(rèn)是不壓縮的。而

在非壓縮的情況下，是靜態(tài)還是動態(tài)，就和我們表中個字段的定義相關(guān)了。只要表中有可變

長度類型的字段存在，那么該表就肯定是 DYNAMIC 格式的，如果沒有任何可變長度的字段，

則為 FIXED 格式，當(dāng)然，你也可以通過 alter table 命令，強行將一個帶有 VARCHAR 類型字

段的 DYNAMIC 的表轉(zhuǎn)換為 FIXED，但是所帶來的結(jié)果是原 VARCHAR 字段類型會被自動轉(zhuǎn)換

成 CHAR 類型。相反如果將 FIXED 轉(zhuǎn)換為 DYNAMIC，也會將 CHAR 類型字段轉(zhuǎn)換為 VARCHAR

類型，

知識點擴(kuò)展：如何根據(jù)表的記錄數(shù)量估算占用的磁盤空間

首先先算一個表中一行有多少個字節(jié)。

然后根據(jù)數(shù)據(jù)庫中的表每天增加多少行記錄，就能夠算出每天要增加多少硬盤空間，這樣就

可根據(jù)數(shù)據(jù)量估算規(guī)劃多大的空間。

例如在數(shù)據(jù)庫 test 中創(chuàng)建一張 tb1 表

執(zhí)行 mysql>desc test1.tb1 查看 tb1 的表結(jié)構(gòu)

10 個字節(jié)+20 個字節(jié)+2 個字節(jié)+20 字節(jié)+8 字節(jié)+8 字節(jié)+100 個字節(jié)=168 字節(jié)

Tb1 表的一個行有 118 個字節(jié)

如果每天增加 10000 條記錄，大約需要 10000x168/1024/1024=1.6MB

這樣就可以根據(jù)每天增加的記錄數(shù)，合理規(guī)劃好磁盤空間了

MyISAM 存儲引擎的某個表文件出錯之后，僅影響到該表，而不會影響到其他表，更不會影

響到其他的數(shù)據(jù)庫。如果我們的數(shù)據(jù)庫正在運行過程中發(fā)現(xiàn)某個 MyISAM 表出現(xiàn)問題了，則

可以在線通過 check table 命令來嘗試校驗他，并可以通過 repair table 命令來嘗試修復(fù)。在

數(shù)據(jù)庫關(guān)閉狀態(tài)下，我們也可以通過 myisamchk 工具來對數(shù)據(jù)庫中某個（或某些）表進(jìn)行檢

測或者修復(fù)。不過強烈建議不到萬不得已不要輕易對表進(jìn)行修復(fù)操作，修復(fù)之前盡量做好可

能的備份工作，以免帶來不必要的后果。

Innodb  存儲引擎簡介 ：

Innodb 之所以能如此受寵，主要是在于其功能方面的較多特點：

1、支持事務(wù)

Innodb 在功能方面最重要的一點就是對事務(wù)的支持，這無疑是讓 Innodb 成為 MySQL 最為流

行的存儲引擎之一的一個非常重要原因。

2、鎖定機制的改進(jìn)

Innodb 改變了 MyISAM 的鎖機制，實現(xiàn)了行鎖。

3、實現(xiàn)外鍵

Innodb 實現(xiàn)了外鍵引用這一數(shù)據(jù)庫的重要特性。

4、Innodb 存儲引擎也和 MyISAM 不太一樣，雖然也有.frm 文件來存放表結(jié)構(gòu)定義相關(guān)的元

數(shù)據(jù)，但是表數(shù)據(jù)和索引數(shù)據(jù)是存放在一起的。至于是每個表單獨存放還是所有表存放在一

起，完全由用戶來決定。

Innodb 的物理結(jié)構(gòu)分為兩大部分：

1、數(shù)據(jù)文件（表數(shù)據(jù)和索引數(shù)據(jù)）

存放數(shù)據(jù)表中的數(shù)據(jù)和所有的索引數(shù)據(jù)，包括主鍵和其他普通索引。在 Innodb 中，存在了

表空間（tablespace）這樣一個概念，但是他和 Oracle 的表空間又有較大的不同。首先，Innodb

的表空間分為兩種形式。一種是共享表空間，也就是所有表和索引數(shù)據(jù)被存放在同一個表空

間（一個或多個數(shù)據(jù)文件）中，通過 innodb_data_file_path 來指定，增加數(shù)據(jù)文件需要停機

重啟。另外一種是獨享表空間，也就是每個表的數(shù)據(jù)和索引被存放在一個單獨的.ibd 文件中。

雖然我們可以自行設(shè)定使用共享表空間還是獨享表空間來存放我們的表，但是共享表空間都

是必須存在的，因為 Innodb 的 undo 信息和其他一些元數(shù)據(jù)信息都是存放在共享表空間里

面的。共享表空間的數(shù)據(jù)文件是可以設(shè)置為固定大小和可自動擴(kuò)展大小兩種形式的。

當(dāng)我們的文件表空間快要用完的時候，我們必須要為其增加數(shù)據(jù)文件，當(dāng)然，只有共享表空

間有此操作。共享表空間增加數(shù)據(jù)文件的操作比較簡單，只需要在 innodb_data_file_path 參

數(shù)后面按照標(biāo)準(zhǔn)格式設(shè)置好文件路徑和相關(guān)屬性即可，不過這里有一點需要注意的，就是

Innodb 在創(chuàng)建新數(shù)據(jù)文件的時候是不會創(chuàng)建目錄的，如果指定目錄不存在，則會報錯并無

法啟動。

2、日志文件

Innodb 的日志文件和 Oracle 的 redo 日志比較類似，同樣可以設(shè)置多個日志組（最少 2

個），同樣采用輪循策略來順序的寫入。

由于 Innodb 是事務(wù)的存儲引擎，所以系統(tǒng) Crash（宕機）對他來說并不能造成非常嚴(yán)重的損

失，由于有 redo 日志（即事物日志）的存在，有 checkpoint 機制的保護(hù)，Innodb 完全可以

通過 redo 日志將數(shù)據(jù)庫 Crash 時刻已經(jīng)完成但還沒有來得及將數(shù)據(jù)寫入磁盤的事務(wù)恢復(fù)，

也能夠?qū)⑺胁糠滞瓿刹⒁呀?jīng)寫入磁盤的未完成事務(wù)回滾并將數(shù)據(jù)還原。

Innodb 不僅在功能特性方面和 MyISAM 存儲引擎有較大區(qū)別，在配置上面也是單獨處理的。

在 MySQL 啟動參數(shù)文件（/etc/my.cnf）設(shè)置中，Innodb 的所有參數(shù)基本上都帶有前綴

“innodb_”，不論是 innodb 數(shù)據(jù)和日志相關(guān)，還是其他一些性能，事務(wù)等等相關(guān)的參數(shù)都

是一樣。和所有 Innodb 相關(guān)的系統(tǒng)變量一樣，所有的 Innodb 相關(guān)的系統(tǒng)狀態(tài)值也同樣全部

以“Innodb_”前綴。

MyISAM 和 InnoDB 的區(qū)別

1、 MyISAM 不支持事務(wù)，而 InnoDB 支持。InnoDB 的 AUTOCOMMIT 默認(rèn)是打開的，即每條

SQL 語句會默認(rèn)被封裝成一個事務(wù)，自動提交，這樣會影響速度，所以最好是把多條 SQL

語句顯示放在 begin 和 commit 之間，組成一個事務(wù)去提交。

mysql> use test_db;

mysql> show tables;

mysql>desc tb1;

mysql> begin;

mysql> insert into tb1 values('lisi',1);

mysql> insert into tb1 values('zhangsan',2);

mysql> commit;

2、InnoDB 支持?jǐn)?shù)據(jù)行鎖定，MyISAM 不支持行鎖定，只支持鎖定整個表。即 MyISAM 同一

個表上的讀鎖和寫鎖是互斥的，MyISAM 并發(fā)讀寫時如果等待隊列中既有讀請求又有寫請求，

默認(rèn)寫請求的優(yōu)先級高，即使讀請求先到，所以 MyISAM 不適合于有大量查詢和修改并存的

情況，那樣查詢進(jìn)程會長時間阻塞。因為 MyISAM 是鎖表。

3、InnoDB 支持外鍵，MyISAM 不支持。

4、InnoDB 不支持全文索引，而 MyISAM 支持。

二、MySQL 自帶工具使用介紹

MySQL 數(shù)據(jù)庫不僅提供了數(shù)據(jù)庫的服務(wù)器端應(yīng)用程序，同時還提供了大量的客戶端工具程

序，如 mysql，mysqladmin，mysqldump 等等

1、mysql 命令

Mysql 命令是用的最多的一個命令工具了，為用戶提供一個命令行接口來操作管理 MySQL

服務(wù)器。

語法格式：

Usage: mysql [OPTIONS] [database]

例如：# mysql-e "select user,host from user"mysql

大家只要運行一下“mysql --help”就會得到如下相應(yīng)的基本使用幫助信息：

這里主要介紹一些在運維過程中會用到的相關(guān)選項：

首先看看“-e, --execute=name”參數(shù)，這個參數(shù)是告訴 mysql，我要執(zhí)行“-e”后面的某個

命令，而不是要通過 mysql 連接登錄到 MySQL Server 上面。此參數(shù)在我們寫一些基本的

MySQL 檢查和監(jiān)控的腳本中非常有用，運維 mysql 時經(jīng)常在腳本中使用到它。

#mysql -hhostname -Pport -uusername -ppassword -e 相關(guān) mysql 的 sql 語句

例 1：

通過 binlog_cache_use 以及 binlog_cache_disk_use 來分析設(shè)置的 binlog_cache_size

是否足夠

例 2：通過腳本創(chuàng)建數(shù)據(jù)庫、表及對表進(jìn)行增、改、刪、查操作。

腳本內(nèi)容如下：

創(chuàng)建授予 test 用戶可以在指定的源登錄

測試 test 用戶連接 mysql 服務(wù)器

授予腳本執(zhí)行權(quán)限

#chmod +x /root/mysql1.sh

執(zhí)行腳本：

如果在連接時候使用了“-E, --vertical”參數(shù)，登入之后的所有查詢結(jié)果都將以縱列顯示，效

果和我們在一條 query 之后以“\G”結(jié)尾一樣。

#mysql -E -u root -p

“--prompt=name”參數(shù)對于做運維的人來說是一個非常重要的參數(shù)選項，其主要功能是定

制自己的 mysql 提示符的顯示內(nèi)容。在默認(rèn)情況下，我們通過 mysql 登入到數(shù)據(jù)庫之后，mysql

的提示符只是一個很簡單的內(nèi)容”mysql>“，沒有其他任何附加信息。非常幸運的是 mysql

通過“--prompt=name”參數(shù)給我們提供了自定義提示信息的辦法，可以通過配置顯示登入

的主機地址，登錄用戶名，當(dāng)前時間，當(dāng)前數(shù)據(jù)庫 schema，MySQL Server 的一些信

息等等。我個人強烈建議將登錄主機名，登錄用戶名和所在的 schema 這三項加入提示內(nèi)容，

因為當(dāng)大家手邊管理的 MySQL 越來越多，操作越來越頻繁的時候，非常容易因為操作的時

候沒有太在意自己當(dāng)前所處的環(huán)境而造成在錯誤的環(huán)境執(zhí)行了錯誤的命令并造成嚴(yán)重后果

的情況。如果我們在提示內(nèi)容中加入了這幾項之后，至少可以更方便的提醒自己當(dāng)前所處環(huán)

境，以盡量減少犯錯誤的概率。

個人強烈建議提示符定義： "\\u@\\h : \\d \\r:\\m:\\s> "，顯示效果：

切換數(shù)據(jù)庫：

提示符解釋：

\u 表示用戶名, \h 表示主機名， \d 表示當(dāng)前數(shù)據(jù)庫，\r 小時（12 小時制），\m 分種，\s

秒，\R The current time, in 24-hour military time (0–23)

“--tee=name”參數(shù)也是對運維人員非常有用的參數(shù)選項，用來告訴 mysql，將所有輸入和

輸出內(nèi)容都記錄進(jìn)文件。在我們一些較大維護(hù)變更的時候，為了方便被查，最好是將整個操

作過程的所有輸入和輸出內(nèi)容都保存下來。

假如 mysql 命令行狀態(tài)下，要進(jìn)行大量的交互操作，其實可以把這些操作記錄在 log 中進(jìn)行

審計，很簡單 mysql -u root -p --tee=/path/xxxx.log

也可以 在服務(wù)器上的/etc/my.cnf 中的[client]加入 

tee =/tmp/client_mysql.log 即可.

注：若沒有[client]就添加即可

或者在 mysql>提示符下執(zhí)行下面的命令

mysql 其他參數(shù)選項可以通過 MySQL 官方參考手冊查閱，也可以通過執(zhí)行“mysql --help”

或 man mysql 得到幫助信息之后通過自行實驗來做進(jìn)一步的深刻認(rèn)識。

2、mysqladmin

Usage: mysqladmin [OPTIONS] command command ...

mysqadmin，顧名思義，提供的功能都是與 MySQL 管理相關(guān)的各種功能。如 MySQL Server

狀態(tài)檢查，各種統(tǒng)計信息的 flush，創(chuàng)建/刪除數(shù)據(jù)庫，關(guān)閉 MySQL Server 等等。mysqladmin

所能做的事情，雖然大部分都可以通過 mysql 連接登錄上 MySQL Server 之后來完成，但是

大部分通過 mysqladmin 來完成操作會更簡單更方便。這里將介紹一下經(jīng)常使用到的幾個常

用功能：

ping 命令可以很容易檢測 MySQL Server 是否還能正常提供服務(wù)

mysql 本機上測試：

在其他主機上測試 mysql server 是否正常提供服務(wù)

注 1：地址 192.168.1.1 是 mysql server 的 ip

注 2：mysql server 的防火墻要允許 3306/tcp 通信

注 3：在 mysql server 上創(chuàng)建授權(quán)用戶

status 命令可以獲取當(dāng)前 MySQL Server 的幾個基本的狀態(tài)值：

mysqladmin status 命令結(jié)果有下述列

Uptime:是 mysql 服務(wù)器運行的秒數(shù)。

Threads:活躍線程的數(shù)量即開啟的會話數(shù)。

Questions： 服務(wù)器啟動以來客戶的問題(查詢)數(shù)目 （只要跟 mysql 作交互，不管查詢表，

還是查詢服務(wù)器狀態(tài)都記一次）。

Slow queries：是慢查詢的數(shù)量。

Opens：mysql 已經(jīng)打開的數(shù)據(jù)庫表的數(shù)量

Flush tables: mysql 已經(jīng)執(zhí)行的 flush tables，refresh 和 reload 命令的數(shù)量。

注：flush tables //刷新表（清除緩存）

reload 重載授權(quán)表

refresh 洗掉所有表并關(guān)閉和打開日志文件

open：打開數(shù)據(jù)庫的表的數(shù)量，以服務(wù)器啟動開始。

Queries per second avg：select 語句平均查詢時間

Memory in use 分配的內(nèi)存(只有在 MySQL 用--with-debug 編譯時可用)

Max memory used 分配的最大內(nèi)存(只有在 MySQL 用--with-debug 編譯時可用)

processlist 獲取當(dāng)前數(shù)據(jù)庫的連接線程信息：

監(jiān)控 mysql 進(jìn)程運行狀態(tài)：

上面的這三個功能在一些簡單監(jiān)控腳本中經(jīng)常使用到的。

mysqladmin 其他參數(shù)選項可以通過執(zhí)行“mysqladmin --help”或 man mysqladmin 得到幫助

信息。

編寫一個簡單的 mysql 監(jiān)控腳本，內(nèi)容如下：

附加知識點 1：

Mysql 的系統(tǒng)數(shù)據(jù)庫：

1） INFORMATION_SCHEMA 數(shù)據(jù)字典：此數(shù)據(jù)庫存貯了其他所有數(shù)據(jù)庫的信息（元數(shù)據(jù)）。

元數(shù)據(jù)是關(guān)于數(shù)據(jù)的數(shù)據(jù)，如 database name 或 table name，列的數(shù)據(jù)類型，或訪問權(quán)

限等。

INFORMATION_SCHEMA 庫的主要系統(tǒng)表

TABLES  表：提供了關(guān)于數(shù)據(jù)庫中的表和視圖的信息。（Table_schema 字段代表 數(shù)據(jù)表所屬的數(shù)據(jù)

庫名）

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='數(shù)據(jù)庫名';

COLUMNS  表：提供了表中的列信息。詳細(xì)表述了某張表的所有列以及每個列的信息。

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='數(shù)據(jù)庫名

‘' AND TABLE_NAME='表名'

TABLE_CONSTRAINTS  表：存儲主鍵約束、外鍵約束、唯一約束、check 約束。各字段的說明

信息

ELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA=' 數(shù) 據(jù) 庫 名

' AND TABLE_NAME='表名'

STATISTICS  表：提供了關(guān)于表索引的信息。

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=' 數(shù) 據(jù) 庫 名

' AND TABLE_NAME='表名'

2）performance_schema 性能字典，此數(shù)據(jù)庫為數(shù)據(jù)庫性能優(yōu)化提供重要的參考信息

3）MYSQL 數(shù)據(jù)庫: 該數(shù)據(jù)庫也是個核心數(shù)據(jù)庫，存儲用戶的權(quán)限信息與幫助信息。

4）MySQL5.7 提供了 sys 系統(tǒng)數(shù)據(jù)庫。 sys 數(shù)據(jù)庫里面包含了一系列的存儲過程、自定義函

數(shù)以及視圖來幫助我們快速的了解系統(tǒng)的元數(shù)據(jù)信息。sys 系統(tǒng)數(shù)據(jù)庫結(jié)合了

information_schema 和 performance_schema 的相關(guān)數(shù)據(jù)，讓我們更加容易的檢索元數(shù)據(jù)。

附加知識點 2：

未經(jīng)允許不得轉(zhuǎn)載傳播--陳英宏

博客地址：hongge.blog.51cto.com

mysql 有關(guān) show 的用法

SHOW DATABASES 列出 MySQL Server 上的數(shù)據(jù)庫。

SHOW TABLES [FROM db_name]列出數(shù)據(jù)庫中的表。

SHOW TABLE STATUS [FROM db_name]列出數(shù)據(jù)庫的表信息，比較詳細(xì)。

SHOW COLUMNS FROM tbl_name [FROM db_name]列出表的列信息，

同 SHOW FIELDS FROM tbl_name [FROM db_name]，

DESCRIBE tbl_name [col_name]。

SHOW FULL COLUMNS FROM tbl_name [FROM db_name] 列 出 表 的 列 信 息 ， 比 較 詳 細(xì) ，

同 SHOW FULL FIELDS FROM tbl_name [FROM db_name]。

SHOW INDEX FROM tbl_name [FROM db_name]列出表的索引信息。

SHOW STATUS 列出 Server 的狀態(tài)信息。

SHOW VARIABLES 列出 MySQL 系參數(shù)值

SHOW PROCESSLIST 查看當(dāng)前 mysql 查詢進(jìn)程

SHOW GRANTS FOR user 列出用戶的授權(quán)命令

3、mysqldump：

這個工具其功能就是將 MySQL Server 中的數(shù)據(jù)以 SQL 語句的形式從數(shù)據(jù)庫中 dump 成文本

文件。mysqldump 是做為 MySQL 的一種邏輯備份工具

4、mysqlbinlog

mysqlbinlog 程序的主要功能就是分析 MySQL Server 所產(chǎn)生的二進(jìn)制日志（也就是 binlog）。

通過 mysqlbinlog，我們可以解析出 binlog 中指定時間段或者指定日志起始和結(jié)束位置的內(nèi)

容解析成 SQL 語句。

三 、Mysqlslap  性能測試 MySQL  二 種存儲引擎

mysqlslap 是 mysql 自帶的基準(zhǔn)測試工具,優(yōu)點:查詢數(shù)據(jù),語法簡單,靈活容易使用.該工具可以

模擬多個客戶端同時并發(fā)的向服務(wù)器發(fā)出查詢更新,給出了性能測試數(shù)據(jù)而且提供了多種引

擎的性能比較.msqlslap 為 mysql 性能優(yōu)化前后提供了直觀的驗證依據(jù),建議系統(tǒng)運維和 DBA

人員應(yīng)該掌握一些常見的壓力測試工具,才能準(zhǔn)確的掌握線上數(shù)據(jù)庫支撐的用戶流量上限及

其抗壓性等問題。

現(xiàn)在看一下這個壓力測試工具 mysqlslap，關(guān)于他的選項手冊上以及--help 介紹的很詳細(xì)。

這里解釋一下一些常用的選項。

--concurrency 代表并發(fā)數(shù)量，多個可以用逗號隔開。例如：concurrency=50,100,200

--engines 代表要測試的引擎，可以有多個，用分隔符隔開。

--iterations 代表要運行這些測試多少次，即運行多少次后，得到結(jié)果。

--auto-generate-sql 代表用系統(tǒng)自己生成的 SQL 腳本來測試。

--auto-generate-sql-load-type 代表要測試的是讀還是寫還是兩者混合的

（read,write,update,mixed）

--number-of-queries 代表總共要運行多少次查詢。每個客戶運行的查詢數(shù)量可以用查詢總數(shù)

/并發(fā)數(shù)來計算。比如倒數(shù)第二個結(jié)果 2=200/100。

--debug-info 代表要額外輸出 CPU 以及內(nèi)存的相關(guān)信息（注：只有在 MySQL 用--with-debug

編譯時可）。

--number-int-cols 代表測試表中的 INTEGER 類型的屬性有幾個。

--number-char-cols 代表測試表的 char 類型字段的數(shù)量。

--create-schema 代表自己定義的模式（在 MySQL 中也就是庫即創(chuàng)建測試的數(shù)據(jù)庫）。

--query 代表自己的 SQL 腳本。

--only-print 如果只想打印看看 SQL 語句是什么，可以用這個選項。

--csv=name生產(chǎn)CSV格式數(shù)據(jù)文件

查看 Mysql 數(shù)據(jù)庫默認(rèn)最大連接數(shù)

可以看到 mysql5.7.13 默認(rèn)是 151，注：不同版本默認(rèn)最大連接數(shù)不差別。一般生產(chǎn)環(huán)境是

不夠的，在 my.cnf [mysqld]下添加 max_connections=1024 增加到 1024，重啟 Mysql。

修改 my.cnf 文件并重啟mysqld 服務(wù)

查看修改后的最大連接數(shù)

查看 Mysql 默認(rèn)使用存儲引擎，如下查看：

mysql> show engines;

現(xiàn)在我們來看一下具體測試的例子

用自帶的 SQL 腳本來測試:

[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=50，100 --iterations=1

--number-int-cols=20 --number-char-cols=30 --auto-generate-sql

--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed

--engine=myisam,innodb --number-of-queries=2000 -uroot -p123456 --verbose

顯示結(jié)果：

測試說明：模擬測試兩次讀寫并發(fā)，第一次50，第二次 100，自動生成 SQL 腳本，測試表

包含 20 個 init 字段，30 個 char 字段，每次執(zhí)行 2000 查詢請求。測試引擎分別是 myisam，

innodb。

測試結(jié)果說明：

Myisam第一次50客戶端同時發(fā)起增查用0.223/s,第二次100客戶端同時發(fā)起

增查用0.234/s

Innodb第一次50客戶端同時發(fā)起增查用0.436/s,第二次100客戶端同時發(fā)起

增查用0.448/s

由此可見 MyISAM 存儲引擎處理性能是最好的，也是最常用的，但不支持事務(wù)。InonDB 存

儲引擎提供了事務(wù)型數(shù)據(jù)引擎（ACID），在事務(wù)型引擎里使用最多的。具有事務(wù)回滾，系統(tǒng)

修復(fù)等特點。

Mysqlslap 測試工具生產(chǎn) CSV 格式數(shù)據(jù)文件并轉(zhuǎn)換成圖表形式：

[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1

--number-int-cols=20 --number-char-cols=30 --auto-generate-sql

--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed

--engine=myisam,innodb --number-of-queries=2000 -uroot -p123456 --csv=/root/a.csv

將 a.csv 拷貝到 windows 主機上，打開并生成圖表

注：通過 mysqlslap 工具對 mysql server 進(jìn)行壓力測試，可以通過--concurrency、

--number-of-queries 等選項的值查看每次測試的結(jié)果，通過反復(fù)測試、優(yōu)化得出 mysql server

的最大并發(fā)數(shù)。

如果 mysqlslap 工具輸出結(jié)果為 Segmentation fault (core dumped)基本表示走超出 mysql

server 的負(fù)載。

謝謝觀看，真心的希望能幫到您！

本文出自 “一盞燭光” 博客，謝絕轉(zhuǎn)載！