当前位置：首页 > news >正文

10-MySQL-性能优化思路

news 来源：原创 2025/8/22 11:20:56

1、优化思路

当我们发现了一个慢SQL的问题的时候，需要做性能优化，一般我们是为了提高SQL查询更快，一个查询的流程由下图的各环节组成，每个环节都会消耗时间，要减少消耗时候需要从各个环节都分析一遍。

2 连接配置优化

第一个环节是客户端连接到服务端，这块可能会出现服务端连接数不够导致应用程序获取不到连接。
"MySQL error 1040 "Too many connections" 指的是你的数据库服务器达到了它的最大连接数限制。这通常发生在数据库服务器同时处理了太多的客户端连接请求时。
1、服务端：从服务端来说可以增加连接数，如果多个应用或者请求同时访问数据库，连接数不够的时候可以设置连接数更大些。

-- 修改最大连接数，当有多个应用连接的时候
SHOW VARIABLES LIKE 'max_connections';

2、服务端：及时释放不活动的连接，交互式和非交互式的客户端默认超时时间都是28800秒，8小时，我们可以把值调小

-- 及时释放不活动的连接，注意不要释放连接池还在使用的连接
SHOW VARIABLES LIKE 'wait_timeout';

3、客户端：减少从服务端获取的连接数，如果想要不上每一次执行SQL都创建一个新的连接，我们可以使用数据库连接池，实现连接的复用。比如dbcp、c3p0、阿里Druid、Hikari(springboot 2.x版本默认的连接池)
连接池也不是越大越好，只要维护好一定数量大小的连接池，其他客户端排队等待获取连接就可以了，有的时候连接池越大，效率反而越低。
Druid默认最大连接池大小是8，Hikari默认最大连接池大小是10。
一般建议连接池大小是机器核数乘以2+1，也就是说4核的机器，连接池维护9个连接就够了，这个公式从一定程度上来说对其他数据库也是适用的。
每一个连接，服务端都是需要创建一个线程来处理它的，连接数越多，服务端创建的线程数就会越多。创建连接会消耗时间消耗资源；而且在CPU同时执行执行超过核数的线程是通过分配时间片以及上下文切换方式实现的。CPU的核数是有限的，频繁的上下文切换会造成比较大的开销。
所以在修改数据库的配置的时候需要结合部署服务器的配置，比如服务器的CPU、内存、磁盘、网络。在不同硬件支撑下MySQL的配置也不尽相同。

