最近開始面試了，410面試了一家公司 針對自己薄弱的面試題庫，深入了解下，也應付下面試。在這里先祝愿大家在現有公司好好沉淀，定位好自己的目標，在自己的領域上發光發熱，在自己想要的領域上（技術管理、項目管理、業務管理等）越走越遠！希望各位面試都能穩過，待遇都是杠杠的！

mysql基礎問題可以查看：兩篇博客會進行不定期更新，更新慢了請大家諒解，大家有想了解的面試題相關，可以評論下，我會盡快更新進去。希望在下個月能讀透所有的技術問題，并找到合適的一個公司：面試題之數據庫相關-mysql篇-CSDN博客

1.如何設計一個支撐每秒10萬寫入的高并發MySQL系統

• 分布式架構核心原則

  • 分布式架構原則：
    • 分而治之：講寫入負載分散到多個節點中
    • 異步處理：解耦即使相應與數據持久化
    • 冗余擴展：所有組件無單點故障
  • 典型架構拓撲

  • 客戶端 → 負載均衡 → 寫入API層 → 消息隊列 → 分庫分表集群↓緩存集群↓數據分析層

• 分庫分表詳細方案

  • 分片策略設計
    • 水平分庫分表
    • 分片路由策略
  • 一致性哈希擴容流程：

    • 新增物理節點注冊到元數據中心

    • 數據遷移工具掃描待遷移分片

    • 雙寫新舊分片直至數據同步完成

    • 流量切換至新分片

    • 清理舊分片冗余數據

• 寫入鏈路優化

  • 異步寫入架構

• 1. 客戶端請求 → 2. API服務驗證 → 3. 寫入Kafka → 4. 返回成功響應↓
  5. 消費者批量寫入MySQL → 6. 更新Redis緩存

  • 批量寫入優化
  • 消息隊列配置：kafka生產配置

• mysql極致優化

  • 關鍵的innodb參數
  • 連接與線程配置

• 高可用保障措施

  • 多活數據中心：設置雙主同步且都有備庫，主庫通過專線同步到熱備主庫，熱備主庫同步熱備從庫信息
  • 故障自動轉移機制
• 性能驗證方法
  • 壓測工具
  • 關鍵指標監控

  • 指標	閾值	監控工具
    QPS	單庫<1萬	Prometheus
    主從延遲	<100ms	pt-heartbeat
    線程運行數	<max_connections×80%	Grafana
    磁盤IOPS	<標稱值70%	iostat

• 典型解決方案

  • 熱點數據問題

    • 動態分片：將熱點分片進一步拆分

    • 本地緩存：在應用層緩存熱點分片路由

    • 限流保護：對特定分片實施寫入限流

  • 分布式事務處理

    • 本地寫入消息隊列

    • 異步發送MQ(可靠消息服務保證)

2.MySQL Group Replication vs MGR vs 傳統主從復制？

• 核心架構差異

特性	傳統主從復制	MySQL Group Replication (MGR)
拓撲結構	主從星型拓撲	全對稱P2P架構
數據同步方式	異步/半同步復制	基于Paxos協議的原子廣播
組成員管理	手動配置	自動故障檢測與成員管理
一致性級別	最終一致性	即時一致性(可配置)
故障切換	需手動或借助工具	自動選舉新Primary

• 技術對比
  • 傳統同步
    • 數據流：基于binlog的邏輯日志復制
    • 模式：
      • 異步復制（默認）
      • 半同步復制（after_commit/after_sync）
    • 局限：
      • 腦裂風險
      • 切換時可能丟數據
      • 從庫可能落后
  • MGR架構
    • 核心組件：
      • group communication engine:基于paxos的xcom協議
      • certification layer：沖突檢測
    • 工作流程
      • 事務在本地執行
      • 廣播到組內所有節點
      • 多數節點確認后提交
      • 應用事務到所有節點
    • 關鍵能力對比

能力維度	傳統復制	MGR
自動故障轉移	? 需要VIP/Proxy	? 內置自動選舉
多主寫入	? 單主	? 支持多主(需配置)
數據一致性	最終一致	強一致(多數節點確認)
網絡分區容忍	? 可能丟數據	? 遵循CP原則
節點擴展性	線性擴展讀能力	建議3-9個節點

• 性能指標對比

指標	傳統半同步復制	MGR單主模式	MGR多主模式
寫入TPS	12,000	9,500	7,200
平均延遲(ms)	8	15	22
故障切換時間(s)	5-30(手動)	1-3	1-3
網絡帶寬消耗	1X	2.5X	3X

• 適用場景

  • 傳統復制適合：

    • 讀寫分離的報表系統

    • 異地災備場景

    • 對一致性要求不高的業務

    • 已有成熟中間件管理的環境

  • MGR適合：

    • 需要高可用的核心業務系統

    • 金融級數據一致性要求的場景

    • 云原生/K8s環境部署

    • 希望減少外部依賴的架構

Q:MGR的Paxos協議如何保證數據一致性？

A:MySQL Group Replication (MGR) 通過改進的Paxos協議（具體實現為XCom）來保證分布式環境下的數據一致性

• 一致性保證核心步驟
  • 事務準備階段：

    • 客戶端事務在Primary節點執行

    • 生成包含所有修改的寫集(writeset)

    • 寫集通過認證層進行沖突檢測

  • 提案廣播：

    • Primary節點作為Proposer將寫集廣播給所有節點

    • 節點收到提案后：

      • 檢查自身狀態是否正常

      • 驗證寫集沖突

      • 返回Promise應答

  • 多數派確認（Accept/Accepted）

    • 當收到多數節點(N/2+1)的Promise后

    • Primary發送Accept請求

    • 各節點將事務寫入relay log（內存+磁盤）

  • 全局提交（Learn）

    • 收到多數節點的Accepted響應后

    • Primary提交本地事務

    • 廣播Commit消息通知所有節點

    • 所有節點應用該事務

