【MySQL】A01、性能优化-参数监控&分析

1、参数监控

1.1、MySQL command 查看

mysql>SHOW STATUS; （服务器状态变量，运行服务器的统计和状态指标）
mysql> SHOW VARIABLES;（服务器系统变量，实际上使用的变量的值）


mysql> SHOW STATUS LIKE "sort%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)
 
mysql>

如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size。当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。如果 sort_merge_passes 状态变量很大，这就指示了磁盘的活动情况。


mysql> show variables like 'read%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_only            | OFF    |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.01 sec)
 
mysql>

read_buffer_size是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描，可能需要增加该值，默认值为131072。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。

read_rnd_buffer_size是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。该参数对应的分配内存也是每连接独享。


mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)
 
mysql>

联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。


mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)
 
mysql> show variables like 'net_buffer_length';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
1 row in set (0.01 sec)
 
mysql>

包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节。该值默认很小，以捕获大的(可能是错误的)数据包。


mysql> show status LIKE 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 3     |
| Threads_connected | 4     |
| Threads_created   | 7     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
 
mysql>

线程缓存。mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。

此处重要的值是 Threads_created，每次 mysqld 需要创建一个新线程时，这个值都会增加。如果这个数字在连续执行 SHOW STATUS 命令时快速增加，就应该尝试增大线程缓存。


mysql> SHOW STATUS LIKE "com_select";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 14    |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_rnd_next | 35887 |
+-----------------------+-------+
1 row in set (0.01 sec)
 
mysql>

MySQL 也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT 语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。

Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过 4000，就应该查看 read_buffer_size，例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！


mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 44    |
+-------------------------+-------+
3 rows in set (0.00 sec)
 
mysql>

临时表可以在更高级的查询中使用，其中数据在进一步进行处理（例如 GROUP BY 字句）之前，都必须先保存到临时表中；理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。

每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。 tmp_table_size 和 max_heap_table_size 都可以控制临时表的最大大小，因此请确保在 my.cnf 中对这两个值都进行了设置。


mysql> show variables like 'innodb%';
+------------------------------------------+------------------------+
| Variable_name                            | Value                  |
+------------------------------------------+------------------------+
Innodb_buffer_pool_pages_data     2559       分配出去， 正在被使用页的数量，包括脏页。单位是page
 Innodb_buffer_pool_pages_dirty    0            脏页但没有被flush除去的页面数。单位是page
 Innodb_buffer_pool_pages_flushed  795       已经flush的页面数。单位是page
 Innodb_buffer_pool_pages_free     1473     当前空闲页面数。单位是page
 Innodb_buffer_pool_pages_misc     64         缓存池中当前已经被用作管理用途或hash index而不能用作为普通数据页的数目。单位是page
 Innodb_buffer_pool_pages_total    4096       缓冲区总共的页面数。单位是page
 Innodb_buffer_pool_read_ahead_rnd 8        随机预读的次数
 Innodb_buffer_pool_read_ahead_seq 1        顺序预读的次数
 Innodb_buffer_pool_read_requests  1725871    从缓冲池中读取页的次数
 Innodb_buffer_pool_reads          2108              从磁盘读取页的次数。缓冲池里面没有， 就会从磁盘读取
 Innodb_buffer_pool_wait_free      0                缓冲池等待空闲页的次数，当需要空闲块而系统中没有时，就会等待空闲页面
 Innodb_buffer_pool_write_requests 2296       缓冲池总共发出的写请求次数
 Innodb_data_fsyncs                695              总共完成的fsync次数
 Innodb_data_pending_fsyncs        0            innodb当前等待的fsync次数
 Innodb_data_pending_reads         0           innodb当前等待的读的次数
 Innodb_data_pending_writes        0            innodb当前等待的写的次数
 Innodb_data_read                  44044288          总共读入的字节数
 Innodb_data_reads                 2191              innodb完成的读的次数
 Innodb_data_writes                1296              innodb完成的写的次数
 Innodb_data_written               26440192     总共写出的字节数
 Innodb_dblwr_pages_written        795     
 Innodb_dblwr_writes               90      
 Innodb_log_waits                  0                   因日志缓存太小而必须等待其被写入所造成的等待数。单位是次。
 Innodb_log_write_requests         263     
 Innodb_log_writes                 410     
 Innodb_os_log_fsyncs              500     
 Innodb_os_log_pending_fsyncs      0       
 Innodb_os_log_pending_writes      0       
 Innodb_os_log_written             343552  
 Innodb_page_size                  16384   
 Innodb_pages_created              4       
 Innodb_pages_read                 2555    
 Innodb_pages_written              795     
 Innodb_row_lock_current_waits     0       
 Innodb_row_lock_time              0       
 Innodb_row_lock_time_avg          0       
 Innodb_row_lock_time_max          0       
 Innodb_row_lock_waits             0       
 Innodb_rows_deleted               0       
 Innodb_rows_inserted              352     
 Innodb_rows_read                  818617  
 Innodb_rows_updated               88          
