MySQL架構(gòu)組成——存儲(chǔ)引擎
防偽碼：尊重現(xiàn)在，往事不記，后事不提。
在前一節(jié)中我們學(xué)習(xí)了 mysql 的物理文件組成，接下來(lái)我們來(lái)學(xué)習(xí) mysql 的邏輯模塊組成。
邏輯模塊組成:
MySQL 邏輯結(jié)構(gòu)可以看成是二層架構(gòu)，第一層我們通常叫做 SQL Layer，在 MySQL 數(shù)據(jù)庫(kù)系
統(tǒng)處理底層數(shù)據(jù)之前的所有工作都是在這一層完成的，包括權(quán)限判斷，sql 解析，執(zhí)行計(jì)劃
優(yōu)化，query cache 的處理等等；第二層就是存儲(chǔ)引擎層，我們通常叫做 StorageEngine Layer，
也就是底層數(shù)據(jù)存取操作實(shí)現(xiàn)部分，由多種存儲(chǔ)引擎共同組成。所以，可以用如下一張最簡(jiǎn)
單的架構(gòu)示意圖來(lái)表示 MySQL 的基本架構(gòu)，如圖所示：
從上圖看起來(lái) MySQL 邏輯架構(gòu)非常的簡(jiǎn)單，但實(shí)際上每一層中都含有各自的很多小模塊，
尤其是第一層 SQL Layer，結(jié)構(gòu)相當(dāng)復(fù)雜的。
接下來(lái)我們來(lái)學(xué)習(xí) mysql 存儲(chǔ)引擎。
一、mysql 存儲(chǔ)引擎介紹：
MySQL在5.1 （不包括）之前的版本中，存儲(chǔ)引擎是需要在MySQL 安裝的時(shí)候就必須和MySQL
一起被編譯并同時(shí)被安裝的。
但是從 MySQL5.1 開(kāi)始，MySQL AB 對(duì)其結(jié)構(gòu)體系做了較大的改造，并引入了一個(gè)新的概念：
插件式存儲(chǔ)引擎體系結(jié)構(gòu)。MySQL AB 在架構(gòu)改造的時(shí)候，讓存儲(chǔ)引擎層和 sqllayer 各自更
為獨(dú)立，耦合更小，甚至可以做到在線(xiàn)加載新的存儲(chǔ)引擎，也就是完全可以將一個(gè)新的存儲(chǔ)
引擎加載到一個(gè)正在運(yùn)行的 MySQL 中，而不影響 MySQL 的正常運(yùn)行。插件式存儲(chǔ)引擎的
架構(gòu)，為存儲(chǔ)引擎的加載和移出更為靈活方便，也使自行開(kāi)發(fā)存儲(chǔ)引擎更為方便簡(jiǎn)單。
MySQL 的插件式存儲(chǔ)引擎主要包括 MyISAM，Innodb，NDB Cluster，Maria，F(xiàn)alcon，Memory，
Archive 等，其中最著名而且使用最為廣泛的 MyISAM 和 Innodb 兩種存儲(chǔ)引擎。MyISAM 是
MySQL 最早的 ISAM 存儲(chǔ)引擎的升級(jí)版本，也是 MySQL 默認(rèn)的存儲(chǔ)引擎。而 Innodb 實(shí)際
上并不是 MySQ 公司的，而是第三方軟件公司 Innobase（在 2005 年被 Oracle 公司所收購(gòu)）
所開(kāi)發(fā)，其最大的特點(diǎn)是提供了事務(wù)控制等特性，所以使用者也非常廣泛。
其他的一些存儲(chǔ)引擎相對(duì)來(lái)說(shuō)使用場(chǎng)景要稍微少一些，都是應(yīng)用于某些特定的場(chǎng)景，如 NDB
Cluster 雖然也支持事務(wù)，但是主要是用于分布式高可用集群環(huán)境。Maria 是 MySQL 最新開(kāi)
發(fā)的對(duì) MyISAM 的升級(jí)版存儲(chǔ)引擎，F(xiàn)alcon 是 MySQL 公司自行研發(fā)的為了替代當(dāng)前的
Innodb 存儲(chǔ)引擎的一款帶有事務(wù)等高級(jí)特性的數(shù)據(jù)庫(kù)存儲(chǔ)引擎。Memory 存儲(chǔ)引擎所有數(shù)
據(jù)和索引均存儲(chǔ)于內(nèi)存中，僅保存.frm 文件在硬盤(pán)，所以主要是用于一些臨時(shí)表，或者對(duì)性
能要求極高，但是允許在 Crash 的時(shí)候丟失數(shù)據(jù)的特定場(chǎng)景下，會(huì)消耗內(nèi)存較大。Archive 是
一個(gè)數(shù)據(jù)經(jīng)過(guò)高比例壓縮存放的存儲(chǔ)引擎，僅支持 insert，select，不支持 update 和 delete，
主要用于存放過(guò)期而且很少訪(fǎng)問(wèn)的歷史信息，不支持索引。
MyISAM 存儲(chǔ)引擎簡(jiǎn)介 ：
1、mysql5.1 之前默認(rèn)存儲(chǔ)引擎。
2、MyISAM 存儲(chǔ)引擎的表在數(shù)據(jù)庫(kù)中，每一個(gè)表都被存放為三個(gè)以表名命名的物理文件。
首先肯定會(huì)有任何存儲(chǔ)引擎都不可缺少的存放表結(jié)構(gòu)定義信息的.frm 文件，另外還有.MYD
和.MYI 文件，分別存放了表的數(shù)據(jù)（.MYD）和索引數(shù)據(jù)（.MYI）。每個(gè)表都有且僅有這樣三
個(gè)文件做為 MyISAM 存儲(chǔ)類(lèi)型的表的存儲(chǔ)，也就是說(shuō)不管這個(gè)表有多少個(gè)索引，都是存放在
同一個(gè).MYI 文件中。
3、MyISAM 支持以下三種類(lèi)型的索引：
B-Tree 索引
B-Tree 索引，就是所有的索引節(jié)點(diǎn)都按照 balance tree 的數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)，所有的索引數(shù)據(jù)
節(jié)點(diǎn)都在葉節(jié)點(diǎn)。
R-Tree 索引
R-Tree 索引的存儲(chǔ)方式和 b-tree 索引有一些區(qū)別，主要設(shè)計(jì)用于為存儲(chǔ)空間和多維數(shù)據(jù)的
字段做索引，所以目前的 MySQL 版本來(lái)說(shuō)，也僅支持 geometry 類(lèi)型的字段作索引。
Full-text 索引
Full-text 索引就是全文索引，他的存儲(chǔ)結(jié)構(gòu)也是b-tree。主要是為了解決在我們需要用like 查
詢(xún)的低效問(wèn)題。
MyISAM 上面三種索引類(lèi)型中，最經(jīng)常使用的就是 B-Tree 索引了，偶爾會(huì)使用到 Fulltext，
但是 R-Tree 索引一般系統(tǒng)中都是很少用到的。另外 MyISAM 的 B-Tree 索引有一個(gè)較大的限
制，那就是參與一個(gè)索引的所有字段的長(zhǎng)度之和不能超過(guò) 1000 字節(jié)。
3、不支持事務(wù)
4、只有表鎖
5、如下情況會(huì)造成表?yè)p壞：
Mysqld 正在寫(xiě)入該表時(shí)，被 kill 掉
主機(jī)宕機(jī)（Crash）
磁盤(pán)硬件故障
MyISAM 存儲(chǔ)引擎的 bug
6、雖然每一個(gè) MyISAM 的表都是存放在一個(gè)相同后綴名的.MYD 文件中，但是每個(gè)文件的
存放格式實(shí)際上可能并不是完全一樣的，因?yàn)?MyISAM 的數(shù)據(jù)存放格式是分為靜態(tài)（FIXED）
固定長(zhǎng)度、動(dòng)態(tài)（DYNAMIC）可變長(zhǎng)度以及壓縮（COMPRESSED）這三種格式。當(dāng)然三種格
式中是否壓縮是完全可以任由自己選擇的，可以在創(chuàng)建表的時(shí)候通過(guò) ROW_FORMAT 來(lái)指定
{COMPRESSED | DEFAULT}，也可以通過(guò) myisampack 工具來(lái)進(jìn)行壓縮，默認(rèn)是不壓縮的。而
在非壓縮的情況下，是靜態(tài)還是動(dòng)態(tài)，就和我們表中個(gè)字段的定義相關(guān)了。只要表中有可變
長(zhǎng)度類(lèi)型的字段存在，那么該表就肯定是 DYNAMIC 格式的，如果沒(méi)有任何可變長(zhǎng)度的字段，
則為 FIXED 格式，當(dāng)然，你也可以通過(guò) alter table 命令，強(qiáng)行將一個(gè)帶有 VARCHAR 類(lèi)型字
段的 DYNAMIC 的表轉(zhuǎn)換為 FIXED，但是所帶來(lái)的結(jié)果是原 VARCHAR 字段類(lèi)型會(huì)被自動(dòng)轉(zhuǎn)換
成 CHAR 類(lèi)型。相反如果將 FIXED 轉(zhuǎn)換為 DYNAMIC，也會(huì)將 CHAR 類(lèi)型字段轉(zhuǎn)換為 VARCHAR
類(lèi)型，
知識(shí)點(diǎn)擴(kuò)展：如何根據(jù)表的記錄數(shù)量估算占用的磁盤(pán)空間
首先先算一個(gè)表中一行有多少個(gè)字節(jié)。
然后根據(jù)數(shù)據(jù)庫(kù)中的表每天增加多少行記錄，就能夠算出每天要增加多少硬盤(pán)空間，這樣就
可根據(jù)數(shù)據(jù)量估算規(guī)劃多大的空間。
例如在數(shù)據(jù)庫(kù) test 中創(chuàng)建一張 tb1 表
執(zhí)行 mysql>desc test1.tb1 查看 tb1 的表結(jié)構(gòu)
10 個(gè)字節(jié)+20 個(gè)字節(jié)+2 個(gè)字節(jié)+20 字節(jié)+8 字節(jié)+8 字節(jié)+100 個(gè)字節(jié)=168 字節(jié)
Tb1 表的一個(gè)行有 118 個(gè)字節(jié)
如果每天增加 10000 條記錄，大約需要 10000x168/1024/1024=1.6MB
這樣就可以根據(jù)每天增加的記錄數(shù)，合理規(guī)劃好磁盤(pán)空間了
MyISAM 存儲(chǔ)引擎的某個(gè)表文件出錯(cuò)之后，僅影響到該表，而不會(huì)影響到其他表，更不會(huì)影
響到其他的數(shù)據(jù)庫(kù)。如果我們的數(shù)據(jù)庫(kù)正在運(yùn)行過(guò)程中發(fā)現(xiàn)某個(gè) MyISAM 表出現(xiàn)問(wèn)題了，則
可以在線(xiàn)通過(guò) check table 命令來(lái)嘗試校驗(yàn)他，并可以通過(guò) repair table 命令來(lái)嘗試修復(fù)。在
數(shù)據(jù)庫(kù)關(guān)閉狀態(tài)下，我們也可以通過(guò) myisamchk 工具來(lái)對(duì)數(shù)據(jù)庫(kù)中某個(gè)（或某些）表進(jìn)行檢
測(cè)或者修復(fù)。不過(guò)強(qiáng)烈建議不到萬(wàn)不得已不要輕易對(duì)表進(jìn)行修復(fù)操作，修復(fù)之前盡量做好可
能的備份工作，以免帶來(lái)不必要的后果。
Innodb 存儲(chǔ)引擎簡(jiǎn)介 ：
Innodb 之所以能如此受寵，主要是在于其功能方面的較多特點(diǎn)：
1、支持事務(wù)
Innodb 在功能方面最重要的一點(diǎn)就是對(duì)事務(wù)的支持，這無(wú)疑是讓 Innodb 成為 MySQL 最為流
行的存儲(chǔ)引擎之一的一個(gè)非常重要原因。
2、鎖定機(jī)制的改進(jìn)
Innodb 改變了 MyISAM 的鎖機(jī)制，實(shí)現(xiàn)了行鎖。
3、實(shí)現(xiàn)外鍵
Innodb 實(shí)現(xiàn)了外鍵引用這一數(shù)據(jù)庫(kù)的重要特性。
4、Innodb 存儲(chǔ)引擎也和 MyISAM 不太一樣，雖然也有.frm 文件來(lái)存放表結(jié)構(gòu)定義相關(guān)的元
數(shù)據(jù)，但是表數(shù)據(jù)和索引數(shù)據(jù)是存放在一起的。至于是每個(gè)表單獨(dú)存放還是所有表存放在一
起，完全由用戶(hù)來(lái)決定。
Innodb 的物理結(jié)構(gòu)分為兩大部分：
1、數(shù)據(jù)文件（表數(shù)據(jù)和索引數(shù)據(jù)）
存放數(shù)據(jù)表中的數(shù)據(jù)和所有的索引數(shù)據(jù)，包括主鍵和其他普通索引。在 Innodb 中，存在了
表空間（tablespace）這樣一個(gè)概念，但是他和 Oracle 的表空間又有較大的不同。首先，Innodb
的表空間分為兩種形式。一種是共享表空間，也就是所有表和索引數(shù)據(jù)被存放在同一個(gè)表空
間（一個(gè)或多個(gè)數(shù)據(jù)文件）中，通過(guò) innodb_data_file_path 來(lái)指定，增加數(shù)據(jù)文件需要停機(jī)
重啟。另外一種是獨(dú)享表空間，也就是每個(gè)表的數(shù)據(jù)和索引被存放在一個(gè)單獨(dú)的.ibd 文件中。
雖然我們可以自行設(shè)定使用共享表空間還是獨(dú)享表空間來(lái)存放我們的表，但是共享表空間都
是必須存在的，因?yàn)?Innodb 的 undo 信息和其他一些元數(shù)據(jù)信息都是存放在共享表空間里
面的。共享表空間的數(shù)據(jù)文件是可以設(shè)置為固定大小和可自動(dòng)擴(kuò)展大小兩種形式的。
當(dāng)我們的文件表空間快要用完的時(shí)候，我們必須要為其增加數(shù)據(jù)文件，當(dāng)然，只有共享表空
間有此操作。共享表空間增加數(shù)據(jù)文件的操作比較簡(jiǎn)單，只需要在 innodb_data_file_path 參
數(shù)后面按照標(biāo)準(zhǔn)格式設(shè)置好文件路徑和相關(guān)屬性即可，不過(guò)這里有一點(diǎn)需要注意的，就是
Innodb 在創(chuàng)建新數(shù)據(jù)文件的時(shí)候是不會(huì)創(chuàng)建目錄的，如果指定目錄不存在，則會(huì)報(bào)錯(cuò)并無(wú)
法啟動(dòng)。
2、日志文件
Innodb 的日志文件和 Oracle 的 redo 日志比較類(lèi)似，同樣可以設(shè)置多個(gè)日志組（最少 2
個(gè)），同樣采用輪循策略來(lái)順序的寫(xiě)入。
由于 Innodb 是事務(wù)的存儲(chǔ)引擎，所以系統(tǒng) Crash（宕機(jī)）對(duì)他來(lái)說(shuō)并不能造成非常嚴(yán)重的損
失，由于有 redo 日志（即事物日志）的存在，有 checkpoint 機(jī)制的保護(hù)，Innodb 完全可以
通過(guò) redo 日志將數(shù)據(jù)庫(kù) Crash 時(shí)刻已經(jīng)完成但還沒(méi)有來(lái)得及將數(shù)據(jù)寫(xiě)入磁盤(pán)的事務(wù)恢復(fù)，
也能夠?qū)⑺胁糠滞瓿刹⒁呀?jīng)寫(xiě)入磁盤(pán)的未完成事務(wù)回滾并將數(shù)據(jù)還原。
Innodb 不僅在功能特性方面和 MyISAM 存儲(chǔ)引擎有較大區(qū)別，在配置上面也是單獨(dú)處理的。
在 MySQL 啟動(dòng)參數(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)是打開(kāi)的，即每條
SQL 語(yǔ)句會(huì)默認(rèn)被封裝成一個(gè)事務(wù)，自動(dòng)提交，這樣會(huì)影響速度，所以最好是把多條 SQL
語(yǔ)句顯示放在 begin 和 commit 之間，組成一個(gè)事務(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 不支持行鎖定，只支持鎖定整個(gè)表。即 MyISAM 同一
個(gè)表上的讀鎖和寫(xiě)鎖是互斥的，MyISAM 并發(fā)讀寫(xiě)時(shí)如果等待隊(duì)列中既有讀請(qǐng)求又有寫(xiě)請(qǐng)求，
默認(rèn)寫(xiě)請(qǐng)求的優(yōu)先級(jí)高，即使讀請(qǐng)求先到，所以 MyISAM 不適合于有大量查詢(xún)和修改并存的
情況，那樣查詢(xún)進(jìn)程會(huì)長(zhǎng)時(shí)間阻塞。因?yàn)?MyISAM 是鎖表。
3、InnoDB 支持外鍵，MyISAM 不支持。
4、InnoDB 不支持全文索引，而 MyISAM 支持。
二、MySQL 自帶工具使用介紹
MySQL 數(shù)據(jù)庫(kù)不僅提供了數(shù)據(jù)庫(kù)的服務(wù)器端應(yīng)用程序，同時(shí)還提供了大量的客戶(hù)端工具程
序，如 mysql，mysqladmin，mysqldump 等等
1、mysql 命令
Mysql 命令是用的最多的一個(gè)命令工具了，為用戶(hù)提供一個(gè)命令行接口來(lái)操作管理 MySQL
服務(wù)器。
語(yǔ)法格式：
Usage: mysql [OPTIONS] [database]
例如：# mysql-e "select user,host from user"mysql
大家只要運(yùn)行一下“mysql --help”就會(huì)得到如下相應(yīng)的基本使用幫助信息：
這里主要介紹一些在運(yùn)維過(guò)程中會(huì)用到的相關(guān)選項(xiàng)：
首先看看“-e, --execute=name”參數(shù)，這個(gè)參數(shù)是告訴 mysql，我要執(zhí)行“-e”后面的某個(gè)
命令，而不是要通過(guò) mysql 連接登錄到 MySQL Server 上面。此參數(shù)在我們寫(xiě)一些基本的
MySQL 檢查和監(jiān)控的腳本中非常有用，運(yùn)維 mysql 時(shí)經(jīng)常在腳本中使用到它。
#mysql -hhostname -Pport -uusername -ppassword -e 相關(guān) mysql 的 sql 語(yǔ)句
例 1：
通過(guò) binlog_cache_use 以及 binlog_cache_disk_use 來(lái)分析設(shè)置的 binlog_cache_size
是否足夠
例 2：通過(guò)腳本創(chuàng)建數(shù)據(jù)庫(kù)、表及對(duì)表進(jìn)行增、改、刪、查操作。
腳本內(nèi)容如下：
創(chuàng)建授予 test 用戶(hù)可以在指定的源登錄
測(cè)試 test 用戶(hù)連接 mysql 服務(wù)器
授予腳本執(zhí)行權(quán)限
#chmod +x /root/mysql1.sh
執(zhí)行腳本：
如果在連接時(shí)候使用了“-E, --vertical”參數(shù)，登入之后的所有查詢(xún)結(jié)果都將以縱列顯示，效
果和我們?cè)谝粭l query 之后以“\G”結(jié)尾一樣。
#mysql -E -u root -p
“--prompt=name”參數(shù)對(duì)于做運(yùn)維的人來(lái)說(shuō)是一個(gè)非常重要的參數(shù)選項(xiàng)，其主要功能是定
制自己的 mysql 提示符的顯示內(nèi)容。在默認(rèn)情況下，我們通過(guò) mysql 登入到數(shù)據(jù)庫(kù)之后，mysql
的提示符只是一個(gè)很簡(jiǎn)單的內(nèi)容”mysql>“，沒(méi)有其他任何附加信息。非常幸運(yùn)的是 mysql
通過(guò)“--prompt=name”參數(shù)給我們提供了自定義提示信息的辦法，可以通過(guò)配置顯示登入
的主機(jī)地址，登錄用戶(hù)名，當(dāng)前時(shí)間，當(dāng)前數(shù)據(jù)庫(kù) schema，MySQL Server 的一些信
息等等。我個(gè)人強(qiáng)烈建議將登錄主機(jī)名，登錄用戶(hù)名和所在的 schema 這三項(xiàng)加入提示內(nèi)容，
因?yàn)楫?dāng)大家手邊管理的 MySQL 越來(lái)越多，操作越來(lái)越頻繁的時(shí)候，非常容易因?yàn)椴僮鞯臅r(shí)
候沒(méi)有太在意自己當(dāng)前所處的環(huán)境而造成在錯(cuò)誤的環(huán)境執(zhí)行了錯(cuò)誤的命令并造成嚴(yán)重后果
的情況。如果我們?cè)谔崾緝?nèi)容中加入了這幾項(xiàng)之后，至少可以更方便的提醒自己當(dāng)前所處環(huán)
境，以盡量減少犯錯(cuò)誤的概率。
個(gè)人強(qiáng)烈建議提示符定義： "\\u@\\h : \\d \\r:\\m:\\s> "，顯示效果：
切換數(shù)據(jù)庫(kù)：
提示符解釋?zhuān)?/span>
\u 表示用戶(hù)名, \h 表示主機(jī)名， \d 表示當(dāng)前數(shù)據(jù)庫(kù)，\r 小時(shí)（12 小時(shí)制），\m 分種，\s
秒，\R The current time, in 24-hour military time (0–23)
“--tee=name”參數(shù)也是對(duì)運(yùn)維人員非常有用的參數(shù)選項(xiàng)，用來(lái)告訴 mysql，將所有輸入和
輸出內(nèi)容都記錄進(jìn)文件。在我們一些較大維護(hù)變更的時(shí)候，為了方便被查，最好是將整個(gè)操
作過(guò)程的所有輸入和輸出內(nèi)容都保存下來(lái)。
假如 mysql 命令行狀態(tài)下，要進(jìn)行大量的交互操作，其實(shí)可以把這些操作記錄在 log 中進(jìn)行
審計(jì)，很簡(jiǎn)單 mysql -u root -p --tee=/path/xxxx.log
也可以 在服務(wù)器上的/etc/my.cnf 中的[client]加入
tee =/tmp/client_mysql.log 即可.
注：若沒(méi)有[client]就添加即可
或者在 mysql>提示符下執(zhí)行下面的命令
mysql 其他參數(shù)選項(xiàng)可以通過(guò) MySQL 官方參考手冊(cè)查閱，也可以通過(guò)執(zhí)行“mysql --help”
或 man mysql 得到幫助信息之后通過(guò)自行實(shí)驗(yàn)來(lái)做進(jìn)一步的深刻認(rèn)識(shí)。
2、mysqladmin
Usage: mysqladmin [OPTIONS] command command ...
mysqadmin，顧名思義，提供的功能都是與 MySQL 管理相關(guān)的各種功能。如 MySQL Server
狀態(tài)檢查，各種統(tǒng)計(jì)信息的 flush，創(chuàng)建/刪除數(shù)據(jù)庫(kù)，關(guān)閉 MySQL Server 等等。mysqladmin
所能做的事情，雖然大部分都可以通過(guò) mysql 連接登錄上 MySQL Server 之后來(lái)完成，但是
大部分通過(guò) mysqladmin 來(lái)完成操作會(huì)更簡(jiǎn)單更方便。這里將介紹一下經(jīng)常使用到的幾個(gè)常
用功能：
ping 命令可以很容易檢測(cè) MySQL Server 是否還能正常提供服務(wù)
mysql 本機(jī)上測(cè)試：
在其他主機(jī)上測(cè)試 mysql server 是否正常提供服務(wù)
注 1：地址 192.168.1.1 是 mysql server 的 ip
注 2：mysql server 的防火墻要允許 3306/tcp 通信
注 3：在 mysql server 上創(chuàng)建授權(quán)用戶(hù)
status 命令可以獲取當(dāng)前 MySQL Server 的幾個(gè)基本的狀態(tài)值：
mysqladmin status 命令結(jié)果有下述列
Uptime:是 mysql 服務(wù)器運(yùn)行的秒數(shù)。
Threads:活躍線(xiàn)程的數(shù)量即開(kāi)啟的會(huì)話(huà)數(shù)。
Questions： 服務(wù)器啟動(dòng)以來(lái)客戶(hù)的問(wèn)題(查詢(xún))數(shù)目 （只要跟 mysql 作交互，不管查詢(xún)表，
還是查詢(xún)服務(wù)器狀態(tài)都記一次）。
Slow queries：是慢查詢(xún)的數(shù)量。
Opens：mysql 已經(jīng)打開(kāi)的數(shù)據(jù)庫(kù)表的數(shù)量
Flush tables: mysql 已經(jīng)執(zhí)行的 flush tables，refresh 和 reload 命令的數(shù)量。
注：flush tables //刷新表（清除緩存）
reload 重載授權(quán)表
refresh 洗掉所有表并關(guān)閉和打開(kāi)日志文件
open：打開(kāi)數(shù)據(jù)庫(kù)的表的數(shù)量，以服務(wù)器啟動(dòng)開(kāi)始。
Queries per second avg：select 語(yǔ)句平均查詢(xún)時(shí)間
Memory in use 分配的內(nèi)存(只有在 MySQL 用--with-debug 編譯時(shí)可用)
Max memory used 分配的最大內(nèi)存(只有在 MySQL 用--with-debug 編譯時(shí)可用)
processlist 獲取當(dāng)前數(shù)據(jù)庫(kù)的連接線(xiàn)程信息：
監(jiān)控 mysql 進(jìn)程運(yùn)行狀態(tài)：
上面的這三個(gè)功能在一些簡(jiǎn)單監(jiān)控腳本中經(jīng)常使用到的。
mysqladmin 其他參數(shù)選項(xiàng)可以通過(guò)執(zhí)行“mysqladmin --help”或 man mysqladmin 得到幫助
信息。
編寫(xiě)一個(gè)簡(jiǎn)單的 mysql 監(jiān)控腳本，內(nèi)容如下：
附加知識(shí)點(diǎn) 1：
Mysql 的系統(tǒng)數(shù)據(jù)庫(kù)：
1） INFORMATION_SCHEMA 數(shù)據(jù)字典：此數(shù)據(jù)庫(kù)存貯了其他所有數(shù)據(jù)庫(kù)的信息（元數(shù)據(jù)）。
元數(shù)據(jù)是關(guān)于數(shù)據(jù)的數(shù)據(jù)，如 database name 或 table name，列的數(shù)據(jù)類(lèi)型，或訪(fǎng)問(wèn)權(quán)
限等。
INFORMATION_SCHEMA 庫(kù)的主要系統(tǒng)表
TABLES 表：提供了關(guān)于數(shù)據(jù)庫(kù)中的表和視圖的信息。（Table_schema 字段代表 數(shù)據(jù)表所屬的數(shù)據(jù)
庫(kù)名）
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='數(shù)據(jù)庫(kù)名';
COLUMNS 表：提供了表中的列信息。詳細(xì)表述了某張表的所有列以及每個(gè)列的信息。
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='數(shù)據(jù)庫(kù)名
‘' AND TABLE_NAME='表名'
TABLE_CONSTRAINTS 表：存儲(chǔ)主鍵約束、外鍵約束、唯一約束、check 約束。各字段的說(shuō)明
信息
ELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA=' 數(shù) 據(jù) 庫(kù) 名
' AND TABLE_NAME='表名'
STATISTICS 表：提供了關(guān)于表索引的信息。
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=' 數(shù) 據(jù) 庫(kù) 名
' AND TABLE_NAME='表名'
2）performance_schema 性能字典，此數(shù)據(jù)庫(kù)為數(shù)據(jù)庫(kù)性能優(yōu)化提供重要的參考信息
3）MYSQL 數(shù)據(jù)庫(kù): 該數(shù)據(jù)庫(kù)也是個(gè)核心數(shù)據(jù)庫(kù)，存儲(chǔ)用戶(hù)的權(quán)限信息與幫助信息。
4）MySQL5.7 提供了 sys 系統(tǒng)數(shù)據(jù)庫(kù)。 sys 數(shù)據(jù)庫(kù)里面包含了一系列的存儲(chǔ)過(guò)程、自定義函
數(shù)以及視圖來(lái)幫助我們快速的了解系統(tǒng)的元數(shù)據(jù)信息。sys 系統(tǒng)數(shù)據(jù)庫(kù)結(jié)合了
information_schema 和 performance_schema 的相關(guān)數(shù)據(jù)，讓我們更加容易的檢索元數(shù)據(jù)。
附加知識(shí)點(diǎn) 2：
未經(jīng)允許不得轉(zhuǎn)載傳播--陳英宏
博客地址：hongge.blog.51cto.com
mysql 有關(guān) show 的用法
SHOW DATABASES 列出 MySQL Server 上的數(shù)據(jù)庫(kù)。
SHOW TABLES [FROM db_name]列出數(shù)據(jù)庫(kù)中的表。
SHOW TABLE STATUS [FROM db_name]列出數(shù)據(jù)庫(kù)的表信息，比較詳細(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 查詢(xún)進(jìn)程
SHOW GRANTS FOR user 列出用戶(hù)的授權(quán)命令
3、mysqldump：
這個(gè)工具其功能就是將 MySQL Server 中的數(shù)據(jù)以 SQL 語(yǔ)句的形式從數(shù)據(jù)庫(kù)中 dump 成文本
文件。mysqldump 是做為 MySQL 的一種邏輯備份工具
4、mysqlbinlog
mysqlbinlog 程序的主要功能就是分析 MySQL Server 所產(chǎn)生的二進(jìn)制日志（也就是 binlog）。
通過(guò) mysqlbinlog，我們可以解析出 binlog 中指定時(shí)間段或者指定日志起始和結(jié)束位置的內(nèi)
容解析成 SQL 語(yǔ)句。
三 、Mysqlslap 性能測(cè)試 MySQL 二 種存儲(chǔ)引擎
mysqlslap 是 mysql 自帶的基準(zhǔn)測(cè)試工具,優(yōu)點(diǎn):查詢(xún)數(shù)據(jù),語(yǔ)法簡(jiǎn)單,靈活容易使用.該工具可以
模擬多個(gè)客戶(hù)端同時(shí)并發(fā)的向服務(wù)器發(fā)出查詢(xún)更新,給出了性能測(cè)試數(shù)據(jù)而且提供了多種引
擎的性能比較.msqlslap 為 mysql 性能優(yōu)化前后提供了直觀(guān)的驗(yàn)證依據(jù),建議系統(tǒng)運(yùn)維和 DBA
人員應(yīng)該掌握一些常見(jiàn)的壓力測(cè)試工具,才能準(zhǔn)確的掌握線(xiàn)上數(shù)據(jù)庫(kù)支撐的用戶(hù)流量上限及
其抗壓性等問(wèn)題。
現(xiàn)在看一下這個(gè)壓力測(cè)試工具 mysqlslap，關(guān)于他的選項(xiàng)手冊(cè)上以及--help 介紹的很詳細(xì)。
這里解釋一下一些常用的選項(xiàng)。
--concurrency 代表并發(fā)數(shù)量，多個(gè)可以用逗號(hào)隔開(kāi)。例如：concurrency=50,100,200
--engines 代表要測(cè)試的引擎，可以有多個(gè)，用分隔符隔開(kāi)。
--iterations 代表要運(yùn)行這些測(cè)試多少次，即運(yùn)行多少次后，得到結(jié)果。
--auto-generate-sql 代表用系統(tǒng)自己生成的 SQL 腳本來(lái)測(cè)試。
--auto-generate-sql-load-type 代表要測(cè)試的是讀還是寫(xiě)還是兩者混合的
（read,write,update,mixed）
--number-of-queries 代表總共要運(yùn)行多少次查詢(xún)。每個(gè)客戶(hù)運(yùn)行的查詢(xún)數(shù)量可以用查詢(xún)總數(shù)
/并發(fā)數(shù)來(lái)計(jì)算。比如倒數(shù)第二個(gè)結(jié)果 2=200/100。
--debug-info 代表要額外輸出 CPU 以及內(nèi)存的相關(guān)信息（注：只有在 MySQL 用--with-debug
編譯時(shí)可）。
--number-int-cols 代表測(cè)試表中的 INTEGER 類(lèi)型的屬性有幾個(gè)。
--number-char-cols 代表測(cè)試表的 char 類(lèi)型字段的數(shù)量。
--create-schema 代表自己定義的模式（在 MySQL 中也就是庫(kù)即創(chuàng)建測(cè)試的數(shù)據(jù)庫(kù)）。
--query 代表自己的 SQL 腳本。
--only-print 如果只想打印看看 SQL 語(yǔ)句是什么，可以用這個(gè)選項(xiàng)。
--csv=name生產(chǎn)CSV格式數(shù)據(jù)文件
查看 Mysql 數(shù)據(jù)庫(kù)默認(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)使用存儲(chǔ)引擎，如下查看：
mysql> show engines;
現(xiàn)在我們來(lái)看一下具體測(cè)試的例子
用自帶的 SQL 腳本來(lái)測(cè)試:
[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é)果：
測(cè)試說(shuō)明：模擬測(cè)試兩次讀寫(xiě)并發(fā)，第一次50，第二次 100，自動(dòng)生成 SQL 腳本，測(cè)試表
包含 20 個(gè) init 字段，30 個(gè) char 字段，每次執(zhí)行 2000 查詢(xún)請(qǐng)求。測(cè)試引擎分別是 myisam，
innodb。
測(cè)試結(jié)果說(shuō)明：
Myisam第一次50客戶(hù)端同時(shí)發(fā)起增查用0.223/s,第二次100客戶(hù)端同時(shí)發(fā)起
增查用0.234/s
Innodb第一次50客戶(hù)端同時(shí)發(fā)起增查用0.436/s,第二次100客戶(hù)端同時(shí)發(fā)起
增查用0.448/s
由此可見(jiàn) MyISAM 存儲(chǔ)引擎處理性能是最好的，也是最常用的，但不支持事務(wù)。InonDB 存
儲(chǔ)引擎提供了事務(wù)型數(shù)據(jù)引擎（ACID），在事務(wù)型引擎里使用最多的。具有事務(wù)回滾，系統(tǒng)
修復(fù)等特點(diǎn)。
Mysqlslap 測(cè)試工具生產(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 主機(jī)上，打開(kāi)并生成圖表
注：通過(guò) mysqlslap 工具對(duì) mysql server 進(jìn)行壓力測(cè)試，可以通過(guò)--concurrency、
--number-of-queries 等選項(xiàng)的值查看每次測(cè)試的結(jié)果，通過(guò)反復(fù)測(cè)試、優(yōu)化得出 mysql server
的最大并發(fā)數(shù)。
如果 mysqlslap 工具輸出結(jié)果為 Segmentation fault (core dumped)基本表示走超出 mysql
server 的負(fù)載。
謝謝觀(guān)看，真心的希望能幫到您！
本文出自 “一盞燭光” 博客，謝絕轉(zhuǎn)載！
更多建議：