绿色记忆:MySQL知识集锦

MySQL知识集锦

By Alex

/ in MySQL

0 Comments

常见术语

术语

说明

change buffer

变更缓冲。由于insert、delete、purge等操作导致相应的索引变化需要写入，这些变化往往意味着随机I/O，为避免性能问题，后台线程周期性的执行索引变更的写入

dirty page

在InnoDB缓冲池中，已经被修改的，但是尚未刷出到数据文件的页，相对的叫clean page

double buffer

双重缓冲是InnoDB引入的文件刷出技术，在把页写入到数据文件之前，首先将其写到一个连续的、称为双重缓冲的区域。只有在这一写入完毕后，InnoDB才把页写到数据文件的相应位置。如果在写数据文件期间出现OS、存储子系统或者MySQL进程的崩溃，InnoDB可以很好的从双重缓冲恢复数据

extent

表空间中的一组页，对于默认的16KB页，一个extend包含64页。对于4/8/16KB的页，extent的大小始终为1MB

Record Lock

记录锁，是InnoDB行锁的一种，锁定一个索引记录（Index record）。记录锁总是锁定索引而不是数据行本身，即便表没有任何索引，InnoDB也会创建一个隐藏的聚簇索引，并用该索引执行记录的锁定

gap lock

间隙锁，是InnoDB行锁的一种。在2个索引记录之间的间隙上的锁定，或者在第一个索引记录之前、最后一个索引记录之后的间隙上的锁定

扫描普通索引时，InnoDB会对当前索引值与下一个索引值之间的间隙进行锁定

如果间隙锁导致了性能问题，可以设置隔离级别为READ-COMMITTED或者开启innodb_locks_unsafe_for_binlog，这相当于禁用索引扫描/搜索的间隙锁，间隙锁将仅仅被用于外键约束检查或者重复键检查

Next-key lock

下一键锁。默认情况下InnoDB工作在隔离级别 REPEATABLE READ下，并且系统变量innodb_locks_unsafe_for_binlog=OFF，这种设置下InnoDB使用下一键锁来进行索引扫描/搜索，同时避免幻影读问题。

下一键锁联合使用了Record lock以及Gap lock，在锁定住索引记录的同时，该索引记录前面的gap也被锁定。因此，如果一个会话持有索引记录R的共享或者独占锁，那么其它会话无法插入一个直接位于R之前（按索引顺序）的记录

下面的例子说明下一键锁的效果：

select id, col from tab; -- 测试表，id为聚簇索引，col为普通辅助索引

+----+------+

| id | col |

+----+------+

| 10 | 10 |

| 15 | 15 |

| 16 | 16 |

| 20 | 20 |

+----+------+

-- session 1

start transaction;

-- 下面两行都可以用来测试，都获得写锁

select * from tab where id=10 for update;

update tab set col=10 where id=10;

-- session 2

start transaction;

update tab set col=10 where id=10; -- 超时，记录锁

insert tab (id,col) values (5, 5);

-- 成功，聚簇索引、唯一性索引不引入上一键锁，仅使用记录锁

-- session 1

start transaction;

-- 下面改用辅助索引执行检索

-- 如果col没有创建索引，这将导致锁整个表

update tab set col=10 where col=10; -- Next-Key范围：(-∞,10]

-- session 2

start transaction;

insert tab (id,col) values (5, 5); -- 超时，处于下一键锁范围

update tab set id=15 where col=15; -- 成功

insert tab (id,col) values (13, 13); -- 超时，辅助索引下一键值的间隙锁(10,15)

update tab set col=13 where col=13; -- 成功，记录不存在的更新不会导致锁等待

-- session 1

start transaction;

update tab set col=15 where col=15; -- Next-Key范围(10,15]

-- session 2

start transaction;

insert tab (id,col) values (13, 13); -- 超时，处于下一键锁范围

-- session 1

start transaction;

update tab set col=20 where col=20; -- Next-Key范围：(16,20]

-- session 2

start transaction;

update tab set col=16 where col=16; -- 成功，不在Next-Key范围

insert tab (id,col) values (17,17); -- 超时，处于下一键锁范围

insert tab (id,col) values (22,22); -- 超时，辅助索引的下一键值的间隙锁

-- session 1

start transaction;

-- 由于此行的插入尚未提交，不会使用下一键锁

insert tab (id,col) values (22,22); -- 下一键范围：(20, +∞)

-- session 2

start transaction;

insert tab (id,col) values (22,22); -- 超时

update tab set col=20 where col=20; -- 成功

insert tab (id,col) values (21,21); -- 成功

insert tab (id,col) values (23,23); -- 成功

insert tab (id,col) values (25,25); -- 成功

metadata lock

5.5.3+引入元数据锁定机制，来管理对数据库对象（而不是表中的数据）的并发访问，并保证数据一致性（防止结构性变更）。除了表以外，元数据锁定还应用到schema、存储对象（procedures、functions、triggers、scheduled events）。下面是元数据锁工作方式的示例：

-- 事务1

start transaction;

select * from tab1;

select * from tab2;

-- 事务1得到tab1、tab2的元数据锁，在事务提交前，这些锁不会释放

-- 事务2

-- 其它事务如果在元数据锁定表上执行DDL或者写锁，会被阻塞

start transaction;

drop table tab1; -- 失败

alter table tab2; -- 失败

lock table tab1 write; -- 失败

在5.5.3版本之前，元数据锁的等价机制会在语句级别得到并释放结构性锁

log

在MySQL中，日志可能指：

重做日志（redo log），在InnoDB的上下文中，日志典型的是指位于ib_logfile*文件的重做日志
撤销日志（undo log），在InnoDB的上下文中，日志还可能指是指位于系统表空间或者撤销表空间的撤销日志
错误日志（error log），记录启动时的诊断信息或者运行时错误
二进制日志（binary log），用于支持主从复制，或者基于时间点的恢复
一般查询日志（general query log），用于诊断应用程序问题
缓慢查询日志（slow query log ），用于记录执行缓慢的语句

log buffer

主要针对InnoDB重做日志，这是一个内存区域，其中包含将要写入到ib_logfile*文件的数据。其大小由innodb_log_buffer_size控制

log file

主要针对InnoDB重做日志，即ib_logfile*文件

log group

主要针对InnoDB重做日志，日志组，通常由ib_logfile0、ib_logfile1等组成

page

页，用来表示在任何时候，InnoDB在磁盘（数据文件）和内存（缓冲池）之间传递数据的最小单位。每个页存放1-N行数据，如果1行数据都放不下，InnoDB会创建额外的指针结构，确保行的相关信息被存放在一个页中

当InnoDB批量读写页以提高吞吐量时，每次执行一个extent

在5.6之前，InnoDB页大小固定为16KB，此后则可以是4KB、8KB或者16KB，由innodb_page_size控制

purge

由专用线程周期性执行的垃圾清理工作：

从索引中移除无用的值
物理的移除由先前delete语句删除的的数据行

redo log

即InnoDB的重做日志，它是一种基于磁盘的数据结构，用于在崩溃恢复时纠正被不完整事务写入的数据。在常规操作中，重做日志编码并存储对InnoDB表数据的写请求（这些请求可能来自SQL语句或者低级别的API）为redo，在宕机恢复时，尚未来得及写入到数据文件的写操作被回放，并写入到数据文件。

在5.6.3之前，重做日志文件最大4GB，此后可达512GB

重做日志的磁盘布局受到innodb_log_file_size、innodb_log_group_home_dir、 innodb_log_files_in_group的控制，最后一个很少使用

row lock

锁定一行数据，防止其他事务对其执行不兼容的访问，表中的其他行可以被自由访问。行锁由InnoDB的DML操作自动发起

savepoint

保存点，用于帮助实现嵌套事务

segment

段，InnoDB表空间中的一部分，如果用目录类比表空间，那么段相当于文件，段可以增长、新建。

对于每表一文件模式，表数据在一个段中，而每一个关联的索引在各自的段中；对于所有表一文件模式，所有表的段都位于系统表空间中。系统表空间还会包括一个或者多个回滚段，供撤销日志使用

随着数据的插入和删除，段会自动增长和收缩。当段需要额外的空间时，它以extent为单位（1MB）增加；反之，当一个extent中的数据都不需要时，这个extent的空间被释放

Temporary Tables

某些情况下MySQL服务器会在创建内部使用的临时表，以便处理查询。临时表可能基于memory引擎被存放在内存中；或者基于MyISAM引擎并存放在磁盘中。服务器往往最初把临时表存放在内存中，当表过大时，转移到磁盘中

临时表的使用场景包括：

union查询会使用到临时表
某些视图会用到临时表，比如基于TEMPTABLE处理算法的视图，或者使用了union查询、聚合函数的视图
当order by和group by 从句不同时，或者order by、group by 包含来自非首个表（按join queue顺序）的列时，使用临时表
distinct和order by 联用可能需要临时表
使用选项SQL_SMALL_RESULT时，MySQL可能使用内存临时表
基于多表的update语句
GROUP_CONCAT()、COUNT(DISTINCT)
衍生表（来自from从句的子查询）

如果需要确定某个查询语句是否使用了临时表，可以通过解释计划的Extra列判断，Using temporary提示使用了临时表，但是注意解释计划可能不对衍生表或者物化临时表显示Using temporary

undo

撤销。在事务的整个生命周期内被维护的日志信息，该日志信息记录了所有数据库改变，以便在回滚发生时，这些改变能够被撤销。该日志被存放在位于系统表空间或者独立的undo表空间中的撤销日志中

undo log

撤销日志，也称为undo buffer。存放被活动事务修改前的数据的副本，如果其他事务需要读取原始数据，MySQL将从undo log中获取。默认撤销日志位于系统表空间中。从MySQL 5.6开始，可以配置innodb_undo_tablespaces、innodb_undo_directory来将撤销日志分散到1-N个独立的表空间文件中，并可选的存放在独立的硬盘，例如SSD中

撤销日志被划分为不同的部分，包括insert undo buffer和update undo buffer

常用命令/SQL

命令

说明

show full processlist;

显示线程列表。如果显示Copying to tmp table，或者时间太大，要注意语句性能问题

show variables;

显示服务器系统变量

-- 显示字符集信息

show variables like 'character_set_%';

-- 显示MySQL版本信息

show variables like "%version%";

-- 显示当前会话的自动提交状态

show variables like 'autocommit';

show status;

显示服务器状态变量，连同session范围的计数器

-- 显示innodb锁统计信息

show status like 'innodb_row_lock_%';

-- 显示innodb缓冲池信息

show status like 'innodb_buffer_pool_%';

-- 显示最后一次查询的成本

show status like 'Last_query_cost';

flush status;

刷空多种统计性的服务器状态变量

show global status;

显示服务器启动以来的计数器

show innodb status;

show engine innodb status;

显示innodb实时状态信息

5.6使用

show table status;

显示当前数据库的表状态，列信息：

Name 表的名称
Engine 表的存储引擎
Row_format 行格式，对于MyISAM表，可以是Dynamic, Fixed, Compressed。如果具有VARCHAR、BLOB列则是Dynamic，Compressed仅存在于MyISAM压缩表
Rows 表的行数，对于MyISAM和大部分引擎，此值是精确的，对于InnoDB则是估算的
Avg_row_length 平均每行的Byte数
Data_length 整个表数据的Byte数
Max_data_length 表持有数据的最大量
Index_length 索引消耗的磁盘空间
Auto_increment 下一个AUTO_INCREMENT值
Create_time 表创建时间
Update_time 表数据最后变化时间
Check_time 表最后一次使用 CHECK TABLE或myisamchk检查的时间
Collation 表的字符集
Checksum 整个表的校验和
Create_options 表创建时指定的任何其他的选项
Comment 包含大量额外信息

select @@global.tx_isolation;

显示系统的事务隔离级别

alter table t_rt_val_his engine=myisam;

修改表的引擎

show create table t_table;

显示DDL语句

mysql -uuser -ppasswd dbname < path

数据库导入命令

-h MySQL主机名或IP
-P MySQL监听端口
-u用户名
-p密码
--default-character-set=字符集，应和导出时设置一致，导出未设置，可以尝试latin1

mysqldump -uuser -ppasswd dbname

[tab1 tab2 tab3……] > path

数据库导出命令，如果不指定表名，则导出整个库

-h MySQL主机名或IP
-P MySQL监听端口
-u用户名
-p密码
--hex-blob 导出HEX格式的blob字段
--default-character-set=字符集
--routines 包含例程（存储过程、函数）
--triggers 包含触发器
--add-drop-table 为每个create table语句添加drop语句
--add-drop-database 为每个create database语句添加drop语句
--add-drop-trigger 为每个create trigger语句添加drop语句
--add-locks 为每个表的dump前后添加lock tables/unlock tables语句，当dump文件reload时获得更快的插入速度
--all-databases Dump出所有数据库的所有表
--complete-insert Dump出的插入语句包含完整的列名
--compress 如果客户端、服务器都支持，启用压缩传输
--databases 指定需要导出的数据库
--no-autocommit 不自动提交每条语句，在每个表所有语句尾部提交一次

start transaction;
select @a:=sum(salary) from t1;
update t2 SET sum=@a;
commit;

命令行中开启事务

set autocommit=0;

在会话（当前连接）中明确禁止自动提交

set profiling = 1;
show profiles ;
show profile for query 1;

启用剖析
显示剖析统计信息
显示指定查询的各步骤的耗时

参考：剖析单个查询

set session transaction isolation level read committed;

在会话（当前连接）中设置事务隔离级别

check table mytable;
repair table mytable;

检查表上的错误
修复MyISAM表

explain

解释执行计划

explain extended select * from tab;
show warnings; --- 可以看到被重构后的SQL语句

explains partitions --- 检查使用的分区表

mysqld

mysqld是MySQL服务器端的主程序，使用mysqld --verbose --help | less 可以查看详细帮助

--defaults-file=/path-to/my.cnf 指定配置文件

create database

创建新的数据库，例如： create database newdb;

mysql_install_db

初始化MySQL数据库。5.7之前，会创建一个空密码的本地root用户，5.7之后使用随机密码，密码存放在～/.mysql_secret中。举例：

cd mysql

adduser mysql

chown -R mysql:mysql data

scripts/mysql_install_db --basedir=. --datadir=data

bin/mysqld_safe --defaults-file=my.cnf &

用户管理命令

创建

在MySQL中 create user 语句用于创建用户，相应的 alter user 、 drop user 用于修改、删除用户。MySQL在 mysql.user 表中存储用户。

语法格式

CREATE USER [IF NOT EXISTS]

-- 目标规格，格式 user [ auth_option ]

-- 其中user必须是'username'@'host'格式，引号可以省略

-- host中可以出现%，用于通配，例如192.168.0.%通配所有192.168.0子网的主机

-- %.gmem.cc则通配所有gmem.cc下的主机

-- auth_option指定身份验证方式，可以是：

-- IDENTIFIED BY 'auth_string' | DENTIFIED BY PASSWORD 'hash_string'

-- IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY 'auth_string'

-- IDENTIFIED WITH auth_plugin AS 'hash_string'

-- 其中auth_string表示明文，hash_string则为身份验证插件处理后的散列值，皆存放在authentication_string字段

user_specification [, user_specification] ...

-- tsl_option，指定与SSL相关的选项，语法和CREATE USER相同，可以是:

-- SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'

[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]

-- resource_option，指定用户的资源配额，可以是：

-- MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count

-- MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count

[WITH resource_option [resource_option] ...]

-- password_option 密码选项，可以是：

-- PASSWORD EXPIRE | PASSWORD EXPIRE DEFAULT

-- PASSWORD EXPIRE NEVER | PASSWORD EXPIRE INTERVAL N DAY

-- lock_option 账户锁定选项，可以是：

-- ACCOUNT LOCK | ACCOUNT UNLOCK

[password_option | lock_option] ...

举例

-- 创建一个可从所有主机上登录的用户

create user 'newuser'@'%' identified by 'newpasswd';

-- 创建一个可从192.168.0网段登录的用户

create user 'newuser'@'192.168.0.%' identified by 'newpasswd';

-- 创建一个只能从服务器本机登录的用户，不授予密码

create user 'newuser'@'localhost';

修改密码

SET PASSWORD [FOR user] = PASSWORD('auth_string');

# 举例

set password for gmem = password('gmem');

授权

在MySQL中， grant 语句用于给MySQL用户授权，相应的 revoke 命令用于收回权限。

语法格式

-- 要给其它用户授权，当前用户必须具有GRANT OPTION权限，同时拥有那些即将授出的权限

-- 如果系统变量read_only启用，则需要额外的权限SUPER

GRANT

-- 权限类型，可以指定多个。括号内的列列表，用于指定特殊的权限级别——列

priv_type [(col_list)] [,priv_type [(col_list)]]...

-- 可选的对象类型，可以是：TABLE、FUNCTION、PROCEDURE

[object_type]

-- 权限级别，授权应用到哪个库的哪些对象，可以是：

priv_level

-- 目标用户，格式 user [ auth_option ]