• 關鍵一致性保護機制
  • 多數派原則(Quorum)

    • 每個事務必須得到多數節點確認

    • 3節點集群至少2個確認

    • 5節點集群至少3個確認

    • 公式：W + R > N?(W寫節點數, R讀節點數, N總節點數)

  • 沖突檢測與解決

    • 基于GTID的認證：檢查沖突

    • 版本向量：每個事務攜帶版本信息

    • 視圖同步

• 異常處理機制

  • 節點故障處理

    • 故障類型	處理方式
      少數節點宕機	繼續服務
      多數節點宕機	停止服務
      網絡分區	多數派分區繼續服務

  • 腦裂預防

    • 世代時鐘(Epoch Number)：每次視圖變更遞增

    • Fencing機制：舊Primary自動降級

  • 恢復流程

    • 故障節點重新加入

    • 從最新節點拉取GTID集合

    • 自動選擇增量同步或全量同步

    • 追平數據后重新加入組

• 與經典的paxos區別

  • 特性	經典Paxos	MGR-XCom
    成員管理	靜態	動態
    消息傳輸	原始UDP	TCP+流控
    領導者角色	臨時選舉	穩定Primary
    數據載體	任意值	事務寫集
    持久化點	多數接受	多數確認

• 性能優化

  • 流水線化處理

  • 批量認證：合并多個事務的寫集檢測，減少網絡往返次數

  • 流控機制

• 一致性級別配置

  • MGR支持兩種模式：

    • 單主模式：SET GLOBAL group_replication_single_primary_mode=ON;

      • 所有寫操作到primary

      • 保證線程一致性

    • 多主模式：SET GLOBAL group_replication_enforce_update_everywhere_checks=ON;

      • 任何節點可寫

      • 保證因果一致性

3.數據誤刪后，如何快速恢復（刪庫刪表后的操作）

• 緊急處理流程：
  • A[發現誤刪] --> B[立即停止相關服務] --> C[評估影響范圍] --> D[選擇恢復方案] --> E[執行恢復] --> F[數據驗證] --> G[恢復服務]
• 基于備份恢復流程
  • 全量數據備份恢復：根據每日備份的數據進行dump恢復
  • 根據時間點恢復：需要binlog有完整的日志
    • 前提需要打開binlog日志，查看腳本：show variales like 'log_bin%';-- 相關內容
    • 根據binlog日志一個個去找：mysqlbinlog -v binlog.000007 | grep -i "drop table AAA"；根據該命令找對應的日志文件。推薦下載到本地，根據binlog中的位置點對數據回滾# at 1020
    • 恢復其他節點的日志到1020
      • 恢復其他binlog日志7之前的所有的文件恢復：
        • mysqlbinlog ./binlog.000001 ./binlog.000002 ./binlog.000003?./binlog.000004?./binlog.000005?./binlog.000006?| mysql -uroot -password
      • 恢復日志文件7的到某個節點：mysqlbinlog --stop-position =1020 ./binlog.000007 |mysql -uroot -password
• 無備份恢復方案

  • 使用binlog2sql工具

  • 使用美團開源的myflash工具

• innodb引擎特殊恢復

  • 使用undrop-for-innodb工具適用場景：無備份且binlog不可用

  • 數據庫恢復服務：MySQL數據恢復專家、DiskInternals MySQL Recovery、Kroll Ontrack

• 不同操作的恢復策略

操作類型	恢復難度	推薦方案
DELETE誤刪	★★☆	binlog2sql閃回
DROP TABLE	★★★	全備恢復+binlog
TRUNCATE	★★★☆	解析表空間文件
DROP DATABASE	★★★★	全備恢復
磁盤損壞	★★★★★	專業恢復工具

• 預防措施
  • 備份策略配置：每天進行數據備份
  • 安全防護配置：設置延遲復制從庫，啟動回收站功能
  • 操作審計措施：使用預生產環境測試更新功能并對DDL操作的語句進行審計后執行
• 關鍵恢復原則

  • 立即停止寫入：防止覆蓋原有數據頁

  • 優先使用邏輯備份：比物理恢復更安全可控

  • 測試恢復流程：定期驗證備份有效性

  • 保留多個副本：采用3-2-1備份策略(3份副本，2種介質，1份離線)

Q:基于binlog的閃回（Flashback）技術實現？

A:MySQL閃回技術是通過逆向解析binlog來恢復誤操作數據的關鍵手段

• 閃回基礎原理
  • binlog記錄機制
    • ROW格式：記錄行級別變更前鏡像（before_image）和變更后鏡像（after_image）
    • 寫入時機：事務提交時一次性寫入整個事務的binlog
  • 閃回核心思想：delete逆向操作原理
• 主流的閃回技術

工具名稱	開發方	語言	特點	適用場景
binlog2sql	大眾點評	Python	純SQL實現	精細恢復
MyFlash	美團	C	二進制級別	高性能恢復
mysqlbinlog_flashback	阿里	C++	集成補丁	云環境

• binlog2sql實現深度解析
  • 核心的處理流程：
    • A[解析binlog] --> B[提取DML事件] --> C[構建行變更對象] --> D[生成逆向SQL] --> E[過濾與排序] --> F[輸出恢復腳本]
  • 關鍵實現代碼：
    • 逆向使用update操作
    • 解析閃回sql，執行恢復

4.主從延遲的根本原因即解決方案

5.InnoDB的自適應哈希索引（AHI）適用場景?

Q:為什么高頻等值查詢能加速？

6.MySQL的CPU利用率飆高，如何定位?

Q:通過performance_schema分析熱點SQL與鎖爭用?

7.如何優化一條SELECT COUNT(*) FROM big_table?

Q:為什么InnoDB不緩存總行數？替代方案?

8.如果讓你設計一個分布式MySQL，你會考慮哪些問題?

Q:數據分片、一致性協議（Raft/Paxos）、分布式事務（XA/TCC）的選擇?

9.MySQL 8.0相比5.7的核心改進?

Q:窗口函數、CTE、原子DDL、直方圖統計?

