关系型数据库MySQL

1. MySQL 安装与配置

1.1 安装方式与版本选择

概念名称	说明	注意事项
MySQL 社区版（Community Edition）	官方免费开源版本，适合学习和中小型项目	功能完整但无官方商业支持
MySQL 企业版（Enterprise Edition）	商业付费版本，含监控、备份、安全等高级功能	需购买许可证，适合大型企业
安装方式 - 包管理器（Linux）	使用 apt（Ubuntu/Debian）或 yum/dnf（CentOS/RHEL）安装	推荐用于服务器部署，自动处理依赖
安装方式 - 官方安装包（Windows/macOS）	从官网下载 .msi（Windows）或 .dmg（macOS）图形化安装	适合本地开发环境，向导式操作
安装方式 - 二进制压缩包	下载通用 Linux 二进制包（.tar.gz），手动配置	适合定制化部署，需手动初始化数据目录
安装方式 - Docker	使用官方 MySQL 镜像（如 mysql:8.0）快速启动容器	适合开发测试，不建议直接用于生产（需持久化配置）
版本选择建议	优先选择长期支持（LTS）版本，如 5.7 或 8.0	8.0 新特性多但有兼容性风险；5.7 更稳定

1.2 配置文件详解（my.cnf / my.ini）

配置项	说明	注意事项
[mysqld] 段	MySQL 服务端主配置段	所有服务端参数必须在此段内
basedir	MySQL 安装目录路径	例如：/usr/local/mysql
datadir	数据文件存储目录	必须存在且 MySQL 进程有读写权限
port	监听端口，默认 3306	修改后需重启服务，注意防火墙规则
socket	Unix 域套接字文件路径（Linux/macOS）	用于本地连接，客户端也需指定相同路径
bind-address	绑定 IP 地址，默认 127.0.0.1	设为 0.0.0.0 可远程访问，注意安全风险
character-set-server	服务端默认字符集	推荐设为 utf8mb4
collation-server	默认排序规则	通常设为 utf8mb4_general_ci 或 utf8mb4_0900_ai_ci
max_connections	最大并发连接数	默认 151，高并发场景需调大
innodb_buffer_pool_size	InnoDB 缓冲池大小	建议设为物理内存的 50%～70%
log-error	错误日志路径	用于排查启动或运行错误
slow_query_log	慢查询日志开关	设为 ON 可开启慢查询记录
long_query_time	慢查询阈值（秒）	默认 10 秒，可设为 1 或 2 用于性能分析
配置文件位置（Linux）	/etc/my.cnf 或 /etc/mysql/my.cnf	也可在 /usr/local/mysql/etc/ 下
配置文件位置（Windows）	安装目录下的 my.ini	通常位于 C:\ProgramData\MySQL\MySQL Server X.X\

注： 修改配置文件后必须重启 MySQL 服务才能生效（部分动态参数可通过 SET GLOBAL 修改）。

1.3 启动与停止服务

操作名称	操作细节	注意事项
Linux 系统（systemd）启动	sudo systemctl start mysqld	服务名可能为 mysql 或 mysqld，视发行版而定
Linux 系统（systemd）停止	sudo systemctl stop mysqld	停止前确保无重要事务在执行
Linux 系统（systemd）重启	sudo systemctl restart mysqld	修改配置后常用
Linux 系统（systemd）查看状态	sudo systemctl status mysqld	可检查是否运行正常
Windows 服务启动	net start mysql	服务名需与安装时注册的名称一致
Windows 服务停止	net stop mysql	需以管理员权限运行命令提示符
macOS（Homebrew 安装）启动	brew services start mysql	或使用 launchctl
手动启动（二进制包）	mysqld_safe --defaults-file=/path/to/my.cnf &	需指定配置文件和用户权限
强制终止进程（紧急情况）	kill -9	仅在服务无响应时使用，可能导致数据损坏
设置开机自启（Linux）	sudo systemctl enable mysqld	确保服务随系统启动

注意： 首次安装后，MySQL 8.0+ 会自动生成临时 root 密码（通常在 /var/log/mysqld.log 中），需先登录并修改密码。

2. 命令行客户端连接与基本操作

2.1 登录与退出

操作名称	语法	用途	代码示例	注意事项
本地登录（交互式）	mysql -u 用户名 -p	使用指定用户登录本地 MySQL 服务	mysql -u root -p	输入命令后会提示输入密码，密码不回显
远程登录	mysql -h 主机地址 -u 用户名 -p -P 端口	连接远程 MySQL 服务器	mysql -h 192.168.1.100 -u admin -p -P 3306	需确保远程主机允许该用户从当前 IP 连接，且防火墙开放端口
本地登录（免密）	mysql -u 用户名	无需输入密码（依赖系统认证或配置文件）	mysql -u root	通常用于 Linux 下使用 socket 认证的 root 用户
直接执行单条 SQL 并退出	mysql -u 用户名 -p -e "SQL语句"	非交互式执行命令	mysql -u root -p -e "SHOW DATABASES;"	适用于脚本中快速查询
退出客户端	exit 或 quit 或 \q	退出 MySQL 命令行客户端	exit	三种方式等效，在 mysql> 提示符下输入即可

2.2 常用命令行参数

参数	语法形式	用途	代码示例	注意事项
-u	-u username	指定连接用户名	mysql -u app_user -p	必须指定，否则默认使用当前系统用户名
-p	-p 或 -p密码	指定密码（不推荐直接写密码）	mysql -u root -pMyPass123	密码明文暴露在历史记录中，建议仅用 -p 让其交互输入
-h	-h host	指定 MySQL 服务器主机地址	mysql -h db.example.com -u root -p	默认为 localhost（使用 socket），远程需显式指定
-P	-P port	指定连接端口	mysql -h 127.0.0.1 -P 3307 -u root -p	注意是大写 P；若连 localhost 且未指定 -h，则走 socket 不走 TCP
-D	-D database	登录后自动 use 指定数据库	mysql -u root -p -D myapp	可省去手动 USE 步骤
--protocol	--protocol=TCP | SOCKET | PIPE	强制指定连接协议	mysql -u root --protocol=TCP	调试连接问题时有用
--default-character-set	--default-character-set=utf8mb4	设置客户端字符集	mysql -u root -p --default-character-set=utf8mb4	避免中文乱码，建议始终使用 utf8mb4
--skip-column-names	--skip-column-names	查询结果不显示列名	mysql -u root -p -e "SELECT name FROM users;" --skip-column-names	适用于脚本解析输出
--batch (-B)	-B	以批处理模式输出（制表符分隔）	mysql -B -u root -p -e "SELECT id,name FROM users;"	便于 awk/sed 处理
--vertical (-E)	-E	当列宽过大时垂直显示结果	mysql -E -u root -p -e "DESCRIBE large_table;"	每列一行，适合宽表查看

2.3 执行 SQL 脚本文件

操作名称	语法	用途	代码示例	注意事项
通过重定向执行脚本	mysql -u 用户名 -p < script.sql	从标准输入读取 SQL 文件执行	mysql -u root -p < init_db.sql	脚本中不能包含交互式命令（如 prompt）
通过 source 命令执行	source /path/to/script.sql	在已登录的 mysql 客户端中执行脚本	mysql> source ./setup.sql;	路径可以是相对或绝对路径，需有读权限
指定数据库执行脚本	mysql -u 用户名 -p -D dbname < script.sql	在指定数据库上下文中执行脚本	mysql -u root -p -D myapp < schema.sql	脚本内可省略 USE 语句
执行脚本并输出日志	mysql -u root -p < script.sql > output.log 2>&1	将执行结果和错误重定向到日志	mysql -u root -p < deploy.sql > deploy.log 2>&1	便于排查部署问题
忽略错误继续执行	mysql -u root -p --force < script.sql	即使某条 SQL 失败也继续执行后续语句	mysql -u root -p --force < migration.sql	适用于非关键初始化脚本，慎用于生产数据变更

