MySQL 面试笔记

参考：MySQL 事务与隔离级别、为什么用 B+ 树做索引

1 事务隔离级别

四种隔离级别由低到高：

Read Uncommitted（读取未提交）：会读到别的事务未提交的数据，若对方回滚则读到脏数据，即【脏读】；
Read Committed（读取已提交）：可能出现【不可重复读】——同一事务内多次读同一数据，因中间有事务提交修改而不一致；
Repeatable Read（可重复读）：MySQL/InnoDB 默认级别。可能出现【幻读】——同一事务内相同条件读到的记录数不同（中间有其他事务插入/删除）；
Serializable（串行读）：事务串行执行，脏读/不可重复读/幻读都不会出现，但效率急剧下降，一般不使用。

幻读 vs 不可重复读：不可重复读重点是修改（读到的数据不一致）；幻读重点是新增/删除（读到的记录数不一样）。

查看隔离级别：

        
select @@global.tx_isolation;  -- 系统级
select @@tx_isolation;         -- 会话级

2 事务

概念：逻辑上的一组操作，要么都执行、要么都不执行。

并发事务的问题

脏读、丢失修改、不可重复读、幻读（见上）；
不可重复读重点是修改，幻读重点是新增或删除。

ACID 特性

Atomicity 原子性：要么全部提交，要么全部回滚；
Consistency 一致性：总是从一个一致状态转换到另一个一致状态；
Isolation 隔离性：提交前对其他事务不可见；
Durability 持久性：提交后永久保存，故障也不丢失。

3 MVCC 多版本并发控制

解决读-写冲突，不用加锁，通过生成一致性数据快照提供一致性读取，读不阻塞写、写不阻塞读。

InnoDB 实现方式：

事务以排他锁形式修改原始数据；
把修改前的数据存放于 undo log，通过回滚指针与主数据关联；
commit 则什么都不做，失败则恢复 undo log 中的数据（rollback）。

MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。

4 为什么索引能提高查询速度

MySQL 基本存储结构是【页】：

各数据页组成双向链表；
每个数据页内记录组成单向链表；
每个数据页为记录生成【页目录】，按主键查找可在页目录中用二分法快速定位槽，再遍历槽内记录。

按非主键列查找只能从最小记录遍历单链表，所以没有索引的查询会很慢，索引就是为了减少这种遍历。

5 为什么用 B+ 树做索引

核心衡量标准是磁盘 IO 次数。索引很大，存储在磁盘上。B-树每个节点都有 data 域，节点大、单次 IO 读出的有效数据少、IO 次数多；而 B+ 树只有叶节点存放数据，其余节点只做索引，节点小，IO 次数少。

B+ 树优点：

只有叶节点存数据，其余节点是索引；
所有数据在叶节点且叶节点之间有链指针，遍历叶子即可获得全部数据，支持区间访问（数据库范围查询非常频繁，B 树不支持这种遍历）。

为何不用 AVL 树或红黑树：

磁盘 IO 过于频繁：红黑树/AVL 深度大，磁盘寻道开销大。树的深度决定磁盘查找次数，B 树每个节点可有几十到上千个子女，降低了树高；
实现复杂度：B+ 树相对红黑树更易实现。

数据库设计者利用磁盘预读原理，把节点大小设为一个页，每个节点只需一次 IO 就能完全载入。

6 聚簇索引与非聚簇索引

聚簇索引：对磁盘实际数据重新组织，存储顺序与索引顺序一致。主键默认创建聚簇索引，一张表只允许一个。叶子节点包含主键值、事务 ID、回滚指针和其余列，即叶子节点就是数据节点；
非聚簇索引：叶子节点仍是索引节点，存指向数据块的指针。二级索引 query 到主键后需要回表查询。

7 InnoDB vs MyISAM

维度	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
索引	聚簇索引，表必须有主键	非聚簇索引，可无主键
count(*)	全表扫描	变量直接读取，很快
锁粒度	行锁	表锁
崩溃恢复	支持	不具备
MVCC	支持	不支持

