Mysql进阶

日志

错误日志

MySQL错误日志是记录MySQL 运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。错误日志的命名默认为主机名.err。

通过show variables like '%log_error%';我们可以查看错误日志的位置

慢查询日志

慢查询日志是记录运行时间超过阈值的信息

默认是不开启的

我们可以修改我们配置文件 /etc/my.cnf 或者使用 set 指令（注意 5.7版本的set命令不会持久化！）开启（只对当前数据库生效。）

需要注意的是慢查询日志记录的仅仅是运行时间超过阈值的，不包括锁等待时间。

举个例子，假如某个语句因为等待锁而超过了阈值，是有可能不被慢查询日志记录的！

通用日志

可以记录数据库的所有操作
通过参数general_log开启
默认文件名为机器名.log
开启后性能下降明显

存储引擎

通过show engines;查看当前版本的存储引擎

实际中我们只使用InnoDB即可

存储引擎的对象是表，也就是说不同表可以使用不同的存储引擎。

为什么有人说Mysql是不可靠的？

实际上在Mysql5.1之前使用的默认存储引擎是MyISAM，MyISAM是表锁的并且不支持事务，数据容易丢失，索引容易损坏，因此性能很差！Mysql也就是在那时产生偏见的

数据类型

数值类型

数值类型有三个属性：

SIGNED/UNSIGNED:是否有符号
ZEROFILL:固定长度。比如对于INT(4)类型的数据来说，如果不加上ZEROFILL这个属性则它的范围不受括号里的数字影响，还是(-2147483648，2147483647) ，如果加上ZEROFILL，则范围最大为9999
AUTO_INCREMENT:是否自增
自增一般是不会回溯的。比如1，2，3，4，5我们将4这个记录删掉，则下次增加仍是6。但是当数据库重启后，自增会进行回溯！坑！（Mysql 5.7下）

建议主键ID使用BIGINT类型，而不是INT类型

对于小数类型（特别是金融计算时），直接使用Demical(M,D)其中M表示整数位，D表示小数点后位数。不要使用FLOAT和DOUBLE

常用函数

floor():向下取整
round()：四舍五入
rand():0-1之间的随机值。如果取(i,j)之间随机整数 floor(i+rand()(j-i))。比如1-100之间随机整数位 floor(1+rand99)

字符串类型

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。而BINARY(n) 和 VARBINARY(n)的n代表字节个数，以UTF-8为例一个字符占用3个字节
mysql 5.7中新增了json数据类型

字符集

只推荐utf8mb4(可以存储表情符号)

Mysql字符集的粒度实际是到列的，即每个列都可以为一个字符集（实际没啥用）

修改数据库默认字符集：修改配置文件 character_set_server=utf8mb4

修改表字符集

alter table t charset=utf8mb4; //使用它更改字符集时，不能修改表中已经存在的列的字符集，只能修改新增列的字符集


alter table t convert to character set utf8mb4; //此时既能修改表中已经存在的列的字符集，又能修改新增列的字符集 (需要注意只能从小字符集转成大字符集)

常用函数

length():返回字节的长度，比如length('我')
char_length()：返回字符的长度
hex():将字符转化成16进制

日期

优先使用TIMESTAMP和DATETIME，这两者的区别一是范围的不同而是TIMESTAMP带时区（也就是说根据时区的不同TIMESTAMP的值会变化）

常用函数

now():执行到该sql语句时的时间
sysdate():执行到该函数时的时间
date_add(): date_add(now()，interval 1 day)表示当前时间+1天
date_format()：对时间格式化

SQL执行顺序

FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

7种JOIN

内连接

 #INNER JOIN=JOIN
 SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key

左连接

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

右连接

 SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

只有A

  SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL

只有B

 SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

全连接

    # MySQL没有FULL OUTER语法。
    SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
    union
    SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

A,B各自独有

  # MySQL没有FULL OUTER语法。
  SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
  union
  SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

索引

索引是帮助Mysql高效获得数据的数据结构

一般来说，索引本身也很大，所以不可能存储在内存中，而是以文件的形式存储在磁盘中

