mysql的面試試題

標籤：

1， mysql的複製原理以及流程。

（1）先問基本原理流程，3個線程以及之間的關聯。

　　答：Mysql複製的三個線程：主庫線程，從庫I/O線程，從庫sql線程；

　　　　複製流程：（1）I/O線程向主庫發出請求

　　　　　　　　　（2）主庫線程響應請求，並推binlog日誌到從庫

　　　　　　　　　（3）I/O線程收到線程並記入中繼日誌

　　　　　　　　　（4）Sql線程從中繼日誌讀取sql，並記入從庫binlog日誌，flush進硬碟；

（2）再問一致性延時性，資料恢複；

　　答：（1）主從複製一致性由binlog執行順序保證（timespan+pos）；

　　　　　　 日誌越詳細，主從一致性越容易保證；

　　　　（2）延時性：延時表現為behind_master_pos後面的數字，其實並不準確；

　　　　　　　　　　5.5.30以前版本都屬於非同步複製，因此都有延時。因為是主庫執行完成後從庫才執行，一先一後就有了延遲；

　　　　　　　　　　主從延遲的準確計算方法是：延遲時間=從庫執行sql完成的時刻-主庫開始執行sql的時間；

　　　　（3）資料恢複：備份時記錄的binlog位置點（timespan+pos）；

（3）再問各種工作遇到的複製bug的解決方案；

　　答：這個問題感覺描述並不準確，不清楚是主從複製故障還是bug；

　　　　故障一般由於主鍵衝突，連結不上主庫，找不到對應的binlog位置等引起；

　　　　解決方案是跳過衝突，檢查主從連結，找正確的pos；

　　　　bug不常見，筆者碰到過一次，分享如下：

　　　　　　環境：主庫從庫都是虛機，每十分鐘與宿主機同步一次時間，大約每次與主機相差2秒；

　　　　　　表現：從庫複製時重複執行兩秒之內的日誌；

　　　　　　　　　從庫show slave status\G，behind_master_pos在60000和0之間迴圈，每兩秒一次；

2， mysql中myisam與innodb的區別，至少5點。

　　（1） 問5點不同

　　　　答：1、 儲存成本不一樣，儲存限制不一樣；

　　　　　　2、CPU使用成本不一樣，innodb快取資料和索引；

　　　　　　3、鎖粒度不一樣，支援MVCC；

　　　　　　4、緩衝機制不一樣(buffer_pool和key_buffer)

　　　　　　5、事務支援；

　　　　　　6、索引支援：全文索引(myisam),外鍵(innodb)，hash(innodb)

　　　　　　7、讀寫速度；

　　　　　　8、備份；

　　（2）、問各種不同mysql版本的2者的改進；　　　　

　　　　　　最近測5.1.38和5.5.35

　　　　　　Innodb:（1）adeptive_innodb_index可控；

　　　　　　　　　　（2）innodb變為預設引擎；

　　　　　　　　　　（3）更快的innodb插入；

　　　　　　　　　　（4）讀寫線程數目；

　　　　　　　　　　（5）半同步複製；

　　　　　　　　　　（6）performance_schame

　　　　　　Myisam（1）

　　（3）2者的索引的實現方式；

　　　　  myisam將索引和資料分開存放，索引記錄索引中索引值的物理位置，根據物理位置去MYD的資料頁中尋找對應的data page；資料排列是堆資料，沒有物理順序，索引只是在邏輯上將資料串起來，並不改變資料的物理位置；

　　　　 innodb採用主鍵將資料進行物理排序存放，新插入的資料根據主鍵的大小，會修改主鍵索引的序列；secondary index通過尋找主鍵來尋找資料；

3，問mysql中varchar與char的區別以及varchar(30)中的30代表的涵義。

　　（1） varchar與char的區別

　　　　答：變長和固定長度

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

           答： 字元最大長度50，所代表的位元組數與字元集有關，比如是utf8佔3個位元組，那麼varchar(50)欄位在表中最大取到150個位元組

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

　　　　答：int是類型的數字，在2進位記錄裡，長度最大為20，數字範圍是-2^19~(2^19-1)；

      （4）為什麼MySQL這樣設計？

　　　　答：在varchar(M)中,varchar在一張表中最大的位元組數目為65535，實際長度跟存放的內容有關；

4，問了innodb的事務與日誌的實現方式。 

　　（1）有多少種日誌

　　　　答：5種，binlog,查詢日誌，慢查詢，錯誤記錄檔，中繼日誌

　　（2）日誌的存放形式

　　　　答：Binlog,中繼日誌都是二進位；

　　　　　　其他三種是文本形式；

　　（3）事務是如何通過日誌來實現的，說得越深入越好。

　　　　答：Innodb日誌需要開啟顯式提交，預設是關閉的；

　　　　　　首先瞭解日誌過程。緩衝進log_buffer,每秒或每十秒刷入redo_log，提交後刷入硬碟

