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加入配置