面試BAT前先搞定這18道MySQL經(jīng)典面試題（含答案解析）

一、MySQL的復(fù)制原理以及流程

(1)復(fù)制基本原理流程

1. 主：binlog線程——記錄下所有改變了數(shù)據(jù)庫(kù)數(shù)據(jù)的語(yǔ)句，放進(jìn)master上的binlog中；

2. 從：io線程——在使用start slave 之后，負(fù)責(zé)從master上拉取 binlog 內(nèi)容，放進(jìn) 自己的relay log中；

3. 從：sql執(zhí)行線程——執(zhí)行relay log中的語(yǔ)句；

(2)MySQL復(fù)制的線程有幾個(gè)及之間的關(guān)聯(lián)

MySQL 的復(fù)制是基于如下 3 個(gè)線程的交互（ 多線程復(fù)制里面應(yīng)該是 4 類線程）：

1. Master 上面的 binlog dump 線程，該線程負(fù)責(zé)將 master 的 binlog event 傳到slave；

2. Slave 上面的 IO 線程，該線程負(fù)責(zé)接收 Master 傳過(guò)來(lái)的 binlog，并寫入 relay log；

3. Slave 上面的 SQL 線程，該線程負(fù)責(zé)讀取 relay log 并執(zhí)行；

4. 如果是多線程復(fù)制，無(wú)論是 5.6 庫(kù)級(jí)別的假多線程還是 MariaDB 或者 5.7 的真正的多線程復(fù)制， SQL 線程只做 coordinator，只負(fù)責(zé)把 relay log 中的 binlog讀出來(lái)然后交給 worker 線程， woker 線程負(fù)責(zé)具體 binlog event 的執(zhí)行；

(3)MySQL如何保證復(fù)制過(guò)程中數(shù)據(jù)一致性及減少數(shù)據(jù)同步延時(shí)

一致性主要有以下幾個(gè)方面：

1.在 MySQL5.5 以及之前， slave 的 SQL 線程執(zhí)行的 relay log 的位置只能保存在文件（ relay-log.info）里面，并且該文件默認(rèn)每執(zhí)行 10000 次事務(wù)做一次同步到磁盤， 這意味著 slave 意外 crash 重啟時(shí)， SQL 線程執(zhí)行到的位置和數(shù)據(jù)庫(kù)的數(shù)據(jù)是不一致的，將導(dǎo)致復(fù)制報(bào)錯(cuò)，如果不重搭復(fù)制，則有可能會(huì)導(dǎo)致數(shù)據(jù)不一致。MySQL 5.6 引入?yún)?shù) relay_log_info_repository，將該參數(shù)設(shè)置為 TABLE 時(shí)， MySQL 將 SQL 線程執(zhí)行到的位置存到mysql.slave_relay_log_info 表，這樣更新該表的位置和 SQL 線程執(zhí)行的用戶事務(wù)綁定成一個(gè)事務(wù)，這樣 slave 意外宕機(jī)后， slave 通過(guò) innodb 的崩潰恢復(fù)可以把 SQL 線程執(zhí)行到的位置和用戶事務(wù)恢復(fù)到一致性的狀態(tài)。

2. MySQL 5.6 引入 GTID 復(fù)制，每個(gè) GTID 對(duì)應(yīng)的事務(wù)在每個(gè)實(shí)例上面最多執(zhí)行一次， 這極大地提高了復(fù)制的數(shù)據(jù)一致性；

3. MySQL 5.5 引入半同步復(fù)制， 用戶安裝半同步復(fù)制插件并且開(kāi)啟參數(shù)后，設(shè)置超時(shí)時(shí)間，可保證在超時(shí)時(shí)間內(nèi)如果 binlog 不傳到 slave 上面，那么用戶提交事務(wù)時(shí)不會(huì)返回，直到超時(shí)后切成異步復(fù)制，但是如果切成異步之前用戶線程提交時(shí)在 master 上面等待的時(shí)候，事務(wù)已經(jīng)提交，該事務(wù)對(duì) master上面的其他 session 是可見(jiàn)的，如果這時(shí) master 宕機(jī)，那么到 slave 上面該事務(wù)又不可見(jiàn)了，該問(wèn)題直到 5.7 才解決；

