MySQL配置性能优化及语法写法

一、配置性能优化

1. 内存相关配置

innodb_buffer_pool_size = 4G  # 通常设为可用内存的50-70%
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
key_buffer_size = 256M
query_cache_size = 0  # MySQL 8.0已移除查询缓存

2. I/O相关配置

innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0  # SSD建议关闭

3. 连接相关配置

max_connections = 200
thread_cache_size = 50
table_open_cache = 4000

4. 其他重要配置

innodb_file_per_table = ON
innodb_stats_on_metadata = OFF
innodb_buffer_pool_instances = 8  # 通常设为buffer pool大小的1/8

二、SQL语法优化

1. 索引优化

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 使用覆盖索引
EXPLAIN SELECT id, name FROM users WHERE name = 'John';

2. 查询优化

-- 避免SELECT *
SELECT id, name, email FROM users WHERE status = 1;

-- 使用JOIN代替子查询
SELECT u.name, o.order_date 
FROM users u
JOIN orders o ON u.id = o.user_id;

3. 分页优化

-- 传统分页(性能差)
SELECT * FROM products LIMIT 10000, 20;

-- 优化分页(使用索引列)
SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 20;

4. 批量操作

-- 批量插入
INSERT INTO users (name, age) VALUES 
('Alice', 25),
('Bob', 30),
('Charlie', 35);

-- 批量更新
UPDATE products 
SET price = CASE 
    WHEN id = 1 THEN 10.99
    WHEN id = 2 THEN 15.99
    WHEN id = 3 THEN 20.99
END
WHERE id IN (1, 2, 3);

三、高级优化技巧

1. 分区表

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

2. 查询重写

-- 原查询
SELECT * FROM users WHERE YEAR(create_time) = 2022;

-- 优化后
SELECT * FROM users WHERE create_time >= '2022-01-01' AND create_time < '2023-01-01';

3. 使用EXPLAIN分析

EXPLAIN SELECT u.name, COUNT(o.id) 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

4. 临时表优化

-- 使用内存临时表
CREATE TEMPORARY TABLE temp_products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=MEMORY;

四、监控与维护

1. 查看性能状态

SHOW STATUS LIKE 'Innodb_row_read%';
SHOW ENGINE INNODB STATUS;

2. 定期维护

-- 优化表
OPTIMIZE TABLE large_table;

-- 分析表
ANALYZE TABLE users;

以上配置和语法优化应根据实际业务场景和服务器配置进行调整，建议在生产环境变更前先在测试环境验证。