MySQL核心

索引

什么是索引？索引的优缺点？

答案： 索引是帮助MySQL高效获取数据的数据结构，类似于书的目录。

优点：

大大加快数据检索速度
通过索引列对数据进行排序，降低排序成本
将随机I/O变为顺序I/O

缺点：

占用存储空间
降低写操作（INSERT、UPDATE、DELETE）性能
需要维护索引

MySQL索引的数据结构？为什么用B+树？

答案：

常见数据结构对比：

数据结构	查询时间	缺点
数组	O(n)	查询慢
二叉搜索树	O(log n)	可能退化成链表
平衡二叉树	O(log n)	树高度大，磁盘I/O多
B树	O(log n)	非叶子节点存数据，浪费空间
B+树	O(log n)	✓ 最优

B+树的优势：

非叶子节点不存数据，只存索引，可以存更多索引，减少树高度
叶子节点用链表连接，范围查询效率高
所有数据都在叶子节点，查询性能稳定
更适合磁盘存储，减少磁盘I/O

B+树结构示例（3阶）：
           [10, 20]
          /    |    \
      [5,8]  [15]  [25,30]
       / \    |     /  |  \
     5,8  10 15,20 25 30 ...
     ↓    ↓   ↓    ↓  ↓
    (叶子节点用链表连接，存储完整数据)

聚簇索引和非聚簇索引的区别？

答案：

聚簇索引（Clustered Index）：

数据和索引存储在一起
InnoDB的主键索引就是聚簇索引
一个表只能有一个聚簇索引
叶子节点存储完整的行数据

非聚簇索引（Secondary Index）：

索引和数据分开存储
叶子节点存储主键值
需要回表查询完整数据

sql

-- 假设有表：user(id, name, age)，id是主键

-- 主键索引（聚簇索引）
SELECT * FROM user WHERE id = 1;
-- 直接从聚簇索引获取完整数据

-- 普通索引（非聚簇索引）
SELECT * FROM user WHERE name = 'Tom';
-- 1. 从name索引找到id
-- 2. 回表：根据id从聚簇索引获取完整数据

回表示意图：

name索引(非聚簇)          id索引(聚簇)
    Tom → id=5    →→→    id=5 → (5, Tom, 20)

什么是覆盖索引？

答案： 查询的列都在索引中，不需要回表查询。

sql

-- 建立联合索引
CREATE INDEX idx_name_age ON user(name, age);

-- 覆盖索引（不需要回表）
SELECT name, age FROM user WHERE name = 'Tom';

-- 非覆盖索引（需要回表）
SELECT * FROM user WHERE name = 'Tom';

优点：

避免回表，减少I/O
提高查询性能

什么是最左前缀原则？

答案： 联合索引遵循最左前缀匹配原则，从左到右依次匹配。

sql

-- 建立联合索引
CREATE INDEX idx_abc ON user(a, b, c);

-- 会使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 只用到a

-- 不会使用索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

原理：索引按照(a, b, c)顺序排序，如果不从a开始，无法利用索引的有序性。

索引失效的场景？

答案：

违反最左前缀原则

sql

WHERE b = 2  -- 联合索引(a,b,c)

使用函数或表达式

sql

WHERE YEAR(create_time) = 2024  -- 失效
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'  -- 生效

隐式类型转换

sql

-- phone是varchar类型
WHERE phone = 12345678901  -- 失效，发生了类型转换
WHERE phone = '12345678901'  -- 生效

使用!=、<>、NOT IN

sql

WHERE age != 18  -- 可能失效

LIKE以%开头

sql

WHERE name LIKE '%Tom'  -- 失效
WHERE name LIKE 'Tom%'  -- 生效

OR条件

sql

WHERE a = 1 OR b = 2  -- 如果b没有索引，整个查询失效

范围查询后的列

sql

-- 联合索引(a, b, c)
WHERE a = 1 AND b > 2 AND c = 3  -- c的索引失效

如何优化慢查询？

答案：

开启慢查询日志

sql

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过2秒记录

使用EXPLAIN分析

sql

EXPLAIN SELECT * FROM user WHERE name = 'Tom';

EXPLAIN关键字段：

type：访问类型（system > const > eq_ref > ref > range > index > ALL）
key：实际使用的索引
rows：扫描的行数
Extra：额外信息（Using filesort、Using temporary等）

优化手段：

添加索引
避免SELECT *
使用覆盖索引
分页优化
避免子查询，改用JOIN

sql

-- 深分页优化
-- 慢查询
SELECT * FROM user LIMIT 1000000, 10;

-- 优化：使用主键索引
SELECT * FROM user WHERE id > 1000000 LIMIT 10;

-- 或使用子查询
SELECT * FROM user WHERE id IN (
    SELECT id FROM user LIMIT 1000000, 10
);

事务

什么是事务？事务的四大特性？

答案： 事务是一组操作的集合，要么全部成功，要么全部失败。

ACID特性：

原子性(Atomicity)：事务不可分割，要么全做要么全不做
一致性(Consistency)：事务前后数据完整性保持一致
隔离性(Isolation)：多个事务并发执行互不干扰
持久性(Durability)：事务提交后永久保存

sql

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK

