一、MySQL 安装目录和配置文件全解析

MySQL 的安装目录结构和配置文件是管理、优化 MySQL 服务的核心基础。不同操作系统（Windows/macOS/Linux）的安装目录路径、配置文件位置差异较大，且默认路径与自定义安装路径也会不同。本文将从 操作系统分类 出发，详细拆解安装目录结构、核心配置文件的作用与参数，同时覆盖配置文件修改、生效方式等实战场景，帮你彻底掌握 MySQL 的 “底层架构”。

1.1 MySQL 安装类型与路径差异

MySQL 主要有两种安装方式，不同方式的目录结构完全不同，需先区分：

1.2 各操作系统的安装目录解析

1.2.1 Windows 系统（最常见场景）

Windows 下 MySQL 主要有 “免安装版（ZIP）” 和 “安装版（.msi）” 两种，路径差异较大。

1免安装版（ZIP 包，推荐开发环境）

默认解压路径：如 D:\mysql-8.0.36-winx64（版本号因安装包而异），目录结构如下：

mysql-8.0.36-winx64/
├─ bin/                  # 核心二进制文件（命令行工具、服务程序）
│  ├─ mysql.exe          # 命令行客户端（连接 MySQL 服务）
│  ├─ mysqld.exe         # MySQL 服务程序（启动/停止服务的核心）
│  ├─ mysqldump.exe      # 数据备份工具（导出数据库）
│  ├─ mysqladmin.exe     # 服务管理工具（创建用户、修改密码等）
│  └─ mysqlcheck.exe     # 表修复/优化工具
├─ data/                 # 数据存储目录（初始化后自动生成）
│  ├─ mysql/             # 系统数据库（存储用户、权限等核心数据）
│  ├─ performance\_schema/ # 性能监控数据库
│  ├─ sys/               # 系统简化视图数据库（方便查询性能数据）
│  └─ 自定义数据库/       # 自己创建的数据库（如 shop\_db）
├─ include/              # 头文件（C/C++ 开发时引用，一般用不到）
├─ lib/                  # 依赖库文件（服务运行和开发时依赖）
├─ share/                # 共享文件（字符集、错误信息、帮助文档）
│  └─ charsets/          # 支持的字符集文件（如 utf8mb4）
├─ my.ini                # 核心配置文件（需手动创建，指定端口、数据目录等）
└─ README.md             # 官方说明文档

2. 安装版（.msi 包，默认路径）

通过官方 .msi 安装时，默认路径为 C:\Program Files\MySQL\MySQL Server 8.0，目录结构与免安装版类似，但部分目录位置不同：

• 数据目录：默认不在安装目录下，而是 C:\ProgramData\MySQL\MySQL Server 8.0\Data（ProgramData 是隐藏文件夹，需开启 “显示隐藏文件”）。
• 配置文件：C:\ProgramData\MySQL\MySQL Server 8.0\my.ini（核心配置），安装目录下的 my.ini 可能是模板，以 ProgramData 下的为准。
• 服务注册：安装时会自动注册为 Windows 服务（可通过 “服务” 面板启动 / 停止，服务名默认 MySQL80）。

1.2.2 macOS 系统

macOS 下 MySQL 主要通过 官方 .dmg 安装包 或 Homebrew 安装，路径差异较大。

1. 官方 .dmg 安装包（推荐）

默认安装路径：/usr/local/mysql/（版本号会自动添加，如 mysql-8.0.36-macos13-x86_64，一般会创建软链接 mysql 指向该目录），目录结构：

/usr/local/mysql/
├─ bin/                  # 二进制工具（mysql、mysqld、mysqldump 等，与 Windows 一致）
├─ data/                 # 数据目录（系统库、自定义库存储，默认路径）
├─ include/              # 头文件
├─ lib/                  # 依赖库
├─ share/                # 共享文件（字符集、帮助文档）
├─ support-files/        # 辅助脚本（如启动脚本 mysql.server）
├─ my.cnf                # 配置文件模板（默认无实际配置，需手动创建或修改）
└─ README.md             # 说明文档

