【應屆應知應會】SQL常用知識點50道

SueWakeup

個人主頁：SueWakeup

系列專欄：借他一雙眼，愿這盛世如先生所愿

個性簽名：人生乏味啊，我欲令之光怪陸離?

本文封面由 凌七七~? 友情提供

?目錄

數據庫的概念 (什么是數據庫)?

RDBMS

NOSQL

?數據庫的分類

關系型數據庫(RDBMS)與(NOSQL)非關系型數據庫的比較?

主流的NOSQL產品?

基礎篇?

1. SQL數據庫結構

2. SQL語句分類?

3. SQL語句的執行流程?

4. 存儲引擎 MyISAM 和 InnoDB的區別?

5. MySQL設計三大范式

6. 什么是 SQL 注入

7. 內連接和外連接的區別 AND 左連接和右連接的區別

8. Union 和 Union all 的區別

9. MySQL 如何取差集

10. Delete 和 Truncate的區別

11. Count(*) 、Count(1)、Count(column) 的區別

12. 模糊查詢使用什么關鍵詞？ %和 _有什么區別？

13. 慢查詢的概念

14. varchar和char的區別

視圖篇

15. 視圖的理解

16. 視圖的缺點

?索引篇

17.?什么是索引

18. 索引的特性

19. 索引的分類

20. 索引的優點及缺點

優點

缺點

21. 如何添加/ 刪除索引(index)

?主鍵索引 (primary key)

唯一索引 (unique)

普通索引 (index)

全文索引 (fulltext)

聯合索引 (復合/多列索引)

22. 主鍵索引和唯一索引的區別

23. 什么是聯合索引？什么情況下會觸發？

24.?如何添加/ 刪除約束(constraint)

主鍵約束 (primary key)

唯一約束 (unique)

外鍵約束 (foreign key)

非空約束 (not null)

檢查約束 (check)

25. 索引和約束的關系及區別

26. B+Tree 索引（MySQL 5.5 之后默認）

26.1 B+樹執行查找操作

27. B樹和B+樹的區別？

28. MySql為什么使用B+樹，不用紅黑樹？

29. 哈希索引

30. 什么是回表？

31. 索引覆蓋

好處

措施

32. 索引的使用場景

33. 索引的失效場景

34. 索引的優化

35. SQL優化

?事務篇

36. 什么是事務

37. MySQL 事務的四大特性

38. NOSQL 的 BASE 理論

39. MySQL 實現事務的步驟

40. 數據庫事務的實現原理

41. 數據庫并發事務會帶來哪些問題？

臟讀

不可重復讀

丟失修改

幻讀

42. 不可重復度和幻讀的區別？

43. 事務的隔離級別

讀未提交

讀已提交

可重復讀

串行化

鎖篇

44. MySQL中的鎖

45. 悲觀鎖和樂觀鎖的區別

悲觀鎖

樂觀鎖

46. 什么是MVCC？

存儲過程篇?

47. 存儲過程

48. 為什么使用存儲過程

49. 存儲過程和函數的區別

50. 存儲過程的缺陷

手機端瀏覽本文章可能會出現 “目錄”無法有效展示的情況，還請諒解！?

數據庫的概念 (什么是數據庫)?

????????長期存放在計算機內，有組織、可共享的大量數據的集合，是一個數據“倉庫”。

  • 數據庫分為：關系型數據庫和非關系型數據庫
  • MySQL的特點：操作便捷、小巧且功能齊全、免費開源的數據庫，可運行于 windows 或 linux 系統

RDBMS

  • 全稱 “Relational Database Management System”，意為 “關系型數據庫管理系統”。

????????使用 SQL（結構化查詢語言）進行數據的查詢和管理，將數據組織為表格，每個表格包含多行和多列記錄與字段。

NOSQL

  • 全稱 “Not Only SQL”,意為 “不局限于 SQL”。

????????通常對數據進行非規范化處理，用于大規模數據應用，特別是分布式數據存儲和大數據應用。

?數據庫的分類

數據庫分類數據模型舉例
關系型（RDBMS）表格Oracle、MySQL、SQL Server等
非關系型（NoSQL）鍵值對（K-V）、文檔（Document）、列族（Column Family）、圖形MongoDB、Redis、Elasticsearch(引擎)、Cassandra等
對象型

以對象作為基本單位

支持面向對象的數據建模和操作

db4o、Versant等
XMLXML文檔eXist、BaseX等
圖形

圖形結構數據

適用于網絡關系、社交網絡等場景

Neo4j、FlockDB等
內存存儲在內存中SAP HANA、MemSQL等
時間序列存儲時間序列InfluxDB、Prometheus等

關系型數據庫(RDBMS)與(NOSQL)非關系型數據庫的比較?

NoSQL關系型數據庫
優點
  1. 簡單易部署，低成本
  2. 查詢速度快（數據存儲于緩存中）
  3. 存儲數據多樣
  4. 擴展性好
  1. 技術又成熟又早，提供豐富的技術支持和生態系統
  2. 標準化查詢語句，對數據的操作和管理更方便靈活
  3. 支持事務管理，確保數據的一致性、完整性和持久性
  4. 存儲基礎數據，對數據的管理直觀簡單
缺點
  1. 相較于關系型數據庫發展時間短，維護工具和資料有限
  2. 不遵循 SQL 標準，提升了學習和使用成本
  3. 不提供關系型數據庫對事務的處理
  1. 相較于 NoSQL 成本高
  2. 存儲在磁盤中，速度不及 NoSQL
  3. 僅能存儲基礎數據
  4. 類似 join 的多表查詢機制限制了擴展性

主流的NOSQL產品?

鍵值對（K-V）存儲列族存儲文檔型存儲圖形數據庫
相關產品Redis、Voldemort、Berkeley DBCassandra，HBase,RiakMongoDB、Elasticsearch、CouchDBNeo4j、InfoGrid、Infinite Graph
典型應用內容緩存，主要用于處理大量數據的高訪問負載分布式的文件系統Web應用（與 K-V 類似，V是結構化的）社交網絡
數據模型一系列鍵值對以列族式存儲，將同一列數據存在一起一系列鍵值對圖結構
優勢快速查詢查找速度快，可擴展性強，更容易進行分布式擴展數據結構要求不嚴格利用圖結構相關算法
劣勢存儲的數據缺少結構化功能相對局限缺乏統一的查詢語法需要對整個圖做計算才能得出結構，不容易做分布式的集群方案

