MySQL 性能优化实战，MySQL 性能调优和系统资源优化解决方案（二）

柔光的暖阳◎ 2023-05-21 08:54 128阅读 0赞

**目录**

前言

服务器优化

八、服务器优化

1、优化服务器硬件

2、优化 MySQL的参数

九、MySQL 中的配置参数详解

1）、MySQL 的基础配置参数

2）、MySQL 查询缓存配置参数

3）、MySQL 中 InnoDB 存储引擎的配置参数

4）、MySQL 中 MyISAM 存储引擎的配置参数

5）、MySQL 日志配置参数

6）、MySQL 连接通信参数配置

7）、MySQL 的Buffer缓冲区参数设置

8）、MySQL 性能优化的其它配置参数

十、MySQL 数据库运行状态检查

1）、常用的状态监控参数

2）、其它补充参数

--------------------

> ## 前言 ##

在上第一部分文章中[《 MySQL 性能优化实战，MySQL 性能调优和系统资源优化解决方案（一） 》][MySQL _MySQL _]完成了MySQL数据库的结构优化、性能调优，接下来这部分主讲系统资源调优、通过MySQL的系统配置参数实现。

> ## 服务器优化 ##

## 八、服务器优化 ##

### 1、优化服务器硬件 ###

**服务器的硬件性能直接决定着 MySQL数据库的性能，硬件的性能瓶颈，直接决定 MySQL数据库的运行速度和效率。**

**硬件服务器的优化需要从以下几个方面考虑：**

1.  **配置较大的内存**，足够大的内存，是提高 MySQL数据库性能的方法之一。内存的 IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的 IO；
2.  **配置高速磁盘**，比如 SSD；
3.  **合理分配磁盘 IO**，把磁盘 IO分散到多个设备上，以减少资源的竞争，提高并行操作能力；
4.  **配置多核处理器**，MySQL 是多线程的数据库，多处理器可以提高同时执行多个线程的能力；

### 2、优化 MySQL的参数 ###

**通过优化 MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。**在 MySQL交互模式下的命令：“ **SHOW  VARIABLES** ”，如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlbGxvX1dvcmxkX1FXUA_size_16_color_FFFFFF_t_70][]

MySQL 的配置参数都在 my.cnf或者 my.ini文件的 \[mysqld\] 组中，配置示例如下：

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    
    [mysqld]
    lower_case_table_names = 1
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    #
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

## ** 九、MySQL 中的配置参数详解** ##

### **1）、MySQL 的基础配置参数** ###

<table> 
 <thead> 
 <tr> 
 <th style="width:244px;"> 参数名 </th> 
 <th style="width:660px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="width:244px;"> user </td> 
 <td style="width:660px;"> 用户名 </td> 
 </tr> 
 <tr> 
 <td style="width:244px;"> bind-address </td> 
 <td style="width:660px;"> 绑定IP地址 </td> 
 </tr> 
 <tr> 
 <td style="width:244px;"> port </td> 
 <td style="width:660px;"> 端口号 </td> 
 </tr> 
 <tr> 
 <td style="width:244px;"> datadir </td> 
 <td style="width:660px;"> 数据文件存放目录 </td> 
 </tr> 
 <tr> 
 <td style="width:244px;"> basedir </td> 
 <td style="width:660px;"> MySQL 应用程序存放目录 </td> 
 </tr> 
 <tr> 
 <td style="width:244px;"> socket </td> 
 <td style="width:660px;"> socket 文件存放目录文件 </td> 
 </tr> 
 <tr> 
 <td style="width:244px;"> default-table-type </td> 
 <td style="width:660px;"> 配置系统默认引擎 </td> 
 </tr> 
 </tbody> 
</table>

### **2）、MySQL 查询缓存配置参数** ###

