Mysql性能优化深度解析-CSDN博客

本文链接：https://blog.csdn.net/dosthing/article/details/114754373

本文深入探讨了Mysql性能优化，从硬件操作系统层面和Mysql自身调优两方面进行分析。硬件调优关注网络、CPU、内存和磁盘性能。Mysql调优包括性能瓶颈分析、日志优化（如慢查询日志）、查询优化（如EXPLAIN分析）和建表优化。通过日志分析，可以定位慢查询并使用mysqldumpslow工具进行分析。查询优化涉及索引使用，避免全表扫描和不当操作符。此外，介绍了建表优化策略，如遵循三大范式，合理选择数据类型，以及使用自增字段作主键。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

- - Mysql 性能优化分析思考

Mysql 性能优化分析思考

性能分析主要是从硬件操作系统和应用软件方面着手：

1.硬件操作系统层面

硬件操作系统调优对其他应用软件也是一样的，主要从网络传输，运算速度、存储性能方面入手，涉及网卡带宽吞吐效率、CPU性能、内存容量大小、磁盘读写IO速度，它们共同决定了单机服务器性能的天花板。常用命令 top uptime vmstat

内核相关参数调优(/etc/sysctl.conf)

# 修改系统 进程的最大数目 和 最大打开的文件数
vim /etc/security/limits.conf  
* soft nproc 65535 
* hard nproc 65535 
* soft nofile 65535 
* hard nofile 65535

# 查看 Linux内核参数调优
vim /etc/sysctl.conf 
# 用户端口范围 
net.ipv4.ip_local_port_range = 1024 65535：
net.ipv4.tcp_max_syn_backlog = 4096  
net.ipv4.tcp_fin_timeout = 30  

net.core.somaxconn = 65535
net.core.netdex_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
加快TCP连接,快速回收TCP连接资源
net.ipv4.tcp_fun_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_te_recycle = 1
加快资源回收效率
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
kernel.shmmax = 4294967295
# 系统文件句柄，控制的是能打开文件数量
fs.file-max=65535
# Swap 调整(不使用 swap 分区)：
vm.swappiness=0

2.Mysql调优

主要着手方面：性能瓶颈分析、日志分析、查询优化、建表优化、架构优化

2.1性能瓶颈分析与优化

分析手段

#我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：
Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）
Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
常用的主要有 show status show processlist

调优

通过 set global variable = xx;或者my.cnf配置文件调整

1.连接优化

# 指定MySQL为快速重用而缓存的线程数量 
thread_cache_size： #值的范围从0~16384，默认值为0。根据内存大小设置1G→8，2G→16，3G→32，大于3G→64
thread_stack：      #每个线程堆栈大小，默认256K。

# 连接相关
max_connections           # 实例最大连接数，根据内存设置合理值     
max_connect_errors        # 错误连接数，能大则大 
max_user_connections      # 用户级最大连接数，0为不限制 
connect_timeout           # 连接超时 
skip-name-resolve         # 跳过域名解析 
wait_timeout              # 等待超时 
back_log                  # 可以在堆栈中的连接数量 

# 查看当前mysql线程相关数据
show global status like 'Threads_%';

Threads_cached    : 当前线程池中缓存有多少空闲线程
Threads_connected : 当前的连接数 ( 也就是线程数 )
Threads_created   : 已经创建的线程总数
Threads_running   : 当前激活的线程数 ( Threads_connected 中的线程有些可能处于休眠状态 )

2、内存缓冲区优化

MySQL占用内存 = 全局缓存 + ( 线程缓存 x 最大连接数 )

# 全局缓存，用于判断内存参数设置是否合理
key_buffer_size:         #索引缓存区大小，对于内存在4GB左右的服务器该参数可设置为384M或512M
innodb_buffer_pool_size：#缓存InnoDB的索引及数据，至关重要的参数，但是尽量设置不要超过物理内存70%
innodb_log_buffer_size： #InnoDB事务日志使用的缓冲区，100M以下
max_heap_table_size：    #用户可以创建的内存表大小
query_cache_size：       #缓存查询(SELECT)的结果

SELECT (@@key_buffer_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@max_heap_table_size + @@query_cache_size)/1024/1024;

# 索引缓存
SHOW GLOBAL STATUS LIKE '%key_read%';
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
| Key_read_requests | 2454354135490   |
| Key_reads         | 23490           |
+-------------------+-----------------+
# 一共有Key_read_requests个索引请求，一共发生了Key_reads次物理IO。Key_reads/Key_read_requests ≈ 0.1% 以下比较好。
SHOW GLOBAL STATUS LIKE 'key_blocks_u%';
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
|