-- 其中user为'username'@'host'格式

-- auth_option可以是：

-- IDENTIFIED BY 'auth_string' | DENTIFIED BY PASSWORD 'hash_string'

-- IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY 'auth_string'

-- IDENTIFIED WITH auth_plugin AS 'hash_string'

user_specification [, user_specification] ...

-- tsl_option，指定与SSL相关的选项，语法和CREATE USER相同，可以是:

-- SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'

[REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]

-- GRANT OPTION 表示新用户可以向其它人授权

-- resource_option，指定用户的资源配额，可以是：

-- MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count

-- MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count

[WITH {GRANT OPTION | resource_option} ...];

权限类型（priv_type）

MySQL支持以下权限类型：

权限	说明
ALL [PRIVILEGES]	授予所有权限，除了GRANT OPTION
ALTER	允许执行ALTER TABLE，支持的权限级别：全局、数据库、表
ALTER ROUTINE	允许修改或删除存储过程（stored routines），支持的权限级别：全局、数据库、存储过程（procedure）
CREATE	允许创建数据库、表，支持的权限级别：全局、数据库、表
CREATE ROUTINE	允许创建存储过程，支持的权限级别：全局、数据库
CREATE TABLESPACE	允许创建、修改、删除表空间和日至文件组，支持的权限级别：全局
CREATE TEMPORARY TABLES	允许执行CREATE TEMPORARY TABLE，支持的权限级别：全局、数据库
CREATE USER	运行执行fCREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES，支持的权限级别：全局
CREATE VIEW	允许创建或修改视图，支持的权限级别：全局、数据库、表
DELETE	允许执行DELETE，支持的权限级别：全局、数据库、表
DROP	允许删除数据库、表、视图，支持的权限级别：全局、数据库、表
EVENT	允许使用事件调度机制（例如CREATE EVENT...），支持的权限级别：全局、数据库
EXECUTE	允许执行存储过程，支持的权限级别：全局、数据库、表
FILE	允许导致服务器读写文件，支持的权限级别：全局
GRANT OPTION	允许向其它用户授权，支持的权限级别：全局、数据库、表、存储过程、代理
INDEX	允许创建或删除索引，支持的权限级别：全局、数据库、表
INSERT	允许执行INSERT语句，支持的权限级别：全局、数据库、表、列
LOCK TABLES	允许对具有SELECT权限的表执行LOCK TABLES，支持的权限级别：全局、数据库
PROCESS	允许通过SHOW PROCESSLIST查看所有进程，支持的权限级别：全局
PROXY	允许使用用户代理
REFERENCES	允许创建外键，支持的权限级别：全局、数据库、表、列
RELOAD	允许执行FLUSH操作，支持的权限级别：全局
REPLICATION CLIENT	允许用户询问master/slave服务器在哪里，支持的权限级别：全局
REPLICATION SLAVE	允许从二进制日志中读取来自master的事件记录，支持的权限级别：全局
SELECT	允许执行SELECT语句，支持的权限级别：全局、数据库、表、列
SHOW DATABASES	允许执行SHOW DATABASES，支持的权限级别：全局
SHOW VIEW	允许执行SHOW CREATE VIEW，支持的权限级别：全局、数据库、表
SHUTDOWN	允许执行命令mysqladmin shutdown，支持的权限级别：全局
SUPER	允许使用其它管理性操作，例如 CHANGE MASTER TO、KILL、PURGE BINARY LOGS、SET GLOBAL以及执行mysqladmin debug命令，支持的权限级别：全局
TRIGGER	允许创建、删除表上的触发器，支持的权限级别：全局、数据库、表
UPDATE	允许执行UPDATE语句，支持的权限级别：全局、数据库、表、列
USAGE	无特权（no privileges）的同义词

举例

-- 把数据库database的object对象的全部权限授予newuser，并允许他把这些权限授予别人

grant all privileges on database.object to 'newuser'@'%' with grant option;

-- 把所有数据库的所有权限授予newuser

grant all privileges on *.* to 'newuser'@'%';

常用函数

MySQL函数

说明

CURDATE

以YYY-MM-DD或YYYYMMDD格式得到当前日期，示例：

-- 今年的第一天

SELECT CONCAT(YEAR(CURDATE()),'-01-01') FROM dual;

-- 得到YYYYMMDD格式的整数

SELECT CURDATE() + 0 FROM dual;

CURTIME

以H:MM:SS或者HHMMSS.uuuuuu格式得到当前时间

MONTH

从日期中抽取月份字段，示例：

1	SELECT MONTH(CURDATE()) FROM dual;

YEAR

从日期中抽取4位数的年度字段

CONCAT

连接多个字符串，示例：

1 2	SELECT CONCAT('My', 'SQL') FROM dual; SELECT CONCAT(6.12) FROM dual;

MySQL配置与服务器状态

系统变量（System Variables）

MySQL系统变量包含一系列配置信息，它们都具有默认值，并且大部分可以在运行时使用SET命令动态的修改，在表达式中，亦可引用系统变量。可以通过多种方式来查看这些系统变量的值：

-- 使用mysqladmin命令查看

mysqladmin variables;

-- 通过information_schema数据库查看

select * from information_schema.SESSION_STATUS;

-- 查看包含了编译时默认值和配置文件合并的系统参数列表

mysqld --verbose --help

-- 查看编译时默认值的系统参数列表

mysqld --no-defaults --verbose --help

可配置系统变量和选项

这些变量和选项（以下统称参数）用来定制MySQL服务器的配置，或者优化性能。基于文件进行配置时，应该把参数列在my.cnf的[mysqld]段。下面列出MySQL 5.6版本的参数说明：

选项

说明

abort_slave_event_count

默认0。如果设置为正数N，那么slave的SQL线程启动后，能接收来自master的N个事件，之后不再接收，就好像与master之间的网络被断开一样

archive

默认ON。是否启用ARCHIVE插件

auto_increment_increment

默认1， 1_65535之间。自增长列的递增值

auto_increment_offset

默认1， 1_65535之间，不得大于auto_increment_increment。确定自增长列的值的起始点，仅在auto_increment_increment !=1时有意义。第N行的自增长列的取值按：auto_increment_offset + N × auto_increment_increment计算

注意：当该参数被修改时，新生成的自增长列值的计算，不会依赖于既有的列值，还是固定按上述公式得出

autocommit

默认TRUE，注意TRUE/FALSE可以用1/0代替，下同。是否在每一个语句执行后自动提交

back_log

默认80。排队中连接请求的最大数目，当MySQL服务器服务大量短时连接时，可以调整该参数

basedir

默认$INS_DIR。MySQL安装目录，很多其它目录通常相对于该目录解析

big_tables

默认FALSE。通过存储到文件来允许大结果集，可以避免大部分"table full"错误

bind_address

默认0.0.0.0。绑定的监听地址

binlog_cache_size

默认32768。二进制日志中，用于事务性引擎的事务性缓存的大小。如果经常使用包含大量语句的事务，可以增加该参数的值

binlog_direct_non_transactional_updates

默认FALSE。如果使用语句格式的二进制日志，打开此选项后，非事务性引擎执行的更新会直接写入二进制日志。注意，打开此选项必须确保事务性、非事务性表之间没有依赖（反例：insert into myisam select * from innodb），否则会导致slave和master不一致

binlog_do_db

明确指定master二进制日志针对的数据库名称，其它数据库不被记录日志

binlog_ignore_db

指定不需要记录二级制日志的数据库名称

binlog_error_action

当语句由于致命错误无法写入二进制日志时的动作，可以忽略或导致master终止

binlog_format

默认STATEMENT。二级制日志的格式， ROW表示基于行的二进制日志；STATEMENT表示基于语句的二进制日志；MIXED表示尽量使用基于语句的格式，除非导致歧义（例如牵涉到时间、用户定义函数）

binlog_order_commits

默认TRUE。是否在内部发起与事务相同顺序的commit操作

binlog_row_event_max_size

默认8192。基于行的二进制日志，每个事件最大字节数，多个行会尽可能合并到一个事件中，该值必须是256的倍数

binlog_rows_query_log_events

默认FALSE。允许写入查询日志事件到二进制日志中

binlog_stmt_cache_size

默认32768。非事务性引擎的语句缓存大小，如果经常使用语句更新大量的行，可以增加该参数的值

blackhole

默认ON。是否启用BLACKHOLE插件

bulk_insert_buffer_size

默认8388608。用于优化批量插入的tree cache的大小，该参数针对单个线程，增加该参数可以有效的提升批量插入的性能

character_set_client_handshake

默认TRUE。是否在握手阶段考虑客户端的编码方式

character_set_filesystem

默认binary。文件系统字符集

character_set_server

默认latin1。服务器默认字符集

character_sets_dir

默认$INS_DIR/share/charsets/。存放字符集的目录

chroot

在启动时设置mysqld的根目录

collation_server

默认latin1_swedish_ci。默认排序规则

concurrent_insert

默认AUTO。对于MyISAM，是否使用并发插入，可用值NEVER, AUTO, ALWAYS

connect_timeout

默认10。服务器等待客户端发送连接数据包(connect packet)的超时秒数，超时后服务器发送Bad handshake给客户端

console

默认FALSE。是否保留控制台窗口

datadir

数据文件的根目录

date_format

默认%Y_%m_%d。日期格式

datetime_format

默认%Y_%m_%d %H:%i:%s。日期时间格式

default_storage_engine

默认InnoDB。新创建表的默认存储引擎

default_time_zone

默认时区

default_tmp_storage_engine

默认InnoDB。新的明确创建的临时表使用的存储引擎

des_key_file

从文件加载des_encrypt()的加密密钥

disconnect_on_expired_password

默认TRUE。如果密码过期，断开连接

expire_logs_days

默认0。二进制日志过期的天数，如果禁用，启用二进制日志会导致磁盘耗尽

external_locking

默认FALSE。使用外部（系统级）锁定，开启后，可以在MySQL处于运行状态时，使用myisamchk检查MyISAM表

flush

默认FALSE。在SQL命令之间，刷出MyISAM表到磁盘

flush_time

默认0。如果大于0，每隔一段时间后，专用线程刷出所有表到磁盘

gdb

默认FALSE。启用调试

general_log

默认FALSE。将连接、查询信息都记录到表或者文件

general_log_file

默认$INS_DIR/data/hostname.log。上述日志的文件名

gtid_mode

默认OFF。是否启用全局事务标识符（Global Transaction Identifiers）

host_cache_size

默认279。为避免DNS解析，最多缓存多少主机名

init_connect

对于每一个新的连接，执行的命令

init_file

在服务器启动时，从文件读出并执行命令

init_slave

Salve的SQL线程启动时执行的命令

innodb

默认ON。

innodb_adaptive_flushing

默认TRUE。在检查点刷出脏页，以避免I/O暴增

自适应刷出（adaptive flushing）是InnoDB引入的一项刷出算法，通过引入检查点的概念，使I/O更加平缓，避免暴涨暴跌。该算法不是把所有脏页一次性从InnoDB缓冲池刷出到数据文件，而是周期性的刷出一小批脏页。该算法根据flush操作的频率、重做日志增长的速度决定最优化的刷出比率

innodb_adaptive_flushing_lwm

默认10。上述刷出机制被禁用的最低水位（脏页占InnoDB重做日志缓冲的比例）

innodb_adaptive_hash_index

默认TRUE。启用InnoDB自适应哈希索引

innodb_adaptive_max_sleep_delay

默认150000。最大休眠微秒数

innodb_autoextend_increment

默认64。数据文件自动扩展的尺寸，MB

innodb_buffer_pool_dump_at_shutdown

默认FALSE。是否在关闭时Dump出InnoDB缓冲池

innodb_buffer_pool_dump_now

默认FALSE。立即Dump出InnoDB缓冲池到@@innodb_buffer_pool_filename

innodb_buffer_pool_filename

默认ib_buffer_pool。 DumpInnoDB缓冲池时的文件名

innodb_buffer_pool_instances

默认0。缓冲池的数量，对于高档服务器，可以增加此值以提高可扩容性

innodb_buffer_pool_load_abort

默认FALSE。立即中止正在执行的缓冲池加载（预热）动作

innodb_buffer_pool_load_at_startup

默认FALSE。在启动时，从@@innodb_buffer_pool_filename读取缓冲池

innodb_buffer_pool_load_now

默认FALSE。立即从@@innodb_buffer_pool_filename读取缓冲池

innodb_buffer_pool_size

默认134217728。用于存储InnoDB数据和索引的缓冲池的大小

如果服务器是供MySQL专用的，可以尽量大，在为操作系统、MySQL其它组件预留足够的内存后，所有剩余的内存均可以分配给缓冲池，典型设置： 5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)

innodb_buffer_pool_stats

默认ON。是否启用INNODB_BUFFER_POOL_STATS插件

innodb_change_buffer_max_size

默认25。变更缓冲（磁盘文件）相对InnoDB缓冲池的最大比例。如果服务器增删改压力非常重，可以增加该参数的值

innodb_change_buffering

默认all。使用变更缓冲的操作类型，可选值inserts、deletes、purges、changes、none、all

innodb_commit_concurrency

默认0。可以同时执行commit的线程数量，0表示不限制。在运行时不能在0和非0之间改变改变该参数

innodb_compression_failure_threshold_pct

默认 5。当压缩失败的比率超过此阈值，InnoDB开始为新的压缩页预留空闲空间，预留空间的大小被动态调整，但不超过innodb_compression_pad_pct_max

关于OLTP场景使用压缩以节约磁盘空间：InnoDB压缩传统上主要用于只读或者几乎只读的场景。但是随着SSD这样快而贵的存储设备的出现，InnoDB压缩可以降低高流量、交互性网站的存储需求、IOPS

关于压缩失败：当InnoDB压缩表被执行DML操作时，如果对一个压缩页的更新时，会导致再次压缩，如果压缩后页空间不够，将导致MySQL执行页拆分，而新拆分的两个页又需要分别压缩，这是一个成本较高的场景。检查information_schema.innodb_cmp中compress_ops/compress_ops_ok的比值可以推测压缩失败出现的频度

innodb_compression_level

默认6 。压缩级别，用于时空交换，范围0_9

innodb_compression_pad_pct_max

默认50。每个压缩页预留空白的最大占比

innodb_concurrency_tickets

默认5000 。同时进入InnoDB的线程的数量具有限制，超过限制后的到达的线程将排队，一旦线程进入后，将得到该参数指定的票据数量，每次线程进入InnoDB都消耗一个票据，耗尽后将继续排队

该参数如果设置为较小的值，那么只牵涉到较少行的线程可以公平的与大量更新的线程竞争，其缺点是后者可能需要多次循环排队才能完成整个事务。show engine innodb status可以看到事务持有的票据数

innodb_data_file_path

默认ibdata1:10M:autoextend。指定系统表空间中InnoDB数据文件的文件名（目录innodb_data_home_dir）和大小。autoextend指定是否自动扩展，注意系统表空间永远不会收缩

innodb_data_home_dir

系统表空间中的InnoDB数据文件的统一存放目录。该参数不会影响innodb_file_per_table启用时每个表的数据文件位置

innodb_disable_sort_file_cache

默认FALSE。禁用合并排序（merge_sort）临时文件的操作系统级缓存，效果上相当于使用O_DIRECT打开这样的临时文件

innodb_doublewrite

默认TRUE。是否在写入数据文件之前，写入到双重缓冲

innodb_fast_shutdown

默认1。 InnoDB的关闭模式：设置为0则在关闭前执行完整的purge和change buffer合并；设置为1则跳过这些操作并关闭；设置为2则刷出innoDB并冷关闭，就像MySQL宕机一样，这会导致未提交的事务丢失

innodb_file_format

默认Antelope。仅在innodb_file_per_table启用时有意义。选择Barracuda才能支持InnoDB压缩特性

innodb_file_io_threads

默认4。 InnoDB负责I/O操作的线程个数

innodb_file_per_table

默认ON。如果启用，InnoDB将新创建的表的数据、索引存放在独立的.ibd文件中，而不是存放在系统表空间。当表被drop或者truncate时，存储空间会被回收。如果要支持压缩，必须启用该选项。启用该选项后ALTER操作会导致表从系统表空间移出

开启该选项的好处：

truncate命令快
可以optimize表
可以使用多块磁盘
可以高效的进行大字段存储，Row format=dynamic时LOB被单独存储
可以单表移动或备份
可以支持数据压缩
崩溃恢复可能更快

开启该选项的缺点：

更多的fsync()调用
更多的文件描述符被打开，太多表（10000+）不推荐打开
向后兼容性问题

innodb_flush_log_at_timeout

默认1。每隔N秒刷出InnoDB重做日志到文件系统。如果OS崩溃或者断电，肯定导致N秒的数据丢失

innodb_flush_log_at_trx_commit

默认1。控制性能与完全的ACID保证之间的权衡。修改该参数能够提高性能，但是可能在崩溃时丢失数据

