MySQL性能调优
应用角度
查询语句优化
- 尽量避免使用 SELECT *，只查询需要的列。
- 使用 JOIN 代替子查询，减少嵌套查询的层次。
- 避免在 WHERE 子句中使用 LIKE '%value%'，这会导致全表扫描。
- 合理使用 LIMIT 子句，限制查询结果的数量。
缓存
在应用程序级别使用缓存（如 Memcached、Redis 等），减少对数据库的直接访问次数。
数据库设计优化
- 读写分离：通过主从复制实现读写分离，将读操作分散到多个从服务器上，减轻主服务器的负载。
- 将表中不常用的字段或大型字段（如 TEXT、BLOB）分离到单独的表中，减少主表的大小和 I/O 开销。
- 分库分表
- 定期将不常用的旧数据归档到历史表中，减少主表的数据量，提高查询性能。
- 避免过度索引：因为每个索引都会占用额外的存储空间，并可能影响写操作的性能。
MySQL参数调优
面试可以说：比如说大幅调大InnoDB缓冲区大小，调整至总内存的60%-80%。关闭SSL等
[mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid
[client]
socket=/data/mysql/run/mysql.sock
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
socket=/data/mysql/run/mysql.sock
port=3306
user=root
default_authentication_plugin=mysql_native_password
ssl=0 #关闭ssl
max_connections=2000 #设置最大连接数
back_log=2048 #设置会话请求缓存个数
performance_schema=OFF #关闭性能模式
max_prepared_stmt_count=128000
#file
innodb_file_per_table=on #设置每个表一个文件
innodb_log_file_size=1500M #设置logfile大小
innodb_log_files_in_group=32 #设置logfile组个数
innodb_open_files=4000 #设置最大打开表个数
#buffers
innodb_buffer_pool_size=230G #设置buffer pool size,一般为服务器内存60%
innodb_buffer_pool_instances=16 #设置buffer pool instance个数，提高并发能力
innodb_log_buffer_size=64M #设置log buffer size大小
#tune
sync_binlog=1 #设置每次sync_binlog事务提交刷盘
innodb_flush_log_at_trx_commit=1 #每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去
innodb_use_native_aio=1 #开启异步IO
innodb_spin_wait_delay=180 #设置spin_wait_delay 参数，防止进入系统自旋
innodb_sync_spin_loops=25 #设置spin_loops 循环次数，防止进入系统自旋
innodb_spin_wait_pause_multiplier=25 #设置spin lock循环随机数
innodb_flush_method=O_DIRECT #设置innodb数据文件及redo log的打开、刷写模式
innodb_io_capacity=20000 # 设置innodb 后台线程每秒最大iops上限
innodb_io_capacity_max=40000 #设置压力下innodb 后台线程每秒最大iops上限
innodb_lru_scan_depth=9000 #设置page cleaner线程每次刷脏页的数量
innodb_page_cleaners=16 #设置将脏数据写入到磁盘的线程数
table_open_cache_instances=32 #设置打开句柄分区数
table_open_cache=30000 #设置打开表的数量
#perf special
innodb_flush_neighbors=0 #检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新，SSD关闭该功能
innodb_write_io_threads=16 #设置写线程数
innodb_read_io_threads=16 #设置读线程数
innodb_purge_threads=32 #设置回收已经使用并分配的undo页线程数
innodb_adaptive_hash_index=0
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES
| 参数名称 | 参数含义 | 优化建议 |
|---|---|---|
| innodb_thread_concurrency | InnoDB使用操作系统线程来处理用户的事务请求。 | 建议取默认值为0，它表示默认情况下不限制线程并发执行的数量。 |
| innodb_read_io_threads | 执行请求队列中的读请求操作的线程数。 | 根据CPU核数及读写比例进一步更改来提高性能。 |
| innodb_write_io_threads | 执行请求队列中的写请求操作的线程数。 | 根据CPU核数及读写比例进一步更改来提高性能。 |
| innodb_buffer_pool_instances | 开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写 | 建议设置8~32。 |
| innodb_open_files | 在innodb_file_per_table模式下，限制Innodb能打开的文件数量。 | 建议此值调大一些，尤其是表特别多的情况。 |
| innodb_buffer_pool_size | 缓存数据和索引的地方。 | 通常建议内存的60%左右。 |
| innodb_log_buffer_size | 缓存重做日志。 | 默认值是64M，建议通过查看innodb_log_wait，调整innodb_log_buffer_size大小。 |
| innodb_io_capacity | innodb 后台线程每秒最大iops上限。 | 建议为IO QPS总能力的75%。 |
| innodb_log_files_in_group | 重做日志组的个数。 | - |
| innodb_log_file_size | 重做日志文件大小。 | 如果存在大量写操作，建议增加日志文件大小，但日志文件过大，会影响数据恢复时间。 如果是非生产环境，测试极限性能时，尽量调大日志文件。 如果是商用场景，需要考虑数据恢复时间，综合折中后设置日志文件大小。 |
| innodb_flush_method | Log和数据刷新磁盘的方法： datasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。 O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成。 O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲。 | 建议O_DIRECT模式。 |
| innodb_spin_wait_delay | 控制轮询的间隔。 | 根据真实场景调试，直到看不到spin_lock热点函数等。优化建议180。 |
| innodb_sync_spin_loops | 控制轮询的循环次数。 | 根据真实场景调试，直到看不到spin_lock热点函数等。优化建议25。 |
| innodb_spin_wait_pause_multiplier | 控制轮询间隔随机数。 | 根据真实场景调试，直到看不到spin_lock热点函数等。默认值50，优化建议25-50。 |
| innodb_lru_scan_depth | LRU列表的可用页数量。 | 默认值是1024，非生产环境，测试极限性能可以适当调大，减少checkpoint次数。 |
| innodb_page_cleaners | 刷新脏数据的线程数。 | 建议与innodb_buffer_pool_instances相等。 |
| innodb_purge_threads | 回收undo的线程数。 | - |
| innodb_flush_log_at_trx_commit | 不管有没有提交，每秒钟都写到binlog日志里. 每次提交事务，都会把log buffer的内容写到磁盘里去，对日志文件做到磁盘刷新，安全性最好。 每次提交事务，都写到操作系统缓存，由OS刷新到磁盘，性能最好。 | 非生产环境，测试极限性能，可以设置为0。 |
| innodb_doublewrite | 是否开启二次写。 | 非生产环境，测试极限性能，可以设置为0，关闭二次写。 |
| ssl | 是否开启安全连接。 | 安全连接对性能影响较大，非生产环境，测试极限性能，可以设置为0，商用场景，根据客户需求调整。 |
| table_open_cache_instances | MySQL 缓存 table 句柄的分区的个数。 | 建议设置16-32。 |
| table_open_cache | Mysqld打开表的数量。 | 建议设置成30000。 |
| skip_log_bin | 是否开启binlog。 | 非生产环境，测试极限性能在参数文件中增加此参数，关闭binlog选项（添加至配置文件中： skip_log_bin #log-bin=mysql-bin）。 |
| performance_schema | 是否开启性能模式。 | 非生产环境，测试极限性能设置为OFF，关闭性能模式。 |
底层优化
在Mysql部署在专用服务器时，操作系统很多为其他任务设计的特性是没有意义的，可以通过设置调优使其专用于Mysql。在操作系统层面、CPU层面进行优化。
可以看这篇 OpenEuler的文档 讲得很好
网卡中断绑核
通过关闭irqbalance服务，使用手动绑定网卡中断到部分专用核上，隔离网卡中断请求和业务请求，可以有效提升系统的网络性能。
在这种情况下，CPU其他核心就不用去访问那几个负责网卡中断的核心的内存，因此可以限定NUMA范围为剩余其他核心。
缺页中断调优
使缺页中断尽可能命中快表可以提升性能，因此可以选择将页面大小调大，可以有效提高命中率。在这种场景下，内存消耗是不敏感的，因此大页带来的内存碎片其实不是非常重要。
通过命令getconf PAGESIZE查看当前系统的内存页大小，如果大小是4096(4K)，则可通过修改linux内核的内存页大小来使用更大的内存页，需要在修改内核编译选项后重新编译内核。简要步骤如下：
使用用户态网络协议栈
原生内核网络协议栈层次深，开销较大，且系统调用的成本也较高。通过gazelle用户态协议栈替代内核协议栈，且通过hook posix接口，避免系统调用带来的开销，能够大幅提高应用的网络I/O吞吐能力。
License:
CC BY 4.0