06 · 字符集、权限与运行维护

对应第 27～30 章。使用本站练习库，按需执行操作。账号管理需要管理员权限，备份与恢复要使用专用实验目标。

到这里已经能写查询和事务，但一个数据库是否可用，还取决于文字能否正确存储、账号能否只做被允许的事、数据能否从备份恢复，以及查询成本是否可解释。本单元把这些问题分别拆成检查步骤和实验，不用「配置好了」代替验证。

第 27 章：编码与比较规则是两件事

USE showdocs_mysql_book;
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW CREATE TABLE courses;
SELECT CHAR_LENGTH('数据库') AS characters, LENGTH('数据库') AS bytes;

连接采用 UTF-8 时，最后一条预期是 3 个字符、9 个字节。字符集决定编码，校对规则决定排序和相等比较。练习库的 utf8mb4_0900_ai_ci 不区分重音和大小写，因此文本相等不一定意味着字节相同。

表定义采用 utf8mb4 还不够，客户端连接也要正确传递字符。遇到乱码时，分别检查输入、连接和存储层；不要直接对已有数据反复转换编码。

一段中文经过哪些层

输入文件或终端先把字符编码为字节，客户端告诉服务器如何解释收到的字节，服务器转换为目标列的字符集保存，查询时再按结果连接的字符集返回。任何一层声明与实际字节不一致，都可能产生乱码或转换失败。

层次	常见检查项	影响范围
客户端输入	文件编码、终端编码、驱动配置	SQL 文本与参数最初是什么字节
连接	character_set_client、character_set_connection、character_set_results	输入解释、表达式处理与结果返回
数据库默认值	SHOW CREATE DATABASE showdocs_mysql_book	新建表未单独指定时继承的默认值
表默认值	SHOW CREATE TABLE courses	新建文本列未单独指定时的默认值
列	SHOW FULL COLUMNS FROM courses	已有字段实际使用的字符集与校对规则

修改数据库默认字符集，不会把所有已存在的列和历史数据自动转换一遍。已有列转换需单独评估数据、索引长度、锁和应用编码。本例从建库就使用 utf8mb4，避免把已弃用的 utf8mb3 与完整 UTF-8 能力混淆。

长度与比较可以分开验证

CHAR_LENGTH 数字符，LENGTH 数字节。在 utf8mb4 下常用汉字占 3 字节，部分字符占 4 字节，因此不能把「VARCHAR(100)」简单解释为「最多 100 字节」。表和索引仍有额外的字节限制，字符长度与存储开销应分别判断。

下面用显式字符集引入符与校对规则，避免依赖连接的默认比较方式：

SELECT
  _utf8mb4'A' COLLATE utf8mb4_0900_ai_ci = _utf8mb4'a' AS same_case_ci,
  _utf8mb4'e' COLLATE utf8mb4_0900_ai_ci = _utf8mb4'é' AS same_accent_ai,
  _utf8mb4'A' COLLATE utf8mb4_0900_as_cs = _utf8mb4'a' AS same_case_cs;

结果为 1 / 1 / 0。ai 表示重音不敏感，ci 表示大小写不敏感；as_cs 则分别敏感。校对规则还影响排序、分组和唯一键：在不区分大小写的唯一列中，SQL 与 sql 可能被视为冲突。

对密码哈希、令牌等需要精确匹配的值，不应直接套用自然语言文本的比较规则。应结合数据性质选择合适的二进制类型或比较方式，而不是依赖默认大小写行为。

乱码排查顺序

先确认源文本本身正确，再核对客户端实际编码与连接变量，然后查看列定义。必要时查询 HEX(column_name) 检查存储字节，并与正确样本比较。若数据库存的是正确字节而终端显示错误，修改表字符集只会增加新问题；若坏字节已经被错误解释后写入，则需要明确原始编码和转换路径才能修复。

第 28 章：给账号分配恰当权限

以下仅在专用实验实例中由管理员运行。MySQL 8.0.18 及以上支持随机密码；记录返回的密码，用于之后连接，再及时清理实验账号：

CREATE USER 'book_reader'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
GRANT SELECT ON showdocs_mysql_book.* TO 'book_reader'@'localhost';
SHOW GRANTS FOR 'book_reader'@'localhost';

在同一台数据库主机上，以该账号新建连接并确认 CURRENT_USER()。读取课程表应成功，写入课程表应被拒绝。'用户名'@'主机' 共同确定账号；远程容器与本机连接的来源可能不同，应按真实来源配置。

