MySQL性能常用优化技巧总结

1. 索引优化

创建合适的索引

-- 为常用查询条件创建索引
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

避免索引失效的情况

-- 避免在索引列上使用函数
SELECT * FROM users WHERE DATE(create_time) = '2023-01-01'; -- 不好
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'; -- 更好-- 避免使用不等于(!=或<>)
SELECT * FROM users WHERE status != 1; -- 可能导致索引失效

2. 查询优化

使用EXPLAIN分析查询

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND status = 'completed';

只查询需要的列

-- 不好
SELECT * FROM users WHERE id = 100;-- 更好
SELECT id, name, email FROM users WHERE id = 100;

使用JOIN优化

-- 确保JOIN字段有索引
SELECT o.order_id, c.customer_name 
FROM orders o 
JOIN customers c ON o.customer_id = c.customer_id; -- 确保customer_id在两个表都有索引

3. 表结构优化

选择合适的数据类型

-- 使用最小的合适数据类型
-- 不好
CREATE TABLE products (id BIGINT, -- 可能过大price DECIMAL(20,2) -- 精度过高
);-- 更好
CREATE TABLE products (id INT UNSIGNED,price DECIMAL(10,2)
);

规范化与反规范化

-- 有时适当反规范化可以提高查询性能
-- 原始规范化设计
SELECT o.*, u.name, u.email 
FROM orders o 
JOIN users u ON o.user_id = u.id;-- 反规范化设计(在orders表中存储用户名和邮箱)
SELECT o.* 
FROM orders o 
WHERE o.user_id = 100; -- 不需要JOIN

4. 配置优化

调整缓冲池大小

-- 在my.cnf/my.ini中设置
[mysqld]
innodb_buffer_pool_size = 4G  # 通常设置为可用内存的70-80%

调整连接数

-- 在my.cnf/my.ini中设置
max_connections = 200

5. 批量操作优化

使用批量插入

-- 不好
INSERT INTO users (name, email) VALUES ('user1', '[e-mail]');
INSERT INTO users (name, email) VALUES ('user2', '[e-mail]');-- 更好
INSERT INTO users (name, email) VALUES 
('user1', '[e-mail]'),
('user2', '[e-mail]');

批量更新优化

-- 不好
UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 2;-- 更好
UPDATE products SET stock = stock - 1 WHERE id IN (1, 2);

6. 分区和分表

使用表分区

-- 按日期范围分区
CREATE TABLE logs (id INT,log_time DATETIME,message TEXT
) PARTITION BY RANGE (YEAR(log_time)) (PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022),PARTITION p2022 VALUES LESS THAN (2023),PARTITION pmax VALUES LESS THAN MAXVALUE
);

7. 其他优化技巧

使用延迟关联

-- 对于大表分页查询
-- 原始查询(性能差)
SELECT * FROM large_table ORDER BY create_time DESC LIMIT 100000, 10;-- 延迟关联(性能更好)
SELECT t.* FROM large_table t
JOIN (SELECT id FROM large_table ORDER BY create_time DESC LIMIT 100000, 10) tmp
ON t.id = tmp.id;

使用覆盖索引

-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_covering (customer_id, status, order_date);-- 查询可以使用覆盖索引
SELECT customer_id, status, order_date FROM orders 
WHERE customer_id = 100 AND status = 'completed';

8. 监控与维护

定期分析表

ANALYZE TABLE orders;

优化表

OPTIMIZE TABLE large_table;

监控慢查询

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询

这些优化技巧可以根据实际应用场景组合使用，通常需要结合EXPLAIN分析查询执行计划来确定最佳的优化策略。