這些年背過的面試題——MySQL篇

• 列儲存 Hbase
• K-V儲存 Redis
• 影像儲存 Neo4j
• 文件儲存 MongoDB
• 雲端儲存OSS

• 使用者行為日誌收集系統收集日誌之後推送到ETL做資料的清洗和轉換

• 把ETL過後的資料傳送到推薦引擎計算每個消費者的推薦結果，其中推薦邏輯包括規則和演算法兩部分

• 收集使用者最近瀏覽、最長停留等特徵，分析商品相似性、使用者相似性、相似性等演算法。

• 把推薦引擎的結果存入Aerospike叢集中，並提供給廣告投放引擎實時獲取

  分別透過HDFS和HBASE對日誌進行離線和實時的分析，然後把使用者畫像的標籤(tag : 程式猿、宅男...)結果存入高效能的Nosql資料庫Aerospike中，同時把資料備份到異地資料中心。前端廣告投放請求透過決策引擎（投放引擎）向使用者畫像資料庫中讀取相應的使用者畫像資料，然後根據競價演算法出價進行競價。競價成功之後就可以展現廣告了。而在競價成功之後，具體給使用者展現什麼樣的廣告，就是有上面說的個性化推薦廣告來完成的。

Neo4j是一個開源基於java開發的圖形noSql資料庫，它將結構化資料儲存在圖中而不是表中。它是一個嵌入式的、基於磁碟的、具備完全的事務特性的Java持久化引擎。程式資料是在一個物件導向的、靈活的網路結構下，而不是嚴格的表中，但具備完全的事務特性、企業級的資料庫的所有好處。

• 優勢總結:
  

• 效能上，使用cql查詢，對長程關係的查詢速度快

• 擅於發現隱藏的關係，例如透過判斷圖上兩點之間有沒有走的通的路徑，就可以發現事物間的關聯

// 查詢三層級關係節點如下：with可以將前面查詢結果作為後面查詢條件match (na:Person)-[re]-(nb:Person) where na.name="林婉兒" WITH na,re,nb match (nb:Person)- [re2:Friends]->(nc:Person) return na,re,nb,re2,nc// 直接拼接關係節點查詢match data=(na:Person{name:"範閒"})-[re]->(nb:Person)-[re2]->(nc:Person) return data// 使用深度運算子顯然使用以上方式比較繁瑣,可變數量的關係->節點可以使用-[:TYPE*minHops..maxHops]-。match data=(na:Person{name:"範閒"})-[*1..2]-(nb:Person) return data

MongoDB 是一個基於分散式檔案儲存的資料庫，是非關聯式資料庫中功能最豐富、最像關聯式資料庫的。在高負載的情況下，透過新增更多的節點，可以保證伺服器效能。由 C++ 編寫，可以為 WEB 應用提供可擴充套件、高效能、易部署的資料儲存解決方案。

{key:value,key2:value2}和Json類似，是一種二進位制形式的儲存格式，支援內嵌的文件物件和陣列物件，但是BSON有JSON沒有的一些資料型別，比如 value包括字串,double,Array,DateBSON可以做為網路資料交換的一種儲存形式,它的優點是靈活性高，但它的缺點是空間利用率不是很理想。

/* 查詢 find() 方法可以傳入多個鍵(key)，每個鍵(key)以逗號隔開*/db.collection.find({key1:value1, key2:value2}).pretty()/* 更新 $set ：設定欄位值 $unset :刪除指定欄位 $inc：對修改的值進行自增*/db.collection.update({where},{$set:{欄位名:值}},{multi:true})/* 刪除 justOne :如果設為true，只刪除一個文件，預設false，刪除所有匹配條件的文件*/db.collection.remove({where}, {justOne: , writeConcern:  } )

• 文件結構的儲存方式，能夠更便捷的獲取資料。

  對於一個層級式的資料結構來說，使用扁平式的，表狀的結構來查詢儲存資料非常的困難。

• 內建GridFS，支援大容量的儲存。
  GridFS是一個出色的分散式檔案系統，支援海量的資料儲存，滿足對大資料集的快速範圍查詢。

• 效能優越

  千萬級別的文件物件，近10G的資料，對有索引的ID的查詢 不會比mysql慢，而對非索引欄位的查詢，則是全面勝出。 mysql實際無法勝任大資料量下任意欄位的查詢，而mongodb的查詢效能實在牛逼。寫入效能同樣很令人滿意，同樣寫入百萬級別的資料，mongodb基本10分鐘以下可以解決。