应用不要共用管理员账号。MySQL 8 中先建用户再授权，不要照搬旧版本通过 GRANT ... IDENTIFIED BY 建账号的写法。

账号名还包括来源主机

'book_reader'@'localhost' 与 'book_reader'@'某个远程来源' 是不同账号。连接时提供同一个用户名，也可能因为连接来源不同而匹配不同授权记录。容器里的 localhost 指向容器自身，不是任意宿主机。

USER() 显示客户端提供的用户名与来源，CURRENT_USER() 表示服务器用于权限判断的账号。排查「明明授权了还是拒绝」时，先确认连到哪台实例、用哪个账号记录认证，再看授权范围。

在本机 mysql 客户端中新建读者连接，--password 后不写明文，使用刚生成的密码在提示时输入：

mysql --user=book_reader --password --database=showdocs_mysql_book

本机连接方式随系统和部署不同而变化；若显式配置 host、port 或协议，应确保仍匹配为该实验账号。在读者连接运行：

SELECT USER(), CURRENT_USER();
SHOW GRANTS;
SELECT course_id, title FROM courses ORDER BY course_id;

课程读取应成功。再单独尝试下面这条不改变价格的 UPDATE，预期仍被权限系统拒绝：

UPDATE courses SET price = price WHERE course_id = 101;

即使新旧值一样，执行 UPDATE 仍需要 UPDATE 权限；这能验证权限，而不依赖产生业务数据变化。测试后关闭读者连接，再回到管理员连接清理账号：

DROP USER 'book_reader'@'localhost';

授权范围与撤销

GRANT SELECT ON showdocs_mysql_book.* 允许读取该库中的表，并非只允许查 courses。更精细时可按表或列授权；多个应用、迁移脚本和备份任务也可使用不同账号，避免为了某一次管理操作扩大所有业务请求的权限。

REVOKE SELECT ON showdocs_mysql_book.* FROM 'book_reader'@'localhost' 撤销相应授权，DROP USER 删除账号定义；两者目的不同。通过 CREATE USER、GRANT、REVOKE 等账号管理语句修改权限后，通常不需要再执行 FLUSH PRIVILEGES。不要直接编辑系统授权表作为常规管理方式。

大量账号需要相同权限时可使用角色统一管理，但角色是否启用、是否设置为默认角色也影响实际会话权限。当前实验只有一个账号，用直接授权即可清楚验证，不额外引入角色配置。

第 29 章：备份之后要试恢复

在终端导出练习库，用户名替换为有备份所需权限的实验账号：

mysqldump --host=127.0.0.1 --user=YOUR_LAB_USER --password --single-transaction --no-tablespaces --set-gtid-purged=OFF --result-file=showdocs-mysql-book.sql showdocs_mysql_book

--result-file 避免不同终端重定向编码的差异；--single-transaction 适合 InnoDB 一致性数据快照，备份期间仍应避免并发 DDL。这里按前文完成清理后的三张练习表导出；真实业务若有存储过程、事件等对象，还要明确相应导出范围与权限。

先知道这个文件包含什么

这里的 mysqldump 是逻辑备份：把表结构与数据导出为可重新执行的 SQL，不是直接复制正在运行的数据库文件。它适合观察和演练，但大库恢复时间可能很长；物理备份、时间点恢复和高可用是另外需要设计的能力。

参数	本次用途	不应作出的推断
--single-transaction	为事务性表读取一致的数据快照	不保证 MyISAM 等非事务表一致，也不允许忽略并发 DDL
--no-tablespaces	不导出表空间相关语句，减少相应权限需求	不代表备份已不需要任何特殊权限
--set-gtid-purged=OFF	本次独立练习不向恢复目标写入源实例的 GTID 集合	不能直接当作所有复制拓扑的通用选项
--result-file=...	让工具直接创建输出文件	不保证磁盘空间充足、退出码成功或文件可恢复
最后的库名	导出当前练习库	未使用 --databases，文件不会据此自动切回原库名

触发器默认包含在 mysqldump 的导出范围中，存储过程和函数、事件则需要按需求使用 --routines、--events 等选项并核对权限。本教程在导出前已清理演示对象，备份范围应只有三张基础表；不要把这个简化范围当成真实系统对象已齐全的证明。