基礎篇?

1. SQL數據庫結構

  • 數據庫（Database）：以文件的形式存放在磁盤上，即對應于一個或多個物理文件
  • 數據表（Table）：由一組數據記錄組成，數據庫中的數據以表為單位進行組織
  • 字段(Field)：也稱域。表中的每一類稱為一個字段。每個字段都有相應的描述信息。
  • 記錄(Record)：表中的每一行稱為一個記錄，由若干字段組成。
  • 索引(Index)：一種特殊類型的表，其中含有關鍵字段的值和指向表實際記錄位置的指針，可以提高訪問數據庫的效率。
  • SQL語句：結構化查詢語句命令，用于存取數據以及查詢、更新和管理關系數據庫系統，同時也是數據庫腳本文件的擴展名。

2. SQL語句分類?

名詞解釋命令

DDL

（數據定義語言)

定義和管理數據對象，

如數據庫，數據表等

create、drop、alter

DML

（數據操作語言）

用于操作數據庫對象中所包含的數據

insert、update、delete

DQL

（數據查詢語言）

用于查詢數據庫數據select

DCL

（數據控制語言）

用來管理數據庫的語言，包括管理權限及數據更改

grant、commit、rollback

3. SQL語句的執行流程?

MySQL請求執行流程示意圖
  1. 詞法/語法解析（Parser）：在收到用戶發送的SQL請求后，將請求中的SQL語句按照語法規則分割成一個個單詞（tokens）。例如，SELECT、FROM、WHERE、AND、OR等都是一個單詞。對單詞進行語法解析，判斷SQL語句是否符合語法規則。如果不符合，則會返回 “語法錯誤” 的結果。
    MySQL語法解析錯誤返回結果圖
  2. 語義分析（Resolver）：對SQL語句進行語義分析，如表名、列名、索引類型等，確保它們都是正確的。如果有錯誤，會拋出語義錯誤（錯誤的表名、列名、數據類型不匹配、引用不存在的對象、違反了數據庫的約束等）。
    MySQL語義解析錯誤返回結果圖其一
  3. 邏輯改寫（Transformer）：采用不同的改寫策略（基于規則 / 基于代價）對用戶的請求字符串進行改寫并生成多個版本的執行計劃，使用成本模型估計每種改寫后的執行計劃的成本，最終讓優化器選擇成本最低的執行計劃來執行查詢。
  4. 優化器（Optimizer）：對SQL語句進行優化，如選擇最優的執行計劃、使用索引等來提高查詢效率。
  5. 代碼生成器（Code Generator）：將優化器生成的執行計劃轉換為可執行代碼。
  6. 執行SQL（Executor）：根據優化后的執行計劃，執行SQL語句。執行過程中，會進行鎖定、緩存、排序、分組等操作。
  7. 執行計劃緩存（Plan Cache）：SQL執行引擎將SQL第一次生成的執行計劃緩存在內存中，后續對該SQL的重復執行可以復用這個計劃，避免重復查詢優化的過程。

4. 存儲引擎 MyISAM 和 InnoDB的區別?

MyISAMInnoDB
外鍵不允許創建外鍵支持外鍵
事務不支持事務事務型數據庫引擎，可以使用 Commit 和 Rollback 語句
并發只支持表級鎖(table-level locking)支持行級鎖(row-level locking)和表級鎖，默認為行級鎖；
備份(緩存)只緩存索引，不緩存真實數據支持在線熱備份(緩存索引和真實數據)
崩潰恢復MyISAM 崩潰后發生損壞的概率比 InnoDB 高很多，而且恢復的速度也更慢。

5. MySQL設計三大范式

數據庫設計范式是數據庫在設計過程中，需要遵守的設計準則，避免數據冗余；

特殊情況不一定要三個范式都滿足，數據庫設計看重需求與性能，需求>性能>表結構

  • 第一范式（1NF）：每個字段都是最小字段，具有原子性，不可再分；?確保每列保持原子性
  • 第二范式（2NF）：每張表必須存在主鍵，并且其它字段也必須與主鍵直接依賴，不能僅僅與主鍵的某一部分依賴（聯合主鍵）?確保表中的每列都和主鍵相關
  • 第三范式（3NF）：每個字段不能與主鍵間接依賴，每張表只保存一種數據，表和表之間使用外鍵關系關聯；?確保每列都和主鍵列直接相關，而不是間接相關

6. 什么是 SQL 注入

  • 攻擊者通過用戶輸入的字符串內容中加入 SQL 語句，與程序中的原 SQL 語句之間產生拼接，形成惡意查詢、非法命令執行等攻擊方式
  • 避免方式：過濾輸入內容和使用參數化傳值（？占位符）

7. 內連接和外連接的區別 AND 左連接和右連接的區別

  1. 內連接是保證兩個表中所有行都滿足連接條件,join不加前綴默認為 inner join
  2. 外連接結果僅包含符合連接條件的行
  3. 外連接不僅包含符合連接條件的行，還包含左表、右表或兩個連接表中的所有數據行

外連接分為：①左外連接、②右外連接、③全外連接

  • 左外連接：保存左表全部數據，右表保留符合條件的行數據
left outer join || left join
  • 右外連接：保存右表全部數據，左表保留符合條件的行數據
right outer join || right join
  • 全外連接：保留所有行數據
full outer join || full join

8. Union 和 Union all 的區別

  1. union?是對兩個結果集進行并集操作，不包括重復行，同時進行默認規則的排序
  2. union all?也是對兩個結果集進行并集操作，包括重復行，不會對結果進行排序

9. MySQL 如何取差集

  1. 當對比的字段存在索引時，使用 NOT EXISTS效率高
  2. 當對比的字段沒有索引時，使用 left join || right join效率高