10.為什么MySQL默認隔離級別是RR（可重復讀）?

• mysql主從復制是通過binlog日志進行數據同步的，而早期的版本中binlog記錄的是sql語句的原文。若此時binlog格式設置為statement時，mysql可能在從庫執行的sql邏輯與主庫不一致。
• 比如在刪除某個區間數據時：delete from user where age >=13 and create_time <= '2025-4-15' limit 1;
• 為什么在sql執行結果不一致：
  • 在主庫執行這條sql的時候，用的是索引age;而在備庫執行這條語句的時候，卻使用了索引create_time.mysql執行優化器會進行采樣預估，在不同的mysql庫里面，采樣計算出來的預估結果不一樣，會影響優化器的判斷，由于優化器會進行成本分析，可能最終選擇索引不一致。這跟sql執行過程有一定關系。
  • 而因為這條delete語句帶了limit，所以查出來的記錄很大可能不會是同一條數據，排序可能不一樣，會導致準備數據不一致的情況。
  • 另外使用RC或者RU的話，是不會添加GAP LOCK間隙鎖，而主從復制過程中出現的事務亂序問題，更容易導致備庫在SQL回滾后與主庫內容不一致。所以mysql選擇了RR隔離級別
  • RR級別在更新數據時會增加記錄鎖和間隙鎖，可以避免事務亂序導致的數據不一致問題。

Q:為啥ORACLE選擇的默認級別是RC？

A：oracle目前支持三種事務隔離級別，RC(讀已提交，默認)，serializable（可串行化），read-only（只讀）；其中Read-only隔離級別類似于序列化隔離級別，但只讀事務甚至不允許在事務中進行數據修改，顯然只能選擇RC

Q:為何大廠要改成RC？

A：出于性能、死鎖和實時性高的需求

• 提高并發性，RC隔離級別下，鎖粒度小，只鎖住一行數據，提高了并發性，尤其讀密集的應用下表現優異。行級鎖，減少了鎖沖突，提升了并發度。
• 減少死鎖，RR級別下會增加GAP Lock和next-key lock，是的所得粒度變大，死鎖的概率也增大。而RC隔離級別下不存在間隙鎖，只需行鎖即可，減少了死鎖的發生概率。
• 滿足實時性：RC每次讀取數據都會獲取最新的行版本，適合實時性要求高的應用，而RR讀取的數據可能不會反應出其他事務對數據的更改，無法滿足對實時性要求高的場景
• 簡化主從同步，RC要求實行行式binlog，有助于減少主從同步時數據不一致問題

11.分庫分表下如何實現精準分頁？

• 全局排序發（推薦）
  • 實現步驟：
  • 統一排序字段，確保所有的分片使用相同的排序規則，如使用時間排序

  • -- 每個分片執行
    SELECT * FROM table ORDER BY sort_field LIMIT (pageNo-1)*pageSize + pageSize

  • 各分片查詢，想所有分片發送相同的分頁查詢請求
  • 內存歸并，將分片返回的結果在內存中排序，然后截取制定頁數據
  • 性能優化：使用流式處理避免內存溢出
  • 優點是結果絕對精確，缺點是隨著頁碼增大性能會下降，出現深分頁問題
• 二次查詢法（優化深分頁）
  • 實現步驟
  • 各分片查詢排序字段值（不返回具體完整行數據）

  • SELECT id FROM table ORDER BY create_time LIMIT 10000, 10
    

  • 獲取最小/最大邊界值
  • 用邊界值精確查詢完整數據

  • SELECT * FROM table 
    WHERE create_time BETWEEN ? AND ?
    ORDER BY create_time LIMIT 10

• 分片鍵連續分頁法
  • 適用場景：
    • 分片鍵本身具有連續性，如時間范圍分片
    • 能預先確定分片鍵的分布情況
  • 實現方式：
    • 根據分片鍵確定數據所在分片
    • 只向特定分片發起查詢
    • 在該分片內做常規分頁
• 適用elasticSeach等搜索引擎
  • 將分庫分表數據同步到ES
  • 利用ES的分布式分頁能力
  • 注意：
    • ES的form+size方式也有深分頁限制
    • 可考慮使用search_after或者scroll api
• 業務層解決方案
  • 禁止跳頁
    • 只提供下一頁功能
    • 每次攜帶最后一條記錄的排序字段值

    • SELECT * FROM table 
      WHERE create_time < ? 
      ORDER BY create_time DESC 
      LIMIT pageSize

• 性能優化建議
  • 避免深分頁：產品設計上限制最大頁碼
  • 使用覆蓋索引：減少回表操作
  • 緩沖熱門頁：對前幾頁結果進行緩沖
  • 預計算：對靜態數據可提前計算分頁結果
  • 分批獲取：客戶端分批加載數據（無線滾動）
• 技術選型

方案	精準度	性能	實現復雜度	適用場景
全局排序	高	中/差	中	數據量中等，要求絕對準確
二次查詢	高	較好	高	深分頁場景
分片鍵連續	高	優	低	分片鍵分布規律明顯
ES搜索	高	優	中	已使用ES的場景
禁止跳頁	中	優	低	用戶瀏覽行為

12.mysql如何同步ES？????????

分庫分表的情況下，如果需要精確的查找數據，需要用elasticsearch的集成，此時如何同步ES，有四種方案，應用同步雙寫、MQ隊列雙寫、基于SQL腳本同步、基于binlog同步組件

• 應用數據庫同步雙寫
  • 特點：應用在同步數據庫的時候也同步給ES，保障了ES的實時性，實現起來相對簡單，不需要引入額外的組件或者復雜的邏輯。但是每次寫入mysql的同時寫入ES可能會對兩個系統的性能產生影響，在高并發的情況下會到值雙寫失敗導致數據不一致等問題，且每次寫入操作都需要雙寫邏輯，增加了業務邏輯的復雜性和維護難度
  • 具體實現：
    • 代碼調整：每次數據庫調整的時候，復制相同邏輯到elasticsearch
    • 事務管理：使用數據庫的事務確保操作的院子性，避免數據不一致
    • 性能優化：盡量批量操作或者使用異步方式來處理，降低對性能的影響
