MySQL调优之服务器参数优化实践

流烟默

已于 2022-11-19 13:57:31 修改

阅读量3k

点赞数 1

分类专栏： # MySQL高级文章标签： MySQL配置调优

于 2019-03-01 18:26:18 首次发布

本文链接：https://blog.csdn.net/J080624/article/details/88065417

版权

MySQL高级专栏收录该内容

43 篇文章 13 订阅

订阅专栏

关联博文：
MySQL中常见的优化策略详解

接上文MySQL中常见的优化策略详解后，我们继续学习MySQL自身配置的优化。本文是基于Innodb引擎。

优化MySQL服务器主要从两个方面优化，一方面是对硬件进行优化，另一方面是对MySQL服务的参数进行优化。

优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈直接决定MySQL数据库的运行速度和效率。针对性能瓶颈提高硬件配置，可以提高MySQL数据库查询、更新的速度。

配置较大的内存。足够大的内存是提高MySQL数据库性能的方法之一，内存的速度比磁盘IO块的多，可以通过增加系统的缓冲区容量使数据在内存中停留的时间更长，以减少磁盘IO。
配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。磁盘的IO能力，也就是它的寻道能力，目前的SCSI高速旋转的是7200转/分钟。这样的速度，一旦访问的用户量上去，磁盘的压力就会过大，如果是每天的网站PV（page view）在150W，一般的配置就无法满足这样的需求了。现在SSD盛行，在SSD上随机访问和顺序访问性能几乎差不多，使用SSD可以减少随机IO带来的性能损耗。
合理分布磁盘IO，把磁盘IO分散在多个设备上，以减少资源竞争，提高并行操作能力。
配置多处理器，MySQL是多线程的数据库，多处理器可以同时执行多个线程。

目前来说，InnoDB是为MySQL处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。在数据量大的网站或是应用中Innodb是倍受青睐的。另一方面，在数据库的复制操作中Innodb也是能保证master和slave数据一致有一定的作用。

可以使用下面几个工具来做基准测试：

sysbench：一个模块化，跨平台以及多线程的性能测试工具
iibench-mysql：基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具
tpcc-mysql：Percona开发的TPC-C测试工具

具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数。通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

【1】内存优化

① innodb_buffer_pool_size

这个是Innodb最重要的参数，和MyISAM的key_buffer_size有相似之处。这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。表示InnoDB类型的表和索引的最大缓存。它不仅仅缓存索引数据，还会缓存表的数据。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。

如果是一个专用DB服务器，那么他可以占到内存的70%-80%。如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M

在MySQL8下，该值默认是128M。设置方法，在my.cnf文件里：

innodb_buffer_pool_size=4G

注意：在MySQL5.7版本之前，调整innodb_buffer_pool_size大小必须在my.cnf配置里修改，然后重启MySQL进程才可以生效。如今到了MySQL5.7+版本，就可以直接动态调整这个参数，方便了很多。

需要注意的是在调整innodb_buffer_pool_size 期间，用户的请求将会阻塞，直到调整完毕，所以请勿在白天调整，在凌晨3-4点低峰期调整。

调整时，内部把数据页移动到一个新的位置，单位是块。如果想增加移动的速度，需要调整innodb_buffer_pool_chunk_size参数的大小，在MySQL8下，该值默认是128M。

innodb_buffer_pool_chunk_size的大小，计算公式是

innodb_buffer_pool_size/innodb_buffer_pool_instances

可以使用如下命令查看：

SELECT 
@@innodb_buffer_pool_size,
@@innodb_buffer_pool_chunk_size,
@@innodb_buffer_pool_instances;

通过查询show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests– Innodb_buffer_pool_reads)/ Innodb_buffer_pool_read_requests越高越好。

② innodb_additional_mem_pool_size

InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小。当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率。当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小。

用来存放Innodb的内部目录，这个值不用分配太大，系统可以自动调。通常设置16Ｍ够用了，如果表比较多，可以适当的增大。

设置方法，在my.cnf文件里：

innodb_additional_mem_pool_size = 16M

【2】日志优化

① innodb_log_file_size

作用：指定在一个日志组中，每个log的大小。结合innodb_buffer_pool_size设置其大小，25%-100%。避免不需要的刷新。