10. Delete 和 Truncate的區別

  1. Delete語句后可寫條件，Truncate不可用
  2. Delete語句是逐條記錄刪除，且刪除的記錄都會寫入日志；Truncate一次性刪除整個數據頁，日志只記錄頁釋放
  3. Delete刪空表后，會保留一個空的數據頁，Truncate 在表中不會留有任何數據頁
  4. 當使用行級鎖執行 Delete語句時，將鎖定表中各行以便刪除；Truncate始終鎖定頁和表
  5. 如果有identity產生的自增id列，Delete后仍會從上次的數字開始增加，種子不變，而Truncate后，種子會恢復初始

11. Count(*) 、Count(1)、Count(column) 的區別

  • 執行方式
    • count(1)計算當前查詢結果存在多少個常量值?1;
    • count(*)會把*號替換為所有字段名，用于計算查詢結果中每個字段存在多少個值;
    • count(column)會統計指定列中非NULL值的行數
  • 執行效率
    • 如果存在主鍵，count(主鍵)效率最高，其次是 count(column) > count(1) > count(*)

12. 模糊查詢使用什么關鍵詞？ %和 _有什么區別？

  • like 關鍵詞默認是大小寫敏感，如果需要進行不區分大小寫的模糊查詢，可以使用collate 子句指定不區分大小寫的字符集，或者使用 lower 或 upper 函數將列值轉換為小寫或大寫進行比較
  • %：表示零個或多個字符。當使用%通配符時，它可以匹配任意長度的字符串，包括空字符串
例如，WHERE column_name LIKE 'abc%'會匹配以"abc"開頭的任意字符串。
  • _：表示一個字符。當使用_通配符時，它可以匹配一個任意字符的位置
例如，WHERE column_name LIKE 'a_c'會匹配"a"后面跟著一個任意字符，然后是"c"的字符串。

13. 慢查詢的概念

  • MySQL默認10秒內沒有響應SQL結果則為慢查詢
  • MySql對慢查詢的操作：
-- 顯示到mysql數據庫的連接數
show status like 'connections';
-- 查詢慢查詢的狀態
Show variables like '%slow_query%';
-- 設置慢查詢到表 mysql.slow_log
set global log_output='TABLE';
-- 設置慢查詢的時間
set global long_query_time=3;
-- 開啟慢查詢
set global slow_query_log='ON';
-- 慢查詢的次數
show status like 'slow_queries';
-- 慢查詢記錄
select * from mysql.slow_log;
-- 慢查詢sql語句
select convert(sql_text using utf8) sql_text from mysql.slow_log
-- 關閉慢查詢
set global slow_query_log='OFF';

14. varchar和char的區別

  1. char類型的長度固定,如果存儲的字符長度小于指定長度，將會用空格進行填充，查詢時可以更快定位
  2. varchar根據實際字符串來動態分配存儲空間，最大長度是創建表時指定的，需要進行額外的長度判斷

視圖篇

15. 視圖的理解

  • 視圖是一個虛擬表，數據來自一個表或多個表，表中的數據發生改變，顯示在視圖中的數據也會發生改變
  • 視圖用于隱藏敏感數據
  • 視圖可降低復雜的查詢，易于理解和使用

16. 視圖的缺點

缺點描述
性能下降在查詢視圖時，數據庫系統需要執行視圖的定義以生成結果集，這可能會導致性能下降
更新限制某些視圖是不可更新的，特別是當視圖的定義中包含了聚合函數、group by 子句或 distinct 關鍵字時。即使視圖是可更新的，更新操作也可能會受到限制，因為數據庫系統需要確保更新操作不會破壞視圖的定義
增加依賴性和耦合性使用視圖可能會增加數據庫對象之間的依賴性和耦合性。當視圖被其他查詢或存儲過程引用時，修改視圖的定義可能會影響到引用該視圖的其他對象，導致系統的復雜性增加
查詢透明度降低視圖隱藏了底層表的細節，使得查詢的透明度降低。當開發人員查詢視圖時，可能不清楚視圖是如何定義的，從而增加了理解和調試的難度
增加維護成本在數據庫中添加、刪除或修改視圖可能會增加維護成本。特別是當數據庫模式發生變化時，需要確保視圖的定義與底層表的結構保持一致，否則可能會導致查詢失敗或產生不正確的結果

?索引篇

17.?什么是索引

  • 索引是一種用于快速查詢和檢查數據的數據庫存儲結構，保存了數據庫指定字段的數據位置
  • MySQL 最經常用的存儲結構： B+Tree 和 HashTable

作用：提升數據庫的查詢性能，如果沒有索引，數據庫的查詢會進行全表搜索，消耗時間，造成大量磁盤的IO操作；如果建立索引，則通過索引中所保存的數據位置，快速找到表中的對應記錄

18. 索引的特性

  1. 高效性：利用索引可以提高數據庫的查詢效率
  2. 唯一性：索引可以確保所查的數據的唯一性
  3. 完整性：加速表和表之間的連接，實現表與表之間的參照完整性

19. 索引的分類

分類方式分類描述
存儲方式B+Tree索引InnoDB 存儲引擎的 B+Tree 索引分為主鍵索引和輔助索引
哈希索引自適應哈希索引
邏輯主鍵索引主鍵列使用索引
輔助索引唯一索引保證該數據列的唯一性，允許數據為Null，但不能出現重復數據，一張表允許創建多個唯一索引
普通索引為了快速查詢數據，一張表允許創建多個普通索引，允許數據重復和 Null
前綴索引只適用于字符串類型的數據，對文本的前幾個字符創建索引，相比普通索引建立的數據更小
全文索引為了檢索大文本數據中的關鍵字的信息，是目前搜索引擎數據庫使用的一種技術
使用字段單列索引針對單個列創建的索引，當查詢條件只涉及單列時，可以有效提高查詢的性能
組合索引針對多個列創建的索引，當查詢條件涉及到多個列時，可以提供更好的性能，查詢時必須按照索引的順序提供條件

20. 索引的優點及缺點

優點

  1. 加快數據的檢索速度，減少數據庫需要掃描的數據行數
  2. 通過創建唯一索引，可以保證數據庫表中每一行數據的唯一性

缺點

  1. 創建索引和維護索引需要耗費許多時間
  2. 對表中數據進行增刪改的時候，如果數據有索引，索引也需要動態的修改，降低SQL的執行效率
  3. 索引需要物理文件存儲，耗費一定空間
  4. 如果數據庫的數據量比較小，那么使用索引也不能帶來很大提升