• MQ隊列異步雙寫
  • 特點：利用MQ隊列可以實現異步處理，通過異步方式，可以降低對數據庫寫入性能的影響，利用消息隊列的持久化和重試機制，可以調數據的可靠性。但是由于異步處理會存在數據延遲問題，而且需要引入消息隊列和額外的消費者邏輯，增加系統的復雜性。
  • 具體實現：
    • 消息隊列集成：選擇兵集成一個消息隊列，如kafka或者RabbitMQ
    • 業務邏輯修改：將數據寫入Mysql后，將變更信息發送到消息隊列
    • 消費者開發：開發消費者服務，從消息隊列中讀取消息并異步寫入到elasticsearch。
    • 異步處理：為消息隊列的消費這實現異步處理和重試邏輯
• 基于SQL抽取：
  • 特點：通過定時任務，根據數據庫的時間戳字段來抽取并同步數據到elasticsearch,同步哦該方式無序修改業務邏輯對原系統無感知，通過定時任務邏輯相對簡單易于理解與維護。但是數據庫同步存在延遲無法滿足實時性要求，定時任務可能對數據庫產生額外的查詢壓力。
  • 具體實現：
    • 時間戳字段添加，在對應的數據庫表中添加時間戳，用于記錄數據變更
    • 定時任務配置：按照固定頻率查詢mysql中自上次同步后發生變化的數據。
    • 數據抽取：定時任務將查詢結果抽取出來，同步到elasticsearch。
    • 數據同步：將抽取的數據寫入到elasticsearch完成同步過程。
• 利用binlog進行同步
  • 特點：利用binlog日志，通過消息隊列或者直接消費binlog變化來同步數據庫到ES中。不需要修改現有的業務代碼，對現有系統無感知，可以利用binlog精確捕捉到數據庫的所有變更，確保數據同步的完整性，binlog可以高效地處理數據變更，對源數據庫性能營銷較小，通常配合消息隊列使用，在網絡波動或者服務故障的情況下也能保證數據庫最終一致性。但是需要搭建和維護binlog監聽和消息對壘系統，增加了系統架構的復雜性，雖然基于實時同步，但是遇到消息隊列積壓，可能會出現數據延遲。
  • 具體實現：
    • binlog啟用：確保mysql實例開啟了binlog功能，并且binlog格式（row或者mixed）能夠支持所需的數據庫同步需求
    • binlog監聽配置：部署并配置binlog監聽器（如debezium），監聽制定的mysql實例和數據庫
    • 消息隊列集成：將binlog監聽器和消息隊列（kafka等）集成，確保binlog變更能夠轉換成消息并發送到隊列中
    • 消息消費者開發：開發消息消費者服務，該服務從消息隊列中讀取binlog變更消息，將其轉換成ES可以理解的格式
    • 數據同步：消息消費者將轉換的數據寫入ES完成同步
    • 異常處理：實現異常處理機制，確保數據在同步失敗后能夠進行重試或者日志記錄一遍排障。

13.order by如何工作的呢？

ORDER BY 是SQL中用于排序結果集的關鍵子句，其工作原理在不同數據庫系統中有所不同，但核心機制相似。

• 基本流程

  • 數據獲取階段：

    • 數據庫首先執行WHERE條件過濾，獲取滿足條件的記錄集

    • 如果使用了索引覆蓋掃描，可能直接從索引獲取數據

  • 排序緩沖區(Sort Buffer)操作：

    • 數據庫分配一塊內存區域作為排序緩沖區

    • 將待排序數據加載到緩沖區（可能只加載排序鍵和行指針）

    • 當緩沖區不夠，會使用磁盤輔助排序

  • 排序執行階段：

    • 在內存或磁盤上對數據進行排序

    • 使用快速排序、歸并排序等算法

  • 結果返回階段：

    • 按照排序后的順序訪問數據行

    • 返回給客戶端或下一處理階段

• 不同場景下的排序

  • 能夠使用索引排序（最優情況）

    • -- 當有索引(idx_age_name)時
      SELECT * FROM users ORDER BY age, name;

    • 工作方式：

      • 直接按索引順序讀取數據（索引本身是有序的）

      • 不需要額外排序操作

      • 性能最佳（稱為"Using index"執行計劃）

  • 內存排序（小數據量）

    • -- 數據量小于sort_buffer_size
      SELECT * FROM small_table ORDER BY create_time DESC;

    • 工作方式：

      • 在內存排序緩沖區完成全部排序

      • 使用快速排序法

      • 速度較快（毫秒級完成）

  • 外部排序（大數據量）

    • -- 數據量超過內存限制
      SELECT * FROM large_table ORDER BY revenue;

    • 工作方式：

      • 將數據分成多個塊，每塊在內存中排序后寫入臨時文件

      • 對這些有序臨時文件進行多路歸并

      • 最終得到完全有序的結果集

      • 可能涉及磁盤I/O，性能較差

• 關鍵參數與優化

  • sort_buffer_size

    • 控制排序內存緩沖區大小

    • 過小會導致頻繁磁盤臨時文件

    • 過大可能浪費內存

  • max_length_for_sort_data

    • 決定排序時存儲完整行還是僅排序鍵+行指針

    • 影響內存使用效率

  • tmpdir

    • 外部排序時臨時文件存儲位置

    • 應放在高性能存儲設備上

• 執行計劃分析

  • 通過explain查看排序方式：

    • using filesort:需要額外排序不走

    • using index：可利用索引順序，無需排序

