本文详细介绍了MySQL 5.1和5.5版本中的全局变量和会话变量，包括`log_slow_queries`、`log_warnings`、`long_query_time`等关键变量的作用、设置及影响。通过例子展示了如何查看和分析慢查询日志，以及如何通过调整变量优化性能。文章旨在系统学习和查阅MySQL变量，内容涵盖了安全、优化、并发和复制等多个方面。
下面是msql5.1 和mysql5.5的全局变量和会话变量总数
MYSQLVERSION:
5.1.72-0ubuntu0.10.04.1-log
select count(*) from global_VARIABLES;
277
select count(*) from session_VARIABLES;
277
MYSQLVERSION:
5.5.38-0ubuntu0.12.04.1
select count(*) from global_VARIABLES;
317
select count(*) from session_VARIABLES;
329
这几天想系统了解一下MySQL变量，总数有五六百个，还是不少，一点一点来吧，一口吃不了个胖子。到网上找了个还算比较全的，放在这儿备查，下面这个文档当时搞了十几天，吼吼。
以下转载自http://www.verydemo.com/demo_c152_i53025.html
要深入研究mysql那首先对mysql的一些系统/扩展变量有一定的了解，因为这些变量不仅决定mysql一些配置信息，还影响了mysql的性能优化提升，其中包括安全、优化、并发、复制等等。
笔者上网查了一下，这些资料有限，以及官网的一个中文文档介绍的内容简短（不包括值域，作用域，有些变量压根没翻译只是给出值）所以想写篇文章，一来学习巩固一下mysql，二来方便以后查阅。其中内容笔者前后用了14天，尽量查阅大量资料（问人，网上查阅，自己测试）以确保尽可能正确，且有些直接从官网英文文档翻译过来，但难免会出现因为知识结构不全面而有什么纰漏。
PS：
一、想知道有那些变量（系统变量、状态变量、集群变量、日志变量…），在mysqladmin 中输入：“mysqladmin –u 用户 –p 密码 variable” 或者在mysql命令端用“showvariables”显示
二、以下所有测试的环境：win7、mysql 5.1.49-community-log
三、以下变量中的作用域有全局、会话，值域表示变量值的范围（这些是中文文档锁没有的）
1、 log_slow_queries | OFF/ON
慢查询记录日志，慢查询是指查询时间超过设定时间（如下面设为2秒）的查询，（还有个指标是第29点的min_examined_row_limit）这个可以记录那些查询语句比较慢，然后通过分析语句而优化数据库或查询语句。具体配置在my.ini加入：
log_show_queries = “日志路径/文件名” #保存日志的路径和文件名，确保权限可写
long_query_time =2 #超过多少秒则保存查询数据
log-queries-not-using-indexs #不使用使用索引
PS：加上代码后重启mysql后log_slow_queries=ON状态（默认OFF）
测试：select * from zd_ask;
结果：在指定的文件里面记录如下
C:\ProgramFiles\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.49-community-log (MySQLCommunity Server (GPL)). started with:
TCP Port: 3306, NamedPipe: (null)
Time Id Command Argument
# Time: 12042520:40:49
# User@Host:root[root] @ localhost [127.0.0.1]
# Query_time:0.452026 Lock_time: 0.187010 Rows_sent: 12408 Rows_examined: 12408
use bus7zd;
SETtimestamp=1335357649;
select * from zd_ask;
说明：超过时间的查询语句：select * from zd_ask; 查到的结果数：12408 时间：0.452026 其他就是环境信息。
作用域：全局
-------------------------------------------------
2、log_warnings | 1
默认值为1，表示在错误日志当中添加更多日志，日至格式：
120426 11:55:09[Worning]内容，上网查了一下，国内这个变量的资料少得可怜，查看官网英文文档，会把一些断开链接的错误写进错误日志里面。
可以在配置文件my.ini 加入skip-log-warnings=1来停止log_warning 的使用， skip-log-warnings=1后在mysqladmin 里面用”mysqladmin –u 用户名 –p密码 variables”查看,会发现log_warning的值是0而不是默认的1.
作用域：全局
--------------------------------------------------
3、long_query_time |10.000000
这是和log_slow_queries一起使用的，它是设置慢查询时间，若值是0.2，则查询大于0.2秒的定为慢查询。如果启用了慢查询日志，则会把慢查询的信息写如慢查询日志文件中。具体可以查看
作用域：全局 、会话
本文第一点。慢查询：http://www.phpben.com/?post=67
-------------------------------------------------
4、 low_priority_updates |OFF
这个变量是降低mysql写数据的权限的，mysql默认情况下写操作权限高于读操作。附加一些知识：在mysql MYISAM表中读写是串行，即是select时锁表，insert等待释放再，反之一样。然后为了减少锁存和锁读的频率，则引入了concurrent_insert这个变量，使读写能并行操作（具体根据concurrent_insert值还有mysql版本而定）
作用域：全局 、会话
在mysql5.06版本之后，concurrent_insert=0则读（select）的时候不能执行写（insert）concurrent_insert=1则select时写操作把数据写在文件，concurrent_insert=2和1差不多，不同的是：1在数据没内存碎片（洞）才能写在文件尾，否则还是写在洞里；而2则是在select时并发写入文件尾，当select释放读锁时，数据写入洞里面。---推荐用current_insert=2
官网描述：
| Value |
Description |
| 0 |
Disables concurrent inserts |
| 1 |
(Default) Enables concurrent insert for MyISAM tables that do not have holes |
| 2 |
Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole. |
回正题：
low_priority_updates=1则是比上述方法更极端，直接给读操作优先与写操作。（不推荐）
----------------------------------------------------------
5、lower_case_file_system |ON
这是控制mysql数据库文件名在文件系统中是否对大小写敏感，默认是ON对大小写不敏感，OFF的话就是对大小写敏感。
作用域：全局
----------------------------------------------------------
6、lower_case_table_names |1
此变量是设置数据库名或表别名是否大小写敏感。
作用域：全局
0：存储时按照指定的表名，比较时对大小写敏感。
1：存储时按照小写（不管表是否有大写），比较时对大小写不敏感。
2：存储时按给定的表名，比较时用小写。
对于windows和Mac OX S 这些文件系统对大小写敏感的系统一般不设0，windows默认设置为1，Mac OXS设置为2。
------------------------------------------------------------
7、max_allowed_packet |1048576
8、net_buffer_length | 16384
先介绍net_buffer_length,它是每个客户端线程的连接缓存区和结果缓冲区都是通过net_buffer_length来初始化，net_buffer_length默认初始值是16384（16K），但其值最大可以达到max_allowed_packet设定值，max_allowed_packe默认值是1M，最大达到1073741824（1G），其值必须是1024的倍数，否则回落到最靠近1024倍数值（如1025则值是1024），在内存允许的情况下，max_allowed_packe越大越好。
若max_allowed_packe设置的小，当增改(insert/update/load data infile…)数据库时，若出现大字符串或blob类型列且大小大于max_allowed_packe值则会出现以下问题。
My.ini加入配置
net_buffer_length=1024 #默认是16384，这里改小为了测试
max_allowed_packet=1025#默认是1M，这里虽然是1025而事实上值是1024
笔者更新表某列（值大于1024）则出现：
ERROR 1153 (08S01):Got a packet bigger than 'max_allowed_packet' bytes
注意：在version()<5.0.84net_buffer_length可以设置但没有效，version()=5.0.84是只读
------------------------------------------------------------
9、 max_binlog_cache_size |4294963200
这是设置最大二进制日志的缓存区大小的变量。若处理多语句事务时需要的内存大小比设置值大的话就会提示一个error：Multi-statement transaction required more than'max_binlog_cache_size' bytes of storage 。
这个变量最小值是4096（4K），最大值，在32位的系统中是4G，64位的是16P。
作用域：全局
在mysql5.0中，max_binlog_cache_size一修改则所有会话都受影响（可能之前的受延迟，笔者没查证过）
--------------------------------------------------------------
10、 max_binlog_size |1073741824
这是设置每个二进制日志文件内容大小的变量，如果当前文件的数据量大于max_binlog_size的值时，则会关闭此文件，新建下个文件写入数据。
但是，当处理多语句事务（大事务）时，会出现文件数据大小比此值设置值大的情况。这是因为事务的二进制日志是块存储的，也就是说不会分割出来存放在两个日志文件中。
作用域：全局
------------------------------------------------------------------
11、 max_connect_errors |10
设置某客户端链接mysql服务器失败次数，若次数超过此值，则锁定禁止该客户端链接服务器且提示错误，若在未超过此值有一次成功链接，则计数器会清零。
这是一个与性能无关的变量，而是安全方面考虑的，主要防止穷举法破解数据库用户和密码。
作用域：全局
默认值是10，32位系统1~ 4294967295，64位的是1~18446744073709547520
官方说：一旦锁定，要解锁只有方法：在mysql客户端flush hosts ；在mysqladmin中用mysqladmin flush-host
笔者在本机测试过：这个功能用不了 。
------------------------------------------------------------------
12、 max_connections | 100
这个设置数据库并发可连接的数量
作用域：全局
值域：
Version()<=5.1.14 ：默认100
Version()>=5.1.15 ：默认151 1~16384
Version()>=5.1.17 ：默认151 1~100000
这是网站成长必要修改的一个变量，允许多少人在网站上并发操作。
------------------------------------------------------------------
13、max_delayed_threads |20
延迟操作Delay_insert最大线程数
作用域：全局 、会话
默认20 值域：0~16384
-----------------------------------------------------------------
14、 max_error_count |64
Show warning 或show error 显示warning或Error显示的最大个数，默认是64，值域0~65535.此值不能改太小，否者若错误提示个数比此值多的话不方便调试。
作用域：全局 、会话
注意：此值不要调太小；对于已经运行系统，可设为0，不会提示错误。
-----------------------------------------------------------------
15、max_heap_table_size |16777216
内存表最大行数。
作用域：全局 、会话
值域：32位操作系统16384~4294967295 64位操作系统16384 ~1844674407370954752
默认值都是16777216
更新设置此值对已存在的内存表没影响（重启mysql服务器就有），对create/update/truncate语句有影响。
-------------------------------------------------------------------
16、max_insert_delayed_threads |20
是max_delayed_threads的别名，看第13点。
作用域：全局 、会话
-----------------------------------------------------------------------
17、 max_join_size |18446744073709551615
18、 sql_big_selects | ON
max_join_size和sql_big_selects
作用域：全局 、会话
sql_big_selects默认值是1，表示所有select查询都执行（不管时间长短）。
sql_big_selects=0/OFF时，mysql先估算单表查询结果行数或多表查询组合行数的大小，若比max_join_size大时，就会放弃该查询语句。
max_join_size默认是4294967295，值域：1~4294967295
注意：sql_big_selects=0/OF的情况下，max_join_size不齐作用，且当max_join_size设置为非默认值时，sql_big_selects会被重置为0.
------------------------------------------------------------------------
19、 max_length_for_sort_data | 1024 ====
确定使用的filesort算法的索引值大小的限值。
作用域：全局 、会话
=========以下引用网上内容这是地址：http://www.itpub.net/thread-1417429-1-1.html
mysql的filesort算法有两种：
一种是最初的算法，在MySQL 4.1以前只有这种算法，一种是改进的filesort算法，它出现在MySQL 4.1以后(blob和text类型的字段不能采用这种改进算法)
"最初的算法"流程如下：
1.读取所有的满足条件的数据，只包含sort key和row pointer两种数据
2.在buffer中执行qsort排序
3.排完序后，再根据row pointer去读取相应的行数据
从中可以看出，每次排序都需要读两次表，而根据row pointer去读表往往都是随机离散读的，所有其开销非常大。
改进后的fileso
最低0.47元/天 解锁文章
扫一扫
4425
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
