MySQL 核心解析：索引、设计、事务与视图

2. 实际使用中的注意点

索引不是越多越好。虽然查得快了，但 INSERT、UPDATE、DELETE 的开销会变大，因为每次写操作都要同步更新索引。

• 适合建索引：查询频繁的字段、WHERE 条件常用的字段、JOIN 关联的字段。
• 不适合建索引：数据量小的表、频繁更新的字段、重复率高的字段（比如性别）。
• 查看索引信息：SHOW INDEX FROM user;

二、数据库设计：遵循范式，兼顾性能

设计的核心是在保证数据完整性的同时减少冗余，还得考虑查询性能。业界主流是'三大范式'，但实际开发中得灵活调整，别过度设计。

1. 三大范式核心原则

第一范式（原子性）：每一列的值必须是不可拆分的原子值。比如'地址'字段，如果业务需要按省份、城市查询，就不能存成'安徽省合肥市庐阳区 XX 路'，得分拆成 province、city、detail_address 三个字段。

第二范式（唯一性）：在 1NF 基础上，确保表中的每一列都和主键完全相关。针对联合主键尤其要注意。比如订单表以'订单编号 + 商品编号'为联合主键，就不能存'商品名称、商品单价'，这些只和商品编号有关，应该拆分出商品表，通过外键关联。

第三范式（直接相关性）：在 2NF 基础上，确保每一列都和主键直接相关，而非间接相关。比如订单表只需存'用户 ID'，别直接存'用户名、用户手机号'，这些属于用户表的属性。

2. 多表关系设计

实际业务里，表与表的关系主要分三种：

• 一对多（如部门和员工）：在'多'的一方（员工表）加外键，指向'一'的一方（部门表）的主键。
• 多对多（如学生和课程）：需创建中间表，包含两个外键，分别指向两张主表的主键。
• 一对一（如人和身份证）：在任意一方添加唯一外键，指向另一方的主键。

3. 设计权衡：范式与冗余

严格遵循第三范式能减少冗余，但会导致多表关联查询，降低性能。实际开发中可适当'反范式'：比如在订单表中冗余'用户名'，避免每次查询都关联用户表，用空间换时间。

三、事务：保证数据一致性的守护神

事务是一组不可分割的操作，要么全成功，要么全失败。转账、下单这类关键业务，全靠它来兜底。

1. 事务的四大特性（ACID）

• 原子性（Atomicity）：所有操作要么全成，要么全回滚，没有中间状态。
• 一致性（Consistency）：执行前后，数据库完整性约束不变。比如转账前后，双方总金额不变。
• 隔离性（Isolation）：多个并发事务互不干扰。
• 持久性（Durability）：提交后永久生效，即使崩溃也不会丢。

2. 事务的基本操作

MySQL 默认自动提交（一条 DML 就是一个事务），手动控制通常这样写：

-- 创建账户表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    balance DOUBLE
);

INSERT INTO account(name, balance) VALUES ('张三', 1000), ('李四', 1000);

-- 开启事务
START TRANSACTION;

-- 张三给李四转账 500 元
UPDATE account SET balance = balance - 500 WHERE name = '张三';
UPDATE account SET balance = balance + 500 WHERE name = '李四';

-- 无异常则提交
COMMIT;
-- 有异常则回滚
-- ROLLBACK;

3. 事务隔离级别

并发操作时可能出现脏读、不可重复读、幻读等问题，靠隔离级别解决：

• READ UNCOMMITTED：最低级，允许读未提交数据，可能脏读。
• READ COMMITTED：避免脏读，只能读已提交数据（Oracle 默认）。
• REPEATABLE READ：避免脏读、不可重复读（MySQL 默认）。
• SERIALIZABLE：最高级，避免所有问题，但性能最差，相当于单线程。

查看或设置隔离级别：

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

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

四、视图：简化查询，保障安全的虚拟表

视图是基于 SQL 查询结果的虚拟表，不存实际数据，只存逻辑。它能简化复杂查询，还能隐藏敏感数据。

1. 视图的创建与使用

-- 创建视图：查询员工姓名、部门名称
CREATE VIEW v_emp_dept AS 
SELECT emp.name, dept.name AS dept_name 
FROM emp JOIN dept ON emp.dept_id = dept.id;

-- 查询视图（和普通表一样）
SELECT * FROM v_emp_dept;

-- 修改视图
ALTER VIEW v_emp_dept AS 
SELECT emp.name, emp.salary, dept.name AS dept_name 
FROM emp JOIN dept ON emp.dept_id = dept.id;

-- 删除视图
DROP VIEW v_emp_dept;

2. 视图的核心价值

• 简化复杂查询：把多表关联、聚合等逻辑封装好，用户查视图就行。
• 数据安全：隐藏密码、手机号等敏感字段，只暴露必要数据。
• 数据独立：源表结构变了，改视图就能适配，不影响前端逻辑。

3. 注意事项

视图不是万能的，以下情况不可更新（INSERT/UPDATE/DELETE）：

• 包含聚合函数（SUM/COUNT/AVG）、DISTINCT、GROUP BY、HAVING、LIMIT；
• 包含 UNION、子查询；
• 多表关联的视图。

实际开发中，视图主要用于查询，不建议通过视图修改数据。

总结

MySQL 的索引、设计、事务和视图是相辅相成的：

• 索引是性能优化的核心，得结合场景合理建；
• 设计要遵循范式，同时兼顾性能，灵活取舍冗余；
• 事务是数据一致性的保障，ACID 特性和隔离级别得掌握；
• 视图是简化查询、保障安全的工具，适合封装复杂逻辑。

实际开发中，把这些知识点结合起来用，既能保证数据的完整和安全，又能让数据库跑出最优性能。