• 性能優化

  • 為order by 創建合適索引

    • -- 為排序創建復合索引
      ALTER TABLE orders ADD INDEX idx_status_created(status, created_at);

  • 避免select ***

    • -- 只查詢需要的列，減少排序數據量
      SELECT id, name FROM users ORDER BY age;

  • 合理設置緩沖區大小

    • -- 會話級調整
      SET SESSION sort_buffer_size = 8*1024*1024;

  • 利用覆蓋索引

    • -- 索引包含所有查詢字段
      SELECT user_id FROM orders?
      WHERE status='paid'?
      ORDER BY create_time;

  • 避免復雜表達是排序

    • -- 不推薦
      SELECT * FROM products ORDER BY ROUND(price*0.9);

      -- 推薦
      SELECT *, ROUND(price*0.9) AS discounted_price?
      FROM products?
      ORDER BY discounted_price;

• 特殊場景處理

  • 多字段混合排序

    • -- 注意索引設計順序
      SELECT * FROM employees?
      ORDER BY department ASC, salary DESC;

  • limit分頁深化

    • -- 深分頁問題解決方案
      SELECT * FROM large_table?
      WHERE id > last_seen_id?
      ORDER BY id?
      LIMIT 100;

  • null值排序控制

    • -- 控制NULL值的排序位置
      SELECT * FROM customers?
      ORDER BY last_purchase_date NULLS LAST;

Q:我們查詢語句怎么匹配到數據的呢？

A：先通過索引樹，找到對應的主鍵，然后在拿到對應的主鍵ID，搜索id主鍵索引樹，找到對應的行數據，這里前提是為排序字段加上索引且不用*，需要什么字段直接查詢什么字段，eg:select name,age,city from user where city ='fuzhou' order by age limit 10，加上order? by后的執行流程就是

• mysql為對應的線程初始化sort_buffer，放入需要查詢的name、age、city字段
• 從索引樹idx_city，找到第一個滿足city='fuzhou'條件的id
• 到主鍵id索引樹中，拿到對應的行數據，取name、age、city三個字段值，存到sort_buffer
• 從索引樹idx_city拿下一個記錄的主鍵id
• 重復上面兩個步驟，知道找不到city=fuzhou的數據為止
• 根據前面找到的所有數據，在sort_buffer中將所有age進行排序
• 按照排序結果前10行返回給調用方
  • 找數據->回表->取數據->排序->取前10條記錄

Q:sort_buffer不夠的時候，磁盤臨時文件如何輔助處理？

sort_buffer大小是通過sort_buffer_size來控制的，如果排序的數據小于sort_buffer_size則正在內存中完成，否則需要借助磁盤臨時文件處理。

可以通過optimizer_trace，開啟統計，在執行sql語句的時候 查詢輸出統計信息：

-- 打開optimizer_trace，開啟統計
set optimizer_trace = "enabled=on";
--執行sql語句
select name,age,city from user where city = 'fuzhou' order by age limit 10;
--查詢輸出統計信息
select * from information_shcema.optimizer_trace;
根據查詢結果中的number_of_tmp_files是否大于0 來查看是否使用了臨時文件,sort_mode默認為additional_feilds

若使用了臨時文件即歸并排序算法，大致流程如下：

• 從主鍵id索引樹拿到需要的數據后，放到sort_buffer內存塊中，當sort_buffer快滿時，對sort_buffer中的數據進行排序，排序后，將數據放到磁盤的一個小文件中。
• 繼續回到主鍵ID索引樹種取數據，繼續放到sort_buffer內存中，排序后，也要把這些數據寫入到磁盤臨時小文件中。
• 繼續循環，知道去除所有滿足條件的數據，最后把磁盤排序好的小文件合并成大文件。

Q:既然sort_buffer中存儲不下數據，為何sort_buffer不只放age字段，節省空間呢？

rowid的排序就是只查詢sql排序的字段和主鍵id，然后在sort_buffer中進行排序。

是否開啟rowid排序主要通過max_length_for_sort_data參數，該參數mysql用于表示排序行數據的長度，超過這個值，mysql就會主動換成rowid排序，默認為1024，根據我們表設計的字段長度來計算。

將max_length_for_sort_data參數改小后，通過optimizer_trace，開啟統計，在執行sql語句的時候 查詢輸出統計信息：

--設置排序字段單行最大長度為64
set max_length_for_sort_data = 64;-- 打開optimizer_trace，開啟統計
set optimizer_trace = "enabled=on";
--執行sql語句
select name,age,city from user where city = 'fuzhou' order by age limit 10;
--查詢輸出統計信息
select * from information_shcema.optimizer_trace;
根據查詢結果中的number_of_tmp_files是否大于0 來查看是否使用了臨時文件。
sort_mode來看是否走了rowid

rowid排序的一個流程：

• mysql為對應的線程初始化sort_buffer，放入需要排序的age字段以及主鍵id
• 從索引樹中拿到對應符合的數據行，取出排序條件的age和主鍵id值存儲到sort_buffer
• 從索引樹中拿到下一個記錄的主鍵id值
• 重復上面兩個步驟知道條件不符合即city!='fuzhou'
• 前面幾步已經找到city='fuzhou'的數據，在sort_buffer中將所有數據根據age進行排序
• 遍歷排序后，取前10行，并按照id的值回表，取出其他需要的字段（city、age、name）返回給調用方

總結：全字段排序與rowid排序對比

• 全字段排序：sort_buffer內存不夠的話，就需要用到磁盤臨時文件，造成磁盤訪問
• rowid排序：sort_buffer可以放更多的數據，但是需要回到原表區數，比全字段多一次回表

一般情況下，對于innodb存儲引擎，會優先使用全字段排序，其中max_length_for_sort_data一般為1024，排序字段一般也不會超過這個長度。

14.存儲引擎的三大特性

mysql存儲引擎中包含三大特性分別為buffer pool、adaptive hash index、double write。