21. 如何添加/ 刪除索引(index)

  • 約束實際上就是表中數據的限制條件,目的是為了保證表中的記錄完整和有效

?主鍵索引 (primary key)

  • 確保表中每一行都有一個唯一的標識符
  • 可在其他表中作為外鍵引用，建立表與表之間的關系
-- 創建表后添加主鍵索引
alter table 表名 add primary key(字段，字段1...);
-- 移除主鍵
alter table 表名 drop primary key;

唯一索引 (unique)

  • unique 約束的字段，具有唯一性，不可重復，但可以為null
-- 創建表后添加唯一索引
alter table 表名 add unique(字段，字段1...);
-- 刪除唯一索引
alter table 表名 drop index 唯一索引名;

普通索引 (index)

  • 允許列中有重復的值，不會限制數據的插入
  • 提高查詢性能，尤其是在經常需要搜索的列上
-- 創建表后添加普通索引
alter table 表名 add index 索引名 (字段);
-- 刪除普通索引
alter table 表名 drop index 索引名;

全文索引 (fulltext)

  • 只能用于CHAR、VARCHAR或TEXT類型的列
  • 用于全文搜索，適用于文本搜索，如文章、博客等
  • 提供了強大的文本匹配和搜索功能，特別是使用MATCH AGAINST操作時
-- 創建表后添加全文索引
alter table 表名 add fulltext(字段,字段1...);
-- 移除全文索引
alter table 表名 drop index 全文索引名;

聯合索引 (復合/多列索引)

  • 在數據庫表中的兩個或更多列上創建的索引
  • 按照索引創建時定義的列的順序存儲索引值，并且通常遵循最左前綴原則提高多條件查詢的性能(特別是當查詢中包含索引的所有列或前導列時)
-- 創建表后添加聯合索引
alter table 表名 add index (字段,字段1,字段2...);
-- 移除聯合索引
alter table 表命 drop index 聯合索引名;

22. 主鍵索引和唯一索引的區別

  1. 定義數量：每張表只能有一個主鍵，而可以有多個唯一索引。主鍵是用來唯一標識表中的每一行記錄的，而唯一索引可以用來保證某一列或多列的取值唯一。
  2. 索引特性：主鍵索引是一種特殊的唯一索引，它具有自動遞增的特性（如果是整數類型），并且不允許為空。主鍵索引在物理存儲上一般會被優化為聚簇索引，即按照主鍵值的順序將數據存儲在磁盤上。而唯一索引沒有這些特性，可以包含空值。
  3. 查詢性能：由于主鍵索引的特殊性，當使用主鍵進行查詢時，MySQL可以直接定位到相應的行，因此查詢速度較快。而唯一索引在查詢時需要進行額外的查找操作。
  4. 重復值：主鍵索引要求列的值必須唯一且非空，而唯一索引則允許有重復值，但對于重復值的插入操作會失敗。
  5. 外鍵關聯：主鍵索引通常被用作外鍵關聯的參考，而唯一索引可以作為外鍵的參考，但不是必須的。

23. 什么是聯合索引？什么情況下會觸發？

  • 在MySQL中，聯合索引也稱為復合索引或多列索引，是指將多個列組合起來創建的一個索引。它可以包含多個列，并且查詢時可以使用這些列的任意組合進行查找
  • 當查詢語句的條件涉及到聯合索引的所有列或部分列時，MySQL會嘗試使用聯合索引來加速查詢，減少掃描的數據量。具體觸發聯合索引的情況包括：
  1. 查詢條件中涉及到聯合索引的所有列：如果查詢語句的條件中使用了聯合索引的所有列，并且按照索引列的順序使用，MySQL可以高效地使用聯合索引進行查找
  2. 查詢條件中涉及到聯合索引的部分列：如果查詢語句的條件中使用了聯合索引的部分列，并且按照索引列的順序使用，MySQL也可以部分利用聯合索引進行查找。這種情況下，MySQL會先根據索引找到滿足條件的記錄，然后再進行進一步的過濾
  • 當查詢條件中只使用了聯合索引的某個列而沒有使用其他列時，并不會觸發聯合索引的使用。此時，MySQL可能會選擇其他適用的索引或者進行全表掃描

24.?如何添加/ 刪除約束(constraint)

主鍵約束 (primary key)

  • 與主鍵索引類似

唯一約束 (unique)

  • 與唯一索引類似

外鍵約束 (foreign key)

  • 維護表與表之間的關系，確保數據的引用完整性
  • 防止在子表中插入不存在的父表中的值
-- 創建表后添加外鍵約束
alter table 表名  add foreign key(字段) references 父級表名(父級表字段);
-- 移除外鍵約束
alter table 表名 drop foreign key 外鍵名;

非空約束 (not null)

  • 用于保證數據的完整性，防止出現無效的數據
  • 確保列中的數據不能有NULL值，必須提供非空的數據
-- 創建表后添加非空約束，且添加約束字段不能存在 NULL 值，否則失敗
alter table 表名 modify 字段 字段類型 not null;
-- 移除非空約束
alter table 表名 modify 字段 字段類型 null;

檢查約束 (check)

  • 從 MySQL 8.0.16 開始支持
  • 用于限制列中可以接受的值，比如年齡不能為負數
  • 允許定義列中的值的范圍或條件，確保數據的合法性
-- 創建表后添加檢查約束
alter table 表名 add check (字段限制范圍);
-- 移除檢查約束
alter table 表名 drop constraint DROP 檢查約束名

25. 索引和約束的關系及區別

索引約束
關系

主鍵約束（primary key）和唯一約束（unique）會自動創建索引，以確保數據的唯一性和提高查詢效率。

外鍵約束（foreign key）通常不會自動創建索引，但在實際應用中，為了提高查詢性能，通常建議在外鍵列上手動創建索引。
區別目的主要用于提高查詢性能。索引可以快速定位到表中的數據，減少全表掃描的次數，從而加快查詢速度。主要用于保證數據的完整性和一致性。
類型包括主鍵索引、普通索引、唯一索引、全文索引、聯合索引等。包括主鍵約束、外鍵約束、唯一約束、非空約束和檢查約束等。
創建使用可以在創建表時定義，也可以在表創建后通過alter table語句或create index語句添加或刪除。通常在創建表時定義，也可以在表創建后通過alter table語句添加或刪除。
必要性是可選的，但通常為了提高性能而創建。并非所有列都需要索引，過多的索引可能會降低插入、更新和刪除操作的性能。是數據庫設計的重要組成部分，用于確保數據的準確性和可靠性。