值0：保证大概每1秒（不能完全保证，因为线程调度的不确定性）重做日志被写入文件并刷出。但是事务提交时，日志缓冲不会被写入到文件。mysqld崩溃时可能丢失1秒的数据
值1：保证完整的ACID兼容，每当事务提交时，重做日志缓冲被写入到日志文件，并且日志文件被刷出到磁盘（Blocked I/O直到写入完成）。该值会导致trx/s大大减小，在高速机械磁盘上，只能达到几百次trx/s
值2：保证每次事务提交时，日志缓冲均被写入到文件，但是不保证文件被刷出到磁盘（可能位于OS缓存）。如果OS崩溃或者断电，可能丢失1秒的数据。设置为该值，并使用具有电池支持的写缓存的RAID磁盘，配合使用innodb_flush_method=O_DIRECT，可能在保证安全性的前提下提高性能

从5.6.6开始，日志文件刷出间隔由innodb_flush_log_at_timeout控制

不管该参数设置为多少，InnoDB崩溃恢复机制都会运行，事务要么被完整的应用，要么被彻底删除

innodb_flush_method

定义刷出数据到InnoDB数据文件、日志文件的方式，仅仅在Linux/Unix系统上可配置：

fsync：执行fsync()系统调用来刷出数据和日志文件，这是默认设置
O_DSYNC：使用O_SYNC打开和刷出日志；使用fsync()刷出数据文件。一般不用，在很多Unix变种上存在问题
O_DIRECT：使用O_DIRECT来打开数据文件，使用fsync()刷出数据和日志文件
O_DIRECT_NO_FSYNC：在刷出I/O时使用O_DIRECT，但是跳过fsync()，只适用于特定文件系统

O_DIRECT选项用于跳过OS缓存直接写入

innodb_flush_neighbors

默认1。指示在刷出InnoDB缓冲池中一页的时候，是否把同一extent中的脏页一起刷出：

值0：不刷出任何相邻脏页
值1：刷出位于缓冲池中同一extent中的连续脏页
值2：刷出位于缓冲池中同一extent中的脏页

对于传统HDD，开启此选项有利于减低I/O负载；对于SSD，由于不存在寻道问题，可以关闭该选项

innodb_flushing_avg_loops

默认30。控制InnoDB自适应刷出机制相应工作负载变化的速度，设置的越大，响应速度越慢

innodb_force_load_corrupted

默认FALSE。强制加载被污染的表，如果某些表因为数据损坏而无法访问，需要手工处理，可以打开此标记，处理完毕后再关闭

innodb_force_recovery

默认0。可以设置0_6，只在非常验证的诊断场景下才需要更改

innodb_io_capacity

默认200。设置InnoDB后台任务（从缓冲池刷出页、合并变更缓冲里的数据等）能执行的I/O活动的上限。该参数供所有缓冲池实例使用，多个缓冲池会平分这一参数

该参数可以设置的与系统的IOPS相近，但是在满足需要的前提下，应当尽量的低，如果设置的过高（例如100万），数据可能过快的移除、插入缓冲池，而导致缓冲池的缓存价值变低

对于5400/7200转的老旧硬盘，有可能需要设置到100。当前的默认值适应大部分现代设备和较高的I/O速率

对于具有多磁盘、SSD等能够支持高IOPS存储设备的系统，提高该参数可能获益

innodb_io_capacity_max

紧急情况下InnoDB后台任务最大I/O能力，如果不设置，默认值是innodb_io_capacity的2倍，并且大于2000

innodb_lock_wait_timeout

默认50。在InnoDB事务放弃前，等待行锁的最长时间，默认50秒。放弃后，会打印Lock wait timeout exceeded信息，并且导致当前语句（而不是整个事务）回滚，innodb_rollback_on_timeout参数会在此场景下导致整个事务回滚

对于高交互性应用或者OLTP，该参数可能需要调低

注意：

该参数只会影响InnoDB的行级锁
该参数对死锁没有作用，InnoDB会立即检测到死锁，并回滚其中一个事务

innodb_log_buffer_size

默认8388608，范围在256KB到4GB之间。InnoDB重做日志的内存缓冲大小，InnoDB事务在修改任何数据时，总是先写入到该缓冲，只在缓冲已满、事务提交、或者过了N秒后，才会把该缓冲刷出到重做日志文件中。

如果存在大量的、增删改大量行或者LOB的事务，可以增加此参数，以减少磁盘I/O。

innodb_log_compressed_pages

默认TRUE。指定是否重压缩的页镜像被存放在重做日志中

innodb_log_file_size

默认50331648。 InnoDB重做日志组中，单个文件的大小，innodb_log_file_size *innodb_log_files_in_group不得超过512G。

1MB到innodb_buffer_pool_size/innodb_log_files_in_group是合理的取值范围。该值设置的越大，InnoDB缓冲池需要的刷出操作越少，因而磁盘I/O也越少，对于高并发负载，可以设置到GB级别

尽管5.5以后，重做日志文件的大小对崩溃恢复速度的影响变小，但是，该参数越大，崩溃恢复时间仍然越长

注意修改此值要准守一定的步骤，在高性能MySQL学习笔记中有详细说明

innodb_log_files_in_group

默认2。 InnoDB重做日志组包含的文件个数，InnoDB采取循环写入的方式使用这些文件

innodb_log_group_home_dir

InnoDB重做日志的存放目录

innodb_lru_scan_depth

默认1024。影响InnoDB缓冲池的刷出操作，用于调优I/O敏感的工作负载。该参数指定了页面清理线程遍历（每秒执行一次）缓冲池LRU列表最大的深度。如果在某个工作负载下还具有空闲的I/O能力，可以增加该参数；如果具有大缓冲池，并且写敏感工作负载使I/O能力饱和，则可以减少该值

innodb_max_dirty_pages_pct

默认75。 InnoDB会把缓冲池中的页刷出到数据文件，以确保脏页占比不超过此参数指定的比例。该参数不会影响刷出操作的速率

该参数设置的很大时，如果重做日志空间不足，可能导致激烈的刷出操作

innodb_max_dirty_pages_pct_lwm

默认0。脏页低水位，超过该比例后，预刷出（preflushing）机制被启用，以控制脏页比率。默认禁用

innodb_max_purge_lag

默认0。当清理操作迟滞时，如何延迟insert/update/delete的执行，默认值表示不延迟。

InnoDB事务系统维护一个transactiond的列表，这些transaction执行了update/delete操作而导致某些索引记录被标记为删除。该列表的长度相当于清理迟滞（purge lag）的值，当purge lag大于innodb_max_purge_lag时所有insert/update/delete操作将被延迟执行。迟滞值会显示在InnoDB状态输出：

Purge done for trx's n:o < 0 290315608 undo n:o < 0 17

#迟滞值为20

History list length 20

对于小事务（100bytes）典型的设置可能是100万，为了避免过分的延迟，可以设置innodb_max_purge_lag_delay

innodb_max_purge_lag_delay

默认0。最大延迟毫秒数，参考上一个参数

innodb_monitor_disable

启禁information_schema.innodb_metrics中一个或者多个计数器

innodb_monitor_enable

innodb_monitor_reset

重置information_schema.innodb_metrics中的计数器

innodb_monitor_reset_all

innodb_old_blocks_pct

默认37。 InnoDB缓冲池中供旧块（old block）子列表使用的比例，默认3/8，通常和innodb_old_blocks_time联用

innodb_old_blocks_time

默认1000。非0值可以用来预防InnoDB缓冲池被仅仅短时间引用的数据（例如全表扫描）充满，增加此值可以增强前述预防功能。

该参数指定一个毫秒数的阈值，插入缓冲池old sublist的块，必须至少等待此阈值后，被访问时才被转移到new sublist中；如果设置为0，一旦old sublist中的块被访问，就插入到new sublist中

old/new块是InnoDB缓冲池改进的LRU算法引入的概念，该算法致力于尽量让hot pages（经常被访问的）驻留在缓冲池中。某些全表扫描、预读导致加入缓冲池的块，可能后续永远不会被使用，也就没有缓存价值，因此，InnoDB默认把新读进去的块存放在LRU列表的后面3/8部分，此部分即old list，存放在其中的块即old blocks

innodb_open_files

默认0。限制InnoDB最多同时打开的.ibd文件的数量，只在使用多个表空间（例如file per table）有意义

innodb_page_size

默认16384。所有InnoDB表空间使用的页的大小。

较小的页更适合存在代码small write的OLTP场景，因为大页包含多个的多行可能出现争用；较小的页亦适用于SSD，因为SSD通常使用小的块，例如4KB。让InnoDB页大小与硬盘的块大小接近，可以避免对未更改数据的重新写入

默认值16KB适用于广泛的应用场景，特别是牵涉到表扫描、批量更新的DML操作的场景

innodb_print_all_deadlocks

默认FALSE。启用后，所有的死锁信息记录在mysqld的错误日志中，否则只用通过InnoDB引擎状态查看最后的死锁。

偶发的死锁不需要特别关注，因为InnoDB会立即检测并回滚事务。如果应用程序没有合适的错误处理、重试逻辑，可以打开此选项进行检测。一般可能需要调整代码中锁的获取顺序，避免死锁

innodb_purge_batch_size

默认300。重做日志中的记录数到达多少时，触发清理操作，这会导致InnoDB缓冲池中的脏页被刷出到数据文件，通常和innodb_purge_threads联用

innodb_purge_threads

默认1。专用于InnoDB清理操作的后台线程的数量，最大值32

innodb_random_read_ahead

默认FALSE。启用随机预读（random read-ahead ）来优化InnoDB的I/O

innodb_read_ahead_threshold

默认56，范围0-64。控制线性预读（linear read-ahead）的敏感性。InnoDB会立即预读当前extent内至少innodb_read_ahead_threshold个页（注意默认一个extent是64页），并发起一个异步读，来读取整个extent后续的页

预读不一定有价值，状态变量Innodb_buffer_pool_read_ahead_evicted/Innodb_buffer_pool_read_ahead的比值可以看到哪些页在预读后没有被使用就清除了。此外，InnoDB状态输出中可以看到预读和预读立即清除的速率：

----------------------

BUFFER POOL AND MEMORY

----------------------

Pages read ahead 0.00/s,

evicted without access 0.00/s,

Random read ahead 0.00/s

innodb_read_io_threads

默认4。执行读操作的InnoDB线程数量

innodb_read_only

默认FALSE。以只读模式运行，可以运用在数据仓库中，让多个MySQL实例共享一份数据文件

innodb_replication_delay

默认0。当innodb_thread_concurrency到达时，Slave上的复制线程延迟的毫秒数

innodb_rollback_on_timeout

默认FALSE。 5.6版本的默认情况下，事务超时只会导致最后一个语句被回滚，指定该选项可以让整个事务回滚

innodb_rollback_segments

默认128。指定系统表空间中有多少个回滚段，该参数会被innodb_undo_logs覆盖

innodb_sort_buffer_size

默认1048576。当创建InnoDB索引的时候，可以使用多大的缓冲区来对数据进行排序。索引创建完毕后，此缓冲被释放，该参数不会影响后续的索引维护操作

innodb_spin_wait_delay

默认6。轮询自旋锁时最大的延迟，由于硬件和OS的不同，该参数不能映射到具体的时间间隔

innodb_stats_auto_recalc

默认TRUE。当表的数据发生足够量的改变时，导致InnoDB自动重新计算索引统计信息

innodb_stats_method

默认nulls_equal。收集统计信息时，如何处理InnoDB索引中的NULL值：

nulls_equal：认为所有NULL值相等，并构成一个单独的value group
nulls_unequal：认为NULL值各不相等，单独形成value group
nulls_ignored：忽略NULL值的统计价值

生成表统计信息的方式，影响查询优化器对执行计划的选择

innodb_stats_on_metadata

默认FALSE。如果启用，InnoDB在元数据语句（metadata statements，例如show table status;show index）被执行时或者查询information_schema.tables|statistics时，自动更新统计信息

innodb_stats_persistent

默认TRUE。 InnoDO索引统计信息是否持久化保存。create|alter表时使用stat persistent从句可以按单个表的设置此参数

innodb_stats_persistent_sample_pages

默认20。进行索引信息统计时，采样的索引页的数量。增加此值会提高统计信息的精确度，但是导致analyze table的I/O成本变大。对innodb_stats_persistent=true的表有效

innodb_stats_transient_sample_pages

默认8。类似上面，对innodb_stats_persistent=false的表有效

innodb_strict_mode

默认FALSE。如果打开，在某些情况下返回错误，而不是警告

innodb_support_xa

默认TRUE。启用两阶段提交的XA分布式事务支持。在master上不要关闭

innodb_sync_spin_loops

自旋锁的最大循环次数，修改此值可以在无谓的线程上下文切换以及浪费CPU时间之间进行平衡

innodb_thread_concurrency

默认0。最大并发线程数，即InnoDB持有的，负责事务处理的最大操作系统线程数。一旦到达限制，其它线程按FIFO排队，因等待锁而阻塞的线程不计算为并发线程

值范围0-1000，0表示无限制，InnoDB会根据需要创建足够多的线程，0亦导致InnoDB状态输出的queries inside InnoDB、queries in queue counters段落被禁用

该参数合理的值依赖于具体的工作负载，特别是MySQL需要和其它应用程序共享CPU的时候。如果设置为某个值后性能很差，可以实时的修改为0

下面是一些帮助确定合理值的经验：

如果当前工作负载并发的用户线程小于64，设置为0
如果工作负载持续沉重或偶尔卡住，初始设置为128，然后96/80/64的递减，直到找到最佳性能点。比如工作负载通常有40-50用户，但是周期性的增加到60/70甚至200，如果发现在80用户时性能稳定，继续增加时则性能衰退，可能innodb_thread_concurrency=80是较好的点
如果不希望InnoDB的用户线程操作指定的虚拟处理器数量，那么可以设置该参数为那个数量；如果要隔离MySQL对其它应用的影响，可以将其绑定到虚拟处理器
该参数过大，可能导致性能衰退，原因是过多的线程争用系统资源

innodb_thread_sleep_delay

默认10000。 InnoDB加入队列前休眠的微秒数

对于5.6.3+，可以设置innodb_adaptive_max_sleep_delay为一个允许的最大休眠时间，并让InnoDB自适应的调整

innodb_undo_directory

默认 .。 InnoDB为撤销日志提供独立表空间的目录，典型的做法是把撤销日志存放在独立的磁盘上。该参数与innodb_undo_logs、innodb_undo_tablespaces联用。默认值.表示和其它日志文件放在一个目录

innodb_undo_logs

默认128。撤销日志（或者说回滚段）的数量，每个撤销日志最多可以持有1024个事务。该参数覆盖innodb_rollback_segments。

当观察到针对撤销日志的争用时，可以调整该值，状态变量Innodb_available_undo_logs反应了目前可用的撤销日志的数量

innodb_undo_tablespaces

默认0。撤销日志表空间的数量，撤销日志会被分配到这些表空间中。注意，系统表空间总是包含一个撤销日志表空间，后者默认用来存放所有撤销日志

额外的撤销表空间被创建在innodb_undo_directory目录中，命令为undoN，每个表空间的默认大小10MB

该参数必须在InnoDB初始前就设置，如果在运行过程中修改为更大的值，会导致MySQL启动失败

innodb_use_native_aio

默认TRUE。仅用于Linux，是否使用操作系统的异步I/O子系统，在5.5+原先仅存在与Windows的异步I/O被添加到Linux版本中。该选项提高了I/O-bound系统的性能

开启此选项时，如果具有大量的InnoDB的I/O线程，可能导致超过Linux系统的限制，从而出现： EAGAIN: The specified maxevents exceeds the user's limit of available events. 错误，此时可以调整 /proc/sys/fs/aio-max-nr 中的限制值

innodb_write_io_threads

默认4。执行写操作的InnoDB线程数量

interactive_timeout

默认28800。交互式客户端超时时间，

join_buffer_size

默认262144，32bit系统最大支持4GB-1。用于普通索引扫描（plain index scans）、范围索引扫描或者不使用索引而进行全表扫描的join操作的缓冲区的最小值

加快join的最好方式是创建合适的索引，如果不能增加索引，才考虑调整此参数的值。每个缓冲只用于两个表的join，因此某些没有用到索引的复杂多表join查询，可能需要用到多个这样的缓冲

不应该将此参数的全局值设置的过大，最好只在执行大join的会话级别设置

key_buffer_size

默认8388608，32bit系统最大支持4GB-1。 MyISAM表的索引块（Index blocks）被缓冲，并由所有线程共享，这个缓冲池被称为Key buffer或者Key cache。该参数指定此缓冲池的大小

增加此参数的值，能够提高所有读操作、很多写操作的性能，对于专用MyISAM的服务器，可以设置到物理内存的25%。如果设置的过高，可能导致OS执行内存换页，导致极差的性能，由于MySQL依赖于OS提供的文件系统缓存来读取数据，必须为OS文件系统缓存预留内存

状态变量：Key_read_requests、Key_reads、 Key_write_requests、Key_writes用于监测此键缓存的性能，Key_reads/Key_read_requests正常情况下应当小于0.01

