那些年背過的面試題—

本文是技術人面試系列 MySQL 篇，面試中關于 MySQL 都需要了解哪些基礎？一文帶你詳細了解，歡迎收藏！

WhyMysql？

NoSQL 數據庫四大家族

列存儲 Hbase
K-V 存儲 Redis
圖像存儲 Neo4j
文檔存儲 MongoDB
云存儲 OSS

海量 Aerospike

Aerospike（簡稱 AS）是一個分布式，可擴展的鍵值存儲的 NoSQL 數據庫。T 級別大數據高并發的結構化數據存儲，采用混合架構，索引存儲在內存中，而數據可存儲在機械硬盤 (HDD) 或固態硬盤(SSD) 上，讀寫操作達微妙級，99% 的響應可在 1 毫秒內實現。

Aerospike 作為一個大容量的 NoSql 解決方案，適合對容量要求比較大，QPS 相對低一些的場景，主要用在廣告行業，個性化推薦廠告是建立在了和掌握消費者獨特的偏好和習性的基礎之上，對消費者的購買需求做出準確的預測或引導，在合適的位置、合適的時間，以合適的形式向消費者呈現與其需求高度吻合的廣告，以此來促進用戶的消費行為。

（ETL 數據倉庫技術）抽取（extract）、轉換（transform）、加載（load）

用戶行為日志收集系統收集日志之后推送到 ETL 做數據的清洗和轉換
把 ETL 過后的數據發送到推薦引擎計算每個消費者的推薦結果，其中推薦邏輯包括規則和算法兩部分
收集用戶最近瀏覽、最長停留等特征，分析商品相似性、用戶相似性、相似性等算法。
把推薦引擎的結果存入 Aerospike 集群中，并提供給廣告投放引擎實時獲取

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

圖譜 Neo4j

Neo4j 是一個開源基于 java 開發的圖形 noSql 數據庫，它將結構化數據存儲在圖中而不是表中。它是一個嵌入式的、基于磁盤的、具備完全的事務特性的 Java 持久化引擎。程序數據是在一個面向對象的、靈活的網絡結構下，而不是嚴格的表中，但具備完全的事務特性、企業級的數據庫的所有好處。

一種基于圖的數據結構，由節點 (Node) 和邊 (Edge) 組成。其中節點即實體，由一個全局唯一的 ID 標示，邊就是關系用于連接兩個節點。通俗地講，知識圖譜就是把所有不同種類的信息，連接在一起而得到的一個關系網絡。知識圖譜提供了從 “關系” 的角度去分析問題的能力。

互聯網、大數據的背景下，谷歌、百度、搜狗等搜索引擎紛紛基于該背景，創建自己的知識圖 Knowledge Graph、知心和知立方，主要用于改進搜索質量。

自己項目主要用作好友推薦，圖數據庫 (Graph database) 指的是以圖數據結構的形式來存儲和查詢數據的數據庫。關系圖譜中，關系的組織形式采用的就是圖結構，所以非常適合用圖庫進行存儲。

優勢總結:
- 性能上，使用 cql 查詢，對長程關系的查詢速度快
- 擅于發現隱藏的關系，例如通過判斷圖上兩點之間有沒有走的通的路徑，就可以發現事物間的關聯

// 查詢三層級關系節點如下：with可以將前面查詢結果作為后面查詢條件match (na:Person)-[re]-(nb:Person) where na. 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

MongoDB 是一個基于分布式文件存儲的數據庫，是非關系數據庫中功能最豐富、最像關系數據庫的。在高負載的情況下，通過添加更多的節點，可以保證服務器性能。由 C++ 編寫，可以為 WEB 應用提供可擴展、高性能、易部署的數據存儲解決方案。

什么是 BSON

{key:value,key2:value2} 和 Json 類似，是一種二進制形式的存儲格式，支持內嵌的文檔對象和數組對象，但是 BSON 有 JSON 沒有的一些數據類型，比如 value 包括字符串, double,Array,DateBSON 可以做為網絡數據交換的一種存儲形式, 它的優點是靈活性高，但它的缺點是空間利用率不是很理想。

