MySQL面试题十八讲

文章目录

• • • 一、B树、B-树、B+树
    • 二、为什么要引入B+树
    • 三、索引
    • 四、InnoDB行格式
    • 五、数据页结构
    • 六、InnoDB和MyISAM
    • 七、Buffer Pool
    • 八、SQL语句执行流程
    • 九、SQL语句执行顺序
    • 十、mysql执行计划
    • 十一、慢查询SQL优化
    • 十二、mysql锁的类型
    • 十三、mysql事务锁
    • 十四、事务ACID和隔离级别
    • 十五、MVCC
    • 十六、mysql主从同步
    • 十七、mysql导入导出
    • 十八、mysql备份恢复

一、B树、B-树、B+树

• B树

  1.二叉搜索树，即排好序的二叉树。

• B-树

  1.排好序的二叉树。

  2.节点可以存放多个元素。

• B+树

  1.排好序的二叉树。

  2.节点可以存放多个元素。

  3.叶子节点之间有双向指针。

  4.非叶子节点同样存在叶子点上，冗余存储。

二、为什么要引入B+树

innodb存储引擎存取数据的最小单位是页。

最主要的目的是减少磁盘I/O（一次性从磁盘中存取更多数据）。

• 局部性原理

  虽然当前程序可能只需要某些数据，但是计算机认为很可能接下来是需要该数据相邻的其他数据，所以为了减少磁盘I/O，就会多取一些数据（OS是4Kb，mysql的InnoDB是16Kb）放到内存中去。

插入数据的时候就会按照主键进行排序（影响插入性能，提高查询性能；所以最好用自增列作为主键）。

show global status like '%page%size%';
show index from tableName;

页示意图

1.为什么要有页目录？

用户数据以链表形式顺序存储，链表的查询性能慢，查询如何提高性能→以空间换时间，页目录。

2.为什么要非叶子节点？

页以链表形式存储，链表的查询性能慢，如果没有非叶子节点，还是要遍历所有页。

所以为了避免全表扫描，加入非叶子节点。在非叶子节点存储主键索引，在叶子节点存储用户数据，叶子节点之间使用双向指针，从而提高查询性能。这就是B+树。

主键索引的叶子节点存储了用户数据，索引即数据，数据即索引。联合索引（又叫辅助索引）的叶子节点存储的主键值。

由索引生成B+树，要求查询必须符合最左前缀原则。

create table t1(a int primary key,b int,c int,d int,e varchar(20))engine=InnoDB;
show index from t1;

insert into t1 values(4,2,1,1,'d');
insert into t1 values(1,1,1,1,'a');
insert into t1 values(8,8,8,8,'h');
insert into t1 values(2,2,2,2,'b');
insert into t1 values(3,3,2,2,'c');
insert into t1 values(7,4,5,5,'g');
insert into t1 values(6,6,4,4,'f');

## innoDB存储引擎会自动排好序，MyIASM则不会。
select * from t1;

## 主键索引
explain select * from t1 where a=1;
explain select * from t1 where a>1;
## 普通字段
explain select * from t1 where c=1;
## 联合索引
create index idx_t1_bcd on t1(b,c,d);
explain select * from t1 where b=1;
explain select * from t1 where b>1;   #索引+7次回表  不如直接全表扫描
explain select * from t1 where b>6;   #索引+2次回表

explain select b from t1;
explain select b from t1 order by b,c,d;

explain select * from t1 where a=1;
explain select * from t1 where a='1';
explain select * from t1 where e=1;
explain select * from t1 where e='1';

三、索引

索引定义：索引是帮助MySQL高效获取数据的排好序的数据结构。

索引的分类：
普通索引：允许被索引的数据列包含重复的值。
唯一索引：可以保证数据记录的唯一性
主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY来创建。
联合索引：索引可以覆盖多个数据列，如像 INDEX( columna, columnb)索引。
全文索引：通过建立倒排索引，可以极大的提升检素效率解决判断字段是否包含的可题，是目前搜索引擎使用的一种关键技术。可以通过 ALTER TABLE table name ADD FULLTEXT( column);来创建全文索引。

索引的优势：
索引可以极大的提高数据的查询速度。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

优势的劣势：
会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。
索引需要占物理空间，除了数据表占数据空间之外，每个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集素引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。

• 索引的基本原理
  索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
  索引的原理：就是把无序的数据变成有序的查询。
  1、把创建了索引的列的内容进行排序。
  2、对排序结果生成倒排表。
  3、在倒排表内容上拼上数据地址链。
  4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