公式： 1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size) 用于计算键缓存当前使用比

key_cache_age_threshold

默认300。影响MyISAM键缓存的内部数据移动，设置的越小，数据从hot子列表转移到warm子列表的速度越快

key_cache_block_size

默认1024。 MyISAM键缓存的数据块的大小

key_cache_division_limit

默认100。 MyISAM键缓存hot/warm的比例，该参数指定了warm子列表的百分比

large_pages

默认FALSE。是否启用大页（large page）支持

large_pages_size

大内存页的大小，目前仅Linux支持

lock_wait_timeout

默认31536000。尝试获取元数据锁（metadata locks）的超时时间。该超时应用到所有使用元数据锁的语句，包括在表、视图上执行的DDL、DML；存储函数；lock tables；flush tables with read lock

该超时不会应用到针对mysql库中对象的隐式访问，例如grant/revoke语句，但是对系统表的显示访问，例如select/update，会使用到该超时

该超时会单独应用到每个元数据锁的获取

log_bin

默认FALSE。是否启用二进制日志的记录

log_bin_basename

默认datadir + '/' + hostname + '-bin'。二进制日志的路径和basename

log_bin_index

二进制日志的索引文件名

log_error

默认hostname.err。记录启动消息和错误日志的文件名

log_output

默认FILE。指定一般查询日志、缓慢查询日志的输出类型，可以选择FILE/TABLE/NONE，如果设置为TABLE，将在mysql库建立general_log、slow_log表以存放日志

log_queries_not_using_indexes

默认FALSE。如果启用缓慢查询日志的同时启用该参数，那么没有使用索引的查询、虽然使用索引单进行了全表扫描的查询会被记录

log_raw

默认FALSE。开启后在记录查询日志时，使用明文密码

log_short_format

默认FALSE。记录更少的内容到二进制日志或者查询日志

log_slave_updates

默认FALSE。 Slave是否把从Master接收到的更新记录到自己的二进制日志中，如果开启，需要Slave启用二进制日志

log_slow_admin_statements

默认FALSE。是否把管理性的缓慢查询记录到缓慢查询日志中，这些查询包括ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE,REPAIR TABLE

log_slow_slave_statements

默认FALSE。是否把Slave上执行缓慢的查询记录到Slave的缓慢查询日志中

log_throttle_queries_not_using_indexes

默认0。当log_queries_not_using_indexes启用的时候，该参数限制每分钟进入缓慢日志的最大查询个数

log_warnings

默认1。是否打印额外的警告信息到错误日志中

long_query_time

默认10。判断缓慢查询的阈值，单位秒。如果一个查询比执行时间超过此值，状态变量Slow_queries被增加，如果缓慢查询日志被启用，该查询会被写入日志

low_priority_updates

默认FALSE。如果设置为TRUE，那么所有insert、update、delete、lock table write语句都会等待，直到所有未决的select、lock table for read操作完成

该参数只对那些仅仅使用表级锁定的引擎有效，例如MyISAM, MEMORY, MERGE

lower_case_table_names

默认0。只读属性，指示底层文件系统是否大小写敏感，0表示敏感

如果从Windows上Dump出数据库到Linux下，应该把该值设置为1，否则可能导致Table '***' doesn't exist的错误

master_info_file

默认master.info。 Slave记录Master信息的文件名

master_info_repository

默认FILE。指示Slave记录Master的状态和连接信息到表而是文件

master_retry_count

默认86400。在Slave放弃到Master的连接尝试前，重试的次数

max_allowed_packet

默认4194304，最大1GB，必须设置为1024的倍数。客户端把整个查询在单个数据报中发送，如果使用很大的查询语句（例如包含大的LOB字段），就必须调整该参数。数据报缓冲的初始值会被设置为net_buffer_length，最大可以增长到max_allowed_packet

在数据复制场景中，集群中每台服务器应当设置一样的值

客户端应当同时设置max_allowed_packet，尽管客户端Library中给出的默认值是1GB，但是不同的客户端程序可能覆盖了这以默认值，例如mysql、mysqldump这两个程序分别使用16MB、24MB作为默认值

max_binlog_cache_size

默认18446744073709547520。如果一个事务要求超过该参数指定的内存，MySQL发出一个 Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage 错误，适当的最大值应该设置为4GB，因为目前MySQL只能支持最大4GB的二进制日志缓冲

max_binlog_size

默认1073741824。如果一个对二进制日志的写操作导致当前日志超过max_binlog_size，则MySQL轮换日志（关闭当前日志，打开下一个日志）

max_binlog_stmt_cache_size

默认18446744073709547520。二进制日志非事务性的语句缓存的最大大小

max_connect_errors

默认100。如果某台主机连续这么多次连接是否，该主机（在重启前）将被禁止后续的连接尝试

max_connections

默认151。最大允许的同时连接数，如果经常出现Too Many Connections的错误提示则可能需要增加此值

max_error_count

默认64。 show errors和show warnings语句能够显示的最大错误、警告的数量

max_heap_table_size

默认16777216。用户创建的内存表的占用内存的最大数量，设置该参数对既有的内存表没有作用，除非这些内存表被某些DDL语句操控，例如create/truncate/alter

该参数常常和tmp_table_size联用，以限制MySQL内部内存表的尺寸

max_join_size

默认18446744073709551615。可以用于禁止执行这样的查询：需要检查超过max_join_size行的单表查询；组合数超过max_join_size的链表查询；可能需要进行超过max_join_size次磁盘寻道的查询

通过设置该参数，可以捕获到没有合理使用键的、因而可能需要很长执行时间的查询

设置该参数为非默认值，将导致sql_big_selects变为0；设置sql_big_selects为1，则该参数的值被忽略

max_length_for_sort_data

默认1024。导致filesort算法被激活的索引值列表深度的阈值

max_prepared_stmt_count

默认16382。限制服务器上预编译语句的最大数量，设置为0则禁用预编译语句

max_relay_log_size

默认0。如果Slave的中继日志超过该参数，那么将导致日志轮换——当前日志文件被关闭并打开新文件

max_sort_length

默认1024。排序时，使用的字段的最大长度，这导致前max_sort_length个字符相等的字段被认为是相等的

对于5.6.9+，MySQL不对integer/decimal/float等数据类型应用该参数

max_sp_recursion_depth

默认0。存储过程最多被递归调用的深度

max_user_connections

默认0。每个MySQL账户最多同时创建的连接数

max_write_lock_count

默认18446744073709551615。在此数量的写锁之后，允许某些未决的读锁请求被处理

memlock

默认FALSE。将mysqld锁定在内存中，方式OS将其交换到磁盘

metadata_locks_cache_size

默认1024。元数据锁缓存的大小，该缓存被MySQL用来避免同步对象的创建和销毁。对于创建同步对象代价较大的OS，例如Windows XP，该参数有用

min_examined_row_limit

默认0。检查行数小于此值的查询，不会被记录到缓慢查询日志

myisam_block_size

默认1024。 MyISAM索引页的块大小

myisam_max_sort_file_size

默认9223372036853727232。当重建MyISAM索引时，MySQL允许使用临时文件的最大尺寸

myisam_mmap_size

默认18446744073709551615。用于MyISAM压缩文件的内存映射文件的最大内存占用，如果使用了大量MyISAM压缩表，可以减少此值以避免潜在的换页问题

myisam_repair_threads

默认1。如果设置的值超过1，当Repair by sorting时，MyISAM索引会被并行的创建

myisam_sort_buffer_size

默认8388608。用来排序MyISAM索引的缓冲区的大小，此排序会在repair table/create index/alter table时发生。该类操作的出现几率较小，提高此参数有利于增强性能

myisam_stats_method

默认nulls_unequal。统计索引值分布时，处理MyISAM表null值的方法，该参数会影响优化器的行为

myisam_use_mmap

默认FALSE。读写MyISAM表时，是否使用内存映射文件

net_buffer_length

默认16384。每个线程会关联一个connection buffer和一个result buffer，其初始值都是net_buffer_length，最大值则是max_allowed_packet。在每个语句执行完毕后result buffer都会收缩到net_buffer_length

该参数通常不需要修改，但是如果服务器内存特别小，可以将其设置为客户端发送的语句的期望大小

net_read_timeout

默认30。服务器在读取客户端数据时，最大的超时时间

net_write_timeout

默认60。服务器在向客户端写数据时，最大的超时时间

open_files_limit

默认1024。操作系统运行mysqld打开的文件的最大数量，如果设置的过小，可能导致too many open files错误，由于现代OS中打开文件几乎不需要消耗什么资源，可以设置为65535

partition

默认ON。开启或者禁用用户定义的表分区功能

pid_file

默认$INS_DIR/data/hostname.pid。进程标识符文件的名字

plugin_dir

默认$INS_DIR/lib/plugin/。插件目录

port

默认3306。 mysqld的TCP/IP监听端口

port_open_timeout

默认0。端口打开的超时时间，某些系统可能对端口重用的时间间隔有限定

preload_buffer_size

默认32768。预加载索引时分配的缓冲的大小

profiling_history_size

默认15，最大值100。当启用profiling时，保留profiling信息的历史语句的最大数量

query_cache_limit

默认1048576。单个查询能够使用的查询缓冲的大小，如果查询的结果超过此限制，将不被缓存

query_cache_min_res_unit

默认4096。每一次对查询结果进行缓存时，需要申请缓存内存中的一个块，该参数指定其最小大小，块的分配属于相对较慢的操作，因为MySQL需要检查空闲块列表，并找到一个足够大的

query_cache_size

默认1048576。设置全局的查询缓存的大小。修改此值后，MySQL立即清除所有的查询缓存，调整缓存的大小，并重新初始化缓存，由于MySQL是串行删除缓存的，所以可能导致服务器停顿较长时间。当一个查询语句执行完毕，其结果会被MySQL缓存，起来，如果后续到到来的SQL一模一样，则直接返回结果，不进入优化和执行阶段。但是，一旦缓存相关的任何表发生了变化，则所有缓存均必须被串行的删除，如果查询缓存特别大，则可能导致插入、更新的效率很低

query_cache_type

默认0。查询缓存的使用方式：0表示关闭；1表示使用（除非select sql_no_cache）；2表示按需（select sql_cache）

query_cache_wlock_invalidate

默认FALSE。默认的，当一个客户端获得一个MyISAM表的写锁时，其它线程发起的、结果存在与查询缓存的语句不会被阻塞。如果设置该参数为true，将导致MyISAM写锁获得时将该表的所有查询缓存禁用，这导致所有后续对该表的请求等待锁的释放

query_prealloc_size

默认8192。用于查询解析和执行的缓冲的大小，该缓冲不会在一个语句执行后被释放。

如果需要运行复杂的查询，增大该值可能提高性能，因为它避免了在语句执行过程中的服务器段内存分配

read_buffer_size

默认131072。每一个对MyISAM表进行顺序扫描（sequential scan）的线程，MySQL为它扫描的每个表分配read_buffer_size的缓冲，如果需要做很多顺序扫描（或者扫描速度较慢），可能需要增加该值，该缓冲直到需要时才会分配

该值必须设置为4KB的倍数

对于所有引擎，下面的场景下该参数亦有效：

当利用order by 对临时文件（而非临时表）中的行排序时，读缓冲可用于临时文件的索引缓存
当对分区表进行批量插入时
当缓存嵌套查询的中间结果时

对于memory引擎，该参数还用于判断memory block的大小

read_only

默认FALSE。除了具有super权限的用户，禁止任何写操作

read_rnd_buffer_size

默认262144。随机读缓冲区的大小，该参数用于MyISAM表的数据读取，以及其它引擎的Multi-Range读操作的优化

对于基于键排序（key-sorting）的MyISAM查询，结果行将通过此缓冲来读取，以避免不必要的随机I/O

如果表很大或者没有存储在引擎的缓存中，基于辅助索引（secondary index，非聚簇索引）的范围扫描可能导致很多的随机磁盘访问。多范围读优化（Multi-Range Read Optimization，MRR）机制是MySQL用来减少此随机I/O的算法。该算法先仅仅扫描索引，然后收集相关行的主键并排序，最后依据排序后的主键来获取表数据，这样就可以减少一定的随机I/O。当使用到覆盖索引时，MRR不会被使用。当使用MRR时，在执行计划的Extra列可以看到Using MRR

在使用MRR时，read_rnd_buffer_size指示了主键缓冲区的大小，这限制了单次（single pass）能处理的数据量

该参数针对每个线程分配，因此不要设置过大的全局值，仅仅在必要时会话级别设置

relay_log

中继日志的名字格式，默认host_name-relay-bin

relay_log_index

存放中继日志索引信息的文件，默认host_name-relay-bin.index

relay_log_info_file

默认relay_log.info。 Slave存放关于中继日志信息的文件

relay_log_info_repository

默认FILE。中继日志信息的保存形式，可以指定为表或者文件

relay_log_purge

默认TRUE。是否在中继日志不需要时就进行清理

relay_log_recovery

默认FALSE。是否在服务器启动后，立即进行中继日志的恢复

relay_log_space_limit

默认0。所有中继日志使用的空间总额限制

safe_user_create

默认FALSE。可以防止用户创建新的MySQL用户

server_id

默认0。主从复制时，使用的服务器ID

server_uuid

5.6+添加的，在用户提供的server_id的基础上新的全局唯一服务器标识符，和主从复制有关

skip_external_locking

当MySQL使用外部锁定（OS锁定）时，该参数为OFF。外部锁定仅对MyISAM引擎有意义

skip_grant_tables

默认FALSE。使MySQL不适用授权系统来启动，因此任何人可以不受限制的访问所有数据库

skip_name_resolve

默认FALSE。当检查客户端连接时，不适用DNS系统，如果设置为TRUE，grant tables中的Host列必须是localhost或者IP地址

skip_networking

默认FALSE。不进行网络监听，所有和mysqld的交互必须通过命名管道、UNIX套接字或者Windows 的共享内存完成。可以用于限制仅允许本机访问

skip_slave_start

默认FALSE。通知Slave不去启动slave threads。后续可以通过start slave启动这些线程

slave_checkpoint_group

默认512。对于多线程Slave，在检查点（checkpoint ）操作被调用前，最大能处理的事务数量，超过此数量后检查点操作被调用，以更新show slave status中显示的Slave状态信息。对于单线程的Slave该参数没有意义

slave_checkpoint_period

默认300。类似上面，但是限制的是时间（毫秒）而不是事务数量

slave_compressed_protocol

默认FALSE。是否使用压缩的Slave/Master协议，前提是主从都支持压缩

slave_load_tmpdir

默认/tmp。 load data infile时Slave使用的临时目录

slave_max_allowed_packet

默认1073741824。 Slave的SQL和I/O线程支持的最大包大小

slave_net_timeout

默认3600。终止对mast/slave连接的读取操作前的超时

slave_parallel_workers

默认0。并行执行复制事件（事务）的线程数

slave_pending_jobs_size_max

默认16777216。对于多线程Slave，该参数设置Slave工作队列持有尚未入库的事件能占用内存的最大空间

slave_skip_errors

默认OFF。通常，当发生错误时，Slave上的复制操作将终止，设置该参数为true则忽略错误，从而允许后续的手工处理

slave_transaction_retries

默认10。如果因为InnoDB死锁导致Slave事务失败，最大的重试次数

slow_launch_time

默认2。如果新建线程超过此时间，MySQL增加计数器Slow_launch_threads的值

slow_query_log

默认FALSE。是否启用缓慢查询日志记录

slow_query_log_file

默认$INS_DIR/data/hostname_slow.log。缓慢查询日志的路径

socket

默认 /tmp/mysql.sock。对于Unix系统，该参数是本地客户端连接使用的套接字文件的名称；对于Windows系统，该参数是用于本地客户端连接的命名管道的名称，默认为MySQL

sort_buffer_size

默认262144。每个会话都会被分配sort_buffer_size大小的排序缓冲，如果show global status输出每秒Sort_merge_passes增加很大，提示需要增加此参数的值，以便提高无法受益于索引、优化器的order by或者group by查询性能

该参数是为每个线程分配的，设置过大的全局值不但消耗内存（即使不用排序，该缓冲也分配给线程）而且会导致大部分查询变慢，因此最好是在session级别设置

super_large_pages

默认FALSE。是否使用超大（内存）页的支持

sync_binlog

默认0。仅用于master，每次事务提交时同步二进制日志，防止崩溃导致的数据丢失

sync_frm

默认TRUE。如果设置为1，所有非临时表被创建后，其frm文件被同步到磁盘

table_definition_cache

默认值依据400 + (table_open_cache / 2)计算。表定义缓存（.frm）存放表定义的最大数量。如果使用了大量表，增加此缓存可以提高open table的速度

table_open_cache

默认431。所有线程能够同时打开的表数量，增加该参数则mysqld需要更多的文件描述符

table_open_cache_instances

默认1。上述缓存的缓存池数量

thread_cache_size