　　　　　　交易記錄會在innodb_buffer_pool頁中標記行是否更新，刪除，commit後刷入硬碟，沒有commit則不計入磁碟，屬於髒資料；

5，問了mysql binlog的幾種日誌錄入格式以及區別

　　（1）    各種日誌格式的涵義

　　　　答：三種日誌格式 ：statement,row,mixed

　　　　　　每一條DML操作的sql都被計入statement日誌；

　　　　　　每條DML操作的sql被記錄為對每條資料的操作，計入row日誌；

　　　　　　mixed日誌是上面兩種的混合，具體記錄的方式由隔離等級+binlog_format共同決定

　　（2）    適用情境

　　　　答：1、Statement，優點：不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高效能；

　　　　　　　　　　　　　　缺點：由於記錄的只是執行語句，為了這些語句能在slave上正確運行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能　　　　　　　　　　　　　　　　　在slave得到和在master端執行時候相同 的結果；

　　　　　　　　　　　　　　　　　某些特定函數功能會引起複製問題,比如sleep()函數， last_insert_id()；

　　　　　　　　　　　　　　　　　某些函數無法計入複製日誌： LOAD_FILE()

　　　　　　2、Row模式，優點：不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了；

　　　　　　　　　　　　  缺點：產生大量的日誌，大量日誌造成io開銷大；

　　　　　　3、mixed模式，一般的語句修改使用statment格式儲存binlog，statement無法完成主從複製的操作，則採用row格式儲存binlog，MySQL根據sql來選擇日誌記錄　　　　　　　　格式，表結構變更的時候就會以statement模式來記錄，update或者delete等修改資料的語句，還是會記錄所有行的變更；

      （3）結合第一個問題，每一種日誌格式在複製中的優劣。

6，問了下mysql資料庫cpu飆升到500%的話他怎麼處理？

　　答：（1）多執行個體的伺服器，先top查看是那一個進程，哪個連接埠佔用CPU多；

　　　　（2）show processeslist查看是否由於大量並發，鎖引起的負載問題；

　　　　（3）否則，查看慢查詢，找出執行時間長的sql；explain分析sql是否走索引，sql最佳化；

　　　　（4）再查看是否緩衝失效引起，需要查看buffer命中率；

7， sql最佳化。

         （1）explain出來的各種item的意義

　　　　答：Select_type：所使用的查詢類型，主要有以下這幾種查詢類型：

　　　　　　　　DEPENDENT SUBQUERY：子查詢內層的第一個SELECT，依賴於外部查詢的結果集。

　　　　　　　　DEPENDENT UNION：子查詢中的UNION，且為UNION中從第二個SELECT開始的後面所有SELECT，同樣依賴於外部查詢的結果集。

　　　　　　　　PRIMARY：子查詢中的最外層查詢，注意並不是主鍵查詢。

　　　　　　　　SIMPLE：除子查詢或UNION之外的其他查詢。

　　　　　　　　SUBQUERY：子查詢內層查詢的第一個SELECT，結果不依賴於外部查詢結果集。

　　　　　　　　UNCACHEABLE SUBQUERY：結果集無法緩衝的子查詢。

　　　　　　　　UNION：UNION語句中第二個SELECT開始後面的所有SELECT，第一個SELECT為PRIMARY。

　　　　　　　　UNION RESULT：UNION 中的合并結果。

　　　　　　Table：顯示這一步所訪問的資料庫中的表的名稱。

　　　　　　Type：告訴我們對錶使用的訪問方式，主要包含如下集中類型。

　　　　　　　　const：讀常量，最多隻會有一條記錄匹配，由於是常量，實際上只須要讀一次。

　　　　　　　　eq_ref：最多隻會有一條匹配結果，一般是通過主鍵或唯一鍵索引來訪問。

　　　　　　　　fulltext：進行全文索引檢索。

　　　　　　　　index：全索引掃描。

　　　　　　　　index_merge：查詢中同時使用兩個（或更多）索引，然後對索引結果進行合并（merge），再讀取表資料。

　　　　　　　　index_subquery：子查詢中的返回結果欄位組合是一個索引（或索引組合），但不是一個主鍵或唯一索引。

　　　　　　　　rang：索引範圍掃描。

　　　　　　Possible_keys：該查詢可以利用的索引。如果沒有任何索引可以使用，就會顯示成null，這項內容對最佳化索引時的調整非常重要。

　　　　　　Key：MySQL Query Optimizer 從 possible_keys 中所選擇使用的索引。

　　　　　　Key_len：被選中使用索引的索引鍵長度。

　　　　　　Ref：列出是通過常量（const），還是某個表的某個欄位（如果是join）來過濾（通過key）的。　　

　　　　　　Rows：MySQL Query Optimizer 通過系統收集的統計資訊估算出來的結果集記錄條數。

　　　　　　Extra：查詢中每一步實現的額外細節資訊，主要會是以下內容。　　　　　　　

　　　　　　註：http://www.cnblogs.com/hustcat/articles/1579244.html

         （2）profile的意義以及使用情境。