优缺点

优点
- 提高了检索效率，降低了数据库的IO成本
缺点
- 占用空间
- 降低了表的更新速度，每次更新表都需要更新索引

什么情况需要创建索引

1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.单值/复合索引选择问题，尽量建复合索引
5.查询中排序的字段应该创建索引，排序字段如果通过索引会大大提高排序速度
6.查询中分组或统计的字段应该创建索引

什么情况不需要创建索引

1.频繁更新的字段不应该建立索引
2.where条件里用不到的字段不建立索引
3.表记录太少
4.某个数据列包含太多重复的内容为他创建索引没有太大的实际效果

索引结构

BTree索引

B树与二叉搜索树区别：B树是多路搜索树，树高度更低，搜索次数更少

B+树与B树的区别：InnoDB中索引的实现采用的是B+树。B+树是B树的变种。B+树非叶子节点不存储数据只存储索引，在相同数据量下，B+树高度更低，且B+树叶子节点之间组成一个链表，便于遍历查询

B树：

B+树：

B+树的优势在于查找效率上，下面我们做一具体说明：
　　首先，Ｂ＋树的查找和Ｂ树一样，类似于二叉搜索树。起始于根节点，自顶向下遍历树，选择其分离值在要查找值的任意一边的子指针。在节点内部典型的使用是二分查找来确定这个位置。
　　（1）不同的是，Ｂ＋树中间节点没有卫星数据（索引元素所指向的数据记录），只有索引，而Ｂ树每个结点中的每个关键字都有卫星数据；这就意味着同样的大小的磁盘页可以容纳更多节点元素，在相同的数据量下，Ｂ＋树更加“矮胖”，ＩＯ操作更少
需要补充的是，在数据库的聚集索引（Clustered Index）中，叶子节点直接包含卫星数据。在非聚集索引（NonClustered Index）中，叶子节点带有指向卫星数据的指针。
　　（2）其次，因为卫星数据的不同，导致查询过程也不同；Ｂ树的查找只需找到匹配元素即可，最好情况下查找到根节点，最坏情况下查找到叶子结点，所说性能很不稳定，而Ｂ＋树每次必须查找到叶子结点，性能稳定
　　（3）在范围查询方面，B+树的优势更加明显。B树的范围查找需要不断依赖中序遍历。首先二分查找到范围下限，在不断通过中序遍历，知道查找到范围的上限即可。整个过程比较耗时。而B+树的范围查找则简单了许多。首先通过二分查找，找到范围下限，然后同过叶子结点的链表顺序遍历，直至找到上限即可，整个过程简单许多，效率也比较高。

Hash索引

full-text全文索引

R-Tree索引

索引分类

单值索引

一个索引只包含单个列，一个表可以有多个单值索引

create index 索引名 on 表名(字段名)；

#比如在user表为name创建索引：
create index idx_user_name on user(name);

唯一索引

索引列的值必须唯一，允许为空

复合索引

一个索引包含多个列

#将name和Email组合 作为索引
create index idx_user_nameEmail on user(name,Email);

索引的前导列

所谓前导列，就是在创建复合索引语句的第一列或者连续的多列。比如通过：CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,xy,xyz都是前导列，而yz，y，z这样的就不是。

创建，删除，查看

创建

create [unique] index 索引名 on 表名(字段名)；

alter 表名 add [unique] index 索引名 on （字段名)；

删除

drop index [索引名] on 表名；

查看

show index from 表名；

覆盖索引与回表查询

什么是回表查询？

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

聚集索引(clustered index)
普通索引(secondary index) （也叫二级索引）

InnoDB聚集索引和普通索引有什么差异？

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了PK（主键），则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

画外音：所以PK查询非常快，直接定位行记录。

InnoDB普通索引的叶子节点存储主键值。

　画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。

举个栗子，不妨设有表：

　　t(id PK, name KEY, sex, flag);

画外音：id是聚集索引，name是普通索引。

表中有四条记录：

　　1, shenjian, m, A

　　3, zhangsan, m, A

　　5, lisi, m, A