• 聚簇索引和非聚簇索引

  都是B+树的数据结构。

  聚簇索引（InnoDB主键）：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的。即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上。
  非聚簇索引（MyIasm主键）：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据。

  聚簇索引的优势
  1、査询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次査询（非覆盖索引的情况下）效率要高。
  2、聚族索引对于范围查询的效率很高，因为其数据是按照大小排好列的。
  3、聚簇索引适合用在排序的场合，非聚簇索引则不适合。

  聚簇索引的劣势
  1、维护索引很昂贵，特别是插入新行或者主键被更新导至要进行分页( page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过 OPTIMIZE TABLE 来优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片。
  2、如果表使用UUID(随机ID)作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_ increment作为主键。
  3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点也会占用更多的物理空间。

  如果涉及到大数据量的排序、全表扫描、 count之类的操作的话，这些操作是需要在内存中完成的。这时候还是MyISAM占优势些，因为索引所占空间小。

• 索引的设计原则

  基本目标：查询更快、占用空间更小。
  1.适合索引的列是出现在 where子句中的列，或者连接子句中指定的列。
  2.基数较小的列，索引效果较差，没有必要在此列建立索引。
  3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
  4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
  5.定义有外键的数据列一定要建立索引。
  6.更新频繁字段不适合创建索引。
  7.若是不能有效区分数据的列，则不适合做索引列(如性別，男女未知，最多也就三种，区分度实在太低)。
  8.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加（a,b)的索引，那么只需要修改原来的索引即可。
  9.对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
  10.对于定义为text、 Image和bit的数据类型的列不要建立索引。

• 为什么推荐使用自增整型主键而不是UUID？

  1、如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的row_id作为隐含的聚集索引(row_id随着行记录的写入而主键递增)。

  2、如果表使用自增主键
  那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页

  3、如果使用非自增主键（例如UUID或身份证号或学号等）
  由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

• 最左前缀优化原则

  当有多列索引时，mysql底层使用索引查询的时候是从左往右进行匹配，并且是需要从左往右连续的。

  多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的。 所以如果不是在前面列的基础上而是但看后面某一列，索引是失效的。

• 覆盖索引

  如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。就是select的数据列只需从索引中就能够取得，不必从数据表中读取，换句话说查找select 的列必须被where的列(索引)覆盖，就能使用到索引覆盖。

  数据量大的时候，二级索引的覆盖索引会比主键查询快。因为主键索引的记录的数据量很大，二级索引的数据小，并且索引覆盖，不需要回表。

• 索引下推
  索引下推一般可用于所求查询字段（select列）不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。比如like ‘hello%’ and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度。这就是索引下推。

四、InnoDB行格式

一行记录可以以不同的格式存在InnoDB中，行格式分别是 Compact、 Redundant、 Dynamic 和 Compressed行格式。

可以在创建或修改表的语句中指定行格式：

CREATE TABLE表名（列的信息） ROW FORMAT=行格式名称
ALTER TABLE表名 ROW FORMAT=行格式名称

