樂視mysql面試題

最近，朋友去樂視面試了mysql DBA，以下是我據整理的樂視mysql面試題答案，供大家參考

1. MYISAM和INNODB的不同？
答：主要有以下幾點區別：
   a)構造上的區別
     MyISAM在磁碟上儲存成三個檔案，其中.frm檔案儲存表定義；.MYD (MYData)為資料檔案；.MYI (MYIndex)為索引檔案。
     而innodb是由.frm檔案、表空間（分為獨立表空間或者共享表空間）和日誌檔案（redo log）組成。
    b)事務上的區別
      myisam不支援事務；而innodb支援事務。
    c)鎖上的區別
 myisam使用的是表鎖；而innodb使用的行鎖（當然innodb也支援表鎖）。
 表級鎖：直接鎖定整張表，在鎖定期間，其他程式無法對該表進行寫操作，如果設定的是寫鎖，那麼其他程式讀也不允許，因此myisam支援的併發量低，但myisam不會出現死鎖；
 行級鎖：只對指定的行進行鎖定，其他程式還是可以對錶中的其他行進行操作的。因此行鎖能大大的減少資料庫操作的衝突，但有時會導致死鎖。
    d)是否支援外來鍵的區別
 myisam不支援外來鍵，innodb支援外來鍵
    e) select count(*)的區別
 對於沒有where的count(*)使用MyISAM要比InnoDB快得多。因為MyISAM內建了一個計數器，count(*)時它直接從計數器中讀，而InnoDB必須掃描全表。
    f)myisam只把索引都load到記憶體中，而innodb儲存引擎是把資料和索引都load到記憶體中

2. 公司現有的資料庫架構，總共有幾組mysql庫？
答：我們公司現在有兩組MySQL。其中一套是生產庫，一套是測試庫。
    生產庫和測試庫都是用的mha +半同步複製做的高可用。
    我們所有的專案web前端量（大概有10個專案）指向的都是一個機器上的mysql例項。因為我們是傳統行業，併發訪問量並不是很大，所以目前我們的生產mysql資料庫未出現效能問題。

3. 如何提高insert的效能？
答：有如下方法：
     a)合併多條 insert 為一條，即： insert into t values(a,b,c),  (d,e,f) ,,,
       原因分析：主要原因是多條insert合併後日志量（MySQL的binlog和innodb的事務讓日誌） 減少了，降低日誌刷盤的資料量和頻率，從而提高效率。透過合併SQL語句，同時也能減少SQL語句解析的次數，減少網路傳輸的IO。
     b)修改引數 bulk_insert_buffer_size， 調大批次插入的快取；
     c)設定 innodb_flush_log_at_trx_commit = 0 ，相對於 innodb_flush_log_at_trx_commit = 1 可以十分明顯的提升匯入速度；
      （備註：innodb_flush_log_at_trx_commit 引數對 InnoDB Log 的寫入效能有非常關鍵的影響。該引數可以設定為0，1，2，解釋如下：
　　  0：log buffer中的資料將以每秒一次的頻率寫入到log file中，且同時會進行檔案系統到磁碟的同步操作，但是每個事務的commit並不會觸發任何log buffer 到log file  的重新整理或者檔案系統到磁碟的重新整理操作;
　　  1：在每次事務提交的時候將log buffer 中的資料都會寫入到log file，同時也會觸發檔案系統到磁碟的同步;
　　  2：事務提交會觸發log buffer 到log file的重新整理，但並不會觸發磁碟檔案系統到磁碟的同步。此外，每秒會有一次檔案系統到磁碟同步操作。
        ）

      d）手動使用事務
           因為mysql預設是autocommit的，這樣每插入一條資料，都會進行一次commit；所以，為了減少建立事務的消耗，我們可用手工使用事務，即START TRANSACTION;insert 。。,insert。。 commit；即執行多個insert後再一起提交；一般1000條insert 提交一次。

4. 和上一個問題相關，如果insert等dml語句的效能有問題的話，或者其他問題的存在，可能造成同步延遲，所以如何有效避免同步延遲的出現？

答：MySQL主從同步延遲的最主要原因就是主庫是多執行緒寫，而從庫只有一個執行緒（即slave_sql_running）來同步，所以在主庫中如果有一個ddl或dml操作執行10分鐘，那麼這個操作在從庫上同樣需要執行10分鐘。有人可能會問：“主庫上那個相同的DDL、DML也需要執行10分，為什麼slave會延時？”，答案是master可以併發，Slave_SQL_Running執行緒卻不可以。
所以，為了減少從庫的延時，我們需要平時做好以下維護：
   a)儘量讓主庫的dml或者ddl快速執行，如提高insert的效率（方法見上）；
   b） 為了安全，有人可能會將主庫的sync_binlog設定為1，innodb_flush_log_at_trx_commit也設定為1之類的，而slave則不需要這麼高的資料安全，完全可以講sync_binlog設定為0或者關閉binlog，innodb_flushlog也可以設定為0，來提高從庫sql的執行效率。
   （備註：sync_binlog是控制binlog_cache重新整理到磁碟binlog頻率的，而innodb_flush_log_at_trx_commit是控制redo log buffer重新整理到磁碟redolog頻率的。sync_binlog=0，表示MySQL不控制binlog的重新整理，由檔案系統自己控制它的快取的重新整理。如果sync_binlog>0，表示每sync_binlog次事務提交，MySQL呼叫檔案系統的重新整理操作將快取刷下去。最安全的就是sync_binlog=1了，表示每次事務提交，MySQL都會把binlog刷下去。這樣的話，在資料庫所在的主機作業系統損壞或者突然掉電的情況下，系統才有可能丟失1個事務的資料。所以sync_binlog=1保證了資料安全，但是效能最差。）
    c）使用比主庫更好的硬體裝置作為slave
    d) 使用mysql 5.6新引數 slave_parallel_workers ，使從庫多執行緒，不過，slave_parallel_workers只能支援一個例項下多個 database 間的併發複製，並不能真正做到多表併發複製。因此在較大併發負載時，slave還是沒有辦法及時追上master，需要想辦法進行最佳化。
    e）升級Mysql到5.7，因為mysql 5.7支援真正意義的從庫多執行緒了，即主庫多少執行緒，從庫也多少執行緒。mysql 5.7號稱主從複製永不丟資料（一直沒時間試用過）。

