MySQL
约 73150 字大约 244 分钟
- 1. 数据库概念
- 2. MySQL支持的数据类型
- 3. MySQL中的运算符
- 4. MySQL常用函数
- 5. 视图
- 6. 存储过程和函数
- 7. 触发器
- 8. MySQL存储引擎
- 9. MEMORY表
- 10. MySQL连接
- 11. MySQL索引
- 12. MySQL事务
- 13. MYSQL锁
- 14. MYSQL日志
- 15. buffer Pool
- 16. SQL优化
- 17. 优化数据库对象
- 18. 优化 MySQL Server
- 19. 磁盘 I/O 优化
- 20. 应用优化
- 21. 备份与恢复
- 22. MySQL权限管理
- 23. MySQL 安全
- 24. MySQL复制
- 25. MySQL Cluster
- 26. 字符集
- 27. SQL Mode
- 28. 全表扫描
- 29. 数据库flush
- 30. 数据删除流程
- 31. 组提交（group commit）机制
- 32. 为什么还有kill不掉的语句？
- 33. 临时表
- 34. 自增主键为什么不是连续的？
- 35. 要不要使用分区表？
- 36. MYSQL数据库设计规范
- 37. 读写分离
- 38. 其他
1. 数据库概念
- 启动MySQL服务:net start mysql5;
- 停止MySQL服务:net stop mysql5
- 查看MySQL服务的状态：netstat -nlp
- 登录mysql:mysql [-host=ip地址] -u用户名 -p
2. MySQL支持的数据类型
2.1. 数值类型
- zerofill(填充0):在数字位数（类型名称后面的小括号内指定显示宽度,但存储字节数不变）不够的空间用字符“0”填满，如果插入大于宽度限制的值，还是按照类型的实际精度进行保存
- UNSIGNED(无符号):整数类型里面保存非负数或者需要较大的上限值时适用，取值范围变为0到上限取原值的2倍，如果一个列指定为zerofill，则该列自动添加 UNSIGNED 属性
- AUTO_INCREMENT。整数类型在需要产生唯一标识符或顺序值时适用。一般从1开始，每行增加 1。一个表中最多只能有一个。对于使用AUTO_INCREMENT的列，应该定义为PRIMARY KEY或UNIQUE键
- 小数包括浮点数和定点数。浮点数包括 float（单精度）和 double（双精度），定点数则只有decimal。以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据
- 浮点数和定点数都可以用类型名称后加“(M,D)”，表示显示M位数字（整数位+小数位），D位位于小数点后面，浮点数如果不写精度M和标度D，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值 decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。
- BIT(M)用来存放多位二进制数，M范围1～64，默认1。必须用bin()（二进制）或者 hex()（六进制）函数读取。数据插入bit类型字段时，首先转换为二进制，如果位数允许，将成功插入；
2.2. 日期时间类型
| 数据类型 | 字节 | 最小值 | 最大值 | 零值 | 场景 |
|---|---|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 | 0000-00-00 | 年月日 |
| TIME | 3 | -838:59:59 | 838:5959 | 00:00:00 | 时分秒 |
| YEAR | 1 | 1901 | 2155 | 0000 | 年份 |
- 如果超出有效值范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来进行存储
2.3. 字符串类型
- BINARY和VARBINARY包含二进制字符串而不包含非二进制字符串。保存BINARY值时通过填充“0x00”（零字节）以达到字段定义长度。BINARY(3)列，当插入时'a'变为'a\0\0'
- ENUM枚举类型，需要在建表时通过枚举方式显式指定值范围（忽略大小写），1～255个成员需要1个字节；255～65535需要2个字节存储。最多65535个。create table t (gender enum('M','F'));插入不在ENUM指定范围内的值时，默认插入第一个值。一次只能选一个成员
- SET也是一个字符串对象，最多64个。每8个成员占1个字节存储；Create table t (col set （'a','b','c','d'）;包含重复成员的集合将只取一次；对于超出允许值范围的值将报错，一次可选多个成员
2.4. 选择合适的数据类型
应该根据实际情况选择能够满足需求的最小存储类型
VARCHAR属于可变长度的字符类型。CHAR属于固定长度的字符类型。所以处理速度比VARCHAR快得多，但其缺点是浪费存储空间，程序需要对行尾空格进行处理，对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型存储
- MyISAM：建议使用CHAR
- MEMORY：CHAR或VARCHAR列都是作为CHAR类型处理
- InnoDB：建议使用VARCHAR类型。所有数据行都使用指向数据列值的头指针，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好
TEXT与BLOB
- BLOB和TEXT值在执行大量的删除操作，会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用 OPTIMIZE TABLE 对表进行碎片整理避免
- BLOB 或 TEXT可以使用合成的（Synthetic）索引来提高查询性能。就是根据大文本字段的内容并通过使用 MD5()SHA1()或 CRC32()函数或者使用自己的应用程序逻辑来计算一个散列值存储在单独的数据列中，接下来就可以通过检索散列值找到数据。只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。如果需要对BLOB或者 CLOB 字段进行模糊查询，MySQL提供了前缀索引，在不必要的时候避免检索大型的BLOB或TEXT值
- 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行 SELECT * 查询的时候不会通过网络传输大量的 BLOB 或 TEXT 值。
浮点数与定点数。浮点数一般用于表示含有小数部分的数值。编程中注意浮点数存在误差，尽量避免比较；对货币等对精度敏感的数据应该用定点数
如果应用只需要记录“年份”，可用1个字节来存储的YEAR类型，能节约存储，提高表的操作效率；如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，而不要用TIMESTAMP。因为TIMESTAMP的日期范围比DATETIME要短得多；如果记录的日期需要让不同时区的用户使用，那么最好使用 TIMESTAMP，因为日期类型中只有它能够和实际时区相对应
尽可能的使用not null定义字段(innodb的特性所决定，非not null的值，需要额外的在字段存储，同时也会增加IO和存储的开销）
尽量少用text类型，非用不可时最好考虑分表。
表中有大字段 X(例如：text 类型)，且字段 X 不会经常更新，以读为为主，请问您是选择拆成子表，还是继续放一起?
- 拆带来的问题：连接消耗+存储拆分空间。拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO ，减少连接消耗，最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。
- 不拆可能带来的问题：查询性能。实际场景下，例如说商品表数据量比较大的情况下使用拆的方案，会将商品描述单独存储到一个表中。
3. MySQL中的运算符
3.1. 算术运算符
- +、-、*、/或者DIV（除法，返回商）、%或者MOD(a,b)（除法，返回余数）
- 除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL
3.2. 比较运算符
- =(不能比较null)、<>或者!=(不能比较null)、<=>（能比较NULL等于）、<、>、<=、>=、[not] between(全包含)、[not] in、is [not] null、like [_'单个字符，'%'多个字符]、REGEXP或RLIKE(正则表达式匹配),select中的比较true则是1，false则是0,用于比较数字、字符串(不区分大小写)和表达式.数字作为浮点数比较，而字符串以不
区分大小写的方式进行比较。
3.3. 逻辑运算符
- NOT或者!
- AND或者&& 逻辑与，操作数有0，结果0；有NULL且无0 结果NULL，其他1
- OR或者|| 逻辑或，操作数有1，结果1；有NULL且无1，结果NULL，其他0
- XOR 逻辑异或,操作数有NULL，结果null,无null真假值相异，结果 1；其他0。
3.4. 位运算符
- & 位与（位 AND）、| 位或 （位 OR ）、^ 位异或（位 XOR）、~ 位取反、>> 位右移、<< 位左移,位运算是将给定的操作数转化为二进制后，对各个操作数每一位都进行指定的逻辑运算，得到的二进制结果转换为十进制数后就是位运算的结果。
4. MySQL常用函数
4.6. 其他函数
- DATABASE() 返回当前数据库名
- VERSION() 返回当前数据库版本
- USER() 返回当前登录用户名
- PASSWORD(str) 返回字符串str的加密版本
- MD5() 返回字符串str的MD5值
5. 视图
- 视图（View）是一种虚拟存在的表，并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
- 视图相对于普通的表的优势
- 简单：用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
- 安全：用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现
- 数据独立：一旦视图结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
# 创建或者修改视图要有 CREATE VIEW权限，并且对于查询涉及的列有SELECT权限。如果使用CREATE OR REPLACE 或者 ALTER 修改视图，还需要该视图的DROP权限。
# 创建视图
mysql> CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
# 修改视图
mysql> ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
# 在 FROM 关键字后面不能包含子查询
# LOCAL 是只要满足本视图的条件就可以更新视图记录；
# CASCADED(默认) 则是必须满足所有针对该视图的所有视图的条件才可以更新视图记录
# 以下情况视图不更新
# 包含聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL
# 常量视图。create or replace view pi as select 3.1415926 as pi;
# SELECT 中包含子查询。create view city_view as select (select city from city where city_id = 1);
# JION
# FROM 一个不能更新的视图。
# WHERE 字句的子查询引用了 FROM 字句中的表。
# 删除视图
# 需要有该视图DROP权限。
mysql> DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]
# 查看视图
# 显示表和视图的名字
mysql> SHOW TABLES
# 显示表和视图的信息（表行数）
mysql> SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
# 查询某个视图的定义
mysql> SHOW CREATE VIEW view_name
# 查看视图的相关信息
mysql> select * from information_schema.views where table_name = 'staff_list'
6. 存储过程和函数
- 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
- 存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，而对调用者的程序完全没有影响。
- 函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型。
- 创建存储过程或者函数需要 CREATE ROUTINE 权限，修改或者删除存储过程或者函数需要 ALTER ROUTINE 权限，执行存储过程或者函数需要 EXECUTE 权限
- 存储过程和函数的优势是可以将数据的处理放在数据库服务器上进行，避免将大量的结果集传输给客户端，减少数据的传输，但是在数据库服务器上进行大量的复杂运算也会占用服务器的CPU，造成数据库服务器的压力，所以不要在存储过程和函数中进行大量的复杂运算，应尽量将这些运算操作分摊到应用服务器上执行
6.1. 创建、修改、删除、查看存储过程或者函数
# proc_parameter: [ IN | OUT | INOUT ] param_name type
# func_parameter: param_name type
# type: Any valid MySQL data type
# characteristic:
# COMMENT 'string'(存储过程或者函数的注释信息)
# SQL SECURITY { DEFINER(默认) | INVOKER }指定子程序该用创建子程序者还是使用调用者的许可来执行
# routine_body: Valid SQL routine statement
# 存储过程和函数允许包含DDL语句，Commit或者回滚，调用其他的过程或者函数，但是不允许执行LOAD DATA INFILE语句
# 执行创建过程和函数可以通过“DELIMITER $$”命令将语句的结束符从“;”修改成其他符号如“$$”，这样“;”就不会被解释成语句的结束而提示错误。创建完毕再通过“DELIMITER ;”命令再将结束符改回成“;”
mysql> CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE 存储过程名 ([proc_parameter[,...]]) [characteristic ...] routine_body
mysql> CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION 函数名 ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
# 修改函数/存储过程
mysql> ALTER {PROCEDURE | FUNCTION} 存储过程名/函数名 [characteristic ...]
# 调用过程
mysql> CALL 存储过程名/函数名([parameter[,...]])
# 创建大量数据的存储过程
mysql> create table t(id int primary key, a int, b int,index(a))engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
# 一次只能删除一个存储过程或者函数，需要有该过程或者函数的ALTER ROUTINE 权限
mysql> DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程名/函数名
# 查看存储过程或者函数
mysql> SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
# 查看存储过程或者函数的定义
mysql> SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名/函数名
# 查看存储过程和函数的信息
mysql> select * from information_schema.routines where ROUTINE_NAME = 'film_in_stock'
6.2. 变量的使用
# 局部变量的定义：作用范围在BEGIN…END块中，可以用在嵌套的块中。必须写在复合语句的开头，并且在任何其他语句前面
mysql> DECLARE 变量名[,...] 数据类型 [DEFAULT value]
# 变量的直接赋值(常量或者赋表达式)
mysql> SET 变量名 = expr [, var_name = expr] ...
# 变量的查询结果赋值
mysql> SELECT col_name[,...] INTO 变量名[,...] table_expr
6.3. 条件的定义和处理
# 条件的定义
# condition_value:{ SQLSTATE [VALUE] sqlstate_value| mysql_error_code }
mysql> DECLARE 条件名 CONDITION FOR condition_value
# 条件的处理
# handler_type: { CONTINUE | EXIT } CONTINUE 表示继续执行下面的语句，EXIT 则表示执行终止
# condition_value:
{ SQLSTATE [VALUE] sqlstate_value
| condition_name 条件名
| SQLWARNING 以 01 开头的 SQLSTATE 代码的速记
| NOT FOUND 以 02 开头的 SQLSTATE 代码的速记
| SQLEXCEPTION 没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的速记
| mysql_error_code }
mysql> DECLARE handler_type HANDLER FOR 条件名[,...] sp_statement
# 对于错误ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 可以这么声明
# SQLSTATE
mysql> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1
# mysql-error-code：
mysql> DECLARE CONTINUE HANDLER FOR 1062 SET @x2 = 1;
# 事先定义 condition_name：
mysql> DECLARE DuplicateKey CONDITION FOR SQLSTATE '23000';
mysql> DECLARE CONTINUE HANDLER FOR DuplicateKey SET @x2 = 1;
# SQLEXCEPTION
mysql> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2 = 1;
6.4. 光标的使用
- 在存储过程和函数中可以使用光标对结果集进行循环的处理。
- 变量、条件、处理程序、光标都是通过DECLARE定义，变量和条件必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明
# 声明光标
mysql> DECLARE 光标名 CURSOR FOR select_statement
# OPEN 光标
mysql> OPEN 光标名
# FETCH 光标
mysql> FETCH 光标名 INTO var_name [, var_name] ...
# CLOSE 光标
mysql> CLOSE 光标名
# 光标使用例子
mysql> CREATE PROCEDURE payment_stat ()
-> BEGIN
-> DECLARE i_staff_id int;
-> DECLARE d_amount decimal(5,2);
-> DECLARE cur_payment cursor for select staff_id,amount from payment;
-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
->
-> set @x1 = 0;
-> set @x2 = 0;
->
-> OPEN cur_payment;
->
-> REPEAT
-> FETCH cur_payment INTO i_staff_id, d_amount;
-> if i_staff_id = 2 then
-> set @x1 = @x1 + d_amount;
-> else
-> set @x2 = @x2 + d_amount;
-> end if;
-> UNTIL 0 END REPEAT;
->
-> CLOSE cur_payment;
->
-> END;
-> $$
6.5. 流程控制
# IF 语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
# CASE 语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
Or:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
# LOOP语句，简单的循环，退出循环的条件需要使用其他的语句定义.通常可以使用 LEAVE 语句实现
[begin_label:] LOOP
statement_list
END LOOP [end_label]
# LEAVE语句，用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。
LEAVE [end_label]
# ITERATE语句，必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。
ITERATE [end_label]
# REPEAT 语句，有条件的循环控制语句，当满足条件的时候退出循环，
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
# WHILE 语句，有条件的循环控制语句，即当满足条件时执行循环的内容，
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
# WHILE是满足条件才执行循环，REPEAT是满足条件退出循环；WHILE在首次循环执行之前就判断条件，最少执行0次，REPEAT是在首次执行循环之后才判断条件，最少执行1次
7. 触发器
- 触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。可以协助应用在数据库端确保数据的完整性
# 创建触发器
# trigger_time触发时间，BEFORE在检查约束前触发，AFTER在检查约束后触发
# trigger_event触发事件INSERT、UPDATE 或者 DELETE
# 对同一个表相同触发时间的相同触发事件，只能定义一个触发器
# 别名OLD.和NEW.来引用触发器中发生变化的记录内容，触发器只支持行级触发的，不支持语句级触发
# 对于有重复记录，需要进行UPDATE操作的INSERT，触发顺序是BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE
# 对于没有重复记录的INSERT，触发顺序是 BEFORE INSERT、AFTER INSERT
# 对于那些实际执行 UPDATE 操作的记录，仍然会执行 BEFORE INSERT 触发器的内容
mysql> CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
# 删除触发器
mysql> DROP TRIGGER [数据库.]trigger_name
# 查询指定触发器的指定信息
mysql> select * from information_schema.triggers where trigger_name = 'ins_film_bef'
# 触发器的使用
# 触发程序不能调用将数据返回客户端的存储程序，不能使用采用CALL语句的动态SQL，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程
# 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANSACTION、COMMIT 或 ROLLBACK。
# 触发器是按照 BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚
8. MySQL存储引擎
8.1. MySQL存储引擎特性
# 查看mysql默认存储引擎
mysql> show variables like 'table_type';
# 查看当前数据库支持的存储引擎
mysql> SHOW ENGINES或者SHOW VARIABLES LIKE 'have%'
8.2. MyISAM
- 数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。需要在建表时通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定指定索引文件和数据文件的绝对路径
- MyISAM类型的表可能会损坏，可以用CHECK TABLE检查MyISAM表，并用REPAIR TABLE修复一个损坏的MyISAM表。表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因
- MyISAM表支持3种不同的存储格式，
- 静态表（默认）:每个记录长度固定。优点是存储速度快，容易缓存，故障恢复容易；缺点是占用的空间通常比动态表多。数据在存储的时候会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉
- 动态表:包含变长字段，优点是占用的空间相对少，但是频繁地更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE或myisamchk -r改善性能，故障恢复困难
- 压缩表。由myisampack工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支
- Myisam 用 B+Tree 来存储数据的。
8.3. InnoDB
自动增长列
- 插入值如果是空或者0，则实际插入的将是自动增长后的值。如果指定了具体值，且小于自增值，则自增值不变；大于等于自增值，则自增值修改为具体值
- 自增值生成算法：从auto_increment_offset初始值开始，以auto_increment_increment为步长，持续叠加
- ALTER TABLE *** AUTO_INCREMENT = n;在内存中设置自动增长列的初始值，默认从1开始，如果数据库重启，那么这个值就会丢失，需要重新设置。可使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条记录，那么返回的是第一条记录使用的自动增长值。对于InnoDB，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，对于MyISAM，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的
外键约束:在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。仅InnoDB 存储引擎支持对外部关键字约束条件的检查。其他不支持，仅起备忘作用
存储方式:InnoDB 存储表和索引有以下两种方式。
- 共享表空间存储（innodb_file_per_table=OFF），表结构保存在.frm 文件中，数据和索引保存在 innodb_data_home_dir和 innodb_data_file_path定义的表空间中，可以是多个文件。表删掉空间不回收
- 多表空间存储（innodb_file_per_table=ON），表结构保存在.frm文件中，数据和索引单独保存在.ibd中。如果是个分区表，每个分区保存在表名+分区名.ibd文件，可以在创建分区的时候指定每个分区的数据文件的位置，将表IO均匀分布在多个磁盘上。修改多表空间的参数innodb_file_per_table并重启后，只对新建的表生效。即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和未作日志放在这个文件中。表删掉空间回收
InnoDB 通过 B+Tree 结构对 ID 建索引，然后在叶子节点中存储记录。
若建索引的字段不是主键 ID，则对该字段建索引，然后在叶子节点中存储的是该记录的主键，然后通过主键索引找到对应的记录。
8.4. MEMORY
- MEMORY使用存在内存中的内容来创建表。存储为一个磁盘文件.frm。访问速度快
- MySQL启动时使用--init-file选项，把INSERT INTO ... SELECT 或 LOAD DATA INFILE放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表
- 释放被MEMORY表使用的内存，执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表（使用 DROP TABLE 操作）。
- max_heap_table_size表示每个MEMORY表中可以放置的数据量的大小
8.5. InnoDB与MEMORY区别
- InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键id。称之为索引组织表（IndexOrganizied Table）。Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（HeapOrganizied Table）。
- 当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，内存表找到空位就可以插入新值；内存表每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用。
- 数据位置发生变化的时候，InnoDB表只需要修改主键索引，内存表需要修改所有索引；
- InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
9. MEMORY表
- 内存临时表排序的时候使用了rowid排序方法
- 如果内存临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。磁盘临时表使用的引擎默认是InnoDB，是由参数internal_tmp_disk_storage_engine控制的。当使用磁盘临时表的时候，对应的就是一个没有显式索引的InnoDB表的排序过程。
- 不建议你在生产环境上使用内存表。
- 内存表的锁是表锁
- 数据没有持久化
- 内存临时表刚好可以无视内存表的两个不足
- 临时表不会被其他线程访问，没有并发性的问题；
- 临时表重启后也是需要删除的，清空数据这个问题不存在；
- 备库的临时表也不会影响主库的用户线程。
- 内存表支持hash索引，对复杂查询的加速效果还是很不错的。
- 怎么避免内存表突然丢数据，然后导致主备同步停止的情况。假设主库暂时不能修改引擎，
- 先把备库的内存表引擎先都改成InnoDB。对于每个内存表，执行set sql_log_bin=off;alter table tbl_name engine=innodb;避免备库重启的时候，数据丢失的问题。由于主库重启后，会往binlog里面写“delete from tbl_name”，这个命令传到备库，备库的同名的表数据也会被清空。因此，就不会出现主备同步停止的问题。
- 如果由于主库异常重启，触发了HA，这时候我们之前修改过引擎的备库变成了主库。而原来的主库变成了新备库，在新备库上把所有的内存表（这时候表里没数据）都改成InnoDB表。所以，如果我们不能直接修改主库上的表引擎，可以配置一个自动巡检的工具，在备库上发现内存表就把引擎改了。
- 同时避免创建新的内存表。
9.1. MERGE
- MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，MERGE表本身并没有数据
- 对MERGE表的插入操作，是通过INSERT_METHOD子句定义插入的表，使用FIRST或LAST值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作，对MERGE表的删除操作，只是删除定义，对内部的表没有任何的影响
- MERGE表在磁盘上保留两个文件，文件存储表定义.frm 和文件包含组合表的信息.MRG(由哪些表组成、插入新的数据时的依据)
- MERGE表并不能智能地将记录写到对应的表中，而分区表是可以的。通常使用MERGE表来对多个表进行查询和更新操作，而对这种按照时间记录的操作日志表则可以透明地进行插入操作。
10. MySQL连接
10.1. 长连接与短连接
长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
有时候Mysql占用内存涨的很快的原因和解决方案？ 建立连接的过程通常是比较复杂的，在使用中要尽量使用长连接。但太多长连接可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。解决方案1.使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开长连接。2.执行mysql_reset_connection来重新初始化连接资源，无需重连和重新做权限验证(MySQL5.7以上)
MySQL建立连接的过程成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。
如果客户端由于压力过大，迟迟不能接收数据，会对服务端造成什么严重的影响？核心是造成了“长事务”。如果前面的语句有更新，意味着它们在占用着行锁，会导致别的语句更新被锁住；当然读的事务也有问题，就是会导致undo log不能被回收，导致回滚段空间膨胀
11. MySQL索引
- 索引是一种用于快速查询数据的数据结构，由存储引擎实现
11.2. 索引分类
11.2.1. 按物理存储分类
11.2.1.1. 聚簇索引
- 聚簇索引：
- 查询速度快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据
- 依赖于有序的数据 ：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
- 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。
11.2.1.2. 非聚簇索引
- 非聚集索引：
- 更新代价比聚集索引要小。非聚集索引的叶子节点是不存放数据的
- 非聚集索引也依赖于有序的数据
- 可能会二次查询（回表）当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询
11.2.2. 按字段特性分类
11.2.2.1. 主键索引
- 在InnoDB表中，当没有显示的指定表的主键时，会使用唯一索引且不允许存在null值的字段为默认的主键，否则InnoDB将会自动创建一个6Byte的rowid作为主键
- 辅助索引（二级索引）：叶子节点存储的数据是主键
11.2.2.2. 唯一普通索引和唯一索引的区别
- 查询过程：查询时都是通过B+树从树根开始，按层搜索到叶子节点(数据页)，然后在数据页内部通过二分法来定位记录。对于普通索引来说，查找到满足条件的第一个记录后，一直查找直到找到第一个不满足条件的记录，对于唯一索引来说，查找到第一个满足条件的记录后，就会停止继续检索。由于InnoDB的数据是按数据页为单位(默认是16KB)来读写的。当找到满足条件的记录的时候，它所在的数据页就都在内存里了。对于普通索引来说，多一次查找和判断下一条记录的操作，就只需要一次指针寻找和一次计算，当如果满足条件的记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，但是，对于整型字段，一个数据页可以放近千个key，因此出现概率低。计算平均性能差异时，对于CPU来说可忽略不计
- 更新过程：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。保证这个数据逻辑的正确性
- change buffer在内存中有拷贝，也会被写入到磁盘上。将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。数据页被访问、系统后台线程定时任务、在数据库正常关闭都会merge。如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率
- 对于唯一索引来说，所有的更新操作都要先判断是否违反唯一性约束。必须要将数据页读入内存才能判断。因此，唯一索引的更新就不能使用change buffer，只有普通索引能使用
- change buffer用的是buffer pool里的内存，innodb_change_buffer_max_size表示change buffer最大占用buffer pool的百分比
- 如果要在表中插入一个新记录的话，InnoDB的处理流程
- 1.这个记录要更新的目标页在内存中。对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。
- 2.这个记录要更新的目标页不在内存中。对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。将数据从磁盘读入内存涉及随机IO的访问。change buffer因为减少了随机磁盘访问，所以对更新性能的提升明显
- change buffer的使用场景
- 对普通索引写多读少的更新过程有加速作用(账单类，日志类系统)，而不适用于唯一索引。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。
- change buffer的操作会在事务提交时记录到redo log，所以崩溃恢复的时候，change buffer不会丢失数据
- merge的执行流程
- 从磁盘读入数据页到内存（老版本的数据页）
- 从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新版数据页
- 写redo log。这个redo log包含了数据的变更和change buffer的变更
- 数据页和内存中change buffer对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据
11.2.3. 按索引字段个数分类
11.2.3.1. 单列索引
11.2.3.2. 组合索引
- 组合索引 是指对表上的多个列进行索引。联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2
- where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引。这样减少索引存储空间的开销
- 如何安排索引内的字段顺序？
- 索引的复用能力。如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
- 空间。如果既有联合查询，又有基于a、b各自的查询呢？这时候你不得不需要同时维护(a,b)、(b) 这两个索引。最好在长度较小的字段上建立索引
11.3. 索引使用
11.4. 重建索引
- 索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间
- 重建普通索引k alter table T drop index k; alter table T add index(k);
- 重建主键索引id alter table T engine=InnoDB;
11.5. 查看索引使用情况
# Handler_read_key代表了一个行被索引值读的次数，值越高表示索引经常使用
# Handler_read_rnd_next在数据文件中读下一行的请求数。值越高表示索引不经常使用
mysql> show status like 'Handler_read%'
11.6. MySQL索引实现
- 在MySQL中，索引是在存储引擎层实现的
- 在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树。
- 根据叶子节点的内容分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。称为二级索引（secondary index）
- 如果是主键查询，则只需要搜索主键所在的B+树
- 如果普通索引查询方式，则需要先搜索索引树，得到主键的值，再到主键所在的索引树搜索一次。这个过程称为回表。基于非主键索引的查询需要多扫描一棵索引树。因此，应该尽量使用主键查询。
11.7. 索引维护
- B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。可能在现有记录后面插入一个新记录。可能需要逻辑上挪动后面的数据，空出位置。如果最后所在的记录所在的数据页已经满了，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下性能下降。页分裂操作降低了数据页的利用率。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程
11.8. 索引设计原则
- 考虑建立联合索引而不是单列索引。因为索引是需要占用磁盘空间的。离散度大的或者长度小的列放到联合索引的前面select count(distince xxx) from t;尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL
- 索引字段越小越好，节省存储空间（因为数据库数据存储单位是以“页”为单位的，数据存储的越多，IO也会越大）
- 取消外键，可交由程序来约束，性能更好
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，会降低查询和连接的性能并增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次
- 字符串创建索引的方式
- 直接创建完整索引，可能比较占用空间；
- 前缀索引，节省空间，磁盘IO减少，索引高速缓存中的块能容纳更多的键值，查询速度加快，但会增加查询扫描次数，并且不能使用覆盖索引，不支持范围查询； 可通过查询count(distinct left(field,n))/count(1) 大于0.95的n，确定使用多长的前缀
- 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；查询select * from t where x = reverse('x'); 记得使用count(distinct)方法去做个验证。
- hash字段/crc32字段索引，查询性能稳定，有额外的存储和计算消耗，不支持范围扫描。添加一个整数字段保存校验码，同时在这个字段上创建索引。这样索引就变小了，记得查询要判断filed和校验码是否相同。select * from t where x=crc32/hash('x') and x='x'
- 普通索引(业务唯一)和唯一索引：
- 写多读少的场景普通索引能用上change buffer优化，推荐使用普通索引而唯一只需业务保证即可
- 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。区分度大的
- 在一些“归档库”的场景，可以考虑使用唯一索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引
- 索引列的基数越大，索引效果越好。组合索引基数更大，推荐使用
- 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储 顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
- 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
- 避免频繁创建和删除临时表，以减少系统表资源的消耗。在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
- 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
11.9. 索引的常见数据模型(实现索引的方式)
- 哈希表
- 以键-值（key-value）存储数据的结构，只要输入查找值key，就可以找到其对应值Value。把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。多个key值经过哈希函数的换算，会出现同一个值的情况。哈希冲突，处理方法是拉出一个链表。查找时先用哈希函数把key换算，找到在哈希表的位置，然后顺序遍历链表，找到value。在哈希表中的key不是递增的，好处是增加新value时很快，只需往后追加，因为不是有序的，所以区间查询速度慢，每次要全部扫描。适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。
- 有序数组
- 在等值查询时，key值按顺序保存，使用二分法就能快速找到key和对应值，时间复杂度是O(log(N)),范围查询时，先用二分查找找到第一个大于最小值的key，然后向右遍历到第一个大于最大值的key，退出循环即可。但是在更新数据的时候，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。只适用于静态存储引擎，比如你要保存的是2017年某个城市的所有人口信息，这类不会再修改的数据。
- B树&B+树：
- B树/B-树/多路平衡查找树 ，B+树是B树的一种变体。B是Balanced(平衡)的意思。
- B树的所有节点既存放键(key) 也存放 数据(data)； B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
- B树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
- B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了；B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
11.10. 自增主键使用场景
- 从性能看，自增主键每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高
- 从存储空间的角度来看。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小，自增主键往往占用的空间比较小
- 什么场景适合用业务字段直接做主键？KV场景，当只有一个唯一索引，此时业务字段直接做主键，由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小。考虑到“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树
12. MySQL事务
- 事务：一个事务中多次操作要么全部成功，要么全部失败
- 在MySQL中，事务支持是在引擎层实现的。
- MySQL 支持对 MyISAM 和 MEMORY 存储引擎的表进行表级锁定，对 BDB 存储引擎的表进行页级锁定，对 InnoDB 存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是有时用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。
12.1. 事务隔离级别
V1、V2、V3的返回值分别是什么?
- 读未提交，V1=V2=V3=2
- 读提交，V1=1,V2=2。事务B的更新在提交后才能被A看到。所以，V3=2。
- 可重复读，V1=V2=1，V3=2。V2=1是因为事务在执行期间看到的数据前后必须一致
- 串行化，在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。从A的角度看，V1=V2=1，V3=2
什么时候需要“可重复读”的场景呢？
数据校对逻辑的案例。假设你在管理一个个人银行账户表。一个表存了每个月月底的余额，一个表存了账单明细。这时候你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响
为什么表结构不支持“可重复读”？这是因为表结构没有对应的行数据，也没有rowtrx_id，因此只能遵循当前读的逻辑
可重复读也可避免幻读，使用行X锁（SELECT ... FOR UPDATE，约等于SERIALIZABLE），即便当前记录id不存在，当前事务也会获得一把记录锁（因为InnoDB的行锁锁定的是索引，存在就加行X锁，否则加 next-key lock间隙X锁），其他事务则无法插入此索引的记录，故杜绝了幻读。
12.2. 查看事务隔离级别
- Oracle数据库的默认隔离级别其实就是“读提交”，因此对于从Oracle迁移到MySQL的应用，一定要将MySQL的隔离级别设置为“读提交”。即启动参数transaction-isolation的值设置成READ-COMMITTED。
- show variables like 'transaction_isolation'
12.3. 事务隔离级别的实现
12.3.1. 锁
- 在读取数据前，对其加锁，阻止其他事务对数据进行修改。
12.3.2. MVCC
- 不用加任何锁，非阻塞并发读，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制/多版本数据库（MultiVersion Concurrency Control，简称 MVCC 或MCC）
- 是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。
12.3.2.1. 快照读
- 在可重复读级别下，不加锁的select操作就是快照读，即不加锁的非阻塞读；基于提高并发性能的考虑，快照读的实现是基于多版本并发控制MVCC,在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
- 串行化隔离级别下，快照读会退化为当前读。
12.3.2.2. 当前读（current read）
- 当前读（current read）读已经提交完成的最新版本,包括update，insert，delete语句、select * from t where id = 1 [lock in share mode/for update]
12.3.2.3. MVCC的实现
- InnoDB使用一致性读视图consistent read view，undo log，隐藏字段，实现MVCC（多版本并发控制）/事务隔离。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性。如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。
- InnoDB每个事务都有自增的唯一的事务ID即transaction id。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本，记为rowtrx_id。数据表中的一行记录，其实可能有多个版本，每个版本有自己的rowtrx_id。其中，多个版本的数据记录在undolog 。事务读取数据时需要根据当前版本和undo log计算出来值。
- undo log
- 当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现快照读
- insert undo log在insert 操作中产生。只对事务本身可见【只在事务回滚时需要】故该可以在事务提交后直接删除。不需要进行 purge 操作
- update undo log update 或 delete 操作中产生。需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。
- 当系统里没有比这个回滚日志更早的read-view的时候。回滚日志会被删除
- 版本链：类似一个链表，通过回滚指针DB_ROLL_PTR，串联起来。链表头部是最新的数据，尾部是最旧的记录
12.4. 如何解决幻读？
- 产生幻读的原因是行锁只能锁住行，不能锁住插入新纪录行。只好引入间隙锁(Gap Lock)。锁的就是两个值之间的空隙
- 执行 select * from t where d=5 for update的时候，不仅将给行加上了行锁，还给行两边的空隙加上了间隙锁。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
- 间隙锁和行锁合称next-key lock，间隙锁记为开区间，把next-key lock记为前开后闭区间。 InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合“都是前开后闭区间”。
- 间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的
- 间隙锁是在可重复读隔离级别下才会生效的。也可以使用读提交隔离级别加binlog_format=row的组合
12.5. 事务管理
12.5.1. MyISAM事务管理
# LOCK TABLES可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
# UNLOCK TABLES可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁
mysql> LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
mysql> UNLOCK TABLES
12.5.2. InnoDB事务管理
- 在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对非事务类型的表进行特别的处理，因为 COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。
- 所有的 DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。
# MySQL是默认自动提交（Autocommit）的,一条sql语句就是一个事务，如果需要通过明确的Commit和Rollback来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务
# 在第一个操作表的语句时启动事务
# 如果在锁表期间，start transaction会造成一个隐含unlock tables被执行
mysql> START TRANSACTION | BEGIN [WORK]
# 立即开启事务
mysql> start transaction with consistent snapshot
# 修改当前连接的提交方式，如果设置了0，则事务持续到提交或者回滚或者连接断开
# 如果只是对某些语句需要进行事务控制，则使用 START TRANSACTION 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改 AUTOCOMMIT 来控制事务比较方便，这样不用在每个事务开始的时候再执行 START TRANSACTION 语句。
mysql> SET AUTOCOMMIT = {0 | 1}
# 提交/回滚事务
# CHAIN 和 RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接
mysql> COMMIT/ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
# 在事务中可以通过定义SAVEPOINT，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT，满足不同的条件时，回滚不同的SAVEPOINT。如果定义了相同名字的 SAVEPOINT，则后面定义的SAVEPOINT 会覆盖之前的定义。删除后的SAVEPOINT，不能再执行ROLLBACK TO SAVEPOINT命令
mysql> savepoint test;
mysql> rollback to savepoint test;
mysql> RELEASE SAVEPOINT test;
12.6. 怎么设计事务连接池？√
- 事务连接池可以保证同一个事务中多个操作共用一个数据库连接，从而保证了事务的一致性和隔离性。
- 事务连接池会在第一个操作开始时获取数据库连接，并将该连接与当前线程绑定，之后的所有操作都会使用该连接，直到事务结束时才将连接释放。这样就可以保证同一个事务中的所有操作都使用同一个连接
- 事务连接池在实现上需要考虑线程安全、事务嵌套等问题，因此比传统连接池更加复杂和高级。一些流行的Java框架和中间件，如Spring和Hibernate，都提供了事务连接池的实现。
12.7. 为什么建议你尽量不要使用长事务?如何避免长事务对业务的影响？
- 查找持续时间超过60s的事务。select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
- 长事务意味着系统里面会存在很老的事务视图。这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小；
- 长事务还占用锁资源，也可能拖垮整个库
- 应用开发端：
- 确认是否使用了set autocommit=0。可通过general_log的日志来确认。把它改成1
- 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。
- 业务连接数据库的时候，根据业务本身的预估，通过SETMAX_EXECUTION_TIME来控制每个语句执行的最长时间，避免单个语句执行太长时间
- 数据库端：
- 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；
- Percona的pt-kill这个工具不错，推荐使用；
- 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
- MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大值）如果出现大事务导致回滚段过大，这样设置后清理起来更方便
12.8. 分布式事务的使用
12.8.1. 分布式事务的原理
- MySQL中使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器
- 资源管理器（RM）用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM 管理的事务
- 事务管理器（TM）用于协调作为一个分布式事务一部分的事务。TM与管理每个事务的RMS进行通讯。一个分布式事务中各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种命名方法进行标识
- MySQL相当于一个用于管理分布式事务中的 XA 事务的资源管理器。与 MySQL 服务器连接的客户端相当于事务管理器
- 要执行一个分布式事务，必须知道这个分布式事务涉及到了哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或回滚时。根据每个资源管理器报告的有关执行情况的内容，这些分支事务必须作为一个原子性操作全部提交或回滚。要管理一个分布式事务，必须要考虑任何组件或连接网络可能会故障。
- 用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。
- 在第一阶段，所有的分支被预备好。它们被TM告知要准备提交。这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。
- 在第二阶段，TM告知RMS是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。在有些情况下，一个分布式事务可能会使用一阶段提交。例如，当一个事务管理器发现，一个分布式事务只由一个事务资源组成（即单一分支），则该资源可以被告知同时进行预备和提交。
12.8.2. 分布式事务语法
# 启动XA事务。xid是一个XA事务标识符，用来唯一标识一个分布式事务。xid值由客户端提供，或由MySQL服务器生成
# xid 值包含 1～3 个部分：gtrid [, bqual [, formatID ]]
# gtrid分布式事务标识符，相同的分布式事务应该使用相同的gtrid，表示xa事务属于哪个分布式事务
# bqual分支限定符，默认值是空串。对于一个分布式事务中的每个分支事务，bqual值必须唯一
# formatID 是一个数字，用于标识由 gtrid 和 bqual 值使用的格式，默认值是1
mysql> XA {START|BEGIN} xid [JOIN|RESUME]
# 标识XA事务，然后使事务进入 PREPARE 状态，两阶段提交的第一个提交阶段。
mysql> XA END xid [SUSPEND [FOR MIGRATE]]
mysql> XA PREPARE xid
# 提交或者回滚具体的分支事务。两阶段提交的第二个提交阶段，分支事务被实际的提交或者回滚
mysql> XA COMMIT xid [ONE PHASE]
mysql> XA ROLLBACK xid
# 返回当前数据库中处于PREPARE状态的分支事务的详细信息
mysql> XA RECOVER
12.8.3. 分布式事务存在的问题
- 分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时的故障解决。
- 如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚得操作，但是提交的事务没有写 binlog，存在一定的隐患，可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致
- 如果分支事务的客户端连接异常中止，那么数据库会自动回滚未完成的分支事务，如果此时分支事务已经执行到 prepare 状态，那么这个分布式事务的其他分支可能已经成功提交，如果这个分支回滚，可能导致分布式事务的不完整，丢失部分分支事务的内容。
- 如果分支事务在执行到 prepare 状态时，数据库异常，且不能再正常启动，需要使用备份和 binlog 来恢复数据，那么那些在 prepare 状态的分支事务因为并没有记录到 binlog，所以不能通过 binlog 进行恢复，在数据库恢复后，将丢失这部分的数据。
13. MYSQL锁
- MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking），同一张表上任何时刻只能有一个更新在执行，影响业务并发度
- BDB存储引擎采用的是页面锁（page-level locking）
- InnoDB存储引擎默认行级锁（row-level locking），也支持表级锁，但默认采用行级锁
- 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用；行级锁则适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用
13.1. 按锁的粒度分类
13.1.1. 全局锁
- 全局锁(FTWRL)是对整个数据库实例加锁。让整个库处于只读状态，使用场景是不支持事务的引擎逻辑备份
# 全局锁
mysql> flush tables with read lock
# 主动释放锁。也可以在客户端断开的时候自动释放
mysql> unlock tables
13.1.2. 表级锁
13.1.2.1. 元数据锁MDL(meta data lock)
- 在访问一个表的时候会被自动加上。为了保证读写的正确性。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。读锁之间不互斥，读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
- 事务中的MDL锁在语句执行是申请，事务提交时才释放
13.1.2.2. MyISAM表锁
- MyISAM 存储引擎只支持表锁
- 查询表级锁争用情况show status like 'table%'，如果Table_locks_waited值比较高，则存在着较严重的表级锁争用情况
- 表级锁的锁模式包括：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。读操作与写操作之间，以及写操作之间是串行的！读读操作是并行的
- lock table 表名 write/read [local];使用unlock tables或者客户端断开的时候释放。别的线程的会被阻塞，local表示在满足 MyISAM 表并发插入条件的情况下，允许其他用户在表尾并发插入记录
- MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。显示加锁一般是为了事务，实现对某一时间点多个表的一致性读取
- 在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁。因此不会出现死锁（Deadlock Free）。当使用 LOCK TABLES 时，不仅需要一次锁定用到的所有表，且同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！
- 并发插入（Concurrent Inserts）
- 系统变量concurrent_insert控制MyISAM存储引擎并发插入,
- 0/NEVER则不允许并发插入。
- 1/AUTO 如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。默认
- 2/ALWAYS ，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录
- 可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞
- 系统变量concurrent_insert控制MyISAM存储引擎并发插入,
- MyISAM锁调度
- 写优先调度机制：进程同时请求读锁和写锁，写进程先获得锁；即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！MySQL认为写请求一般比读请求要重要。因此MyISAM 表不太适合于有大量更新操作和查询操作应用，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
- 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
- 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
- 系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
- 一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。
13.1.2.3. InnoDB什么时候使用表锁
- 情况1：事务需要更新大表大量数据，如果使用默认的行锁，事务执行效率低，可能造成其他事务长时间锁等待和锁冲突
- 情况2：事务涉及多个表，可能引起死锁，造成大量事务回滚。可以考虑一次性锁定事务涉及的表
- InnoDB使用表锁注意点
- LOCK TABLES给InnoDB加表级锁，由Server层负责，仅当autocommit=0、innodb_table_locks=1（默认）时，InnoDB才会给MySQL加的表锁，Server层也才能感知InnoDB加的行锁，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁
- 事务结束前，不要用UNLOCK TABLES释放表锁，因为会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES 加的表级锁，必须用UNLOCK TABLES释放
# 写表 t1 并从表 t 读
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;
13.1.3. InnoDB行锁
# 查看InnoDB行锁争用情况（如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg）
mysql> show status like 'innodb_row_lock%';
# 如果发现锁争用比较严重，可通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因
# 设置监视器
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
# 查看监视器
show engine innodb status;
# 关闭监视器
DROP TABLE innodb_monitor;
# 设置监视器后，在 SHOW INNODB STATUS 的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。打开监视器以后，默认情况下每 15 秒会向日志中记录监控的内容，如果长时间打开会导致.err 文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器
- 两阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上的，等到事务结束时才释放。
- 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
- 服务器CPU消耗接近100%，但整个数据库每秒就执行不到100个事务。这是什么原因呢？可能是死锁了
13.1.3.1. InnoDB 行锁实现方式
- InnoDB行锁是通过给索引对应的记录所要索引值加锁来实现的，Oracle是通过在数据块中对相应数据行加锁来实现的
- InnoDB行锁在不通过索引条件查询的时候，使用的是表锁
- 是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的。如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，InnoDB将使用表锁
13.2. 按锁的类型分类
13.2.1. 读写锁(行锁模式)
- 共享锁(S)/读锁：事务在读取记录的时候获取共享锁，允许多个事务同时获取。SELECT * FROM 表名 WHERE条件 LOCK IN SHARE MODE
- 排他锁(X)/写锁：事务在修改记录的时候获取排他锁，不允许多个事务同时获取。SELECT * FROM 表名 WHERE条件 FOR UPDATE
- 共享锁主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有其他事务对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT... FOR UPDATE 方式获得排他锁
13.2.2. 意向锁(表锁模式)
- 根据表中的记录没有行锁并快速判断是否可以对某个表使用表锁。
- 意向共享锁(Intention Shared Lock,IS)事务打算给数据行加行共享锁(S锁)，加共享锁前必须先取得该表的IS锁
- 意向排他锁(Intention Exclusive Lock，IX)事务打算给数据行加行排他锁(x锁)，加排他锁前必须先取得该表的IX锁
- 意向锁是InnoDB自动加的，无法手动操作，在为数据行加共享/排他锁之前，InooDB会先获取该数据行所在在数据表的对应意向锁
13.2.3. 读写锁与意向锁兼容性
- X与任何锁冲突，IX与S冲突，其他都兼容
13.3. InnoDB中行锁定的方式
13.3.1. 记录锁（Record Lock）
13.3.2. 间隙锁（Gap Lock）
13.3.3. 临键锁（Next-Key 锁）
- 使用用范围条件而不是相等条件检索数据并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；也会对键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，加锁，这种锁机制就是间隙锁（Next-Key锁）
- 间隙锁的目的，为了防止幻读，以满足相关隔离级别的要求，也为了满足其恢复和复制的需要。
- 在使用范围条件检索并锁定记录时，InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入，往往会造成严重的锁等待。因此，应用开发要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件
- 如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
- 一个空表只有一个间隙，比如select * from t where id>0 for update;加锁的范围就是next-key lock (-∞, supremum]
13.3.4. 加锁规则
# 开启锁监视器
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
- 可重复读隔离级别(repeatable-read)下。遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。加锁的基本单位是next-key lock。前开后闭区间，查找过程中访问到的对象才会加锁（limit限制访问对象也会限制锁的范围）
- 提交隔离级别(read-committed)下，没有间隙锁
- 根据完整结果集判断加锁规则（受order by条件,where条件，limit等影响）
- 等值查询（in也是等值查询）
- 记录不存在都是加(x-1,x+1)Gap-key Lock
- 记录存在
- 聚簇索引：聚簇索引Record Lock（Next-key Lock降级优化为 Record Lock）
- 唯一索引：唯一索引Record Lock、聚簇索引Record Lock（都是Next-key Lock降级优化为 Record Lock）
- 普通索引：普通索引(x-1,x]Next-key Lock、(x,x+1)Gap-key Lock、聚簇索引Record Lock
- 范围查询
- 聚集索引
- 匹配记录加Next-key Lock;：只有>=的等值(=)匹配加Record Lock
- 对于<和<=：向右扫描直到找到不匹配的索引记录上Next-key Lock.
- 对于>和>=，会对supremum (上界限伪值) 上Next-key Lock：锁的是聚集索引最大值后面的间隙
- 唯一索引 和 普通索引：
- 如果走了索引：
- 匹配记录加Next-key Lock，对应的聚集索引上Record Lock;
- 对于<和<=，向右扫描直到找到不匹配的索引记录上Next-key Lock，对应的聚集索引上Record Lock
- 对于>和>=，会对supremum (上界限伪值) 上Next-key Lock：锁的是该索引最大值后面的间隙
- 如果没走索引，那么就会把所有 聚集索引记录和间隙都锁上，就是所谓的锁表，或叫行锁升表锁.
- 如果走了索引：
- 聚集索引
13.4. 恢复和复制的需要，对 InnoDB 锁机制的影响
- MySQL的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点。
- MySQL的恢复是重新执行BINLOG中的SQL语句。Oracle是基于数据库文件块的。
- MySQL的恢复是按按照事务提交的先后顺序进行的。Oracle的恢复是按系统更新号（System Change Number，SCN）来进行的，每个事务都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的
- MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，不允许出现幻读，实际上是要求事务要串行化。因此InnoDB要用到间隙锁
- 对于insert into target_tab select * from source_tab where ...和create table new_tab ...select ... From source_tab where ...(CTAS)语句，InnoDB给source_tab加共享锁，并没有使用多版本数据一致性读技术！为了保证恢复和复制的正确性。不加锁的话，如果在上述语句执行过程中，其他事务对 source_tab 做了更新操作，就可能导致数据恢复的结果错误。因此，INSERT...SELECT...和CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。
- 如果应用中一定要用这种 SQL 来实现业务逻辑，又不希望对源表的并发更新产生影响，可通过使用“select * from source_tab ... Into outfile”和“load data infile ...”语句组合来间接实现
14. MYSQL日志
- WAL技术(Write-Ahead Logging)：先顺序写日志，再写磁盘.尽量减少随机读写。为了解决MySQL里如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本很高的问题
14.1. 日志分类
14.1.1. mysql配置文件
# 搜索mysql配置文件位置
shell> mysql --help | grep 'Default options' -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
14.1.2. 错误日志
# 用--log-error[=file_name]选项启动
shell> $(which mysqld_safe) --log-error=/var/log/newlog.log &
# 查看错误日志位置->log_error:/var/log/mysqld.log
mysql> show variables like 'log_error';
14.1.3. 二进制日志（BINLOG）
- binlog是MySQL的Server层实现的，所有引擎都可以使用。
- binlog是逻辑日志，记录语句的原始逻辑，比如“给ID=2这一行的c字段加1”
- binlog是可以追加写入的。binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
- 为什么会有两份日志呢？因为MyISAM没有crash-safe的能力，binlog日志只能用于归档。所以InnoDB使用另外一套redo log来实现crash-safe能力。
- binlog的写入机制：事务执行过程中，先把日志写到binlog cache内存(线程私有)，然后write到page cache，事务提交的时候，fsync持久化到binlog文件中。并清空binlog cache。
- binlog_cache_size控制单个线程内binlog cache所占内存的大小。如果超过了，就要暂存到磁盘。
- sync_binlog控制write和fsync的时机
- 0表示每次提交事务都只write，不fsync；
- 1表示每次提交事务都会执行fsync持久化到磁盘。保证MySQL异常重启之后binlog不丢失
- 大于1表示每次提交事务都write，但累积N个事务后才fsync。主机发生异常重启，会丢失最近N个事务的binlog日志
# my.cnf配置或者用--log-bin[=file_name]选项启动
# 配置serverid
server-id=1
# 开启binlog日志
log_bin=ON
# binlog日志的基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
# binlog文件的索引文件，管理所有binlog文件
log_bin_index=/var/lib/mysql/mysql-bin.index
# 设置日志路径，注意路经需要mysql用户有权限写=上面最后三句
# log-bin = /data/mysql/logs/mysql-bin.log
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置binlog清理时间 临时设置binlog文件保留时长，重启失效set global expire_logs_days=30;
expire_logs_days = 7
#binlog每个日志文件大小 临时设置binlog大小，重启失效set global max_binlog_size = 1024;
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
# 重启mysql
1、使用 service 启动：service mysql start
2、使用 mysqld 脚本启动：/etc/inint.d/mysql start
3、使用 safe_mysqld 启动：safe_mysql&
1、使用 service 启动：service mysql stop
2、使用 mysqld 脚本启动：/etc/inint.d/mysql stop
3、mysqladmin shutdown
1、使用 service 重启：service mysql restart
2、使用 mysqld 脚本重启：/etc/inint.d/mysql restart
# 查看binlog配置
mysql> SHOW VARIABLES LIKE 'log_bin%';
# 查看binlog保留时长
mysql>show variables like 'expire_logs_days';
# 查看binlog文件大小限制
mysql>show variables like 'max_binlog_size';
# 查看日志列表
shell> system ls -ltr /var/lib/mysql/mysql-bin*
# 其中 mysql-bin.index是日志的索引文件，记录了最大的日志序号
mysql> show binary logs;
# 查看binlog内容
shell> mysqlbinlog mysql-bin.000002;
shell> mysqlbinlog --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" mysql-bin.000002
shell> mysqlbinlog --start-position=205 --stop-position=2205 mysql-bin.000002
shell> mysqlbinlog --no-defaults -d databasename mysql-bin.000002
mysql> show binlog events in 'mysql-bin.000123';
mysql> show master status;
# 删除日志
## 方法1删除所有 BINLOG 日志，新日志编号从“000001”开始
mysql> reset MASTER;
## 方法2删除编号000006之前的日志
mysql>purge master logs to 'mysql-bin.000006';
## 方法3删除日期为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志
mysql> purge master logs before '2007-08-10 04:07:00';
## 方法4设置参数--expire_logs_days=#，过了指定的天数后日志将会被自动删除
在 my.cnf 的[mysqld]中加入“expire_logs_day=3”
将系统时间改为 3 天以后
shell> date -s '20071226 14:00:00'
show variables like 'expire_logs_days'; # 查看过期时间
set global expire_logs_days = 30; # 设置过期时间
# 触发日志文件更新
mysql> flush logs
# 修改配置后刷新日志文件
mysql>flush logs
# 其他选项
--binlog-do-db=db_name只记录指定数据库的binlog
--binlog-ignore-db=db_name忽略指定数据库的binlog
--innodb-safe-binlog和--sync-binlog＝N（每写 N 次日志同步磁盘）配合使用，使得事务在日志中的记录更加安全
14.1.3.1. binlog日志格式
# 当binlog_format=statement时，记录的是SQL原文,减少了日志量，节约了IO，提高性能。此时某些sql会产生warning，因为有可能产生主备不一致的情况，即在主数据库与在从数据库使用的索引不一样导致数据不一致的问题
# 使用以下函数的语句也无法被复制：
- LOAD_FILE()
- UUID()
- USER()
- FOUND_ROWS()
- SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
- 在 INSERT ...SELECT 会产生比 RBR 更多的行级锁 。
# 当binlog_format=statement时，insert into t values(10,10, now());如果这个binlog过了1分钟才传给备库的话，那主备的数据不就不一致了吗？原来binlog在记录event的时候，多记了一条命令：SET TIMESTAMP=1546103491。约定了接下来的now()函数的返回时间。因此值是固定的。确保了主备数据的一致性
mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
mysql> show binlog events in 'master.000001';
+--------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
|master.000001 | 5889 | Anonymous_Gtid | 1 | 5954 | SET @ESESSION.GTID_NEXT= 'ANONYMOUS' |
|master.000001 | 5954 | Query | 1 | 6041 | BEGIN |
|master.000001 | 6041 | Query | 1 | 6197 | delete from t/*comment/ wherea=4 andtmodified='2018-11-10’ limit 1 |
|master.000001 | 6197 | xid | 1 | 6228 | COMMIT/* xid=61 */ |
+--------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
# 当binlog_format=row时，binlog里面记录了真实删除行的主键id，这样binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题,但某些sql的日志量会很大
# 表结构变更的时候还是会以 Statement 模式来记录。
# server id 1，表示这个事务是在server_id=1的这个库上执行的。每个event都有CRC32的值，因为参数binlog_checksum=CRC32。
# Table_map event，显示了接下来要打开的表，map到数字226。每个表都有一个对应的Table_map event、都会map到一个单独的数字，用于区分对不同表的操作。
# Delete_rows event，用于定义删除的行为。
mysql> mysqlbinlog -vvdata/master.000001 --start-position=8900;
+--------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
|master.000001 | 8900 | Anonymous_Gtid | 1 | 5954 | SET @ESESSION.GTID_NEXT= 'ANONYMOUS' |
|master.000001 | 8965 | Query | 1 | 6041 | BEGIN |
|master.000001 | 9045 | Table_map | 1 | 6041 | table_id:226(test.t) |
|master.000001 | 9092 | Delete_rows | 1 | 6197 | table_id:226 flags: STMT_END_F |
|master.000001 | 9140 | xid | 1 | 6228 | COMMIT/* xid=68 */ |
+--------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
# 为什么会有mixed格式的binlog？
# mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。建议把binlog的格式设置为mixed。
# 一般的语句修改使用 Statement 格式保存 binlog 。
# 如一些函数，statement 无法完成主从复制的操作，则采用 Row 格式保存 binlog 。
# 适用场景？
- SQL操作多行数据时，Statement节省空间，但不可靠，Row可靠，但更占用空间。
# 每一种日志格式在复制中的优劣？
- Statement 可能占用空间会相对小一些，传送到 slave 的时间可能也短，但是没有 Row 模式的可靠。但在操作多行数据时更占用空间
- 在占用空间和可靠之间的选择。
14.1.4. 查询日志
# my.cnf配置或者--log[=file_name]或-l [file_name]选项启动
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# 查看查询日志位置
mysql>SHOW VARIABLES LIKE 'general_log%';
# 查看日志
shell> more mysql.log
14.1.5. 慢查询日志
# 设置慢查询日志
14.1.6. redo log
InnoDB引擎特有的日志
redo log是物理日志，记录的是“在某个数据页上做了什么修改”；
当有一条记录需要更新的时候，InnoDB引擎会把记录写到redo log并更新内存，在系统比较空闲的时候，将这个操作记录更新到磁盘里面
crash-safe是指InnoDB通过redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失
redo log的三种状态
- 存在redo log buffer中，物理上是在MySQL进程内存中(速度快)
- 写到磁盘(write)，但是没有持久化(fsync)，物理上是在文件系统的page cache里面(速度快)
- 持久化到磁盘，对应的是hard disk(速度慢)
参数innodb_flush_log_at_trx_commit控制redo log的写入策略
- 0表示每次事务提交时都只是把redo log留在redo log buffer中
- 1每次事务提交时都将redo log直接持久化到磁盘;保证MySQL异常重启之后数据不丢失
- 2表示每次事务提交时都只是把redo log写到page cache.InnoDB有一个后台线程，每隔1秒就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘
14.1.7. undo log（回滚日志）
- 它记录的是数据被修改前的样子，主要用于事务在发生异常时，可以回滚事务。
- 它保证事务的原子性。
- 每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log，并将undo log保存到磁盘。
- 在 MySQL5.5 之前，undo 只能存放在 ibdata* 文件里面， 5.6 之后，可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata* 之外。
14.2. 事务是如何通过日志来实现的?
- 因为事务在修改页时，要先记 undo ，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。 redo（里面包括 undo 的修改）一定要比数据页先持久化到磁盘。
- 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的状态。
- 崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo 把该事务的修改回滚到事务开始之前。如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。
14.3. 两阶段提交
14.3.1. 为什么日志必须有“两阶段提交”呢？
redo log保证了数据库崩溃后，重启数据库不丢失已经提交的事务数据
binlog保证了从备份加binlog数据库恢复时，数据库不丢失已经提交的事务数据
在两阶段提交的不同时刻，MySQL异常重启会出现什么现象？
- 如果redo log里面的事务已经有了commit标识，则直接提交
- 如果redo log里面的事务只有prepare，则判断对应的事务binlog是否存在并完整。是，则提交事务；否则回滚事务。
redo log 和 binlog是怎么关联的?
- 它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：
- 如果碰到既有prepare、又有commit的redo log，就直接提交；
- 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务
15. buffer Pool
- InnoDB存储引擎中的一个内存组件，里面缓存了磁盘上的真实数据，对数据库的增删改操作，主要是在这个缓存中执行的操作
- 作用
- 缓存更新结果change buffer，再配合redo log，就避免了随机写盘。加速更新
- 管理内存的数据页
- 由于有WAL机制，当事务提交的时候，磁盘上的数据页是旧的，那如果这时候马上有一个查询要来读这个数据页，是不是要马上把redo log应用到数据页呢？不需要。因为这时候内存数据页的结果是最新的，直接读内存页就可以了。所以Buffer Pool还有加速查询的作用。
- 内存命中率。在show engine innodb status的Buffer pool hit rate，要在99%以上
- redo log 和 binlog通过叫XID关联。崩溃恢复的时候，会按顺序扫描redo log：如果碰到既有prepare、又有commit的redo log，就直接提交；如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务
15.1. 数据页
- 数据库对数据抽象出一个数据页的概念，将很多行的数据放到一个数据页中。即磁盘中有很多数据页，每个数据页存放很多行数据。当要更新一行数据的时候，数据库会找到这行数据所在的数据页，然后从磁盘文件中把这行数据所在的数据页加载到 Buffer Pool 中。即Buffer Pool中存放的是一个一个的数据页
15.2. 缓存页
- Buffer Pool中存放的一个一个的数据页，通常叫做缓存页。（Buffer Pool是一个缓冲池，里面的数据是从磁盘缓存到内存中的）
- Buffer Pool中默认情况下，一个缓存页的大小和磁盘上一个数据页的大小是一一对应的，都是16KB。
15.3. sort_buffer
- sort_buffer：MySQL给每个线程分配用于排序的一块内存
- sort_buffer_size:MySQL为排序开辟的内存的大小。
- 全字段排序：如果行数据长度小于max_length_for_sort_data，将所有数据放入sort buffer排序,无需回表
- rowId排序：如果行数据长度大于max_length_for_sort_data，将排序字段和主键放入sort buffer排序，需要回表
- 如果排序数据量大于tmp_table_size，则分成多份磁盘临时文件使用归并排序算法辅助排序
- 如果排序数据量小于tmp_table_size，则会使用内存临时表
- 如果排序要取得数据量太小（limit 3），会使用优先队列算法(堆排序)
- 如果数据量小于sort_buffer_size，排序就在内存中完成
- order by索引，并且所有索引顺序一致，那么就不用排序
15.3.1. 全字段排序
# 全字段排序
mysql> select city,name,age from t where city='杭州' order by name limit 1000;
1. 初始化sort_buffer，放入name、city、age这三个字段
2. 从索引city找到第一个满足city='杭州’条件的主键id
3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中
4. 从索引city取下一个记录的主键id
5. 重复步骤3、4直到city的值不满足查询条件为止
6. 对sort_buffer中的数据按照字段name做快速排序；此动作可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size
7. 按照排序结果取前1000行返回给客户端
# number_of_tmp_files排序过程中使用的临时文件数
# examined_rows，参与排序的行数
# sort_mode的packed_additional_fields表示排序过程对字符串做了“紧凑”处理。即使name字段的定义是varchar(16)，在排序过程中还是要按照实际长度来分配空间的。
# filesort_priority_queue_optimization这个部分的chosen=true，就表示使用了优先队列排序算法
# select @b-@a 表示整个执行过程只扫描了4000行。
# 为了避免对结论造成干扰，把internal_tmp_disk_storage_engine设置成MyISAM。否则，select @b-@a的结果会显示为4001。因为查询OPTIMIZER_TRACE表需要用到临时表，而internal_tmp_disk_storage_engine的=InnoDB。如果使用的是InnoDB引擎的话，把数据从临时表取出来的时候，会让Innodb_rows_read的值加1
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;
15.3.2. rowid排序
mysql> SET max_length_for_sort_data = 16;
1. 初始化sort_buffer，确定放入两个字段，即name和id
2. 从索引city找到第一个满足city='杭州’条件的主键id
3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到不满足city='杭州’条件为止
6. 对sort_buffer中的数据按照字段name进行排序
7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端，多了回表操作
# examined_rows表示用于排序的数据还是4000行
# select @b-@a变成5000。因为除了排序过程外，还要根据id去原表取值。因此会多读1000行
# sort_mode变成了<sort_key, rowid>，表示参与排序的只有name和id这两个字段。
# number_of_tmp_files变成10了，是因为这时候参与排序的行数虽然仍然是4000行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了
15.4. join buffer
- join buffer用于存放join语句的驱动表数据
- 如果驱动表的数据大于join_buffer_size，则MySQL会分段放，结果集返回后清空joinbuffer后再重复join，
- 假设驱动表的行数是N,驱动表的行数是M，需要分K段才能完成算法流程，那么扫描行数是N+kxM，注意K不是常数，N越大K就会越大，因此把K表示为λN，显然λ的取值范围是(0,1)。此时扫描行数是 N+λNM； 内存判断 NM次，N越小，扫描行数越小，因此应该让小表当驱动表
- where条件过滤后参与join的各个字段的总数据量小的那个表作为驱动表
15.4.1. Nested-Loop join（循环嵌套）算法
15.4.1.1. Index Nested-Loop Join(NLJ)
- NLJ:先遍历驱动表，然后根据从驱动表中取出的每行数据中的值，去被驱动表中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，称之为Index Nested-Loop Join
- NLJ的复杂度是N+N2log M，其中驱动表的行数是N,驱动表的行数是M，N对扫描行数的影响更大，因此应该让小表来做驱动表。
15.4.1.2. Batched Key Acess(BKA)算法
- Multi-Range Read优化(MRR)。主要目的是尽量使用顺序读盘。思路是将对主键id的值进行递增排序，然后批量回表
- read_rnd_buffer，存放Multi-Range Read优化数据的内存
- read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的，如果放满了就会先返回数据，然后继续循环重新取
- 在NLJ的基础上使用MRR优化
- 启用BKA优化算法
mysql> set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
15.4.1.3. Block Nested-Loop Join(BNL)
把驱动表的数据读入线程内存join_buffer中，扫描被驱动表，把被驱动表中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
BNL总的扫描行数是M+N；内存中的判断次数是M*N。其中小表的行数是N，大表的行数是M，因此什么表都可以当驱动表
BNL被驱动表是大的冷数据表的性能问题
- 可能会多次扫描被驱动表，占用磁盘IO资源；
- 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
- 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。
- 当冷表的数据量很小，小于整个Buffer Pool的3/8，能够完全放入old区域时。此时BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1秒，就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部
- 当冷表的数据量很大，那么业务正常访问的数据页，没有机会进入young区域。由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。但是，由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰
BNL算法优化
直接在被驱动表上建索引，把BNL算法转成BKA算法
使用临时表(不适合被驱动表上加索引，低频sql，可以创建使用memory创建被驱动表的临时表，并加上索引，触发BKA算法)
hash join：可以分别取数据将关联字段转成hash一一匹配判断
15.5. redo log buffer
- redo log buffer就是一块存redo日志的内存。执行语句后数据的内存被修改了，redo log buffer也写入了日志，但是，真正把日志写到redo log文件（文件名是 ib_logfile+数字），是在执行commit语句的时候。事务执行过程中不会“主动去刷盘”，以减少不必要的IO消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。
16. SQL优化
16.1. SQL优化步骤
16.1.1. 慢查询日志分析
- pt-query-digest工具分析
- 安装wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y percona-toolkit-2.2.16-1.noarch.rpm
- 检查是否安装完成：pt-query-digest --help
- 工具使用简介：wget http://percona.com/get/pt-summary
- 查看服务器信息 pt-summary
- 查看磁盘开销使用信息 pt-diskstats
- 查看mysql数据库信息 pt-mysql-summary --user=root --password=123456
- 分析慢查询日志 pt-query-digest /data/mysql/data/db-3-12-slow.log
- 查找mysql的从库和同步状态pt-slave-find --host=localhost --user=root --password=123456
- 查看mysql的死锁信息pt-deadlock-logger --user=root --password=123456 localhost
- 从慢查询日志中分析索引使用情况pt-index-usage -uroot -padmin slow_20131009.log
- 查找数据库表中重复的索引pt-duplicate-key-checker --host=localhost --user=root --password=123456
- 查看mysql表和文件的当前活动IO开销pt-ioprofile
- 查看不同mysql配置文件的差异pt-config-diff /etc/my.cnf /etc/my_master.cnf
- 查找数据库里大于2G的表：pt-find --user=root --password=123456 --tablesize +2G
- 查找10天前创建，MyISAM引擎的表：pt-find --user=root --password=123456 --ctime +10 --engine MyISAM
- 查看表和索引大小并排序 pt-find --user=root --password=123456 --printf "%T\t%D.%N\n" | sort -rn
- 显示查询时间大于60秒的查询pt-kill --user=root --password=123456 --busy-time 60 --print
- kill掉大于60秒的查询pt-kill --user=root --password=123456 --busy-time 60 --kill
- 查看mysql授权pt-show-grants --user=root --password=123456或者pt-show-grants --user=root --password=123456 --separate –revoke
- 验证数据库复制的完整性pt-table-checksum --user=root --password=123456
- 输出到文件pt-query-digest slow-log > slow_log.report
- 输出到数据库pt-query-digest slow-log -review h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review --create-reviewtable --review-history t=hostname_slow
- 如何通过慢查日志发现有问题的SQL
- 查询次数多且每次查询占用时间长的sql pt-query-digest分析的前几个查询；该工具可以很清楚的看出每个SQL执行的次数及百分比等信息，执行的次数多，占比比较大的SQL
- IO大的sql pt-query-digest分析中的Rows examine项。扫描的行数越多，IO越大。
- 未命中的索引的SQL 注意pt-query-digest分析中的Rows examine 和Rows Send的对比。说明该SQL的索引命中率不高，
15.4.2. 慢SQL预防
- 上线前，在测试环境，把慢查询日志（slowlog）打开，并且把long_query_time设置成0，确保每个语句都会被记录入慢查询日志；
- 在测试表里插入模拟线上的数据，做一遍回归测试；
- 观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期一致。
- 如果新增的SQL语句不多，手动跑一下就可以。而如果是新项目的话，或者是修改了原有项目的表结构设计，全量回归测试都是必要的。可以使用开源工具pt-query-digest(https://www.percona.com/doc/perconatoolkit/3.0/pt-query-digest.html)。检查所有的SQL语句的返回结果。
16.2. 常用SQL的优化
16.2.1. 定期分析表、检查表、优化表
# 定期分析表：用于分析和存储表的关键字分布，使系统得到准确的统计信息，使SQL能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于 MyISAM, BDB 和 InnoDB 表有效。对于 MyISAM 表，与使用 myisamchk -a 相当
mysql> ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
# 定期检查表：检查表或视图是否有错误，比如在视图定义中被引用的表已不存在，对MyISAM 和InnoDB表有作用。对于 MyISAM 表，关键字统计数据被更新
mysql> CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
# 定期优化表：如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。将表中的空间碎片进行合并，消除由于删除或者更新造成的空间浪费，但只对 MyISAM、BDB 和 InnoDB 表有效
mysql> OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
# ANALYZE、CHECK、OPTIMIZE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作
16.2.2. 大批量插入数据
# 对于MyISAM表，DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置
mysql> ALTER TABLE tbl_name DISABLE KEYS;
mysql> loading the data
mysql> ALTER TABLE tbl_name ENABLE KEYS;
# 对于Innodb表，因为InnoDB表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率
# 在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率
# 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率
16.2.3. 优化 INSERT 语句
- 如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，缩减客户端与数据库之间的连接、关闭等消耗mysql> insert into test values(1,2),(1,3),(1,4)…
- 如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
- 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
- MyISAM表如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度
- 当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。
16.2.4. 优化 GROUP BY 语句
- MySQL 对所有 GROUP BY col1，col2....的字段进行排序。这与在查询中指定ORDER BY col1，col2...类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。如果查询包括 GROUP BY 但想要避免排序结果的消耗，则可以指定 ORDER BY NULL禁止排序，
- select a.* from a left join (select b.aid,count(1) from b group by b.aid) c on a.id = c.aid; => select a.*,(select count(1) from b where b.aid = a.id) from a;
16.2.5. 优化 ORDER BY 语句
MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引，并且 Order by顺序和索引顺序相同，字段都是升序或者都是降序
可以考虑将多个order的结果集取出来，然后使用归并排序
- order by a,b limit n;则取order by a limit n结果集A,order by b limit n结果集B,然后使用归并排序取前100即可
- order by a limit n,m;则取order by a limit n+M结果集A，order by b n+M结果集B,然后使用规定排序取前n~(n+m)个即可，然后通过id回表查询
16.2.6. 优化LIMIT语句
- limit常用于分页处理,时常会伴随order by 从句使用, 因此大多时候会使用Filesorts,这样会造成大量的IO问题
- 使用有索引的列或者主键进行Order By操作
- 记录上次返回的主键,在下次查询的时候使用id过滤(如果主键不是连续的,是字符串类型,可以创建一个列记录)
16.2.7. 优化嵌套查询
- 子查询可以被更有效率的连接（JOIN）替代。因为JOIN不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作
select * from sales2 where company_id not in ( select id from company2 )
改成
explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is null
16.2.8. 对于含有 OR 的查询子句
- 如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。
16.2.9. 使用FORCE INDEX指定索引
- select * from sales2 force index (ind_sales2_id) where id > 0
16.2.10. 删除大量数据
- 删除一个表里面的前10000行数据，
- 在一个连接中循环执行20次 delete from T limit 500;(相对较好的)
- 在20个连接中同时执行delete from T limit 500。（会人为造成锁冲突。如果可以加上特定条件，将这10000行天然分开，实际上在操作的时候我也建议你尽量拿到ID再删除）
16.2.11. 优化count()查询
- 使用count(*)，不要使用count(列名)，因为要取出列的数据
- MyISAM中没有任何where条件的count(* )查询速度非常快
- 有where条件时，MyISAM的count统计不一定比其他表引擎快
- 增加汇总表或者使用缓存
17. 优化数据库对象
17.1. 优化表的数据类型
- 表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样浪费磁盘存储空间,同时在应用程序操作时也浪费物理内存。可以使用函数 PROCEDURE ANALYSE()对当前应用的表进行分析，对数据表中列的数据类型提出优化建议
# 输出的每一列信息都会对数据表中的列的数据类型提出优化建议。
mysql> SELECT * FROM tbl_name PROCEDURE ANALYSE();
# 不要为那些包含的值多于16个或者256字节的ENUM类型提出建议
mysql> SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);
17.2. 通过拆分提高表的访问效率
- 垂直拆分：把常用列放到一个表，然后非常用列放到另一个表中。可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少 I/O 次数。其缺点是需要管理冗余列，查询所有数据需要联合（JOIN）操作
- 水平拆分：即根据一列或多列数据的值把数据行放到两个独立的表中。通常在以下几种情况下使用
- 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
- 表中的数据本来就有独立性，例如，表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用
- 需要把数据存放到多个介质上。近3个月的账单数据存在一个表中，3个月前的历史账单存放在另外一个表中，超过1年的历史账单可以存储到单独的存储介质上
- 水平拆分会给应用增加复杂度，通常查询时需要多个表名，需要UNION操作。这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加2至3倍数据量，查询时也就增加读一个索引层的磁盘次数，所以水平拆分要考虑数据量的增长速度
17.2.1. 逆规范化
- 规范化越高，那么产生的关系就越多，关系过多的直接结果就是导致表之间的连接操作越频繁，而表之间的连接操作是性能较低的操作，直接影响到查询的速度，所以对于查询较多的应用就需要根据实际情况运用逆规范化对数据进行设计，通过逆规范化来提高查询的性能。
- 反规范的好处是降低连接操作的需求、降低外键和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。
- 增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作
- 增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。其作用是在查询时减少连接操作，避免使用聚合函数
- 重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能
- 分割表：垂直拆分和水平拆分
- 逆规范技术需要维护数据的完整性。常用的方法是批处理维护、应用逻辑和触发器。
- 批处理维护：指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，只能在对实时性要求不高的情况下使用。
- 应用逻辑：要求必须在同一事务中对所有涉及的表进行增、删、改操作。风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，不易维护
- 触发器（推荐）对数据的任何修改立即触发对复制列或派生列的相应修改。实时，且相应处理逻辑只在一个地方出现，易维护
17.2.2. 使用中间表提高大数据表统计查询速度
- 中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响
- 中间表上可以灵活的添加索引或增加临时用的新字段,从而达到提高统计查询效率和辅助统计查询作用
18. 优化 MySQL Server
- 根据服务器稳定运行了一段时间后的”状态”进行优化。
- 打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。
18.1. Linux系统优化
- 硬件配置优化：选择适合数据库服务的cpu，更快的IO，更高的内存；cpu并不是越多越好，某些数据库版本有最大的限制，ＩＯ操作并不是减少阻塞。
- 修改/etc/sysctl.conf(网络)
- net.ipv4.tcp_max_syn_backlog = 65535//增加tcp支持的队列数
- net.ipv4.tcp_max_tw_buckets = 8000 //通过netstat查看TCP连接状态，为了加快timewait状态的连接回收，就需要调整下面的四个参数
- net.ipv4.tcp_tw_reuse = 1
- net.ipv4.tcp_tw_recycle = 1
- net.ipv4.tcp_fin_timeout = 10
- 修改/etc/security/limits.conf(打开文件数的限制ulimit –n查看)
- *Soft nofile 65535
- *Hard nofile 65535
- 最好在MySQL服务器上关闭iptables，selinux等防火墙软件。
- mysql打开的最大文件数 max_open_files = table_cache * 2 + max_connections，受两个参数的影响：系统打开的最大文件数（ulimit -n）和 open_files_limit 。
18.2. 查看 影响MySQL Server 性能的重要参数
# 查看服务器静态参数值(如缓冲区大小、字符集、数据文件名称等)数据库启动后不会动态更改
mysql> show variables [ like 参数];
# 查看服务器动态参数值(锁等待、当前连接数等。)数据库运行期间的动态变化
mysql> show status;
# 查看帮助
mysql> mysqld --verbose --help|grep character-set-server
18.2.1. MyISAM-key_buffer_size
# 用来设置MYISAM索引块（Index Blocks）缓存的大小，它被所有线程共享
# 建立/重建一个索引缓存,global 表示对每一个新的连接，此参数都将生效
mysql> set global hot_cache2.key_buffer_size=128*1024;
# 不能大于可用内存30%,因为操作系统也需要内存来缓存行,即使不使用MyISAM表，也应该将其设置为8-64M，因为它也将用于内部临时磁盘表。所有线程共享，对于内存在4GB左右的服务器该参数可设置为256M或384M。该参数值设置的过大反而会是服务器整体效率降低！
# 调优方法1 show global status like 'key_read%'; => 索引未命中缓存的概率key_cache_miss_rate = Key_reads(内存中没有找到直接从硬盘读取索引) / Key_read_requests * 100% < 0.1%
# 调优方法2 show global status like 'key_blocks_u%'; => Key_blocks_used(曾经用到的最大的blocks数) / (Key_blocks_unused(未使用的) + Key_blocks_used) * 100% ≈ 80%
# 调优方法3 show global status like 'created_tmp%'; =>Created_tmp_disk_tables(磁盘临时表) / Created_tmp_tables (内存临时表)* 100% <= 25%
# show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 只有max_heap_table_size以下的临时表才能全部放内存，超过的就会用到硬盘临时表。
key_buffer_size=8M
# 把相关表的索引放到指定的索引缓存中
mysql> cache index sales,sales2 in hot_cache2;
# cache index 命令在一个表和 key_buffer 之间建立一种联系，但每次服务器重启时key_buffer 中的数据将清空。如果想要每次服务器重启时相应表的索引能自动放到 key_buffer中，可以在配置文件中设置init-file选项来指定包含cache index语句的文件路径
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql
然后在对应的文件mysqld_init.sql中写入 cache index 语句。
CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache;
CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache;
# 将索引预装到默认key_buffer
mysql> load index into cache sales;
# 删除索引缓存
mysql> set global hot_cache2.key_buffer_size=0;
18.2.2. MYISAM-table_cache
- 数据库用户打开表的缓存数量。每个连接进来，都会至少打开一个表缓存。与max_connections有关，例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200×N，N是可执行的查询的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符
- 可以通过检查mysqld的状态变量open_tables和opened_tables确定这个参数是否过小，这两个参数的区别是前者表示当前打开的表缓存数，如果执行 FLUSH TABLES 情况表缓存操作，则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小；后者表示曾经打开的表缓存数，会一直进行累加，如果执行 FLUSH TABLES 操作，值不会减少。show global status like 'open_tables';
18.2.3. innodb_buffer_pool_size
InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。MyISAM 的 key_buffer_size 只缓存索引键，而innodb_buffer_pool_size却是同时为数据块和索引块做缓存，这个值设得越高，访问表中数据需要的磁盘 I/O 就越少。在一个专用的数据库服务器上，可以设置这个参数达机器物理内存大小的 80%。但不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。
适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。为Innodb加速优化首要参数。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据量不大，并且不会暴增，那么可分配是你的数据大小＋１０％左右做为这个参数的值。例如：数据大小为50M,那么给这个值分配64M
命中率=innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_read_ahead + innodb_buffer_pool_reads)
18.2.4. innodb_flush_log_at_trx_commit设置
- 控制缓冲区中的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机。
- 0，日志缓冲每秒一次地被写到日志文件，并且对日志文件做向磁盘刷新的操作，但是在一个事务提交不做任何操作。数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失1秒钟的事务，最不安全的，效率最高（执行是由mysql的master thread线程来执行的。主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1
- 1，在每个事务提交时，日志缓冲被写到日志文件，并且对日志文件做向磁盘刷新的操作,确保了事务ACID（默认）
- 2，在每个事务提交时，日志缓冲被写到日志文件，但不对日志文件做向磁盘刷新的操作，对日志文件每秒向磁盘做一次刷新操作。只要操作系统没有崩溃，那么并没有丢失数据，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。比设置成0更安全一些
- innodb_flush_log_at_trx_commit 参数的默认值是 1，也是最安全的设置，即每个事务提交的时候都会从 log buffer 写到日志文件，而且会实际刷新磁盘，但是这样性能有一定的损失。如果可以容忍在数据库崩溃的时候损失一部分数据，那么设置成0或者2都会有所改善。设置成 0，则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失 1 秒钟的事务，这种方式是最不安全的，也是效率最高的。设置成 2 的时候，因为只是没有刷新到磁盘，但是已经写入日志文件，所以只要操作系统没有崩溃，那么并没有丢失数据，比设置成 0 更安全一些。
18.2.5. innodb_additional_mem_pool_size设置
- InnoDB存储引擎用来存储数据库结构和其他内部数据结构的内存池的大小。应用程序里的表越多，则需要在这里分配越多的内存。如果 InnoDB 用光了这个池内的内存，则 InnoDB 开始从操作系统分配内存，并且往 MySQL 错误日志写警告信息。没有必要给这个缓冲池分配非常大的空间，在应用相对稳定的情况下，这个缓冲池的大小也相对稳定。2G内存，推荐20M
18.2.6. innodb_lock_wait_timeout
- MySQL可以自动地监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用于在出现类似情况的时候等待指定的时间后回滚。默认50秒，可根据应用的需要调整
18.2.7. innodb_support_xa
- 设置是否支持分布式事务，默认ON或者1，支持分布式事务。不需要使用分布式事务则可以关闭，减少磁盘刷新的次数并获得更好的InnoDB性能
18.2.8. innodb_log_buffer_size
日志缓存的大小。默认的设置在中等强度写入负载以及较短事务的情况下，一般都可以满足服务器的性能要求。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存，因为它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常设置为 8～16MB 就足够了。越小的系统它的值越小。
调优：控制在2-8M.内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。一般最大指定为3M比较合适。show global status like 'Innodb_os_log_written'如果这个值增长过快，可以适当的增加innodb_log_buffer_size 另外如果你需要处理大理的text，或是blog字段，可以考虑增加这个参数的值。默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存.它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。
18.2.9. innodb_log_file_size
一个日志组（log group）中每个日志文件的大小。此参数在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但当系统灾难时恢复时间会加大
调优方法：几个日志成员大小加起来差不多和你的innodb_buffer_pool_size相等。在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系
18.2.10. 其他参数
# MySQL最大并发连接数。过多会占用太多内存(MySQL会为每个连接提供连接缓冲区)
# 服务器响应的最大连接数show global status like 'Max_used_connections';
# 最大连接数show variables like 'max_connections';
# 调优方法 Max_used_connections / max_connections* 100% ≈ 85%
max_connections=151
# 线程独享的配置，与max_connections的设置有关。200个并行运行的连接，应该让表的缓存至少有200×N ,N是网站程序一次查询所用到的表的最大值。
read_buffer_size = 2M # 每个进行一个顺序扫描的线程为其扫描的每张表分配该大小的一个缓冲区
read_rnd_buffer_size = 8M # 每个进行一个随机扫描的线程为其扫描的每张表分配该大小的一个缓冲区(ORDER BY)
sort_buffer_size = 8M # 每个需要进行排序的线程分配该大小的一个缓冲区。加速ORDER BY/GROUP BY操作 4GB左右的服务器推荐设置为6-8M show global status like 'sort%';Sort_merge_passes很大需要调优
join_buffer_size = 8M # 每个需要联合查询的线程分配该大小的一个缓冲区。(JOIN ON)
table_open_cache = 128 # 每个需要打开表的线程分配改大小的一个缓冲区。每个线程会独自持有一个数据文件的文件描述符，而索引文件的文件描述符是公用的。当table cache不够用的时候，MySQL会采用LRU算法踢掉最长时间没有使用的表。如果table_cache设置过小，MySQL就会反复打开、关闭 frm文件，造成一定的性能损失。如果table_cache设置过大，MySQL将会消耗很多CPU去做 table cache的算法运算。而InnoDB的元数据管理是放在共享表空间里面做的，获取表的结构不需要去反复解析frm文件，所以对于InnoDB的影响也是很小的。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
# 如果发现 open_tables 接近 table_cache 的时候，如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加，就说明缓存命中率不够。并且多次执行FLUSH TABLES(通过shell > mysqladmin -uroot -ppassword variables status )，那就说明可能 table_cache 设置的偏小，经常需要将缓存的表清出，将新的表放入缓存，这时可以考虑增加这个参数的大小来改善访问的效率。如果 Open_tables 比 table_cache 设置小很多，就说明table_cache 设的太大了。
# 调优方法1 show global status like 'open%tables%'; => Open_tables(Open_tables表示打开表的数量) / Opened_tables(表示打开过的表数量) * 100% >= 85%
# 调优方法2 show variables like 'table_open_cache'; => Open_tables / table_cache * 100% <= 95%
# 1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M
# back_log MySQL能暂存的连接数量。在大量并发连接或超过max_connections生效。不能大于操作系统对到来的TCP/IP连接的侦听队列的大小net.ipv4.tcp_max_syn_backlog 。
back_log = 600
# 一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户(即cmd)。默认28800秒（8小时）
interactive_timeout = 28800
# 一个非交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户(jdbc等))。默认28800秒（8小时）4GB服务器可以设置为5-10
# 指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型
# 客户端如果太长时间没动静，连接器就会自动将它断开。由参数wait_timeout控制的，默认值是8小时。
wait_timeout = 28800
-------------------------------------------------------------------------------------------------------------------------------------------------------------
# 临时表的大小，如果调高该值，同时将增加heap表的大小，可达到提高联接查询/GROUP BY速度的效果，建议尽量优化查询确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表
# show global status like 'created_tmp%';每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，
# 调优方法：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%.默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞
# 每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。
tmp_table_size = 16M
# 可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。
# 如果有很多新的线程，增加这个值可以改善系统性能.调优：1G —> 8、2G —> 16、3G —> 32、大于3G —> 64
thread_cache_size = 8
-------------------------------------------------------------------------------------------------------
# MySQL打开的文件描述符限制，当open_files_limit没有被配置的时候，取max_connections*5和ulimit -n的最大值，当open_file_limit被配置的时候，取open_files_limit和max_connections*5最大值
# show global status like 'open_files';
# show variables like 'open_files_limit';
# 调优方法 Open_files / open_files_limit * 100% <= 75%
open_files_limit = 65535
# 接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。
# 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。
# 包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节。该值默认很小，以捕获大的(可能是错误的)数据包。
max_allowed_packet = 1048576
net_buffer_length = 16384
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1 #不区分大小写
skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启
# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间,推荐启用，
# 每个表的数据和索引都会存在各自的独立表空间中。可以实现单表在不同的数据库中移动。空间可以回收（除drop table操作处，表空不能自已回收）
# 缺点：单表增加过大，如超过100G
# 结论：共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files
# 调优：只有使用不使用。但DB还需要有一个公共的表空间。InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。查看是否开启：show variables like '%per_table%';
innodb_file_per_table = ON
# 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
# 调优：如果库里的表特别多的情况，请增加这个。这个值默认是300。这个值必须超过你配置的innodb_data_file_path个数。
innodb_open_files = 500
# 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
innodb_log_files_in_group = 3
# innodb主线程刷新缓存池中的数据，使脏数据比例小于90%
# 控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.
innodb_max_dirty_pages_pct = 90
# 批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时， 提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。
bulk_insert_buffer_size = 8M
# MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_sort_buffer_size = 8M
# 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出
myisam_max_sort_file_size = 10G
# InnoDB的并发线程上限。一旦并发线程数达到这个值，InnoDB在接收到新请求的时候，就会进入等待状态，直到有线程
退出
# 0表示不限制并发线程数量
# 在线程进入锁等待以后，并发线程的计数会减一
innodb_thread_concurrency = 0
19. 磁盘 I/O 优化
19.1. 使用磁盘阵列
- RAID 是 Redundant Array of Inexpensive Disks 的缩写，“廉价磁盘冗余阵列”，通常就叫做磁盘阵列。RAID 就是按照一定策略将数据分布到若干物理磁盘上，这样不仅增强了数据存储的可靠性，而且可以提高数据读写的整体性能，因为通过分布实现了数据的“并行”读写。
19.1.1. 常见 RAID 级别及其特性
- RAID 0/条带化（Stripe），按一定的条带大（Chunk Size)将数据依次分布到各个磁盘，没有数据冗余数据
- 并发读写速度快，无额外磁盘空间开销，投资省
- 数据无冗余保护，可靠性差
- RAID 1/磁盘镜像（Mirror），两个磁盘一组，所有数据都同时写入两个磁盘，读时从任一磁盘读都可以
- 数据有完全冗余保护，只要不出现两块镜像磁盘同时损坏，不会影响使用；可以提高并发读性能
- 容量一定的话，需要2倍的磁盘，投资比较大
- RAID 10是RAID 1和RAID 0的结合，先对磁盘做镜像，再条带话，兼具RAID 1 的可靠性和RAID 0的优良并发读写性能
- 可性高，并发读写性能优良
- 容量一定的话，需要2倍的磁盘，投资比较大
- RAID 4：对磁盘组条带化，需要额外增加一个磁盘，用来写各 Stripe 的校验纠错数据
- RAID 中的一个磁盘损坏，其数据可以通过校验纠错数据计算出来，具有一定容错保护能力；读数据速度快
- 每个Stripe上数据的修改都要写校验纠错块，写性能受影响；所有纠错数据都在同一磁盘上，风险大，也会形成一个性能瓶颈；在出现坏盘时，读性能会下降
- RAID 5:将每一个条带（Stripe）的校验纠错数据块也分布写到各个磁盘，而不是写到一个特定的磁盘
- 写性能和数据保护能力要比RAID4更强一点
- 写性能不及RAID 0、RAID 1和 RAID 10，容错能力也不及 RAID 1；在出现坏盘时，读性能会下降
19.1.2. 如何选择 RAID 级别
- 根据数据读写的特点、可靠性要求，以及投资预算等来选择合适的RAID级别
- 数据读写都很频繁，可靠性要求也很高，最好选择 RAID 10；
- 数据读很频繁，写相对较少，对可靠性有一定要求，可以选择 RAID 5；
- 数据读写都很频繁，但可靠性要求不高，可以选择 RAID 0。
19.1.3. 虚拟文件卷或软RAID
- 一些操作系统中提供的软件包，也模拟实现了一些 RAID 的特性，虽然性能上不如硬RAID，但相比单个磁盘，性能和可靠性都有所改善。比如Linux下的逻辑卷（Logical Volume）系统 lvm2，支持条带化（Stripe）；Linux 下的 MD（Multiple Device）驱动，支持 RAID 0、RAID 1、RAID 4、RAID 5、RAID 6等。在不具备硬件条件的情况下，可以考虑使用上述虚拟文件卷或软 RAID技术
19.2. 使用 Symbolic Links 分布 I/O
- 可以利用操作系统的符号连接（Symbolic Links）将不同的数据库或表、索引指向不同的物理磁盘，从而达到分布磁盘 I/O目的。发挥多磁盘并行读写的优势
1.将一个数据库指向其他物理磁盘。先在目标磁盘上创建目录，然后再创建从 MySQL 数据目录到目标目录的符号连接：
shell> mkdir /otherdisk/databases/test
shell> ln -s /otherdisk/databases/test /path/to/datadir
2. 将MyISAM（其他存储引擎的表不支持）表的数据文件或索引文件指向其他物理磁盘。
# 对于新建的表，可以通过在CREATE TABLE语句中增加DATA DIRECTORY和INDEX DIRECTORY选项来完成，
mysql> Create table test(id int primary key, Name varchar(20)) Type = myisam DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk3/index'
# 对于已有的表，可以先将其数据文件（.MYD）或索引文件（.MYI）转移到目标磁盘，然后再建立符号连接即可。需要说明的是表定义文件（.frm）必须位于MySQL数据文件目录下，不能用符号连接。
# 使用Symbolic Links存在一定的安全风险，如果不使用Symbolic Links，应通过启动参skip-symbolic-links禁用这一功能
19.3. 禁止操作系统更新文件的 atime 属性
- atime 是 Linux/UNIX 系统下的一个文件属性，每当读取文件时，操作系统都会将读操作发生的时间回写到磁盘上。对于读写频繁的数据库文件来会增加磁盘系统的负担，影响 I/O 的性能！因此，可以通过设置文件系统的 mount属性，阻止操作系统写 atime 信息
# 修改文件系统配置文件/etc/fstab，指定 noatime 选项：
LABEL=/home /home ext3 noatime 1 2
# 然后重新 mount 文件系统：
mount -oremount /home
19.4. 用裸设备（Raw Device）存放 InnoDB 的共享表空间
- MyISAM存储引擎有自己的索引缓存机制，但数据文件的读写完全依赖于操作系统，操作系统磁盘I/O缓存对MyISAM表的存取很重要
- InnoDB存储引擎与采用数据缓存机制来 Cache 索引和数据，操作系统的磁盘I/O缓存对其性能不仅没有帮助，甚至还有反作用。因此，在InnoDB缓存充足的情况下，可以考虑使用Raw Device来存放 InnoDB共享表空间
# 修改MySQL配置文件，在innodb_data_file_path参数中增加裸设备文件名并指定newraw属性：
......
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
......
# 启动MySQL，使其完成分区初始化工作，然后关闭MySQL。此时还不能创建或修改InnoDB表。
# 将innodb_data_file_path中的newraw改成raw：
......
class=programlisting[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
......
# 重新启动即可开始使用
20. 应用优化
20.1. 使用连接池
20.2. 减少对 MySQL 的访问
20.3. 使用缓存
20.4. 负载均衡
20.4.1. 主从复制-读写分离
- 具体的实现是一个主服务器承担更新操作，而多台从服务器承担查询操作，主从之间通过复制实现数据的同步。多台从服务器一方面用来确保可用性，一方面可以创建不同的索引以满足不同查询的需要
20.4.2. 采用分布式数据库架构MySQL CLUSTER
20.5. 其他优化措施
- 对于没有删除行操作的 MyISAM 表，插入操作和查询操作可以并行进行，因为没有删除操作的表查询期间不会阻塞插入操作。对于确实需要执行删除操作的表，尽量在空闲时间进行批量删除操作，并且在进行删除操作之后应该进行 OPTIMIZE 操作来消除由于删除操作带来的空洞，以避免将来的更新操作阻塞其他操作
- 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这会减少 MySQL 需要做的语法分析从而提高插入速度
- 表的字段尽量不使用自增长变量，在高并发情况下该字段的自增可能对效率有比较大的影响，推荐通过应用来实现字段的自增长
21. 备份与恢复
21.1. 备份分类，备份数据的类型和工具
- 完全备份：优点保存最新备份，恢复时间短，缺点是备份时间长，但负载压力大；
- 增量备份：优点只需要备份每天的增量日志，备份时间短，负载压力小，缺点是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间长
- 事务日志备份：事务日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。为了使数据库具有鲁棒性，
- 文件备份：数据库可以由硬盘上的许多文件构成。如果这个数据库非常大，并且一个晚上也不能将它备份完，那么可以使用文件备份每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储，所以这种备份不是很常用。
- 备份数据的类型：热备份、温备份、冷备份
- 备份工具：cp、mysqldump、xtrabackup、lvm2、快照
21.2. 备份/恢复策略
表的存储引擎是事务型还是非事务性?数据一致性?
全备份还是增量备份
可以考虑采取复制的方法来做异地备份，但复制不能代替备份，它对数据库的误操作也无能为力
要定期备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行
确保BINLOG选项打开，MySQL才可以做完整恢复，或基于时间点或位置的恢复
100G内的库使用 mysqldump ，备份时间选在业务低峰期，每天全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。
100G以上的库使用 xtrabackup ，一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。
推荐一周做一次完全备份，推荐每天做一次差异备份，推荐每小时甚至更频繁的备份事务日志
21.3. 逻辑备份与恢复
- MySQL 中的逻辑备份是将数据库中的数据备份为一个文本文件,备份的文件可以被查看和编辑
- 优点：最大好处是能够与正在运行的 MySQL 自动协同工作，在运行期间可以确保备份是当时的点，它会自动将对应操作的表锁定，不允许其他用户修改(只能访问)。可能会阻止修改操作。SQL 文件通用方便移植。
- 缺点：速度慢。如果是数据量很多的时候，就很耗时间。如果数据库服务器处在提供给用户服务状态，在这段长时间操作过程中，意味着要锁定表(一般是读锁定，只能读不能写入数据)，那么服务就会影响的。
# 为了保证数据备份的一致性，MyISAM备份需加上-l，表示在备份期间所有表将只读。--single-transaction表示InnoDB得到一个快照（Snapshot），使得备份的数据能够保证一致性。由于MVCC的支持，这个过程中数据是可以正常更新
# –add-locks设置为0，表示在输出的文件结果里不增加" LOCKTABLES t WRITE;" ；
# –no-create-info的意思是，不需要导出表结构
# –set-gtid-purged=off表示的是，不输出跟GTID相关的信息；
# –result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。
# -F 表示生成一个新的日志文件 -只导出表结构
# 备份指定的数据库，或者此数据库中某些表。
shell> mysqldump -root -p [options] db_name [tables] >bakfile
# 备份指定的一个或多个数据库。
shell> mysqldump -root -p [options] ---database DB1 [DB2 DB3...] > bakfile
# 备份所有数据库。
shell> mysqldump -root -p [options] --all--database >bakfile
# 导入最新全量备份
shell> mysql –uroot –p dbname < bakfile
# 完全恢复
# 恢复自 mysqldump 备份以来的 BINLOG
shell> mysqlbinlog localhost-bin.000015 | mysql -u root –p dbname
# 基于时间点恢复
# 如果上午 10 点发生了误操作，可以用以下语句用备份和 BINLOG 将数据恢复到故障前：
shell> mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u root –p
# 跳过故障时的时间点，继续执行后面的 BINLOG，完成恢复。
shell> mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456| mysql -u root -p
# 基于位置恢复(推荐)
# 找到出错语句前后的位置号
shell> mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
# 恢复到停止位置为止的所有事务
shell> mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -p
# 恢复从给定的起始位置直到二进制日志结束的所有事务
shell> mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -p
21.4. 物理备份和恢复
- 物理备份的原理都是基于文件的 cp，备份和恢复的速度更快，只适用于 MyISAM 类型的表。
- 缺点：不能去操作正在运行的 MySQL 服务器(在拷贝的过程中有用户通过应用程序访问更新数据，这样就无法备份当时的数据)，可能无法移植到其他机器上去。
21.4.1. 冷备份
- 备份：停掉MySQL服务，在操作系统级别备份 MySQL 的数据文件和日志文件到备份目录。
- 恢复：停掉MySQL服务，在操作系统级别恢复 MySQL 的数据文件；重启服务，使用mysqlbinlog工具恢复自备份以来的所有BINLOG
21.4.2. 热备份
- MyISAM：将要备份的表加读锁，然后再 cp 数据文件到备份目录。
- 方法1shell> mysqlhotcopy db_name [/path/to/new_directory]
- 方法2mysql> flush tables for read 并cp 数据文件到备份目录
- InnoDB 存储引擎:ibbackup
21.5. 双机热备份
- 优点：适合数据量大的时候。大公司对于 MySQL 数据备份，都是采用热机备份。搭建多台数据库服务器，进行主从复制。
21.6. 数据库不能停机，请问如何备份? 如何进行全备份和增量备份?
- 使用逻辑备份和双机热备份。
- 完全备份：完整备份一般一段时间进行一次，且在网站访问量最小的时候，这样常借助批处理文件定时备份。主要是写一个批处理文件在里面写上处理程序的绝对路径然后把要处理的东西写在后面，即完全备份数据库。
- 增量备份：对 ddl 和 dml 语句进行二进制备份。增量备份需要在 my.ini 文件中配置备份路径即可，重启 MySQL 服务器，增量备份就启动了。
21.7. 备份恢复时间是多长？
- 物理备份恢复快，逻辑备份恢复慢。与硬盘的速率有关系，
- 20G 的 2 分钟（mysqldump）
- 80G 的 30分钟（mysqldump)
- 111G 的 30分钟（mysqldump)
- 288G 的 3 小时（xtrabackup)
- 3T 的 4 小时（xtrabackup)
- 逻辑导入时间一般是备份时间的 5 倍以上。
21.8. 备份恢复失败如何处理？
- 首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。
21.9. mysqldump 和 xtrabackup 实现原理？
- mysqldump 是最简单的逻辑备份方式。
- 在备份 MyISAM 表的时候，如果要得到一致的数据，就需要锁表，简单而粗暴。
- 在备份 InnoDB 表的时候，加上 –master-data=1 –single-transaction 选项，在事务开始时刻，记录下 binlog pos 点，然后利用 MVCC 来获取一致的数据，由于是一个长事务，在写入和更新量很大的数据库上，将产生非常多的 undo ，显著影响性能，所以要慎用。
- 优点：简单，可针对单表备份，在全量导出表结构的时候尤其有用。
- 缺点：简单粗暴，单线程，备份慢而且恢复慢，跨 IDC 有可能遇到时区问题
- trabackup:实际上是物理备份+逻辑备份的组合。
- 在备份 InnoDB 表的时候，它拷贝 ibd 文件，并一刻不停的监视 redo log 的变化，append 到自己的事务日志文件。在拷贝 ibd 文件过程中，ibd文件本身可能被写”花”，这都不是问题，因为在拷贝完成后的第一个 prepare 阶段，xtrabackup 采用类似于 Innodb 崩溃恢复的方法，把数据文件恢复到与日志文件一致的状态，并把未提交的事务回滚。
- 如果同时需要备份 MyISAM 表以及 InnoDB 表结构等文件，那么就需要用 flush tables with lock 来获得全局锁，开始拷贝这些不再变化的文件，同时获得 binlog 位置，拷贝结束后释放锁，也停止对 redo log 的监视。
21.10. 表的导入导出
- 表导出目的：
- 将表里的数据导出为某些符号分割的纯数据文本，而不是SQL语句。如导出EXCEL；
- 节省备份空间；
- 为了快速的加载数据，LOAD DATA 的加载速度比普通的SQL要快
- into outfile指定了文件的生成位置（/server_tmp/），这个
# 表的导入导出受参数secure_file_priv限制。
# 设置为empty，表示不限制文件生成的位置，这是不安全的设置；
# 设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
# 设置为NULL，就表示禁止在这个MySQL实例上执行select …into outfile 操作。
# 查看表导出位置
mysq> SHOW VARIABLES LIKE "secure_file_priv";
# 导出
# option
# FIELDS TERMINATED BY 'string' （字段分隔符，默认为制表符’\t’）；
# FIELDS [OPTIONALLY] ENCLOSED BY 'char'（字段引用符，如果加 OPTIONALLY则只用在 char、varchar 和 text 等字符型字段上。默认不使用引用符）；
# FIELDS ESCAPED BY 'char' （转义字符，默认为’\’）；
# LINES STARTING BY 'string' （每行前都加此字符串，默认''）；
# LINES TERMINATED BY 'string'（行结束符，默认为’\n’）；
# char表示单个字符，string表示字符串
# 当导出命令中包含字段引用符时，数据中含有转义字符本身和字段引用符的字符需要被转义
# 当导出命令中不包含字段引用符时，数据中含有转义字符本身和字段分隔符的字符需要被转义
# 导出文件如果在目标目录下有重名文件，将不会创建成功，源文件不能被自动覆盖
# 不加“local”，是读取服务端的文件，这个文件必须在secure_file_priv指定的目录或子目录下
# 加上“local”，读取的是客户端的文件，只要mysql客户端有访问这个文件的权限即可
# tab 可同时导出表结构定义文件
mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];
# option
# --fields-terminated-by=name字段分隔符
# --fields-enclosed-by=name字段引用符
# --fields-optionally-enclosed-by=name字段引用符，只用在 char、varchar 和 text 等字符型字段上
# --fields-escaped-by=name转义字符
# --lines-terminated-by=name记录结束符
# 实际调用的就是SELEC…INTO OUTFILE…提供的接口，并添加了一些新的功能而已。如多了一个表的创建脚本文件
shell> mysqldump –u username –T target_dir dbname tablename [option]
# 导入
# option
# FIELDS TERMINATED BY 'string'（字段分隔符，默认为制表符'\t'）
# FIELDS [OPTIONALLY] ENCLOSED BY 'char'（字段引用符，如果加 OPTIONALLY 选项则只用在 char、varchar 和 text 等字符型字段上。默认不使用引用符）
# FIELDS ESCAPED BY 'char'（转义字符，默认为'\'）
# LINES STARTING BY 'string'（每行前都加此字符串，默认''）
# LINES TERMINATED BY 'string'（行结束符，默认为'\n'）
# IGNORE number LINES（忽略输入文件中的前 n 行数据）
# (col_name_or_user_var,...) （按照列出的字段顺序和字段数量加载数据）
# SET col_name = expr,... 将列做一定的数值转换后再加载
# char 表示此符号只能是单个字符，string 表示可以是字符串
# 不加“local”，是读取服务端的文件，这个文件必须在secure_file_priv指定的目录或子目录下
# 加上“local”，是读取客户端的文件，客户端要有访问这个文件的权限。客户端会先把本地文件传给服务端，然后执行load data
mysql > LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option]
# option
# --fields-terminated-by=name（字段分隔符）；
# --fields-enclosed-by=name（字段引用符）；
# --fields-optionally-enclosed-by=name（字段引用符，只用在 char、varchar 和 text 等字符型字段上）；
# --fields-escaped-by=name（转义字符）；
# --lines-terminated-by=name（记录结束符）；
# -- ignore-lines=number（或略前几行）。
shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option]
# 如果导入和导出是跨平台操作的，那么要注意设置参数line-terminated-by，Windows上设置为line-terminated-by=’\r\n’ ， Linux上设置为line-terminated-by=’\n’。
# 开启/关闭外键检查，导入多表数据时关闭
mysql> SET FOREIGN_KEY_CHECKS = 1/0;
21.11. 表的复制
- 如果可以控制对源表的扫描行数和加锁范围很小的话，可以使用insert …select 语句即可实现。
- 为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。
- 使用mysqldump逻辑备份
- 使用表的导入导出
- 当binlog_format=statement时，load data命令解析出来为什么用的是load data local?
- 为了确保备库应用binlog正常。因为备库可能配置了secure_file_priv=null，所以如果不用local的话，可能会导入失败，造成主备同步延迟;
- 另一种应用场景是使用mysqlbinlog工具解析binlog文件，并应用到目标库的情况。你可以使用下面这条命令：mysqlbinlog
21.12. 误删数据
21.12.1. delete误删数据行
21.12.1.1. 恢复方法
- 前提binlog_format=row和binlog_row_image=FULL
- Flashback工具通过闪回把数据恢复回来。其原理是修改binlog的内容，拿回原库重放
- 恢复数据时找备份或者一个从库作为临时库执行这些操作，然后再将确认过的临时库的数据，恢复回主库
21.12.1.2. 预防删记录方法
- sql_safe_updates=on，此时在delete或者update中没有写where条件，或者where条件里面没有包含索引字段的话就会报错
- 代码上线前，必须经过SQL审计
21.12.1.3. 删除表所有数据的方法
- 删除小表全部数据 delete from table where id>0。速度慢，需要生成undo log、写redo、写binlog。能用Flashback恢复
- truncate table/drop table命令。不能用Flashback恢复。因为即使binlog_format=row，记录的binlog还是statement格式。只有一个truncate/drop语句，是恢复不出数据的
21.12.2. drop table或者truncate table误删数据表/drop database误删库
21.12.2.1. 恢复方法
前提：定期有全量备份和实时备份binlog
取全量备份恢复到临时库，然后取出binlog备份时间之后的日志，把日志处理误删数据的语句外，应用到临时库
- mysqlbinlog -dataBase指定误删所在的库
- 如果原实例没有使用GTID模式，只能在应用到包含误删时刻的binlog文件的时候，先用–stop-position参数执行到误操作之前的日志，然后再用–start-position从误操作之后的日志继续执行；
- 如果实例使用了GTID模式，假设误操作命令的GTID是gtid1，那么只需要执行set gtid_next=gtid1;begin;commit; 先把这个GTID加到临时实例的GTID集合，之后按顺序执行binlog的时候，就会自动跳过误操作的语句。
- 如果只是误删表，在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库。在start slave之前，先通过执行change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；这样做也可以用上并行复制技术，加速整个数据恢复过程。
- 如果临时库缺少binlog，则从备份系统下载需要的binlog，放到备库的日志目录下，修改日志目录下的master.index文件，在文件开头加入./master.xxxx；重启临时库，让临时库识别这两个日志文件。这个备库上就有了临时库需要的所有binlog了，建立主备关系，就可以正常同步了
有什么方法可以缩短恢复数据需要的时间呢？
- 可以考虑搭建延迟复制的备库(MySQL5.6后)。延迟复制的备库通过 CHANGE MASTER TOMASTER_DELAY=N指定这个备库持续保持跟主库有N秒的延迟。如果主库上有数据被误删了，并且在N秒内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。
21.12.2.2. 预防误删库/表的方法
- 账号分离。可以避免写错命令。只给业务开发DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，也可以通过开发管理系统得到支持。即使是DBA团队成员，日常规定只使用只读账号，必要才使用有更新权限的账号
- 制定操作规范。避免写错要删除的表名。比如：在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。改表名的时候，要求给表名加固定的后缀（比如加_to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且管理系统删除固定后缀的表。
21.12.3. rm命令误删整个MySQL实例
- 只要不是恶意地把整个集群删除，只是删掉了其中某一个节点的数据的话，HA系统就会选出一个新主库，保证整个集群的正常工作。只需在这个节点上把数据恢复回来，再接入整个集群。
- DBA和SA（系统管理员）都有自动化系统，允许一个批量下线机器的操作，会让你整个MySQL集群的所有节点都全军覆没。此时应尽量把备份跨机房，或者最好是跨城市保存。
22. MySQL权限管理
22.1. 权限系统的工作原理
用户名(user)+地址(host)才表示一个用户，因此ua@ip1和ua@ip2代表的是两个不同的用户
MySQL 权限系统通过下面两个阶段进行认证：
- 对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接
- 对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作
MySQL权限表在数据库启动的时候就载入内存。在权限存取过程中系统会用到默认的mysql数据库中user、host和db表
认证：从user表中的host、user和password这3个字段中判断连接的IP、用户名和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接
鉴权与授权：如果通过身份验证，则按照以下权限表顺序鉴权与授权：user(全局)->db(数据库)->tables_priv(表)->columns_priv(列)。权限范围依次递减，全局权限覆盖局部权限，权限范围大的为Y则无需检查权限范围小的
全局权限保存在mysql.db表中，内存保存在acl_user中.已经存在的连接的全局权限不受grant的影响
基于库的权限保存在mysql.db表中，内存保存在全局数组acl_dbs中，对已经存在的并且已经use对应数据库的连接数据库权限不受grant的影响
表权限定义存放在表mysql.tables_priv中，列权限定义存放在表mysql.columns_priv中。这两类权限，组合起来存放在内存的hash结构column_priv_hash中。每次grant的时候都会修改数据表并同步修改内存中的hash结构。因此会马上影响到已经存在的连接
flush privileges命令会以数据表中的数据为准，会将所有权限对应内存重新加载一遍。如果内存的权限数据和磁盘数据表相同的话，不需要执行flush privileges。当数据表中的权限数据跟内存中的权限数据不一致的时候（比如直接用DML语句操作系统权限表），flush privileges语句可以用来重建内存数据，达到一致状态。
22.2. 账号管理
# 创建一个用户’ua’@’%’，密码是pa
# 磁盘上，往mysql.user表里插入一行，且这行数据上所有表示权限的字段的值都是N；
# 内存里，往数组acl_users里插入一个acl_user对象，这个对象的access字段值为0。
mysql> create user 'ua'@'%' identified by 'pa';
# 创建账号&授权
# object_type = TABLE | FUNCTION | PROCEDURE
mysql> GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH GRANT OPTION]
# 授全局权限
# 磁盘上，将mysql.user表里，用户’z1’@’localhost'这一行的所有表示权限的字段的值都修改为‘Y’；
# 内存里，从数组acl_users中找到这个用户对应的对象，将access值（权限位）修改为二进制的“全1”。
mysql> grant all privileges on *.* to z1@localhost;
# 授数据库级别权限
# 磁盘上，往mysql.db表中插入了一行记录，所有权限位字段设置为“Y”；
# 内存里，增加一个对象到数组acl_dbs中，这个对象的权限位为“全1”。
mysql> grant all privileges on db1.* to 'ua'@'%' with grant option;
# 授予表权限
mysql> grant all privileges on db1.t1 to 'ua'@'%' with grant option;
# 授予列权限
mysql> GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;
# 查看权限
mysql> show grants for user@host;
mysql> select * from information_schema.SCHEMA_PRIVILEGES where grantee="'z1'@'localhost'"
# 修改权限
mysql> REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
# 回收所有权限
# 磁盘上，将mysql.user表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为“N”；
# 内存里，从数组acl_users中找到这个用户对应的对象，将access的值修改为0。
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
# 修改密码
mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
mysql> SET PASSWORD = PASSWORD('biscuit');
# 删除账号
mysql> DROP USER user [, user] ...
# mysql连接的权限是以最精确的匹配作为重复用户的权限
# mysql数据库的user表中host值为*或者空，表示所有外部IP都可以连接，但是不包括本地服务器localhost，因此必须单独为 localhost 赋予权限
# 刷新权限
flush privileges
23. MySQL 安全
# 任何用户都采取独立的账号登录，管理员通过mysql专有用户管理MySQL
# mysql用户目录下，数据目录属主设置为mysql，其他文件和目录属主都改为root。当使用mysql用户启动数据库时，可以防止任何具有FILE权限的用户能够用root 创建文件。而如果使用root用户启动数据库，则任何具有FILE权限的用户都可以读写 root 用户的文件，这样会给系统造成严重的安全隐患.
# 防止DNS欺骗，创建用户时，host尽量指定ip，如果指定域名，而对应的IP地址被恶意修改，则数据库就会被恶意的IP地址进行访问，导致安全隐患。
# 删除匿名和和废弃账号，某些版本MySQL会自动安装一个空账号，对test数据库的全部权限。比如可以创建一个大表，占用大量磁盘空间，这样将给系统造成安全隐患
mysql> drop user ''@'localhost';
# 只授予账号必须的权限
# 除 root 外，任何用户不应有 mysql 库 user 表的存取权限
# 不要把 FILE、PROCESS 或 SUPER 权限授予管理员以外的账号
# FILE权限：
- 将数据库的信息通过 SELECT …INTO OUTFILE…写到服务器上有写权限的目录（启动 MySQL 时的用户权限目录）下，作为文本格式存放
- 可以将有读权限的文本文件通过 LOAD DATA INFILE…命令写入数据库表，如果这些表中存放了很重要的信息，将对系统造成很大的安全隐患
# PROCESS权限能被用来执行“show processlist”命令，查看当前所有用户执行的查询的明文文本，包括设定或改变密码的查询。有可能会使得普通用户能够看到管理员执行的命令
# SUPER权限能执行kill命令，终止其他用户进程。
# LOAD DATA默认读的是服务器上的文件，但是加LOCAL后就可以随意将本地具有访问权限的文件加载到数据库。带来安全隐患，可以--local-infile=0 选项启动禁用所有 LOAD DATA LOCAL命令
# DROP TABLE 命令并不收回以前的相关访问授权，在删除表时，要同时取消其他用户在此表上的相应权限
# 在一个数据库上多次赋予权限，权限会自动合并；但是在多个数据库上多次赋予权限，每个数据库上都会认为是单独的一组权限，必须在此数据库上用REVOKE命令来单独进行权限收回
# 27. SQL 中的安全问题
## 27.1. SQL注入简介
- SQL 注入就是利用某些数据库的外部接口将用户数据插入到实际的数据库操作语言（SQL）当中，从而达到入侵数据库乃至操作系统的目的。它的产生主要是由于程序对用户输入的数据没有进行严格的过滤，导致非法数据库查询语句的执行。
## 27.2. SQL注入防范
- 对Java开发的应用,可以使用 PrepareStatement+Bind-variable来防止SQL注入，或者自己定义函数进行校验
# 避免从互联网访问MySQL数据库，确保特定主机才拥有访问特权，禁用或限制远程访问skip-networking
# 定期备份数据库
# 移除测试(test)数据库
# 移除和禁用.mysql_history文件
24. MySQL复制
- 复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到复制服务器（也叫从服务器）上，然后在从服务器上对这些日志重新执行（也叫重做），从而使得从服务器和主服务器的数据保持同步。
- MySQL 支持一台主服务器同时向多台从服务器进行复制，从服务器同时也可以作为其他服务器的主服务器，实现链状的复制
- MySQL 复制的优点：
- 如果主服务器出现问题，可以快速切换到从服务器提供服务
- 可以在从服务器上执行查询操作，降低主服务器的访问压力
- 可以在从服务器上执行备份，以避免备份期间影响主服务器的服务
- 由于 MySQL 实现的是异步的复制，所以主从服务器之间存在一定的差距，在从服务器上进行的查询操作需要考虑到这些数据的差异，一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询，实时性要求高的数据仍然需要从主数据库获得
24.1. 主从复制流程
- 主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的：
- 在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。
- 在备库B上执行start slave命令，备库会启动io_thread和sql_thread。io_thread负责与主库建立连接
- 主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。
- 备库B拿到binlog后，io_thread写到中转日志（relay log）。
- sql_thread读取中转日志，解析出日志里的命令，并执行。 后来由于多线程复制方案的引入，sql_thread演化成为了多个线程.SQL 线程只做 coordinator ，只负责把 relay log 中的 binlog 读出来然后交给 worker 线程， woker 线程负责具体 binlog event 的执行。
24.2. 复制配置
# MySQL 的复制至少需要两个相同版本的MySQL服务
# 在主服务器上，设置一个复制使用的账户，并授予 REPLICATION SLAVE 权限。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY '1234test';
# 修改主数据库服务器的配置文件 my.cnf，开启BINLOG，并设置 server-id 的值。并重启数据库服务
[mysqld]
log-bin = /home/mysql/log/mysql-bin.log
server-id = 1
# 在主服务器上，设置读锁定有效，为了确保没有数据库操作，以便获得一个一致性的快照：
mysql> flush tables with read lock;
# 然后得到主服务器上当前的二进制日志名和偏移量值。在从数据库启动以后，从这个点开始进行数据的恢复。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000039 | 102 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
# 主数据库备份，然后主数据库恢复写操作
mysql> unlock tables;
# 将主数据库的一致性备份恢复到从数据库上，修改从数据库的配置文件 my.cnf，增加 server-id参数(唯一)
[mysqld]
server-id = 2
# 在从服务器上，使用--skip-slave-start 选项启动从数据库，这样不会立即启动从数据库服务上的复制进程
shell> ./bin/mysqld_safe --skip-slave-start &
# 从数据库服务器指定复制使用的用户，主数据库服务器的IP、端口以及开始执行复制的日志文件和位置
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.100',
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='1234test',
-> MASTER_LOG_FILE='mysql-bin.000039',
-> MASTER_LOG_POS=102;
Query OK, 0 rows affected (0.10 sec)
# 在从服务器上，启动 slave 线程
mysql> start slave;
# slave上执行 show processlist查看进程：也可以测试复制服务的正确性，在主数据库上执行一个更新操作，观察是否在从数据库上同步
mysql> show processlist \G
# 主要复制启动选项
# log-slave-updates:配置从服务器上的更新操作是否写二进制日志，默认不打开。在这个从服务器同时也要作为其他服务器的主服务器时打开。需要和--logs-bin参数一起使用
# master-connect-retry：设置在和主服务器的连接丢失的时候，重试的时间间隔，默认60秒
# read-only：设置从服务器只能接受超级用户的更新操作。普通用户会报错；可以防止误操作，有时候一些运营类的查询语句会被放到备库上去查；防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致。判断节点的角色。把备库设置成只读了，还怎么跟主库保持同步更新呢？因为readonly设置对超级(super)权限用户是无效的，而用于同步更新的线程拥有超级权限
# replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table或replicate-wild-do-table指定从主数据库复制到从数据库的数据库或者表
24.3. 备库并行复制能力
- 日志在备库上的执行，如果是用单线程的话，就会导致备库应用日志不够快，造成主备延迟。要把线程的sql_thread拆成多个线程，coordinator就是原来的sql_thread, 只负责读取中转日志和分发事务。真正更新日志的，变成了worker线程。参数slave_parallel_workers决定work线程的个数
- coordinator分发邀请
- 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
- 同一个事务不能被拆开，必须放到同一个worker中。
24.4. 并行复制策略
24.4.1. MySQL 5.6按库并行复制
24.4.2. MySQL 5.7的并行复制策略
- 利用redo log组提交(group commit)特性：能够在同一组里提交的事务，一定不会修改同一行；主库上可以并行执行的事务，备库上也一定是可以并行执行的。同时处于prepare状态的事务，在备库执行时是可以并行的；处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的。
- 在实现上，一组里面一起提交的事务，有一个相同的commit_id，下一组就是commit_id+1，commit_id直接写到binlog里面；传到备库应用的时候，相同commit_id的事务分发到多个worker执行；这一组全部执行完成后，coordinator再去取下一批。
- 吞吐量就不够。在主库上，一组事务在commit的时候，下一组事务是同时处于“执行中”状态的。在备库上执行的时候，要等第一组事务完全执行完成后，第二组事务才能开始执行。
- 很容易被大事务拖后腿。假设组内有大事务，那么同一组的事务要等大事务完成，下一组才能开始执行。此时只有一个worker线程在工作，资源浪费。
- slave-parallel-type来控制并行复制策略
- DATABASE使用按库并行策略
- LOGICAL_CLOCK使用类似MariaDB的策略
- binlog_group_commit_sync_delay表示延迟多少微秒后才调用fsync;binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。这两个参数是用于故意拉长binlog从write到fsync的时间，以此减少binlog的写盘次数。在MySQL5.7的并行复制策略里，它们可以用来制造更多的“同时处于prepare阶段的事务”。这样就增加了备库复制的并行度。
24.4.3. MySQL 5.7.22的并行复制策略
binlog-transaction-dependency-tracking
- COMMIT_ORDER表示同时进入prepare和commit来判断是否可以并行的策略。基于组提交
- WRITESET表示的是对于事务涉及更新的每一行，计算出这一行的hash值，组成集合writeset。如果两个事务没有操作相同的行，也就是说它们的writeset没有交集，就可以并行。
- WRITESET_SESSION，比WRITESET多了一个约束，在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序
唯一标识hash值是“库名+表名+索引名+值”。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert语句对应的writeset就要多增加一个hash值
优点
- writeset是主库直接写入到binlog里面的，在备库执行的时候，不需要解析binlog内容（event里的行数据），节省计算量
- 不需要把整个事务的binlog都扫一遍才能决定分发到哪个worker，更省内存
- 不依赖于binlog内容，与格式无关
对于“表上没主键”和“外键约束”的场景，WRITESET策略也是没法并行的，也会暂时退化为单线程模型。
如果主库都是单线程压力模式，在从库追主库的过程中，binlog-transaction-dependency-tracking 应该选用什么参数？由于主库是单线程压力模式，所以每个事务的commit_id都不同，那么设置为COMMIT_ORDER模式的话，从库也只能单线程执行。同样地，由于WRITESET_SESSION模式要求在备库应用日志的时候，同一个线程的日志必须与主库上执行的先后顺序相同，也会导致主库单线程压力模式下退化成单线程复制。所以，应该将binlog-transaction-dependency-tracking 设置为WRITESET。
24.5. 主从复制问题
24.5.1. 循环复制问题
- 生产环境中，节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。
- 节点A上更新了一条语句，然后再把生成的binlog 发给节点B，节点B执行完这条更新 语句后也会生成binlog发送给节点A。（建议把参数log_slave_updates设置为on，表示备库执行relay log后生成binlog）。如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？
- MySQL在binlog中记录了这个命令第一次执行时所在实例的server id，两个互为主备库的server id必须不同；备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
- 什么情况下双M结构会出现循环复制？
- 主库更新事务后，用命令set global server_id=x修改了server_id。等日志再传回来的时候，发现server_id跟自己的server_id不同就只能执行了
- 数据库迁移三节点复制场景。假设从备份恢复的数据库B，设置主从结构的数据库A和A',A要恢复数据，设置主服务器为B，此时由于B的serverId不等于A，A'的serverId,因此会出现循环复制，可执行如下操作
stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave;
stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave
24.5.2. 主备延迟
- 主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器掉电。
- 与主备数据库的数据同步有关的时间点主要包括：主库A执行完成一个事务写入binlog记为T1;备库B接收完这个binlog记为T2(可忽略);备库B执行完成事务记为T3。主备延迟就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，即T3-T1。主备延迟最直接的表现是备库消费中转日志（relay log）的速度，比主库生产binlog的速度要慢。seconds_behind_master结果用于表示当前备库延迟了多少秒。计算方法是备库执行事务的时间-binlog在主库写入的时间。就是T3-T1。
- 如果主备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？不会。因为备库连接到主库的时候，会通过执行SELECTUNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行seconds_behind_master计算的时候会自动扣掉这个差值。
24.5.3. 主备延迟的来源
- 备库所在机器的性能要比主库所在的机器性能差。因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署。
- 备库的压力大。大量运营或者其他查询在备库
- 一主多从。除了备库外，可以多接几个从库来分担读的压力（建议）
- 通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力。
- 大事务。因为主库上必须等事务执行完成才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟
- 不要一次性地用delete语句删除太多数据。要控制每个事务删除的数据量，分成多次删除。并且要开空闲时间
- 计划内的大表DDL，建议使用gh-ost方案或者使用 pt-online-schema-change
- 备库的并行复制能力。由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略。
24.5.4. 主备延迟场景
- 一般现在的数据库运维系统都有备库延迟监控，其实就是在备库上执行showslave status，采集seconds_behind_master的值。当延迟监控是表现为45度的线段，可能是什么原因呢？备库的同步在这段时间完全被堵住了，可能包括以下场景
- 大事务（包括大表DDL、一个事务操作很多行）
- 备库起了一个长事务，比如begin;select * from limit 1;就不动了，主库对表t做了一个加字段操作，即使这个表很小，这个DDL在备库应用的时候也会被堵住，也不能看到这个现象
24.5.5. 主从不一致
- 在 MySQL5.5 以及之前， slave 的 SQL 线程执行的 relay log 的位置只能保存在文件（ relay-log.info）里面，并且该文件默认每执行 10000 次事务做一次同步到磁盘， 这意味着 slave 意外 crash 重启时， SQL 线程执行到的位置和数据库的数据是不一致的，将导致复制报错，如果不重搭复制，则有可能会导致数据不一致。
- MySQL 5.6 引入参数 relay_log_info_repository，将该参数设置为 TABLE 时， MySQL 将 SQL 线程执行到的位置存到 mysql.slave_relay_log_info 表，这样更新该表的位置和 SQL 线程执行的用户事务绑定成一个事务，这样 slave 意外宕机后，slave 通过 innodb 的崩溃恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。
- MySQL 5.6 引入 GTID 复制，每个 GTID 对应的事务在每个实例上面最多执行一次， 这极大地提高了复制的数据一致性。
- MySQL 5.5 引入半同步复制， 用户安装半同步复制插件并且开启参数后，设置超时时间，可保证在超时时间内如果 binlog 不传到 slave 上面，那么用户提交事务时不会返回，直到超时后切成异步复制，但是如果切成异步之前用户线程提交时在 master 上面等待的时候，事务已经提交，该事务对 master 上面的其他 session 是可见的，如果这时 master 宕机，那么到 slave 上面该事务又不可见了，该问题直到 5.7 才解决。
- MySQL 5.7 引入无损半同步复制，引入参 rpl_semi_sync_master_wait_point，该参数默认为 after_sync，指的是在切成半同步之前，事务不提交，而是接收到 slave 的 ACK 确认之后才提交该事务，从此，复制真正可以做到无损的了。
- 可以再说一下 5.7 的无损复制情况下， master 意外宕机，重启后发现有 binlog 没传到 slave 上面，这部分 binlog 怎么办？？？分 2 种情况讨论， 1 宕机时已经切成异步了， 2 是宕机时还没切成异步？？？ 这个怎么判断宕机时有没有切成异步呢？？？ 分别怎么处理？？？
24.6. 日常管理维护
24.6.1. 查看从服务器状态
# Slave_IO_Running负责从主服务器（Master）上读取 BINLOG日志，并写入从服务器上的中继日志中。
# Slave_SQL_Running：此进程负责读取并且执行中继日志中的 BINLOG 日志
# 只要其中有一个进程的状态是 no，则表示复制进程停止
# seconds_behind_master 主备延时
mysql> show slave status
24.6.2. 主从服务器同步
# 当主从服务器数据差距太大时，需要在负载较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。
# 在主服务器上阻塞主数据库的所有更新操作：
mysql>FLUSH TABLES WITH READ LOCK;
# 记录 SHOW 语句的输出的日志名和偏移量，这些是从服务器复制的目的坐标。
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000039 | 974 | | |
+------------------+----------+--------------+------------------+
# 从服务器上执行中 MASTER_POS_WAIT()函数。阻塞直到从服务器达到指定的日志文件和偏移量，返回-1表示超时退出。返回0则从服务器与主服务器同步
mysql> select MASTER_POS_WAIT('mysql-bin.000039','偏移量');
# 在主服务器上允许主服务器重新开始处理更新
mysql> UNLOCK TABLES;
24.6.3. 从服务器复制出错的处理
# 如果是表结构不同导致的，则修改从服务器的表与主服务器的相同，重新运行START SLAVE 语句
# 如果不是表结构不同导致的更新失败，则需要确认手动更新是否安全，忽视n条来自主服务器的更新失败的语句。如果来自主服务器的更新语句不使用 AUTO_INCREMENT 或 LAST_INSERT_ID()，n 值应为1，否则应为 2。原因是使用AUTO_INCREMENT 或 LAST_INSERT_ID()的语句需要从二进制日志中取两个事件
# 停止复制
mysql> stop slave;
# 跳过2个语句
mysql> SET GLOBALSQL_SLAVE_SKIP_COUNTER = n
# 继续复制
mysql> start slave;
24.6.4. log event entry exceeded max_allowed_packet 的处理
# 当含有大文本的记录无法通过网络进行传输时，出现“log event entry exceeded max_allowed_packet”错误
# 查看
mysql> show variables like 'max_allowed_packet';
# 在主从服务器上增加 max_allowed_packet 参数的大小
mysql> SET @@global.max_allowed_packet=16777216
同时在 my.cnf 里，设置 max_allowed_packet = 16M,保证数据库重启后参数继续有效
24.6.5. 多主复制(多台主服务器对一台从服务器)时的自增长变量冲突问题
# 如果多个主服务器的表采用自动增长变量，那么复制到同一个从服务器的同一张表后很可能会引起主键冲突，因为系统参数auto_increment_increment和auto_increment_offset默认值1，需要修改这2个配置。比如，两个master 的情况可以按照以下设置
Master1 上：auto_increment_increment = 2，auto_increment_offset = 1；（1,3,5,7…序列）
Master2 上：auto_increment_increment = 2，auto_increment_offset = 0；（0,2,4,6…序列）
mysql> SHOW VARIABLES LIKE 'auto_inc%';
mysql> SET @@auto_increment_increment=10;
mysql> SET @@auto_increment_offset=5
24.6.6. 查看从服务器的复制进度
# 通过SHOW PROCESSLIST列表中的Slave_SQL_Running 线程的Time值得到，它记录了从服务器当前执行的 SQL 时间戳与系统时间之间的差距，单位秒
# MySQL 复制的机制是执行主服务器传输过来的二进制日志，二进制日志中的每个语句通过设置时间戳来保证执行时间和顺序的正确性，所以每个语句执行之前都会首先设置时间戳，而通过查询这个进程的 Time 就可以知道最后设置的时间戳和当前时间的差距。
24.6.7. 切换主备服务器
24.6.7.1. 主备切换策略
- 在双M结构下，主备切换详细过程：(一般是由专门的HA系统来完成)
- 判断备库B现在的seconds_behind_master，如果小于某个值（比如5秒）继续下一步，否则持续重试这一步；
- 把主库A改成只读状态（把readonly设置为true）
- 判断备库B的seconds_behind_master的值，直到这个值变成0为止；
- 把备库B改成可读写状态，把readonly设置为false；
- 把业务请求切到备库B。
- 在步骤2之后，主库A和备库B都处于readonly状态，系统处于不可写状态，直到步骤5完成后才能恢复。在这个不可用状态中，比较耗费时间的是步骤3，可能需要耗费好几秒的时间。所以需要在步骤1先做判断，确保seconds_behind_master的值足够小。
- 在满足数据可靠性的前提下，MySQL高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。
- 异常切换会是什么效果？假设，主库A和备库B间的主备延迟是30分钟，这时候主库A掉电了，HA系统要切换B作为主库。此时系统处于完全不可用的状态。因为，主库A掉电后，我们的连接还没有切到备库B。
- 那能不能直接切换到备库B，但是保持B只读呢？不行。因为，中转日志还没有应用完成，如果直接发起主备切换，客户端查询看不到之前执行完成的事务，会认为有“数据丢失”。虽然随着中转日志的继续应用，这些数据会恢复回来，但是对于一些业务来说，查询到“暂时丢失数据的状态”也是不能被接受的
24.6.7.2. 基于位点的主备切换
# 首先要确保所有的从数据库都已经执行了 relay log 中的全部更新
# 在每个从服务器上，关闭IO_THREAD
mysql> STOP SLAVE IO_THREAD;
# 检查进程状态，直到看到状态是 Has read all relay log，表示更新都执行完毕
mysql> SHOW PROCESSLIST
# 在从数据库 S1 上，执行 STOP SLAVE 停止从服务，然后 RESET MASTER 重置成主数据库。
mysql> STOP SLAVE;
mysql> reset master;
# 得到从数据库 S1 上最新的mysql-bin.xxxxx和position;
mysql> show master status;
# 取原数据库故障的时刻T;使用binlog解析S1的mysql-bin.xxxxx获得T时刻的位点position
mysql> mysqlbinlog mysql-bin.xxxxx --stop-datetime=T --start-datetime=T
# 在从数据库 S2 上，执行 STOP SLAVE 停止从服务
mysql> STOP SLAVE;
# 设置从数据库 S2 的住数据库为S1 并启动复制。同时指定mysql-bin.xxxx和position
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.101';
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='1234test',
-> MASTER_LOG_FILE='mysql-bin.xxxxx',
-> MASTER_LOG_POS=102;
# 启动复制
mysql> start slave;
# 通知所有的客户端将应用指向 S1，这样客户端发送的所有的更新语法写入到 S1 的二进制日志。
# 删除新的主数据库服务器上的 master.info 和 relay-log.info 文件，否则下次重启的时候还会按照从服务器启动。
# 最后，如果 M 服务器可以修复，则可以按照 S2 的方法配置成 S1 的从服务器。
# 默认 S1 是打开 log-bin 选项的，这样重置成主数据库后可以将二进制日志传输到其他从服务器。其次，S1上没有打开 log-slave-updates 参数，否则重置成主数据库后，可能会将已经执行过的二进制日志重复传输给 S2，导致S2的同步错误
# 同步位点值并不精确。为什么呢？假设在T这个时刻，主库A已经执行完成了一个insert语句插入了一行数据R，并且已经将binlog传给了A’和B，然后在传完的瞬间主库A就掉电了。那么
- 在从库B上，由于同步了binlog， R这一行已经存在；
- 在新主库A’上， R这一行也已经存在，日志是写在123这个位置之后的；
- 在从库B上执行change master命令，指向A’的File文件的123位置，就会把插入R这一行数据的binlog又同步到从库B去执行。这从库B的同步线程就会提示出现了主键冲突，然后停止同步。
# 所以在切换任务的时候，要先主动跳过这些错误，有两种常用的方法。
- 1.主动跳过一个事务。set global sql_slave_skip_counter=1;start slave;因为切换过程中，可能会不止重复执行一个事务，所以我们需要在从库B刚开始接到新主库A’时，持续观察，每次碰到这些错误就停下来，执行一次跳过命令，直到不再出现停下来的情况，以此来跳过可能涉及的所有事务。
- 2.设置slave_skip_errors参数跳过指定错误码，如1062错误是插入数据时唯一键冲突；1032错误是删除数据时找不到行。后面一定要把这个参数设置为空避免主从不一致
24.6.7.3. 主备切换方案GTID(Global Transaction Identifier)
- GTID全局事务ID，一个事务在提交的时候生成的，GTID=source_id:transaction_id。source_id是一个实例第一次启动时自动生成的，是一个全局唯一的值；transaction_id是一个整数，初始值是1，每次提交事务的时候分配给这个事务，并加1。
- 当启动参数gtid_mode=on和enforce_gtid_consistency=on表示开启GTID模式，每个事务都会跟一个GTID一一对应。
- GTID有两种生成方式，取决于session变量gtid_next的值
- automatic代表使用默认值。MySQL就会把source_id:transaction_id分配给这个事务。记录binlog的时候，先记录一行 SET@@SESSION.GTID_NEXT='source_id:transaction_id'然后把这个GTID加入本实例的GTID集合
- 指定的GTID的值，比如set gtid_next='current_gtid’，只能给一个事务提交，下一个事务要重新执行set命令把gtid_next设置成另外一个gtid或者automatic。如果current_gtid已经存在于实例的GTID集合那么事务会直接被系统忽略；如果没有存在于实例的GTID集合中，就将这个current_gtid分配给接下来要执行的事务
- 每个MySQL实例都维护了一个GTID集合，用来对应“这个实例执行过的所有事务”。
- 如何避免主键冲突？假设插入数据的GTID=A，可通过提交空事务解决
set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;
set gtid_next=automatic;
start slave;
- GTID实现原理：主数据库A收到从数据库B连接请求后，会求出A的GTID集合set_A与B的GTID集合set_B差集set_C(存在set_A,但不存在set_b),并判断A是否包含了set_C所需要的所有binlog，如果不包含直接返回错误，否则顺序一条一条发送binlog给B
- 设计思想：在基于GTID的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例B需要的日志已经不存在，A’就拒绝把日志发给B。
- 引入GTID后，一主多从的切换场景下，从库B、C、D只需要分别执行change master命令指向实例A’即可。找位点这个工作，在实例A’内部就已经自动完成了。之后这个系统就由新主库A’写入，主库A’的自己生成的binlog中的GTID集合格式是：server_uuid_of_A’:1-M。如果之前从库B的GTID集合格式是 server_uuid_of_A:1-N， 那么切换之后GTID集合的格式就变成了server_uuid_of_A:1-N, server_uuid_of_A’:1-M。当然，主库A’之前也是A的备库，因此主库A’和从库B的GTID集合是一样的。
# GTID模式下，备库B要设置为新主库A’的从库的语法
mysql> CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
master_auto_position=1 # 表示这个主备关系使用的是GTID协议。
- 在GTID模式下，如果一个新的从库接上主库，但是需要的binlog已经没了，要怎么做
- 如果业务允许主从不一致的情况，那么可以在主库上先执行showglobal variables like ‘gtid_purged’，得到主库已经删除的GTID集合，假设是gtid_purged1；然后先在从库上执行reset master，再执行set global gtid_purged =‘gtid_purged1’；最后执行start slave，就会从主库现存的binlog开始同步。binlog缺失的那一部分，数据在从库上就可能会有丢失，造成主从不一致。
- 如果需要主从数据一致的话，最好还是通过重新搭建从库来做。
- 如果有其他的从库保留有全量的binlog的话，可以把新的从库先接到这个保留了全量binlog的从库，追上日志以后，如果有需要，再接回主库。
- 如果binlog有备份的情况，可以先在从库上应用缺失的binlog，然后再执行start slave。
24.7. 如何判断一个(主)数据库是不是出问题了？
24.7.1. 外部判断
- 在系统库（mysql库）里创建一个表比如health_check，存放timestamp字段，用来表示最后一次执行检测时间
- 节点可用性的检测都应该包含主库和备库。为了让主备之间的更新不产生冲突，mysql.health_check表存入多行数据，并用A、B的server_id做主键
- 更新语句如果失败或者超时，就可以发起主备切换了，但还有判定慢的问题？所有的检测逻辑都需要一个超时时间N。执行一条update语句，超过N秒后还不返回，就认为系统不可用。当日志盘的IO利用率已经是100%时，整个系统响应非常慢，已经需要做主备切换了。但IO利用率100%表示系统的IO是在工作的，每个请求都有机会获得IO资源，执行自己的任务。而update命令需要的资源很少，所以可能在拿到IO资源的时候就可以提交成功，并且在超时时间N秒未到达之前就返回给了检测系统。检测就得到了“系统正常”的结论。但这时候在业务系统上正常的SQL语句已经执行得很慢了，由于更新表基于外部检测的。需要定时轮询，所以系统可能已经出问题了，却需要等到下一个检测发起执行语句的时候，我们才有可能发现问题。会导致切换慢的问题。
mysql> CREATE TABLE `health_check` (
`id` int(11) NOTNULL,
`t_modified` timestamp NOTNULL DEFAULTCURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();
24.7.2. 内部判断
# 查看redo log统计信息（每次IO请求的时间）
mysql> select * from performance_schema.file_summary_by_event_name where event_name='wait/io/file/innodb/innodb_log_file'
# 查看binlog统计信息
mysql> select * from performance_schema.file_summary_by_event_name where event_name='wait/io/file/sql/binlog'
# performance_schema打开统计功能是有性能损耗的。建议只打开自己需要的项进行统计。
# 开启redo log和binlog统计信息
mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file'
# 可通过redo log和binlog这两个统计信息的MAX_TIMER的是否超过阈值(比如单次IO请求时间200毫秒)来判断数据库是否出问题
mysql> select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;
# 发现异常后，取到需要的信息，再把之前的统计信息清空。如果再次出现这个异常，就可以加入监控累积值了
mysql> truncate table performance_schema.file_summary_by_event_name;
25. MySQL Cluster
- Cluster就是一组“节点”的组合。这里的“节点”是一个逻辑概念，一台计算机上可以存放一个节点，也可以存放多个节点。这些节点的功能各不相同，有的用来存储数据（数据节点），有的用来存放表结构（SQL 节点），有的用来对其他节点进行管理（管理节点）。这些节点组合在一起，可以为应用提供具有高可用性、高性能和可缩放性的 Cluster 数据管理。
- MySQL 使用 NDB 存储引擎来对数据节点的数据进行存储，NDB 存储引擎支持基于内存的数据表和基于磁盘的数据表
- MySQL Cluster 通过数据的分布式存储和可扩展的系统架构，可以满足更大规模的应用；而且通过冗余策略，可以大大地提高系统的可靠性和数据的有效性。
25.1. MySQL Cluster架构
- 管理节点
- 管理节点用来对其他节点进行管理。实际操作中，是通过对一个叫作config.ini的配置文件进行维护而起到管理的作用。该文件可以用来配置有多少需要维护的副本、需要在每个数据节点上为数据和索引分配多少内存、数据节点的位置、在每个数据节点上保存数据的磁盘位置、SQL节点的位置等信息。管理节点只能有一个，配置要求不高。
- SQL 节点
- SQL节点可以理解为应用和数据节点之间的一个桥梁。应用不能直接访问数据节点，只能先访问SQL节点，然后SQL节点再去访问数据节点来返回数据。Cluster中可以有多个SQL节点，通过每个SQL节点查询到的数据都是一致的，通常来说，SQL节点越多，分配到每个SQL节点的负载就越小，系统的整体性能就越好。
- 数据节点
- 用来存放Cluster里面的数据，可以有多个数据节点。每个数据节点可以有多个镜像节点。任何一个数据节点发生故障，只要它的镜像节点正常，Cluster就可以正常运行。
- 这 3 种逻辑上不同的节点物理上可以存放在不同的服务器上，也可以在同一台服务器上。MySQL Cluster 的访问过程：前台应用利用一定的负载均衡算法将对数据库的访问分散到不同的 SQL 节点上，然后 SQL 节点对数据节点进行数据访问并从数据节点返回结果，最后 SQL 节点将收到的结果返给前台应用。而管理节点并不参与访问过程，它只用来对 SQL 节点和数据节点进行配置管理
26. 字符集
- 字符集就是一套文字符号及其编码、比较规则的集合。1960年，美国标准化组织ANSI发布了第一个计算机字符集──ASCII（American Standard Code for Information Interchange），采用7位编码，定义了包括大小写英文字母、阿拉伯数字和标点符号，以及33个控制符号等。其后制定的各种字符集基本都兼容ASCII字符集
26.1. 怎样选择合适的字符集
- 字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会有影响。
- 主要考虑因素包括：
- 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择 Unicode字符集。如UTF-8
- 如果应用中涉及已有数据的导入，需要考虑数据库字符集对已有数据的兼容性。
- 如果数据库只需要支持一般中文，数据量很大，性能要求也高，就应该选双字节定长编码的中文字符集，比如GBK。因为GBK汉字编码只占2个字节，而UTF-8占3个字节，这样可以减少磁盘 I/O、数据库cache，以及网络传输的时间，从而提高性能。如果应用主要处理英文字符，仅有少量汉字数据，那么选UTF-8更好，因为GBK的西文字符编码都是2个字节,会造成很大不必要的开销
- 如果数据库需要做大量的字符运算，如比较、排序等，选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
- 如果所有客户端程序都支持相同的字符集，应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失
26.2. MySQL 字符集
# 查看所有可用的字符集
mysql> show character set：
# 显示所有的字符集和该字符集默认的校对规则
# 字符集是用来定义MySQL存储字符串的方式，校对规则（COLLATION）则是定义了比较字符串的方式。每个字符集至少对应一个校对规则
mysql> desc information_schema.character_sets;
# 查看相关字符集的校对规则
mysql> SHOW COLLATION LIKE 'gbk%';或者desc information_schema.COLLATIONS;
26.3. MySQL 字符集的设置
# 服务器字符集和校对规则在服务启动的时候确定
# 查询当前服务器的字符集
mysql> show variables like 'character_set_server';
# 查询当前服务器的校对规则
mysql> show variables like 'collation_server';
# 在my.cnf设置服务器校验规则。
[mysqld]
default-character-set=gbk
# 在启动选项中指定服务器校验规则
mysqld --default-character-set=gbk
# 在编译的时候指定服务器校验规则
./configure --with-charset=gbk
# 数据库字符集和校对规则在创建数据库的时候指定
# 可以使用“alter database”命令进行修改。如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容
# 显示数据库的字符集
mysql> show variables like 'character_set_database'
# 显示数据库的校对规则
mysql> show variables like 'collation_database'
# 表字符集和校对规则,在创建表的时候指定
# 显示表的字符集和校对规则
mysql> show create table 表名;
# 可以通过alter table命令进行修改，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集
# 列字符集和校对规则可以在创建表时指定，或者在修改表时调整
# 服务端与客户端连接字符集和校对规则
# character_set_client、character_set_connection 和 character_set_results，分别代表客户端、连接和返回结果的字符集，这3个字符集应该是相同的，才可以确保用户写入的数据可以正确地读出，
# 设置连接的字符集和校对规则（同时修改这 3 个参数的值）
mysql>SET NAMES ***;
# 设置my.cnf
[mysql]
default-character-set=gbk
# 字符集的修改步骤(对旧记录也生效)
# 如果在应用开始阶段没有正确的设置字符集，在运行一段时间以后才发现存在不能满足要求需要调整，又不想丢弃这段时间的数据，那么就需要进行字符集的修改。
# 字符集的修改不能直接通过alter database character set ***或者alter table tablename character set ***命令进行，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成
# 注意：选择目标字符集的时候，要注意最好是源字符集的超级，或者确定比源字符集的字库更大，目标字符集中不支持的字符倒入后会变成乱码，丢失一部分数据。例如，GBK 字符集的字库大于 GB2312 字符集，那么 GBK 字符集的数据，如果导入 GB2312 数据库中，就会丢失 GB2312 中不支持的那部分汉字的数据
# 模拟将latin1字符集的数据库修改成GBK字符集的数据库的过程。
# 1.导出表结构：
# default-character-set=gbk 表示设置以什么字符集连接 d 表示只导出表结构，不导出数据。
mysql> mysqldump -uroot -p --default-character-set=gbk -d 数据库名> createtab.sql其中，
# 2.手工修改 createtab.sql 中表结构定义中的字符集为新的字符集。
# 3.确保记录不再更新，导出所有记录
# quick：用于转储大的表。强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中
# extended-insert：使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小，重载文件时可以加速插入
# no-create-info：不写重新创建每个转储表的 CREATE TABLE 语句
# default-character-set=latin1：按照原有的字符集导出所有数据
mysql> mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 数据库名> data.sql
# 4.打开 data.sql，将 SET NAMES latin1 修改成 SET NAMES gbk
# 5.使用新的字符集创建新的数据库
mysql> create database databasename default charset gbk;
# 6.创建表，执行createtab.sql
mysql> mysql -uroot -p databasename < createtab.sql
# 7.导入数据，执行 data.sql
mysql> mysql -uroot -p databasename < data.sql
27. SQL Mode
- MySQL可以运行不同的SQL Mode（SQL 模式下。SQL Mode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL
27.1. SQL Mode简介
- SQL Mode解决的问题：完成不同严格程度的数据校验，有效地保障数据准确性。同时使不同数据库迁移更方便
# 查询SQL Mode的值
mysql> select @@sql_mode;
# 设置SQL Mode
# SESSION选项表示只在本次连接中生效；GLOBAL选项表示在本次连接中并不生效，而对于新的连接则生效，也可使用“--sql-mode="modes"”选项，在MySQL启动时设置
mysql> SET [SESSION|GLOBAL] sql_mode='xxx';
27.2. 常用的 SQL Mode
- ANSI 等同于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 和 ANSI组合模式，这种模式使语法和行为更符合标准的-
- STRICT_TRANS_TABLES 适用于事务表和非事务表，它是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告
- TRADITIONAL等同于 STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、TRADITIONAL和 NO_AUTO_CREATE_USER 组合模式，所以它也是严格模式，对于插入不正确的值是给出错误而不是警告。可以应用在事务表和非事务表，用在事务表时，只要出现错误就会立即回滚
27.3. SQL Mode 在迁移中如何使用
- 如果 MySQL 与其他异构数据库之间有数据迁移的需求的话，那么 MySQL 中提供的的数据库组合模式则会对数据迁移过程会有所帮助。例如“ORACLE”、“DB2”等
28. 全表扫描
28.1. 全表扫描对server层的影响
- 全表扫描流程
- 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length决定。
- 重复获取行，直到net_buffer写满，调用网络接口发出去。
- 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
- 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer默认定义/proc/sys/net/core/wmem_default）写满了，会暂停读数据的流程，进入等待。直到网络栈重新可写，再继续发送
- 查询的结果是分段发给客户端的，因此扫描全表，查询返回大量的数据，并不会把内存打爆。如果客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长。
28.2. 全表扫描对InnoDB的影响
- InnoDB内存管理Buffer Pool用的是经过改进的最近最少使用 (Least RecentlyUsed, LRU)算法，核心是淘汰最久未使用的数据。用链表来实现。按照5:3的比例把整个LRU链表分成了young区域和old区域，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域。old区域用来处理全表扫描的冷数据（快速失效），而young区域用来处理正常查询的数据，从而保证Buffer Pool响应正常业务的查询命中率。
- 当访问young区域的数据页时，将其移到LRU链表头部
- 当访问新的不存在于LRU链表的数据页时，则淘汰掉末尾数据页Pm，但是新插入的数据页Px，是放在LRU_old处。
- 处于old区域的数据页，每次被访问的时候都要做下面这个判断：
- 若这个数据页在LRU链表中存在的时间超过了innodb_old_blocks_time(1秒)，就把它移动到链表头部；
- 如果这个数据页在LRU链表中存在的时间短于innodb_old_blocks_time(1秒)，位置保持不变。
- 这个策略就是为了处理类似全表扫描的操作量身定制
- 扫描过程中，需要新插入的数据页，都被放到old区域;
- 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；
- 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是young区域），很快就会被淘汰出去。
28.3. 扫描行数
- mysql> show index from 表名;可以看到一个索引上不同值的个数-基数cardinality，基数值越大，索引区分度越好
- 基数计算方法：因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。
InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。参数innodb_stats_persistent的值为on时表示统计信息会持久化存储。N是20，M是10；为off时，表示统计信息只存储在内存中，N是8，M是16 - 如果统计信息不对，可通过analyze table t重新统计索引信息
28.4. 如何正确地显示随机消息？
# 随机选一个
# 扫描行数C+Y+1行
# 1. 取得整个表的行数C
# 2. 取得 Y = floor(C*rand())。 floor函数取整数部分
# 3. 再用limit Y,1取得一行
mysql> select count(*) into @C from t;
mysql> set @Y = floor(@C * rand());
mysql> set @sql = concat("select * from t limit ", @Y, ",1");
mysql> prepare stmt from @sql;
mysql> execute stmt;
mysql> DEALLOCATE prepare stmt;
# 随机选多个
# 1. 取得整个表的行数C；
# 2. 根据相同的随机方法得到Y1、Y2、Y3；
mysql> select count(*) into @C from t;
mysql> set @Y1 = floor(@C * rand());
mysql> set @Y2 = floor(@C * rand());
mysql> set @Y3 = floor(@C * rand());
# 方法1. 再执行三个limit Y, 1语句得到三行数据。扫描行数C+(Y1+1)+(Y2+1)+(Y3+1)
mysql> select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
mysql> select * from t limit @Y2，1；
mysql> select * from t limit @Y3，1；
# 方法2. 取Y1、Y2和Y3里面最大数M，最小的数N，扫描行数C+M+1行
# 也可以先取回id值，在应用中确定了三个id值以后，再执行三次where id=X的语句
mysql> select * from t limit N, M-N+1;
28.5. 救火方法
28.5.1. 短连接风暴
- 短连接模式在业务高峰期时出现连接数突然暴涨的情况
- 增大max_connections(不推荐)
- 减少连接过程的消耗。跳过权限验证，–skip-grant-tables参数重启数据库(MySQL 8.0会同时把--skip-networking参数打开，表示数据库只能被本地的客户端连接)(不推荐)
- kill connection+线程id，断开事务外空闲太久的连接；断开事务内空闲太久的连接的线程select * from information_schema.innodb_trx where trx_mysql_thread_id = threadid
28.5.2. QPS突增问题
- 删除数据库白名单(运维:虚拟化、白名单机制、业务账号分离。)
- 删除数据库用户(运维)
- 查询重写，把压力最大的SQL语句直接重写成"select 1"返回。（慎用）
28.5.3. 查询慢
- 执行show processlist命令，查看state
# Command列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接
# 表被锁住：Waiting for table metadata lock表示有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了
# 举例 sessionA->lock table t write;session B->select * from t where id = 1
# 解法：kill掉持有MDL锁的进程。启动参数performance_schema=on，通过select blocking_pid from sys.schema_table_lock_waits; 再用kill命令断开连接
# 等flush：Waiting for table flush表示现在有一个线程要对表t做flush操作被阻塞了
mysql> flush tables [t] with read lock;
# 举例
session A mysql> sleep(1) from t;//执行10万秒
session B mysql> flush tables [t] [with read lock];//关闭表t
session C mysql> select * from t where id = 1
# 等行锁: Locked表示有一个线程持有行锁，要等待锁释放
# 解决方案：select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`查到谁占着这个写锁。 KILL id连接被断开的时候，会自动回滚这个连接里面正在执行的线程，同时释放行锁
# 举例
session A mysql> begin;update set c=c+1 where id = 1;//加了写锁
session B mysql> select * from t where id=1 lock in share mode;//需要读锁被锁住
# 扫描行数太多或者undo.log太多
# sql问题可参照慢sql定位及解决
28.5.4. 如何安全地给小表加字段？
- 先暂停DDL,再kill掉长事务，事务不提交，就会一直占着MDL锁。查到当前执行中的事务select * from information_schema.innodb_trx
- 在alter table语句里面设定等待时间，拿不到MDL写锁也不要阻塞后面的业务语句，先放弃。之后再重试
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
28.5.5. 在 MySQL 服务器运行缓慢的情况下输入什么命令能缓解服务器压力？
- 检查系统的状态：比如 CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲，这也可能不是一个正常的状态，因为 CPU 可能正等待IO的完成。除此之外，还应观注那些占用系统资源(CPU、内存)的进程。
- 使用 sar 来检查操作系统是否存在 IO 问题。
- 使用 vmstat 监控内存 CPU 资源。
- 磁盘 IO 问题，处理方式：做 raid10 提高性能 。
- 网络问题，telnet 一下 MySQL 对外开放的端口。如果不通的话，看看防火墙是否正确设置了。另外，看看 MySQ L是不是开启了 skip-networking 的选项，如果开启请关闭。
- 检查 MySQL 参数
- max_connect_errors
- connect_timeout
- skip-name-resolve
- slave-net-timeout=seconds
- master-connect-retry
- 检查 MySQL 相关状态值
- 连接数
- 系统锁情况
- 慢查询（slow query）日志
29. 数据库flush
- 一条SQL语句，正常执行的时候特别快，偶尔会变得特别慢，并且场景很难复现，可能就是在刷脏页（flush）:把内存里的数据写入磁盘的过程；InnoDB会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了一下的原因。
- 当内存数据页跟磁盘数据页内容不一致的时候，称为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”
29.1. flush的触发条件
- redo log写满了。MySQL会停止所有更新操作，把从check point到write pos之间的部分脏页flush到磁盘上，redo log留出空间可以继续写.从监控上看，更新数会跌为0。
- 系统内存不足。InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。当要读入的数据页不在内存时，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用。系统内存不足和刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：
- 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
- 日志写满，更新全部堵住，写性能跌为0
- 所以，InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况
- MySQL认为系统“空闲”的时候
- MySQL正常关闭。把内存的脏页都flush到磁盘上，下次启动时就可以直接从磁盘上读数据，启动速度会很快
29.2. InnoDB刷脏页的控制策略
- 将innodb_io_capacity这设置成磁盘的IOPS。可以通过fio这个工具来测试磁盘随机读写能力：fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size
- InnoDB的刷盘速度参考因素：脏页比例和redo log写盘速度。InnoDB会根据这两个因素先单独算出两个数字。
- 参数innodb_max_dirty_pages_pct是脏页比例上限，默认75%。InnoDB会根据当前的脏页比例M，算出一个范围在0到100之间的数字F1(M)；InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N)。然后，根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。
- 要合理地设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75%。其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
参数innodb_flush_neighbors=1是将互相相邻的脏页刷到磁盘，为0则只刷本页。在机械硬盘情况下可以减少很多随机IO。SSD这类IOPS比较高的设备，建议设置成0。
如果一个高配的机器，redo log设置太小，会发生什么情况?每次事务提交都要写redo log，如果设置太小，很快就会被写满，write pos一直追着CP。这时候系统不得不停止所有更新，去推进checkpoint。这时现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌
30. 数据删除流程
- InnoDB里的数据都是用B+树的结构组织的。InnoDB的数据是按页存储的，如果删除同页中的数据，InnoDB引擎会将这个记录标记为删除。如果之后要在同一页插入数据，可能会复用这个位置。如果删掉了一个数据页上的所有记录，整个数据页就可以被复用了。
- 记录的复用，只限于符合范围条件的数据。如果数据页中的一条记录被删掉，如果插入的数据在符合范围条件内，则可以直接复用，否则不能复用。
- 数据页的复用，当整个页从B+树里面摘掉以后，可以复用到任何位置。如果将数据页上的所有记录删除以后，会被标记为可复用。如果要插入一条记录需要使用新页的时候，该数据页是可以被复用的。如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。
- 用delete命令把整个表的数据删除呢？所有的数据页都会被标记为可复用。但是磁盘文件不会变小。通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。
- 插入数据也会造成空洞。如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。
- 更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。这也是会造成空洞的。经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。
30.1. 重建表-解决空洞问题
- 表数据删除回收空间，解决表空洞，空间收缩，都需要重建表，需要考虑在业务低峰时期使用，因为会阻塞DML
mysql> alter table A engine=InnoDB - MySQL 5.6以后Online DDL优化，添加日志文件记录和重放操作这个功能，允许对表进行CRUD操作，重建表的流程：
- 建立一个临时文件，扫描表A主键的所有数据页；
- 用数据页中表A的记录生成B+树，存储到临时文件中；
- 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件
- 用临时文件替换表A的数据文件。
- alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为什么要退化呢？为了实现Online，MDL读锁不会阻塞增删改操作。那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。而对于一个大表来说，Online DDL最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个DDL过程来说，锁的时间非常短。对业务来说，就可以认为是Online的。
- 上述的这些重建方法都会扫描原表数据和构建临时文件。对于大表来说是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，推荐使用GitHub开源的gh-ost来做
- 什么时候使用alter table t engine=InnoDB会让一个表占用的空间反而变大？
- 表本身就已经没有空洞
- 在DDL期间，如果刚好有外部的DML在执行，这期间可能会引入一些新的空洞
- 重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。其实重建表之后不是“最”紧凑的
31. 组提交（group commit）机制
两阶段提交是redo log先prepare， 再写binlog，最后再redo log commit。当innodb_flush_log_at_trx_commit=1，那么redo log在prepare阶段就要持久化一次、因为崩溃恢复是根据prepare 的redo log，再加上binlog来恢复，而redo log在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了，因此，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog
当TPS是每秒两万，每秒就会写四万次磁盘。但是磁盘能力也就两万左右，怎么能实现两万的TPS？因为用到组提交（group commit）机制
日志逻辑序列号（log sequence number，LSN），单调递增，对应redo log的一个个写入点。每次写入长度为length的redo log，LSN就会加上length。LSN也会写到InnoDB的数据页中，确保数据页不会被多次执行重复的redo log
redo log组提交：当多个并发事务都写完redo log buffer时，第一个事务会被选为这组的leader，此时LSN会变成组内的最大值MAX，当leader调用fsync写盘的时候，LSN=MAX，等leader返回时，所有LSN小于MAX的redo log都已经被持久化到磁盘。对应的事务也可以直接返回。
在单线程场景下，只能一个事务对应一次持久化。在并发更新场景下，第一个事务写完redo log buffer以后，接下来这个fsync越晚调用，组员可能越多，节约IOPS的效果就越好。
binlog组提交：写binlog分成两步：1. 先把binlog从binlog cache中写到磁盘上的binlog文件；2. 调用fsync持久化。 为了让组提交的效果更好，把redo log做fsync的时间拖到了redo log写盘之后。binlog也可以组提交了。在执把binlog fsync到磁盘时，如果有多个事务的binlog已经写完了，也是一起持久化的，这样也可以减少IOPS的消耗。 但binlog的write和fsync间的间隔时间短，导致能集合到一起持久化的binlog比较少，因此binlog的组提交的效果通常不如redo log的效果好
参数binlog_group_commit_sync_delay表示延迟多少微秒后才调用fsync; 参数binlog_group_commit_sync_no_delay_count表示累积多少次以后才调用fsync。 只要有一个满足条件就会调用fsync。
当binlog_group_commit_sync_delay=0，binlog_group_commit_sync_no_delay_count也无效了。
WAL机制是减少磁盘写，可是每次提交事务都要写redo log和binlog，这磁盘读写次数也没变少呀？WAL机制主要得益于：1.redo log和binlog都是顺序写，比随机写速度要快2.组提交机制大幅度降低磁盘的IOPS消耗。
如果你的MySQL现在出现了性能瓶颈，而且瓶颈在IO上，可以通过哪些方法来提升性能呢？
- 设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
- 将sync_binlog 设置为大于1的值（比较常见是100~1000）。但主机掉电时会丢binlog日志。
- 将innodb_flush_log_at_trx_commit设置为2。但主机掉电的时候会丢数据。不建议设置成0。因为此时表示redo log只保存在内存中，MySQL本身异常重启也会丢数据，而redo log写到文件系统的page cache的速度也是很快的，所以将这个参数设置成2跟设置成0其实性能差不多，但MySQL异常重启时就不会丢数据了，
32. 为什么还有kill不掉的语句？
kill query 线程id，表示终止这个线程中正在执行的语句
kill [connection] 线程id，表示先停止正在执行的语句，再断开这个线程的连接
使用kill没能断开连接。show processlist的Command=Killed
如果一个事务被kill之后，持续处于回滚状态，从恢复速度的角度看，应该重启等它执行结束，还是应该强行重启整个MySQL进程
？因为重启之后该做的回滚动作还是不能少的，所以从恢复速度的角度来说，应该让它自己结束。当然，如果这个语句可能会占用别的锁，或者由于占用IO资源过多，从而影响到了别的语句执行的话，就需要先做主备切换，切到新主库提供服务。切换之后别的线程都断开了连接，自动停止执行。接下来还是等它自己执行完成。这个操作属于减少系统压力，加速终止逻辑。
32.1. kill命令执行过程
- 把对应线程的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)；给对应线程发一个信号，让线程处理THD::KILL_QUERY状态，如果在等待，那么要先退出可被唤醒的等待，才能执行与THD::KILL_QUERY状态有关的逻辑
- kill无效的情况
- 线程没有执行到判断线程状态的逻辑。或者由于IO压力过大，读写IO的函数一直无法返回，导致不能及时判断线程的状态。如果一个线程的状态是KILL_CONNECTION，就把Command列显示成Killed。
- 终止逻辑耗时较长。show processlist的Command=Killed，需要等到终止逻辑完成，语句才算真正完成
- 超大事务 回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
- 大查询回滚。如果生成了较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待IO资源，导致耗时很长
- DDL命令执行到最后阶段被kill，需要删除中间过程的临时文件，也可能受IO资源影响耗时较久。
- 执行Ctrl+C的时候，是客户端另外启动一个连接，然后发送一个kill query命令
33. 临时表
33.1. 内存表与临时表区别
# 内存表，使用Memory引擎的表，数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
mysql> create table …engine=memory
# 临时表，不限制引擎类型，如果使用InnoDB引擎或者MyISAM引擎，数据保存在磁盘上。
mysql> create temporary table temp_t(id int primary key)engine=innodb;
33.2. 临时表特性
- 一个临时表只能被创建它的session访问，对其他线程不可见; 在session结束的时候会自动删除临时表。
- 不同session的临时表可以重名
- 临时表可以与普通表同名。show create以及增删改查语句访问的优先访问临时表。
- show tables命令不显示临时表
- 为什么不能用rename修改临时表的改名？而alter可以？
- 在实现上，执行rename table语句的时候，要求按照“库名/表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的frm文件是放在tmpdir目录下的，并且文件名的规则是“#sql{进程id}_{线程id}_序列号.frm”，因此会报“找不到文件名”的错误。
33.3. 临时表的应用
- 复杂查询优化：join优化
- 没有用到分区键的分库分表查询：在其中某个数据库上建立临时表存储从不同分区查询的结果，在数据库端处理完后返回
33.4. 为什么临时表可以重名？
- 磁盘上，临时表的数据文件#sql{进程id}_{线程id}_序列号.frm放在临时文件目录(select @@tmpdir)下，5.7前会在临时文件目录下创建一个相同.ibd为后缀的存放数据的文件；5.7后引入了一个临时文件表空间用来存放临时文件的数据。因此不需要再创建ibd文件了。根据文件名的前缀规则可知允许重名
- 内存里，每个表都对应一个table_def_key。普通表的table_def_key=库名+表名，临时表的table_def_key=server_id+thread_id+库名+表名，不同session的table_def_key不同，磁盘文件名也不同，因此可以并存；
- 在实现上，每个线程都维护了自己的临时表链表。每次session内操作表都会遍历链表查找同名临时表，如果有就优先操作临时表，如果没有再操作普通表；session结束时，对链表里的每个临时表，执行DROP TEMPORARY TABLE +表名。
- 在binlog_format=statment/mixed时，binlog中会记录所有临时表操作，为row不记录
- 为什么binlog中多记录了DROP TEMPORARY TABLE +表名 命令？因为主库在线程退出的时候会自动删除临时表，但备库同步线程是在持续运行的
- MySQL在记录binlog的时候，不论是create table还是alter table语句，都是原样记录，甚至于连空格都不变。但是如果执行drop table t_normal，系统记录binlog就会写成：
- 备库怎么解决不同session的同名临时表放到同一个worker的执行的情况呢？在记录binlog时会把主库执行这个语句的线程id写到binlog中。在备库的应用线程就能够知道执行每个语句的主库线程id，并利用这个线程id来构造临时表的table_def_key=库名+t1+“M的serverid”+“session A的thread_id”;由于table_def_key不同，所以这两个表在备库的应用线程里面是不会冲突的。
33.5. 什么时候会使用内部临时表？
- union：union会创建临时表，然后指定一个唯一索引，将union两边的数据加入到临时表中，如果唯一索引已经存在，那么将不在加入到临时表中
- group by：在表需要的数据小于tmp_table_size时，会创建内存临时表，然后指定一个唯一索引，使用sort_buffer排序，将数据插入到临时表中，如果唯一索引已经存在，那么会更新的聚合参数结果字段结果，不存在则直接加入到临时表中；大于tmp_table_size时，会把内存临时表转成磁盘临时表（默认InnoDB）
- group by为什么需要临时表？如果扫描过程中出现的数据是无序的，那么需要排序，排序一般用到临时表，如果是无序的，那么每次统计都需要反复查找对应的数据，性能较差
- group by优化
- 使用索引保证group by字段有序
- 不适合创建索引情况，直接用磁盘临时表select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
- group by使用原则
- 如果对group by语句的结果没有排序要求，要加order by null；
- 尽量让group by用上表的索引，确认方法是explain结果里没有Using temporary和 Using filesort；
- 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可通过适当调大tmp_table_size避免用到磁盘临时表；
- 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。
34. 自增主键为什么不是连续的？
- 自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。但实际上自增主键不能保证连续递增
34.1. 自增值保存位置
- MyISAM引擎的自增值保存在数据文件中。
- MySQL5.7前自增值保存在内存，每次重启后首次打开表都会去找自增值的最大值加1作为这个表当前的自增值
- MySQL 8.0将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值
34.2. 自增值修改机制
- 自增主键不连续产生原因：唯一键冲突；事务回滚；批量插入时申请id没完全用完
- 为什么唯一键冲突或者事务回滚，自增值不回退？为了避免主键冲突：id较大的事务成功提交，id较小的事务回滚，如果自增值回滚了，那么会导致自增值与id较大冲突，如果要解决主键冲突并回退，则需要给同表事务加锁，此时并发度大大下降
- 自增锁每次申请完就马上释放，以便允许别的事务再申请。由参数innodb_autoinc_lock_mode控制：
- 0表示语句执行结束后才释放锁；
- 1表示普通insert语句，自增锁在申请之后就马上释放；insert …select等批量插入数据语句，为了数据一致性，自增锁还是等语句结束后才释放
- 2表示所有的申请自增主键的动作都是申请后就释放锁。
- MySQL批量申请自增id策略，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍
35. 要不要使用分区表？
# 本分区表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。对于引擎层来说是4个表； 对于Server层来说是1个表。锁规则按各自表引擎特性来操作，间隙锁边界为分区键界限
CREATE TABLE `t` (
`ftime` datetime NOTNULL,
`c` int(11) DEFAULTNULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULTCHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);
- 分区表和手工分表的区别：一个是由server层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的。但从server层看，两者打开表的行为不同
35.1. 分区表特性
- MySQL每当第一次访问一个分区表的时候，需要把所有的分区都访问一遍。如果打开表的个数超过open_files_limit就会报错。
- server层一个分区表认为这是同一张表，因此所有分区共用同一个MDL锁；
- 引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。
- 如果查询语句的where条件中没有分区key，那就只能访问所有分区了
35.2. 什么场景下适合使用分区表呢？
业务透明，业务代码比手工分表简洁。清理历史数据方便alter table t drop partition …
数据库备份为什么要加锁呢？不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的
35.3. 分区表主键怎么设计？
- 最好是联合索引，主键+一个其他字段
CREATE TABLE `t` (
`id` int(11) NOTNULLAUTO_INCREMENT,
`ftime` datetime NOTNULL,
`c` int(11) DEFAULTNULL,
PRIMARY KEY (`ftime`,`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULTCHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
36. MYSQL数据库设计规范
- MYSQL数据库设计原则
- 核心原则
- 不在数据库做运算;
- cpu计算务必移至业务层;
- 控制列数量(字段少而精,字段数建议在20以内);
- 平衡范式与冗余(效率优先；往往牺牲范式)
- 拒绝3B(拒绝大sql语句：big sql、拒绝大事务：big transaction、拒绝大批量：big batch);
- 数据库设计
- 尽量把数据库设计的更小的占磁盘空间.
- 尽可能使用更小的整数类型.(mediumint就比int更合适).
- 尽可能的定义字段为not null,除非这个字段需要null.
- 所有字段都得有默认值。 - 在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。
- 如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.
- 使用load data infile来代替insert导入大量数据 - 经常OPTIMIZE TABLE 来整理碎片.
- date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。
- 系统的瓶颈
- 磁盘搜索。并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
- 磁盘读写(IO)。可以从多个媒介中并行的读取数据。
- CPU周期。数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
- 内存带宽。当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.
- 磁盘搜索。并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
37. 读写分离
37.1. 读写分离基本架构
- 主要目标是分摊主库的压力。
- 基本架构：客户端主动做负载均衡，一般会把数据库的连接信息放在客户端的连接层。由客户端来选择后端数据库进行查询。因为少了一层proxy转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是需要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如Zookeeper，尽量让业务端只专注于业务逻辑开发。
- 带proxy架构：在MySQL和客户端之间有一个中间代理层proxy，客户端只连接proxy，由proxy根据请求类型和上下文决定请求的分发路由。对客户端友好。不需要关注后端细节，连接维护、后端信息维护由proxy完成的。对后端维护团队的要求会更高。而且proxy也需要有高可用架构。因此，带proxy架构的整体就相对比较复杂。(趋势)
37.2. 过期读其解决方案
37.2.1. 判断主备无延迟方案
- 1.等到show slave status的seconds_behind_master=0才执行查询请求
- 2.对比位点确保主备无延迟：如果Master_Log_File和Relay_Master_Log_File读到的主库的最新位点；、Read_Master_Log_Pos和Exec_Master_Log_Pos备库执行的最新位点。这两组值完全相同，就表示接收到的日志已经同步完成。
- 3.对比GTID集合确保主备无延迟：当Auto_Position=1表示这对主备关系使用了GTID协议。备库收到的所有日志的GTID集合Retrieved_Gtid_Set和备库所有已经执行完成的GTID集合Executed_Gtid_Set这两个集合相同表示备库接收到的日志都同步完成
- 但还有一部分日志，处于客户端已经收到提交确认，主库已经执行完成，而备库还没收到日志的状态。因此理论上还是有过期读
37.2.2. 配合semi-sync方案
- 半同步复制semi-sync replication：
- 事务提交的时候，主库把binlog发给从库；
- 从库收到binlog以后，发回给主库一个ack，表示收到了；
- 主库收到这个ack以后，才能给客户端返回“事务完成”的确认。
- 如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。
- 如果主库掉电的时候，binlog还来不及发给从库，会不会导致系统数据丢失？普通的异步复制模式可能会丢失，semi-sync可以解决。配合前面关于位点的判断，就能够确定在从库上执行的查询请求，可以避免过期读。
- semi-sync配合判断主备无延迟的方案，存在两个问题：
- 一主多从的时候，在非响应ack的从库执行查询请求会存在过期读的现象；
- 在业务更新的高峰期下，可能存在持续延迟的情况，可能出现过度等待的问题
37.2.3. 等主库位点方案；
- 从库执行select master_pos_wait(file, pos[, timeout])file和pos指的是主库上的文件名和位置；timeout为正整数N表示最多等待N秒
- 如果执行期间，备库同步线程发生异常，则返回NULL；
- 如果等待超过N秒，就返回-1；
- 如果刚开始执行的时候，就发现已经执行过这个位置了，则返回0。
- 正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置
- 具体逻辑：
- 事务更新完成后，马上执行show master status得到当前主库执行到的File和Position；
- 选定一个从库执行查询语句；
- 在从库上执行select master_pos_wait(File, Position, 1)；
- 如果返回值是>=0的正整数，则在这个从库执行查询语句；
- 否则，到主库执行查询语句。
- 如果所有的从库都延迟超过1秒了，那查询压力不就都跑到主库上了吗？确实是这样。但是，按照不允许过期读的要求，就只有超时放弃或者转到主库查询。具体怎么选择，就需要做好限流策略
37.2.4. 等GTID方案
select wait_for_executed_gtid_set(gtid_set, 1);等待直到这个库执行的事务中包含传入的gtid_set，返回0；超时返回1
MySQL5.7.6版本允许在执行完更新类事务后，把这个事务的GTID返回给客户端，流程就变成了
- 事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1
- 选定一个从库执行查询语句
- 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)
- 如果返回值是0，则在这个从库执行查询语句
- 否则，到主库执行查询语句。等待超时后是否直接到主库查询，需要业务开发同学来做限流考虑。
如何让MySQL在执行事务后，返回包中带上GTID呢？将session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可。其实，MySQL并没有提供这类接口的SQL用法，是提供给程序的API(https://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html)
如果使用GTID等位点的方案做读写分离，在对大表做DDL的时候会怎么样
- 假设，这条语句在主库上要执行10分钟，提交后传到备库就要10分钟（典型的大事务）。那么，在主库DDL之后再提交的事务的GTID，去备库查的时候，就会等10分钟才出现。这样，这个读写分离机制在这10分钟之内都会超时，然后走主库。这种预期内的操作，应该在业务低峰期的时候，确保主库能够支持所有业务查询，然后把读请求都切到主库，再在主库上做DDL。等备库延迟追上以后，再把读请求切回备库。
- 使用gh-ost方案来解决
38. 其他
38.1. mysql自增id用完怎么办?
server层 xid 8字节(0-2^64-1)达到上限后会归0重新递增（为了关联InnoDB事务和server层）MySQL在内存维护了一个全局变量global_query_id，每次执行语句的时候将它赋值给Query_id，然后给这个变量加1。还会给事务第一条语句的xid设置为Query_id，M重启后xid会重置为0并重新生成binlog
InnoDB层 trx_id 6字节(0-2^48-1)达到上限后会归0重新递增.InnoDB内部维护了一个max_trx_id全局变量，每次需要申请一个新的trx_id时，就获得 max_trx_id的当前值，然后将max_trx_id加1。除了只读事务InnoDB并不会分配trx_id（select for update不是只读事务）外，每创建一个事务，max_trx_id都会+1.只读事务不分配trx_id可以减小事务视图里面活跃事务数组的大小,可以减少trx_id的申请次数.减少了并发事务申请trx_id的锁冲突.
mysql> select trx_id,trx_mysql_thread_id from information_schema.innodb_trx;thread_id 4个字节(0-2^32-1)达到上限后会归0重新递增。系统保存了一个全局变量thread_id_counter，每新建一个连接，就将thread_id_counter赋值给这个新连接的线程变量，但不会在showprocesslist里看到两个相同的thread_id
38.2. jdbc中statement和Preparestatement的区别？
- 使用PreparedStatement更安全，解决了Sql注入的问题
38.3. MySQL监控
监控工具：Zabbix ，Lepus
第6讲问题
第8讲问题
记录数量最好用数据库计数。InnoDB是支持崩溃恢复不丢数据的。为了增加系统并发能力，先insert再update计数，同时给字段计数对应的唯一键加索引
information_schema：数据库对象信息表。比如用户表信息、列信息、权限信息、分区信息等
cluster：集群信息表
mysql：用户权限信息表
INSERT(str,x,y,instr) 将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr
LEFT(str ,x) 返回字符串 str 最左边的 x 个字符，如果第二个参数是 NULL，那么将不返回任何字符串
RIGHT(str,x) 返回字符串 str 最右边的 x 个字符，如果第二个参数是 NULL，那么将不返回任何字符串
LPAD(str,n,pad) 用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度
RPAD(str,n,pad) 用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度
REPEAT(str,x) 返回 str 重复 x 次的结果
STRCMP(s1,s2)函数：比较字符串 s1 和 s2 的 ASCII 码值的大小
RAND() 返回0到1内随机值 产生0～100随机整数 ceil(100*rand())
TRUNCATE(x,y) 返回数字x截断为y位小数的结果，不进行四舍五入
DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB()返回一个日期或时间值加上、减去一个时间间隔的时间值
- HOUR 小时 hh
- MINUTE 分 mm
- SECOND 秒 ss
- YEAR 年 YY
- MONTH 月 MM
- DAY 日 DD
- YEAR_MONTH 年和月 YY-MM
- DAY_HOUR 日和小时 DD hh
- DAY_MINUTE 日和分钟 DD hh:mm
- DAY_ SECOND 日和秒 DD hh:mm:ss
- HOUR_MINUTE 小时和分 hh:mm
- HOUR_SECOND 小时和秒 hh:ss
- MINUTE_SECOND 分钟和秒 mm:ss
UNIX_TIMESTAMP(date) 返回日期date的UNIX时间戳
FROM_UNIXTIME 返回 UNIX 时间戳的日期值
WEEK(date) 返回日期 date 为一年中的第几周
YEAR(date) 返回日期 date 的年份
HOUR(time) 返回 time 的小时值
MINUTE(time) 返回 time 的分钟值
DATE_FORMAT(date,fmt) 返回按字符串 fmt 格式化日期 date 值
- %S,%s 两位数字形式的秒（00,01,...,59）
- %i 两位数字形式的分（00,01,...,59）
- %H 两位数字形式的小时，24 小时（00,01,...,23）
- %h,%I 两位数字形式的小时，12 小时（01,02,...,12）
- %k 数字形式的小时，24 小时（0,1,...,23）
- %l 数字形式的小时，12 小时（1,2,...,12）
- %T 24 小时的时间形式（hh:mm:ss）
- %r 12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
- %p AM 或 PM
- %W 一周中每一天的名称（Sunday,Monday,...,Saturday）
- %a 一周中每一天名称的缩写（Sun,Mon,...,Sat）
- %d 两位数字表示月中的天数（00,01,...,31）
- %e 数字形式表示月中的天数（1,2，...,31）
- %D 英文后缀表示月中的天数（1st,2nd,3rd,...）
- %w 以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
- %j 以 3 位数字表示年中的天数（001,002,...,366）
- %U 周（0,1,52），其中 Sunday 为周中的第一天
- %u 周（0,1,52），其中 Monday 为周中的第一天
- %M 月名（January,February,...,December）
- %b 缩写的月名（January,February,...,December）
- %m 两位数字表示的月份（01,02,...,12）
- %c 数字表示的月份（1,2,...,12）
- %Y 4 位数字表示的年份
- %y 两位数字表示的年份
- %% 直接值“%”