1、總結(jié)mysql常見的存儲引擎以及特點(diǎn)。

MyISAM 存儲引擎

引擎特點(diǎn)

• 不支持事務(wù)
• 表級鎖定
• 讀寫相互阻塞，寫入是不能讀，讀時(shí)不能寫
• 只緩存索引
• 不支持外鍵約束
• 讀取數(shù)據(jù)較快，占用資源較少
• 不支持MVCC（多版本并發(fā)控制機(jī)制）高并發(fā)
• 崩潰恢復(fù)性較差
• MySQL5.5.5 前默認(rèn)的數(shù)據(jù)庫引擎

適用場景

• 只讀（或者寫較少）
• 表較?。梢越邮荛L時(shí)間進(jìn)行修復(fù)操作）

InnoDB 引擎

引擎特點(diǎn)

• 行級鎖
• 支持事務(wù)，適合處理大量短期的事務(wù)
• 讀寫阻塞與事務(wù)隔離級別相關(guān)
• 可緩存數(shù)據(jù)和索引
• 支持聚簇索引
• 崩潰恢復(fù)性好
• 支持MVCC高并發(fā)
• 從MySQL5.5后支持全文索引
• 從MySQL5.5.5開始為默認(rèn)的數(shù)據(jù)庫引擎

其他存儲引擎

• Performance_Schema：Performance_Schema數(shù)據(jù)庫使用
• Memory ：將所有數(shù)據(jù)存儲在RAM中，以便在需要快速查找參考和其他類似數(shù)據(jù)的環(huán)境中進(jìn)行快速訪問。適用存放臨時(shí)數(shù)據(jù)。引擎以前被稱為HEAP引擎
• MRG_MyISAM：使MySQL DBA或開發(fā)人員能夠?qū)σ幌盗邢嗤腗yISAM表進(jìn)行邏輯分組，并將它們作為一個對象引用。適用于VLDB(Very Large Data Base)環(huán)境，如數(shù)據(jù)倉庫
• Archive ：為存儲和檢索大量很少參考的存檔或安全審核信息，只支持SELECT和INSERT操作；支持行級鎖和專用緩存區(qū)
• Federated聯(lián)合：用于訪問其它遠(yuǎn)程MySQL服務(wù)器一個代理，它通過創(chuàng)建一個到遠(yuǎn)程MySQL服務(wù)器的客戶端連接，并將查詢傳輸?shù)竭h(yuǎn)程服務(wù)器執(zhí)行，而后完成數(shù)據(jù)存取，提供鏈接單獨(dú)MySQL服務(wù)器的能力，以便從多個物理服務(wù)器創(chuàng)建一個邏輯數(shù)據(jù)庫。非常適合分布式或數(shù)據(jù)集市環(huán)境
• BDB：可替代InnoDB的事務(wù)引擎，支持COMMIT、ROLLBACK和其他事務(wù)特性
• Cluster/NDB：MySQL的簇式數(shù)據(jù)庫引擎，尤其適合于具有高性能查找要求的應(yīng)用程序，這類查找需求還要求具有最高的正常工作時(shí)間和可用性
• CSV：CSV存儲引擎使用逗號分隔值格式將數(shù)據(jù)存儲在文本文件中。可以使用CSV引擎以CSV格式導(dǎo)入和導(dǎo)出其他軟件和應(yīng)用程序之間的數(shù)據(jù)交換
• BLACKHOLE ：黑洞存儲引擎接受但不存儲數(shù)據(jù)，檢索總是返回一個空集。該功能可用于分布式數(shù)據(jù)庫設(shè)計(jì)，數(shù)據(jù)自動復(fù)制，但不是本地存儲
• example：“stub”引擎，它什么都不做。可以使用此引擎創(chuàng)建表，但不能將數(shù)據(jù)存儲在其中或從中檢索。目的是作為例子來說明如何開始編寫新的存儲引擎

2、MySQL查詢緩存優(yōu)化總結(jié)。

概述
mysql查詢緩存在數(shù)據(jù)庫優(yōu)化可以起到很大的作用

