mysql-best-practices

MySQL 最佳实践

核心原则

• 根据使用场景选择合适的存储引擎（大多数场景使用 InnoDB）
• 使用 EXPLAIN 和适当的索引优化查询
• 使用合适的数据类型以减少存储占用并提升性能
• 适当实现连接池和查询缓存
• 遵循 MySQL 特有的安全加固实践

模式设计

存储引擎选择

• 使用 InnoDB 作为默认引擎（符合 ACID，支持行级锁）
• 仅在读操作密集且无需事务的工作负载中考虑使用 MyISAM
• 对有高速需求的临时表使用 MEMORY 引擎

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

-- Appropriate data type selection
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
• 尽可能避免使用复合主键

-- UUID storage optimization
CREATE TABLE distributed_events (
    event_id BINARY(16) PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- Query with UUID
SELECT * FROM distributed_events
WHERE event_id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

索引策略

索引类型

• 大多数查询使用 B-tree 索引（默认）
• 文本搜索使用 FULLTEXT 索引
• 地理数据使用 SPATIAL 索引
• 对频繁执行的查询，可考虑使用覆盖索引

-- Composite index for common query patterns
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Covering index
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, status, total_amount);

-- Fulltext index for search
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc (name, description);

-- Search using fulltext
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('wireless bluetooth' IN NATURAL LANGUAGE MODE);

索引指南

• 为用于 WHERE、JOIN、ORDER BY 和 GROUP BY 的列创建索引
• 在复合索引中，将选择性最高的列放在前面
• 避免单独为低基数列创建索引
• 监控并删除未使用的索引

-- Check index usage
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）
• 检查是否正确使用索引
• 监控扫描行数与返回行数

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，而非子查询
• 对重复查询使用预处理语句

-- Efficient pagination
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 20 OFFSET 0;

-- Keyset pagination (more efficient for large offsets)
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;

避免常见陷阱

-- Avoid: Function on indexed column
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- Preferred: Range comparison
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- Avoid: Implicit type conversion
SELECT * FROM users WHERE user_id = '123';  -- user_id is INT

-- Preferred: Proper types
SELECT * FROM users WHERE user_id = 123;

-- Avoid: LIKE with leading wildcard
SELECT * FROM products WHERE name LIKE '%phone%';

-- Preferred: Fulltext search for text matching
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

JSON 支持

• 对半结构化数据使用 JSON 数据类型（MySQL 5.7+）
• 为频繁访问的 JSON 字段创建生成列
• 在查询中使用合适的 JSON 函数

CREATE TABLE events (
    event_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSON NOT NULL,
    -- Generated column for indexing
    user_id INT UNSIGNED AS (payload->>'$.user_id') STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
);

-- Query JSON data
SELECT event_id, event_type,
       JSON_EXTRACT(payload, '$.action') AS action
FROM events
WHERE JSON_EXTRACT(payload, '$.user_id') = 123;

-- Or using -> operator
SELECT * FROM events WHERE payload->'$.user_id' = 123;

事务管理

• 事务表使用 InnoDB
• 缩短事务时长，以尽量减少锁竞争
• 选择合适的隔离级别
• 妥善处理死锁

-- Transaction with error handling
START TRANSACTION;

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

-- Check for errors and commit or rollback
COMMIT;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

复制与高可用性

只读副本

• 将读查询定向到副本
• 使用支持读写分离的连接池
• 监控复制延迟

-- Check replication status
SHOW SLAVE STATUS\G

-- Check replication lag
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 注入
• 审计敏感操作

-- Create user with limited privileges
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

-- Require SSL
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- View user privileges
SHOW GRANTS FOR 'app_user'@'%';

维护

定期维护任务

-- Analyze tables for optimizer statistics
ANALYZE TABLE orders, customers, products;

-- Optimize tables (reclaim space, defragment)
OPTIMIZE TABLE orders;

-- Check table integrity
CHECK TABLE orders;

监控查询

-- Find slow queries
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Current process list
SHOW FULL PROCESSLIST;

-- InnoDB status
SHOW ENGINE INNODB STATUS;

-- Table sizes
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;

配置建议

# my.cnf recommended settings

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

# Connection settings
max_connections = 500
wait_timeout = 300
interactive_timeout = 300

# Query cache (disabled in MySQL 8.0+)
query_cache_type = 0

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2