　　　　　　profile是為了鎖定sql執行過程中，在每一步消耗的資源；然後有針對性的進行最佳化；

         （3）explain中的索引問題。

 　　　　　　

8,  備份計劃，mysqldump以及xtranbackup的實現原理，

 

      答： Mysqldump：先鎖所有表，然後把表中每條sql拼接為insert語句，一頁為一小段，

 

                                   備份結構為：表結構+insert

 

             Xtrabackup分對innodb和myisam引擎表的備份；

　　　　　　　　myisam：鎖表進行copy；

　　　　　　　　innodb： Xtrabackup備份Innodb是利用了innodb的crach_recovery功能；

 

                                   Crash_recovery是對交易記錄，commit的sql記入datafile，沒有commit的則復原，這點在innodb啟動時被應用；

 

                                   Xtrabackup由三個線程進行：

 

       　　　　　　　　　　　　線程1，copy innodb的頁，每秒copy1M，64頁，copy過程中頁資料是rw的，利用Innodb的內建表進行copy；

 

　　　　　　　　　　　　　　 線程2，監控copy過程中頁資料是否正常，正常則copy，不正常則再copy一次，最多重複10次；

 

                                   　　線程3，監控logfile，有變化則立刻copy走；

 

                                   Copy結束後，記錄位置點；

 

             增量備份:檢查與上次備份，哪些頁有變化，比較上次備份頁的lsn與當前頁lsn大小，有變化則copy走，copy結束後，則記錄最後的位置點；

 

　　（1）備份計劃

 

　　　　Dump，每天備份一次，每15分鐘記錄備份；

 

　　　　Xtranbackup：每三天備份一次，每天一次增量備份，每15分鐘一次記錄備份；

 

　　（2）備份恢復

 

 　　　　這個真心沒看懂問的什麼意思。

 

　　（3） 備份恢複失敗如何處理

 

　　　　答：檢查表是否損壞，正常則再重新備份恢複；

 

9，  500台db，在最快時間之內重啟。
 
10，在當前的工作中，你碰到到的最大的mysql db問題是？

 

11，  innodb的讀寫參數最佳化

 

　　（1）    讀取參數，global buffer pool以及 local buffer 

 

　　　　　　Innodb_buffer_pool_size，理論上越大越好，建議伺服器50%~80%，實際為資料大小80%~90%即可；

 

　　　　　　Innodb_read_io_thread，根據處理器核心數決定；

 

　　　　　　Read_buffer_size;

 

　　　　　　Sort_buffer_size

 

　　（2）    寫入參數

 

　　　　　　Insert_buffer_size；

 

　　　　　　Innodb_double_write；

 

　　　　　　Innodb_write_io_thread

 

　　　　　　innodb_flush_method

 

　　（3）    與IO相關的參數

 

　　　　　　Innodb_log_buffer_size

 

　　　　　　innodb_flush_log_at_trx_commit

 

　　　　　　innodb_file_io_threads

 

　　　　　　innodb_max_dirty_pages_pct

 

     （4）緩衝參數以及緩衝的適用情境

 

 

12 ，請簡潔地描述下MySQL中InnoDB支援的四種交易隔離等級名稱，以及逐級之間的區別？

       未提交讀（uncommited read），提交讀（commited read），重複讀（repeatable read），串列讀（serializable）

 

       這四種隔離等級逐個提高，區別表現在髒讀，非重複讀，幻讀這三點，還有對並發的影響，隔離等級越高，並發性越差；

       所謂髒讀，就是同一事務中，會讀取還未提交的事務修改的資料；

       非重複讀，是指在同一事務中，在t1時刻，讀取某行資料時為A，t2時刻讀取同一行資料時，由於其他事務更新，這行資料已經發生改變；

       幻讀，是指在同一事務中，同一查詢多次進行時，由於其他事務的提交，插入新紀錄，導致每次查詢的結果都不同；

 　　

　　區別在於：

       未提交讀：會造成髒讀，非重複讀，幻讀；

       提交讀：不會造成髒讀，但是會有非重複讀，幻讀；

       重複讀：可能會造成幻讀；

       串列讀：不會造成髒讀，非重複讀，幻讀；

 

13，表中有大欄位X（例如：text類型），且欄位X不會經常更新，以讀為為主，請問

（1）    您 是選擇拆成子表，還是繼續放一起？

a)      放在子表中

（2）    寫出您這樣選擇的理由？

a)       避免大資料被頻繁的從buffer重換進換出，影響其他資料的緩衝；

14，MySQL中InnoDB引擎的行鎖是通過加在什麼上完成（或稱實現）的？為什麼是這樣子的？ 

       Innodb的行鎖是加在索引實現的；

　　 原因是:innodb是將primary key index和相關的行資料共同放在B+樹的分葉節點；innodb一定會有一個primary key，secondary index尋找的時候，也是通過找到對應的primary，再找對應的資料行；

mysql的面試試題