<table> 
 <thead> 
 <tr> 
 <th style="width:246px;"> 参数名 </th> 
 <th style="width:658px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="width:246px;"> query_cache_size </td> 
 <td style="width:658px;"> 配置 MySQL的查询缓存大小， 缓存大小的直接因素： 1、对DDL和DML语句的性能影响； 2、内部维护成本； 3、命中率及内存使用率； 4、项目/产品的业务类型； </td> 
 </tr> 
 <tr> 
 <td style="width:246px;"> query_cache_type </td> 
 <td style="width:658px;"> 查询缓存类型。取值如下： 0 OFF，禁用查询缓存功能； 1 ON，启用缓存的功能，缓存所有符合要求的查询结果集，除SELECT SQL_NO_CACHE ...，以及不符合查询缓存设置的结果集外； 2 DEMAND，仅仅缓存SELECT SQL_CACHE ...子句的查询结果集，除不符合查询缓存设置的结果集外； </td> 
 </tr> 
 <tr> 
 <td style="width:246px;"> query_cache_limit </td> 
 <td style="width:658px;"> 查询结果长度的长度限制，小于这个长度的数据才能被缓存 </td> 
 </tr> 
 </tbody> 
</table>

### **3）、MySQL 中 InnoDB 存储引擎的配置参数** ###

InnoDB 参数较少，内存的管理多由 InnoDB引擎自己负责，主要配置参数为“innodb\_buffer\_pool\_size”，参数分配的缓冲。

<table> 
 <thead> 
 <tr> 
 <th style="vertical-align:top;width:246px;"> 参数名 </th> 
 <th style="vertical-align:top;width:658px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_buffer_pool_size </td> 
 <td style="vertical-align:top;width:658px;"> 新开辟一片内存用于缓存 InnoDB引擎表的数据和索引，一般设置为机器内存的50%-80%（关键参数） 该参数的最大值受限于服务器本身 CPU的架构，支持32位还是支持64位，另外还受限于操作系统32位还是64位； 合理设值 innodb_buffer_pool_size，有利于节约访问表对象中数据的物理IO。在官方手册上建议专用的数据库服务器，考虑设置为物理内存总量的80%。除了物理服务器的物理内存总量外，还需要考虑整个数据库中是否只使用 InnoDB引擎、mysqld内部管理占用的内存、最大线程连接数和临时表等额外的因素，官方提供的 80%仅作为一个参考。 在以下四种情况中，需要考虑降低 innodb_buffer_pool_size的设值： 1、出现物理内存的竞争，可能导致操作系统的分页； 2、InnoDB预分配额外的内存给缓冲区和结构管理，当分配的总内存量超过 innodb_buffer_pool_size值的10%； 3、地址内存空间要求必须为连续的，在windows系统有一个严重问题，DLL需要加载在特定的地址空间内； 4、初始化缓冲区的时间消耗，与缓冲区的大小成正比； </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_log_buffer_size </td> 
 <td style="vertical-align:top;width:658px;"> InnoDB 日志缓冲区大小 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_flush_method </td> 
 <td style="vertical-align:top;width:658px;"> 刷新日志的方法 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_additional_mem_pool_size </td> 
 <td style="vertical-align:top;width:658px;"> 开辟一片内存用于缓存 InnoDB引擎的数据字典信息和内部数据结构（比如：自适应HASH索引结构），默认值：build-in版本默认值为：1M；Plugin-innodb版本默认值为：8M； 注：若是 mysqld服务上的表对象数量较多，InnoDB引擎数据量很大，且 innodb_buffer_pool_size的值设置较大，则应该适当地调整 innodb_additional_mem_pool_size的值。若是出现缓存区的内存不足，则会直接向操作系统申请内存分配，并且会向 MySQL的 error日志文件写入警告信息； </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_data_home_dir </td> 
 <td style="vertical-align:top;width:658px;"> InnoDB 数据文件的目录 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_data_file_path </td> 
 <td style="vertical-align:top;width:658px;"> 数据文件配置 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_log_files_in_group </td> 
 <td style="vertical-align:top;width:658px;"> InnoDB 日志的 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_log_file_size </td> 
 <td style="vertical-align:top;width:658px;"> 指定 InnoDB 日志文件的大小 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_lock_wait_timeout </td> 
 <td style="vertical-align:top;width:658px;"> 等待锁的超时时间，避免死锁 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_flush_log_at_trx_commit </td> 
 <td style="vertical-align:top;width:658px;"> 日志提交方式 （关键参数）： 0，每秒写1次日志，将数据刷入磁盘，相当于每秒提交一次事务； 1，每次提交事务写日志，同时刷新相应磁盘 -&gt; 默认参数； 2，每提交事务写一次日志，但每隔一秒刷新一次相应的磁盘文件； </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:246px;"> innodb_force_recovery </td> 
 <td style="vertical-align:top;width:658px;"> 在 InnoDB 的自动恢复失败后，从崩溃中强制启动，有1-6个级别，数值越低恢复的方式也保守，默认为4。尽量使用较保守方式恢复。恢复后要注释删除这一行。 </td> 
 </tr> 
 </tbody> 