26. B+Tree 索引（MySQL 5.5 之后默認）

  • 因為 B+ Tree 的有序性，所以除了用于查找，還可以用于排序和分組
  • InnoDB的B+Tree索引分為主鍵索引和輔助索引
  • 主鍵索引的葉子節點 data域記錄著完整的數據記錄
  • 原則：盡量選擇訪問頻率高的字段值作為主鍵索引
  • 輔助索引的葉子節點data域記錄著主鍵的值，因此在使用輔助索引進行查找時，需要先查找到主鍵值，然后再到主鍵索引中進行查找

26.1 B+樹執行查找操作

  • 進行查找操作時，首先在根節點進行二分查找，找到對應的葉子節點。然后在葉子節點上進行二分查找，找出 key 所對應的 data
  • 區間查找操作時，由于葉子節點形成了有序列表，可以直接通過指針繼續遍歷相鄰個葉子節點，提高區間查詢效率

27. B樹和B+樹的區別？

區別B樹B+樹
節點結構B樹的節點除了包含關鍵字和子節點指針外，還包含了數據記錄的指針B+樹的非葉子節點只包含關鍵字和子節點指針，所有的數據記錄都存儲在葉子節點中
葉子節點鏈表B樹的葉子節點并沒有形成一個鏈表，而是通過非葉子節點來進行定位B+樹的葉子節點之間形成了一個有序的鏈表，便于范圍查詢和遍歷整個表
查詢方式B樹可以直接從非葉子節點開始查找，然后按照關鍵字順序遍歷葉子節點B+樹必須從根節點到葉子節點遍歷整棵樹才能找到所需的數據記錄
磁盤讀寫B樹需要遍歷整棵樹B+樹的葉子節點之間形成了一個有序的鏈表，因此在進行范圍查詢時只需要遍歷葉子節點鏈表即可
使用場景數據量較小或者需要支持隨機訪問需要支持范圍查詢或者大數據量的索引

28. MySql為什么使用B+樹，不用紅黑樹？

區別紅黑樹B+樹結論
磁盤讀寫需要進行隨機讀取，導致磁盤IO次數較多B+ 樹的葉子節點形成了一個有序鏈表，可以通過順序讀取的方式高效地進行范圍查詢和遍歷由于數據庫系統通常面對大量的數據和頻繁的查詢操作，B+ 樹的磁盤讀寫優化能夠提升數據庫的整體性能
數據有序性紅黑樹沒有對數據進行排序，只是通過平衡性質來保證樹的高度平衡將數據按照關鍵字排序存儲在葉子節點中，并通過非葉子節點進行定位這種有序性使得 B+ 樹在范圍查詢、排序等操作上具有更好的性能
持久化存儲紅黑樹由于需要存儲更多的指針信息，導致節點的大小相對較小，增加了磁盤IO次數，降低了寫入性能B+ 樹的節點結構相對簡單，每個節點存儲的關鍵字數量較多，使得每次寫入磁盤的數據量較大，減少了磁盤IO次數數據庫系統需要將索引結構持久化地存儲到磁盤上，以防止系統故障時數據丟失

29. 哈希索引

  • 能以O(1)時間復雜度進行查找，但是失去了有序性
  • 無法用于排序和分組
  • 只支持精確查找，無法用于部分查找和范圍查找
  • InnoDB存儲引擎有一個特殊的功能叫”自適應哈希索引“，當某個索引值被使用的非常頻繁時，會在B+Tree索引之上創建一個哈希索引，讓B+Tree索引具有哈希索引的一些優點

30. 什么是回表？

  • 在使用索引進行查詢時，如果查詢需要返回的數據不在索引中，MySQL會根據索引中的數據行的主鍵值再次到表中取檢索數據

31. 索引覆蓋

  • 一個查詢可以完全使用索引來滿足，而無需訪問實際的數據行

好處

  1. 減少磁盤 IO：從索引中獲取，不需要回表訪問實際的數據行
  2. 減少內存開銷：當查詢只涉及到索引列，MySQL只需要將索引數據加載到內存中
  3. 減少了網絡傳輸開銷：當數據庫和應用程序分布在不同的服務器上時，索引覆蓋可以減少從數據庫服務器到應用服務器之間的網絡傳輸開銷

措施

  1. 使用合適的查詢語句：編寫查詢語句時，明確指定需要返回的列，并確保這些列都包含在索引中。避免使用 select *，它可能無法實現索引覆蓋
  2. 合理涉及索引：確保索引包含需要的所有列，盡量覆蓋查詢所需的列

32. 索引的使用場景

  1. 匹配全值：對索引中所有列都指定具體值，即對索引中的所有列都有等值匹配的條件。
  2. 匹配值的范圍查詢：對索引的值能夠進行范圍查找
  3. 匹配最左前綴：僅僅使用索引中的最左邊列進行查詢。比如組合索引（col1,col2,col3）
  4. 能夠被col1，col1+col2，col1+col2+col3的等值查詢利用到的。
  5. 僅對索引進行查詢：當查詢列都在索引字段中。即select中的列都在索引中。
  6. 匹配列前綴：僅僅使用索引的第一列，并且只包含索引第1列的開頭部分進行查找。例
  7. 如：WHERE title LIKE ‘xxx%’
  8. 索引部分等值匹配，部分范圍匹配
  9. 若列名是索引，則使用column_name is null就會使用索引

33. 索引的失效場景

  1. 使用模糊查詢時，%在字符的左側
  2. 組合索引包含從左到右的字段使用索引，不包含左邊的字段索引失效
  3. 數據類型不匹配
  4. 不等于運算（!= 、 <、>、not in）
  5. 字段內容為 null
  6. 添加索引的字段上使用函數或計算
  7. or前后條件中的字段都包含索引或前后有一個字段不包含索引