注意：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。一般取256M可以兼顾性能和recovery的速度。

分配原则：几个日志成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日志上限大小为4G。一般控制在几个Log文件相加大小在2G以内为佳。具体情况还需要看你的事务大小，数据大小为依据。

可以在my.cnf文件里设置：

innodb_log_file_size = 256M

这个参数是一个日志组中每个日志文件的大小,此参数在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但好似副作用是一旦系统崩溃恢复的时间会加长.

② innodb_log_files_in_group

作用：指定你有几个日志组。

分配原则：一般我们可以用2-3个日志组。默认为两个。

设置方法：在my.cnf文件里：

innodb_log_files_in_group=3

③ innodb_log_buffer_size

这是InnoDB存储引擎的事务日志所使用的缓冲区。为了提高性能，也是先将信息写入innodb log buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件（或者日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。

另外如果你需要处理大量的TEXT，或是BLOB字段，可以考虑增加这个参数的值。

MySQL8.0下默认值为16M。

show variables like '%innodb_log_buffer_size%';

innodb_log_buffer_size	16777216

可以在my.cnf文件里设置：

innodb_log_buffer_size=64M

④ innodb_flush_log_at_trx_commit

控制事务的提交方式,也就是控制log的刷新到磁盘的方式,表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于InnoDB存储引擎非常重要，该参数有3个值，分别为0、1、2，默认值为1。

分配原则：这个参数只有3个值（0，1，2）。默认为1，性能更高的可以设置为0或是2，这样可以适当的减少磁盘IO（但会丢失一秒钟的事务），游戏库的MySQL建议设置为0。

0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作。但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；
1：（默认为1）在每次事务提交的时候将logbuffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；
2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。

当这个值为0时：表示每秒1次的频率将数据写入日志文件并将日志文件写入磁盘。每个事务的commit并不会触发前面的任何操作。该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当这个值为1时：innodb 的事务LOG在每次提交后写入日志文件，并对日志做刷新到磁盘。这个可以做到不丢任何一个事务。该模式是最安全的，但也是最慢的一种方式。因为每次事务提交或事务外的指令都需要把日志写入（flush）硬盘。

当这个值为2时：表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

从以上分析，当这个值不为１时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。

设置方法在my.cnf文件里：

innodb_flush_log_at_trx_commit=1

【3】文件IO与空间占用

① innodb_file_per_table

作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。默认是关闭的，建议打开（innodb_file_per_table=1）

分配原则：只有使用不使用。但DB还需要有一个公共的表空间。

在my.cnf文件里设置：

innodb_file_per_table=1

② innodb_read_io_threads/innodb_write_io_threads

innodb_read_io_threads这个参数可调整的读请求的后台线程数

innodb_write_io_threads这个参数可调整的写请求的后台线程数

在my.cnf文件里设置：

innodb_read_io_threads=4
innodb_write_io_threads=4

③ innodb_open_files

作用：限制Innodb能打开的表的数据。

分配原则：这个值默认是300。如果库里的表特别多的情况，可以适当增大为1000。

innodb_open_files的大小对InnoDB效率的影响比较小。但是在InnoDBcrash的情况下，innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。

在my.cnf文件里设置：

innodb_open_files=800

④ innodb_data_file_path

指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。

例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend

两个数据文件放在不同的磁盘上。数据首先放在ibdata1 中，当达到900M以后，数据就放在ibdata2中。

在my.cnf文件里设置：

innodb_data_file_path
=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:1G:autoextend

⑤ innodb_data_home_dir

放置表空间数据的目录，默认在MySQL的数据目录，设置到和MySQL安装文件不同的分区可以提高性能。

在my.cnf文件里：（比如MySQL的数据目录是/data/MySQL/data，这里可以设置到不同的分
区/home/MySQL下）

innodb_data_home_dir = /home/MySQL

【4】其他参数调优

① innodb_max_dirty_pages_pct

作用：在buffer pool缓冲中，允许Innodb的脏页的百分比，值在范围1-100,默认为90，建议保持默认。

这个参数的另一个用处：当Innodb的内存分配过大，致使Swap占用严重时，可以适当的减小调整这个值，使达到Swap空间释放出来。

建义：这个值最大在90%，最小在15%。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。

在my.cnf文件里：