注意：

• SQL 脚本文件应使用 UTF-8 编码保存；
• 脚本中每条 SQL 语句应以分号 ; 结尾；
• 若脚本包含存储过程或函数（含 DELIMITER），需确保客户端支持（mysql 命令行默认支持）；
• 生产环境执行前务必在测试环境验证。

3. 数据库与用户管理

3.1 数据库的创建、查看、删除

方法名称	语法	用途	代码示例	注意事项
创建数据库	CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset_name] [COLLATE collation_name];	创建新数据库，可指定字符集和排序规则	CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;	若未指定字符集，默认使用服务端配置；建议显式指定 utf8mb4
查看所有数据库	SHOW DATABASES;	列出当前用户有权限查看的所有数据库	SHOW DATABASES;	结果包含系统库如 information_schema、mysql 等
使用数据库	USE db_name;	切换当前会话的默认数据库	USE myapp;	后续操作（如建表）将作用于该数据库
查看当前数据库	SELECT DATABASE();	返回当前会话正在使用的数据库名	SELECT DATABASE();	若未 USE 任何库，返回 NULL
删除数据库	DROP DATABASE [IF EXISTS] db_name;	删除整个数据库及其所有对象（表、视图等）	DROP DATABASE IF EXISTS test_db;	不可逆操作，生产环境慎用；需 DROP 权限

3.2 用户的创建、授权、删除

方法名称	语法	用途	代码示例	注意事项
创建用户	CREATE USER 'username'@'host' IDENTIFIED BY 'password';	创建新用户，指定允许登录的主机和密码	CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePass123!';	host 可为 localhost、IP、%（任意主机）；MySQL 8.0+ 默认使用 caching_sha2_password 插件
修改用户密码	ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';	更改现有用户密码	ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootPass!';	推荐方式；旧版可用 SET PASSWORD，但已不推荐
查看用户信息	SELECT User, Host FROM mysql.user;	查询所有用户及其允许连接的主机	SELECT User, Host FROM mysql.user WHERE User = 'app_user';	需要 SELECT 权限 on mysql.user 表（通常仅 root 有）
删除用户	DROP USER 'username'@'host';	删除指定用户	DROP USER 'temp_user'@'192.168.1.%';	若用户有权限或对象依赖，建议先 REVOKE 再删除
刷新权限	FLUSH PRIVILEGES;	重新加载权限表（通常不需要手动执行）	FLUSH PRIVILEGES;	执行 GRANT/REVOKE 后 MySQL 自动刷新，仅在直接修改 mysql.user 表时需要

注意：

• 用户由 (User, Host) 唯一标识，'user'@'localhost' 和 'user'@'%' 是两个不同账户；
• 新建用户默认无任何权限，必须通过 GRANT 显式授权；
• 密码应满足 validate_password 插件策略（若启用），如长度、复杂度要求。

3.3 权限管理（GRANT / REVOKE）

方法名称	语法	用途	代码示例	注意事项
授予全部数据库权限	GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';	赋予用户对所有数据库的所有权限（类似超级用户）	GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';	仅限可信管理员账户，避免用于应用用户
授予单库全部权限	GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host';	赋予用户对指定数据库的所有操作权限	GRANT ALL PRIVILEGES ON myapp.* TO 'app_user'@'%';	最常见授权方式，适用于应用连接用户
授予特定权限	GRANT SELECT, INSERT, UPDATE ON db_name.table_name TO 'username'@'host';	按需授予细粒度权限	GRANT SELECT, INSERT ON myapp.users TO 'reporter'@'%';	遵循最小权限原则，提升安全性
授予带授权权限	GRANT ... TO 'username'@'host' WITH GRANT OPTION;	允许该用户将自身权限再授予他人	GRANT SELECT ON myapp.* TO 'delegate'@'%' WITH GRANT OPTION;	高风险操作，慎用
查看用户权限	SHOW GRANTS FOR 'username'@'host';	显示指定用户的权限列表	SHOW GRANTS FOR 'app_user'@'%';	输出为可执行的 GRANT 语句
撤销全部权限	REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';	撤销用户所有权限（包括授权权）	REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';	用户仍存在，但无法操作任何数据
撤销特定权限	REVOKE INSERT, UPDATE ON db_name.* FROM 'username'@'host';	撤销部分权限	REVOKE DELETE ON myapp.orders FROM 'support'@'%';	保留其他未撤销的权限
刷新权限缓存	FLUSH PRIVILEGES;	（通常自动完成）强制重载权限	FLUSH PRIVILEGES;	仅在绕过 GRANT 直接修改 mysql.user 表时需要

常见权限类型说明：

权限	说明
ALL PRIVILEGES	除 GRANT OPTION 外的所有权限
SELECT / INSERT / UPDATE / DELETE	基本 DML 权限
CREATE / DROP / ALTER	DDL 权限
INDEX	创建/删除索引
REFERENCES	外键引用（InnoDB 中实际未强制）
USAGE	无权限（常用于创建用户但暂不授权）

注意：

• 权限变更立即生效（无需重启）；
• 权限作用范围格式：*.*（全局）、db.*（库级）、db.tbl（表级）；
• 生产环境应严格限制 DROP、ALTER、GRANT OPTION 等高危权限。

4. 表结构操作（DDL）

4.1 创建表（CREATE TABLE）

方法名称	语法	用途	代码示例	注意事项
基本建表语句	CREATE TABLE table_name ( column_def [, ...] ) [ENGINE=engine] [CHARSET=charset];	创建新表，定义列、引擎、字符集等	CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE ) ENGINE=InnoDB CHARSET=utf8mb4;	必须指定至少一列；推荐显式指定 ENGINE 和 CHARSET
指定主键	在列定义中使用 PRIMARY KEY 或表级约束	定义唯一标识行的主键	id INT PRIMARY KEY 或 CONSTRAINT pk PRIMARY KEY (id)	主键自动隐含 NOT NULL 和唯一性
自增列	使用 AUTO_INCREMENT 属性	自动生成递增整数 ID	id INT AUTO_INCREMENT PRIMARY KEY	仅适用于整型列；需配合主键或唯一索引
默认值	DEFAULT value	为列设置默认值	created_at DATETIME DEFAULT CURRENT_TIMESTAMP	函数如 NOW()、CURRENT_TIMESTAMP 可用于时间类型
非空约束	NOT NULL	禁止该列为空	name VARCHAR(100) NOT NULL	插入时若未提供值且无 DEFAULT，将报错
唯一约束	UNIQUE [KEY | INDEX]	保证列值唯一	email VARCHAR(150) UNIQUE	可多列组合唯一（表级约束）
外键约束	FOREIGN KEY (col) REFERENCES parent_table(pk_col) [ON DELETE action]	建立表间引用关系	FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE	仅 InnoDB 支持；父表必须有索引
从查询结果建表	CREATE TABLE new_table AS SELECT ...	根据查询结果创建新表（含数据）	CREATE TABLE active_users AS SELECT * FROM users WHERE status='active';	新表无主键、索引、自增属性；仅复制数据结构和内容

注意：

• 表名和列名区分大小写受 lower_case_table_names 参数影响；
• 推荐使用 InnoDB 引擎（支持事务、外键、行锁）；
• 字符集应统一使用 utf8mb4 以支持 emoji 和完整 Unicode。

4.2 修改表结构（ALTER TABLE）