------------------
 
mysql>

log-bin=mysql-bin
binlog_format=mixed
mysql-bin.000001、mysql-bin.000002等文件是数据库的操作日志，例如UPDATE一个表，或者DELETE一些数据，即使该语句没有匹配的数据，这个命令也会存储到日志文件中，还包括每个语句执行的时间，也会记录进去的。

innodb_buffer_pool_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。为Innodb加速优化首要参数。默认值8M

这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据量不大，并且不会暴增，那么可分配是你的数据大小＋１０％左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M

命中率=innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_read_ahead + innodb_buffer_pool_reads)

innodb_buffer_pool_size: 如果不使用InnoDB存储引擎，可以不用调整这个参数，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样将但来说也是“越大越好”。

innodb_additional_pool_size，这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。20M足够了。

innodb_log_file_size：

作用：指定日志的大小。
分配原则：几个日志成员大小加起来差不多和你的innodb_buffer_pool_size相等。在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。
说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。

innodb_log_buffer_size：

作用：事务在内存中的缓冲。
分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。一般最大指定为3M比较合适。
参考：Innodb_os_log_written(show global status 可以拿到)

如果这个值增长过快，可以适当的增加innodb_log_buffer_size，另外如果你需要处理大理的text，或是blog字段，可以考虑增加这个参数的值。

默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

innodb_flush_logs_at_trx_commit：

作用：控制事务的提交方式
分配原则：这个参数只有3个值，0，1，2请确认一下自已能接受的级别。默认为1，主库请不要更改了。性能更高的可以设置为0或是2，但会丢失一秒钟的事务。
- 值为1时：innodb 的事务LOG在每次提交后写入日志文件，并对日志做刷新到磁盘。这个可以做到不丢任何一个事务。
- 值为2事，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了，不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 只会丢失刷新到操作系统缓存的那部分事务。


mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)
 
mysql>

作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。
分配原则：只有使用不使用。但DB还需要有一个公共的表空间。

InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。

innodb_open_files：

作用：限制Innodb能打开的表的数据。
分配原则：如果库里的表特别多的情况，请增加这个。这个值默认是300。这个值必须超过你配置的innodb_data_file_path个数。请适当的增加table_cache

innodb_flush_method：

作用：Innodb和系统打交道的一个IO模型
分配原则：Windows不用设置。UNIX可以设置：fdatasync (默认设置)，O_DIRECT，和O_DSYNC，O_DIRECT跳过了操作系统的文件系统Disk Cache，让MySQL直接读写磁盘。有数据表明，如果是大量随机写入操作，O_DIRECT会提升效率。但是顺序写入和读取效率都会降低。

innodb_max_dirty_pages_pct：

作用：控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.

O_DIRECT的flush_method更适合于操作系统内存有限的情况下（可以避免不必要的对交换空间的读写操作），否则，它会由于禁用了os的缓冲降低对数据的读写操作的效能。

