Mysql

1.COUNT 作用于主鍵列和非主鍵列時，結果會有不同嗎？

在大多數情況下，COUNT() 作用于主鍵列和非主鍵列時，如果這些列中不包含 NULL 值，那么結果是相同的，都表示符合條件的行數。

然而，關鍵的區別在于 COUNT(column_name) 的行為：COUNT(column_name) 只計算指定列中非 NULL 值的數量。

• 主鍵列 (Primary Key Column): 根據定義，主鍵列的值不允許為 NULL。因此，COUNT(主鍵列) 實際上等同于 COUNT(*) 或者 COUNT(1)，它會統計表中的總行數（或者符合 WHERE 子句條件的行數）。

• 非主鍵列 (Non-Primary Key Column):

  • 如果該非主鍵列不允許為 NULL (定義了 NOT NULL 約束)，那么 COUNT(非主鍵列) 的結果也會和 COUNT(*) 相同。
  • 如果該非主鍵列允許為 NULL，并且該列中實際存在 NULL 值，那么 COUNT(非主鍵列) 的結果將會小于 COUNT(*)，因為它只統計了該列中非 NULL 的行。

總結一下 COUNT 的幾種常見用法：

• COUNT(*): 計算表中的總行數（或者符合 WHERE 子句條件的行數），包含 NULL 值的行。這是最常用的方式，通常效率也較好。
• COUNT(1): 效果和 COUNT() 類似，也是計算總行數。MySQL 對其進行了優化，效率與 COUNT() 相當。
• COUNT(column_name): 計算指定列 column_name 中非 NULL 值的行數。
• COUNT(DISTINCT column_name): 計算指定列 column_name 中不重復且非 NULL 值的行數。

-- 創建一個示例表
CREATE TABLE employees (id INT PRIMARY KEY,          -- 主鍵列 (不允許 NULL)name VARCHAR(100) NOT NULL, -- 非主鍵列，不允許 NULLdepartment VARCHAR(50),      -- 非主鍵列，允許 NULLsalary DECIMAL(10, 2)
);-- 插入一些數據
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 50000.00),
(2, 'Bob', 'Engineering', 70000.00),
(3, 'Charlie', 'HR', 55000.00),
(4, 'David', NULL, 60000.00),         -- department 是 NULL
(5, 'Eve', 'Engineering', NULL),      -- salary 是 NULL
(6, 'Frank', NULL, 75000.00);       -- department 是 NULL-- 查看表中的所有數據
SELECT * FROM employees;-- 1. COUNT(*) - 統計所有行
SELECT COUNT(*) AS total_rows FROM employees;
-- 預期結果: 6-- 2. COUNT(主鍵列) - 統計主鍵列 'id' (不允許 NULL)
SELECT COUNT(id) AS count_primary_key FROM employees;
-- 預期結果: 6 (與 COUNT(*) 相同，因為主鍵列不能為 NULL)-- 3. COUNT(非主鍵列，NOT NULL) - 統計非主鍵列 'name' (不允許 NULL)
SELECT COUNT(name) AS count_name_not_null FROM employees;
-- 預期結果: 6 (與 COUNT(*) 相同，因為 'name' 列不允許 NULL)-- 4. COUNT(非主鍵列，允許 NULL) - 統計非主鍵列 'department' (允許 NULL)
SELECT COUNT(department) AS count_department_nullable FROM employees;
-- 預期結果: 4 (因為 'department' 列有兩個 NULL 值，它們不被計算在內)-- 5. COUNT(非主鍵列，允許 NULL) - 統計非主鍵列 'salary' (允許 NULL)
SELECT COUNT(salary) AS count_salary_nullable FROM employees;
-- 預期結果: 5 (因為 'salary' 列有一個 NULL 值，它不被計算在內)

2.MySQL 中的內連接（INNER JOIN）和外連接（OUTER JOIN）有什么主要的區別？

• 內連接： 返回兩個表中匹配的行（交集）。
• 外連接： 返回匹配的行加上驅動表中不匹配的行。
• 左外連接： 保留左表所有行，右表不匹配則填充 NULL。
• 右外連接： 保留右表所有行，左表不匹配則填充 NULL。