• buffer pool:
  • 原理：buffer pool是innodb存儲引擎用于緩存數據也和索引的內存區域。它提高了數據庫的讀寫性能，因為數據和索引在內存中讀寫比在磁盤讀寫快，當需要訪問數據時，會先從buffer pool查找，在進去磁盤查找
  • 工作方式：buffer pool采用LRU（最近最少使用）算法來管理內存中的頁。當buffer pool滿時，會把最近最少使用的也淘汰，騰出新的空間。
  • 配置參數：
    • innodb_buffer_pool_size：設置buffer pool的大小，通常建議設置為物理內存的70%，以平衡數據庫和其他系統的需求。
    • innobd_buffer_pool_instances：設置buffer pool的實例數量，用于減少所得競爭，提高并發性能。
  • 應用場景適用于頻繁讀取數據的場景，如事務處理、查詢優化等
• Adaptive hash index
  • 原理：adaptive hash index （AHI）是innodb存儲引擎中的一種內存結構，用于加速等值查詢。inodb會自動監控索引的使用情況，如果發現某個索引頻繁被訪問，就會在內存中為該索引創建哈希索引，減少查詢時間。
  • 工作方法：AHI是一個哈希表結構，鍵是索引鍵值，值是該鍵值對應的頁面位置。需二級索引頻繁查詢（固定時間內連續多次等值查詢==號，>=號不行order by無效）成為熱點數據會建立hash index帶來速度的提升。可通過%hash_index%查詢
  • 配置參數
    • innodb_adaptive_hash_index:控制是否啟用AHI，默認啟用。在高并發的場景下，如果CPU資源緊張，可考慮禁用
    • innodb_adaptive_hash_index_partitions:設置AHI的分區數量，增加分區數量可以減少哈希沖突，但也會增加內存消耗。
    • 應用場景：使用與頻繁等值查詢，如主鍵查詢或者唯一索引查詢。在高并發讀取的場景下，AHI可以減少磁盤I/O，條查詢應用速度。
  • 限制：
    • 只能用于等值比較如=，<=> ,IN等
    • 無法用于排序
    • 存在哈希沖突的可能性，可能導致性能下降
    • 維護AHI需要額外的內存和CPU資源
• double write:
  • 原理：double write是innodb存儲引擎用于提高數據完整性和可靠性的機制。它通常在數據頁寫入數據文件之前，將他們寫入成double write buffer的連續存儲區域，確保數據的一致性。如果在寫入過程中發生崩潰，innodb可以從double write buffer中恢復數據
  • 工作方式：數據頁首先被寫入double write buffer 然后通過兩次寫入操作（順序寫）將數據寫入文件的適當位置。這種方式減少I/O開銷，因為數據是連續寫入的。
  • 配置參數：
    • innodb_doublewrite:控制是否啟用，默認啟用
    • innodb_doublewrite_dir:設置文件存儲位置
    • innodb_doublewrite_files:定義文件數量
    • innodb_doublewrite_pages：控制每個線程的最大double write頁數
  • 應用場景：用于需要高可靠性和數據一致性的場景。如生產環境中的數據庫系統

15.自增id用完了怎么辦？

• 表的自增id達到上限后，在申請時它的值就不會該表，進而導致插入時報主鍵沖突，無法繼續插入
• 當表未創建自增Id時rowid自增達到上限后，會歸0再重新遞增，若出現相同的rowid則覆蓋前面的數據
• thread_id自增到上限，重置為0，然后繼續增加，但我們在show processlist不會看到兩個一樣的線程id

16.jion算法說明

jion算法包含了簡單嵌套循環，索引嵌套循環，塊嵌套循環，哈希連接，排序合并連接

• 簡單嵌套循環（simple nested-loop join snlj）

  • 原理：驅動表中的每一條記錄，會依次與被驅動表中的每一條記錄比較，尋找符合條件的匹配記錄。
  • 特點：SNLJ實現簡單，直接，同時也非常低效，通常在小表連接大表的時候使用。
  • 優點：實現機器簡單，可廣泛應用快速驗證
  • 缺點：性能差，對大數據量的查詢效率非常低，尤其是驅動表和被驅動表數據差大的時候。
  • 執行過程：
    • 從驅動表取出一條記錄
    • 遍歷被驅動表中的所有記錄，找到匹配的記錄
    • 將匹配的結果加入結果集

• 索引嵌套循環連接（index nested-loop join ,inlj）

  • 原理：INLJ是嵌套循環連接的優化版本，通過被驅動表上的索引，可以避免對其進行全表掃描，而直接通過索引定位到匹配的記錄
  • 特點：利用了被驅動表索引提升查詢效率，非常適合·小標連接大表·或者·大表間的少量匹配·場景
  • 優點：速度快，大幅減少了被驅動表的掃描此書
  • 依賴被驅動表索引，若索引丟失，性能直接退化為簡單嵌套循環
  • 執行過程
    • 從驅動表中取出一條記錄
    • 使用該記錄的連接鍵，通過索引快速定位被驅動表中的匹配記錄
    • 將匹配結果加入結果集

• 塊嵌套循環連接（block nested-loop join,bnlj）

  • 原理：在BNLJ中，驅動表的一部分數據會首先被加載到內存緩沖區（join buffer）中，然后在于被驅動表進行比較匹配操作
  • 特點：通過批量加載緩沖區減少被驅動表的掃描此書，適合于·大表主鍵無索引連接·
  • 優點：緩沖機制可以顯著降低被驅動表掃描次數
  • 缺點：對內存消耗較大，緩沖取不足會導致效率下降
  • 執行過程：
    • 將驅動表的一部分數據加載到join buffer中。
    • 遍歷被驅動表的所有記錄，與join buffer中的數據匹配
    • 重復上述過程，知道驅動表的數據全部處理完畢

• 哈希連接（hash join）

  • 原理：哈希連接是一種高效的連接算法，適用于·等值連接·，核心思想是
    • 構建階段：以較小的表為基準，基于連接鍵構建哈希表 作為構建表
    • 探測階段：掃描較大的表（探測表），根據哈希值快速定位構建表中的匹配項
  • 特點：哈希連接使用于需要連接的大表之間的數據量大，缺少索引、但連接條件是·等值條件·
  • 優點：在等值連接上效率極高，尤其適合無索引的大數據集
  • 缺點：無法處理范圍條件、不等式條件，哈希表構建階段對內存的依賴較大
  • 執行流程：
    • 哈希連接（hash join）是mysql8.0引入的一種高效的連接算法，適用于大數據集的等值連接
    • 構建階段：將較小的表加載到內存中，并基于連接構建哈希表
    • 探測階段：掃描較大的表，根據連接鍵查找哈希表中的匹配記錄