innodb_max_dirty_pages_pct＝90

动态更改需要有管理员权限：

set global innodb_max_dirty_pages_pct=50;

② innodb_thread_concurrency

同时在Innodb内核中处理的线程数量。建议默认值。

在my.cnf文件里：

innodb_thread_concurrency = 16

③ max_connections

表示允许临街到MySQL数据库的最大数量，默认值是151。如果状态变量connection_errors_max_connections不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这时可以考虑增大max_connections的值。

在Linux平台下，性能好的服务器，支持500-1000个连接不是难事，需要根据服务器性能进行评估设定。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能导致MySQL服务器僵死。

MySQL8下查看默认值：

show variables like '%max_connections%';

max_connections	151
mysqlx_max_connections	100

数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。

max_connect_errors	100
max_connections	151

# 响应的连接数
max_user_connections	0

较理想的设置是：max_user_connections / max_connections * 100% ≈ 85% 。最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

④ query_cache_size

表示查询缓冲区的大小，缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集。

当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。

可以通过命令show status like 'query_cache_%'查看目前系统query cache使用大小。通过检查状态值Qcache_，可以知道query_cache_size设置是否合理。

show global status like '%Qcache%';

Qcache_free_blocks	1
Qcache_free_memory	1031872
Qcache_hits	0
Qcache_inserts	0
Qcache_lowmem_prunes	0
Qcache_not_cached	135103
Qcache_queries_in_cache	0
Qcache_total_blocks	1

可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes 的值非常大，则表明经常出现缓冲不够的情况。就要增加Query_cache_size的值。如果Qcache_hits 的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小。如果Qcache_hits的值较小，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。

此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓
冲。

select SQL_NO_CACHE * from tb_sys_user

与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。

have_query_cache	YES
query_cache_limit	1048576
query_cache_min_res_unit	4096
query_cache_size	1048576
query_cache_type	OFF
query_cache_wlock_invalidate	OFF

query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。
- - query_cache_type值为0时，所有的查询都不使用查询缓存区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓存区内存。
- - query_cache_type=1时，所有的查询都将使用查询缓存区，除非在查询语句中指定SQL_NO_CACHE。
- - query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓存区。使用查询缓存区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况
query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。
query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。

检查状态值 Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。

查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

需要特别说明的是，MySQL8.0之后，查询缓冲区失效，不再存在该配置。

⑤ sort_buffer_size

表示每个需要进行排序的线程分配的缓冲区的大小，connection级的参数。增加这个参数的值可以提高order by 或 group by 操作的速度。

MySQL8下默认值如下所示：

show variables like '%sort_buffer_size%';

# 1M
innodb_sort_buffer_size	1048576
# 148M
myisam_sort_buffer_size	155189248
# 256KB
sort_buffer_size	262144

对于内存在4GB左右的服务器推荐设置为6-8M，如果有100个连接，那么实际分配的总共排序缓冲区大小为100*6=600MB。

⑥ read_buffer_size

connection级的参数,表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（单位字节）。当线程从表中连续读取记录时需要用到这个缓冲区。

MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能。

MySQL8下默认值为64K，可以设置为4M

show variables like '%read_buffer_size%';

read_buffer_size	65536

⑦ max_allowed_packet

网络包的大小，为避免出现较大的网络包错误，建议设置为16M

⑧ table_open_cache

当某一连接访问一个表时，MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开，则会直接访问缓存中的表，以加快查询速度；如果该表未被缓存，则会将当前的表添加进缓存并进行查询。

通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。

如果发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么就需要增加table_open_cache的值。通常设置为512即可满足需求。

⑨ thread_cache_size

线程池缓存线程数量的大小。当客户端断开连接后将当前线程缓存起来，当再接到新的连接请求时快速响应无需创建新的线程。这尤其对那些使用短连接的应用程序来说可以极大的提高创建连接的效率。

show variables like '%thread_cache_size%';

# MySQL8下默认为10
thread_cache_size	10

线程缓存，如果一个客户端断开连接，这个线程就会被放到thread_cache_size中（缓冲池未满），可以通过使用如下命令监控服务器线程状态来适当调整线程池的大小：

show global status like '%threads%';