• COMPACT行格式

  | 变长字段长度列表 | NULL标志位 | 记录头信息 | 列1数据 | 列2数据 | … |

  记录的额外信息：这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为三类，分别是：
  1、变长字段长度列表
  2、NULL值列表
  3、记录头信息

  变长字段长度列表
  MySQL支持一些变长的数据类型，比如 VARCHAr(M）、 VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成个变长字段长度列表。

  CHAR是一种固定长度的类型， VARCHAR则是一种可变长度的类型 。
  VARCHAr(M)，M代表最大能存多少个字符。（MYSQL5.0.3以前是字节，以后就是字符）。

  (root@localhost)[test]> CREATE TABLE varchar_size_demo(c VARCHAr(65535)) CHARSET=ascii ROW_FORMAT=Compact;
  ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  ## 因为行记录的边长字段和NULL标志位共需要占用三个字节。
  (root@localhost)[test]> CREATE TABLE varchar_size_demo(c VARCHAr(65532)) CHARSET=ascii ROW_FORMAT=Compact;
  ## 最大可以存65532个字节，而页默认大小16Kb，所以行溢出。
  ## COMPACT和Dynamic的行溢出实现的方式是不一样的。这也是它俩的区别。
  

  NUL值列表
  Compact行格式会把可以为NULL的列统一管理起来，存一个标记为在NUL值列表中，如果表中没有允许存储NUL的列，则NUL值列表也不存在。
  二进制位的值为1时，代表该列的值为NULL。
  二进制位的值为0时，代表该列的值不为NUL。

  记录头信息
  除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

五、数据页结构

六、InnoDB和MyISAM

MyISAM

• 不支持事务，但是每次查询都是原子的；
• 支持表级锁，即每次操作是对整个表加锁；
• 存储表的总行数；
• 一个 MYSAM表有三个文件：索引文件、表结构文件、数据文件；
• 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅助索引与主索引基本一致，但是辅助索引不用保证唯一性。

InnoDB

• 支持ACID的事务，支持事务的四种隔离级别;
• 支持行级锁及外键约束：因此可以支持写并发;
• 不存储总行数；
• 一个 Innodb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制），也有可能为多个(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小
  的限制；
• 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅助索引的数据域存储主键的值；因此从辅助索引查找数据，需要先通过辅助索引找到主键值，再访问主键索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件需要的大调整。

七、Buffer Pool

sql语句不是直接执行在磁盘上进行磁盘I/O的，而是通过buffer pool缓冲区与磁盘进行数据的CRUD。如何保证高效？这就需要高超的缓冲区设计。

• 三个链表

  free链表：管理空闲页（通过基节点+控制块）。比如select操作。

  flush链表：管理脏页（通过基节点+控制块）。比如update操作。

  LRU链表：最近最少淘汰的页链表（通过基节点+控制块）。

• 升级版LRU链表

  执行select * from table进行全表扫描，可能更新全部LRU链表。如何避免？

  可以认为LRU链表的头部都是热数据，尾部是冷数据。默认将LRU链表分为：热数据：冷数据=5:3。

  全表扫描时，将数据页直接放在冷数据链，当两次访问某些冷数据页的时候大于1s时，就把冷数据页放到热数据页。

• 重做日志redo log

  后台线程是如何刷新脏页到磁盘，才能避免数据丢失？

  对于每一个sql语句，都会生成redo log，待redo log持久化之后，再反馈sql执行结果。所以redo log就能保证数据的一致性。

  为什么不直接修改页，要去持久化redo log？因为页的刷盘是随机I/O，redo log的刷盘是顺序I/O的。

  show variables like '%innodb_page_size%';  # 页大小 默认16k
  show variables like '%innodb_log_file_size%';  # redo log日志文件（ib_logfile0）大小 默认48M，可以把值调大点，避免经常脏页刷盘（重启可能会比较慢）。
  

  redo log的两个文件（ib_logfile0和ib_logfile1）都满了之后，继续写入redo log会触发checkpoint，这时自动进行脏页刷盘，所有脏页刷盘成功之后，两个文件就清空，这时可以继续写入redo log。

• redo log持久化的时机

  事务提交的时候进行持久化。

  show variables like '%innodb_flush_log_at_trx_commit%';
  # 0表示事务提交时，不立即对 redo log 进行持久化，这个任务交给后台线程去做。
  # 1表示事务提交时，立即把 redo log 进行持久化。
  # 2表示事务提交时，立即将 redo log 写到操作系统的缓冲区，并不会直接将 redo log 进行持久化，这种情况下，如果数据库挂了，但是操作系统没挂，那么事务的持久性还是可以保证的。
  

• redo log和binlog的区别

  binlog是mysql数据库层面的日志，记录的是sql，主要用于主从。

  redo log是innodb引擎层面的日志，记录的数据修改的物理地址，所以更快，主要重做。

• 上图各组件简介

  Buffer Pool：缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

  Log Buffer： 是一块内存区域用来保存要写入磁盘上的日志文件的数据。 Log Buffer的大小由innodb_log_buffer_size变量定义，默认大小为16MB。Log Buffer的内容会定期刷到磁盘上。大的Log Buffer让较大事务能够运行，而无需在事务提交之前将redo log中的数据写入磁盘。

  Change Buffer： 将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。

  System Tablespace： 系统表空间包含InnoDB数据字典(元数据以及相关对象)并且doublewrite buffer,change buffer,undo logs的存储区域。系统表空间也同时包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。 由一个或者多个数据文件组成。 默认情况下，1个名ibdata1的系统数据文件在MySQL的data目录下被创建，数据文件的大小和数量配置取决于innodb_data_file_path。

  Doublewrite Buffer Files： 在Innodb将数据页写到数据存储文件之前，存储从Innodb缓存池刷过来的数据页。且只有将数写入doublewriter buffer后，Innodb才会进行数据页的物理存储。如果在数据页写盘时发生操作系统、存储系统、或者myql进程中断，Innodb可以从doublewriter buffer存储中找回丢失的数据页备份。可以通过配置innnodb_doublewrite=0关闭。

  Undo Tablespaces： 在MySQL5.6中开始支持把undo log分离到独立的表空间，并放到单独的文件目录下。这给部署不同IO类型的文件位置带来便利，对于并发写入型负载，可以把undo文件部署到单独的高速SSD存储设备上。

  Redo Log： redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且能恢复到最后一次提交的位置) 。

  File-Per-Table Tablespaces：单独表空间。设置innodb_file_per_table=ON可以把每个表数据单独保存 。

  General Tablespaces：是一种共享的 innodb 表空间，有点类似系统表空间 ibdata1 。可以在一个表空间数据文件下存储多张表，即使这些表来自不同的 schame。

  Temporary Tablespaces： 临时表空间。 比如：语句中有order by或是group by，还有当我们要修改表的一些属性，比如增加加索引或是添加字段，这些都有可能使用到临时表，我们通过explain这个命令输出的extra列可以看到这样的提示using temporary。

八、SQL语句执行流程

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器: 没有命中缓存的话，SQL 语句就会经过分析器：第一步词法分析，第二步语法分析。分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。 比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
• 执行器: 执行语句， 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。 然后从存储引擎返回数据。

• MySQL 主要分为 Server 层和引擎层。

• Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。

• 引擎层主要负责数据的存储和读取 。它是插件式的，目前主要包括：InnoDB,MyISAM,Memory 等。

• SQL 等执行过程分为两类：

  一类对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎。

  一类对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit。

九、SQL语句执行顺序

sql语句定义的顺序

(1) SELECt (2)DISTINCT
(3) FROM 
(4)  JOIN 
(5)         ON 
(6) WHERe 
(7) GROUP BY 
(8) WITH {CUBE|ROLLUP}
(9) HAVINg 
(10) ORDER BY 
(11) LIMIT 

sql语句执行的顺序

(8) SELECt (9)DISTINCT
(1) FROM 
(3)  JOIN 
(2)         ON 
(4) WHERe 
(5) GROUP BY 
(6) WITH {CUBE|ROLLUP}
(7) HAVINg 
(10) ORDER BY 
(11) LIMIT 

解释：最先执行的是FROM操作，最后执行的是LIMIT操作。每个操作都会产生一个虚拟表，该虚拟表作为下一个操作的输入。

(1) FROM:对FROM子句中的左表和右表执行笛卡儿积，产生虚拟表VT1;
(2) ON: 对虚拟表VT1进行ON筛选，只有那些符合的行才被插入虚拟表VT2;
(3) JOIN: 如果指定了OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN)，那么保留表中未匹配的行作为外部行添加到虚拟表VT2，产生虚拟表VT3。如果FROM子句包含两个以上的表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3，直到处理完所有的表;
(4) WHERe: 对虚拟表VT3应用WHERe过滤条件，只有符合的记录才会被插入虚拟表VT4;
(5) GROUP By: 根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5;
(6) CUBE|ROllUP: 对VT5进行CUBE或ROLLUP操作，产生表VT6;
(7) HAVINg: 对虚拟表VT6应用HAVINg过滤器，只有符合的记录才会被插入到VT7;
(8) SELECt: 第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中;
(9) DISTINCT: 去除重复，产生虚拟表VT9;
(10) ORDER BY: 将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10;
(11) LIMIT: 取出指定街行的记录，产生虚拟表VT11，并返回给查询用户

