mysql变量之系统静态变量、状态变量

初学mysql的时候，一直觉得其系统环境变量很多，很神秘。一句show variables;就能查询到200多个环境变量，要是能把每个变量的意思都搞明白，岂不是就能窥探到mysql的衣角。

Mysql的变量分为三种：系统静态变量、系统动态变量和系统状态变量，在这里介绍了一下系统静态变量和状态变量，记录如下：

 

变量名称	解释说明	默认值
auto_increment_increment	控制列的值的增量值	1
auto_increment_offset	控制列的值的偏移量	1
Back_log	监听队列的最大值	50
Basedir	Mysql的安装位置
Bdb_cache_size	为BDB表缓存索引和行分配的缓冲区的大小
Binlog_cache_size	同时开启事物和二进制日志，每个客户端日志的缓存大小
Bulk_insert_buffer_size	非空表插数据(INSERT…SELECT|LOAD DATA INFILE),树状缓存	8M
Character_set_client	客户端语句的字符集
Character_set_connection	用于没有字符集导入符的文字和数字----字符串转换
Character_set_database	默认数据库使用的字符集
Character_set_results	向客户端返回结果的字符集
Character_set_server	服务器的默认字符集
Character_set_system	服务器用来保存识别符的字符集，此值一定是utf8	Utf8
Character_sets_dir	字符集安装目录
Completion_type	如果该值为0(默认)，COMMIT和ROLLBACK不受影响 如果该值为1，新事务用刚刚结束的事务相同的间隔等级立即启动 如果该值为2，事务终止后，服务器断开	0
Concurrent_insert	默认为ON，允许没有空数据块的前提下，insert和select并行，0为关闭
Connect_timeout	Mysqld服务器用bad handshake响应前等待连接包的秒数	10
Datadir	数据保存目录
Default_week_format	WEEK()函数使用的默认值
div_precision_increment	除法运算结果小数保留位数，默认为4，最大30	4
Expire_logs_days	二进制日志删除的时间，如果开启会自动删除二进制日志	0
Flush		ON
Group_concat_max_len	GROUP_CONCAT()函数结果的最大长度
Have_compress	如果不支持，不能使用COMPRESS()和UNCOMPRESS()函数
Have_query_cache	是否支持查询缓存	YES
Innodb_buffer_pool_size	Innodb缓冲池的大小，一般可以设置为内存的一半
innodb_flush_log_at_trx_commit	是直接写磁盘，还是写系统缓存
Key_buffer_size	索引的缓存区，多线程共享，最大为4G，并且受到操作系统对每个进程使用RAM的限制，最好是设置为内存的25%
Local_infile	是否支持LOAD DATA INFILE
Log	是否启用常规查询日志	OFF
Log_bin	是否启用二进制日志
Log_error	错误日志的位置
Log_slow_queries	是否记录慢查询
Log_warnings	是否产生其他警告信息
Long_query_time	慢查询的时间标准
Max_binlog_cache_size	多语句事务需要更大的内存
Max_binlog_size	二进制日志文件的大小限制	1G
Max_connections	允许的并行客户端连接数目。
Max_error_count	保存由show errors和show warnings显示的相关信息的条数
Max_user_connecttions	账户允许的最大同时连接数，0表示没有限制
Pid_file	进程ID文件的路径名
Port	服务器监听端口
Query_cache_size	为查询缓存分配的内存大小
Query_cache_type	OFF不缓存查询，这样不会取消query_cache_size的空间开销。应该将其置为0 ON缓存除SQL_NO_CACHE开头的所有查询	On
Socket	客户端链接的套接字文件（unix）
Sort_buffer_size	每个排序线程分配的缓冲区大小，可以加快order by和group by
Tmpdir	保存临时文件和临时表的目录
Version	服务器版本号

 

上面只是一部分，还有很多没有记录，这是系统的环境设定的静态变量，那我们如果想查看mysql运行以来的状态，就可以用show status;

 