</table>

### **4）、MySQL 中 MyISAM 存储引擎的配置参数** ###

### **5）、MySQL 日志配置参数** ###

MySQL中日志是监控数据库系统的重要途径。MySQL的日志分为6种：查询日志、慢查询日志、变更日志、二进制变更日志、告警日志、错误日志。

<table> 
 <thead> 
 <tr> 
 <th style="vertical-align:top;width:248px;"> 参数名 </th> 
 <th style="vertical-align:top;width:656px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> log </td> 
 <td style="vertical-align:top;width:656px;"> 查询日志，记录所有的 MySQL的命令操作，在跟踪数据库运行时非常有帮助 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> log-slow-queries </td> 
 <td style="vertical-align:top;width:656px;"> 慢查询日志 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> log-update </td> 
 <td style="vertical-align:top;width:656px;"> 变更日志，用文本方式记录所有改变数据的变更操作 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> log-bin </td> 
 <td style="vertical-align:top;width:656px;"> 二进制变更日志，更加紧凑，使用mysqlbinlog读取，操作，转换 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> log-warnings </td> 
 <td style="vertical-align:top;width:656px;"> 告警日志 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> log-error </td> 
 <td style="vertical-align:top;width:656px;"> 错误日志 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> binlog_cache_size </td> 
 <td style="vertical-align:top;width:656px;"> 临时存放某次事务的 SQL语句缓冲长度 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> max_binlog_cache_szie </td> 
 <td style="vertical-align:top;width:656px;"> 最大的二进制缓存日志缓冲区大小 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> max_binlog_size </td> 
 <td style="vertical-align:top;width:656px;"> 最大的二进制日志大小 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> long_query_time </td> 
 <td style="vertical-align:top;width:656px;"> 慢查询时间限度，超过这个限度，MySQL 将认为是一个慢查询，并记录到慢查询日志中 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:248px;"> log-queries-not-using-indexes </td> 
 <td style="vertical-align:top;width:656px;"> 没有使用索引查询的日志，方便记录长时间访问的查询进行优化 </td> 
 </tr> 
 </tbody> 
</table>

### **6）、MySQL 连接通信参数配置** ###

<table> 
 <thead> 
 <tr> 
 <th style="vertical-align:top;width:249px;"> 参数名 </th> 
 <th style="vertical-align:top;width:655px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> max_connections </td> 
 <td style="vertical-align:top;width:655px;"> MySQL 的最大连接数，该值一般情况下都会设值在128-1024的范围，再结合实际业务可能的最大事务并发度。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> max_connect_errors </td> 
 <td style="vertical-align:top;width:655px;"> 同一个连接地址最大错误连接数，防止恶意攻击，默认值为10，也即 mysqld线程没重新启动过，一台物理服务器只要连接异常中断累计超过10次，就再也无法连接上 mysqld服务；若异常中断累计超过参数设置的值，有两种解决方案，执行命令：FLUSH HOSTS 或者重新启动 mysqld服务。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> net_buffer_length </td> 
 <td style="vertical-align:top;width:655px;"> 服务器和客户端之间通讯，使用的缓冲区长度，单位：字节 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> max_allowed_packet </td> 
 <td style="vertical-align:top;width:655px;"> 服务器和客户端之间最大的通信缓冲区长度，单位：字节 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> net_read_timeout </td> 
 <td style="vertical-align:top;width:655px;"> 读取超时 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> net_write_timeout </td> 
 <td style="vertical-align:top;width:655px;"> 写入超时 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> interactive_timeout </td> 
 <td style="vertical-align:top;width:655px;"> 交互模式下，没有操作后的超时时间，单位：秒 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> wait_ timeout </td> 
 <td style="vertical-align:top;width:655px;"> 非交互模式，没有操作后的超时时间，此参数只对基于 TCP/IP或基于 Socket通信协议建立的连接才有效，单位：秒 </td> 
 </tr> 
 </tbody> 
