MySQL项目中常用的19条优化方法分别是什么

发布时间:2021-11-29 16:58:33 作者:柒染
来源:亿速云 阅读:721
# MySQL项目中常用的19条优化方法分别是什么

MySQL作为最流行的开源关系型数据库,其性能优化是开发者必须掌握的技能。本文将详细解析19个MySQL项目中的核心优化方法,涵盖索引设计、查询优化、配置调优等多个维度,帮助您构建高性能数据库系统。

## 一、索引优化策略

### 1. 为高频查询字段创建合适索引
```sql
-- 为user表的username字段添加普通索引
ALTER TABLE `user` ADD INDEX idx_username (`username`);

-- 为订单表创建组合索引(user_id + create_time)
ALTER TABLE `order` ADD INDEX idx_user_create (user_id, create_time);

优化原理
- 减少全表扫描,索引查询效率比全表扫描高10-100倍 - 组合索引遵循最左前缀原则,适合多条件查询

注意事项
- 单表索引不宜超过5个 - TEXT/BLOB类型需使用前缀索引

2. 避免索引失效的常见场景

典型失效案例:

-- 索引失效操作示例
SELECT * FROM user WHERE LEFT(username, 3) = 'abc';  -- 函数操作
SELECT * FROM order WHERE amount*2 > 100;           -- 运算操作
SELECT * FROM user WHERE username LIKE '%admin%';    -- 前导通配符

3. 使用覆盖索引减少IO

-- 使用覆盖索引优化
-- 原始查询(需要回表):
SELECT * FROM products WHERE category_id = 5;

-- 优化后(使用覆盖索引):
ALTER TABLE products ADD INDEX idx_category_name (category_id, product_name);
SELECT category_id, product_name FROM products WHERE category_id = 5;

二、SQL查询优化

4. EXPLN执行计划分析

EXPLN SELECT u.*, o.order_no 
FROM users u 
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

关键指标解读

列名 优化关注点
type 目标至少达到range级别
key 确认使用了正确索引
rows 预估扫描行数越少越好
Extra 避免出现”Using filesort”

5. 避免SELECT * 查询

-- 不推荐
SELECT * FROM employees WHERE dept_id = 10;

-- 推荐
SELECT emp_id, emp_name, position 
FROM employees 
WHERE dept_id = 10;

性能对比
- 减少30-50%的网络传输量 - 提升覆盖索引使用概率

6. 优化JOIN操作

-- 低效写法
SELECT * FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
LEFT JOIN table_c c ON b.id = c.b_id;

-- 优化方案
SELECT a.*, b.field1, b.field2, c.key_field 
FROM table_a a
JOIN (SELECT id, a_id, field1, field2 FROM table_b WHERE ...) b ON a.id = b.a_id
JOIN (SELECT id, b_id, key_field FROM table_c WHERE ...) c ON b.id = c.b_id;

7. 合理使用分页查询

-- 传统分页(大数据量性能差)
SELECT * FROM logs ORDER BY create_time DESC LIMIT 100000, 20;

-- 优化分页(使用延迟关联)
SELECT t.* FROM logs t
JOIN (SELECT id FROM logs ORDER BY create_time DESC LIMIT 100000, 20) tmp
ON t.id = tmp.id;

三、表结构设计优化

8. 选择合适的数据类型

场景 推荐类型 存储空间
短字符串(255内) VARCHAR 变长
枚举值 ENUM 1-2字节
整数类型 TINYINT/SMALLINT 1-2字节
大文本 TEXT L+2字节

9. 规范化与反规范化平衡

规范化优点: - 减少数据冗余 - 避免更新异常

反规范化场景: - 频繁JOIN查询的表 - 读多写少的统计字段

10. 垂直拆分大表

-- 原始用户表
CREATE TABLE users (
  id BIGINT,
  username VARCHAR(50),
  password VARCHAR(100),
  profile_text TEXT,
  login_history JSON,
  ...
);

-- 拆分后
CREATE TABLE users_basic (
  id BIGINT,
  username VARCHAR(50),
  password VARCHAR(100)
);

CREATE TABLE users_profile (
  user_id BIGINT,
  profile_text TEXT,
  ...
);

四、服务器配置优化

11. 关键参数调整

# my.cnf 关键配置
[mysqld]
innodb_buffer_pool_size = 12G    # 通常设为物理内存的70-80%
innodb_log_file_size = 2G        # 重做日志大小
max_connections = 500           # 根据应用需求调整
query_cache_type = 0            # 8.0+版本已移除

12. 事务隔离级别选择

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别(通常选RC)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

各隔离级别对比

级别 脏读 不可重复读 幻读 性能
READ UNCOMMITTED 最高
READ COMMITTED ×
REPEATABLE READ × × 中等
SERIALIZABLE × × × 最低

五、高级优化技巧

13. 使用批处理代替循环

-- 低效做法(应用程序循环插入)
INSERT INTO order_items(order_id, product_id) VALUES(1001, 501);
INSERT INTO order_items(order_id, product_id) VALUES(1001, 502);
...

-- 高效批处理
INSERT INTO order_items(order_id, product_id) 
VALUES (1001,501), (1001,502), (1001,503), ...;

性能对比
- 批量插入比单条插入快10倍以上 - 减少网络往返和SQL解析开销

14. 冷热数据分离

-- 历史订单归档方案
CREATE TABLE orders_archive LIKE orders;
INSERT INTO orders_archive 
SELECT * FROM orders WHERE create_time < '2023-01-01';
DELETE FROM orders WHERE create_time < '2023-01-01';

15. 使用中间表优化统计

-- 创建预计算统计表
CREATE TABLE product_stats (
  product_id INT PRIMARY KEY,
  sale_count INT,
  avg_rating DECIMAL(3,2),
  last_calc_time DATETIME
);

-- 定期更新(使用事件调度)
CREATE EVENT update_product_stats
ON SCHEDULE EVERY 1 HOUR
DO
  REPLACE INTO product_stats
  SELECT product_id, COUNT(*), AVG(rating), NOW()
  FROM order_items
  WHERE create_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
  GROUP BY product_id;

六、监控与维护

16. 慢查询日志分析

# 启用慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

分析工具

# 使用pt-query-digest分析
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

17. 定期维护表结构

-- 优化表(MyISAM引擎)
OPTIMIZE TABLE large_table;

-- 分析表(更新索引统计信息)
ANALYZE TABLE user_profile;

-- 8.0+版本在线DDL操作
ALTER TABLE orders ALGORITHM=INPLACE, LOCK=NONE, ADD COLUMN memo TEXT;

七、架构级优化

18. 读写分离实现

graph TD
    A[应用服务器] -->|写操作| B[Master]
    A -->|读操作| C[Slave1]
    A -->|读操作| D[Slave2]
    B -->|复制| C
    B -->|复制| D

实现方案
- 使用MySQL Router - 基于Spring的AbstractRoutingDataSource - ShardingSphere-JDBC

19. 分库分表策略

分片策略对比

策略类型 优点 缺点
范围分片 易于扩展 可能产生热点
哈希分片 数据分布均匀 难以范围查询
时间分片 便于冷热分离 需要定期维护

总结

本文介绍的19个MySQL优化方法包括: 1. 合理创建索引 2. 避免索引失效 3. 使用覆盖索引 4. 分析执行计划 5. 避免SELECT * 6. 优化JOIN操作 7. 高效分页实现 8. 选择合适数据类型 9. 平衡规范化设计 10. 垂直拆分大表 11. 关键参数配置 12. 事务隔离级别选择 13. 批处理操作 14. 冷热数据分离 15. 中间表预计算 16. 慢查询分析 17. 定期表维护 18. 读写分离架构 19. 分库分表策略

实际项目中需要根据具体业务场景组合使用这些优化手段,并通过持续监控验证优化效果。记住:没有放之四海皆准的最优方案,只有最适合当前业务场景的优化策略。 “`

推荐阅读:
  1. 浅谈MySQL中SQL优化的常用方法
  2. 优化mysql的几种常用方法

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:Python怎么修改表格数据

下一篇:C/C++ Qt TreeWidget单层树形组件怎么应用

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》