內連接（INNER JOIN）：它會只返回兩個表中連接條件相匹配的行。也就是說，只有當連接的兩個表中都存在滿足 ON 子句條件的記錄時，這條記錄才會被包含在結果集中。內連接關注的是兩個表的交集部分。

外連接（OUTER JOIN）：外連接則會返回兩個表中匹配的行，并且還會包含某個表（或兩個表）中不匹配的行。MySQL 外連接主要分為兩種：

• 左外連接（LEFT JOIN 或 LEFT OUTER JOIN）：它會返回左表中的所有行，以及右表中與左表匹配的行。如果右表中沒有與左表某行匹配的記錄，那么在結果集中，右表對應的列將會顯示為 NULL。左連接適用于需要保留左表所有數據的場景。
• 右外連接（RIGHT JOIN 或 RIGHT OUTER JOIN）：它會返回右表中的所有行，以及左表中與右表匹配的行。如果左表中沒有與右表某行匹配的記錄，那么在結果集中，左表對應的列將會顯示為 NULL。右連接適用于需要保留右表所有數據的場景。

-- 創建顧客表
CREATE TABLE Customers (CustomerID INT PRIMARY KEY,CustomerName VARCHAR(100)
);-- 創建訂單表
CREATE TABLE Orders (OrderID INT PRIMARY KEY,CustomerID INT,        -- 外鍵，關聯到 Customers 表的 CustomerIDOrderDate DATE,Amount DECIMAL(10, 2)
);-- 插入顧客數據
INSERT INTO Customers (CustomerID, CustomerName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'); -- Diana 沒有下過訂單-- 插入訂單數據
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount) VALUES
(101, 1, '2024-01-15', 150.00), -- Alice的訂單
(102, 2, '2024-01-20', 200.00), -- Bob的訂單
(103, 1, '2024-02-10', 75.50),  -- Alice的另一個訂單
(104, 5, '2024-02-15', 300.00); -- 這個訂單的CustomerID在Customers表中不存在-- 查看表數據 (可選)
SELECT * FROM Customers;
SELECT * FROM Orders;--內連接
SELECTc.CustomerName,o.OrderID,o.OrderDate,o.Amount
FROMCustomers c
INNER JOINOrders o ON c.CustomerID = o.CustomerID;--left join
SELECTc.CustomerName,o.OrderID,o.OrderDate,o.Amount
FROMCustomers c  -- 左表
LEFT JOINOrders o ON c.CustomerID = o.CustomerID; -- 右表--right join
SELECTc.CustomerName,o.OrderID,o.OrderDate,o.Amount
FROMCustomers c  -- 左表
RIGHT JOINOrders o ON c.CustomerID = o.CustomerID; -- 右表

3.能詳細描述一下 MySQL 執行一條查詢 SQL 語句的完整流程嗎？

首先，客戶端會通過連接器與 MySQL 服務器建立連接。連接器負責處理 TCP 連接、用戶身份驗證和權限校驗。

連接成功后，MySQL 會先檢查查詢緩存（在 MySQL 8.0 版本之前）。如果查詢的 SQL 語句和查詢條件與緩存中的記錄完全一致，并且查詢結果未過期，則會直接從緩存中返回結果，避免了后續的執行過程。但由于查詢緩存的命中率不高，MySQL 8.0 已經移除了這個功能。

如果查詢緩存沒有命中或者版本在 8.0 之后，SQL 語句會被發送到解析器。解析器會對 SQL 語句進行詞法分析和語法分析，識別出語句的類型、表名、字段名等，并構建一個抽象語法樹（AST）。

接下來是優化器的階段。優化器會根據一定的規則和成本模型，對語法樹進行優化，生成最優的執行計劃。這包括選擇合適的索引、決定表的連接順序等。優化器的目標是找到執行成本最低的方案。

優化完成后，執行器會根據優化器生成的執行計劃，調用存儲引擎（如 InnoDB、MyISAM 等）提供的接口來執行實際的數據查詢操作。執行器會負責控制數據的讀取和返回，它就像一個調度員，指揮存儲引擎完成任務。查詢結果返回給執行器后，由執行器最終返回給客戶端。