Delayed_insert_threads	0
Mysqlx_worker_threads	2
Mysqlx_worker_threads_active	0
Slow_launch_threads	0
Threads_cached	0
Threads_connected	2
Threads_created	2
Threads_running	2

当Threads_cached越来越少，但Threads_connected始终不降，且Threads_created持续升高，可适当增加thread_cache_size的大小。

⑩ innodb_thread_concurrency

线程并发数，建议设置为CPU内核数*2

key_buffer_size

表示索引缓冲区的大小。MySQL8下该值默认为8M。索引缓冲区是所有的线程共享，增加索引缓冲区可以得到更好处理的索引（对所有读和多重写）。

仅作用于 MyISAM存储引擎，用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。

当然，这个值不是越大越好，它的大小取决于内存的大小。如果这个值太大，就会导致操作系统频繁换页，也会降低系统性能。对于内存4G左右，可设为256M或384M，通过查询show status like'key_read%'，保证key_reads / key_read_requests在0.1%以下最好。

不要设置超过可用内存的30%。即使不用MyISAM存储引擎，也要设置该值8-64M，用于临时表。

(11) innodb_lock_wait_timeout

这个参数自动检测行锁导致的死锁并进行相应处理,但是对于表锁导致的死锁不能自动检测。默认值为50秒。

(12) innodb_support_xa

这个参数设置MySQL是否支持分布式事务

(13) innodb_io_capacity

这个参数刷新脏页数量和合并插入数量，改善磁盘IO处理能力。对应的还有innodb_io_capacity_max。

(14) innodb_use_native_aio

异步I/O在一定程度上提高系统的并发能力。在Linux系统上，可以通过将MySQL的服务器此参数的值设定为ON。设定InnoDB可以使用Linux的异步I/O子系统.

(15) innodb_buffer_pool_instances

这个参数能较好的运行于多核处理器，支持使用此参数对服务器变量建立多个缓冲池实例。

每个缓冲池实例分别自我管理空闲列表、列表刷写、LRU以及其它跟缓冲池相关的数据结构，并通过各自的互斥锁进行保护。

(16) innodb_purge_threads

MySQL5.5以前碎片回收操作是主线程的一部分，这经定期调度的方式运行，但会阻塞数据库的其他操作。

到5.5以后，可以将这个线程独立出来。这个能让碎片回收得更及时而且不影响其他线程的操作

(17) innodb_flush_method

这个参数控制着innodb数据文件及redo log的打开、刷写模式，对于这个参数，有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT

默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer

当为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件

当为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

总结一下三者写数据方式：

fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成
O_DIRECT模式：数据文件的写入操作是直接从MySQL innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲

(18) back_log

用于控制MySQL监听TCP端口时设置的积压请求栈大小。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log。如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。效果等同于Tomcat connector中的参数accptCount。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。

MySQL5.6.6版本之前默认值为50，之后的版本默认为50+(max_connections/5)，对于Linux系统推荐设置为小于512的整数，但最大不超过900。MySQL8.0下该值默认为80。

(19) interactive_timeout

一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对MySQL_real_connect()使用CLIENT_INTERACTIVE 选项的客户。

默认数值是28800，可调优为7200。

(20) read_rnd_buffer_size

MySQL随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

一般可设置为16M。

(21) join_buffer_size

联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样，该参数对应的分配内存也是每个连接共享。如果在SQL调优时，存在很多必须存在的join查询，那么可以适当增加该参数大小。

read_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，假设都是16M。也就是说，如果有100个线程连接，则占用为16M*100

(22) table_cache

表高速缓存的大小，表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。物理内存越大，设置就越大。默认为2402，调到512-1024最佳。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。

主要有如下三个：

show variables like '%cache%'

table_definition_cache	1400
table_open_cache	2000
table_open_cache_instances	16

每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_open_cache ，并且opened_tables在
不断增长，那么你就需要增加table_open_cache 的值了。

查看状态值语法如下

show global status like '%Open%';

Open_table_definitions	336
Open_tables	1250
Opened_files	2174
Opened_table_definitions	773
Opened_tables	1751
Slave_open_temp_tables	0
Table_open_cache_hits	197933
Table_open_cache_misses	1749
Table_open_cache_overflows	29

注意，不能盲目地把table_open_cache 设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

比较合适的值为：

Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。

(23) max_heap_table_size

