MySQL的復制原理以及流程

基本原理流程瓤檐，3個線程以及之間的關聯(lián)辽剧；

1. 主：binlog線程——記錄下所有改變了數(shù)據(jù)庫數(shù)據(jù)的語句臂寝，放進master上的binlog中章鲤；
2. 從：io線程——在使用start slave 之后，負責從master上拉取 binlog 內(nèi)容咆贬，放進 自己的relay log中败徊；
3. 從：sql執(zhí)行線程——執(zhí)行relay log中的語句；

mysql中myisam與innodb的區(qū)別

5點不同

• InnoDB支持事務掏缎，MyISAM不支持皱蹦，對于InnoDB每一條SQL語言都默認封裝成事務，自動提交眷蜈，這樣會影響速度沪哺，所以最好把多條SQL語言放在begin和commit之間，組成一個事務酌儒；

• InnoDB支持外鍵辜妓，而MyISAM不支持。對一個包含外鍵的InnoDB表轉(zhuǎn)為MYISAM會失斀穸埂嫌拣；

• InnoDB是聚集索引，數(shù)據(jù)文件是和索引綁在一起的呆躲，必須要有主鍵异逐，通過主鍵索引效率很高。但是輔助索引需要兩次查詢插掂，先查詢到主鍵灰瞻，然后再通過主鍵查詢到數(shù)據(jù)腥例。因此，主鍵不應該過大酝润，因為主鍵太大燎竖，其他索引也都會很大。而MyISAM是非聚集索引要销，數(shù)據(jù)文件是分離的构回，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨立的疏咐。

• InnoDB支持MVCC, 而MyISAM不支持

• InnoDB不保存表的具體行數(shù)纤掸，執(zhí)行select count(*) from table時需要全表掃描。而MyISAM用一個變量保存了整個表的行數(shù)浑塞，執(zhí)行上述語句時只需要讀出該變量即可借跪，速度很快； 但是MyISAM只要簡單的讀出保存好的行數(shù)即可酌壕。注意的是掏愁，當count(*)語句包含 where條件時，兩種表的操作是一樣的卵牍。

• Innodb不支持全文索引果港，而MyISAM支持全文索引，查詢效率上MyISAM要高辽慕；

• 對于AUTO_INCREMENT類型的字段京腥，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中溅蛉，可以和其他字段一起建立聯(lián)合索引公浪。

• DELETE FROM table時，InnoDB不會重新建立表船侧，而是一行一行的刪除

innodb引擎的4大特性

• 插入緩沖（insert buffer)
  插入主鍵聚集索引欠气，是順序的，不需要磁盤的隨機讀染盗谩预柒；但是這也導致同一個表中的非聚集索引不是順序的，因為B+樹的特性決定了非聚集索引插入的離散型袁梗。
  插入緩存就是為提高非聚集索引的插入和更新操作的性能而做的優(yōu)化設計宜鸯，其原理將插入數(shù)據(jù)先放到內(nèi)存就直接返回上層，上層看來已經(jīng)插入成功遮怜，其實插入數(shù)據(jù)還在內(nèi)存中淋袖，內(nèi)部會觸發(fā)內(nèi)存的索引數(shù)據(jù)與物理的索引數(shù)據(jù)進行合并操作，合并時將多個插入合并到一個操作（正好一個索引頁）锯梁，這樣大大提高了對非聚集索引插入的性能即碗。

• 二次寫(double write)
  為了提升數(shù)據(jù)頁的可靠性焰情。
  寫數(shù)據(jù)頁的時候宕機怎么辦？
  重做日志剥懒，但是如果物理頁已經(jīng)損壞了怎么版内舟？
  用doubleWrite：發(fā)現(xiàn)物理頁損壞了，則找到其前面的一個副本初橘，用副本來還原當前頁验游，再重做日志。

• 自適應哈希索引(ahi)
  原先的索引是B+樹結構壁却，當查詢頻繁批狱，建立哈下阕迹可以提高效率展东，則自動構建哈希索引，提高速度炒俱。

• 異步IO(Async IO)
  同時發(fā)起多個IO請求（索引頁的掃描）盐肃，可以將多個IO請求合并為一個IO操作，同時將每個IO請求的結果進行Merge权悟。

• 刷新鄰接頁
  刷新一個臟頁的同事檢查所在區(qū)的其他頁是否需要一起刷新砸王。

2者selectcount(*)哪個更快，為什么

myisam更快峦阁，因為myisam內(nèi)部維護了一個計數(shù)器谦铃，可以直接調(diào)取。

MySQL中varchar與char的區(qū)別以及varchar(50)中的50代表的涵義

(1)榔昔、varchar與char的區(qū)別
char是一種固定長度的類型驹闰，varchar則是一種可變長度的類型