5. 有沒有用GTID,對GTID瞭解嗎？
答：用過GTID。曾經民航局的一個專案就用的是GTID。
    GTID是mysql 5.6的新東西，用事務提交號替換binlog的位置號。不過GTID這個東西在5.6還是有很多侷限性的，個人不建議用。
    GTID的全稱為 global transaction identifier  ， 可以翻譯為全域性事務標示符。
    GTID由兩部分組成：GTID = source_id:transaction_id
    source_id用於標示源伺服器，用server_uuid來表示，這個值在第一次啟動時生成，並寫入到配置檔案data/auto.cnf中
    transaction_id則是根據在源伺服器上第幾個提交的事務來確定。

6. Innodb是行鎖，那什麼時候會產生行鎖，什麼情況下會變成表鎖？
答：一般情況下，innodb只對指定的行進行鎖定，其他程式還是可以對錶中的其他行進行操作的，因此，這時候innodb加的就是行鎖；
   但是，如果在執行一個SQL語句時MySQL不能確定要掃描的範圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%”。

7. 使用過其他分支版本的資料庫嗎？percona,mariadb等。對percona的pxc叢集瞭解嗎？

答：除了oracle旗下的MySQL外，我還使用過percona server。percona是在源生mysql的基礎上，進行了最佳化和改進，所以percona的效能比mysql更好。目前，我知道percona提供免費的執行緒池功能，而社群版的mysql沒有執行緒池的功能（當然，企業版的mysql是有執行緒池的，但是需要收費）；另外percona還支援NUMA等功能。
    我熟悉pxc，我曾經在測試環境搭建過pxc，但是沒有在生產上使用，因為目前使用pxc的企業不是很多，目前我知道搜狐在用pxc。
    pxc是摒棄mysql主從的概念，即對於pxc來說，每個節點都可以讀寫，並且寫一份資料，其他節點會同時擁有，這是一種同步的複製方案（區別於Mysql主從的非同步複製）

8. 除了mysql，還了解過其他資料庫嗎？oracle，redis，mongodb等。

答：除了mysql，我還熟悉oracle，對oracle有兩年的使用經驗。
    不過，我對redis和mongodb沒有接觸過，如果工作需要，我會學習他們。

9. 工作中遇到的最大的問題以及做的最好的工作？
答：自由發揮

10. 分庫分表有沒有用到，怎麼實現的？

答：目前，根據我們的業務量，還沒有使用分庫分表。但是我有在關注MySQL的分散式方案，以前mysql分散式比較常用的方法是用阿里巴巴的cobar，將一張表水平拆分成多份分別放入不同的庫來實現表的水平拆分，或將不同的表放入不同的庫，但是後來發現cobar有一個問題一直不能很好的解決。目前，我關注到有很多人用mycat替換了cobar。

11. 新建立的資料庫，需要調整哪些引數？
答：調整兩方面的引數，即調整作業系統的和資料庫my.cnf的：
    a)作業系統的引數
      linux引數系統的預設引數很多都是很保守的，所以需要根據伺服器效能將一些引數進行加大，如我會調整nofile（最大檔案控制程式碼數）和nproc（最大執行緒數），將其放到最大；我會將vm.swappiness設定為0，表示最大限度使用實體記憶體，然後才是 swap空間；我會將net.ipv4.tcp_tw_reuse 設定為1，表示將netstat中出現的TIME-WAIT狀態的sockets重用到新的TCP連線上...等等
    b）資料庫的引數
 對於mysql來說，my.cnf的引數調整非常重要，如果採用預設值，那麼是很難發揮mysql效能的。一般我會特別關注innodb_buffer_pool這個值，該值一般設定為實體記憶體的70%,這樣就可以把mysql的表和索引最大限度的load到記憶體中，從而使mysql資料庫效能得到大的提升；另外，我還特別關注sync_binlog和innodb_flush_log_at_trx_commit這兩個值的設定，具體含義見上；還有max_user_connections ，我一般將該值設定為2000；還有innodb_lock_wait_timeout，看程式是長連線還是短連線，一般我會設定為60秒；還有innodb_log_file_size ，這個值也設定的大一點，我一般設定的為500M或1G。

12. mysql的許可權怎麼管理？
答：只給insert,update，select和delete四個許可權即可。有時候delete都不給。

13. 有開發基礎嗎？
答：沒有

14. 如果發現CPU，或者IO壓力很大，怎麼定位問題？

答：
   1、首先我會用top命令和iostat命令，定位是什麼程式在佔用cpu和磁碟io； 
   2、如果是mysql的問題，我會登入到資料庫，透過show full processlist命令，看現在資料庫在執行什麼sql語句，是否有語句長時間執行使資料庫卡住；
   3、執行show innodb engine status命令，檢視資料庫是否有鎖資源爭用；
   4、檢視mysql慢查詢日誌，看是否有慢sql；
   5、找到引起資料庫佔用資源高的語句，進行最佳化，該建索引的建索引，索引不合適的刪索引，或者根據情況kill掉耗費資源的sql語句等