方法名称	语法	用途	代码示例	注意事项
添加列	ALTER TABLE table_name ADD COLUMN col_name type [constraints];	向表中新增字段	ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER name;	可用 FIRST / AFTER 指定位置；默认加在最后
删除列	ALTER TABLE table_name DROP COLUMN col_name;	移除表中某列及其数据	ALTER TABLE users DROP COLUMN temp_flag;	不可逆，数据永久丢失
修改列定义（保留数据）	ALTER TABLE table_name MODIFY COLUMN col_name new_type [new_constraints];	改变列类型或约束，保留原数据	ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;	类型变更需兼容（如 INT → BIGINT 可行，VARCHAR → INT 可能失败）
重命名列并修改类型	ALTER TABLE table_name CHANGE COLUMN old_name new_name new_type;	同时改列名和类型	ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100);	即使类型不变也需重复写出
重命名表	ALTER TABLE old_name RENAME TO new_name;	更改表名	ALTER TABLE user_profiles RENAME TO profiles;	也可用 RENAME TABLE old TO new;
添加主键	ALTER TABLE table_name ADD PRIMARY KEY (col);	为表添加主键	ALTER TABLE logs ADD PRIMARY KEY (id);	表必须无主键，且列值唯一非空
删除主键	ALTER TABLE table_name DROP PRIMARY KEY;	移除主键	ALTER TABLE temp_table DROP PRIMARY KEY;	若主键是自增列，需先 DROP 主键再 DROP 列
添加索引	ALTER TABLE table_name ADD INDEX idx_name (col[, ...]);	创建普通索引	ALTER TABLE orders ADD INDEX idx_user_id (user_id);	可同时建多列复合索引
添加唯一索引	ALTER TABLE table_name ADD UNIQUE KEY uk_email (email);	创建唯一约束索引	ALTER TABLE users ADD UNIQUE KEY uk_email (email);	若存在重复值会失败
添加外键	ALTER TABLE child ADD FOREIGN KEY (fk_col) REFERENCES parent(pk_col);	建立外键关系	ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);	两表必须同引擎（通常 InnoDB），且父表列有索引
删除索引	ALTER TABLE table_name DROP INDEX idx_name;	删除指定索引	ALTER TABLE users DROP INDEX idx_old_phone;	主键索引不能用此方式删除，需用 DROP PRIMARY KEY
修改表选项	ALTER TABLE table_name ENGINE=InnoDB CHARSET=utf8mb4;	更改存储引擎或字符集	ALTER TABLE legacy_table ENGINE=InnoDB;	转换引擎可能耗时（重建表）

注意：

• 大部分 ALTER 操作在 MySQL 5.6+ 支持 Online DDL（不锁表或短时锁），但某些操作（如 DROP COLUMN、修改列类型）仍会重建表；
• 生产环境大表结构变更建议在低峰期执行，并提前备份。

4.3 删除表（DROP TABLE）

方法名称	语法	用途	代码示例	注意事项
删除单表	DROP TABLE [IF EXISTS] table_name;	删除整个表结构及所有数据	DROP TABLE IF EXISTS temp_import;	不可逆操作，数据无法恢复
删除多表	DROP TABLE table1, table2;	一次删除多个表	DROP TABLE logs_2020, logs_2021;	若任一表不存在且未用 IF EXISTS，整个语句失败
仅清空数据保留结构	TRUNCATE TABLE table_name;	快速清空表数据（非 DDL，但效果类似）	TRUNCATE TABLE session_data;	比 DELETE FROM 更快，重置自增值，不可回滚（DDL 语句）

注意：

• DROP TABLE 会同时删除所有索引、触发器、外键引用（子表外键不受影响，但可能变为无效）；
• 需要 DROP 权限；
• 若表被外键引用，默认行为取决于 foreign_key_checks 设置（通常允许删除，但子表外键失效）。

4.4 查看表结构（DESC / SHOW CREATE TABLE）

方法名称	语法	用途	代码示例	注意事项
简略查看列信息	DESC table_name; 或 DESCRIBE table_name;	显示表的列名、类型、是否为空、键、默认值、额外属性	DESC users;	输出简洁，适合快速查看字段
完整建表语句	SHOW CREATE TABLE table_name;	返回可直接执行的 CREATE TABLE 语句	SHOW CREATE TABLE orders;	包含引擎、字符集、索引、外键等全部细节
查看表状态	SHOW TABLE STATUS LIKE 'table_name';	显示表的元数据（行数、数据大小、引擎等）	SHOW TABLE STATUS LIKE 'users';	行数为估算值（InnoDB）
查看列详细信息	SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='db' AND TABLE_NAME='tbl';	从系统表查询列定义	SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_NAME='users';	可跨库查询，适合程序化分析
查看索引信息	SHOW INDEX FROM table_name;	列出表的所有索引详情	SHOW INDEX FROM users;	包含索引名、列、唯一性、排序等

注意：

• DESC 是 DESCRIBE 的缩写，两者等效；
• SHOW CREATE TABLE 的输出可用于迁移或备份表结构；
• information_schema 是只读系统库，包含所有数据库元数据。

5. 数据操作（DML）

5.1 插入数据（INSERT）

方法名称	语法	用途	代码示例	注意事项
插入单行指定列	INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);	向表中插入一行数据，可指定部分列	INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');	未指定的列必须允许 NULL 或有 DEFAULT 值
插入单行所有列	INSERT INTO table_name VALUES (val1, val2, ...);	按表定义顺序插入所有列值	INSERT INTO users VALUES (1, 'Bob', 'bob@example.com', NOW());	必须提供全部列值，顺序不能错
插入多行	INSERT INTO table_name (cols) VALUES (row1), (row2), ...;	一次插入多行数据	INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com'), ('Diana', 'diana@example.com');	比多次单行 INSERT 更高效
插入查询结果	INSERT INTO table_name (cols) SELECT ... FROM other_table;	将查询结果插入表中	INSERT INTO active_users (id, name) SELECT id, name FROM users WHERE status = 'active';	列数和类型需匹配；可用于数据迁移或备份
忽略重复插入	INSERT IGNORE INTO table_name ...	遇到主键/唯一冲突时跳过，不报错	INSERT IGNORE INTO users (email) VALUES ('alice@example.com');	冲突行被忽略，其他行正常插入
冲突时更新（UPSERT）	INSERT INTO ... ON DUPLICATE KEY UPDATE col = val;	若主键/唯一键冲突，则执行 UPDATE	INSERT INTO users (id, name, login_count) VALUES (1, 'Alice', 1) ON DUPLICATE KEY UPDATE login_count = login_count + 1;	MySQL 特有语法，常用于计数器或缓存更新
替换插入（先删后插）	REPLACE INTO table_name ...	若主键/唯一键冲突，先 DELETE 再 INSERT	REPLACE INTO users (id, name) VALUES (1, 'New Alice');	会触发 DELETE 和 INSERT 触发器；自增 ID 可能变化

注意：

• 插入字符串、日期等需用单引号；
• 自增列可显式赋值（如设为 0 或 NULL 以触发自增）；
• 批量插入性能远优于循环单条插入。

5.2 查询数据（SELECT）

方法名称	语法	用途	代码示例	注意事项
查询所有列	SELECT * FROM table_name;	返回表中所有列和行	SELECT * FROM users;	开发调试可用，生产环境建议明确列名
查询指定列	SELECT col1, col2 FROM table_name;	仅返回需要的列	SELECT name, email FROM users;	减少网络传输和内存占用
条件过滤	SELECT ... FROM ... WHERE condition;	根据条件筛选行	SELECT * FROM orders WHERE amount > 100;	支持 =、!=、<、>、BETWEEN、IN、LIKE、IS NULL 等
去重	SELECT DISTINCT col FROM table;	返回某列的唯一值	SELECT DISTINCT status FROM orders;	可作用于多列组合去重
列别名	SELECT col AS alias FROM table;	为列设置显示名称	SELECT name AS full_name FROM users;	AS 可省略：SELECT name full_name
表别名	SELECT u.name FROM users u;	为表设置简短别名	SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;	提高可读性，尤其在多表连接时
限制返回行数	SELECT ... LIMIT n;	限制结果集大小	SELECT * FROM logs LIMIT 10;	常用于分页或取样
分页查询	SELECT ... LIMIT offset, count;	实现分页（从第 offset 行开始取 count 行）	SELECT * FROM products LIMIT 20, 10; -- 第3页（每页10条）	OFFSET 越大性能越差，大数据量建议用游标分页
排序	SELECT ... ORDER BY col [ASC | DESC];	按指定列排序	SELECT * FROM users ORDER BY created_at DESC;	可多列排序：ORDER BY status, created_at DESC

