优化SQL系统变量分析

56次阅读

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

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

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