• 不支援事務
• 磁碟佔用空間大

MySQL 8.0 版本

1、開通服務

2、建立儲存空間

3、上傳檔案、下載檔案、刪除檔案

4、域名繫結、日誌記錄

圖片編輯（裁剪、模糊、水印）

影片截圖

開源的輕量級分散式檔案系統。它對檔案進行管理，功能包括：檔案儲存、檔案同步、檔案訪問（檔案上傳、檔案下載）等，解決了大容量儲存和負載均衡的問題。使用FastDFS很容易搭建一套高效能的檔案伺服器叢集提供檔案上傳、下載等服務。如相簿網站、影片網站等。

• 和流行的web server無縫銜接，FastDFS已提供apache和nginx擴充套件模組

• 檔案ID由FastDFS生成，作為檔案訪問憑證，FastDFS不需要傳統的name server

• 分組儲存，靈活簡潔、對等結構，不存在單點

• 檔案不分塊儲存，上傳的檔案和OS檔案系統中的檔案一一對應

• 中、小檔案均可以很好支援，支援海量小檔案儲存

• 支援相同內容的檔案只儲存一份，節約磁碟空間

• 支援多塊磁碟，支援單盤資料恢復

• 支援線上擴容 支援主從檔案

• 下載檔案支援多執行緒方式，支援斷點續傳

• 客戶端（client）

  透過專有介面，使用TCP/IP協議與跟蹤器伺服器或儲存節點進行資料互動。

• 跟蹤器（tracker）

  Trackerserver作用是負載均衡和排程，透過Tracker server在檔案上傳時可以根據策略找到檔案上傳的地址。Tracker在訪問上起負載均衡的作用。

• 儲存節點（storage）

  Storageserver作用是檔案儲存，客戶端上傳的檔案最終儲存在Storage伺服器上，Storage server沒有實現自己的檔案系統而是利用作業系統的檔案系統來管理檔案。儲存節點中的伺服器均可以隨時增加或下線而不會影響線上服務。

// FastDFS採用記憶體池的做法。 // v5.04對預分配採用增量方式，tracker一次預分配 1024個，storage一次預分配256個。 max_connections = 10240// 根據實際需要將 max_connections 設定為一個較大的數值，比如 10240 甚至更大。// 同時需要將一個程式允許開啟的最大檔案數調大vi /etc/security/limits.conf 重啟系統生效 * soft nofile 65535 * hard nofile 65535

work_threads = 4 // 說明：為了避免CPU上下文切換的開銷，以及不必要的資源消耗，不建議將本引數設定得過大。// 公式為： work_threads + (reader_threads + writer_threads) = CPU數

// 對於單盤掛載方式，磁碟讀寫執行緒分 別設定為 1即可 // 如果磁碟做了RAID，那麼需要酌情加大讀寫執行緒數，這樣才能最大程度地發揮磁碟效能disk_rw_separated：磁碟讀寫是否分離 disk_reader_threads：單個磁碟讀執行緒數 disk_writer_threads：單個磁碟寫執行緒數

select id from table_xx where id = ? and version = Vupdate id from table_xx where id = ? and version = V+1

select id from table_xx where id > 100 for update;select id from table_xx where id > 100 lock in share mode;

CREATE  [UNIQUE | FULLTEXT]  INDEX  索引名 ON  表名(欄位名) [USING 索引方法]；說明：UNIQUE:可選。表示索引為唯一性索引。FULLTEXT:可選。表示索引為全文索引。INDEX和KEY:用於指定欄位為索引，兩者選擇其中之一就可以了，作用是一樣的。索引名:可選。給建立的索引取一個新名稱。欄位名1:指定索引對應的欄位的名稱，該欄位必須是前面定義好的欄位。注：索引方法預設使用B+TREE。

select * from student  A where A.age='18' and A.name='張三';

mysql> explain select * from staff;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | staff | ALL  | NULL          | 索引  | NULL    | NULL |    2 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set

SELECT uid From user Where gid = 2 order by ctime asc limit 10ALTER TABLE user add index idx_gid_ctime_uid(gid,ctime,uid) #建立聯合覆蓋索引，避免回表查詢

select * from mytbl order by id limit 100000,10  改進後的SQL語句如下：select * from mytbl where id >= ( select id from mytbl order by id limit 100000,1 ) limit 10select * from mytbl inner ori join (select id from mytbl order by id limit 100000,10) as tmp on tmp.id=ori.id;