　　9, wangwu, f, B

两个B+树索引分别如上图：

　　（1）id为PK，聚集索引，叶子节点存储行记录；

　　（2）name为KEY，普通索引，叶子节点存储PK值，即id；

既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？

通常情况下，需要扫码两遍索引树。

例如：

select * from t where name='lisi';

如粉红色路径，需要扫码两遍索引树：

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

覆盖索引：查询的字段就是索引的字段（不必关心顺序和个数，不遵守最左原则，也就是说有一个复合索引(A,B,C),此时select B 仍然是覆盖索引）。此时索引的叶子节点就已经存储了该索引字段的信息，因此不需要根据聚集索引去进行回表查询！

性能分析

EXPLAIN

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道Mysql是如何处理你的SQL语句的，分析你的SQL语句或是表结构的性能瓶颈

EXPLAIN+SQL语句

id

select查询的序列号，包含一组数字，表示查询中执行select语句或操作表的顺序

id相同：执行顺序由上至下
id不同：id值越大，越先执行
id不同相同同时存在：先执行id大的，id相同从上到下执行

select_type

查询的类型

simple:简单的select查询,不包含子查询和union
primary：查询中若包含子查询，则最外层查询为primary
subquery：在select或where中包含的子查询
derived：在from中包含的子查询被标记为derived(衍生)，Mysql会递归执行这些子查询把结果放在临时表中
union：若第二个select出现在union之后，则被标记为union
union result：从union表获取结果的select

table

显示这一行是关于哪个表的

type

显示查询使用了何种访问类型

常见的从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

一般来说得保证查询至少达到range级别，最好能达到ref

system:表只有一行记录（等于系统表），是const类型的特例，平时几乎不会出现
const:表示通过索引一次就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快。如果将主键放在where中，Mysql就能将该查询转化为一个常量
eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描
ref:非唯一性索引扫描，返回匹配索引值的所有行
range:使用索引只检索给定范围的行，一般where语句中出现between,>,<,in等的查询
index:index与ALL的区别在于，index是对索引的全表扫描，ALL是对硬盘的全表扫描
ALL:全表扫描

possible_keys与key

possible_keys:可能使用的索引（查询涉及的字段存在的索引），一个或多个，但不一定被实际使用

key:实际使用的索引，查询中如果出现了覆盖索引，则该索引仅出现在key列表中

key_len

表示索引中使用的字节数，可以通过该列计算查询中使用的索引长度，在不损失精度的情况下，索引长度越短越好

key_len的值为索引字段的最大可能长度，而不是实际使用长度，也就是说key_len是根据表定义计算得出，不是通过表内检索出的

ref

ref列显示使用哪个列或常数与索引一起从表中选择行。（换句话说就是索引列的值=哪个列或常数）

rows

大致估算出找到所需记录需要查询的行数

Extra

其他信息

Using filesort:说明mysql会对文件进行外部的索引排序而不是按照表内的索引顺序进行读取（也就是说Mysql无法利用索引进行排序）。应该减少
Using temporary:使用了临时表保存中间结果，常见于order by 和group by。应该减少
Using index: 表示相应的select操作使用了覆盖索引避免访问了表的数据行。应该增加
Using where：不是很懂什么意思？
using join buffer:使用了连接缓冲
impossible where: 不可能根据where找到任何数据比如 where 性别=‘男’ and 性别=‘女’
select tables optimized away:直接在表信息中就能获取到结果，不需要执行select
distinct:优化distinct，找到第一个匹配的数据后就停止寻找

索引优化

索引失效

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

