MySQL的优化

最新推荐文章于 2025-06-10 09:25:31 发布

weixin_46106069

最新推荐文章于 2025-06-10 09:25:31 发布

阅读量86

点赞数

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_46106069/article/details/113178776

版权

MySQL优化三大方向

优化MySQL所在服务器内核。
对MySQL配置参数进行优化（my.cnf）此优化需要进行压力测试来进行参数调整。
对SQL语句以及表优化。

服务器内核方面优化：

网络参数配置需要修改配置文件/etc/sysctl.conf，可以在配置文件后面添加以下内容：

net.core.somaxconn=65536     #每个监听端口允许的最大的监听队列长度，这个值应该适当增大。
net.core.netdev_max_backlog=65536     #当网络端口接受数据包的速率比内核处理数据速度快时，允许发送到队列中的最大包数。
net.ipv4.tcp_max_syn_backlog=65536     #还未获得链接的请求可保持在队列中的数量，当超过这个数目时，链接请求就可能被抛弃。
net.ipv4.tcp_fin_timeout=10     #tcp链接等待时间，调整这个参数目的是为了加快tcp链接回收的速度。
net.ipv4.tcp_tw_reuse=1     #加快tcp链接回收的速度
net.ipv4.tcp_tw_recycle=1     #加快tcp连接回收的速度

以下四个参数设置tcp链接接受和发送数据时缓冲区大小的最大值和默认值，应该将其调整的更大一些。

net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216

以下三个参数用于减少时效的tcp链接所占用的资源，提升资源回收效率，改的较小一些。

net.ipv4.tcp_keepalive_time=100     #代表TCP发送keepalive探测消息的时间间隔
net.ipv4.tcp_keepalive_intvl=10     #代表当TCp发送keepalive探测消息未获得确认是重发探测消息的时间间隔
net.ipv4.tcp_keepalive_probes=3     #代表TCP链接失效之前，最多发送多少keepalive探测消息

内存相关参数配置：
内存的相关参数同样修改/etc/sysctl.conf文件：

kernel.shmmax=2147483648     #用于定义单个共享内存段的最大值，这个参数应该设置的足够大，以便共享内存段能容纳下整个InnoDB缓冲池的大小。建议设置成物理内存的一半。
vm.swappiness=0 表示除非虚拟内存使用完了，否则不用swap分区。

特定资源限制配置：
这个配置需要修改配置文件/etc/security/limits.conf，主要就是对打开文件数量的配置，在MySQL的InnoDB存储引擎中，每一张表就对应着2-3个文件，对于允许打开文件的数量适当增大，可以使MySQL的数据库性能更好。
打开文件数的配置：

* soft nofile 65535
* hard nofile 65535
* 表示对所有用户生效
soft 表示是当前系统生效的设置
hard 表示系统中所能设定的最大值
nofile 表示所限制的资源是打开文件的数量

磁盘调度策略配置
一般默认情况下，centos系统的磁盘调度策略是完全公平队列模式（cfg），这种调度策略对于数据库服务器来说不太适合，而应该使用deadline调度策略。
通过以下命令可以修改调度策略：

echo deadline>/sys/block/devname/queue/scheduler

MySQL参数优化：

1.MySQL默认的最大连接数为100，可以在MySQL客户端使用以下命令查看：

mysql> show variables like 'max_connections';

在这里插入图片描述

2.查看当前访问MySQL的线程：

mysql> show processlist;

在这里插入图片描述
3.设置最大连接数：
注：最大可设置16384。

mysql> set global max_connections = 5000;

在这里插入图片描述
4.查看当前被使用的connections：

mysql> show global status like 'max_user_connections'

对MySQL语句性能优化：

1.为查询缓存优化查询。
2.EXPLAIN我们的select查询（可以查看执行的行数）。
3.当只要一行数据时使用limit 1。
4.为搜索字段建立索引。
5.在join表的时候使用想当类型的列，并将其索引。
6.千万不要order by rand（）。
7.避免select * 查询。
8.永远为每张表设置一个ID。
9.可以使用enum而不要varchar。
10.尽可能使用not null。
11.固定长度的表会更快。
12.垂直分割。
13.小心“永久链接”。