导出返回成功后，还应确认文件不为空、保存位置正确，并记录 MySQL 版本与备份时间。备份中可能包含业务数据，应按数据本身的访问要求保管；本例仅包含虚构练习数据。

在独立实验实例创建不同名称的恢复库，连接后通过 mysql 客户端加载文件：

CREATE DATABASE showdocs_mysql_book_restore
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE showdocs_mysql_book_restore;
SOURCE /absolute/path/showdocs-mysql-book.sql;
SELECT COUNT(*) AS registrations, SUM(paid) AS revenue FROM enrollments;

把路径改为实际文件位置，Windows 可写成 D:/backup/showdocs-mysql-book.sql。导出命令没有使用 --databases，因此恢复时由当前选中的库接收数据。预期报名记录 3 条、收入 157.00；还应核对三张表的结构、行数和约束。

恢复验收不能只看一条 COUNT

在恢复库执行以下查询：

SELECT DATABASE() AS restored_database;
SHOW TABLES;
SELECT (SELECT COUNT(*) FROM learners) AS learners,
       (SELECT COUNT(*) FROM courses) AS courses,
       (SELECT COUNT(*) FROM enrollments) AS registrations,
       (SELECT SUM(paid) FROM enrollments) AS revenue;
SELECT course_id, title, price FROM courses ORDER BY course_id;
SHOW CREATE TABLE enrollments;

检查点包括：当前库名确实是 showdocs_mysql_book_restore；有三张预期表；计数 3 / 4 / 3 与收入 157.00 一致；中文课程名可读；报名表的复合主键与两个外键仍在。真实系统还应验证对象、关键业务查询、恢复耗时和数据截止时间。

文件能下载、大小看着正常、导入命令没有立即报错，都不足以证明备份可用。恢复演练把「有备份」变成「能恢复到明确状态」，也暴露账号、版本、定义者、路径和编码等问题。

练习完成后可以保留恢复库用于比较。若要清理，仅在核对库名确实是本次专用恢复库后执行 DROP DATABASE showdocs_mysql_book_restore;；后续章节先切回原练习库，避免把恢复数据与基线混用。

启动或连接故障先看错误日志；慢查询问题再结合慢查询日志和执行计划。日志路径由部署配置决定，可通过 SHOW VARIABLES LIKE 'log_error'; 查询，不能假定所有系统使用同一路径。

日志与维护命令各解决什么问题

工具或信息	适合回答的问题	边界
错误日志	服务启动、崩溃恢复、运行异常发生了什么	容器或系统服务可能把它输出到标准错误或日志系统
慢查询日志	哪些满足记录条件的语句耗时较长	未启用或阈值过高时，未记录不代表没有慢查询
通用查询日志	客户端发送了哪些语句	输出量和开销可能很大，不宜无评估地长期全开
二进制日志	哪些数据变更可供复制或时间点恢复使用	不是慢查询分析的替代品，也不是完整备份的替代品
ANALYZE TABLE	更新优化器使用的统计信息	不会凭空创建缺失的业务索引，也不保证每次都提速
OPTIMIZE TABLE	按引擎行为整理或重建相关结构	可能涉及明显资源与锁成本，不是每次删除后都必须执行

不要机械地把旧教程里的 REPAIR TABLE 用于 InnoDB 损坏恢复，它不支持同样的修复路径。异常应结合引擎、日志、备份和官方恢复流程诊断。本单元不在练习表上执行没有必要的维护操作。

第 30 章：用执行计划验证假设

USE showdocs_mysql_book;
EXPLAIN SELECT learner_id, course_id, paid
FROM enrollments
WHERE enrolled_at >= '2026-10-01' AND enrolled_at < '2026-11-01';

CREATE INDEX idx_book_enrolled_at ON enrollments (enrolled_at);

EXPLAIN SELECT learner_id, course_id, paid
FROM enrollments
WHERE enrolled_at >= '2026-10-01' AND enrolled_at < '2026-11-01';

DROP INDEX idx_book_enrolled_at ON enrollments;

观察 type、possible_keys、key、rows 和 Extra。只有三行的练习表中，优化器仍可能选择全表扫描，这不是索引失效的证据。性能判断要结合实际数据量、选择性和测量结果，不能从本练习推导生产提速比例。EXPLAIN ANALYZE 会实际执行语句，用前应了解目标查询的成本。

一份执行计划怎样读