</table>

### **7）、MySQL 的Buffer缓冲区参数设置** ###

在前面一篇文章MySQL的MyCat中间件[《 Mycat中的两个主要Buffer池，BufferPool的二级缓存 》][Mycat_Buffer_BufferPool_]中介绍到了Buffer 池的概念，以及其设值的规则，和这儿的 Buffer 类似，但又不同。

### **8）、MySQL 性能优化的其它配置参数** ###

关闭某些系统项，可以加快 MySQL的运行速度

<table> 
 <thead> 
 <tr> 
 <th style="vertical-align:top;width:247px;"> 参数名 </th> 
 <th style="vertical-align:top;width:657px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:247px;"> skip-openssl </td> 
 <td style="vertical-align:top;width:657px;"> 关闭 MySQL服务器对SSL加密的支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:247px;"> skip-isam </td> 
 <td style="vertical-align:top;width:657px;"> 关闭 MySQL服务器对 ISAM引擎的支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:247px;"> skip-bdb </td> 
 <td style="vertical-align:top;width:657px;"> 关闭 MySQL服务器对 BDB引擎的支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:247px;"> skip-external-locking </td> 
 <td style="vertical-align:top;width:657px;"> 关闭外部锁，MySQL的外部锁用于防止其他程序修改正在使用的数据文件，但在部分系统上不可靠，一般都不使用。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:247px;"> skip-innodb </td> 
 <td style="vertical-align:top;width:657px;"> 关闭 MySQL服务器对 InnoDB引擎的支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:247px;"> skip_networking </td> 
 <td style="vertical-align:top;width:657px;"> 关闭 MySQL服务器的远程访问，只能从本地访问数据库 </td> 
 </tr> 
 </tbody> 
</table>

MySQL 磁盘写操作的优化参数：

delay\_key\_write、flush、flush\_time配置参数，可以进一步提高 MyISAM引擎的性能，但在服务器宕机的时候，可能会导致业务数据丢失或数据表损坏的情况。

delay\_insert\_limit、delay\_insert\_timeout配置参数，在 MySQL中对于插入语句控制，如果有配置此参数，MySQL 会将新进的插入语句放入到一个队列中，而不是不马上读入到磁盘。

delay\_key\_write配置参数，在 MySQL创建表的时候，如果表的主键发生改动，改动会缓冲在 key\_buffer中，而不是立即回写到磁盘。

<table> 
 <thead> 
 <tr> 
 <th style="vertical-align:top;width:249px;"> 参数名 </th> 
 <th style="vertical-align:top;width:655px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> delay_insert_limit </td> 
 <td style="vertical-align:top;width:655px;"> INSERT DELAYED 语句选项。（插入语句的描述） 处理INSERT DELAYED语句，MYSQL插入delay_insert_limit条语句后检查是否有查询语句，如有有去查询，如果没有，则继续插入 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> delay_insert_timeout </td> 
 <td style="vertical-align:top;width:655px;"> 在处理完INSERT DELAYED对列的插入数据后，MYSQL等待delay_insert_timeout秒后看看是否有INSERT DELAYED数据，如果有继续，如果没有结束这次操作。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> delay_query_size </td> 
 <td style="vertical-align:top;width:655px;"> INSERT DELAYED插入数据对列的长度 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> max_delayed_threads </td> 
 <td style="vertical-align:top;width:655px;"> 处理INSERT DELAYED语句的最大线程个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> delay_key_write </td> 
 <td style="vertical-align:top;width:655px;"> 对于使用DELAY_KEY_WRITE选项的创建的表，可以延缓键读写 0N&nbsp;&nbsp;不延缓所有的键写如操作 OFF延缓有DELAY_KEY_WRITE选项的标的键写入操作 ALL延缓所有的表 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> flush </td> 
 <td style="vertical-align:top;width:655px;"> 是否要在每个操作后立即刷新数据表 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:249px;"> flush_time </td> 
 <td style="vertical-align:top;width:655px;"> 每隔多少秒，对数据表进行一次刷新。关闭后打开。 </td> 
 </tr> 
 </tbody> 
