------------------------------------------------------------
mysql有多种日志，常见的有:
错误日志（ErrorLog）
更新日志（UpdateLog）
二进制日志（Binlog）
查询日志（QueryLog）
慢查询日志（SlowQueryLog）
Binlog可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，此外Binlog是事务安全型的。
Binlog一般作用是可以用于实时备份，与master/slave主从复制结合。
一、事务ACID
⑴ 原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
⑵ 一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
⑶ 隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
⑷ 持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
二、隔离级别
脏读
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读
不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
虚读(幻读)
幻读是事务非独立执行时发生的一种现象。
四种隔离级别：
① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
③ Read committed (读已提交)：可避免脏读的发生。
④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
3、事务隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重复读（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
数据库事务隔离级别分为四种(级别递减)：
1、Serializable (串行化)：最严格的级别，事务串行执行，资源消耗最大;
2、REPEATABLE READ(重复读) ：保证了一个事务不会修改已经由另一个事务读取但未提交(回滚)的数据。避免了“脏读取”和“不可重复读取”的情况，但不能避免“幻读”，但是带来了更多的性能损失。
3、READ COMMITTED (提交读)：大多数主流数据库的默认事务等级，保证了一个事务不会读到另一个并行事务已修改但未提交的数据，避免了“脏读取”，但不能避免“幻读”和“不可重复读取”。该级别适用于大多数系统。
4、Read Uncommitted(未提交读) ：事务中的修改，即使没有提交，其他事务也可以看得到，会导致“脏读”、“幻读”和“不可重复读取”。
三、锁介绍
表级锁：开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。
行锁 和 表锁 的区别：
表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
表锁和行锁应用场景
表级锁使用与并发性不高，以查询为主，少量更新的应用，比如小型的web应用；
而行级锁适用于高并发环境下，对事务完整性要求较高的系统，如在线事务处理系统。
悲观锁 和 乐观锁
（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
四、SQL查询执行全过程解析
五、InnoDB 索引原理
MySQL表中的所有数据被存储在一个空间内，称之为表空间，表空间内部又可以分为段(segment)、区(extent)、页(page)、行(row),
InnoDB行记录格式
InnoDB提供了两种格式来存储行记录：Redundant格式、Compact格式、Dynamic格式、Compressed格式，Redudant格式是为了兼容保留的。
- 列数据：此行存储着列字段数据，Null是不占存储空间的；
- 隐藏列：事务id和回滚列id,分别占用6、7字节，若此表没有主键，还会增加6字节的rowid列。
B树与B+树
B树（B-TREE）满足如下条件，即可称之为m阶B树：
- 每个节点之多拥有m棵子树；
- 根结点至少拥有两颗子树（存在子树的情况下);
- 除了根结点以外，其余每个分支结点至少拥有 m/2 棵子树；
- 所有的叶结点都在同一层上；
- 有 k 棵子树的分支结点则存在 k-1 个关键码，关键码按照递增次序进行排列；
- 关键字数量需要满足ceil(m/2)-1 <= n <= m-1；
B+树满足如下条件，即可称之为m阶B+树：
- 根结点只有一个，分支数量范围为[2，m]
- 分支结点，每个结点包含分支数范围为[ceil(m/2), m]；
- 分支结点的关键字数量等于其子分支的数量减一，关键字的数量范围为[ceil(m/2)-1, m-1]，关键字顺序递增；
- 所有叶子结点都在同一层；
B树与B+树区别：
以m阶树为例：
- 关键字不同：B+树中分支结点有m个关键字，其叶子结点也有m个，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
- 存储位置不同：B+树非叶子节点的关键字只起到索引作用，实际的关键字存储在叶子节点，B树的非叶子节点也存储关键字。
- 分支构造不同：B+树的分支结点仅仅存储着关键字信息和儿子的指针，也就是说内部结点仅仅包含着索引信息。
- 查询不同（稳定）：B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。
聚簇索引
每个InnoDB的表都拥有一个索引，称之为聚簇索引，此索引中存储着行记录，一般来说，聚簇索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作，理解InnoDB聚簇索引是很有必要的。
聚簇索引按照如下规则创建：
- 当定义了主键后，InnoDB会利用主键来生成其聚簇索引；
- 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引；
- 如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引。
辅助索引
除了聚簇索引之外的索引都可以称之为辅助索引，与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。
索引的分类
MySQL主要的几种索引类型：1.普通索引、2.唯一索引、3.主键索引、4.组合索引、5.全文索引。
1.普通索引
是最基本的索引，它没有任何限制。
2.唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
主键索引和唯一索引的区别：
主键必唯一，但是唯一索引不一定是主键；
一张表上只能有一个主键，但是可以有一个或多个唯一索引。
4.组合索引
一个索引包含多个列，实际开发中推荐使用复合索引。
5.全文索引
FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
索引的优点缺点
优点：
（1）提高数据检索的效率，降低数据库IO成本。
（2）通过索引对数据进行排序，降低数据的排序成本，降低CPU的消耗。
缺点：
（1）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
六、索引的注意事项
1.尽量少使用模糊查询，如果要使用那么，通配符%可以出现在结尾，不能在开头。
如：name like ‘张%’ ，索引有效
而：name like ‘%张’ ,索引无效，全表查询
2：or 会引起全表扫描
3：不要使用NOT、!=、NOT IN、NOT LIKE等
4.尽量少使用select*，而是根据需求来选择需要显示的字段
5.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
6.不要在列上进行运算，这将导致索引失效而进行全表扫描
7.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作.
8、union并不绝对比or的执行效率高
我们前面已经谈到了在where子句中使用or会引起全表扫描，一般的，我所见过的资料都是推荐这里用union来代替or。事实证明，这种说法对于大部分都是适用的。
有一点不适用:如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。
1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16'' or fariqi=''2004-2-5''
用时：6423毫秒。扫描计数 2，逻辑读 14726 次，物理读 1 次，预读 7176 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-2-5''
用时：11640毫秒。扫描计数 8，逻辑读 14806 次，物理读 108 次，预读 1144 次。
9、索引有如下有点：减少服务器扫描的数据量、避免排序和临时表、将随机I/O变为顺序I/O。
可使用B+树索引的查询方式
- 全值匹配：与索引中的所有列进行匹配，也就是条件字段与联合索引的字段个数与顺序相同；
- 匹配最左前缀：只使用联合索引的前几个字段；
- 匹配列前缀：比如like 'xx%'可以走索引；
- 匹配范围值：范围查询，比如>,like等；
- 匹配某一列并范围匹配另外一列：精确查找+范围查找；
- 只访问索引查询：索引覆盖，select的字段为主键；
七、索引方式（结构）
mysql有两种所以方式：Hash和BTree。
Hash索引
所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO。
因为Hash索引比较的是经过Hash计算的值，所以在= in <=>(安全等于的时候）塔的效率是非常，但我们开发一般会选择Btree，Hash会存在如下一些缺点。
（1）Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
（2）Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值，而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
（3）Hash索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
（4）Hash索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
（5）Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
BTREE
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型。简单理解，塔就像一棵树，B-Tree索引需要从根节点到枝节点，就能才能访问到页节点的具体数据。
btree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限，最终存储引擎要么是找到对应的值，要么是该记录不存在。
B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较。如果 LIKE 的参数是一个没有以通配符起始的常量字符串的话也可以使用这种索引。
八、explain 分析sql语句
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; -- 结果： id: 1 select_type: SIMPLE -- 查询类型（简单查询,联合查询,子查询） table: user -- 显示这一行的数据是关于哪张表的 type: range -- 区间索引（在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。 possible_keys: birthday -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。 key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。 key_len: 4 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好 ref: const -- 显示哪个字段或常数与key一起被使用。 rows: 1 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。 Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using
select_type
simple： 简单的select 查询，查询中不包含子查询或者union
primary： 查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery： 在select或where 列表中包含了子查询
derived： 在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
union： 若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
union result： 从union表获取结果的select
type
这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all
对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
all： 全表扫描。全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
index： 索引全表扫描。全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。
range： 索引范围扫描。只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
ref： 非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。
eq_ref： 唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，
const： 表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可
possible_keys
显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。
key
显示查询语句实际使用的索引。若为null，则表示没有使用索引。select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
key_len
显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。
ref
如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
rows
这里是执行计划中估算的扫描行数，不是精确值，值越大越不好。
extra
Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where： 表明使用了where 过滤
Using join buffer： 表明使用了连接缓存
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
filtered
一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。
总结：
通过explain之后，我们可以通过一些属性的优化
id属性（通过id属性我们能够准确知道查询语句的执行属性，同时结合小表驱动大表的原则进行优化。
type属性（至少优化到range级别），
key_len属性（在不损失精确性的情况下索引长度越短越好）
rows属性 (看是否减少扫描行数)。
extra属性（如果出现Using filesort Using temporary必须优化，如果能出现Using index那就完美了）。
十、慢查询
1、参数说明
slow_query_log ： 慢查询开启状态（默认关闭）
slow_query_log_file ： 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限， 一般设置为MySQL的数据存放目录）
long_query_time ： 查询超过多少秒才记录(默认10秒）
show variables like 'slow_query%';
3.配置慢查询
它有两种配置方式，一种是全局变量配置，一种是配置文件配置
（1）全局变量配置
--将 slow_query_log 全局变量设置为“ON”状态
set global slow_query_log='ON';
--设置慢查询日志存放的位置
set global slow_query_log_file='/usr/local/mysql/data/slow.log';
--查询超过1秒就记录
set global long_query_time=1;
（2）修改配置文件my.cnf(linux环境下)
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
十一、变量的分类
1、系统变量：
全局变量
①查看所有全局变量 SHOW GLOBAL VARIABLES; # ②查看满足条件的部分系统变量 SHOW GLOBAL VARIABLES LIKE '%char%';
会话变量
①查看所有会话变量 SHOW SESSION VARIABLES; # ②查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE '%char%';
2、自定义变量：
用户变量
①声明并初始化(三种方式） SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值; # SELECT只能用:=
局部变量
# 和上面用户变量不同的是: 局部变量只能申明在bengin和end内 # ①声明 DECLARE 变量名 类型; DECLARE 变量名 类型 【DEFAULT 值】;
十二、Mysql几种约束列出来：
主键约束 外键约束 唯一性约束 非空约束 默认值约束 自增约束
查看约束
SHOW INDEX FROM 表名;
单独建约束
上面的约束是建表时候建的，下面是在表建好后独立添加。
-- 1.添加非空约束
ALTER TABLE student MODIFY COLUMN id INT auto_increment;
-- 2.添加默认约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 INT DEFAULT 16;
-- 3、主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
-- 4、唯一约束
ALTER TABLE 表名 ADD UNIQUE(字段名); #字段名可以多个,多个用逗号隔开
-- 5、主键自增
# 注意自增一张表只有一个,同时只能在主键上
ALTER TABLE 表名 MODIFY COLUMN 字段名 INT auto_increment;删除约束
-- 1、删除非空约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 VARCHAR(20) NULL;
-- 2、删除主键
-- 这里需要注意如果主键设置为自增的情况下是不能删除成功的,因为自增只能设置在主键上，你删主键自增还存在当然不行。
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 3、删除唯一
ALTER TABLE 表名 DROP INDEX 字段名;十三、解决MySQL死锁问题
当前自己的数据版本是8.0.22
mysql> select @@version;数据库隔离级别(默认隔离级别)
mysql> select @@transaction_isolation;自动提交关闭
mysql> select @@autocommit;查看分析死锁日志
可以用 show engine innodb status，查看最近一次死锁日志哈，执行后，死锁日志如下(只展示部分日志)：
十四、MVCC原理详解
多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。
MVCC只在已提交读（Read Committed）和可重复读（Repeatable Read）两个隔离级别下工作，其他两个隔离级别和MVCC是不兼容的。因为未提交读，总数读取最新的数据行，而不是读取符合当前事务版本的数据行。而串行化（Serializable）则会对读的所有数据多加锁。
MVCC的实现原理主要是依赖每一行记录中两个隐藏字段，undo log，ReadView
2、隐藏字段
对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer,如果数据表中存在主键或者非NULL的UNIQUE键时不会创建row_id,否则InnoDB会自动生成单调递增的隐藏主键row_id。
| 列名 | 是否必须 | 描述 |
|---|---|---|
| row_id | 否 | 单调递增的行ID，不是必需的，占用6个字节。 这个跟MVCC关系不大 |
| trx_id | 是 | 记录操作该行数据事务的事务ID |
| roll_pointer | 是 | 回滚指针，指向当前记录行的undo log信息 |
undo log分为如下两类：
1）insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
2）update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被删除。
ReadView 保存了不应该让这个事务看到的其他事务 ID 列表。
ReadView是如何保证可见性判断的呢？我们先看看 ReadView 的几个重要属性
-
trx_ids: 当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。(
重点注意:这里的trx_ids中的活跃事务，不包括当前事务自己和已提交的事务，这点非常重要) -
low_limit_id: 目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
-
up_limit_id: 活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。
-
creator_trx_id: 表示生成该 ReadView 的事务的 事务id
十五、主从复制原理
(1) Master的更新事件(update、insert、delete)会按照顺序写入bin-log中。当Slave连接到Master的后,Master机器会为Slave开启
binlog dump线程,该线程会去读取bin-log日志
(2) Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。
(3) Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行。
(1)异步复制
MySQL主从同步 默认是异步复制的。就是上面三步中,只有第一步是同步的(也就是Mater写入bin log日志),就是主库写入binlog日志后即可成功返回客户端，无须等待binlog
（2)同步复制
对于同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到所有Slave节点（如果有多个Slave）返回数据复制成功的信息给Master。这种复制方式最安
全，但是同时，效率也是最差的。
（3)半同步复制
对于半同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到其中一个Slave节点（如果有多个Slave）返回数据复制成功的信息给Master。由此增强了
如何查看主从延迟时间
通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：
影响延迟因素
这里整理了影响主从复制延迟大致有以下几个原因：
1）主节点如果执行一个很大的事务，那么就会对主从延迟产生较大的影响
2）网络延迟，日志较大，slave数量过多
3）主上多线程写入，从节点只有单线程同步
4）机器性能问题，从节点是否使用了“烂机器”
5）锁冲突问题也可能导致从机的SQL线程执行慢
优化主从复制延迟
1）大事务：将大事务分为小事务，分批更新数据
2）减少Slave的数量，不要超过5个，减少单次事务的大小
3）MySQL 5.7之后，可以使用多线程复制，使用MGR复制架构
4）在磁盘、raid卡、调度策略有问题的情况下可能会出现单个IO延迟很高的情况，可用iostat命令查看DB数据盘的IO情况，再进一步判断
5）针对锁问题可以通过抓去processlist以及查看information_schema下面和锁以及事务相关的表来查看
十六、性能优化全攻略
存储、主机和操作系统方面：
-
主机架构稳定性
-
I/O 规划及配置
-
Swap 交换分区
-
OS 内核参数和网络问题
应用程序方面：
-
应用程序稳定性
-
SQL 语句性能
-
串行访问资源
-
性能欠佳会话管理
-
这个应用适不适合用 MySQL
数据库优化方面：
-
内存
-
数据库结构（物理&逻辑）
-
实例配置
数据库优化维度有如下四个：
-
硬件
-
系统配置
-
数据库表结构
-
SQL 及索引
检查问题常用的 12 个工具：
-
MySQL
-
mysqladmin：MySQL 客户端，可进行管理操作
-
mysqlshow：功能强大的查看 shell 命令
-
SHOW [SESSION | GLOBAL] variables：查看数据库参数信息
-
SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息
-
information_schema：获取元数据的方法
-
SHOW ENGINE INNODB STATUS：Innodb 引擎的所有状态
-
SHOW PROCESSLIST：查看当前所有连接的 session 状态
-
explain：获取查询语句的执行计划
-
show index：查看表的索引信息
-
slow-log：记录慢查询语句
-
mysqldumpslow：分析 slowlog 文件的工具
不常用但好用的 7 个工具：
-
Zabbix：监控主机、系统、数据库（部署 Zabbix 监控平台）
-
pt-query-digest：分析慢日志
-
MySQL slap：分析慢日志
-
sysbench：压力测试工具
-
MySQL profiling：统计数据库整体状态工具
-
Performance Schema：MySQL 性能状态统计的数据
-
workbench：管理、备份、监控、分析、优化工具（比较费资源）
十七、配置参数优化全攻略
1、max_connections
MySQL的最大连接数，如果服务器的并发连接请求量较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，MySQL回味每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小经常会出现ERROR 1040：Too mant connetcions错误，可以通过mysql>show status like ‘connections'；通配符来查看当前状态的连接数量（试图连接到MySQL（不管是否连接成功）的连接数），以定夺该值的大小。
show variadles like ‘max_connections'最大连接数
show variables like ‘max_used_connection'相应连接数
max_used_connection/max_connections*100%（理想值约等于85%）
如果max_used_connections和max_connections相同，那么就是max_connections值设置过低或者超过服务器的负载上限了，低于10%则设置过大了。
2、back_log
MySQL能够暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，他就会起作用。如果MySQL的连接数据达到max_connections时，新的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将不被接受连接资源。
3、wait_timeout和interative_timeout
wait_timeout：指的是MySQL再关闭一个非交互的连接之前所需要等待的秒数。
interative_timeout：指的是关闭一个交互的连接之前所需要等待的秒数。
对性能的影响
wait_timeout
（1）如果设置太小，那么连接关闭的很快，从而使一些持久的连接不起作用
（2）如果设置太大容易造成连接打开时间过长，在show processlist时，能够看到太多的sleep状态的连接，从而造成too many connections错误。
（3）一般希望wait_timeuot尽可能的低
interative_timeout的设置将对你的web application没有多大的影响
2）缓冲区变量
全局缓冲
4、key_buffer_size
key_buffer_size指定索引缓冲区的大小，他决定索引的处理速度，尤其是索引读的速度。通过检查状态值 key_read_requests和key_reads，可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，至少是1：100，1：1000更好（上述状态值可以使用show status like ‘key_read%'获得）
未命中缓存的概率：
key_cache_miss_rate = key_reads/key_read_requests*100%
key_buffer_size只对MAISAM表起作用。
如何调整key_buffer_size的值
默认的配置数时8388608（8M），主机有4G内存可以调优值为268435456（256M）
5、query_cache_size（查询缓存简称QC)
使用查询缓存，MySQL将查询结果存放在缓冲区中，今后对同样的select语句（区分大小写），将直接从缓冲区中读取结果。
一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。
注：两个SQL语句，只要相差哪怕是一个字符（例如 大小写不一样：多一个空格等），那么两个SQL将使用不同的cache
通过 show ststus like ‘Qcache%' 可以知道query_cache_size的设置是否合理
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示过大，则说明Query Cache中的内存碎片较多了。
注：当一个表被更新后，和他相关的cache block将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用 flush query cache语句来清空free blocks。
Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query Cache内存大小是否足够，是需要增多还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。
Qcache_inserts：表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存 应用到的比较少，效果也就不理想。
Qcache_lowmen_prunes：多少条Query因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory 相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。
Qcache_queries_in_cache：当前Query Cache 中cache的Query数量
Qcache_total_blocks：当前Query Cache中block的数量
查询服务器关于query_cache的配置
各字段的解释：
query_cache_limit：超出此大小的查询将不被缓存
query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）
query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下：
0：OFF 相当于禁用了
1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存
2：DENAND 则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成在读表获取结果。
查询缓存碎片率：Qcache_free_block/Qcache_total_block*100%
如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%
查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50
的话说明query_cache_size可能有点小，要不就是碎片太多
查询缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100%
Query Cache的限制
a）所有子查询中的外部查询SQL 不能被Cache：
b）在p'rocedure，function以及trigger中的Query不能被Cache
c）包含其他很多每次执行可能得到不一样的结果的函数的Query不能被Cache
6、max_connect_errors：
是一个MySQL中与安全有关的计数器值，他负责阻止过多尝试失败的客户端以防止暴力破解密码的情况，当超过指定次数，MySQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hotos命令清空此host的相关信息。（与性能并无太大的关系）
7、sort_buffer_size：
每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或 GROUP BY操作
sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(2M)=1G
8、max_allowed_packet=32M
根据配置文件限制server接受的数据包大小。
9、join_buffer_size=2M
用于表示关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
10、thread_cache_size=300
服务器线程缓存，这个值表示可以重新利用保存在缓存中的线程数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁（前提时缓存数未达上限），如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，这个线程将被重新请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能，通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。
设置规则如下：1G内存配置为8，2G内存为16.服务器处理此客户的线程将会缓存起来以响应下一个客户而不是被销毁（前提是缓存数未到达上限）
Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created：代表最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，说明MySQL服务器一直在创建线程，这也比较消耗资源，可以适当增加配置文件中thread_cache_size值
Threads_running：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
3）配置Innodb的几个变量
11、innodb_buffer_pool_size
对于innodb表来说，innodb_buffer_pool_size的作用相当于key_buffer_size对于MyISAM表的作用一样。Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的80%。
12、innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0，1，2.
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。
13、innodb_thread_concurrency=0
此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。
14、innodb_log_buffer_size
此参数确定日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
15、innodb_log_file_size=50M
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能。
16、innodb_log_files_in_group=3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
17、read_buffer_size=1M
MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配到一个读入缓冲区MySQL会为他分配一段内存缓冲区
18、read_rnd_buffer_size=16M
MySQL 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时（例如，按照排序顺序），将分配到一个随机都缓冲区。进行排序查询时，MySQL会首先扫描一遍该缓冲区，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但是MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存消耗过大。
注：顺序读是根据索引的叶节点数据就能顺序的读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找侍其巷进行数据，而辅助索引和主键所在的数据端不同，因此访问方式是随机的。
19、bulk_insert_buffer_size=64M
批量插入数据缓存大小，可以有效的提高插入效率，默认为8M
20、binary log
binlog_cache_size=2M //为每个session分配的内存，在事务过程中用来存储二进制日志的缓存，提高记录bin-log的效率。
max_binlog_cache_size=8M //表示的是binlog能够使用的最大cache内存大小
max_binlog_size=512M //指定binlog日志文件的大小。不能将变量设置为大于1G或小于4096字节。默认值为1G.在导入大容量的sql文件时，建议关闭，sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days=7 //定义了mysql清除过期日志的时间
十八、MyISAM与InnoDB 的区别
InnoDB：
- 支持事务
- 行锁
- 读操作无锁
- 4种隔离级别，默认为repeatable
- 自适应hash索引
- 每张表的存储都是按主键的顺序记性存放
- 支持全文索引（InnoDB1.2.x - mysql5.6）
- 支持MVCC（多版本并发控制）实现高并发
MyISAM：
- 不支持事务
- 表锁
- 支持全文索引
区别：
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
3.支持特性的图
