mysql-best-practices

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

MySQL Best Practices

MySQL 开发最佳实践

Core Principles

核心原则

Design schemas with appropriate storage engines (InnoDB for most use cases)
Optimize queries using EXPLAIN and proper indexing
Use proper data types to minimize storage and improve performance
Implement connection pooling and query caching appropriately
Follow MySQL-specific security hardening practices

为Schema设计选择合适的存储引擎（大多数场景下使用InnoDB）
使用EXPLAIN和合理的索引优化查询
使用恰当的数据类型以最小化存储并提升性能
合理实现连接池与查询缓存
遵循MySQL专属的安全加固实践

Schema Design

Schema设计

Storage Engine Selection

存储引擎选择

Use InnoDB as the default engine (ACID compliant, row-level locking)
Consider MyISAM only for read-heavy, non-transactional workloads
Use MEMORY engine for temporary tables with high-speed requirements

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;

将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;

Data Types

数据类型

Use smallest data type that fits your needs
Prefer INT UNSIGNED over BIGINT when possible
Use DECIMAL for financial calculations, not FLOAT/DOUBLE
Use ENUM for fixed sets of values
Use VARCHAR for variable-length strings, CHAR for fixed-length
Always use utf8mb4 charset for full Unicode support

sql

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

使用能满足需求的最小数据类型
尽可能优先使用INT UNSIGNED而非BIGINT
财务计算使用DECIMAL，而非FLOAT/DOUBLE
固定值集合使用ENUM
可变长度字符串使用VARCHAR，固定长度使用CHAR
始终使用utf8mb4字符集以支持完整Unicode

sql

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

Primary Keys

主键

Use AUTO_INCREMENT integer primary keys for InnoDB tables
Consider UUIDs stored as BINARY(16) for distributed systems
Avoid composite primary keys when possible

sql

-- 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');

为InnoDB表使用自增整数主键
分布式系统中考虑将UUID存储为BINARY(16)
尽可能避免复合主键

sql

-- 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');

Indexing Strategies

索引策略

Index Types

索引类型

Use B-tree indexes (default) for most queries
Use FULLTEXT indexes for text search
Use SPATIAL indexes for geographic data
Consider covering indexes for frequently executed queries

sql

-- 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);

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

sql

-- 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);

Index Guidelines

索引指南

Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY
Place most selective columns first in composite indexes
Avoid indexing low-cardinality columns alone
Monitor and remove unused indexes

sql

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

为WHERE、JOIN、ORDER BY和GROUP BY中使用的列创建索引
复合索引中优先放置选择性最高的列
避免单独为低基数列创建索引
监控并移除未使用的索引

sql

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

Query Optimization

查询优化

EXPLAIN Analysis

EXPLAIN分析

Use EXPLAIN to analyze query execution plans
Look for full table scans (type: ALL)
Check for proper index usage
Monitor rows examined vs rows returned

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;

使用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;

Query Best Practices

查询最佳实践

Avoid SELECT * in production code
Use LIMIT for pagination
Prefer JOINs over subqueries when possible
Use prepared statements for repeated queries

sql

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

生产环境代码中避免使用SELECT *
分页使用LIMIT
可能的话优先使用JOIN而非子查询
重复执行的查询使用预处理语句

sql

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

Avoiding Common Pitfalls

避免常见陷阱

sql

-- 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');

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 Support

JSON支持

Use JSON data type for semi-structured data (MySQL 5.7+)
Create generated columns for frequently accessed JSON fields
Use appropriate JSON functions for queries

sql

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;

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

sql

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;

Transaction Management

事务管理

Use InnoDB for transactional tables
Keep transactions short to minimize lock contention
Choose appropriate isolation level
Handle deadlocks gracefully

sql

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

事务性表使用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;

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

Replication and High Availability

复制与高可用

Read Replicas

只读副本

Direct read queries to replicas
Use connection pooling with read/write splitting
Monitor replication lag

sql

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

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

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;

Security

安全

Use strong passwords and secure connections (SSL/TLS)
Apply principle of least privilege
Use prepared statements to prevent SQL injection
Audit sensitive operations

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'@'%';

使用强密码与安全连接（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'@'%';

Maintenance

维护

Regular Maintenance Tasks

常规维护任务

sql

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

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

-- Check table integrity
CHECK TABLE orders;

sql

-- 分析表以更新优化器统计信息
ANALYZE TABLE orders, customers, products;

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

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

Monitoring Queries

监控查询

sql

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

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;

Configuration Recommendations

配置建议

ini

undefined

ini

undefined

my.cnf recommended settings

my.cnf 推荐配置

[mysqld]

InnoDB settings

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

Connection settings

连接设置

max_connections = 500 wait_timeout = 300 interactive_timeout = 300

Query cache (disabled in MySQL 8.0+)

查询缓存（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

undefined

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

undefined