注意：

• SELECT 不加 WHERE 会扫描全表（慎用于大表）；
• LIMIT 必须放在语句最后（在 ORDER BY 之后）；
• 字符串比较默认不区分大小写（取决于排序规则 collation）。

5.3 更新数据（UPDATE）

方法名称	语法	用途	代码示例	注意事项
更新指定行	UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;	修改满足条件的行	UPDATE users SET email = 'new@example.com' WHERE id = 100;	必须带 WHERE，否则更新全表
更新多列	UPDATE table_name SET col1 = expr1, col2 = expr2 ...	一次更新多个字段	UPDATE products SET price = price * 1.1, updated_at = NOW() WHERE category = 'electronics';	表达式可引用原列值
无条件更新（危险）	UPDATE table_name SET col = val;	更新表中所有行	UPDATE logs SET processed = 1;	除非明确需要，否则极易误操作
基于子查询更新	UPDATE table1 SET col = (SELECT ... FROM table2 WHERE ...) WHERE ...;	使用子查询结果更新	UPDATE users SET last_order_date = (SELECT MAX(created_at) FROM orders WHERE orders.user_id = users.id) WHERE id IN (SELECT user_id FROM orders);	子查询不能直接引用被更新表（MySQL 限制），需用派生表绕过

注意：

• UPDATE 是 DML 语句，可回滚（在事务中）；
• 生产环境建议先用 SELECT 验证 WHERE 条件；
• 大量更新可能锁表或产生大量 binlog，影响性能。

5.4 删除数据（DELETE / TRUNCATE）

方法名称	语法	用途	代码示例	注意事项
条件删除	DELETE FROM table_name WHERE condition;	删除满足条件的行	DELETE FROM sessions WHERE expired_at < NOW();	必须带 WHERE，否则删除全表
清空全表（逐行删）	DELETE FROM table_name;	删除表中所有数据，保留结构	DELETE FROM temp_cache;	触发 DELETE 触发器；可回滚；自增值不重置
快速清空表	TRUNCATE TABLE table_name;	快速删除所有数据并重置自增计数器	TRUNCATE TABLE logs;	DDL 语句，不可回滚；不触发触发器；比 DELETE 快得多
基于连接删除	DELETE t1 FROM table1 t1 JOIN table2 t2 ON ... WHERE ...;	多表关联删除（MySQL 扩展语法）	DELETE u FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL; -- 删除无订单用户	标准 SQL 不支持，但 MySQL 允许
安全模式限制	sql_safe_updates=1	防止无 WHERE 的 DELETE/UPDATE	SET sql_safe_updates = 1;	启用后，无 WHERE 或无 KEY 的 DELETE/UPDATE 会被拒绝

注意：

• DELETE 可配合 LIMIT 使用（如 DELETE FROM logs LIMIT 1000）实现分批删除；
• TRUNCATE 不能用于有外键引用的表（除非禁用 foreign_key_checks）；
• 重要数据删除前务必备份或使用事务包裹（仅对 DELETE 有效）。

6. 查询进阶

6.1 条件查询（WHERE）

操作符/关键字	语法	用途	代码示例	注意事项
等值比较	col = value	匹配等于某值的行	SELECT * FROM users WHERE age = 25;	字符串需用单引号：name = 'Alice'
不等比较	col != value 或 col <> value	匹配不等于某值的行	SELECT * FROM products WHERE price != 0;	两者等效，<> 是 SQL 标准
范围比较	col BETWEEN low AND high	匹配闭区间内的值	SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;	包含边界值；等价于 >= low AND <= high
集合匹配	col IN (val1, val2, ...)	匹配列表中的任意值	SELECT * FROM users WHERE status IN ('active', 'pending');	可替代多个 OR 条件
空值判断	col IS NULL / IS NOT NULL	判断是否为 NULL	SELECT * FROM users WHERE email IS NULL;	不能用 = NULL，必须用 IS
模糊匹配	col LIKE pattern	使用通配符匹配字符串	SELECT * FROM users WHERE name LIKE 'A%'; -- 以 A 开头	% 匹配任意字符（包括空），_ 匹配单个字符
正则匹配	col REGEXP pattern	使用正则表达式匹配	SELECT * FROM users WHERE email REGEXP '@example\\.com$';	MySQL 特有，性能低于 LIKE
逻辑与	condition1 AND condition2	同时满足多个条件	SELECT * FROM orders WHERE user_id = 10 AND status = 'shipped';	可用 && 替代（非标准）
逻辑或	condition1 OR condition2	满足任一条件	SELECT * FROM products WHERE category = 'book' OR price < 10;	可用 || 替代（需 sql_mode 支持）
逻辑非	NOT condition	取反条件	SELECT * FROM users WHERE NOT (age < 18);	等价于 age >= 18

注意：

• WHERE 子句在 GROUP BY 之前执行；
• 对索引列使用函数（如 WHERE YEAR(created_at) = 2025）会导致索引失效；
• NULL 与任何值比较（包括 NULL）结果均为 UNKNOWN，不会被 WHERE 返回。

6.2 排序与分页（ORDER BY / LIMIT）

方法名称	语法	用途	代码示例	注意事项
单列升序排序	ORDER BY col ASC	按列升序排列（默认）	SELECT * FROM users ORDER BY name ASC;	ASC 可省略
单列降序排序	ORDER BY col DESC	按列降序排列	SELECT * FROM orders ORDER BY created_at DESC;	常用于获取最新记录
多列排序	ORDER BY col1 [ASC/DESC], col2 [ASC/DESC]	先按 col1 排，再按 col2 排	SELECT * FROM students ORDER BY grade DESC, score DESC;	优先级从左到右
按表达式排序	ORDER BY expression	按计算结果排序	SELECT name, salary/12 AS monthly FROM employees ORDER BY monthly;	可用列别名
分页（取前 N 行）	LIMIT n	限制返回最多 n 行	SELECT * FROM logs LIMIT 10;	常用于取样或首页
分页（跳过 M 行取 N 行）	LIMIT m, n	跳过前 m 行，取接下来 n 行	SELECT * FROM products LIMIT 20, 10; -- 第3页（每页10条）	等价于 LIMIT n OFFSET m
安全分页	WHERE id > last_id ORDER BY id LIMIT n	基于游标的高效分页	SELECT * FROM messages WHERE id > 1000 ORDER BY id LIMIT 20;	适用于按自增 ID 或时间戳分页，性能稳定
随机排序	ORDER BY RAND()	随机打乱结果顺序	SELECT * FROM questions ORDER BY RAND() LIMIT 1;	性能极差，大表禁用

注意：

• ORDER BY 必须在 WHERE 之后、LIMIT 之前；
• LIMIT 仅接受非负整数常量（不能是表达式或子查询）；
• 深度分页（如 LIMIT 100000, 10）会导致全表扫描前 100010 行，应避免。

6.3 聚合函数（COUNT, SUM, AVG 等）

聚合函数	语法	用途	代码示例	注意事项
计数（所有行）	COUNT(*)	统计结果集总行数	SELECT COUNT(*) FROM users;	包含 NULL 值行
计数（非 NULL 值）	COUNT(col)	统计某列非 NULL 值的数量	SELECT COUNT(email) FROM users;	忽略 NULL
求和	SUM(col)	对数值列求和	SELECT SUM(amount) FROM orders;	忽略 NULL；若全为 NULL 返回 NULL
平均值	AVG(col)	计算平均值	SELECT AVG(score) FROM exams;	忽略 NULL；等价于 SUM(col)/COUNT(col)
最大值	MAX(col)	返回最大值	SELECT MAX(price) FROM products;	可用于字符串、日期等可比较类型
最小值	MIN(col)	返回最小值	SELECT MIN(created_at) FROM logs;	同上
分组聚合	SELECT col, AGG_FUNC(...) FROM table GROUP BY col;	按列分组后聚合	SELECT status, COUNT(*) FROM orders GROUP BY status;	SELECT 中非聚合列必须出现在 GROUP BY 中
过滤分组结果	HAVING condition	对 GROUP BY 结果进行筛选	SELECT dept, AVG(salary) FROM employees GROUP BY dept HAVING AVG(salary) > 5000;	WHERE 不能用于聚合条件，必须用 HAVING