• 排序合并連接（sort-merge join）

  • 原理：排序合并連接通過·先對表的連接鍵進行排序·，然后使用類似歸并排序的方式合并兩個表：
    • 為表A和表B基于連接鍵進行排序
    • 分別從兩個排序后的表讀取數據，對比連接鍵合并匹配的記錄
    • 對不滿足條件的記錄，通過移動較小的一方指針解決
  • 特點：非常適合需要排序的大表或者需要進行范圍條件連接的時候
  • 優點：適用于范圍條件和等值條件，特別適合超大數據集
  • 缺點：排序階段可能會導致較高的I/O開銷
  • 執行流程：
    • 排序合并連接（sort-merge join）是一種高效的連接算法，特別適用于沒有索引的大數據集和需要范圍連接的場景。
    • 排序階段：對兩個表的連接鍵進行排序
    • 合并階段：使用類似·歸并排序·的方式，依次掃描兩個排序后的表

• 總結：

  • SNLJ：適合小表或者快速驗證
  • INLJ：優選小表到大表且有索引的場景
  • BNLJ：適合無索引的場景
  • HASH JOIN：等值連接的高效利器
  • SORT-MERGE JOIN：格式和范圍查詢和排序場景
• Q:為何大廠都不建議使用多表join
  • 性能問題：
    • 計算復雜性：多表關聯需要進行復雜的計算，如哈希連接、合并連接，這些會增加查詢的計算開銷，影響相應時間
    • 資源消耗：join操作特別是在多表參與時，會占用大量的內存和cpu資源，尤其是當色劑大表或者需要大量數據進行join是
    • 索引依賴：join性能很大程度上依賴于索引的錯在和選擇，當索引不當或者沒有搜因時，查詢性能會大幅度下降
  • 可擴展性：
    • 水平擴展挑戰，在分布式數據庫架構中，join操作可能需要跨多個節點獲取數據，即跨節點join，這往往會導致大量的數據傳輸，增加系統負擔
    • 難以分片，復雜的join查詢在分片數據中難以優化，因為涉及多個分片的數據可能位于不同的物理節點上，難以高效的進行
  • 維護與復雜度
    • 代碼復雜性：復雜的多表join查詢往往導致sql語句難以理解和維護
    • 調試困難：當數據錯誤或者查詢性能不佳時，復雜join查詢更難以調試和定位問題
  • 架構設計：
    • 領域模型割裂：join通常需要深度了解不同表之間的關系，容易導致領域模型設計上不一致
    • 微服務架構限制：在微服務架構中，數據通常是中心化和跨服務的，join查詢往往違反服務數據自治原則
  • 替代策略
    • 反范式化：適當的冗余存儲以減少join需求
    • 數據冗余：在不同表中冗余存儲一些公共字段，避免頻繁的join
    • 分而治之：將復雜的查詢拆分為多個簡單查詢，在應用層進行數據組合
    • 預先計算：通過ETL作業預先計算和存儲結果
    • 使用nosql數據庫：設計生產系統數據時，采用文檔數據庫或者鍵值存儲一類的nosql數據庫來支持復雜數據需求

17.NULL值引發的命案