1.2、mysqladmin 查看

用法：mysqladmin -uroot -ppassword variables status/extended-status


[root@192 src]# mysqladmin  -uroot -proot@123  variables extended-status
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                            | Value                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_generate_certs                                      | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| auto_increment_increment                                 | 1                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| auto_increment_offset                                    | 1                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| autocommit                                               | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| automatic_sp_privileges                                  | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| avoid_temporal_upgrade                                   | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| back_log                                                 | 80                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| basedir                                                  | /usr/                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| big_tables                                               | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| bind_address                                             | *                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| binlog_cache_size                                        | 32768                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| binlog_checksum                                          | CRC32                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| binlog_direct_non_transactional_updates                  | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| binlog_error_action                                      | ABORT_SERVER                                                                                                                                                                                                                                                                                                                                                                                                                              |
| binlog_format                                            | ROW                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| binlog_group_commit_sync_delay                           | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| binlog_group_commit_sync_no_delay_count                  | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| binlog_gtid_simple_recovery                              | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| binlog_max_flush_queue_time                              | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| binlog_order_commits                                     | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| binlog_row_image                                         | FULL                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| binlog_rows_query_log_events                             | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| binlog_stmt_cache_size                                   | 32768                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| binlog_transaction_dependency_history_size               | 25000                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| binlog_transaction_dependency_tracking                   | COMMIT_ORDER                                                                                                                                                                                                                                                                                                                                                                                                                              |
| block_encryption_mode                                    | aes-128-ecb                                                                                                                                                                                                                                                                                                                                                                                                                               |
| bulk_insert_buffer_size                                  | 8388608                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| character_set_client                                     | latin1                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| character_set_connection                                 | latin1                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| character_set_database                                   | latin1                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| character_set_filesystem                                 | binary                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| character_set_results                                    | latin1                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| character_set_server                                     | latin1                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| character_set_system                                     | utf8                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| character_sets_dir                                       | /usr/share/mysql/charsets/                                                                                                                                                                                                                                                                                                                                                                                                                |
| check_proxy_users                                        | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| collation_connection                                     | latin1_swedish_ci                                                                                                                                                                                                                                                                                                                                                                                                                         |
| collation_database                                       | latin1_swedish_ci                                                                                                                                                                                                                                                                                                                                                                                                                         |
| collation_server                                         | latin1_swedish_ci                                                                                                                                                                                                                                                                                                                                                                                                                         |
| completion_type                                          | NO_CHAIN                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| concurrent_insert                                        | AUTO                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| connect_timeout                                          | 10                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| core_file                                                | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| datadir                                                  | /var/lib/mysql/                                                                                                                                                                                                                                                                                                                                                                                                                           |
| date_format                                              | %Y-%m-%d                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| datetime_format                                          | %Y-%m-%d %H:%i:%s                                                                                                                                                                                                                                                                                                                                                                                                                         |
| default_authentication_plugin                            | mysql_native_password                                                                                                                                                                                                                                                                                                                                                                                                                     |
| default_password_lifetime                                | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| default_storage_engine                                   | InnoDB                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| default_tmp_storage_engine                               | InnoDB                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| default_week_format                                      | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| delay_key_write                                          | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| delayed_insert_limit                                     | 100                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| delayed_insert_timeout                                   | 300                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| delayed_queue_size                                       | 1000                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| disabled_storage_engines                                 |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| disconnect_on_expired_password                           | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| div_precision_increment                                  | 4                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| end_markers_in_json                                      | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| enforce_gtid_consistency                                 | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| eq_range_index_dive_limit                                | 200                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| event_scheduler                                          | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| expire_logs_days                                         | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| explicit_defaults_for_timestamp                          | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| flush                                                    | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| flush_time                                               | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| foreign_key_checks                                       | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| ft_boolean_syntax                                        | + -><()~*:""&|                                                                                                                                                                                                                                                                                                                                                                                                                            |
| ft_max_word_len                                          | 84                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| ft_min_word_len                                          | 4                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| ft_query_expansion_limit                                 | 20                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| ft_stopword_file                                         | (built-in)                                                                                                                                                                                                                                                                                                                                                                                                                                |
| general_log                                              | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| general_log_file                                         | /var/lib/mysql/192.log                                                                                                                                                                                                                                                                                                                                                                                                                    |
| group_concat_max_len                                     | 1024                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| gtid_executed                                            |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| gtid_executed_compression_period                         | 1000                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| gtid_mode                                                | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| gtid_owned                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| gtid_purged                                              |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| have_compress                                            | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_crypt                                               | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_dynamic_loading                                     | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_geometry                                            | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_openssl                                             | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_profiling                                           | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_query_cache                                         | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_rtree_keys                                          | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_ssl                                                 | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_statement_timeout                                   | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_symlink                                             | DISABLED                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| host_cache_size                                          | 279                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| hostname                                                 | 192.168.56.108                                                                                                                                                                                                                                                                                                                                                                                                                            |
| ignore_builtin_innodb                                    | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| ignore_db_dirs                                           |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| init_connect                                             |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| init_file                                                |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| init_slave                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| innodb_adaptive_flushing                                 | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
 