注意：

• 聚合函数忽略 NULL 值（COUNT(*) 除外）；
• 若无匹配行，COUNT 返回 0，其他聚合函数返回 NULL；
• GROUP BY 会自动对分组列去重。

6.4 多表连接（JOIN）

连接类型	语法	用途	代码示例	注意事项
内连接（INNER JOIN）	SELECT ... FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id;	仅返回两表匹配的行	SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id;	默认 JOIN 即 INNER JOIN
左外连接（LEFT JOIN）	SELECT ... FROM t1 LEFT JOIN t2 ON ...;	返回左表全部行，右表无匹配则为 NULL	SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id;	常用于”主表+附属信息”场景
右外连接（RIGHT JOIN）	SELECT ... FROM t1 RIGHT JOIN t2 ON ...;	返回右表全部行，左表无匹配则为 NULL	SELECT u.name, p.title FROM posts p RIGHT JOIN users u ON p.author_id = u.id;	较少使用，多数可用 LEFT JOIN 替代
全外连接	MySQL 不直接支持	返回两表所有行	需用 UNION 模拟	MySQL 无 FULL OUTER JOIN
自连接	SELECT ... FROM table t1 JOIN table t2 ON ...;	表与自身连接	SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;	常用于树形结构（如组织架构）
多表连接	SELECT ... FROM t1 JOIN t2 ON ... JOIN t3 ON ...;	连接三个及以上表	SELECT u.name, o.id, p.title FROM users u JOIN orders o ON u.id=o.user_id JOIN products p ON o.product_id=p.id;	注意连接顺序和 ON 条件
USING 简写	SELECT ... FROM t1 JOIN t2 USING (common_col);	当连接列同名时简化语法	SELECT * FROM users u JOIN profiles p USING (user_id);	等价于 ON t1.common_col = t2.common_col

注意：

• 连接条件应尽量使用索引列；
• LEFT JOIN 中 WHERE 条件若作用于右表，可能将外连接退化为内连接；
• 避免笛卡尔积（未指定 ON 条件的 JOIN）。

6.5 子查询

子查询类型	语法	用途	代码示例	注意事项
标量子查询	WHERE col = (SELECT single_val FROM ...);	返回单个值的子查询	SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE id = 100);	必须只返回一行一列，否则报错
行子查询	WHERE (col1, col2) = (SELECT val1, val2 FROM ...);	返回单行多列	SELECT * FROM products WHERE (category, price) = (SELECT 'electronics', MAX(price) FROM products WHERE category='electronics');	较少使用
列子查询（IN）	WHERE col IN (SELECT col FROM ...);	返回单列多行	SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);	等价于 EXISTS（性能可能不同）
EXISTS 子查询	WHERE EXISTS (SELECT 1 FROM ... WHERE 关联条件);	判断是否存在匹配行	SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);	通常比 IN 更高效，尤其右表大时
FROM 子查询（派生表）	SELECT ... FROM (SELECT ...) AS alias;	将子查询作为临时表	SELECT avg_score FROM (SELECT user_id, AVG(score) AS avg_score FROM exams GROUP BY user_id) AS user_avg WHERE avg_score > 80;	必须为派生表指定别名
相关子查询	子查询中引用外层表列	逐行关联计算	SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u;	性能较差，大表慎用

注意：

• 子查询可嵌套多层，但影响可读性和性能；
• MySQL 5.6+ 对部分子查询做了优化（如 IN 转 JOIN）；
• 尽量用 JOIN 替代相关子查询以提升性能。

7. 事务与锁机制

7.1 事务控制（BEGIN / COMMIT / ROLLBACK）

方法名称	语法	用途	代码示例	注意事项
显式开启事务	START TRANSACTION; 或 BEGIN;	手动开始一个新事务	START TRANSACTION;	两者等效；默认自动提交（autocommit=1）下需显式开启
提交事务	COMMIT;	永久保存事务中所有更改	COMMIT;	提交后无法回滚；释放所有行锁
回滚事务	ROLLBACK;	撤销事务中所有未提交的更改	ROLLBACK;	数据恢复到事务开始前状态
设置保存点	SAVEPOINT sp_name;	在事务中设置回滚标记点	SAVEPOINT before_update;	可设多个保存点
回滚到保存点	ROLLBACK TO SAVEPOINT sp_name;	仅撤销保存点之后的操作	ROLLBACK TO SAVEPOINT before_update;	保存点之前的更改仍保留
释放保存点	RELEASE SAVEPOINT sp_name;	显式删除保存点	RELEASE SAVEPOINT before_update;	非必需，COMMIT/ROLLBACK 后自动清除
自动提交模式开关	SET autocommit = {0 | 1};	控制是否每条语句自动提交	SET autocommit = 0; -- 关闭自动提交	默认为 1；关闭后需手动 COMMIT/ROLLBACK
查看当前事务状态	SELECT @@autocommit, @@transaction_isolation;	检查自动提交和隔离级别	SELECT @@autocommit;	用于调试事务行为

注意：

• DDL 语句（如 CREATE、ALTER、DROP）会隐式提交当前事务；
• MyISAM 引擎不支持事务，只有 InnoDB 支持；
• 事务中应尽量减少操作时间，避免长时间持有锁。

7.2 事务隔离级别

隔离级别	语法	说明	能防止的问题	不能防止的问题	注意事项
READ UNCOMMITTED	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	最低隔离级别，可读未提交数据	无	脏读、不可重复读、幻读	性能最好，但数据一致性最差；极少使用
READ COMMITTED	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	只能读已提交数据	脏读	不可重复读、幻读	Oracle 默认级别；每次 SELECT 都生成新快照
REPEATABLE READ	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	同一事务内多次读结果一致	脏读、不可重复读	幻读（MySQL 通过 MVCC + 间隙锁避免）	MySQL InnoDB 默认级别；保证可重复读
SERIALIZABLE	SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;	最高隔离级别，完全串行执行	脏读、不可重复读、幻读	无	性能最差，所有 SELECT 隐式转为 SELECT ... FOR SHARE

补充说明：

概念	说明
脏读	读到其他事务未提交的修改
不可重复读	同一事务内两次读同一行，值不同（因被 UPDATE）
幻读	同一事务内两次查询，行数不同（因被 INSERT/DELETE）

注意：

• MySQL InnoDB 在 REPEATABLE READ 下通过 Next-Key Lock（行锁+间隙锁）避免幻读；
• 隔离级别可通过 SELECT @@transaction_isolation; 查看；
• 设置仅对当前会话有效，全局设置需用 SET GLOBAL（影响新连接）。

7.3 锁类型与使用场景

