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缓存热点数据、非实时操作走消息队列