一、緩存條件，原理
MySQL Query Cache是用來緩存我們所執(zhí)行的SELECT語句以及該語句的結(jié)果集，MySql在實(shí)現(xiàn)Query Cache的具體技術(shù)細(xì)節(jié)上類似典型的KV存儲，就是將SELECT語句和該查詢語句的結(jié)果集做了一個HASH映射并保存在一定的內(nèi)存區(qū)域中。當(dāng)客戶端發(fā)起SQL查詢時(shí)，Query Cache的查找邏輯是，先對SQL進(jìn)行相應(yīng)的權(quán)限驗(yàn)證，接著就通過Query Cache來查找結(jié)果（注意必須是完全相同，即使多一個空格或者大小寫不同都認(rèn)為不同，即使完全相同的SQL，如果使用不同的字符集、不同的協(xié)議等也會被認(rèn)為是不同的查詢而分別進(jìn)行緩存）。它不需要經(jīng)過Optimizer模塊進(jìn)行執(zhí)行計(jì)劃的分析優(yōu)化，更不需要發(fā)生同任何存儲引擎的交互，減少了大量的磁盤IO和CPU運(yùn) 算，所以有時(shí)候效率非常高。

查詢緩存的工作流程如下:

1：命中條件

緩存存在一個hash表中,通過查詢SQL,查詢數(shù)據(jù)庫,客戶端協(xié)議等作為key.在判斷是否命中前,MySQL不會解析SQL,而是直接使用SQL去查詢緩存,SQL任何字符上的不同,如空格,注釋,都會導(dǎo)致緩存不命中.

如果查詢中有不確定數(shù)據(jù),例如CURRENT_DATE()和NOW()函數(shù),那么查詢完畢后則不會被緩存.所以,包含不確定數(shù)據(jù)的查詢是肯定不會找到可用緩存的

2：工作流程

1. 服務(wù)器接收SQL,以SQL和一些其他條件為key查找緩存表(額外性能消耗)
2. 如果找到了緩存,則直接返回緩存(性能提升)
3. 如果沒有找到緩存,則執(zhí)行SQL查詢,包括原來的SQL解析,優(yōu)化等.
4. 執(zhí)行完SQL查詢結(jié)果以后,將SQL查詢結(jié)果存入緩存表(額外性能消耗)

二、相關(guān)SQL語句
2.1、查看SQL緩存參數(shù)：
show variables like '%query_cache%';

mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

其中各個參數(shù)的意義如下：

Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)。數(shù)目大說明可能有碎片。FLUSH QUERY CACHE會對緩存中的碎片進(jìn)行整理，從而得到一個空閑塊。 Qcache_free_memory：緩存中的空閑內(nèi)存。 Qcache_hits：每次查詢在緩存中命中時(shí)就增大 Qcache_inserts：每次插入一個查詢時(shí)就增大。命中次數(shù)除以插入次數(shù)就是不中比率。 Qcache_lowmem_prunes：緩存出現(xiàn)內(nèi)存不足并且必須要進(jìn)行清理以便為更多查詢提供空間的次數(shù)。這個數(shù)字最好長時(shí)間來看;如果這個 數(shù)字在不斷增長，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少。(上面的 free_blocks和free_memory可以告訴您屬于哪種情況) Qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。 Qcache_queries_in_cache：當(dāng)前緩存的查詢(和響應(yīng))的數(shù)量。 Qcache_total_blocks：緩存中塊的數(shù)量。
2.2、開啟SQL緩存：set global query_cache_type = 1;
2.3、關(guān)閉SQL緩存：set global query_cache_type = 0;
2.4、設(shè)置緩存空間：set global query_cache_size = 1024102464 （64M）
2.5、固定SQL語句聲明不適用緩存：select sql_no_cache * from 表名
注意：改變SQL語句的大小寫或者數(shù)據(jù)表有數(shù)據(jù)改動，則不會調(diào)用緩存。

2.6、配置查詢緩存
vim /etc/mysql/mysql.conf.d/mysqld
query_cache_size=300M
query_cache_type=1

mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 314572800 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

