天天看點
傳回

面試1_mysql1. 基礎知識2. 存儲引擎3. 索引4. 事務5. 鎖6. MySQL高并發

面試_mysql

  • 1. 基礎知識
    • 1) 資料庫三大範式是什麼
    • 2) mysql有關權限的表
    • 3) binlog錄入格式
    • 4) char、varchar 差別
    • 5)int(20)中20的涵義
    • 6) 字段為什麼要求定義為not null？
    • 7) 雪花算法原理？為什麼是順序ID? 如何解決時鐘回撥？
    • 8) 自增ID與UUID的差別
    • 9) 為何用自增主鍵(自增ID的好處)？
    • 10) 什麼是臨時表，臨時表什麼時候删除?
    • 11) UNION、UNION ALL差別
    • 12) SQL語句分類
    • 14) in、exists 差別
  • 2. 存儲引擎
    • 1) InnoDB、MyIsam、Memmry的差別
    • 2) MyISAM索引、InnoDB索引的差別
    • 3) 存儲引擎選擇
    • 4) [InnoDB引擎的4大特性](https://zhuanlan.zhihu.com/p/109528131)
    • 5) InnoDB如何存資料的
    • 6) InnoDB索引類型，差別？
  • 3. 索引
    • 1) 什麼是索引？
    • 2) 索引使用場景（重點）
    • 3) 索引代價
    • 4) 索引分類
    • 5) 少建索引的原則
    • 6)建立索引的原則（重中之重）
    • 7) 索引建立、删除
    • 8) 聚簇、非聚簇差別
    • 9) 适合使用索引覆寫來優化SQL的場景
    • 10) B+樹索引、 Hash索引差別
    • 11) B+樹索引、B樹索引差別？為什麼使用B+樹而不是B樹？
    • 12) 如何利用索引提升查詢性能？
    • 13) 索引維護
  • 4. 事務
    • 1) 什麼是資料庫事務？
    • 2) 事物的四大特性(ACID)
    • 3) [ACID實作原理](https://www.cnblogs.com/kismetv/p/10331633.html)
    • 4) 事務的隔離級别
    • 5) [四個隔離級别的實作原理](https://blog.csdn.net/QEcode/article/details/97274409)
  • 5. 鎖
    • 1) InnoDB鎖的算法
    • 2) 鎖類别
    • 3) 死鎖判定原理和具體場景，死鎖怎麼解決？
    • 4) InnoDB行鎖的實作
  • 6. MySQL高并發
    • 1) 高并發解決方案
    • 2) 分表後的ID怎麼保證唯一性？
    • 3) mysql主從同步怎麼做？

1. 基礎知識

1) 資料庫三大範式是什麼

範式 規則
1NF 列不可以拆分。
2NF 1NF + 非主鍵列完全依賴于主鍵（非一部分）
3NF 2NF + 非主鍵列隻依賴于主鍵

2) mysql有關權限的表

表名 内容
user 記錄允許連接配接到伺服器的使用者帳号資訊，裡面的權限是全局級的
db 記錄各個帳号在各個資料庫上的操作權限
table_priv 記錄資料表級的操作權限
columns_priv 記錄資料列級的操作權限
host

配合db權限表對給定主機上資料庫級操作權限作更細緻的控制。

這個權限表不受GRANT和REVOKE語句的影響

3) binlog錄入格式

格式 内容
statement 每一條會修改資料的sql都會記錄在binlog中
row 不記錄 sql 語句上下文相關資訊，僅儲存哪條記錄被修改
mixed 普通操作使用statement記錄，當無法使用statement的時候使用row。

4) char、varchar 差別

char varchar
定長 長可變
存取速度快，便于存儲查找 慢
最多存 255，非unicode 最多存 65532，非unicode
英文1位元組，中文2位元組 均為2位元組
長度 < 定長，空格填充 按插入資料的長度來存儲

5)int(20)中20的涵義

  1. 顯示字元的長度。
  2. 20表示最大顯示寬度為20，但仍占4位元組存儲，存儲範圍不變；
  3. 不影響内部存儲，隻是影響帶

    zerofill

     定義的 int 時，前面補多少個 0，易于報表展示

6) 字段為什麼要求定義為not null？

null

值會占用更多的位元組。

7) 雪花算法原理？為什麼是順序ID? 如何解決時鐘回撥？

  1. 1bit無用符号位 + 41bit時間戳 + 10bit機器ID + 12bit序列号
  2. 時間戳在高位，整個ID都是趨勢遞增的。
  3. 時鐘回撥問題的解決方案讨論
    • 時間戳自增（徹底解決）
    • 緩存曆史序列号（緩解）
    • 等待時鐘校正