十、mysql执行计划

执行计划就是sq的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数。

(root@localhost)[test]> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 1.id

  是一个有顺序的编号，是查询的顺序号，有几个 select就显示几行。id的顺序是按 select出现的顺序增
  长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。

• 2.select_type

  表示查询中每个 select子句的类型：

  SIMPLE:表示此查询不包含 UNIOn查询或子查询。
  PRIMARY:表示此査询是最外层的查询（包含子查询）。
  SUBQUERY:子查询中的第一个 SELECT。
  UNIOn:表示此查询是 UNIOn的第二或随后的查询。
  DEPENDENT UNIOn: UNIOn中的第二个或后面的查询语句，取決于外面的查询。
  UNIOn RESULT, UNIOn的结果。
  DEPENDENT SUBQUERY:子查询中的第一个 SELECT,取決于外面的查询。即子查询依赖于外层查询的结果。
  DERIVED:行生，表示导出表的 SELECT(FROM子句的子查询)。

• 3.table

  表示该语句查询的表

• 4.partitions

  表示该语句查询表的分区信息。

• 5.type

  优化sql的重要字段，也是我们判断sq性能和优化程度重要指标。它的取值类型范围：
  const:通过索引ー次命中，匹配一行数据。
  system:表中只有一行记录，相当于系统表。
  eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。
  ref:非唯一性索引扫描返回匹配某个值的所有。
  range:只检素给定范围的行，使用一个索引来选择行，一般用于 between、<、>。
  index:只遍历索引树，遍历所有的索引。
  ALL:表示全表扫描，这个类型的查询是性能最差的查询之ー。那么基本就是随着表的数量増多，执行效率越
  慢。

  执行效率：
  ALL< index< range< ref< eq_ref< const< system。最好是避免ALL和 index。