最佳左前缀法则：假如复合索引为（A,B,C）,只有where字段的从索引的最左列开始，并且不跳过索引的列 索引才生效。
- where A='a' and B='b' and C='c'; 使用索引
- where A='a' and B='b' ; 使用索引
- where B='b' and A='a' ; 使用索引，跟where的顺序无关（mysql会自动优化顺序），只跟有无该字段有关
- where A='a';使用索引
- where B='b';无法使用索引
- where B='b' and C='C';无法使用索引
- where A='a' and C='C'; 复合索引部分生效（A无法使用索引，C使用索引）
不能再索引列上做任何操作（即使操作结果和原数据相同），包括计算，函数，（自动或手动）类型转换，否则或导致无法使用索引而转向全表扫描
范围条件右边的列作为索引失效
- 假如复合索引为(A,B,C,D)，where A='a' and B='b' and C>20 and D='d'; 此时D无法使用索引
尽量使用覆盖索引（select字段是索引字段），减少select *
使用！=或者<>（单独的< 或 >可以使用索引）无法使用索引（8.0好像可以了？）
is null或is not null 无法使用索引（因版本而异）
like以通配符（%）开头无法使用索引
- like "%ABC%" ,like "%ABC" 无法使用索引
  - 可以使用覆盖索引解决 like"% %" 索引失效的问题
- like "ABC%" 可以使用索引，但要注意此时是范围，范围之后索引失效。
字符串不加 ' ' 会导致无法使用索引
少用or，or只有在两个条件都有索引时才会使用索引，否则无法使用索引

一个SQL语句只能使用一个索引吗？

MySQL5.0之后是有索引合并这个概念的，所以第一个问题解决了，MySQL可以同时使用多个索引。

什么情况下会使用两个索引？

与其说是“数据库查询只能用到一个索引”，倒不是说是和全表扫描/只使用一个索引的速度比起来，去分析两个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是是用一个索引。

优化

小表驱动大表

类似于循环嵌套

for(int i=5;.......)
{
     for(int j=1000;......)
     {}
}

如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

①当B表的数据集小于A表数据集时，用in优于exists。

select *from tb_emp_bigdata A where A.deptno in (select B.deptno from tb_dept_bigdata B)

B表为tb_dept_bigdata：100条数据，A表tb_emp_bigdata：5000条数据。

用in的查询时间为：

将上面sql转换成exists：

select *from tb_emp_bigdata A where exists(select 1 from tb_dept_bigdata B where B.deptno=A.deptno);

用exists的查询时间：

②当A表的数据集小于B表的数据集时，用exists优于in。

select *from tb_dept_bigdata A where A.deptno in(select B.deptno from tb_emp_bigdata B);

用in的查询时间为：

将上面sql转换成exists：

select *from tb_dept_bigdata A where exists(select 1 from tb_emp_bigdata B where B.deptno=A.deptno);

用exists的查询时间：

由于数据量并不是很大，因此对比并不是难么的强烈。

下面结论都是针对in或exists的。

in后面跟的是小表，exists后面跟的是大表。

简记：in小，exists大。

对于exists

select .....from table where exists(subquery);

可以理解为：将主查询的数据放入子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据是否得以保留。

Order By使用索引排序

Order By后字段是索引字段且符合最左匹配原则时或者 Where 和 Order By 组合满足最左匹配原则时，排序使用索引排序

否则使用 filesort

如果不在索引列上，filesort有两种算法：单路排序和双路排序

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。（问题就在于sortbuffer可能放不下，需要放多次）
双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。

在MySQL4.1版本之前只有第一种排序算法双路排序，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sortbuffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法。

MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data 的大小和 Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data 更大，则使用单路排序，反之使用双路排序。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要注意max_length_for_sort_data参数的设置。曾经就有同事的数据库出现大量的排序等待，造成系统负载很高，而且响应时间变得很长，最后查出正是因为MySQL 使用了传统的第一种排序算法而导致，在加大了max_length_for_sort_data参数值之后，系统负载马上得到了大的缓解，响应也快了很多。

所以filesort调优的思路就是：

增大max_length_for_sort_data的值
增大sort buffer_size的值
使之尽可能使用单路排序

Group By 优化

基本与Order By相同，需要注意的是，where优于having，能写在where中就不要写在having中

show profile

是mysql 提供用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优的测量

1.查看当前mysql是否支持

show variables like 'profiling'

2.默认为关闭，需要开启

set profiling=on;

3.运行我们的sql语句

4.查看运行结果

show profiles;

5.诊断某个具体的sql

show profile cpu, block io for query SQL编号  （上图红框里的）