默认8 + (max_connections / 100)。服务器创建多少线程以备重用，当一个客户端断开时，如果线程池数量大于thread_cache_size，则该客户端的线程被销毁而不是返回池中。

新连接请求到达时，会首先检查线程池，如果没有空闲线程才会新建

在有良好的线程库的情况下，修改此参数不会有很大的性能提升，但是如果工作负载包含大量短时连接请求（100+/s），则有必要增大此参数

thread_concurrency

默认 10。仅用于Solaris 8-，已废弃

thread_handling

默认one_thread_per_connection。线程处理模型，no-threads用于Linux下的debug。如果使用了线程池插件，则可以设置为dynamically-loaded

thread_stack

默认262144。线程的栈的大小，32bit系统默认192KB，64bit系统默认256KB，这些默认值一般足够使用，如果该参数设置的过小，则服务器处理复杂查询的能力受限

tmp_table_size

默认16777216。 MySQL内部创建的内存临时表的最大尺寸，该尺寸同时受到max_heap_table_size的限制。如果某个内存临时表超过该参数的限制，MySQL自动将其转换为MyISAM磁盘表

如果需要大量复杂的group by查询并且用于很多内存，则增加max_heap_table_size和tmp_table_size的值

状态变量Created_tmp_disk_tables、Created_tmp_tables有利于判断是否需要增加此值

tmpdir

默认/tmp。用于创建临时文件、临时表的目录多个文件用:（Linux）或者;（Windows）分隔

使用多个目录可以用来磁盘的负载均衡；使用SSD分区有利于提高性能

transaction_alloc_block_size

默认8192。每个事务独占的内存池的增量分配大小

transaction_isolation

默认REPEATABLE_READ。事务的隔离级别，支持的值包括 READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

transaction_prealloc_size

默认4096。每个事务独占的内存池的初始分配大小，如果事务内存池不足，则后续每次分配transaction_alloc_block_size字节，事务结束后，该内存池截断为transaction_prealloc_size

设置该参数足够大，以包含事务的全部语句，可以避免过多的malloc()操作

transaction_read_only

默认FALSE。设置默认的事务访问级别，默认禁用了只读事务

配置文件示例（16G专用服务器）

[client]

default_character_set=utf8

socket=/var/lib/mysql/mysql.sock

port=3306

[mysql]

default_character_set=utf8

[mysqld]

character_set_server = utf8

basedir = /var/lib/mysql/

datadir = /var/lib/mysql/data/

socket = /var/lib/mysql/mysql.sock

pid_file = /var/lib/mysql/mysql.pid

user = mysql

port = 3306

default_storage_engine = InnoDB

transaction_isolation=READ-COMMITTED

innodb_log_buffer_size = 8M

innodb_buffer_pool_size = 4096M

innodb_log_file_size = 512M

innodb_log_files_in_group = 2

innodb_flush_log_at_trx_commit = 2

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_thread_concurrency = 0

innodb_data_file_path = ibdata1:1024M:autoextend

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 30

innodb_sync_spin_loops = 100

innodb_file_io_threads = 4

innodb_read_io_threads = 4

innodb_write_io_threads = 4

key_buffer_size = 2048M

myisam_block_size = 4K

skip_external_locking

myisam-recover

sort_buffer_size = 2M

join_buffer_size = 2M

read_buffer_size = 1M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 64M

tmp_table_size = 256M

max_heap_table_size = 256M

thread_stack = 192K

query_cache_type = 0

query_cache_size = 64M

query_cache_limit = 4M

query_cache_min_res_unit = 2K

max_connections = 3000

max_connect_errors = 32

max_allowed_packet = 32M

thread_cache_size = 256

table_cache_size = 9000

open_files_limit = 65535

back_log = 600

log_error = /var/lib/mysql/mysql-error.log

long_query_time = 2

slow_query_log_file = /var/lib/mysql/mysql-slow.log

log_bin = /var/lib/mysql/mysql-bin

server_id = 1

sync_binlog = 1

innodb_support_xa = 1

log_slave_updates = 1

relay_log = /var/lib/mysql/mysql-relay-bin

skip_slave_start = 1

sync_master_info = 1

sync_relay_log = 1

sync_relay_log_info = 1

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 512M

read_only = 1

expire_logs_days = 7

lower_case_table_names = 1

skip_name_resolve = 1

[mysqldump]

quick

状态变量（Status Variables）

show status命令简介

使用show status命令、mysqladmin extended-status可以查看服务器/连接的状态信息，这些状态是只读的：

mysql> show status like 'Innodb_buffer_pool_pages_d%';

+---------------------------------------+----------+

| Variable_name | Value |

+---------------------------------------+----------+

| Innodb_buffer_pool_pages_data | 993 |

+---------------------------------------+----------+

#使用information_schema亦可：

mysql> SELECT * FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'INNODB_BUFFER_POOL%'; --显示全局状态

mysql> SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'INNODB_BUFFER_POOL%'; --显示会话状态

MySQL5.0以后，show status的功能发生很大变化，MySQL把一部分变量全局的维护，另外一部分per-connection的维护，show status则是显示两者的混合。

通过比较一段时间前后的值，可以总体上评估MySQL的工作负载。使用innotop的命令摘要模式（Command Summary mode），可以很容易看到这种对比，亦可使用命令：

1 2	#每10秒更新一次包含Select的变量 mysqladmin -uroot -ppassword extended -r -i10 \| grep Select

或者这样的SQL：

SELECT STRAIGHT_JOIN

LOWER(gs0.VARIABLE_NAME) AS variable_name,

gs0.VARIABLE_VALUE AS value_0,

gs1.VARIABLE_VALUE AS value_1,

( s1.VARIABLE_VALUE - gs0.VARIABLE_VALUE ) AS diff,

( gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE ) / 10 AS per_sec,

( gs1.VARIABLE_VALUE - gs0.VARIABLE_VALUE ) * 60 / 10 AS per_min

FROM

( SELECT VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS UNION ALL

SELECT '', SLEEP(10) FROM DUAL

) AS gs0

JOIN information_schema.GLOBAL_STATUS gs1 USING (VARIABLE_NAME)

WHERE gs1.VARIABLE_VALUE <> gs0.VARIABLE_VALUE;

状态变量分类

类别	说明
线程和连接统计	这些变量跟踪连接尝试、中断的连接、网络流量、线程统计信息
二进制日志状态	Binlog_cache_use：已经存在于二进制缓存中的事务数 Binlog_cache_disk_use：有多少事务太大，导致其语句被存放在临时文件 Binlog_stmt_cache_use：对于非事务性语句，已经存在于缓存的数量 Binlog_stmt_cache_disk_use：对于非事务性语句，语句太大导致其被存放在临时文件二进制缓存命中率对配置二进制缓存大小没有意义
命令计数器	Com_*开头的变量统计各种SQL、底层API命令被执行的次数
临时文件和表	Created_tmp*显示系统内部创建的临时表和文件的信息
Handler Operations	Handler API是MySQL与存储引擎的接口。Handler_*统计handler Op的数量
MyISAM键缓冲	Key_*显示MyISAM键缓冲的度量和计数信息
文件描述符	如果主要是用MyISAM，可以通过Open_*查看打开.frm、.MYI、.MYD文件的频繁度
查询缓存	Qcache_*显示查询缓存相关的统计信息
SELECT查询类型	Select_*显示各类查询操作的数量，可能提示查询性能问题
排序	Sort_*显示文件排序的状态信息
表锁定	显示服务器级别（不是引擎级别）的锁定信息，Table_locks_immediate、Table_locks_waited分别显示多少锁是立即分配，多少是必须等待的
InnoDB相关	Innodb_*开头的信息显示了一些包含在show engine innodb status中的内容。注意：检查这些值会导致对InnoDB缓冲池的全局锁定，因此会影响性能，对某些参数的准确性产生影响

状态变量详解

变量	说明
Aborted_clients	表示客户端连接被中断，原因可能是程序忘记关闭连接，或者客户端意外终止
Aborted_connects	连接MySQL的尝试失败（错误的密码、数据库名、telnet了3306端口）的数量，如果值太高可能导致某个主机被阻拦。更细致的相关性信息可以从 Connection_errors_*查看
Binlog_cache_disk_use	使用了临时二进制日志缓存，并且超过binlog_cache_size限制，导致使用临时文件的事务的数量
Binlog_cache_use	使用了二进制日志缓存的事务数量
Binlog_stmt_cache_disk_use	使用了二进制日志语句缓存，并且超过binlog_stmt_cache_size限制，导致使用临时文件的非事务性语句的数量
Binlog_stmt_cache_size	使用了二进制日志语句缓存的非事务性语句的数量
Bytes_received	从所有客户端的接收到的总字节数
Bytes_sent	发送给所有客户端的总字节数
Com_delete	执行的delete语句的数量
Com_delete_multi	与上面类似，但是针对多表语法
Com_flush	执行的flush操作的数量，不管是flush tables、flush logs或者其它
Com_update	执行的update语句的数量
Com_update_multi	与上面类似，但是针对多表语法
Com_select	执行的select语句的数量，如果命中查询缓存，计数器Qcache_hits被增加，该计数器不变
Com_stmt_*	所有这些变量在预编译语句被处理时增加，不管预编译语句是否成功
Compression	指示客户端连接是否使用压缩
Connection_errors_*	客户端的各类型的连接失败的计数器：
Connections	连接尝试的数量，不管是否成功
Created_tmp_disk_tables	执行语句时，服务器内部创建的磁盘临时表的数量。当服务器创建一个内存临时表，并且超过最大限制（tmp_table_size和max_heap_table_size中的较小值）时，会自动被转换为磁盘临时表。如果该计数器过大，可能需要增加前面提到的两个系统变量
Created_tmp_files	mysqld创建的临时文件的总数
Created_tmp_tables	服务器内部创建的临时表的总数，包括内存表和磁盘表，该状态与Created_tmp_disk_tables的比值可以帮助判断是否需要增加tmp_table_size或max_heap_table_size
Flush_commands	服务器刷空（flush）表的次数，不管因为用户执行flush tables语句，还是由于内部的原因
Handler_commit	内部commit语句的执行数量
Handler_delete	数据行被从表中删除的次数
Handler_external_lock	每当调用external_lock()函数，该计数器增加
Handler_mrr_init	服务器使用存储引擎自有的多范围（Multi-Range）读取机制来进行表访问的次数
Handler_prepare	两阶段提交操作的prepare阶段的次数
Handler_read_first	索引中第一个条目被读取的次数，如果该计数器很高，提示服务器执行了太多的全范围索引（Full index scan）扫描。例如select name from person，如果name是索引列，该语句会导致本计数器增加
Handler_read_key	基于键读取行的请求的次数，如果该计数器很高，提示表的索引很好的适应了查询需求
Handler_read_last	类似于Handler_read_first，但是由于order by desc导致索引的逆序扫描
Handler_read_next	按照所键顺序读取下一行数据的请求次数，当使用范围约束查询一个索引列或者执行索引扫描时，该计数器会增加
Handler_read_prev	类似于上面，但是由于order by desc导致索引的逆序扫描
Handler_read_rnd	基于固定位置读取行的请求次数，如果执行很多需要对结果集排序的查询时，该计数器会增高。该计数器升高可能提示很多的全表扫描，或者没有适当使用键的join操作
Handler_read_rnd_next	请求读取数据文件下一行的次数，如果做了很多表扫描，该计数器会增加。该计数器升高可能提示表没有被合适的索引或者查询语句没有用到索引
Handler_rollback	请求存储引擎执行回滚的次数
Handler_savepoint	请求存储引擎设置保存点的次数
Handler_savepoint_rollback	请求存储引擎回滚到保存点的次数
Handler_update	请求更新表中一行的次数
Handler_write	请求插入表中一行的次数
Innodb_available_undo_logs	总计可用的InnoDB撤销日志的数量。该状态变量是对系统变量innodb_undo_logs的补充，后者报告活动的撤销日志数量
Innodb_buffer_pool_dump_status	InnoDB缓冲池Dump操作的进度，Dump可能由innodb_buffer_pool_dump_at_shutdown或者innodb_buffer_pool_dump_now触发
Innodb_buffer_pool_load_status	InnoDB缓冲池Warmup操作的进度，预热可能由innodb_buffer_pool_load_at_startup、innodb_buffer_pool_load_now触发，并且可以被innodb_buffer_pool_load_abort中止。预热过程MySQL会加载一系列和早先时间点相关的数据页到缓冲池
Innodb_buffer_pool_bytes_data	InnoDB缓冲池中包含数据字节数，包含脏数据
Innodb_buffer_pool_pages_data	InnoDB缓冲池中包含数据的页的数量，包含脏页
Innodb_buffer_pool_bytes_dirty	InnoDB缓冲池中包含脏数据的字节数
Innodb_buffer_pool_pages_dirty	InnoDB缓冲池中脏页的数量
Innodb_buffer_pool_pages_flushed	InnoDB缓冲池中刷出的页数量
Innodb_buffer_pool_pages_free	InnoDB缓冲池中空闲页的数量
Innodb_buffer_pool_pages_latched	InnoDB缓冲池中当前被锁住的页数量，这些页可能正在被读写，或者因为其他原因不能被移除、刷出
Innodb_buffer_pool_pages_misc	等于：Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。这些额外的页可能被分配用来执行行锁、自适应哈希索引（ adaptive hash index ）
Innodb_buffer_pool_pages_total	InnoDB缓冲池的总页数
Innodb_buffer_pool_read_ahead	后台预读（read-ahead）线程读入InnoDB缓冲池的页数
Innodb_buffer_pool_read_ahead_evicted	符合上一条说明的页，在随后没有被任何查询使用时就被清除出缓冲池的页数
Innodb_buffer_pool_read_requests	InnoDB缓冲池逻辑读请求次数
Innodb_buffer_pool_reads	没有命中缓冲的逻辑读请求次数，导致直接磁盘访问
Innodb_buffer_pool_wait_free	等待空闲页出现的次数。当InnoDB需要读取或者创建一个缓冲池页，而没有空白页可用时，InnoDB会刷出某些脏页并等待此操作完成。如果innodb_buffer_pool_size设置适当，该计数器应该很小
Innodb_buffer_pool_write_requests	对InnoDB缓冲池执行的写操作次数
Innodb_data_fsyncs	到目前为止fsync()操作的次数，该操作执行频率受到innodb_flush_method影响
Innodb_data_pending_fsyncs	当前未决fsync()操作的个数
Innodb_data_pending_reads	当前未决的读请求次数
Innodb_data_pending_writes	当前未决的写请求次数
Innodb_data_read	自服务器启动以来读取的数据量
Innodb_data_reads	总计读取数据的量
Innodb_data_writes	总计写入数据的量
Innodb_data_written	到目前为止写入的字节数
Innodb_dblwr_pages_written	写入双重缓冲的页数量
Innodb_dblwr_writes	双重缓冲写入的操作次数
Innodb_log_waits	因重做日志缓冲（log buffer）太小导致必须等待刷出操作完成的次数
Innodb_log_write_requests	InnoDO重做日志写请求次数
Innodb_log_writes	InnoDO重做日志物理写次数
Innodb_num_open_files	InnoDB当前打开的文件句柄数量
Innodb_os_log_fsyncs	针对InnoDB重做日志的fsync()写操作次数
Innodb_os_log_pending_fsyncs	针对InnoDB重做日志的未决fsync()操作数量
Innodb_os_log_pending_writes	针对InnoDB重做日志的未决写操作数量
Innodb_os_log_written	InnoDB重做日志的写入的字节数
Innodb_page_size	InnoDB页大小，默认16KB
Innodb_pages_created	因对InnoDB表执行操作而创建的页数量
Innodb_pages_read	因对InnoDB表执行操作而读取的页数量
Innodb_pages_written	因对InnoDB表执行操作而写入的页数量
Innodb_row_lock_current_waits	当前正在等待行锁的操作的数量
Innodb_row_lock_time	系统启动依赖，获取InnoDB行锁总计消耗的毫秒数
Innodb_row_lock_time_avg	获取InnoDB行锁平均消耗的毫秒数
Innodb_row_lock_time_max	获取InnoDB行锁最大消耗的毫秒数
Innodb_row_lock_waits	因对InnoDB表执行操作而等待行锁的总次数
Innodb_rows_deleted	从InnoDB表中删除的行数量
Innodb_rows_inserted	插入到InnoDB表中的行数量
Innodb_rows_read	从InnoDB表中读取的行数量
Innodb_rows_updated	更新InnoDB表中的行数量
Innodb_truncated_status_writes	show engine innodb status命令的输出被截断的次数
Key_blocks_not_flushed	MyISAM键缓存（Key cache）中已经被修改，但是尚未刷出的键块（Key blocks）数量
Key_blocks_unused	MyISAM键缓存中未使用的键块数量
Key_blocks_used	MyISAM键缓存中已使用过的键块数量，这变量显示高水位而不是当前值
Key_read_requests	从MyISAM键缓存中读取一个键块的请求数量
Key_reads	从磁盘上读取键块到MyISAM键缓存的操作的次数，如果该值太大，提示key_buffer_size太小。Key_reads/Key_read_requests为缓存丢失率
Key_write_requests	请求写入一个键块到MyISAM键缓冲的次数
Key_writes	从MyISAM键缓存把键块写入到磁盘的次数
Last_query_cost	会话级。查询优化器计算出的上一个编译的查询的总计成本，默认值0表示没有任何查询被编译。该状态可以用来比较同一查询在不同的执行计划下的成本。注意该状态只能用于“扁平”的查询，如果存在子查询或者union，该状态返回0
Max_used_connections	从服务器启动开始，连接数使用的峰值
Open_files	处于打开状态的常规文件的数量，不包含套接字或者管道
Open_table_definitions	处于打开状态的缓存的.frm文件数量
Open_tables	处于打开状态的表的数量
Opened_files	使用my_open()打开的文件总数
Opened_table_definitions	打开的缓存的.frm文件的总数
Opened_tables	打开的表的总数。如果该状态太大，提示table_open_cache可能过小
Performance_schema_xxx	performance_schema相关的状态变量
Prepared_stmt_count	当前预编译语句的数量。最大数量由max_prepared_stmt_count限制
Qcache_free_blocks	查询缓存中的空闲内存块数量
Qcache_free_memory	查询缓存中的空闲内存数量
Qcache_hits	查询缓存命中的总计次数
Qcache_inserts	总计添加到查询缓存的查询数目
Qcache_lowmem_prunes	由于内存不足，从查询缓存中删除的查询数目
Qcache_not_cached	没有被缓存（可能由于不可缓存，或者受query_cache_type制约）的查询总计数量
Qcache_queries_in_cache	查询缓存中注册的查询数目
Qcache_total_blocks	查询缓存总计块数
Queries	服务器总计执行的查询数量，包括客户端发起的查询、存储过程执行的查询
Questions	同上，但是不包含存储过程执行的查询
Select_full_join	由于不能使用索引（例如没有使用任何过滤条件），导致join操作执行表扫描的次数。如果该值非0，应当检查表索引的设计，如果快速增加，提示有严重性能问题
Select_full_range_join	使用第N个表中的某个值在第N+1个表（参考表）上进行索引范围扫描的次数
Select_range	在第一张表上使用范围扫描的join查询数量，即使很大一般也不是重大问题
Select_range_check	在第N+1个表中对第N个表中的每个值进行索引重评估（reevaluate）的join的次数。这说明在N+1表没有合适的索引，查询计划成本很高。如果非0，应当检查表索引的设计，如果快速增加，提示有严重性能问题
Select_scan	在第一个表上进行全表扫描的join次数，如果第一个表的数据全部需要参与查询，则不是特别致命的情况
Slow_launch_threads	超过slow_launch_time才创建的线程数量
Slow_queries	执行时间超过long_query_time的查询数量
Sort_merge_passes	MySQL使用该排序缓冲来存放若干(a chunk of)行并进行排序，完成排序后，把这些行合并到结果集中，并增加此计数器的值。如果该计数器过大，考虑增加sort_buffer_size
Sort_range	使用索引范围扫描完成的排序的次数
Sort_rows	排序总计牵涉的行数
Sort_scan	使用表扫描完成的排序的次数
Table_locks_immediate	请求表锁定并立即获取的次数
Table_locks_waited	请求表锁定但被迫等待的次数，该数字高提示有性能问题，应当首先优化查询、必要时考虑分区表或者复制
Table_open_cache_hits	命中已打开表缓存的次数
Table_open_cache_misses	丢失已打开表缓存的次数
Table_open_cache_overflows	打开的表缓存溢出的次数
Threads_cached	线程缓存中的线程数量
Threads_connected	当前打开的连接数
Threads_created	总计创建的线程数，如果该值过高，考虑增加thread_cache_size
Threads_running	正在运行的（而不是休眠）线程，可能有查询正在执行
Uptime	服务器启动依赖流逝的秒数
Uptime_since_flush_status	执行flush status后流逝的秒数