InnoDB 辅助索引叶节点存的是主键值而非行指针，减少数据移动/页面分裂时维护索引的开销，但通过辅助索引查询需要先查主键再回表。因此主键不应过大，否则其他索引也会很大。

选择建议：需要事务选 InnoDB；绝大多数只读查询可选 MyISAM；读写都频繁选 InnoDB；能否接受崩溃后难恢复也要考虑。

8 key 与 index 的区别

key：数据库物理结构，既包含约束（constraint）又包含索引。primary key、unique key、foreign key 都同时是约束和索引；
index：只是辅助查询的结构，不会约束字段行为。分类有前缀索引、全文索引等。

9 最左前缀原则

联合索引（如 name, city）遵循最左前缀：

        
      
select * from user where name=x and city=y;  -- 命中
select * from user where name=xx;            -- 命中
select * from user where city=x and name=y;  -- 命中（优化器调整顺序）
select * from user where city=x;             -- 无法命中

规则：

查询必须从联合索引最左边列开始；
不能跳过某一索引列；
存储引擎不能使用索引中范围条件右边的列（LIKE 是范围查询）。

10 表级锁 vs 行级锁

表级锁：粒度最大，实现简单、资源消耗少、加锁快、不会死锁，但锁冲突概率最高、并发度最低，MyISAM 和 InnoDB 都支持；
行级锁：粒度最小，只锁当前操作行，并发度高，但加锁开销大、可能出现死锁，InnoDB 支持。

11 truncate & drop & delete 区别

truncate 删除表再创建、重置 auto_increment、不返回删除行数、保留分区；DELETE 逐条删除、不重置自增、返回删除行数；
drop 直接删表（结构+约束+触发器+索引）；
相同点：都删除表内数据；
不同点：drop/truncate 是 DDL（自动提交、不可回滚、不触发 trigger）；delete 是 DML（放入 rollback segment、可回滚、触发 trigger）；
速度：drop > truncate > delete；
使用：删部分数据用 delete（带 where）；删表用 drop；保留表清空数据且与事务无关用 truncate。

12 当前读 vs 快照读

快照读（snapshot read）：简单 select 就是快照读（不含 select ... lock in share mode、select ... for update）。RR 级别下通过 MVCC + undo log 实现；
当前读（current read）：select ... for update、select ... lock in share mode、insert、update、delete。通过 record lock + gap lock 实现。

RR 下快照读只能部分防止幻读（读到 undo 旧版本），但当前读通过加锁完全避免不可重复读和幻读。

13 综合 SQL 练习：部门与员工统计

        
      
-- 创建部门表
CREATE TABLE IF NOT EXISTS dept(
    id INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '部门编号（主键）',
    department VARCHAR(50) COMMENT '部门名称'
);

-- 创建员工表
CREATE TABLE IF NOT EXISTS employee(
    id INT(11) NOT NULL PRIMARY KEY COMMENT '员工编号（主键）',
    last_name VARCHAR(50) COMMENT '姓名',
    dept_id INT(11) NOT NULL COMMENT '部门编号'
);

-- 添加数据
INSERT INTO dept(department) VALUES ('销售部'),('研发部'),('产品部');
INSERT INTO employee(last_name, dept_id) VALUES
('jack',1),('lucy',1),('andy',1),('mike',2),('smith',2),
('peter',3),('lily',3),('elsa',3),('victory',3);

-- 统计每个部门人数并排序
SELECT dept.department, COUNT(*) AS num
FROM employee JOIN dept ON dept.id=employee.dept_id
GROUP BY dept_id ORDER BY num DESC;

-- 给部门表增加人数字段
ALTER TABLE dept ADD num INT(4);

-- 用视图统计并更新 dept.num
CREATE VIEW tmp AS SELECT dept_id, COUNT(*) AS num
FROM employee INNER JOIN dept ON dept.id=employee.dept_id
GROUP BY employee.dept_id ORDER BY num DESC;

UPDATE dept d SET num = (SELECT num FROM tmp WHERE d.id = dept_id);

SELECT * FROM dept;   -- 查看是否更新
DROP VIEW tmp;        -- 删除视图

阅读 — · 全站 —