简介一份覆盖 MySQL 从安装启动到高级应用的成体系学习笔记面向希望系统掌握数据库操作、备考或从事后端开发的数据从业者。内容以 1000 行精心整理的命令与要点为主线从 Windows 服务启动、连接与断开服务器、库表增删改查到存储引擎选型、字符集与排序规则、临时表、视图、触发器、存储过程、事务与索引优化等高级特性均有涉及适合按章节逐段对照练习也便于面试前快速回顾。资源为单个 docx 文档约 49KB体积小巧却信息密集便于阅读、检索与二次编辑可快速定位日常开发或学习所需知识点。目前已有 477 人学习适合初学者打基础也适合有经验者查漏补缺。笔记对每条命令都配有简明注释例如建表时的字段属性、表选项、修改表结构、重命名等均有实例说明读者可据此搭建自己的 MySQL 速查手册。1. 为什么还缺一份 1000 行的 MySQL 学习笔记学 MySQL 最大的困境从来不是资料少而是资料太多。官方手册按章节铺开培训课程动辄几十个小时等到真要写一条 UPDATE 时能记住的往往只剩 SELECT *。1000 行学习笔记这个体量恰好卡在“手册”和“小抄”之间它装不下内核源码级别的实现却能覆盖日常开发九成会用到的建库、CRUD、索引、事务与备份操作这也正是“史上最全珍藏版”这类标题真正有资格承载的内容密度。笔记的价值不在堆页数而在每一行都能对上具体场景。连接器、优化器、存储引擎这三层架构如何影响你写 SQL组合索引为什么有时完全失效InnoDB 默认的 REPEATABLE READ 下为什么还能读到旧版本数据——这些才是值得反复看的部分而不是把 CREATE TABLE 抄十遍。这份笔记按我自己的整理顺序展开先高频 SQL 与执行顺序再索引和执行计划然后事务与 MVCC最后落到安装排查和备份这些终端操作。适合已经写过 SELECT、但没把 MySQL 系统串成一条线的后端工程师也适合面试前想快速把知识点连成链的人。每条规则后面都给了可复现的命令和参数可以直接抄走改着用。2. 从建库到查数MySQL 高频 SQL 怎么记才不白背2.1 建库建表的第一行决定后面三年utf8mb4 与 InnoDB笔记里如果把建表当填空题只记字段不记理由三个月后翻出来照样不敢改。字符集和存储引擎这两项我一般会写清楚选择依据后面所有 SQL 行为都建立在它们之上。CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE shop; CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, user_name VARCHAR(64) NOT NULL COMMENT 登录名, phone CHAR(11) DEFAULT NULL COMMENT 手机号, age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 年龄, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;说明utf8mb4 是必选项而不是可选项MySQL 8.0 起默认就是这个老库迁移时要确认所有表的 charset 已改否则 emoji 和生僻字直接报 Incorrect string value。CHAR(11) 存手机号这类定长数据避免了 VARCHAR 的变长头和页内碎片。updated_at 依赖 ON UPDATE 自动刷新程序里少写一行也能保证任何途径的 UPDATE 都留下时间戳。字段类型上有个容易翻车的细节TINYINT 的范围只有 -128 到 127unsigned 上限 255。笔记里若出现 age 1 这类自增更新先确认列宽线上出现过一次 TINYINT 溢出报错把整个事务回滚的案例。id 用 BIGINT UNSIGNED 也是同样的考虑INT 自增在千万行规模下真的会顶到天花板。注意不要为了省那几 MB 把状态字段设成 ENUM后续加枚举值要 ALTER TABLE业务上线的速度会被一次 DDL 卡住。TINYINT 注释表是运维更认可的写法。2.2 UPDATE 语法与子查询ERROR 1093 和“忘写 WHERE”两个坑高频 SQL 里风险最高的不是 SELECT 而是 UPDATE。笔记中值得单独做一节的一是同表子查询更新报错二是 WHERE 条件缺失三是整数溢出。这三个问题我都踩过。-- 反例MySQL 不允许 UPDATE 的目标表出现在子查询的 FROM 里 UPDATE t_user SET age age 1 WHERE id IN (SELECT id FROM t_user WHERE status 0); -- ERROR 1093: You cant specify target table t_user for update in FROM clause -- 解法一把子查询包成派生表让 MySQL 先物化 UPDATE t_user SET age age 1 WHERE id IN ( SELECT id FROM (SELECT id FROM t_user WHERE status 0) tmp ); -- 解法二JOIN 更新语义更直观 UPDATE t_user u JOIN (SELECT id FROM t_user WHERE status 0) tmp ON u.id tmp.id SET u.age u.age 1;说明ERROR 1093 的根源是 MySQL 无法确定“读了同一张表”是否安全。包一层派生表之后子查询结果被物化成临时表更新目标就和数据源区分开了。JOIN 更新表达的是同一件事关联键用主键时性能没有问题。多表 UPDATE 时SET 里写别名列u.ageWHERE 里的条件也建议全部带表别名避免字段歧义。update 语法里还有两个边界第一UPDATE 支持 LIMIT 和 ORDER BY低风险的批处理可以写成 UPDATE t_user SET status 0 WHERE … LIMIT 200配合循环逐批提交避免一次锁太多行第二想给某列设置默认值 0别在 UPDATE 里写死用 ALTER TABLE t_user ALTER COLUMN age SET DEFAULT 0 让模型层去兜底。生产环境的常见做法是先 SELECT COUNT(*) 确认影响行数再执行 UPDATE这是 1000 行笔记里最便宜也最值钱的一条。2.3 SELECT 执行顺序与 JOIN 语义排序、去重和 ON/WHERE 的边界SELECT 写得流畅的前提是脑子里有一张执行顺序表。顺序决定了很多“为什么这里报错”顺序关键字作用1FROM / JOIN确定数据源并生成中间结果2ON对 JOIN 结果做匹配过滤3WHERE行级过滤4GROUP BY按列分组5HAVING分组后的过滤6SELECT投影列与表达式7DISTINCT去掉重复行8ORDER BY排序9LIMIT限定行数由这张表能推出三条直接可用的规则WHERE 里不能用 SELECT 别名因为 WHERE 执行在第 6 步之前ORDER BY 反而可以用别名它执行在 SELECT 之后HAVING 在没有 GROUP BY 时配合聚合函数才有意义严格模式下散列在 SELECT 里会直接报错。排序和去重是被问得最多的两个点。ORDER BY 走索引时是顺序读否则就是 Using filesort代价是数据先装进排序缓冲区所以 order by 的列尽量放进组合索引且排序方向保持一致。至于“OR 能去重吗”这类问题答案是去重只认 DISTINCT 和 GROUP BYOR 在 JOIN 条件里出现时连接条件变成一个不等式集既难走索引又容易和 ON 另一侧的匹配组合出重复行。常见做法是拆成两条查询用 UNION 合并让每条分支都吃到自己的索引。JOIN 的语义区分是这样的-- 查所有用户及其 status1 的订单无订单的用户也保留 SELECT u.id, o.order_no FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND o.status 1; -- 只查有有效订单的用户WHERE 把 NULL 行滤掉了 SELECT u.id, o.order_no FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE o.status 1;说明LEFT JOIN 时第二个查询的结果等价于 INNER JOIN因为 WHERE 在 JOIN 完成后执行把未匹配产生的 NULL 行过滤掉了。第一条语句才是真正意义上的“左表全保留右表按需匹配”。这就是 JOIN 含义里最核心、也最容易在面试里一问就倒的边界。深分页的 LIMIT 100000, 20 这种写法越翻越慢常见优化是记录上一页最大 id用 WHERE id ? ORDER BY id LIMIT 20 代替偏移量。3. MySQL 索引与执行计划笔记里真正值钱的 200 行3.1 索引为什么快B 树、聚簇索引与回表索引笔记不需要背 B 树的插入分裂细节但要记结论B 树非叶节点只存键和指针一层能装上千个键三到四层就能覆盖千万行查询次数等于树高和行数几乎无关。这就是索引快的基本盘。InnoDB 的聚簇索引把主键直接当作索引文件叶子节点里就是整行数据二级索引的叶子节点存的是主键值。所以用二级索引查一条 SELECT *要先扫二级索引拿到主键再回聚簇索引取整行这个动作叫回表。回表是随机读行数一多成本就上来。反直觉的结论是当查询要读取的行数超过全表的 15% 到 20%经验值优化器常常放弃索引改走全表扫描因为顺序读比大量随机读更便宜。理解这一点才能理解 mysql 执行计划里出现的 ALL 不一定都是坏事。3.2 创建索引的语法与最左前缀原则组合索引怎么设计-- 在已有表上加索引 ALTER TABLE t_user ADD INDEX idx_user_name (user_name); -- 组合索引条件里最常用的是 age status 这个组合 CREATE INDEX idx_age_status ON t_user (age, status); -- 删除索引DDL 会锁表低峰期执行 DROP INDEX idx_age_status ON t_user; -- 查看表上有哪些索引 SHOW INDEX FROM t_user;说明ADD INDEX 和 CREATE INDEX 作用相同前者适合建表后补后者适合脚本里语义化创建。组合索引的列顺序是设计重点字段顺序决定了它能被哪些查询复用查询条件是否走 idx_age_status说明WHERE age 20走命中最左列WHERE age 20 AND status 1走完全命中WHERE status 1不走跳过最左列WHERE age 20走range 扫描WHERE status 1 AND age 20走优化器会做等值重排最左前缀原则是三句话条件里的列必须从索引最左列开始连续匹配范围列、、between后面的列不再走索引等值列放前面范围列放后面。设计组合索引的常见做法是把区分度高的列放在前面并优先覆盖“等值 排序”的组合比如 (age, created_at) 既过滤又免掉 filesort。还有几个索引失效的高频场景笔记里必须记索引列上做函数运算WHERE DATE(created_at) 2025-06-01不走索引应改写成 created_at 2025-06-01 AND created_at 2025-06-02 的范围条件隐式类型转换phone 是 CHAR却拿数字去比较会让索引失效LIKE %abc 前置通配符用不上索引abc% 可以OR 条件要两边的列都有索引且优化器用 index merge 才可能走最稳妥是拆 UNION。3.3 用 EXPLAIN 读执行计划type、key、rows 三个信号索引建得对不对别靠猜用 EXPLAIN。这一节是 mysql 性能调优里投入产出比最高的部分。EXPLAIN SELECT user_name, age, status FROM t_user WHERE age 20 AND status 1 ORDER BY created_at;执行计划输出是一个表我一般只先看四列列含义重点关注type访问类型至少要 range 及以上key实际用到的索引是否为预期索引rows估算扫描行数与真实行数对比Extra附加信息出现 filesort、temporary 要警惕type 从好到坏排列system/const主键或唯一键等值、eq_ref被驱动表按主键连接、ref普通索引等值、range索引范围扫描、index扫全索引树、ALL全表扫描。线上慢 SQL 的排查路径是固定的先看 type 是否掉到 index 或 ALL再看 key 是不是设计中的那个索引最后看 Extra 里有没有 Using filesort 和 Using temporary——这两个出现任何一个都说明排序或分组没有吃到索引。rows 是估算值不是实际值但它能暴露统计信息是否过期。如果 EXPLAIN 估算一万行实际查出来只有几十行多半是表统计信息没更新执行 ANALYZE TABLE t_user 后再看。补充一点连接池只能省下建连和握手开销SQL 本身慢开多少连接池都救不回来慢 SQL 的定位要靠慢查询日志long_query_time 设为 1 秒加 EXPLAIN 逐条过这才是 mysql 性能调优的日常节奏。提示EXPLAIN 不会真正执行 SQL它只生成优化器认为可行的执行路径。改完索引之后记得用 ANALYZE TABLE 刷新统计信息否则 rows 列会一直参考旧数据。4. MySQL 事务隔离与 MVCC锁与快照的博弈4.1 四种隔离级别分别在防什么脏读、不可重复读与幻读事务笔记的第一张表一定是隔离级别。MySQL 的可重复读RR是默认值但很多人说不出它和读已提交RC到底差在哪一列参数上隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不会可能可能REPEATABLE READ默认不会不会快照读下基本不会SERIALIZABLE不会不会不会脏读是读到未提交的数据不可重复读是同一个事务里两次 SELECT 读到不同行幻读是同一个事务里两次范围查询第二次多出或少了行。MySQL 的 RR 靠 MVCC 和 next-key 锁把幻读压到了极小范围所以默认隔离级别可以直接用。-- 查看当前隔离级别 SELECT transaction_isolation; -- 会话级临时切换只影响当前连接 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 全局级切换8.0 里对已有连接不生效 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;说明互联网公司线上很多用 RC原因是 RC 下间隙锁大幅减少并发度更高binlog 配合 row 格式就够了RR 默认用于对一致性要求高、允许锁开销的业务。SET GLOBAL 只对新连接生效DBA 通常会把参数写进 my.cnf 再滚动重启笔记里要区分 session 和 global 两个作用域。4.2 MVCC 的隐藏列与 Read View为什么 RR 能重复读MVCC 是 MySQL 实现读写不互斥的核心理念。InnoDB 在每行数据后面加了两列DB_TRX_ID 记录最后修改它的事务 idDB_ROLL_PTR 指向上一个版本的 undo log 位置。行数据因此形成一条版本链。读数据时事务会生成一个 Read View里面记录了当前活跃事务列表和最小/最大事务 id。判断规则可以简化成一句数据行的 trx_id 小于 Read View 中最早活跃事务 id或不在活跃列表内且小于最大 id就可见否则沿版本链往回找。RC 和 RR 的本质差别只有一个RC 每条语句生成新的 Read ViewRR 在事务第一次读时生成并复用。-- 事务 A START TRANSACTION; SELECT * FROM t_user WHERE id 1; -- 第一次读生成 Read View -- 此时事务 B 更新 id1 并提交 SELECT * FROM t_user WHERE id 1; -- 仍读到第一次的值 COMMIT;说明在 RR 下事务 A 的两次 SELECT 结果完全一致这就是“可重复读”的机制根源把隔离级别换成 RC第二次 SELECT 会读到事务 B 提交后的新值。UPDATE、DELETE、SELECT ... FOR UPDATE 这类当前读不走版本链它们读最新版本并对行加锁这也是为什么“明明开了事务还读不到别人未提交的数据”这类问题本质上要先分清快照读和当前读。4.3 行锁、间隙锁与死锁排查当前读和锁范围加了 for update 的当前读锁的范围取决于条件和索引-- 会话 A 开事务锁住主键 id8 这一行 START TRANSACTION; SELECT * FROM t_user WHERE id 8 FOR UPDATE; -- 会话 B 对同一行 UPDATE 会阻塞直到 A 提交 UPDATE t_user SET status 0 WHERE id 8; -- 查当前事务和锁等待 SELECT * FROM information_schema.innodb_trx\G SELECT * FROM information_schema.innodb_lock_waits\G -- 死锁发生后的第一现场 SHOW ENGINE INNODB STATUS\G说明主键等值命中时是记录锁只锁一行范围条件age BETWEEN 10 AND 20在 RR 下会加间隙锁或 next-key 锁锁住索引区间防止其他事务插入这是 RR 抑制幻读的重要手段。最容易引发线上事故的写法是 UPDATE 或 DELETE 的 WHERE 列没有索引——InnoDB 找不到精确的索引区间只能给大量记录加锁RR 下还会升级成大面积间隙锁表现为“一句话把整张表堵死”。死锁的常规应对是防而不是救多个事务按相同顺序访问资源事务体尽量短把无关查询挪到事务外每个 SQL 都尽量走索引缩小锁范围。真正的死锁发生概率低InnoDB 会自动回滚代价较小的事务接口侧报错一般是 1213接到这个错误码做一次重试即可笔记里应该把“死锁不一定都是 bug”这个观念写进去。5. 把 MySQL 笔记落到终端安装配置、general_log 与备份命令笔记最后留一节终端命令是因为装上 MySQL 却连不上、找不到初始密码、不知道程序发的哪条 SQL 慢前面所有知识点都用不上。这一节我把最常被检索的几类问题压缩成了可以直接执行的命令。5.1 安装配置后的第一件事初始密码与端口号# RPM 包方式安装的 MySQL 8.0初始随机密码在日志里 sudo grep temporary password /var/log/mysqld.log # 确认服务与端口 3306 的监听状态 sudo systemctl status mysqld ss -lntp | grep 3306 # 登录连通性测试 mysqladmin -uroot -p ping说明从官网下载安装包时注意选 MySQL Community Server 8.0而不是最新的 innovation 版本后者迭代快生产环境兼容性需要更多验证。Docker 方式安装可以省去初始化步骤docker run --name mysql8 -e MYSQL_ROOT_PASSWORD你的密码 -p 3306:3306 -d mysql:8.0但要把数据目录用 -v 挂到宿主机容器删掉数据还在。用 Workbench 或 Navicat 连接时主机名填 127.0.0.1、端口 3306、用户名 root密码用安装时设置的连不上先 ping 3306 而不是怀疑密码。5.2 打开 general_log看清每一条真实 SQL连接池掩盖了大量行为程序实际发出去的 SQL 和心里想写的经常不一样。把 general.log 打开一次一切现形SET GLOBAL general_log ON; SET GLOBAL general_log_file /var/log/mysql/general.log; -- 定位完问题立刻关闭生产环境不建议开启 SET GLOBAL general_log OFF;说明general_log 记录所有到达服务器的 SQL包括成功的和失败的排查“为什么某条语句耗时高但慢日志里没有”很有效。打开后另开一个终端执行 tail -f /var/log/mysql/general.log 就能实时观察。存储过程这类写法笔记里只需要抄两处声明时用 DELIMITER $$ 临时改结束符过程体内保持 CREATE PROCEDURE 开头、BEGIN...END 收尾即可。5.3 备份与恢复一条 mysqldump 和三个必带参数# 单事务一致备份InnoDB 表不锁业务写 mysqldump -uroot -p --single-transaction \ --default-character-setutf8mb4 shop shop_$(date %F).sql # 恢复 mysql -uroot -p shop shop_2025-06-01.sql说明--single-transaction 用一致性快照实现备份不加它会退化成 LOCK TABLES备份期间业务写入全部阻塞--default-character-setutf8mb4 保证 emoji 和中文注释不变成乱码备份文件名带日期是防止覆盖上一次备份。恢复完的测试库不要急着删把第 3 章的 EXPLAIN 原样跑一遍key 列和 Extra 列会直接告诉你索引笔记里哪几行值得再背一轮。本文还有配套的精品资源点击获取