4. MySQL 5.7 引入無(wú)損半同步復(fù)制，引入?yún)?rpl_semi_sync_master_wait_point，該參數(shù)默認(rèn)為 after_sync，指的是在切成半同步之前，事務(wù)不提交，而是接收到 slave 的 ACK 確認(rèn)之后才提交該事務(wù)，從此，復(fù)制真正可以做到無(wú)損的了。

5.可以再說(shuō)一下 5.7 的無(wú)損復(fù)制情況下， master 意外宕機(jī)，重啟后發(fā)現(xiàn)有 binlog沒(méi)傳到 slave 上面，這部分 binlog 怎么辦？？？分 2 種情況討論， 1 宕機(jī)時(shí)已經(jīng)切成異步了， 2 是宕機(jī)時(shí)還沒(méi)切成異步？？？這個(gè)怎么判斷宕機(jī)時(shí)有沒(méi)有切成異步呢？？？分別怎么處理？？？

延時(shí)性：

5.5 是單線程復(fù)制， 5.6 是多庫(kù)復(fù)制（對(duì)于單庫(kù)或者單表的并發(fā)操作是沒(méi)用的）， 5.7 是真正意義的多線程復(fù)制，它的原理是基于 group commit， 只要master 上面的事務(wù)是 group commit 的，那 slave 上面也可以通過(guò)多個(gè) worker線程去并發(fā)執(zhí)行。和 MairaDB10.0.0.5 引入多線程復(fù)制的原理基本一樣。

(4)工作遇到的復(fù)制 bug 的解決方法

5.6 的多庫(kù)復(fù)制有時(shí)候自己會(huì)停止，我們寫了一個(gè)腳本重新 start slave;待補(bǔ)充…

二、MySQL中myisam與innodb的區(qū)別，至少5點(diǎn)

(1)問(wèn)5點(diǎn)不同

1.InnoDB支持事物，而MyISAM不支持事物

2.InnoDB支持行級(jí)鎖，而MyISAM支持表級(jí)鎖

3.InnoDB支持MVCC, 而MyISAM不支持

4.InnoDB支持外鍵，而MyISAM不支持

5.InnoDB不支持全文索引，而MyISAM支持。

6.InnoDB不能通過(guò)直接拷貝表文件的方法拷貝表到另外一臺(tái)機(jī)器， myisam 支持

7.InnoDB表支持多種行格式， myisam 不支持

8.InnoDB是索引組織表， myisam 是堆表

(2)innodb引擎的4大特性

1.插入緩沖（insert buffer)

2.二次寫(double write)

3.自適應(yīng)哈希索引(ahi)

4.預(yù)讀(read ahead)

(3)各種不同 mysql 版本的Innodb的改進(jìn)

MySQL5.6 下 Innodb 引擎的主要改進(jìn)：

1.online DDL

2. memcached NoSQL 接口

3.transportable tablespace（ alter table discard/import tablespace）

4.MySQL 正常關(guān)閉時(shí)，可以 dump 出 buffer pool 的（ space， page_no），重啟時(shí) reload，加快預(yù)熱速度

5.索引和表的統(tǒng)計(jì)信息持久化到 mysql.innodb_table_stats 和mysql.innodb_index_stats，可提供穩(wěn)定的執(zhí)行計(jì)劃

6.Compressed row format 支持壓縮表

MySQL 5.7 innodb 引擎主要改進(jìn)

1.修改 varchar 字段長(zhǎng)度有時(shí)可以使用 online DDL

2. Buffer pool 支持在線改變大小

3.Buffer pool 支持導(dǎo)出部分比例

4.支持新建 innodb tablespace，并可以在其中創(chuàng)建多張表

5.磁盤臨時(shí)表采用 innodb 存儲(chǔ)，并且存儲(chǔ)在 innodb temp tablespace 里面，以前是 myisam 存儲(chǔ)

6.透明表空間壓縮功能

(4)2者select count(*)哪個(gè)更快，為什么

myisam更快，因?yàn)閙yisam內(nèi)部維護(hù)了一個(gè)計(jì)數(shù)器，可以直接調(diào)取。