字段	先看什么	常见误读
table	当前步骤在访问哪张表或派生结果	不要把 SQL 书写顺序当作必然执行顺序
type	是全表扫描、范围访问还是键值查找	ALL 不一定错误，小表或返回大部分数据时可能合理
possible_keys	有哪些索引可能用于定位	出现名字不代表最终采用
key	最终选择哪个索引	选了索引也不代表只扫描很少数据
rows	优化器估算读取多少行	是估算，不是这次运行测到的实际行数
filtered	估算经过条件后还留下多大比例	依赖统计与估算，应结合实际执行验证
Extra	额外过滤、覆盖、排序或临时处理	Using filesort 不必然表示真的写了磁盘文件

对本例来说，日期条件覆盖了全部三条报名。读整张小表可能比通过日期索引逐条回表更便宜，因此增加索引后依旧扫描全表完全可能。为了展示索引而使用 FORCE INDEX，只能证明可以强制选择该路径，不能证明它更快。

索引应匹配过滤与排序方式

已有报名主键为 (learner_id, course_id)。它适合从学员编号开始定位，也能同时精确查找某位学员对某门课的报名。只给 course_id 时，不应把第二列当成具有与第一列相同的普通前缀定位能力；具体仍需结合其他索引与优化器计划。本例外键可能使数据库建立支持 course_id 的索引，可用 SHOW INDEX FROM enrollments; 查看实际状态。

设计一个新索引前，先列出常见查询：等值条件是什么、范围条件是什么、是否需要排序、最终返回多少行。多个单列索引不自动等于一个符合访问顺序的联合索引；索引越多，写入、存储与维护成本通常也越高。

避免在可直接比较的索引列外包裹不必要的函数、用字符串和数字随意混比，或在名称前加 % 后仍期待普通索引高效前缀查找。某些版本可通过函数索引等方式处理特定表达式，但那是针对明确需求的设计，不是所有函数条件都会自动获得加速。

一次可解释的优化流程

1. 保存准确 SQL、参数、数据规模和实际耗时，先确认问题可重复。
2. 检查结果是否正确，尤其是连接重复与缺少过滤造成的额外行。
3. 读取执行计划，明确主要扫描、排序、回表或等待发生在哪里。
4. 一次只改变一个与假设有关的因素，例如索引或等价的条件写法。
5. 在接近真实分布的数据上重新测量，并检查写入成本与其他查询是否受影响。

EXPLAIN ANALYZE 在支持的 SELECT 场景下会真正执行查询，能给出实际行数、循环与耗时信息；普通 EXPLAIN 主要展示优化器计划。大查询即使只为分析而运行，也会消耗资源，不能把 ANALYZE 当成没有成本的静态检查。

此外，查询慢不一定来自扫描路径：锁等待、连接池排队、网络传输、一次返回太多行都可能占时间。需要把数据库执行与调用方等待分开测量，才能知道该优化哪一段。

完成检查

练习一：大小写不同能否同时作为唯一名称

如果 tag_name 使用 utf8mb4_0900_ai_ci 并有唯一约束，先写 SQL 再写 sql 是否一定能成功？不能。唯一性判断受校对规则影响；需要区分大小写的业务，应明确选择相应规则并评估历史数据。

练习二：只读账号的验证证据是什么

只有 SHOW GRANTS 还不够。应在真正使用该账号的新连接中确认 CURRENT_USER()，执行一条读取成功、一条写入被拒绝的实验，并确认连接到了预期实例。管理员连接能查到数据，不代表应用账号也有相同权限。

练习三：备份恢复至少核对哪些结果

回答应包含恢复目标库名、结构与对象、行数、关键金额和代表性查询结果，而不只是「导入成功」。本例的验收点是三表 3 / 4 / 3 行、收入 157.00、中文正常、主外键存在。需要时间点恢复的真实系统，还须说明日志保存范围与目标时间。

练习四：为什么加索引后计划没变

本例数据只有三行且条件选中了全部记录，索引路径不必更便宜。先看数据规模与选择性，再看统计信息和实际耗时；不能仅凭 key = NULL 宣称索引设计无效，也不能仅凭 key 有名字宣称性能问题解决。

最后回到导读中的综合练习，尝试独立完成查询、写入回滚和恢复核对。能解释每一步的结果与失败条件，才说明这些操作已经连成一个完整的工作流程。

官方账号管理 · 官方 mysqldump 说明 · 官方执行计划说明

返回书籍导读