• 配置文件优先级：macOS 会优先读取 /etc/my.cnf > /etc/mysql/my.cnf > /usr/local/mysql/my.cnf，建议在 /etc/my.cnf 手动创建配置文件（需管理员权限：sudo vim /etc/my.cnf）。
• 服务管理：可通过系统偏好设置的 “MySQL” 面板启动 / 停止，或命令行：`sudo

2. Homebrew 安装（便捷）

通过 brew install mysql 安装，路径更隐蔽：

• 安装目录：/usr/local/Cellar/mysql/8.0.36（版本号随安装版本变化）。
• 数据目录：/usr/local/var/mysql/。
• 配置文件：默认无，需手动在 /usr/local/etc/my.cnf 创建。
• 服务管理：brew services start mysql（启动）、brew services stop mysql（停止）。

1.2.3 Linux 系统（CentOS/Ubuntu，服务器场景）

Linux 是 MySQL 服务器的主流环境，主要通过 RPM 包（CentOS） 或 DEB 包（Ubuntu） 安装，路径标准化程度高。

1. CentOS 系统（RPM 包安装）

通过官方 RPM 包或 yum 安装（yum install mysql-server），目录结构如下：

/
├─ /usr/bin/             # 二进制工具（mysql、mysqldump 等，系统全局可调用）
├─ /usr/sbin/mysqld      # MySQL 服务程序（核心，启动服务的二进制文件）
├─ /var/lib/mysql/       # 数据目录（系统库、自定义库，默认路径）
├─ /usr/share/mysql/     # 共享文件（字符集、错误信息、帮助文档）
├─ /etc/my.cnf           # 核心配置文件（Linux 下唯一默认配置文件，优先级最高）
├─ /etc/my.cnf.d/        # 配置文件扩展目录（可放自定义子配置，如 mysql-server.cnf）
└─ /var/log/mysqld.log   # 日志文件（服务启动日志、错误日志，排查问题关键）

• 服务管理：通过 systemctl 命令：systemctl start mysqld（启动）、systemctl stop mysqld（停止）、systemctl enable mysqld（开机自启）。
• 初始密码：RPM 安装后，初始密码会存放在 /var/log/mysqld.log 中，需通过 grep 'temporary password' /var/log/mysqld.log 查看。

2. Ubuntu 系统（DEB 包安装）

通过 apt-get install mysql-server 安装，目录结构与 CentOS 类似，但部分路径不同：

• 数据目录：/var/lib/mysql/（与 CentOS 一致）。
• 配置文件：/etc/mysql/my.cnf（主配置），扩展配置在 /etc/mysql/mysql.conf.d/（如 mysqld.cnf 是服务核心配置）。
• 日志文件：/var/log/mysql/error.log（错误日志）。
• 服务管理：systemctl start mysql（启动，服务名是 mysql 而非 mysqld）、systemctl stop mysql。

1.3 核心配置文件（my.ini/my.cnf）全解析

MySQL 的配置文件是 my.ini（Windows）或 my.cnf（macOS/Linux），用于定义服务端口、数据目录、字符集、缓存大小等核心参数。所有参数分为不同 “段落”（如 [mysqld] 是服务端配置，[mysql] 是客户端配置），修改后需重启服务生效。

1.3.1 配置文件段落说明

1.3.2 必知核心参数（[mysqld] 段落）

[mysqld] 是配置文件中最重要的段落，直接影响 MySQL 服务的运行、性能和安全性，以下是开发 / 运维必改的参数：

1.3.3 客户端配置（[mysql] 段落）

用于优化命令行客户端（mysql.exe）的使用体验，避免每次连接都指定参数：

[mysql]
default-character-set = utf8mb4  # 客户端默认字符集（与服务端一致，避免乱码）
port = 3306                      # 默认连接端口
user = root                      # 默认登录用户名（可选，不建议，安全性低）
password = 123456                # 默认登录密码（强烈不建议，明文存储不安全）

1.3.4 配置文件修改实战（以 Windows 为例）

1. 找到配置文件：免安装版在 D:\mysql-8.0.36-winx64\my.ini，安装版在 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini。

2. 编辑配置：用记事本或 Notepad++ 打开，添加 / 修改参数（以设置字符集和数据目录为例）：

   [mysqld]
   port = 3306
   datadir = D:/mysql-8.0.36-winx64/data  # 确保路径正确，用 / 或 \\
   character-set-server = utf8mb4
   collation-server = utf8mb4_general_ci
   max_connections = 500
   innodb_buffer_pool_size = 1G
   log-error = D:/mysql-8.0.36-winx64/logs/error.log  # 需手动创建 logs 文件夹
   [mysql]
   default-character-set = utf8mb4
   

重启服务生效：

• Windows 免安装版：命令行执行 net stop mysql（停止）→ net start mysql（启动，需先注册服务）。
• Windows 安装版：通过 “服务” 面板找到 MySQL80，右键 “重启”。
• Linux：systemctl restart mysqld。

二、MySQL 库表操作

MySQL 中 “库”（数据库）和 “表” 是数据存储的核心载体，所有数据操作均围绕二者展开。本指南从数据库全生命周期操作和数据表全生命周期操作两大维度，覆盖创建、查询、修改、删除、优化等所有关键场景，同时包含约束、索引、事务等进阶操作，提供完整的实战参考。

2.1 数据库（Database）操作

数据库是表的 “容器”，一个 MySQL 服务可管理多个数据库，操作需遵循 “先创建 / 选择，再使用 / 修改，最后删除（谨慎）” 的逻辑。

2.1.1 数据库核心操作（CRUD）

1. 创建数据库（CREATE DATABASE）

创建数据库时需指定字符集（默认 latin1，推荐 utf8mb4 以支持 emoji 和特殊字符）和排序规则（默认 utf8mb4_general_ci，区分大小写用 utf8mb4_bin）。

-- 基础创建（默认字符集）
CREATE DATABASE IF NOT EXISTS shop_db;

-- 指定字符集和排序规则（推荐）
CREATE DATABASE IF NOT EXISTS shop_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

• IF NOT EXISTS：避免数据库已存在时抛出错误，是生产环境的 “安全操作”。
• 字符集说明：utf8 实际仅支持 3 字节字符，utf8mb4 支持 4 字节字符（含 emoji），推荐优先使用。

2. 查询数据库（SHOW / SELECT）

用于查看已存在的数据库、数据库详情或当前使用的数据库。

-- 1. 查看所有数据库
SHOW DATABASES;

-- 2. 查看指定数据库的创建语句（含字符集、排序规则）
SHOW CREATE DATABASE shop_db;

-- 3. 查看当前正在使用的数据库
SELECT DATABASE();

3. 使用数据库（USE）

操作表前必须 “切换” 到目标数据库，后续所有表操作默认在该数据库下执行。

USE shop_db;  -- 切换到 shop_db 数据库

4. 修改数据库（ALTER DATABASE）

仅支持修改数据库的字符集和排序规则，不支持修改数据库名（修改名需用 RENAME DATABASE，但该命令在 MySQL 8.0 中已废弃，风险极高，不推荐）。

-- 修改 shop_db 的字符集为 utf8mb4，排序规则为 utf8mb4_bin
ALTER DATABASE shop_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

5. 删除数据库（DROP DATABASE）

高危操作！删除数据库会同时删除其下所有表和数据，且无法恢复，必须加 IF EXISTS 并确认操作。

DROP DATABASE IF EXISTS shop_db;

2.1.2 数据库权限与用户（进阶）

若需多用户管理不同数据库，需通过 CREATE USER 和 GRANT 分配权限（避免直接使用 root 账号操作业务库）。

-- 1. 创建用户（用户名：shop_user，密码：Shop@123456，仅允许本地访问）
CREATE USER IF NOT EXISTS 'shop_user'@'localhost' IDENTIFIED BY 'Shop@123456';

-- 2. 授予用户对 shop_db 的所有权限（含表操作，但不含删除数据库）
GRANT ALL PRIVILEGES ON shop_db.\* TO 'shop_user'@'localhost';

-- 3. 刷新权限（使权限生效）
FLUSH PRIVILEGES;

-- 4. 查看用户权限
SHOW GRANTS FOR 'shop_user'@'localhost';

-- 5. 回收权限（如需）
REVOKE DELETE ON shop_db.\* FROM 'shop_user'@'localhost';

2.2 数据表（Table）操作

2.2.1 数据表核心操作（CRUD）

表是数据的 “结构化载体”，操作需覆盖结构定义（字段、类型、约束）、结构修改、数据关联（索引、外键）、表删除等场景。

1. 数据表核心操作（CRUD）

创建表是最核心的操作，需明确字段名、数据类型、约束（确保数据有效性）和表选项（字符集、存储引擎）。

MySQL 常用存储引擎：

• InnoDB（默认）：支持事务、外键、行级锁，适合业务表（如用户表、订单表）。
• MyISAM：不支持事务和外键，仅支持表级锁，适合只读场景（如日志表、统计报表）。

-- 1. 添加唯一约束（确保 user_nickname 不重复）
ALTER TABLE user_info
ADD UNIQUE INDEX idx_user_nickname (user_nickname);

-- 2. 删除唯一约束（需指定索引名，通过 SHOW CREATE TABLE 查看）
ALTER TABLE user_info
DROP INDEX idx_user_nickname;

-- 3. 添加外键约束（示例：订单表 orders 关联用户表 user_info）
-- 先创建订单表
CREATE TABLE IF NOT EXISTS orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    user_id INT UNSIGNED NOT NULL COMMENT '关联用户ID',
    order_amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    order_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
    -- 外键约束：user_id 关联 user_info 的 id
    CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id)
    REFERENCES user_info (id)
    -- 外键动作：删除用户时，处理关联订单（RESTRICT 禁止删除，CASCADE 级联删除）
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE = InnoDB COMMENT = '订单表';

-- 4. 删除外键约束（需指定外键名，通过 SHOW CREATE TABLE 查看）
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_user_id;

关键说明：

• 数据类型选择：

  • 整数：优先用 UNSIGNED（无负数，扩大存储范围），如 INT UNSIGNED（0-4294967295）、TINYINT UNSIGNED（0-255）。
  • 字符串：固定长度用 CHAR（如手机号、身份证号），可变长度用 VARCHAR（如用户名、邮箱），避免过度预留长度（如 VARCHAR(2000) 会浪费内存）。
  • 时间：DATE（年月日）、DATETIME（年月日时分秒，支持范围广）、TIMESTAMP（依赖系统时间，范围小，不推荐）。

• 约束：PRIMARY KEY（主键）、UNIQUE（唯一）、NOT NULL（非空）、DEFAULT（默认值）、ENUM（枚举），约束是数据一致性的 “第一道防线”。

2. 查询表（SHOW / DESC）

用于查看表结构、表创建语句、表状态等信息。

-- 1. 查看当前数据库下所有表
SHOW TABLES;

-- 2. 查看表结构（字段名、类型、是否为空、键、默认值、备注）
DESC users;  -- 或 SHOW COLUMNS FROM users;

-- 3. 查看表的创建语句（含存储引擎、索引、约束）
SHOW CREATE TABLE users;

-- 4. 查看表状态（如行数、数据大小、更新时间）
SHOW TABLE STATUS LIKE 'users';  -- LIKE 支持模糊匹配（如 'user%'）

3. 修改表（ALTER TABLE）

表结构需调整时（如新增字段、修改字段类型、删除索引），用 ALTER TABLE，操作需谨慎（尤其是生产环境，可能锁表）。

常用修改场景：

-- 1. 新增字段（在 status 字段后新增“昵称”字段）
ALTER TABLE users
ADD COLUMN nickname VARCHAR(50) DEFAULT '' COMMENT '用户昵称'
AFTER status;  -- AFTER 指定位置，FIRST 表示放在第一列（默认在最后）

-- 2. 修改字段类型（将 nickname 长度从 50 改为 80，同时保留默认值）
ALTER TABLE users
MODIFY COLUMN nickname VARCHAR(80) DEFAULT '' COMMENT '用户昵称';

-- 3. 修改字段名（将 nickname 改为 user_nickname，需重新指定类型和约束）
ALTER TABLE users
CHANGE COLUMN nickname user_nickname VARCHAR(80) DEFAULT '' COMMENT '用户昵称';

-- 4. 删除字段（删除 birthday 字段，需确认该字段无业务依赖）
ALTER TABLE users
DROP COLUMN birthday;

-- 5. 重命名表（将 users 改为 user_info，推荐用此方式，比 RENAME DATABASE 安全）
ALTER TABLE users
RENAME TO user_info;  -- 或 RENAME TABLE users TO user_info;

-- 6. 修改表备注
ALTER TABLE user_info
COMMENT '用户信息表（含昵称）';

-- 7. 修改表存储引擎（从 InnoDB 改为 MyISAM，需确认无事务依赖）
ALTER TABLE user_info
ENGINE = MyISAM;

4. 删除表（DROP TABLE / TRUNCATE TABLE）

• DROP TABLE：删除表结构和所有数据，无法恢复，高危操作。
• TRUNCATE TABLE：清空表中所有数据，但保留表结构，效率比 DELETE FROM 表名 高（直接重置表，不记录日志），同样无法恢复。

-- 1. 删除表（谨慎！）
DROP TABLE IF EXISTS user_info;

-- 2. 清空表数据（保留结构）
TRUNCATE TABLE user_info;

注意：TRUNCATE 会重置自增主键（如 id 会从 1 重新开始），而 DELETE FROM user_info 不会重置自增。

2.2.2 数据表约束与索引（进阶）

约束确保数据 “有效性”，索引确保查询 “高效性”，是表设计的核心优化点。

1. 约束管理

除了创建表时定义约束，还可单独添加 / 删除约束（主键、唯一、外键等）。

-- 1. 添加唯一约束（确保 user_nickname 不重复）
ALTER TABLE user_info
ADD UNIQUE INDEX idx_user_nickname (user_nickname);

-- 2. 删除唯一约束（需指定索引名，通过 SHOW CREATE TABLE 查看）
ALTER TABLE user_info
DROP INDEX idx_user_nickname;

-- 3. 添加外键约束（示例：订单表 orders 关联用户表 user_info）
-- 先创建订单表
CREATE TABLE IF NOT EXISTS orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    user_id INT UNSIGNED NOT NULL COMMENT '关联用户ID',
    order_amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    order_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
    -- 外键约束：user_id 关联 user_info 的 id
    CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id)
    REFERENCES user_info (id)
    -- 外键动作：删除用户时，处理关联订单（RESTRICT 禁止删除，CASCADE 级联删除）
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE = InnoDB COMMENT = '订单表';

-- 4. 删除外键约束（需指定外键名，通过 SHOW CREATE TABLE 查看）
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_user_id;

外键说明：ON DELETE 和 ON UPDATE 定义关联数据的处理逻辑：

• RESTRICT（默认）：若有关联订单，禁止删除用户（避免数据孤儿）。
• CASCADE：删除用户时，级联删除所有关联订单（慎用，可能误删大量数据）。
• SET NULL：删除用户时，将订单的 user_id 设为 NULL（需 user_id 允许为空）。

2. 索引管理

索引是 “加速查询的工具”，但会降低插入 / 修改 / 删除的效率（需维护索引结构），需根据查询场景合理创建。

-- 1. 创建普通索引（优化“按 phone 查询用户”的场景）
ALTER TABLE user_info
ADD INDEX idx_phone (phone);

-- 2. 创建联合索引（优化“按 status + create_time 筛选”的场景，遵循“最左前缀原则”）
ALTER TABLE user_info
ADD INDEX idx_status_create_time (status, create_time);

-- 3. 创建主键索引（表默认已存在，若需重建，先删除旧主键）
ALTER TABLE user_info
DROP PRIMARY KEY,
ADD PRIMARY KEY (id);

-- 4. 查看表所有索引（通过 KEY_NAME 区分索引类型）
SHOW INDEX FROM user_info;

-- 5. 删除索引（需指定索引名）
ALTER TABLE user_info
DROP INDEX idx_phone;

索引最佳实践：

• 优先给 WHERE、JOIN、ORDER BY 中频繁使用的字段加索引。
• 避免给 “低基数字段” 加索引（如 status 只有 0/1 两个值，索引效果差）。
• 联合索引遵循 “最左前缀原则”：如 idx_status_create_time (status, create_time)，仅支持 status、status+create_time 的查询，不支持单独 create_time 的查询。

3. 数据表事务与锁（进阶）

InnoDB 支持事务（ACID 特性），可确保多步操作的原子性（要么全成功，要么全失败），避免数据不一致。

-- 开启事务（默认自动提交，开启后需手动提交/回滚）
START TRANSACTION;  -- 或 BEGIN;

-- 操作1：用户表新增一条记录
INSERT INTO user_info (username, password, phone)
VALUES ('zhaoliu', 'e10adc3949ba59abbe56e057f20f883e', '13800138000');

-- 操作2：订单表新增关联订单
INSERT INTO orders (user_id, order_amount)
VALUES (LAST_INSERT_ID(), 99.99);  -- LAST_INSERT_ID() 获取上一步插入的 user_id

-- 确认操作无误，提交事务（数据永久生效）
COMMIT;

-- 若操作出错，回滚事务（所有操作全部撤销）
-- ROLLBACK;

事务隔离级别：MySQL 默认 REPEATABLE READ（可重复读），支持 4 个隔离级别（从低到高）：

1. READ UNCOMMITTED（读未提交）：可能读取到未提交的数据（脏读）。
2. READ COMMITTED（读已提交）：避免脏读，但可能出现不可重复读。
3. REPEATABLE READ（可重复读）：避免脏读和不可重复读，InnoDB 默认。
4. SERIALIZABLE（串行化）：避免所有问题，但性能极差，仅用于特殊场景。

修改隔离级别：

-- 临时修改（当前会话生效）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 永久修改（需重启 MySQL，修改 my.cnf 配置文件）
-- transaction-isolation = READ-COMMITTED

2.3 库表操作最佳实践

1. 命名规范：

• 数据库名：小写字母 + 下划线（如 shop_db），避免中文和特殊字符。
• 表名：小写字母 + 下划线，前缀统一（如 user_info、user_address），清晰区分业务模块。
• 字段名：小写字母 + 下划线，语义明确（如 user_id 而非 id，order_amount 而非 amount）。
• 索引名：普通索引 idx_字段名（如 idx_phone），联合索引 idx_字段1_字段2（如 idx_status_create_time），外键 fk_表名_字段名（如 fk_orders_user_id）。

2. 性能优化：

   表是数据存储的直接载体，其优化直接影响查询效率和数据安全性，需从 “结构设计→数据操作→索引维护” 全流程把控：

   1. 表结构优化：减少冗余，适配业务场景

   • 避免冗余字段：不存储可通过计算得到的数据，如 “订单表” 无需存 order_total（订单总额），可通过 “订单详情表” 的 price*quantity实时计算；若需频繁查询，可通过 “视图” 或 “定时任务生成汇总表” 实现，减少数据一致性风险。

   • 字段类型精准选择：避免 “大字段滥用”，按实际需求选最小可用类型，降低存储和查询开销：

     • 整数类：如 “用户状态（0 = 禁用，1 = 正常）” 用 TINYINT UNSIGNED（1 字节），而非 INT（4 字节）；“订单数量” 若不超过 65535，用 SMALLINT UNSIGNED（2 字节），而非 BIGINT（8 字节）。
     • 字符串类：“手机号” 固定 11 位，用 CHAR(11)（查询更快），而非 VARCHAR(20)；“商品描述” 若长度超 255，优先用 VARCHAR(1000)（可变长度，节省空间），而非 TEXT（大文本类型，查询效率低，仅用于超 4000 字符场景）。
     • 时间类：“订单创建时间” 只需精确到秒，用 DATETIME（8 字节），而非 TIMESTAMP（依赖系统时区，易出问题）；“日志记录时间” 若需毫秒级，用 DATETIME(3)（如 2024-05-20 14:30:00.123），避免冗余精度。

   • 拆分大表：当单表数据量超过 1000 万行或表字段超过 50 个时，需拆分优化：

     • 垂直拆分：按 “高频查询字段” 和 “低频查询字段” 拆分，如 “用户表” 拆为 user_base（id、username、phone 等高频字段）和 user_extend（birthday、address 等低频字段），减少每次查询的数据量。
     • 水平拆分：按 “时间范围”“地域”“用户 ID 哈希” 拆分，如 “订单表” 按 “创建时间” 拆为 orders_2023（2023 年订单）、orders_2024（2024 年订单），或按 “用户 ID%10” 拆为 orders_0-orders_9，降低单表数据量。
   2. 数据操作优化：减少锁表，提升执行效率

   • 批量操作替代循环单条操作：插入 / 更新大量数据时，用批量语句减少 SQL 交互次数：

     • 批量插入：INSERT INTO users (username, phone) VALUES ('a1','138001'), ('a2','138002'), ...（单次批量建议不超过 1000 条，避免数据包过大），比循环执行 INSERT效率提升 5-10 倍。
     • 批量更新：用 CASE WHEN实现，如 UPDATE goods SET stock = CASE id WHEN 1 THEN 10 WHEN 2 THEN 20 END WHERE id IN (1,2)，避免循环 UPDATE导致的频繁锁表。

   • 避免长事务：长事务会占用锁资源，阻塞其他操作，如 “订单创建” 流程应简化为 “插入订单→扣减库存” 两步，避免包含 “用户确认收货” 等耗时操作；若需复杂逻辑，可拆分为 “短事务 + 异步任务”（如用消息队列处理后续通知）。

   • 合理使用事务隔离级别：非核心业务（如统计报表）用 READ COMMITTED（读已提交），降低锁开销；核心业务（如订单支付）用 REPEATABLE READ（可重复读，InnoDB 默认），确保数据一致性，避免滥用 SERIALIZABLE（串行化，性能极差）。

   • 批量操作替代循环单条操作：插入 / 更新大量数据时，用批量语句减少 SQL 交互次数：

     • 批量插入：INSERT INTO users (username, phone) VALUES ('a1','138001'), ('a2','138002'), ...（单次批量建议不超过 1000 条，避免数据包过大），比循环执行 INSERT效率提升 5-10 倍。
     • 批量更新：用 CASE WHEN实现，如 UPDATE goods SET stock = CASE id WHEN 1 THEN 10 WHEN 2 THEN 20 END WHERE id IN (1,2)，避免循环 UPDATE导致的频繁锁表。

   • 避免长事务：长事务会占用锁资源，阻塞其他操作，如 “订单创建” 流程应简化为 “插入订单→扣减库存” 两步，避免包含 “用户确认收货” 等耗时操作；若需复杂逻辑，可拆分为 “短事务 + 异步任务”（如用消息队列处理后续通知）。

   • 合理使用事务隔离级别：非核心业务（如统计报表）用 READ COMMITTED（读已提交），降低锁开销；核心业务（如订单支付）用 REPEATABLE READ（可重复读，InnoDB 默认），确保数据一致性，避免滥用 SERIALIZABLE（串行化，性能极差）。

   3. 索引优化：精准创建，避免冗余

   • 拒绝 “全表索引” 和 “冗余索引”：

     • 不给 “低基数字段” 建索引：如 “性别（male/female）”“状态（0/1）”，索引选择性低（查询时仍需扫描大量行），反而增加写入开销。
     • 不建重复索引：如 “idx_phone（phone）” 和 “idx_phone_status（phone, status）”，前者是后者的前缀索引，查询 phone时会优先用 idx_phone_status，idx_phone属于冗余，需删除。

   • 索引适配查询场景：

     • 高频单字段查询：如 “按手机号查用户”，建普通索引 idx_phone。
     • 多字段组合查询：如 “按状态 + 创建时间查订单”，建联合索引 idx_status_create_time，且遵循 “最左前缀原则”（将过滤条件更严格的字段放前面，如 status过滤后数据更少，放前）。
     • 范围查询字段放联合索引末尾：如 “按创建时间范围 + 状态查订单”（WHERE status=1 AND create_time BETWEEN '2024-01-01' AND '2024-05-01'），联合索引应设为 idx_status_create_time（status是等值查询，create_time是范围查询，放末尾），若设为 idx_create_time_status，status无法利用索引。

   • 定期维护索引：

     • 查看索引使用情况：通过 sys.schema_unused_indexes（MySQL 8.0+）或 SHOW PROFILE分析未使用的索引，及时删除冗余索引。
     • 优化碎片化索引：当表频繁执行 DELETE/UPDATE导致索引碎片化（可通过 SHOW TABLE STATUS LIKE '表名'查看 Data_free字段，过大表示碎片化严重），执行 OPTIMIZE TABLE 表名（InnoDB 引擎需表为 innodb_file_per_table模式），重建索引和表空间，提升查询效率。
   4. 数据操作优化：减少锁表，提升执行效率

   • 读写分离：当读请求远超写请求（如电商商品详情页，读：写 = 100:1），可搭建 “主从复制” 架构，主库负责写操作（插入 / 更新 / 删除），从库负责读操作（查询），通过中间件（如 MyCat、ProxySQL）实现读写路由，减轻主库压力。

   • 分表后的数据查询：水平分表后，查询需指定分表条件（如 “查 2024 年订单” 直接查 orders_2024），避免 “全部分表扫描”；若需跨分表查询（如 “查用户近 3 年订单”），可通过 “联邦查询” 或 “汇总表” 实现，减少跨表开销。

   • 临时表与视图的合理使用：

     • 临时表：用于存储单次复杂查询的中间结果（如 CREATE TEMPORARY TABLE temp_order AS SELECT * FROM orders WHERE status=1），避免重复计算，但临时表仅当前会话可见，且会占用内存，使用后需及时删除（DROP TEMPORARY TABLE temp_order）。
     • 视图：用于封装复杂查询逻辑（如 “用户订单汇总视图”CREATE VIEW user_order_summary AS SELECT ``u.id``, u.username, COUNT(``o.id``) AS order_count FROM users u LEFT JOIN orders o ON ``u.id``=o.user_id GROUP BY ``u.id），简化业务查询，但视图不存储数据，每次查询都会重新执行底层 SQL，不适合高频查询场景。

三、Redis基础操作

3.1 连接 Redis

# 本地连接（默认端口6379）
redis-cli

# 连接远程服务器
redis-cli -h host -p port -a password

3.2 键（Key）操作

# 设置键值对（字符串类型）
SET key value

# 获取键的值
GET key

# 查看键是否存在
EXISTS key

# 删除键
DEL key

# 设置键的过期时间（秒）
EXPIRE key seconds

# 查看键的剩余过期时间
TTL key

# 移除键的过期时间
PERSIST key

# 查看所有键
KEYS *

3.3 字符串（String）操作

# 设置值并指定过期时间（秒）
SET key value EX seconds

# 自增1
INCR key

# 自减1
DECR key

# 增加指定数值
INCRBY key increment

# 减少指定数值
DECRBY key decrement

# 获取字符串长度
STRLEN key

# 追加内容到字符串
APPEND key value

3.4 哈希（Hash）操作

# 设置哈希字段值
HSET hash_key field value

# 获取哈希字段值
HGET hash_key field

# 获取哈希中所有字段和值
HGETALL hash_key

# 获取哈希中所有字段
HKEYS hash_key

# 获取哈希中所有值
HVALS hash_key

# 检查哈希字段是否存在
HEXISTS hash_key field

# 删除哈希字段
HDEL hash_key field

# 获取哈希字段数量
HLEN hash_key

3.5 列表（List）操作

# 从列表左侧添加元素
LPUSH list_key value1 value2 ...

# 从列表右侧添加元素
RPUSH list_key value1 value2 ...

# 从列表左侧移除并返回元素
LPOP list_key

# 从列表右侧移除并返回元素
RPOP list_key

# 获取列表指定范围的元素（0表示第一个，-1表示最后一个）
LRANGE list_key start stop

# 获取列表长度
LLEN list_key

3.6 集合（Set）操作

# 向集合添加元素
SADD set_key member1 member2 ...

# 获取集合所有元素
SMEMBERS set_key

# 检查元素是否在集合中
SISMEMBER set_key member

# 从集合移除元素
SREM set_key member1 member2 ...

# 获取集合元素数量
SCARD set_key

# 求两个集合的交集
SINTER set1_key set2_key

# 求两个集合的并集
SUNION set1_key set2_key

3.7 有序集合（Sorted Set）操作

# 向有序集合添加元素（score为排序权重）
ZADD zset_key score1 member1 score2 member2 ...

# 获取有序集合指定范围的元素（升序）
ZRANGE zset_key start stop [WITHSCORES]

# 获取有序集合指定范围的元素（降序）
ZREVRANGE zset_key start stop [WITHSCORES]

# 获取元素在有序集合中的排名（升序）
ZRANK zset_key member

# 获取元素的分数
ZSCORE zset_key member

# 从有序集合移除元素
ZREM zset_key member1 member2 ...

# 获取有序集合元素数量
ZCARD zset_key

3.8 服务器操作

# 查看Redis服务器信息
INFO

# 测试连接是否正常
PING

# 清空当前数据库数据
FLUSHDB

# 清空所有数据库数据
FLUSHALL

# 退出连接
QUIT