---
name: mysql-best-practices
description: MySQL 开发最佳实践：模式设计、查询优化和数据库管理
---

# MySQL 最佳实践

## 核心原则

- 使用合适的存储引擎设计表结构（大多数情况下推荐 InnoDB）
- 使用 EXPLAIN 和合适的索引优化查询
- 选择合适的数据类型，以减少存储空间并提升性能
- 合理实现连接池和查询缓存
- 遵循 MySQL 特有的安全加固措施

## 表结构设计

### 存储引擎选择

- 默认使用 InnoDB 引擎（ACID 支持，行级锁）
- 仅在以读取为主、无需事务的场景下考虑 MyISAM
- 临时且高性能表可考虑 MEMORY 引擎

```sql
CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
        NOT NULL DEFAULT 'pending',
    INDEX idx_customer (customer_id),
    INDEX idx_date_status (order_date, status),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### 数据类型

- 选择最小满足需求的数据类型
- 能用 INT UNSIGNED 就不要用 BIGINT
- 金融运算用 DECIMAL，避免使用 FLOAT/DOUBLE
- 固定取值集合用 ENUM
- 变长字符串用 VARCHAR，定长用 CHAR
- 始终使用 utf8mb4 字符集以支持完整 Unicode

```sql
-- 合理选择数据类型
CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    weight DECIMAL(8, 3),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_sku (sku)
) ENGINE=InnoDB;
```

### 主键

- InnoDB 表推荐使用 AUTO_INCREMENT 整数字段做主键
- 分布式系统可考虑以 BINARY(16) 存储 UUID
- 避免使用复合主键

```sql
-- 优化 UUID 存储
CREATE TABLE distributed_events (
    event_id BINARY(16) PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入 UUID
INSERT INTO distributed_events (event_id, event_type, payload)
VALUES (UUID_TO_BIN(UUID()), 'user_signup', '{"user_id": 123}');

-- 使用 UUID 查询
SELECT * FROM distributed_events
WHERE event_id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');
```

## 索引策略

### 索引类型

- 常规查询使用 B-tree 索引（默认）
- 文本搜索使用 FULLTEXT 索引
- 地理数据使用 SPATIAL 索引
- 频繁查询考虑覆盖（covering）索引

```sql
-- 常见组合查询的复合索引
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 覆盖索引
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, status, total_amount);

-- 建立全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);

-- 使用全文索引搜索
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('wireless bluetooth' IN NATURAL LANGUAGE MODE);
```

### 索引规范

- 应索引 WHERE、JOIN、ORDER BY、GROUP BY 用到的字段
- 复合索引中将区分度高的列排在前面
- 避免单独索引低基数字段
- 定期监控并移除未使用索引

```sql
-- 检查索引使用情况
SELECT
    table_schema, table_name, index_name,
    seq_in_index, column_name, cardinality
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database'
ORDER BY table_name, index_name, seq_in_index;
```

## 查询优化

### EXPLAIN 分析

- 使用 EXPLAIN 分析查询执行计划
- 注意是否有全表扫描（type: ALL）
- 检查索引是否被正确使用
- 关注扫描行数与返回行数的比值

```sql
EXPLAIN FORMAT=JSON
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at > '2024-01-01'
GROUP BY c.customer_id;
```

### 查询最佳实践

- 生产代码避免使用 SELECT *
- 分页推荐使用 LIMIT
- 优先使用 JOIN，避免不必要的子查询
- 重复查询建议用预处理语句

```sql
-- 高效分页
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 20 OFFSET 0;

-- Keyset 分页（大量偏移时效率更高）
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = ?
    AND (order_date, order_id) < (?, ?)
ORDER BY order_date DESC, order_id DESC
LIMIT 20;
```

### 避免常见陷阱

```sql
-- 避免：对索引列进行函数操作
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 推荐：范围比较
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- 避免：隐式类型转换
SELECT * FROM users WHERE user_id = '123';  -- user_id 是 INT

-- 推荐：使用正确类型
SELECT * FROM users WHERE user_id = 123;

-- 避免：LIKE 前置通配符
SELECT * FROM products WHERE name LIKE '%phone%';

-- 推荐：用全文检索进行文本匹配
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');
```

## JSON 支持

- 用 JSON 类型存储半结构化数据（MySQL 5.7+）
- 对常用 JSON 字段可建立生成（generated）列以便索引
- 查询时使用合适的 JSON 函数

```sql
CREATE TABLE events (
    event_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON NOT NULL,
    -- 用生成列便于索引
    user_id INT UNSIGNED AS (payload->>'$.user_id') STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
);

-- 查询 JSON 数据
SELECT event_id, event_type,
       JSON_EXTRACT(payload, '$.action') AS action
FROM events
WHERE JSON_EXTRACT(payload, '$.user_id') = 123;

-- 也可使用 -> 操作符
SELECT * FROM events WHERE payload->'$.user_id' = 123;
```

## 事务管理

- 事务表需使用 InnoDB 引擎
- 保持事务简短以减少锁竞争
- 选择合适的隔离级别
- 优雅处理死锁

```sql
-- 事务及错误处理
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 检查错误并 commit 或 rollback
COMMIT;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

## 主从复制与高可用

### 只读副本

- 读请求优先指向副本
- 结合连接池实现读写分离
- 持续监控主从延迟

```sql
-- 查看主从状态
SHOW SLAVE STATUS\G

-- 检查复制延迟
SELECT TIMESTAMPDIFF(SECOND,
    MAX(LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP),
    NOW()) AS lag_seconds
FROM performance_schema.replication_applier_status_by_worker;
```

## 安全建议

- 使用强密码和加密连接（SSL/TLS）
- 权限尽量最小化
- 采用预处理语句防范 SQL 注入
- 审计敏感操作

```sql
-- 创建权限受限用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

-- 强制 SSL 连接
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'%';
```

## 运维建议

### 常规维护操作

```sql
-- 分析表获得优化器统计信息
ANALYZE TABLE orders, customers, products;

-- 优化表结构（回收空间，碎片整理）
OPTIMIZE TABLE orders;

-- 检查表完整性
CHECK TABLE orders;
```

### 查询监控

```sql
-- 查找慢查询
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- 当前进程列表
SHOW FULL PROCESSLIST;

-- InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 表空间大小
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    table_rows
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
ORDER BY data_length DESC;
```

## 配置推荐

```ini
# my.cnf 推荐配置

[mysqld]
# InnoDB 配置
innodb_buffer_pool_size = 70%_of_RAM
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# 连接相关配置
max_connections = 500
wait_timeout = 300
interactive_timeout = 300

# 查询缓存（MySQL 8.0+ 已废弃）
query_cache_type = 0

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
```