本文档截取自电子工业出版社出版的图书《千金良方:MySQL

性能优化金字塔法则》的附录部分,该图书由来自沃趣科技的李春、罗小波、董红禹三位作者共同撰写.
更多精彩内容可关注下方的微信公众号"沃趣技术"与"知数堂"附录CMySQL常用配置变量和状态变量详解本文档主要基于MySQL5.
6.
35和5.
7.
17版本编写,但在编写后期也加入了一些新版本特性相关的参数:例如,MySQL5.
7.
22以及8.
0.
x版本中的并行复制优化参数1、MySQL关键配置参数注:以下配置参数融合了5.
6.
35版本和5.
7.
17版本1.
1.
基本设置1.
1.
1.
transaction_isolation设置隔离级别的参数:transaction_isolation='read-uncommitted|read-committed|repeatale-read|serializable':默认是repeatable-read,几个值分别代表的含义:read-uncommitted:读未提交,允许脏读;read-committed:读提交,不允许脏读,但允许不可重复读;repeatable-read:可重复读,不允许脏读、不可重复读,但允许幻读;serializable:串行化,以上都不允许o该参数在mysql命令行直接动态修改时使用的参数名称是tx_isolation='repeatable-read',必须有中杠连接并带有引号o该参数在my.
cnf的[mysqld]标签下使用的参数名称是transaction_isolation=repeatable-read,必须有中杠连接,引号可有可无o该参数也可以使用类似语句settransactionisolationlevelrepeatableread;来间接修改,且不带中杠也不带引号,隔离级别关键字之间是使用空格隔开.
o动态修改隔离级别时,带global关键字的语句表示对后来的会话生效,对当前会话不生效,带session关键字的语句表示立即对当前会话生效,不带global和session关键字的表示对当前会话的下一个事务或者说下一个请求生效.
注意:使用begin或starttransaction语句显式开启一个事务之后,不能在活跃的事务内更改隔离级别.
这些关键字的作用范围与修改配置参数时效果是一样的o全局,会话,动态变量,枚举类型,默认值为repeatable-read.
o注意:该参数有个比较尴尬的地方,即在my.
cnf中只能写作transaction_isolation(这个是mysqld的启动选项,但非serversystemvariables),不能写成tx_isolation(这个是serversystemvariables但非启动选项),但是在命令行中只能使用tx_isolation,不能使用transaction_isolation.
1.
1.
2.
max_allowed_packet控制一个数据包或由mysql_stmt_send_long_data()CAPI函数发送的任何参数的最大大小.
默认值为4MB,要注意,客户端和服务端都要同时设置为一样大的值,比如在mysqldump备份的时候,生成整表单条insert语句的时候,太小的值可能导致备份失败.
一般建议设置为32M或64Mo包消息缓冲区初始化为net_buffer_length定义值大小,但在需要时可以增长到max_allowed_packet定义值大小o如果使用BLOB列或长字符串,则必须增加此值.
设置为你想要使用的最大BLOB一样大,max_allowed_packet的协议限制为1GB.
该值应为1024的倍数;否则四舍五入取最接近的倍数.
o当您通过更改max_allowed_packet变量的值来更改消息缓冲区大小时,如果客户端程序允许修改,还应该同时更改客户端的缓冲区大小.
内置到客户端库中的默认max_allowed_packet值为1GB,但各个客户端程序可能会使用此参数定义另外一个值,该值将覆盖客户端库中的默认值.
例如,mysql和mysqldump分别定义一个默认值16MB和24MB.
它们还允许您通过在命令行或选项文件中设置max_allowed_packet来更改客户端值.
o全局变量,会话变量,动态变量(注意,仅仅只是全局动态,会话是只读的,so,这个变量特殊的地方是会话变量是只读,所以对于服务端来讲,动态修改全局值会立即影响当前会话发送的包大小,此时会忽略会话值的大小,而客户端接收的大小仍然是以会话值为准的.
因此建议在动态修改这个值之后,断开连接重连,避免让你认为发生了灵异事件),单位为字节.
5.
6.
5及其之前版本默认值为1M,5.
6.
6开始默认为4M,最小值为1K,最大值为1G,整型值.
1.
1.
3.
max_length_for_sort_data控制MySQL排序的最大字段定义的列总字节长度omysql有两种文件排序算法(双路排序和单路排序),如果需要排序的列的总大小加上orderby列的大小超过了max_length_for_sort_data定义的字节,mysql就会使用双路排序,当任何需要的列(包含结果集列和orderby的列)包含text、blob列时,也会使用双路排序,(可以使用subsstring()把这些列转化为可以单路排序的列).
o可以通过改变max_length_for_sort_data变量的值来影响mysql选择的算法.
因为单路排序要将排序的每一行创建固定的缓冲区,varchar列的最大长度是max_length_for_sort_data规定的值,而不是排序数据的实际大小(5.
7.
x版本中对排序做了优化,分配排序缓冲时针对变长列可以根据数据实际占用的大小来分配内存).
o当MySQl不得不对text、blob列进行排序时,它只会使用前缀并忽略剩余的值,这是因为不得不分配固定大小的结构来容纳数据并且从外部存储中将前缀拷贝回结构中,可以使用max_sort_length定义前缀应该是多大omysql并不能查看某个查询执行时内部使用的是哪种算法,如果增大了max_length_for_sort_data的值,并且磁盘使用率上升,cpu使用率下降,Sort_merge_passes的值比以前增加的更快,也许该强制排序使用单路排序算法.
*双路排序:读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出.
双路排序的开销可能会非常巨大,因为他会读取表两次,第二次读取会引发大量的随机I/O,对于MyISAM涞说,这个代价尤其昂贵,MyISAM表利用系统调用去提取每行的数据.
*单路排序:读取查询需要的所有列,按照orderby列对他们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据.
并且把随机I/O变成了顺序I/O,但是它会使用更多的内存空间,因为它把排序需要的所有列都一次性度的去出来保存在内存中了o全局,会话,动态变量,整型值,单位为字节,取值范围4~8388608字节,默认值为1024字节.
1.
1.
4.
optimizer_switch控制查询优化器优化行为的参数(>=5.
6.
9版本)ooptimizer_switch系统变量允许控制优化器行为.
此变量的值是一组标签,每个标签(子选项)具有on或off值,以指示相应的优化程序行为是启用还是禁用.
该参数的各个子选项之间没有顺序的限制.
o该参数有众多子选项,全局,会话,动态变量,set类型,全局默认值可以在服务器启动时设置,默认值为:index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=ono每个标签有三个有效值:*default:重置该子选项为server默认值,在你经过一些修改之后不记得默认值是什么的时候有用*on:开启该子选项对应的优化器行为*off:关闭该子选项对应的优化器行为o以下是每个标签(子选项)的含义如下表(所有子选项中,在5.
6.
9之后的版本默认值只有batched_key_access才是OFF,而BKA特性对于join查询有帮助,所以建议默认开启,要注意,目前基于成本的MRR估算太悲观,所以要使用MRR和BKA,必须要将mrr_cost_based设置为OFF,即5.
6.
x版本中除了mrr_cost_based建议设置为OFF之外,其他的子选项都建议设置为ON):表附C-1优化特性名标志名称含义默认值批量键访问batched_key_access控制是否开启BKA连接算法OFF块嵌套循环block_nested_loop控制是否开启BNL连接算法ON引擎条件下推engine_condition_pushdown控制是否开启引擎条件下推ON索引条件下推index_condition_pushdown控制是否开启索引条件下推ON索引扩展use_index_extensions控制是否开启索引扩展优化ON索引合并index_merge控制是否开启所有的索引合并优化特性ONindex_merge_intersection控制是否开启索引合并交集查询优化ONindex_merge_sort_union控制是否开启索引合并排序联合查询优化ONindex_merge_union控制是否开启索引合并联合查询优化ON多范围读取mrr控制是否开启多范围读取优化策略ONmrr_cost_based如果mrr=on,则该子选项控制是否开启基于成本的MRR优化策略ON半连接semijoin控制是否开启所有半连接查询优化策略ONfirstmatch控制是否开启半连接FirstMatch优化策略ONloosescan控制是否开启半连接LooseScan优化策略(不要与用于GROUPBY的LooseScan混淆,这里的是用于semijoin查询的LooseScan)ON物化子查询materialization控制是否开启物化查询(包括半连接物化查询)ONsubquery_materialization_cost_based控制是否开启基于成本的物化子查询选择ON控制查询优化器优化行为的参数(>=5.
7.
8版本)与5.
6类似,以下列出5.
7中的默认值,与5.
6相同的选项就不再列举,只列出5.
7新增的优化器策略,5.
7默认值为:index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on以下列出5.
7.
8以上的5.
7.
x版本中与5.
6.
9以上的5.
6.
x版本中相比多出来的查询优化器策略:oduplicateweedout:控制半连接重复Weedout策略是否开启ocondition_fanout_filter:控制在计算查询优化器代价时,是否计算条件过滤的策略(5.
7在代价类型上分为io,cpu和me,mysql5.
7代价计算相对之前的版本有较大的改进.
例如*代价模型参数可以动态配置,可以适应不同的硬件*区分考虑数据在内存和在磁盘中的代价*代价精度提升为浮点型*jion计算时不仅要考虑condition,还要考虑condition上的filter,此参数就是控制是否使用condition上的filter的oderived_merge:控制是否将派生表和视图合并到外部查询块中如果在join查询中,开启了BKA特性,驱动表有排序字段,且where条件与排序字段是一个联合索引时,可能导致驱动表执行计划中出现'Usingtemporary;Usingfilesort',此时请关闭BKA特性,关闭之后驱动表使用ICP特性进行数据过滤(开启BKA无法使用索引排序的原因是:BKA是先根据where条件在二级索引中找出符合的主键字段值,再在joinbuffer里面根据主键排序,然后使用主键再去join被驱动表,如果驱动表有二级索引的排序字段,那么此时就无法再使用二级索引进行排序了),BKA特性如果在驱动表没有按照二级索引排序时,可以打开,该特性默认关闭1.
1.
5.
memlockmysqld启动选项(对应的serversystemvariables为locked_in_memory),如果开该参数,则会把分配给mysqld的内存锁定在物理内存中,防止被交换到swap空间中,以提高数据访问和操作效率,在内存资源紧张时,可以尽量保证分配给mysqld的内存不被swapout到磁盘.
但是有些副作用,比如:mysqld运行在一个大内存的服务器上,而bufferpool参数配置得又比较大时,如果mysqld执行重启操作,分配给mysql的内存越大启动时间越长,同时在mysqld尚未启动成功之前,涉及到会收集内存统计信息的所有命令都会被hang住(如psaux命令需要读取/proc下的一些信息,/proc/$mysqlpid/cmdline文件在mysqld没启动完成之前不可读),因为mysqld使用memlock参数实际上是使用了mlockall()系统调用,这个操作会把mysqld分配的内存大小一次性都给锁住(分配给mysqld的内存size中的每个内存page都需要设置PG_mlocked标签,所以导致分配动作很慢),直到mysqld启动成功.
过程中其他需要读取这部分内存分配信息的进程都需要进行等待.
当然,如果在你的mysql服务器操作系统中有因为内存不足而导致mysqld的占用的内存被swapout到磁盘的问题发生,那么则此选项可能有所帮助.
omemlock启动选项只在支持mlockall()系统调用的生效系统上有用.
操作系统包括Solaris、大多数使用2.
4或更高内核版本的Linux发行版,以及其他Unix系统.
在Linux系统上,您可以通过检查在系统mman.
h文件中是否有定义mlockall()来判断你的操作系统是否受支持mlockall()系统调用,如下所示:shell>grepmlockall/usr/include/sys/mman.
h,如果支持mlockall(),你应该能看到输出如下:externintmlockall(int__flags)__THROW;o注意:*使用此选项可能需要以root用户身份运行mysqlserver,出于安全考虑,这通常不是一个好主意.
*在Linux和其他系统上,如果需要限制以root启动mysqlserver,您可以通过更改limits.
conf文件来避免以root用户身份运行服务器*不要尝试在不支持mlockall()系统调用的系统上使用此选项,否则mysqld很可能启动失败*布尔类型,默认值为FALSE,如果设置为ON,则代表使用mlockall()系统调用来分配mysqld的内存.
另外,该选项并不是mysqld配置参数(非systemvariables,非innodbvariables),而是mysqld的一个启动选项.
1.
1.
6.
default_password_lifetime=0此变量定义全局密码自动到期策略.
它适用于使用MySQL内置身份验证方法(使用mysql_native_password,mysql_old_password或sha256_password的身份验证插件的帐户)的帐户o全局变量,动态变量,整型值,5.
7.
4版本引入,5.
7.
11版本之前默认值为360(代表密码360天之后过期),5.
7.
11及其之后的版本默认值为0(代表禁用密码过期策略,密码永不过期).
如果default_password_lifetime的值为正整数N,则表示密码每N天必须要更改一次,否则不允许登录并提示密码已经过期.
可以根据需要使用ALTERUSER语句单独指定单个帐户的密码过期策略.
o注意:*从MySQL5.
7.
4到5.
7.
10,默认的default_password_lifetime值为360(密码必须每年更改大约一次).
如果你的mysql版本包含在这个范围内,请留意,如果不对default_password_lifetime变量或单个用户帐户进行另行更改,则所有用户密码将在360天后过期,并且所有用户帐户将在受限模式下开始运行.
连接到服务器的客户端(有效用户)将收到一条错误,指示密码必须更改:"ERROR1820(HY000):YoumustresetyourpasswordusingALTERUSERstatementbeforeexecutingthisstatement",但是,对于自动连接到服务器的客户端(例如,由脚本创建的连接),密码到期之后这些工具使用的帐号报错信息很容易被疏忽掉.
为避免由于密码过期而使此类客户端突然停止工作,请务必更改这些客户端的密码到期设置,如下所示:ALTERUSER'script'@'localhost'PASSWORDEXPIRENEVER,或者,将default_password_lifetime变量设置为0,从而禁用所有用户的密码过期策略1.
1.
7.
old_passwords=0此变量控制PASSWORD()函数使用的密码散列方法.
它还影响由CREATEUSER和使用IDENTIFIEDBY子句指定密码的GRANT语句执行的密码散列o全局,会话变量,动态变量,枚举类型,默认值为0,在5.
7.
5之前的版本中,有0,1,2三个值(0代表使用mysql_native_password密码验证插件,密码加密字符串长度为41位,1代表使用4.
1之前的mysql_old_password密码验证插件,密码加密字符串长度为16位,2表示使用sha256_password密码验证插件,密码加密字符串长度为256位),在5.
7.
5及其之后的版本中只有0,2两个值(删除了对1的支持,即删除了对4.
1之前的密码格式的支持).
注意:该变量是已经弃用的功能,在5.
7.
6之后可能被移除.
o注意:*使用4.
1之前的哈希方法的密码比使用native密码哈希方法的密码安全性低,应该避免使用旧密码格式.
4.
1之前的密码已被弃用,并且在MySQL5.
7.
5中删除了对它们的支持.
因此,从5.
7.
5开始是不允许使用4.
1之前的密码格式的.
*默认的密码认证插件是mysql_native_password,可以使用系统变量default_authentication_plugin在配置文件中进行设置.
注意:该全局变量是只读变量.
如果某个客户端使用了不同的密码验证插件,则请在客户端连接成功之后,使用old_passwords变量进行修改为对应认证查询类型的数字,如:客户端使用的是sha256_password密码认证插件,则请在session级别修改old_passwords=2.
1.
1.
8.
group_concat_max_len=1024用于控制group_concat()函数能够连接的最大结果集字节长度o全局,会话,动态变量,整型值,默认值为1024字节,64位平台取值范围为:4~18446744073709551615字节1.
1.
9.
default_storage_engine=innodb设置默认存储引擎.
此变量仅为永久表(basetable)设置存储引擎.
要设置临时表的存储引擎,请设置default_tmp_storage_engine系统变量.
o要查看哪些存储引擎可用,请使用SHOWENGINES语句或查询INFORMATION_SCHEMAENGINES表odefault_storage_engine配置参数优先于storage_engine使用,storage_engine配置参数已被弃用,并在MySQL5.
7.
5中被删除o全局,会话,动态变量,枚举类型,默认值为InnoDB,有效值:使用SHOWENGINES语句或查询INFORMATION_SCHEMAENGINES表的结果集1.
1.
10.
default_tmp_storage_engine=innodb设置使用createtemporary语句显式创建的临时表设置默认存储引擎.
o全局,会话,动态变量,枚举类型,默认值为InnoDB,有效值:使用SHOWENGINES语句或查询INFORMATION_SCHEMAENGINES表的结果集,该变量在5.
6.
3版本中引入1.
1.
11.
internal_tmp_disk_storage_engine=innodb设置内部临时表使用的默认存储引擎,如:Usingfilesort,Usingtemporary等需要使用到磁盘临时表的场景时,内部创建的内部临时表的默认存储引擎.
o全局变量,会话变量,枚举类型,有效值为MYISAM和INNODB,5.
7.
5引入,默认值在5.
7.
5中为MyISAM,在5.
7.
6及其之后的版本中为InnoDB.
o注意:在internal_tmp_disk_storage_engine=INNODB下,如果内部临时表生成超过InnoDB行或列限制的查询结果集将返回Rowsizetoolarge或Toomanycolumnserrors.
此时解决方法是将internal_tmp_disk_storage_engine设置为MYISAM.
1.
1.
12.
explicit_defaults_for_timestamp=ON在MySQL中,TIMESTAMP数据类型与其他数据类型不同o在该参数出现以前,timestamp数据类型有如下特性,这些特性与其他数据类型相比,是非标准的行为:*未明确声明为NULL属性的TIMESTAMP列会被分配为NOTNULL属性.
(其他数据类型的列,如果未显式声明为NOTNULL,则允许NULL值.
),向此列插入NULL时将自动转换为当前时间戳*表中的第一个TIMESTAMP列,如果未声明为NULL属性或显式设置DEFAULT属性或ONUPDATE子句,将自动分配DEFAULTCURRENT_TIMESTAMP和ONUPDATECURRENT_TIMESTAMP属性.
*后续的TIMESTAMP列,如果未声明为NULL属性或显式设置DEFAULT子句,将自动分配DEFAULT'0000-00-0000:00:00'.
对于不指定此列的插入操作,将分配"0000-00-0000:00:00"值,并且不会发生警告o从MySQL5.
6.
6开始,可以使用配置参数explicit_defaults_for_timestamp关闭timestamp数据类型的非标准行为,如果不设置explicit_defaults_for_timestamp,在server启动时错误日志中会出现此警告:[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.
Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails).
启用此变量后,服务器处理TIMESTAMP的规则如下:*未明确声明为NOTNULL的TIMESTAMP列将允许为NULL.
将此列设置为NULL之后,再对此列插入NULL时就是null值,而不是当前时间戳*不再为第一个TIMESTAMP列自动分配DEFAULTCURRENT_TIMESTAMP或ONUPDATECURRENT_TIMESTAMP属性.
如果要使用这些属性则必须明确指定*不再为后续未声明为NULL属性或显式设置DEFAULT子句的TIMESTAMP列自动分配DEFAULT'0000-00-0000:00:00',在开启这个参数之后,声明为NOTNULL且没有显式DEFAULT子句的TIMESTAMP列被视为没有默认值.
对于插入时没有显式指定该列的操作时,如何处理取决于SQL模式.
如果启用了严格的SQL模式,则会发生错误.
如果未启用严格的SQL模式,则会为列分配隐式默认值"0000-00-0000:00:00",并发出警告.
这MySQL如何处理其他时间类型类似,如DATETIME.
o注意:explicit_defaults_for_timestamp本身已被弃用,它的唯一目的是用于控制在将来的MySQL版本中将被删除的且现在已经不推荐使用的TIMESTAMP数据类型的行为.
如果timestamp数据类型被删除,则explicit_defaults_for_timestamp配置参数也跟着废弃,同时也会被删除掉.
在5.
6.
6开始,timestamp数据类型在内部被当作int处理.
o全局,会话,动态变量,布尔型,默认值为false.
5.
6.
6版本引入,同时废弃掉了timestamp数据类型.
1.
1.
13.
unique_checks=ON控制是否检查唯一约束的开关o全局,会话,动态变量,布尔型,默认值为1.
如果设置为1,则存储引擎会对辅助索引执行唯一约束检查,发现重复值会报错拒绝插入,但是,如果设置为0,则存储引擎不会对辅助索引执行唯一约束检查,但仍然会在存储引擎层检查,如果发现重复值,仍然会报错拒绝插入,但设置为0时可能对大表插入有加速作用1.
1.
14.
time_zone=SYSTEM用于指定客户端当前时区,在客户端连接初始化时,对客户端使用这个变量设置的时区,默认值为SYSTEM,代表使用system_time_zone系统变量设置的时区值.
也可以使用--default-time-zone启动选项在服务器启动时显式指定该值.
o全局,会话,动态变量,string类型,默认值为SYSTEM,代表使用system_time_zone系统变量设置的时区值.
1.
1.
15.
sync_frm=ON控制是否在创建非临时表时,同时把.
frm文件落盘,这可能比较慢,但是在意外崩溃时更安全o全局变量,动态变量,布尔值,默认值为true.
o该变量从5.
7.
6开始不建议使用,在8.
0版本中已经废弃,默认内部开启,因为关闭这个参数被认为不安全1.
1.
16.
super_read_only=OFF控制是否开启super权限帐号只读.
如果启用了read_only系统变量,则服务器仅允许具有SUPER权限的用户进行更新.
如果还同时启用了super_read_only系统变量,则服务器同时禁止具有SUPER的用户更新.
在主库上对super_read_only的更改不会复制到从库上.
主备各自设置该变量不会相互影响.
o全局变量,动态变量,布尔型,默认值为OFF,5.
7.
8版本引入,要注意:该变量设置为ON时,会同时强制把read_only变量也设置为ON,当read_only变量设置为OFF时,也会同时强制把该变量设置为OFF1.
1.
17.
sql_safe_updates=OFF如果设置为1,MySQL将禁止执行在WHERE子句或LIMIT子句中不带索引列条件的UPDATE或DELETE语句.
即,UPDATE和DELETE语句必须有一个WHERE子句,且where条件列必须使用能使用到索引,如果不能使用索引时还必须另外加一个LIMIT子句,或两者都有更好.
否则不允许执行o示例(setsql_safe_updates=1;):*表结构:CREATETABLEsbtest1(idint(10)unsignedNOTNULLAUTO_INCREMENT,kint(10)unsignedNOTNULLDEFAULT'0',cchar(120)COLLATEutf8_binNOTNULLDEFAULT'',padchar(60)COLLATEutf8_binNOTNULLDEFAULT'',PRIMARYKEY(id),KEYk_1(k))ENGINE=InnoDB;留意这里的id,k和c列,id是主键,k有索引,c列无索引*deletefromsbtest1wherec='17962233561-27816105035-66206962467-49296211054-73206072617-48858000511-36677164809-97422115612-64772317024-51197779187';不允许执行,带where条件,但条件列无索引*deletefromsbtest1wherec='17962233561-27816105035-66206962467-49296211054-73206072617-48858000511-36677164809-97422115612-64772317024-51197779187'limit1;允许执行,带where条件,条件列无索引,但多加一个limit子句即可执行*deletefromsbtest1;不允许执行,不带where条件*deletefromsbtest1limit1;不允许执行,不带where条件*deletefromsbtest1whereid=1;允许执行,id列为主键,不需要额外加limit子句*deletefromsbtest1wherek=1;允许执行,k列有辅助索引,不需要额外加limit子句*对于update语句在设置sql_safe_updates=1时,也跟delete表现完全一致,只看where子句有没有索引,有索引就允许执行,无索引就不允许执行,除非再加上limit子句.
1.
1.
18.
datadir=/data/mysql/data/设置数据文件存放路径.
如果log_bin、relay_log、error_log、general_log、slow_query_log、innodb_log_group_home_dir、innodb_data_home_dir等路径参数没有设置路径值时,默认会把这些参数对应的磁盘文件存放在datadir目录下o全局变量,只读变量,directoryname类型,无默认值,但如果是二进制包安装,在mysql.
server文件中的默认datadir=/usr/local/mysql/data,如果是编译安装或者rpm包安装,在mysql.
server文件中的默认datadir=/var/lib/mysql.
且rpm包的mysql.
server中在启动时还会判断datadir是否存在,如果不存在还会创建一个datadir并进行mysqld的初始化操作,所以你会发现rpm包安装的mysql只需要rpm安装一下,然后直接servicemysqldstart就可以使用了.
1.
1.
19.
collation_server=utf8_bin设置server的默认校对规则(排序规则),该变量的值会被collation_database参数继承.
如果创建表时没有单独设置校对规则,那么表也会继承,如果定义字段时没有单独指定校对规则,则字段也会继承.
o全局,会话,动态变量,string类型,默认值为latin1_swedish_ci,有效值:通过select*frominformation_schema.
collations;语句查询的结果集的COLLATION_NAME字段,其中IS_DEFAULT字段标识了是否是对应的CHARACTER_SET_NAME列列出的字符集的默认校对规则.
o注意:5.
7.
x的版本不再支持collation启动选项,使用collation_server在配置文件中代替该启动选项.
另外,如果character_set_server使用了非默认的字符集,则不指定校对规则时,会使用对应字符集的默认校对规则.
1.
1.
20.
character_set_server=utf8设置server的默认字符集,该变量的值会被character_set_client、character_set_connection、character_set_database、character_set_results、character_set_system参数继承.
如果创建表时没有单独设置字符集,那么表也会继承,如果定义字段时没有单独指定字符集,则字段也会继承o全局,会话,动态变量,string类型,默认值为latin1,有效值:通过select*frominformation_schema.
collations;语句查询结果集的CHARACTER_SET_NAME字段1.
1.
21.
basedir=/usr/local/mysql指定mysql的程序安装路径,其中mysql相关的所有脚本中调用mysql的程序都默认使用该路径.
o全局变量,只读变量,directoryname类型,默认值通常为:/usr/local/mysql1.
1.
22.
autocommit=1控制是否开启自动提交模式.
如果设置为1,对表的所有更改将立即提交.
如果设置为0,则必须显式使用COMMIT提交事务或ROLLBACK回滚事务.
如果autocommit在session级别从0更改为1时(无论是在事务内还是在事务外,只要该值从0变为1,就会隐式提交当前会话中的所有事务),MySQL将对会话级别活跃的事务隐式执行COMMIT操作,默认情况下,客户端连接server是自动提交的.
要使客户端以默认值0开始,请使用--autocommit=0选项启动server来禁止全局自动提交值.
或者在启动server前在my.
cnf中的[mysqld]标签下添加autocommit=0配置参数.
o全局,会话,动态参数,布尔型,默认值为ON1.
1.
23.
auto_increment_increment=2设置自增的步长值,常常与auto_increment_offset变量一起用于在主主复制环境中避开双写时的主键冲突.
o示例:如果auto_increment_increment步长设置为2,auto_increment_offset设置为1,那么自增插入值会自动以1,3,5,7,9这样递增,以此类推,如果auto_increment_increment步长设置为2,auto_increment_offset设置为2,那么自增插入值会自动以2,4,6,8这样递增.
o自增值的算法是:auto_increment_offset+N*auto_increment_increment,N为1,2,3.
.
.
这样递增的数值,当你修改这两个变量中的任意一个值之后,再次插入新数据时你会发现这个自增变得非常诡异.
其实在修改这两个变量之后,再次插入第一条数据的时候,该条数据的自增值还会按照如下两个规则进行重新计算:*计算出的值会与表中最大的自增做比较,如果比表中最大的值小,则把N+1继续计算下一个值*如果计算出来的值比表中最大的值大,则比较计算出来的自增值是否是auto_increment_offset的倍数,如果不是,则把N+1继续计算下一个值.
直到计算出的自增值符合这两个条件时,才把这个自增值插入表中.
这么做的原因是重新计算出来的值必须要错开双主复制中其他的主写入的id,尽量避免双主复制中双写时可能的id冲突.
o全局,会话,动态变量,整型值,取值范围1~65535,如果设置该变量值为小于等于0时,默认使用1代替,如果设置为大于65535的值时,使用65535代替1.
1.
24.
auto_increment_offset=2设置自增的偏移量(起始值),常常与auto_increment_increment变量一起用于在主主复制环境中避开双写时的主键冲突.
o全局,会话,动态变量,整型值,取值范围1~65535,如果设置该变量值为小于等于0时,默认使用1代替,如果设置为大于65535的值时,使用65535代替o注意:auto_increment_offset的值不能大于步长auto_increment_increment的值,如果设置大于auto_increment_increment的值,则在实际影响自增插入值时会被自动忽略auto_increment_offset的设置,而使用默认值11.
1.
25.
sql_mode=''设置当前server的sqlmodeo有效值及其对应的含义如下:*ALLOW_INVALID_DATES:不检查完整格式的日期.
仅检查月份和日期.
此模式适用于DATE和DATETIME列.
不适用于始终需要有效日期的TIMESTAMP列,服务器加你差月和日要求值合法,而不仅仅是月份在1到12、日期在1到31之间就可以的.
当禁用严格模式时,如"2004-04-31"等无效日期会被转换为"0000-00-00"插入,并产生警告.
当启用严格模式后,无效日期会生成错误并拒绝插入.
如果要允许此类无效日期写入,请启用ALLOW_INVALID_DATES值*ANSI_QUOTES:对于",把它当作一个标识符,而不是当作一个字符串的引号,当开启了这个sqlmode值之后,你就不能使用"来当作字符串的引号,因为此时会把"当作一个sqlmode标识符*ERROR_FOR_DIVISION_BY_ZERO:该模式影响除零操作的处理,包括MOD(N,0).
对于数据写入操作(INSERT,UPDATE),其表现行为还取决于是否启用了严格的SQL模式(是否启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES):如果未启用此sqlmode,那么除零操作将插入NULL并且不产生警告,如果启用此sqlmode,除零操作将插入NULL并产生警告,如果启用此sqlmode和严格模式,除零操作将产生错误并拒绝写入,除非给出了IGNORE关键字.
对于INSERTIGNORE和UPDATEIGNORE,除零操作将插入NULL并产生警告.
对于SELECT,无论是否启用严格模式除零操作都返回NULL并产生警告.
从MySQL5.
6.
17开始,不建议使用ERROR_FOR_DIVISION_BY_ZEROsqlmode.
在5.
7.
8版本之后默认开启,并且不推荐去掉,在将来的版本中将删除该模式而内部直接开启该模式.
*HIGH_NOT_PRECEDENCE:是否开启NOT运算符的高优先级模式,如果不使用该模式,那么如NOTBETWEENbANDc之类的表达式被解析为NOT(BETWEENbANDc),not不如between优先级高(如:SETsql_mode='';SELECTNOT1BETWEEN-5AND5;将优先执行1BETWEEN-5AND5,结果为1,然后再执行not,最后返回结果为0).
在一些较旧版本的MySQL中,表达式被解析为(NOTa)BETWEENbANDc,not的优先级比between高,那么nota优先解析,假如Nota返回0,那么0BETWEENbANDc有可能就返回1了,因为1有可能包含在b和c之间(如:SETsql_mode='HIGH_NOT_PRECEDENCE';SELECTNOT1BETWEEN-5AND5;先执行not1返回0,而0BETWEEN-5AND5会返回1).
通过启用HIGH_NOT_PRECEDENCESQL模式将not设置为高优先级.
*IGNORE_SPACE:该模式允许函数名和小括号之间存在空格,这样在函数名与小括号之间就算有空格,也仍然可以将内部函数名称作为保留字来处理,因此与函数名称相同的标识符必须使用反撇(如:name)来引用,示例:因为有一个COUNT()函数,在以下语句中使用count作为表名会导致错误:(CREATETABLEcount(iINT);),但是,在默认的模式下,在count与括号之间加一个空格,就允许创建了:CREATETABLEcount(iINT);此时,如果使用IGNORE_SPACE模式,那么无论在函数名与小括号之间使用多少个空格,仍然会忽略这些空格,然后把函数名当作一个保留字来处理.
注意:IGNORE_SPACESQL模式适用于内置函数,不适用于用户定义的函数或存储的函数.
无论IGNORE_SPACE是否启用,在UDF或存储的函数名称之后始终允许留出空格且不会把UDF或存储名字当作保留字.
*NO_AUTO_CREATE_USER:该模式下,除非指定了认证信息,否则拒绝GRANT语句自动创建新用户,该模式下grant语句必须使用IDENTIFIEDBY指定一个非空密码或使用IDENTIFIEDWITH指定认证插件,否则不会执行创建用户的功能而直接去mysql.
user表中查找用户并赋权,也就是说,肯定找不到用户直接报错(ERROR1133(42000):Can'tfindanymatchingrowintheusertable).
该模式在5.
7.
8版本之后默认设置,并且不推荐去掉,在将来的版本中将删除该模式而内部直接开启该模式.
另外,也不推荐使用grant语句来创建用户,在将来的版本中将移除grant语句创建用户的功能,使用createuser和dropuser语句代替创建用户,然后再使用grant语句赋予权限(不带identifiedby指定密码的语句).
*NO_AUTO_VALUE_ON_ZERO:该模式影响AUTO_INCREMENT列的处理.
通常,对自增列插入NULL或0值时,会生成自增序列号插入自增列.
NO_AUTO_VALUE_ON_ZERO模式开启之后将限制0值插入自增列,只有NULL才能生成自增序列号.
如果在带有自增属性的表中的自增列已经存在0值,则此模式可能很有用.
(存储0值是不推荐的做法.
)例如,如果您使用mysqldump导出表,然后重新导入实例,则MySQL通常会在遇到0值时生成新的序列号代替0值,这就导致导入的数据与导出的数据不一样了.
此时可以在导入之前导出的备份文件之前启用NO_AUTO_VALUE_ON_ZERO模式可以解决此问题.
目前mysqldump导出的文件中会自动包含一个NO_AUTO_VALUE_ON_ZERO模式(在mysqldump生成的备份文件开头的地方有类似:/!
40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'/;把sqlmode设置为该模式,在文件末尾即数据sql导入完成之后使用/!
40101SETSQL_MODE=@OLD_SQL_MODE/;把sqlmode修改回原来的值),以避免此问题*NO_BACKSLASH_ESCAPES:该模式禁止在字符串中使用反斜杠字符(\)作为转义字符.
启用此模式后,反斜杠成为普通字符*NO_DIR_IN_CREATE:该模式启用时,在创建表时,忽略所有INDEXDIRECTORY和DATADIRECTORY指令.
此选项在复制架构中对从库很有用*NO_ENGINE_SUBSTITUTION:在不使用此模式时(禁用NO_ENGINE_SUBSTITUTION),执行如CREATETABLE或ALTERTABLE之类的语句指定的存储引擎被禁用或指定的存储引擎不支持时将自动转换为默认的存储引擎,并发出警告.
对于CREATETABLE语句(会发出警告,并使用默认的存储引擎创建表;对于ALTERTABLE,会发生警告并忽略更改操作(可以执行语句,但并不会发生修改动作)).
启用NO_ENGINE_SUBSTITUTION后,如果CREATETABLE或ALTERTABLE语句所需的引擎不可用,则会发生错误,并且拒绝执行创建或更改表的语句*NO_FIELD_OPTIONS:在该模式下,SHOWCREATETABLE的输出中不打印MySQL特定的列选项.
在异构数据库之间移植数据库时mysqldump在该模式导出数据使用*NO_KEY_OPTIONS:在该模式下,SHOWCREATETABLE的输出中不打印MySQL特定的索引选项.
在异构数据库之间移植数据库时mysqldump在该模式导出数据使用*NO_TABLE_OPTIONS:在该模式下,SHOWCREATETABLE的输出中不打印MySQL特定的表选项(如ENGINE).
在异构数据库之间移植数据库时mysqldump在该模式导出数据使用*NO_UNSIGNED_SUBTRACTION:在该模式下,无符号限制被忽略,无符号的减法在0-N的情况下将产生负数结果值,另外还有一种建表时使用减法的将产生很诡异的结果,示例:(select示例:SETsql_mode='';SELECTCAST(0ASUNSIGNED)-1;此时直接报错:ERROR1690(22003):BIGINTUNSIGNEDvalueisoutofrangein'(cast(0asunsigned)-1)',修改sqlmode:SETsql_mode='NO_UNSIGNED_SUBTRACTION';SELECTCAST(0ASUNSIGNED)-1;此时返回-1.
建表示例:SETsql_mode='';CREATETABLEtest(c1BIGINTUNSIGNEDNOTNULL);CREATETABLEt1SELECTc1-1ASc2FROMtest;DESCRIBEt1;此时看到t1表的的bigint带有unsigned属性(bigint(21)unsigned),修改sqlmode:SETsql_mode='NO_UNSIGNED_SUBTRACTION';CREATETABLEt2SELECTc1-1ASc2FROMtest;DESCRIBEt2;此时看到t2表的bigint不带unsigned属性(bigint(21))).
*NO_ZERO_DATE:该模式影响服务器是否允许"0000-00-00"作为有效日期插入.
其具体行为还取决于是否启用严格的SQL模式.
如果未启用此模式,则允许零值"0000-00-00"插入,并且插入不产生警告,如果启用此模式,则允许零值"0000-00-00"插入,并且插入会产生警告.
如果启用此模式和严格sql模式,则不允许零值"0000-00-00"插入,插入会产生错误并拒绝执行插入操作,除非包含了IGNORE关键字.
对于INSERTIGNORE和UPDATEIGNORE,允许零值'0000-00-00'插入,插入会产生警告.
从MySQL5.
6.
17版本起,NO_ZERO_DATE已被弃用.
在5.
7.
8及其之后的版本中,默认的sql_mode包含该模式,在后续的版本规划中将在严格的SQL模式中默认包含此模式的行为,注意:该模式仅仅只是限制时间格式全为0的时候,像"0000-00-01"这样的值,或者对于datetime数据类型,类似'0000-00-0000:00:01'值仍然可以插入.
*NO_ZERO_IN_DATE:该模式的作用、后续的版本规划与NO_ZERO_DATE类似,但是该模式只会限制日期中的月和日部分,当启用该模式时,日期中的月和日不能为零值.
只要月和日期部分不为零值就允许插入.
*ONLY_FULL_GROUP_BY:开启该模式之后,selecttargetlist、orderbytargetlist、havingtargetlist中引用的列要么都来自于groupbylist(如:selectid,testfromtestgroupbyid,test;selectid,testfromtestgroupbyid,testhavingidisnullorderbytest,id;selecttargetlist、orderbytargetlist、havingtargetlist在不使用聚合函数的情况下,引用的列必须来自于groupbylist,否则报错),要么非groupbylist都来自于聚合函数的值(如:selectcount(id),testfromtestgroupbytest;),另外:selecttargetlist、orderbytargetlist、havingtargetlist和groupbylist还可以使用表达式或者别名,但必须严格匹配(如:select1+idfromtestgroupby1+id;select1+idasafromtestgroupbya;注意:select1+idfromtestgroupbyid+1;这种把1+反过来写的被人为是不严格匹配,即非法的),再者:前面介绍的三种情况,还可以混用(如:selectid+1,count(test)fromtestgroupbyid+1;selectid+1asa,count(test)fromtestgroupbya;),除此之外,其他情况都人为是非法的(报错信息类似:ERROR1055(42000):Expression#1ofSELECTlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn'xiaoboluo.
test.
id'whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisisincompatiblewithsql_mode=only_full_group_by),开启ONLY_FULL_GROUP_BY之后会拒绝执行查询.
*PAD_CHAR_TO_FULL_LENGTH:默认sqlmode下,在数据检索时,返回结果修剪了CHAR列尾部空格.
如果启用了PAD_CHAR_TO_FULL_LENGTH模式,则在数据检索时不会进行尾部空格修剪,而是将CHAR值填充到其列值定义的长度(检索时保留尾随空格,注意:此模式不适用于VARCHAR列),示例:CREATETABLEt1(c1CHAR(10));INSERTINTOt1(c1)VALUES('xy');SETsql_mode='';SELECTc1,CHAR_LENGTH(c1),length(c1)FROMt1;此时返回结果集中CHAR_LENGTH(c1)和length(c1)结果集是列值尾部被修剪之后的值,都返回2,现在修改sqlmode:SETsql_mode='PAD_CHAR_TO_FULL_LENGTH';SELECTc1,CHAR_LENGTH(c1),length(c1)FROMt1;此时返回结果集中CHAR_LENGTH(c1)和length(c1)结果集是列值尾部空格未被修剪的值,都返回10*PIPES_AS_CONC:该模式将||操作法视为连接操作符(与concat()函数作用一样),而不是视为逻辑或操作符OR*REAL_AS_FLOAT:该模式下将REAL视为FLOAT的同义词,默认情况下是将REAL视为DOUBLE的同义词*STRICT_ALL_TABLES:该模式表示为所有的存储引擎开启严格SQL模式,在该模式下会拒绝无效的值插入*STRICT_TRANS_TABLES:该模式下表示为支持事务的存储引擎开启严格SQL模式,也可能会为不支持事务的存储引擎开启严格SQL模式.
o以上sqlmode有效值可以根据不同的DB设置不同的组合值,有效组合值如下;*ANSI:在5.
6.
x版本中等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE.
在5.
7.
x版本中等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY.
标准ansisql中,where子句中的查询无法聚合引用外部表的字段,如:selecta.
*fromtestasawhereidin(selectmax(a.
id)fromtestasbwhereb.
id=);所以,实际上你设置sql_mode=ansi时,showvariables看到的sql_mode除了等同值之外,还多了一个ansi模式*DB2:5.
6.
x版本中和5.
7.
x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,DB2,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,实际上你设置sql_mode=db2时,showvariables看到的sql_mode除了等同值之外,还多了一个db2模式*MAXDB:5.
6.
x版本中和5.
7.
x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,实际上你设置sql_mode=MAXDB时,showvariables看到的sql_mode除了等同值之外,还多了一个MAXDB模式*MSSQL:5.
6.
x版本中和5.
7.
x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,实际上你设置sql_mode=MSSQL时,showvariables看到的sql_mode除了等同值之外,还多了一个MSSQL模式*MYSQL323:5.
6.
x版本中和5.
7.
x版本中都等同于MYSQL323,HIGH_NOT_PRECEDENCE,实际上你设置sql_mode=MYSQL323时,showvariables看到的sql_mode除了等同值之外,还多了一个MYSQL323模式*MYSQL40:5.
6.
x版本中和5.
7.
x版本中都等同于MYSQL40,HIGH_NOT_PRECEDENCE,实际上你设置sql_mode=MYSQL40时,showvariables看到的sql_mode除了等同值之外,还多了一个MYSQL40模式*ORACLE:5.
6.
x版本中和5.
7.
x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,实际上你设置sql_mode=ORACLE时,showvariables看到的sql_mode除了等同值之外,还多了一个ORACLE模式*POSTGRESQL:5.
6.
x版本中和5.
7.
x版本中都等同于PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,实际上你设置sql_mode=POSTGRESQL时,showvariables看到的sql_mode除了等同值之外,还多了一个POSTGRESQL模式*TRADITIONAL:5.
6.
x版本中和5.
7.
x版本中都等同于STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,实际上你设置sql_mode=TRADITIONAL时,showvariables看到的sql_mode除了等同值之外,还多了一个TRADITIONAL模式o常用的最重要的sqlmode值有:ANSI、STRICT_TRANS_TABLES、TRADITIONALo如果使用的表是InnoDB表,则还同时会受到innodb_strict_mode参数的影响.
该参数对于InnoDB表会开启额外的错误检查.
o全局,会话,动态变量,set类型,5.
6.
5及其之前的版本默认值为空,大于等于5.
6.
6之后的5.
6.
x版本默认值为NO_ENGINE_SUBSTITUTION(5.
6.
x版本中有一些默认值可能为STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,实测5.
6.
21版本是这个默认值),小于等于5.
7.
4之前的5.
7.
x版本默认值为NO_ENGINE_SUBSTITUTION,大于等于5.
7.
5and小于等于5.
7.
6版本默认值为ONLY_FULL_GROUP_BYSTRICT_TRANS_TABLESNO_ENGINE_SUBSTITUTION,5.
7.
7版本默认值为ONLY_FULL_GROUP_BYSTRICT_TRANS_TABLESNO_AUTO_CREATE_USERNO_ENGINE_SUBSTITUTION,大于等于5.
7.
8之后的5.
7.
x版本默认值为ONLY_FULL_GROUP_BYSTRICT_TRANS_TABLESNO_ZERO_IN_DATENO_ZERO_DATEERROR_FOR_DIVISION_BY_ZERONO_AUTO_CREATE_USERNO_ENGINE_SUBSTITUTIONo注意:在其他类型的数据库数据迁移到mysql时,需要设置对用类型数据库的sqlmode,此时可以参考组合值,mysql与mysql之间迁移数据时,需要留意数据源数据库中的sqlmode值是多少,迁移到新的数据库中之后,留意是否需要在新数据库中修改为相同的sqlmode.
另外,NO_ZERO_DATE、NO_ZERO_IN_DATE、ERROR_FOR_DIVISION_BY_ZERO模式在后续的版本中,将被合并到严格sqlmode中,即,不设置该模式的情况下,启用严格sqlmode的效果也等于启用了这些模式.
oPS:什么是严格的SQL模式*严格SQL模式控制MySQL如何处理数据变更语句(如INSERT或UPDATE)中的无效或缺失值.
值无效的原因有:插入值可能有与表定义中的列属性不对应,写入了错误的数据类型值,或者可能插入值超出范围.
缺失值可能是在插入值没有显式定义了DEFAULTnotNULL列的值(对于定义了defaultNULL属性的列,如果插入时没有显式指定列值,则默认插入NULL).
严格模式还会影响DDL语句(如CREATETABLE)*如果严格SQL模式不启用或不生效时,MySQL会为无效或缺失值插入一个内部调整之后的值,并产生警告.
如果需要,在严格模式下,您可以使用INSERTIGNORE或UPDATEIGNORE关键字在严格模式下插入无效值*对于不更改数据的SELECT语句,无效值会在严格模式下生成警告,但不会返回错误*从MySQL5.
6.
11起,尝试为超过最大索引长度的列创建索引时,在严格SQL模式下会产生一个错误并拒绝执行.
在这个版本之前,会给出警告并按照最大索引长度截断来创建一个前缀索引(这与严格SQL模式未启用时的行为相同)*严格SQL模式不影响是否检查外键约束.
如果需要关闭外键约束,可以使用foreign_key_checks=0*如果启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES,严格SQL模式即生效,但两者有些区别:对于事务表,当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES时,数据更改语句中的无效或缺失值会返回错误并中止执行语句并回滚.
对于非事务性表,如果无效值出现在要插入或更新的第一行数据中,则对于两个严格SQL模式行为相同,该语句都会被中止,并且不会对该表做任何数据变更操作.
如果该语句同时插入或修改多行时,并且无效值发生在第二行或及其之后的行,则结果取决于启用了哪种严格SQL模式(启用了STRICT_ALL_TABLES严格模式时,MySQL返回一个错误,忽略剩下未做数据更改的行操作.
但是,由于发生错误之前的行已被插入或更新,所以最终结果是发生了部分更新.
为了避免这种情况,请使用等值匹配.
启用了STRICT_TRANS_TABLES严格模式时,MySQL将无效值转换为与列定义的属性最接近的有效值,并插入内部调整后的值.
如果是缺少某列值,MySQL会为缺少值的列插入对应列的数据类型的隐式默认值.
在STRICT_TRANS_TABLES模式下无效值和缺失值时,MySQL都会生成一个警告而不是错误,并继续处理该语句)*结合ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式,严格模式还会影响零值,零日期值的处理.
1.
1.
26.
read_only=OFF设置是否开启限制非super权限用户更改数据库,当设置为ON时,除了具有super权限的用户之外,其他用户不可对数据库做变更操作o即使启用了read_only,服务器也允许这些操作:*如果服务器是复制架构中的从库,则复制线程允许通过复制的方式执行更新数据.
在复制设置中,在从库上启用read_only可确保从库只接受来自主库的数据更新,而不接收来自客户端发起的数据更新操作.
这可以预防主备数据不一致.
*允许使用ANALYZETABLE或OPTIMIZETABLE语句(5.
7.
17版本实测在read_only开启时不能执行OPTIMIZE语句).
只读模式的目的是防止更改表结构或表数据内容.
分析和优化不属于此类预防的对象,例如,可以使用mysqlcheck--all-databases--analyze执行对只读从库做一致性检查*允许使用createTEMPORARY创建表并做DML、DDL以及drop临时表等操作(对临时表的操作不允许在事务内执行)*允许对日志表(mysql.
general_log和mysql.
slow_log)进行DML操作(5.
7.
17版本实测在read_only参数打开时,不具有super权限的用户不能执行DML)*从MySQL5.
7.
16开始,还允许更新performanceschema下的表,如做UPDATE或TRUNCATETABLE操作(5.
7.
17实测在read_only打开时,不具有super权限的用户不能执行UPDATE,会报拒绝XX用户执行UPDATE)o对主库上read_only的更改不会复制到从库.
该值在主从之间相互独立,设置互不影响o全局,会话,动态变量,默认值为OFFoPS:注意事项:以下场景适用于启用read_only(包括启用super_read_only引起的隐式尝试):*如果您在启用该变量之前,对于表级锁或页级锁的存储引擎表,有任何显式锁定(以LOCKTABLES获取)或具有待处理事务(InnoDB表5.
7.
17版本实测不具有super权限的用户显式开启一个事务并使用forupdate语句加锁,具有super权限的用户仍然能够修改read_only=ON),则尝试修改该变量的操作会失败并发生错误(5.
7.
17版本实测对于MyISAM表是会发生阻塞等待以及使用autocommit=0和innodb_table_locks=1时对InnoDB表使用locktable语句时也是会发生阻塞等待,并不是报错)*对于表级锁或页级锁的存储引擎表,尝试阻止其他客户端持有显式表锁或具有处理的事务时,需要等到释放锁并且事务结束之后才可以修改该变量.
此时启用read_only的尝试会阻塞并进行等待,其他客户端对表锁的请求或事务的请求也将被阻塞,直到read_only被设置成功*对于表级锁或页级锁的存储引擎表,如果存在持有元数据锁的活跃事务,那么修改该变量的操作将被阻塞,直到这些事务结束为止,但当您持有全局读锁定(使用FLUSHTABLESWITHREADLOCK获取的锁)时,可以立即启用read_only,因为它不涉及表锁1.
1.
27.
pid_file=/data/mysql/data/localhost.
localdomain.
pid指定mysql进程id存放文件的路径和名称,默认情况下不指定时在datadir下生成一个hostname.
pido全局变量,只读变量,filename类型1.
1.
28.
port=3306用于指定TCP/IP连接监听的端口号,在unix或类unix系统中,非root用户启动mysqld时只能指定大于1024的端口号(但可以使用0值),使用root用户启动mysqld才能使用1024以下的端口号o全局变量,只读变量,整型值,默认值为3306,取值范围:0~65535o注意:设置为0时,mysqld是直接使用默认的3306端口代替,另外,对于mysql客户端使用-P参数时,如果是走的socket连接(不加-h参数时默认使用socket方式),则mysql客户端会直接忽略-P参数,因为端口是用于TCP/IP方式连接时使用1.
1.
29.
max_user_connections=0设置单个用户连接mysqlserver的最大连接数量o如果createuser、grant语句的max_user_connections选项设置不一致时,针对createuser、grant语句操作的用户最大连接数则以createuser、grant语句的max_user_connections选项指定的值为准.
o全局,会话,动态变量,整型类型,取值范围为0~4294967295,默认值为0,表示不限制,非零值表示限制单个用户同时连接数为指定值.
注意:该变量的会话级别是只读的oPS:注意*默认情况下,没有操作过动态修改max_user_connections变量的情况下,则max_user_connections实际限制时是以createuser、grant语句的max_user_connections选项为准*在操作过动态修改max_user_connections变量之后(setglobalmax_user_connections=N;),那么,对于具有非零值限制的已存在用户,仍然以用户自身的限制值为准,对于零值的已存在用户,则以动态修改之后的globalmax_user_connections限制值为准*以用户自身限制为准时,超过了这个值之后报错信息为:ERROR1226(42000):User'test'hasexceededthe'max_user_connections'resource(currentvalue:2.
以globalmax_user_connections限制值为准时,超过了这个限制值时报错信息为:ERROR1203(42000):UserAAAalreadyhasmorethan'max_user_connections'activeconnections1.
1.
30.
max_sort_length=1024在进行排序操作时,GROUPBY,ORDERBY,和DISTINCT操作都仅仅使用字符串max_sort_length这个字节长度的前缀进行比较(注意:这里指的是单个字段的前缀长度),而忽略剩下的字节长度部分,当增加这个值时,还需要相应的增加sort_buffer_size参数o全局,会话,动态参数,整型值,取值范围4~8388608字节(8M),默认为1024字节1.
1.
31.
lower_case_table_names=ON设置数据库中的数据库名称,表名称是否区分大小写o有0,1,2三个值,含义如下:*设置为0,表名将按指定的字符串存储,且比较时区分大小写*设置为1,数据库名和表名称将转换为小写存储在磁盘上,且比较时不区分大小写*设置为2,表名称将按照给定的字符串存储,但以小写形式进行比较o全局变量,只读变量,整型值,有效值为0~2,在Windows上,默认值为1,在OSX上,默认值为2,在类UNIX系统上,默认值为0o注意:*如果在不区分大小写的文件系统(如Windows或OSX)上运行MySQL,则不应将lower_case_table_names设置为0.
在不区分大小写的文件系统上设置为0,不支持INSERTINTOtb_name.
.
.
SELECT.
.
.
FROMtbl_name组合语句,可能会导致mysql被挂起状况,另外,错误的tbl_name字母大小写.
在使用MyISAM引擎时,使用不同的字母大小写访问表名可能会导致索引损坏*从MySQL5.
6.
27开始,如果在不区分大小写的文件系统上尝试使用--lower_case_table_names=0启动服务器,则会报错并退出*如果使用InnoDB表,则应在所有平台上将此变量设置为1,以强制将名称转换为小写*MySQL5.
6中此变量的设置会影响复制过滤选项在区分大小写方面的行为*在5.
6之前的MySQL版本中,如果从库使用区分大小写的文件系统,则在复制主库和从库上对lower_case_table_names使用不同的设置可能导致复制失败.
这个问题在MySQL5.
6.
1中解决了1.
1.
32.
foreign_key_checks=ON控制是否开启外键约束检查o如果设置为1(默认值),则会检查InnoDB表的外键约束.
如果设置为0,外键约束将被忽略,但有一些例外.
如:当删除重新创建父表时,如果父表定义不符合引用该表的子表外键约束要求(父表中被子表引用的字段的列数据类型和属性要一致,被子表引用的列必须要有索引等),则返回错误(ERROR1215(HY000):Cannotaddforeignkeyconstraint).
同样,如果外键定义不正确,则操作将返回错误o从MySQLNDBCluster7.
3.
2开始,此变量对NDB表的影响与InnoDB表具有相同的效果.
通常,如果你的数据表中使用了外键,那么您在正常操作期间使此设置保持启用,以强制引用完整性.
但生产环境不建议使用外键约束o在父表与子表中的数据需要重新载入时,需要禁用外键约束,以避免父/子关系所要求的顺序在重新载入时不符合要求而发生错误o全局,会话,动态变量,布尔型,默认值为ONo注意:*将foreign_key_checks设置为1不会触发对现有表数据的扫描.
因此,在foreign_key_checks=0时添加到表中的行将不会验证父子表的数据一致性,且重新设置为1时,只对新插入的数据才进行一致性验证.
*使用foreign_key_checks=0之后,如果删除外键约束所需的索引会使父子表的数据可能处于不一致的状态,且可能会导致在表数据重新加载时发生外键检查失败.
为了避免这个问题,此时你需要删除外键约束,然后再删除索引*创建外键约束时,子表的外键字段没有索引会自动创建一个辅助索引,但是,父表在子表创建外键时,一定要有索引,否则报错:ERROR1215(HY000):Cannotaddforeignkeyconstraint,另外,如果子表创建外键时*创建外键约束时,父表中的记录不能有null值,否则报错:ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(xiaoboluo.
#sql-1498_2a,CONSTRAINT#sql-1498_2a_ibfk_1FOREIGNKEY(id)REFERENCESparent(test))1.
1.
33.
local_infile=ON设置是否允许使用LOADDATAINFILE语句的LOCAL方式(即LOADDATALOCALINFILE).
如果该变量被禁用,客户端不能在LOADDATA语句中使用LOCAL.
虽然此变量的默认值为true,但是LOADDATAINFILELOCAL是否实际允许取决于MySQL的编译方式(编译时是否编译支持了local_infile),以及客户端上连接时是否使用了--local-infile选项o全局变量,动态变量,布尔型,默认值为true1.
1.
34.
disabled_storage_engines=''此变量指定哪些存储引擎不能用于创建表或表空间.
例如,要防止创建新的MyISAM或FEDERATED表,使用disabled_storage_engines="MyISAM,FEDERATED"o默认情况下,disabled_storage_engines为空,表示没有引擎被禁用,多个引擎需要禁用时可以使用逗号分隔(不区分大小写).
被禁用的引擎对应的表并且不能再使用ALTERTABLE.
.
.
ENGINE或ALTERTABLESPACE.
.
.
ENGINE这种DDL语句.
否则报错:ER_DISABLED_STORAGE_ENGINEodisabled_storage_engines不会限制现有表的其他DDL语句,如CREATEINDEX,TRUNCATETABLE,ANALYZETABLE,DROPTABLE或DROPTABLESPACE.
这是为了平滑过渡,以便使用ALTERTABLE.
.
.
ENGINEallowed_engine的方式来把禁用的存储引擎表修改并迁移到其他允许的存储引擎上.
o允许将default_storage_engine或default_tmp_storage_engine系统变量设置为disabled_storage_engines变量设置的禁用的存储引擎.
但这可能会导致应用程序运行不正常或失败,所以,需要留意这个问题odisabled_storage_engines设置了非空值时,不影响使用如:--bootstrap,--initialize,--initialize-insecure,--skip-grant-tables这些启动选项o全局变量,只读变量,string类型,默认值为空,5.
7.
8版本引入1.
1.
35.
skip_show_database=OFF如果没有SHOWDATABASES权限,则可以防止用户使用SHOWDATABASES语句.
如果您担心用户能够看到不属于用户自己的数据库,那么这个选项默认值可以提高安全性.
如果变量值为ON,SHOWDATABASES语句仅允许具有SHOWDATABASES权限的用户,但该语句会显示所有数据库名称(包括不具有访问权限的数据库名称).
如果值为OFF,则允许所有用户使用SHOWDATABASES,且仅显示用户具有SHOWDATABASES及其具有访问权限的数据库的名称.
o全局变量,只读变量,布尔型,默认值为OFF1.
1.
36.
skip_grant_tablesmysqld启动选项,非systemvariables,在mysqld启动时跳过加载系统字典库(包括mysql库中的权限字典表、禁止加载用户自定义函数、scheduledevents,以及禁止使用installplugin语句安装插件),使用该选项启动mysqld后,任何人都可以免密码登录数据库.
要在不重启mysqld的情况下重新加载系统字典库,可以使用如下方法:oshell命令行使用mysqladminreload或mysqladminflush-privilegeso连接到mysqlserver中执行sql:FLUSHPRIVILEGES;o注意:使用mysql_upgrade命令在更新完成之后会直接刷新系统字典库1.
1.
37.
system_time_zone=CST设置mysqlserver的时区.
当服务器启动时,从操作系统继承时区.
也可以使用mysqld_safe脚本的--timezone启动选项指定.
注意:system_time_zone变量与time_zone变量不同.
虽然它们可能具有相同的值,但是后者用于初始化每个客户端连接的时区.
o全局变量,只读变量,string类型,默认值为从操作系统中继承(操作系统的时区可以使用date命令查看)1.
1.
38.
secure_file_priv=null此变量用于限制dataimport和export操作,例如由LOADDATA和SELECT.
.
.
INTOOUTFILE语句和LOAD_FILE()函数的操作.
这些操作只允许具有FILE权限的用户o该变量有如下有效值:*空串:如果为空,则该变量无效,这是不安全的设置*directname:如果设置为目录的名称,则只允许将导入和导出操作的数据存放目录限制为该值指定的目录.
启动mysql时该目录必须存在,服务器不会自己创建*null:如果设置为NULL,则将禁用导入和导出操作.
从MySQL5.
6.
34起允许此值(5.
6.
34之前的版本没有这个值)o在MySQL5.
6.
34之前,默认情况下该变量为空.
从5.
6.
34开始,默认值可以自己指定,源码编译时,要显式指定默认的secure_file_priv值,可以使用INSTALL_SECURE_FILE_PRIVDIRCMake选项,如果编译时没有设置这个选项,则默认情况下mysql不支持loadimport、export数据o从MySQL5.
6.
34开始,服务器在启动时检查secure_file_priv的值,如果该值不安全(如设置为空串),或者该值是datadir或datadir的子目录,或所有用户可访问的目录,则会向错误日志写入警告.
如果将secure_file_priv设置为不存在的路径,则服务器会将错误消息写入错误日志并退出启动o全局变量,只读变量,默认值5.
6.
33及其之前的版本为空串,5.
6.
34及其之后的5.
6.
x版本为null,5.
7.
5及其之前的5.
7.
x版本为空串,5.
7.
6及其之后的版本默认值为nulloPS:*如果loaddata语句使用了local子句,则客户端使用TCP远程连接mysqlserver时,没有file权限仍然能够导入文本文件,这个时候是非常危险的,因为local子句的内部原理是从客户端的主机读取文本文件并传送到server端的/tmp目录并保存为一个临时文件,再执行loaddata语句的.
另外,要使用local子句,还需要看server端启动是否关闭了local_infile选项(如果不指定该选项,则服务端默认为ON),mysqlclient连接时是否关闭了local_infile选项(如果不指定该选项,则客户端默认为ON),local_infile在server或client端任意一端关闭都不能使用local子句,会报错误:ERROR1148(42000):TheusedcommandisnotallowedwiththisMySQLversion*如果loaddata语句不使用local子句,则这个时候用户必须要有file权限才能够执行导入文本文件(并且只能够导入server端的本地文本文件),如果没有file权限,可能报没有file权限的错误,也可能报错:ERROR1045(28000):Accessdeniedforuser'test'@'%'(usingpassword:YES)*如果不想这么麻烦(因为要限制客户端使用local子句在没有file权限的时候使用loaddata语句,需要在server端使用local_infile=OFF来关闭,不使用local子句时,如果用户没有file权限,那很显然不能够使用loaddata语句,但是如果还想限制不使用local子句但由具有file权限的用户怎么办),可以使用参数secure_file_priv=null,设置为null时,全面禁止使用loaddata语句(不管使用local子句还是不使用都不允许执行loaddata语句)1.
1.
39.
log_timestamps=SYSTEM此变量控制错误日志信息的时间戳与时区,以及查询日志和慢查询日志写入文件时的时间戳与时区,但不会影响查询日志和慢查询日志写入表中时的时间戳与时区(mysql.
general_log,mysql.
slow_log).
从这些表中检索的行中的时间信息可以通过CONVERT_TZ()函数或通过设置会话time_zone系统变量来转换为本地系统时间戳与时区,允许设置的值有UTC(默认值)和SYSTEM(本地系统时区),时间戳是使用ISO8601/RFC3339格式编写的:YYYY-MM-DDThh:mm:ss.
uuuuuuuzoneo全局变量,动态变量,枚举类型,有效值为UTC、SYSTEM,默认值为UTC,5.
7.
2版本引入,在5.
7.
2之前时间戳使用的是本地系统时间,而不是UTC1.
1.
40.
max_execution_time=0控制SELECT语句的执行超时(单位为毫秒).
如果值为0,则表示不启用超时omax_execution_time适用如下:用于控制会话中执行的SELECT语句的执行超时时间,但不包括MAX_EXECUTION_TIME(N)优化器提示语句,或者当该系统参数设置为0时,该参数失效,表示不控制SELECT超时omax_execution_time适用于只读SELECT语句.
存储过程或函数中的SELECT语句会忽略max_execution_time系统参数的值,即存储过程或函数中的SELECT不受该参数的影响o全局,会话,动态变量,整型值,默认值为0,5.
7.
8版本引入1.
1.
41.
slave_exec_mode=STRICT该参数用于复制架构中的从库自动跳过主键冲突(1062:duplicate-key)和记录没有找到(1023:no-key-found)的复制错误,可动态设置此变量值为IDEMPOTENT,但设置之后要stopslave;startslave重启一下复制才会生效o有效值如下:*IDEMPOTENT模式:自动跳过主键冲突(1062:duplicate-key)和记录没有找到(1023:no-key-found)的复制错误.
这种模式应该用于多主复制,循环复制和其他一些特殊的复制场景.
*STRICT模式:默认模式,适用于大多数其他情况o多主复制,循环复制以及NDB群集复制的其他特殊复制方案建议设置为IDEMPOTENT模式.
NDB群集强制slave_exec_mode使用IDEMPOTENT,设置任何其他值都会将其视为IDEMPOTENT,在将NDB存储引擎复制到InnoDB时也需要使用IDEMPOTENT模式o注意:该参数不建议在配置文件中修改默认值STRICT为IDEMPOTENT,如果的确经常碰到主键冲突或没有找到记录的错误,并且能够确认这些错误是可以忽略的,那么可以采用在计划任务中放入一个脚本,每十分钟执行一次动态修改该系统参数为IDEMPOTENT的方式,并重启复制,以使其自动跳过主键冲突或没有找到记录的复制错误.
o全局变量,动态变量,默认值为STRICT(但在NDB存储引擎中默认值为IDEMPOTENT),有效值为:STRICT、IDEMPOTENT,枚举类型,1.
1.
42.
offline_mode=OFF控制是否设置服务器为"离线模式"o离线模式具有以下特点:*没有SUPER权限的已连接的客户端用户,如果连接不断开,则不影响,如果连接断开则将在下次请求连接时直接断开连接请求,并给出适当的错误返回信息*具有SUPER权限的的用户没有任何影响*允许复制从线程继续复制数据,即对内部复制线程没有任何影响o只有具有SUPER权限的用户才能修改此变量,在离线模式下,拒绝访问的客户端将收到ER_SERVER_OFFLINE_MODE错误(ERROR3032(HY000):Theserveriscurrentlyinofflinemode)o全局变量,动态变量,布尔型,默认值为OFF,5.
7.
5版本引入1.
1.
43.
show_compatibility_56=OFF控制是否开启information_schema库中的global_status、session_status、global_variables、session_variables表查询功能o全局变量,动态变量,布尔型,默认值在5.
7.
7及其之前的5.
7.
x版本中默认为ON,5.
7.
8及其之后的版本中默认为OFF,5.
7.
6版本引入,该变量在将来的版本中将移除,该变量是在过渡版本中用于控制即将废弃的information_schema库下的global_status、session_status、global_variables、session_variables表查询功能查询功能使用的.
oPS:在5.
7.
6版本中引入该变量之后,information_schema库下的global_status、session_status、global_variables、session_variables表查询功能迁移到了performance_schema库下(同时,原先的showstatus和showvariables等查询语句对应的数据源也由information_schema变更为performance_schema),且将原来的功能细分并增加了一些对应status和variables的按会话线程、主机、用户分别统计的status表,同时把可以在会话级别修改的变量放到了一个独立的表中,对于showslavestatus输出语句还增加了一些该语句中无法查看到的一些状态变量放到replication_开头的一些表中,这些表及其对应的作用列表如下(只列出再用且比较有用的部分表):*performance_schema.
global_variables:仅用于存放全局生效的globalsystemvariables的表,对应原来information_schema下的global_variables表*performance_schema.
session_variables:会话级别生效的systemvariables,包含会话级别的systemvariables以及没有会话级别的全局systemvariables(在5.
7.
7及其之前的版本不包含没有会话级别的全局systemvariables,在5.
7.
8中修正),对应information_schema的session_variables*performance_schema.
variables_by_thread:仅用于存放会话级别生效的sessionsystemvariables*performance_schema.
global_status:仅用于存放globalstatusvariables,对应information_schema.
global_status表*performance_schema.
session_status:会话级别生效的状态statusvariables,包含会话级别的statusvariables以及没有会话级别的全局statusvariables(在5.
7.
7及其之前的版本不包含没有会话级别的全局statusvariables,在5.
7.
8中修正),对应information_schema的session_status*performance_schema.
status_by_account:仅包含sessionstatus变量且按照帐号(username@host格式)聚合统计结果记录的表(聚合指的是针对每个sessionstatus的聚合)*performance_schema.
status_by_host:仅包含sessionstatus变量且按照host聚合统计结果记录的表(聚合指的是针对每个sessionstatus的聚合)*performance_schema.
status_by_thread:仅包含sessionstatus变量且按照thread-id聚合统计结果记录的表(聚合指的是针对每个sessionstatus的聚合)*performance_schema.
status_by_user:仅包含sessionstatus变量且按照username聚合统计结果记录的表(聚合指的是针对每个sessionstatus的聚合)*performance_schema.
replication_applier_status:复制SQL线程的状态信息,其中COUNT_TRANSACTIONS_RETRIES列表示从库事务重试次数,CHANNEL_NAME代表着复制通道名称,在5.
7中由于支持多源复制,严格来说,应该叫做应用applier线程*performance_schema.
replication_connection_status:复制连接线程的状态信息,其中LAST_HEARTBEAT_TIMESTAMP表示最近一次复制心跳正常接收到的时间,COUNT_RECEIVED_HEARTBEATS表示复制心跳重试了多少次*performance_schema.
replication_applier_configuration:复制SQL线程的配置信息,该表中记录着CHANNEL_NAME和DESIRED_DELAY,在5.
7中由于支持多源复制,严格来说,应该叫做应用applier线程*performance_schema.
replication_applier_status_by_coordinator:协调器线程的状态信息*performance_schema.
replication_applier_status_by_worker:worker线程的状态信息1.
1.
44.
tx_read_only=OFF设置默认事务访问模式o该变量可以直接在会话中设置,也可以使用SETTRANSACTION语句简介设置(如:set[session|global]transactionisolationlevelxxx[readwrite|readonly];该语句如果不指定事务访问模式,默认为读写模式,另外,在5.
6.
35及5.
7.
17版本中实测,read_only选项设置为ON时,无法控制如临时表的创建和对临时表的DDL、DML等,设置tx_read_only=ON只读事务可以禁止对临时表的DDL操作,但仍然无法针对temporarytable禁止DML,另外,tx_read_only设置为ON时,任何存储引擎都无法执行DDL)o要在启动时设置默认事务访问模式,请使用--transaction-read-only启动选项指定(如:--transaction-read-only=[OFF|ON])o全局,会话,动态变量,布尔型,5.
6.
5版本引入,默认值是OFF(代表事务可读/写,默认),设置为ON时代表事务只读,如果事务中有可能导致数据发生变更的语句,则报错:1.
1.
45.
sql_slave_skip_counter=0在从库上用于跳过来自主库的events,该参数设置的值代表跳过events的个数o虽然该选项可以动态修改,但是修改之后不会立即生效,需要stopslave;startslave;才会生效,每跳过一个events该变量的值就会减一,当跳过指定events个数之后该值将减少为0o在多源复制拓扑中的slave上使用该变量进行跳过events时,必须指定一个通道号,因为该变量只能用于跳过一个主库的events,不能用于同时跳过多个主库的events,可以在使用setglobalsql_slave_skip_counter=N语句设置好需要跳过的events数量之后,使用startslaveforchannel'channel_name';指定channel号启动,以表示是需要跳过该channel的主库eventso在MySQL5.
7.
11版本之前,此选项与基于GTID的复制模式不兼容,并且--gtid-mode=ON时不能将其设置为非零值.
但可以使用设置空事务的方式跳过一个事务,在MySQL5.
7.
11和更高版本中可以使用该参数来跳过events,对于DDL语句,设置为1即可跳过,但是对于DML语句的事务,可能需要设置为2才能跳过事务o全局变量,动态变量,整型值1.
1.
46.
old_alter_table=0控制是否使用旧的方式执行DDL语句o设置为1时:表示启用此变量,server处理ALTERTABLE操作时不使用优化(onlineddl),server使用老的方式:先创建一个临时表,逐行copy数据到临时表,然后将临时表重命名为原始表,然后再删除旧表.
在MySQL5.
0及更早版本中使用(注:5.
1的InnoDB插件和5.
5中除了创建和删除索引可以使用FastIndexCreation特性之外,其他类型的DDL操作都需要使用copy方式,5.
6及其后续的版本中也有少数DDL操作需要使用copy方式)o全局,会话,动态变量,布尔型,默认值为OFF1.
1.
47.
max_prepared_stmt_count=16382此变量限制服务器中prepared语句的总数,可用于预防潜在的大量prepared语句消耗大量内存导致mysqldOOM的DDOS攻击,如果该值设置为低于当前server中的prepared语句数量,则现有语句执行不受影响,但在当前prepared语句数量大于了该参数的限制值,则就不能再执行prepare语句,直到当前的prepare语句降低到该变量值之下才可以继续执行prepare语句o全局变量,动态变量,整型值,默认值为16,382,取值范围为0~1048576,将值设置为0时将禁用prepare语句oPS:这里说的prepare语句不是两阶段提交中的prepare,这里说的prepare语句实际上就是一个预编译语句,先把SQL语句进行编译,且可以设定参数占位符(例如:符号),然后调用时通过用户变量传入具体的参数值,如果一个语句需要多次执行而仅仅只是where条件不同,那么使用prepare语句可以大大减少硬解析的开销,prepare语句有三个步骤,预编译prepare语句,执行prepare语句,释放销毁prepare语句,prepare语句.
1.
1.
48.
key_buffer_size=8MMyISAM表的索引块缓冲大小(MyISAM只能缓存索引,数据需要依赖文件系统层缓存),该缓冲块(默认的keybuffer)可以被所有线程共享,keybuffer也叫做keycacheokey_buffer_size的最大有效值在32位平台上为4GB-1字节,在64位平台上最大值依赖于操作系统、单个进程允许最大值、硬件数量的限制,该变量给定的值只代表一个最大阀值,并不是mysql进程启动时就占用设定的内存大小,所以,用于keybuffer的实际内存大小通常会少于该变量设定的大小o在大量使用MyISAM引擎的MySQL实例中,您可以通过增加该变量值来提高索引访问在keybuffer中的读写命中率进而提高表访问效率,但最大值不建议超过物理内存的25%,如果您的值太大(例如,超过物理内存的50%,操作系统中的内存页操作可能会变得非常慢.
因为MyISAM引擎依靠操作系统的文件系统缓存来进行表数据行读,所以需要为文件系统缓存保留一些内存空间).
除了MyISAM引擎使用的keybuffer之外,还应该考虑任何其他存储引擎的内存使用要求o您可以通过SHOWSTATUS语句查看状态变量Key_read_requests,Key_reads,Key_write_requests和Key_writes的值来判断keybuffer的性能(使用率).
Key_reads/Key_read_requests的比例通常应小于0.
01.
如果您主要业务是更新和删除,则Key_writes/Key_write_requests比例应该接近1,但你的业务主要是更新或者使用DELAY_KEY_WRITE表选项,则该比值可能会小一些o可以使用系统变量key_buffer_size和key_cache_block_size结合状态变量Key_blocks_unused来计算keybuffer的使用率:1-((Key_blocks_unused*key_cache_block_size)/key_buffer_size),该值是一个近似值,因为keybuffer中的一些空间会被内部分配给管理结构使用.
例如:块大小和指针大小的管理结构数据.
随着块大小的增加,keybuffer的命中率可能会降低.
更大的块会导致更少的读取操作数(因为每个读取操作一次性可以获得更多的key)o可以创建多个MyISAMkeybuffer,每个单独的keybuffer大小限制为4GBo全局变量,动态变量,整型值,32为平台取值范围为8~4294967295字节,默认值为8388608(8M),64位平台取值范围为8~系统限制大小,默认值为8388608(8M)1.
1.
49.
plugin-load='xx.
so'用于在mysqlserver启动时加载插件库,该配置是mysqlserver的启动选项,并非systemvariableso该启动选项加载插件是一次性的,重启server之后,下次仍然还需要使用该启动选项才能重新加载插件,这与installplugin命令不同,installplugin命令执行时会在mysql.
plugins表中添加一条对应加载库的记录,下次重启server时就直接从该表中读取安装好的库的记录来加载库文件,而无需重新执行installplugin命令o要注意:如果使用了skip-grant-tables选项,则mysqlserver在启动时会忽略mysql.
plugins表,也就是说此时不会加载库文件,但是,如果使用plugin-load启动选项指定要加载的库文件,即使同时使用了skip-grant-tables选项,也仍然可以加载plugin-load启动选项指定的库文件o示例:plugin_load="validate_password.
so;rpl_semi_sync_master=semisync_master.
so;rpl_semi_sync_slave=semisync_slave.
so"1.
1.
50.
metadata_locks_hash_instances=64设置元数据锁集合分成多少个不同的散列表,以便允许高并发场景下不同连接访问不同对象的时使用不同的锁散列,以减少争用.
o在MySQL5.
7.
4中,改变了元数据锁的实现机制,使得这个变量不再需要,该变量被弃用,在未来的MySQL版本中将被删除o全局变量,只读变量,整型值,默认值为8,取值范围为:1~10241.
1.
51.
slave_max_allowed_packet=1G此变量设置从库SQL和I/O线程的最大数据包大小,以便在基于行的复制模式中减少因为较大的事务更新产生的数据包大小超过max_allowed_packet的大小而导致复制报错的问题.
如果设置此变量,则会立即生效,包括正在运行的所有复制通道o该全局变量必须是1024字节的正整数倍.
如果将其设置为某个不是1024正整数倍的值,则该值将向下舍入为大于你指定的非法值的下一个1024的倍数的值(例如:将slave_max_allowed_packet设置为0,则mysq会自动更正为1024字节,同时发出设置值被截断的警告信息)oslave_max_allowed_packet变量的值也可以在启动时使用--slave-max-allowed-packet选项来设置.
o全局变量,动态变量,整型值,默认值为1G,取值范围为:1024字节~1GB1.
1.
52.
default_authentication_plugin=mysql_native_password设置默认的用户身份验证插件.
o有效值为:*mysql_native_password:使用mysqlnative认证插件*sha256_password:使用SHA-256认证插件*注意:如果此变量的值不是mysql_native_password,因为MySQL5.
5.
7之前的mysql客户端不支持mysql_native_password身份验证协议之外的协议,所以这个时候mysql客户端无法连接到serverodefault_authentication_plugin值影响如下:*CREATEUSER或GRANT语句创建用户时,如果未明确指定用户认证插件,则默认使用该变量指定的值*old_passwords系统变量会影响使用mysql_native_password或sha256_password身份验证插件的帐户的密码哈希值.
old_passwords会根据default_authentication_plugin设置的不同认证插件来调整密码哈希方法所需的值o如果客户端成功连接到Server,且使用了与服务端不同的密码认证插件,则在执行CREATEUSER和GRANT语句创建用户并设置密码时会报错,可以使用--default-authentication-plugin命令行选项指定与server相同的密码认证插件o全局变量,只读变量,枚举类型,默认值为mysql_native_password(8.
0中默认值为sha256_password),有效值为:mysql_native_password、sha256_password,MySQL5.
7.
2版本引入1.
1.
53.
tmpdir=/home/mysql/data/mysqldata1/tmpdir用于临时文件和临时表的目录.
此变量可以设置多个路径(每个路径可以是独立的磁盘,这样就可以在多个物理磁盘之间分配负载)以循环方式使用.
多个路径在unix系统上使用冒号分割(:),在WIN上使用分号分割(;)o如果MySQLServer为从库,则不应将tmpdir参数执行一个基于内存文件系统的目录上,也不应该指向一个主机重启时会清除数据的目录上.
因为从库重新启动时,会从临时目录下读取一些临时文件用于恢复复制中断之前的一些未完成的操作(例如:未完成的LOADDATAINFILE操作).
如果MySQLServer重新启动时,它所需要的临时文件在临时目录中丢失,则未完成的操作将恢复失败,报复制失败的错误.
这个时候,可以使用slave_load_tmpdir系统变量为复制单独设置一个在持久化设备上的临时目录.
一旦设置该参数之后,将不再使用tmpdir参数指定的路径存放临时文件.
o全局变量,只读变量,值为字符串的路径值,可以设置多个路径,多个路径之间使用冒号分隔(win下使用分号分隔)1.
2.
连接设置1.
2.
1.
interactive_timeout=1800指的是mysql在关闭一个交互的连接之前所要等待的秒数(交互连接如mysqlguitool中的连接)o全局,会话变量,动态变量,默认值为28800(8个小时),整型值1.
2.
2.
wait_timeout=1800指的是MySQL在关闭一个非交互的连接之前所要等待的秒数o全局,会话变量,动态变量,默认值为28800(8个小时),整型值.
1.
2.
3.
lock_wait_timeout=1800MDL元数据锁的超时时间o注意,凡是需要获取MDL锁的操作都受到这个超时参数的影响,不单单是DDL语句,包含在表上的DML、DDL操作,以及视图、存储过程、存储函数、locktables,flushtablewithreadlock、handler语句等.
但不适用于隐式访问系统表的语句(但适用于使用select、update显式访问系统表的语句),如:grant和revoke等o该参数不适用于延迟插入的语句,因为发起延迟插入的会话并不会收到超时的通知.
o全局,会话变量,动态变量,默认值为31536000(一年),整型值,取值范围1~31536000,单位秒1.
2.
4.
skip_name_resolve=1设置是否跳过域名解析,只使用IP和localhost作为主机地址o如果为OFF,则mysqld在检查客户端连接时会使用DNS解析主机名(会优先使用/etc/hosts中的记录进行解析,如果不存在对应的记录,就会走DNS查找,如果DNS查找失败).
如果为ON,则mysqld只使用IP解析客户端.
在这种情况下,授权表中的所有主机列值必须为IP地址或localhosto全局变量,只读变量,布尔型,默认值为OFF1.
2.
5.
max_connections=512此参数控制允许连接到mysql的最大数量,默认是151,如果状态变量connect_errors_max_connections不为零,并且一直在增长,就说明不断有连接请求因为数据库连接达到最大允许而连接失败,应该考虑增大max_connections参数的值,另外,由于每个session操作数据库表时都要占用文件描述符,数据库连接本身也要占用文件描述符,因此在调整这个参数时,也要对应调整操作系统的open-files-limit设置.
建议设置为历史最高位的80%,太大了容易导致所有连接卡死.
注意:这个参数完全控制不具有super权限的用户的并发连接数,另外mysql还为具有super权限的用户额外预留了一个连接.
即mysql的最大连接数其实是:max_connections+1o全局变量,动态变量,整型值,默认为151,取值范围为1~1000001.
2.
6.
max_connect_errors=1000000如果一个主机连续登录mysql失败超过这个参数(如果在没有超过这个次数之后重试连接成功过一次,那么就会重置错误计数器),则server断拒绝这个主机继续尝试登录,可以用flushhosts命令刷新这个登录失败的主机记录,并调整这个参数的大小避免这种情况的发生o全局变量,动态变量,整型值,默认值为100,64位平台取值范围为1~184467440737095516151.
2.
7.
net_write_timeout=60客户端向server端请求数据后,server端返回client所需数据并在向client端发送数据时(server端向客户端写入数据时),如果一个数据包写入客户端之后,客户端没有响应,那么server端会等待该参数定义的时长,如果超过这个时长后客户端还没有给出响应,那么server端将断开client连接.
这与net_read_timeout参数的作用类似o全局,会话,动态变量,整型值,最小值为1,默认值为601.
2.
8.
net_retry_count=10设置在通信端口上的读取或写入中断时,重试多次放弃之后仍然失败就放弃.
由于FreeBSD系统内部中断信号会发送到所有线程,因此在FreeBSD操作系统上该变量的值应该设置一个较高的值o全局,会话,动态变量,整型值,默认值为10,64位平台取值范围为:1~184467440737095516151.
2.
9.
net_read_timeout=30设置client向server端写入数据时(server端向客户端读取数据),如果server端读取一个数据包client端超过net_read_timeout定义的时间没有响应,则断开client连接.
这与net_write_timeout参数作用类似o全局,会话,动态变量,整型值,最小值为1,默认值为301.
2.
10.
net_buffer_length=65535设置每个客户端会话线程相关的连接缓冲区和结果集缓冲区初始大小,两个缓冲区在连接创建开始就设置为net_buffer_length变量给出的大小,后续根据需要可动态放大到max_allowed_packet系统变量指定的字节大小,每个SQL语句执行完成之后,结果集缓冲区自动缩小到net_buffer_length变量指定的大小o该变量通常不应该更改,但是如果内存很少,则可以将其设置小一些o全局变量,会话变量(会话级别为只读变量),动态变量,整型值,默认值为16K,取值范围为:1024~1048576字节(1K~1M)1.
2.
11.
skip_networking=OFF设置是否跳过TCP/IP连接方式,如果服务器仅允许本地(非TCP/IP)连接,则设置此选项为ON.
在Unix上,本地连接使用Unix套接字文件.
在Windows上,本地连接使用命名管道或共享内存.
设置仅允许本地连接时,使用--skip-networking启动选项或者在配置文件中将此变量设置为ONo全局变量,只读变量,布尔型,默认值为OFF1.
3.
表缓存性能设置1.
3.
1.
table_open_cache=4096控制全局打开表数(注意是表的数据文件,且使用了文件描述符),通常是有多少个表,把表数量乘以两三倍就可以了(每一个sql执行线程至少需要打开一个缓存表,这个参数就是控制所有SQL执行线程可打开缓存表的数量,应该与最大连接数max_connections以及每个连接执行关联查询中所涉及表的最大个数(用N来表示)来设定:即,max_connections*N作为该参数的值).
table_definition_cache变量和table_open_cache设置一样大(如果在showprocesslist里经常发现openingtable那么可能就是这两个变量设置小了(要注意,调整这两个变量的时候,也要注意系统open-files-limit的设置和innodb_open_files、open_files_limit变量),另外,状态变量Table_open_cache_hits和Table_open_cache_misses也可以观察到,这两个状态变量是5.
6.
x开始才有).
另外还有4个变量(5.
6.
x之前的版本可以使用下面4个变量查看):o默认值为2000,整型值.
全局变量,动态变量,取值范围为1~524288mysql>showstatuslike'Open%_table%';|Variable_name|Value||Open_table_definitions|512||Open_tables|512||Opened_table_definitions|51||Opened_tables|51|4rowsinset(0.
00sec)#Open_tables=>opening#当前在table_open_cache中缓存着的表数量,当这个值接近table_open_cache时,就表示缓存数量已经被用完了,再有表打开时opend_tables变量就会增加#Opened_tables=>opened_total#实例启动以来,每次SQL查询都需要重新打开表,执行完后再把这个表关掉,而无法使用到table_open_cache的表数量#缓存机制当MySQL访问一个表时,如果该表在缓存中已经被打开,则可以直接访问缓存;如果还没有被缓存,但是在MySQL表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区;如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放,使用表缓存的好处是可以更快速地访问表中的内容.
#参数调优一般来说,可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables,判断是否需要增加table_cache的值(其中open_tables是当前打开的表的数量,Opened_tables则是已经打开的表的数量).
即如果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,那就要考虑增加这个值的大小了.
还有就是Table_locks_waited比较高的时候,也需要增加table_cache.
#比较适合的值:Open_tables/Opened_tables>=0.
85Open_tables/table_cacheflushtables;1.
3.
2.
table_definition_cache=4096与table_open_cache作用类似,但是是缓存表定义文件.
frm的数量.
5.
6.
8之前的默认值是400,之后版本默认是自动计算的,计算公式(400+(table_open_cache/2)),表定义缓存比table_open_cache使用更少的空间,且不使用文件描述符.
o对于InnoDB来说,table_definition_cache可以对InnoDB数据字典缓存中打开的表实例数量进行软限制.
即,如果打开的表实例的数量超过table_definition_cache设置,则LRU机制标记表实例并最终从数据字典缓存中删除.
该限制有助于缓解大量内存被极少使用的表实例占用的情况,注意:具有缓存元数据的表实例数可能高于table_definition_cache定义的限制,因为带有外键关系的InnoDB系统表实例和具有父子关系的表实例不会放在LRU列表上,无法通过从内存中逐出这些表实例来减少table_definition_cache的数量.
o此外,table_definition_cache对单个InnoDB文件一次可以打开表的数量也具有软限制,且innodb_open_files也同时控制这个数量.
如果同时设置了table_definition_cache和innodb_open_files,则使用两者最高值设置.
如果两者都没有显式设置,则使用具有较高缺省值的table_definition_cache.
如果打开的表空间文件句柄数超过table_definition_cache或innodb_open_files定义的限制,则LRU机制在表空间文件LRU列表中搜索已完全刷新并且当前长时间未使用的文件.
每次打开新的表空间时都会执行此过程.
如果存在"非活动"表空间就会执行关闭表空间文件.
o默认值为-1,表示自动计算,整型值.
全局变量,动态变量,固定值取值范围为:400~5242881.
3.
3.
table_open_cache_instances=32table_open_cache实例的数量,5.
6.
6版本新增,默认情况下,table_open_cache_instances的值为1,表示只有一个table_open_cache实例,此时,DML、DDL语句访问表时,需要把整个table_open_cache实例锁住,可以通过table_open_cache_instances设置大于1的值来将table_open_cache划分为多个实例.
这样DML访问就不需要把所有的table_open_cache实例锁住,在一些场景下可以提高性能.
但DDL语句仍然会把所有的table_open_cache实例锁住.
o默认值为1(在MySQL5.
7.
8及其之后的版本中默认值为16),整型值,全局变量,只读变量,取值范围为1~641.
4.
session相关的内存设置1.
4.
1.
read_buffer_size=16MMyISAM表顺序扫描的缓存大小,如果要经常顺序扫描MyISAM表,可以通过增大read_buffer_size的值来改善性能,但是这个变量是每会话独占,如果设置太大,会造成内存浪费,甚至物理内存耗尽o在以下场景中适用于所有的存储引擎o此选项也在以下场景中用于所有存储引擎:*控制ORDERBY行排序时的临时文件(不是临时表,而是排序的文件)中缓存索引的大小*控制批量插入到分区中的数据大小*控制缓存嵌套查询的结果集大小omemory存储引擎中该参数的值决定MEMORY表的内存块大小,注意是块大小,而不是memory表可以使用的内存总大小,memory引擎表的数据总大小限制由max_heap_table_size参数控制.
o全局,会话变量,默认为128K,最大为2G,整型值.
注意:设置值必须为4K的倍数,否则会截取你设定值的最接近4K的倍数的值作为该参数的值.
取值范围为:8200~2147479552字节oPS:为啥这个变量官方描述的是用于MyISAM,而在某些场景也适用于所有存储引擎呢因为在这些场景下,mysql内部保存这些临时数据仍然使用的是MyISAM表,在mysql5.
7.
x的版本中,这些内部临时表可以设置使用InnoDB存储引擎,如果临时表设置为InnoDB存储引擎,则这个参数的大小就无需多关注了(除非你使用MyISAM引擎和memory引擎).
1.
4.
2.
read_rnd_buffer_size=32MMyISAM表排序缓存的缓存大小(注意,是key-sorting索引排序),如果需要对MyISAM表做排序,可以增大read_rnd_buffer_size改善性能,这个变量同样的每会话独占,也不能设置太大.
o该变量控制的buffer在从任何存储引擎读取数据的时候,都会用于缓存读取的数据,包括MRR优化特性也会使用o全局,会话变量,动态变量,默认值为256K,最大值为2G,整型值.
取值范围1~2147483647字节(2G)1.
4.
3.
sort_buffer_size=32M用于存放排序数据的缓存大小,超过这个大小就会使用文件排序.
如果通过showglobalstatus看到Sort_merge_passes的值很大,可以考虑通过适当调整sort_buffer_size的值来增大排序缓存区(另外增加max_sort_length参数的值时可能也需要增加sort_buffer_size参数的值),改善带有orderby和groupby子句的sql性能.
sort_buffer_size是每线程独占分配(每个执行排序操作的会话才会分配),不要设置过大,最好是设置一个较小的全局值,如果碰到较大表做排序,就对这个session设置较大的值o该参数适通用于所有存储引擎,最小值必须要足够大,至少要在排序缓冲中能够存放15个排序元组o5.
6.
4之前的版本总是在有排序操作时直接分配整个排序缓冲给会话,从5.
6.
4版本开始优化器尝试计算排序数据的大小来分配排序缓冲的大小给会话.
o在linux上,大于256K或者2M的排序缓冲大小可能显著降低内存分配的性能o全局变量,会话变量,动态变量,默认值在不同版本中频繁变化,大于5.
6.
4版本号的64位版本默认值为256K,整型值.
64位平台取值范围为:32768~18446744073709551615字节1.
4.
4.
tmp_table_size=64M内存临时表超过这个大小后,就会把内存临时表转换为磁盘临时表,线程级别的参数,不要设置过大,一般全局设置不大于100M就可以了,否则很容易发生内存溢出,但是,如果说在临时大数据查询的时候,可以打开一个会话临时设置大一点,避免产生临时磁盘表.
或者,可以使用索引查询尽量减少返回的数据量.
o内部用于控制内存临时表大小使用,是适用于用户创建的内存表和临时表o实际限制从tmp_table_size和max_heap_table_size两个变量的的值中取较小值.
如果内存中临时表超过这个限制,MySQL会自动将其转换为磁盘上的MyISAM表.
如果您执行许多高级GROUPBY查询并且用到大量内存,请增加tmp_table_size的值(如果必要,也请增加max_heap_table_size的值)o状态变量Created_tmp_disk_tables持续增加时,需要增加tmp_table_size的值oCreated_tmp_disk_tables/(Created_tmp_disk_tables+Created_tmp_tables)*100%>10%的话,就需要注意适当提高tmp_table_size的大小,但是不能设置太大,因为它是每个session都会分配的,可能会导致OOM(outofmemoryo全局变量,会话变量,动态变量,默认值为16M,整型值.
取值范围为:1024~18446744073709551615字节1.
4.
5.
max_heap_table_size=64M默认是16M,可以根据需要加大,在定义内存表时,可以使用max_rows子句指定表的最大行数来约定内存表的数据量.
该参数是用于控制用户创建的内存表的数据大小.
o设置此变量不会影响任何现有的MEMORY表,除非使用如CREATETABLE或使用ALTERTABLE或TRUNCATETABLE语句重新创建表、修改表结构或清空表数据.
服务器重新启动也会将现有MEMORY表的数据最大大小限制设置为全局max_heap_table_size值.
o此变量也与tmp_table_size结合使用以限制内部内存表的大小.
如果设置与tmp_table_size大小不一样,则控制内部内存临时表以较小的为准.
o全局变量,会话变量,动态变量,64位版本默认值为16M,整型值.
取值范围为:16384~1844674407370954752字节1.
4.
6.
join_buffer_size=32M用于存放join查询中间结果的缓存大小.
对于无法通过索引进行联结操作的查询,可以通过适当增大join_buffer_size的值来改善联结查询性能(但最好是想办法让join使用到索引来提高性能).
join_buffer_size都是每线程独占分配,不要设置过大(除非能够使用BKA特性,当使用BKA时,join_buffer_size的值的大小决定了向存储引擎的每个请求中包含的键值对的多少,缓冲区越大,联结操作的右表即被驱动表的顺序I/O访问就越多,BKA的作用就是把随机I/O访问变为顺序I/O访问,这可以显着提高性能),最好是设置一个较小的全局值,如果碰到较大表做联结查询,或者是比较复杂的联结表查询,就对这个session设置较大的值o对于简单查询的索引扫描、索引范围扫描以及因为不能使用到索引做全表扫描的join查询时,无论返回的数据多大,都会分配该参数的最小值128字节那么多joinbuffer做查询o对于简单的两个表之间的查询分配一个joinbuffer,但是对于复杂的多表join查询且不能使用索引的时候,可能会分配多个joinbuffero全局变量,会话变量,动态变量,默认值在不同版本中频繁变化,大于5.
6.
6版本号的64位版本默认值为256K,最小值为128字节,整型值.
取值范围为:128~18446744073709547520字节1.
4.
7.
thread_cache_size=64为加快数据库连接的速度,mysql会缓存一定数量的客户端服务线程以备重用,通过这个参数可以控制mysql缓存客户端服务线程的数量,可以通过计算线程cache的失效率:Threads_created/Connections状态变量比值来衡量thread_cache_size参数的设置是否合理,该值越接近1,说明线程cache的命中率越低,就应该考虑增加这个参数的值o当一个客户端访问完成断开连接时,如果线程缓存中的客户端连接线程没有达到thread_cache_size定义的值,则这个客户端线程会被put到缓存中,当另外一个客户端新建连接时,如果线程缓存不为空(即有客户端线程缓存在线程缓存中),就会从缓存中取出这个客户端连接进行重用.
在大多数情况下,如果你的并发连接不高的时候,这个值对性能的影响可能就看不出来,但是当有大量并发连接时,通过增大这个值可以缓解高并发连接的压力.
o默认值为-1,表示自动计算(计算公式:8+(max_connections/100)),全局变量,动态变量,整型值.
固定值取值范围为:0~16384oPS:这个变量对嵌入式服务器(libmysqld)没有任何影响,而在MySQL5.
7.
2在嵌入式服务器中移除了这个变量1.
5.
日志设置1.
5.
1.
slow_query_log=1打开、关闭慢查询日志功能o参数slow_query_log_file可设置慢查询日志路径和文件名,如果不设置,则默认在datadir目录下命名为hostname-slow.
logo参数log_output控制慢查询日志的输出路径.
默认为FILE,如果慢查询日志功能打开,则记录到文件,NONE值表示文件和表都不记录,TABLE表示记录到表(slow_log表),注意,log_output参数也同时控制普通查询日志的输出,关于查询日志的相关说明参考general_log参数o全局变量,动态变量,默认为OFF,布尔型.
1.
5.
2.
log_queries_not_using_indexes=1控制没有使用索引的Query是否也计入慢查询日志.
全局变量,动态变量,默认为OFF,布尔型值.
测试环境可以开启,线上环境不建议开启,因为开启之后,像set,commit之类的没有用到索引的SQL都会进行记录1.
5.
3.
log_slow_admin_statements=1控制是否把管理语句慢的也计入慢查询日志,这个参数打开可能导致一些没有意义的sql被记录到慢日志里.
线上环境不建议开启,排查问题时可以临时打开.
全局变量,动态变量,默认为OFF,布尔型值.
1.
5.
4.
log_slow_slave_statements=1控制是否把从库执行慢的语句也计入从库的慢查询日志中.
全局变量,动态变量,默认为OFF,布尔型值.
设置这个变量不会立即生效,需要重启当前的slave线程,或者对于5.
7.
x的版本对后续启动的复制通道生效.
1.
5.
5.
expire_logs_days=90设置二进制日志的过期天数,过了指定天数的日志将被自动删除,可动态修改o如果设置了非0值,则在mysqld启动和日志刷新时,可能执行清理超过定义天数的binlogfileo全局变量,动态变量,默认值为0(代表不会自动清理binlog),整型值,取值范围为0~991.
5.
6.
long_query_time=2定义执行时间超过多少秒的查询会被记录到慢查询日志中,如果一个查询花费的时间超过这么多秒,服务器会增加Slow_queries状态变量.
如果启用了慢查询日志功能,则将该查询记录到慢查询日志文件.
此值是实时测量的,而不是CPU时间,long_query_time的最小值和默认值分别为0和10.
该值的精度可以到微秒.
但对于记录到文件的慢日志,写入内容包括微秒部分的时间.
对于记录到表的慢日志,只写入整数倍;微秒部分被忽略(注意:经测试,从MySQL5.
7.
x版本开始计入慢查询日志表中的执行时间与计入到慢查询日志文件中的时间单位一样,包含了微妙部分,在MySQL5.
6.
x及其之前的版本中计入慢查询日志表中的时间单位才是记录单位为秒的整型值.
例如:5.
6及其之前的版本中记录慢查询执行时间的格式为00:00:00,而在5.
7及其之后的版本中记录的时间格式为00:00:00.
000000).
o全局,会话,动态变量,默认值10,最小值0,没有最大值限制,numeric类型.
取值范围:0~无穷大1.
5.
7.
binlog_rows_query_log_events=1binlog_format=row时控制是否记录用户原生SQL的参数到binlogfile中obinlog_rows_query_log_events系统变量仅影响基于行的日志记录.
启用时,在MySQL5.
6.
2或更高版本中的服务器会将信息性日志事件的原始SQL(如行查询日志事件)写入其二进制日志.
该信息可用于调试和相关目的;如当从库通过row格式执行出错时,可以通过查看这些原始SQL来得知在master上对应的event在做什么操作.
o这些原始SQL的事件通常被读取二进制日志的MySQL程序忽略,因此在从备份复制或恢复时不会产生任何问题.
如果要查看它们,请使用mysqlbinlog的--verbose选项两次来增加详细程度级别,即"-vv"或"--verbose--verbose".
例如:mysqlbinlog-vvmysql-bin.
000001,打印的结果中每一个begin;commit之间的开头部分都会多一个这种记录原始SQL的Rows_queryeventso全局,会话,动态变量,默认值为false,布尔型值1.
5.
8.
log_slave_updates=1将master传输过来的变更操作,再次记录成本地binlog,用于做二次复制,当做中继分发节点,但是要注意,从库中记录的这个binlog与主库中的binlog内容格式不一样,从库中的binlog包含了主库和从库的server-id,为了防止复制的循环重复执行.
在GTID复制模式下,所有数据库都要开启log_slave_updates参数,是因为为了方便快速同步数据,如果涉及到切换,那么可以快速地把二进制日志进行同步(因为所有服务器都有记录完整的binlog)o全局变量,只读变量,默认值为false,布尔型值o注:该参数在5.
7.
x版本中如果关闭,备库在复制重放来自主库的事务时,每一个事务提交时都会实时更新mysql.
gtid_executed表1.
5.
9.
general_log=0打开、关闭普通查询日志功能,如果设置为1或者ON,则数据库所有的SQL都会被记录下来o参数general_log_file可设置普通查询日志路径和文件名,如果不设置,则默认在datadir目录下命名为hostname.
logo参数log_output控制查询日志的输出路径.
默认为FILE,如果查询日志功能打开,则记录到文件,NONE值表示文件和表都不记录,TABLE表示记录到表(general_log表),注意,log_output参数也同时控制慢查询日志的输出,关于慢查询日志的相关说明参考slow_query_log参数.
o普通查询日志可以使用sql_log_off系统变量关闭当前会话或者全局的普通查询日志记录功能,该变量默认值为0,表示启用记录功能(setglobal|sessionsql_log_off=0|1;)o全局变量,动态变量,默认为OFF,布尔型.
线上环境不建议打开,在排查问题时可以在session级别打开.
1.
5.
10.
log_bin=mysql-bin是否开启binlog功能o默认关闭,如果显式指定该参数,则都会被认为开启binlog记录功能,如果设置值不带路径,则默认binlog文件存放在datadir目录下,以指定值为前缀,后跟上数字序列号组成,如:xxx.
N,这个N是递增的数字编号,从000001开始,没切换一个日志数字就+1.
o注意:该变量在showvariables的查询结果中,仅仅只是告知了binlog记录功能是否开启(ON或OFF),并不显示该变量具体设置的值,具体设置的值被设置到了log_bin_basename参数上,该变量默认值为:datadir+'/'+hostname+'-bin',注意:如果log_bin参数实际设置的值有带路径(并不是只有文件名前缀,如:/home/mysql/data/mysqldata1/binlog/mysql-bin,那么此时log_bin_basename的值等于log_bin参数实际设置的值,如果log_bin参数不带路径,则log_bin_basename使用自身的默认值来拼接一个完整的路径,以便生成binlogfile.
另外,log_bin_basename只是一个系统只读参数,不能写到配置文件中.
还有,sql_log_bin这个参数是会话级别用于临时修改binlog是否需要记录,但必须在一个事务开始之前修改,不能事务内修改,且log_bin参数未配置时,该变量默认值也会为ON,so,不要被这个默认值迷惑了,sql_log_bin为ON时要生效的前提是log_bin参数必须设置)1.
5.
11.
sql_log_bin=ON此变量控制是否开启对二进制日志的日志记录功能.
默认值为1(做日志记录).
用于在会话级别临时开关binlog记录功能,如果要更改当前会话的日志记录,请更改此变量的会话值.
用户必须具有SUPER权限o在MySQL5.
6及其之后的版本中,不可能在事务或子查询中设置@@session.
sql_log_bin.
o会话变量,动态变量,布尔型,默认值为ON,该变量要使得binlog记录功能生效,必须打开log_bin参数,否则就算sql_log_bin=ON也不会记录binlog.
1.
5.
12.
log_bin_index=/data/mysql/data/mysql-bin.
indexbinlog索引文件的路径+名称,索引文件中存放着binlog文件的路径和名称(log_bin如何定义路径,这个索引文件中就如何存放binlog路径,如:log_bin只定义了mysql-bin,则那么index文件中记录的就是.
/mysql-bin.
000001这样的记录条目,如果log_bin定义的是/path/mysql-bin,则index文件中记录的就是/path/mysql-bin.
000001这样的记录条目o全局变量,只读变量,默认值为datadir+log_bin定义的前缀+index1.
5.
13.
log_syslog_tag=指定要添加到服务器标识符的标记,该标记用于将mysql的错误日志输出到系统日志中,除非启用了log_syslog系统变量,否则指定此变量的值无效o默认情况下,服务器标识符是没有标签的mysqld.
如果指定了tag_val值,则使用中杠连接字符将指定的值附加到服务器标识符mysqld上,从而产生一个新的标识符为mysqld-tag_valo在Windows上,已经存在的标记不能使用,且必须使用具有管理员权限的帐户运行服务器,以便有权限允许把标签字符串创建到注册表项中.
如果标签已经存在,则不需要提升权限来修改注册表o全局变量,动态变量,string类型,默认值为空串,空串时当log_syslog系统变量设置为ON时,写入系统日志中的mysql标签就是mysqld,该变量在5.
7.
5版本引入1.
5.
14.
log_syslog=OFF设置是否将错误日志输出写入syslog(在Unix和类Unix系统上)或事件日志(在Windows上).
o全局变量,动态变量,布尔型值,在Unix和Unix系统上,该变量默认为OFF,在Windows上,事件日志输出默认启用,这与旧的MySQL版本一致1.
5.
15.
log_syslog_include_pid=ON在syslog中写入错误日志信息时,是否同时写入mysqlserver的进程ID,该变量需要在log_syslog=ON时才生效o全局变量,动态变量,布尔值,默认值为ON1.
5.
15.
log_error=/data/mysql/data/error.
log指定错误日志的位置(路径和名称),默认情况下是标准输出路径(datadir+hostname.
err)o全局变量,只读变量,filename类型o注意:因为该变量是只读变量,不能动态打开,需要注意如下情况*如果数据库是使用mysqld_safe或者service脚本启动的,且使用该选项指定了错误日志文件的名称,则你需要注意不要误删除了错误日志文件,否则数据库将无法正确启动(因为mysqld_safe程序不负责重建错误日志,是mysqld程序负责创建的),此时可以使用:flusherrorlogs语句来重新生成一个错误日志文件(需要在误删除文件且数据库未重启的时候才有效).
如果数据库是直接使用mysqld程序启动的,则无论是否使用该选项指定错误日志文件,误删除错误日志文件的时候不影响数据库的启动,因为mysqld程序发现错误日志不存在时会自动重建.
但是有一定区别,区别在于指定了该选项时使用指定的名称重建,未指定的时候,使用默认的错误日志文件名重建.
*如果是使用innobackupex备份恢复工具恢复到一个并未初始化过的实例目录中,且启动的时候使用了mysqld_safe或者service方式启动,且使用该选项指定了错误日志文件名称,则启动时会因为缺少错误日志文件而无法启动,会报类似的错误:mysqld_safe:error:log-errorsetto'/home/mysql/data/mysqldata1/log/error.
log',howeverfiledon'texists.
Createwritableforuser'mysql'.
此时,可以手工touch一下/home/mysql/data/mysqldata1/log/error.
log然后chown修改宿主即可*如果错误日志没有任何权限,那就悲剧了,就算你尝试使用mysqld_safe和mysqld启动,都无法看到任何的报错信息,只会看到mysql一启动就停止的信息.
就算使用mysqld--log-syslog记录到系统日志中也无法看到有效的报错信息,也只能看到一启动就停止的信息1.
5.
16.
log_error_verbosity=3此变量控制错误日志的详细等级,将error,warnings,note(info)消息写入错误日志中.
o有效值如下:*1:仅记录error信息到错误日志中*2:记录error和warning信息到错误日志中*3:记录error、warning、note(info)信息到错误日志中o全局变量,动态变量,整型值,默认值为3,5.
7.
2版本引入oPS:在5.
7.
2版本之前使用log_warnings系统变量1.
5.
17.
log_warnings=是否向错误日志记录额外的警告信息.
该变量默启用(1),可以通过将其设置为0来禁用.
如果值大于0,则服务器记录基于语句的日志记录不安全的语句的消息.
如果设置大于1,则新的连接和访问被拒绝时进行重连的错误日志将被记录到错误日志中.
o在5.
6.
4及其之后的版本中,该变量为全局,动态变量(删除了会话级别的变量),整型值,默认值为1,64位平台下取值范围为:0~18446744073709551615,5.
7.
2及其之后的版本默认值为2,另外,从MySQL5.
7.
2开始,该变量的功能由log_error_verbosity系统变量来控制(log_warnings系统变量和--log-warnings命令行选项已被弃用,将在未来的MySQL版本中被删除),在两个变量共存时,分配给log_warnings的值也会同时将值分配给log_error_verbosity,相关对应关系如下:*设置log_warnings=0相当于log_error_verbosity=1(仅error记录)*设置log_warnings=1等价于log_error_verbosity=2(记录error和warning)*设置log_warnings=2(或更高)等价于log_error_verbosity=3(记录error,warning,note),如果指定了大于2的值,则将log_warnings设置为21.
5.
18.
log_output=FILE该选项用于设置普通查询日志和慢查询日志输出的目的地.
选项值可以为TABLE,FILE或NONE中的一个或多个,多个值使用逗号分隔.
oTABLE表示将日志记录到表中:mysql.
general_log和mysql.
slow_log表oFILE表示将日志记录到磁盘文件中:--general_log和--slow_query_log选项指定的路径下的文件,如果不指定,则默认在datadir下,普通日志文件名为hostname.
log,慢查询日志文件名为hostname-slow.
logoNONE表示禁用日志记录.
如果选项值中存在NONE,则NONE值优先于任何其他值生效.
o如果TABLE和FILE同时指定,则日记在表和磁盘文件中都记录o全局变量,动态变量,集合类型,默认值为FILE,有效值为:FILE、TABLE、FILE,TABLE、NONE1.
5.
19.
min_examined_row_limit=0当一个查询语句在存储引擎中检查的数据行数超过了这个变量设置的行数时,该语句就会被记录到慢查询中,默认情况下,该变量为0,表示不限制返回记录数量o全局会话变量,动态变量,整型值,默认值为0,取值范围:32位平台为0~4294967295,64位平台为0~184467440737095516151.
5.
20.
log_short_format=1用于控制慢查询的长度,这是mysqld的启动选项,不是系统参数,启用该参数之后,会使得慢查询日志记录更少的信息omysqld启动选项,布尔型,默认值为FALSE1.
5.
21.
log_throttle_queries_not_using_indexes=0如果启用log_queries_not_using_indexes参数,则log_throttle_queries_not_using_indexes变量如果同时启用,则log_throttle_queries_not_using_indexes变量设置的值会限制每60秒内可写入慢查询日志的查询语句次数.
默认值为0(默认值)表示"无限制"o全局变量,动态变量,整型值,默认值为0,取值范围为int类型的有效值范围1.
5.
22.
transaction_write_set_extraction=XXHASH64定义用于生成与写入事务关联的哈希算法.
在组复制架构中,哈希值将用于分布式冲突检测和处理.
所以64位系统上运行组复制架构,建议将其设置为XXHASH64,以避免不必要的哈希冲突导致用户事务认证失败回滚o全局,会话变量,动态变量,枚举类型,默认值为OFF,MySQL5.
7.
6版本引入,5.
7.
13版本之前有效值为OFF、MURMUR32,5.
7.
13及其之后的版本有效值为OFF、MURMUR32、XXHASH64o注意:当binlog_transaction_dependency_tracking系统变量设置为WRITESET或WRITESET_SESSION值时,transaction_write_set_extraction不能修改,也不能设置为OFF,只能使用MURMUR32、XXHASH64值(MySQL8.
0.
2及其以上版本默认值为XXHASH64)1.
5.
23.
binlog_transaction_dependency_tracking=COMMIT_ORDER控制事务依赖模式,让从库根据主库写入binlog中的committimestamps或者writesets并行回放事务(引入该参数之后,binlog的格式记录的内容中增加了时间戳和writesets信息)o有三个取值:*COMMIT_ORDERE:使用5.
7本来就支持的Groupcommit的方式决定事务依赖*WRITESET:使用WriteSet的方式决定判定事务直接的冲突,发现冲突则依赖冲突事务,否则按照COMMIT_ORDERE方式决定依赖*WRITESET_SESSION:在WRITESET方式的基础上,保证同一个session内的事务不可并行o全局变量,动态变量,枚举类型,默认值COMMIT_ORDER,有效值:COMMIT_ORDER、WRITESET、WRITESET_SESSION,MySQL5.
7.
22版本引入(>=8.
0.
1)oPS:*WRITESET是一个hash数组,大小由参数binlog_transaction_dependency_history_size决定,参数transaction_write_set_extraction决定hash算法,可选值:OFF、MURMUR32、XXHASH64,默认值XXHASH64,如果WriteSet记录了事务的更新行信息,决定commit_parent时,使用事务自己的sessionWriteSet和historyWriteSet进行比对,找到最近的冲突行,设为commit_parent.
如果WriteSet找不到commit_parent,则还是使用COMMIT_ORDERE决定commit_parent*如果transaction_write_set_extraction为OFF,则binlog_transaction_dependency_tracking变量的值只能设置为COMMIT_ORDER,设置其他值会报错.
另外,如果binlog_transaction_dependency_tracking的当前值为WRITESET或WRITESET_SESSION,则无法更改transaction_write_set_extraction的值1.
5.
24.
binlog_transaction_dependency_history_size=25000设置最近发生数据修改的事务在内存中保存的用于计算hash值的最大数据行数,达到上限之后,历史记录将被清除o全局变量,动态变量,整型值,默认值为25000,取值范围为:1~1000000,MySQL5.
7.
22版本引入oPS:请根据数据库配置高低设置binlog_transaction_dependency_history_size,性能有富余的实例可以适当调大该参数,找到更小的commitparent,提高备库回放并行度.
内存和CPU紧张的实例最好避免在WriteSet上消耗太多资源.
binlog_transaction_dependency_history_size过大,不光消耗内存,还会降低冲突查询的效率1.
6.
innodb引擎设置1.
6.
1.
innodb_buffer_pool_size=160G决定InnoDB存储引擎表的数据和索引的最大缓存区大小,和MyISAM不同,InnoDB的缓存池是同时缓存数据和索引,在保证操作系统和其它程序有足够内存的情况下,这个参数越大越好,缓存命中率越高,访问磁盘I/O就越少,性能也就越高,在一个专用的数据库服务器上,可以将80%的物理内存分配给这个参数,但是要注意,别过大而导致swap产生.
o使用如下公式计算InnoDB缓存池的命中率,如果命中率太低,则考虑扩充内存,增加此参数值(innodb_buffer_pool_reads/innodb_buffer_pool_read_requests是状态变量):(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)*100o较大的缓冲池需要较少的磁盘I/O多次访问同一个表数据.
在专用数据库服务器上,可以将缓冲池大小设置为机器物理内存大小的80%.
在配置缓冲池大小时,请注意以下潜在问题,可能需要缩小缓冲池的大小*物理内存竞争可能导致在操作系统中分页*InnoDB为缓冲区和控制结构保留额外的内存,因此实际bufferpool的总分配空间比指定的缓冲池大小要大约大10%.
*缓冲池的地址空间必须是连续的,因为不连续的地址空间可能在Windows系统上加载要求具有特定地址的DLL文件时出现问题.
o初始化缓冲池的时间与其缓冲池的大小成正比.
在具有大缓冲池的实例上,初始化时间可能很大.
为了减少初始化时间,可以在mysqld服务器关闭时保存缓冲池状态,并在mysqld服务器启动时加载缓冲池状态恢复.
请参见第14.
6.
3.
7节"保存和恢复缓冲池状态".
o全局变量,只读变量(5.
7.
x版本可动态修改),默认值为128M,整型值,最大值需要看操作系统CPU的寻址能力,32位CPU最大值可设置为4294967295(2^32-1),64位CPU可以设置最大值为18446744073709551615(2^64-1),但CPU的架构和操作系统实际上可能会限制这个最大值的实际大小.
当缓冲池的大小大于1GB时,可以将innodb_buffer_pool_instances设置为大于1的值,这样可以提高繁忙服务器的可伸缩性oPS:*在5.
7.
x中,当增加或减少缓冲池大小时,操作将以块为单位执行.
块大小由innodb_buffer_pool_chunk_size(5.
7.
5版本引入的参数,全局变量,静态变量,取值范围为:1048576~innodb_buffer_pool_size/innodb_buffer_pool_instances字节)配置选项定义,默认值为128MB.
*缓冲池大小必须大于等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的值.
如果将缓冲池大小更改为小于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的值,则缓冲池大小会自动调整为大于等于innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的值*innodb_buffer_pool_size在5.
7.
5版本之后可以动态调整(5.
7.
4及其之前的版本不能动态调整)可以动态设置,这样可以在不重新启动服务器的情况下调整缓冲池大小.
Innodb_buffer_pool_resize_status状态变量打印在线调整缓冲池大小操作的状态1.
6.
2.
innodb_buffer_pool_instances=16调整缓存池实例数量,减少内部对缓存池数据结构的争用o由于mysql内部不同线程对InnoDB缓存池的访问在某些阶段是互斥的,这种内部竞争也会产生性能问题,尤其在高并发和bufferpool较大的情况下(将缓冲池分为多个单独的实例可以减少不同线程读取和写入缓存页面时的争用,从而提高并发性.
,每个页面存储到缓冲池中或从缓冲池读取时都使用散列函数随机分配给其中一个缓冲池实例.
每个缓冲池实例都独立管理自己的空闲列表(freelists),刷新列表(flushlists),LRUs和连接到缓冲池的所有其他数据结构,并由其自己的缓冲池互斥保护临界),所以,InnoDB的缓存系统引入了innodb_buffer_pool_instances配置参数,对于较大的缓存池,可以适当增大这个参数的值(默认是1,最大为64),可以降低并发导致的缓存访问冲突,改善性能,InnoDB缓存系统会将参数innodb_buffer_pool_size指定的大小平均分配为此参数指定的数量个bufferpoolo此选项在innodb_buffer_pool_size大于1G时生效,小于1G时就算该参数设置大于1也不会生效即只有一个bufferpool实例.
另外,innodb_buffer_pool_size设置大于1G时,请尽量保证每个instance的大小足够1G.
o全局变量,只读变量,默认值在innodb_buffer_pool_size小于1G时为1,大于1G时为8,在32位WIN平台上是自动计算的(如果innodb_buffer_pool_size大于1.
3GB,则innodb_buffer_pool_instances的默认值为innodb_buffer_pool_size/128MB,每个块具有单独的内存分配请求.
小于1.
3G时则instance默认值为1.
选择1.
3GB作为边界,在该边界处存在32位Windows无法分配单个缓冲池所需的连续地址空间的重大风险)o注意,在5.
6版本中有一个BUG,可能导致在innodb_buffer_pool_size小于1G时showvariables查看到的innodb_buffer_pool_size_instance默认值为8(Bug#18343670),你可以通过showengineinnodbstatus语句查看真实的缓冲池实例数量,如果有多个真实的缓冲池实例存在,则输出结果中会有一个INDIVIDUALBUFFERPOOLINFO输出部分.
1.
6.
3.
innodb_buffer_pool_dump_at_shutdown=1如果开启该参数,停止MySQL服务时,InnoDB将InnoDB缓冲池中的热数据页列表保存到本地硬盘中,默认在共享表空间文件的路径下(innodb_data_home_dir参数定义的路径下)的ib_buffer_pool文件(innodb_buffer_pool_filename控制文件名称:默认datadir下的ib_buffer_pool,路径不可更改只可更改文件名.
文件内容为文本格式,每个热数据页对应一行记录,格式为tablespace_id,page_id)o全局变量,动态变量,布尔型值,在5.
6.
x版本中默认关闭,在5.
7.
6及其之前的5.
7.
x版本中默认关闭,在5.
7.
7及其之后的版本中,默认开启oPS:当innodb_fast_shutdown设置为大于1时,在关闭MySQL会导致无法导出ib_buffer_pool文件,即使参数innodb_buffer_pool_dump_at_shutdown的默认值为ON也不行,这个时候要导出ib_buffer_pool文件,要么使用setglobalinnodb_buffer_pool_dump_now=ON手动触发导出,要么setglobalinnodb_fast_shutdown=1或者0,再关闭MySQL,否则无法自动导出ib_buffer_pool这个文件,在下次启动时会导致加载这个文件报错:[ERROR]InnoDB:Cannotopen'/data/mysqldata3306241/innodb_ts/ib_buffer_pool'forreading:Nosuchfileordirectory1.
6.
4.
innodb_buffer_pool_load_at_startup=1如果在my.
cnf中设置为ON,则就会在mysqld启动之后,重新加载缓冲池热数据页列表ib_buffer_pool文件o全局变量,只读变量,布尔型值,5.
6.
x、5.
7.
6及其之前的的版本默认为OFF,5.
7.
7及其之后的版本默认为ON1.
6.
5.
innodb_io_capacity=10000innodb_io_capacity参数设置由InnoDB后台任务执行的I/O活动的上限,例如从缓冲池刷新页面以及从更改缓冲区合并数据.
oinnodb_io_capacity限制是所有缓冲池实例的总限制.
当刷新脏页时,限制值按照缓冲池数量平均分配oinnodb_io_capacity应该设置为磁盘设备每秒可以执行的I/O操作数.
理想情况下,保持设置尽可能低,但不要低到影响后台线程活动.
如果值太高,则会导致数据快速从缓冲池中被删除,但可以对insertbuffer快速地插入缓冲区提供显着的益处o全局变量,动态变量(动态修改该值需要有super权限),默认值为200,整型值,对于有更高I/O能力的磁盘设备,比如:SAS阵列可以设置为1000,高端的SSD盘或SSD阵列可以设置为4000,PCIE卡可以设置为10000或更多,但不建议超过20000,过大的值实践表明并没有明显的好处,除非你能证明你的系统在20000的IOPS下仍然I/O性能不足.
o注意:该变量设置的数值是InnoDB的总I/O限制,包括bufferpool中的脏页刷新,修改插入缓冲并写入到辅助索引等,如果bufferpoolinstance有多个,在刷新脏页时,这个I/O限制值是平均分配到每个instance的,另外,在5.
7.
8之后的版本新引入了一个参数innodb_flush_sync,该参数默认为ON,该参数设置为ON时,会导致在做checkpoint时的I/O活动突增的情况下,忽略InnoDB_io_capacity设置的限制值.
1.
6.
6.
InnoDB_flush_method=O_DIRECT控制着InnoDB数据文件及redolog的open、write(write的表现根据open时使用的参数不同而不同)、flush模式o类unix系统下常用值有O_DIRECT、fsync、O_DSYNC,不常用的值有littlesync、nosync、O_DIRECT_NO_FSYNC(littlesync、nosync这两个值用于内部性能测试,如果使用个人承担风险,O_DIRECT_NO_FSYNC可以使用,但不适合XFS文件系统),默认为fsync.
*fsync:默认为Null,为null时默认使用fsync.
在MySQL5.
1.
24之前,该参数默认选项为fdatasync,此时InnoDB使用fsync()系统调用来刷新数据和日志文件,fsync()负责将一个文件描述符(什么是文件描述符,它是unix、类unix系统打开文件的一种方式,应该相当于打开文件的一个句柄一样)打开的文件写到物理设备,而且是真正的同步写,没有写完成就不会返回,而且写的时候讲文件本身的一些元数据都会更新到物理设备上去,比如atime,mtime等等.
为了避免将该参数的fdatasync选项名称与fdatasync()系统调用混淆,在MySQL5.
1.
24及其之后的版本中,fdatasync选项名被更改为fsync,使之与实际调用的fsync()系统调用匹配,InnoDB使用fsync()系统调用刷新(flush)数据和日志文件(数据和日志文件在write这一步并不需要真正写到磁盘才算完成,可能写入到操作系统buffer中就会返回完成,因为open数据和日志文件时不带o_direct参数),真正完成是flush操作,buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘.
所以对于InnoDB来说,可以保障数据的安全性,但要注意:fsync会使用oscache(文件系统缓存)来刷新数据文件,增大了内存开销.
另外,InnoDB本身也有Buffer,这就导致了与oscache有一定的重复性*O_DSYNC:为O_DSYNC时,写日志操作是在write这步就算完成,而数据文件的写入是在flush这步通过fsync系统调用时完成(InnoDB使用O_SYNC打开和刷新日志文件,使用fsync()刷新数据文件.
InnoDB不直接使用O_DSYNC,因为它在Unix上出现了许多各种各样的问题)*O_DIRECT:InnoDB打开数据文件时使用O_DIRECT选项(open调用带上O_DIRECT选项),此时数据文件的写入操作是直接从mysqlinnodbbuffer到磁盘的,并不用通过操作系统的缓冲,但对于InnoDB来说,数据文件的写入操作真正的完成也是在flush这步(需要在调用fsync系统函数完成之后),但是日志文件的写入还是要经过OS缓冲(此选项在某些GNU/Linux版本,FreeBSD和Solaris上可用).
要注意:O_DIRECT表示数据文件的读写直接跳过文件系统缓存.
如果磁盘设备是本地闪存设备或者带CACHE的RAID卡,那么设置该参数可以减少bufferpool和oscache双倍内存开销.
但如果不具备这个条件,可能导致数据库性能很差(使用O_DIRECT打开文件,则读/写操作都会跳过OScache,直接在device(disk)上读/写.
因为没有了OScache,所以会降低文件的顺序读写的效率,但有数据表明,如果是大量随机写入操作,O_DIRECT会提升效率.
对于使用MySQLInnoDB的场景来说,大多数都是OLTP,而不是OLAP,所以,如果使用InnoDB存储引擎,通常建议使用O_DIRECT),另外,在docker环境中使用InnoDB存储引擎时,O_DIRECT可能导致性能不稳定,抖动严重,如果出现此问题,建议使用fsync,或许有利于提高稳定性*O_DIRECT_NO_FSYNC:InnoDB在刷新I/O期间使用O_DIRECT,但之后跳过fsync()系统调用.
此设置只适用于某些类型的文件系统.
例如,它不适合XFS.
如果不确定您使用的文件系统是否需要fsync(因为O_DIRECT_NO_FSYNC会跳过fsync系统调用),但你又需要保留所有文件元数据时,请改用O_DIRECToWIN系统中,5.
6.
x中常用的值有且只有一个async_unbuffered(也有normal和unbuffered两个值,但是在5.
6.
x版本中还是内部测试功能,使用需要个人承担风险),5.
7.
x版本中normal和unbuffered值的描述中把测试功能,个人承担风险的字眼去掉了,看样子是可以使用了.
如果设置为NULL,则默认使用async_unbuffered选项*async_unbuffered:InnoDB使用Windows异步I/O和非缓冲I/O.
async_unbuffered是Windows系统上的默认设置*normal:InnoDB使用模拟异步I/O和缓冲I/O*unbuffered:InnoDB使用模拟异步I/O和非缓冲I/Oo全局变量,只读变量,string类型,unix版本默认值为null(null时默认使用fsync),win版本默认值为null(null时默认使用async_unbuffered)oPS:*该变量在8.
0中可以使用数字来代替相应的值,且8.
0中unix默认值设置为fsync,WIN版本默认值为unbuffered,删除了async_unbuffered1.
6.
7.
innodb_file_format=Barracuda配置InnoDB的文件格式,目前支持barracuda、antelope两种,配置为antelope时InnoDB表可以使用redundant和compact两种行格式,配置为barracuda时,InnoDB表可以使用redundant和compact,compressed和dynamic四种行格式.
o修改这个参数记得session和global以及配置文件my.
cn都要同步修改,且表定义中需要添加ROW_FORMAT=xxx行格式配合使用o可以通过参数innodb_file_format_check来检测当前InnoDB存储引擎文件格式的支持度,默认为ON.
o默认innodb_file_format为antelope,如果没有很多大字段时,建议使用默认的antelope文件格式和compact的行格式,适用于绝大多数场景,如果有很多大字段,需要采用innodb_file_format为barracuda,建议行格式使用dynamic(使用compressed和dynamic行格式时,还需要参数开启参数innodb_file_per_table=1支持),不建议使用compressed格式(因为innodb_buffer中的pages无法压缩,只能压缩保存到磁盘中的page).
另外,barracuda文件格式适用于不需要经常修改和备份的表o全局变量,动态变量(修改文件格式时,不影响已经存在的InnoDB表的行格式),默认值为antelope(5.
7.
7版本开始默认值为barracuda),字符串类型值.
oPS:在5.
7.
x的文档中,对于使用dynamic和compressed格式的描述变了,对于使用dynamic行格式,则会自动忽略innodb_file_format参数设置直接使用Barracuda文件格式.
对于使用compressed格式,则必须把innodb_file_format设置为barracuda.
另外,在5.
7.
x文档中说该参数是将要废弃的功能,innodb_file_format选项的目的是允许用户降级到MySQL5.
1中的内置版本的InnoDB.
现在,MySQL5.
1已经到了其产品生命周期的终点,不再需要此选项提供的降级支持.
在8.
0版本中已经移除了该配置参数.
1.
6.
8.
innodb_flush_neighbors=0开启与关闭刷新邻接页功能(bufferpool中相对于磁盘上相同的区中的脏页),默认为1,可动态修改,枚举类型.
对于传统机械磁盘建议设置为1即启用,对于SSD则建议设置为0即关闭.
此外还有一个为2的值,该值也同样刷新邻接页,但不要求在同一个区中的脏页是连续的.
而1是要求bufferpool中相对于磁盘上相同的区之外,还要求在同一个区中是连续的1.
6.
9.
innodb_log_file_size=17179869184控制redolog单个文件的大小.
o全局变量,只读变量,默认值为48M,最小值为1M(5.
7.
11版本开始最小值被增加为4M),最大值5.
6.
2及其之前的版本为4G,5.
6.
3版本开始支持最大512G(日志文件的总大小(innodb_log_file_size*innodb_log_files_in_group)不能超过最大值,需要稍小于512GB的最大值.
例如,两个255GB的日志文件综合510G接近但不大于等于限制)o通常,日志文件应足够大,以便服务器能够平均峰值和谷值的性能抖动,使服务器在一个平滑的性能下工作,通常需要redolog能够保存一个小时的写入活动.
值越大,缓冲池中需要的检查点刷新活动越少,从而节省磁盘I/O.
要注意:虽然MySQL5.
5和更高版本中引入checkpoint机制来减少崩恢复的时间,但是更大的日志文件也使崩溃恢复更慢.
o重要*由于Bug#69477,对于大型,外部存储的BLOB字段产生的重做日志写入可能会覆盖最近的检查点.
为了解决这个问题,MySQL5.
6.
20中引入的补丁将BLOB能够写的重做日志的大小限制为重做日志文件单个大小的10%.
在该限制机制下,innodb_log_file_size应设置为大于表的行中最大BLOB数据大小的10倍加上其他可变长度字段(VARCHAR,VARBINARY和TEXT类型字段)的长度的值.
*在MySQL5.
6.
22中,重做日志BLOB写入限制被放宽并修改为总重做日志大小的10%(innodb_log_file_size*innodb_log_files_in_group).
(bug#19498877)o当一个日志写满后,InnoDB会切换到另外一个日志文件,但是切换时会触发数据库检查点,这将导致InnoDB脏页缓存的小批量刷新,会明显降低InnoDB的性能,所以可能需要适当加大InnoDB日志文件大小参数:innodb_log_file_size,但是不能设置过大,过大就意味着需要更长的时间来恢复,一般来说,半小时写满一个日志文件比较合适,下面是即算每小时产生的日志量并估算合适的innodb_log_file_size值的方法:#首先即算每分钟产生的日志量:pagergrep-i'Logsequencenumber'showengineinnodbstatus\Gselectsleep(60);showengineinnodbstatus\G;#把后边一次结果减去前边一次结果,进行运算,得出的结果就是每分钟产生的日志量,然后乘以60就是一小时的日志量:selectround((2029338537-2029338537)/1024/1024/@@innodb_log_files_in_group)asMB;#查询每分钟的日志量也可以通过查询information_schema.
global_status表:select@a1:=variable_valueasa1frominformation_schema.
global_statuswherevariable_name='innodb_os_log_written'unionallselectsleep(60)unionallselect@a2:=variable_valueasa2frominformation_schema.
global_statuswherevariable_name='innodb_os_log_written';#把后边一次结果减去前边一次结果并进行即算,得出的结果就是每分钟的日志量:selectround((@a2-@a1)/1024/1024/@@innodb_log_files_in_group)asMB;PS:5.
7.
x中,对于Bug#69477和bug#19498877的提示已经去掉了1.
6.
10.
innodb_log_files_in_group=2设置redolog日志组中有多少个redolog文件,全局变量,只读变量,默认值且也是推荐值为2,最大值为100,最小值也为2,整型值.
1.
6.
11.
innodb_purge_threads=4控制purge线程是否独立出主线程,开启多少个独立的purge线程,如果CPU核心数比较多且磁盘性能比较高,可适当修改上面几个参数.
o设置多个独立purge线程时,有助于提高DML多表操作的效率.
以及减少InnoDB内部的资源争用o全局变量,只读变量,5.
6.
4及其之前的版本默认值为0,5.
6.
5开始的版本默认值为1,最大值为32,整型值1.
6.
12.
innodb_thread_concurrency=64控制同时有多少个线程进入InnoDB内核o该值建议为:CPU核心数*磁盘数量*2,实际使用的值最好稍微比这个公式值小一点,以下设置规则供参考*如果工作负载的并发用户线程数小于64,请设置innodb_thread_concurrency=0*如果您的工作负载持续繁重或偶尔出现峰值,请先设置innodb_thread_concurrency=128,然后将值降低到逐一降低到96,80,64,依此类推,直到找到提供最佳稳定的性能的线程数.
oInnoDB尝试保持在InnoDB内的并发的操作系统线程数小于或等于此变量给出的限制值(InnoDB使用操作系统线程来处理用户事务).
一旦线程数达到此限制,则线程被放进"先进先出"(FIFO)队列中被且置于等待状态以供执行.
处于等待锁状态的线程不会计入并发执行的线程计数中.
o全局变量,只读变量,默认为0,整型值,取值范围为0~1000,最小值为0,也是默认值,表示不对InnoDB内部的线程做限制.
设置为0时,InnoDB根据需要自由地创建内部线程,此时,InnoDB关闭了进入InnoDB内部的线程数量检测机制,也同时关闭了SHOWENGINEINNODBSTATUS输出中的ROWOPERATIONS部分的queriesinsideInnoDB和queriesinqueuecounters统计.
1.
6.
13.
innodb_write_io_threads=16控制InnoDB内部写线程数量参数o在一台机器上跑多实例的时候,要注意操作系统的aio-max-nr设置限制.
如果被限制时,要么增大操作系统的这个值,要么减少my.
cnf中的配置o另外,binlog的刷新也是使用这个线程,特别是sync_binlog=1时也要把这个参数加一o全局变量,只读变量,默认值为4,最小值为1,最大值为641.
6.
14.
innodb_read_io_threads=16控制InnoDB内部读线程数量参数o全局变量,只读变量,默认值为4,最小值为1,最大值为641.
6.
15.
innodb_file_per_table=1设置InnoDB表是否使用独立表空间,如果设置为1,表示使用独立表空间,每个表的表数据、索引信息、插入缓冲bitmap在独立表空间里存放,但是表的元数据、undolog、插入缓冲等等还是保存在共享表空间里o全局变量,动态变量(如果动态修改,立即对所有的连接生效,动态修改需要有super权限),5.
6.
5及其之前的版本默认为OFF,5.
6.
6及其之后的版本默认为ON,当为OFF时,所有的表的数据和索引都存放在共享表空间,共享表空间增大之后无法收缩(5.
7.
x版本可以自动收缩)1.
6.
16.
innodb_autoinc_lock_mode=2控制InnoDB自增长锁模式参数,全局变量,只读变量,整型值,有0,1,2三个值(分别代表traditional("传统"),consecutive("连续")和interleaved("交叉")),默认为1,表示自增ID是连续的.
o在5.
1.
22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode,该实现方式是通过轻量级互斥量的增长机制完成的.
它是专门用来在使用auto_increment的情况下调整锁策略的,目前有三种选择:*0:通过表锁的方式进行,也就是所有类型的insert都用AUTO-inclocking.
*1:默认值,对于simpleinsert自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulkinsert则还是使用表锁的方式进行.
直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_incrementid,只会将整个分配的过程锁住.
*2:对所有的insert-like自增长值的产生使用互斥量机制完成,分配自增id时是预分配超过需要的自增范围段,性能最高,并发插入可能导致自增值不连续,可能会导致Statement的Replication出现不一致,使用该模式,需要用RowReplication的模式.
innodb_autoinc_lock_mode=2和innodb_autoinc_lock_mode=1的测试情况一样.
但该模式下是来一个分配一个,而不会锁表,只会锁住分配id的过程,和1的区别在于,不会预分配多个,这种方式并发性最高.
但是在replication中当binlog_format为statement-based时存在问题o尽量让主键ID没有业务意义,或则使用simpleinserts模式插入,下面是InnoDB插入类型说明:*1).
INSERT-LIKE:指所有的插入语句,比如INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOADDATA等*2).
Simpleinserts:指在插入前就能确定插入行数的语句,包括INSERT、REPLACE,不包含INSERT…ONDUPLICATEKEYUPDATE这类语句.
*3).
Bulkinserts:指在插入前不能确定得到插入行的语句.
如INSERT…SELECT,REPLACE…SELECT,LOADDATA.
*4).
Mixed-modeinserts:指其中一部分是自增长的,有一部分是确定的.
oPS:通过上面2个问题的说明,自增主键会产生表锁,从而引发问题;自增主键有业务意义,不连续的主键导致主从主键不一致到出现问题.
对于simpleinserts的插入类型,上面的问题都不会出现.
对于Bulkinserts的插入类型,会出现上述的问题.
1.
6.
17.
innodb_open_files=4096.
InnoDB引擎限制一次打开表空间文件.
ibd的文件描述符数量,只在使用独立表空间时才有用,范围10~4294967295o该值仅用于限制InnoDB表空间.
ibd文件的描述符数量,不影响tablecache的数量,增大这个变量需要注意同时修改server层的变量open_files_limit=num(只读变量),这个参数为系统级别的文件句柄限制参数,由于每个session操作数据库表时都要占用文件描述符,数据库连接本身也要占用文件描述符,因此如果手动指定innodb_open_files参数时,还需要注意并发连接数量open_files_limit的大小设置o全局变量,只读变量,5.
6.
6之后的版本默认为-1,表示根据实际打开数量自动设置(自动计算规则:如果innodb_file_per_table没有打开,则默认值为300,如果打开了,则以innodb_open_files与table_open_cache中的较大值为准).
5.
6.
5及其之前的版本默认为3001.
6.
18.
innodb_adaptive_hash_index=ON控制InnoDB自适应哈希索引特性是否开启参数.
开启时,会在内存中把热点数据对应的B树索引在内存中映射到一个hash表中,这样可以加快热点数据的查询o禁用自适应散列索引会立即清空hash散列表.
当散列表被清空时,正常操作可以继续,但正在使用hash散列表的查询立即直接访问B树索引.
当重新启用自适应hash散列索引时,又会在内存中把热点数据对应的B树索引在内存中重新映射到一个hash表中o全局变量,动态变量(修改需要有super权限),默认为ON,布尔型值1.
6.
19.
innodb_data_file_path=ibdata1:20M:autoextend指定共享表空间文件的路径和名称,初始值大小,是否自动扩展.
o该参数受控于innodb_data_home_dir参数,如果该参数为空,且innodb_data_file_path不带绝对路径时,默认放在datadir下,此时可以给共享表空间文件使用绝对路径,如果innodb_data_home_dir设置了不为空的值,则innodb_data_file_path不能带路径,只能指定共享表空间名称,初始值大小,是否自动扩展.
另外innodb_data_home_dir只能影响共享表空间的路径,对于独立表空间不受这个参数影响(受datadir影响).
o可以设置多个共享表空间文件:innodb_data_file_path=ibdata1:10M:autoextend;ibdata2:10M:autoextend#如果这两个文件在不同的磁盘上,那么可以提高I/O性能,注意:这个参数的初始化值最好设置大一点,如500M~1G(如果文件系统和操作系统支持大文件,可以设置一个大于等于4G的值),如果太小可能在高并发下undolog急剧增大而频繁自动扩展文件大小影响性能o全局变量,只读变量,默认值5.
6.
6及其之前的版本为ibdata1:10M:autoextend,5.
6.
7及其之后的版本为ibdata1:12M:autoextendoPS:在5.
7.
x之前的版本中,第一个系统表空间文件的最小大小为10M,在5.
7.
x及其之后的版本中,根据innodb_page_size设置的不同,为第一个系统表空间数据文件(ibdata1)强制执行以下最小文件大小策略,以确保有足够的空间用于doublewritebufferpages:*对于innodb_page_size值为16KB或更小,最小数据文件大小为3MB*对于innodb_page_size值为32KB,最小数据文件大小为6MB*对于innodb_page_size值为64KB,最小数据文件大小为12MB1.
6.
20.
innodb_log_group_home_dir=.
设置日志组文件路径和文件名称,全局变量,只读变量,默认为datadir+ib_logfileN1.
6.
21.
innodb_doublewrite=ON控制InnoDB双写缓冲的开关的变量,因为双写缓冲是对连续磁盘空间的顺序写,因此开启双写缓冲对性能的影响不太大(但刷数据的流量会增加一半),对于要求高性能,又能容忍极端情况下可以丢失少量数据的应用,可以在配置文件中增加innodb_doublewrite=0来关闭双写缓冲o全局变量,只读变量,默认值为ON,布尔型值1.
6.
22.
innodb_fast_shutdown=1控制mysqld的关闭模式参数o有0,1,2三个值*0表示慢速关闭,在mysql关闭数据库时,InnoDB需要完成所有的fullpruge和mergeinsertbuffer,并且将所有的脏页刷新回磁盘,这需要一些时间,有时甚至需要几个小时来完成,如果在进行InnoDB升级或者降级,必须将这个参数设置为0,然后再关闭数据库,以便在升级过程更新文件格式时,所有数据文件中的数据都已完整.
*1是默认的,表示不需要完成fullpurge和mergeinsertbuffer操作(把这部分内容写进日志里),但是在缓冲池中的一些脏数据还是会刷新到磁盘.
下次启动时再读取日志内容进行fullpurge和插入缓冲合并*2表示不完成fullpurge和mergeinsertbuffer操作,也不将缓冲池中的数据脏页写回磁盘,而是将日志都写入日志文件,这样不会有任何的事务丢失,但是下次启动数据库时,会进行恢复操作.
o当正常关闭数据库时,下次的启动应该会非常"正常",但是如果没有正常地关闭数据库,用kill关闭的,在mysql数据库运行中重启了服务器,或在关闭数据库时,将参数innodb_fast_shutdown设置为了2,或者说值为2时发生了crash,下次mysql启动时都会对InnoDB的表进行崩溃恢复操作.
o全局变量,动态变量,默认值为1,整型值oPS:当innodb_fast_shutdown设置为大于1时,在关闭MySQL会导致无法导出ib_buffer_pool文件,即使参数innodb_buffer_pool_dump_at_shutdown的默认值为ON也不行,这个时候要导出ib_buffer_pool文件,要么使用setglobalinnodb_buffer_pool_dump_now=ON手动触发导出,要么setglobalinnodb_fast_shutdown=1或者0,再关闭MySQL,否则无法自动导出ib_buffer_pool这个文件,在下次启动时会导致加载这个文件报错:[ERROR]InnoDB:Cannotopen'/data/mysqldata3306241/innodb_ts/ib_buffer_pool'forreading:Nosuchfileordirectory1.
6.
23.
innodb_flush_log_at_trx_commit=1控制redobuffer何时刷新到磁盘日志文件的参数oinnodb_flush_log_at_trx_commit参数可以控制将redobuffer中的更新记录写入到磁盘日志文件的操作时机,通过调整这个参数,可以在性能和数据安全之间做取舍.
此参数有以下几个值:*0:在事务提交时,不会立即触发将缓存日志写入到磁盘日志文件中,而是大约每秒一次由主线程刷新到磁盘文件的操作,并调用操作系统的fsync刷新I/O缓存.
最不安全,但是性能最好.
如果mysql服务挂掉或者操作系统挂掉,会丢失至少一秒的数据(由于主线程最小每秒去落盘,主线程可能由于工作负载的原因,这个循环间隔可能大于1S才去执行,那么如果碰到这种情况下mysqld或者操作系统crash掉了,将丢失超过一秒的数据)*1:在事务提交时,InnoDB立即触发将缓存中的redo日志写回到磁盘日志文件中,并调用系统fsync刷新I/O缓存,最安全,但是性能最差.
mysql挂掉或者操作系统挂掉不丢失数据.
完全符合ACID的标准要求设置为1*2:在事务提交时,InnoDB立即将缓存中的redo日志写回到日志文件,但是并不马上调用fsync来刷新I/O缓存,而是大约每秒一次磁盘I/O缓存刷新操作或者交由操作系统来决定何时刷新磁盘I/O缓存.
安全性和性能介于0和1之间,如果mysql服务挂掉而操作系统不挂,不会丢失数据,如果操作系统挂掉,也会丢失至少一秒的数据(由于主线程最小每秒去落盘,主线程可能由于工作负载的原因,这个循环间隔可能大于1S才去执行,那么如果碰到这种情况下mysqld挂了,不会丢失数据,但是如果操作系统crash掉了,将丢失超过一秒的数据)oInnoDB日志刷新频率由innodb_flush_log_at_timeout参数控制,允许您将日志刷新频率设置为N秒(其中N为1.
.
.
2700,默认值为1).
但是,如果mysqld进程崩溃或者操作系统崩溃都可以导致丢失多达N秒的事务oDDL操作和其他内部InnoDB活动刷新InnoDB日志的动作不受此参数控制oInnoDB的崩溃恢复过程中,忽略该参数的设置,总是完全应用redolog(前滚和回滚)o对于有主从复制的环境,要保证主从数据的持久性和一致性,必须设置该参数为1,且同时设置sync_binlog=1o全局变量,动态变量,默认值为1,枚举类型o警告:许多操作系统和某些磁盘硬件会执行flush-to-disk操作欺骗.
即他们可能告诉mysqldredolog已经落盘了,其实这个时候并没有落盘.
在这种情况下,即使设置1,也不能保证事务的持久性,在最坏的情况下,断电可能会损坏InnoDB数据.
所以使用UPS备用电源和带BBU的阵列卡可以使操作更安全.
当然您也可以尝试禁用磁盘缓存硬件高速缓存1.
6.
24.
innodb_force_recovery=0设置InnoDBcrashrecovery模式参数:数据库在机器crash可能导致日志文件损坏,重启之后无法正常恢复,更无法正常对外提供服务,该参数有如下有效值:o如果日志已经损坏,这里采用非常规手段,首先修改innodb_force_recovery参数,使mysqld跳过恢复步骤,将mysqld启动,将数据导出来然后重建数据库.
innodb_force_recovery可以设置为0-6,大的数字包含前面所有数字的影响.
*1:(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页.
*2:(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行fullpurge操作,会导致crash.
*3:(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作.
*4:(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作.
*5:(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交.
*6:(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作o只有在紧急情况下,才去更改为大于0的值,以便可以启动InnoDB并导出数据表.
作为安全措施,当innodb_force_recovery大于0时,InnoDB会阻止INSERT,UPDATE或DELETE操作.
从5.
6.
15开始,innodb_force_recovery设置为4或更大才将InnoDB设置为只读模式o全局变量,只读变量,默认值为0,有效值为0~6o注意:这些限制可能会导致复制管理命令失败并显示错误,如InnoDB表中的--relay-log-info-repository=TABLE和--master-info-repository=TABLE存储I/O和SQL线程信息时1.
6.
25.
innodb_lock_wait_timeout=50.
InnoDB事务请求行锁的超时时间限制参数o超时回滚时,如果innodb_rollback_on_timeout参数设置为OFF,则会回滚掉执行超时的那个SQL影响的数据,但不会回滚整个事务(除非你断开连接或者手动执行rollback语句),如果innodb_rollback_on_timeout参数设置为ON,则会在超时回滚时把整个事务的数据一起回滚掉oinnodb_lock_wait_timeout仅适用于InnoDB行锁.
MySQL表锁在InnoDB中不会发生,并且此超时不适用于等待表锁o锁等待超时值不适用于死锁,因为InnoDB会立即检测到它们并回滚其中一个死锁事务o全局,会话,动态变量(修改需要super权限),默认值为50,代表50S,最小值为1,整型值.
当发生锁等待超时时,mysql数据库会抛出一个1205的错误1.
6.
26.
innodb_locks_unsafe_for_binlog=OFF影响MySQL的gaplock查找和索引扫描的参数,布尔型值,举个例子说明o执行insertinto.
.
.
select语句时是否对数据源表加gap锁参数*0即表示要对数据源表加gap锁,MySQL查找和索引扫描会使用gaplock*1时表示不对数据源表加gap锁,MySQL查找和索引扫描只使用index-recordlock,但是启用innodb_locks_unsafe_for_binlog不会禁用对外键约束检查或重复键检查使用间隙锁定.
o隔离级别在select*frommysql.
innodb_index_statslimit1\G1.
rowdatabase_name:employeestable_name:departmentsindex_name:PRIMARYlast_update:2016-04-0420:29:30stat_name:n_diff_pfx01stat_value:9sample_size:1stat_description:dept_no1rowinset(0.
00sec)root@192.
168.
2.
107SatApr1511:47:30201711:47:30[(none)]>select*frommysql.
innodb_table_statslimit1\G1.
rowdatabase_name:employeestable_name:departmentslast_update:2016-04-0420:29:30n_rows:9clustered_index_size:1sum_of_other_index_sizes:11rowinset(0.
00sec)root@192.
168.
2.
107SatApr1511:47:36201711:47:36[(none)]>useemployees;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedroot@192.
168.
2.
107SatApr1511:50:40201711:50:40[employees]>showtablestatuslike'departments'\G1.
rowName:departmentsEngine:InnoDBVersion:10Row_format:CompactRows:9Avg_row_length:1820Data_length:16384Max_data_length:0Index_length:16384Data_free:0Auto_increment:NULLCreate_time:2016-04-0420:29:10Update_time:NULLCheck_time:NULLCollation:utf8_general_ciChecksum:NULLCreate_options:Comment:1rowinset(0.
00sec)root@192.
168.
2.
107SatApr1511:50:56201711:50:56[employees]>showindexfromdepartments;|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment||departments|0|PRIMARY|1|dept_no|A|9|NULL|NULL||BTREE||||departments|0|dept_name|1|dept_name|A|9|NULL|NULL||BTREE|||2rowsinset(0.
00sec)1.
6.
52.
innodb_stats_persistent_sample_pages=20估算索引列的基数和其他统计信息时的采样索引页数,例如由ANALYZETABLE触发的计算索引统计信息时的采样索引页数.
增加该系统参数的值可以提高索引统计的准确性,这可以改善查询执行计划的精确性,但在对InnoDB表执行ANALYZETABLE时会增加I/O消耗.
oinnodb_stats_persistent_sample_pages仅适用于为表启用innodb_stats_persistent时(持久化统计信息);当innodb_stats_persistent被禁用时,innodb_stats_transient_sample_pages代替(该系统参数为非持久化统计信息的采样页数)o注意:innodb_stats_persistent_sample_pages设置较高的值可能会导致ANALYZETABLE执行时间变长o全局变量,动态变量,整型值,默认值为201.
6.
53.
innodb_stats_transient_sample_pages=8估算索引列的基数和其他统计信息的采样索引页数,例如由ANALYZETABLE触发的计算统计信息的采样索引页数.
默认值为8.
增加该值可提高索引统计信息的准确性,从而可以改善查询执行计划的精确性,但在第一次打开InnoDB表或重新计算统计信息时会增加I/O消耗.
oinnodb_stats_transient_sample_pages仅适用于对表禁用innodb_stats_persistent系统参数时;当启用innodb_stats_persistent持久化统计信息系统参数时,非持久化统计信息使用innodb_stats_persistent_sample_pages系统变量定义的采样页数代替o注意:innodb_stats_transient_sample_pages设置较高的值可能导致ANALYZETABLE执行时间冗长.
ANALYZETABLE操作时估算索引统计信息需要访问的更多的数据库页数o全局变量,动态变量,整型值,默认值为8,5.
6.
2版本引入,代替5.
6.
2之前版本的innodb_stats_sample_pages变量,innodb_stats_sample_pages变量在5.
6.
3版本废弃1.
6.
54.
innodb_page_size=16384指定MySQL实例中所有InnoDB表空间的页面大小o您可以使用值64k,32k,16k(默认值),8k或4k页面大小.
或者使用字节为单位的页面大小(65536,32768,16384,8192,4096)oinnodb_page_size只能在初始化MySQL实例之前进行指定,初始化之后无法修改,否则无法启动(5.
6.
x版本中报错:2017-04-1513:17:365174[ERROR]InnoDB:innodb-page-sizemismatchindatafile.
/ibdata1,5.
7.
x版本中报错:2017-04-15T13:19:15.
688268+08:000[ERROR]InnoDB:Datafile'.
/ibdata1'usespagesize16384,buttheinnodb_page_sizestart-upparameteris4096).
如果未指定值,则使用默认页面大小初始化该实例.
oMySQL5.
6.
x中只支持4K、8K、16K,MySQL5.
7中添加了32k和64k页面大小的支持.
对于32k和64k页面大小,最大行长度约为16000字节(16K中因为有双向链表,所以最大行长度是8096字节).
oinnodb_page_size设置为32KB或64KB时,不支持建表选项ROW_FORMAT=COMPRESSEDo对于innodb_page_size=32k,区大小为2MB.
对于innodb_page_size=64k,区大小为4MB.
当使用32k或64k页面大小时,innodb_log_buffer_size系统变量应设置为至少16M(默认值)o默认的16KB页面大小或更大的页面大小适用于大多数应用场景,特别是对于涉及表扫描和涉及批量更新的DML操作.
但对于涉及许多短频快的小事务的DMLOLTP场景,则较小的页面大小可能会更有助于性能提升,因为当单页包含较多数据行时,各个事务之间的争用可能是一个问题.
对于SSD存储设备(SSD通常使用小块).
将InnoDB页面大小保持在接近存储设备块大小的情况下,能最大限度地减少重写到磁盘的没有发生变更的数据量o第一个系统表空间数据文件(ibdata1)的最小文件大小因innodb_page_size值设置不同而不同.
有关更多信息,请参阅innodb_data_file_path选项说明部分o全局变量,只读变量,枚举类型,5.
7.
5及其之前的版本只支持4K、8K、16K页面大小,5.
7.
6及其之后的版本中增加支持32K、64K页面大小,默认值为16K1.
6.
55.
innodb_page_cleaners=1从缓冲池实例刷新脏页的页面清理线程数.
页面清洁线程执行flush列表和LRU刷新.
oMySQL5.
6中引入了一个单独的页面清理线程(但在5.
6.
x版本中只支持一个线程,不可配置),用于从InnoDB主线程卸载缓冲池刷新工作.
在MySQL5.
7中,InnoDB支持多线程清理.
在5.
7.
7及其之前的版本该变量默认值为1,5.
7.
8及其之后的版本默认值为4,当该变量大于等于1时,每个缓冲池实例的缓冲池刷新任务将分派到空闲页面清理线程.
如果页面清理线程数超过缓冲池实例的数量,则innodb_page_cleaners系统参数的值会自动设置为与innodb_buffer_pool_instances相同的值o如果在将脏页面从缓冲池实例刷新到数据文件时,如果您的工作负载是写入I/O为主,并且如果您的系统硬件写I/O能力有一定的空闲,则增加页清理线程的数量可能有助于提高写入I/O吞吐量o在5.
7中也支持在正常关闭和启动恢复阶段进行多线程页面清理osetpriority()系统调用在支持的Linux平台上以及mysqld执行用户被授权的地方使用,以使page_cleaner线程优先于其他MySQL和InnoDB线程,以帮助页面刷新动作与当前工作负载保持同步.
可以在/etc/security/limits.
conf中配置mysqld执行用户授权,例如,如果mysqld在mysql用户下运行,则可以通过向/etc/security/limits.
conf中添加与以下规则来授权mysql用户:*具体设置参考此参数的官方原文手册部分解释o全局变量,只读变量,整型值,5.
7.
7之前的5.
7.
x版本默认值为1,5.
7.
8及其之后的版本默认值为4,取值范围为1~64,5.
7.
4版本引入oPS:实测:该参数值会受到innodb_buffer_pool_instances值影响,该参数实际的有效值为:10%的话,就可以考虑调整这两个参数的值了,但是要注意,这两个变量为会话变量,如果有大量数据需要使用临时表,请在session级别单独修改,查询执行完成后修改回global的值)来减小使用磁盘临时表的可能性.
2.
1.
2.
Created_tmp_tables服务器执行语句时自动创建的内存中的临时表的数量,注意该变量是包含所有的内部临时表的数量(包含memory和ondisk)o在某些情况下,服务器在处理语句时创建内部临时表.
用户无法直接控制发生这种情况的时间,服务器在以下条件下将创建临时表:*评估UNION语句时*评估一些视图,例如使用TEMPTABLE算法,UNION去重或UNION聚合的视图*评估派生表(FROM子句中的子查询)*为实现子查询或半连接物化视图而创建的表(请参见第8.
2.
2节"优化子查询,派生表和视图").
*评估包含ORDERBY子句和不同GROUPBY子句的语句(指的可能是两个语句使用到的列上都有独立的索引,而一个查询中是无法同时使用两个索引的,其中有一个操作无法使用到索引),或者ORDERBY或GROUPBY使用的列不都是驱动表的列(此时两个操作就算列有索引,没有使用到驱动表中的列是无法使用到索引的)*评估DISTINCT与ORDERBY组合的可能需要临时表*对于使用SQL_SMALL_RESULT修饰符的查询,MySQL使用内存中临时表,除非查询还包含必须需要使用磁盘临时表的元素(稍后描述)*评估多表UPDATE语句*评估GROUP_CONCAT()或COUNT(DISTINCT)表达式o要在语句真正执行前确定语句是否需要临时表,请使用EXPLAIN并检查Extra列以的值可以确认是否为使用了临时表,如果使用了,可以看到Usingtemporaryo当服务器创建内部临时表(内存或磁盘上)时,它会增加Created_tmp_tables状态变量.
如果服务器在磁盘上创建表,它会增加Created_tmp_disk_tables状态变量o某些查询场景不能使用内存中临时表,在这种情况下,服务器使用磁盘表替代,这些可能的场景如下:*在表中存在BLOB或TEXT列*在GROUPBY或DISTINCT子句中有字符串列的,对于binarystring大于512字节,对于nonbinarystring大于512字符.
(在MySQL5.
6.
15之前,不管字符串类型,都限制为512字节,超过即不能使用内存临时表)*如果使用UNION或UNIONALL,则在SELECT列表中存在任何列最大长度大于512个字符或512个字节时(对于binarystring大于512字节,对于nonbinarystring大于512字符)*SHOWCOLUMNS和DESCRIBE语句使用BLOB作为某些列的类型,结果集会使用磁盘临时表o内存临时表保存在内存中并由MEMORY存储引擎处理,磁盘临时表存储在磁盘上并由MyISAM存储引擎处理o如果内部临时表被创建为内存表,但后续主键变大超过了限制,MySQL会自动将其转换为磁盘表.
内存中临时表的最大大小由tmp_table_size和max_heap_table_size的值中较小者确定.
这不同于使用CREATETABLE显式创建的MEMORY表:对于这些表,只有max_heap_table_size系统变量确定允许表增长多少,并且不会转换为磁盘表o内存中临时表由MEMORY存储引擎管理,该引擎使用固定长度的行格式.
VARCHAR和VARBINARY列按照最大长度分配固定长度的内存,实际上将它们转换为CHAR和BINARY列,磁盘临时表由MyISAM存储引擎使用变长行格式管理.
字符串列是使用变长的,按数据的大小动态分配,与使用固定长度行的磁盘表相比,这减少了磁盘I/O、空间以及处理时间(在MySQL5.
6.
5之前,磁盘临时表存储使用固定长度的行)o对于最初在内存中创建内部临时表的语句,然后预估一定会导致会转换为磁盘表的查询,可以通过跳过转换步骤并在直接创建磁盘临时表避免内部转换的时间,可能有更好的性能.
通过big_tables系统变量可实现强制内部临时表直接使用磁盘临时表(注意,该变量只能碰到必须要使用大量临时表的语句,且只能session级别修改,不要全局修改,不要放到配置文件中)2.
1.
3.
Table_open_cache_hits打开一个表时,在表缓存查找中的命中次数,这个变量是在MySQL5.
6.
6中添加的2.
1.
4.
Table_open_cache_misses打开一个表时,在表缓存查找中的未命中次数,这个变量是在MySQL5.
6.
6中添加的2.
1.
5.
Table_open_cache_overflows打开表缓存的溢出次数,在打开或关闭表之后,cacheinstance中具有未使用的条目,此时如果打开表的总数大于table_open_cache/table_open_cache_instances数量.
则该变量的值就会增加,且会开始尝试清理每一个cacheinstance中未使用的表(如果一旦后续的查询中又开始使用被清理出cacheinstance的表,那么此时就会导致Table_open_cache_misses和Table_open_cache_overflows值的增加).
所以,在高并发的情况下,你需要尽量保证table_open_cache/table_open_cache_instances=你的实例中的表数量的1倍左右(防止在高并发的情况下实例中的每一个表都被打开至少一次,甚至是高并发下大量的多表联结查询),这个变量是在MySQL5.
6.
6中添加的.
2.
1.
6.
Com_lock_tables显式锁表语句执行的次数2.
1.
7.
Com_unlock_tables显式解锁语句执行的次数2.
2.
Handlers本章节主要讲解对句柄的操作计数变量2.
2.
1.
Handler_commit内部提交语句的数量,对于insert语句,通常可能回有两个Handler_commit计数,但首次操作表会计数4次,对于其他DDL语句的操作,该计数器会增加数十次以上,所以,该计数器通常在TPS/QPS计算中略有偏差,特别是数据库实例刚运行不久的时候2.
2.
2.
Handler_read_first读取索引中第一条记录的次数.
如果此值很高,则表明服务器正在执行大量全索引扫描;例如,SELECTcol1FROMfoo语句,假设col1已建立索引.
2.
2.
3.
Handler_read_key基于索引键读取行的请求数.
如果此值很高,这是一个很好的指示,代表你的查询使用到了适当的索引进行查询2.
2.
4.
Handler_read_last查询读索引最后一个索引键的请求数.
当使用ORDERBY时,服务器优先发出使用第一个索引键的请求,之后顺序往后扫描索引键.
当使用ORDERBYDESC时,服务器优先发出使用最后一个索引键的请求,之后向前扫描索引键.
这个变量在MySQL5.
6.
1中添加2.
2.
5.
Handler_read_next以索引键顺序读取下一行的请求数.
如果查询带有范围约束且使用到了索引列或者正在执行索引扫描,则此值将增加.
2.
2.
6.
Handler_read_rnd基于固定位置读取行的请求数.
如果您正在执行大量需要对结果排序的查询,那么此值很高.
你可能有很多查询需要MySQL扫描整个表,或者有不能正确使用索引键来进行排序的查询2.
2.
7.
Handler_read_rnd_next读取数据文件中下一行的请求数.
如果您正在进行大量表扫描,则此值很高.
通常,这表明表中没有创建合适的索引,或者查询语句编写有问题(不满足查询优化器使用索引的要求)而导致没有正确利用到已有的索引2.
2.
8.
Handler_delete从表中删除的数据行数,注意,不是SQL语句执行次数(不需要事务提交)2.
2.
9.
Handler_discover在使用NDBCLUSTER的场景中,MySQLServer在接收到给定表的请求时,询问NDBCLUSTER是否知道该表,该过程被称作discovery,该变量就表示使用该机制发现表的次数2.
2.
10.
Handler_external_lock服务器在每次调用external_lock()函数的时候递增此变量,该函数通常在开始访问一个表和访问结束关闭表的时候各自调用一次(即,对一个表访问一次,该变量会增加2),该变量在不同的存储引擎之间可能存在差异,例如:如果是分区表,则每个分区表自身会加2,然后每个被访问到的分区表都会各自加2,即要计算一个分区表被锁定的分区数量,可以先减去2,然后再除以2就得到了被锁定分区表的数量2.
2.
11.
Handler_mrr_initMySQLServer使用InnoDB存储引擎的MRR(多范围读)来进行表访问的次数2.
2.
12.
Handler_prepare两阶段提交中prepare阶段的执行次数,每个DDL语句修改表结构该状态变量会增加4,每个DML修改数据该状态变量会增加22.
2.
13.
Handler_read_prev按照索引顺序读取前一行的请求数,该读取数据的方法主要用于优化ORDERBY.
.
.
DESC语句,即,使用了ORDERBY.
.
.
DESC语句查询的每一行数据,都会增加该状态变量一次2.
2.
14.
Handler_rollback存储引擎请求执行回滚操作的次数,对于使用rollback语句显式回滚的操作,状态变量每次加1,如果是超时或者死锁自动回滚,每个语句状态变量加22.
2.
15.
Handler_savepoint存储引擎使用savepointsavepoint_name;语句设置保存点的请求数,每执行一次savepoint语句该状态变量会增加22.
2.
16.
Handler_savepoint_rollback存储引擎使用rollbacktosavepoint_name;语句回滚到某个指定保存点的请求数,每执行一次rollbacktosavepoint_name语句该状态变量会增加22.
2.
17.
Handler_update更新表中的行记录的请求次数,对于表中真实修改的每一行记录,该状态变量都会加1(不需要事务提交),注意:匹配到但未真实修改的行记录不会增加该状态变量值2.
2.
18.
Handler_write插入表中的行记录的请求数,对于插入表中的每一行记录,该状态变量都会加1(不需要事务提交)2.
3.
locks2.
3.
1.
Innodb_row_lock_current_waits在InnoDB表上的操作当前正在等待的行锁的数量2.
3.
2.
Table_locks_immediate可以立即授予表级锁请求的次数,如果你使用的是MyISAM,Memory这种表级别锁的引擎,那么这个值增加时需要关注这个值,否则如果你使用的是InnoDB表,那么很可能是应用层显式锁表了2.
3.
3.
Table_locks_waited无法立即授予表级锁请求并需要等待的次数.
如果这个值比较高,代表可能有性能问题(注意:如果是MyISAM表,并发写的时候可能急剧增加,如果是InnoDB,那么这个值就应该很小,如果也很大,说明应用层锁表了,或者其他地方显式对InnoDB加表锁了),应该首先优化您的查询,然后拆分大表(或者把频繁查询的字段按照业务类型进行拆分)或使用复制架构做读写分离.
2.
3.
4.
Innodb_row_lock_timeInnoDB表等待获取行锁的总时间开销,单位毫秒,注意:需要事务结束的时候才会对事务的行锁请求等待时间进行计算2.
3.
5.
Innodb_row_lock_time_avgInnoDB表等待获取行锁的平均时间开销,单位毫秒,注意:事务开始请求锁和事务结束的时候都会对事务的行锁请求等待时间进行计算2.
3.
6.
Innodb_row_lock_time_maxInnoDB表等待获取行锁的最大时间开销,单位毫秒,注意:需要事务结束的时候才会对行锁请求的等待事件进行计算,如果有事务行锁请求的时间大于当前值,则会进行更新2.
3.
7.
Innodb_row_lock_waitsInnoDB表行锁等待的操作次数,事务请求锁时出现锁等待即状态变量加12.
4.
innodbbufferpool2.
4.
1.
Innodb_buffer_pool_pages_dirtyInnoDB缓冲池中当前的脏页数量,与fulshlist对应.
当使用压缩表时,脏页数量可能会增多,可能导致最终使用该状态变量的值统计bufferpool中的总页数时超过Innodb_buffer_pool_pages_total状态变量的值2.
4.
2.
Innodb_buffer_pool_pages_freeInnoDB缓冲池中的可用页数量(空闲页数量),与freelist对应2.
4.
3.
Innodb_buffer_pool_pages_dataInnoDB缓冲池中的包含数据的页数量.
与lrulist对应(注意:lrulist也包含了dirtypage),该数字包括脏页和干净页(脏页就是在在bufferpool中被修改过的页,干净页就是在bufferpool中没有发生过修改的页).
当使用压缩表时,Innodb_buffer_pool_pages_data值可能大于Innodb_buffer_pool_pages_total(bug#59550)2.
4.
4.
innodb_buffer_pool_reads表示从物理磁盘读取页的次数(InnoDB无法从缓冲池中满足逻辑读取时,直接从磁盘读取页的次数),当一次I/O请求涉及到多个页时,会累加计算2.
4.
5.
innodb_buffer_pool_read_requestsInnoDB逻辑读次数(从bufferpool中读取数据的次数),这个是衡量系统压力的重要指标,也是SQL调优最精准的参照.
与InnoDBphysicalreads对比可以得到bufferpool的命中率2.
4.
6.
Innodb_buffer_pool_write_requestsInnoDB写入数据文件,请求的页数量,当一次I/O请求涉及到多个页时,会累加计算(注意是对InnoDB缓冲池执行的写入数,即逻辑写),physicalreads和physicalwrites可以估算应用的读写比例2.
4.
7.
Innodb_buffer_pool_pages_flushedInnoDB缓存池中刷新页请求的数目2.
4.
7.
Innodb_buffer_pool_wait_free当InnoDB后台线程需要读取一个页到内存中或需要创建一个页且没有可用的空闲页(cleanpage)时,InnoDB首先刷新一些脏页到磁盘,并等待该操作完成,此状态变量即计数这些等待,正常情况下,如果innodb_buffer_pool_size设置够大,那么此值应很小,如果不为0且在持续增加,说明当前ibp(innodb_buffer_pool_size)严重不足,需要加大ibp2.
4.
8.
Innodb_buffer_pool_dump_status设置了系统变量innodb_buffer_pool_dump_at_shutdown=1或innodb_buffer_pool_dump_now=1时,会触发导出InnoDB缓冲池中保存的页面的操作的过程(即触发导出bufferpool中的热点数据页列表到datadir下的ib_buffer_pool文件中),该状态变量即统计整个导出过程的进度信息(总共需要导出多少个页,当前导出了多少个页等,导出完成时显示完成时间戳)2.
4.
9.
Innodb_buffer_pool_load_status加载更早的时间对innodbbufferpool中导出的热点数据也列表文件ib_buffer_pool时的进度状态信息(总共需要导入多少个页,当前导入了多少个页等,导入完成时显示完成时间戳).
该操作由innodb_buffer_pool_load_at_startup=1或innodb_buffer_pool_load_load_now=1系统变量的设置触发.
如果导入热点数据页操作开销太高(磁盘读I/O),可以通过设置innodb_buffer_pool_load_abort=1来终止热点数据页的导入过程2.
4.
10.
Innodb_buffer_pool_resize_status动态设置innodb_buffer_pool_size系统参数可以触发动态调整InnoDB缓冲池大小,该状态变量显示动态调整bufferpoolsize大小操作的状态(进度信息)2.
4.
11.
Innodb_buffer_pool_bytes_dataInnoDB缓冲池中包含数据的页的字节总数.
其中包括脏页和干净的页.
对于Innodb_buffer_pool_pages_data状态变量来说,Innodb_buffer_pool_bytes_data状态变量统计内存使用量更精确,详见Innodb_buffer_pool_pages_data状态变量解释部分2.
4.
12.
Innodb_buffer_pool_bytes_dirtyInnoDB缓冲池中脏页数据字节总数.
相对于Innodb_buffer_pool_pages_dirty状态变量来说,Innodb_buffer_pool_bytes_dirty状态变量对于内存使用统计更精确,详见Innodb_buffer_pool_pages_dirty状态变量解释部分2.
4.
13.
Innodb_buffer_pool_pages_miscInnoDB缓冲池中为了管理资源分配而维护的页数量(如行锁或自适应哈希索引页等).
这个值可以通过公式Innodb_buffer_pool_pages_totalInnodb_buffer_pool_pages_freeInnodb_buffer_pool_pages_data=Innodb_buffer_pool_pages_misc来进行计算.
但当使用压缩表时,Innodb_buffer_pool_pages_misc状态变量可能会显示一个巨大的溢出值.
2.
4.
14.
Innodb_buffer_pool_pages_totalInnoDB缓冲池的总页数量.
当使用压缩表时,Innodb_buffer_pool_pages_data状态变量可能显示一个巨大的溢出值2.
4.
15.
Innodb_buffer_pool_read_ahead_rndInnoDB发起的"随机"预读的次数.
当查询以随机扫描访问表中的大部分数据时,会发生预读操作.
2.
4.
16.
Innodb_buffer_pool_read_ahead预读后台线程读入InnoDB缓冲池的页数2.
4.
17.
Innodb_buffer_pool_read_ahead_evicted由预读后台线程读入InnoDB缓冲池中的且因为一定时间内没有被访问而被驱逐出innodbbufferpool中的页数量2.
4.
18.
Innodb_page_sizeInnoDB页大小(默认为16KB).
许多状态变量的值都是以页为单位进行计算,使用页大小来计算字节数等2.
4.
19.
Innodb_pages_created在InnoDB表中创建的页数量,需要物理扩展表空间文件大小2.
5.
innodbdataio2.
5.
1.
innodb_data_readInnoDB存储引擎读数据文件的I/O流量(字节为单位)2.
5.
2.
innodb_data_readsInnoDB数据读取的总I/O次数(OS文件读取,即发起物理读取的I/O请求次数,每次读取可能需要读取多个页,但并不会每个页累加这个值)2.
5.
3.
Innodb_data_writesInnoDB数据写入的总I/O次数(OS文件写入,即发起物理写入的I/O请求次数,每次写入可能需要写入多个页,但并不会每个页累加这个值)2.
5.
4.
Innodb_data_writtenInnoDB存储引擎写数据文件的I/O流量(字节为单位)2.
5.
5.
Innodb_pages_written通过操作InnoDB表引起的数据写入涵盖的页数,即向InnoDB表中写入的页数2.
5.
6.
Innodb_pages_read通过操作InnoDB表引起的从InnoDB缓冲池读取数据涵盖的页数,即,在对表执行读取操作时,直接从innodbbufferpool中读取返回数据的页数2.
5.
7.
Innodb_data_fsyncs刷新数据文件的fsync()系统调用的次数,fsync()系统调用频率受innodb_flush_method参数设置影响2.
5.
8.
Innodb_data_pending_fsyncs当前挂起(等待处理)的fsync()操作数量.
fsync()调用的频率会受innodb_flush_method配置选项设置的影响2.
5.
9.
Innodb_data_pending_reads当前挂起(等待处理)的读请求数量2.
5.
10.
Innodb_data_pending_writes当前挂起(等待处理)的写请求数量2.
5.
11.
Innodb_dblwr_pages_written已经写入到双写缓冲中的页数2.
5.
12.
Innodb_dblwr_writes已经执行完成的doublewrite写操作的次数2.
6.
innodbrows2.
6.
1.
innodb_rows_deleted从InnoDB表删除的行数,通过计算每秒的差值,可以衡量InnoDB每秒删除行能力(不需要事务提交)2.
6.
2.
innodb_rows_inserted在InnoDB表插入的行数,通过计算每秒的差值,可以衡量InnoDB每秒插入行能力(不需要事务提交)2.
6.
3.
innodb_rows_read从InnoDB表读取的行数,通过计算每秒的差值,可以衡量InnoDB每秒读取能力.
如果这个值比较大请检查select的相关指标2.
6.
4.
innodb_rows_updated在InnoDB表更新的行数,通过计算每秒的差值,可以衡量InnoDB每秒更新行能力(不需要事务提交)2.
7.
threads,clientconnection2.
7.
1.
threads_running当前正处于激活状态的线程个数(MySQL内部正在干活的线程个数),代表MySQL并发用户活动会话数量,在系统负载很大时,SQL持续时间会增加,这个指标会上升2.
7.
2.
threads_connectedMySQL当前已连接的线程的个数(包含MySQL内部正在干活的线程数以及处于sleep状态的线程数)2.
7.
3.
Threads_created为处理新的连接而新创建的线程数.
如果Threads_created比较大,可能要增加thread_cache_size值,线程缓存命中率可以使用公式:Threads_created/Connections计算,用该比例值可以衡量thread_cache_size参数的设置是否合理,该值越接近1,说明线程cache的命中率越低,就应该考虑增加thread_cache_size这个参数的值2.
7.
4.
Connections尝试连接MySQL的次数,包括成功的与不成功的连接数2.
7.
5.
Aborted_clients针对与建立的连接发生非正常断开的情况,由于某种原因导致客户程序不能正常关闭连接的数量.
如果客户不在退出之前调用mysql_close()函数,或者长时间保持连接且没有发生任何数据包,那么在wait_timeout或interactive_timeout的限制被超出时将被强制断开,或者是客户端程序在传输数据的过程中突然被终止,则这种情况会记录到该变量中,如果这个变量一直在增长,要注意你的应用对数据库的连接是否设计合理oCommunicationErrors:在你打开log_warnings=2选项时(是否向错误日志写入附加的警告消息.
默认情况下启用此变量(为1),可以通过将其设置为0来禁用此变量.
如果值为1,则服务器会记录有关基于语句的日志记录不安全的语句的消息.
如值大于1,则新的连接尝试连接失败和某些原因导致访问拒绝的错误都将记录到错误日志中)o可能导致Aborted_clients状态变量增加的原因有如下几种,发生如下几种情况时,错误日志中会记录Abortedconnection信息*客户端程序在未调用mysql_close()函数之前直接退出(有可能出现在程序员忘记写mysql_close()函数或者程序意外崩溃,或者在类似LVS+mycat架构中,应用通过mycat连接LVS的VIP,VIP路由到数据库服务器的IP,而一旦使用长连接,则如果LVS的TCP超时时间比mycat的长连接超时时间短,就会发生mycat没有正常断开连接的情况下被LVS从TCP层面断开了连接)*一个长时间保持的连接在wait_timeout和interactive_timeout时间限制内没有发起任何请求的,则会被server端强制断开*客户端程序在传输数据的过程中突然终止程序*客户端传输的数据包超过了max_allowed_packet设置的大小,或者查询需要使用到的内存超过了分配给mysqld的内存大小*在linux系统上的以太网设备驱动程序的半全双工协商有问题(不稳定或者有BUG)*线程库有BUG导致读取中断*TCP/IP配置不当*物理层的设备有问题,如:交换机,路由器,网卡,网线2.
7.
6.
Aborted_connects针对新创建的连接,指试图连接到MYSQL的失败的请求次数.
这种情况在客户尝试用错误的密码进行连接时,或者没有权限进行连接时,或者为获得连接的数据包所花费的时间超过了connect_timeout限制的秒数,或数据包中没有包含正确的信息时(发送的数据包没有遵循MySQL的通讯协议,server端无法识别是什么客户端),都会记录在该变量中,这个状态变量如果在一直增长,就表示有很多错误连接,要小心了,你可以查看Connection_errors_*开头的几个状态变量,看看哪种原因导致连接失败的计数器增加了(Connection_errors_accept:侦听端口上调用accept()函数期间发生的错误数、Connection_errors_internal:由于服务器中的内部错误而拒绝的连接数,例如无法启动新线程或内存不足的情况、Connection_errors_max_connections:由于达到服务器max_connections限制而拒绝的连接数、Connection_errors_peer_addr:在搜索连接客户端IP地址时发生的错误数、Connection_errors_select:在侦听端口上调用select()或poll()函数期间发生的错误数,此操作失败不一定意味着有客户端连接被拒绝、Connection_errors_tcpwrap:libwrap库设置的tcpwrap限制拒绝的连接数),另外还可以查看performance_schema.
host_cache表(该表正常记录客户端访问的IP和主机名信息,依赖于performance_schema功能是否开启,如果没有开启,该表为空)oAbortedConnections:在你打开log_warnings=2选项时(是否向错误日志写入附加的警告消息.
默认情况下启用此变量(为1),可以通过将其设置为0来禁用此变量.
如果值为1,则服务器会记录有关基于语句的日志记录不安全的语句的消息.
如值大于1,则新的连接尝试连接失败和某些原因导致访问拒绝的错误都将记录到错误日志中),可能会看到如:Abortedconnection854todb:'employees'user:'josh'的错误日志记录o可能导致Aborted_connects状态变量增加的原因有如下几种*客户端尝试访问一个实例,但是没有权限*客户端尝试访问一个实例,但是用户名或者密码无效或者错误*客户端尝试访问一个实例,但是发送的通讯包并没有遵循MySQL的通讯协议(如使用telnet连接mysql)*客户端尝试访问一个实例,但是server端等待客户端响应时,客户端发送的响应通讯包时间超过了connect_timeout限制2.
7.
8.
Connection_errors_acceptMySQLServer在侦听端口上调用accept()函数期间发生的错误数量2.
7.
9.
Connection_errors_internal由于MySQLServer内部错误导致连接被拒绝的数量,例如:无法启动新线程或内存不足时导致的连接创建失败会增加该状态变量的值2.
7.
10.
Connection_errors_max_connections由于客户端连接数达到了系统变量max_connections的限制阈值而被拒绝的连接数2.
7.
11.
Connection_errors_peer_address搜索连接客户端IP地址时发生的错误数2.
7.
12.
Connection_errors_select在侦听端口上调用select()或poll()函数期间发生的错误数.
(注意:此调用操作失败并不是客户端连接被拒绝)2.
7.
13.
Connection_errors_tcpwrap由libwrap库拒绝的连接数2.
7.
14.
Slow_launch_threads创建线程的时间超过slow_launch_time系统变量设置值的线程数,此状态变量在嵌入式Server(libmysqld)中没有意义,从MySQL5.
7.
2版本开始在嵌入式Server中该状态变量不再可见2.
7.
15.
Threads_cached线程缓存中的线程数.
该状态变量在嵌入式服务器(libmysqld)中没有意义,从MySQL5.
7.
2开始在嵌入式服务器中不再可见2.
8.
innodblogio2.
8.
1.
Innodb_log_writesInnoDBredo日志写次数,指的是对InnoDB重做日志文件的物理写入次数(注意,这里不是指的操作系统层的fsyncredolog文件次数,而是mysql层面的对redolog的写入次数)2.
8.
2.
Innodb_os_log_fsyncsInnoDBredo日志文件在操作系统层面的fsync()次数.
该指标数量主要与innodb_flush_log_at_trx_commit设置值有关2.
8.
3.
Innodb_log_waitsinnodbredolog写发生的等待次数,可能因为日志缓冲区太小,导致写redologbuffer时需要等待,如果这个大于0,就表示innodb_log_buffer不够用了,需要加大2.
8.
4.
Innodb_os_log_writtenInnoDB存储引擎写redolog的I/O流量(字节为单位)2.
8.
5.
Innodb_log_write_requestsInnoDBredolog写请求数量2.
8.
6.
Innodb_os_log_pending_fsyncsInnoDB重做日志文件的fsync()调用挂起(等待处理)次数2.
8.
7.
Innodb_os_log_pending_writesInnoDB重做日志文件写入操作被挂起(等待处理)操作次数2.
9.
queries,responsetime2.
9.
1.
Queries服务器执行的语句数.
此变量包括在存储过程和函数中执行的语句,与Questions变量不同.
它不计算COM_PING或COM_STATISTICS命令oSUM(Com_xxx)+Qcache_hits=Questions+statementsexecutedwithinstoredprograms=Queries2.
9.
2.
Questions服务器执行的语句数.
仅包括由客户端发送到服务器的语句,而不包括在存储过程和存储函数中执行的语句,这与Queries变量不同.
此变量不计算COM_PING,COM_STATISTICS,COM_STMT_PREPARE,COM_STMT_CLOSE或COM_STMT_RESET命令oqps计算:questions/uptime或者基于com_%计算:Com_select/s+Com_insert/s+Com_update/s+Com_delete/s2.
9.
2.
Com_commitMySQL提交的事务数量oCom_xxx类的语句状态变量指示每个xxx类别语句已执行的次数.
每种类型的语句都有一个状态变量.
例如,Com_delete和Com_update分别计数DELETE和UPDATE语句.
Com_delete_multi和Com_update_multi类似,但适用于使用多表语法的DELETE和UPDATE语句,如果从查询缓存中返回查询结果,则服务器将增加Qcache_hits状态变量,而不是Com_select(不开启查询缓存则忽略Qcache_hits)otps的计算:Com_commit/S+Com_rollback/S(这种计算方式中有一些版本可能不能统计隐式提交的事务数),或者(Handler_commit+Handler_rollback)/Uptime2.
9.
3.
Com_rollbackMySQL回滚的事务数量2.
9.
4.
Slow_queriesMySQL产生的慢查条数,查询时间超过long_query_time秒的查询的个数(只要查询时间超过了long_query_time参数该状态变量就会增加,不管慢查询日志记录功能是否开启),正常的话慢查时间设置为1秒,该值应该小于12.
9.
5.
Com_deletedelete语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
9.
5.
Com_delete_multi多表关联的delete语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
9.
5.
Com_insertinsert语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
9.
5.
Com_insert_selectinsert.
.
.
select语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
9.
5.
Com_replacereplace语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
9.
5.
Com_replace_selectreplace.
.
.
select语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
9.
5.
Com_selectselect语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功,如果开启了查询缓存,则直接从查询缓存返回,该变量计数不增加,而是增加Qcache_hits的计数2.
9.
5.
Com_updateupdate语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
9.
5.
Com_update_multi多表关联的update语句执行的次数,注意,只是记录语句执行次数,不管语句是否执行成功2.
10.
select2.
10.
1.
Select_scan单表查询或者join的第一个表使用全表扫描方式的select查询数量.
该值应该小于5.
如果该指标数量比较多,说明表中没有合适的索引或者索引设计不合理导致查询无法正确使用,建议添加索引或检查索引创建是否符合mysql查询优化器使用索引的规则,或者看看你的查询语句是否编写有问题2.
10.
2.
Select_range单表查询或者join的第一个表使用索引范围扫描方式的select查询数量,该值正常情况下变得比较大也问题不大2.
10.
3.
Select_full_joinjoin查询中被驱动表使用全表扫描(被驱动表不能使用索引)的select查询数量.
该值应该小于5.
如果该指标数量比较多,建议添加索引或者检查索引创建是否符合mysql查询优化器使用索引的规则,或者看看你的查询语句是否编写有问题2.
10.
4.
Select_range_checkjoin查询中关联字段没有索引,导致在join查询中被驱动表每次都需要检查是否可以使用索引范围扫描的select查询数量,如果此值不为0,则应仔细检查表的索引2.
10.
5.
Select_full_range_joinjoin查询中被驱动表使用索引范围扫描的select查询数量2.
11.
排序2.
11.
1.
Sort_rowsMySQL排序的行数.
用于衡量SQL排序效率.
如果这个值比较大请检查sort相关指标2.
11.
2.
Sort_scan使用全表扫描来进行排序的select次数.
该指标数量较多建议增加索引,以使排序使用索引2.
11.
3.
Sort_range使用索引范围扫描来进行排序的select次数2.
11.
4.
Sort_merge_passes排序算法已经执行的合并的次数.
如果这个变量值较大,应考虑增加sort_buffer_size、max_length_for_sort_data或max_sort_length相关系统变量的值.
调大一点2.
12.
网络流量2.
12.
1.
Bytes_sentMySQL发送给所有客户端的网络流量字节数,结合bytesreceived,可以作为数据库网卡吞吐量的评测指标,单位字节2.
12.
2.
Bytes_receivedMySQL从所有客户端接收的网络流量字节数,结合bytessent,可以作为数据库网卡吞吐量的评测指标,单位字节2.
13.
binlog,binlogcache2.
13.
1.
Binlog_cache_disk_use使用临时的二进制日志高速缓存保存事务语句变更数据时大小超过了binlog_cache_size值,导致使用到临时文件来保存事务语句变更数据的事务数量,如果是非事务的语句产生的binlog日志量超过了binlog_cache_size值,则会增加Binlog_stmt_cache_disk_use状态变量额值,表示非事务语句使用到binlog磁盘临时文件的语句数量2.
13.
2.
Binlog_cache_use事务语句在写binlog日志时使用到二进制日志高速缓存的事务数量2.
13.
3.
Binlog_stmt_cache_disk_use非事务语句写binlog日志时,由于超过了binlog_cache_size值导致使用到了临时文件存储这些日志数据的语句数量2.
13.
4.
Binlog_stmt_cache_use非事务语句写binlog日志时使用到二进制日志语句高速缓存的非事务性语句的数量2.
13.
5.
Com_binlog执行binlog语句的次数,binlog语句用于执行base64编码的binlog日志(解析二进制日志中的BINLOG'xxx';这个就是),可以用于判断在当前实例中是否有通过解析二进制日志来补偿数据的行为.
通常不需要关注2.
13.
6.
Com_show_binlog_events执行showbinlogeventsin'binlog_file'语句的次数2.
13.
7.
Com_show_binlogs执行showbinarylogs语句的次数2.
14.
replication,slave2.
14.
1.
Slave_open_temp_tables从库的SQL线程当前打开的临时表的数量.
如果该值大于零,则从库进程意外关闭被认为是不安全的.
此状态变量统计所有复制通道打开的临时表的数量o当binlog_format=row时临时表不会写入binlog中,当binlog_format=statement|mixed时,binlog中会记录对temporarytable的操作语句,这个时候大概从库SQL线程在执行这些针对临时表的语句时,就不能直接关闭从库示例,需要按照如下步骤操作*先停止SQL线程*然后查询Slave_open_temp_tables状态变量是否为0*如果变量不为0,则重新启动SQL线程,然过一会后再停止SQL线程,直到Slave_open_temp_tables状态变量为0为止*当Slave_open_temp_tables状态变量为0时,就可以正常关闭从库实例了o如果从库复制必须使用statement|mixed格式复制,那么要避免这个情况,可以规范临时表的命令,例如:都命名为noreptmp开头,然后使用复制过滤选项--replicate-wild-ignore-table=norep%来在从库上过滤掉临时表的复制2.
14.
2.
Com_change_db执行usedb_name;切换默认数据库的语句执行的次数2.
14.
3.
Com_change_master执行changemaster;配置或修改复制信息的语句执行的次数2.
14.
4.
Com_change_repl_filter该变量表示执行修改复制过滤规则语句(CHANGEREPLICATIONFILTER)的次数2.
14.
5.
Com_slave_start执行startslave;语句的次数2.
14.
6.
Com_slave_stop执行stopslave;语句的次数2.
15.
ddlsql2.
15.
1.
Com_alter_db执行ALTER{DATABASE|SCHEMA}[db_name]语句的次数(不带UPGRADE子句)2.
15.
2.
Com_alter_db_upgrade执行ALTER{DATABASE|SCHEMA}db_nameUPGRADEDATADIRECTORYNAME语句的次数2.
15.
3.
Com_alter_event执行alterevent语句的次数2.
15.
4.
Com_alter_function执行alterfunction语句的次数2.
15.
5.
Com_alter_instance执行alterinstance语句的次数,instance和表空间加密相关2.
15.
6.
Com_alter_procedure执行alterprocedure语句的次数2.
15.
7.
Com_alter_server执行ALTERSERVERserver_nameOPTIONS(option[,option].
.
.
)语句的次数2.
15.
8.
Com_alter_table执行altertable.
.
语句的次数2.
15.
9.
Com_alter_tablespace执行ALTERTABLESPACEtablespace_name.
.
.
语句的次数2.
15.
10.
Com_alter_user执行ALTERUSER[IFEXISTS].
.
.
语句的次数2.
15.
11.
Com_create_db执行createdatabase语句的次数2.
15.
12.
Com_create_event执行createevent语句的次数2.
15.
13.
Com_create_function执行createfunction语句的次数2.
15.
14.
Com_create_index执行CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name.
.
.
语句的次数2.
15.
15.
Com_create_procedure执行createprocedure.
.
.
语句的次数2.
15.
16.
Com_create_server执行createserver.
.
.
语句的次数2.
15.
17.
Com_create_table执行createtable.
.
语句的次数2.
15.
18.
Com_create_trigger执行createtrigger.
.
.
语句的次数2.
15.
19.
Com_create_udf执行CREATE[AGGREGATE]FUNCTIONfunction_nameRETURNS{STRING|INTEGER|REAL|DECIMAL}SONAMEshared_library_name语句的次数,即创建用户自定义函数语句的次数,用户自定义函数需要从plugindir目录下的库文件加载,需要用户有mysql.
func表的INSERT权限2.
15.
20.
Com_create_user执行createuser.
.
.
语句的次数2.
15.
21.
Com_create_view执行createview.
.
.
语句的次数2.
15.
22.
Com_drop_db执行dropdatabase.
.
语句的次数2.
15.
23.
Com_drop_event执行dropevent.
.
.
语句的次数2.
15.
24.
Com_drop_function执行dropfunction.
.
.
语句的次数2.
15.
25.
Com_drop_index执行dropindex.
.
.
语句的次数2.
15.
26.
Com_drop_procedure执行dropprocedure.
.
.
语句的次数2.
15.
27.
Com_drop_server执行dropserver.
.
.
语句的次数2.
15.
28Com_drop_table执行droptable.
.
.
语句的次数2.
15.
29.
Com_drop_trigger执行droptrigger.
.
.
语句的次数2.
15.
30.
Com_drop_user执行driopuser.
.
.
语句的次数2.
15.
31.
Com_drop_view执行driopview.
.
语句的次数2.
16.
adminsql2.
16.
1.
Com_analyze执行ANALYZE[NO_WRITE_TO_BINLOG|LOCAL]TABLEtbl_name[,tbl_name].
.
.
语句的次数2.
16.
2.
Com_check执行CHECKTABLEtbl_name[,tbl_name].
.
.
[option].
.
语句的次数2.
16.
3.
Com_install_plugin执行INSTALLPLUGINplugin_nameSONAME'shared_library_name'语句的次数2.
16.
4.
Com_kill执行KILL[CONNECTION|QUERY]processlist_id.
.
语句的次数2.
16.
5.
Com_load执行LOADDATA[LOW_PRIORITY|CONCURRENT][LOCAL]INFILE'file_name'.
.
语句的次数2.
16.
6.
Com_purge执行PURGE{BINARY|MASTER}LOGS{TO'log_name'|BEFOREdatetime_expr}语句的次数(不包含BEFOREdatetime_expr子句)2.
16.
7.
Com_purge_before_date执行PURGE{BINARY|MASTER}LOGS{TO'log_name'|BEFOREdatetime_expr}语句的次数(不包含TO'log_name'子句)2.
16.
8.
Com_release_savepoint执行RELEASESAVEPOINTidentifier语句的次数2.
16.
9.
Com_rename_table执行RENAMETABLEtbl_nameTOnew_tbl_name[,tbl_name2TOnew_tbl_name2].
.
.
语句的次数2.
16.
10.
Com_rename_user执行RENAMEUSERold_userTOnew_user[,old_userTOnew_user].
.
.
语句的次数2.
16.
11.
Com_repair执行REPAIR[NO_WRITE_TO_BINLOG|LOCAL]TABLEtbl_name[,tbl_name].
.
.
语句的次数2.
16.
12.
Com_revoke执行REVOKEALLPRIVILEGES,GRANTOPTIONFROMuser[,user].
.
.
语句的次数(不包含GRANTOPTION权限回收的revoke语句)2.
16.
13.
Com_revoke_all执行REVOKEALLPRIVILEGES,GRANTOPTIONFROMuser.
.
.
语句的次数(一次性回收包含GRANTOPTION权限的所有权限的revoke语句)2.
16.
14.
Com_reset执行resetquerycache;resetslave[all];resetmaster;语句的次数2.
16.
15.
Com_resignal执行RESIGNAL[condition_value][SETsignal_information_item[,signal_information_item].
.
.
]修改MySQL错误的语句次数2.
16.
16.
Com_flush已执行了多少次FLUSH语句,所有flush语句的子句都会统计2.
16.
17.
Com_get_diagnostics执行GET[CURRENT|STACKED]DIAGNOSTICS语句的次数2.
16.
18.
Com_grant执行grant授权语句的次数2.
16.
19.
Com_optimize执行OPTIMIZE[NO_WRITE_TO_BINLOG|LOCAL]TABLEtbl_name[,tbl_name]语句的次数2.
16.
20.
Com_shutdown执行shutdown语句的次数,执行shutdown语句需要有shutdown权限,在MySQL5.
7.
9引入了该命令的SQL命令行接口级别的功能,和mysqladmin命令的的shutdown子句一样,用于关闭MySQLServer2.
16.
21.
Com_truncate执行TRUNCATE[TABLE]tbl_name语句的次数2.
16.
22.
Com_uninstall_plugin执行UNINSTALLPLUGINplugin_name语句的次数2.
16.
23.
Com_set_option执行set语句的次数,例如:set@x=1;、setsql_log_bin=1;2.
16.
24.
Com_signal执行SIGNALcondition_value[SETsignal_information_item[,signal_information_item].
.
.
]语句的次数(该语句可以根据需要在语句或存储过程中设置错误号打印)2.
16.
25.
Flush_commandsMySQLServer执行刷新表的次数,用户手工执行FLUSHTABLES语句和Server内部执行刷新表操作都会增加该状态变量的值,另外,MySQLServer接收COM_REFRESH数据包时该状态变量也会递增2.
16.
26.
Com_do执行DOexpr[,expr].
.
.
语句的次数,do语句与select语句类似,也可以执行语句,但是do语句不返回结果信息(注:do语句只能在执行一些函数的时候,如果不需要返回结果,就可以使用do语句来代替select)2.
16.
27.
Compression客户端连接Server端时是否使用压缩2.
17.
transaction2.
17.
1.
Com_begin执行begin;语句的次数,这里是指的是标记事务开始的begin,而不是存储过程或函数中的begin语句2.
17.
2.
Com_rollback_to_savepoint执行rollbacktosavepoint_name;语句的次数2.
17.
3.
Com_savepoint执行SAVEPOINTidentifier.
.
.
语句的次数2.
17.
4.
Com_stmt_execute执行execute语句的次数,该变量统计语句执行次数(语句格式:EXECUTEstmt_name[USING@var_name[,@var_name].
.
.
]),而不管语句是否执行成功,该变量对应旧的状态统计变量Com_execute_sql2.
17.
5.
Com_stmt_close关闭prepare语句的次数,该变量统计语句执行次数(语句格式:{DEALLOCATE|DROP}PREPAREstmt_name),而不管语句是否执行成功,该变量对应旧的状态统计变量Com_dealloc_sql2.
17.
6.
Com_stmt_fetch表示使用prepare语句时,从游标获取数据的次数,该变量统计语句执行次数,而不管语句是否执行成功2.
17.
7.
Com_stmt_prepare执行prepare语句的次数,该变量统计语句执行次数(语句格式:PREPAREstmt_nameFROMpreparable_stmt),而不管语句是否执行成功,该变量对应旧的状态统计变量Com_prepare_sql2.
17.
8.
Com_stmt_reprepare该变量表示在对prepare语句引用的表或视图的元数据更改后,服务器自动重新预编译prepare语句的次数.
reprepare操作同时增加Com_stmt_reprepare和Com_stmt_prepare状态变量值2.
17.
9.
Com_xa_commit执行XACOMMITxid[ONEPHASE].
.
.
语句的次数2.
17.
10.
Com_xa_end执行XAENDxid[SUSPEND[FORMIGRATE]].
.
.
语句的次数2.
17.
11.
Com_xa_prepare执行XAPREPARExid.
.
.
语句的次数2.
17.
12.
Com_xa_recover执行XARECOVER[CONVERTXID].
.
.
语句的次数2.
17.
13.
Com_xa_rollback执行XAROLLBACKxid.
.
.
语句的次数2.
17.
14.
Com_xa_start执行XA{START|BEGIN}xid[JOIN|RESUME].
.
.
语句的次数2.
18.
showsql2.
18.
1.
Com_show_charsets执行showcharset;或showcharactersetlike'%%';语句的次数2.
18.
2.
Com_show_collations执行SHOWCOLLATION[LIKE'pattern'|WHEREexpr].
.
.
语句的次数2.
18.
3.
Com_show_create_db执行SHOWCREATE{DATABASE|SCHEMA}[IFNOTEXISTS]db_name.
.
.
语句的次数2.
18.
4.
Com_show_create_event执行SHOWCREATEEVENTevent_name语句的次数2.
18.
5.
Com_show_create_func执行SHOWCREATEFUNCTIONfunc_name语句的次数2.
18.
6.
Com_show_create_proc执行SHOWCREATEPROCEDUREproc_name.
.
语句的次数2.
18.
7.
Com_show_create_table执行SHOWCREATETABLEtbl_name语句的次数2.
18.
8.
Com_show_create_trigger执行SHOWCREATETRIGGERtrigger_name语句的次数2.
18.
9.
Com_show_databases执行SHOW{DATABASES|SCHEMAS}[LIKE'pattern'|WHEREexpr].
.
.
语句的次数2.
18.
10.
Com_show_engine_logs执行showengineengine_namelogs语句的次数2.
18.
11.
Com_show_engine_mutex执行showengineengine_namemutex语句的次数2.
18.
12.
Com_show_engine_status执行showengineengine_namestatus语句的次数2.
18.
13.
Com_show_events执行SHOWEVENTS[{FROM|IN}schema_name][LIKE'pattern'|WHEREexpr].
.
.
语句的次数2.
18.
14.
Com_show_errors执行SHOWERRORS[LIMIT[offset,]row_count]语句的次数2.
18.
15.
Com_show_fields执行SHOW[FULL]{COLUMNS|FIELDS}{FROM|IN}tbl_name[{FROM|IN}db_name[LIKE'pattern'|WHEREexpr]语句的次数2.
18.
16.
Com_show_function_code执行SHOWFUNCTIONCODEfunc_name语句的次数,注意:需要MySQLServer使用--with-debug选项初始化2.
18.
17.
Com_show_function_status执行SHOWRUNCTIONSTATUS[like_or_where]'function_name';语句的次数2.
18.
18.
Com_show_grants执行SHOWGRANTS;SHOWGRANTSFORCURRENT_USER;SHOWGRANTSFORCURRENT_USER();语句的次数2.
18.
19.
Com_show_keys执行SHOWINDEXFROMtbl_name[FROMdb_name]语句的次数2.
18.
20.
Com_show_master_status执行showmasterstatus;语句的次数2.
18.
21.
Com_show_open_tables执行SHOWOPENTABLES[{FROM|IN}db_name][LIKE'pattern'|WHEREexpr]语句的次数2.
18.
22.
Com_show_plugins执行SHOWPLUGINS语句的次数2.
18.
23.
Com_show_privileges执行SHOWPRIVILEGES语句的次数2.
18.
24.
Com_show_procedure_code执行SHOWPROCEDURECODEproc_name语句的次数,注意:需要MySQLServer使用--with-debug选项初始化2.
18.
25.
Com_show_procedure_status执行SHOWPROCEDURESTATUS[like_or_where]'procedure_name';语句的次数2.
18.
26.
Com_show_processlist执行showprocesslist;语句的次数2.
18.
27.
Com_show_profile执行SHOWPROFILE[type[,type]FORQUERYn][LIMITrow_count[OFFSEToffset]]语句的次数type:ALL|BLOCKIO|CONTEXTSWITCHES|CPU|IPC|MEMORY|PAGEFAULTS|SOURCE|SWAPS.
.
.
.
.
.
Optionaltypevaluesmaybespecifiedtodisplayspecificadditionaltypesofinformation:oALLdisplaysallinformationoBLOCKIOdisplayscountsforblockinputandoutputoperationsoCONTEXTSWITCHESdisplayscountsforvoluntaryandinvoluntarycontextswitchesoCPUdisplaysuserandsystemCPUusagetimesoIPCdisplayscountsformessagessentandreceivedoMEMORYisnotcurrentlyimplementedoPAGEFAULTSdisplayscountsformajorandminorpagefaultsoSOURCEdisplaysthenamesoffunctionsfromthesourcecode,togetherwiththenameandlinenumberofthefileinwhichthefunctionoccursoSWAPSdisplaysswapcounts2.
18.
28.
Com_show_profiles执行SHOWPROFILES语句的次数2.
18.
29.
Com_show_relaylog_events执行SHOWRELAYLOGEVENTS[IN'log_name'][FROMpos][LIMIT[offset,]row_count]语句的次数2.
18.
30.
Com_show_slave_hosts执行SHOWSLAVEHOSTS语句的次数2.
18.
31.
Com_show_slave_status执行SHOWSLAVESTATUS[FORCHANNELchannel]语句的次数2.
18.
32.
Com_show_status执行SHOW[GLOBAL|SESSION]STATUS[LIKE'pattern'|WHEREexpr]语句的次数2.
18.
33.
Com_show_storage_engines执行SHOW[STORAGE]ENGINES语句的次数2.
18.
34.
Com_show_table_status执行SHOWTABLESTATUS[{FROM|IN}db_name][LIKE'pattern'|WHEREexpr]语句的次数2.
18.
35.
Com_show_tables执行SHOW[FULL]TABLES[{FROM|IN}db_name][LIKE'pattern'|WHEREexpr]语句的次数2.
18.
36.
Com_show_triggers执行SHOWTRIGGERS[{FROM|IN}db_name][LIKE'pattern'|WHEREexpr]语句的次数2.
18.
37.
Com_show_variables执行SHOW[GLOBAL|SESSION]VARIABLES[LIKE'pattern'|WHEREexpr]语句的次数2.
18.
38.
Com_show_warnings执行SHOWWARNINGS[LIMIT[offset,]row_count]语句的次数2.
18.
39.
Com_show_create_user执行SHOWCREATEUSERuser语句的次数2.
19.
performance_schema本小节状态变量含义请结合1.
9小节的系统变量含义进行阅读2.
19.
1.
Performance_schema_accounts_lost由于访问Server的账号数量超过了在accounts表中的记录(记录数量超过performance_schema_accounts_size系统变量设置的值)而无法添加到该表中的记录数2.
19.
2.
Performance_schema_cond_classes_lost由于加载的条件对象采集器数量超过了系统变量performance_schema_max_cond_classes的值而无法加载的条件对象采集器数量2.
19.
3.
Performance_schema_cond_instances_lost由于创建的条件对象实例数量超过了系统变量performance_schema_max_cond_instances的值而无法创建的条件对象采集器数量2.
19.
4.
Performance_schema_digest_lost无法在events_statements_summary_by_digest表中写入的摘要信息的数量.
如果performance_schema_digests_size的值太小,则此值可能就不为零2.
19.
5.
Performance_schema_file_classes_lost由于加载的文件对象采集器数量超过了系统变量performance_schema_max_file_classes的值而无法加载的文件对象采集器数量2.
19.
6.
Performance_schema_file_handles_lost由于打开的文件对象实例数量超过了performance_schema_max_file_handles系统变量设置的值而无法打开的文件对象实例的数量performance_schema_max_file_handles2.
19.
7.
Performance_schema_file_instances_lost由于创建的文件对象实例数量超过了performance_schema_max_file_instances系统变量设置的值而无法创建的文件对象采集器的数量2.
19.
8.
Performance_schema_hosts_lost由于访问Server的主机数量超过了在hosts表中记录的数据行数(超过了performance_schema_hosts_size系统变量设置的值)而无法添加到hosts表中的记录数2.
19.
9.
Performance_schema_index_stat_lost由于需要统计的索引数量超过了在表table_io_waits_summary_by_index_usage中的索引数量(超过了performance_schema_max_index_stat系统变量的值)而导致丢失统计的索引数量(无法添加到table_io_waits_summary_by_index_usage表)2.
19.
10.
Performance_schema_memory_classes_lost由于加载的内存采集器数量超过了performance_schema_max_memory_classes系统变量设置的值而无法加载的内存采集器的数量2.
19.
11.
Performance_schema_metadata_lock_lost由于metadata_locks中记录的MDL锁数量超过了performance_schema_max_metadata_locks系统变量设置的值而无法采集信息的MDL锁数量,如果performance_schema_max_metadata_locks设置太小,则会发现该状态变量可能不为02.
19.
12.
Performance_schema_mutex_classes_lost由于加载的互斥对象采集器数量超过了performance_schema_max_mutex_classes系统变量设置的值而无法加载的互斥对象采集器数量2.
19.
13.
Performance_schema_mutex_instances_lost由于创建的互斥对象实例超过了performance_schema_max_mutex_instances系统变量设置的值而无法创建的互斥对象采集器的数量2.
19.
14.
Performance_schema_nested_statement_lost丢失统计信息的存储程序语句数.
如果performance_schema_max_statement_stack的值太小,则此状态变量值可能不为零2.
19.
15.
Performance_schema_prepared_statements_lost由于创建的prepare语句数量超过了prapareperformance_schema_max_prepared_statements_instances系统变量的值而无法在prepared_statements_instances表中记录的prepare语句数量,prapareperformance_schema_max_prepared_statements_instances系统变量设置太小可能导致该状态变量不为02.
19.
16.
Performance_schema_program_lost丢失统计信息的存储程序数(注意与Performance_schema_nested_statement_lost状态变量区分,该状态变量统计丢失语句数量,而Performance_schema_program_lost统计的是丢失的存储程序数量).
如果performance_schema_max_program_instances的值太小,则此值可能不为零2.
19.
17.
Performance_schema_rwlock_classes_lost由于加载的rwlock采集器数量超过了performance_schema_max_rwlock_classes系统变量设置的值而无法加载的rwlock采集器的数量2.
19.
18.
Performance_schema_rwlock_instances_lost由于创建的rwlock实例数量超过了performance_schema_max_rwlock_instances系统变量设置的值而无法创建的rwlock采集器的数量2.
19.
19.
Performance_schema_session_connect_attrs_lost连接属性被清除的连接数.
对于给定连接,如果客户端发送的属性总大小大于performance_schema_session_connect_attrs_size系统变量值,则performance_schema会清理该连接的属性数据并递增Performance_schema_session_connect_attrs_lost状态变量值.
如果该状态变量不为0,则您可能需要增大performance_schema_session_connect_attrs_size系统变量的值2.
19.
20.
Performance_schema_socket_classes_lost由于加载的socket采集器超过了performance_schema_max_socket_classes系统变量的值而无法加载的socket采集器的数量2.
19.
21.
Performance_schema_socket_instances_lost由于创建的socket实例数量超过了performance_schema_max_socket_instances系统变量的值而无法监控的socket实例数量2.
19.
22.
Performance_schema_stage_classes_lost由于加载的阶段事件采集器的数量超过了performance_schema_max_stage_classes系统变量的值而无法加载的阶段事件采集器的数量2.
19.
23.
Performance_schema_statement_classes_lost由于加载的语句事件采集器的数量超过了performance_schema_max_statement_classes系统变量的值而无法加载的语句事件采集器的数量2.
19.
24.
Performance_schema_table_handles_lost由于打开的表文件句数量超过了performance_schema_max_table_handles系统变量的值而无法被监控的表句柄实例数量,如果performance_schema_max_table_handles系统变量设置太小可能导致该状态变量不为02.
19.
25.
Performance_schema_table_instances_lost由于创建的表对象采集器实例超过了performance_schema_max_table_instances系统变量设置的值而无法创建的表对象采集器实例的数量2.
19.
26.
Performance_schema_table_lock_stat_lost由于创建的表锁数量超过了performance_schema_max_table_lock_stat系统变量设置的值而无法统计的表锁数量,如果performance_schema_max_table_lock_stat系统变量设置太小,可能导致该状态变量不为02.
19.
27.
Performance_schema_thread_classes_lost由于加载的线程对象采集器数量超过了performance_schema_max_thread_classes系统变量的值而无法加载的线程对象采集器的数量2.
19.
28.
Performance_schema_thread_instances_lost由于创建的线程实例数量超过了threads表中的最大监控数量(超过了performance_schema_max_thread_instances系统变量设置的值)而无法被添加到threads表进行监控的线程数量,如果performance_schema_max_thread_instances系统变量设置的太小可能导致该状态变量不为02.
19.
29.
Performance_schema_users_lost由于访问Server的用户数量超过了users表的最大监控数量(超过了performance_schema_users_size系统变量的值)而无法被添加到users表进行监控的用户数量2.
20.
other2.
20.
1.
Com_dealloc_sql与状态变量Com_stmt_close含义相同,该变量为旧的状态变量2.
20.
2.
Com_execute_sql与状态变量Com_stmt_execute含义相同,该变量为旧的状态变量2.
20.
3.
Com_explain_other表示EXPLAIN[options]FORCONNECTIONconnection_id;语句执行的次数,例如:explainforconnection33;33为执行selectconnection_id()查询返回的结果2.
20.
4.
Com_prepare_sql与状态变量Com_stmt_prepare含义相同,该变量为旧的状态变量2.
20.
5Com_help执行help语句的次数2.
20.
6.
Com_preload_keys执行LOADINDEXINTOCACHEtb_name;语句的次数,表示重新加载索引到key_buffer中,注意,该语句只支持MyISAM引擎2.
20.
7.
Com_checksum执行CHECKSUMTABLEtb_name;语句的次数2.
20.
8.
Com_call_procedure执行CALLsp_name([parameter[语句调用存储过程的语句次数2.
20.
9.
Created_tmp_files表示mysqld进程创建了多少个临时文件2.
20.
10.
Delayed_errors表示延迟插入发生错误的次数,该变量即将废弃,在将来的版本中将会移除(因为后续版本会移除延迟插入功能)2.
20.
11.
Delayed_insert_threads表示延迟插入的线程数量,该变量即将废弃,在将来的版本中将会移除(因为后续版本会移除延迟插入功能)2.
20.
12.
Delayed_writes表示延迟插入的次数,该变量即将废弃,在将来的版本中将会移除(因为后续版本会移除延迟插入功能)2.
20.
13.
Innodb_num_open_filesInnoDB引擎当前打开的文件数量,注意:并不是表数量,也不是正在使用的表数量,打开文件数包含所有可能使用到的磁盘文件,例如:共享表空间,undo,redo,表空间文件等2.
20.
14.
Innodb_truncated_status_writesshowengineinnodbstatus;语句输出信息过长被截断的次数(默认输出长度限制为1MB)2.
20.
15.
Innodb_available_undo_logsInnoDB可用的回滚段总数,该总数还跟系统变量innodb_rollback_segments的设置有关(5.
6.
3版本之后该系统变量变更为innodb_undo_logs),MySQL5.
7引入临时表空间文件之后,使用独立Undo的时候,共享表空间中保留了1个undo段用于支持在线回收undolog,临时表空间中保留32个Undo段分配给临时表使用,所以Innodb_available_undo_logs状态变量输出的值不会小于>=33,但<=128(innodb_undo_logs取值范围为0-128)o该状态变量从MySQL5.
7.
19开始弃用,将在以后的版本中删除.
2.
20.
16.
Key_blocks_not_flushed在MyISAM引擎中,已经修改的键缓存块但还未刷新到磁盘的键缓存块数量2.
20.
17.
Key_blocks_unusedMyISAM的键缓存中未使用的块数量,你可以使用该状态变量的值来确定当前有多少未使用的键缓存o详见1.
1.
48中key_buffer_size系统变量介绍2.
20.
18.
Key_blocks_usedMyISAM键缓存中已使用块的数量,此值是一个高水位标记,表示使用过的最大块数量2.
20.
19.
Key_read_requests从MyISAM键缓存中读取索引块的请求数2.
20.
20.
Key_reads从磁盘物理读取索引块并写入到MyISAM键缓存中的次数.
如果Key_reads很大,那么key_buffer_size系统变量的值可能太小了.
使用公式:Key_reads/Key_read_requests可以计算MyISAM键缓存的命中率2.
20.
21.
Key_write_requests将索引块写入到MyISAM键缓存中的请求数2.
20.
22.
Key_writes将索引块从MyISAM键缓存中写入到磁盘的物理写的请求数2.
20.
23.
Last_query_cost查询优化器计算的查询的总开销成本.
可用于比较同一查询在不同查询计划下的成本开销.
默认值为0,表示当前会话未执行过任何查询(Last_query_cost为会话级别状态变量),注意:这里指的是查询表数据形式的查询,不是show语句oLast_query_cost值只能为简单查询提供成本开销的精确计算,不能对复杂的查询进行计算成本开销,例如:带有子查询或UNION子句的查询,对于复杂查询,该状态变量为02.
20.
24.
Last_query_partial_plans查询优化器在查询语句真正执行之前,在执行计划构造中进行的迭代次数.
该状态变量为会话级别变量2.
20.
25.
Locked_connects从版本5.
7.
6开始,MySQL支持使用ACCOUNTLOCK和ACCOUNTUNLOCK子句在执行CREATEUSER和ALTERUSER语句时指定是否要锁定和解锁用户帐户,如下:o与CREATEUSER一起使用时,ACCOUNTLOCK和ACCOUNTUNLOCK子句指定新帐户的初始锁定状态.
如果不指定,则新建的账号默认为未锁定状态o与ALTERUSER一起使用时,ACCOUNTLOCK和ACCOUNTUNLOCK子句指定已存在帐户的新锁定状态.
如果不指定,则已存在帐户的锁定状态保持不变o帐户的锁定状态记录在mysql.
user表的account_locked列中.
使用SHOWCREATEUSER语句可以查看到帐户的锁定状态o如果客户端使用处于锁定状态的账号尝试连接到Server时,则会提示ER_ACCOUNT_HAS_BEEN_LOCKED错误,并将错误写入错误日志.
同时Server递增Locked_connects状态变量值,表示客户端使用锁定账号尝试连接的次数oServer中存储过程或存储函数内部调用,或者使用代理用户进行连接的,则这些请求访问不受用户的锁定状态的影响,要注意:如果要从旧版本升级到MySQL5.
7.
6及更高版本,请运行mysql_upgrade以确保mysql.
user表的account_locked列存在.
否则对于没有account_locked列的账号,Server会视为已解锁状态,且在使用ACCOUNTLOCK或ACCOUNTUNLOCK子句会产生错误2.
20.
26.
Max_execution_time_exceeded执行时间超时(超过max_execution_time系统变量设置的值--秒数)的select语句数量2.
20.
27.
Max_execution_time_set当设置了max_execution_time为非零值时,或者使用了MAX_EXECUTION_TIME(指定了非零值)优化器提示的语句的select语句中,执行时间超过了设定的时间的语句数量2.
20.
28.
Max_execution_time_set_failed尝试设置一个超时时间失败的语句数量2.
20.
29.
Max_used_connections自服务器启动以来的最大并发连接数2.
20.
30.
Max_used_connections_timeMax_used_connections状态变量当前值的更新时间,即,Max_used_connections状态变量是什么时候达到最高值的2.
20.
31.
Not_flushed_delayed_rows延迟插入相关的状态变量,将废弃,因为延迟插入功能将废弃2.
20.
32.
Ongoing_anonymous_transaction_count显示已标记为匿名的正在进行的匿名事务的数量.
这可用于查看确保没有匿名事务等待处理(在MySQL5.
7版本之后的GTID在线切换时需要用于查看确保无匿名事务需要处理)2.
20.
33.
Open_filesMySQLServer层打开的文件数.
包括MySQLServer层打开的常规文件,但不包括其他类型的文件(如套接字或管道).
另外,它也不包括存储引擎使用自己的内部功能打开的文件,这些文件由存储引擎自己进行统计2.
20.
34.
Open_streams打开的日志流的数量,主要用于日志记录2.
20.
35.
Open_table_definitions当前缓存的.
frm表定义文件数量2.
20.
36.
Open_tables当前打开的表数量2.
20.
37.
Opened_files自MySQLServer启动以来使用my_open()函数(mysys库函数)打开的文件数.
如果不使用此函数打开的文件在MySQLServer中不会进行统计2.
20.
38.
Opened_table_definitions从MySQLServer启动以来缓存的总的.
frm文件数量2.
20.
39.
Opened_tables自MySQLServer启动起来总的已打开的表的数量.
如果Opened_tables状态变量值很大,则table_open_cache系统变量值可能设置太小了2.
20.
40.
Prepared_stmt_count当前prepare语句的数量(prepare最大语句数由max_prepared_stmt_count系统变量设置)2.
20.
41.
Qcache_free_blocksQC查询缓存中的空闲内存块数量,QC功能不推荐使用,在8.
0中已废除2.
20.
42.
Qcache_free_memoryQC查询缓存中的空闲内存字节数,与Qcache_free_blocks状态变量一样,8.
0中已废除2.
20.
43.
Qcache_hitsQC查询缓存的命中次数,与Qcache_free_blocks状态变量一样,8.
0中已废除2.
20.
44.
Qcache_inserts被添加到QC查询缓存中的查询数量,与Qcache_free_blocks状态变量一样,8.
0中已废除2.
20.
45.
Qcache_lowmem_prunes由于内存不足而从QC查询缓存中删除的查询数,与Qcache_free_blocks状态变量一样,8.
0中已废除2.
20.
46.
Qcache_not_cached未缓存的查询数(由于query_cache_type系统变量的设置导致无法缓存或其他愿意导致未缓存的查询数),与Qcache_free_blocks状态变量一样,8.
0中已废除2.
20.
47.
Qcache_queries_in_cache在QC查询缓存中注册的查询数,与Qcache_free_blocks状态变量一样,8.
0中已废除2.
20.
48.
Qcache_total_blocksQC查询缓存中的总块数,与Qcache_free_blocks状态变量一样,8.
0中已废除2.
20.
49.
Ssl_accept_renegotiates建立SSL连接需要协商的次数2.
20.
50.
Ssl_accepts已接受的SSL连接的次数2.
20.
51.
Ssl_callback_cache_hits回调命中缓存的次数2.
20.
52.
Ssl_cipher当前加密连接的加密密码(对于未加密的连接该状态变量显示为空)2.
20.
53.
Ssl_cipher_list可能的SSL加密密码列表(对于非SSL连接该状态变量为空)2.
20.
54.
Ssl_client_connects在启用了SSL的MySQLmaster中的SSL连接尝试的次数2.
20.
55.
Ssl_connect_renegotiates与一个启用SSL的主库建立SSL连接所需要的协商次数2.
20.
56.
Ssl_ctx_verify_depthSSL上下文验证深度(链中测试验证的证书数量)2.
20.
57.
Ssl_ctx_verify_modeSSL上下文验证模式2.
20.
58.
Ssl_default_timeout默认的SSL超时时间2.
20.
59.
Ssl_finished_accepts成功以SSL方式连接到Server的数量2.
20.
60.
Ssl_finished_connects成功使用SSL方式与主库建立连接的从库数量2.
20.
61.
Ssl_server_not_afterSSL证书有效的结束日期.
要检查SSL证书过期信息,可以通过该状态变量查询2.
20.
62.
Ssl_server_not_beforeSSL证书有效有效期的起始日期2.
20.
63.
Ssl_session_cache_hits缓存命中的SSL会话数量2.
20.
64.
Ssl_session_cache_misses缓存未命中的SSL会话数量2.
20.
65.
Ssl_session_cache_modeSSL会话缓存模式2.
20.
66.
Ssl_session_cache_overflowsSSL会话缓存溢出的数量2.
20.
67.
Ssl_session_cache_sizeSSL会话缓存大小2.
20.
68.
Ssl_session_cache_timeoutsSSL会话缓存超时的数量2.
20.
69.
Ssl_sessions_reused显示有多少个连接是从缓存中重用的连接2.
20.
70.
Ssl_used_session_cache_entries使用了多少SSL会话缓存记录2.
20.
71.
Ssl_verify_depth复制SSL连接的验证深度2.
20.
72.
Ssl_verify_modeMySQLServer用于验证使用了SSL的连接的验证模式.
该值是一个位掩码;在openssl/ssl.
h头文件中定义,如下#defineSSL_VERIFY_NONE0x00#defineSSL_VERIFY_PEER0x01#defineSSL_VERIFY_FAIL_IF_NO_PEER_CERT0x02#defineSSL_VERIFY_CLIENT_ONCE0x04SSL_VERIFY_PEER表示MySQLServer要求提供客户端证书.
如果客户端提供了证书,则MySQLServer使用该证书进行验证,验证成功之后继续往下执行后续步骤.
SSL_VERIFY_CLIENT_ONCE表示仅在初始握手中完成对客户端证书的请求2.
20.
73.
Ssl_version连接使用的SSL协议版本:例如,TLSv1.
如果连接使用SSL未加密,则该状态变量值为空2.
20.
74.
Tc_log_max_pages_used当使用mysqld作为内部XA事务恢复的协调器时,需要使用到日志与内存的映射实现功能,此状态变量表示自MySQLServer启动以来日志使用的最大页数量,如果Tc_log_max_pages_used和Tc_log_page_size的乘积总是明显小于日志大小,则需要使用--log-tc-size启动选项增加tclog的大小,通常该功能不使用,除非在MySQLServer中存在两个以上的支持两阶段提交的且支持XA事务的存储引擎(当前在MySQLServer中InnoDB是唯一即支持两阶段提交又支持XA事务的引擎)2.
20.
75.
Tc_log_page_size用于XA恢复日志的内存映射功能的页面大小.
默认大小使用getpagesize()函数确定.
该状态变量当前并未使用,原因与Tc_log_max_pages_used状态变量相同2.
20.
76.
Tc_log_page_waits该状态变量也是基于tclog日志恢复的内存映射功能相关的状态变量,该状态变量表示每次MySQLServer因为必须等待日志中的空闲页的次数,如果该值较大,则说明需要使用--log-tc-size启动选项调整tclog的大小.
对于基于二进制日志的恢复,每次因为正在执行两阶段提交而无法关闭二进制日志时,此状态变量也会递增(关闭二进制日志的操作将一直等到所有此类事务全部提交完成才能成功执行)2.
20.
77.
Uptime自MySQLServer启动以来运行的总时间(单位秒)2.
20.
78.
Uptime_since_flush_status自最近执行过FLUSHSTATUS语句以来MySQLServer运行的总时间(单位秒)2.
21.
group_replication2.
21.
1.
Com_group_replication_start执行startgroup_replication;语句的次数2.
21.
2.
Com_group_replication_stop执行stopgroup_replication;语句的次数2.
21.
3.
group_replication_primary_member单主模式运行时,在集群节点中查看该状态变量显示主要成员的UUID.
如果多主模式运行,则该状态变量显示为空字符串editors沃趣科技-罗小波