mysql系统变量专题学习
要深入研究mysql那首先对mysql的一些系统/扩展变量有一定的了解,因为这些变量不仅决定mysql一些配置信息,还影响了mysql的性能优化提升,其中包括安全、优化、并发、复制等等。
笔者上网查了一下,这些资料有限,以及官网的一个中文文档介绍的内容简短(不包括值域,作用域,有些变量压根没翻译只是给出值)所以想写篇文章,一来学习巩固一下mysql,二来方便以后查阅。其中内容笔者前后用了14天,尽量查阅大量资料(问人,网上查阅,自己测试)以确保尽可能正确,且有些直接从官网英文文档翻译过来,但难免会出现因为知识结构不全面而有什么纰漏。
PS:
一、想知道有那些变量(系统变量、状态变量、集群变量、日志变量…),在mysqladmin 中输入:“mysqladmin -u 用户 -p 密码variable” 或者在mysql命令端用“show variables”显示
二、以下所有测试的环境: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:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.49-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 120425 20: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;
SET timestamp=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 OX S设置为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.84 net_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去读表往往都是随机离散读的,所有其开销非常大。
改进后的filesort算法是:
1.读取所需要的数据,包含sort key,row pointer和查询所需要访问的字段
2.根据sort key排序
3.按排序后的顺序读取数据,由于sort_buffer_size中包含了所需要的字段,因此不需要再回表了,可以直接返回结果给客户端。
很明显,这种改进的方法对sort_buffer_size的需求也大大增加.
所以为了防止性能下降,mysql增加了一个参数max_length_for_sort_data,当第一步中除了sort key以外的字段内容大于max_length_for_sort_data这个参数时,mysql将采用第一种排序算法。
--------------------------------------------------------------------
20、max_prepared_stmt_count | 16382
该变量设置预处理语句限制数。这个功能能防止拒绝服务攻击,因为攻击可以通过大量的预处理语句致使服务器内存溢出来攻击。设置此变量就是一道安全屏障,此变量对原有的预处理语句不影响,但若是预处理语句的数量超过该变量的值,则不会新增预处理语句,而是等到预处理语句的值小于max_prepared_stmt_count的值才增加新语句。
作用域:全局
默认值:16382 值域:0~1048576
若设为0则表示不允许预处理语句。
注意:该变量是version()>5.0.21才有
-----------------------------------------------------------------
21、max_relay_log_size | 0
中继日志大小,和第10点的max_binlog_size类同,只不过中继日志是保存从服务器的日志,是从主服务器复制过来的二进制日志。
当max_relay_log_size =0则max_relay_log_size=max_binlog_size
当max_relay_log_size >0则中继日志大小是max_relay_log_size设定的值
作用域:全局
默认值:0 值域:0~1073741824
-----------------------------------------------------------------
22、max_seeks_for_key | 4294967295
此变量假定索引搜索行数最大值。也就是说这个值是索引搜索最大值。查询优化器会忽略索引基数(用show index from table 可看到基数cardinality),它假定扫描索引匹配的行数不会超过max_seeks_for_key设定值。
官网:可以通过改小这个值来强制mysql使用索引来代替全表扫描。
作用域:全局 、会话
在32位系统:默认4294967295,值域:1~ 4294967295
在64位系统:默认18446744073709547520,值域1~18446744073709547520
--------------------------------------------------------------------
23、 max_sort_length | 1024
当排序BLOB或者TEXT类型列数据时用的字节数。当数据长度> max_sort_length设定值,排序就用max_sort_length的长度来排序,后面的数据被忽略。
作用域:全局 、会话
默认是1024 值域:4~ 8388608
----------------------------------------------------------------------
25、max_sp_recursion_depth | 0
设定存储过程(sp=>save procedures)最大递归数。
作用域:全局 、会话
默认0,禁止存储过程递归,最大值255
PS:此变量在version()=5.0.17引入
----------------------------------------------------------------------
26、max_tmp_tables | 32
设定客户端同时能打开临时表个数的最大值
作用域:全局 、会话
32位系统:默认32 值域:1~4294967295
64位系统:默认32 值域:1~18446744073709547520
PS: 官网:This variable does not yet do anything(该变量还没生效)
------------------------------------------------------------
27、max_user_connections | 0
Mysql每个用户能同时链接服务器最大值。0表示没有限制
默认值:0 值域:1~ 4294967295
作用域:全局 、会话
----------------------------------------------------------------------
28、max_write_lock_count | 4294967295
设定数据表写锁定最大数,注意:这是对同一个表来说的。
作用域:全局
值域:
32位系统:默认4294967295 值域:1~ 4294967295
64位系统:默认18446744073709547520 值域:1~18446744073709547520
网上有人说:当对同一个表锁定个数超过设定值的时候,服务器会释放读锁定。
笔者有些怀疑,做了个测试:
max_write_lock_count =4294967295
开启三个客户端,第一个锁定写,第二个select读取,第三个有加一个
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:Benwin 来源: Benwin 's blog
- 标签: 系统变量
- 发布时间:2012-10-28 23:28:33
- [67] Go Reflect 性能
- [65] Oracle MTS模式下 进程地址与会话信
- [62] 如何拿下简短的域名
- [60] 【社会化设计】自我(self)部分――欢迎区
- [60] android 开发入门
- [58] 图书馆的世界纪录
- [58] IOS安全–浅谈关于IOS加固的几种方法
- [54] 视觉调整-设计师 vs. 逻辑
- [48] 界面设计速成
- [47] 给自己的字体课(一)——英文字体基础