show processlist

进程列表（process list）是连接到MySQL的Connections（Threads）的列表。show processlist可以显示连接状态的列表。innotop可以显示自动更新的进程列表。show full processlist不会截断文字的显示。

show engine innodb mutex

显示InnoDB详细的互斥信息，对分析并发、可扩展性问题很有帮助。该命令的输出形式依MySQL版本的不同而变化，MySQL5.5的输出如下：

mysql> SHOW ENGINE INNODB MUTEX;

+--------+------------------------------+-------------+

| Type | Name | Status |

+--------+------------------------------+-------------+

| InnoDB | &table->autoinc_mutex | os_waits=1 |

| InnoDB | &dict_sys->mutex | os_waits=1 |

| InnoDB | &log_sys->mutex | os_waits=12 |

| InnoDB | &fil_system->mutex | os_waits=11 |

| InnoDB | &kernel_mutex | os_waits=1 |

| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |

| InnoDB | &dict_operation_lock | os_waits=13 |

| InnoDB | &log_sys->checkpoint_lock | os_waits=66 |

| InnoDB | combined &block->lock | os_waits=2 |

+--------+------------------------------+-------------+

根据输出中等待的数量可以确定InnoDB的哪个部分存在性能瓶颈，任何互斥都代表着潜在的资源争用。

数据复制状态（Replication Status）

MySQL提供若干命令来监控数据复制的状态：

show master status显示master的复制状态和配置：

mysql> SHOW MASTER STATUS

*************************** 1. row ***************************

File: mysql-bin.000079

Position: 13847

Binlog_Do_DB:

Binlog_Ignore_DB:

show binary logs显示二进制日志的列表
show binlog events显示日志中的事件
show relaylog events显示转播日志中的事件
show slave status显示replica的状态

information_schema

从MySQL5.0+引入，包含一系列类似数据字典的功能。MySQL Forge 、common_schema提供很多针对这些字典的视图。

该schema的最大缺点是某些时候比相应的show命令缓慢。

InnoDB表

表	说明
INNODB_CMP INNODB_CMP_RESET	显示InnoDB压缩数据信息与上个命令类似，但是具有副作用：重置其包含的数据
INNODB_CMPMEM INNODB_CMPMEM_RESET	显示InnoDB缓冲池被压缩数据使用的情况，第二个也是重置表
INNODB_TRX INNODB_LOCKS INNODB_LOCK_WAITS	显示事务、事务持有/等待的锁。对于诊断锁等待、长事务很重要。
INNODB_METRICS	MySQL 5.6+，显示很多关于InnoDB的性能信息，可以用于代替performance_schema

performance_schema

从MySQL5.5+引入，默认情况下被禁用，启用后可能导致8% - 11%的资源占用。

常用数据类型

TINYINT	整数，8位空间 -2^7 (-128) 到 2^7 – 1 (127)
SMALLINT	整数，16位空间 -2^15 (-32,768) 到 2^15 – 1 (32,767)
MEDIUMINT	整数，24位空间 -2^23 (-8,388,608) 到 2^23 – 1 (8,388,607)
INT	整数，32位空间 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647)
BIGINT	整数，64位空间，存放不能用SMALLINT 或 INT描述的超大整数（19位数字） -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807)
FLOAT	单精度浮点型数据
DOUBLE	双精度浮点型数据
DECIMAL	用户自定义精度的浮点型数据，以特别高的精度存储小数数据。
CHAR (M)	固定长度的字符串。默认255长度，最大65535
VARCHAR(M)	具有最大限制的可变长度的字符串。默认255长度，最大65535
TEXT	字符串大对象。TinyText 最大 255，Text/SmallText 最大 65K，MediumText 中等16M，LongText 最大 4G。具体内存放多少个字符，与字符集相关
BLOB	二进制大对象。TinyBlob 最大 255，Blob/SmallBlog 最大 65K，MediumBlob 中等16M，LongBlob 最大 4G
DATE	yyyy-mm-dd格式的日期
TIME	hh:mm:ss格式的时间
DATETIME	yyyy-mm-ddhh:mm:ss格式结合日期和时间
TIMESTAMP	以yyyy-mm-ddhh:mm:ss格式结合日期和时间
YEAR	yyyy格式的年份
ENUM	一组数据，用户可从中选择其中一个
SET	一组数据，用户可从中选择其中0，1或更多

常用数据字典

数据库	数据字典表	说明
information_schema	innodb_trx	活动的事务，字段说明： trx_id 事务ID trx_state 事务状态： trx_started 事务开始时间； trx_requested_lock_id innodb_locks.lock_id trx_wait_started 事务开始等待的时间 trx_weight trx_mysql_thread_id 事务线程ID trx_query 具体SQL语句 trx_operation_state 事务当前操作状态 trx_tables_in_use 事务中有多少个表被使用 trx_tables_locked 事务拥有多少个锁 trx_lock_structs trx_lock_memory_bytes 事务锁住的内存大小（B） trx_rows_locked 事务锁住的行数 trx_rows_modified 事务更改的行数 trx_concurrency_tickets 事务并发票数 trx_isolation_level 事务隔离级别 trx_unique_checks 是否唯一性检查 trx_foreign_key_checks 是否外键检查 trx_last_foreign_key_error 最后的外键错误 trx_adaptive_hash_latched trx_adaptive_hash_timeout
information_schema	innodb_locks	当前出现的锁，字段说明： lock_id 锁ID lock_trx_id 拥有锁的事务ID lock_mode 锁模式 lock_type 锁类型 lock_table 被锁的表 lock_index 被锁的索引 lock_space 被锁的表空间号 lock_page 被锁的页号 lock_rec 被锁的记录号 lock_data 被锁的数据
information_schema	innodb_lock_waits	锁等待的对应关系，字段说明： requesting_trx_id 请求锁的事务ID requested_lock_id 请求锁的锁ID blocking_trx_id 当前拥有锁的事务ID blocking_lock_id 当前拥有锁的锁ID
information_schema	plugins	显示插件版本信息

解读MySQL执行计划

只需要在select语句前附加explain，即可获取其执行计划。explain命令包含两个变体：

explain extended：提示服务器把执行计划“反编译”为SQL语句，通过后续的show warnings命令，即可看到该语句。
explain partitions：显示查询需要访问的所有表分区

explain可能会导致语句的执行，例如对于from中包含子查询的SQL，则会执行子查询并把结果放入临时表。

使用pt-visual-explain可以使用树形结构来显示执行计划

执行计划返回的是近似的信息，其包含以下限制：

explain不提供任何可能影响查询的触发器、存储函数、UDF信息
对存储过程无效
不提供MySQL执行时即时优化的信息
执行计划显示的统计信息只是近似的，有时会很不准确
某些情形下没有做合理的区分，例如filesort可以表示内存排序、临时文件排序；Using temporary可能指内存或者磁盘临时表

MySQL5.6针对执行计划做了以下改进：

支持update, insert之类语句的执行计划
尽量推迟临时表的物化，可以在不执行子查询的情况下完成执行计划的解释
添加optimizer trace功能，允许用户查看优化器的决定，以及作出决定的原因

执行计划结果列

列名称

说明

显示当前行所属的select，如果没有子查询、union，则只会显示一行，否则inner查询会按照其在原始SQL中的出现顺序，每个一行的显示在执行计划的结果中。MySQL把select分为简单、复杂类型，后者包括简单子查询、导出表（derived tables，from中的子查询）、unions：

//简单子查询的例子

mysql> EXPLAIN SELECT (SELECT 1 FROM sakila.actor LIMIT 1) FROM sakila.film;

+----+-------------+-------+...

| id | select_type | table |...

+----+-------------+-------+...

| 1 | PRIMARY | film |...

| 2 | SUBQUERY | actor |...

+----+-------------+-------+...

//导出表的例子

mysql> EXPLAIN SELECT film_id FROM (SELECT film_id FROM sakila.film) AS der;

+----+-------------+------------+...

| id | select_type | table |...

+----+-------------+------------+...

| 1 | PRIMARY | |...

| 2 | DERIVED | film |...

+----+-------------+------------+...

//UNION的例子

mysql> EXPLAIN SELECT 1 UNION ALL SELECT 1;

+------+--------------+------------+...

| id | select_type | table |...

+------+--------------+------------+...

| 1 | PRIMARY | NULL |...

| 2 | UNION | NULL |...

//UNION的结果总是存放到临时表中，并重新读取

| NULL | UNION RESULT | <union1,2> |...

+------+--------------+------------+...

select_type

显示当前行是简单还是某种复杂查询

SIMPLE：查询不包含子查询或者unions
PRIMARY：最外层的select
UNION：联合查询的第二个及以后的查询标记为union
UNION RESULT：MySQL内部用于从临时表中获取联合查询结果的语句
SUBQUERY：子查询中的第一个select
DERIVED：表示存在于from子句的子查询，MySQL会递归的执行，并放入临时表
MATERIALIZED：物化子查询
UNCACHEABLE SUBQUERY：不可缓存的子查询，对于外部查询的每一行，该子查询必须被重新估算
DEPENDENT：表示子查询、union依赖于外部查询提供的数据。往往提示使用了相关子查询（correlated subquery）

table

显示当前行访问的表，可能显示表名或者别名，从上而下的阅读此列，即为join操作实际发生的顺序：

mysql> EXPLAIN SELECT film.film_id

-> FROM sakila.film

-> INNER JOIN sakila.film_actor USING(film_id)

-> INNER JOIN sakila.actor USING(actor_id);

+----+-------------+------------+...

| id | select_type | table |...

+----+-------------+------------+... //这三行都是属于第一个查询

| 1 | SIMPLE | actor |... //MySQL修改了原始查询中给定的JOIN顺序

| 1 | SIMPLE | film_actor |...

| 1 | SIMPLE | film |...

+----+-------------+------------+...

注意MySQL查询计划是一棵最左最深数（left-deep tree），如果顺时针把该树旋转90度，可以很容易的查看JOIN节点与执行计划行的对应关系： explain-1

如果from子句中包含子查询，则该列显示，N表示子查询的ID
如果当前查询是union，则union result table列包含参与union的查询ID的列表

下面是一个复杂的例子：

1 EXPLAIN

2 select

3 actor_id,

4 (select 1 from film_actor where actor_id = der_1.actor_id limit 1)

5 from (

6 select actor_id

7 from actor limit 5

8 ) AS der_1

10 UNION ALL

12 select film_id,

13 (select @var1 from rental LIMIT 1)

14 from(

15 select film_id, (select 1 from store limit 1) from film limit 5

16 ) AS der_2;

+------+----------------------+------------+...

| id | select_type | table |...

+------+----------------------+------------+...

//对der_1的前向引用，对应SQL第2行

| 1 | PRIMARY | |...

//id为3表示它是第3个SELECT的部分，DERIVED表示它是FROM子查询，对应SQL第6行

| 3 | DERIVED | actor |...

//对应SQL第4行，其具有一个较小的id，表示它是后来执行的（executed afterward）

//DEPENDENT表示它的结果依赖于外部查询的结果（亦称相关子查询correlated subquery）

| 2 | DEPENDENT SUBQUERY | film_actor |...

//表示其是UNION语句中的第二个或者更靠后的查询，从6中获取数据

| 4 | UNION | |...

//对应SQL第15行

| 6 | DERIVED | film |...

//的SELECT子句的子查询，注意其ID为7，这很重要，它不是相关子查询

| 7 | SUBQUERY | store |...

//注意ID变小了，每当出现一个DERIVED类型的查询时，意味着开启了一个嵌套的scope

//其后ID会变大，如果又变得比DERIVED查询小了，说明嵌套scope已经结束

//下一行是使用进行查询的SELECT的一部分

| 5 | UNCACHEABLE SUBQUERY | rental |...

//从临时表中读取数据，对应的导出表的ID为1、4

| NULL | UNION RESULT | <union1,4> |...

+------+----------------------+------------+...

type

访问类型（access type，MySQL官方称为join type）——MySQL决定通过何种方式找到表中的行，从效率由差到好，有：
ALL：全表扫描，需要扫描整个表，另外情况有：使用limit的查询；Extra:Using distinct/not exists
index：与全表扫描类似只是MySQL根据索引顺序，而非行顺序扫描，其主要优点是避免排序，缺点则是意味着随机访问各行。如果出现Extra:Using index说明MySQL使用了覆盖索引并仅扫描索引数据，这种情况下则不会有前述随机访问的消耗
range：表示限制范围的索引扫描，从索引的某处开始，返回一系列匹配值范围的行
ref：索引查找，根据单个索引值返回若干行。只会出现在非唯一性索引或者唯一性索引的某个前缀上。称为ref的原因是通常需要和某个引用值（常量或者从多表连接查询的前某个表获取的值）进行比较
ref_or_null：ref的变种，表示MySQL需要在常规索引查找后，找到索引null条目
eq_ref：这是一类索引查找，且MySQL知道其只会返回一个值，通常使用唯一键或主键与某个值对比时会出现
const, system：如果可以优化掉SQL的某一部分，并用常量代替
NULL：表示MySQL可以在优化阶段解决查询，甚至在执行阶段不需要索引或者表访问，例如获取索引的最小值

