优化SQL系统变量分析

这篇文章主要介绍“优化SQL系统变量分析”，在日常操作中，相信很多人在优化SQL系统变量分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”优化SQL系统变量分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

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%';

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

到此，关于“优化SQL系统变量分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！