参数名称	案例值	描述
key_buffer_size	1024MB	用于索引的缓冲区大小
query_cache_size	384MB	查询缓存,不开启请设为0
tmp_table_size	1024MB	临时表缓存大小
innodb_buffer_pool_size	4096MB	Innodb缓冲区大小
innodb_log_buffer_size	0MB	Innodb日志缓冲区大小
sort_buffer_size	4096KB	*连接数,每个线程排序的缓冲大小
read_buffer_size	4096KB	连接数,读入缓冲区大小
read_rnd_buffer_size	2048KB	连接数,随机读取缓冲区大小
join_buffer_size	8192KB	连接数,关联表缓存大小
thread_stack	512KB	连接数,每个线程的堆栈大小
binlog_cache_size	256KB	连接数，二进制日志缓存大小(4096的倍数
thread_cache_size	256	线程池大小
table_open_cache	2048	表缓存(最大不要超过2048)
max_connections	500	最大连接数

3 缓存-架构优化

3.1 缓存

在系统里面有一些很慢的查询，要么数据量大，要么关联的表多，要么计算逻辑非常复杂，这样的查询每次都会占用很长的连接时间。所以为了减轻数据库的压力，和提升查询效率，我们可以把数据放到内存缓存起来，比如使用redis；缓存适用于实时性不是特别高的业务，例如报表数据，一次查询2分钟，但是一天只需要查询一次。

3.2 集群主从复制

集群，冗余的处理方式，将请求发散到多个MySQL服务器中，减少单台服务器的压力；引入集群需要解决主从数据一致性的问题，一般使用的复制技术，将数据从主节点复制到从节点中。

MySQL的主从复制实现：MySQL所有的更新语句会记录到Server的binlog中，有了这个binlog，从服务器不断获取服务器的binlog文件，然后解析里面的SQL语句中，在从服务器上面执行一遍，从而保证主从数据的一致性。

1）主节点 binary log dump 线程

当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送binlog的内容。在读取binlog中的操作时，此线程会对主节点上的binlog加锁，当读取完成，在发送给从节点之前，锁会被释放。

（2）从节点I/O线程

当从节点上执行`start slave`命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的binlog。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log（中继日志）中。

（3）从节点SQL线程

SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
master节点只处理写请求，读请求分散到slave节点；这种方式虽然可以缓解单机的读写压力但是无法解决数据量很大的情况；因为主从结构中每个节点的数据都是相同的。

3.3 分库分表

分库分表总体上可以分为两大类，具体内容可以学习分库分表的课程。
垂直分表，减少并发压力；水平分表，解决存储瓶颈。
垂直分库的做法，把一个数据库按照业务拆分成不同的数据库。
水平分库分表：把单张表的数据按照一定的规则分布到多个数据库。

4 优化器 --SQL语句分析与优化

优化器的作用就是对我们的SQL语句进行优化分析，生成执行计划。在MySQL中有慢查询日志可以发现慢SQL。

4.1 慢查询日志

打开慢查询日志：SHOW VARIABLES LIKE 'slow_query%';

SQL查询超过3秒记录到慢SQL中：SHOW VARIABLES LIKE '%long_query%';

参数修改的方式：

1、set动态修改(重启后失效)

-- 1开启，0关闭，重启后失效
SET @@global.slow_query_log=1;
-- 默认10秒，另开一个窗口才会查到最新值
SET @@global.slow_query_log=3;SHOW VARIABLES LIKE '%long_query%';
SHOW VARIABLES LIKE '%slow_query_log%';

2 修改配置文件my.cnf
以下配置定义了慢查询日志的开关、时间、日志存放的路径

slow_query_log=ON
long_query_time=3
slow_query_log_file=/www/server/data/mysql-slow.log

4.2 Explain执行计划（下期详细介绍）

EXPLAIN SELECT * FROM system_users;

4.3 SQL与索引优化

当我们遇到SQL语句比较复杂，有多个关联和子查询的时候，就要分析SQL语句有没有改写的方法。

比如说： select * from user_innodb limit 9000000,10; 问题就是偏移量比较大，按照B+树的存储方式，叶子页面是一个双向链表，会一个一个的向后查找，导致查询耗时很长。可以改成先过滤ID，再limit；比如 select * from user_innodb where id>9000000,10

5 存储引擎

5.1 存储引擎选择

根据不同的业务表选择不同的存储引擎，例如：查询插入操作的业务表，用MyISAM。临时数据用Memory，常规的并发大更新多的表用InnoDB。

5.2 分表或分区

交易历史表：可以在年底为下一个年度建立12个分区，每个与一个分区
渠道交易表：分成：当日表、当月表、历史表、历史表再做分区

5.3 字段定义

原则：使用可以正确存储数据的最小数据类型，为每一列选择合适的字段类型。

比如整数类型： tinyint smallint mediumint int integer bigint bit，不同的类型最大的存储范围是不一样的，占用的存储空间也是不一样的，比如性别，只有男、女这种枚举类型，可以使用tinyint字段存储。字符类型varchar char，变长情况下使用varchar更节省空间，但是对于varchar字段，需要一个字节来记录长度。比如联系地址。固定长度使用char，比如手机号码等等。

6 总结

在工作中如果遇到了数据库瓶颈需要优化的时候，可以从以下维度来优化：基于开发成本的原则可以从以下几点分析：

1）SQL与索引
2）存储引擎与表结构（Innodb、MyISAM，表拆分：垂直拆分、水平拆分）
3）数据库架构(主从架构提升并发读、按业务类型拆分库、多主多从水平拆分表数据，解决单张表存储容量过大的问题)
4）MySQL配置(缓冲池，bufferpool越大性能越好等)
5）硬件与操作系统(cpu 内存网络带宽)

7 最佳实践

如果出现连接变慢、查询被阻塞，无法获取连接的情况

1、重启mysql
2、show processlist查看线程状态，连接数量、连接时间、状态
3、查看锁的状态
4、kill有问题的线程

对于具体的慢SQL
一、分析查询基本情况
涉及到的表结构、字段的索引情况、每张表的数据量、查询的业务含义。这样可能会发现有些SQL没有必要这么写或者表设计有问题(可以通过冗余字段提升性能)

二、找出慢的原因
可以通过Explain查看执行计划，分析SQL的执行情况，了解表访问顺序、访问类型、索引、扫描行数等信息。
如果总体的时间比较长，不确定哪一个因素影响最大，可以通过条件的增减、顺序的调整，找出引起查询慢的主要的原因，不断地尝试验证。定位出原因，比如没有走索引，或者在散列度很低的字段上增加了索引等情况。

创建索引或者联合索引
改写SQL,例如：
使用小表驱动大表
用join来代替子查询
not exist转换为left join is null
or 改成union
如果结果集允许重复的话，使用UNION ALL代替UNION
大偏移量的limit，先过滤再排序
表结构(冗余、拆分)、架构优化(缓存读写分类、分库分表)
业务层面的优化，必须条件是否必要