34. 索引的優化

  1. 選擇合適的字段創建索引
  2. 被頻繁更新的字段應該慎重建立索引
  3. 盡可能考慮建立聯合索引而不是單列索引
  4. 避免冗余索引
  5. 考慮在字符串類型的字段上使用前綴索引代替普通索引
  6. 避免?where子句中對索引字段使用函數，這會造成索引失效

35. SQL優化

  1. 在 SQL查詢中禁止使用 select *，必須明確查詢字段名稱
  2. 對查詢進行優化，盡量避免全表掃描，應考慮在 where 及 order by 涉及到的字段上建立索引
  3. 盡量避免在 where子句上對字段進行 null值判斷，否則會導致引擎放棄使用索引進行全表掃描
  4. 避免在where子句中使用 !=或<>操作符
  5. 避免在where子句中使用or連接條件
  6. 慎用 in和not in，否則會導致全表掃描
  7. 嚴禁左模糊查詢或者全模糊查詢，因為索引采用 B-Tree存儲，具有B-Tree的最左前綴匹配特性
  8. 盡量避免在 where子句中對字段進行算術運算操作
  9. 盡量避免在where子句中對字段進行函數操作
  10. 使用 explain查詢語句，觀察SQL執行計劃

?事務篇

36. 什么是事務

  • 事務是數據庫的一種特性，用于確保一個執行過程中所有步驟全部成功或者全部失敗，是數據庫操作的最小執行單元

37. MySQL 事務的四大特性

ACID vs BASE
  • 原子性：事務是數據庫的最小執行單元，事務的原子性確保數據庫操作過程中的所有步驟全部成功或者全部失敗
  • 一致性：數據修改前和修改后的狀態保持一致
  • 隔離性：一個事務的執行不會被其他事務干擾，一個事務內部的操作和使用的數據，在并發中對其他事務是隔離的
  • 持久性：事務一旦被提交，將對數據庫中的數據的改變是永久性的

38. NOSQL 的 BASE 理論

  • BASE是對ACID（原子性、一致性、隔離性、持久性）事務特性的一個補充，反映了NoSQL 數據庫在設計上對可用性和一致性取舍的不同。
  • 基本可用

????????系統在面臨任何故障時，仍然能夠提供一定程度的服務。即使是在高負載或系統故障的情況下，系統也能夠響應客戶端的請求，盡管響應可能不是完全正常或快速的

  • 軟狀態

????????數據的狀態可以在多個節點之間同步，而不需要立即保持一致。數據可以在多個節點上存在短暫的差異，最終會通過某種機制（如網絡同步、消息隊列等）達到一致

  • 最終一致性

????????系統不保證數據在任意時刻都是正確的，而是保證在一定時間后數據會收斂到正確的狀態

  • 核心思想：在分布式系統中，由于網絡延遲、節點故障等因素，數據的一致性可能無法實時保證，但是可以保證數據的最終一致性。

39. MySQL 實現事務的步驟

-- 關閉事務自動提交
set autocommit=0;
-- 開啟一個事務，標記事務的起始點
start transaction；
-- 向數據庫提交事務
commit;
-- 將事務回滾，所有數據庫操作被取消
rollback;
-- 開啟mysql自動提交
set autocommit=1;

40. 數據庫事務的實現原理

  • MySQL InnoDB引擎使用 redo log（重做日志）保證事務的的持久性，使用 undo log（回滾日志）來保證事務的原子性
  • redo log 是 InnoDB存儲引擎層的日志，又稱重做日志文件，用于記錄事務操作的變化，記錄的是數據修改之后的值。當MySQL意外宕機，InnoDB存儲引擎會使用 redo log恢復，以此來確保數據的持久性
  • undo log保存了事務發生之前的數據的一個版本，用于回滾
  • MySQL InnoDB引擎通過鎖機制、MVCC（多版本并發控制）等手段保證事務的隔離性
  • 保證了事務的持久性、原子性、隔離性之后，一致性才能得到保障

41. 數據庫并發事務會帶來哪些問題？

臟讀

  • 一個事務正在訪問數據并且對數據進行了修改，而修改操作還沒提交到數據庫中，這時另外一個事務也訪問這個數據，然后使用這個數據。由于這個數據是沒有提交的數據，那么另外一個事務讀到的這個數據就是“臟數據”

不可重復讀

  • 一個事務內多次讀同一個數據，在這個事務還沒結束時，另外一個事務也訪問該數據，在第一個事務中的兩次讀數據之間，由于第二個事務的修改導致第一個事務讀取到的數據可能不一致，這就發生了在一個事務內兩次讀到的數據是不一致的情況，所以稱為不可重復讀

丟失修改

  • 一個事務讀取一個數據時，另外一個事務也訪問這個數據，在第一個事務修改這個數據后，第二個事務也修改了這個數據導致第一個事務的修改丟失

幻讀

  • 與不可重復讀類似，在第一個事務讀取了幾行數據，另一個事務插入了一些數據，在隨后的查詢中，第一個事務發現多了一些原本不存在的記錄

42. 不可重復度和幻讀的區別？

  • 不可重復讀的重點是修改，多次讀取一條記錄，發現其中某些列的值被修改
  • 幻讀的重點在于新增或刪除，比如多次讀取某條件下的記錄，發現記錄增多或減少

43. 事務的隔離級別

讀未提交

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

讀已提交

  • 允許讀取并發事務已經提交的數據，可以阻止臟讀

可重復讀

  • 對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重復讀

串行化

  • 最高的隔離級別，完全服從 ACID 的隔離級別，所有的事務依次逐個執行，防止事務之間產生干擾，可阻止臟讀、不可重復度以及幻讀

鎖篇

44. MySQL中的鎖

  • 意向鎖是InnoDB存儲引擎特有的概念，它們是表級鎖，用于表示事務對表中的行有意向進行共享或排他鎖定。意向鎖不會阻塞其他事務對表的訪問，除非它們也嘗試在同一行上獲取排他鎖。

45. 悲觀鎖和樂觀鎖的區別

悲觀鎖
  • 總是假設最壞的情況，每次讀取數據都默認其他事務會修改數據，會進行加鎖操作，操作之后釋放鎖
樂觀鎖
  • 一般情況不會造成沖突，在數據進行提交更新時，才會對數據的沖突與否進行檢查，一般是通過版本號控制，適用于讀多寫少的場景