事务的隔离级别？

答案：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✓	✓	✓
READ COMMITTED	✗	✓	✓
REPEATABLE READ（MySQL默认）	✗	✗	✗
SERIALIZABLE	✗	✗	✗

问题说明：

脏读：读到其他事务未提交的数据
不可重复读：同一事务内多次读取同一数据，结果不一致（UPDATE）
幻读：同一事务内多次查询，结果集不一致（INSERT/DELETE）

sql

-- 查看隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL如何实现MVCC？

答案： MVCC (Multi-Version Concurrency Control) 多版本并发控制，通过保存数据的历史版本实现并发控制。

核心概念：

隐藏字段：
- DB_TRX_ID：最后修改该行的事务ID
- DB_ROLL_PTR：回滚指针，指向undo log
- DB_ROW_ID：隐藏主键
undo log：记录数据的历史版本
Read View：事务快照，记录当前活跃事务

工作流程：

1. 事务开始时创建Read View
2. 查询数据时，根据Read View判断数据版本是否可见
3. 如果不可见，通过undo log找到可见的历史版本

可见性判断：

java

if (DB_TRX_ID < min_trx_id) {
    // 数据在事务开始前已提交，可见
} else if (DB_TRX_ID > max_trx_id) {
    // 数据在事务开始后才生成，不可见
} else if (DB_TRX_ID in active_trx_ids) {
    // 数据由活跃事务生成，不可见
} else {
    // 数据已提交，可见
}

锁

MySQL的锁有哪些？

答案：

按粒度分类：

表锁：锁整张表，开销小，并发低
行锁：锁某一行，开销大，并发高
间隙锁：锁索引记录之间的间隙，防止幻读

按类型分类：

共享锁(S锁)：读锁，多个事务可以同时持有
排他锁(X锁)：写锁，只能一个事务持有

sql

-- 共享锁
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁
SELECT * FROM user WHERE id = 1 FOR UPDATE;

InnoDB锁类型：

Record Lock：记录锁，锁住索引记录
Gap Lock：间隙锁，锁住索引记录之间的间隙
Next-Key Lock：Record Lock + Gap Lock

什么是死锁？如何避免？

答案： 两个或多个事务互相等待对方释放锁，形成循环等待。

示例：

sql

-- 事务1
START TRANSACTION;
UPDATE user SET name = 'A' WHERE id = 1;  -- 锁住id=1
UPDATE user SET name = 'B' WHERE id = 2;  -- 等待id=2的锁

-- 事务2
START TRANSACTION;
UPDATE user SET name = 'C' WHERE id = 2;  -- 锁住id=2
UPDATE user SET name = 'D' WHERE id = 1;  -- 等待id=1的锁，死锁！

避免死锁：

按相同顺序访问资源
尽量使用索引，减少锁范围
缩短事务时间
降低隔离级别
使用乐观锁

sql

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

主从复制

MySQL主从复制原理？

答案：

三个线程：

Master - Binlog Dump线程：读取binlog发送给Slave
Slave - I/O线程：接收binlog写入relay log
Slave - SQL线程：读取relay log执行SQL

复制流程：

Master
  ↓ (写入binlog)
Binlog Dump线程
  ↓ (发送binlog)
Slave I/O线程
  ↓ (写入relay log)
Slave SQL线程
  ↓ (执行SQL)
Slave数据库

复制模式：

异步复制（默认）：Master不等Slave确认
半同步复制：至少一个Slave确认后才返回
全同步复制：所有Slave确认后才返回

主从延迟如何解决？

答案：

原因：

Slave性能差
大事务执行时间长
网络延迟
Slave并发回放能力不足

解决方案：

升级Slave硬件
并行复制：MySQL 5.7+支持
读写分离：写主库，读从库
缓存：热点数据放Redis
分库分表：减少单库压力

练习题

为什么InnoDB推荐使用自增主键？
什么是回表？如何避免？
COUNT(*)、COUNT(1)、COUNT(列)的区别？
InnoDB和MyISAM的区别？

MySQL核心 ​

索引 ​

什么是索引？索引的优缺点？ ​

MySQL索引的数据结构？为什么用B+树？ ​

聚簇索引和非聚簇索引的区别？ ​

什么是覆盖索引？ ​

什么是最左前缀原则？ ​

索引失效的场景？ ​

如何优化慢查询？ ​

事务 ​

什么是事务？事务的四大特性？ ​

事务的隔离级别？ ​

MySQL如何实现MVCC？ ​

锁 ​

MySQL的锁有哪些？ ​

什么是死锁？如何避免？ ​

主从复制 ​

MySQL主从复制原理？ ​

主从延迟如何解决？ ​

练习题 ​

MySQL核心

索引

什么是索引？索引的优缺点？

MySQL索引的数据结构？为什么用B+树？

聚簇索引和非聚簇索引的区别？

什么是覆盖索引？

什么是最左前缀原则？

索引失效的场景？

如何优化慢查询？

事务

什么是事务？事务的四大特性？

事务的隔离级别？

MySQL如何实现MVCC？

锁

MySQL的锁有哪些？

什么是死锁？如何避免？

主从复制

MySQL主从复制原理？

主从延迟如何解决？

练习题