………………………………
                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Threads_running                               | 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Uptime                                        | 169478                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| Uptime_since_flush_status                     | 169478                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| validate_password_dictionary_file_last_parsed | 2024-04-22 17:36:02                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| validate_password_dictionary_file_words_count | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@192 src]#

注：value 值的单位是byte ，要得到M ，需除以2次1024

auto_increment_increment 与 auto_increment_offset 两个变量值都只能为1到65,535之间的整数值。设置为非整数值，则会给出错误。这两个变量影响AUTO_INCREMENT列。

auto_increment_increment控制列中的值的增量值（步进量）。
auto_increment_offset确定AUTO_INCREMENT列值的初始值。

一般不去更改。更改方法：mysql> SET @auto_increment_offset=5;


-----------------------------------------------------------------------------------------------------
max_connections                    100
table_cache                             64
open_files_limit                       1024 
Open_tables                           64
Opened_tables                       187690

几个参数的关系：table_cache * 2 + max_connections＝max_open_files，max_connections 默认为100

表缓存的说明：

当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还没有被缓存，但是在 Mysql 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。
每个连接进来，都会至少打开一个表缓存。因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是网站程序一次查询所用到的表的最大值。

每个线程会独自持有一个数据文件的文件描述符，而索引文件的文件描述符是公用的。当table cache不够用的时候，MySQL会采用LRU算法踢掉最长时间没有使用的表。如果table_cache设置过小，MySQL就会反复打开、关闭 frm文件，造成一定的性能损失。如果table_cache设置过大，MySQL将会消耗很多CPU去做 table cache的算法运算。

而InnoDB的元数据管理是放在共享表空间里面做的，所以获取表的结构不需要去反复解析frm文件，这是比MyISAM强的地方。即使 table_cache设置过小，对于InnoDB的影响也是很小的，因为它根本不需要反复打开、关闭frm文件去获取元数据。合理设置table_cache的大小：通过查看open_tables，Opened_tables，Flush tables 的值来比较。


shell > mysqladmin  -uroot -ppassword  variables status
----------------------------------
Opens: 221872   则是已经打开的表的数量。
Flush tables: 1 
Open tables: 64   是当前打开的表的数量
----------------------------------
mysql> show global status like 'open%_tables';
----------------------------------
open_tables 是当前打开的表的数量，
Opened_tables 表示打开过的表数量
----------------------------------

清空表缓存

mysql> flush tables;

如果发现 open_tables 接近 table_cache 的时候，如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加，就说明缓存命中率不够。并且多次执行FLUSH TABLES(通过shell > mysqladmin -uroot -ppassword variables status )，那就说明可能 table_cache 设置的偏小，经常需要将缓存的表清出，将新的表放入缓存，这时可以考虑增加这个参数的大小来改善访问的效率。

