MySQL最佳实践 #

-- 选择最小的数据类型以节省空间
-- 不好的做法
CREATE TABLE users (
    id BIGINT,           -- 如果ID不会超过INT范围，使用INT即可
    age VARCHAR(10),     -- 年龄用TINYINT即可
    status VARCHAR(255)  -- 状态用ENUM或TINYINT更合适
);

-- 好的做法
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- UNSIGNED节省空间
    age TINYINT UNSIGNED NOT NULL,              -- 精确的数据类型
    status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',  -- 枚举类型
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 推荐使用自增整数主键
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- 避免使用复合主键作为业务主键
-- 不推荐
-- CREATE TABLE order_items (
--     order_id INT,
--     product_id INT,
--     quantity INT,
--     PRIMARY KEY (order_id, product_id)  -- 业务主键
-- );

-- 推荐
CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    
    UNIQUE KEY uk_order_product (order_id, product_id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
);

-- 统一使用UTF8MB4字符集
CREATE DATABASE myapp 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
) 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 1. 为经常查询的列创建索引
-- 2. 为JOIN条件创建索引
-- 3. 为ORDER BY和GROUP BY创建索引
-- 4. 优先为高选择性的列创建索引

-- 示例：电商订单表索引设计
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    status TINYINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);  -- 复合索引
CREATE INDEX idx_status ON orders(status);  -- 状态查询
CREATE INDEX idx_created_at ON orders(created_at);  -- 时间范围查询

-- 复合索引列顺序原则：
-- 1. 等值查询列优先
-- 2. 高选择性列优先
-- 3. 高频查询列优先

-- 好的复合索引设计
CREATE INDEX idx_status_user_date ON orders(status, user_id, created_at);

-- 支持的查询模式
-- WHERE status = ? 
-- WHERE status = ? AND user_id = ?
-- WHERE status = ? AND user_id = ? AND created_at > ?

-- 不支持的查询模式
-- WHERE user_id = ?                    -- 跳过了最左列
-- WHERE created_at > ?                 -- 跳过了最左列
-- WHERE user_id = ? AND created_at > ? -- 跳过了中间列

-- 避免在WHERE子句中使用函数
-- 不好的做法
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 好的做法
SELECT * FROM users 
WHERE created_at >= '2023-01-01' 
AND created_at < '2024-01-01';

-- 避免SELECT *
-- 不好的做法
SELECT * FROM users WHERE id = 1;

-- 好的做法
SELECT id, username, email FROM users WHERE id = 1;

-- 使用EXISTS替代IN（在某些情况下）
-- 不好的做法
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'pending');

-- 好的做法
SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'pending'
);

-- 避免OFFSET深分页问题
-- 不好的做法
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;  -- 深分页性能差

-- 好的做法：使用游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 使用延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) AS lim ON o.id = lim.id;

DELIMITER //
CREATE PROCEDURE TransferMoney(
    IN p_from_user INT,
    IN p_to_user INT,
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE v_from_balance DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- 使用FOR UPDATE锁定记录
    SELECT balance INTO v_from_balance 
    FROM accounts 
    WHERE user_id = p_from_user 
    FOR UPDATE;

    IF v_from_balance < p_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;

    -- 执行转账
    UPDATE accounts SET balance = balance - p_amount WHERE user_id = p_from_user;
    UPDATE accounts SET balance = balance + p_amount WHERE user_id = p_to_user;

    -- 记录交易日志
    INSERT INTO transactions (from_user, to_user, amount, created_at)
    VALUES (p_from_user, p_to_user, p_amount, NOW());

    COMMIT;
END //
DELIMITER ;

-- 保持事务尽可能短
-- 不好的做法：长时间运行的事务
START TRANSACTION;
SELECT * FROM large_table; -- 处理大量数据
-- 长时间计算
UPDATE accounts SET balance = new_balance WHERE id = 1;
COMMIT;

-- 好的做法：缩短事务范围
SELECT * FROM large_table; -- 在事务外处理数据
START TRANSACTION;
UPDATE accounts SET balance = calculated_balance WHERE id = 1;
COMMIT;

-- 遵循最小权限原则
-- 应用程序用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';

-- 只读用户
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON myapp.* TO 'readonly_user'@'%';

-- 备份用户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

-- 监控用户
CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'monitor_password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';
FLUSH PRIVILEGES;

-- 启用密码验证插件（MySQL 8.0+）
INSTALL COMPONENT 'file://component_validate_password';

SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;

-- 定期更新密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_strong_password';

-- 设置密码过期策略
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 启用二进制日志（在my.cnf中配置）
-- [mysqld]
-- log-bin=mysql-bin
-- expire_logs_days=7
-- max_binlog_size=100M
-- binlog_format=ROW

-- 查看二进制日志配置
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW MASTER LOGS;

-- 监控连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

-- 监控查询性能
SHOW STATUS LIKE 'Questions';  -- 总查询数
SHOW STATUS LIKE 'Slow_queries';  -- 慢查询数

-- 监控InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 使用Performance Schema监控
SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT/1000000000 AS TOTAL_WAIT_S
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看最慢的查询
SELECT * FROM sys.statement_analysis 
ORDER BY total_latency DESC 
LIMIT 10;

-- 查看全表扫描的查询
SELECT * FROM sys.statements_with_full_table_scans 
WHERE exec_count > 10;

-- 查看表统计信息
SELECT * FROM sys.schema_table_statistics 
ORDER BY rows_fetched DESC;

-- 主库配置 (my.cnf)
# [mysqld]
# server-id=1
# log-bin=mysql-bin
# binlog-format=ROW
# binlog-do-db=myapp

-- 从库配置 (my.cnf)
# [mysqld]
# server-id=2
# relay-log=relay-bin
# read_only=1
# replicate-do-db=myapp

-- 设置主从复制
-- 在主库上
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'repl_password';

-- 在从库上
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

-- 1. 检查是否有合适的索引
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 2. 避免SELECT *
SELECT id, username, email FROM users WHERE id = 1;

-- 3. 使用LIMIT限制结果集
SELECT * FROM large_table WHERE condition LIMIT 100;

-- 4. 优化JOIN操作
-- 确保JOIN条件上有索引
-- 避免笛卡尔积

-- 5. 使用批量操作
INSERT INTO users (username, email) VALUES 
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

-- 定期分析表统计信息
ANALYZE TABLE users, orders;

-- 优化表（整理碎片）
OPTIMIZE TABLE large_table;

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

-- 使用分区表处理大数据量
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 每周执行
ANALYZE TABLE users, orders, products;

-- 每月执行
OPTIMIZE TABLE large_log_table;

-- 检查表损坏
CHECK TABLE important_table;

-- 清理过期数据
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);