(5)2 者的索引的實(shí)現(xiàn)方式

都是 B+樹(shù)索引， Innodb 是索引組織表， myisam 是堆表， 索引組織表和堆表的區(qū)別要熟悉

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

(1)varchar與char的區(qū)別

在單字節(jié)字符集下， char（N） 在內(nèi)部存儲(chǔ)的時(shí)候總是定長(zhǎng)， 而且沒(méi)有變長(zhǎng)字段長(zhǎng)度列表中。在多字節(jié)字符集下面， char(N)如果存儲(chǔ)的字節(jié)數(shù)超過(guò) N，那么 char（N）將和 varchar（N）沒(méi)有區(qū)別。在多字節(jié)字符集下面，如果存儲(chǔ)的字節(jié)數(shù)少于 N，那么存儲(chǔ) N 個(gè)字節(jié)，后面補(bǔ)空格，補(bǔ)到 N 字節(jié)長(zhǎng)度。都存儲(chǔ)變長(zhǎng)的數(shù)據(jù)和變長(zhǎng)字段長(zhǎng)度列表。varchar(N)無(wú)論是什么字節(jié)字符集，都是變長(zhǎng)的，即都存儲(chǔ)變長(zhǎng)數(shù)據(jù)和變長(zhǎng)字段長(zhǎng)度列表

(2)varchar(50)中50的涵義

最多存放50個(gè)字符，varchar(50)和(200)存儲(chǔ)hello所占空間一樣，但后者在排序時(shí)會(huì)消耗更多內(nèi)存，因?yàn)閛rder by col采用fixed_length計(jì)算col長(zhǎng)度(memory引擎也一樣)。在早期 MySQL 版本中， 50 代表字節(jié)數(shù)，現(xiàn)在代表字符數(shù)。

(3)int（20）中20的涵義

是指顯示字符的長(zhǎng)度

不影響內(nèi)部存儲(chǔ)，只是影響帶 zerofill 定義的 int 時(shí)，前面補(bǔ)多少個(gè) 0，易于報(bào)表展示

(4)mysql為什么這么設(shè)計(jì)

對(duì)大多數(shù)應(yīng)用沒(méi)有意義，只是規(guī)定一些工具用來(lái)顯示字符的個(gè)數(shù)；int(1)和int(20)存儲(chǔ)和計(jì)算均一樣；

四、innodb的事務(wù)與日志的實(shí)現(xiàn)方式

(1)有多少種日志

redo和undo

(2)日志的存放形式

redo：在頁(yè)修改的時(shí)候，先寫到 redo log buffer 里面， 然后寫到 redo log 的文件系統(tǒng)緩存里面(fwrite)，然后再同步到磁盤文件（ fsync）。

Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata*文件里面， 5.6 之后，可以通過(guò)設(shè)置 innodb_undo_tablespaces 參數(shù)把 undo log 存放在 ibdata*之外。

(3)事務(wù)是如何通過(guò)日志來(lái)實(shí)現(xiàn)的，說(shuō)得越深入越好

基本流程如下：

因?yàn)槭聞?wù)在修改頁(yè)時(shí)，要先記 undo，在記 undo 之前要記 undo 的 redo， 然后修改數(shù)據(jù)頁(yè)，再記數(shù)據(jù)頁(yè)修改的 redo。Redo（里面包括 undo 的修改） 一定要比數(shù)據(jù)頁(yè)先持久化到磁盤。當(dāng)事務(wù)需要回滾時(shí)，因?yàn)橛?undo，可以把數(shù)據(jù)頁(yè)回滾到前鏡像的

狀態(tài)，崩潰恢復(fù)時(shí)，如果 redo log 中事務(wù)沒(méi)有對(duì)應(yīng)的 commit 記錄，那么需要用 undo把該事務(wù)的修改回滾到事務(wù)開(kāi)始之前。如果有 commit 記錄，就用 redo 前滾到該事務(wù)完成時(shí)并提交掉。

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

(1) 各種日志格式的涵義

1.Statement：每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄在binlog中。

