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

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

MyISAM存儲引擎適用場景：
● 只讀（或者寫較少）
● 表較叙木俊（可以接受長時間進(jìn)行修復(fù)操作）

MyISAM引擎文件：
tbl_name.frm 表格式定義
tbl_name.MYD 數(shù)據(jù)文件
tbl_name.MYI 索引文件

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

InnoDB數(shù)據(jù)庫文件：
● 所有InnoDB表的數(shù)據(jù)和索引放置于同一個表空間中
● 每個表單獨(dú)使用一個表空間存儲表的數(shù)據(jù)和索引

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

查詢緩存原理：
緩存SELECT操作或預(yù)處理查詢的結(jié)果集和SQL語句，當(dāng)有新的SELECT語句或預(yù)處理查詢語句請求绍妨，先去查詢緩存润脸，判斷是否存在可用的記錄集，判斷標(biāo)準(zhǔn)：與緩存的SQL語句他去，是否完全一樣毙驯，區(qū)分大小寫

優(yōu)缺點(diǎn)：
不需要對SQL語句做任何解析和執(zhí)行，當(dāng)然語法解析必須通過在先灾测，直接從Query Cache中獲得查詢結(jié)果爆价，提高查詢性能
查詢緩存的判斷規(guī)則，不夠智能媳搪，也即提高了查詢緩存的使用門檻铭段，降低效率
查詢緩存的使用，會增加檢查和清理Query Cache中記錄集的開銷

查詢緩存的工作流程：
命中條件：
緩存存在內(nèi)存中的一個hash表中,通過查詢SQL,查詢數(shù)據(jù)庫等作為key.在判斷是否命中前,MySQL不會解析SQL,而是直接使用SQL去查詢緩存,判斷標(biāo)準(zhǔn)：與緩存的SQL語句蛾号，是否完全一樣稠项，區(qū)分大小寫SQL任何字符上的不同,如空格,注釋,都會導(dǎo)致緩存不命中.

1. 服務(wù)器接收SQL,以SQL的語句為key查找緩存表(區(qū)分大小寫和空格)
2. 如果找到了緩存,則直接返回緩存(命中緩存)
3. 如果沒有找到緩存,則執(zhí)行SQL查詢,包括原來的SQL解析,優(yōu)化等.
4. 執(zhí)行完SQL查詢結(jié)果以后,將SQL查詢結(jié)果存入緩存表(額外性能消耗)

SELECT語句的緩存控制：
● SQL_CACHE：顯式指定存儲查詢結(jié)果于緩存之中
● SQL_NO_CACHE：顯式查詢結(jié)果不予緩存
● query_cache_type參數(shù)變量
● query_cache_type的值為OFF或0時，查詢緩存功能關(guān)閉
● query_cache_type的值為ON或1時鲜结，查詢緩存功能打開展运，SELECT的結(jié)果符合緩存條件即會緩存，否則精刷，不予緩存拗胜，顯式指定SQL_NO_CACHE，不予緩存怒允，此為默認(rèn)值
● query_cache_type的值為DEMAND或2時埂软，查詢緩存功能按需進(jìn)行，顯式指定SQL_CACHE的SELECT語句才會緩存；其它均不予緩存

查詢緩存相關(guān)的狀態(tài)變量：

SHOW GLOBAL STATUS LIKE 'Qcache%';

● Qcache_free_blocks：處于空閑狀態(tài) Query Cache中內(nèi)存 Block 數(shù)
● Qcache_total_blocks：Query Cache 中總Block 勘畔，當(dāng)Qcache_free_blocks相對此值較大時所灸，可能用內(nèi)存碎片，執(zhí)行FLUSH QUERY CACHE清理碎片
● Qcache_free_memory：處于空閑狀態(tài)的 Query Cache 內(nèi)存總量
● Qcache_hits：Query Cache 命中次數(shù)
● Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數(shù)炫七，即沒有命中的次數(shù)
● Qcache_lowmem_prunes：記錄因為內(nèi)存不足而被移除出查詢緩存的查詢數(shù)
● Qcache_not_cached：沒有被 Cache 的 SQL 數(shù)爬立，包括無法被 Cache 的 SQL 以及由于query_cache_type 設(shè)置的不會被 Cache 的 SQL語句
● Qcache_queries_in_cache：在 Query Cache 中的 SQL 數(shù)量

