MySQL系统变量优化详述

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查询结果集的最大值。增大这个值会运行查询返回更大的结果集。