優(yōu)點(diǎn)：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。(相比row能節(jié)約多少性能 與日志量，這個(gè)取決于應(yīng)用的SQL情況，正常同一條記錄修改或者插入row格式所產(chǎn)生的日志量還小于Statement產(chǎn)生的日志量，

但是考慮到如果帶條 件的update操作，以及整表刪除，alter表等操作，ROW格式會(huì)產(chǎn)生大量日志，因此在考慮是否使用ROW格式日志時(shí)應(yīng)該跟據(jù)應(yīng)用的實(shí)際情況，其所 產(chǎn)生的日志量會(huì)增加多少，以及帶來(lái)的IO性能問(wèn)題。)

缺點(diǎn)：由于記錄的只是執(zhí)行語(yǔ)句，為了這些語(yǔ)句能在slave上正確運(yùn)行，因此還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的 一些相關(guān)信息，以保證所有語(yǔ)句能在slave得到和在master端執(zhí)行時(shí)候相同 的結(jié)果。另外mysql 的復(fù)制,

像一些特定函數(shù)功能，slave可與master上要保持一致會(huì)有很多相關(guān)問(wèn)題(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)會(huì)出現(xiàn)問(wèn)題).

使用以下函數(shù)的語(yǔ)句也無(wú)法被復(fù)制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非啟動(dòng)時(shí)啟用了 --sysdate-is-now 選項(xiàng))

同時(shí)在INSERT ...SELECT 會(huì)產(chǎn)生比 RBR 更多的行級(jí)鎖

2.Row:不記錄sql語(yǔ)句上下文相關(guān)信息，僅保存哪條記錄被修改。

優(yōu)點(diǎn)：binlog中可以不記錄執(zhí)行的sql語(yǔ)句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下 每一行數(shù)據(jù)修改的細(xì)節(jié)。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程，或function，以及trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題

缺點(diǎn):所有的執(zhí)行的語(yǔ)句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來(lái)記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容,比 如一條update語(yǔ)句，修改多條記錄，則binlog中每一條修改都會(huì)有記錄，這樣造成binlog日志量會(huì)很大，特別是當(dāng)執(zhí)行alter table之類的語(yǔ)句的時(shí)候，由于表結(jié)構(gòu)修改，每條記錄都發(fā)生改變，那么該表每一條記錄都會(huì)記錄到日志中。

3.Mixedlevel: 是以上兩種level的混合使用，一般的語(yǔ)句修改使用statment格式保存binlog，如一些函數(shù)，statement無(wú)法完成主從復(fù)制的操作，則 采用row格式保存binlog,MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式，

也就是在Statement和Row之間選擇 一種.新版本的MySQL中隊(duì)row level模式也被做了優(yōu)化，并不是所有的修改都會(huì)以row level來(lái)記錄，像遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以statement模式來(lái)記錄。至于update或者delete等修改數(shù)據(jù)的語(yǔ)句，還是會(huì)記錄所有行的變更。

(2)適用場(chǎng)景

在一條 SQL 操作了多行數(shù)據(jù)時(shí)， statement 更節(jié)省空間， row 更占用空間。但是 row模式更可靠。

(3)結(jié)合第一個(gè)問(wèn)題，每一種日志格式在復(fù)制中的優(yōu)劣

Statement 可能占用空間會(huì)相對(duì)小一些，傳送到 slave 的時(shí)間可能也短，但是沒(méi)有 row模式的可靠。Row 模式在操作多行數(shù)據(jù)時(shí)更占用空間， 但是可靠。

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

當(dāng) cpu 飆升到 500%時(shí)，先用操作系統(tǒng)命令 top 命令觀察是不是 mysqld 占用導(dǎo)致的，如果不是，找出占用高的進(jìn)程，并進(jìn)行相關(guān)處理。如果是 mysqld 造成的， show processlist，看看里面跑的 session 情況，是不是有消耗資源的 sql 在運(yùn)行。找出消耗高的 sql，看看執(zhí)行計(jì)劃是否準(zhǔn)確， index 是否缺失，或者實(shí)在是數(shù)據(jù)量太大造成。一般來(lái)說(shuō)，肯定要 kill 掉這些線程(同時(shí)觀察 cpu 使用率是否下降)，等進(jìn)行相應(yīng)的調(diào)整(比如說(shuō)加索引、改 sql、改內(nèi)存參數(shù))之后，再重新跑這些 SQL。也有可能是每個(gè) sql 消耗資源并不多，但是突然之間，有大量的 session 連進(jìn)來(lái)導(dǎo)致 cpu 飆升，這種情況就需要跟應(yīng)用一起來(lái)分析為何連接數(shù)會(huì)激增，再做出相應(yīng)的調(diào)整，比如說(shuō)限制連接數(shù)等

