MySQL 基本面試題

一、SQL的基本操作

1、SQL查詢的執行順序

2、count(*)、count(1) 、count(列名) 的區別

3、char 和 varchar 的區別

4、MySQL 中常用的基礎函數

5、MySQL的執行流程

6、MyISAM和InnoDB的區別

二、事務

1、事務的基本概念

2、事務的四大特性（ACID)

3、事務的四種隔離級別

4、MVCC多版本并發控制協議

三、索引

1、什么是索引

2、索引按功能分類

3、什么是索引覆蓋和回表查詢

4、什么是最左匹配原則

5、MySQL 索引失效的常見場景

四、鎖

1、按鎖粒度分類

2、按鎖模式分類

五、優化

1、針對千萬級數據表的性能優化

六、MySQL 的組復制

1、什么是MySQL Group Replication？它與傳統主從復制有什么區別？

2、Group Replication如何保證數據一致性

3、當Group Replication出現腦裂問題時如何解決？

一、SQL的基本操作

1、SQL查詢的執行順序

from > join > where > group by > having > select > order by > limit

（1）FROM 和 JOIN?- 首先確定數據來源，包括表及其連接方式

（2）WHERE?- 對數據進行初步篩選

（3）GROUP BY?- 按照指定列分組

（4）HAVING?- 對分組后的結果進行篩選

（5）SELECT?- 選擇要返回的列（包括計算列）

（6）DISTINCT?- 去除重復行

（7）ORDER BY?- 對結果排序

（8）LIMIT - 限制返回的行數

2、count(*)、count(1) 、count(列名) 的區別

（1）count(*)：統計表中所有行的數量，包括 NULL 值。

（2）count(1)：統計表中所有行的數量，與 count(*)?效果相同，MySQL 會優化為相同執行計劃。

（3）count(列名)：統計指定列中非 NULL 值的數量。

3、char 和 varchar 的區別

（1）存儲方式：char?固定長度，總是占用定義的長度空間，例如 char（9）只存了3個字節，那么剩余的6個字節插入時會用空格填充到指定長度，查詢時會去除尾部空格。varchar 可變長度，按原樣存儲，不會自動添加或去除空格。

（2）存儲效率：char?適合存儲長度基本固定的數據（如MD5哈希值、國家代碼等）。varchar?適合存儲長度變化大的數據（如用戶名、地址等）。

4、MySQL 中常用的基礎函數

（1）concat（a,b,c）：合并字符串?

-- 合并字符串
SELECT name,department, CONCAT(name, '(', department, ')') AS name_dept FROM employees;

（2）SUBSTRING(s, start, length)，從字符串 s 的 start 位置截取長度為 length 的子字符串。

--  SUBSTRING 提取字符串
SELECT email, SUBSTRING(email, 1, 5) AS prefix FROM employees WHERE email IS NOT NULL;

（3）replace(原始字符串, 要查找的子串, 替換為的新字符串)

-- 將 name 中的"八"替換為"九"
SELECT name,REPLACE(name, '八', '九') AS new_description
FROM employees;

5、MySQL的執行流程

（1）連接階段：客戶端通過連接器與MySQL服務器建立連接，連接器負責身份驗證（用戶名/密碼驗證），驗證通過后，連接器檢查權限表確定用戶的權限。

（2）查詢緩存階段：服務器查詢緩存，如果找到完全匹配的緩存，直接返回結果。

（3）解析階段：將 SQL 語句分解，檢查 SQL 語句是否正確，生成解析樹。

（4）預處理階段：檢查表和列是否存在，檢查列名是否歧義，檢查用戶是否有權限訪問相關表。

（5）查詢優化階段：優化器會重寫查詢以提高性能，生成執行計劃，選擇最優計劃。

（6）執行階段：通過執行計劃查詢引擎，并調用API接口訪問存儲引擎獲取數據。

（7）返回結果階段：將查詢結果返回客戶端，并存入緩存。

6、MyISAM和InnoDB的區別

特性	MyISAM	InnoDB
事務支持	? 不支持	? 支持 ACID 事務
鎖機制	表級鎖	行級鎖（默認）、支持多版本并發控制 (MVCC)
外鍵支持	? 不支持	? 支持
崩潰恢復	? 較差（可能丟失數據）	? 優秀（通過事務日志恢復）
存儲結構	3個文件： .frm(表結構) .MYD(數據) .MYI(索引)	1個文件： .frm(表結構)+表空間文件（數據和索引）
緩存機制	只緩存索引（Key Cache）	緩存數據和索引（Buffer Pool）
全文索引	? 支持（FULLTEXT）	? MySQL 5.6+ 支持
*COUNT() 效率**	? 極快（存儲行數）	? 較慢（需掃描表或索引）
壓縮表	? 支持	? 不支持
熱備份	? 需要鎖表	? 支持（通過事務日志）
適用場景	讀密集型應用不需要事務大量COUNT查詢	寫密集型應用需要事務高并發操作
默認引擎	MySQL 5.5 之前默認	MySQL 5.5+ 默認
數據文件大小限制	256TB	64TB（理論上可更大）
AUTO_INCREMENT	表級計數器	內存中的計數器（更高效）
地理空間索引	? 支持	? MySQL 5.7+ 支持
哈希索引	? 不支持	? 支持（自適應哈希索引）

二、事務

1、事務的基本概念