8) 自增ID與UUID的差別

  1. UUID隻是

    全球唯一Id

     ，在高并發情況下不會出現ID沖突
  2. 自增ID
    1. 字段長度較uuid小很多，

      占空間小
    2. 增量增長，按順序存放，對于檢索非常有利，作為聚簇索引

      提升查詢效率

       。

9) 為何用自增主鍵(自增ID的好處)？

  1. InnoDB使用主鍵索引，用自增主鍵，每次插入記錄會

    順序添加

     到目前索引節點的後續位置
  2. 不是自增主鍵，可能會在中間插入引起B+樹的

    節點分裂

10) 什麼是臨時表，臨時表什麼時候删除?

  1. 存儲一些中間結果集的表
  2. 臨時表隻在目前連接配接可見
  3. 當關閉連接配接時，Mysql會自動删除表并釋放所有空間。
    1. 記憶體臨時表(

      memory

       )
    2. 磁盤臨時表(5.7.6後

      innodb

       ，之前

      myisam

       )

11) UNION、UNION ALL差別

  1. UNION ALL

     不會合并重複的記錄行

  2. UNION

     效率 高于

    UNION ALL

12) SQL語句分類

  1. 資料定義語言DDL：

    CREATE，DROP，ALTER
  2. 資料查詢語言DQL：

    SELECT
  3. 資料操縱語言DML：

    INSERT，UPDATE，DELETE
  4. 資料控制功能DCL：

    GRANT，REVOKE，COMMIT，ROLLBACK

14) in、exists 差別

  1. in：外表和内表作

    hash

     連接配接
  2. exists ：對外表作

    loop

     循環，每次

    loop

     循環再對内表進行查詢。

2. 存儲引擎

1) InnoDB、MyIsam、Memmry的差別

種類 InnoDB MyISAM Memmry
鎖機制 行鎖、表鎖 表鎖 表鎖
B+樹索引 支援（

索引組織表

 ） 		支援 （

堆表

 ） 		支援
哈希索引 X X 支援
全文索引 5.6後支援 支援 X
外鍵 支援 X X
事務 支援 X X
記錄存儲順序 按插入順序儲存 主鍵大小有序插入

2) MyISAM索引、InnoDB索引的差別

InnoDB索引 MyISAM索引

聚簇

非聚簇

主鍵

 索引的葉子節點存儲着

行資料

非主鍵

 索引的葉子節點存儲的是

主鍵、帶索引的列資料

 		葉子節點存儲的是

行資料位址

 需要

再尋址

 一次才能得到資料

3) 存儲引擎選擇

  1. 預設的

    Innodb
    1. 更新（删除）頻率高
    2. 資料的完整性
    3. 并發量高，支援事務和外鍵
    4. 崩潰後更容易恢複

  2. MyISAM
    1. 讀寫插入為主的應用
    2. 全文搜尋
    3. 高速存儲、檢索

4) InnoDB引擎的4大特性

  1. 插入緩沖（insert buffer)
  2. 二次寫(double write)
  3. 自适應哈希索引(ahi)
  4. 預讀(read ahead)

5) InnoDB如何存資料的

  1. 5.6 之前 系統表空間 , 對應

    ibdata1

     檔案
  2. 5.6 之後 獨立表空間
    1. 8.0 之前

      1. ibd

         檔案裡儲存的僅僅是該表的資料

      2. frm

         檔案裡儲存表結構
    2. 8.0 之後, 表結構資訊以

      SDI

       的形式放在了

      ibd

       檔案中

6) InnoDB索引類型，差別？

主鍵索引 非主鍵索引
聚簇 輔助
ID主鍵即可查詢出資料行 可能需要回表

3. 索引

1) 什麼是索引？

  1. 一種特殊的

    檔案
  2. 一種用于快速查找資料的

    資料結構

2) 索引使用場景（重點）

  1. where
  2. order by
  3. join

3) 索引代價

  1. 占用磁盤空間
  2. 建立或修改等操作時，比沒有索引或沒有建立覆寫索引時的要慢。
  3. 索引是有大量資料的時候才建立的，沒有大量資料反而會浪費時間。

4) 索引分類

分類标準 分類
資料結構 B+Tree 、 Hash索引 、 全文索引
實體存儲 聚簇索引 、 非聚簇索引(二級、輔助)
字段特性

普通索引

唯一索引 （值唯一，允許有空值）、主鍵索引 （不允許有空值）

字首索引（選擇索引列的最左n個字元來建立索引）
字段個數 單列索引、 聯合索引

5) 少建索引的原則

  1. 表記錄太少
  2. 經常插入、删除、修改的表
  3. 資料重複且分布平均的表字段。（字段A隻有T和F兩種值，且每個值的分布機率大約為50%）
  4. 經常和主字段一塊查詢但主字段索引值比較多的表字段