七、sql優(yōu)化

(1)、explain出來(lái)的各種item的意義

id:每個(gè)被獨(dú)立執(zhí)行的操作的標(biāo)志，表示對(duì)象被操作的順序。一般來(lái)說(shuō)， id 值大，先被執(zhí)行；如果 id 值相同，則順序從上到下。

select_type：查詢中每個(gè) select 子句的類型。

table:名字，被操作的對(duì)象名稱，通常的表名(或者別名)，但是也有其他格式。

partitions:匹配的分區(qū)信息。

type:join 類型。

possible_keys：列出可能會(huì)用到的索引。

key:實(shí)際用到的索引。

key_len:用到的索引鍵的平均長(zhǎng)度，單位為字節(jié)。

ref:表示本行被操作的對(duì)象的參照對(duì)象，可能是一個(gè)常量用 const 表示，也可能是其他表的

key 指向的對(duì)象，比如說(shuō)驅(qū)動(dòng)表的連接列。

rows:估計(jì)每次需要掃描的行數(shù)。

filtered:rows*filtered/100 表示該步驟最后得到的行數(shù)(估計(jì)值)。

extra:重要的補(bǔ)充信息。

(2)、profile的意義以及使用場(chǎng)景

Profile 用來(lái)分析 sql 性能的消耗分布情況。當(dāng)用 explain 無(wú)法解決慢 SQL 的時(shí)候，需要用profile 來(lái)對(duì) sql 進(jìn)行更細(xì)致的分析，找出 sql 所花的時(shí)間大部分消耗在哪個(gè)部分，確認(rèn) sql的性能瓶頸。

(3)、explain 中的索引問(wèn)題

Explain 結(jié)果中，一般來(lái)說(shuō)，要看到盡量用 index(type 為 const、 ref 等， key 列有值)，避免使用全表掃描(type 顯式為 ALL)。比如說(shuō)有 where 條件且選擇性不錯(cuò)的列，需要建立索引。

被驅(qū)動(dòng)表的連接列，也需要建立索引。被驅(qū)動(dòng)表的連接列也可能會(huì)跟 where 條件列一起建立聯(lián)合索引。當(dāng)有排序或者 group by 的需求時(shí)，也可以考慮建立索引來(lái)達(dá)到直接排序和匯總的需求。
歡迎大家關(guān)注我的公種浩【程序員追風(fēng)】，文章都會(huì)在里面更新，整理的資料也會(huì)放在里面。

八、備份計(jì)劃，mysqldump以及xtranbackup的實(shí)現(xiàn)原理

(1)備份計(jì)劃

視庫(kù)的大小來(lái)定，一般來(lái)說(shuō) 100G 內(nèi)的庫(kù)，可以考慮使用 mysqldump 來(lái)做，因?yàn)?mysqldump更加輕巧靈活，備份時(shí)間選在業(yè)務(wù)低峰期，可以每天進(jìn)行都進(jìn)行全量備份(mysqldump 備份

出來(lái)的文件比較小，壓縮之后更小)。100G 以上的庫(kù)，可以考慮用 xtranbackup 來(lái)做，備份速度明顯要比 mysqldump 要快。一般是選擇一周一個(gè)全備，其余每天進(jìn)行增量備份，備份時(shí)間為業(yè)務(wù)低峰期。

(2)備份恢復(fù)時(shí)間

物理備份恢復(fù)快，邏輯備份恢復(fù)慢

這里跟機(jī)器，尤其是硬盤的速率有關(guān)系，以下列舉幾個(gè)僅供參考

20G的2分鐘（mysqldump）

80G的30分鐘(mysqldump)

111G的30分鐘（mysqldump)

288G的3小時(shí)（xtra)

3T的4小時(shí)（xtra)

