MySQL評(píng)估范圍：
前期檢查
資料準(zhǔn)備
數(shù)據(jù)庫(kù)字典
常用SQL
表數(shù)據(jù)量與頻度
定期清理機(jī)制
設(shè)計(jì)問(wèn)題
保留關(guān)鍵字
字段默認(rèn)值
字段類(lèi)型
字符集選擇
命名規(guī)范
索引設(shè)計(jì)
外鍵問(wèn)題
準(zhǔn)實(shí)時(shí)性數(shù)據(jù)處理
垂直分割需求
水平拆分需求
適度冗余的設(shè)計(jì)
數(shù)據(jù)庫(kù)安全
賬戶(hù)和權(quán)限
優(yōu)化
MySQL安裝優(yōu)化
配置文件優(yōu)化
測(cè)試階段
監(jiān)控
監(jiān)控?cái)?shù)據(jù)庫(kù)配置參數(shù)-MySQL Report
給出配置參數(shù)優(yōu)化建議- tuning-primer
開(kāi)啟慢查詢(xún)?nèi)罩?，記錄所有查詢(xún)- log_slow_queries
數(shù)據(jù)增長(zhǎng)量監(jiān)控-MONyog
數(shù)據(jù)庫(kù)主從狀態(tài)監(jiān)控
穩(wěn)定期
建立性能基線(xiàn)
Cacti性能監(jiān)控
MONyog詳細(xì)信息監(jiān)控
MySQL Report
第一章 需要開(kāi)發(fā)商提供的資料
1數(shù)據(jù)字典
（提供詳細(xì)的數(shù)據(jù)庫(kù)所有表的詳細(xì)說(shuō)明，如下格式，外加表的簡(jiǎn)單描述）
admin_action
字段名
字段含義
字段類(lèi)型
默認(rèn)值
Key
id
自增ID
int(11)
YES
cate_id
對(duì)應(yīng)根菜單的ID,parent_id為0時(shí)設(shè)置此字段
int(11)
parent_id
操作父ID
int(11)
order
排序值，越大越靠前
int(11)
action_name
操作名
varchar(50)
action_desc
action描述
varchar(50)
extend
擴(kuò)展字段
varchar(50)
2常用SQL
（針對(duì)數(shù)據(jù)字典，給出每個(gè)表上的常發(fā)生的SQL語(yǔ)句，形式如，針對(duì)上面的表給出的SQL）
表數(shù)據(jù)量與頻度
（預(yù)估上面每個(gè)表的一年內(nèi)的數(shù)據(jù)增長(zhǎng)量，和每個(gè)表的讀寫(xiě)頻率，如）
(之后可以使用MONyog來(lái)觀察每個(gè)庫(kù)以及每個(gè)表的數(shù)據(jù)和索引的大小)
4定期清理機(jī)制
對(duì)log或history等類(lèi)型表的清理機(jī)制
第二章 設(shè)計(jì)問(wèn)題
1保留關(guān)鍵字
不要使用MySQL保留關(guān)鍵字作為名稱(chēng)，容易造成SQL語(yǔ)法錯(cuò)誤。
2字段默認(rèn)值設(shè)計(jì)
表的字段盡量使用NOT NULL。
摘自MySQL文檔：
“NULL columns require additional space in the row to record whethertheir values are NULL. For MyISAM tables, each NULL column takes one bit extra,rounded up to the nearest byte.”
字段設(shè)計(jì)
數(shù)字和日期類(lèi)型：
實(shí)際上還有一類(lèi)數(shù)字類(lèi)型是通過(guò)二進(jìn)制格式以字符串來(lái)存放的，如，DECIMAL(DEC)[(M[,D])]，NUMERIC[(M[,D])]，由于其存放長(zhǎng)度主要通過(guò)其定義時(shí)候的M所決定，M定義多大，則實(shí)際就有多長(zhǎng)。M代表整個(gè)位數(shù)的長(zhǎng)度，D代表小數(shù)殿后的位數(shù)，默認(rèn)M為10，D為0。一般考慮到這種數(shù)據(jù)完全可以變化形式以整數(shù)存放，所以這種數(shù)據(jù)類(lèi)型（）一般不用。但是，在mysql 中float、double（或real）是浮點(diǎn)數(shù)，decimal（或numberic）是定點(diǎn)數(shù)。浮點(diǎn)數(shù)（float、double）存在誤差問(wèn)題；對(duì)貨幣等對(duì)精度敏感的數(shù)據(jù)，應(yīng)該用定點(diǎn)數(shù)表示或存儲(chǔ)；編程中，如果用到浮點(diǎn)數(shù)，要特別注意誤差問(wèn)題，并盡量避免做浮點(diǎn)數(shù)比較；
另外IP地址字段的話(huà)，盡量設(shè)置為UNSIGNED INT（32無(wú)符號(hào)整形），查詢(xún)時(shí)可以使用
INET_ATON()把一個(gè)字符串IP轉(zhuǎn)成一個(gè)整形，并使用
INET_NTOA()把一個(gè)IP轉(zhuǎn)換成字符串IP。
時(shí)間存儲(chǔ)格式，常用的是DATETIME，DATE，TIMESTAMP這三種，從存儲(chǔ)空間看TIMESTAMP最少，4個(gè)字節(jié)，而其他兩種都要8個(gè)字節(jié)，多了一倍。如果有需要存放早于1970年之前的時(shí)間，則要使用DATETIME類(lèi)型，假如不需要，則最好使用TIMESTAMP來(lái)減少存儲(chǔ)空間的占用。
字符存儲(chǔ)類(lèi)型：
mysql5.0.3之后的表示，VARCHAR[(M)]表示的字節(jié)數(shù)，最大限制和字符集有關(guān)，如果是gbk編碼，最大長(zhǎng)度為(65535-1-2)/2=32766，減1的原因是實(shí)際行存儲(chǔ)從第二個(gè)字節(jié)開(kāi)始，減2的原因是varchar頭部的2個(gè)字節(jié)表示長(zhǎng)度，除2因?yàn)槭莋bk編碼；如果是utf8編碼，最大長(zhǎng)度為(65535-1-2)/3=21844。字段盡量選擇固定長(zhǎng)度。另外MySQL 5.0之后版本存儲(chǔ)VARCHAR類(lèi)型的時(shí)候會(huì)保留末尾空格，CHAR[(M)]都是以空格填補(bǔ)剩余的空間，所以，如果要保留結(jié)尾空格的話(huà)，一定要使用VARCHAR。
另外不要使用數(shù)字作為ENUM枚舉常量！
盡量用char(1)來(lái)代替bit(1)，盡量避免程序錯(cuò)誤！
MyISAM表的話(huà)，盡量使用char()代替varchar()；對(duì)于InnoDB數(shù)據(jù)表，內(nèi)部的行存儲(chǔ)格式?jīng)]有區(qū)分固定長(zhǎng)度和可變長(zhǎng)度列（所有數(shù)據(jù)行都使用指向數(shù)據(jù)列值的頭指針），主要的性能因素是數(shù)據(jù)行使用的存儲(chǔ)總量，建議使用varcahr()，可以減少存儲(chǔ)空間。
對(duì)于BLOB和TEXT字段，如果可以，就把這些字段分拆到另外一個(gè)表里面去。
4字符集選擇
mysql的字符集包括字符集（CHARACTER）和校對(duì)規(guī)則（COLLATION）兩個(gè)概念。字符集是用來(lái)定義mysql存儲(chǔ)字符串的方式，校對(duì)規(guī)則是定義了比較字符串的方式。
UTF-8：是用以解決國(guó)際上字符的一種多字節(jié)編碼，它對(duì)英文使用8位（即一個(gè)字節(jié)），中文使用24位（三個(gè)字節(jié)）來(lái)編碼。UTF-8包含全世界所有國(guó)家需要用到的字符，是國(guó)際編碼，通用性強(qiáng)。
GBK： 是國(guó)家標(biāo)準(zhǔn)GB2312基礎(chǔ)上擴(kuò)容后兼容GB2312的標(biāo)準(zhǔn)。GBK的文字編碼是用雙字節(jié)來(lái)表示的，即不論中、英文字符均使用雙字節(jié)來(lái)表示，為了區(qū)分中文，將其最高位都設(shè)定成1。GBK包含全部中文字符，是國(guó)家編碼，通用性比UTF8差，不過(guò)UTF8占用的數(shù)據(jù)庫(kù)比GBK大。
為了避免所有亂碼問(wèn)題，應(yīng)該采用UTF-8，將來(lái)要支持國(guó)際化也非常方便UTF-8可以看作是大字符集，它包含了大部分文字的編碼。
5命名規(guī)范
1、 數(shù)據(jù)庫(kù)和表名應(yīng)盡可能和所服務(wù)的業(yè)務(wù)模塊名一致；
2、 服務(wù)于同一子模塊的一類(lèi)表盡量以子模塊名（或部分單詞）為前綴或后綴；
3、 表名應(yīng)盡量包含與所存放數(shù)據(jù)相對(duì)應(yīng)的單詞；
4、 字段名稱(chēng)也盡量保持和實(shí)際數(shù)據(jù)相對(duì)應(yīng)
5、 索引名稱(chēng)盡量包含所有的索引鍵字段名或者縮寫(xiě)，且各字段名在索引名中的順序應(yīng)與索引鍵在索引中的索引順序一致，且盡量包含一個(gè)類(lèi)似于idx 或者ind 之類(lèi)的前綴或者后綴，以表名其對(duì)象類(lèi)型是索引，同時(shí)還可以包含該索引所屬表的名稱(chēng)；
6、 約束等其他對(duì)象也應(yīng)該盡可能包含所屬表或其他對(duì)象的名稱(chēng)，以表名各自關(guān)系。
7、 同一個(gè)SQL語(yǔ)句中必須統(tǒng)一大小寫(xiě)，不允許SELECT * FROM my_tableWHERE MY_TABLE.col=1;
索引的設(shè)計(jì)
說(shuō)明：MyISAM的主鍵索引（聚集索引）和普通索引（非聚集索引）；InnoDB中索引分為聚集索引形式的主鍵索引和非聚集索引形式的普通索引。
頻繁的作為條件進(jìn)行查詢(xún)的字段應(yīng)創(chuàng)建索引
·唯一性太差的字段不要?jiǎng)?chuàng)建索引
頻繁更新的字段不適合創(chuàng)建索引
·能建立組合索引的盡量建立組合索引
·索引鍵長(zhǎng)不能過(guò)長(zhǎng)（最大限制為1000個(gè)字節(jié)）
·join條件字段類(lèi)型不一致的時(shí)候mysql無(wú)法使用索引
·如果一個(gè)字段設(shè)計(jì)成用來(lái)存儲(chǔ)URL，則可以新增一個(gè)被索引的url_crc列，使用CRC32做哈希
·如果對(duì)較長(zhǎng)的字段使用索引，可以考慮前綴索引（注意索引唯一性）
·避免使用重復(fù)索引(可以使用pt-duplicate-key-checker來(lái)檢查重復(fù)索引)
·統(tǒng)計(jì)未使用的索引(可以使用pt-index-usage來(lái)查看)
·只有MyISAM存儲(chǔ)引擎支持FULLTEXT索引，并且只為CHAR、VARCHAR 和TEXT 列
7外鍵問(wèn)題
InnoDB中定義外鍵時(shí)需要注意的地方：
InnoDB有問(wèn)題的方式
InnoDB正常的方式
Create Table: CREATE TABLE `person` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` char(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE person2 (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE shirt2 (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(owner) REFERENCES person2(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> show create table shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table shirt2\G
*************************** 1. row ***************************
Table: shirt2
Create Table: CREATE TABLE `shirt2` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `owner` (`owner`),
CONSTRAINT `` FOREIGN KEY (`owner`) REFERENCES `person2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Show create table的時(shí)候，表shirt并沒(méi)有出現(xiàn)外鍵定義！也相當(dāng)于沒(méi)有定義外鍵！在刪除父表的時(shí)候，不會(huì)報(bào)錯(cuò)！
這樣定義的外鍵就會(huì)有CONSTRAINT語(yǔ)句，外鍵約束在起作用，當(dāng)刪除父表數(shù)據(jù)時(shí)，會(huì)報(bào)錯(cuò)：ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`shirt2`, CONSTRAINT `shirt2_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `person2` (`id`))
alter table shirt2 drop FOREIGN KEY shirt2_ibfk_1;
alter table shirt2 add constraint shirt2_ibfk_1 FOREIGN KEY (owner) REFERENCES person2(id) ON DELETE CASCADE ON UPDATE CASCADE;
7準(zhǔn)實(shí)時(shí)性數(shù)據(jù)處理
如系統(tǒng)當(dāng)前在線(xiàn)人數(shù)，論壇系統(tǒng)當(dāng)前總帖數(shù)、回帖數(shù)等，多條件大結(jié)果集查詢(xún)頁(yè)面的總結(jié)果數(shù)以及
總頁(yè)數(shù)，某些虛擬積分的top n 排名等等。這些統(tǒng)計(jì)的計(jì)算都會(huì)涉及到大量的數(shù)據(jù)，同時(shí)也需要大量的計(jì)算資源，訪問(wèn)頻率也都非常的高。如果都通過(guò)實(shí)時(shí)統(tǒng)計(jì)，恐怕只要數(shù)據(jù)量稍微大一些，都會(huì)帶來(lái)非常大的硬件資源開(kāi)銷(xiāo)。但在短時(shí)間內(nèi)的不夠精確，又并不會(huì)帶來(lái)太大用戶(hù)體驗(yàn)的降低。所以完全可以通過(guò)定時(shí)任務(wù)程序（如cront跑一個(gè)腳本定時(shí)的去算統(tǒng)計(jì)數(shù)據(jù)），沒(méi)隔一定時(shí)間段進(jìn)行一次統(tǒng)計(jì)后存放在專(zhuān)門(mén)設(shè)計(jì)的統(tǒng)計(jì)表中。這樣，在統(tǒng)計(jì)數(shù)據(jù)需要展示的時(shí)候，我們只需要從統(tǒng)計(jì)好的結(jié)果數(shù)據(jù)中取出即可。
垂直分割需求
示例一：在Users表中有一個(gè)字段是家庭地址，這個(gè)字段是可選字段，相比起，除了個(gè)人信息外，并不需要經(jīng)常讀取或是改寫(xiě)這個(gè)字段。那么，可以把這個(gè)字段設(shè)計(jì)到另外一張表中。這樣的表有更好的性能。
示例二： 你有一個(gè)叫 “l(fā)ast_login” 的字段，它會(huì)在每次用戶(hù)登錄時(shí)被更新。但是，每次更新時(shí)會(huì)導(dǎo)致該表的查詢(xún)緩存被清空。所以，你可以把這個(gè)字段放到另一個(gè)表中，這樣就不會(huì)影響你對(duì)用戶(hù)ID，用戶(hù)名，用戶(hù)角色的不停地讀取了，因?yàn)椴樵?xún)緩存會(huì)幫你增加很多性能。
另外，你需要注意的是，這些被分出去的字段所形成的表，確保不會(huì)經(jīng)常性地去Join他們，不然的話(huà)，這樣的性能會(huì)比不分割時(shí)還要差，而且，會(huì)是指數(shù)級(jí)的下降。
9水平拆分需求
譬如DNWeb的eventlog表，里面的log類(lèi)型分為很多種，而且這個(gè)表又比較大，所以這個(gè)時(shí)候最好做水平拆分，將像登陸記錄，統(tǒng)計(jì)在線(xiàn)人數(shù)等類(lèi)型分離出去。
10適度冗余的設(shè)計(jì)
被頻繁引用且只能通過(guò)join 2張（或者更多）大表的方式才能得到的獨(dú)立小字段，適合作為一個(gè)冗余字段，但要保證數(shù)據(jù)的一致性！
第三章數(shù)據(jù)庫(kù)安全審核
1賬戶(hù)和權(quán)限
User
From Host
Administrative Roles
ga2
192.168.0.132
root
Localhost
執(zhí)行：SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS queryFROM mysql.user;
（可以根據(jù)MySQL Workbench這個(gè)軟件來(lái)看）
第四章MySQL優(yōu)化
安裝優(yōu)化
（1）二進(jìn)制的發(fā)行版包括RPM等包裝好的特定二進(jìn)制版本：
好處：
a) 通過(guò)非常簡(jiǎn)單的安裝方式快速完成MySQL 的部署；
b) 安裝版本是經(jīng)過(guò)比較完善的功能和性能測(cè)試的編譯版本；
c) 所使用的編譯參數(shù)更具通用性的，且比較穩(wěn)定；
不足：存儲(chǔ)引擎的種類(lèi)，字符集的選擇，安裝目錄，等都是定制好的（后期也可以更改），一臺(tái)server上只能安裝一個(gè)MySQL。
補(bǔ)充RPM默認(rèn)安裝路徑：
/usr/bin
客戶(hù)端程序和腳本、比如 mysqladmin mysqldump等命令
/var/lib/mysql
數(shù)據(jù)目錄
/usr/share/mysql
mysql.server命令及配置文件, 錯(cuò)誤消息和字符集文件
/etc/rc.d/init.d/
啟動(dòng)腳本文件mysql的目錄
/usr/share/sql-bench
基準(zhǔn)測(cè)試程序
（2）源碼編譯安裝：
好處：
a) 針對(duì)自己的硬件平臺(tái)選用合適的編譯器來(lái)優(yōu)化編譯后的二進(jìn)制代碼；
b) 根據(jù)不同的軟件平臺(tái)環(huán)境調(diào)整相關(guān)的編譯參數(shù)；
c) 針對(duì)我們特定應(yīng)用場(chǎng)景選擇需要什么組件不需要什么組件；
d) 根據(jù)我們的所需要存儲(chǔ)的數(shù)據(jù)內(nèi)容選擇只安裝我們需要的字符集；
e) 同一臺(tái)主機(jī)上面可以安裝多個(gè)MySQL；
不足：
a) 對(duì)編譯參數(shù)的不夠了解造成編譯參數(shù)使用不當(dāng)可能使編譯出來(lái)的二進(jìn)制代碼不夠穩(wěn)定；
b) 對(duì)自己的應(yīng)用環(huán)境把握失誤而使用的優(yōu)化參數(shù)可能反而使系統(tǒng)性能更差；
c) 還有一個(gè)并不能稱(chēng)之為隱患的小問(wèn)題就是源碼編譯安裝將使安裝部署過(guò)程更為復(fù)雜，所花費(fèi)的時(shí)間更長(zhǎng)
補(bǔ)充
（mysql5.5以后是通過(guò)cmake來(lái)編譯的）安裝cmake-2.8.4.tar.gz
# yum install make gcc gcc-c++ ncurses-devel openssl-devel bison cmake -y
或者
# wget
http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz# tar zxvf cmake-2.8.4.tar.gz
# cd cmake-2.8.4
# ./bootstrap
# gmake
# gmake install
# ./configure
# make && make install
添加mysql用戶(hù)
查看是否有mysql用戶(hù)及用戶(hù)組
# cat /etc/passwd 查看用戶(hù)列表
# cat /etc/group 查看用戶(hù)組列表
如果沒(méi)有就創(chuàng)建
# groupadd mysql
# useradd -g mysql mysql
修改/usr/local/mysql權(quán)限
# chown -R mysql:mysql /usr/local/mysql
獲取解壓mysql-5.6.15
#wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.15.tar.gz
# tar zxvf mysql-5.6.15.tar.gz
# cd mysql-5.6.15
編譯mysql-5.6.15
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR= \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1\
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-=1 \ #啟用readline庫(kù)支持（提供可編輯的命令行）
-DWITH_SSL=system \ #啟用ssl庫(kù)支持（安全套接層）
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \ #指定sock路徑
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_USER=mysql \ #指定mysql用戶(hù)(默認(rèn)為mysql)
-DENABLED_LOCAL_INFILE=1 \ #啟用本地?cái)?shù)據(jù)導(dǎo)入支持
-DWITH_DEBUG=0\ #禁用debug（默認(rèn)為禁用）
-=0\ #禁用Profiling分析（默認(rèn)為開(kāi)啟）
-DEXTRA_CHARSETS=all \ #啟用額外的字符集類(lèi)型（默認(rèn)為all）
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
# make && make install
復(fù)制配置文件
# cp support-files/my-medium.cnf /etc/my.cnf
初始化數(shù)據(jù)庫(kù)
# chmod 755 scripts/mysql_install_db
# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ \
--datadir=/usr/local/mysql/data/
設(shè)置mysqld的開(kāi)機(jī)啟動(dòng)
# cp support-files/mysql.server /etc/init.d/mysql
# chmod 755 /etc/init.d/mysql
# chkconfig mysql on
設(shè)置PATH
# export PATH=/usr/local/mysql/bin:$PATH
讓PATH生效
source /etc/profile
2 MySQL日志設(shè)置優(yōu)化
首先看下BINLOG（做主從的時(shí)候要開(kāi)啟）（默認(rèn)情況下，系統(tǒng)僅僅打開(kāi)錯(cuò)誤日志log_error）：
·：在事務(wù)過(guò)程中容納二進(jìn)制日志SQL語(yǔ)句的緩存大小，是為每個(gè)客戶(hù)端分配的?？梢詮腳use和binlog_cache_disk_use來(lái)判斷binlog_cache_size的設(shè)置是否合理。
·max_binlog_cache_size：指所有binlog能夠使用的最大cache內(nèi)存大小。默認(rèn)即可
·max_binlog_size：binlog日志的最大值，一般設(shè)置為512M或者1G，最大為1G。
·sync_binlog：默認(rèn)0，表示當(dāng)事務(wù)提交之后，MySQL不做fsync之類(lèi)的磁盤(pán)同步指令刷新binlog_cache中的信息到磁盤(pán)，而讓filesystem自行決定什么時(shí)候同步，或者cache滿(mǎn)了之后才同步到磁盤(pán)。這個(gè)時(shí)候性能是最好的，但也是風(fēng)險(xiǎn)較大的。設(shè)置為1的時(shí)候，故障時(shí)最多只丟失binglog_cache中未完成的一個(gè)事務(wù)，是最安全但是性能損耗較大。對(duì)于高并發(fā)事務(wù)系統(tǒng)來(lái)說(shuō)，sync_binlog保持默認(rèn)即可。
·binlog_do_db：設(shè)置哪些數(shù)據(jù)庫(kù)需要記錄binlog；最好不要使用此設(shè)置
·binlog_ignore_db：設(shè)置哪些數(shù)據(jù)庫(kù)不需要記錄binlog；最好不要使用此設(shè)置
例如：
binlog_cache_size = 4M
##max_binlog_cache_size = 16EB ##默認(rèn)即可
max_binlog_size = 512M
sync_binlog = 0
SLOW QUERY LOG相關(guān)參數(shù)及使用建議（初期臨時(shí)可以開(kāi)啟，動(dòng)態(tài)變量可以直接關(guān)閉set global slow_query_log=OFF;）：
slow_query_log_file = /var/log/mylogs/slowquery.log
long_query_time = 1
log-queries-not-using-indexes
優(yōu)化
QueryCache的缺點(diǎn)（所以一般禁用query_cache）：后端的任何一個(gè)表的任何一條數(shù)據(jù)發(fā)生變化后，就會(huì)將與該表有關(guān)的query的cache全部失效。query語(yǔ)句的hash運(yùn)算以及hash查找資源CPU消耗；query cache中緩存的是result set，而不是數(shù)據(jù)頁(yè)
QueryCache相關(guān)參數(shù)：
·have_query_cache：是否支持Query Cache；
·query_cache_limit：存放的單條query最大的result set，默認(rèn)1M；
·query_cache_min_res_unit：query cache每個(gè)result set存放的最小內(nèi)存大小，默認(rèn)4K；
·query_cache_size：系統(tǒng)中用于query cache內(nèi)存的大??；默認(rèn)1048576B=1024K
·query_cache_type：是否啟用Query Cache；（>= 5.6.8，默認(rèn)禁止）
query_cache_wlock_invalidate：針對(duì)MyISAM存儲(chǔ)引擎，設(shè)置當(dāng)有write lock在某個(gè)table上面的時(shí)候，讀請(qǐng)求時(shí)要等write lock釋放資源之后再查詢(xún)，還是允許直接從query cache中讀取結(jié)果，默認(rèn)為false（可以直接從query cache中取出結(jié)果）；
Query Cache 命中率=Qcache_hits / ( Qcache_hits + Qcache_inserts )；
例如：
query_cache_type = 0 ##禁止Query Cache，MySQL>= 5.6.8，默認(rèn)禁止
query_cache_limit = 1M
query_cache_min_res_unit = 4K
query_cache_size = 1M
query_cache_wlock_invalidate = 0 ##默認(rèn)0，可以直接從query cache中取出結(jié)果
4 MySQL Server其他常用優(yōu)化
網(wǎng)絡(luò)連接與連接線(xiàn)程：
max_connections：整個(gè)MySQL允許的最大連接數(shù)；一般來(lái)說(shuō)500 到800 左右是一個(gè)比較合適的參考值，默認(rèn)為151，可以依照Max_used_connections/ max_connections * 100% ≈ 85%。
·net_buffer_length：網(wǎng)絡(luò)包傳輸中，傳輸消息之前的net buffer 初始化大小；系統(tǒng)默認(rèn)大小為16KB，一般來(lái)說(shuō)可以滿(mǎn)足大多數(shù)場(chǎng)景
max_allowed_packet：在網(wǎng)絡(luò)傳輸中，一次傳消息輸量的最大值；這個(gè)參數(shù)與net_buffer_length 相對(duì)應(yīng)，只不過(guò)是net buffer 的最大值。系統(tǒng)默認(rèn)值為16MB，最大值是1GB，必須設(shè)定為1024 的倍
數(shù)，單位為字節(jié)。當(dāng)與大的BLOB字段一起工作，可以適當(dāng)增加。
·back_log：在MySQL 的連接請(qǐng)求等待隊(duì)列中允許存放的最大連接請(qǐng)求數(shù)。 (MySQL>= 5.6.6)默認(rèn)為-1 (autosized)，初始為50 + (max_connections / 5)，封頂為900
·thread_cache_size：Thread Cache 池中應(yīng)該存放的連接線(xiàn)程數(shù)。一般來(lái)說(shuō)可能50 到100 之間應(yīng)該就可以了。默認(rèn)為-1 (autosized)，初始為 8 + (max_connections / 100)，封頂為100
·thread_stack：每個(gè)連接線(xiàn)程被創(chuàng)建的時(shí)候，MySQL 給他分配線(xiàn)程使用的堆大小。使用系統(tǒng)的默認(rèn)值（256KB）基本上可以所有的普通應(yīng)用環(huán)境。
Threads_Cache_Hit =(Connections - Threads_created) / Connections * 100%
Table Cache相關(guān)的優(yōu)化：
table_open_cache：設(shè)置系統(tǒng)中Cache的打開(kāi)表文件描述符的數(shù)量，默認(rèn)2000, (MySQL>= 5.6.12, autosized)。
Sort Buffer和Join Buffer：
·join_buffer_size：當(dāng)Join是ALL，index，rang或者index_merge 的時(shí)候使用的Buffer；一般設(shè)置為1-2M即可。
·sort_buffer_size：系統(tǒng)中對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候使用的Buffer；一般設(shè)置設(shè)置為2-4M即可。
例如：
max_connections = 800
net_buffer_length = 16K
max_allowed_packet = 32M
back_log = -1 ##默認(rèn)為-1 (autosized)，初始為 50 + (max_connections / 5)，封頂為900
thread_cache_size = -1 ##默認(rèn)為-1 (autosized)，初始為 8 + (max_connections / 100)，封頂為100
thread_stack = 256K
table_open_cache = 2000 ## 默認(rèn)2000，MySQL>= 5.6.12, autosized
join_buffer_size = 2M
sort_buffer_size = 4M
5 MyISAM常用優(yōu)化
索引緩存優(yōu)化：
·key_buffer_size：索引緩存大??；一般來(lái)說(shuō)，如果我們的MySQL 是運(yùn)行在32 位平臺(tái)紙上，此值建議不要超過(guò)2GB 大小。如果是運(yùn)行在64 位平臺(tái)紙上則不用考慮此限制，但也最好不要超過(guò)4GB。(只適用于MyISAMServer)
，索引緩存中的Cache Block Size，默認(rèn)即可；
Key_buffer_UsageRatio = (1 -Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *100%
Key_Buffer_Read_HitRatio = (1 -Key_reads/Key_read_requests) * 100%
Key_Buffer_Write_HitRatio= (1 - Key_writes/Key_Write_requests) * 100%
表讀取緩存優(yōu)化：
read_buffer_size，以SequentialScan 方式掃描表數(shù)據(jù)時(shí)候使用的Buffer；每個(gè)Thread進(jìn)行Sequential Scan 的時(shí)候都會(huì)產(chǎn)生該Buffer，所以在設(shè)置的時(shí)候盡量不要太高，避免因?yàn)椴l(fā)太大造成內(nèi)存不夠。系統(tǒng)默認(rèn)為128KB，最大為2GB，設(shè)置的值必須是4KB的倍數(shù)，否則系統(tǒng)會(huì)自動(dòng)更改成小于設(shè)置值的最大的4KB 的倍數(shù)?？梢試L試適當(dāng)調(diào)大此參數(shù)看是否能夠改善全表掃描的性能，一般設(shè)置為2MB即可。
read_rnd_buffer_size，進(jìn)行RandomScan 的時(shí)候使用的Buffer；read_rnd_buffer_size的默認(rèn)值256KB，最大值為4G。一般來(lái)說(shuō)，read_rnd_buffer_size 值的適當(dāng)調(diào)大，對(duì)提高ORDER BY 操作的性能有一定的效果。一般設(shè)置為read_rnd_buffer_size= 8M
并發(fā)優(yōu)化：
·concurrent_insert：提高INSERT 操作和SELECT 之間的并發(fā)處理，使二者盡可能并行。大部分情況下concurrent_insert 的值都被設(shè)置為1，當(dāng)表中沒(méi)有刪除記錄留下的空余空間的時(shí)候都可以在尾部并行插入。這其實(shí)也是MyISAM 的默認(rèn)設(shè)置。如果我們的系統(tǒng)主要以寫(xiě)為主，尤其是有大量的INSERT 的時(shí)候。為了盡可能提高INSERT 的效率，我們可以將concurrent_insert 設(shè)置為2，也就是告訴MyISAM，不管在表中是否有刪除行留下的空余空間，都在尾部進(jìn)行并發(fā)插入，使INSERT 和SELECT 能夠互不干擾。默認(rèn)設(shè)置AUTO，保留默認(rèn)設(shè)置即可。
例如：
key_buffer_size = 512M ##此值只對(duì)MyISAM表有效
read_buffer_size = 2M
read_rnd_buffer_size = 8M
concurrent_insert = AUTO
常用優(yōu)化
Innodb緩存相關(guān)優(yōu)化：
·innodb_buffer_pool_size：設(shè)置Innodb 最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是緩存用戶(hù)表及索引數(shù)據(jù)的最主要緩存空間，對(duì)Innodb 整體性能影響也最大。一般為設(shè)置Innodb 的Buffer Pool 設(shè)置為整個(gè)系統(tǒng)物理內(nèi)存的之間
innodb_log_buffer_size：這個(gè)參數(shù)就是用來(lái)設(shè)置Innodb 的Log Buffer 大小的，系統(tǒng)默認(rèn)值為1MB。Log Buffer的主要作用就是緩沖Log 數(shù)據(jù)，提高寫(xiě)Log 的IO 性能。一般來(lái)說(shuō)，如果你的系統(tǒng)不是寫(xiě)負(fù)載非常高且以大事務(wù)居多的話(huà)，8MB 以?xún)?nèi)的大小就完全足夠了。
innodb_additional_mem_pool_size：是用于存放Innodb的字典信息和其他一些內(nèi)部結(jié)構(gòu)所需要的內(nèi)存空間。一個(gè)常規(guī)的幾百個(gè)Innodb 表的MySQL，如果不是每個(gè)表都是上百個(gè)字段的話(huà)，20MB 內(nèi)存已經(jīng)足夠了。
innodb_doublewrite：Innodb 在將數(shù)據(jù)同步到數(shù)據(jù)文件進(jìn)行持久化之前，首先會(huì)將需要同步的內(nèi)容寫(xiě)入存在于表空間中的系統(tǒng)保留的存儲(chǔ)空間，也就是被我們稱(chēng)之為Double Write Buffer 的地方，然后再將數(shù)據(jù)進(jìn)行文件同步。默認(rèn)ON即可。
innodb_adaptive_hash_index：淺顯一點(diǎn)就是給Buffer Pool 中的數(shù)據(jù)做的索引，保持默認(rèn)即可。
事務(wù)優(yōu)化：
·innodb_flush_log_at_trx_commit：一般來(lái)說(shuō)，如果完全不能接受數(shù)據(jù)的丟失，那么我們肯定會(huì)通過(guò)犧牲一定的性能來(lái)?yè)Q取數(shù)據(jù)的安全性，選擇設(shè)置為1。而如果我們可以丟失很少量的數(shù)據(jù)(比如說(shuō)1 秒之內(nèi))，那么我們可以設(shè)置為0。當(dāng)然，如果大家覺(jué)得我們的OS 足夠穩(wěn)定，主機(jī)硬件設(shè)備，而且主機(jī)的供電系統(tǒng)也足夠安全，我們也可以將innodb_flush_log_at_trx_commit設(shè)置為2 讓系統(tǒng)的整體性能盡可能的高。（說(shuō)明：如果是游戲服務(wù)器，建議此值設(shè)置為2；如果是對(duì)數(shù)據(jù)安全要求極高的應(yīng)用，建議設(shè)置為1；設(shè)置為0性能最高，但如果發(fā)生故障，數(shù)據(jù)可能會(huì)有丟失的危險(xiǎn)?。?div style="height:15px;">
innodb_flush_method：如果磁盤(pán)是通過(guò)RAID 卡做了硬件級(jí)別的RAID，建議可以使用O_DIRECT，可以一定程度上提高IO 性能。
·innodb_thread_concurrency：主要控制Innodb 內(nèi)部的并發(fā)處理線(xiàn)程數(shù)量的最大值，默認(rèn)為0，完全讓Innodb自己根據(jù)實(shí)際需要?jiǎng)?chuàng)建并行線(xiàn)程，而且在不少場(chǎng)景下設(shè)置為0 還是一個(gè)非常不錯(cuò)的選擇，尤其是當(dāng)系統(tǒng)寫(xiě)IO 壓力較大的時(shí)候。過(guò)高的值可能導(dǎo)致線(xiàn)程的互斥。
可以通過(guò)innodb_log_group_home_dir參數(shù)來(lái)指定Innodb 日志存放位置，同時(shí)再通過(guò)設(shè)置數(shù)據(jù)文件位置innodb_data_home_dir 參數(shù)來(lái)告訴Innodb 我們希望將數(shù)據(jù)文件存放在哪里。
/usr/bin/pt-mysql-summary --user=root --password=root --save-samples=/usr/tuzhen/pt-mysql-summary-2
檢查下最大的過(guò)往使用連接數(shù)，這個(gè)值在max_connections的85%左右是比較合適的，如果過(guò)高則是max_connections過(guò)少或者系統(tǒng)負(fù)荷過(guò)高了。
如果正在運(yùn)行的語(yǔ)句太多，運(yùn)行時(shí)間太長(zhǎng)，表示MySQL效率有問(wèn)題。必要的時(shí)候可以將對(duì)應(yīng)的進(jìn)程kill掉。