• 6.possible_keys

  表示Mysq在执行该sq语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。

• 7.key

  此字段是mysq在当前查询时所真正使用到的索引。它是 possible_keys的子集。

• 8.key_len

  表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标。

• 9.ref

  索引的字段名或者常量或者NULL。

• 10.rows

  查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大。

• 11.filtered

  返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集很少。

• 12.Extra

  using filesort:表示mysql对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询cpu资源消耗大，延时大。
  using index:覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
  using temporary:查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化。
  using where:sql使用了 where过效率较高。

十一、慢查询SQL优化

在业务系统中，除了使用主键进行的查询，其它的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。
慢查询的优化首先要搞明白慢的原因是什么？是load了不需要的数据列？是查询条件没有命中索引？还是数据量太大？
所以优化也是针对这三个方向来的。

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情況，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

十二、mysql锁的类型

基于锁的粒度分类：行级锁(INNODB)、表级锁 (NNODB、 MYISAM)、页级锁（BDB引擎）、记录锁、间隙锁、临键锁。

基于锁的属性分类：共享锁、排他锁。

基于锁的状态分类：意向共享锁、意向排它锁。

• 共享锁( Share Lock)
  共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避兔出现重复读的问题。
• 排他锁( exclusive Lock)
  排他锁又称写锁，简称X锁：当一个事务为数加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。
• 表锁
  表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访
  特点：粒度大，加锁简单，容易冲突；
• 行锁
  行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问
  特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。
• 记录锁( Record Lock)
  记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。
  精准条件命中，并且命中的条件字段是唯一索引
  加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。
• 页锁
  页级锁是 MYSQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级锁冲突少，但速度慢。
  所以取了折中的页级锁，一次锁定相邻的一组记录。
  特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
• 间隙锁（ Gap Lock ）
  属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成个区间，遵循左闭右开原则。范围查询并且查询未命中记录，查询条件必须命中索引，间隙锁只会出现在 REPEATABLE_READ（可重复读）的事务级别中。
  触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生：在同一个事务里，A事务的两次査询出的结果会不一样。
  比如表里面的数据按切ID为1,4,5,7,10,那么会形成以下几个间隙区间，[-n,1)区间，[1,4)区间，[7,10)区间，[10,n)区间。(-n代表负无穷大，n代表正无穷大)
• 临建锁(Next-Key Lock)
  也于行锁的一种，并且它是InnoDB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合（左闭右闭），临键锁会把查询出来的记录锁住，同时也会把该范围査询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。
  触发条件：范围查询并命中，查询命中了索引。
  结合记录锁和间隙锁的特性，临键锁避免了在范围査询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

• 意向共享锁
  当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。
• 意向排他锁
  当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

十三、mysql事务锁

• 读操作
  对于普通 SELECt语句， INNODB不会加任何锁。

• select … lock in share mode
  将查找到的数据加上一个S锁，允许其他事务继续获取这些记录的S锁，不能获取这些记录的X锁（会阻塞）
  使用场景：读出数据后，其他事务不能修改，但是自己也不一定能修改，因为其他事务也可以使用 select … lock in share mode。

• select … for update
  将查找到的数据加上ー个X锁，不允许其他事务获取这些记录的S锁和X锁。
  使用场景：读出数据后，其他事务即不能写，也不能加读锁，那么就导致只有自己可以修改数据。

• 写操作
  DELETE：刪除一条数据时，先对记录加X锁，再执行删除操作。
  INSERT：插入一条记录时，会先加”隐式锁“来保护这条新插入的记录在本事务提交前不被别的事务访问到。
  UPDATE：如果被更新的列，修改前后没有导致存储空间变化，那么会先给记录加X锁，再直接对记录进行修改；如果被更新的列，修改前后导致存储空间发生了变化，那么会先给记录加X锁，然后将记录删掉，再Insert一条新记录。

  隐式锁：一个事务插入一条记录后，还未提交，这条记录会保存本次事务id，而其他事务如果想来对这个记录加锁时会发现事务id不对应，这时会产生X锁，所以相当于在插入一条记录时，隐式的给这条记录加了一把隐式X锁。（类似乐观锁）

## 查看锁
pager
pager less
set global innodb_status_output_locks=1

锁：对共享资源进行并发访问，提供数据的完整性和一致性。