6.日常常用结论

如果在Status中出现以下内容，说明sql需要优化：

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上面搬了
Create tmp table 创建临时表
Copying to tmp table on disk 把内存中的临时表复制到磁盘，危险！！！！
bocked

锁

按对数据操作的类型分为读锁和写锁：

读锁：共享锁，加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。
写锁：排他锁，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。
读锁会阻塞写，写锁会阻塞读和写

按对数据操作的粒度分为行锁和表锁：

表锁：MyISAM存储引擎使用表锁，锁的粒度大，发生锁冲突的概率小，并发性差，以下都是基于MyISAM存储引擎进行分析
- 查看哪些表被加锁了：show open tables
- 手动加表锁：

   lock table 表名 read(write);

释放锁：

   unlock tables;

sessio1 对 table1 加了读锁后，就不能再更新该表，也不能读其他表（会报错）
- session2 可以读table1 ，但是更新table1时会阻塞！
sessio1 对 table1 加了写锁后，可以对table1进行读和写
- session2 不能对 table1 进行读也不能写（会阻塞）
**总之就是读锁会阻塞写，写锁会阻塞读和写 **
行锁：InnoDB使用行锁，开销大，加锁慢，锁的粒度较细，发生锁冲突概率最小，并发度最高。
- 行锁是加在索引字段上的
- 在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁
- 索引失效会导致行锁变为表锁

间隙锁：
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

例：
假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的：
（1）防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录(行锁只能锁住当前行不能锁住间隙)，那么本事务如果再次执行上述语句，就会发生幻读；
（2）为了满足其恢复和复制的需要。

next-key lock锁：

next_lock锁是索引记录上的行锁和索引记录之前的间隙上的间隙锁的组合。

什么意思？

比如现在有一个next_lock锁为(1,5]它代表的是(1,5)这个间隙锁和 5 这个行锁的组合

事务

事务是一组原子性sql查询语句，被当作一个工作单元。若mysql对改事务单元内的所有sql语句都正常的执行完，则事务操作视为成功，所有的sql语句才对数据生效，若sql中任意不能执行或出错则事务操作失败，所有对数据的操作则无效(通过回滚恢复数据)。事务有四个属性：
1、原子性：事务被认为不可分的一个工作单元，要么全部正常执行，要么全部不执行。

2、一致性：事务操作对数据库总是从一种一致性的状态转换成另外一种一致性状态。

3、隔离性：一个事务的操作结果在内部一致，可见，而对除自己以外的事务是不可见的。

4、永久性：事务在未提交前数据一般情况下可以回滚恢复数据，一旦提交(commit)数据的改变则变成永久(当然用update肯定还能修改)。

ps:MYSAM 引擎的数据库不支持事务，所以事务最好不要对混合引擎(如INNODB 、MYISAM)操作，若能正常运行且是你想要的最好，否则事务中对非支持事务表的操作是不能回滚恢复的。

事务的隔离级别

InnoDB的隔离级别是可重复读（RR），但是InnoDB 实际解决了幻读的问题！

幻读，脏读，不可重复读，序列化

MVCC

MVCC就是一种快照机制，他保存了事务在开始时刻的快照，举个例子

先初始化建表预置数据:


create table `test` ( `a` int not null primary key);
insert into `test` values(1);事务A启动并查询计数字段（值为1）：mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

事务B启动，递增字段（值为2），并且提交：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set a = a + 1 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

这时候事务A，无论你怎么查，都只能查到计数值为1。因为InnoDB在可重复读下通过MVCC提供的视图确保了不可能发生幻读！！

mysql> select * from test;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

但是！如果事务A执行insert、update语句，会触发一次当前读，当前读获得的最新计数值是2！所以当你在事务A尝试执行以下insert一个计数行2，当前最新读就会发现主键冲突：

mysql> insert into test values(2);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

而当你在事务A尝试执行update语句，进行递增，就会在当前最新读的基础上递增，结果为3：mysql> update test set a = a + 1 ;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from test;
+---+
| a |
+---+
| 1 |
| 3 |
+---+
2 rows in set (0.00 sec)