• 命案1：count/distinct數據丟失
  • count是mysql常用的統計函數，當字段值為NULL時，count(column)會忽略這些記錄導致統計不準確
    • 解決方案：使用count(*)：統計所有的記錄，包括字段值為NULL的行
    • 擴展：避免使用count(常量），因為他的行為與count(*)一致，但可讀性較差
  • 當使用count(distinct column1, column2)時，如果任意一個字段值為null，即使另一列有不同的值，查詢結果也會忽略這些記錄。
    • 解決方案：
      • 避免字段值為null，在建表時設置字段值非空，并指定默認值；
      • 使用代替函數，通過IFNULL(column,default_value)將NULL轉換為默認值。
• 命案2：NULL對比結果為未知
  • 在使用非等于查詢（<>或者!=）時，NULL值的記錄會被忽略
  • 解決方案：顯示處理NULL，在查詢條件中加入對NULL的判斷
• 命案3：NULL值運算都為NULL
  • 在使用NULL值進行運算，比如加減乘除，拼接等，最終結果都NULL
• 命案4：聚合空指針異常
  • 在使用聚合函數（如SUM、AVG）時，如果字段值為NULL，查詢結果也會為NULL，而不是預期為0，這可能是導致程序在處理結果時出現空指針異常等。
  • 解決方案：使用IFNULL函數，將NULL轉換成0
• 命案5：GROUP BY/ORDER BY會統計NULL值
  • 在使用group by與order by時，不會剔除NULL，會將NULL作為最小值
  • 解決方案：使用is not null ，剔除NULL記錄
• 命案6：NULL值導致索引失效？
  • 結論：NULL值不一定會導致索引失效，但會影響索引效率
  • 字段定義為NOT NULL時
    • 查詢IS NULL被任務是impossible where 不會走索引
    • 查詢is not null 通常不走索引，而是全表掃描
    • 如果查詢只涉及索引字段（覆蓋索引場景），is not null查詢可以走索引
  • 字段允許null時
    • 查詢條件 is null5.7之后可以走索引，is not null通常不使用索引除非覆蓋索引。
    • null值分布占比會顯著影響查詢性能和索引使用
  • NULL值分布占比的影響：
    • 當NULL值占比較小時，IS NULL查詢更傾向于使用索引
    • 當非NULL值占比比較小時，is not null 查詢更傾向于使用索引
  • 優化建議：
    • 合理的設計字段屬性，對不會存儲NULL值的字段建議定義為NOT NULL，減少NULL值處理開銷，若字段需要存儲NULL值，確保查詢條件能夠充分利用索引
    • 定期更新統計信息：使用analyze table 更新表的統計信息，確保優化器成本估算更準確
  • 小結：
    • 字段中的NULL值贊比對索引使用有重要影響，
      • 當NULL值占比小，IS NULL查詢走索引，IS NOT NULL查詢可能全表掃描；
      • NULL值占比大時，IS NOT NULL查詢走索引，IS NULL查詢可能全表掃描
    • 優化器的成本計算可能出現誤判
      • 統計信息不準確時，優化器可能錯誤的選擇使用索引。定期更新統計信息避免誤判
    • 回表操作可能導致性能下降
      • 二級索引掃描行數過多時，回表成本可能高于全表掃描
• 總結：
  • 表設計時，盡量避免NULL值，設置字段的默認值為空字符串''或者0
  • 查詢時顯示處理NULL，使用IF NULL或者COALESCE函數將NULL轉換成默認值；在查詢條件中加入IS NULL或者IS NOT NULL
  • 優先使用ISNULL(COLUMN)判斷NULL值，提升代碼可讀性和執行效率

18.mysql備份的方式

• 備份數據三種方式：熱備份、溫備份、冷備份
  • 熱備份：當數據庫進行備份時，數據庫的讀寫操作不受影響
  • 溫備份：當數據庫備份時，數據庫的讀操作可以執行，但是不能執行寫操作
  • 冷備份：當數據庫備份時，數據庫不能進行讀寫操作，需要下線處理
  • innodb支持三種備份方式，而myisam不支持熱備份
• 數據庫備份兩種方式：物理備份、邏輯備份
  • 物理備份：通過tar、cp等命令直接打包復制數據庫的書庫文件，達到備份效果
  • 邏輯備份：通過特定工具從數據庫導出數據并另存備份（邏輯備份丟失數據的精度）
• 備份策略
  • 一般備份需要備份：數據、二進制日志 innodb事務日志、代碼（存儲過程、存儲函數、觸發器、事件調度器）、服務器配置文件
  • 直接cp,tar復制數據庫文件：數據量小，直接復制數據庫文件
  • mysqldump:數據量一般，先使用mysqldump對數據庫進行完全備份
  • xtrabackup:數據量很大，而又不過分影響運行，使用xtarback進行完全備份后，定期使用xtrabackup進行增量備份或者差異備份
• 備份工具：
  • mysqldump：邏輯備份工具，適合所有的存儲引擎，支持溫備、完全備份、部分備份，對于innodb存儲引擎支持熱備cp，tar等歸檔復制工具
  • 物理備份工具，使用與所有的存儲引擎，冷備、完全備份和部分備份
  • lvm2 snapshot：幾乎熱備，借助文件系統管理工具進行備份
  • mysqlhot copy：名不副實的一個工具，幾乎冷備，僅支持myisam存儲引擎，
  • xtrabackup:一款非常強大的innodb/etardb熱備工具，支持完全備份、增量備份

19.數據庫在線遷移方案

20.對大表的查詢，為何不會把內存撐爆？

• 假設我們要對一個300G的innodb表A執行一個全表掃描。當未設置查詢條件，select * from A；此時innodb的鄭航數據保存在主鍵索引上的，所以全表掃描實際是直接掃描表A的主鍵索引，由于沒有設置條件，所以查到的每一行數據都可以直接放到結果集返回給調用方。
• 執行邏輯
  • 獲取一行，寫到net_buffer。這塊內存的大小由參數net_buffer_length定義，默認為16K
  • 重復后去行，知道net_buffer寫滿，調用網絡接口發出去
  • 如果發送成功，就清空net_buffer，然后繼續取下一行數據，并寫入到net_buffer。
  • 如果發送函數返回eagain或者wsaewouldblock，就標識本地網絡棧（socket send buffer）寫滿了，進入等待，知道網路棧重新可寫，在繼續發送。
• 執行流程
  • 
  • 一個查詢在發送過程中，占用的mysql內部內存最大就是net_buffer_length這么大，并不會達到300G
  • socket send buffer 也可能達到300G（默認定義/proc/sys/net/core/wmem_default），如果socket send buffer 被寫滿，就會暫停讀數據的流程
  • 也就是說mysql是邊讀邊發的，這就說明若客戶端接收比較慢，會導致mysql服務端由于結果發不出去，這個事務的執行時間邊長，但是不會導致數據庫內存打爆。

21.加密后的數據如何支持模糊查詢

• 內存解密：數據量小時，簡單方便，但數據量大時可能會導致OOM
• 映射查詢：建立兩個表的映射關系，加密表通過非加密表進行數據查詢
• 加密函數：通過AES DES等方式對字段解密后查詢
• 分片查詢：根據加密算法的策略對字段值進行分片拆分后查詢

22.mysql8的索引跳躍掃描？

• mysql中的索引跳躍掃描（skip scan）是一種優化策略，為了提高特定條件下對符合索引的利用效率。
  • 以staff表為例，表中有id、dept、job三個字段，并對dept和job進行復合索引

  • id	dept	job
    1	軟件	it
    2	軟件	it
    3	管理	am
    4	管理	bm

  • 跳躍掃描的優化實現
    • 分區掃描
      • 將索引按照dept的唯一值進行分割，每個分區相當于不同的部門。
    • 應用條件
      • 對每個dept分區中的position行進行掃描，即跳躍掃描僅搜索job='it'的員工
    • 跳過無關分區
      • 由于job='it'的限定，我們只在每個dept的分區中尋找匹配的job而不是對整個表進行掃描
  • 實際效果
    • 雖然mysql索引跳躍掃描概率上并不是獨立標識的特性，但此類索引優化策略在新版的mysql中能改進索引條件推送和查詢優化器來實現。他潛在的減少了讀取和濾除不必要的數據開銷。

????????