MySQL系统变量优化的方法是什么

54次阅读

共计 8130 个字符，预计需要花费 21 分钟才能阅读完成。

本篇内容介绍了“MySQL 系统变量优化的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1、全局内存缓冲区
1）key_buffer_size
该变量是只存储 MyISAM 索引信息的全局内存缓冲区。在对应的.MYI 文件中的索引数据从磁盘上被读取出来然后存入这个缓冲区。想要调整 key_buffer_size 的大小，只需要简单统计所有 MyISAM 表中总索引的大小，然后随着数据随时间增长而调整。
当这个索引码缓冲区中没有足够的空间来存储新的索引数据时，将会用最近最少使用的的方法覆盖掉旧的页面。
2）innodb_buffer_pool_size
innodb_buffer_pool_size 是用来存储所有 InnoDB 数据和索引的全局内存缓冲区。对完全使用 InnoDB 的数据库来说，这是个很重要的缓冲区，一定要正确分配，不正确的分配这个缓冲区可能导致额外的磁盘 IO 开销并降低查询性能。
常见的方法是把 innodb_buffer_pool_size 设定为 RAM 的 80%，但是很多情况下这样设定不合理，如 RAM 大小 50G，而数据库总量只有 2G。
可以使用 SHOW GLOBAL STATUS 或者 SHOW ENGINE INNODB STATUS 命令来监控 InnoDB 缓冲池的使用情况。
mysql SHOW GLOBAL STATUS LIKE innodb_buffer%
+—————————————+————————————————–+
| Variable_name | Value |
+—————————————+————————————————–+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 180330 16:27:30 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 51679 |
| Innodb_buffer_pool_bytes_data | 846708736 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 116888 |
| Innodb_buffer_pool_pages_free | 1024 |
| Innodb_buffer_pool_pages_misc | 4641 |
| Innodb_buffer_pool_pages_total | 57344 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 91375799 |
| Innodb_buffer_pool_reads | 380 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 41378543 |
+—————————————+————————————————–+
mysql SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-04-10 14:29:36 0x7f6777c37700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
—————–
BACKGROUND THREAD
—————–
srv_master_thread loops: 327 srv_active, 0 srv_shutdown, 942463 srv_idle
srv_master_thread log flush and writes: 942790
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 2095
OS WAIT ARRAY INFO: signal count 2039
RW-shared spins 0, rounds 469, OS waits 237
RW-excl spins 0, rounds 9654, OS waits 322
RW-sx spins 148, rounds 4422, OS waits 145
Spin rounds per wait: 469.00 RW-shared, 9654.00 RW-excl, 29.88 RW-sx
————
TRANSACTIONS
————
Trx id counter 10166
Purge done for trx s n:o 10166 undo n:o 0 state: running but idle
History list length 56
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 421558371456848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o s:, sync i/o s:
Pending flushes (fsync) log: 0; buffer pool: 0
894 OS file reads, 125124 OS file writes, 6256 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 232523, node heap has 0 buffer(s)
Hash table size 232523, node heap has 0 buffer(s)
Hash table size 232523, node heap has 0 buffer(s)
Hash table size 232523, node heap has 3093 buffer(s)
Hash table size 232523, node heap has 1 buffer(s)
Hash table size 232523, node heap has 1545 buffer(s)
Hash table size 232523, node heap has 1 buffer(s)
Hash table size 232523, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
—
LOG
—
Log sequence number 1258938797
Log flushed up to 1258938797
Pages flushed up to 1258938797
Last checkpoint at 1258938788
0 pending log flushes, 0 pending chkp writes
1286 log i/o s done, 0.00 log i/o s/second
———————-
BUFFER POOL AND MEMORY
———————-
Total large memory allocated 962002944
Dictionary memory allocated 320881
Buffer pool size 57344
Free buffers 1024
Database pages 51679
Old database pages 19056
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 335, not young 124
0.00 youngs/s, 0.00 non-youngs/s
Pages read 379, created 60479, written 118447
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 51679, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
————–
ROW OPERATIONS
————–
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=10779, Main thread ID=140081746663168, state: sleeping
Number of rows inserted 10501644, updated 2100327, deleted 0, read 21003388
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
—————————-
END OF INNODB MONITOR OUTPUT
============================
3）innodb_additional_mem_pool_size
innodb_additional_mem_pool_size 变量为 InnoDB 特定数据字典信息定义了内存池。对于这个变量，没有什么好的方法来确定它的最优值，一般将其设置为 10M。
4）query_cache_size
query_cache_size 变量是一个用来存储经常缓存过的查询全局内存缓冲区。使用 query_cache_type 变量可以总体启用和禁用查询缓存。启用时 query_cache_size 的值可能为 0，这表示没有查询需要被缓存。而 MySQL 实例可以通过动态的改变 query_cache_size 的值在某个时间仍然可以支持缓存。
2、全局 / 会话内存缓冲区
1）max_heap_table_size
这个变量定义了 MySQL MEMORY 存储引擎表的最大容量。当某个表容量超过最大值时，应用程序会收到下面的信息。
mysql set session max_heap_table_size=1024*1024;
Query OK, 0 rows affected (0.02 sec)
mysql create table test (id int(10),user_type varchar(20),code int(10)) engine=memory;
Query OK, 0 rows affected (0.02 sec)
mysql insert into test select * from t_user_info_tmp_bak;
ERROR 1114 (HY000): The table test is full
这个变量有一个全局默认值，而且在上例的每个线程上也可以指定这个变量的值。MySQL 并没有为所有 MEMORY 表的总容量做任何限制。这个变量仅用于单个表。
MEMORY 存储引擎表的总大小可以通过 SHOW TABLE STATUS 命令和 INFORMATION_SCHEMA.TABLES 表来确定。
2）tmp_table_size
max_heap_table_size 和 tmp_table_size 变量中的最小值定义了内部表的最大容量，内部临时表用于存储在内存中的查询执行过程。如果在 explain select 的结果中的 extra 列中出现了 using temporary，那么可以判断在查询执行过程中用到了内部临时表。
MySQL 使用 memory 存储引擎来支持这些内部临时表，但是内部临时表的容量超过 max_heap_table_size 和 tmp_table_size 中的最小值是，MySQL 会在临时位置创建一个基于 MyISAM 磁盘的表。
3、会话缓冲区
1）join_buffer_size
join_buffer_size 定义了每个线程的内存缓冲区，当查询必须连接两个表的数据集并且不能使用索引时，这个缓冲区会被用到。这个缓冲区是专门为每个线程的索引连接操作准备的。可以通过查询计划中 Extra 列的值为 Using join buffer 来证明使用了这个缓冲区。建议这个缓冲区设置为默认大小。增加这个缓冲区的大小也不会加快连接操作的速度。
2）sort_buffer_size
这个变量定义了每个线程用于对结果集排序的每线程缓冲区。可以通过查询计划中 extra 列的值为 Using file-sort 来确定使用了这个缓冲区。不推荐增加这个缓冲区的大小，因为这个缓冲区是完全分配给每个请求的，而且当默认值太大时可能会降低查询的执行速度。
3）read_buffer_size
当 SQL 查询执行连续的表数据扫描时会用到这个缓冲区。只有在大量连续表数据扫描时才推荐增加这个缓冲区的大小。
4）read_rnd_buffer_size
这个缓冲区用来存储那些作为排序操作的结果被读取的数据。这个缓冲区和 read_buffer_size 的不同之处在于，他读取的连续数据是和数据在磁盘上的存储方式相关的。只有在执行大型 ORDER BY 语句时才推荐增加这个缓冲区的大小。
4、有关基础工具的变量
1）slow_query_log
这个布尔类型的变量可以启用执行缓慢的查询的日志功能，日志将会报告索引执行时间超过 long_query_time 变量值的查询。
2）slow_query_log_file
这个变量定义了当慢查询日志功能开启时保存所有被记录的查询文件的文件名。这个是全局变量，可以动态改变它的值。
3）general_log
这个变量用来启用记录每条查询执行情况的全面查询日志。这个变量只能在每个服务器实例值上启用或者禁用。这是个全局变量，可以动态改变它的值。
4）general_log_file
这个变量定义了记录了当全面日志启用时所有 SQL 查询的文件名，这是个全局变量，可以动态改变它的值。
5）long_query_time
这个变量指定了一个查询执行时间的限制，当慢查询日志功能启用时，执行时间超过这个限制的查询都会被记录在慢查询日志中。
6）log_output
这个变量定义了慢查询日志和全面查询日志的输出位置，有效的选项有 file,table,none。当定义输出位置为 file 是，日志的输出文件分别由 slow_query_log_file 和 general_log_file 系统变量来定义。如果这个变量为 table，日志输出将会分别记录在 mysql.slow_log 和 mysql.general_log 表中。这两个表是在内部以 CSV 存储引擎定义的，所以不支持任何索引。这是个全局变量，可被动态定义。
5、其他优化变量
1）optimizer_switch
这个变量定义了一系列 MySQL 查询优化器特性的高级开关，可以用来关闭（默认是激活状态）三种不同的索引合并条件以及引擎下推条件。
2）default_storage_engine
当未指定 ENGINE 值时，这个变量用来为 create table 命令指定存储引擎。
3）max_allowed_packet
可以用 max_allowed_packet 变量来定义 SQL 查询结果集的最大值。增大这个值会运行查询返回更大的结果集。