等等！为什么变成两条数据了？？ 3可以理解，但是1从哪里来的？？？前边不是提到了MVCC吗，就是这玩意儿保证了1的存在。当事务A commit 之后，就只剩下3了：

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select *from test;
+---+
| a |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

在InooDB中也是通过undo log（回滚日志）记录不同的快照版本，当事务开始时会记录当前的版本号，在该事务中执行select语句时，从回滚日志中只读取版本号<=当前版本号的快照，实现了MVCC。

幻读

幻读：当某个事务在读取某个范围的记录的时候，另外一个事务又在该范围插入了新的记录，当前事务再次读取这个范围的记录，会产生幻行（Phantom Data）。

需要注意的是InnoDB通过MVCC和间隙锁（在间隙锁的情况下无法对间隙数据进行update!）解决了幻读的问题，因此幻读无法在InnoDB中复现出来

脏读

假设事务A将某个值修改为 100，事务B 读取到了这个值，并用这个值做了一系列操作，然后事务A 又将事务进行了回滚，该值变为原来的0，这样就产生了脏读。

脏读就是指事务B 读取到了事务A还没有提交事务的数据，事务A又将其进行了改变。

InnoDB不会出现脏读

不可重复读

假设缓存页里一条数据原来的值是 A 值，此时事务 A 开启之后，第一次查询这条数据，读取到的就是 A 值。

接着事务 B 更新了那行数据的值为 B 值，同时事务 B 立马提交了，然后事务 A 此时还没提交。大家注意，此时事务 A 是没提交的，它在事务执行期间第二次查询数据，此时查到的是事务 B 修改过的值，B 值，因为事务 B 已经提交了，所以事务 A 是可以读到的。

显然，在一次事务中，事务A并没有进行更新操作，两次读取的数据却不同，这就叫不可重复读。

InnoDB不会出现不可重复读

InnoDB是如何保证事务ACID？

博客传送门

原子性

原子性是指一个事务就是一个不可分割的工作单位，要么全部都执行成功，要么全部都执行失败，没有中间状态或是只执行一部分。

MySQL的InnoDB引擎是靠undo log（回滚日志）来实现的，undo log能够保证在事务回滚时，能够撤销所有已经执行成功的SQL。

undo log 属于逻辑日志，它记录的是SQL执行相关的信息。当事务对数据库进行修改时，InnoDB会生成与之对应的undo log。如果事务执行失败或者调用的rollback，导致事务需要回滚，InnoDB引擎会根据undo log中的记录，将数据回滚到之前的样子。

例如在执行insert语句时会生成相关的delete语句的undo log。反之执行delete语句也会生成相关的insert语句的undo log。执行update语句时也是如此，不过update语句在执行undo log回滚时有可能会涉及到MVCC。主要是为了保证在执行undo log的时候的select能看到哪个版本的数据。

持久性

持久性是指事务一旦提交，对数据库的操作就是永久性的，接下来的其他操作和异常故障不应该对它有任何影响。

我们都知道MySQL的数据最终是存放在磁盘中的，所以才会有磁盘的容量大小决定数据容量的大小。但是如果对MySQL的操作都是通过读写磁盘来进行的话，那么光是磁盘的I/O就够把效率大大的拉低了。
所以InnoDB为MySQL提供了缓冲池（Buffer Pool）,Buffer Pool中包含了磁盘中部分数据页的映射。

当从数据库读取数据时，会先从Buffer Pool中读取数据，如果Buffer Pool中没有，则从磁盘读取后放入到Buffer Pool中。

当向数据库写入数据时，会先写入到Buffer Pool中，Buffer Pool中更新的数据会定期刷新到磁盘中（此过程称为刷脏）。

虽然Buffer Pool为MySQL的读写提高了效率，但是却也带来了新的问题，那就是如果数据刚更新到Buffer Pool中还没来得及刷新到磁盘中时，MySQL突然宕机了，这就会导致数据丢失，造成事务的持久性无法保证了。

