简介面向郑州大学计算机与人工智能学院《数据库系统原理》课程学习者这份实验报告围绕DBMS系统认识与万里数据库GreatSQL的安装、启动、管理和使用展开适合需要完成课内实验、整理实验记录或复盘数据库环境配置的读者参考。压缩包内仅含1个docx文档大小约3.69MB内容按实验目的、实验内容、实验类型、学时分配、仪器材料、实验过程与结果分析组织并涉及关闭SELinux和防火墙、安装依赖包、下载解压二进制包、了解基本表/视图/触发器/存储过程/约束等逻辑组件及物理存储组件、截图编号和表格规范等环节。已有509人学习可作为实验报告撰写模板与GreatSQL入门操作对照材料帮助读者梳理从环境搭建、服务管理到结果反思的完整实验链路理解常见问题、处理思路与报告排版要求也便于按课程实验评分点自查内容完整性。1. 装一套数据库真正装的是什么很多人以为实验一的核心是装软件解压、敲几条命令、服务起来就算收工。实际相反解压那一步在整个流程里技术含量最低。真正被建立起来的是一份三方约定——一个以低权限账号运行的常驻进程、一套固定布局的数据目录、一组允许它读写的系统权限边界。这三样里任何一项没对齐后面做数据库原理实验时那些看起来跟表毫无关系的报错就会冒出来。搞懂这份约定你才明白为什么关掉某个强制访问控制模块服务就能起来、为什么数据目录属主错了初始化永远失败。这份实验报告的价值不在于它记了多少条命令而在于它把九个实验串成了一条从进程到数据页、从 DDL 到查询执行链路的完整观察路径。2. 从 systemctl start 到常驻进程一个数据库服务是怎么被拉起来的2.1 单元文件真正决定服务生死的那几十行二进制包解压完很多人直接敲mysqld手动跑起来看到提示符能连上就以为装好了。这和注册成系统服务是两回事。手动跑的进程挂在前台终端上终端一关进程跟着走注册成服务之后它被交给 init 系统托管才有了崩溃重启、开机自启、日志统一收集这些能力。在主流 Linux 发行版上这份托管关系写在单元文件里。实验报告里那条vim /lib/systemd/system/greatsql.service编辑的就是它但报告只写了创建这个文件没写清楚每个字段在管什么# /lib/systemd/system/gdb.service —— 服务单元文件的关键字段GDB 代指实验所用发行版 [Unit] DescriptionGDB Server Afternetwork.target # 网络栈就绪后再启动避免监听失败 [Service] Typesimple # mysqld 不 fork前台常驻systemd 直接盯住主进程 Usermysql # 关键用低权限账号运行不用 root Groupmysql ExecStart/usr/local/gdb/bin/mysqld --defaults-file/etc/my.cnf ExecReload/bin/kill -HUP $MAINPID LimitNOFILE65535 # 每个连接占一个文件描述符这里先设上限 Restarton-failure # 非正常退出时自动拉起 [Install] WantedBymulti-user.target # 挂到多用户运行级等价于开机启动Typesimple决定了 systemd 认哪个进程是服务本体。如果写成Typeforking就必须再给一个PIDFile否则 systemd 会以为服务启动超时然后把它杀掉——这是二进制包安装最常见的翻车点之一。Usermysql是安全边界数据库进程只需要读写自己那套目录没必要拿 root 的全盘权限一旦被利用损失范围被限制在数据目录内。LimitNOFILE则是一个隐蔽的前置约束连接数上限并不先由max_connections卡住而是先被文件描述符数量卡住表现是连接建立到某个数量后突然报 Too many open files而配置文件里的连接数明明还差得远。改完单元文件必须执行systemctl daemon-reload。原因是 systemd 启动时把单元文件解析成内存里的对象缓存之后的start操作走的是缓存不 reload 等于文件白改——这个行为没有提示、没有警告只有我明明改了配置怎么没生效的困惑。2.2 关 SELinux 与防火墙被误读最深的两条命令实验指导里几乎都会写关闭 SELinux 和防火墙报告里给出的理由也很像回事安全性、简化配置、性能。但把机制拆开看这两件事解决的根本不是同一个问题。SELinux 是一套强制访问控制MAC机制。传统 Linux 权限看的是你是谁、文件属主是谁、权限位怎么给SELinux 在之上又加了一层即便你是 root、文件权限 777只要进程的域和文件的类型标签不匹配照样拒绝。数据库二进制包解压到自定义目录时这些文件继承的是父目录的默认标签不是数据库专用类型于是进程启动时读写数据目录直接被拦。报错通常是Permission denied或Errcode: 13而ls -l看上去权限完全正常——这就是它最坑的地方普通权限位和强制访问控制是两套独立的判定。防火墙解决的是另一个维度入站 TCP 连接能不能到达监听端口。数据库默认监听 3306防火墙没放行时本机mysql客户端用 socket 能连上远程客户端却一直超时。这个现象很有迷惑性——服务明明活着进程也在ss -lntp也能看到监听就是连不上。真正的取舍在于关掉是最省事的但代价是把整台机器的这两层防护一起拆了。更克制的做法是只放行需要的那一点点# 只给数据目录打上正确的文件类型标签而不是关掉整个 MAC 机制 semanage fcontext -a -t mysqld_db_t /var/lib/gdb(/.*)? restorecon -Rv /var/lib/gdb # 只放行数据库端口而不是停掉防火墙 semanage port -a -t mysqld_port_t -p tcp 3306 firewall-cmd --permanent --add-port3306/tcp firewall-cmd --reload # 确认端口真的在监听以及监听在哪个地址上 ss -lntp | grep mysqld # LISTEN 0 151 *:3306 *:* users:((mysqld,pid1234,fd21))ss输出里*:3306和127.0.0.1:3306是完全不同的两件事后者只接受本机连接远程怎么连都是超时而这跟防火墙一点关系都没有是bind-address配置的问题。实验环境里常把这两类连不上混为一谈排查时白白绕一大圈。2.3 账号、目录属主与初始化进程拿什么身份写文件报告里那条useradd -g mysql mysql -d /dev/null -s /sbin/nologin值得单独说。-d /dev/null把家目录指到一个不存在的设备-s /sbin/nologin把登录 shell 关掉——这两个参数合起来的语义是这个账号不是给人用的只是给进程一个可归属的 UID。系统里所有以它身份运行的进程权限上限就锁死在这个 UID 能碰到的范围内。紧接着的chown -R mysql:mysql /var/lib/mysql决定了数据目录的归属。二进制包安装时数据目录是空的第一次启动要做初始化建系统表空间、写初始账号如果目录属主还是 root初始化进程以 mysql 身份去写就会失败。这类失败有一个共同特征报错信息里带Errcode: 13或者Cant create/write to file但错误里出现的文件路径往往不是你正在想的那一个可能是临时目录、可能是日志目录、也可能是 socket 文件所在目录——凡是进程要写的地方属主都得对。所以我一般会把datadir、log-error、socket、pid-file这四个路径一起检查而不是只 chown 数据目录。目录权限本身也有讲究。数据目录通常是700意味着除了属主其他任何账号连列目录都不行。这不是洁癖数据文件里存的是明文行记录能读文件就等于能读全库绕过了所有 SQL 层的权限检查。3. 逻辑组件与物理组件一条 SQL 的两套坐标3.1 五个逻辑组件的元数据落点报告把基本表、视图、触发器、存储过程、约束列为逻辑组件这个分类是对的但把它当成五个名词背下来毫无意义。它们真正的差别在于元数据存在哪里、什么时候被执行。基本表是唯一真正持有行数据的对象视图只是一条被存起来的查询语句本身不占数据页触发器是绑定在某张表某个事件上的动作由 DML 隐式触发存储过程是命名好的语句块由调用显式触发约束是数据字典里的规则由写入路径强制执行。这五者在information_schema里各有自己的落点-- 逻辑组件在数据字典里的位置把 db 换成自己的库名 SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA db; -- BASE TABLE 与 VIEW 都在这里 SELECT TABLE_NAME, VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_SCHEMA db; -- 视图只存定义没有数据行 SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA db; -- 触发器绑在哪张表、哪个时机 SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA db AND ROUTINE_TYPE PROCEDURE; SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA db; -- 主键、外键、唯一约束都在这里有个细节挺有意思报告里查视图用的命令是SHOW FULL PROCESSLIST这条命令返回的其实是当前的连接线程列表跟视图没有半点关系。这个混淆恰好说明了视图和表的本质区别——视图没有物理实体可查你能查到的只有它的定义。正确的入口是information_schema.VIEWS或者SHOW FULL TABLES WHERE Table_typeVIEW。把这几张字典表连起来看会得到一个关键认知数据库的所有结构本身也是数据也存在表里也走同一条查询路径。这就是为什么改表结构要加元数据锁也是为什么 DDL 在大表上会卡住整张表的读写。3.2 数据页、表空间与独立表文件数据到底存在哪物理层的第一个事实是存储单位不是行是页。默认页大小 16KB一次磁盘 I/O 最少读一页哪怕你只要一行。第二个事实是页之上的组织单位连续 64 个页组成一个区extent约 1MB区再往上组成段段再组成表空间表空间最后落到磁盘文件上。一个页内部的布局大致分四块页头存页号、页类型、校验和、前后页指针、行记录区真正的数据行按主键顺序排列、空闲空间给后续插入留的、页目录每几行一个槽位用于页内二分查找。这个结构直接解释了一个常见困惑为什么删了大量数据表文件大小一点没变。删除只是在行记录上打个删除标记空间留在页内部只有整页都空了才可能被回收而且回收后文件通常也不会自动缩小。# 数据目录里能看到几类完全不同的文件 ls -lh /var/lib/gdb/ # ibdata1 系统表空间数据字典、回滚段等共享结构 # db/students.ibd 独立表空间开启 file_per_table 后一表一文件 # #innodb_redo/ 重做日志崩溃恢复的依据循环写 # binlog.000001 归档日志主从复制与按时间点恢复的依据innodb_file_per_table这个开关决定了一表一文件还是全部塞进ibdata1。打开的好处是单表可以独立备份、TRUNCATE能真正把空间还给操作系统、单表损坏不影响别人代价是文件句柄变多且元数据仍然在共享表空间里光拷.ibd文件不能直接恢复一张表。这又是一处典型的取舍。3.3 连接、解析、优化、执行一条 SQL 走完的四道关报告里列了连接池组件、SQL 接口、查询解析器、查询优化器、可插拔存储引擎这串名词如果只是背下来考试完就忘了。放到一条真实查询上看就活了-- 走一遍完整链路的查询 EXPLAIN SELECT s.Sname, c.Cname, sc.Grade FROM SC sc JOIN Students s ON s.Sno sc.Sno JOIN Courses c ON c.Cno sc.Cno WHERE sc.Grade 60;连接层先做认证和权限检查决定这个连接能不能碰这几张表解析器做词法分析和语法分析把字符串变成语法树这一步只关心写得对不对不关心快不快优化器拿到语法树后做逻辑等价变换谓词下推、外连接转内连接、子查询解嵌套再用成本模型估算每种执行计划的代价——它依据的是统计信息里的行数估算和索引基数不是精确值执行器按选定的计划逐行向存储引擎要数据引擎负责在页和索引上定位。EXPLAIN输出的几个字段直接对应这条链路type是访问方式ALL全表扫、index全索引扫、range范围扫、ref非唯一索引等值、eq_ref唯一索引等值key是最终选中的索引rows是优化器估算要扫的行数Extra里的Using where表示引擎返回后还要过滤、Using index表示覆盖索引不用回表、Using filesort表示排序没走索引。看懂这四个字段就基本能判断一条慢查询卡在哪一道关。4. 建库建表建索引DDL 里每个字都在给后面的行为设限4.1 char 与 varchar、主键与外键的层次差别CHAR(9)和VARCHAR(20)的差别不只是定长变长四个字。CHAR会按声明长度预留空间并右补空格取出来时又要把尾部空格去掉VARCHAR前面带 12 字节的长度前缀实际占用按内容算。在 utf8mb4 下CHAR(9)最坏要占 36 字节每字符 4 字节而它带来的好处是定长行在页内定位更快。学号这种长度绝对固定的字段用CHAR合理姓名、专业这种长度浮动大的字段用VARCHAR更省空间。真正决定数据物理排列的是主键。默认存储引擎里主键索引就是聚簇索引整张表的数据行按主键顺序存在 B 树的叶子页上没有独立的数据文件。这意味着两件事——主键不能太大因为每个二级索引的叶子节点都要存一份主键主键最好单调递增否则随机插入会导致页分裂写入性能断崖式下降。用随机字符串当主键是血泪经验里出现频率极高的一条。-- 建表时每个选择都在约束后面的行为 CREATE TABLE Students ( Sno CHAR(9) PRIMARY KEY, -- 定长、唯一天然做主键 Sname VARCHAR(20) NOT NULL, -- 变长省空间 Sex CHAR(2) CHECK (Sex IN (男,女)), Birthday DATE, -- 用 DATE绝不用字符串存日期 Speciality VARCHAR(20), Dno CHAR(4), KEY idx_student_dno (Dno), -- 外键列必须有索引 CONSTRAINT fk_student_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;外键列为什么要显式建索引因为外键检查是双向的往子表插入时要确认父表存在对应行走父表主键没问题删改父表时要确认子表没有引用要走子表的外键列。后者如果没有索引就只能全表扫子表而且是在持锁状态下扫。很多人以为外键很慢问题往往不在外键本身而在漏了这个索引。还有两处容易忽略的一致性要求外键列和被引用列的类型、长度、字符集必须完全一致CHAR(4)对VARCHAR(4)会被拒绝父表被引用的列必须是索引的最左列通常是主键或唯一键。4.2 索引不是加速开关B 树、回表与选择性实验里在Dno上建了索引然后查一次数据发现好像没变快于是得出索引没用的结论。这是数据量太小造成的错觉。先算一笔账页 16KB假设每行 1KB一页存 16 行非叶子节点存的是主键 页指针大约 14 字节一组一页能放约 1100 个指针。三层 B 树能索引的行数约是 1100 × 1100 × 16 ≈ 1900 万行也就是说近两千万行的表等值查询只需要三次页访问。但当表里只有 6 行、全在同一个页里时全表扫描的成本和走索引的成本几乎一样优化器会果断选择全表扫——它的成本模型没错是数据不配合。第二个必须搞清的概念是回表。聚簇索引的叶子存整行二级索引的叶子只存索引列 主键值。通过二级索引找到主键后还要再拿主键去聚簇索引里捞整行这就是回表一次查询可能变成两倍甚至更多的页访问。如果查询要的列全在索引里比如SELECT Dno FROM Students WHERE DnoCS就不需要回表EXPLAIN的Extra会显示Using index这叫覆盖索引。-- 造数据前后各看一次执行计划差别非常直观 -- 6 行时typeALLkeyNULLrows6 -- 10 万行时typerefkeyidx_student_dnorows1240 EXPLAIN SELECT * FROM Students WHERE Dno CS; -- 覆盖索引只取索引列Extra 出现 Using index不回表 EXPLAIN SELECT Dno FROM Students WHERE Dno CS;第三个概念是最左前缀。联合索引(Sno, Cno)的排序规则是先按Sno再按Cno所以WHERE Sno?能用上WHERE Cno?用不上WHERE Sno? AND Cno?能用满。这个规则不是实现细节是 B 树排序的必然结果——索引只能按它被定义的顺序排列。索引还有维护成本每次插入、更新、删除都要同步改所有相关索引的 B 树。一张表上堆十几个索引写入性能会明显下降。判断一个索引值不值得建看选择性——即不同值的个数 / 总行数越接近 1 越适合。性别这种只有两三个值的列建索引扫出来的行占总行数一大半优化器基本不会用。4.3 建表顺序与级联删除DDL 也有依赖图外键把表之间的关系从逻辑上有关联变成了物理上有依赖于是 DDL 的执行顺序被约束住了。建表必须先父后子删表必须反过来先子后父。报告里的处理方式是先建三张表、插完数据再用ALTER TABLE ... ADD FOREIGN KEY补上约束——这个顺序是聪明的因为补约束的时候父表数据已经在了校验能一次通过如果先加约束再插数据任何一条顺序错误的插入都会失败。-- 依赖图Departments 是根Students/Teachers 指向它SC 指向 Students 和 Courses ALTER TABLE Students ADD CONSTRAINT fk_s_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno); -- 父表必须先存在且有数据 ALTER TABLE SC ADD CONSTRAINT fk_sc_student FOREIGN KEY (Sno) REFERENCES Students(Sno);删表这条路上有个经典坑DROP TABLE students CASCADE看着像连带删掉所有引用它的表实际上在主流 MySQL 兼容发行版里CASCADE关键字会被语法解析器接受然后直接忽略真正的行为等价于RESTRICT一旦有子表引用就报 1451 错误。这个关键字是从别的数据库方言带过来的习惯抄过来不会报语法错但也不会生效属于那种写错了却不告诉你的坑。5. 排查五条踩坑记录与它们的因果链5.1 服务侧起不来和连不上是两件完全不同的事踩坑一systemctl start之后状态立刻变成 failed。现象是命令返回没有明显报错systemctl status显示Active: failed日志里一行Cant create/write to file /var/lib/gdb/xxx (Errcode: 13 - Permission denied)但ls -l看权限是 777。 原因是进程以mysql账号运行而那个路径的属主是 root或者属主对了但 SELinux 的文件类型标签不对强制访问控制把它拦下来了。这两种原因报同一句话靠ls -l分不出来。 解决办法是先看ls -ld确认属主属主没问题就用ls -Z看标签缺标签就semanage fcontext加restorecon补实在排查不动就临时setenforce 0验证是不是它干的确认后再改回 enforcing 做精细化放行。踩坑二服务状态 active本机能连远程连不上。现象是远程客户端一直超时ping通、端口 telnet 不通。 原因有两层外层是防火墙没放行 3306 入站内层是my.cnf里bind-address127.0.0.1只监听回环地址。前者现象是连接被拒绝或超时后者现象是本机ss -lntp能看到监听但地址是127.0.0.1:3306。 解决办法是先用ss -lntp | grep 3306看监听地址再决定是改bind-address还是开防火墙端口两个都确认过再让客户端重试。踩坑三连接数一到某个值就报 Too many open files。现象是并发上来之后新连接全部失败错误指向文件描述符而不是连接数。 原因是每个连接在服务端占一个 fd而进程的 fd 上限由 systemd 的LimitNOFILE决定默认值往往只有 1024比配置文件里的max_connections小得多。 解决办法是在单元文件的[Service]段把LimitNOFILE提到 65535然后systemctl daemon-reload加systemctl restart光改配置文件不重启进程没用。5.2 数据侧写不进去和查不出来踩坑四日期插入报 Incorrect date value。现象是INSERT INTO Students VALUES(..., 1999_12_16, ...)直接失败提示日期值非法。 原因是DATE类型的字面量分隔符必须是-下划线不是合法分隔符。在严格 SQL 模式下这会直接报错在非严格模式下更阴险——它不报错而是插进去一个0000-00-00或者NULL等到后面YEAR(Birthday)算出NULL时你已经忘了问题出在插入那一步。 解决办法是统一用1999-12-16格式或者在插入时用STR_TO_DATE(1999_12_16,%Y_%m_%d)显式转换把格式问题暴露在转换函数这一层而不是让它悄悄变成脏数据。踩坑五NOT IN查出来永远是空集。现象是WHERE Speciality NOT IN (SELECT Speciality FROM ...)这条子查询里只要有一个NULL整个结果集就空了。 原因是 SQL 的三值逻辑x NOT IN (a, NULL)展开成xa AND xNULL而任何值和NULL比较的结果是UNKNOWN而不是TRUE整个AND链就永远不成立。 解决办法是在子查询里加WHERE Speciality IS NOT NULL把NULL排掉或者改用NOT EXISTS后者的语义是不存在满足条件的行天然不受NULL影响。还有一条不算踩坑但值得记2024 - YEAR(Birthday)算出来的是年份差不是周岁生日没过的人会多算一岁。要精确就得用TIMESTAMPDIFF(YEAR, Birthday, CURDATE())它会把月日一起算进去。6. 取舍从实验报告的编号规范看可复现性实验报告要求截图下方标图号图题、表格上方标表名编号、段首空两格、行文用书面语——这些规矩看着像形式主义其实指向一个工程习惯让结果可复现、可引用、可回查。一张没有编号的截图在我当初到底配了什么这个问题面前等于不存在一条没有记录sql_mode的报错隔一周就再也复现不出来。这套习惯在真实排障里价值极高。数据库问题的一大特征是环境和时序强相关——同一句 SQL数据量不同、索引不同、统计信息新旧不同执行计划就不同。所以验一个猜想时我一般会固定三样东西当前的sql_mode、当前表的行数与索引清单、以及那次查询的EXPLAIN输出。只留一句我执行过很慢等于什么都没留。同样的取舍思路贯穿整个实验。关 SELinux 和防火墙换来的是操作顺畅付出的是整机防护缺失做法省事程度代价适用场景关闭 SELinux 停防火墙高一条命令搞定强制访问控制与入站过滤全部失效单机隔离实验环境只加文件标签 只放行端口中要理解标签和端口类型配错了同样起不来报错还更隐蔽多人共用或长期运行的环境CHAR与VARCHAR的选择也是同一类取舍定长换来更快的行定位变长换来更省的存储没有普适答案只有这张表的读写比例和字段长度分布。索引更典型——建索引加速读、拖慢写优化器在两者之间用统计信息做权衡你能做的是别在选择性极低的列上浪费索引位。验证这些取舍有个笨办法但很有效把数据造到十万行以上再跑一次EXPLAIN对比type和rows两个字段。样本量不够的时候任何关于索引和优化器的结论都不可信——这也是我在实验阶段最容易自我欺骗的地方六行数据的表上得出索引无用的结论然后在真正的项目里被慢查询教做人。从那以后我每次评估索引或者执行计划都强制先造够数据再看EXPLAIN宁可多花十分钟准备环境也不拿小样本骗自己。希望帮到你。本文还有配套的精品资源点击获取
简介面向郑州大学计算机与人工智能学院《数据库系统原理》课程学习者这份实验报告围绕DBMS系统认识与万里数据库GreatSQL的安装、启动、管理和使用展开适合需要完成课内实验、整理实验记录或复盘数据库环境配置的读者参考。压缩包内仅含1个docx文档大小约3.69MB内容按实验目的、实验内容、实验类型、学时分配、仪器材料、实验过程与结果分析组织并涉及关闭SELinux和防火墙、安装依赖包、下载解压二进制包、了解基本表/视图/触发器/存储过程/约束等逻辑组件及物理存储组件、截图编号和表格规范等环节。已有509人学习可作为实验报告撰写模板与GreatSQL入门操作对照材料帮助读者梳理从环境搭建、服务管理到结果反思的完整实验链路理解常见问题、处理思路与报告排版要求也便于按课程实验评分点自查内容完整性。1. 装一套数据库真正装的是什么很多人以为实验一的核心是装软件解压、敲几条命令、服务起来就算收工。实际相反解压那一步在整个流程里技术含量最低。真正被建立起来的是一份三方约定——一个以低权限账号运行的常驻进程、一套固定布局的数据目录、一组允许它读写的系统权限边界。这三样里任何一项没对齐后面做数据库原理实验时那些看起来跟表毫无关系的报错就会冒出来。搞懂这份约定你才明白为什么关掉某个强制访问控制模块服务就能起来、为什么数据目录属主错了初始化永远失败。这份实验报告的价值不在于它记了多少条命令而在于它把九个实验串成了一条从进程到数据页、从 DDL 到查询执行链路的完整观察路径。2. 从 systemctl start 到常驻进程一个数据库服务是怎么被拉起来的2.1 单元文件真正决定服务生死的那几十行二进制包解压完很多人直接敲mysqld手动跑起来看到提示符能连上就以为装好了。这和注册成系统服务是两回事。手动跑的进程挂在前台终端上终端一关进程跟着走注册成服务之后它被交给 init 系统托管才有了崩溃重启、开机自启、日志统一收集这些能力。在主流 Linux 发行版上这份托管关系写在单元文件里。实验报告里那条vim /lib/systemd/system/greatsql.service编辑的就是它但报告只写了创建这个文件没写清楚每个字段在管什么# /lib/systemd/system/gdb.service —— 服务单元文件的关键字段GDB 代指实验所用发行版 [Unit] DescriptionGDB Server Afternetwork.target # 网络栈就绪后再启动避免监听失败 [Service] Typesimple # mysqld 不 fork前台常驻systemd 直接盯住主进程 Usermysql # 关键用低权限账号运行不用 root Groupmysql ExecStart/usr/local/gdb/bin/mysqld --defaults-file/etc/my.cnf ExecReload/bin/kill -HUP $MAINPID LimitNOFILE65535 # 每个连接占一个文件描述符这里先设上限 Restarton-failure # 非正常退出时自动拉起 [Install] WantedBymulti-user.target # 挂到多用户运行级等价于开机启动Typesimple决定了 systemd 认哪个进程是服务本体。如果写成Typeforking就必须再给一个PIDFile否则 systemd 会以为服务启动超时然后把它杀掉——这是二进制包安装最常见的翻车点之一。Usermysql是安全边界数据库进程只需要读写自己那套目录没必要拿 root 的全盘权限一旦被利用损失范围被限制在数据目录内。LimitNOFILE则是一个隐蔽的前置约束连接数上限并不先由max_connections卡住而是先被文件描述符数量卡住表现是连接建立到某个数量后突然报 Too many open files而配置文件里的连接数明明还差得远。改完单元文件必须执行systemctl daemon-reload。原因是 systemd 启动时把单元文件解析成内存里的对象缓存之后的start操作走的是缓存不 reload 等于文件白改——这个行为没有提示、没有警告只有我明明改了配置怎么没生效的困惑。2.2 关 SELinux 与防火墙被误读最深的两条命令实验指导里几乎都会写关闭 SELinux 和防火墙报告里给出的理由也很像回事安全性、简化配置、性能。但把机制拆开看这两件事解决的根本不是同一个问题。SELinux 是一套强制访问控制MAC机制。传统 Linux 权限看的是你是谁、文件属主是谁、权限位怎么给SELinux 在之上又加了一层即便你是 root、文件权限 777只要进程的域和文件的类型标签不匹配照样拒绝。数据库二进制包解压到自定义目录时这些文件继承的是父目录的默认标签不是数据库专用类型于是进程启动时读写数据目录直接被拦。报错通常是Permission denied或Errcode: 13而ls -l看上去权限完全正常——这就是它最坑的地方普通权限位和强制访问控制是两套独立的判定。防火墙解决的是另一个维度入站 TCP 连接能不能到达监听端口。数据库默认监听 3306防火墙没放行时本机mysql客户端用 socket 能连上远程客户端却一直超时。这个现象很有迷惑性——服务明明活着进程也在ss -lntp也能看到监听就是连不上。真正的取舍在于关掉是最省事的但代价是把整台机器的这两层防护一起拆了。更克制的做法是只放行需要的那一点点# 只给数据目录打上正确的文件类型标签而不是关掉整个 MAC 机制 semanage fcontext -a -t mysqld_db_t /var/lib/gdb(/.*)? restorecon -Rv /var/lib/gdb # 只放行数据库端口而不是停掉防火墙 semanage port -a -t mysqld_port_t -p tcp 3306 firewall-cmd --permanent --add-port3306/tcp firewall-cmd --reload # 确认端口真的在监听以及监听在哪个地址上 ss -lntp | grep mysqld # LISTEN 0 151 *:3306 *:* users:((mysqld,pid1234,fd21))ss输出里*:3306和127.0.0.1:3306是完全不同的两件事后者只接受本机连接远程怎么连都是超时而这跟防火墙一点关系都没有是bind-address配置的问题。实验环境里常把这两类连不上混为一谈排查时白白绕一大圈。2.3 账号、目录属主与初始化进程拿什么身份写文件报告里那条useradd -g mysql mysql -d /dev/null -s /sbin/nologin值得单独说。-d /dev/null把家目录指到一个不存在的设备-s /sbin/nologin把登录 shell 关掉——这两个参数合起来的语义是这个账号不是给人用的只是给进程一个可归属的 UID。系统里所有以它身份运行的进程权限上限就锁死在这个 UID 能碰到的范围内。紧接着的chown -R mysql:mysql /var/lib/mysql决定了数据目录的归属。二进制包安装时数据目录是空的第一次启动要做初始化建系统表空间、写初始账号如果目录属主还是 root初始化进程以 mysql 身份去写就会失败。这类失败有一个共同特征报错信息里带Errcode: 13或者Cant create/write to file但错误里出现的文件路径往往不是你正在想的那一个可能是临时目录、可能是日志目录、也可能是 socket 文件所在目录——凡是进程要写的地方属主都得对。所以我一般会把datadir、log-error、socket、pid-file这四个路径一起检查而不是只 chown 数据目录。目录权限本身也有讲究。数据目录通常是700意味着除了属主其他任何账号连列目录都不行。这不是洁癖数据文件里存的是明文行记录能读文件就等于能读全库绕过了所有 SQL 层的权限检查。3. 逻辑组件与物理组件一条 SQL 的两套坐标3.1 五个逻辑组件的元数据落点报告把基本表、视图、触发器、存储过程、约束列为逻辑组件这个分类是对的但把它当成五个名词背下来毫无意义。它们真正的差别在于元数据存在哪里、什么时候被执行。基本表是唯一真正持有行数据的对象视图只是一条被存起来的查询语句本身不占数据页触发器是绑定在某张表某个事件上的动作由 DML 隐式触发存储过程是命名好的语句块由调用显式触发约束是数据字典里的规则由写入路径强制执行。这五者在information_schema里各有自己的落点-- 逻辑组件在数据字典里的位置把 db 换成自己的库名 SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA db; -- BASE TABLE 与 VIEW 都在这里 SELECT TABLE_NAME, VIEW_DEFINITION FROM information_schema.VIEWS WHERE TABLE_SCHEMA db; -- 视图只存定义没有数据行 SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA db; -- 触发器绑在哪张表、哪个时机 SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA db AND ROUTINE_TYPE PROCEDURE; SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA db; -- 主键、外键、唯一约束都在这里有个细节挺有意思报告里查视图用的命令是SHOW FULL PROCESSLIST这条命令返回的其实是当前的连接线程列表跟视图没有半点关系。这个混淆恰好说明了视图和表的本质区别——视图没有物理实体可查你能查到的只有它的定义。正确的入口是information_schema.VIEWS或者SHOW FULL TABLES WHERE Table_typeVIEW。把这几张字典表连起来看会得到一个关键认知数据库的所有结构本身也是数据也存在表里也走同一条查询路径。这就是为什么改表结构要加元数据锁也是为什么 DDL 在大表上会卡住整张表的读写。3.2 数据页、表空间与独立表文件数据到底存在哪物理层的第一个事实是存储单位不是行是页。默认页大小 16KB一次磁盘 I/O 最少读一页哪怕你只要一行。第二个事实是页之上的组织单位连续 64 个页组成一个区extent约 1MB区再往上组成段段再组成表空间表空间最后落到磁盘文件上。一个页内部的布局大致分四块页头存页号、页类型、校验和、前后页指针、行记录区真正的数据行按主键顺序排列、空闲空间给后续插入留的、页目录每几行一个槽位用于页内二分查找。这个结构直接解释了一个常见困惑为什么删了大量数据表文件大小一点没变。删除只是在行记录上打个删除标记空间留在页内部只有整页都空了才可能被回收而且回收后文件通常也不会自动缩小。# 数据目录里能看到几类完全不同的文件 ls -lh /var/lib/gdb/ # ibdata1 系统表空间数据字典、回滚段等共享结构 # db/students.ibd 独立表空间开启 file_per_table 后一表一文件 # #innodb_redo/ 重做日志崩溃恢复的依据循环写 # binlog.000001 归档日志主从复制与按时间点恢复的依据innodb_file_per_table这个开关决定了一表一文件还是全部塞进ibdata1。打开的好处是单表可以独立备份、TRUNCATE能真正把空间还给操作系统、单表损坏不影响别人代价是文件句柄变多且元数据仍然在共享表空间里光拷.ibd文件不能直接恢复一张表。这又是一处典型的取舍。3.3 连接、解析、优化、执行一条 SQL 走完的四道关报告里列了连接池组件、SQL 接口、查询解析器、查询优化器、可插拔存储引擎这串名词如果只是背下来考试完就忘了。放到一条真实查询上看就活了-- 走一遍完整链路的查询 EXPLAIN SELECT s.Sname, c.Cname, sc.Grade FROM SC sc JOIN Students s ON s.Sno sc.Sno JOIN Courses c ON c.Cno sc.Cno WHERE sc.Grade 60;连接层先做认证和权限检查决定这个连接能不能碰这几张表解析器做词法分析和语法分析把字符串变成语法树这一步只关心写得对不对不关心快不快优化器拿到语法树后做逻辑等价变换谓词下推、外连接转内连接、子查询解嵌套再用成本模型估算每种执行计划的代价——它依据的是统计信息里的行数估算和索引基数不是精确值执行器按选定的计划逐行向存储引擎要数据引擎负责在页和索引上定位。EXPLAIN输出的几个字段直接对应这条链路type是访问方式ALL全表扫、index全索引扫、range范围扫、ref非唯一索引等值、eq_ref唯一索引等值key是最终选中的索引rows是优化器估算要扫的行数Extra里的Using where表示引擎返回后还要过滤、Using index表示覆盖索引不用回表、Using filesort表示排序没走索引。看懂这四个字段就基本能判断一条慢查询卡在哪一道关。4. 建库建表建索引DDL 里每个字都在给后面的行为设限4.1 char 与 varchar、主键与外键的层次差别CHAR(9)和VARCHAR(20)的差别不只是定长变长四个字。CHAR会按声明长度预留空间并右补空格取出来时又要把尾部空格去掉VARCHAR前面带 12 字节的长度前缀实际占用按内容算。在 utf8mb4 下CHAR(9)最坏要占 36 字节每字符 4 字节而它带来的好处是定长行在页内定位更快。学号这种长度绝对固定的字段用CHAR合理姓名、专业这种长度浮动大的字段用VARCHAR更省空间。真正决定数据物理排列的是主键。默认存储引擎里主键索引就是聚簇索引整张表的数据行按主键顺序存在 B 树的叶子页上没有独立的数据文件。这意味着两件事——主键不能太大因为每个二级索引的叶子节点都要存一份主键主键最好单调递增否则随机插入会导致页分裂写入性能断崖式下降。用随机字符串当主键是血泪经验里出现频率极高的一条。-- 建表时每个选择都在约束后面的行为 CREATE TABLE Students ( Sno CHAR(9) PRIMARY KEY, -- 定长、唯一天然做主键 Sname VARCHAR(20) NOT NULL, -- 变长省空间 Sex CHAR(2) CHECK (Sex IN (男,女)), Birthday DATE, -- 用 DATE绝不用字符串存日期 Speciality VARCHAR(20), Dno CHAR(4), KEY idx_student_dno (Dno), -- 外键列必须有索引 CONSTRAINT fk_student_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;外键列为什么要显式建索引因为外键检查是双向的往子表插入时要确认父表存在对应行走父表主键没问题删改父表时要确认子表没有引用要走子表的外键列。后者如果没有索引就只能全表扫子表而且是在持锁状态下扫。很多人以为外键很慢问题往往不在外键本身而在漏了这个索引。还有两处容易忽略的一致性要求外键列和被引用列的类型、长度、字符集必须完全一致CHAR(4)对VARCHAR(4)会被拒绝父表被引用的列必须是索引的最左列通常是主键或唯一键。4.2 索引不是加速开关B 树、回表与选择性实验里在Dno上建了索引然后查一次数据发现好像没变快于是得出索引没用的结论。这是数据量太小造成的错觉。先算一笔账页 16KB假设每行 1KB一页存 16 行非叶子节点存的是主键 页指针大约 14 字节一组一页能放约 1100 个指针。三层 B 树能索引的行数约是 1100 × 1100 × 16 ≈ 1900 万行也就是说近两千万行的表等值查询只需要三次页访问。但当表里只有 6 行、全在同一个页里时全表扫描的成本和走索引的成本几乎一样优化器会果断选择全表扫——它的成本模型没错是数据不配合。第二个必须搞清的概念是回表。聚簇索引的叶子存整行二级索引的叶子只存索引列 主键值。通过二级索引找到主键后还要再拿主键去聚簇索引里捞整行这就是回表一次查询可能变成两倍甚至更多的页访问。如果查询要的列全在索引里比如SELECT Dno FROM Students WHERE DnoCS就不需要回表EXPLAIN的Extra会显示Using index这叫覆盖索引。-- 造数据前后各看一次执行计划差别非常直观 -- 6 行时typeALLkeyNULLrows6 -- 10 万行时typerefkeyidx_student_dnorows1240 EXPLAIN SELECT * FROM Students WHERE Dno CS; -- 覆盖索引只取索引列Extra 出现 Using index不回表 EXPLAIN SELECT Dno FROM Students WHERE Dno CS;第三个概念是最左前缀。联合索引(Sno, Cno)的排序规则是先按Sno再按Cno所以WHERE Sno?能用上WHERE Cno?用不上WHERE Sno? AND Cno?能用满。这个规则不是实现细节是 B 树排序的必然结果——索引只能按它被定义的顺序排列。索引还有维护成本每次插入、更新、删除都要同步改所有相关索引的 B 树。一张表上堆十几个索引写入性能会明显下降。判断一个索引值不值得建看选择性——即不同值的个数 / 总行数越接近 1 越适合。性别这种只有两三个值的列建索引扫出来的行占总行数一大半优化器基本不会用。4.3 建表顺序与级联删除DDL 也有依赖图外键把表之间的关系从逻辑上有关联变成了物理上有依赖于是 DDL 的执行顺序被约束住了。建表必须先父后子删表必须反过来先子后父。报告里的处理方式是先建三张表、插完数据再用ALTER TABLE ... ADD FOREIGN KEY补上约束——这个顺序是聪明的因为补约束的时候父表数据已经在了校验能一次通过如果先加约束再插数据任何一条顺序错误的插入都会失败。-- 依赖图Departments 是根Students/Teachers 指向它SC 指向 Students 和 Courses ALTER TABLE Students ADD CONSTRAINT fk_s_dept FOREIGN KEY (Dno) REFERENCES Departments(Dno); -- 父表必须先存在且有数据 ALTER TABLE SC ADD CONSTRAINT fk_sc_student FOREIGN KEY (Sno) REFERENCES Students(Sno);删表这条路上有个经典坑DROP TABLE students CASCADE看着像连带删掉所有引用它的表实际上在主流 MySQL 兼容发行版里CASCADE关键字会被语法解析器接受然后直接忽略真正的行为等价于RESTRICT一旦有子表引用就报 1451 错误。这个关键字是从别的数据库方言带过来的习惯抄过来不会报语法错但也不会生效属于那种写错了却不告诉你的坑。5. 排查五条踩坑记录与它们的因果链5.1 服务侧起不来和连不上是两件完全不同的事踩坑一systemctl start之后状态立刻变成 failed。现象是命令返回没有明显报错systemctl status显示Active: failed日志里一行Cant create/write to file /var/lib/gdb/xxx (Errcode: 13 - Permission denied)但ls -l看权限是 777。 原因是进程以mysql账号运行而那个路径的属主是 root或者属主对了但 SELinux 的文件类型标签不对强制访问控制把它拦下来了。这两种原因报同一句话靠ls -l分不出来。 解决办法是先看ls -ld确认属主属主没问题就用ls -Z看标签缺标签就semanage fcontext加restorecon补实在排查不动就临时setenforce 0验证是不是它干的确认后再改回 enforcing 做精细化放行。踩坑二服务状态 active本机能连远程连不上。现象是远程客户端一直超时ping通、端口 telnet 不通。 原因有两层外层是防火墙没放行 3306 入站内层是my.cnf里bind-address127.0.0.1只监听回环地址。前者现象是连接被拒绝或超时后者现象是本机ss -lntp能看到监听但地址是127.0.0.1:3306。 解决办法是先用ss -lntp | grep 3306看监听地址再决定是改bind-address还是开防火墙端口两个都确认过再让客户端重试。踩坑三连接数一到某个值就报 Too many open files。现象是并发上来之后新连接全部失败错误指向文件描述符而不是连接数。 原因是每个连接在服务端占一个 fd而进程的 fd 上限由 systemd 的LimitNOFILE决定默认值往往只有 1024比配置文件里的max_connections小得多。 解决办法是在单元文件的[Service]段把LimitNOFILE提到 65535然后systemctl daemon-reload加systemctl restart光改配置文件不重启进程没用。5.2 数据侧写不进去和查不出来踩坑四日期插入报 Incorrect date value。现象是INSERT INTO Students VALUES(..., 1999_12_16, ...)直接失败提示日期值非法。 原因是DATE类型的字面量分隔符必须是-下划线不是合法分隔符。在严格 SQL 模式下这会直接报错在非严格模式下更阴险——它不报错而是插进去一个0000-00-00或者NULL等到后面YEAR(Birthday)算出NULL时你已经忘了问题出在插入那一步。 解决办法是统一用1999-12-16格式或者在插入时用STR_TO_DATE(1999_12_16,%Y_%m_%d)显式转换把格式问题暴露在转换函数这一层而不是让它悄悄变成脏数据。踩坑五NOT IN查出来永远是空集。现象是WHERE Speciality NOT IN (SELECT Speciality FROM ...)这条子查询里只要有一个NULL整个结果集就空了。 原因是 SQL 的三值逻辑x NOT IN (a, NULL)展开成xa AND xNULL而任何值和NULL比较的结果是UNKNOWN而不是TRUE整个AND链就永远不成立。 解决办法是在子查询里加WHERE Speciality IS NOT NULL把NULL排掉或者改用NOT EXISTS后者的语义是不存在满足条件的行天然不受NULL影响。还有一条不算踩坑但值得记2024 - YEAR(Birthday)算出来的是年份差不是周岁生日没过的人会多算一岁。要精确就得用TIMESTAMPDIFF(YEAR, Birthday, CURDATE())它会把月日一起算进去。6. 取舍从实验报告的编号规范看可复现性实验报告要求截图下方标图号图题、表格上方标表名编号、段首空两格、行文用书面语——这些规矩看着像形式主义其实指向一个工程习惯让结果可复现、可引用、可回查。一张没有编号的截图在我当初到底配了什么这个问题面前等于不存在一条没有记录sql_mode的报错隔一周就再也复现不出来。这套习惯在真实排障里价值极高。数据库问题的一大特征是环境和时序强相关——同一句 SQL数据量不同、索引不同、统计信息新旧不同执行计划就不同。所以验一个猜想时我一般会固定三样东西当前的sql_mode、当前表的行数与索引清单、以及那次查询的EXPLAIN输出。只留一句我执行过很慢等于什么都没留。同样的取舍思路贯穿整个实验。关 SELinux 和防火墙换来的是操作顺畅付出的是整机防护缺失做法省事程度代价适用场景关闭 SELinux 停防火墙高一条命令搞定强制访问控制与入站过滤全部失效单机隔离实验环境只加文件标签 只放行端口中要理解标签和端口类型配错了同样起不来报错还更隐蔽多人共用或长期运行的环境CHAR与VARCHAR的选择也是同一类取舍定长换来更快的行定位变长换来更省的存储没有普适答案只有这张表的读写比例和字段长度分布。索引更典型——建索引加速读、拖慢写优化器在两者之间用统计信息做权衡你能做的是别在选择性极低的列上浪费索引位。验证这些取舍有个笨办法但很有效把数据造到十万行以上再跑一次EXPLAIN对比type和rows两个字段。样本量不够的时候任何关于索引和优化器的结论都不可信——这也是我在实验阶段最容易自我欺骗的地方六行数据的表上得出索引无用的结论然后在真正的项目里被慢查询教做人。从那以后我每次评估索引或者执行计划都强制先造够数据再看EXPLAIN宁可多花十分钟准备环境也不拿小样本骗自己。希望帮到你。本文还有配套的精品资源点击获取