</table>

## 十、MySQL 数据库运行状态检查 ##

通过命令 “ **SHOW STATUS** ” 进行查看，通过观察其状态来判断当前数据库中的表或者数据是否需要优化。例如，当前我们最常用的数据库引擎是InnoDB，观察其运行情况可入输入命令 “ **SHOW STATUS WHERE variable\_name LIKE '%INNODB%';** ”，如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlbGxvX1dvcmxkX1FXUA_size_16_color_FFFFFF_t_70 1][]

通过观察可以看到，MySQL数据库运行状态大多为类似于计数器的模式进行记录的，可以通过 “ **FLUSH STATUS** ” 命令将所有运行状态进行重置。

### 1）、常用的状态监控参数 ###

<table> 
 <thead> 
 <tr> 
 <th style="vertical-align:top;width:245px;"> 参数名 </th> 
 <th style="vertical-align:top;width:659px;"> 说明 </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Aborted_clients </td> 
 <td style="vertical-align:top;width:659px;"> 因客户没有正确关闭而丢弃的连接数量，没有正确关闭指没有调用mysql_close就退出，连接超时，数据传送中客户端退出 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Aborted_connects </td> 
 <td style="vertical-align:top;width:659px;"> 试图连接MySQL服务器但没有成功的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Bytes_received </td> 
 <td style="vertical-align:top;width:659px;"> 从客户收到的字节总数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Bytes_send </td> 
 <td style="vertical-align:top;width:659px;"> 发送给客户的字节总数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Com_XXX </td> 
 <td style="vertical-align:top;width:659px;"> 执行语句的计数器，比如Com_select变量记录了select语句的个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Connections </td> 
 <td style="vertical-align:top;width:659px;"> 试图连接MySQL服务器的尝试次数，（包括成功的和没有成功） </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Created_tmp_disk_tables </td> 
 <td style="vertical-align:top;width:659px;"> 使用磁盘创建临时表的次数，如果要创建的临时表的尺寸大于tmp_table_size，那么临时表将创建在磁盘上， </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Created_tmp_tables </td> 
 <td style="vertical-align:top;width:659px;"> 创建临时表的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Delayed_XXX </td> 
 <td style="vertical-align:top;width:659px;"> INSERT DELAYED语句的执行性能参数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_commit Handler_rollback </td> 
 <td style="vertical-align:top;width:659px;"> 事务提交或者回滚的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_delete </td> 
 <td style="vertical-align:top;width:659px;"> 对数据表删除一条记录的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_update </td> 
 <td style="vertical-align:top;width:659px;"> 对数据表修改一条记录的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_write </td> 
 <td style="vertical-align:top;width:659px;"> 对数据表插入一条记录的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_read_first </td> 
 <td style="vertical-align:top;width:659px;"> 读取索引中第一个索引项的个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_read_key </td> 
 <td style="vertical-align:top;width:659px;"> 根据索引直接读取一行数据的次数，这个数值高表示数据库有较好的检索能力。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_read_next </td> 
 <td style="vertical-align:top;width:659px;"> 根据索引读取下个数据行的请求次数.&nbsp;在一个索引的区间内进行查询( &gt; &lt; ,orderby 这类查询条件)会影响这个计数器。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_read_prev </td> 
 <td style="vertical-align:top;width:659px;"> 根据索引读取前个数据行的请求次数.用于一些反序查询。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_read_rnd </td> 
 <td style="vertical-align:top;width:659px;"> 通过一个固定位置(应该就是不通过索引)读取一个数据行的次数。这个数值很高表示你的很多查询操作的结果需要排序，可能这些查询操作不能适当使用索引而要检索整个表。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Handler_read_rnd_next </td> 
 <td style="vertical-align:top;width:659px;"> 请求从数据文件中读取下一个记录的次数.如果有很多全表的检索这个值将很高. 通常这表示数据表没有合适的索引。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> key_blocks_used </td> 
 <td style="vertical-align:top;width:659px;"> 索引缓冲区块中已经被使用的区块大小。Block的尺寸默认是1024字节，4.1.1后可以通过key_cache_block_size参数设置。可以根据key_buffer_size/(1024 or key_cache_block_size) 得到Block总数，然后知道key_buffer的利用率 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Key_read_requests </td> 
 <td style="vertical-align:top;width:659px;"> 从缓冲读取1个Block的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Key_read </td> 
 <td style="vertical-align:top;width:659px;"> 从磁盘读取的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Key_write_requests </td> 
 <td style="vertical-align:top;width:659px;"> 写入索引缓冲区写入一个Block的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Key_write </td> 
 <td style="vertical-align:top;width:659px;"> 写回磁盘的次数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Innodb_XXXX </td> 
 <td style="vertical-align:top;width:659px;"> InnoDB的状态技术器，不过只有MySQL 5.02的版本才支持这些计数器。这儿略过 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Opened_tables </td> 
 <td style="vertical-align:top;width:659px;"> 曾经打开过的数据表总数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Open_tables </td> 
 <td style="vertical-align:top;width:659px;"> 当前处于打开的表个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Open_files </td> 
 <td style="vertical-align:top;width:659px;"> 当前处于打开的文件个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Qcache_free_blocks </td> 
 <td style="vertical-align:top;width:659px;"> Qcache没有使用的内存块个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Qcache_free_memory </td> 
 <td style="vertical-align:top;width:659px;"> Qcache没有使用的内存尺寸 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Qcache_hits </td> 
 <td style="vertical-align:top;width:659px;"> 查询在Qcache中的命中次数，和Com_select比较，就可以知道Qache的大约命中率是多少。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Qcache_inserts </td> 
 <td style="vertical-align:top;width:659px;"> 加入Cache中的查询个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Qcache_lowmem_prunes </td> 
 <td style="vertical-align:top;width:659px;"> 由于Qcache不够用，造成替换出Qcache的查询个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Qcache_not_cached </td> 
 <td style="vertical-align:top;width:659px;"> 没有能Cache的查询个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Slow_queries </td> 
 <td style="vertical-align:top;width:659px;"> 慢查询的次数，如果一个查询的所用的时间大于long_query_time设置的时间，则计数加1 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Select_XXXX </td> 
 <td style="vertical-align:top;width:659px;"> 关联查询的一些状态计数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Table_locks_waited </td> 
 <td style="vertical-align:top;width:659px;"> 必须等待后才能完成表锁定的请求个数，如果这个数值和下面数值的比率过大，表示数据库的性能较低 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Table_locks_immediate </td> 
 <td style="vertical-align:top;width:659px;"> 无需等待，立即完成表锁定的请求个数。 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Thread_connected </td> 
 <td style="vertical-align:top;width:659px;"> 现在处在连接打开状态的线程个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Thread_cached </td> 
 <td style="vertical-align:top;width:659px;"> 现在在现场缓冲区的线程个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Thread_created </td> 
 <td style="vertical-align:top;width:659px;"> 到目前为止,创建的线程个数 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:245px;"> Thead_running </td> 
 <td style="vertical-align:top;width:659px;"> 现在运行的线程个数，不是所有打开的线程都在运行，有些会处于SLEEP状态 </td> 
 </tr> 
 </tbody> 