(2)、varchar(50)中50的涵義
最多存放50個字符撒会，varchar(50)和(200)存儲hello所占空間一樣嘹朗，但后者在排序時會消耗更多內(nèi)存，因為order by col采用fixed_length計算col長度(memory引擎也一樣)

(3)诵肛、int（20）中20的涵義
是指顯示字符的長度
但要加參數(shù)的屹培，最大為255，比如它是記錄行數(shù)的id,插入10筆資料怔檩，它就顯示00000000001 ~~~00000000010褪秀，當字符的位數(shù)超過11,它也只顯示11位，如果你沒有加那個讓它未滿11位就前面加0的參數(shù)薛训，它不會在前面加0
20表示最大顯示寬度為20媒吗，但仍占4字節(jié)存儲，存儲范圍不變许蓖；

(4)蝴猪、mysql為什么這么設計
對大多數(shù)應用沒有意義调衰，只是規(guī)定一些工具用來顯示字符的個數(shù)；int(1)和int(20)存儲和計算均一樣自阱；

innodb的事務與日志的實現(xiàn)方式

(1)嚎莉、有多少種日志；

• 錯誤日志：記錄出錯信息沛豌，也記錄一些警告信息或者正確的信息趋箩。
• 查詢?nèi)罩荆河涗浰袑?shù)據(jù)庫請求的信息，不論這些請求是否得到了正確的執(zhí)行加派。
• 慢查詢?nèi)罩荆涸O置一個閾值叫确，將運行時間超過該值的所有SQL語句都記錄到慢查詢的日志文件中。
• 二進制日志binlog：記錄對數(shù)據(jù)庫執(zhí)行更改的所有操作芍锦。
• 中繼日志relay log：
• 事務日志 redo log / undo log：

(2)竹勉、事物的4種隔離級別

• 讀未提交(RU)
• 讀已提交(RC)
• 可重復讀(RR)
• 串行

(3)、事務是如何通過日志來實現(xiàn)的娄琉，說得越深入越好次乓。
事務日志是通過redo和innodb的存儲引擎日志緩沖（Innodb log buffer）來實現(xiàn)的，當開始一個事務的時候孽水，會記錄該事務的lsn(log sequence number)號; 當事務執(zhí)行時票腰，會往InnoDB存儲引擎的日志
的日志緩存里面插入事務日志；當事務提交時女气，必須將存儲引擎的日志緩沖寫入磁盤（通過innodb_flush_log_at_trx_commit來控制）杏慰，也就是寫數(shù)據(jù)前，需要先寫日志炼鞠。這種方式稱為“預寫日志方式”

MySQL binlog的幾種日志錄入格式以及區(qū)別

(1)缘滥、binlog的日志格式的種類和分別
(2)、適用場景簇搅；
(3)完域、結合第一個問題，每一種日志格式在復制中的優(yōu)劣瘩将。

• Statement：
  每一條會修改數(shù)據(jù)的sql都會記錄在binlog中吟税，過程導向（沒有關注結果）。
  優(yōu)點：記錄sql語句上下文相關信息
  缺點：存儲過程姿现，或function肠仪，以及trigger的調(diào)用和觸發(fā)無法被正確復制
• Row:
  不記錄sql語句上下文相關信息，僅保存哪條記錄被修改成什么樣子备典，結果導向（不關注過程）异旧。
  優(yōu)點：僅需要記錄那一條記錄被修改成什么了。所以會非常清楚的記錄下每一行數(shù)據(jù)修改的細節(jié)提佣。
  缺點：產(chǎn)生大量的日志內(nèi)容吮蛹。
• Mixedlevel:
  是以上兩種level的混合使用荤崇，一般的語句修改使用statment格式保存binlog，如一些函數(shù)潮针，statement無法完成主從復制的操作术荤，則 采用row格式保存binlog,MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式

MySQL數(shù)據(jù)庫cpu飆升到500%的話他怎么處理？

(1)每篷、沒有經(jīng)驗的瓣戚，可以不問；
(2)焦读、有經(jīng)驗的子库，問他們的處理思路。

• 找出占用的線程殺掉矗晃，分析日志仑嗅，找問題，解決
• mysql> show processlist; 找出占用cpu的線程
• 常見問題 ：
  1. 睡眠連接過多喧兄，嚴重消耗mysql服務器資源(主要是cpu, 內(nèi)存)无畔，并可能導致mysql崩潰。
     解決辦法 ：
     mysql的配置my.ini文件中wait_timeout, 即可設置睡眠連接超時秒數(shù)吠冤，如果某個連接超時，會被mysql自然終止恭理。
     mysql> set global wait_timeout=20;
  2. 增加 tmp_table_size 值
  3. SQL語句沒有建立索引
  4. 函數(shù)計算的拯辙，放到應用層進行