46. 什么是MVCC？

  1. MVCC是行級鎖的一個變種，在很多情況下避免了加鎖情況，開銷更低。
  2. 大多數的MVCC都實現了非阻塞的讀操作，寫操作也只鎖定必要的行；
  3. MVCC是一種用來解決 讀-寫沖突的并發控制，為事務分配單向增長的時間戳，為每個修改保存一個版本，每個事務都有一個對應版本的快照，快照版本按照單向增長的時間戳來決定先后順序
  4. 讀操作，只需要讀該事務開始前的數據庫快照，并不去讀取正在修改的數據，僅讀取事務開始前的最新版本

存儲過程篇?

47. 存儲過程

  • 在大型數據庫系統中，一組為了完成特定功能的SQL語句集，存儲在數據庫中，經過第一次編譯后，再次調用不需要重復編譯，用戶通過指定存儲過程的名字并給出參數來執行它
delimiter //
create procedure 存儲過程名([ [in | out | inout] 參數名 參數類型],...
)
begindeclare 變量 變量類型;-- 存儲邏輯 ...
end;
//
delimiter ;
  • 查看存儲過程： 
SELECT * FROM information_schema.routines WHERE routine_schema = '庫名';
  • 刪除存儲過程
drop procedure 存儲過程名;

48. 為什么使用存儲過程

  1. 業務流程復雜：業務復雜時，SQL語句相互依賴，順序執行
  2. 頻繁訪問數據庫：每條SQL語句都需單獨連接和訪問數據庫
  3. 先編譯后執行：SQL語句的執行需要先編譯

49. 存儲過程和函數的區別

要素存儲過程函數
關鍵字procedurefunction
執行可以獨立執行必須依賴表達式的調用
返回值可以定義多個返回結果只有一個返回值
功能函數不易做復雜的業務邏輯，但是存儲過程可以

50. 存儲過程的缺陷

  1. 移植性：大多數關系數據庫的存儲過程存在細微差異
  2. 維護性：存儲過程的維護成本高，修改調試較為麻煩
  3. 協作性：團隊中對于存儲過程的使用大多是依賴文檔

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/39796.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/39796.shtml
英文地址，請注明出處：http://en.pswp.cn/web/39796.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

Qt涂鴉板

Qt涂鴉板

Qt版本&#xff1a;Qt6 具體代碼&#xff1a; 頭文件 dialog.h #ifndef DIALOG_H #define DIALOG_H#include <QDialog>QT_BEGIN_NAMESPACE namespace Ui { class Dialog; } QT_END_NAMESPACEclass Dialog : public QDialog {Q_OBJECTpublic:Dialog(QWidget *parent n…
閱讀更多...
0145__contain_of的原理與實現

0145__contain_of的原理與實現

contain_of的原理與實現_contain of-CSDN博客
閱讀更多...
從零開始！Jupyter Notebook的安裝教程

從零開始！Jupyter Notebook的安裝教程

引言 Jupyter Notebook作為一種交互式的開發環境&#xff0c;已經成為數據科學和機器學習領域中不可或缺的工具之一。它能夠將代碼、文本、圖像和數據結合在一個靈活的文檔中&#xff0c;使得數據分析和可視化變得更加直觀和高效。 本文將詳細介紹Jupyter Notebook的安裝過程…
閱讀更多...
深入理解 Git `git add -p` 命令中的交互選項

深入理解 Git `git add -p` 命令中的交互選項

個人名片 &#x1f393;作者簡介&#xff1a;java領域優質創作者 &#x1f310;個人主頁&#xff1a;碼農阿豪 &#x1f4de;工作室&#xff1a;新空間代碼工作室&#xff08;提供各種軟件服務&#xff09; &#x1f48c;個人郵箱&#xff1a;[2435024119qq.com] &#x1f4f1…
閱讀更多...
500mA、低壓差、低噪聲、超快、無需旁路電容的CMOS LDO穩壓器RT9013

500mA、低壓差、低噪聲、超快、無需旁路電容的CMOS LDO穩壓器RT9013

一般描述 RT9013 SOT23-5封裝的外觀和絲印 RT9013 是一款高性能的 500mA LDO 穩壓器&#xff0c;具有極高的 PSRR 和超低壓差。非常適合具有苛刻性能和空間要求的便攜式射頻和無線應用。 RT9013的靜態電流低至25μA&#xff0c;進一步延長了電池的使用壽命。RT9013 也適用于低…
閱讀更多...
mysql在部署時的問題

mysql在部署時的問題

1.遠程連接是否開放問題 DataGrip遠程連接Ubuntu Linux MySQL服務器報錯DBMS: MySQL (no ver.)-CSDN博客 【MySQL】DataGrip遠程連接MySQL_datagrip連接遠程mysql數據庫-CSDN博客 一定要把對應端口規則打開 2.遠程連接不適用3306作為默認運行端口 打開mysql的配置文件&…
閱讀更多...
音樂發行平臺無加密開源源碼

音樂發行平臺無加密開源源碼

適用于唱片公司&#xff0c;用于接收物料&#xff0c;下載物料功能&#xff1a;個人或機構認證&#xff0c;上傳專輯和歌曲&#xff0c;版稅結算環境要求php7.4Nginx 1、導入數據庫 2、/inc/conn.php里填寫數據庫密碼等后臺路徑/admin&#xff08;可自行修改任意入口名稱&…
閱讀更多...
AI在軟件開發中的角色：助手還是取代者？

AI在軟件開發中的角色：助手還是取代者？

目錄 前言 一、AI工具現狀&#xff1a;高效助手的崛起 二、AI對開發者的影響&#xff1a;新技能與競爭力的重塑 三、AI開發的未來&#xff1a;共生而非取代 寫在最后 前言 隨著科技的飛速發展&#xff0c;生成式人工智能&#xff08;AIGC&#xff09;在軟件開發領域的應用日…
閱讀更多...
【JS】過濾數組中空值——arr.filter(Boolean)

【JS】過濾數組中空值——arr.filter(Boolean)

前言&#xff1a;過濾數組中的空值&#xff0c;包括 &#xff08;undefined、null、“”、0、false、NaN&#xff09; Boolean函數可以將一個值轉換為布爾值&#xff0c;空值會被轉換為false&#xff0c;非空值會被轉換為true 方法&#xff1a; const arr [1, 2, ""…
閱讀更多...
【SQL常用日期函數（一）】

