MySQL 完整学习总结

MySQL
开源关系型数据库（RDBMS），采用 C/S 架构；数据存储在二维表，支持 SQL 结构化查询语言。

二、MySQL 数据类型

二进制浮点无法精确表示小数；必须 DECIMAL(总长度,小数位数)

2. 字符串类型

MySQL5.7 中 varchar 最大 65535 字节，utf8 一个字符占 3 字节，不是随便写 n=10000

3.日期时间类型
DATE 3 字节：yyyy-MM-dd 只存日期
TIME 3 字节：HH:mm:ss 时间
DATETIME 8 字节：日期 + 时间，不受时区影响
TIMESTAMP 4 字节：时间戳，自动转时区；范围较小（1970~2038）【2038 时间炸弹】

新项目优先 DATETIME，避开 timestamp2038 上限
自动更新时间：update_time DATETIME ON UPDATE CURRENT_TIMESTAMP
4. 枚举 & 集合
ENUM (’ 男 ‘,’ 女 ')：单选
SET (‘a’,‘b’)：多选，极少使用

三、字段约束（建表必备）
PRIMARY KEY 主键：唯一 + 非空，一张表只能一个主键（可以联合主键）
UNIQUE 唯一约束：值不能重复，允许一个 null
NOT NULL 非空
DEFAULT 默认值
AUTO_INCREMENT 自增（仅整数主键）
FOREIGN KEY 外键：维护表与表关系

生产建议：InnoDB 虽然支持外键，但互联网项目大多禁用外键。
外键会降低并发性能，死锁风险提升，业务代码手动控制关联完整性

四、DDL 库、表实操命令

— 创建数据库
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4;
— utf8mb4 完整utf8，支持emoji；普通utf8不支持表情

— 创建表示例
CREATE TABLE `user`(
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
username VARCHAR(50) NOT NULL COMMENT '用户名',
phone CHAR(11) UNIQUE COMMENT '手机号',
money DECIMAL(12,2) DEFAULT 0 COMMENT '余额',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

— 修改表
ALTER TABLE user ADD age TINYINT;
ALTER TABLE user MODIFY username VARCHAR(80);
ALTER TABLE user DROP COLUMN age;

— 删除
DROP TABLE IF EXISTS user;
TRUNCATE TABLE user; — 清空表数据，重置自增；DDL语句，无法回滚

DELETE：DML，逐行删除，可以事务回滚，不会重置自增
TRUNCATE：DDL，销毁重建表，速度极快，不能回滚

五、DML 增删改查

–插入
INSERT INTO user(username,phone) VALUES('张','13800138000');
–查询
SELECT id,username FROM user WHERE id=1;
–修改（必须加WHERE，不然会全表更新）
UPDATE user SET money=100 WHERE id=1;
–删除
DELETE FROM user WHERE id=1;

查询

–条件、模糊查询
SELECT * FROM user WHERE username LIKE '张%';
–分页 LIMIT offset,size
SELECT * FROM user LIMIT 0,10;

–聚合函数：COUNT SUM MAX MIN AVG
–COUNT(*) 统计所有行；COUNT(字段)忽略NULL值
SELECT COUNT(*) FROM user;

–分组 GROUP BY + HAVING
–WHERE过滤原始数据，HAVING过滤分组后的结果
SELECT age,COUNT(*) FROM user GROUP BY age HAVING COUNT(*)>2;

–多表连接【重】
–INNER JOIN 内连接：两边匹配的数据才出现
–LEFT JOIN 左连接：左表全部保留，右表匹配不到填充null
–RIGHT JOIN 右连接
SELECT u.username,o.order_id
FROM `user` u
LEFT JOIN `order` o ON u.id=o.user_id;

六、存储引擎

执行 SHOW ENGINES; 查看支持引擎

七、索引
一种有序数据结构（InnoDB 默认 B + 树），相当于书籍目录，加速查询；

代价：占用磁盘、降低插入 / 更新 / 删除速度（需要维护索引）。

InnoDB B + 树索引原理
B + 树：只有叶子节点存储真实行数据；非叶子节点只存索引键，用于路由
所有叶子节点通过双向链表相连，**范围查询（> < between、like 前缀匹配）**效率极高
对比 B 树：B 树每个节点都带数据，IO 次数更多
索引分类

二级索引存储的不是完整数据，存主键值
. 回表查询：通过二级索引拿到主键，再去聚簇索引查找完整数据（性能损耗）

–生效
where a=?
where a=? and b=?
where a=? and b=? and c=?
–失效，跳过最左a
where b=?
where b=? and c=?

范围条件后面的索引失效 where a=1 and b>10 and c=99 b 是范围，c 字段索引不生效

例：索引 idx_name_phone (name,phone) select name,phone from user where
name=‘张’ → 覆盖索引

索引失效场景
1.字段上使用函数：
WHERE YEAR(create_time)=2026
2.隐式类型转换：字符串 varchar 字段使用数字查询 where phone=138000
3.使用 != <> NOT IN IS NOT NULL（大部分情况失效）
4.like、 %关键词 、通配符放前面
5.or 左右字段没有同时建立索引
6.违反最左匹配原则

看字段： type：性能等级 system > const > eq_ref > ref > range > index > ALL ALL
代表全表扫描，必须优化 key：实际使用索引 Extra：Using filesort（文件排序，无索引排序）、Using
temporary（临时表，严重损耗）

八、事务 Transaction
一组 SQL 语句，要么全部执行成功，要么全部失败回滚，保证业务原子性（转账：A 扣钱，B 加钱必须同时成功）
2. ACID 四大特性

核心：undo 日志（数据历史版本）+ 行隐藏字段（trx_id，roll_pointer）+ Read View

两种读：
快照读（普通 select）：走 MVCC，读取历史快照，不加行锁
当前读（select … for update /update/delete）：读取最新数据，加行锁

RR 级别下 MVCC 机制解决幻读；仅快照读生效；当前读依旧存在幻读问题，InnoDB 依靠Gap Lock 间隙锁解决幻读

九、InnoDB 锁机制
锁分类

RC 隔离级别没有间隙锁 间隙锁会带来死锁风险

死锁
多个事务互相持有对方需要的锁，无限等待
触发条件：事务获取锁顺序不一致 + 行锁 + 间隙锁
排查：SHOW ENGINE INNODB STATUS;
规避方案：统一所有事务加锁顺序，缩短事务执行时间

十、三大日志（底层原理）
redo log（重做日志）：崩溃恢复，保证持久性。先写日志，再刷磁盘（WAL 预写日志机制）
undo log（回滚日志）：事务回滚、MVCC 历史数据版本
binlog（二进制日志）：服务层日志，记录所有修改数据 SQL；用于主从复制、数据恢复
redo 属于 InnoDB 引擎层；binlog 属于 MySQL 服务层
WAL 机制（Write Ahead Log）
修改数据不立刻刷新磁盘页，先写入 redo log；提升性能，防止宕机丢失数据

拓展进阶方向（后续深入）
主从复制、读写分离、分库分表、慢查询日志调优、MySQL 监控、缓存与数据库一致性

General SQL Optimization Solutions
1.禁止 SELECT *，只查询需要字段，减少网络 + 内存消耗；
2. 避免大事务，拆分长事务（减少锁持有时间，降低死锁）；
3.分页深度优化：limit 100000,10 极慢，改用主键过滤 where id>100000 limit 10
4. 大表不要使用 JOIN 超大关联，考虑业务拆分 ；
5.控制单表数据量，建议千万级别考虑分库分表；
6.合理选择字符集 utf8mb4；