邏輯導(dǎo)入時(shí)間一般是備份時(shí)間的5倍以上

(3)備份恢復(fù)失敗如何處理

首先在恢復(fù)之前就應(yīng)該做足準(zhǔn)備工作，避免恢復(fù)的時(shí)候出錯(cuò)。比如說(shuō)備份之后的有效性檢查、權(quán)限檢查、空間檢查等。如果萬(wàn)一報(bào)錯(cuò)，再根據(jù)報(bào)錯(cuò)的提示來(lái)進(jìn)行相應(yīng)的調(diào)整。

(4)mysqldump和xtrabackup實(shí)現(xiàn)原理

mysqldump

mysqldump 屬于邏輯備份。加入--single-transaction 選項(xiàng)可以進(jìn)行一致性備份。后臺(tái)進(jìn)程會(huì)先設(shè)置 session 的事務(wù)隔離級(jí)別為 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，之后顯式開(kāi)啟一個(gè)事務(wù)(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)，這樣就保證了該事務(wù)里讀到的數(shù)據(jù)都是事務(wù)事務(wù)時(shí)候的快照。之后再把表的數(shù)據(jù)讀取出來(lái)。如果加上--master-data=1 的話，在剛開(kāi)始的時(shí)候還會(huì)加一個(gè)數(shù)據(jù)庫(kù)的讀鎖(FLUSH TABLES WITH READ LOCK),等開(kāi)啟事務(wù)后，再記錄下數(shù)據(jù)庫(kù)此時(shí) binlog 的位置(showmaster status)，馬上解鎖，再讀取表的數(shù)據(jù)。等所有的數(shù)據(jù)都已經(jīng)導(dǎo)完，就可以結(jié)束事務(wù)

Xtrabackup:

xtrabackup 屬于物理備份，直接拷貝表空間文件，同時(shí)不斷掃描產(chǎn)生的 redo 日志并保存下來(lái)。最后完成 innodb 的備份后，會(huì)做一個(gè) flush engine logs 的操作(老版本在有 bug，在5.6 上不做此操作會(huì)丟數(shù)據(jù))，確保所有的 redo log 都已經(jīng)落盤(涉及到事務(wù)的兩階段提交

概念，因?yàn)?xtrabackup 并不拷貝 binlog，所以必須保證所有的 redo log 都落盤，否則可能會(huì)丟最后一組提交事務(wù)的數(shù)據(jù))。這個(gè)時(shí)間點(diǎn)就是 innodb 完成備份的時(shí)間點(diǎn)，數(shù)據(jù)文件雖然不是一致性的，但是有這段時(shí)間的 redo 就可以讓數(shù)據(jù)文件達(dá)到一致性(恢復(fù)的時(shí)候做的事

情)。然后還需要 flush tables with read lock，把 myisam 等其他引擎的表給備份出來(lái)，備份完后解鎖。這樣就做到了完美的熱備。

九、mysqldump中備份出來(lái)的sql，如果我想sql文件中，一行只有一個(gè)insert....value()的話，怎么辦？如果備份需要帶上master的復(fù)制點(diǎn)信息怎么辦？

--skip-extended-insert
[root@helei-zhuanshu?~]#?mysqldump?-uroot?-p?helei?--skip-extended-insert
Enter?password:
??KEY?`idx_c1`?(`c1`),
??KEY?`idx_c2`?(`c2`)
)?ENGINE=InnoDB?AUTO_INCREMENT=51?DEFAULT?CHARSET=latin1;
/*!40101?SET?character_set_client?=?@saved_cs_client?*/;
--
--?Dumping?data?for?table?`helei`
--
LOCK?TABLES?`helei`?WRITE;
/*!40000?ALTER?TABLE?`helei`?DISABLE?KEYS?*/;
INSERT?INTO?`helei`?VALUES?(1,32,37,38,'2016-10-18?06:19:24','susususususususususususu');
INSERT?INTO?`helei`?VALUES?(2,37,46,21,'2016-10-18?06:19:24','susususususu');
INSERT?INTO?`helei`?VALUES?(3,21,5,14,'2016-10-18?06:19:24','susu');

十、500臺(tái)db，在最快時(shí)間之內(nèi)重啟