参数	说明
基本情况
Connections	连接服务器(不管是否成功)的次数
Uptime	服务器工作时间
Max_used_connections	同时使用的最大连接数量
Open_tables	当前打开的表的数量。
Opened_tables	已经打开的表的数量。调优静态变量表缓存数table_cache：如果open_tables接近table_cache，并且opened_tables不断增长，就需要增加table_cache的值。 table_cache是所有线程打开的表的数目(一个表使用2个文件描述符)，表数量多，就要大一些。增大该值可以增加mysqld需要的文件描述符的数量。根据数据库系统中表数量来决定该值，如2048。
线程使用情况
Threads_cached	线程缓存内的线程数
Threads_connected	当前打开的线程数
Threads_created	创建过的线程数。调优静态变量线程缓存数thread_cache：如果该值增加很快，当前thread_cache_size的值可能太小。缓存访问率是Threads_created/Connections。 服务器应缓存多少线程以便重新使用。当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程被放入缓存，一般配置8。
Threads_running	运行(非睡眠)状态的线程数
查询缓存
Qcache_free_blocks	缓存中相邻内存块的个数。数目大说明可能有碎片。调优方法：FLUSH QUERY CACHE;会对缓存中的碎片进行整理，从而得到一个空闲块，如果flush运行的时间很长，说明缓存太大了，可以适当调小静态变量query_cache_size的值。
Qcache_free_memory	缓存中剩余的内存。调优静态参数query_cache_size：如果剩余内存不足，可以增加该值，如设置query_cache_size=64M
Qcache_hits	查询缓存命中次数，该值越大越好
Qcache_inserts	插入查询缓存的次数。缓存命中率 = 1 – Qcache_hits/ Qcache_inserts。80%以上的查询缓存命中率就算合格。
Qcache_lowmem_prunes	查询缓存过低的次数。缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks 和 free_memory 可以告诉您属于哪种情况）。
Qcache_not_cached	不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache	当前缓存的查询（和响应）的数量。
Qcache_total_blocks	缓存中块的数量。
SQL执行频率
Com_select	执行select操作次数
Com_insert	执行insert操作次数
Com_update	执行update操作次数
Com_delete	执行delete操作次数
Com_commit	事务执行commit操作次数
Comm_rollback	事务执行rollback操作次数。如果回滚频繁，就说明程序存在某些问题。
Slow_queries	慢查询的次数。调优SQL性能：如果该值增加很快，需要分析慢查询日志，针对查询SQL优化。
Innodb_rows_read	执行select返回的行数。以下几个InnoDB的。
Innodb_rows_inserted	执行insert操作的行数。通过这几个参数，可以知道数据库是查询为主还是插入为主。
Innodb_rows_updated	执行update操作的行数
Innodb_rows_deleted	执行delete操作的行数
Sort_merge_passes	排序算法已经执行的合并的数量。调优静态变量sort_buffer_size：如果该值很大，说明排序缓冲区太小，如设置sort_buffer_size = 5M 当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。
索引使用情况
Handler_read_first	使用全索引扫描的次数。如SELECT col1 FROM foo，假定col1有索引
Handler_read_key	使用索引次数，该值越高越好。
Handler_read_next	按照键顺序读下一行的请求数。使用索引描述时，从数据文件取数据的次数
Handler_read_prev	使用索引描述时，按索引倒序从数据文件取数据的次数。一般是order by/desc查询
Handler_read_rnd	查询直接操作数据文件的次数，有可能未使用索引
Handler_read_rnd_next	在数据文件中读下一行的请求数。若该值非常大，说明使用了大量的表扫描，索引使用率不高或没有使用索引。Handler_read_rnd_next/Com_select是表扫描比率，如果该值超过 4000，就应该调优静态参数read_buffer_size。如read_buffer_size=1M，若超过8M，那么就要优化SQL了。
锁使用情况
Innodb_row_lock_current_waits	当前等待行锁的行数
Innodb_row_lock_time	行锁定用的总时间(ms)
Innodb_row_lock_time_avg	行锁定的平均时间(ms)。该值大，说明锁冲突大
Innodb_row_lock_time_max	行锁定的最长时间(ms)
Innodb_row_lock_waits	行锁定必须等待的时间(ms)。该值大，说明锁冲突大

 

这也只是一部分，看完mysql的静态变量和状态变量，对与mysql的优化就应该有一定的认识了，准备有时间的时候测试一下。

原文地址：http://www.arkulo.com/?post=21