锁类型	获取方式	作用范围	兼容性	使用场景	注意事项
共享锁（S Lock）	SELECT ... LOCK IN SHARE MODE;（MySQL 8.0+ 推荐用 FOR SHARE）	允许多个事务同时读同一行	与其他 S 锁兼容，与 X 锁互斥	读取后可能立即更新，防止中间被改	较少使用，易引发死锁
排他锁（X Lock）	SELECT ... FOR UPDATE;	独占一行，禁止其他事务读写（带锁）	与任何锁互斥	”读取-修改-写入”原子操作（如扣库存）	最常用行级锁；仅在事务中生效
意向共享锁（IS）	自动加	表级，表示表中某行将加 S 锁	与 IX、IS 兼容，与 X、S 表锁互斥	InnoDB 自动管理，用户不可见	用于快速判断表是否可加表锁
意向排他锁（IX）	自动加	表级，表示表中某行将加 X 锁	与 IS、IX 兼容，与 X、S 表锁互斥	同上	用户无需干预
记录锁（Record Lock）	自动加（如 WHERE id=10 FOR UPDATE）	单行记录	—	精确匹配索引行的锁定	基于索引，若无索引则锁全表
间隙锁（Gap Lock）	自动加（REPEATABLE READ 下范围查询）	索引记录之间的”间隙”	—	防止幻读（阻止 INSERT 到间隙）	仅 InnoDB 在 RR 级别启用；可关闭
Next-Key Lock	自动加	记录锁 + 间隙锁（左开右闭区间）	—	InnoDB 默认行锁算法，防幻读核心机制	例如 WHERE id > 10 会锁 (10, +∞)
表锁（Table Lock）	LOCK TABLES table_name READ/WRITE;	整个表	—	MyISAM 引擎或批量维护操作	InnoDB 应避免使用；需手动 UNLOCK TABLES
自增锁（AUTO-INC Lock）	插入含 AUTO_INCREMENT 列时	表级，短暂持有	—	保证自增值唯一和连续	MySQL 8.0 改为轻量级锁，提升并发

死锁处理：

• InnoDB 自动检测死锁并回滚代价较小的事务；
• 应用需捕获 1213 错误（Deadlock found）并重试；
• 避免策略：按固定顺序访问表/行，减少事务粒度。

查看锁信息：

命令	说明
SHOW ENGINE INNODB STATUS;	查看 TRANSACTIONS 部分
performance_schema.data_locks	MySQL 8.0+ 锁信息
information_schema.INNODB_LOCKS	已废弃，5.7 及以下可用

8. 索引与性能优化

8.1 索引类型（主键、唯一、普通、全文等）

索引类型	说明	特点	适用场景	注意事项
主键索引（PRIMARY KEY）	唯一标识表中每一行的索引	自动创建聚簇索引（InnoDB）；不允许 NULL；每表仅一个	所有表都应定义主键（如自增 ID）	InnoDB 中数据按主键物理存储；无主键时会隐式生成
唯一索引（UNIQUE INDEX）	保证列值唯一（允许一个 NULL）	非聚簇；可多列组合；每列值必须唯一	用户名、邮箱、身份证号等需唯一字段	插入重复值会报错（Duplicate entry）
普通索引（INDEX / KEY）	最基本的索引类型	加速查询；不强制唯一性；可重复值	高频查询条件列（如 status、category_id）	过多索引影响写性能（INSERT/UPDATE/DELETE）
全文索引（FULLTEXT INDEX）	支持全文搜索（MATCH ... AGAINST）	仅 MyISAM 和 InnoDB（5.6+）支持；基于分词	文章内容、产品描述等文本搜索	不支持中文分词（需配合 ngram 插件或外部工具如 Elasticsearch）
前缀索引	对字符串列的前 N 个字符建索引	节省空间；适用于长字符串	url、email、长名称等	需通过 SELECT COUNT(DISTINCT LEFT(col, N))/COUNT(*) 评估区分度
组合索引（复合索引）	多列组成的单个索引	遵循最左前缀原则；减少索引数量	多条件联合查询（如 WHERE a=1 AND b=2）	(a,b,c) 索引可支持 (a)、(a,b)、(a,b,c) 查询，但不支持 (b) 或 (c)
覆盖索引	查询列全部包含在索引中	无需回表查数据行；性能极高	SELECT id,name FROM users WHERE status='active'	尽量让 SELECT 列包含在索引中
降序索引（MySQL 8.0+）	明确指定索引列排序方向	支持 ASC/DESC 混合	ORDER BY col1 ASC, col2 DESC	旧版本所有索引逻辑上都是升序

注意：

• InnoDB 使用 B+Tree 结构存储索引；
• 索引列应尽量 NOT NULL（NULL 值不参与索引优化）；
• 频繁更新的列不宜建索引（维护成本高）。

8.2 创建与删除索引

操作名称	语法	用途	代码示例	注意事项
创建普通索引	CREATE INDEX idx_name ON table_name (col);	为现有表添加普通索引	CREATE INDEX idx_email ON users (email);	可在线操作（MySQL 5.6+ Online DDL）
创建唯一索引	CREATE UNIQUE INDEX uk_name ON table_name (col);	添加唯一约束索引	CREATE UNIQUE INDEX uk_phone ON users (phone);	若存在重复值会失败
创建组合索引	CREATE INDEX idx_composite ON table_name (col1, col2);	多列联合索引	CREATE INDEX idx_status_created ON orders (status, created_at);	顺序很重要，高频过滤列放前面
创建前缀索引	CREATE INDEX idx_prefix ON table_name (col(N));	对字符串前 N 字符建索引	CREATE INDEX idx_url ON logs (url(50));	N 应足够区分不同值
建表时定义索引	CREATE TABLE ... ( ..., INDEX idx_name (col), UNIQUE KEY uk_name (col) );	在 CREATE TABLE 中直接声明	CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), INDEX idx_name (name) );	推荐方式，结构清晰
删除索引	DROP INDEX idx_name ON table_name;	移除指定索引	DROP INDEX idx_old ON users;	主键索引不能用此方式删除
删除主键索引	ALTER TABLE table_name DROP PRIMARY KEY;	移除主键（仅当未自增时）	ALTER TABLE temp_table DROP PRIMARY KEY;	若主键是自增列，需先 DROP 列或修改属性
查看表索引	SHOW INDEX FROM table_name;	列出所有索引详情	SHOW INDEX FROM orders;	包含列名、唯一性、Cardinality（基数）等
强制使用索引（Hint）	SELECT ... FROM table FORCE INDEX (idx_name) WHERE ...;	强制优化器使用指定索引	SELECT * FROM users FORCE INDEX (idx_email) WHERE email LIKE '%@example.com';	仅用于调试，一般不应硬编码

注意：

• 大表加索引可能耗时较长（即使 Online DDL 也会短暂锁表）；
• 删除不用的索引可提升写性能；
• MySQL 8.0 支持不可见索引（INVISIBLE INDEX），便于测试删除影响。

8.3 EXPLAIN 执行计划分析

EXPLAIN 字段	说明	常见值及含义	优化建议	注意事项
id	查询序列号	相同：从上到下执行；不同：id 大的先执行（子查询）	—	多表连接时用于判断执行顺序
select_type	查询类型	SIMPLE（简单查询）、PRIMARY（最外层）、SUBQUERY（子查询）、DERIVED（派生表）	避免 DEPENDENT SUBQUERY（相关子查询）	DERIVED 表示 FROM 子查询
table	表名	实际表名或别名，或 <derivedN>（派生表）	—	<unionM,N> 表示 UNION 结果
partitions	分区匹配	若未分区则为 NULL	—	分区表才显示
type	访问类型（关键指标）	system > const > eq_ref > ref > range > index > ALL	目标：至少达到 range，避免 ALL（全表扫描）	const：主键/唯一索引等值查询；ref：非唯一索引等值；range：范围查询
possible_keys	可能使用的索引	列出优化器考虑的索引	若为 NULL，说明无可用索引	不代表实际使用
key	实际使用的索引	优化器最终选择的索引	若为 NULL 且 type=ALL，需加索引	可通过 FORCE INDEX 强制指定
key_len	使用索引的字节数	数值越小越好（表示索引前缀短）	检查是否用到了组合索引的全部列	例如：utf8mb4 字符串(20) → 20*4 + 2 = 82 字节
ref	与索引比较的列/常量	const（常量）、func（函数）、表列名	避免 func（如 WHERE YEAR(col)=2025）	表示索引如何被使用
rows	估算扫描行数	数值越小越好	若远大于实际返回行数，说明索引效率低	基于统计信息，可能不准
filtered	按条件过滤后剩余百分比	100 表示无过滤，10 表示 90% 被过滤	越高越好（说明 WHERE 条件高效）	与 rows 结合看实际处理量
Extra	额外信息（关键）	Using index（覆盖索引）、Using where、Using filesort、Using temporary	避免：Using filesort（额外排序）、Using temporary（临时表）	Using index 是好现象；filesort 常因 ORDER BY 无索引引起