用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#

这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。

(24) tmp_table_size

通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。

如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。

show global status like 'created_tmp%';

Created_tmp_disk_tables	789
Created_tmp_files	9
Created_tmp_tables	2106

每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数。

比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%。

默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞

(25) wait_timeout

MySQL服务器关闭非交互连接之前等待的秒数。在会话启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型–由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义。参数默认值：28800秒（8小时)。

换句话就是指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10 。

MySQL8下查看等待超时时间：

show variables like '%wait_timeout%';

innodb_lock_wait_timeout	50
lock_wait_timeout	31536000
mysqlx_wait_timeout	28800
wait_timeout	28800

(26) skip_name_resolve

禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问。

(27) record_buffer

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值。

【5】实践总结

① 查看线程使用情况

通过如下命令查看：

show variables like '%thread%'

thread_cache_size	10
thread_handling	one-thread-per-connection
thread_stack	262144


show global status like 'Thread%';

Threads_cached	6
Threads_connected	4
Threads_created	306
Threads_running	1

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。

Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。

② 查询缓存

使用如下命令：

show global status like 'qcache%';

Qcache_free_blocks	1
Qcache_free_memory	1031872
Qcache_hits	0
Qcache_inserts	0
Qcache_lowmem_prunes	0
Qcache_not_cached	135148
Qcache_queries_in_cache	0
Qcache_total_blocks	1

状态值解释：

Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。

我们再查询一下服务器关于query_cache的配置：

MySQL> show variables like 'query_cache%';

query_cache_limit	1048576
query_cache_min_res_unit	4096
query_cache_size	1048576
query_cache_type	OFF
query_cache_wlock_invalidate	OFF

各字段的解释：

query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在querycache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

③ 关于排序

show global status like 'sort%';

Sort_merge_passes	0
Sort_range	119
Sort_rows	8366
Sort_scan	1210

Sort_merge_passes 包括两步。

MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量sort_buffer_size 决定。

如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。

实际上，MySQL会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes增加的数值是建临时文件数的两倍。

因为用到了临时文件，所以速度可能会比较慢，增加Sort_buffer_size 会减少Sort_merge_passes 和创建临时文件的次数。

但盲目的增加Sort_buffer_size 并不一定能提高速度，另外，增加read_rnd_buffer_size的值对排序的操作也有一点的好处。

④ 关于打开文件数

# 查看限制
show variables like 'open_files_limit'

# 查看当前值
show global status like 'open_files%';

比较合适的设置：Open_files / open_files_limit * 100% <= 75％

⑤ 关于表锁

show global status like 'table_locks%';

Table_locks_immediate	364
Table_locks_waited	0

Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数。

如果Table_locks_immediate / Table_locks_waited >5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。

在MySQL5.6+版本，在my.cnf配置文件里，加入如下配置就可以把死锁信息打印到错误日志里

innodb_print_all_deadlocks = 1

⑥ 关于表扫描情况

show global status like 'handler_read%';

Handler_read_first	24357
Handler_read_key	37614
Handler_read_last	1375
Handler_read_next	64846
Handler_read_prev	17505
Handler_read_rnd	8268
Handler_read_rnd_next	5513400

服务器完成的查询请求次数：

show global status like 'com_select%';

计算表扫描率：

表扫描率 ＝ Handler_read_rnd_next / Com_select

如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。

【6】MySQL服务器调优参数配置参考

下面给出一份MySQL服务器的配置文件参考。

[mysqld]
port=3306
serverid=1
socket=/tmp/mysql.sock
skip-locking
#避免MySQL的外部锁定，减少出错几率增强稳定性
skip-name-resolve
#禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的诗句。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
back_log=384
key_buffer_size=256M
max_allowed_packet=4M
thread_stack=256K
table_cache=128K
sort_buffer_size=6M
read_buffer_size=4M
read_rnd_buffer_size=16M
join_buffer_size=8M
myisam_sort_buffer_size=64M
thread_cache_size=64
query_cache_size=64M
tmp_table_size=256M
max_connections=768
max_connect_errors=10000000
wait_timeout=10
thread_concurrency=8
#该参数取值为服务器逻辑CPU数量*2
skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果web服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项。
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_thread_concurrency=8
#与服务器CPU数量保持一致