三、清除緩存
mysql的FLUSH句法(清除緩存）

FLUSH flush_option [,flush_option]
如果你想要清除一些MySQL使用內(nèi)部緩存，你應(yīng)該使用FLUSH命令。為了執(zhí)行FLUSH，你必須有reload權(quán)限。

flush_option可以是下列任何東西：

HOSTS 這個用的最多，經(jīng)常碰見。主要是用來清空主機(jī)緩存表。如果你的某些主機(jī)改變IP數(shù)字，或如果你得到錯誤消息Host ... isblocked，你應(yīng)該清空主機(jī)表。當(dāng)在連接MySQL服務(wù)器時(shí)，對一臺給定的主機(jī)有多于 max_connect_errors個錯誤連續(xù)不斷地發(fā)生，MySQL為了安全的需要將會阻止該主機(jī)進(jìn)一步的連接請求。清空主機(jī)表允許主機(jī)再嘗試連接。 LOGS 關(guān)閉當(dāng)前的二進(jìn)制日志文件并創(chuàng)建一個新文件，新的二進(jìn)制日志文件的名字在當(dāng)前的二進(jìn)制文件的編號上加1。 PRIVILEGES 這個也是經(jīng)常使用的，每當(dāng)重新賦權(quán)后，為了以防萬一，讓新權(quán)限立即生效，一般都執(zhí)行一把，目地是從數(shù)據(jù)庫授權(quán)表中重新裝載權(quán)限到緩存中。 TABLES 關(guān)閉所有打開的表，同時(shí)該操作將會清空查詢緩存中的內(nèi)容。 FLUSH TABLES WITH READ LOCK 關(guān)閉所有打開的表，同時(shí)對于所有數(shù)據(jù)庫中的表都加一個讀鎖，直到顯示地執(zhí)行unlock tables，該操作常常用于數(shù)據(jù)備份的時(shí)候。 STATUS 重置大多數(shù)狀態(tài)變量到0。 MASTER 刪除所有的二進(jìn)制日志索引文件中的二進(jìn)制日志文件，重置二進(jìn)制日志文件的索引文件為空，創(chuàng)建一個新的二進(jìn)制日志文件,不過這個已經(jīng)不推薦使用，改成reset master 了。可以想象，以前自己是多土啊，本來一條簡單的命令就可以搞定的，卻要好幾條命令來，以前的做法是先查出來當(dāng)前的二進(jìn)制日志文件名，再用purge 操作。 QUERY CACHE 重整查詢緩存，消除其中的碎片，提高性能，但是并不影響查詢緩存中現(xiàn)有的數(shù)據(jù)，這點(diǎn)和Flush table 和Reset Query Cache（將會清空查詢緩存的內(nèi)容）不一樣的。 SLAVE 類似于重置復(fù)制吧，讓從數(shù)據(jù)庫忘記主數(shù)據(jù)庫的復(fù)制位置，同時(shí)也會刪除已經(jīng)下載下來的relay log,與Master一樣，已經(jīng)不推薦使用，改成Reset Slave了。這個也很有用的。
一般來講，F(xiàn)lush操作都會記錄在二進(jìn)制日志文件中，但是FLUSH LOGS、FLUSH MASTER、FLUSH SLAVE、FLUSH TABLES WITH READ LOCK不會記錄，因此上述操作如果記錄在二進(jìn)制日志文件中話，會對從數(shù)據(jù)庫造成影響。注意：Reset操作其實(shí)扮演的是一個Flush操作的增強(qiáng)版的角色。

四、緩存的內(nèi)存管理
緩存會在內(nèi)存中開辟一塊內(nèi)存(query_cache_size)來維護(hù)緩存數(shù)據(jù),其中有大概40K的空間是用來維護(hù)緩存的元數(shù)據(jù)的,例如空間內(nèi)存,數(shù)據(jù)表和查詢結(jié)果的映射,SQL和查詢結(jié)果的映射等.

MySQL將這個大內(nèi)存塊分為小的內(nèi)存塊(query_cache_min_res_unit),每個小塊中存儲自身的類型,大小和查詢結(jié)果數(shù)據(jù),還有指向前后內(nèi)存塊的指針.

MySQL需要設(shè)置單個小存儲塊的大小,在SQL查詢開始(還未得到結(jié)果)時(shí)就去申請一塊空間,所以即使你的緩存數(shù)據(jù)沒有達(dá)到這個大小,也需要用這 個大小的數(shù)據(jù)塊去存(這點(diǎn)跟Linux文件系統(tǒng)的Block一樣).如果結(jié)果超出這個內(nèi)存塊的大小,則需要再去申請一個內(nèi)存塊.當(dāng)查詢完成發(fā)現(xiàn)申請的內(nèi)存 塊有富余,則會將富余的空間釋放掉,這就會造成內(nèi)存碎片問題

在查詢開始時(shí)申請分配內(nèi)存Block需要鎖住整個空閑內(nèi)存區(qū),所以分配內(nèi)存塊是非常消耗資源的.注意這里所說的分配內(nèi)存是在MySQL初始化時(shí)就開辟的那塊內(nèi)存上分配的.

五、緩存的使用時(shí)機(jī) & 性能
衡量打開緩存是否對系統(tǒng)有性能提升是一個很難的話題

1. 通過緩存命中率判斷, 緩存命中率 = 緩存命中次數(shù) (Qcache_hits) / 查詢次數(shù) (Com_select)
2. 通過緩存寫入率, 寫入率 = 緩存寫入次數(shù) (Qcache_inserts) / 查詢次數(shù) (Qcache_inserts)
3. 通過命中-寫入率 判斷, 比率 = 命中次數(shù) (Qcache_hits) / 寫入次數(shù) (Qcache_inserts), 高性能MySQL中稱之為比較能反映性能提升的指數(shù),一般來說達(dá)到3:1則算是查詢緩存有效,而最好能夠達(dá)到10:1

任何事情過猶不及，尤其對于某些寫頻繁的系統(tǒng)，開啟Query Cache功能可能并不能讓系統(tǒng)性能有提升，有時(shí)反而會有下降。原因是MySql為了保證Query Cache緩存的內(nèi)容和實(shí)際數(shù)據(jù)絕對一致，當(dāng)某個數(shù)據(jù)表發(fā)生了更新、刪除及插入操作，MySql都會強(qiáng)制使所有引用到該表的查詢SQL的Query Cache失效。對于密集寫操作，啟用查詢緩存后很可能造成頻繁的緩存失效，間接引發(fā)內(nèi)存激增及CPU飆升，對已經(jīng)非常忙碌的數(shù)據(jù)庫系統(tǒng)這是一種極大的負(fù)擔(dān)。

**六、查詢緩存問題分析 **
分析和配置查詢緩存

總結(jié)
這里要注意Query Cache因MySql的存儲引擎不同而實(shí)現(xiàn)略有差異，比如MyISAM，緩存的結(jié)果集存儲在OS Cache中，而最流行的InnoDB則放在Buffer Pool中。

3、MySQL各類日志總結(jié)。

• 日志類型：

錯誤日志(error log)
常規(guī)日志(general log)
二進(jìn)制日志(bin log)
中繼日志(relay log)
慢日志(slow log)
InnoDB引擎的redo log

錯誤日志

log-error = error.log
不加存儲路徑存儲到datadir下，如果不指定名字是hostname.err
記錄MySQL啟動關(guān)閉，運(yùn)行中的異?；蚴侵匾崾拘畔?br> 在生產(chǎn)環(huán)境中，監(jiān)控這個日志

全量日志(常規(guī)日志)

general_log = OFF|ON
general_log_file = general.log
不指定路徑存儲到datadir下，如果不指定名字是hostname.log
開啟后，記錄client和數(shù)據(jù)庫的所有請求

二進(jìn)行日志

最大文件數(shù)為2的32次方個
log-bin = /data/mysql/mysql3306/logs/mysql-bin
設(shè)置二進(jìn)制日志的存儲位置及prefix名字
用記錄數(shù)據(jù)庫寫入操作的日志，可以用于備份或是master/slave的復(fù)制，必須有server-id
如果沒有可以通過修改內(nèi)存里的值加上，但是有風(fēng)險(xiǎn)
不能太快產(chǎn)生，盡量大些，5分鐘左右產(chǎn)生一個日志
log_bin = /data/mysql/mysql3306/log/mysql-bin 是否開啟binlog
log_bin_index = /data/mysql/mysql3306/log/mysql-bin.index
指定binlog的一個索引文件，默認(rèn)是在datadir形成log_bin的prefix名index
binlog_do_db = thunder
用于指定只記錄那個庫的二進(jìn)制日志(建議不要用)
max_binlog_size = 500M
指定binlog的大小為500M一個文件，默認(rèn)是1G
expire-logs-days = 5 指定保留binlog的天數(shù)
binlog_format = row
指定binlog的日志格式，支持statement,row,mixed格式
statement格式記錄原生的sql
row格式里沒有，但5.6后增加了一個query event可以看到原來的sql
binlog_rows_query_log_events 默認(rèn)是off
mixed格式是兩種形式的混合體，DDL語句記錄statement格式，DML語句記錄row格式
推薦使用row格式
binlog_row_image = full
控制日志中binlog的詳細(xì)相關(guān)的程度，支持full(default),minimal,nobolb
minimal:只記錄變化的行和唯一標(biāo)識列
nobolb:除了blob,text其它列都記
binlog_error_action = abort_server
當(dāng)遇到mysql不能寫binlog時(shí)，報(bào)出異常，默認(rèn)是ignore error不報(bào)錯
binlog_direct_non_transactional_updates = on
對于非事務(wù)引擎表，直接走日志，不走2pc提交，默認(rèn)是不支持
binlog_order_commit = on
按順序?qū)懭肴罩?br> binlog_cache_size=1M 已經(jīng)很大,此參數(shù)表示binlog使用的內(nèi)存大小，可以通過狀態(tài)變量binlog_cache_use和bin_cache_disk_use 來幫助測試
binlog_cache_use:使用二進(jìn)制日志緩存的事務(wù)數(shù)量
binlog_cache_disk_use:使用二進(jìn)制日志緩存但超過binlog_cache_size值并使用臨時(shí)文件來保存事務(wù)中的語句的事務(wù)數(shù)量
binlog_checksum (5.6.2引入)在高版本上修改
mysql5.6.6后默認(rèn)是crc32，之前版本是none
在啟用這個參數(shù)之前對于日志的完整校驗(yàn)就是通過對比長度，引入新的方式后，利用新方法對內(nèi)容進(jìn)行校驗(yàn)
binlog_rows_query_log_events (5.6.2引入)
只作用于RBR格式，默認(rèn)不啟用
如果啟用，會把用戶寫直的原生態(tài)DML操作記錄到binlog中
log_bin_use_v1_row_events (5.6.6引入)
默認(rèn)是0，如果使用1是使用Version1的格式，mysql5.5可以認(rèn)出來的形式，如果0是5.6.6后使用的version2格式
sync_binlog = 1|0
這個參數(shù)對性能影響嚴(yán)重，數(shù)據(jù)一致性條件要求高調(diào)整為1，性能差別大概為10倍
當(dāng)事務(wù)提交后，mysql僅僅是將binlog_cache中的數(shù)據(jù)寫入binlog文件，但不執(zhí)行fsync之類的磁盤同步指令通知文件系統(tǒng)將緩存刷新到磁盤，面讓filesystem自行決定什么時(shí)候來同步，這個是性能最好的。
sync_binlog=n 在進(jìn)行n次事務(wù)提交以后，mysql將執(zhí)行一次fsync之類的磁盤同步指令，同步文件系統(tǒng)將binlog文件緩存刷新到磁盤。
mysql中默認(rèn)的設(shè)置是sync_binlog=0,即不作任何強(qiáng)制性的磁盤刷新指令，這是性能是最好的，但風(fēng)險(xiǎn)也是最大的。一旦系統(tǒng)crash，在文件系統(tǒng)緩存中的所有binlog信息都會丟失

中繼日志

relay-log = relay-bin
設(shè)置中繼日志的名稱前綴，不指定路徑默認(rèn)在datadir下
log_thread從master就讀到的日志寫到中繼日志中，供sql_thread執(zhí)行，以完成復(fù)制

慢日志

slow-query-log-file = slow.log
slow-query-log
long_query_time=1
是怎么計(jì)時(shí)的，
ddl語句 exec時(shí)間
dml語句 select從等待鎖開始計(jì)時(shí)，insert只記錄執(zhí)行時(shí)間
利用slow-query-log這個參數(shù)打開慢日志，slow-query-log-file指定慢日志的名稱，不指定路徑默認(rèn)在datadir下
建議每天一個文件，時(shí)間長可能會很大，做定時(shí)任務(wù)

Innodb的redo-log

事務(wù)處理日志
innodb_log_group_home_dir=/data/mysql/mysql3317/logs 默認(rèn)在datadir下
SSD磁盤中，日志文件不要放在SSD磁盤中，普通硬盤即可
innodb_log_file_size = 200M 512M左右即可
innodb_log_files_in_group = 3 個數(shù)
指定redo log的存儲位置及大小，文件個數(shù)
Innodb事務(wù)操作不可缺少的一個環(huán)節(jié)