4.MySQL 中都有哪些常見的存儲引擎？你能簡單介紹一下嗎？

MySQL 中常見的存儲引擎主要有 InnoDB、MyISAM 和 Memory。

我比較熟悉的是 InnoDB 引擎，它也是 MySQL 的默認存儲引擎。InnoDB 支持事務，具有事務的提交、回滾和崩潰恢復功能，這保證了數據的完整性和一致性。此外，InnoDB 還支持行級鎖，這使得在并發環境下可以有更好的性能。

MyISAM 引擎是我在學習過程中了解到的，它不支持事務，只支持表級鎖。這意味著當一個會話正在寫入 MyISAM 表時，其他會話都無法讀取或寫入該表，并發性能相對較差。但 MyISAM 在某些只讀或者讀多寫少的場景下，可能會有更高的性能，因為它維護了一個表的行數，執行 COUNT(*) 操作會非常快。

Memory 引擎正如其名，它將數據存儲在內存中，因此讀寫速度非常快。但是，Memory 引擎不支持持久化存儲，一旦服務器發生故障或者重啟，數據就會丟失。它通常用于臨時存儲對性能要求較高的中間結果或者作為緩存。

當然，MySQL 還支持其他的存儲引擎，比如 Archive、CSV、NDB Cluster 等

5.MySQL 支持哪些常見的索引類型呢？

MySQL 支持多種索引類型，這主要取決于存儲引擎的實現。不同的存儲引擎支持的索引類型可能會有所不同。

從存儲結構上來看，主要的索引類型有：

• B+ 樹索引：這是 MySQL 中最常見和最廣泛使用的索引類型，尤其是在 InnoDB 和 MyISAM 存儲引擎中。它是一種平衡樹結構，非常適合范圍查詢、排序查詢和等值查詢。
• 哈希索引：這種索引類型主要由 Memory 存儲引擎實現。它的特點是查找速度非常快，時間復雜度可以達到 O(1)，但它不支持范圍查詢、排序等操作。
• 全文索引：全文索引用于在文本類型的數據中進行關鍵詞搜索，可以支持模糊匹配等功能。它主要用于解決 LIKE ‘%keyword%’ 這類查詢效率低下的問題。

此外，我們還可以從其他角度對索引進行分類：

• 按存儲方式（針對 InnoDB 引擎）：可以分為聚簇索引和非聚簇索引。
• 按字段特性：可以分為主鍵索引、唯一索引、普通索引和前綴索引。
• 按索引列數：可以分為單列索引和聯合索引。

我個人比較常用的是 B+ 樹索引，因為它是 InnoDB 引擎默認使用的索引類型，而且功能非常全面，支持各種常見的查詢場景。

順便提一下，MySQL 選擇使用樹結構（特別是 B+ 樹）作為索引，主要是因為樹結構能夠很好地支持范圍查詢，并且在磁盤 I/O 方面做了優化，更適合數據庫這種需要頻繁進行磁盤操作的場景。像哈希索引雖然查找快，但不支持范圍查詢，而跳表在磁盤場景下的適應性不如樹結構。

6.InnoDB 存儲引擎的索引底層是基于什么數據結構實現的呢？

InnoDB 存儲引擎主要采用 B+ 樹作為其索引的數據結構。

B+ 樹是一種自平衡的多路查找樹。對于一個度數為 m 的 B+ 樹，它的每個節點最多可以有 m 個子節點，并且所有的數據都存儲在葉子節點上。葉子節點之間通常會通過指針連接起來，形成一個有序鏈表（在 InnoDB 中，這個鏈表是雙向的）。

B+ 樹的一些關鍵特性使得它非常適合作為數據庫索引的底層結構，比如它的平衡性減少了磁盤的IO次數，保證了查詢效率，數據只存儲在葉子節點的特性以及葉子節點之間的鏈表結構都為范圍查詢提供了便利。

7.能詳細說說 B+ 樹這種數據結構都有哪些特點嗎？

B+ 樹作為一種常用的索引結構，它有幾個非常重要的特性：