如果 Open_tables 比 table_cache 设置小很多，就说明table_cache 设的太大了。table_cache的值在2G内存以下的机器中的值默认时256到512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，因为table_cache加大后，使得mysql对SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。

注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。对于有1G内存的机器，推荐值是128－256。


--------------------------------------------------------------------
key_buffer_size                         67108864（/1024/1024=64M）
Key_read_requests                   40944  从缓存读键的数据块的请求数。
Key_reads                                 2711     从硬盘读取键的数据块的次数。
Key_write_requests    将键的数据块写入缓存的请求数。
Key_writes       向硬盘写入将键的数据块的物理写操作的次数。
 
#获得信息：
#shell > mysqladmin  -uroot -ppassword  variables extended-status
#shell>mysqladmin -uroot -ppassword  variable status
#mysql> show status like '%key_read%';

key_buffer_size设置索引块（index blocks）缓存的大小，保存了 MyISAM 表的索引块。它被所有线程共享，决定了数据库索引处理的速度，尤其是索引读的速度。理想情况下，对于这些块的请求应该来自于内存，而不是来自于磁盘。
只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。

key_buffer_size: 如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”。合理设置key_buffer_size的方法：

查看Key_read_requests和Key_reads的比例，Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数。命中磁盘的读请求数除以读请求总数就是不中比率。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。

key_reads / key_read_requests的值应该尽可能的低，比如1:100，1:1000 ，1:10000。


myisam_sort_buffer_size                 8388608
#当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配之缓冲区。
 
sort_buffer_size                         2097144
#每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作。

注意：该参数对应的分配内存是每个连接独享，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。

1.3、shell 查看

shell > /usr/sbin/mysqld --verbose --help （这个命令生成所有mysqld选项和可配置变量的列表）


[root@192 src]# /usr/sbin/mysqld --verbose --help
/usr/sbin/mysqld  Ver 5.7.44-log for Linux on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Starts the MySQL database server.
 
Usage: /usr/sbin/mysqld [OPTIONS]
 
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-5.7
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.
  --abort-slave-event-count=# 
                      Option used by mysql-test for debugging and testing of
                      replication.
  --allow-suspicious-udfs 
                      Allows use of UDFs consisting of only one symbol xxx()

2、参数分析

2.1、慢查询


mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------+
| Variable_name             | Value                                   |
+---------------------------+-----------------------------------------+
| log_slow_admin_statements | OFF                                     |
| log_slow_slave_statements | OFF                                     |
| slow_launch_time          | 2                                       |
| slow_query_log            | ON                                      |
| slow_query_log_file       | /usr/share/mysql/sql_log/mysql-slow.log |
+---------------------------+-----------------------------------------+
5 rows in set (0.01 sec)
 
mysql> show global status like '%slow%'; 
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 40    |
+---------------------+-------+
2 rows in set (0.02 sec)
 
mysql>

配置中打开了记录慢查询，执行时间超过 2秒的即为慢查询，系统显示有 40 个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁，记得找对应的版本。

打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。

2.2、连接数

经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：


mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)
 
mysql>

这台MySQL服务器最大连接数是 151，然后查询一下服务器响应的最大连接数：


mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 7     |
+----------------------+-------+
1 row in set (0.00 sec)
 
mysql>

MySQL服务器过去的最大连接数是 7，没有达到服务器连接数上限 151，应该没有出现1040错误，比较理想的设置是：

Max_used_connections / max_connections * 100% ≈ 85%

最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

2.3、Key_buffer_size

key_buffer_size是对 MyISAM 表性能影响最大的一个参数，下面一台以 MyISAM 为主要存储引擎服务器的配置：


mysql> show variables like 'key_buffer_size'; 
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.01 sec)
 
mysql>

分配了 8MB内存给key_buffer_size，我们再看一下key_buffer_size的使用情况：