常用优化技巧：

• 对 WHERE、JOIN、ORDER BY、GROUP BY 涉及的列建索引；
• 组合索引遵循”等值在前，范围在后”原则；
• 避免在索引列上使用函数或表达式；
• 使用 EXPLAIN FORMAT=JSON 获取更详细信息（MySQL 5.6+）；
• 定期运行 ANALYZE TABLE 更新统计信息，帮助优化器选择正确索引。

9. 备份与恢复

9.1 逻辑备份（mysqldump）

操作名称	语法	用途	代码示例	注意事项
备份单个数据库	mysqldump -u 用户 -p --databases db_name > backup.sql	导出指定数据库的结构和数据	mysqldump -u root -p --databases myapp > myapp_20260202.sql	包含 CREATE DATABASE 和 USE 语句
备份多个数据库	mysqldump -u 用户 -p --databases db1 db2 > backup.sql	同时备份多个库	mysqldump -u root -p --databases sales inventory > multi_db.sql	—
备份所有数据库	mysqldump -u 用户 -p --all-databases > full.sql	全量逻辑备份（含 mysql、sys 等系统库）	mysqldump -u root -p --all-databases > full_backup.sql	慎用：包含用户权限信息，恢复时可能覆盖现有账户
仅备份表结构	mysqldump -u 用户 -p --no-data db_name > schema.sql	只导出建表语句，不含数据	mysqldump -u root -p --no-data myapp > myapp_schema.sql	适用于迁移结构
仅备份数据（无建表语句）	mysqldump -u 用户 -p --no-create-info db_name > data.sql	只导出 INSERT 语句	mysqldump -u root -p --no-create-info myapp > myapp_data.sql	需确保目标表已存在
备份指定表	mysqldump -u 用户 -p db_name table1 table2 > tables.sql	仅备份库中部分表	mysqldump -u root -p myapp users orders > user_order.sql	表名之间用空格分隔
带事务一致性备份（InnoDB）	mysqldump -u 用户 -p --single-transaction --routines --triggers db_name > backup.sql	在不锁表情况下获得一致备份	mysqldump -u root -p --single-transaction --routines --triggers myapp > consistent.sql	推荐用于 InnoDB；--routines 导出存储过程/函数
带主从位置信息	mysqldump -u 用户 -p --master-data=2 db_name > backup.sql	在输出中注释记录 binlog 文件名和位置	mysqldump -u root -p --master-data=2 myapp > backup_with_pos.sql	=1 为可执行 CHANGE MASTER 语句，=2 为注释形式
压缩备份输出	mysqldump ... | gzip > backup.sql.gz	减少磁盘占用	mysqldump -u root -p myapp | gzip > myapp.sql.gz	—
排除特定表	mysqldump ... --ignore-table=db.table	跳过某些大表或临时表	mysqldump -u root -p myapp --ignore-table=myapp.logs > no_logs.sql	可多次使用 --ignore-table

注意：

• mysqldump 是逻辑备份，生成 SQL 文本，跨版本兼容性好；
• MyISAM 表在备份时会加读锁（FLUSH TABLES WITH READ LOCK），而 --single-transaction 对 MyISAM 无效；
• 大数据库备份建议在业务低峰期进行；
• 备份文件应妥善保管并定期验证。

9.2 物理备份（mysqlbackup / xtrabackup）

工具/操作	语法	用途	代码示例	注意事项
MySQL Enterprise Backup（mysqlbackup）	mysqlbackup --user=root --password=xxx --backup-dir=/backup/full backup	官方企业版物理备份工具	mysqlbackup --user=root --password=pass --backup-dir=/opt/backups backup	需企业版许可证；支持压缩、加密、增量备份
Percona XtraBackup（开源）	xtrabackup --user=root --password=xxx --backup --target-dir=/backup/full	开源物理热备工具（仅 InnoDB）	xtrabackup --user=root --password=pass --backup --target-dir=/data/backup	免费且广泛使用；支持在线备份
XtraBackup 准备备份（apply-log）	xtrabackup --prepare --target-dir=/backup/full	将备份数据重做日志应用，使其一致	xtrabackup --prepare --target-dir=/data/backup	恢复前必须执行此步
XtraBackup 增量备份	xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full	基于全量备份的增量备份	xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full	节省空间和时间
XtraBackup 恢复（复制文件）	rsync -avrP /backup/full/ /var/lib/mysql/	将准备好的备份复制回数据目录	rsync -avrP /data/backup/ /var/lib/mysql/	需停止 MySQL 服务；确保权限正确（chown -R mysql:mysql）
备份 MyISAM 表（XtraBackup）	xtrabackup 自动处理	XtraBackup 8.0+ 支持 MyISAM（通过短暂锁）	—	旧版需配合 --lock-ddl 或手动处理
流式备份（节省磁盘）	xtrabackup --backup --stream=xbstream | gzip > backup.xb.gz	直接输出流式压缩包	xtrabackup --backup --stream=xbstream | gzip > full.xb.gz	适合网络传输或直接存对象存储

注意：

• 物理备份直接复制数据文件，速度远快于逻辑备份；
• 仅适用于同架构、同版本（或兼容版本）的 MySQL 实例恢复；
• XtraBackup 不支持 Windows；
• 恢复后需检查 innodb_force_recovery 等参数是否需调整；
• 物理备份无法跨存储引擎迁移（如 InnoDB → MyISAM）。

9.3 数据恢复操作