Lock和 latch的区别
Lock是数据库层面的锁，锁住的行记录。
latch（就是mysql代码的锁，DBA不用关注）是内存层面控制并发访问数据结构的锁，锁住的临界资源。
死锁：Lock由于有死锁检测与锁超时，不会死锁。latch存在死锁，需要在代码层面保证无死锁。

innodb锁管理
使用锁位示图（仅仅占用约不到30字节，可以忽略其大小），通过(space,pageno)定位页，通过bitmap（即heap no，页中必有min，max两个值，min的heap no=0，max的heap no=1）来定位页中的哪一行记录上锁。

演示锁举例(排它锁、读写锁、共享锁、IS、IX)

create table l( a int primary key, b int,c int,d int, key(b));

show engine innodb mutex;
show VARIABLES like "%tx_isolation%";
set tx_isolation='REPEATABLE-READ';
set tx_isolation='read-COMMITTED';

insert into l values( 2, 4,  6,  8)
insert into l values( 4, 6,  8, 10)
insert into l values( 6, 8, 10, 12)
insert into l values( 8, 10,12, 14)
alter table l add unique key idx_c(c);
show create table l;
select * from l ;
select * from l where a=2 for update;
select * from l where a=2 lock in share mode;
show engine innodb status;  # → 4 lock struct(s), heap size 1136, 2 row lock(s)          
show VARIABLES like "%innodb%lock%wait%";
set innodb_lock_wait_timeout=3;  # 默认50s

意向锁
用于实现多粒度级别的锁（库、表、页、记录都可以加锁。对于mysql中来说只能在表、记录上加锁）
揭示下一层级请求的锁类型
IS:事务想要获得一张表中某几行的共享锁
IX:事务想要获得一张表中某几行的排他锁
INNODB存储引擎中意向锁都是表锁（MySQL对某行记录进行加锁，需要先对表加意向锁）

自增锁

create table ai ( a int auto_increment, b int, key(b,a))
create table ai ( a int auto_increment, b int, key(a,b))
insert into ai(a, b)select NULL, id from sbtest.sbtest1 Limit 10000
drop table ai;
show variables like '%innodb%auto%';
show variables like '%binlog_format%';
set innodb_autoinc_lock_mode=2
set binlog_format=row

锁与隔离级别
要想达到真正的ACID，就必须要设置SERIALIZABLE。但是在MySQL中，RR就可以解决99%的幻读问题。
为了提高并发性，务必设置为READ-COMMITTED。不可重复读和幻读在现实生活中并没有什么影响。

锁算法
1.Record Lock：单个行记录上的锁（Oracle只有这个锁）。这是RC级别下的锁（但是RC下也有gap lock）。
2.Gap Lock：锁定一个范围，但不包含记录本身
3.Next-key Lock：Gap Lock+ Record Lock,锁定一个范围（锁范围大，以解决99%的幻读），并且锁定记录本身。这是RR级别下的锁（但有些情况可以优化为记录锁）。
锁住的是索引。锁主键，则是锁主键；锁二级索引，则是锁二级索引范围+锁主键；锁普通值，则是锁二级索引联合主键的范围+锁主键

插入意向锁：gap lock下也能提高并发插入性能。

锁升级：一个事务持有5000行锁升级到表锁。

锁重用：同一把锁可以共享。

显式锁：就是正常的锁。

隐式锁：不创建锁对象，需要的时候转为显式锁。

死锁演示（ABBA死锁：注意执行顺序：锁超时和死锁）

#client1

begin;
select * from z where b=1 for update;
select * from l where a=2 for update;

#client2

begin;
select * from l where a=2 for update;
select * from z where b=1 for update;

#查看锁、死锁
set global innodb_status_output_locks=1
show engine innodb statusG

死锁查看

## 方法一：
show OPEN TABLES where In_use > 0;
show processlist;
kill PID;
## 方法二：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
SELECt * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECt * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
kill PID;

解决方案：

1.死锁检测（通过锁等待图（由事务等待链表和锁信息链表获取）判断有没有回路），通过undo事务的量决定对那个事务进行回滚。

2.锁超时

show variables like '%innodb_status_output_locks%';
use sys;
select * from innodb_lock_waitsG
show VARIABLES like "%innodb%dead%";
set GLOBAL innodb_print_all_deadlocks=1   # 将死锁记录到日志中

淘宝对mysql提的需求(秒杀场景性能有些许提升，有点鸡肋),不检查死锁。只能通过锁超时解决死锁，且不回滚。
set GLOBAL innodb_deadlock_detect=0;

常见死锁，购物车死锁：多人并发加购商品，更新库存，常常会引发死锁。
解决方案：业务层：排序skuid，消息队列；数据库层：开启连接池。

锁高级
mysql8.0锁的两个新特性