</table>

### 2）、其它补充参数 ###

<table> 
 <thead> 
 <tr> 
 <th style="width:244px;">参数名</th> 
 <th>说明</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="width:244px;">sync_binlog</td> 
 <td> 日志提交方式（关键参数）： 大于0，每次向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上； 等于0，不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统来决定； 和 innodb_flush_log_at_trx_commit 配置使用（最佳推荐）： 1,1，适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统； 1,0，适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制； 2,0/2,y(0&lt;y&lt;100)，适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受； 0,0，磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受； 注：x,y，x表示innodb_flush_log_at_trx_commit，y表示sync_binlog </td> 
 </tr> 
 <tr> 
 <td style="width:244px;">innodb_file_per_table</td> 
 <td>启用单表空间，减少共享表空间维护成本，减少空闲磁盘空间释放的压力。另外，在数据量较大情况下的性能也会有提升，为此，一般建议使用独立表空间代替共享表空间的方式。</td> 
 </tr> 
 <tr> 
 <td style="width:244px;">innodb_commit_concurrency</td> 
 <td> 同一时刻，允许多少个线程同时提交InnoDB事务，默认值为0，范围0-1000。取值定义： 0，允许任意数量的事务在同一时间点提交； 大于0，允许指定个数事务在同一时间点提交； </td> 
 </tr> 
 <tr> 
 <td style="width:244px;">innodb_concurrency_tickets</td> 
 <td>同一时刻，能访问 InnoDB引擎数据的线程数，默认值为500，取值范围1-4294967295。如果，当访问 InnoDB引擎数据的线程数达到设值的上线，线程将会被放到队列中，等待其他线程释放 tickets后再进入，一般情况下保持默认即可够用了。</td> 
 </tr> 
 <tr> 
 <td style="width:244px;">innodb_fast_shutdown</td> 
 <td> InnoDB引擎关闭的方式，默认值为：1，表示正常关闭的，取值： 0，mysqld服务关闭前，先进行数据完全的清理和插入缓冲区的合并操作，若是脏数据较多或者服务器性能等因素，会导致此过程需要数分钟或者更长时间； 1，正常关闭mysqld服务，当 InnoDB引擎不做任何其他的操作； 2，若是 mysqld出现崩溃，立即刷事务日志到磁盘上并且冷关闭mysqld服务；没有提交的事务将会丢失，但是当再启动 mysqld服务的时候会进行事务回滚恢复； &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="width:244px;">innodb_force_recovery</td> 
 <td> mysqld服务出现崩溃之后，InnoDB引擎进行回滚的模式，默认值为0。只有在需要从错误状态的数据库进行数据备份时，才会设值 innodb_force_recovery，若是把此参数作为安全选项，也可以把参数的值设置大于0，防止 InnoDB引擎的数据变更，取值： 0，正常的关闭和启动，不会做任何强迫恢复操作； 1，跳过错误项目，让 mysqld服务继续运行。跳过错误索引记录和存储，尝试通过SELECT * INOT OUTFILE ‘../filename’ FROM tablename 方式，完成数据备份； 2，阻止 InnoDB的主线程运行。清理操作时出现 mysqld服务崩溃，则会阻止数据恢复操作； 3，恢复的时候，不进行事务回滚； 4，阻止 INSERT缓冲区的合并操作。不做合并操作，为防止出现 mysqld服务崩溃。不计算表的统计信息； 5，mysqld 服务启动的时候不检查回滚日志，InnoDB引擎对待每个不确定的事务就像提交的事务一样； 6，不做事务日志前滚恢复操作； &nbsp; 推荐组合配置： innodb_fast_shutdown = 1 mysqld服务关闭前，先进行数据完全的清理和插入缓冲区的合并操作，若是脏数据较多或者服务器性能等因素，会导致此过程需要数分钟或者更长时间,若是机房条件较好可设置为0（例如：双路电源、UPS、RAID卡电池和供电系统稳定性） innodb_force_recovery = 0 正常的关闭和启动，不会做任何强迫恢复操作 </td> 
 </tr> 
 <tr> 
 <td style="width:244px;">lower_case_table_names</td> 
 <td>在Linux或Unix中，对文件名称大小写敏感，也即对数据库、表、存储过程等对象名称大小写敏感，为减少开发人员的开发成本，推荐设值该参数，值为1，表示所有对象名称都统一的自动转换成小写（重要参数）</td> 
 </tr> 
 <tr> 
 <td style="width:244px;">transaction-isolation</td> 
 <td>事务隔离级别，隔离级别包括：read-uncommitted、read-committed、repeatable-read、serializable，默认值为：repeatable-read，有关事务隔离级别设值详情可参考MySQL事务隔离级别和日志登记模式的选择。</td> 
 </tr> 
 <tr> 
 <td style="width:244px;">binlog-format</td> 
 <td> 复制的模式，可供设置的值：statement、row、mixed（注：5.0.x 只有 statement，5.1.x 版本默认设置：mixed），组合配置推荐： 1、只读为主的业务应用场景 transaction-isolation = &nbsp;read-committed binlog-format &nbsp;= mixed 2、非只读的业务应用场景 transaction-isolation = repeatabled-read binlog-format &nbsp;= mixed </td> 
 </tr> 
 <tr> 
 <td style="width:244px;">event_scheduler</td> 
 <td>事务调度，事务调度默认是关闭状态，可以临时打开，命令：SET GLOBAL event_scheduler=1;</td> 
 </tr> 
 <tr> 
 <td style="width:244px;">skip_external_locking</td> 
 <td>外部锁，也即操作系统实施的锁，仅 MyISAM引擎有效，且容易造成死锁发生，所以一律禁用</td> 
 </tr> 
 <tr> 
 <td style="width:244px;">innodb_adaptive_hash_index</td> 
 <td>InnoDB 引擎会根据数据的访问频繁度，把表的数据逐渐缓到内存，若是一张表的数据大量缓存在内存中，则使用Hash Index（散列索引）会更高效。InnoDB 内有Hash Index机制，监控数据的访问情况，可以自动创建和维护一个Hash Index，以提高访问效率，减少内存的使用。</td> 
 </tr> 
 <tr> 
 <td style="width:244px;">innodb_max_dirty_pages_pct</td> 
 <td>InnoDB 主线程会直接更新 innodb_buffer_pool_size中存在的数据，并且不会实时刷回到磁盘，而是等待相关的事件发生，许缓存空间的数据量不实时刷回磁盘的最大百分比。比例设置较小，有利于 减少 mysqld服务出现问题的时候恢复时间，缺点则是需要更多的物理IO，所以，需要根据业务特点和可承受范围内进行一个折中。</td> 
 </tr> 
 </tbody> 
