SQL与MySQL
介绍sql与mysql理论知识
1. 数据库与表结构管理（DDL）
| SQL 语句 | 解释与使用场景 |
|---|---|
SHOW DATABASES; |
列出当前 MySQL 实例中所有数据库。运维时用于查看实例上有哪些库，确认备份/迁移范围。 |
CREATE DATABASE testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
创建名为 testdb 的数据库，指定字符集为 utf8mb4（支持 emoji 和特殊字符），排序规则为通用不区分大小写。测试时需要快速搭建同构环境。 |
DROP DATABASE testdb; |
删除整个数据库（危险操作）。测试后清理环境时使用，不可逆。 |
USE testdb; |
切换到 testdb 数据库，后续操作默认在该库上执行。 |
SHOW TABLES; |
查看当前库下所有表。确认表是否创建成功或排查表丢失问题。 |
SHOW CREATE TABLE user\G |
显示建表时的完整 DDL，包括引擎、字符集、索引、注释等。\G 表示纵向显示，阅读更清晰。常用于迁移表结构或排查表定义差异。 |
DESC user; |
等价于DESCRIBE user，展示列名、类型、是否允许空、键信息等。快速查看表结构。 |
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, ...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
创建表，定义 id 为主键自增，指定 InnoDB 引擎和 utf8mb4 编码。InnoDB 支持事务，是生产主流。 |
ALTER TABLE t ADD COLUMN age TINYINT AFTER name; |
在 name 列后新增 age 列，类型 TINYINT。注意大表添加列会锁表（视版本和算法而定），测试时应了解其影响。 |
ALTER TABLE t MODIFY COLUMN name VARCHAR(100); |
修改 name 列类型或约束。可能引起锁表和数据重建，需谨慎。 |
ALTER TABLE t DROP COLUMN age; |
删除 age 列，同样有性能风险，生产操作应避开高峰期。 |
RENAME TABLE t TO t_new; |
表重命名，原子操作，不影响数据。运维时用于归档旧表或表切换。 |
TRUNCATE TABLE t; |
清空表所有数据并重置自增计数器，不可回滚（DDL 隐式提交）。比 DELETE 快，但测试时注意它无法触发触发器。 |
DROP TABLE IF EXISTS t; |
如果表存在则删除。谨慎操作，数据无法恢复。 |
2. 索引管理
| SQL 语句 | 解释 |
|---|---|
SHOW INDEX FROM table_name; |
查看表上所有索引的名称、列、唯一性、基数等，帮助分析索引使用情况。 |
ALTER TABLE t ADD INDEX idx_name (name); |
创建普通索引，加快基于 name 的等值或范围查询。索引会占用额外空间并影响写入速度。 |
ALTER TABLE t ADD UNIQUE idx_email (email); |
唯一索引，确保 email 值不重复，同时加速查询。测试中常用于验证唯一约束的正确性。 |
ALTER TABLE t ADD PRIMARY KEY (id); |
定义主键，InnoDB 会根据主键构建聚集索引，表数据按主键顺序存储。没有主键时会创建隐藏的 row_id，不建议依赖。 |
ALTER TABLE t ADD INDEX idx_multi (col1, col2); |
联合索引，遵循最左前缀原则。例如查询 where col1=? 可以用上，但直接查 col2=? 不能有效使用。 |
ALTER TABLE t DROP INDEX idx_name; |
删除指定索引，减少写入负担，但要评估查询影响。 |
SELECT * FROM t FORCE INDEX (idx_name) WHERE name='test'; |
强制优化器使用指定索引，用于测试索引性能对比或优化器选错索引时的临时修复。 |
3. 用户与权限管理（DCL）
| SQL 语句 | 解释 |
|---|---|
SELECT user,host FROM mysql.user; |
查看 MySQL 全局用户账号及允许连接的主机。host 为 % 表示允许任何主机连接。 |
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPass123!'; |
创建可从任意主机登录的用户，密码需符合密码策略。测试常需要创建不同权限的账号验证访问控制。 |
GRANT ALL PRIVILEGES ON testdb.* TO 'appuser'@'%'; |
将 testdb 库所有对象的所有权限赋予 appuser。权限粒度可以是库、表、列。 |
GRANT SELECT ON testdb.* TO 'readonly'@'192.168.1.%'; |
仅授予 SELECT 权限，限制来源 IP 段，符合最小权限原则。 |
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%'; |
创建复制专用账号，赋予从库复制所必需的权限。 |
REVOKE DELETE ON testdb.* FROM 'appuser'@'%'; |
回收特定权限，立即生效，但该用户已建立的连接不会受影响。 |
FLUSH PRIVILEGES; |
重新加载授权表。直接 DML 操作权限表后必须执行，但用 GRANT/REVOKE 时通常自动生效。 |
DROP USER 'readonly'@'192.168.1.%'; |
删除用户，同时清理其所有权限。 |
4. 数据查询与分析（DQL）
| SQL 语句 | 解释 |
|---|---|
SELECT * FROM t WHERE id > 100 LIMIT 10; |
条件分页查询，LIMIT 限制返回行数，测试可用来小范围验证数据。 |
SELECT COUNT(*), MAX(score), AVG(score) FROM t GROUP BY class; |
聚合查询，按班级统计人数、最高分和平均分。常用于报表和校验数据。 |
SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='testdb'; |
从信息模式表中获取每张表的估算行数（非精确，但可快速掌握数据量级），用于制定备份策略或估算测试时间。 |
SELECT email, COUNT(*) FROM user GROUP BY email HAVING COUNT(*)>1; |
查找重复的 email 记录，HAVING 对聚合结果过滤，测试数据质量校验常用。 |
SELECT * FROM large_table ORDER BY RAND() LIMIT 100; |
随机取 100 条，全表排序产生临时表，极慢，仅适合极小表。测试性能时要避免。 |
SELECT * FROM large_table WHERE id >= (SELECT FLOOR(MAX(id)*RAND()) FROM large_table) LIMIT 100; |
随机抽样改进版，利用主键范围随机起点，效率高，适合测试环境快速取样本。 |
SELECT GROUP_CONCAT(id) FROM t WHERE status=1; |
将多个 id 拼接成一个字符串，默认用逗号分隔，方便导出数据清单或构造 IN 列表。 |
SELECT name, score, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rank FROM student; |
窗口函数（8.0+），按班级分组内排名，不折叠行，比 GROUP BY 灵活，测试中用于分析排名和数据分布。 |
5. 数据操作与事务（DML）
| SQL 语句 | 解释 |
|---|---|
INSERT INTO t (name, age) VALUES ('test', 25), ('demo', 30); |
批量插入，减少网络往返。测试时用于快速生成基础数据。 |
INSERT INTO t SELECT * FROM backup_t WHERE create_date < '2025-01-01'; |
将查询结果直接插入目标表，用于数据迁移或备份恢复。注意列顺序和类型匹配。 |
UPDATE t SET status=0 WHERE id IN (1,2,3); |
条件更新，IN 列表适合少量明确记录。大批量更新需分批进行，避免锁表。 |
DELETE FROM t WHERE expire_time < NOW(); |
根据条件删除过期数据。生产需注意一次性删除大量行会占用锁和 undo 表空间。 |
START TRANSACTION; UPDATE ...; COMMIT; |
显式事务，所有操作作为一个原子单元。测试中用来模拟事务性操作和验证 ACID。 |
ROLLBACK; |
回滚事务，撤销所有未提交更改。常用于测试异常场景和回滚段压力。 |
SELECT * FROM information_schema.INNODB_TRX; |
查看当前活跃的 InnoDB 事务，包括事务 ID、状态、持续时间等，协助排查长事务锁问题。 |
6. 性能诊断与监控
| SQL 语句 | 解释 |
|---|---|
SHOW PROCESSLIST; |
列出所有前端连接及其当前执行的 SQL 和执行状态。发现慢查询、锁等待、连接数异常时首先使用。 |
KILL 123; |
杀掉 ID 为 123 的连接，用于终止异常查询或释放锁。KILL 不保证立即终止，可能需要 KILL QUERY 然后 KILL CONNECTION。 |
EXPLAIN SELECT * FROM t WHERE name='test'; |
查看执行计划，包括访问方式、使用索引、扫描行数等。是 SQL 优化的核心工具。 |
EXPLAIN FORMAT=JSON SELECT ...; |
输出 JSON 格式的执行计划，包含成本信息，更精细。 |
EXPLAIN ANALYZE SELECT ...; (MySQL 8.0.18+) |
实际执行 SQL 并返回每步的实际时间和行数，用于验证优化器估算的准确性。 |
SHOW VARIABLES LIKE 'slow_query%'; |
检查慢查询日志相关设置：是否开启、日志文件路径。 |
SHOW VARIABLES LIKE 'long_query_time'; |
慢查询时间阈值，超过该值的 SQL 会被记录。 |
SELECT * FROM mysql.slow_log LIMIT 10; |
当启用表输出（log_output=TABLE）时，可直接查询慢 SQL 记录。 |
SELECT * FROM information_schema.INNODB_LOCKS; |
查看当前 InnoDB 锁等待信息（5.7 可用，8.0 用performance_schema.data_locks）。 |
SELECT * FROM information_schema.INNODB_LOCK_WAITS; |
展示哪个事务被哪个事务阻塞，用于死锁排查。 |
SELECT table_name, data_length, index_length, data_free FROM information_schema.TABLES WHERE table_schema='testdb'; |
查看表的数据大小、索引大小及碎片空间，data_free 过大时可考虑 OPTIMIZE TABLE 回收。 |
SELECT @@transaction_isolation; |
查看当前会话的事务隔离级别，确认环境配置。 |
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; |
动态修改会话隔离级别，常用于临时满足特定业务需求或测试不同隔离级别的行为。 |
7. 备份与恢复相关 SQL
| 命令/SQL | 解释 |
|---|---|
mysqldump -u root -p --no-data testdb > schema.sql |
仅导出建表结构，不含数据。用于搭建相同表结构的测试环境或版本控制。 |
mysqldump -u root -p testdb table1 table2 > data.sql |
导出指定表的结构+数据，常用于单表恢复或迁移。 |
mysqldump ... --single-transaction --master-data=2 |
事务一致性备份，不锁表，并记录 binlog 位置（master-data=2 输出为注释），为搭建从库或时间点恢复提供起点。 |
SELECT * INTO OUTFILE '/tmp/user.csv' FIELDS TERMINATED BY ',' FROM user; |
将查询结果导出为 CSV 文件。需确保 MySQL 有目录写入权限，安全参数secure_file_priv 不限制此路径。 |
LOAD DATA INFILE '/tmp/user.csv' INTO TABLE user FIELDS TERMINATED BY ','; |
从文件快速批量导入数据，性能远高于逐条 INSERT，测试大量数据装载必用。 |
mysql -u root -p testdb < backup.sql |
执行 SQL 文件进行恢复。注意可能需要临时调大max_allowed_packet。 |
8. 系统状态与变量
| SQL 语句 | 解释 |
|---|---|
SHOW GLOBAL STATUS LIKE 'Threads_connected'; |
当前打开的连接数，反映并发量。对比max_connections 评估连接使用率。 |
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'; |
查看缓冲池命中率等相关状态，Innodb_buffer_pool_read_requests 与 reads 的比值即命中率，过低需调大 buffer pool。 |
SHOW VARIABLES LIKE 'max_connections'; |
查看最大允许连接数。若Threads_connected 接近此值，客户端会报连接拒绝。 |
SHOW VARIABLES LIKE '%innodb%'; |
查看所有 InnoDB 相关配置，如日志文件大小、刷新策略、IO 线程数等。 |
SHOW VARIABLES LIKE 'binlog%'; |
查看二进制日志配置，如是否开启、格式、过期时间等，对复制和恢复至关重要。 |
SET GLOBAL max_connections = 500; |
在线动态调大连接上限，无需重启。但需评估系统承受能力。 |
9. 主从复制运维
| SQL 语句 | 解释 |
|---|---|
SHOW SLAVE STATUS\G |
查看从库复制状态：IO/SQL 线程运行状态、主库信息、延迟秒数、错误信息等。运维最常用。 |
START SLAVE; / STOP SLAVE; |
启动/停止复制线程。维护时可能需要临时停止，注意延迟会累积。 |
RESET SLAVE ALL; |
清除所有从库配置信息，彻底断开复制关系。切换主库或重建从库时执行。 |
SHOW MASTER STATUS; |
查看当前主库的 binlog 文件名和位置，配置从库或增量备份时需要。 |
SHOW BINARY LOGS; |
列出所有 binlog 文件及其大小，用于判断日志保留策略和磁盘占用。 |
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10; |
查看 binlog 中的具体事件，用于解析数据变更、排查问题或验证复制故障。 |
二、MySQL 理论知识逐条详解
1. 体系架构
MySQL 分为四层：
- 连接层：处理客户端连接、用户认证，每个连接分配一个线程（线程池可复用），半双工协议。
- 服务层：核心大脑。SQL 接口、解析器（语法分析）、查询优化器（选择索引、join 顺序）、查询缓存（8.0 已移除）、内置函数（日期、数学等）。所有引擎共享此层。
- 引擎层：可插拔存储引擎，负责数据实际的存储和读取。InnoDB 是最主要的事务引擎。
- 存储层：物理文件系统，包括
.ibd（表空间）、redo log、undo log、binlog、错误日志等。
测试运维意义：了解架构能快速定位问题（是 SQL 解析慢还是引擎 I/O 瓶颈），理解缓存和日志的交互。
2. 存储引擎对比
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务 | 支持 ACID，通过 undo/redo 实现 | 不支持 |
| 锁粒度 | 行级锁（通过索引实现）、间隙锁 | 表级锁 |
| 外键 | 支持 | 不支持 |
| 崩溃恢复 | 基于 redo log 自动恢复 | 依赖修复工具，易损坏 |
| 存储结构 | 表空间（共享或独立），数据和索引按主键聚集 | 数据文件.MYD 和索引文件 .MYI 分离 |
| 适用 | 高并发 OLTP、核心业务 | 只读历史表、日志表（无事务要求） |
现代测试环境：默认使用 InnoDB，MyISAM 仅用于对比锁行为或临时表。
3. 索引原理
- B+Tree 结构：所有数据存储于叶子节点，非叶子节点只存导航键。叶子节点有序且包含完整数据或主键值，支持范围扫描。
- 聚集索引：InnoDB 以主键构建 B+Tree，叶子节点包含整行数据。因此主键插入应使用有序递增 ID，避免随机主键导致页分裂。
- 辅助索引：非主键索引的叶子节点存主键值，查询需要回表（二次查找聚集索引）。覆盖索引可避免回表。
- 联合索引最左前缀：索引
(a,b,c)相当于创建了a,a,b,a,b,c三个索引，查询条件中缺少 a 时该索引用不上。 - 覆盖索引 (Covering Index)：查询所需列全部包含在索引中，
EXPLAIN的Extra列会显示Using index，性能最佳。 - 索引下推 (ICP)：存储引擎层利用索引中的列提前过滤掉不满足 WHERE 条件的记录，减少回表次数。
EXPLAIN显示Using index condition。 - 索引失效场景：
LIKE '%abc'开头模糊- 在索引列上使用函数
WHERE UPPER(name)='A' - 隐式类型转换
WHERE phone=13800138000（phone 是 varchar 时走不上索引） OR条件中有一边无索引
测试必备：构造不同查询验证索引使用，分析执行计划，对比性能。
4. 事务与隔离级别
- ACID：
- 原子性：通过 undo log 回滚实现。
- 持久性：通过 redo log 保障，即使崩溃后也能重做。
- 隔离性：MVCC + 锁机制实现并发事务互不干扰。
- 一致性：由以上三者共同保障，同时业务逻辑约束（外键、触发器等）维护。
- 四种隔离级别：
- READ UNCOMMITTED：可能读到未提交的数据（脏读），基本不用于生产。
- READ COMMITTED：每次查询都重建 ReadView，只能读到已提交的数据，解决脏读，但存在不可重复读（同一事务两次读取结果不同）。
- REPEATABLE READ (默认)：事务首次查询时创建 ReadView，整个事务使用同一快照，避免不可重复读。通过间隙锁/Next-Key Lock 进一步减少幻读。
- SERIALIZABLE：所有 SELECT 隐式变为
LOCK IN SHARE MODE，全串行化，并发最低。
- MVCC 机制：每行记录有隐藏的
DB_TRX_ID（最近修改事务ID）和DB_ROLL_PTR（回滚指针指向 undo 记录）。通过比较事务 ID 和 ReadView 确定数据可见性。 - ReadView 可见性算法：包含活跃事务列表，若行记录的
DB_TRX_ID小于最小活跃事务 ID，则可见；若大于等于下一个分配的事务 ID，则不可见（需要回查 undo 链）。
测试常见：模拟并发更新，验证幻读、不可重复读行为，理解 RR 级别下“快照读”与“当前读”的区别。
5. 锁机制
- 表锁：直接锁定整张表，实现简单但并发低，MyISAM 及部分 DDL 使用。
- 行锁（Record Lock）：精确锁定索引记录，只有通过索引条件检索时 InnoDB 才会使用行锁，否则退化为表锁。
- 共享锁（S）和排他锁（X）：S 锁允许其他事务加 S 但不允许加 X；X 锁排斥一切其他锁。
- 意向锁（IS/IX）：表级锁，表明事务有意在行上加 S 或 X 锁，用于快速判断表级冲突，不阻塞行锁。
- 间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止其他事务插入记录，是 RR 隔离级别下防幻读的手段。
- Next-Key Lock：记录锁 + 间隙锁的组合，锁定一个左开右闭区间。
- 死锁：两个事务互相等待对方持有的锁，形成循环等待。InnoDB 自动检测，回滚代价较小的事务（影响行数少）。
SHOW ENGINE INNODB STATUS查看死锁详情。 - 排查锁问题：通过
information_schema的锁相关表或sys.schema_table_lock_waits（8.0）识别阻塞者和被阻塞者。
运维测试：压测时观察锁等待率、死锁频率，优化事务顺序和索引设计。
6. 日志系统
- binlog（二进制日志）：Server 层产生，记录数据库所有修改事件（DDL/DML），用于复制和时间点恢复。格式有 STATEMENT（记录SQL）、ROW（记录行变更，更安全）、MIXED。
- redo log：InnoDB 物理日志，循环写入两组固定大小文件，记录“数据页的物理修改”。保证事务持久性，崩溃恢复时利用 redo log 重做已提交事务。
- undo log：逻辑日志，记录变更前的数据版本，用于事务回滚和 MVCC 构建历史版本。存放在 undo 表空间。
- error log：记录启动、运行、关闭过程中的重要错误和告警。排查服务起不来、崩溃等问题的第一入口。
- slow query log：记录执行时间超过
long_query_time的 SQL，可以通过log_output输出到文件或表，是 SQL 优化的主要来源。 - general log：记录所有客户端连接和 SQL 请求，极度消耗性能，仅用于短暂调试，生产不可开启。
测试关注：redo log 大小和刷盘策略（
innodb_flush_log_at_trx_commit）影响写入性能；binlog 格式影响恢复和复制。
7. SQL 优化流程
- 发现慢 SQL：开启慢查询日志或通过监控工具（如 Prometheus + mysqld_exporter）。
- 分析执行计划：使用
EXPLAIN，重点关注：- type：连接类型，从优到差
system > const > eq_ref > ref > range > index > ALL。 - key：实际使用的索引。
- rows：优化器估算需要扫描的行数。
- Extra：
Using filesort（需要额外排序）和Using temporary（使用临时表）通常需要优化。
- type：连接类型，从优到差
- 优化手段：
- 创建合适索引：优先考虑高选择性列，联合索引注意最左前缀。
- 改写 SQL：避免
SELECT *，只取需要的列；分页用延迟关联；用JOIN替代子查询（视情况）。 - 表结构优化：使用合适的数据类型，减少行大小，提高缓冲池利用率。
- 参数调优：增大
join_buffer_size，调整sort_buffer_size等。
- 大表处理：考虑分区（按时间范围等），或通过中间件进行水平分库分表。
测试流程：构造不同数据量级，压测优化前后的 SQL，量化提升效果。
8. 主从复制与高可用
- 复制原理：
- 主库：事务提交时写入 binlog，binlog dump 线程将日志推送给从库 I/O 线程。
- 从库：I/O 线程接收日志写入 relay log，SQL 线程重放 relay log 中的事件。
- 异步复制：主库提交后不等待从库确认，从库可能延迟甚至丢失数据。性能最高。
- 半同步复制：主库等待至少一个从库将 binlog 写入 relay log（并刷盘）才返回客户端，保证数据至少存在于一台从库，降低丢失风险。
- GTID 复制：每个事务分配全局唯一 GTID，简化故障切换时的位置定位，
CHANGE MASTER TO MASTER_AUTO_POSITION=1即可自动对齐。 - 并行复制：从库 SQL 线程可以并行回放不同库或不同组事务（基于逻辑时钟），大幅减少延迟。
运维测试：模拟主库宕机，验证从库提升流程和数据一致性；压测时观察复制延迟。
9. 备份与恢复策略
- 逻辑备份（mysqldump/mydumper）：导出为 SQL 文件，可读可编辑，跨版本迁移方便，但速度慢，大库恢复时间长达数小时。
- 物理备份（Xtrabackup/MySQL Enterprise Backup）：直接拷贝 InnoDB 数据文件并记录 redo log 增量，支持在线热备不阻塞业务，恢复速度接近文件拷贝，是生产首选。
- 时间点恢复（PITR）：恢复流程为“全量备份恢复 + 从全备后的 binlog 重放到指定时间点”。基于 GTID 或时间点位置
mysqlbinlog工具进行解析。
测试必须：定期全备+增备演练，记录备份和恢复耗时，确保 RPO/RTO 达标。
10. 重要参数与调优方向
- 连接：
max_connections：最大连接数，过大会消耗内存。back_log：等待连接队列长度。thread_cache_size：线程缓存数，减少频繁创建销毁开销。
- InnoDB 缓冲池：
innodb_buffer_pool_size：核心内存区域，缓存数据页和索引页，建议设置为物理内存的 50%~80%。命中率过低会频繁磁盘 I/O。
- 日志：
innodb_log_file_size：redo log 单个文件大小，过小会导致频繁 checkpoint 刷脏页，影响写入性能；过大恢复时间长。innodb_flush_log_at_trx_commit：- 1：每次提交刷 redo log，最安全。
- 2：每次提交写 OS 缓存但不立刻刷盘，MySQL 崩溃不会丢，OS 崩溃会丢失 1 秒内事务。
- 0：每秒刷一次，性能最好但可能丢失 1 秒数据。
- IO 配置：
innodb_read_io_threads/innodb_write_io_threads：后台 IO 线程数，根据磁盘 SSD 调整。
- 临时表：
tmp_table_size、max_heap_table_size：控制内存临时表大小，超限转为磁盘临时表，影响查询速度。
- 查询缓存（5.7 已废弃）：MySQL 8.0 移除，应用层（Redis）替代。
测试价值：压测不同参数组合，观察 TPS/QPS 变化，找到适合业务的配置基线。
本文采用 CC BY-NC-SA 4.0 许可协议，转载请标明出处