6)建立索引的原則（重中之重）

  1. 最左字首比對原則
  2. 查詢頻繁的字段
  3. 有外鍵的資料列
  4. 字段唯一
  5. 查詢中排序的字段
  6. 查詢中統計或分組統計的字段

7) 索引建立、删除

  1. CREATE TABLE

     時建立

  2. ALTER TABLE

     添加

  3. CREATE INDEX

     指令建立
  4. 删除普通索引、唯一索引、全文索引：

    alter table 表名 drop KEY 索引名
  5. 删除主鍵索引：

    alter table 表名 drop primary key

8) 聚簇、非聚簇差別

聚簇索引 非聚簇索引
順序存儲 無序存儲
葉子節點

資料行

 		葉子節點

索引 + 主鍵值

（InnoDB）

指向存放資料塊的

指針

 （MyISAM）

隻能有一個

 		多個

稀疏索引

 ，資料頁上一級索引存儲是

頁指針

密集索引

 ，資料頁上一級索引頁為

每一個資料行存儲一條記錄

9) 适合使用索引覆寫來優化SQL的場景

  1. 全表count查詢優化
  2. 列查詢回表優化
  3. 分頁查詢

10) B+樹索引、 Hash索引差別

Hash索引相比B+樹索引的優缺點

  1. 不支援

    範圍查詢
  2. 不支援

    模糊查詢
  3. 不支援

    聯合索引的最左比對規則
  4. 不支援

    利用索引完成排序
  5. 存在

    哈希碰撞

     問題
  6. 避免不了

    回表

     查詢資料

  7. 等值查詢

     效果更好，但是不穩定

11) B+樹索引、B樹索引差別？為什麼使用B+樹而不是B樹？

特點 BTree B+Tree
結點 索引 + 資料

資料僅在葉子結點

 ，且葉子結點間存在

雙向連結清單
随機檢索 支援 支援

順序檢索

 		X 支援

範圍查詢

 		X 支援
查詢效率 更穩定
元素周遊 效率低下
增删檔案（節點）的效率 更高
空間使用率更高，

磁盤讀寫代價更低

 		X 支援

12) 如何利用索引提升查詢性能？

  1. 覆寫索引

     ：減少回表次數
  2. 聯合索引的

    最左字首原則

  3. 索引下推

     ：節省了一次回表次數
    用

    聯合索引

    時

    先通過前一個字段的索引找到合适位置之後

    sql引擎會

    自動判斷後一個字段

    的值是否符合條件，

    如果符合條件的話，

    取出主鍵ID進行回表查詢

13) 索引維護

  1. 直接優化表 順帶會優化索引
  2. 删除索引，從新建立
  3. 修改索引 
    ALTER INDEX 索引名 REBUILD ; 
ALTER INDEX 索引名 REBUILD ONLINE; 
ALTER INDEX 索引名 REBUILD ONLINE NOLOGGING;
  4. 合并索引：不需額外存儲空間，代價較低

4. 事務

1) 什麼是資料庫事務？

  1. 事務是一個

    不可分割的資料庫操作序列

     。
  2. 事務是資料庫并發控制的基本機關，其執行的結果必須使資料庫從

    一種一緻性狀态

     變到

    另一種一緻性狀态

     。
  3. 事務是邏輯上的一組操作，

    要麼都執行，要麼都不執行

     。

2) 事物的四大特性(ACID)

  1. 原子性

     ： 事務是最小的執行機關，不允許分割。要麼都執行，要麼都不執行。

  2. 一緻性

     ： 執行事務前後，資料保持一緻，多個事務對同一個資料讀取的結果是相同的；

  3. 隔離性

     ： 并發通路資料庫時，一個使用者的事務不被其他事務所幹擾，各并發事務之間資料庫是獨立的；

  4. 持久性

     ： 一個事務被送出之後。它對資料庫中資料的改變是持久的，即使資料庫發生故障也不應該對其有任何影響

3) ACID實作原理

  1. 原子性：

    undo log

     ，事務修改資料庫時記錄，失敗復原
  2. 一緻性：

    其餘三大特性 + 應用層代碼控制
  3. 隔離性：

    鎖 + MVCC（隐藏列、基于undo log的版本鍊、ReadView）
  4. 持久性：

    redo log

     ，資料修改時記錄，事務送出時刷盤，當機則可讀取redo log中的資料來恢複

4) 事務的隔離級别

事務隔離級别 讀未送出 讀已送出 可重複讀 串行化 備注
髒讀 √ X X X

讀取未送出資料
不可重複讀 √ √ X X