可以使用批量 ssh 工具 pssh 來(lái)對(duì)需要重啟的機(jī)器執(zhí)行重啟命令。也可以使用 salt（前提是客戶端有安裝 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多線程工具同時(shí)操作多臺(tái)服務(wù)器

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

(1)讀取參數(shù)

global?buffer?以及?local?buffer；
Global?buffer：
Innodb_buffer_pool_size
innodb_log_buffer_size
innodb_additional_mem_pool_size
local?buffer(下面的都是?server?層的?session?變量，不是?innodb?的)：
Read_buffer_size
Join_buffer_size
Sort_buffer_size
Key_buffer_size
Binlog_cache_size

(2)寫入?yún)?shù)

innodb_flush_log_at_trx_commit
innodb_buffer_pool_size
insert_buffer_size
innodb_double_write
innodb_write_io_thread
innodb_flush_method

(3)與IO相關(guān)的參數(shù)

innodb_write_io_threads?=?8
innodb_read_io_threads?=?8
innodb_thread_concurrency?=?0
Sync_binlog
Innodb_flush_log_at_trx_commit
Innodb_lru_scan_depth
Innodb_io_capacity
Innodb_io_capacity_max
innodb_log_buffer_size
innodb_max_dirty_pages_pct

(4)緩存參數(shù)以及緩存的適用場(chǎng)景

query cache/query_cache_type

并不是所有表都適合使用query cache。造成query cache失效的原因主要是相應(yīng)的table發(fā)生了變更

第一個(gè)：讀操作多的話看看比例，簡(jiǎn)單來(lái)說(shuō)，如果是用戶清單表，或者說(shuō)是數(shù)據(jù)比例比較固定，比如說(shuō)商品列表，是可以打開(kāi)的，前提是這些庫(kù)比較集中，數(shù)據(jù)庫(kù)中的實(shí)務(wù)比較小。

第二個(gè)：我們“行騙”的時(shí)候，比如說(shuō)我們競(jìng)標(biāo)的時(shí)候壓測(cè)，把query cache打開(kāi)，還是能收到qps激增的效果，當(dāng)然前提示前端的連接池什么的都配置一樣。大部分情況下如果寫入的居多，訪問(wèn)量并不多，那么就不要打開(kāi)，例如社交網(wǎng)站的，10%的人產(chǎn)生內(nèi)容，其余的90%都在消費(fèi)，打開(kāi)還是效果很好的，但是你如果是qq消息，或者聊天，那就很要命。

第三個(gè)：小網(wǎng)站或者沒(méi)有高并發(fā)的無(wú)所謂，高并發(fā)下，會(huì)看到 很多 qcache 鎖 等待，所以一般高并發(fā)下，不建議打開(kāi)query cache

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

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

十三、你是否做過(guò)主從一致性校驗(yàn)，如果有，怎么做的，如果沒(méi)有，你打算怎么做？

主從一致性校驗(yàn)有多種工具 例如checksum、mysqldiff、pt-table-checksum等

十四、表中有大字段X(例如：text類型)，且字段X不會(huì)經(jīng)常更新，以讀為為主，請(qǐng)問(wèn)您是選擇拆成子表，還是繼續(xù)放一起?寫出您這樣選擇的理由

答：拆帶來(lái)的問(wèn)題：連接消耗 + 存儲(chǔ)拆分空間；不拆可能帶來(lái)的問(wèn)題：查詢性能；

如果能容忍拆分帶來(lái)的空間問(wèn)題,拆的話最好和經(jīng)常要查詢的表的主鍵在物理結(jié)構(gòu)上放置在一起(分區(qū)) 順序IO,減少連接消耗,最后這是一個(gè)文本列再加上一個(gè)全文索引來(lái)盡量抵消連接消耗

如果能容忍不拆分帶來(lái)的查詢性能損失的話:上面的方案在某個(gè)極致條件下肯定會(huì)出現(xiàn)問(wèn)題,那么不拆就是最好的選擇

十五、MySQL中InnoDB引擎的行鎖是通過(guò)加在什么上完成(或稱實(shí)現(xiàn))的？為什么是這樣子的？