BSON 有三個特點：輕量性、可遍歷性、高效性

/* 查詢 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: <boolean>, writeConcern: <回執> } )

優點：

文檔結構的存儲方式，能夠更便捷的獲取數據。

對于一個層級式的數據結構來說，使用扁平式的，表狀的結構來查詢保存數據非常的困難。
內置 GridFS，支持大容量的存儲。

GridFS 是一個出色的分布式文件系統，支持海量的數據存儲，滿足對大數據集的快速范圍查詢。
性能優越

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

缺點：

不支持事務
磁盤占用空間大

MySQL 8.0 版本

性能： MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。
NoSQL： MySQL 從 5.7 版本開始提供 NoSQL 存儲功能，在 8.0 版本中 nosql 得到了更大的改進。
窗口函數： 實現若干新的查詢方式。窗口函數與 SUM()、COUNT() 這種集合函數類似，但它不會將多行查詢結果合并為一行，而是將結果放回多行當中，即窗口函數不需要 GROUP BY。
隱藏索引： 在 MySQL 8.0 中，索引可以被 “隱藏” 和“顯示”。當對索引進行隱藏時，它不會被查詢優化器所使用。我們可以使用這個特性用于性能調試，例如我們先隱藏一個索引，然后觀察其對數據庫的影響。如果數據庫性能有所下降，說明這個索引是有用的，然后將其 “恢復顯示” 即可；如果數據庫性能看不出變化，說明這個索引是多余的，可以考慮刪掉。

云存儲

使用步驟

1、開通服務

2、創建存儲空間

3、上傳文件、下載文件、刪除文件

4、域名綁定、日志記錄

5、根據開放接口進行鑒權訪問

功能

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

視頻截圖

音頻轉碼、視頻修復

CDN 加速

對象存儲 OSS 與阿里云 CDN 服務結合，可優化靜態熱點文件下載加速的場景（即同一地區大量用戶同時下載同一個靜態文件的場景）。可以將 OSS 的存儲空間（Bucket）作為源站，利用阿里云 CDN 將源內容發布到邊緣節點。當大量終端用戶重復訪問同一文件時，可以直接從邊緣節點獲取已緩存的數據，提高訪問的響應速度。

FastDFS

開源的輕量級分布式文件系統。它對文件進行管理，功能包括：文件存儲、文件同步、文件訪問（文件上傳、文件下載）等，解決了大容量存儲和負載均衡的問題。使用 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 沒有實現自己的文件系統而是利用操作系統的文件系統來管理文件。存儲節點中的服務器均可以隨時增加或下線而不會影響線上服務。

上傳

下載

斷點續傳

續傳涉及到的文件大小 MD5 不會改變。續傳流程與文件上傳類似，先定位到源 storage，完成完整或部分上傳，再通過 binlog 進行同 group 內 server 文件同步。

配置優化

配置文件：tracker.conf 和 storage.conf

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

避免重復

如何避免文件重復上傳解決方案上傳成功后計算文件對應的 MD5 然后存入 MySQL, 添加文件時把**文件 MD5 和之前存入 MYSQL 中的存儲的信息對比?。**DigestUtils.md5DigestAsHex(bytes)。

事務

1、事務 4 大特性

**事務 4 大特性：**原子性、一致性、隔離性、持久性

原?性：?事務是最?的執行單位，不允許分割。事務的原?性確保動作要么全部完成，要么全不執行

一致性：?執行事務前后，數據保持?致，多個事務對同?個數據讀取的結果是相同的；

隔離性：?并發訪問數據庫時，?個?戶的事務不被其他事務所?擾，各并發事務之間數據庫是獨?的；

持久性：??個事務被提交之后。它對數據庫中數據的改變是持久的，即使數據庫發?故障也不應該對其有任何影響。

實現保證：

MySQL 的存儲引擎 InnoDB 使用重做日志保證一致性與持久性，回滾日志保證原子性，使用各種鎖來保證隔離性。

2、事務隔離級別

**讀未提交：**最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重復讀。

**讀已提交：**允許讀取并發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重復讀仍有可能發?。

**可重復讀：**同?字段的多次讀取結果都是?致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重復讀，會有幻讀。

**串行化：**最?的隔離級別，完全服從 ACID 的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產??擾。

3、默認隔離級別 - RR

**默認隔離級別：**可重復讀；

同?字段的多次讀取結果都是?致的，除非數據是被本身事務自己所修改；

可重復讀是有可能出現幻讀的，如果要保證絕對的安全只能把隔離級別設置成 SERIALIZABLE；這樣所有事務都只能順序執行，自然不會因為并發有什么影響了，但是性能會下降許多。

第二種方式，使用 MVCC 解決快照讀幻讀問題（如簡單 select），讀取的不是最新的數據。維護一個字段作為 version，這樣可以控制到每次只能有一個人更新一個版本。

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

第三種方式，如果需要讀最新的數據，可以通過 GapLock+Next-KeyLock 可以解決當前讀幻讀問題，

// 對于單盤掛載方式，磁盤讀寫線程分 別設置為 1即可 
// 如果磁盤做了RAID，那么需要酌情加大讀寫線程數，這樣才能最大程度地發揮磁盤性能disk_rw_separated：磁盤讀寫是否分離 disk_reader_threads：單個磁盤讀線程數 disk_writer_threads：單個磁盤寫線程數

4、RR 和 RC 使用場景

事務隔離級別 RC(read commit) 和 RR（repeatable read）兩種事務隔離級別基于多版本并發控制 MVCC(multi-version concurrency control）來實現。

5、行鎖，表鎖，意向鎖

InnoDB 支持行級鎖 (row-level locking) 和表級鎖, 默認為行級鎖

InnoDB 按照不同的分類的鎖：

共享 / 排它鎖 (Shared and Exclusive Locks)：行級別鎖，

意向鎖 (Intention Locks)，表級別鎖

間隙鎖 (Gap Locks)，鎖定一個區間

記錄鎖 (Record Locks)，鎖定一個行記錄

表級鎖：（串行化）

Mysql 中鎖定粒度最大的一種鎖，對當前操作的整張表加鎖，實現簡單，資源消耗也比較少，加鎖快，不會出現死鎖。其鎖定粒度最大，觸發鎖沖突的概率最高，并發度最低，MyISAM 和 InnoDB 引擎都支持表級鎖。

行級鎖：（RR、RC）

Mysql 中鎖定粒度最小的一種鎖，只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小，并發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。 InnoDB 支持的行級鎖，包括如下幾種：

記錄鎖（Record Lock）:?對索引項加鎖，鎖定符合條件的行。其他事務不能修改和刪除加鎖項；

間隙鎖（Gap Lock）: 對索引項之間的 “間隙” 加鎖，鎖定記錄的范圍，不包含索引項本身，其他事務不能在鎖范圍內插入數據。

Next-key Lock：?鎖定索引項本身和索引范圍。即 Record Lock 和 Gap Lock 的結合。可解決幻讀問題。

InnoDB 支持多粒度鎖（multiple granularity locking），它允許行級鎖與表級鎖共存，而意向鎖就是其中的一種表鎖。

共享鎖（ shared lock, S ）鎖允許持有鎖讀取行的事務。加鎖時將自己和子節點全加 S 鎖，父節點直到表頭全加 IS 鎖

排他鎖（ exclusive lock， X ）鎖允許持有鎖修改行的事務。加鎖時將自己和子節點全加 X 鎖，父節點直到表頭全加 IX 鎖

意向共享鎖（intention shared lock, IS）：事務有意向對表中的某些行加共享鎖（S 鎖）

意向排他鎖（intention exclusive lock, IX）：事務有意向對表中的某些行加排他鎖（X 鎖）

6、MVCC 多版本并發控制

MVCC 是一種多版本并發控制機制，通過事務的可見性看到自己預期的數據，能降低其系統開銷。（RC 和 RR 級別工作）

InnoDB 的 MVCC, 是通過在每行記錄后面保存系統版本號 (可以理解為事務的 ID)，每開始一個新的事務，系統版本號就會自動遞增，事務開始時刻的系統版本號會作為事務的 ID。這樣可以確保事務讀取的行，要么是在事務開始前已經存在的，要么是事務自身插入或者修改過的，防止幻讀的產生。

1.MVCC 手段只適用于 Msyql 隔離級別中的讀已提交（Read committed）和可重復讀（Repeatable Read）.

2.Read uncimmitted 由于存在臟讀，即能讀到未提交事務的數據行，所以不適用 MVCC.

簡單的 select 快照度不會加鎖，刪改及 select for update 等需要當前讀的場景會加鎖

原因是 MVCC 的創建版本和刪除版本只要在事務提交后才會產生。客觀上，mysql 使用的是樂觀鎖的一整實現方式，就是每行都有版本號，保存時根據版本號決定是否成功。Innodb 的 MVCC 使用到的快照存儲在 Undo 日志中，該日志通過回滾指針把一個數據行所有快照連接起來。

版本鏈

在 InnoDB 引擎表中，它的聚簇索引記錄中有兩個必要的隱藏列：

trx_id

這個 id 用來存儲的每次對某條聚簇索引記錄進行修改的時候的事務 id。

roll_pointer

每次對哪條聚簇索引記錄有修改的時候，都會把老版本寫入 undo 日志中。這個 roll_pointer 就是存了一個指針，它指向這條聚簇索引記錄的上一個版本的位置，通過它來獲得上一個版本的記錄信息。(注意插入操作的 undo 日志沒有這個屬性，因為它沒有老版本)

每次修改都會在版本鏈中記錄。SELECT 可以去版本鏈中拿記錄，這就實現了讀 - 寫，寫 - 讀的并發執行，提升了系統的性能。

索引

1、Innodb 和 Myisam 引擎

**Myisam：**支持表鎖，適合讀密集的場景，不支持外鍵，不支持事務，索引與數據在不同的文件

**Innodb：**支持行、表鎖，默認為行鎖，適合并發場景，支持外鍵，支持事務，索引與數據同一文件

2、哈希索引

哈希索引用索引列的值計算該值的 hashCode，然后在 hashCode 相應的位置存執該值所在行數據的物理位置，因為使用散列算法，因此訪問速度非常快，但是一個值只能對應一個 hashCode，而且是散列的分布方式，因此哈希索引不支持范圍查找和排序的功能。

3、B + 樹索引

優點：

B + 樹的磁盤讀寫代價低，更少的查詢次數，查詢效率更加穩定，有利于對數據庫的掃描

B + 樹是 B 樹的升級版，B + 樹只有葉節點存放數據，其余節點用來索引。索引節點可以全部加入內存，增加查詢效率，葉子節點可以做雙向鏈表，從而提高范圍查找的效率，增加的索引的范圍。

在大規模數據存儲的時候，紅黑樹往往出現由于樹的深度 過大而造成磁盤 IO 讀寫過于頻繁，進而導致效率低下的情況。所以，只要我們通過某種較好的樹結構減少樹的結構盡量減少樹的高度，B 樹與 B + 樹可以有多個子女，從幾十到上千，可以降低樹的高度。

磁盤預讀原理：將一個節點的大小設為等于一個頁，這樣每個節點只需要一次 I/O 就可以完全載入。為了達到這個目的，在實際實現 B-Tree 還需要使用如下技巧：每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現了一個 node 只需一次 I/O。

4、創建索引

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

5、聚簇索引和非聚簇索引

**聚簇索引：**將數據存儲與索引放到了一塊，索引結構的葉子節點保存了行數據（主鍵索引）

**非聚簇索引：**將數據與索引分開存儲，索引結構的葉子節點指向了數據對應的位置（輔助索引）

聚簇索引的葉子節點就是數據節點，而非聚簇索引的葉子節點仍然是索引節點，只不過有指向對應數據塊的指針。

6、最左前綴問題

最左前綴原則主要使用在聯合索引中，聯合索引的 B+Tree 是按照第一個關鍵字進行索引排列的。

聯合索引的底層是一顆 B + 樹，只不過聯合索引的 B + 樹節點中存儲的是鍵值。由于構建一棵 B + 樹只能根據一個值來確定索引關系，所以數據庫依賴聯合索引最左的字段來構建。

采用 >、< 等進行匹配都會導致后面的列無法走索引，因為通過以上方式匹配到的數據是不可知的。

SQL 查詢

1、SQL 語句的執行過程

查詢語句：

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

結合上面的說明，我們分析下這個語句的執行流程：

①通過客戶端 / 服務器通信協議與 MySQL 建立連接。并查詢是否有權限

②Mysql8.0 之前看是否開啟緩存，開啟了 Query Cache 且命中完全相同的 SQL 語句，則將查詢結果直接返回給客戶端；

③由解析器進行語法語義解析，并生成解析樹。如查詢是 select、表名 tb_student、條件是 id=‘1’

④查詢優化器生成執行計劃。根據索引看看是否可以優化

⑤查詢執行引擎執行 SQL 語句，根據存儲引擎類型，得到查詢結果。若開啟了 Query Cache，則緩存，否則直接返回。

2、回表查詢和覆蓋索引

普通索引（唯一索引 + 聯合索引 + 全文索引）需要掃描兩遍索引樹

（1）先通過普通索引定位到主鍵值 id=5；

（2）在通過聚集索引定位到行記錄；

這就是所謂的回表查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。

**覆蓋索引：**主鍵索引 == 聚簇索引 == 覆蓋索引

如果 where 條件的列和返回的數據在一個索引中，那么不需要回查表，那么就叫覆蓋索引。

**實現覆蓋索引：**常見的方法是，將被查詢的字段，建立到聯合索引里去。

3、Explain 及優化

參考：https://www.jianshu.com/p/8fab76bbf448

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

索引優化：

①最左前綴索引：like 只用于’string%'，語句中的 = 和 in 會動態調整順序

②唯一索引：唯一鍵區分度在 0.1 以上

③無法使用索引：!= 、is null 、 or、>< 、（5.7 以后根據數量自動判定） in 、not in

④聯合索引：避免 select * ，查詢列使用覆蓋索引

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

語句優化：

①char 固定長度查詢效率高，varchar 第一個字節記錄數據長度

②應該針對 Explain 中 Rows 增加索引

③group/order by 字段均會涉及索引

④Limit 中分頁查詢會隨著 start 值增大而變緩慢，通過子查詢 + 表連接解決

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 10
select * from mytbl inner ori join (select id from mytbl order by id limit 100000,10) as tmp on tmp.id=ori.id;

⑤count 會進行全表掃描，如果估算可以使用 explain

⑥delete 刪除表時會增加大量 undo 和 redo 日志，確定刪除可使用 trancate

表結構優化：

①單庫不超過 200 張表

②單表不超過 500w 數據

③單表不超過 40 列

④單表索引不超過 5 個

數據庫范式?：

①第一范式（1NF）列不可分割

②第二范式（2NF）屬性完全依賴于主鍵 [消除部分子函數依賴]

③第三范式（3NF）屬性不依賴于其它非主屬性 [消除傳遞依賴]

配置優化：

配置連接數、禁用 Swap、增加內存、升級 SSD 硬盤

4、JOIN 查詢

left join(左聯接) 返回包括左表中的所有記錄和右表中關聯字段相等的記錄

right join(右聯接) 返回包括右表中的所有記錄和左表中關聯字段相等的記錄

inner join(等值連接) 只返回兩個表中關聯字段相等的行

集群

1、主從復制過程

MySQl 主從復制：

原理： 將主服務器的 binlog 日志復制到從服務器上執行一遍，達到主從數據的一致狀態。
過程： 從庫開啟一個 I/O 線程，向主庫請求 Binlog 日志。主節點開啟一個 binlog dump 線程，檢查自己的二進制日志，并發送給從節點；從庫將接收到的數據保存到中繼日志（Relay log）中，另外開啟一個 SQL 線程，把 Relay 中的操作在自身機器上執行一遍
優點：
- 作為備用數據庫，并且不影響業務
- 可做讀寫分離，一個寫庫，一個或多個讀庫，在不同的服務器上，充分發揮服務器和數據庫的性能，但要保證數據的一致性

**binlog 記錄格式：**statement、row、mixed

基于語句 statement 的復制、基于行 row 的復制、基于語句和行（mix）的復制。其中基于 row 的復制方式更能保證主從庫數據的一致性，但日志量較大，在設置時考慮磁盤的空間問題。

2、數據一致性問題

“主從復制有延時”，這個延時期間讀取從庫，可能讀到不一致的數據。

緩存記錄寫 key 法：

在 cache 里記錄哪些記錄發生過的寫請求，來路由讀主庫還是讀從庫

異步復制：

在異步復制中，主庫執行完操作后，寫入 binlog 日志后，就返回客戶端，這一動作就結束了，并不會驗證從庫有沒有收到，完不完整，所以這樣可能會造成數據的不一致。

半同步復制：

當主庫每提交一個事務后，不會立即返回，而是等待其中一個從庫接收到 Binlog 并成功寫入 Relay-log 中才返回客戶端，通過一份在主庫的 Binlog，另一份在其中一個從庫的 Relay-log，可以保證了數據的安全性和一致性。

全同步復制：

指當主庫執行完一個事務，所有的從庫都執行了該事務才返回給客戶端。因為需要等待所有從庫執行完該事務才能返回，所以全同步復制的性能必然會收到嚴重的影響。

3、集群架構

Keepalived + VIP + MySQL 主從 / 雙主

當寫節點 Master db1 出現故障時，由 MMM Monitor 或 Keepalived 觸發切換腳本，將 VIP 漂移到可用的 Master db2 上。當出現網絡抖動或網絡分區時，MMM Monitor 會誤判，嚴重時來回切換寫 VIP 導致集群雙寫，當數據復制延遲時，應用程序會出現數據錯亂或數據沖突的故障。有效避免單點失效的架構就是采用共享存儲，單點故障切換可以通過分布式哨兵系統監控。

**架構選型：**MMM 集群 -> MHA 集群 -> MHA+Arksentinel。

4、故障轉移和恢復

轉移方式及恢復方法

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

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

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

嘗試將原 Master 設置 read_only 為 on，避免集群多點寫入。借助 binlog server 保留 Master 的 Binlog；當出現數據延遲時，再提升 Slave 為新 Master 之前需要進行數據補齊，否則會丟失數據。

面試題

分庫分表

如何進行分庫分表

分表用戶 id 進行分表，每個表控制在 300 萬數據。

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

Sharding-jdbc?這種 client 層方案的優點在于不用部署，運維成本低，不需要代理層的二次轉發請求，性能很高，但是各個系統都需要耦合?Sharding-jdbc 的依賴，升級比較麻煩

Mycat?這種 proxy 層方案的缺點在于需要部署，自己運維一套中間件，運維成本高，但是好處在于對于各個項目是透明的，如果遇到升級之類的都是自己中間件那里搞就行了

水平拆分：一個表放到多個庫，分擔高并發，加快查詢速度

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

垂直拆分： 一個表拆成多個表，可以將一些冷數據拆分到冗余庫中

不是寫瓶頸優先進行分表

分庫數據間的數據無法再通過數據庫直接查詢了。會產生深分頁的問題
分庫越多，出現問題的可能性越大，維護成本也變得更高。
分庫后無法保障跨庫間事務，只能借助其他中間件實現最終一致性。

分庫首先需考慮滿足業務最核心的場景：

1、訂單數據按用戶分庫，可以提升用戶的全流程體驗

2、超級客戶導致數據傾斜可以使用最細粒度唯一標識進行 hash 拆分

3、按照最細粒度如訂單號拆分以后，數據庫就無法進行單庫排重了

三個問題：

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

借助分庫網關 + 分庫業務雖然能夠實現多維度查詢的能力，但整體上性能不佳且對正常的寫入請求有一定的影響。業界應對多維度實時查詢的最常見方式便是借助?ElasticSearch；
數據傾斜：數據分庫基礎上再進行分表；
分布式事務：跨多庫的修改及多個微服務間的寫操作導致的分布式事務問題？
深分頁問題：按游標查詢，或者叫每次查詢都帶上上一次查詢經過排序后的最大 ID；

如何將老數據進行遷移

雙寫不中斷遷移

線上系統里所有寫庫的地方，增刪改操作，除了對老庫增刪改，都加上對新庫的增刪改；
系統部署以后，還需要跑程序讀老庫數據寫新庫，寫的時候需要判斷 updateTime；
循環執行，直至兩個庫的數據完全一致，最后重新部署分庫分表的代碼就行了；

系統性能的評估及擴容

和家親目前有 1 億用戶：場景 10 萬寫并發，100 萬讀并發，60 億數據量

設計時考慮極限情況，32 庫 * 32 表~ 64 個表，一共 1000 ~ 2000 張表

支持 3 萬的寫并發，配合 MQ 實現每秒 10 萬的寫入速度
讀寫分離 6 萬讀并發，配合分布式緩存每秒 100 讀并發
2000 張表每張 300 萬，可以最多寫入 60 億的數據
32 張用戶表，支撐億級用戶，后續最多也就擴容一次

動態擴容的步驟

推薦是 32 庫 * 32 表，對于我們公司來說，可能幾年都夠了。
配置路由的規則，uid % 32 = 庫，uid / 32 % 32 = 表
擴容的時候，申請增加更多的數據庫服務器，呈倍數擴容
由 DBA 負責將原先數據庫服務器的庫，遷移到新的數據庫服務器上去
修改一下配置，重新發布系統，上線，原先的路由規則變都不用變
直接可以基于 n 倍的數據庫服務器的資源，繼續進行線上系統的提供服務。

如何生成自增的 id 主鍵

使用 redis 可以
并發不高可以單獨起一個服務，生成自增 id
設置數據庫 step 自增步長可以支撐水平伸縮
UUID 適合文件名、編號，但是不適合做主鍵
snowflake 雪花算法，綜合了 41 時間（ms）、10 機器、12 序列號（ms 內自增）

其中機器預留的 10bit 可以根據自己的業務場景配置。

線上故障及優化

更新失敗 | 主從同步延時

以前線上確實處理過因為主從同步延時問題而導致的線上的 bug，屬于小型的生產事故。

是這個么場景。有個同學是這樣寫代碼邏輯的。先插入一條數據，再把它查出來，然后更新這條數據。在生產環境高峰期，寫并發達到了 2000/s，這個時候，主從復制延時大概是在小幾十毫秒。線上會發現，每天總有那么一些數據，我們期望更新一些重要的數據狀態，但在高峰期時候卻沒更新。用戶跟客服反饋，而客服就會反饋給我們。

我們通過 MySQL 命令：

show slave status

查看?Seconds_Behind_Master?，可以看到從庫復制主庫的數據落后了幾 ms。

一般來說，如果主從延遲較為嚴重，有以下解決方案：

分庫，拆分為多個主庫，每個主庫的寫并發就減少了幾倍，主從延遲可以忽略不計。
重寫代碼，寫代碼的同學，要慎重，插入數據時立馬查詢可能查不到。
如果確實是存在必須先插入，立馬要求就查詢到，然后立馬就要反過來執行一些操作，對這個查詢設置直連主庫或者延遲查詢。主從復制延遲一般不會超過 50ms

應用崩潰 | 分庫分表優化

我們有一個線上通行記錄的表，由于數據量過大，進行了分庫分表，當時分庫分表初期經常產生一些問題。典型的就是通行記錄查詢中使用了深分頁，通過一些工具如 MAT、Jstack 追蹤到是由于 sharding-jdbc 內部引用造成的。

通行記錄數據被存放在兩個庫中。如果沒有提供切分鍵，查詢語句就會被分發到所有的數據庫中，比如查詢語句是 limit 10、offset 1000，最終結果只需要返回 10 條記錄，但是數據庫中間件要完成這種計算，則需要 (1000+10)*2=2020 條記錄來完成這個計算過程。如果 offset 的值過大，使用的內存就會暴漲。雖然 sharding-jdbc 使用歸并算法進行了一些優化，但在實際場景中，深分頁仍然引起了內存和性能問題。

這種在中間節點進行歸并聚合的操作，在分布式框架中非常常見。比如在 ElasticSearch 中，就存在相似的數據獲取邏輯，不加限制的深分頁，同樣會造成 ES 的內存問題。

業界解決方案：

方法一：全局視野法

（1）將 order by time offset X limit Y，改寫成 order by time offset 0 limit X+Y

（2）服務層對得到的 N*(X+Y) 條數據進行內存排序，內存排序后再取偏移量 X 后的 Y 條記錄

這種方法隨著翻頁的進行，性能越來越低。

方法二：業務折衷法 - 禁止跳頁查詢

（1）用正常的方法取得第一頁數據，并得到第一頁記錄的 time_max

（2）每次翻頁，將 order by time offset X limit Y，改寫成 order by time where time>$time_max limit Y

以保證每次只返回一頁數據，性能為常量。

方法三：業務折衷法 - 允許模糊數據

（1）將 order by time offset X limit Y，改寫成 order by time offset X/N limit Y/N

方法四：二次查詢法

（2）將 order by time offset X limit Y，改寫成 order by time offset X/N limit Y

（3）找到最小值 time_min

（4）between 二次查詢，order by time between?timeminandtime_i_max

（5）設置虛擬 time_min，找到 time_min 在各個分庫的 offset，從而得到 time_min 在全局的 offset

（6）得到了 time_min 在全局的 offset，自然得到了全局的 offset X limit Y

查詢異常 | SQL 調優

分庫分表前，有一段用用戶名來查詢某個用戶的 SQL 語句：

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

為了達到動態拼接的效果，這句 SQL 語句被一位同事進行了如下修改。他的本意是，當 name 或者 community 傳入為空的時候，動態去掉這些查詢條件。這種寫法，在 MyBaits 的配置文件中，也非常常見。大多數情況下，這種寫法是沒有問題的，因為結果集合是可以控制的。但隨著系統的運行，用戶表的記錄越來越多，當傳入的 name 和 community 全部為空時，悲劇的事情發生了:

select * from user where 1=1

數據庫中的所有記錄，都會被查詢出來，載入到 JVM 的內存中。由于數據庫記錄實在太多，直接把內存給撐爆了。由于這種原因引起的內存溢出，發生的頻率非常高，比如導入 Excel 文件時。

通常的解決方式是強行加入分頁功能，或者對一些必填的參數進行校驗

Controller 層

現在很多項目都采用前后端分離架構，所以 Controller 層的方法，一般使用 @ResponseBody 注解，把查詢的結果，解析成 JSON 數據返回。這在數據集非常大的情況下，會占用很多內存資源。假如結果集在解析成 JSON 之前，占用的內存是 10MB，那么在解析過程中，有可能會使用 20M 或者更多的內存

因此，保持結果集的精簡，是非常有必要的，這也是 DTO（Data Transfer Object）存在的必要。互聯網環境不怕小結果集的高并發請求，卻非常恐懼大結果集的耗時請求，這是其中一方面的原因。

Service 層

Service 層用于處理具體的業務，更加貼合業務的功能需求。一個 Service，可能會被多個 Controller 層所使用，也可能會使用多個 dao 結構的查詢結果進行計算、拼裝。

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

代碼 review 中發現了定時炸彈，這種在數據量達到一定程度后，才會暴露問題。

ORM 層

比如使用 Mybatis 時，有一個批量導入服務，在 MyBatis 執行批量插入的時候，竟然產生了內存溢出，按道理這種插入操作是不會引起額外內存占用的，最后通過源碼追蹤到了問題。

這是因為 MyBatis 循環處理 batch 的時候，操作對象是數組，而我們在接口定義的時候，使用的是 List；當傳入一個非常大的 List 時，它需要調用 List 的 toArray 方法將列表轉換成數組（淺拷貝）；在最后的拼裝階段，又使用了 StringBuilder 來拼接最終的 SQL，所以實際使用的內存要比 List 多很多。

事實證明，不論是插入操作還是查詢動作，只要涉及的數據集非常大，就容易出現問題。由于項目中眾多框架的引入，想要分析這些具體的內存占用，就變得非常困難。所以保持小批量操作和結果集的干凈，是一個非常好的習慣。