select * from l where a=2 for update nowait;
select * from l where a=2 for update skip locked;

十四、事务ACID和隔离级别

事务基本特性

• 原子性指的是一个事务中的操作要么全部成功，要么全部失败。
• 一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。
• 隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。
• 持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

事务隔离级别

• read uncommit读未提交，可能会读到其他事务未提交的数据，也叫做脏读。
• read commit读已提交，两次读取结果不一致，叫做不可重复读。
• repeatable read可重复复读，这是mysq的默认级別，就是每次读取结果都一样，但是有可能产生幻读。
• serializable串行化，一般是不会使用的，它会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

ACID如何保证

A原子性由 undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。
C一致性由其它三大特性保证、同时程序代码要保证业务上的一致性。
I隔离性由MVCC来保证。
D持久性由内存 + redo log 来保证，mysq修改数据同时在内存和 redo log 记录这次操作，宕机的时候可以从redo log恢复。

redo log和binlog的一致性流程：InnoDB存储引擎 redo log写盘，InnoDB事务进入 prepare状态。如果前面 prepare成功，binlog写盘，再继续将事务日志持久化到binlog，如果持久化成功，那么 InnoDB事务则进入 commit状态(在redo log里面写一个 commit记录)。

redo log的刷盘会在系统空闲时进行。

十五、MVCC

读已提交可以读到其它事务已提交的数据，可重复读却读不到，如何做到的→MVCC版本链控制，提高并发读。

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据，版本链。
MVCC只在 READ COMMITTED和 REPEATABLE READ两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容，因为 READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE则会对所有读取的行都加锁。

聚簇索引记录中有两个必要的隐藏列：
trx_id:用来存储每次对某条緊簇索引记录进行修改的时候的事务id.
roll_pointer:每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个 roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。（注意插入操作的undo日志没有这个属性，因为它没有者版本）
READ COMMITTED 和 REPEATABLE READ的区别就在于它们生成 Readview的策略不同。

开始事务时会创建 readview, readview维护当前活动的事务id，即未提交的事务id，排序生成一个数组访问数据，获取数据中的事务id(获取的是事务d最大的记录)，对比 rearview：如果在 readview的左边(t比 rearview都小)，可以访可（在左边意味着该事务已经提交）；如果在 readview的右边(比 rearview都大)或者就在 readview中，不可以访问，说明该事务id未提交，此时就要通过 roll_pointer获取上一版本的事务，重新对比(在右边意味着该事务在 readview生成之后出现，在 readview中意味着该事务还未提交)

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的 readview，而可重复读隔离级别则在第一次读的时候生成一个readview，之后的读都复用之前的 readview。

这就是Mysq的MVCC，通过版本链，实现多版本，可并发读-写或写-读。通过 readview生成策略的不同来实现不同的隔离级别。

十六、mysql主从同步

• mysql主从同步的过程
  mysql的主从复制中主要有三个线程——Master中的一条线程和 Slave中的两条线程： master( binlog dump thread)、slave(I/O thread、SQL thread)。
  主节点 binlog：主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的个文件。
  主节点binlog dump线程，当 binlog有变动时， log dump线程读取其内容并发送给从节点。
  从节点I/O线程，接收 binlog内容，并将其写入到 relay log文件中。
  从节点的SQL线程，读取 relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性。

  主从节点使用binlog文件+ position偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position的位置发起同步。

由于mysq默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样可能会产生一个问题：假设主库挂了，从库写入日志处理失败，这时候从库升为主库后，日志就丢失了，破坏的数据一致性。如何解决该问题：由此产生两个概念。
全同步复制
主库写入 binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成

• mysql主从同步延时

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高，slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多，另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。

解决方案：
1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
4.不同业务的mysql物理上放在不同机器，分散压力。
5.使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然会变小。
6.使用更加强劲的硬件设备

• mysql读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于前面我们学习过的rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

十七、mysql导入导出

• 导出

  在某些情况下，为了一些特定的目的，经常需要将表里的数据导出为某些符号分割的纯数据文本，而不是 SQL 语句，因为LOAD DATA 的加载速度比普通的 SQL 加载要快 20 倍以上

  方法 1：使用 SELECT …INTO OUTFILe …命令来导出数据。

  SELECT * FROM tablename INTO OUTFILe ‘target_file’ [option];

  方法 2：用 mysqldump 导出数据为文本。

  mysqldump –u username –T target_dir dbname tablename [option];

