MySQL优化
MySQL优化
调优思路:
• 0.硬件优化
• 1.磁盘io优化
• 2.操作系统的优化
• 3.纵向拆解、横向拆解
• 4. my.cnf参数的优化
• 5.MySQL查询优化
• 6. MySQL存储引擎
硬件优化
CPU:64 位、高主频、高缓存,高并行处理能力
内存:大内存、主频高,尽量不要用SWAP
硬盘:建议是15000转或更大转数,使用RAID10、raid5磁盘阵列或SSD固态磁盘
网络:服务器标配的千兆网卡,建议是10G网卡,使用网卡bond技术。MySQL服务器尽可能和使用它的web服务器在同一局域网内,尽量避免诸如防火墙策略等不必要的开销。
注:网卡bond是通过把多张网卡绑定为一个逻辑网卡,实现本地网卡的冗余,带宽扩容和负载均衡。
磁盘 io 规划,io相关的技术
1、raid 技术:raid10或raid5
2、建议是15000转或更大转数。有条件的可以使用SSD固态磁盘
3、swap 分区:最好使用raid0或SSD
4、磁盘分区:将数据库目录放到一个磁盘或分区。存储数据的硬盘或分区和系统所在的硬盘分开。
5、由于binlog日志频繁记录操作,开销非常大,需要把binlog日志放到单独的硬盘分区上。
操作系统的优化--内核、tcp 连接数量
tcp连接数量、系统打开文件的最大限制
1、设置tcp 连接数量限制
1)修改内核对TCP连接的有关限制:
内核编译时默认设置的本地端口号范围可能太小,因此需要修改此本地端口范围限制。
第一步,修改/etc/sysctl.conf文件,在文件中添加如下行:
# net.ipv4.ip_local_port_range = 1024 65000
这表明将系统对本地端口范围限制设置为1024~65000之间。请注意,本地端口范围的最小值必须大于或等于1024;而端口范围的最大值则应小于或等于65535.修改完后保存此文件。
第二步,执行sysctl命令使修改生效:
# sysctl -p
2)Linux的最大并发允许的连接:
查看当前服务器的并发连接数命令:
查看使用了多少连接数:
# cat /proc/sys/net/ipv4/netfilter/ip_conntrack_count
新版本CentOS7用这个:
# cat /proc/sys/net/netfilter/nf_conntrack_count
查看总数多少:
# cat /proc/sys/net/ipv4/ip_conntrack_max
新版本CentOS7用这个:
# cat /proc/sys/net/netfilter/nf_conntrack_max
注:确保模块nf_conntrack被加载,执行modprobe nf_conntrack
临时设置增加并发数:
echo 524288 > /proc/sys/net/ipv4/ip_conntrack_max
新版本centos7用这个:
echo 524288 > /proc/sys/net/netfilter/nf_conntrack_max
永久设置增加并发数:
/etc/sysctl.conf中加入:net.ipv4.ip_conntract_max =102400
新版本centos7用这个:
vi /etc/sysctl.conf中加入:net.netfilter.nf_conntrack_max = 102400
执行systcl -p使修改生效:
# sysctl -p
3)Linux的并发过程中,time_wait的数量太大,引起连接的挂起等待,需要在服务器增加以下配置:
编辑文件vi /etc/sysctl.conf,加入以下内容:
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30
然后执行 /sbin/sysctl -p 让参数生效。
net.ipv4.tcp_syncookies = 1 表示开启SYN Cookies。当出现SYN等待队列溢出时,启用cookies来处理,可防范少量SYN攻击,默认为0,表示关闭;
net.ipv4.tcp_tw_reuse = 1 表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接,默认为0,表示关闭;
net.ipv4.tcp_tw_recycle = 1 表示开启TCP连接中TIME-WAIT sockets的快速回收,默认为0,表示关闭。
net.ipv4.tcp_fin_timeout 修改系默认的 TIMEOUT 时间
2、系统打开文件的最大限制:
用户级别:
(1)查看Linux系统用户最大打开的文件限制
# ulimit -n
(2)、修改打开文件限制
# vi /etc/security/limits.conf
mysql soft nofile 102400
mysql hard nofile 102400
mysql soft nproc 102400
mysql hard nproc 102400
其中mysql指定了要修改那个用户的打开文件数限制,可用“*”号表示修改所有用户的限制;soft或hard指定要修改软限制还是硬限制,102400则指定了想要修改的新的限制值,即最大打开文件数(请注意软限制要小于或等于硬限制)。
(3)、修改/etc/pam.d/login
# vi /etc/pam.d/login
session required /usr/lib64/security/pam_limits.so
这是告诉linux在用户完成系统登录后,应该调用pam_limits.so模块来设置系统对该用户可使用的各种资源数量的最大限制(包括用户可打开的最大文件数限制),而pam_limits.so模块就会从/etc/security/limits.conf文件中读取配置来设置这些限制值。
Linux系统级别:
查看linux系统对同时打开文件数的硬限制
# sysctl -a | grep file-max
fs.file-max = 65535
这表明这台linux系统最多允许同时打开(即包含所有用户打开文件数总和)65535个文件,是linux系统级硬限制,所有用户级的打开文件数限制都不会超过这个数值。通常这个系统级硬限制是linux系统在启动时根据系统硬件资源状况计算出来的最佳的最大同时打开文件数限制。
(1)、修改file-max限制
# vi /etc/sysctl.conf
fs.file-max = 1000000
执行sysctl -p生效
# sysctl -p
禁用不必要启动的服务
文件系统调优,
给数据库一个单独的文件系统,推荐使用XFS,一般效率更高、更可靠。
可以考虑在挂载分区时启用 noatime 选项。 #noatime#不记录访问时间。
[root@cong11 ~]# vim /etc/fstab #在挂载项中添加noatime选项就可以了。
UUID=46cb104c-e4dc-4f84-8afc-552f21279c65 /data xfs defaults,noatime 0 0
使设置立即生效,可运行:
[root@cong11 ~]# mount -o remount /data/
[root@cong11 ~]# mount
。。。
/dev/sdb1 on /data type xfs (rw,noatime)
这样以后系统在读此分区下的文件时,将不会再修改atime属性。
最小化原则:
1) 安装系统最小化
2) 开启程序服务最小化原则
3) 登录最小化原则
4) 权限最小化
纵向拆解、横向拆解
1、纵向拆解: 专机专用
例:现在公司一台服务器同时负责 web、ftp、数据库等多个角色。
纵向拆解后:数据库服务器专机专用,避免额外的服务可能导致的性能下降和不稳定性。
2、横向拆解:
主从同步、负载均衡、高可用性集群,当单个mysql数据库无法满足日益增加的需求时,可以考虑在数据库这个逻辑层面增加多台服务器,以达到稳定、高效的效果。
my.cnf 参数的优化
优化总原则:
给 mysql 的资源太少,则 mysql 施展不开:给 mysql 的资源太多,可能会拖累整个 OS。
40%资源给OS, 60%-70% 给mysql (内存和CPU)
对查询进行缓存
大多数LAMP应用都严重依赖于数据库查询,查询的大致过程如下:
PHP发出查询请求->数据库收到指令对查询语句进行分析->确定如何查询->从磁盘中加载信息->返回结果
如果反复查询,就反复执行这些。MySQL 有一个特性称为查询缓存,他可以将查询的结果保存在内存缓冲区中,今后对于同样的SELECT语句,将直接从缓冲区中读取结果。这样会极大地提高性能。不过,问题是查询缓存在默认情况下是禁用的。
注:一个SQL查询如果以select开头,那么MySQL服务器将尝试对其使用查询缓存。两个SQL语句,只要相差哪怕是一个字符(例如大小写不一样;多一个空格等),那么这两个SQL将使用不同的一个CACHE。
启动查询缓存:
[root@cong11 ~]# vi /etc/my.cnf 添加:
[mysqld] #在此字段中添加
query_cache_size = 256M #设置缓存为256M
query_cache_type=1 #1是开启mysql查询缓存,0是不缓存
注:通常设置为32-512Mb。设置完之后最好跟踪一段时间,查看是否运行良好。
[root@cong11 ~]# systemctl restart mysqld
查看:查询缓存
mysql> show status like 'qcache%';
参数说明:
1. Qcache_free_blocks:缓存中相邻内存块的个数。数目大说明可能有碎片。
如果数目比较大,可以执行:
mysql> flush query cache;
#对缓存中的碎片进行整理,从而得到一个空闲块。
2. Qcache_free_memory:缓存中的空闲内存大小,通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是减少。
3. Qcache_hits:表示有多少次命中缓存。每次查询在缓存中命中时就增大。数字越大,缓存效果越理想。
4. Qcache_inserts 表示多少次未命中然后插入,意思是新来的SQL请求在缓存中未找到,不得不执行查询处理,执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多,表示查询缓存应用到的比较少,效果也就不理想。当然系统刚启动后,查询缓存是空的,这很正常。
5. Qcache_lowmem_prunes:因内存不足删除缓存次数,缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。返回数字最长时间来看;如果返回数字在不断增长,就表示可能碎片非常严重,或者缓存内存很少。
如果Qcache_free_blocks比较大,说明碎片严重。 如果 free_memory 很小,说明缓存不够用了。
6. Qcache_not_cached:不适合进行缓存的查询的数量,通常是由于这些查询不是SELECT语句或者用了now()之类的函数。
7. Qcache_queries_in_cache:在当前缓存的查询(和响应)的数量。
8. Qcache_total_blocks:缓存中块的数量。
使用mysql查询缓存
mysql> create database aa;
mysql> use aa;
mysql> create table test3 (id int, name varchar(255)) ;
mysql> insert into test3 values (1,'aaaa'), (2,'aaaa');
mysql> select * from test3;
mysql> show status like "qcache%"; #没有命中
再查询:
mysql> select * from test3;
mysql> select * from test3;
mysql> show status like "qcache%"; #可以看见缓存了2次
强制限制MySQL资源设置
您可以在mysqld中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。
[root@cong11 ~]# vi /etc/my.cnf
[mysqld]
max_connections=500
wait_timeout=10
max_connect_errors = 100
参数:
max_connections:MySQL的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这是建立在服务器能支撑的情况下,因为如果连接数越多, MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。
可执行mysql> show variables like 'max_connections';查看mysql的最大连接数设置。
注:数值过小会经常出现ERROR 1040: Too many connections错误
wait_timeout:指的是MySQL在关闭一个非交互的连接之前所要等待的秒数(空闲时间)。
可执行mysql> show variables like 'wait_timeout';查看wait_timeout的值。
max_connect_errors:是一个MySQL中与安全有关的计数器值,它负责阻止过多尝试连接失败的客户端,以防止暴力破解密码的情况, 当超过指定次数,MYSQL服务器将禁止该主机的连接请求。max_connect_errors的值与性能并无太大关系。
可执行mysql> show variables like 'max_connect_errors';查看该参数的设置值。
如果一个主机在连接到服务器时有问题,并重试很多次后放弃,那么这个主机就会被锁定,直到执行:mysql> FLUSH HOSTS;
测试:
[root@cong11 ~]# vi /etc/my.cnf #在配置文件中添加一下内容
[mysqld]
max_connections=500
wait_timeout=10
max_connect_errors = 100
验证:
mysql> show status like 'max_used_connections'; //当前有1个连接
再另一个客户端打开一个mysql连接,执行一下查询,可以看到有两个:
mysql> show status like 'max_used_connections';
表高速缓存:
数据库中的每个表存储在一个文件中,要读取文件的内容,你必须先打开文件,然后再读取。为了加快从文件中读取数据的过程,mysqld 对这些打开文件进行了缓存,其最大数目由 /etc/my.cnf中的 table_cache 指定
可以执行mysql> show variables like 'table_open_cache';查看该参数的设置值。
[root@cong11 ~]# vim /etc/my.cnf #在表中添加以下内容
[mysqld]
table_open_cache=512 #最多缓存512个表
[root@cong11 ~]# systemctl restart mysqld
mysql> show global status like 'open%_tables';
Open_tables 表示打开表的数量,Opened_tables表示打开过的表数量,如果Opened_tables数量过大,说明配置中 table_open_cache值可能太小.
table_cache 的值在 2G 内存以下的机器中的值默认从 256 到 512个。
对于有 1G 内存的机器,推荐值是 128-256。
注:通常在设置table_open_cache参数的时候,在业务的高峰时期,检查open_Tables的值,如果open_Tables的值与table_open_cache的值相等,并且opened_tables的值在不断的增加,这个时候就需要对table_open_cache的值增加了。
关键字(索引)缓冲区
key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。
执行mysql> show variables like 'key_buffer_size';查看该参数的设置值。
[root@cong11 ~]# vim /etc/my.cnf //在表中添加以下内容
[mysqld]
key_buffer_size=512M #关键字缓冲区大小
- [root@cong11 ~]# systemctl restart mysqld
- 查看:
- mysql> show status like '%key_read%';
Key_reads 代表命中磁盘的请求个数,Key_read_requests 是总数, 命中磁盘的读请求数除以读请求总数就是不命中比率.
总结:
MySQL有超过100个可以调节的设置,要记住那么多基本是不可能的,但是幸运的是你只需要记住很少一部分你就可以基本满足你的需求了,我们还可以通过“show status”命令来查看MySQL是否按照我们的期望在运行。
查询优化
1、启用mysql的慢查询日志
慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志,可以查找出哪些查询语句的执行效率很低,以便进行优化。
查看慢查询日志的定义:
mysql> show variables like '%slow_query_log%';
mysql> show global variables like '%long%';
其中:
slow_query_log: off关闭状态 on开启状态
slow_query_log_file:慢查询日志存放地点
long_query_time:选项来设置一个时间值,时间以秒为单位,可以精确到微秒。如果查询时间超过了这个时间值(默认为10秒),这个查询语句将被记录到慢查询日志中, 设置为0的话表示记录所有的查询。
开启慢查询日志功能:
方法1:通过配置文件vi /etc/my.cnf开启慢查询日志,在配置文件my.cnf中添加下面的配置项:
slow_query_log = 1 #开启慢查询日志
slow-query-log-file=/data/mysql/log/slow.log #这个路径对 mysql 用户具有可写权限
long_query_time=1 #查询超过1秒钟的语句记录下来
log-queries-not-using-indexes =1 #没有使用索引的查询
注:如果不指定存储路径,慢查询日志默认存储到MySQL数据库的数据文件下,如果不指定文件名,默认文件名为hostname-slow.log
重启mysqld服务,使修改的参数生效,执行:
systemctl restart mysqld
测试:
mysql> create table test (id int,name varchar(20));
mysql> insert into test values (1, ‘man’);
mysql> select * from test;
# cat /data/mysql/log/slow.log
方法2:通过登录mysql服务器直接定义,方式如下:
mysql>set global slow_query_log=1; #开启慢查询日志
mysql>set global long_query_time=0.001; #更改时间
2、使用explain执行计划
可以通过在select语句前使用 explain,来获取该查询语句的执行计划,而不是真正执行该语句。
3、当只要一行数据时使用limit 1
4、只取自己需要的column,避免使用select *
5、添加索引(主键索引/唯一索引/普通索引/复合索引)
6、不做列运算:select id from tablename where age + 1 = 10,任何对列的操作都将导致表扫描,查询时要尽可能将操作移至等号右边
7、SQL语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大SQL可以堵死整个库。
8、or改写成in
9、避免%xxx式查询
10、尽量避免在where子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· DeepSeek “源神”启动!「GitHub 热点速览」
· 上周热点回顾(2.17-2.23)