</table>

参考文档：

MySQL中文官网：【[MySQL 性能调优和优化资源][MySQL]】

--------------------

好了，关于 MySQL 性能优化实战，MySQL 性能调优和系统资源优化解决方案（二） 就写到这儿了，如果还有什么疑问或遇到什么问题欢迎扫码提问，也可以给我留言哦，我会一一详细的解答的。   
歇后语：“ 共同学习，共同进步 ”，也希望大家多多关注CSND的IT社区。

--------------------

<table> 
 <tbody> 
 <tr> 
 <td>作&nbsp; &nbsp; &nbsp; &nbsp;者：</td> 
 <td>华 &nbsp; &nbsp;仔</td> 
 </tr> 
 <tr> 
 <td>联系作者：</td> 
 <td>who.seek.me@java98k.vip</td> 
 </tr> 
 <tr> 
 <td>来&nbsp; &nbsp; &nbsp; &nbsp; 源：</td> 
 <td>CSDN (Chinese Software Developer Network)</td> 
 </tr> 
 <tr> 
 <td>原&nbsp; &nbsp; &nbsp; &nbsp; 文：</td> 
 <td><a href="https://blog.csdn.net/Hello_World_QWP/article/details/105731579" rel="nofollow">https://blog.csdn.net/Hello_World_QWP/article/details/105731579</a></td> 
 </tr> 
 <tr> 
 <td>版权声明：</td> 
 <td>本文为博主原创文章，请在转载时务必注明博文出处！</td> 
 </tr> 
 </tbody> 
</table>

[MySQL _MySQL _]: https://blog.csdn.net/Hello_World_QWP/article/details/104900695
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlbGxvX1dvcmxkX1FXUA_size_16_color_FFFFFF_t_70]: /images/20230521/638e2c3dbea3409597d9e051b39b7612.png
[Mycat_Buffer_BufferPool_]: https://blog.csdn.net/Hello_World_QWP/article/details/105050152
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hlbGxvX1dvcmxkX1FXUA_size_16_color_FFFFFF_t_70 1]: /images/20230521/63863bb32f694fb7b1766b3cbf35c675.png
[MySQL]: https://www.mysql.com/cn/why-mysql/performance/index.html