mysql> show global status like 'key_read%'; 
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 200   |
| Key_reads         | 5     |
+-------------------+-------+
2 rows in set (0.00 sec)
 
mysql>

一共有 200 个索引读取请求，有 5 个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

key_cache_miss_rate = Key_reads / Key_read_requests * 100%

比如上面的数据，key_cache_miss_rate为 2.5%，40 个索引读取请求才有一个直接读硬盘，效率一般。key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘)，如果key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。

MySQL服务器还提供了key_blocks_*参数：


mysql> show global status like 'key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 6693  |
| Key_blocks_used   | 5     |
+-------------------+-------+
2 rows in set (0.00 sec)
 
mysql>

Key_blocks_unused 表示未使用的缓存簇(blocks)数，Key_blocks_used 表示曾经用到的最大的blocks数。比较理想的设置：

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

2.4、临时表


mysql> show global status like 'created_tmp%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 105   |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 2451  |
+-------------------------+-------+
3 rows in set (0.01 sec)
 
mysql>

每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% = 4.2%，应该相还可以。我们再看一下MySQL服务器对临时表的配置：


mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
2 rows in set (0.01 sec)
 
mysql>

只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。

2.5、Open Table情况


mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1656  |
| Opened_tables | 2081  |
+---------------+-------+
2 rows in set (0.00 sec)
 
mysql>

Open_tables表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小，我们查询一下服务器table_cache值：


mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+
1 row in set (0.00 sec)
 
mysql>


#比较合适的值为： 
Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

2.6、进程使用情况


mysql> show global status like 'Thread%'; 
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 3     |
| Threads_connected | 4     |
| Threads_created   | 7     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
 
mysql>

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

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


mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.00 sec)
 
mysql>

2.7、查询缓存(query cache)


mysql> show global status like 'qcache%'; 
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 197     |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)
 
mysql>

MySQL查询缓存变量解释：

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：缓存中块的数量。


mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.00 sec)
 
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.44-log |
+------------+
1 row in set (0.00 sec)
 
mysql>

各字段的解释：

query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小
query_cache_size：查询缓存大小
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回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%

2.8、排序使用情况


mysql> show global status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 495   |
| Sort_scan         | 27    |
+-------------------+-------+
4 rows in set (0.00 sec)
 
mysql>

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 并不一定能提高速度，见 How fast can you sort data with MySQL?

另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处，参见

2.9、文件打开数(open_files)


mysql> show global status like 'open_files'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 78    |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> 
mysql> show variables like 'open_files_limit'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 5000  |
+------------------+-------+
1 row in set (0.00 sec)
 
mysql>

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

2.10、表锁情况


mysql> show global status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 247   |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)
 
mysql>

Table_locks_immediate 表示立即释放表锁数，Table_locks_waited 表示需要等待的表锁数，如果 Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为 InnoDB是行锁而 MyISAM 是表锁，对于高并发写入的应用 InnoDB 效果会好些。

2.11、表扫描情况


mysql> show global status like 'handler_read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 33     |
| Handler_read_key      | 3445   |
| Handler_read_last     | 2      |
| Handler_read_next     | 7899   |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 512    |
| Handler_read_rnd_next | 199218 |
+-----------------------+--------+
7 rows in set (0.00 sec)
 
mysql>

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


mysql> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 201   |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

计算表扫描率：

表扫描率 = Handler_read_rnd_next / Com_select

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

相关阅读:
【CompletableFuture 总结】
QT的TCP连接功能概述
 计算机网络常见端口号
 【广州华锐互动】人体血管器官3D动态展示为医学生提供哪些便利？
django 任务管理-crontab
高等数学（第七版）同济大学习题8-1 个人解答
 c高级day5（9.12）宏和Makefile
2022年10月31日-11月6日（ue5 pak收尾）
基于Java毕业设计二手手机回收平台系统源码+系统+mysql+lw文档+部署软件
 webp格式如何转成png？
原文地址：https://blog.csdn.net/qq_37165235/article/details/138160939