尧图网页设计 
1. 开课前的准备这堂课到底讲什么3月16日这堂MySQL课内容密度比想象中大得多。虽然课程标题只写了“MySQL上课笔记”但实际上从安装环境开始一路讲到索引优化、存储过程、触发器还把日常开发里经常踩的报错都过了一遍。用一句话概括这是一堂从“能跑起来”到“跑得明白”的MySQL入门加进阶课。如果你是刚接触数据库的初学者这堂课能帮你把MySQL的安装、配置、常用SQL命令、索引原理、执行计划这些零散知识点串成一条线。如果你已经写过一些SQL但对存储过程、触发器、子查询更新、执行计划这些内容知其然不知其所以然这堂课同样可以帮你补上“为什么”的那一块。MySQL的学习有一个特点就是“会写SQL”和“懂MySQL”是完全不同的两个阶段——前者只需要语法后者需要理解数据是怎么存的、SQL是怎么执行的、索引是怎么起作用的。这堂课明显是往第二个阶段打的。课程整体分成了六块环境安装、SQL基础、进阶查询、索引与性能、存储过程与触发器、报错排查。这个顺序本身就值得说一句它按照“从搭建到使用从使用到理解从理解到调优”的逻辑递进比直接上来就背SQL语法要高效得多。下面我按课堂实际推进的顺序把笔记整理出来。2. MySQL安装与环境配置2.1 版本选择8.0还是8.4不要盲目追新课堂第一件事不是装软件而是选版本。这里老师花了大概十分钟讲版本选择原因很现实——版本没选对后面全是坑。目前主流的MySQL版本有两个分支8.0系列和8.4系列也就是所谓的LTS长期支持版本。实际开发中8.0依然是市场占有率最高的国内大多数企业的生产环境都跑在8.0上。8.4属于新出的长期支持版官方会提供更久的安全更新但新的版本带来的兼容性问题也很明显。举个例子这次热词里有一条“django.db.utils.notsupportederror: mysql 8.4 or later is required (found 8.0)”说明Django这类框架已经适配了8.4但反过来很多老项目如果直接升到8.4反而会报各种找不到驱动、认证方式不支持的问题。我的建议是个人学习和新项目选8.0.43或者8.4都可以但如果是课堂练习、跟着教程走、希望复现网上绝大多数案例老老实实选8.0系列。因为网上90%的教程、问答、课程案例都是基于8.0写的你用8.4去跑可能导致行为不一样。这就好比大家都在踩熟的路你非要走一条新修的岔路看着路更宽但坑在哪里没人告诉过你。下载渠道要注意务必去官网下载不要用第三方站点转存的安装包。官网的下载入口一般会有两个选择一个是MySQL InstallerWindows下带图形界面的安装器一个是ZIP免安装版。热词里出现了“mysql压缩包安装教程8.0.43”和“mysql免安装版本”说明很多人更倾向免安装方式这个后面细说。官网下载地址有国内镜像分流下载速度一般可以接受不用额外找加速手段。2.2 安装方式图形安装器与免安装版取舍Windows下安装MySQL有两条主流路线官方提供的MySQL Installer以及ZIP压缩包免安装版本。MySQL Installer的好处是“省心”它会自动检测依赖环境比如缺少Visual C运行库时会提醒安装也会把MySQL Server、MySQL Shell、MySQL Workbench、Router等组件打包成一个安装流程勾选后一次装齐。但它有个毛病安装到“Start the Server”这一步经常卡住。热词里“安装mysql start the server”就是这个问题。卡住的原因很多最常见的是3306端口被占用——之前装过MySQL或者电脑上有其他数据库服务把端口占了。还有一种是防火墙拦截了安装程序对端口的访问。ZIP免安装版则完全相反它的核心就是一个目录解压后手动改配置、手动初始化数据目录、手动注册Windows服务。看着麻烦但整个过程每一步都是可控的哪里出错一眼就能看到。课堂演示用的是免安装版因为老师希望我们能自己把每个环节走一遍而不是靠安装器一键搞定。免安装版的流程大致如下解压到指定目录后在根目录新建my.ini配置文件至少需要指定basedirMySQL所在目录和datadir数据存储目录然后以管理员身份打开命令行进入bin目录执行mysqld --initialize-insecure进行初始化这个命令会生成一个data目录同时默认root账号密码为空接着执行mysqld --install把MySQL注册成Windows服务再执行net start mysql启动服务。如果不想注册服务每次用mysqld --console前台启动也行适合调试时看日志。2.3 初始密码与第一次登录初始化方式决定了初始密码的状态。用mysqld --initialize生成的密码会随机生成并写入安装目录下的日志文件一般是data目录下的*.err文件搜索“temporary password”关键字就能找到是一串随机字符第一次登录后必须马上改掉。用mysqld --initialize-insecure生成的root用户默认为空密码直接mysql -u root就能登录登录后再执行ALTER USER语句设置密码即可。很多初学者卡在“mysql的初始密码是什么”其实关键不是密码本身而是要知道初始化方式决定了密码在哪里。课堂上的操作是mysql -u root -p # 提示输入密码此处直接回车因为是用--initialize-insecure初始化的 ALTER USER rootlocalhost IDENTIFIED BY 新密码; FLUSH PRIVILEGES;这里有个容易被忽略的细节8.0之后默认认证插件是caching_sha2_password如果你的客户端或者图形工具比较老连接时会报认证失败。遇到这种情况要么升级客户端要么把root账号的认证方式改成mysql_native_password。课堂建议是能用新客户端就用新的不要为兼容老工具把安全等级拉低。2.4 客户端工具命令行、Workbench还是Navicat环境装好后下一个问题是怎么连上去操作。课堂上三种工具都演示了一遍。命令行是必须掌握的不管以后用什么图形工具最终问题排查都要回到命令行。平时练SQL也建议多在命令行里敲养成手写命令的习惯对记忆语法帮助很大。MySQL Workbench是官方出的图形客户端免费、跨平台非常适合做ER图建模、可视化看表结构、跑查询看执行计划。热词里“mysql workbench使用教程”也是高频搜索词说明不少人选择了这个工具入门。Workbench的优点是数据库建模功能强可以从已有的库反向生成ER图对上课做的“学生课程成绩信息实体表设计”这种作业非常方便。Navicat则是很多开发者的主力工具直观、功能全、跨多种数据库但商业版收费。热词里“navigator for mysql 免费版”实际上应该指的是Navicat for MySQL的旧版本或者Lite版Navicat系列目前没有官方免费版网上的“免费版”多数是破解版不建议使用合规和安全都没保证。如果实在不想付费用Workbench或者开源的DBeaver都行。3. SQL基础命令与课堂练习3.1 建库建表一个完整的学生成绩案例环境就绪后课堂进入SQL部分。这节课没有用“员工表”“部门表”这种烂大街案例而是用了“学生课程成绩信息实体表设计”来做练习这个案例特别适合讲清楚MySQL里最关键的表关系。先建数据库和数据表CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE school; CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, student_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT M COMMENT 性别, birth_date DATE COMMENT 出生日期, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(100) NOT NULL, credit DECIMAL(3,1) NOT NULL DEFAULT 2.0 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2), exam_date DATE, FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (course_id) REFERENCES course(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这个设计里有几个考点值得记住字符集选utf8mb4而不是utf8因为utf8在MySQL里最多存3字节支持不了emoji和生僻字utf8mb4才是真正完整的UTF-8主键用自增INT简单高效UUID做主键会带来页分裂问题性能上吃亏外键约束能保证数据完整性但也带来写入性能损耗在互联网高并发场景下通常要靠业务层保证而不用物理外键课堂案例因为有教学目的所以保留了外键。3.2 插入数据与三大基础查询语法建表之后就是往里塞数据。课堂上插入了几条示例数据后就开始做筛选练习。基础查询中“排序”是关键点之一热词里的“mysql排序”对应的就是ORDER BY子句。排序要注意的是默认是升序ASC降序用DESC多个排序字段用逗号分隔从左到右依次作用于前一个字段相同的情况NULL值排序时默认排在最前面升序时和很多其他数据库的行为不一样。举一个课堂上的实际例子-- 查询成绩大于80分的学生按成绩降序成绩相同按学号升序 SELECT student_no, name, score FROM student s JOIN score sc ON s.id sc.student_id WHERE sc.score 80 ORDER BY sc.score DESC, s.student_no ASC LIMIT 10;LIMIT配合ORDER BY是分页的基础第一页就用LIMIT 10第二页LIMIT 10 OFFSET 10。但这个写法在数据量大时会有性能问题深分页时MySQL要扫描并丢弃前N行这个后面讲性能优化时再展开。3.3 去重、别名与条件筛选DISTINCT、AS别名、WHERE条件这属于每天的饭就这些看起来简单但细节很多。DISTINCT去重要记住它作用于后面所有列的组合不是只作用于第一个字段。你要是写SELECT DISTINCT course_id, student_id结果里是按照两列的组合去重不是只按course_id去重。想要单一字段的去重结果别在SELECT后面拼一堆字段。别名这里有一个容易踩的坑WHERE子句中不能直接使用SELECT别名。比如SELECT name AS n FROM student WHERE n 张三这个写法是错的MySQL会报错说不认识n这一列。原因是SQL语句的执行顺序里WHERE是先于SELECT执行的WHERE被执行时别名还没生成。但ORDER BY和HAVING又可以使用别名这是因为它们的执行顺序在SELECT之后。搞清楚执行顺序很多语法问题就迎刃而解了。WHERE条件筛选还可以配合LIKE、IN、BETWEEN这些操作-- 查询姓“张”的学生 SELECT * FROM student WHERE name LIKE 张%; -- 查询出生日期在2000-2005之间的学生 SELECT * FROM student WHERE birth_date BETWEEN 2000-01-01 AND 2005-12-31; -- 查询选了课程1或课程2的学生 SELECT * FROM score WHERE course_id IN (1, 2);LIKE里的通配符%匹配任意多个字符_匹配单个字符。只要搞清楚这两个的区别like就能玩出很多花样。3.4 UPDATE与批量修改的几种方式热词里“mysql批量修改”也是高频问题。课堂讲的批量修改方案按场景不同有三种写法。第一种是逐条UPDATE。适用于只有几条数据、前后SQL逻辑复杂的情况。缺点是每改一条都要一次网络往返量大时效率低。第二种是CASE WHEN配合一条UPDATE实现多行不同值的修改UPDATE score SET score CASE course_id WHEN 1 THEN 90 WHEN 2 THEN 85 WHEN 3 THEN 88 END WHERE course_id IN (1, 2, 3);这一条SQL可以针对不同行设置不同的新值只需要一次更新效率比逐条UPDATE高出一个数量级而且代码简洁。前提是你要修改的数据都能通过条件定位出来。第三种是INSERT ... ON DUPLICATE KEY UPDATE。这种写法特别适合“存在就更新不存在就插入”的业务场景比如批量导入成绩单新记录插入已有记录更新。课堂上示例是把Excel里的成绩单导入INSERT INTO score (student_id, course_id, score, exam_date) VALUES (1, 1, 92, 2026-01-10) ON DUPLICATE KEY UPDATE score VALUES(score), exam_date VALUES(exam_date);ON DUPLICATE KEY UPDATE的执行前提是有唯一索引或主键冲突。这里就必须给score表加一个联合唯一索引UNIQUE(student_id, course_id)保证每个学生每门课只能有一条记录。热词里单独出现“mysql创建索引”正好就是这个问题。3.5 一个经典考点mysql中int5“mysql中int5”这个热词可以拆成两个完全不同的考点。第一个考点是数据读取时对数值型字段做算术运算。比如要查询所有成绩加5分之后的结果写成SELECT score 5 FROM score数据库会把score字段的值都加5后返回但是这个操作不会修改表里的真实数据。要注意的是score如果定义为DECIMAL(5,2)加上5之后如果你只是展示没问题如果想存回去必须通过UPDATE语句。另外一个细节如果字段是INT类型做算术运算时注意溢出INT最大能到21亿多一不小心就变成负数了。第二个考点更隐蔽是字符串拼接和数值相加的区别。在Oracle里“a” || “b”是拼接在SQL Server里是“a” “b”但在MySQL里加号“”永远只做数学加法不做字符串拼接。MySQL的字符串拼接要用CONCAT函数。这个考点是很多培训班面试题里的常见坑SELECT 5 5结果是10而不是55SELECT abc 1结果是1因为MySQL把abc转成数字时转不出来就按0处理。面试官经常拿这种题考候选人有没有真正写过MySQL。4. 进阶查询JOIN、子查询与分组统计4.1 JOIN到底是什么热词里“mysql数据库join含义”能上热搜说明有太多人只用了JOIN却说不清JOIN。课堂专门花了半小时讲这个。JOIN就是把两张表按照某个关联条件横向拼接起来。它的核心是“一对多匹配”当我们用成绩表去点赞学生表时学生的id如果关联了多条成绩记录JOIN的结果就是多行。这有点类似于Excel里的VLOOKUP但功能更强因为它可以同时处理多张表而且可以指定匹配不上的行是否保留。JOIN常见的四种类型用一个比方便于区分记忆。假设有两拨人A是学生表B是成绩表。INNER JOIN就是“两边都认识的人”只返回能在两表中匹配成功的记录。LEFT JOIN就是“A全保留B能配上就配上配不上就填NULL”所以即使某学生还没考过试他在LEFT JOIN结果里还是会有一行只是成绩为NULL。RIGHT JOIN同理是B全保留实际开发中用得少因为把表顺序换一下再用LEFT JOIN就等价了。FULL OUTER JOIN是两边都保留但MySQL原生不支持不过可以用LEFT JOIN UNION RIGHT JOIN的方式达到相同效果。课堂上的例子是统计“哪些学生还没参加过考试”SELECT s.id, s.name FROM student s LEFT JOIN score sc ON s.id sc.student_id WHERE sc.id IS NULL;这个查法非常实用几乎可以说是LEFT JOIN最经典的使用场景右表主键为NULL意味着左表这条记录在右表中没有匹配。考试考这个的频率极高。4.2 子查询与更新子查询的坑子查询就是嵌套在另一个SQL中的SELECT分相关子查询和非相关子查询两种。非相关子查询先执行内层再把结果给外层用相关子查询则是外层每处理一行内层都要重新执行一遍。非相关子查询好理解相关子查询是性能杀手后面讲慢查询时经常会碰到。课堂重点讲了一个热词“mysql中更新子查询”。很多人写UPDATE时喜欢这样写-- 将选课人数超过5人的课程学分上调0.5 UPDATE course SET credit credit 0.5 WHERE id IN (SELECT course_id FROM score GROUP BY course_id HAVING COUNT(*) 5);这个写法在MySQL里直接执行会报错You cant specify target table course for update in FROM clause。原因很简单你不能在同一张表上边查边改。MySQL为了保证数据一致性禁止在UPDATE语句的子查询中直接引用和目标表相同的表。解决办法是套一层派生表把子查询结果包一层UPDATE course SET credit credit 0.5 WHERE id IN ( SELECT course_id FROM ( SELECT course_id, COUNT(*) cnt FROM score GROUP BY course_id HAVING COUNT(*) 5 ) AS tmp );包一层之后内外两层查询的语义上就不算直接在同一层级操作了MySQL就会认为这次更新是安全的。这是一个非常典型的坑面试和实际工作都会碰到。热词里能搜到“mysql中更新子查询”说明这个报错确实拦住了不少人。4.3 聚合函数与GROUP BY的分组逻辑聚合函数是SQL查询中绕不开的重要内容。COUNT、SUM、AVG、MAX、MIN这五个是最基础的。要注意的是COUNT()和COUNT(字段)的区别COUNT()统计的是行数包括NULLCOUNT(字段)统计的是这个字段非NULL的行数。如果字段里有NULL两者结果不同。GROUP BY的分组逻辑是一个容易搞混的地方。SELECT后面能出现的列要么是分组字段本身要么是聚合函数的结果不能随便混搭。比如下面这条SQL在大多数数据库里都是错的-- 错误示例name字段既不在GROUP BY里也不是聚合函数 SELECT course_id, student.name, AVG(score) FROM score JOIN student ON score.student_id student.id GROUP BY course_id;MySQL有一个特殊的行为它默认允许这种查询执行并且返回的name是该组内随机的某一行这其实是MySQL宽松模式留下的坑。如果你开启了ONLY_FULL_GROUP_BY模式上面这条就会直接报错。课堂建议是开发环境开启ONLY_FULL_GROUP_BY逼自己写符合标准的SQL这对以后对接PostgreSQL、SQL Server等数据库有好处。聚合函数的经典场景是“每门课的平均分、最高分、选课人数”SELECT course_id, COUNT(*) AS stu_cnt, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM score GROUP BY course_id HAVING COUNT(*) 3 ORDER BY avg_score DESC;HAVING和WHERE的区别课堂也强调了WHERE是在分组前过滤原始行HAVING是在分组后过滤分组结果。这个区别在写复杂统计SQL时非常关键。想把成绩大于80分的记录分组和分组后平均值大于80的分组两者对同一个字段的限制条件作用阶段完全不同结果也完全不同。5. 索引、执行计划与性能调优5.1 索引的本质排好序的目录热词里“mysql创建索引”出现多次课堂这一节算是对索引的正面讲解。索引本质上就是一种数据结构用于快速定位数据。拿《新华字典》里的偏旁部首目录来类比你要查一个字第一反应不会从第一页翻到最后而是先查目录找到对应的页码然后直接翻到那一页。数据库索引干的就是这件事MySQL里最常见的索引数据结构是BTree。索引的分类要清楚按功能分有普通索引、唯一索引、主键索引、全文索引按结构分主要有BTree索引和Hash索引按列的个数分有单列索引和复合索引。实际开发中用得最多的是普通索引和唯一索引查询条件下如果没有索引就叫做“全表扫描”一张表几十万条数据时一次全表扫描可能要几百毫秒甚至几秒有合适索引时查询可以降到几毫秒差距巨大。创建索引的SQL很简单CREATE INDEX idx_score_course ON score(course_id); CREATE UNIQUE INDEX uk_student_course ON score(student_id, course_id); -- 查看表的索引 SHOW INDEX FROM score; -- 删除索引 DROP INDEX idx_score_course ON score;但“会创建索引”距离“建对索引”差距很大。课堂上给了一个建索引的经验原则一是查询频繁出现在WHERE和JOIN条件的字段才适合建索引不要给每个字段都建索引也会占用空间写入时还要维护索引结构索引多了写入会变慢。二是区分度高的列优先比如gender这种只有两个值的列建了索引也没有区分度数据库可能干脆不走索引。三是复合索引要遵守最左前缀原则索引idx_a_b_c相当于创建了(a)、(a,b)、(a,b,c)三组有效索引查询条件是b和c时用不上这个索引。5.2 执行计划怎么看热词“mysql执行计划”直接用关键字EXPLAIN就能看到MySQL执行一条SQL的详细过程。课堂上用EXPLAIN演示了几条查询这里把关键字段整理一下。EXPLAIN的输出中最值得关注的是这几个列type、key、rows、Extra。type列代表访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。system级别基本上只有表里只有一行数据时出现平时见不到const是根据主键或唯一索引精准定位比如WHERE id 1ref是普通索引等值匹配range是范围扫描比如大于小于、BETWEEN、INindex是索引全扫描还不如rangeALL就是全表扫描这是最差的如果重要查询里出现ALL基本上就要考虑加索引了。key列显示实际用到的索引possible_keys列显示可能用到的索引这两个要对比看。有时候possible_keys里有索引但实际用的是NULL说明优化器认为走索引反而更慢或者因为索引无法正确匹配。rows列是一个估算值表示要扫描多少行这个值越小越好。Extra列常出现的几个值也值得记一下Using filesort说明ORDER BY没能利用索引排序需要额外排序数据量大时有性能风险Using temporary说明查询中用到了临时表常见于GROUP BY和DISTINCT操作Using index说明使用到了覆盖索引查询所需的数据直接从索引里拿到了无需回表这是最快的状态Using where表示在存储引擎返回的记录上做了条件过滤Using join buffer则表示JOIN时使用的是缓存区多以小表驱动大表来避免这种场景。5.3 覆盖索引、最左前缀与常用调优手段执行计划配合索引优化课堂讲了一个经典的覆盖索引案例。假设有这样一条查询SELECT student_id, course_id FROM score WHERE student_id 1;如果表上有一个联合索引UNIQUE(student_id, course_id)那么执行计划中Extra会显示Using index说明MySQL直接从索引叶子节点获取了全部需要的数据不需要回表查原数据。这就是覆盖索引的威力。但如果查询变成了SELECT score这个不在索引里的字段MySQL就得拿着主键ID回到聚簇索引里再找一次也就是回表。回表和覆盖索引是面试高频题理解了这两个概念就能解释很多SQL快慢不同的原因。最左前缀原则也在这一节里再强调了一次。索引idx_a_b_c在查询条件为WHERE b 1 AND c 2时是用不上的除非查询中也包含a字段。这一点在面试中考察比例极高而且实际工作中经常会因为忽略了它建了冗余索引。常见的调优手段课堂列了一个清单避免SELECT *只查需要的字段减少回表和网络传输。分页优化深分页场景下用WHERE id 上一页最大ID LIMIT 10替代LIMIT 10 OFFSET 100000。使用EXPLAIN检查执行计划尤其关注type和rows。开启慢查询日志定位执行时间超过阈值的SQL再逐个分析。利用数据库连接池管理连接不要频繁创建和销毁连接。大批量插入时考虑分批提交每批几百条到几千条不要一条条插入也不要一次性几万条。对只读压力大的业务用主从复制读分离来分散压力。热词里“mysql性能调优”相关的搜索量很高实际上调优第一步绝对不是改配置参数而是先找到慢SQL再分析慢在哪里最后才是调整索引或SQL写法。顺序反了一切努力都是白费。6. 存储过程、触发器与分隔符的坑6.1 存储过程的声明与调用存储过程是课堂上比较靠后的内容但也是“MySQL干货”里区分新手和老手的一道分水岭。简单说存储过程就是把一段SQL逻辑封装成一个可重复调用的程序块类似于编程语言里的函数。热词里“mysql声明存储过程”和“mysql存储过程”指向的是同一件事。一个最简单的无参存储过程如下DELIMITER // CREATE PROCEDURE GetAllStudents() BEGIN SELECT * FROM student; END // DELIMITER ;注意这里的DELIMITER它可能是初学存储过程时最莫名其妙的语法。它的作用是把MySQL客户端的语句分隔符从默认的分号“;”改成其他符号比如“//”。为什么因为存储过程内部会有大量以分号结尾的SQL语句如果MySQL客户端用分号作为一条完整命令的结束标志那它看到CREATE PROCEDURE内部的第一条分号就会认为命令结束了后面的内容就会被当成完全独立的语句执行必然报错。所以我们要先把分隔符改成别的符号让客户端知道整个CREATE PROCEDURE是一个完整的整体直到遇到//才算结束。执行完再改回分号恢复正常操作。课堂上老师打了一个比方这就像在Word里输入一段包含大量句号的文字但要求整段文字作为一个整体复制就必须用一对引号把它包起来。DELIMITER的作用就相当于告诉MySQL“从现在起用引号来界定整段内容别看到句号就断句”。6.2 带参数的存储过程示例存储过程支持IN、OUT、INOUT三种参数类型。IN表示传入参数OUT表示输出参数INOUT表示既可传入又能被修改后传出去。课堂示例是在成绩表里查出某门课程的平均分并放到OUT参数里DELIMITER // CREATE PROCEDURE GetAvgScore( IN p_course_id INT, OUT p_avg DECIMAL(5,2) ) BEGIN SELECT AVG(score) INTO p_avg FROM score WHERE course_id p_course_id; END // DELIMITER ; CALL GetAvgScore(1, avg_score); SELECT avg_score;SELECT ... INTO语法可以把查询结果直接赋给变量这里特别注意OUT参数必须使用开头的用户变量才能接收结果。存储过程内部还可以定义局部变量使用DECLARE声明只在BEGIN...END块内有效。IF条件判断、WHILE循环、CURSOR游标都可以在存储过程中使用但课堂明确说只要能用一条SQL解决的事情就不要写存储过程。存储过程在复杂的报表统计中偶尔有用但在业务系统里过度使用会让逻辑难以维护、数据库压力过大现代的微服务架构里已经很少用存储过程承载业务逻辑了。6.3 触发器的DELIMITER问题热词里“mysql中触发器中分隔符”的搜索量说明这也是一个集中踩坑点。触发器和存储过程一样定义时需要先用DELIMITER改掉分隔符。触发器会在表上执行INSERT、UPDATE、DELETE操作时自动触发一段逻辑。课堂创建了一个成绩变更日志表然后做了一个触发器当成绩表被更新时自动把修改前后的值记录到日志表CREATE TABLE score_log ( id INT PRIMARY KEY AUTO_INCREMENT, score_id INT, old_score DECIMAL(5,2), new_score DECIMAL(5,2), changed_at DATETIME DEFAULT CURRENT_TIMESTAMP ); DELIMITER // CREATE TRIGGER trg_score_update AFTER UPDATE ON score FOR EACH ROW BEGIN INSERT INTO score_log(score_id, old_score, new_score) VALUES (OLD.id, OLD.score, NEW.score); END // DELIMITER ;触发器里有OLD和NEW两个关键字UPDATE时OLD代表更新前的旧行NEW代表更新后的新行INSERT只有NEWDELETE只有OLD。这个机制有点像编程里的回调函数数据库帮你监听事件事件发生时自动执行。触发器的问题是“隐式逻辑”你并不知道这段代码什么时候会执行出了问题很难排查。热词里“mysql logs目录下 general.log”就是排查这类问题的一种方式开启general_log后服务器上执行的所有SQL都会记录到日志文件中方便还原现场。6.4 数据库连接池与“9.7”的搞笑乌龙课堂最后还提了一句数据库连接池。热词里“mysql的数据库连接池”也很热门。连接池的作用简单说就是数据库连接是很贵的资源每次连接要建立TCP连接、做认证、可能还要加密握手用完就关闭太浪费。连接池会在程序启动时就预先创建一批连接放在池子里需要时取出用完归还连接数可控连接复用性能明显提升。Java生态里常用HikariCP、DruidPython生态里常用SQLAlchemy的连接池。这里还有一个课堂上的插曲热词里出现“mysql 9.7”看起来像一个新版本号实际上MySQL官方目前的版本序列并没有9.7这大概率是某个教程或工具界面里出现的笔误或乌龙。类似这种不存在的“版本号搜索”每年都会出现在热搜里大家在学习时要注意以官方文档为准不要被道听途说的版本号带偏了节奏。MySQL正确的官方版本归宿就是看官网的下载页看清发行版本号再动手。7. 常见报错与排查技巧实录7.1 安装启动报错start the server卡住热词里“安装mysql start the server”是非常典型的安装问题汇总。MySQL Installer执行到Start the Server步骤时卡住甚至进度条一直不动最后弹窗报错绝大多数是这几个原因3306端口被占用这是最常见的原因。排查方法是在命令行执行netstat -ano | findstr 3306看哪个进程占用了3306端口。如果确实被占用要么关闭占用进程要么修改my.ini里的port配置。如果你之前装过MySQL服务可能还在运行先停掉再安装。服务名冲突如果之前卸载MySQL不彻底服务列表里残留了MySQL80之类的旧服务新装的注册不上。把旧的卸载干净删除服务可以用sc delete MySQL80命令管理员权限执行。防火墙拦截Windows防火墙默认没有放行MySQL的3306端口安装器执行服务启动验证时连接不上就会出现超时。检查防火墙入站规则为3306端口放行或者安装时暂时关闭防火墙装好后再放行端口。7.2 连接报错认证插件和端口问题用Navicat或Workbench连接MySQL时报错是最多的场景。最常见的错误是“Authentication plugin caching_sha2_password cannot be loaded”这就是本文前面提到的认证方式兼容性问题。新装的MySQL 8.0默认用caching_sha2_password而一些旧版的数据库驱动和图形客户端只认识mysql_native_password。解决办法就是改认证方式ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码; FLUSH PRIVILEGES;另一个常见报错是“Cant connect to MySQL server on localhost (10061)”这个10061错误码的意思是端口无法访问。排查思路按顺序来先确认MySQL服务是否启动命令行net start mysql或者看任务管理器里的mysqld进程再确认端口是否对my.ini里的port是不是改过连接时是否写对了端口号再确认本机防火墙是否放行3306。还有一个坑是host。安装时root账号默认绑定localhost如果你用Docker或者其他远程机器连接MySQL需要单独创建一个允许远程访问的账号或者修改root账号的host为%但更安全的做法是创建专有账号CREATE USER app% IDENTIFIED BY StrongPassword123!; GRANT ALL PRIVILEGES ON school.* TO app%; FLUSH PRIVILEGES;7.3 日志排查general.log和错误日志位置排查MySQL问题绕不开日志。热词里“mysql logs目录下 general.log”说明有人看到了这个文件但不明白它的用途。MySQL日志体系主要有四种错误日志、查询日志general log、慢查询日志、二进制日志。错误日志记录的是MySQL启动、运行、出错时的状态信息默认在数据目录下文件名一般是主机名.err初始化时生成的临时密码就在这里找。查询日志也叫general log记录服务器收到的所有SQL语句包括客户端执行的每条命令开启后非常占空间生产环境一般不开排查问题时临时开一下。慢查询日志则记录执行时间超过阈值的SQL是性能调优的第一手材料。二进制日志就是binlog用于主从复制、数据恢复和增量同步热词里的“mysql增量同步工具|4款高适配实时同步工具选型推荐”可以基于binlog的解析来做实际业务中也有多种同步工具这里只是提一句有兴趣的可以单独深入研究。开启general log的命令是SET GLOBAL general_log ON; SET GLOBAL general_log_file D:/mysql_log/general.log;用完记得关掉以免日志文件膨胀。7.4 Docker安装MySQL时的数据持久化热词里“docker安装mysql”也是高频需求。课堂演示了Docker方式安装MySQL核心点是数据持久化和配置挂载不然容器一删数据全没了。一条比较完整的命令是这样docker run -d \ --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD你的密码 \ -e MYSQL_DATABASEschool \ -v /docker/mysql/data:/var/lib/mysql \ -v /docker/mysql/conf:/etc/mysql/conf.d \ mysql:8.0-v参数把容器内的数据目录挂载到宿主机这样容器删了重建数据还在。还需要注意容器里MySQL的时区问题默认是UTC和北京时间差8小时连接串里最好加上serverTimezoneAsia/Shanghai参数。用Docker跑MySQL很适合本地开发尤其是你不想污染宿主机环境的时候。但也正因为它是容器一些初始化脚本和配置方式和Windows原生安装有很大差别。初学者建议还是先在Windows或者Linux上直接装一次能对MySQL的运行机制有一个真实的体感再上Docker不迟。8. 课后复盘与经验补充分享这堂课上完我自己最大的收获不是记住了多少SQL语法而是建立起一个认知框架学习MySQL不能只停留在“会写查询”的层面。安装配置、索引原理、执行计划、存储过程、触发器、报错排查……这些东西本质上是在回答同一个问题——MySQL是怎么工作的。搞懂工作机制再去写SQL、调优、排错就都不是背题而是顺理成章的事。如果只能从这堂课带走三件事我会选这三条第一安装MySQL不要盲目追最新版本8.0系列是当前性价比最高的选择第二遇到任何SQL执行问题和性能问题先EXPLAIN看执行计划你会有源源不断的结论和排查依据第三存储过程、触发器这类数据库端逻辑了解它们的语法和适用场景即可业务代码里能不用尽量不用。还有一个小技巧想分享课堂上练习用的“学生课程成绩”三张表建议课后自己反复折腾。给它造一万条数据试着找出选课人数最多的前三门课、算每门课成绩的方差、把每张表加上索引看查询时间的变化。数据库这个东西看十遍文档不如自己动手做一遍踩过坑才能长记性。这一万条数据扔进去查询你才能真正体会到索引不是概念而是真金白银的查询速度差异。 MySQL实战笔记：从安装配置到索引优化与报错排查
1. 开课前的准备这堂课到底讲什么3月16日这堂MySQL课内容密度比想象中大得多。虽然课程标题只写了“MySQL上课笔记”但实际上从安装环境开始一路讲到索引优化、存储过程、触发器还把日常开发里经常踩的报错都过了一遍。用一句话概括这是一堂从“能跑起来”到“跑得明白”的MySQL入门加进阶课。如果你是刚接触数据库的初学者这堂课能帮你把MySQL的安装、配置、常用SQL命令、索引原理、执行计划这些零散知识点串成一条线。如果你已经写过一些SQL但对存储过程、触发器、子查询更新、执行计划这些内容知其然不知其所以然这堂课同样可以帮你补上“为什么”的那一块。MySQL的学习有一个特点就是“会写SQL”和“懂MySQL”是完全不同的两个阶段——前者只需要语法后者需要理解数据是怎么存的、SQL是怎么执行的、索引是怎么起作用的。这堂课明显是往第二个阶段打的。课程整体分成了六块环境安装、SQL基础、进阶查询、索引与性能、存储过程与触发器、报错排查。这个顺序本身就值得说一句它按照“从搭建到使用从使用到理解从理解到调优”的逻辑递进比直接上来就背SQL语法要高效得多。下面我按课堂实际推进的顺序把笔记整理出来。2. MySQL安装与环境配置2.1 版本选择8.0还是8.4不要盲目追新课堂第一件事不是装软件而是选版本。这里老师花了大概十分钟讲版本选择原因很现实——版本没选对后面全是坑。目前主流的MySQL版本有两个分支8.0系列和8.4系列也就是所谓的LTS长期支持版本。实际开发中8.0依然是市场占有率最高的国内大多数企业的生产环境都跑在8.0上。8.4属于新出的长期支持版官方会提供更久的安全更新但新的版本带来的兼容性问题也很明显。举个例子这次热词里有一条“django.db.utils.notsupportederror: mysql 8.4 or later is required (found 8.0)”说明Django这类框架已经适配了8.4但反过来很多老项目如果直接升到8.4反而会报各种找不到驱动、认证方式不支持的问题。我的建议是个人学习和新项目选8.0.43或者8.4都可以但如果是课堂练习、跟着教程走、希望复现网上绝大多数案例老老实实选8.0系列。因为网上90%的教程、问答、课程案例都是基于8.0写的你用8.4去跑可能导致行为不一样。这就好比大家都在踩熟的路你非要走一条新修的岔路看着路更宽但坑在哪里没人告诉过你。下载渠道要注意务必去官网下载不要用第三方站点转存的安装包。官网的下载入口一般会有两个选择一个是MySQL InstallerWindows下带图形界面的安装器一个是ZIP免安装版。热词里出现了“mysql压缩包安装教程8.0.43”和“mysql免安装版本”说明很多人更倾向免安装方式这个后面细说。官网下载地址有国内镜像分流下载速度一般可以接受不用额外找加速手段。2.2 安装方式图形安装器与免安装版取舍Windows下安装MySQL有两条主流路线官方提供的MySQL Installer以及ZIP压缩包免安装版本。MySQL Installer的好处是“省心”它会自动检测依赖环境比如缺少Visual C运行库时会提醒安装也会把MySQL Server、MySQL Shell、MySQL Workbench、Router等组件打包成一个安装流程勾选后一次装齐。但它有个毛病安装到“Start the Server”这一步经常卡住。热词里“安装mysql start the server”就是这个问题。卡住的原因很多最常见的是3306端口被占用——之前装过MySQL或者电脑上有其他数据库服务把端口占了。还有一种是防火墙拦截了安装程序对端口的访问。ZIP免安装版则完全相反它的核心就是一个目录解压后手动改配置、手动初始化数据目录、手动注册Windows服务。看着麻烦但整个过程每一步都是可控的哪里出错一眼就能看到。课堂演示用的是免安装版因为老师希望我们能自己把每个环节走一遍而不是靠安装器一键搞定。免安装版的流程大致如下解压到指定目录后在根目录新建my.ini配置文件至少需要指定basedirMySQL所在目录和datadir数据存储目录然后以管理员身份打开命令行进入bin目录执行mysqld --initialize-insecure进行初始化这个命令会生成一个data目录同时默认root账号密码为空接着执行mysqld --install把MySQL注册成Windows服务再执行net start mysql启动服务。如果不想注册服务每次用mysqld --console前台启动也行适合调试时看日志。2.3 初始密码与第一次登录初始化方式决定了初始密码的状态。用mysqld --initialize生成的密码会随机生成并写入安装目录下的日志文件一般是data目录下的*.err文件搜索“temporary password”关键字就能找到是一串随机字符第一次登录后必须马上改掉。用mysqld --initialize-insecure生成的root用户默认为空密码直接mysql -u root就能登录登录后再执行ALTER USER语句设置密码即可。很多初学者卡在“mysql的初始密码是什么”其实关键不是密码本身而是要知道初始化方式决定了密码在哪里。课堂上的操作是mysql -u root -p # 提示输入密码此处直接回车因为是用--initialize-insecure初始化的 ALTER USER rootlocalhost IDENTIFIED BY 新密码; FLUSH PRIVILEGES;这里有个容易被忽略的细节8.0之后默认认证插件是caching_sha2_password如果你的客户端或者图形工具比较老连接时会报认证失败。遇到这种情况要么升级客户端要么把root账号的认证方式改成mysql_native_password。课堂建议是能用新客户端就用新的不要为兼容老工具把安全等级拉低。2.4 客户端工具命令行、Workbench还是Navicat环境装好后下一个问题是怎么连上去操作。课堂上三种工具都演示了一遍。命令行是必须掌握的不管以后用什么图形工具最终问题排查都要回到命令行。平时练SQL也建议多在命令行里敲养成手写命令的习惯对记忆语法帮助很大。MySQL Workbench是官方出的图形客户端免费、跨平台非常适合做ER图建模、可视化看表结构、跑查询看执行计划。热词里“mysql workbench使用教程”也是高频搜索词说明不少人选择了这个工具入门。Workbench的优点是数据库建模功能强可以从已有的库反向生成ER图对上课做的“学生课程成绩信息实体表设计”这种作业非常方便。Navicat则是很多开发者的主力工具直观、功能全、跨多种数据库但商业版收费。热词里“navigator for mysql 免费版”实际上应该指的是Navicat for MySQL的旧版本或者Lite版Navicat系列目前没有官方免费版网上的“免费版”多数是破解版不建议使用合规和安全都没保证。如果实在不想付费用Workbench或者开源的DBeaver都行。3. SQL基础命令与课堂练习3.1 建库建表一个完整的学生成绩案例环境就绪后课堂进入SQL部分。这节课没有用“员工表”“部门表”这种烂大街案例而是用了“学生课程成绩信息实体表设计”来做练习这个案例特别适合讲清楚MySQL里最关键的表关系。先建数据库和数据表CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE school; CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, student_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT M COMMENT 性别, birth_date DATE COMMENT 出生日期, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(100) NOT NULL, credit DECIMAL(3,1) NOT NULL DEFAULT 2.0 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2), exam_date DATE, FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (course_id) REFERENCES course(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这个设计里有几个考点值得记住字符集选utf8mb4而不是utf8因为utf8在MySQL里最多存3字节支持不了emoji和生僻字utf8mb4才是真正完整的UTF-8主键用自增INT简单高效UUID做主键会带来页分裂问题性能上吃亏外键约束能保证数据完整性但也带来写入性能损耗在互联网高并发场景下通常要靠业务层保证而不用物理外键课堂案例因为有教学目的所以保留了外键。3.2 插入数据与三大基础查询语法建表之后就是往里塞数据。课堂上插入了几条示例数据后就开始做筛选练习。基础查询中“排序”是关键点之一热词里的“mysql排序”对应的就是ORDER BY子句。排序要注意的是默认是升序ASC降序用DESC多个排序字段用逗号分隔从左到右依次作用于前一个字段相同的情况NULL值排序时默认排在最前面升序时和很多其他数据库的行为不一样。举一个课堂上的实际例子-- 查询成绩大于80分的学生按成绩降序成绩相同按学号升序 SELECT student_no, name, score FROM student s JOIN score sc ON s.id sc.student_id WHERE sc.score 80 ORDER BY sc.score DESC, s.student_no ASC LIMIT 10;LIMIT配合ORDER BY是分页的基础第一页就用LIMIT 10第二页LIMIT 10 OFFSET 10。但这个写法在数据量大时会有性能问题深分页时MySQL要扫描并丢弃前N行这个后面讲性能优化时再展开。3.3 去重、别名与条件筛选DISTINCT、AS别名、WHERE条件这属于每天的饭就这些看起来简单但细节很多。DISTINCT去重要记住它作用于后面所有列的组合不是只作用于第一个字段。你要是写SELECT DISTINCT course_id, student_id结果里是按照两列的组合去重不是只按course_id去重。想要单一字段的去重结果别在SELECT后面拼一堆字段。别名这里有一个容易踩的坑WHERE子句中不能直接使用SELECT别名。比如SELECT name AS n FROM student WHERE n 张三这个写法是错的MySQL会报错说不认识n这一列。原因是SQL语句的执行顺序里WHERE是先于SELECT执行的WHERE被执行时别名还没生成。但ORDER BY和HAVING又可以使用别名这是因为它们的执行顺序在SELECT之后。搞清楚执行顺序很多语法问题就迎刃而解了。WHERE条件筛选还可以配合LIKE、IN、BETWEEN这些操作-- 查询姓“张”的学生 SELECT * FROM student WHERE name LIKE 张%; -- 查询出生日期在2000-2005之间的学生 SELECT * FROM student WHERE birth_date BETWEEN 2000-01-01 AND 2005-12-31; -- 查询选了课程1或课程2的学生 SELECT * FROM score WHERE course_id IN (1, 2);LIKE里的通配符%匹配任意多个字符_匹配单个字符。只要搞清楚这两个的区别like就能玩出很多花样。3.4 UPDATE与批量修改的几种方式热词里“mysql批量修改”也是高频问题。课堂讲的批量修改方案按场景不同有三种写法。第一种是逐条UPDATE。适用于只有几条数据、前后SQL逻辑复杂的情况。缺点是每改一条都要一次网络往返量大时效率低。第二种是CASE WHEN配合一条UPDATE实现多行不同值的修改UPDATE score SET score CASE course_id WHEN 1 THEN 90 WHEN 2 THEN 85 WHEN 3 THEN 88 END WHERE course_id IN (1, 2, 3);这一条SQL可以针对不同行设置不同的新值只需要一次更新效率比逐条UPDATE高出一个数量级而且代码简洁。前提是你要修改的数据都能通过条件定位出来。第三种是INSERT ... ON DUPLICATE KEY UPDATE。这种写法特别适合“存在就更新不存在就插入”的业务场景比如批量导入成绩单新记录插入已有记录更新。课堂上示例是把Excel里的成绩单导入INSERT INTO score (student_id, course_id, score, exam_date) VALUES (1, 1, 92, 2026-01-10) ON DUPLICATE KEY UPDATE score VALUES(score), exam_date VALUES(exam_date);ON DUPLICATE KEY UPDATE的执行前提是有唯一索引或主键冲突。这里就必须给score表加一个联合唯一索引UNIQUE(student_id, course_id)保证每个学生每门课只能有一条记录。热词里单独出现“mysql创建索引”正好就是这个问题。3.5 一个经典考点mysql中int5“mysql中int5”这个热词可以拆成两个完全不同的考点。第一个考点是数据读取时对数值型字段做算术运算。比如要查询所有成绩加5分之后的结果写成SELECT score 5 FROM score数据库会把score字段的值都加5后返回但是这个操作不会修改表里的真实数据。要注意的是score如果定义为DECIMAL(5,2)加上5之后如果你只是展示没问题如果想存回去必须通过UPDATE语句。另外一个细节如果字段是INT类型做算术运算时注意溢出INT最大能到21亿多一不小心就变成负数了。第二个考点更隐蔽是字符串拼接和数值相加的区别。在Oracle里“a” || “b”是拼接在SQL Server里是“a” “b”但在MySQL里加号“”永远只做数学加法不做字符串拼接。MySQL的字符串拼接要用CONCAT函数。这个考点是很多培训班面试题里的常见坑SELECT 5 5结果是10而不是55SELECT abc 1结果是1因为MySQL把abc转成数字时转不出来就按0处理。面试官经常拿这种题考候选人有没有真正写过MySQL。4. 进阶查询JOIN、子查询与分组统计4.1 JOIN到底是什么热词里“mysql数据库join含义”能上热搜说明有太多人只用了JOIN却说不清JOIN。课堂专门花了半小时讲这个。JOIN就是把两张表按照某个关联条件横向拼接起来。它的核心是“一对多匹配”当我们用成绩表去点赞学生表时学生的id如果关联了多条成绩记录JOIN的结果就是多行。这有点类似于Excel里的VLOOKUP但功能更强因为它可以同时处理多张表而且可以指定匹配不上的行是否保留。JOIN常见的四种类型用一个比方便于区分记忆。假设有两拨人A是学生表B是成绩表。INNER JOIN就是“两边都认识的人”只返回能在两表中匹配成功的记录。LEFT JOIN就是“A全保留B能配上就配上配不上就填NULL”所以即使某学生还没考过试他在LEFT JOIN结果里还是会有一行只是成绩为NULL。RIGHT JOIN同理是B全保留实际开发中用得少因为把表顺序换一下再用LEFT JOIN就等价了。FULL OUTER JOIN是两边都保留但MySQL原生不支持不过可以用LEFT JOIN UNION RIGHT JOIN的方式达到相同效果。课堂上的例子是统计“哪些学生还没参加过考试”SELECT s.id, s.name FROM student s LEFT JOIN score sc ON s.id sc.student_id WHERE sc.id IS NULL;这个查法非常实用几乎可以说是LEFT JOIN最经典的使用场景右表主键为NULL意味着左表这条记录在右表中没有匹配。考试考这个的频率极高。4.2 子查询与更新子查询的坑子查询就是嵌套在另一个SQL中的SELECT分相关子查询和非相关子查询两种。非相关子查询先执行内层再把结果给外层用相关子查询则是外层每处理一行内层都要重新执行一遍。非相关子查询好理解相关子查询是性能杀手后面讲慢查询时经常会碰到。课堂重点讲了一个热词“mysql中更新子查询”。很多人写UPDATE时喜欢这样写-- 将选课人数超过5人的课程学分上调0.5 UPDATE course SET credit credit 0.5 WHERE id IN (SELECT course_id FROM score GROUP BY course_id HAVING COUNT(*) 5);这个写法在MySQL里直接执行会报错You cant specify target table course for update in FROM clause。原因很简单你不能在同一张表上边查边改。MySQL为了保证数据一致性禁止在UPDATE语句的子查询中直接引用和目标表相同的表。解决办法是套一层派生表把子查询结果包一层UPDATE course SET credit credit 0.5 WHERE id IN ( SELECT course_id FROM ( SELECT course_id, COUNT(*) cnt FROM score GROUP BY course_id HAVING COUNT(*) 5 ) AS tmp );包一层之后内外两层查询的语义上就不算直接在同一层级操作了MySQL就会认为这次更新是安全的。这是一个非常典型的坑面试和实际工作都会碰到。热词里能搜到“mysql中更新子查询”说明这个报错确实拦住了不少人。4.3 聚合函数与GROUP BY的分组逻辑聚合函数是SQL查询中绕不开的重要内容。COUNT、SUM、AVG、MAX、MIN这五个是最基础的。要注意的是COUNT()和COUNT(字段)的区别COUNT()统计的是行数包括NULLCOUNT(字段)统计的是这个字段非NULL的行数。如果字段里有NULL两者结果不同。GROUP BY的分组逻辑是一个容易搞混的地方。SELECT后面能出现的列要么是分组字段本身要么是聚合函数的结果不能随便混搭。比如下面这条SQL在大多数数据库里都是错的-- 错误示例name字段既不在GROUP BY里也不是聚合函数 SELECT course_id, student.name, AVG(score) FROM score JOIN student ON score.student_id student.id GROUP BY course_id;MySQL有一个特殊的行为它默认允许这种查询执行并且返回的name是该组内随机的某一行这其实是MySQL宽松模式留下的坑。如果你开启了ONLY_FULL_GROUP_BY模式上面这条就会直接报错。课堂建议是开发环境开启ONLY_FULL_GROUP_BY逼自己写符合标准的SQL这对以后对接PostgreSQL、SQL Server等数据库有好处。聚合函数的经典场景是“每门课的平均分、最高分、选课人数”SELECT course_id, COUNT(*) AS stu_cnt, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM score GROUP BY course_id HAVING COUNT(*) 3 ORDER BY avg_score DESC;HAVING和WHERE的区别课堂也强调了WHERE是在分组前过滤原始行HAVING是在分组后过滤分组结果。这个区别在写复杂统计SQL时非常关键。想把成绩大于80分的记录分组和分组后平均值大于80的分组两者对同一个字段的限制条件作用阶段完全不同结果也完全不同。5. 索引、执行计划与性能调优5.1 索引的本质排好序的目录热词里“mysql创建索引”出现多次课堂这一节算是对索引的正面讲解。索引本质上就是一种数据结构用于快速定位数据。拿《新华字典》里的偏旁部首目录来类比你要查一个字第一反应不会从第一页翻到最后而是先查目录找到对应的页码然后直接翻到那一页。数据库索引干的就是这件事MySQL里最常见的索引数据结构是BTree。索引的分类要清楚按功能分有普通索引、唯一索引、主键索引、全文索引按结构分主要有BTree索引和Hash索引按列的个数分有单列索引和复合索引。实际开发中用得最多的是普通索引和唯一索引查询条件下如果没有索引就叫做“全表扫描”一张表几十万条数据时一次全表扫描可能要几百毫秒甚至几秒有合适索引时查询可以降到几毫秒差距巨大。创建索引的SQL很简单CREATE INDEX idx_score_course ON score(course_id); CREATE UNIQUE INDEX uk_student_course ON score(student_id, course_id); -- 查看表的索引 SHOW INDEX FROM score; -- 删除索引 DROP INDEX idx_score_course ON score;但“会创建索引”距离“建对索引”差距很大。课堂上给了一个建索引的经验原则一是查询频繁出现在WHERE和JOIN条件的字段才适合建索引不要给每个字段都建索引也会占用空间写入时还要维护索引结构索引多了写入会变慢。二是区分度高的列优先比如gender这种只有两个值的列建了索引也没有区分度数据库可能干脆不走索引。三是复合索引要遵守最左前缀原则索引idx_a_b_c相当于创建了(a)、(a,b)、(a,b,c)三组有效索引查询条件是b和c时用不上这个索引。5.2 执行计划怎么看热词“mysql执行计划”直接用关键字EXPLAIN就能看到MySQL执行一条SQL的详细过程。课堂上用EXPLAIN演示了几条查询这里把关键字段整理一下。EXPLAIN的输出中最值得关注的是这几个列type、key、rows、Extra。type列代表访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。system级别基本上只有表里只有一行数据时出现平时见不到const是根据主键或唯一索引精准定位比如WHERE id 1ref是普通索引等值匹配range是范围扫描比如大于小于、BETWEEN、INindex是索引全扫描还不如rangeALL就是全表扫描这是最差的如果重要查询里出现ALL基本上就要考虑加索引了。key列显示实际用到的索引possible_keys列显示可能用到的索引这两个要对比看。有时候possible_keys里有索引但实际用的是NULL说明优化器认为走索引反而更慢或者因为索引无法正确匹配。rows列是一个估算值表示要扫描多少行这个值越小越好。Extra列常出现的几个值也值得记一下Using filesort说明ORDER BY没能利用索引排序需要额外排序数据量大时有性能风险Using temporary说明查询中用到了临时表常见于GROUP BY和DISTINCT操作Using index说明使用到了覆盖索引查询所需的数据直接从索引里拿到了无需回表这是最快的状态Using where表示在存储引擎返回的记录上做了条件过滤Using join buffer则表示JOIN时使用的是缓存区多以小表驱动大表来避免这种场景。5.3 覆盖索引、最左前缀与常用调优手段执行计划配合索引优化课堂讲了一个经典的覆盖索引案例。假设有这样一条查询SELECT student_id, course_id FROM score WHERE student_id 1;如果表上有一个联合索引UNIQUE(student_id, course_id)那么执行计划中Extra会显示Using index说明MySQL直接从索引叶子节点获取了全部需要的数据不需要回表查原数据。这就是覆盖索引的威力。但如果查询变成了SELECT score这个不在索引里的字段MySQL就得拿着主键ID回到聚簇索引里再找一次也就是回表。回表和覆盖索引是面试高频题理解了这两个概念就能解释很多SQL快慢不同的原因。最左前缀原则也在这一节里再强调了一次。索引idx_a_b_c在查询条件为WHERE b 1 AND c 2时是用不上的除非查询中也包含a字段。这一点在面试中考察比例极高而且实际工作中经常会因为忽略了它建了冗余索引。常见的调优手段课堂列了一个清单避免SELECT *只查需要的字段减少回表和网络传输。分页优化深分页场景下用WHERE id 上一页最大ID LIMIT 10替代LIMIT 10 OFFSET 100000。使用EXPLAIN检查执行计划尤其关注type和rows。开启慢查询日志定位执行时间超过阈值的SQL再逐个分析。利用数据库连接池管理连接不要频繁创建和销毁连接。大批量插入时考虑分批提交每批几百条到几千条不要一条条插入也不要一次性几万条。对只读压力大的业务用主从复制读分离来分散压力。热词里“mysql性能调优”相关的搜索量很高实际上调优第一步绝对不是改配置参数而是先找到慢SQL再分析慢在哪里最后才是调整索引或SQL写法。顺序反了一切努力都是白费。6. 存储过程、触发器与分隔符的坑6.1 存储过程的声明与调用存储过程是课堂上比较靠后的内容但也是“MySQL干货”里区分新手和老手的一道分水岭。简单说存储过程就是把一段SQL逻辑封装成一个可重复调用的程序块类似于编程语言里的函数。热词里“mysql声明存储过程”和“mysql存储过程”指向的是同一件事。一个最简单的无参存储过程如下DELIMITER // CREATE PROCEDURE GetAllStudents() BEGIN SELECT * FROM student; END // DELIMITER ;注意这里的DELIMITER它可能是初学存储过程时最莫名其妙的语法。它的作用是把MySQL客户端的语句分隔符从默认的分号“;”改成其他符号比如“//”。为什么因为存储过程内部会有大量以分号结尾的SQL语句如果MySQL客户端用分号作为一条完整命令的结束标志那它看到CREATE PROCEDURE内部的第一条分号就会认为命令结束了后面的内容就会被当成完全独立的语句执行必然报错。所以我们要先把分隔符改成别的符号让客户端知道整个CREATE PROCEDURE是一个完整的整体直到遇到//才算结束。执行完再改回分号恢复正常操作。课堂上老师打了一个比方这就像在Word里输入一段包含大量句号的文字但要求整段文字作为一个整体复制就必须用一对引号把它包起来。DELIMITER的作用就相当于告诉MySQL“从现在起用引号来界定整段内容别看到句号就断句”。6.2 带参数的存储过程示例存储过程支持IN、OUT、INOUT三种参数类型。IN表示传入参数OUT表示输出参数INOUT表示既可传入又能被修改后传出去。课堂示例是在成绩表里查出某门课程的平均分并放到OUT参数里DELIMITER // CREATE PROCEDURE GetAvgScore( IN p_course_id INT, OUT p_avg DECIMAL(5,2) ) BEGIN SELECT AVG(score) INTO p_avg FROM score WHERE course_id p_course_id; END // DELIMITER ; CALL GetAvgScore(1, avg_score); SELECT avg_score;SELECT ... INTO语法可以把查询结果直接赋给变量这里特别注意OUT参数必须使用开头的用户变量才能接收结果。存储过程内部还可以定义局部变量使用DECLARE声明只在BEGIN...END块内有效。IF条件判断、WHILE循环、CURSOR游标都可以在存储过程中使用但课堂明确说只要能用一条SQL解决的事情就不要写存储过程。存储过程在复杂的报表统计中偶尔有用但在业务系统里过度使用会让逻辑难以维护、数据库压力过大现代的微服务架构里已经很少用存储过程承载业务逻辑了。6.3 触发器的DELIMITER问题热词里“mysql中触发器中分隔符”的搜索量说明这也是一个集中踩坑点。触发器和存储过程一样定义时需要先用DELIMITER改掉分隔符。触发器会在表上执行INSERT、UPDATE、DELETE操作时自动触发一段逻辑。课堂创建了一个成绩变更日志表然后做了一个触发器当成绩表被更新时自动把修改前后的值记录到日志表CREATE TABLE score_log ( id INT PRIMARY KEY AUTO_INCREMENT, score_id INT, old_score DECIMAL(5,2), new_score DECIMAL(5,2), changed_at DATETIME DEFAULT CURRENT_TIMESTAMP ); DELIMITER // CREATE TRIGGER trg_score_update AFTER UPDATE ON score FOR EACH ROW BEGIN INSERT INTO score_log(score_id, old_score, new_score) VALUES (OLD.id, OLD.score, NEW.score); END // DELIMITER ;触发器里有OLD和NEW两个关键字UPDATE时OLD代表更新前的旧行NEW代表更新后的新行INSERT只有NEWDELETE只有OLD。这个机制有点像编程里的回调函数数据库帮你监听事件事件发生时自动执行。触发器的问题是“隐式逻辑”你并不知道这段代码什么时候会执行出了问题很难排查。热词里“mysql logs目录下 general.log”就是排查这类问题的一种方式开启general_log后服务器上执行的所有SQL都会记录到日志文件中方便还原现场。6.4 数据库连接池与“9.7”的搞笑乌龙课堂最后还提了一句数据库连接池。热词里“mysql的数据库连接池”也很热门。连接池的作用简单说就是数据库连接是很贵的资源每次连接要建立TCP连接、做认证、可能还要加密握手用完就关闭太浪费。连接池会在程序启动时就预先创建一批连接放在池子里需要时取出用完归还连接数可控连接复用性能明显提升。Java生态里常用HikariCP、DruidPython生态里常用SQLAlchemy的连接池。这里还有一个课堂上的插曲热词里出现“mysql 9.7”看起来像一个新版本号实际上MySQL官方目前的版本序列并没有9.7这大概率是某个教程或工具界面里出现的笔误或乌龙。类似这种不存在的“版本号搜索”每年都会出现在热搜里大家在学习时要注意以官方文档为准不要被道听途说的版本号带偏了节奏。MySQL正确的官方版本归宿就是看官网的下载页看清发行版本号再动手。7. 常见报错与排查技巧实录7.1 安装启动报错start the server卡住热词里“安装mysql start the server”是非常典型的安装问题汇总。MySQL Installer执行到Start the Server步骤时卡住甚至进度条一直不动最后弹窗报错绝大多数是这几个原因3306端口被占用这是最常见的原因。排查方法是在命令行执行netstat -ano | findstr 3306看哪个进程占用了3306端口。如果确实被占用要么关闭占用进程要么修改my.ini里的port配置。如果你之前装过MySQL服务可能还在运行先停掉再安装。服务名冲突如果之前卸载MySQL不彻底服务列表里残留了MySQL80之类的旧服务新装的注册不上。把旧的卸载干净删除服务可以用sc delete MySQL80命令管理员权限执行。防火墙拦截Windows防火墙默认没有放行MySQL的3306端口安装器执行服务启动验证时连接不上就会出现超时。检查防火墙入站规则为3306端口放行或者安装时暂时关闭防火墙装好后再放行端口。7.2 连接报错认证插件和端口问题用Navicat或Workbench连接MySQL时报错是最多的场景。最常见的错误是“Authentication plugin caching_sha2_password cannot be loaded”这就是本文前面提到的认证方式兼容性问题。新装的MySQL 8.0默认用caching_sha2_password而一些旧版的数据库驱动和图形客户端只认识mysql_native_password。解决办法就是改认证方式ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 你的密码; FLUSH PRIVILEGES;另一个常见报错是“Cant connect to MySQL server on localhost (10061)”这个10061错误码的意思是端口无法访问。排查思路按顺序来先确认MySQL服务是否启动命令行net start mysql或者看任务管理器里的mysqld进程再确认端口是否对my.ini里的port是不是改过连接时是否写对了端口号再确认本机防火墙是否放行3306。还有一个坑是host。安装时root账号默认绑定localhost如果你用Docker或者其他远程机器连接MySQL需要单独创建一个允许远程访问的账号或者修改root账号的host为%但更安全的做法是创建专有账号CREATE USER app% IDENTIFIED BY StrongPassword123!; GRANT ALL PRIVILEGES ON school.* TO app%; FLUSH PRIVILEGES;7.3 日志排查general.log和错误日志位置排查MySQL问题绕不开日志。热词里“mysql logs目录下 general.log”说明有人看到了这个文件但不明白它的用途。MySQL日志体系主要有四种错误日志、查询日志general log、慢查询日志、二进制日志。错误日志记录的是MySQL启动、运行、出错时的状态信息默认在数据目录下文件名一般是主机名.err初始化时生成的临时密码就在这里找。查询日志也叫general log记录服务器收到的所有SQL语句包括客户端执行的每条命令开启后非常占空间生产环境一般不开排查问题时临时开一下。慢查询日志则记录执行时间超过阈值的SQL是性能调优的第一手材料。二进制日志就是binlog用于主从复制、数据恢复和增量同步热词里的“mysql增量同步工具|4款高适配实时同步工具选型推荐”可以基于binlog的解析来做实际业务中也有多种同步工具这里只是提一句有兴趣的可以单独深入研究。开启general log的命令是SET GLOBAL general_log ON; SET GLOBAL general_log_file D:/mysql_log/general.log;用完记得关掉以免日志文件膨胀。7.4 Docker安装MySQL时的数据持久化热词里“docker安装mysql”也是高频需求。课堂演示了Docker方式安装MySQL核心点是数据持久化和配置挂载不然容器一删数据全没了。一条比较完整的命令是这样docker run -d \ --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD你的密码 \ -e MYSQL_DATABASEschool \ -v /docker/mysql/data:/var/lib/mysql \ -v /docker/mysql/conf:/etc/mysql/conf.d \ mysql:8.0-v参数把容器内的数据目录挂载到宿主机这样容器删了重建数据还在。还需要注意容器里MySQL的时区问题默认是UTC和北京时间差8小时连接串里最好加上serverTimezoneAsia/Shanghai参数。用Docker跑MySQL很适合本地开发尤其是你不想污染宿主机环境的时候。但也正因为它是容器一些初始化脚本和配置方式和Windows原生安装有很大差别。初学者建议还是先在Windows或者Linux上直接装一次能对MySQL的运行机制有一个真实的体感再上Docker不迟。8. 课后复盘与经验补充分享这堂课上完我自己最大的收获不是记住了多少SQL语法而是建立起一个认知框架学习MySQL不能只停留在“会写查询”的层面。安装配置、索引原理、执行计划、存储过程、触发器、报错排查……这些东西本质上是在回答同一个问题——MySQL是怎么工作的。搞懂工作机制再去写SQL、调优、排错就都不是背题而是顺理成章的事。如果只能从这堂课带走三件事我会选这三条第一安装MySQL不要盲目追最新版本8.0系列是当前性价比最高的选择第二遇到任何SQL执行问题和性能问题先EXPLAIN看执行计划你会有源源不断的结论和排查依据第三存储过程、触发器这类数据库端逻辑了解它们的语法和适用场景即可业务代码里能不用尽量不用。还有一个小技巧想分享课堂上练习用的“学生课程成绩”三张表建议课后自己反复折腾。给它造一万条数据试着找出选课人数最多的前三门课、算每门课成绩的方差、把每张表加上索引看查询时间的变化。数据库这个东西看十遍文档不如自己动手做一遍踩过坑才能长记性。这一万条数据扔进去查询你才能真正体会到索引不是概念而是真金白银的查询速度差异。