为了解决这个缓存的一致性问题，redo log就出现了。在对Buffer Pool中的数据进行修改的时候通过redo log记录这次操作。
因为在事务提交时会把redo log同步在磁盘中的（写入到了磁盘的redo log文件中），所以当MySQL出现宕机时，可以从磁盘中读取redo log文件进行数据的恢复，从而保证了事务的持久性。

redo log 采用的预写的方式记录日志，即事务提交的时候，会先把redo log buffer写入redo log file，写入成功才算提交成功。而Buffer Pool的数据由后台线程在后续某个时刻写入磁盘。刷脏的时候一定会保证对应的redo log已经落盘了。

这要解释一下，redo log 也是写磁盘，刷脏也是写磁盘，为啥要先记录redo log而不是直接刷脏？

主要原因就是redo log比刷脏快很多。

第一点是，redo log是追加操作日志，是顺序IO；而刷脏是随机IO，因为每次更新的数据不一定是挨着的，也就是随机的。

第二点是，刷脏是以数据页（Page）为单位的（即每次最少从磁盘中读取一页数据到内存，或者最少刷一页数据到磁盘），MySQL默认页大小是16KB，对一个页上的修改，都要整个页都刷到磁盘中；而redo log只包含真正的需要写入磁盘的操作日志。

MySQL还有一个记录操作的日志，叫binlog ，那么redo log和binlog又有什么区别呢？

第一点作用上的区别：
redo log是用来记录更新缓存的，为了保证MySQL就算宕机也不会影响事务的持久性；binlog是用来记录什么时间操作了什么，主要有时间点，可以保证将数据恢复到某个时间点，
也有用于主从同步数据的。
第二点层次上的区别：
redo log是存储引擎InnoDB实现的（MyISAM就没有redo log），而binlog是在MySQL服务器层面存在的任何其他存储引擎也有binlog。
存储内容上，redo log是物理日志，基于磁盘的数据页，binlog是逻辑日志，存储的一条执行SQL。
第三点写入时机的区别：
redo log 在默认情况下是在事务提交时，进行刷盘的；可以通过参数：innodb_flush_log_at_trx_commit 来改变策略，可以不用等到事务提交时才进行刷盘。
如：可以设置成每秒提交一次。
binlog是在事务提交时写入。

隔离性

Mysql默认的隔离级别是不可重复读，正是通过MVCC和锁来实现的。这里就不具体分析了，上边已经分析过了

一致性

Mysql一致性通过以上三个特性以及在应用层面进行保证

主从复制

slave会从master读取binlog来进行数据的同步

原理

Mysql主从复制分为三步
（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求获取master二进制事件

（3）同时master服务器为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至slave服务器本地的中继日志（relay log）中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

复制的最大问题

延时！

一主一从常见配置

Mysql版本一致，且能相互ping 通

主从配置都在[mysqld]节点下

因修改过配置文件，主机和从机重启服务！

主机从机关闭防火墙

posted @ 2021-08-22 22:49 刚刚好。阅读(96) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

刚刚好。

Mysql进阶

日志

错误日志

慢查询日志

通用日志

存储引擎

数据类型

数值类型

常用函数

字符串类型

字符集

常用函数

日期

常用函数

SQL执行顺序

7种JOIN

索引

优缺点

什么情况需要创建索引

什么情况不需要创建索引

索引结构

BTree索引

Hash索引

full-text全文索引

R-Tree索引

索引分类

单值索引

唯一索引

复合索引

索引的前导列

创建，删除，查看

创建

删除

查看

覆盖索引 与 回表查询

性能分析

EXPLAIN

id

select_type

table

type

possible_keys与key

key_len

ref

rows

Extra

索引优化

索引失效

一个SQL语句只能使用一个索引吗？

优化

小表驱动大表

Order By使用索引排序

Group By 优化

show profile

锁

事务

事务的隔离级别

MVCC

幻读

脏读

不可重复读

InnoDB是如何保证事务ACID？

原子性

持久性

隔离性

一致性

主从复制

原理

复制的最大问题

一主一从常见配置

公告

覆盖索引与回表查询