possible_keys

显示MySQL可能用在此查询上的索引，根据where子句访问的列、比较操作符确定。该列在优化的早期阶段完成，因此其列出的某些索引在后续优化阶段可能没用

key

显示MySQL决定用来优化查询的索引，如果不在 possible_keys中存在，说明MySQL因为其它原因选择了该索引，例如，在没有WHERE子句的情况下，依然可能选择一个覆盖索引

key_len

显示MySQL使用的索引长度，注意MySQL5.5-只能使用索引最左边的部分。比如某个表由两个smallint列构成的联合主键，那么其主键索引就是2*2bytes=4字节长，可以依据此值来推断查询只使用了索引左边的一部分还是全部

ref

用来显示使用前面表（多表连接时）的哪个列或者常量来从key列中查找值

rows

为了获得期望的结果，MySQL估计需要读取的数据行数，在嵌套循环（nested-loop）执行计划中，该值是针对单次循环的（平均值）

依据索引的选择度、表统计信息的准确性，该值可能相当的不准确。通过把join相关执行计划行的rows相乘，可以大概估算总计需要访问行数

filtered

MySQL 5.1+，使用explain extend时出现。显示表需要参与查询的行数的悲观估计

Extra

显示不在其它列中的额外信息

Using index：提示MySQL使用覆盖索引来避免进行表访问
Using where：表示一个WHERE子句被使用，以限制哪些行可以与下一张表进行匹配，或限制哪些行用于返回客户端。当type的值是ALL或者index时，如果Extra中没有Using where，可能提示查询存在问题，除非你真的想获取/检查表的所有行
Using temporary：表示MySQL将使用临时表进行结果集排序
Using filesort：表示需要进行额外的排序操作，MySQL支持两种排序算法，均支持内存、磁盘排序
Range checked for each record (index map: N)：表示没有合适的索引，在JOIN中的每行都要进行索引重估算

与Java、Oracle类型的对应关系

MySQL类型	Oracle类型	Java类型
BIGIHT	NUMBER(19,0)	java.lang.Long
BIT	RAW	byte[]
BLOB	BLOB RAW	byte[]
CHAR	CHAR	java.lang.String
DATE	DATE	java.sql.Date
DATETIME	DATE	java.sql.Timestamp
DECIMAL	FLOAT(24)	java.math.BigDecimal
DOUBLE	FLOAT(24)	java.lang.Double
DOUBLE PRECISION	FLOAT(24)	java.lang.Double
ENUM	VARCHAR2	java.lang.String
FLOAT	FLOAT	java.lang.Float
INT	NUMBER(10,0)	java.lang.Integer
INTEGER	NUMBER(10,0)	java.lang.Integer
LONGBLOB	BLOB RAW	byte[]
LONGTEXT	CLOB RAW	java.lang.String
MEDIUMBLOB	BLOB RAW	byte[]
MEDIUMINT	NUMBER(7,0)	java.lang.Integer
MEDIUMTEXT	CLOB RAW	java.lang.String
NUMERIC	NUMBER
REAL	FLOAT(24)
SET	VARCHAR2	java.lang.String
SMALLINT	NUMBER(5,0)	java.lang.Integer
TEXT	VARCHAR2 CLOB	java.lang.String
TIME	DATE	java.sql.Time
TIMESTAMP	DATE	java.sql.Timestamp
TINYBLOB	RAW	byte[]
TINYINT	NUMBER(3,0)	java.lang.Byte
TINYTEXT	VARCHAR2	java.lang.String
VARCHAR	VARCHAR2 CLOB	java.lang.String
YEAR	NUMBER	java.sql.Date

show engine innodb status详解

与其它的show命令不同，该命令的输出是一个字符串，分为若干个段落，分别关注InnoDB的不同方面。

其输出包含一些自上一次命令输出以来的平均统计信息，例如fsync()每秒被调用的次数，如果需要分析这些统计信息，务必等待30秒或更长以保证统计的采样更加精确，并且通过多次采样来分析InnoDB的运行走势。诸如innotop之类的工具可以提供比该命令更方便的功能。

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

mysql> show engine innodb status;

'第一部分是信息头'

1 =====================================

2 070913 10:31:48 INNODB MONITOR OUTPUT

3 =====================================

4 Per second averages calculated from the last 49 seconds '上一次统计以来的流逝的时间，最好大于30秒'

'第二部分是信号量，对于高并发的工作负载的分析具有重要意义。InnoDB使用互斥量和信号量来保护代码关键区的独占访问'

'以及在存在活动reader的时候限制writer的操作，等等。'

'包含两类数据：1、事件计数器；2、一个当前等待的列表。如果出现性能瓶颈，这些数据有利于分析瓶颈的所在'

'下面是信号量的简单输出示例：'

1 ----------

2 SEMAPHORES

3 ----------

'下行显示操作系统等待数组的信息，这是一个插槽（slots）的队列，InnoDB为信号量保留数组中的插槽，操作系统'

'使用信号量来提示（signal）线程可以继续并进行其正在等待（某种资源）去做的事情了，该行显示了InnoDB需要'

'使用系统等待的次数。其中reservation count说明了InnoDB分配插槽的频度；signal count则说明了线程被通过'

'OS等待数组被signal的频度。相比起自旋等待（spin waits），操作系统等待的成本更高'

4 OS WAIT ARRAY INFO: reservation count 13569, signal count 11421

'5-12行显示了当前正在等待互斥量（mutex）的线程的信息，每个等待以Thread has waited at ...开头'

'本例中，包含2个等待的线程，理想情况下，最好为0个，除非系统并发非常高，从而必须使用操作系统等待'

'这些信息中最有价值的是线程等待的文件名，可以去分析资源争用出现什么地方，例如buf0buf.ic说明存在缓冲池争用'

5 --Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds '等待时间'

the semaphore:

6 Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0

7 waiters flag 0 '多少个等待者正在等待该互斥量'

8 wait is ending '表示互斥量当前已经可用了，但是操作系统尚未调度线程运行'

9 --Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds

the semaphore:

10 Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0

11 waiters flag 0

12 wait is ending

'InnoDB使用多阶段等待策略，首先，尝试对锁的自旋等待，如果失败，则进入比较昂贵的操作系统等待。尽管自旋等待需要'

'占用CPU周期进行不停的检查，但是通常并不消耗太多资源，因为在CPU等待I/O的时候通常存在空闲CPU周期。除了自旋等待'

'以外，另外一种方式是线程上下文切换，导致当前线程休眠，这期间其它线程可以运行，然后，休眠线程可以通过OS等待队列'

'里面的信号量来激活（signal）。信号量激活是很快的，但是上下文切换则比较昂贵，特别是每秒上千次切换将消耗大量资源'

'调整innodb_sync_spin_loops来在自旋和操作系统等待之间获得平衡，如果自旋循环（rounds）达到每秒成百上千，则可'

'以考虑自旋等待的配置是否有问题。可以结合SHOW ENGINE INNODB MUTEX、performance_schema来解决问题'

'显示几个与互斥有关的计数器。注意操作系统等待的次数'

13 Mutex spin waits 5672442, rounds 3899888, OS waits 4719

'显示与读/写共享锁、独占锁的计数器。注意操作系统等待的次数'

14 RW-shared spins 5920, OS waits 2918; RW-excl spins 3463, OS waits 3163

'第三部分是最近发生的外键错误记录，如果没有外键错误发生，该部分不会显示，如果出现新错误，旧的可能被覆盖'

'外键错误发生时机：1、在进行增删改操作时违反外键约束时；2、尝试删除存在外键约束的表时'

'增删改操作时违反外键约束的例子，依次执行下面三个SQL语句'

'INSERT INTO parent(parent_id) VALUES(1);'

'INSERT INTO child(parent_id) VALUES(1);'

'DELETE FROM parent;'

'会出现错误：'

'ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint'

'fails (`test/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES'

'`parent` (`parent_id`)) 执行SEIS命令，输出如下：'

1 ------------------------

2 LATEST FOREIGN KEY ERROR

3 ------------------------

4 070913 10:57:34 Transaction: '显示外键错误发生的时间'

'5-9行显示违反外键约束的事务的详细信息'

5 TRANSACTION 0 3793469, ACTIVE 0 sec, process no 5488, OS thread id 52064 updating or deleting...

6 mysql tables in use 1, locked 1

7 4 lock struct(s), heap size 1216, undo log entries 1

8 MySQL thread id 9, query id 305 localhost baron updating

9 DELETE FROM parent

'10-19行显示修改时出现错误的数据的精确信息'

10 Foreign key constraint fails for table `test/child`:

12 CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`)

13 Trying to delete or update in parent table, in index `PRIMARY` tuple:

14 DATA TUPLE: 3 fields;

15 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000039e23d; asc 9 =;; 2: len

7; hex 000000002d0e24; asc - $;;

17 But in child table `test/child`, in index `parent_id`, there is a record:

18 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

19 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000500; asc ;;

'尝试删除存在外键约束的表的例子，执行下面的语句：'

'ALTER TABLE parent MODIFY parent_id INT UNSIGNED NOT NULL;会出现错误：'

'ERROR 1025 (HY000): Error on rename of ./test/#sql-1570_9 to ./test/parent'

'上面的1025报错没有给出足够的信息， SEIS命令则阐明了详细的错误原因：'

1 ------------------------

2 LATEST FOREIGN KEY ERROR

3 ------------------------

4 070913 11:06:03 Error in foreign key constraint of table test/child:

5 there is no index in referenced table which would contain

6 the columns as the first columns, or the data types in the

'外键类型必须完全匹配，包括类似UNSIGNED的修饰符'

7 referenced table do not match to the ones in table. Constraint:

8 ,

9 CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (parent_id)

10 The index in the foreign key in table is parent_id

11 See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

12 for correct foreign key definition.

'第四部分是死锁相关的信息，如果不存在死锁，该部分不会显示，新的死锁可能覆盖旧的信息'

'死锁是等待图（waits-for graph）中出现的闭环，该环可能具有任意的大小，每当事务需要等待一个锁时，InnoDB就会立即'

'进行死锁检测，因此在InnoDB中死锁是即刻发现的，不需要超时机制。死锁可能非常复杂，但是该部分内容只会显示最后两个牵'

'涉到死锁中的事务、每个事务最后执行的语句、以及在等待图中制造了闭环的锁。除了我们平常熟悉的死锁，InnoDB把检查闭环'

'的开销过大的情形也认为是发生死锁，这些情形包括：等待图中超过100万个锁；检查时递归超过200个事务'

'注意除了事务、等待的锁之外，该部分的输出还包括相关的记录本身，这可能非常大，导致内容阶段，可以使用Percona Server'

'下面是一个简单的死锁的例子：'

1 ------------------------

2 LATEST DETECTED DEADLOCK

3 ------------------------

4 070913 11:14:21 '显示死锁发生的时间'

'5-10行显示牵涉到死锁的第一个事务的基本信息'

5 *** (1) TRANSACTION:

6 TRANSACTION 0 3793488, ACTIVE 2 sec, process no 5488, OS thread id 1141287232

starting index read

7 mysql tables in use 1, locked 1

8 LOCK WAIT 4 lock struct(s), heap size 1216

9 MySQL thread id 11, query id 350 localhost baron Updating

10 UPDATE test.tiny_dl SET a = 0 WHERE a <> 0

'11=15行显示了死锁发生时，事务一正在等待的锁'

11 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

'这一行的内容最重要，说明事务一在等待test.tiny_dl表的GEN_CLUST_INDEX索引的独占锁'

12 RECORD LOCKS space id 0 page no 3662 n bits 72 index `GEN_CLUST_INDEX` of table

`test/tiny_dl` trx id 0 3793488 lock_mode X waiting

13 Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

14 0: len 6; hex 000000000501 ...[ omitted ] ... '这边的信息用于调试InnoDB本身，被略去'

'16-21行显示牵涉到死锁的第二个事务的基本信息'

16 *** (2) TRANSACTION:

17 TRANSACTION 0 3793489, ACTIVE 2 sec, process no 5488, OS thread id 1141422400

starting index read, thread declared inside InnoDB 500

18 mysql tables in use 1, locked 1

19 4 lock struct(s), heap size 1216

20 MySQL thread id 12, query id 351 localhost baron Updating

21 UPDATE test.tiny_dl SET a = 1 WHERE a <> 1

'22-26行显示第二个事务持有的锁'

22 *** (2) HOLDS THE LOCK(S):

23 RECORD LOCKS space id 0 page no 3662 n bits 72 index `GEN_CLUST_INDEX` of table

`test/tiny_dl` trx id 0 3793489 lock mode S

24 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

25 0: ... [ omitted ] ... '这边省略了若干牵涉到的记录'

'27-31行显示了死锁发生时，事务二正在等待的锁'

27 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

28 RECORD LOCKS space id 0 page no 3662 n bits 72 index `GEN_CLUST_INDEX` of table

`test/tiny_dl` trx id 0 3793489 lock_mode X waiting

29 Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

30 0: len 6; hex 000000000501 ...[ omitted ] ...

'通常上面的信息足够判断查询使用了什么索引，并可以确定是否可以避免死锁'

'如果能使两个事务按照同样的顺序扫描同一个索引，可能减少死锁发生的次数，因为在同样顺序的情况下，不可能出现闭环'

'例如，两个事务需要更新同一张表，那么在内存中按照主键排序，并且按照该顺序依次更新，则两个事务不会发生死锁'

32 *** WE ROLL BACK TRANSACTION (2) '事务二被回滚了，InnoDB会尝试回滚其认为代价最低的事务'

'第五部分是事务相关一些摘要性的信息'

1 ------------

2 TRANSACTIONS

3 ------------

4 Trx id counter 0 80157601 '当前的事务标识符，对于每一个事务，值会递增'

'InnoDB清理MVCC旧版本的进度——已经到达的事务标识符。通过下面的值与当前事务标识符的差值，可以知道有多少旧版本'

'MMVC尚未被清理：80157601 - 80154573 = 3028。没有严格的规则来判断此差值的安全最大值：如果当前不存在任何'

'数据更新，即使差值很大也不代表存在未清理的数据，因为所有事务都使用同样的版本；反之，如果很多行被更新，或者内'

'内中有很多行的一个或多个版本，则应该尽快的提交事务，因为开启的事务即使什么都不做，也会禁止旧版本数据的清理'

5 Purge done for trxs n:o= 0 80157601, sees

锁的调试

发生在Server级别的锁等待

如果在Server级别发生锁争用，则show processlist中会有所证据。

Server级别锁包括以下几类：

表锁（Table Locks）

表锁可以是明确的或者隐含的，通过lock table语句可以创建明确锁：

-- 在会话1上对表进行共享锁定

LOCK TABLES sakila.film READ;

-- 在会话2上对表进行独占锁定，语句会挂起而无法完成：

LOCK TABLES sakila.film WRITE;

-- 显示进程列表，可以看到

SHOW PROCESSLIST

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Info: SHOW PROCESSLIST

*************************** 2. row ***************************

Id: 11

User: baron

Host: localhost

db: NULL

Command: Query

Time: 4

State: Locked --注意该线程的状态是被锁定，出现此状态表示在Server层被锁

Info: LOCK TABLES sakila.film WRITE

-- 锁也可能是隐含获得的，例如在MyISAM上执行以下语句

SELECT SLEEP(30) FROM sakila.film LIMIT 1;

-- 在30秒内，开启另外一个会话，运行LOCK TABLES sakila.film WRITE;会被锁定

注意InnoDB，对于明确锁，它会做你期望做的，但是对于隐含锁，InnoDB会在Server表锁和InnoDB表锁类型进行必要的转换。

mysqladmin debug命令可以用于发现持有MyISAM表锁的线程。

全局读锁（The Global Read Lock）

-- 使用下面的语句可以获取全局读锁

FLUSH TABLES WITH READ LOCK;

-- 另外一个会话尝试锁定时：

LOCK TABLES sakila.film WRITE;

-- 会挂起，通过SHOW PROCESSLIST可以看到：

State: Waiting for release of readlock --这意味着全局锁

名称锁（Name Locks）

这是一种表锁，当服务器进行表重命名或者DROP的时候会创建，该锁会与其他类型的表锁冲突并导致等待。

用户锁（User Locks）

本质上是一种命名的互斥量：

-- 会话1中执行：

SELECT GET_LOCK('my lock', 100);

-- 尝试在会话2上述语句，会导致挂起，SHOW PROCESSLIST显示：

State: User lock

发生在InnoDB级别的锁等待

任何支持row-level锁的存储引擎，在其内部实现锁定机制。

InnoDB通过show engine innodb status暴露了一些关于锁的信息，如果某个事务在等待一个锁，在transactions段可以看到相应的输出：

mysql> SET AUTOCOMMIT=0;

mysql> BEGIN;

mysql> SELECT film_id FROM sakila.film LIMIT 1 FOR UPDATE;

show engine innodb status

//显示类似如下的类型

1 LOCK WAIT 2 lock struct(s), heap size 1216

2 MySQL thread id 8, query id 89 localhost baron Sending data

3 SELECT film_id FROM sakila.film LIMIT 1 FOR UPDATE

4 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED: //已经等待了9秒

//线程正在等待索引idx_fk_language_id的194页的独占锁（lock_mode X）

5 RECORD LOCKS space id 0 page no 194 n bits 1072 index `idx` of table `sakila/film` trx id 0 61714 lock_mode X waiting

//最终，所等待会超时，显示以下内容：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

//但是，无法看到是谁锁定了表，可以创建下面的表来激活InnoDB 锁监视器

CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;

//上述语句执行后，InnoDB定期（通常每分钟数次）会打印增强版本的（亦可手工执行）

//show engine innodb status信息到标准输出（通常是MySQL错误日志文件）

//如果要停止监控，DROP innodb_lock_monitor

//打开错误日志，可以看到类似下面的详细信息：

1 ---TRANSACTION 0 61717, ACTIVE 3 sec, process no 5102, OS thread id 1141152080

2 3 lock struct(s), heap size 1216

//显示进程ID，与process list的ID列一致

3 MySQL thread id 11, query id 108 localhost baron

4 show innodb status

//该事务在film表上持有一个隐含的I独占锁X

5 TABLE LOCK table `sakila/film` trx id 0 61717 lock mode IX

//显示索引上的锁的详细信息

6 RECORD LOCKS space id 0 page no 194 n bits 1072 index `idx` of table `sakila/film` trx id 0 61717 lock_mode X

7 Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

8 ... omitted ... //包含被锁定记录的DUMP，难以看懂，可以使用innotop、Percona Server或者MariaDB

10 RECORD LOCKS space id 0 page no 231 n bits 168 index `PRIMARY` of table `sakila/film`

trx id 0 61717 lock_mode X locks rec but not gap

11 Record lock, heap no 2 PHYSICAL RECORD: n_fields 15; compact format; info bits 0

12 ... omitted ...

可以使用information_schema中的表来获取锁的信息：

-- 显示谁持有锁（blocking），谁在等待(waiting)，等待了多久

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread,

TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time,

r.trx_query AS waiting_query,

l.lock_table AS waiting_table_lock,

b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread,

SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_host,

SUBSTRING(p.host, INSTR(p.host, ':') +1) AS blocking_port,

IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,

b.trx_query AS blocking_query

FROM information_schema.INNODB_LOCK_WAITS AS w

INNER JOIN information_schema.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_schema.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id

LEFT JOIN information_schema.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id

ORDER BY wait_time DESC

*************************** 1. row ***************************

waiting_trx_id: 5D03

waiting_thread: 3

wait_time: 6 --已经等待了6秒

waiting_query: select * from store limit 1 for update

waiting_table_lock: `sakila`.`store`

blocking_trx_id: 5D02

blocking_thread: 2

blocking_host: localhost

blocking_port: 40298

idle_in_trx: 8 --持有锁，已经空闲了8秒

blocking_query: NULL

-- 显示某个锁的持有者，多少线程在等待，等待了多久

SELECT CONCAT('thread ', b.trx_mysql_thread_id, ' from ', p.host) AS who_blocks,

IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,

MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW())) AS max_wait_time,

COUNT(*) AS num_waiters

FROM information_schema.INNODB_LOCK_WAITS AS w

INNER JOIN information_schema.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id

LEFT JOIN information_schema.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id

GROUP BY who_blocks ORDER BY num_waiters DESC

*************************** 1. row ***************************

who_blocks: thread 2 from localhost:40298

idle_in_trx: 1016 -- 线程2已经空闲了很久

max_wait_time: 37 -- 最长的线程已经等待了37秒

num_waiters: 8 -- 8个线程在等待2完成工作并提交

复制模式

执行同步之前，首先要保证两个服务器的数据集一致。

主主复制

主主复制和主从复制类似，只是互相指认对方为Master。需要注意可能存在的修改丢失问题 —— 如果SQL同步完成之间，同一条数据在目标库上修改，则随后发生的同步会覆盖此修改。

本节介绍一个主主复制的实例，服务器HK 47.90.76.189和BJ 39.107.94.255进行主主同步。

创建同步用户

在两台机器上分别执行：

mysql -uroot <<SQL

create user 'sync'@'localhost' identified by 'sync';

grant replication slave on *.* to 'sync'@'localhost';

flush privileges;

SQL

创建安全通道

两台服务器位于公网上，因此必须使用安全信道。我们可以使用SSH隧道。

首先安装SSH隧道监控工具：

wget http://www.harding.motd.ca/autossh/autossh-1.4e.tgz

tar xzf autossh-1.4e.tgz && rm autossh-1.4e.tgz

cd autossh-1.4e/

./configure --prefix=/usr

make && make install

cd ..

rm -rf autossh-1.4e

然后开启自我维护的SSH隧道：

# 在BJ执行：

# localhost:3307:localhost:3306，将本机网络接口localhost上的3307端口绑定到远程主机localhost上的3306端口

autossh -M 22222 -fnN -L localhost:3307:localhost:3306 -i ~/Documents/puTTY/gmem.key root@hk.gmem.cc

# 在HK执行：

autossh -M 22222 -fnN -L localhost:3307:localhost:3306 -i ~/Documents/puTTY/gmem.key root@bj.gmem.cc

修改配置文件

主要相关的配置项如下表：

[mysqld]

# 必须，服务器标识符全局唯一

server-id = 1

# 必须，启用二进制日志

log-bin = /var/lib/mysql/mysql-bin

# 可选，混合二进制日志格式

binlog_format = mixed

# 可选，二进制日志保存时间

expire-logs-days = 14

# 可选，每次事务提交后，都同步二进制日志缓存到磁盘

sync-binlog = 1

# Master相关配置

# 记录二进制日志的数据库列表，逗号分隔

binlog-do-db = gmem

# 必须，不记录二进制日志的数据库

binlog-ignore-db = mysql,information_schema,performance_schema

# 主主同步时，每个数据库都可能插入数据，因此需要防止自增长键的冲突

# 自增长键每次增长的步长

auto-increment-increment = 2

# 自增长键的偏移量

auto-increment-offset = 1

# SLAVE相关配置

# 重放二进制日志的数据库列表

replicate-do-db = gmem

# 必须，忽略二进制日志的数据库

replicate-ignore-db = mysql,information_schema,performance_schema

# 可选，开启中继日志。复制线程先把Master的日志拷贝到中继日志中，再异步入库

relay_log = /var/lib/mysql/relay-bin

# 必须，中继日志执行之后将变化写入自己的二进制文件

log-slave-updates = ON

# 可选，跳过所有的失败的SQL

slave-skip-errors = all

获取二进制日志信息

让两台服务器分别进入只读模式，然后获取二进制日志偏移量：

# HK上的执行结果

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000002

Position: 1373523

Binlog_Do_DB: gmem

Binlog_Ignore_DB: mysql,information_schema,performance_schema

1 row in set (0.00 sec)

mysql> UNLOCK TABLES;

# BJ上的执行结果：

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000002

Position: 434

Binlog_Do_DB: gmem

Binlog_Ignore_DB: mysql,information_schema,performance_schema

1 row in set (0.00 sec)

指定MASTER位置

# HK上的命令

mysql> CHANGE MASTER TO MASTER_HOST='localhost',MASTER_PORT=3307,MASTER_USER='sync',MASTER_PASSWORD='sync',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=434;

# BJ上的命令

mysql> CHANGE MASTER TO MASTER_HOST='localhost',MASTER_PORT=3307,MASTER_USER='sync',MASTER_PASSWORD='sync',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1373523;

启动Slave线程

在两台服务器上分别执行：

mysql> start slave;

# 查看状态：

mysql> show slave status\G

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

除非手工停止，MySQL重启后Slave都会自动启动。

停止Slave线程

如果不希望继续进行数据同步，可以在服务器上执行：

1	mysql> stop slave;

阅读执行计划

基础

该列并非某种序列号，两个相邻的具有相同id的行，意味着基于标准的一遍扫描多次联接（single-sweep multi-join，从第一个表读取一行，然后从第二个表查找其匹配行）方式进行JOIN。

select_type

可以用于提示：

一个新的子作用域被打开
当前行和前面的行的某种关系
当前行执行何种操作

具体如下：

取值	说明
SIMPLE	整个查询没有子查询，或者UNION
PRIMARY	这行是最外层的SELECT
UNION	UNION语句中第二个，以及后续的SELECT
DEPENDENT UNION	UNION语句中第二个，以及后续的SELECT，且依赖于外层查询
UNION RESULT	UNION语句的结果
SUBQUERY	子查询中的第一个SELECT语句，打开了一个子作用域，出现在WHERE、SELECT中的子查询
DEPENDENT SUBQUERY	子查询中的第一个SELECT语句，且依赖于外层查询
DERIVED	出现在WHERE中的子查询
UNCACHEABLE SUBQUERY	子查询的结果不可以被缓存，必须为外层查询的每一行重新执行子查询
UNCACHEABLE UNION	子查询是一个UNION，子查询不可以被缓存。这种情况下第二个，以及后续的SELECT显示为此值

table

通常是表名或者别名。也可以是：

derivedN：表示此行对ID为N的子查询所产生的临时表进行访问
unionM,N：表示对ID为M～N的查询的UNION结果进行访问

partitions

查询匹配哪些分区，仅仅用于分区表。

type

说明表如何被JOIN：

取值	说明
system	const的特例，表仅仅包含一行（系统表）
const	表中最多包含一行匹配。因而，此唯一匹配行中的任何列的引用，都可以被查询优化器作为常量看待。const表非常快，因为它仅仅被读取一次如果你在SQL中对主键、唯一性索引和常量值进行等于比较，则const出现
eq_ref	对于前表的所有行（或者前面N表的行的任何组合），当前表中的单行被读取，以便与其匹配除了system/const之外，这是最好的JOIN方式。如果唯一性索引、主键索引被完整使用并且操作符为=，则eq_ref出现
ref	对于前表的所有行（或者前面N表的行的任何组合），当前表索引值匹配的全部行被读取，以便与前者匹配使用索引前缀、使用非唯一性索引时，ref会出现
fulltext	使用全文索引进行JOIN
ref_or_null	类似于ref，但是MySQL会额外检查NULL值的行
index_merge	提示使用了索引合并优化。在这种情况下，key列包含了一系列使用到的索引
unique_subquery	对于某些形式的子查询，替代eq_ref，例如： value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery	类似于上一条，但是不使用唯一性索引，例如： value IN (SELECT key_column FROM single_table WHERE some_expr)
range	使用索引来匹配行，仅仅索引值在范围内的行被取出。在这种情况下，key列显示用到的索引，key_len显示用到的key长度，ref列显示为NULL 当你将索引列和常量进行=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()操作时，会出现range
index	类似于ALL，但是进行索引树扫描，两种情况下发生：如果索引对查询来说是覆盖的，可以获取所有需要的数据，则仅仅扫描索引树。这种情况下Extra提示Using index。index扫描通常大于ALL，因为索引数据通常小于表数据执行了全表扫描，但是利用索引，按照索引序读取数据行。这种情况下Using index不出现在Extra
ALL	对于前表的所有行（或者前面N表的行的任何组合），当前表的所有行都要被取出进行匹配

possible_keys

MySQL可以选择哪些索引来寻找当前表中的匹配行，如果显示NULL则意味着没有可用索引。

key

MySQL实际决定使用的索引。

key_len

MySQL使用索引的前多少字节。

ref

使用到的索引（key字段）和什么列/常量进行比较，以获取匹配的行。

rows

为了执行查询，MySQL需要检查的行数。对于InnoDB来说这一数值是大概的，不一定准确。

filtered

大概估算的被条件过滤的行的比例。rows × filtered / 100为实际和前表进行JOIN的行数量。

执行命令EXPLAIN EXTENDED时该列出现。

Extra

关于MySQL如何解析查询的额外信息。

Percona工具集

pt-visual-explain

用于将MySQL的执行计划转换为树状形式。命令格式：

# 从FILES读取执行计划

pt-visual-explain [OPTIONS] [FILES]

# 从MySQL命令输出中读取执行计划

mysql -e SQL | pt-visual-explain

常见问题

树状显示执行计划

mysql -h 10.255.223.249 -P 13306 -uuser -ppasswd -D digital -e

"EXPLAIN SELECT count(1) subscriptionCount

FROM media_customer_subscription s

LEFT JOIN media_chapter c ON s.media_id = c.media_id

WHERE 1 = 1 AND s.media_id = 1960162291 AND s.cust_id = 274516853 AND c.last_modifyed_date >= '2018-03-21 19:35:19'"

| pt-visual-explain

# 输出

JOIN

+- Filter with WHERE

| +- Bookmark lookup

| +- Table

| | table c

| | possible_keys index_media_id_index_order

| +- Index lookup

| key c->index_media_id_index_order

| possible_keys index_media_id_index_order

| key_len 9

| ref const

| rows 2823

+- Filter with WHERE

+- Index lookup

key s->customer_subscription_cust_media_id

possible_keys customer_subscription_media_id,customer_subscription_cust_id_index,customer_subscription_cust_media_id

key_len 18

ref const,const

rows 1

# 解释如下：

# 1、对s表进行索引扫描，估计访问1行。ref分别为s.media_id、s.cust_id两个常量值

# 2、对c表进行索引扫描，预计访问2823行。ref为c.media_id

# 3、对c表通过索引定位的每一行，进行bookmark查找，即根据非聚簇索引上的条目查找实际数据

# 4、执行JOIN

频繁CUD表的优化

频繁的对表进行插入、修改、删除，可能导致表空间上出现大量零散的空白，即碎片化，碎片会影响表的读取效率。下面的语句可以查看表的碎片化情况：

SELECT

ENGINE,

TABLE_NAME,

Round(DATA_LENGTH / 1024 / 1024) AS data_mb,

round(INDEX_LENGTH / 1024 / 1024) AS index_mb,

round(DATA_FREE / 1024 / 1024) AS data_free_mb

FROM

information_schema. TABLES

WHERE

DATA_FREE > 0

ORDER BY

DATA_FREE DESC

注意一个表空间中的所有表，会共享一个data_free，因此如果使用单独数据文件，可能看到很多重复的data_free值。使用下面的语句可以进行表优化：

optimize table wp_posts;

-- 如果提示： Table does not support optimize, doing recreate + analyze instead

-- 说明引擎不支持，可以改用：

alter table wp_posts engine='InnoDB';

-- 注意，如果设置：innodb_file_per_table=1，那么alter将为表新建独立的.ibd文件

1067进程意外终止（process terminated unexpectedly）

找到my.ini，定位datadir的位置，其目录下有日志文件：服务器名.error，里面包含详细错误信息。如果配置存在问题，可以通过bin\MySQLIntanceConfig.exe进行重新配置。

注意，MySQL错误日志文件路径可以通过log-error参数定制。

MySQL ERROR 1215 (HY000): Cannot add foreign key constraint

添加外键约束时出现此错误，可能原因是：外键字段与目标表的主键字段的类型不兼容，例如varchar与int。

The user specified as a definer ('root'@'%') does not exist

属于授权问题，可以授予目标用户相关的权限来解决：

1	grant all privileges on . to root@"%" identified by "root";

InnoDB大表批量删除效率问题

大表删除速度慢，原因和聚簇索引（cluster index，此索引每张表只能有一个）有关，MySQL会使用主键、唯一键（作为聚簇索引列）来在物理磁盘上对表的数据行进行排序，一旦行被删除，就会进行整个表的重新排序，这个排序正是耗时操作所在。解决方案：

规划主键的设计，如果删除的数据，其主键是集中在一个区间的，最好了
或者，添加一个BIT列，在删除的时候，将其状态改为false，在某个时候进行单次批量删除

如何确认MySQL使用哪个my.cnf文件

rem 打印MySQL使用的运行参数，可以检测配置是否生效

mysql --print-defaults

rem 执行命令

mysql --verbose --help | less

rem 一直按回车，直到看见“Default options are read from the following files in the given order”

rem 下一行即为读取的配置文件位置，例如

rem C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf D:\Programs\MySQL\my.ini D:\Programs\MySQL\my.cnf

另外，如果MySQL在Windows下作为服务运行，可能需要注意MySQL服务的配置，其命令行参数可能附带 --defaults-file参数，该参数指明了该服务实例使用的my.cnf文件。

彻底重新安装

sudo apt-get purge mysql* -y

sudo apt-get autoremove -y

sudo apt-get autoclean

sudo apt-get install mysql-server -y

使用MySQL客户端命令

1 2	# 执行命令行提供的SQL语句并退出 mysql -uroot -proot -e "create database newdb;"

← AspectJ编程学习笔记

HTML5学习笔记 →

MySQL知识集锦

MySQL知识集锦

Leave a Reply Cancel reply

ABOUT ME

ABOUT GMEM

GMEM HISTORY

MIRROR INFO

Meta

Recent Posts

TOPLINKS

Recent Comments