前後多次讀取，資料内容不一緻
幻讀 √ √ √ X

前後多次讀取，資料總量不一緻

5) 四個隔離級别的實作原理

操作 讀未送出 讀已送出 可重複讀 串行化
讀 不加鎖 不加鎖, ReadView 不加鎖, ReadView 加鎖
寫 行鎖（排它鎖） 行鎖, undo log的版本鍊 行鎖, undo log的版本鍊 加鎖

RC 和 RR 不同之處：行記錄對于目前事務的

可見性
  1. RC級别對資料的可見性是該資料的

    最新記錄

     ，
  2. RR基本對資料的可見性是

    事務開始時，該資料的記錄

     。

5. 鎖

1) InnoDB鎖的算法

  1. Record lock：

    行鎖
  2. Gap lock：

    間隙鎖

     ，鎖定一個範圍，不包括記錄本身

  3. Next-key lock

     ：record + gap 鎖定一個範圍，包含記錄本身

2) 鎖類别

  1. 樂觀鎖：

    版本号控制

     、

    CAS算法原理
  2. 悲觀鎖：

    鎖

    1. 共享鎖

       （讀鎖）：

      in share mode

    2. 排他鎖

       （寫鎖）：

      for update

3) 死鎖判定原理和具體場景，死鎖怎麼解決？

  1. 死鎖判定原理
    1. 互斥條件：一個資源每次隻能被一個程序使用。
    2. 請求與保持條件：一個程序因請求資源而阻塞時，對已獲得的資源保持不放。
    3. 不剝奪條件:程序已獲得的資源，在末使用完之前，不能強行剝奪。
    4. 循環等待條件:若幹程序之間形成一種頭尾相接的循環等待資源關系。
  2. 有助于最大限度地降低死鎖
    1. 順序通路。
    2. 一次請求所有資源
    3. 主動釋放鎖
    4. mysql中避免死鎖：避免事務中的使用者互動。保持事務簡短并在一個批進行中。使用低隔離級别。使用綁定連接配接。
  3. MySQL解決死鎖方法
    1. 第一種: 查詢是否鎖表 -->查詢程序 --> 殺死程序id

      kill id
    2. 第二種: 檢視目前的事務 --> 檢視目前鎖定的事務 --> 檢視目前等鎖的事務 --> 殺死程序id

4) InnoDB行鎖的實作

基于索引，

for update

6. MySQL高并發

1) 高并發解決方案

  1. 水準分庫分表

     ，由單點分布到多點資料庫中，進而降低單點資料庫壓力。
  2. ** 叢集**方案：解決DB當機帶來的單點DB不能通路問題。
  3. 引入

    負載均衡

     政策（LoadBalancePolicy簡稱LB）

  4. 主從複制

     實作讀寫

    分離政策

     ：極大限度提高了應用中Read資料的速度和并發量。無法解決高寫入壓力
    1. 通過

      mybatis plugin

       攔截sql語句，僅

      select

       通路salve庫

    2. plugin

       通過

      注解

       或者

      分析語句是讀寫方法

       來標明

      主從庫
    3. 重寫一下

      DataSourceTransactionManager

       來支援事務， 将

      read-only

       的事務扔進讀庫， 其餘扔進寫庫。
  5. 使用

    redis做一個緩沖操作

     ，讓請求先通路到redis

    1. 延時雙删

       政策

    2. 異步更新緩存

       (基于訂閱

      binlog

       的同步機制)

2) 分表後的ID怎麼保證唯一性？

  1. 設定步長

  2. 分布式ID

     ，如雪花算法

  3. 每張表單獨新增一個字段作為唯一主鍵

3) mysql主從同步怎麼做？

  1. 主從同步

     的原理(異步)
    1. master送出完事務後，寫入binlog
    2. slave連接配接到master，擷取binlog
    3. master建立dump線程，推送binlog到slave
    4. slave啟動一個IO線程讀取同步過來的master的binlog，記錄到relay log中繼日志中
    5. slave再開啟一個sql線程讀取relay log事件并在slave執行，完成同步
    6. slave記錄自己的binglog

  2. 主庫挂了

     怎麼處理

    • 全同步複制

       ：主庫寫入binlog後

      強制同步

       日志到從庫，

      所有從庫

       都執行完成後才傳回給用戶端

    • 半同步複制

       : 解決資料丢失的問題。從庫寫入日志成功後傳回

      ACK

       确認給主庫，主庫收到

      至少一個從庫

       的确認就認為寫操作完成。

    • 并行複制

       : 解決從庫複制延遲的問題
面試 mysql
上一篇: 面試mysql準備
下一篇: Python 資料科學：使用 Python 進行資料分析和可視化

繼續閱讀