• 原理：將主伺服器的binlog日誌複製到從伺服器上執行一遍，達到主從資料的一致狀態。
• 過程：從庫開啟一個I/O執行緒，向主庫請求Binlog日誌。主節點開啟一個binlog dump執行緒，檢查自己的二進位制日誌，併傳送給從節點；從庫將接收到的資料儲存到中繼日誌（Relay log）中，另外開啟一個SQL執行緒，把Relay中的操作在自身機器上執行一遍
• 優點：
• 作為備用資料庫，並且不影響業務

• 可做讀寫分離，一個寫庫，一個或多個讀庫，在不同的伺服器上，充分發揮伺服器和資料庫的效能，但要保證資料的一致性

1. 虛擬IP或DNS服務 （Keepalived +VIP/DNS  和 MMM 架構）

問題：在虛擬 IP 運維過程中，重新整理ARP過程中有時會出現一個 VIP 繫結在多臺伺服器同時提供連線的問題。這也是為什麼要避免使用 Keepalived+VIP 和 MMM 架構的原因之一，因為它處理不了這類問題而導致叢集多點寫入。

2. 提升備庫為主庫（MHA、QMHA）

如何進行分庫分表

分表使用者id進行分表，每個表控制在300萬資料。

分庫根據業務場景和地域分庫，每個庫併發不超過2000

• id保證業務在關聯多張表時可以在同一庫上操作
• range方便擴容和資料統計
• hash可以使得資料更加平均

垂直拆分：一個表拆成多個表，可以將一些冷資料拆分到冗餘庫中

不是寫瓶頸優先進行分表

• 分庫資料間的資料無法再透過資料庫直接查詢了。會產生深分頁的問題

• 分庫越多，出現問題的可能性越大，維護成本也變得更高。

• 分庫後無法保障跨庫間事務，只能藉助其他中介軟體實現最終一致性。

• 富查詢：採用分庫分表之後，如何滿足跨越分庫的查詢？使用ES的寬表

  藉助分庫閘道器+分庫業務雖然能夠實現多維度查詢的能力，但整體上效能不佳且對正常的寫入請求有一定的影響。業界應對多維度實時查詢的最常見方式便是藉助 ElasticSearch；

• 資料傾斜：資料分庫基礎上再進行分表；

• 分散式事務：跨多庫的修改及多個微服務間的寫操作導致的分散式事務問題？

• 深分頁問題：按遊標查詢，或者叫每次查詢都帶上上一次查詢經過排序後的最大 ID；

• 線上系統裡所有寫庫的地方，增刪改操作，除了對老庫增刪改，都加上對新庫的增刪改；

• 系統部署以後，還需要跑程式讀老庫資料寫新庫，寫的時候需要判斷updateTime；

• 迴圈執行，直至兩個庫的資料完全一致，最後重新部署分庫分表的程式碼就行了；

• 支援3萬的寫併發，配合MQ實現每秒10萬的寫入速度

• 讀寫分離6萬讀併發，配合分散式快取每秒100讀併發

• 2000張表每張300萬，可以最多寫入60億的資料

• 32張使用者表，支撐億級使用者，後續最多也就擴容一次

1. 推薦是 32 庫 * 32 表，對於我們公司來說，可能幾年都夠了。

2. 配置路由的規則，uid % 32 = 庫，uid / 32 % 32 = 表

3. 擴容的時候，申請增加更多的資料庫伺服器，呈倍數擴容

4. 由 DBA 負責將原先資料庫伺服器的庫，遷移到新的資料庫伺服器上去

5. 修改一下配置，重新發布系統，上線，原先的路由規則變都不用變

6. 直接可以基於 n 倍的資料庫伺服器的資源，繼續進行線上系統的提供服務。

• 使用redis可以

• 併發不高可以單獨起一個服務，生成自增id

• 設定資料庫step自增步長可以支撐水平伸縮

• UUID適合檔名、編號，但是不適合做主鍵

• snowflake雪花演算法，綜合了41時間（ms）、10機器、12序列號（ms內自增）

show slave status

• 分庫，拆分為多個主庫，每個主庫的寫併發就減少了幾倍，主從延遲可以忽略不計。

• 重寫程式碼，寫程式碼的同學，要慎重，插入資料時立馬查詢可能查不到。

• 如果確實是存在必須先插入，立馬要求就查詢到，然後立馬就要反過來執行一些操作，對這個查詢設定直連主庫或者延遲查詢。主從複製延遲一般不會超過50ms

select * from user where name = "xxx" and community="other";

select * from user where 1=1

int getUserSize() {        List users = dao.getAllUser();        return null == users ? 0 : users.size();}