恢复场景	操作步骤	代码示例	注意事项
从逻辑备份恢复单库	1. 创建空数据库 / 2. 执行 SQL 脚本	mysql -u root -p -e "CREATE DATABASE myapp;" mysql -u root -p myapp < myapp_backup.sql	若备份含 CREATE DATABASE，可直接导入：mysql -u root -p < full_backup.sql
从逻辑备份恢复全库	直接导入全量 SQL	mysql -u root -p < full_backup.sql	会覆盖现有用户和权限；建议先备份当前权限
从压缩逻辑备份恢复	先解压再导入，或管道导入	zcat myapp.sql.gz | mysql -u root -p myapp	或 gunzip -c myapp.sql.gz | mysql -u root -p myapp
从物理备份恢复	1. 停止 MySQL / 2. 清空原 datadir / 3. 复制备份文件 / 4. 修复权限 / 5. 启动 MySQL	systemctl stop mysqld rm -rf /var/lib/mysql/* rsync -avrP /backup/full/ /var/lib/mysql/ chown -R mysql:mysql /var/lib/mysql systemctl start mysqld	必须先 --prepare（XtraBackup）；确保 SELinux/AppArmor 不阻止
基于时间点恢复（PITR）	1. 恢复最近全备 / 2. 使用 mysqlbinlog 回放 binlog	mysqlbinlog --start-datetime="2026-02-01 10:00:00" --stop-datetime="2026-02-02 09:00:00" binlog.000001 | mysql -u root -p	需提前开启 binlog（log-bin）；记录备份时的 binlog 位置（--master-data）
跳过错误继续恢复	mysql -f < backup.sql	mysql -f -u root -p myapp < partial_backup.sql	强制忽略 SQL 错误继续执行
恢复单表（逻辑）	从全库备份中提取建表和数据语句	sed -n '/^-- Table structure for table users/,/^-- Table structure for table/p' full.sql > users.sql	或使用第三方工具如 mydumper/myloader

通用注意事项：

• 恢复前务必停止写入，避免数据冲突；
• 生产恢复应在测试环境先验证；
• 重要系统应制定 RTO（恢复时间目标）和 RPO（恢复点目标）；
• 定期演练恢复流程，确保备份有效；
• 恢复后检查数据完整性（如 COUNT、校验和）。

10. 常用命令行工具

10.1 mysql 命令行客户端

功能/操作	语法/命令	用途	代码示例	注意事项
启动交互式客户端	mysql [options]	连接 MySQL 服务器并进入交互模式	mysql -u root -p	默认连接 localhost:3306
执行单条 SQL	mysql -u 用户 -p -e "SQL"	非交互式执行 SQL 并返回结果	mysql -u app -p -e "SELECT COUNT(*) FROM logs;"	适用于脚本自动化
指定数据库	mysql -u 用户 -p -D dbname	登录后自动 USE 指定库	mysql -u root -p -D myapp	可省去手动 USE 步骤
使用配置文件	mysql --defaults-file=/path/my.cnf	从指定配置文件读取连接参数	mysql --defaults-file=./client.cnf	配置文件中可含 [client] 段的 user、password、host 等
批处理模式（制表符分隔）	mysql -B -u 用户 -p -e "SQL"	输出适合 awk/sed 处理的格式	mysql -B -u root -p -e "SELECT id,name FROM users;"	列间用 tab 分隔，无边框
垂直显示结果	mysql -E -u 用户 -p -e "SQL"	当列宽过大时每列一行显示	mysql -E -u root -p -e "DESCRIBE wide_table;"	便于查看宽表结构
安全密码输入	mysql_config_editor set --login-path=local --user=root --password	创建加密登录凭证	mysql --login-path=local -e "SHOW DATABASES;"	密码存储在 ~/.mylogin.cnf，避免明文暴露
客户端内置命令	\h	显示帮助	mysql> \h	其他常用：\q（退出）、\G（垂直输出）、\T filename（日志记录）
记录会话日志	\T /path/to/logfile	将后续操作写入日志文件	mysql> \T /tmp/mysql_session.log	用 \t 停止记录

注意：

• 客户端默认使用 utf8mb4 字符集（若服务端支持）；
• 密码避免在命令行中直接写（如 -pMyPass），以防被 history 或 ps 捕获；
• 支持多语句执行（需启用 --force 或客户端设置）。

10.2 mysqldump

功能/选项	语法	用途	代码示例	注意事项
基本备份	mysqldump [opts] db_name [tbl...] > file.sql	逻辑备份数据库或表	mysqldump -u root -p myapp > backup.sql	默认包含 DROP TABLE 和 CREATE TABLE
一致性备份（InnoDB）	--single-transaction	在不锁表情况下获得一致快照	mysqldump -u root -p --single-transaction myapp > backup.sql	推荐用于 InnoDB；对 MyISAM 无效
锁表备份（MyISAM）	--lock-all-tables	全局读锁确保一致性	mysqldump -u root -p --lock-all-tables myapp > backup.sql	会阻塞写操作，慎用于生产高峰
包含存储过程/函数	--routines	导出存储程序	mysqldump -u root -p --routines myapp > backup.sql	默认不导出
包含事件调度器	--events	导出事件（Event）	mysqldump -u root -p --events myapp > backup.sql	—
包含触发器	--triggers	导出表触发器（默认开启）	mysqldump -u root -p --triggers myapp > backup.sql	可用 --skip-triggers 禁用
记录 binlog 位置	--master-data=2	在输出中注释记录 binlog 文件和位置	mysqldump -u root -p --master-data=2 myapp > backup.sql	用于搭建从库或 PITR（时间点恢复）
设置字符集	--default-character-set=utf8mb4	指定导出字符集	mysqldump -u root -p --default-character-set=utf8mb4 myapp > backup.sql	避免乱码
忽略特定表	--ignore-table=db.table	跳过某些表	mysqldump -u root -p myapp --ignore-table=myapp.logs > backup.sql	可多次使用
压缩输出	| gzip	减少备份体积	mysqldump -u root -p myapp | gzip > backup.sql.gz	恢复时需解压或管道导入
仅数据（无建表语句）	--no-create-info	仅导出 INSERT 语句	mysqldump -u root -p --no-create-info myapp users > data_only.sql	用于数据迁移
仅结构（无数据）	--no-data	仅导出 DDL	mysqldump -u root -p --no-data myapp > schema.sql	用于版本控制或结构对比

注意：

• mysqldump 是单线程工具，大库备份较慢；
• 恢复时需确保目标 MySQL 版本兼容；
• 生产环境建议结合 --single-transaction + --master-data 实现热备。

10.3 mysqladmin

功能/命令	语法	用途	代码示例	注意事项
查看服务器状态	mysqladmin -u root -p status	显示简要运行状态（Uptime、Threads、Questions 等）	mysqladmin -u root -p status	快速检查服务是否正常
查看详细变量	mysqladmin -u root -p variables	列出所有系统变量	mysqladmin -u root -p variables | grep max_connections	用于排查配置问题
刷新权限	mysqladmin -u root -p reload	重新加载权限表（等价于 FLUSH PRIVILEGES）	mysqladmin -u root -p reload	在直接修改 mysql.user 表后使用
刷新日志	mysqladmin -u root -p flush-logs	关闭当前日志并新建（binlog、slow log 等）	mysqladmin -u root -p flush-logs	用于日志轮转
关闭服务	mysqladmin -u root -p shutdown	安全关闭 MySQL 服务	mysqladmin -u root -p shutdown	需 SHUTDOWN 权限
创建数据库	mysqladmin -u root -p create db_name	创建新数据库	mysqladmin -u root -p create test_db	等价于 CREATE DATABASE
删除数据库	mysqladmin -u root -p drop db_name	删除数据库（交互确认）	mysqladmin -u root -p drop old_db	危险操作，会提示确认
查看进程列表	mysqladmin -u root -p processlist	显示当前连接和查询	mysqladmin -u root -p processlist	类似 SHOW PROCESSLIST
Ping 服务	mysqladmin -u root -p ping	检查 MySQL 是否响应	mysqladmin -u root -p ping	返回 “mysqld is alive” 表示正常
设置密码	mysqladmin -u root -p password 'newpass'	修改当前用户密码	mysqladmin -u root -p password 'NewPass123!'	MySQL 8.0+ 不推荐，应使用 ALTER USER

注意：

• mysqladmin 主要用于管理任务，而非数据操作；
• 所有操作需对应权限（如 shutdown、reload）；
• 部分功能在云数据库（如 RDS）中受限。

10.4 mysqlshow

功能/命令	语法	用途	代码示例	注意事项
列出所有数据库	mysqlshow -u root -p	显示服务器上所有数据库	mysqlshow -u root -p	等价于 SHOW DATABASES
查看某库的表	mysqlshow -u root -p db_name	列出指定数据库的所有表	mysqlshow -u root -p myapp	—
查看表结构	mysqlshow -u root -p db_name table_name	显示表的列信息	mysqlshow -u root -p myapp users	等价于 DESCRIBE users
查看索引信息	mysqlshow -u root -p --keys db_name table_name	显示表的索引详情	mysqlshow -u root -p --keys myapp orders	包含索引名、列、唯一性等
查看列详细信息	mysqlshow -u root -p --columns db_name table_name	显示列的完整元数据	mysqlshow -u root -p --columns myapp users	包含类型、是否为空、默认值等
查看状态信息	mysqlshow -u root -p --status	显示类似 mysqladmin status 的信息	mysqlshow -u root -p --status	较少使用
查看权限信息	mysqlshow -u root -p --privileges	显示用户权限（需高权限）	mysqlshow -u root -p --privileges	输出冗长，一般用 SHOW GRANTS

注意：

• mysqlshow 是 SHOW 语句的命令行封装，功能较基础；
• 适合快速查看元数据，复杂查询仍需进入 mysql 客户端；
• 不支持 SQL 条件过滤，仅展示静态结构。