命中率和內(nèi)存使用率估算:
● 查詢緩存中內(nèi)存塊的最小分配單位query_cache_min_res_unit ：

(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

● 查詢緩存命中率 ：

Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%

● 查詢緩存內(nèi)存使用率：

(query_cache_size – qcache_free_memory) / query_cache_size * 100%

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

MySQL 支持豐富的日志類型侠驯，如下：
● 事務(wù)日志：transaction log
事務(wù)日志的寫入類型為“追加”，因此其操作為“順序IO”奕巍；通常也被稱為：預(yù)寫式日志 write ahead logging
事務(wù)日志文件： ib_logfile0吟策， ib_logfile1
● 錯誤日志 error log
● 通用日志 general log
● 慢查詢?nèi)罩?slow query log
● 二進(jìn)制日志 binary log
● 中繼日志 reley log，在主從復(fù)制架構(gòu)中的止，從服務(wù)器用于保存從主服務(wù)器的二進(jìn)制日志中讀取的事件

事務(wù)日志
事務(wù)日志：transaction log
事務(wù)型存儲引擎自行管理和使用檩坚，建議和數(shù)據(jù)文件分開存放，redo log和undo log

Innodb事務(wù)日志相關(guān)配置：
show variables like '%innodb_log%';

innodb_log_file_size   50331648 每個日志文件大小
innodb_log_files_in_group  2     日志組成員個數(shù)
innodb_log_group_home_dir ./ 事務(wù)文件路徑
innodb_flush_log_at_trx_commit 默認(rèn)為1

事務(wù)日志性能優(yōu)化

innodb_flush_log_at_trx_commit=0|1|2

1 此為默認(rèn)值冲杀，日志緩沖區(qū)將寫入日志文件效床，并在每次事務(wù)后執(zhí)行刷新到磁盤。 這是完全遵守ACID特性
0 提交時沒有寫磁盤的操作; 而是每秒執(zhí)行一次將日志緩沖區(qū)的提交的事務(wù)寫入刷新到磁盤权谁。 這樣可提供更好的性能，但服務(wù)器崩潰可能丟失最后一秒的事務(wù)
2 每次提交后都會寫入OS的緩沖區(qū)憋沿，但每秒才會進(jìn)行一次刷新到磁盤文件中旺芽。 性能比0略差一些，但操作系統(tǒng)或停電可能導(dǎo)致最后一秒的交易丟失

高并發(fā)業(yè)務(wù)行業(yè)最佳實(shí)踐辐啄，是使用第三種折衷配置（=2）
設(shè)置為1采章，同時sync_binlog = 1表示最高級別的容錯

** 錯誤日志**
mysqld啟動和關(guān)閉過程中輸出的事件信息
mysqld運(yùn)行中產(chǎn)生的錯誤信息
event scheduler運(yùn)行一個event時產(chǎn)生的日志信息
在主從復(fù)制架構(gòu)中的從服務(wù)器上啟動從服務(wù)器線程時產(chǎn)生的信息
錯誤文件路徑

SHOW GLOBAL VARIABLES LIKE 'log_error'

通用日志
通用日志：記錄對數(shù)據(jù)庫的通用操作，包括:錯誤的SQL語句
通用日志可以保存在：file（默認(rèn)值）或 table（mysql.general_log表）
通用日志相關(guān)設(shè)置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

慢查詢?nèi)罩?/strong>
慢查詢?nèi)罩荆河涗泩?zhí)行查詢時長超出指定時長的操作
慢查詢相關(guān)變量

slow_query_log=ON|OFF #開啟或關(guān)閉慢查詢壶辜，支持全局和會話悯舟，只有全局設(shè)置才會生成慢查詢文件
long_query_time=N #慢查詢的閥值，單位秒
slow_query_log_file=HOSTNAME-slow.log  #慢查詢?nèi)罩疚募?log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查詢類型且查詢時長超過long_query_time砸民，則記錄日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引掃描抵怎，不論是否達(dá)到慢查詢閥值的語
句是否記錄日志，默認(rèn)OFF岭参，即不記錄
log_slow_rate_limit = 1 #多少次查詢才記錄反惕，mariadb特有
log_slow_verbosity= Query_plan,explain #記錄內(nèi)容
log_slow_queries = OFF    #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已刪除