MySQL性能优化

合集 - MySQL(9)

1.浅谈InnoDB索引2023-04-202.聊聊MySQL锁2023-04-203.MySQL数据库事务2023-04-194.MyISAM 和 InnoDB 的区别2023-04-235.Explain执行计划分析2023-04-236.Innodb实现事务流程2023-04-247.前缀索引2023-04-248.MySQL8分析利器Explain analyze2023-04-24

9.MySQL性能优化2023-04-24

收起

提升硬件配置

凡是优化性能,只要没有资源过剩,提升硬件配置一定是可行思路

公共参数优化

1. 最大连接数
   :::info
   max_connections = max_used_connections * 1.25
   :::

2. 单条SQL结果集缓存上限,默认4KB
   :::info
   query_cache_limit = 1M;
   :::

InnoDB参数优化

背景:
数据库引擎为InnoDB,配置为4核8G
优化步骤:

1. 优化缓冲池大小,默认8M,一般配置成可用内存的3/4
   :::info
   innodb_buffer_pool_size = 6G;
   :::

2. 优化I/O线程,一般配置成CPU核心数的1/2
   :::info
   innodb_read_io_threads = 2;
   innodb_write_io_threads = 2;
   :::

3. 优化I/O模型
   :::info
   innodb_flush_method = O_DIRECT;
   :::

4. 优化事务日志文件大小,默认5M,改成1G
   :::info
   innodb_log_file_size = 1G;
   :::

分库分表

一般我们分库分表从四个方向考虑:垂直分表,垂直分库,水平分表,水平分库

垂直分表

概念

将数据表字段按照业务需求,访问频率,字段大小等拆分到多个表

优点

• 提升了热门数据的操作效率
• 减少了IO争抢和锁表概率

问题

• 性能提升还是受限于单库物理机器

垂直分库

概念

将数据表按照业务需求拆分到多个库,一言概之--专库专用

优点

• 解决了业务层面的耦合
• 可以对不同业务数据进行分级管理,维护,监控,拓展等
• 一定程度上解决了单机性能瓶颈,提高了数据库连接和IO

问题

• 业务无法拆分时会有单表数据量过大导致的性能瓶颈

水平分表

概念

将数据表数据按照一定的规则拆分到多个表,比如按照业务唯一键哈希取模

优点

• 优化了单表数据量过大的性能问题
• 减少了IO争抢和锁表概率

问题

• 操作需要额外的路由寻表,提升了系统的复杂度

水平分库

概念

将数据表数据按照一定的规则拆分到多个库,比如按照业务唯一键哈希取模

优点

• 解决了单库大数据,高并发的性能问题
• 提高了系统稳定性和可用性

问题

• 操作需要额外的路由寻库,提升了系统的复杂度

索引优化

索引这个方向可以基于以下思路尝试优化:

1. 是否有索引?

没有就建立合适的索引

2. 是否用了索引?

没有使用则检查SQL,优化SQL

3. 是否用了最优索引?

可以先优化SQL,究极手法是手动指定走哪个索引

索引覆盖

当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,减少了回表操作,提高了效率.
下面的SQL语句就可以通过建立普通索引idx_sort_datas(sort,datas)使查询覆盖索引,提高效率

SELECT sort,datas FROM t WHERE sort = 1

索引失效

索引失效的一般场景:

• 索引列参与计算
• 对索引列使用函数
• 使用OR的同时查询条件判断符号出现<或者>
• LIKE时‘%’在参数之前
• 字段做了隐式类型转换
• 使用!=判断,使用IS NOT NULL判断,使用ORDER BY,使用IN都可能

深度分页优化

优化前SQL:

SELECT id,sort,datas FROM t ORDER BY sort LIMIT 100,10;

优化后SQL:

SELECT id,sort,datas FROM t 
INNER JOIN (SELECT id FROM t ORDER BY sort LIMIT 100,10) AS tlimit USING(id);

其他优化

• 避免select *,只查询需要的字段
• 尽量用union all代替union,减少去重的消耗
• 字段设置合适的数据类型
• 小表驱动大表
• 适当批量操作
• 灵活运用limit,只查需要的条数
• 子查询改为表连接查询
• 适当反范式,减少表连接查询
• 数据同步等场景时可以记录标志位做增量同步