事務(Transaction)是數據庫操作的最小工作單元，是作為單個邏輯工作單元執行的一系列操作，這些操作要么全部執行，要么全部不執行。

START TRANSACTION;  -- 或 BEGIN
-- 執行SQL語句
COMMIT;            -- 提交事務
-- 或
ROLLBACK;          -- 回滾事務

2、事務的四大特性（ACID)

（1）原子性

定義：事務是一個不可分割的工作單位，事務中的操作要么全部成功，要么全部失敗回滾。

實現原理：通過 undo log 進行回滾。

（2）一致性

定義：事務執行前后，數據完整性約束沒有被破壞。

（3）隔離性

定義：多個事務并發執行時，一個事務的執行不應影響其他事務的執行。

（4）持久性

定義：事務一旦提交，其結果就是永久性的，即使系統故障也不會丟失。

實現原理：通過redo log(重做日志)實現。

3、事務的四種隔離級別

隔離級別名稱	定義
讀未提交(READ UNCOMMITTED)	最低級別，事務可以讀取未提交的數據，可能導致臟讀
讀已提交(READ COMMITTED)	提高了數據一致性，事務只能讀取已提交的數據，但仍可能導致不可重復讀
可重復讀(REPEATABLE READ)	默認級別，確保在同一事務中多次查詢同一數據的結果相同，避免不可重復讀，但可能導致幻讀
串行化(SERIALIZABLE)	最高級別，強制事務串行執行，完全避免臟讀、不可重復讀與幻讀，但相應地降低并發性

問題類型	描述	各隔離級別能否解決
臟讀(Dirty Read)	讀取到其他事務未提交的數據	RU允許，RC/RR/SERIALIZABLE解決
不可重復讀	同一事務內兩次讀取同一數據結果不同	RR/SERIALIZABLE解決
幻讀(Phantom)	同一事務內兩次查詢返回不同行數	SERIALIZABLE完全解決，RR部分解決

4、MVCC多版本并發控制協議

MVCC（多版本并發控制）是數據庫管理系統中的關鍵技術，它通過數據版本管理顯著提升了并發性能和讀取效率。該技術通過為每個事務創建數據快照，使事務始終看到開始時的數據狀態，從而實現了無沖突的并發讀取。即便其他事務在此期間修改數據，當前事務仍能保持數據視圖的一致性。這種機制完美平衡了數據一致性和系統性能需求，尤其適用于高并發查詢場景，已成為主流數據庫系統的核心特性之一。

三、索引

1、什么是索引

索引是數據庫中用于加速查詢的一種數據結構，它允許快速查找數據而不是對整個表進行掃描。

2、索引按功能分類

（1）普通索引：最基本的索引，無特殊約束

（2）唯一索引：確保索引列的值唯一

（3）主鍵索引：特殊的唯一索引，不允許 NULL 值

（4）復合索引：多個列組合的索引

3、什么是索引覆蓋和回表查詢

索引覆蓋：當索引包含查詢所需的所有字段時，MySQL可以直接從索引中獲取數據，無需訪問數據行。
回表查詢：需要根據索引查找到主鍵后，再通過主鍵查詢完整數據。

4、什么是最左匹配原則

MySQL 在利用復合索引（多列索引）時，會從索引的最左列開始向右匹配，直到遇到范圍查詢(>、<、like、between等)就停止匹配。

5、MySQL 索引失效的常見場景

（1）復合索引未遵循最左匹配原則。

（2）對列使用函數或運算符。

SELECT * FROM users WHERE YEAR(create_time) = 2023;  -- 對列使用函數
SELECT * FROM products WHERE price * 2 > 100;       -- 對列進行運算

（3）隱式類型轉換。

-- user_id 是字符串類型
SELECT * FROM users WHERE user_id = 123;  -- 數字與字符串比較

（4）復合索引不能使用不等于（!=或<>）或 is null（is not null），否則索引失效。

（5）like盡量以常量開頭，不要以%開頭，否則索引失效

（6）盡量不要使用or，否則索引失效。

四、鎖

1、按鎖粒度分類

鎖類型	描述	存儲引擎支持	特點
表鎖	鎖定整張表	所有引擎支持	開銷小、加鎖快，但并發度低
行鎖	鎖定表中的單行記錄	僅InnoDB支持	開銷大、加鎖慢，但并發度高
頁鎖	鎖定數據頁（BDB引擎支持）	僅BDB支持（已基本淘汰）	介于表鎖和行鎖之間

2、按鎖模式分類

鎖模式	簡稱	描述	兼容性
共享鎖(S)	S鎖	允許其他事務讀但不可寫	與共享鎖兼容，與排他鎖互斥
排他鎖(X)	X鎖	禁止其他事務加任何鎖	與其他所有鎖都互斥

五、優化

1、針對千萬級數據表的性能優化

（1）索引優化：建立合適索引，使用復合索引遵循最左前綴原則。

（2）表結構優化：將大字段拆分到單獨表，使用合適的數據類型。

（3）查詢優化：使用EXPLAIN分析執行計劃

-- 傳統分頁(性能差)
SELECT * FROM table LIMIT 1000000, 20;-- 優化分頁(使用索引覆蓋)
SELECT * FROM table 
WHERE id > (SELECT id FROM table ORDER BY id LIMIT 1000000, 1)
LIMIT 20;

（4）架構層優化：讀寫分離、分庫分表、使用Redis緩存熱點數據、非實時操作走消息隊列