答：InnoDB是基于索引來(lái)完成行鎖

例: select * from tab_with_index where id = 1 for update;

for update 可以根據(jù)條件來(lái)完成行鎖鎖定,并且 id 是有索引鍵的列,

如果 id 不是索引鍵那么InnoDB將完成表鎖,,并發(fā)將無(wú)從談起

十六、如何從mysqldump產(chǎn)生的全庫(kù)備份中只恢復(fù)某一個(gè)庫(kù)、某一張表？

全庫(kù)備份
[root@HE1?~]#?mysqldump?-uroot?-p?--single-transaction?-A?--master-data=2?>dump.sql
只還原erp庫(kù)的內(nèi)容
[root@HE1?~]#?mysql?-uroot?-pMANAGER?erp?--one-database?<dump.sql

可以看出這里主要用到的參數(shù)是--one-database簡(jiǎn)寫-o的參數(shù)，極大方便了我們的恢復(fù)靈活性
那么如何從全庫(kù)備份中抽取某張表呢，全庫(kù)恢復(fù)，再恢復(fù)某張表小庫(kù)還可以，大庫(kù)就很麻煩了，那我們可以利用正則表達(dá)式來(lái)進(jìn)行快速抽取，具體實(shí)現(xiàn)方法如下：
?
從全庫(kù)備份中抽取出t表的表結(jié)構(gòu)
[root@HE1?~]#?sed?-e'/./{H;$!d;}'?-e?'x;/CREATE?TABLE?`t`/!d;q'?dump.sql
?
DROP?TABLE?IF?EXISTS`t`;
/*!40101?SET@saved_cs_client?????=@@character_set_client?*/;
/*!40101?SETcharacter_set_client?=?utf8?*/;
CREATE?TABLE?`t`?(
??`id`?int(10)?NOT?NULL?AUTO_INCREMENT,
??`age`?tinyint(4)?NOT?NULL?DEFAULT?'0',
??`name`?varchar(30)?NOT?NULL?DEFAULT?'',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDBAUTO_INCREMENT=4?DEFAULT?CHARSET=utf8;
/*!40101?SETcharacter_set_client?=?@saved_cs_client?*/;
?
從全庫(kù)備份中抽取出t表的內(nèi)容
[root@HE1?~]#?grep'INSERT?INTO?`t`'?dump.sql
INSERT?INTO?`t`VALUES?(0,0,''),(1,0,'aa'),(2,0,'bbb'),(3,25,'helei');

十七、在當(dāng)前的工作中，你碰到到的最大的 mysql db 問(wèn)題以及如何解決的？

可以選擇一個(gè)處理過(guò)的比較棘手的案例，或者選擇一個(gè)老師在課程上講過(guò)的死鎖的案例;沒(méi)有及時(shí) Purge + insert 唯一索引造成的死鎖：具體案例可以參考學(xué)委筆記。

十八、請(qǐng)簡(jiǎn)潔地描述下 MySQL 中 InnoDB 支持的四種事務(wù)隔離級(jí)別名稱，以及逐級(jí)之間的區(qū)別？

(1)事物的4種隔離級(jí)別

讀未提交(read uncommitted)

讀已提交(read committed)

可重復(fù)讀(repeatable read)

串行(serializable)

(2)不同級(jí)別的現(xiàn)象

Read Uncommitted:可以讀取其他 session 未提交的臟數(shù)據(jù)。

Read Committed:允許不可重復(fù)讀取，但不允許臟讀取。提交后，其他會(huì)話可以看到提交的數(shù)據(jù)。

Repeatable Read: 禁止不可重復(fù)讀取和臟讀取、以及幻讀(innodb 獨(dú)有)。

Serializable: 事務(wù)只能一個(gè)接著一個(gè)地執(zhí)行，但不能并發(fā)執(zhí)行。事務(wù)隔離級(jí)別最高。

不同的隔離級(jí)別有不同的現(xiàn)象，并有不同的鎖定/并發(fā)機(jī)制，隔離級(jí)別越高，數(shù)據(jù)庫(kù)的并發(fā)性就越差。

最后
歡迎大家一起交流，喜歡文章記得點(diǎn)個(gè)贊喲，感謝支持！