• 导入

  和导出类似，导入也有两种不同的方法，分别是 LOAD DATA INFILE…和mysqlimport，它们的本质是一样的，区别只是在于一个在 MySQL 内部执行，另一个在 MySQL 外部执行。

  方法 1： 使用“LOAD DATA INFILE…”命令导入数据。

  LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option];

  方法 2：用 mysqlimport 来实现导入数据。

  mysqlimport –u root –p*** [–LOCAL] dbname order_tab.txt [option];

show tables;
show variables like '%secure_file_priv%';  # 默认NULL
[mysqld]
secure_file_priv=/tmp
## 导出
select * into outfile 'order.dat' from orders;
select * into outfile 'order.dat' from orders where o_orderkey<10000;
## 导入
create table ordersbake like orders;
load data '/tmp/order.dat' into table ordersbake;

MySQL5.6开始支持独立表空间的导入导出，操作步骤：

1.目的服务器： ALTER TABLE t DISCARD TABLESPACE;
2.源服务器： FLUSH TABLES t FOR EXPORT;
3.从源服务器上拷贝t.ibd,t.cfg文件到目的服务器;
4.源服务器： UNLOCK TABLES;
5.目的服务器： ALTER TABLE t import TABLESPACE;

十八、mysql备份恢复

备份类型：热备、冷备、温备（针对MyIasm）
热备工具：（逻辑备份）mysqldump、mydumper；（物理备份）ibbackup、xtrabackup；
逻辑备份：备份逻辑内容、备份文件小、恢复速度慢、I/O负载大、缓冲池污染
物理备份：备份物理内容、备份文件大、恢复速度快、I/O负载大

• mysqldump

  单线程，备份慢；恢复也慢；要么全恢复，要么不恢复。

  1.备份所有数据库: mysqldump -u root -p --all-databases > all_database_sql

  2.备份mysql数据库：mysqldump -u root -p --databases mysql > mysql_database_sql

  3.备份指定的多个数据库：mysqldump -u root -p --databases db1 db2 db3 > bak.sql

  4.备份mysql数据库下的user表：mysqldump -u root -p mysql user > user_table

  mysql -u root -p < all_database_sql，这里不需要指定库，因为是全部数据库

  mysql -u root -p mysql < mysql_database_sql #这里就需要指定是mysql库

  my.cnf添加配置如下

  [mysqldump]
  single-transaction
  master-data=1 ##获取文件的二进制位置，将库锁成只读。

  mysqldump --single-transaction --databases bank > bank.sql
  mysqldump --single-transaction --all-databases > alldatabase.sql
  mysql bank.sql 
  #备份并压缩 需要开启binlog
  show variables like '%log%bin%';
  set global log_bin=1;
  mysqldump --single-transaction --master-data=1 -R --triggers -E -B emp | gzip -c > emp.tgz
  #恢复
  gunzip < emp.tgz | mysql
  

  mysqldump备份原理（重要）

  1、开启RR事务的隔离级别。开启ftwrl。一致性备份：START TRANSACTION
  2、备份的数据是从备份开始的时间开始备份。
  3、备份是有savepoint。备份失败可以回滚，以释放元数据锁。

  # 可以通过general_log来查看
  show variables like '%output%';
  set global log_output='table';
  set global log_output='FILE';
  show variables like '%general%';
  set global general_log=1;
  set global general_log=0;
  
  use mysql;
  select * from general_log;  #大致找出备份时的thread_id
  select thread_id,left(argument,64) from general_log where thread_id = 62 order by event_time;
  # 备份时添加参数master-data=1后再看一下备份原理，用于获取文件的二进制位置
  

• mydumper

  并行备份（基于行）；恢复也是并行的；恢复灵活，可以恢复指定表。

  git clone https://github.com/maxbube/mydumper
  #基于每张表进行备份，并自动找到二进制文件位置
  mydumper -G -E -R --trx-consistency-only -t 4 -C-B emp -o emp_backup_20210909
  #恢复
  myloader -d emp_backup_20210909 -t 8 -B emptest
  ##mydumper原理：如何并行？如何保证一致性？一张表为什么也能并行？（根据唯一索引进行分片）
  ##ftwrl后开启多个START TRANSACTION  事务，所以多个线程看到的数据都是一致的。
  flush tables with read lock;
  START TRANSACTION 
  

• xtrabackup

  xtrabackup原理（通过开启general.log来查看）

  1、首先备份表空间，然后开启线程去备份（重做）日志；当所有表空间备份完毕后，ftwrl。再show master status，将当前二进制位置保存。最后解读锁。

  2、与mysqldump区别：ftwrl时间点不一样，因此备份的数据是从备份结束的时间开始备份。

  3、xtrabackup增量备份很慢（所以基本不用），因为它要扫描全部表空间。建议基于二进制日志做增量备份。