sql優(yōu)化

• 使用explain，分析優(yōu)化颜价， 各item的意義涯保；
  select_type
  表示查詢中每個select子句的類型
  type
  表示MySQL在表中找到所需行的方式，又稱“訪問類型”
  possible_keys
  指出MySQL能使用哪個索引在表中找到行周伦，查詢涉及到的字段上若存在索引夕春，則該索引將被列出，但不一定被查詢使用
  key
  顯示MySQL在查詢中實際使用的索引专挪，若沒有使用索引及志，顯示為NULL
  key_len
  表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度
  ref
  表示上述表的連接匹配條件寨腔，即哪些列或常量被用于查找索引列上的值
  Extra
  包含不適合在其他列中顯示但十分重要的額外信息

• profile的意義以及使用場景
  查詢到 SQL 會執(zhí)行多少時間, 并看出 CPU/Memory 使用量, 執(zhí)行過程中 Systemlock, Table lock 花多少時間等等

備份計劃速侈，mysqldump以及xtranbackup的實現(xiàn)原理

(1)、備份計劃迫卢；
利用空閑間隔
長期全量備份
每天增量備份
刪除1個月前的備份數(shù)據(jù)

(2)倚搬、備份恢復時間；
(3)乾蛤、xtrabackup實現(xiàn)原理
在InnoDB內(nèi)部會維護一個redo日志文件每界，我們也可以叫做事務日志文件捅僵。事務日志會存儲每一個InnoDB表數(shù)據(jù)的記錄修改。當InnoDB啟動時眨层，InnoDB會檢查數(shù)據(jù)文件和事務日志命咐，并執(zhí)行兩個步驟：它應用（前滾）已經(jīng)提交的事務日志到數(shù)據(jù)文件，并將修改過但沒有提交的數(shù)據(jù)進行回滾操作谐岁。

500臺db醋奠，在最快時間之內(nèi)重啟

采用docker swarm
或者自動化配置和部署工具，如Puppet伊佃、Chef窜司、Ansible和SaltStack

innodb的讀寫參數(shù)優(yōu)化

(1)、讀取參數(shù)
global buffer pool以及 local buffer航揉；

(2)塞祈、寫入?yún)?shù)；
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size

(3)帅涂、與IO相關的參數(shù)议薪；
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0

(4)、緩存參數(shù)以及緩存的適用場景媳友。
query cache/query_cache_type

你是如何監(jiān)控你們的數(shù)據(jù)庫的斯议？你們的慢日志都是怎么查詢的？

監(jiān)控的工具有很多醇锚，例如zabbix哼御，lepus，我這里用的是lepus

你是否做過主從一致性校驗焊唬，如果有恋昼，怎么做的，如果沒有赶促，你打算怎么做液肌？

主從一致性校驗有多種工具 例如checksum、mysqldiff鸥滨、pt-table-checksum等

表中有大字段X(例如：text類型)嗦哆，且字段X不會經(jīng)常更新，以讀為為主爵赵，請問

(1)吝秕、您是選擇拆成子表，還是繼續(xù)放一起空幻；
(2)烁峭、寫出您這樣選擇的理由。
拆帶來的問題：連接消耗 + 存儲拆分空間；不拆可能帶來的問題：查詢性能约郁；
如果能容忍拆分帶來的空間問題,拆的話最好和經(jīng)常要查詢的表的主鍵在物理結構上放置在一起(分區(qū)) 順序IO,減少連接消耗,最后這是一個文本列再加上一個全文索引來盡量抵消連接消耗
如果能容忍不拆分帶來的查詢性能損失的話:上面的方案在某個極致條件下肯定會出現(xiàn)問題,那么不拆就是最好的選擇

18缩挑、MySQL中InnoDB引擎的行鎖是通過加在什么上完成(或稱實現(xiàn))的？為什么是這樣子的鬓梅？
InnoDB是基于索引來完成行鎖
例: select * from tab_with_index where id = 1 for update;
for update 可以根據(jù)條件來完成行鎖鎖定,并且 id 是有索引鍵的列,
如果 id 不是索引鍵那么InnoDB將完成表鎖,,并發(fā)將無從談起

開放性問題：

一個6億的表a供置，一個3億的表b，通過外間tid關聯(lián)绽快，你如何最快的查詢出滿足條件的第50000到第50200中的這200條數(shù)據(jù)記錄
1芥丧、如果A表TID是自增長,并且是連續(xù)的,B表的ID為索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2、如果A表的TID不是連續(xù)的,那么就需要使用覆蓋索引.TID要么是主鍵,要么是輔助索引,B表ID也需要有索引坊罢。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;