• 首先，B+ 樹的中間節點（也就是非葉子節點）不會存儲實際的數據，它們只存儲索引信息（也就是鍵值）以及指向子節點的指針。這樣做的好處是，在相同的磁盤頁大小下，中間節點可以存儲更多的索引，從而使得 B+ 樹更加“矮胖”，降低了樹的高度。更低的樹高度意味著查詢時需要訪問的磁盤 I/O 次數更少，提高了查詢效率。
• 其次，B+ 樹的所有實際數據都存儲在葉子節點上。每個葉子節點包含了索引的鍵值以及對應的數據記錄。
• 第三個重要的特性是，B+ 樹的所有葉子節點之間會通過雙向指針（在 InnoDB 中）串聯在一起，形成一個雙向鏈表。這個特性對于進行范圍查詢（例如查找某個區間內的數據）非常高效。我們只需要找到范圍的起始葉子節點，然后沿著鏈表順序遍歷即可，不需要再回到上層節點進行搜索。同時，這個鏈表也方便進行全表掃描。
• 最后，B+ 樹的查詢性能非常穩定。因為所有的數據都存儲在葉子節點，所以每次查詢都需要從根節點遍歷到葉子節點，查詢路徑的長度是相同的，這確保了所有數據項的檢索都具有相同的 I/O 延遲。而且，由于 B+ 樹通常能夠保持很低的樹高度（例如，3-4 層的 B+ 樹可以存儲千萬級別的數據），因此查詢效率非常高。

8.B+ 樹和 B 樹這兩種數據結構有什么主要的區別呢？

B+ 樹和 B 樹都是常用的多路平衡查找樹，它們之間主要的區別可以從以下幾個方面來說明：

• 數據存儲的區別：B 樹的所有節點（包括中間節點和葉子節點）都會存儲索引和實際的數據，而 B+ 樹只有葉子節點才會存儲實際的數據，中間節點只存儲索引信息和指向子節點的指針。這意味著在存儲相同數據量的情況下，B+ 樹的中間節點可以存儲更多的索引，因此 B+ 樹通常會比 B 樹更加“矮胖”，樹的高度更低，從而減少了查詢葉子節點所需的磁盤 I/O 次數。
• 范圍查詢的區別：B+ 樹的所有葉子節點之間會通過雙向指針串聯在一起，形成一個有序鏈表。這種結構使得 B+ 樹在進行范圍查詢時非常高效。我們只需要找到范圍的起始點，然后沿著鏈表順序遍歷即可。而 B 樹并沒有將葉子節點用鏈表連接起來，進行范圍查詢時可能需要通過中序遍歷，這可能會涉及更多節點的磁盤 I/O 操作，因此在范圍查詢方面，B+ 樹通常比 B 樹更高效。
• 查詢效率的區別：在 B 樹中，如果我們要查找的值恰好在某個非葉子節點上，那么在查找到該節點后就可以直接結束查詢，B 樹的優點在于其查找速度可能更快，尤其是在要查找的數據位于靠近根節點的非葉子節點時。然而，B+ 樹由于數據只存儲在葉子節點，所以每次查詢都必須從根節點搜索到葉子節點，查詢路徑的長度是固定的。從平均時間代價來看，B 樹可能在某些情況下會比 B+ 樹稍快一些，但是 B+ 樹的查詢性能更加穩定，因為每一次成功的查詢都需要訪問到葉子節點，具有相同的 I/O 延遲。

9.你覺得為什么 MySQL 會選擇使用 B+ 樹作為其索引結構呢？

MySQL 選擇使用 B+ 樹作為其索引結構，我認為主要有以下幾個重要的原因：