【SQL常用日期函數（一）】

SQL 常用日期函數-基于impala 引擎 當前日期&#xff08;YYYY-MM-DD&#xff09; SELECT CURRENT_DATE(); -- 2024-06-30昨天 SELECT CURRENT_DATE(); -- 2024-06-30 SELECT CAST( DAYS_ADD(TO_DATE( CURRENT_DATE() ), -1 ) AS VARCHAR(10) ); -- 2024-06-29 SELECT CAST( …
閱讀更多...
Linux-頁表如何對物理內存進行映射

Linux-頁表如何對物理內存進行映射

1.1 頁框和頁幀 我們知道通過頁表可以將虛擬內存映射到對應的物理內存&#xff0c;而操作系統對于物理內存的管理并不是以字節為單位的&#xff0c;而是將物理內存分為許多大小為4KB的塊&#xff0c;稱為頁框或頁幀&#xff0c;這就是為什么我們在創建共享內存是建議將大小設定…
閱讀更多...
LTSPICE仿真電路：（十九）磁珠的一些簡單仿真

LTSPICE仿真電路：（十九）磁珠的一些簡單仿真

1.作用 簡單來說就是用來濾波的&#xff0c;將高頻信號轉化為熱量濾除掉&#xff0c;低頻有用信號正常通過 2.參數 上圖幾個參數比較簡單&#xff0c;就是字面上的意思&#xff0c;更重要的就是頻率阻抗圖 不同曲線代表不同型號的磁珠&#xff0c;實際上除了額定電流外&#…
閱讀更多...
基于springboot+vue+uniapp的語言課學習系統小程序

基于springboot+vue+uniapp的語言課學習系統小程序

開發語言&#xff1a;Java框架&#xff1a;springbootuniappJDK版本&#xff1a;JDK1.8服務器&#xff1a;tomcat7數據庫&#xff1a;mysql 5.7&#xff08;一定要5.7版本&#xff09;數據庫工具&#xff1a;Navicat11開發軟件&#xff1a;eclipse/myeclipse/ideaMaven包&#…
閱讀更多...
藝活網DIY手工制作網站源碼 工藝制作教程平臺源碼，帶數據

藝活網DIY手工制作網站源碼 工藝制作教程平臺源碼，帶數據

帝國CMS仿《手藝活》DIY手工制作網源碼&#xff0c;仿手藝活自適應手機版模板。 帶數據庫和圖片資源&#xff0c;一共5個G大小&#xff0c;下載需耐心。 92開發 手藝活網DIY手工制作網站源碼 創意手工藝品制作教程平臺系統帝國h5自適應手機端 是一套展示各種 DIY 小物品精美又…
閱讀更多...
@react-google-maps/api實現谷歌地圖中添加多邊圍欄，并可編輯，編輯后可獲得圍欄各個點的經緯度

@react-google-maps/api實現谷歌地圖中添加多邊圍欄，并可編輯，編輯后可獲得圍欄各個點的經緯度

先上一張效果圖 看看是不是大家想要的效果&#xff5e; ?? 由于該功能微微復雜一點&#xff0c;為了讓大家精準了解 我精簡了一下地圖代碼 大家根據自己的需求將center值和paths&#xff0c;用setState做活就可以了 1.第一步要加入項目package.json中或者直接yarn install它…
閱讀更多...
[激光原理與應用-97]：激光焊接焊中檢測系統系列介紹 - 1 - 什么是焊接以及傳統的焊接方法

[激光原理與應用-97]：激光焊接焊中檢測系統系列介紹 - 1 - 什么是焊接以及傳統的焊接方法

目錄 一、什么是焊接 1.1 概述 1.2 基本原理 二、傳統的焊接技術與方法 2.1 手工電弧焊&#xff1a; 1、定義與原理 2、特點 3、焊條類型 4、應用領域 5、安全注意事項 2.2 氣體保護焊&#xff1a; 1、原理與特點 2、應用領域 3、氣體選擇 4、注意事項 2.3 電阻…
閱讀更多...
高級IO_多路轉接之Poll

高級IO_多路轉接之Poll

文章目錄 前言一、poll二、poll使用步驟總結 前言 上一章我們學習了select&#xff0c;但是select作為早期的多路轉接接口&#xff0c;缺點十分明顯&#xff0c;于是又出現poll和epoll等接口&#xff0c;今天我們就來學習一下poll的使用 提示&#xff1a;以下是本篇文章正文內…
閱讀更多...
60種AI工具用法 學會探索AI的無限可能

60種AI工具用法 學會探索AI的無限可能

外面還在賣的課程&#xff0c;學會探索AI的無限可能&#xff0c;從構建精準的提示詞到獲取個性化新聞&#xff0c;從快速制作PPT到短視頻內容的智能提煉&#xff0c;再到編程、股市分析和視頻剪輯&#xff0c;AI工具助您工作學習效率飛躍提升&#xff01; 百度網盤 請輸入提取…
閱讀更多...
2024 世界人工智能大會暨人工智能全球治理高級別會議全體會議在上海舉辦，推動智能向善造福全人類

2024 世界人工智能大會暨人工智能全球治理高級別會議全體會議在上海舉辦，推動智能向善造福全人類

2024 年 7 月 4 日&#xff0c;2024 世界人工智能大會暨人工智能全球治理高級別會議-全體會議在上海世博中心舉辦。聯合國以及各國政府代表、專業國際組織代表&#xff0c;全球知名專家、企業家、投資家 1000 余人參加了本次會議&#xff0c;圍繞“以共商促共享&#xff0c;以善…
閱讀更多...
【圖像分割】mask2former：通用的圖像分割模型詳解

【圖像分割】mask2former：通用的圖像分割模型詳解

最近看到幾個項目都用mask2former做圖像分割&#xff0c;雖然是1年前的論文&#xff0c;但是其attention的設計還是很有借鑒意義&#xff0c;同時&#xff0c;mask2former參考了detr的query設計&#xff0c;實現了語義和實例分割任務的統一。 1.背景 1.1 detr簡介 detr算是第…
閱讀更多...
最新文章

Copyright @ 2022~2023