• 首先，B+ 樹是一種多路平衡查找樹，這種結構非常適合磁盤 I/O 操作。數據庫的數據通常存儲在磁盤上，而每次磁盤 I/O 的成本是比較高的。B+ 樹的非葉子節點只存儲索引鍵值和子節點指針，而不存儲實際的數據，這使得每個節點能夠存儲更多的索引，從而顯著降低了樹的高度。例如，一個 3-4 層的 B+ 樹就可以存儲千萬級別的數據，這意味著查詢一條數據只需要 3-4 次磁盤 I/O 操作，大大提高了查找效率。相比之下，像二叉搜索樹（包括平衡樹和紅黑樹），在存儲大量數據時樹的高度會很高，導致更多的磁盤 I/O。
• 其次，B+ 樹通過自平衡的機制（節點的分裂和合并）來保持樹的平衡，這確保了查詢路徑的長度是相對穩定的，從而保證了查詢、插入和刪除操作的時間復雜度都是 O(log n)，具有較好的性能穩定性。對于頻繁進行增刪改操作的數據庫來說，這一點非常重要。
• 第三，B+ 樹特別適合范圍查詢。這是因為 B+ 樹的所有葉子節點都通過鏈表連接在一起，形成一個有序鏈表。在進行范圍查詢時，我們只需要找到范圍的起始葉子節點，然后沿著鏈表順序掃描即可，非常高效。而像 B 樹在進行范圍查詢時可能需要進行多次中序遍歷。
• 此外，對比其他數據結構，例如哈希表雖然等值查詢很快，但是不支持范圍查詢；跳表在內存中表現良好，但在磁盤場景下不如 B+ 樹；B 樹雖然所有節點都存儲數據，可能在某些情況下等值查詢更快，但其范圍查詢性能不如 B+ 樹，且查詢性能不如 B+ 樹穩定。

綜上所述，B+ 樹在平衡性、查詢效率（特別是范圍查詢）、以及對磁盤 I/O 的優化等方面都非常適合數據庫索引的需求，因此成為了 MySQL 等主流數據庫系統的首選索引結構。

10.聚簇索引和非聚簇索引之間有什么主要的區別？

聚簇索引和非聚簇索引是 MySQL 中兩種主要的索引類型，它們最主要的區別在于 B+ 樹的葉子節點所存儲的內容不同：

• 聚簇索引：在 InnoDB 存儲引擎中，主鍵索引就是聚簇索引。聚簇索引的 B+ 樹的葉子節點存儲的是完整的行數據，包含了該行所有列的值。由于數據和索引是存儲在一起的，所以通過聚簇索引查找數據時，可以直接獲取到整行數據，不需要進行額外的查找。一個表只能有一個聚簇索引，通常是表的主鍵。
• 非聚簇索引（也叫二級索引或輔助索引）：非聚簇索引的 B+ 樹的葉子節點存儲的不是完整的行數據，而是索引列的值以及對應行的主鍵 ID。如果我們需要查詢的數據列不是索引列本身，也不是主鍵，那么在使用非聚簇索引查詢到主鍵 ID 后，通常還需要回到聚簇索引中再進行一次查找，才能獲取到完整的行數據，這個過程被稱為“回表”。一個表可以有多個非聚簇索引。

因此，從查詢效率上來說，通常情況下，通過聚簇索引查詢數據的速度會更快，因為只需要掃描一次 B+ 樹就可以獲取到所需的數據。而通過非聚簇索引查詢時，可能需要掃描兩次 B+ 樹（一次非聚簇索引，一次聚簇索引，即回表）。

另外，需要補充一點的是，如果一個表沒有顯式地定義主鍵，InnoDB 會默認選擇一個唯一的非空索引作為聚簇索引。

CREATE TABLE users (id INT PRIMARY KEY,         -- 聚簇索引 (主鍵)name VARCHAR(100),email VARCHAR(100),age INT
);-- 在 email 列上創建非聚簇索引
CREATE INDEX idx_email ON users (email);-- 由于 name 和 age 列不在 idx_email 的葉子節點中，數據庫必須使用 id = 10 這個主鍵值去聚簇索引中查找完整的行數據，然后取出 name 和 age。這就是“回表”
SELECT name, age FROM users WHERE email = 'test@example.com';-- 查詢所需的所有信息都在索引的葉子節點中找到了，無需回表。 這個查詢就是使用了覆蓋索引
SELECT email, id FROM users WHERE email = 'test@example.com';-- 即使只查詢 email 字段，也構成覆蓋索引
SELECT email FROM users WHERE email = 'test@example.com';