MySql高级

//创建表时，列名需要使用反引号括起来 键盘第二列第一个 英文标点符号不行

CREATE TABLE `student_myisam` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL, `age` int DEFAULT NULL, `sex` varchar(2) DEFAULT NULL, PRIMARY KEY(`id`) )ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;

举例:数据库重,表b。

1、如果表b采用InnoDB，data\a中会产生1个或者2个文件:

b.frm:描述表结构文件,字段长度等
如果采用系统表空间模式的,数据信息和索引信息都存储在ibdata1中
如果采用独立表空间存储模式,datala中还会产生b.ibd文件(存储数据信息和素引信息)

此外:

①MySQL5.7中会在data/a的目录下生成db.opt文件用于保存数据库的相关配置。比如:字符集、比较规则。而 MySQL8.0不再提供db.opt文件。

②MySQL8.0中不再单独提供b.frm,而是合并在b.ibd文件中。

2、如果表b采用MyISAM，data\a中会产生3个文件:

MySQL5.7中: b. frm:描述表结构文件,字段长度等。
MySQL8.0中b.xxx.sdi :描述表结构文件,字段长度等。
b.MpD (MYData):数据信息文件,存储数据信息(如果采用独立表存储模式)。
b.MYI (MYIndex):存放索引信息文件。

mysql启动命令：mysql -uroot -p

同时可以写全一点：mysql -h localhost -P 3306 -p datest1 -e "select * from emp1";

mysql -uroot -p    //启动mysql 简写
mysql -h localhost -P 3306 -p datest1 -e "select * from emp1"; //还可以这样写全，直接执行sql语句
create user 'wjh' identified by '123456';  //创建用户
create user 'wjh'@'localhost' identified by '123456';  //创建只能本地连接得用户
flush privileges;   //刷新权限
drop user 'wjh'@'%';  //删除用户

alter user user() identified by 'newpassword';    //修改当前登录用户的密码

alter user 'wjh'@'%' identified by 'newpassword';    //修改其他用户的密码

grant select,update on datest1.* to 'wjh'@'%';    //赋予wjh用户查看和修改数据库下所有表的权限

grant all privileges on *.* to 'li4'@'%';     //赋予li4用户所有权限，但是唯独不能赋予其他用户权限，root用户独有

show grants;     //查询当前用户的权限

revoke select,update on datest1.* to 'wjh'@'%';    //回收wjh用户查看和修改数据库下所有表的权限

desc user;     //查看表结构

create role 'boss'@'%';     //创建boss角色

grant select,update on datest1.* to 'manager'@'%';   //赋予manager角色查询/修改的权限

grant all privileges on *.* to 'boss'@'%';     //赋予boss角色所有权限

show grants for 'boss'@'%';     //查看角色的权限

revoke select on datest1.* to 'manager'@'%';   //移除manager角色查询的权限

drop role 'boss'@'%';     //删除角色

grant 'manager'@'%' to 'wjh'@'%';   //赋予用户manager角色

select current_role();    //查看角色是否激活

set default role 'manager'@'%' to 'wjh'@'%';     //激活角色，但是需要退出重新登录

revoke'manager'@'%' from 'wjh'@'%';   //移除用户manager角色

MySql逻辑架构

1.MySql 服务器端的逻辑架构说明

简化为三层结构

连接层：客户端与服务端建立连接，客户端发送SQL到服务器。
SQL层（服务层）：对SQL语句进行查询处理，与数据库文件的存储方式无关。
存储引擎层：与数据库文件打交道，负责数据的存储和读取。

2.SQL的执行流程

把SQL查询优化分成两个部分,逻辑查询优化和物理查询优化。虽然SQL查询优化的技术有很多,但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

物理查询优化则是通过索引和表连接方式等技术来进行优化,这里重点需要掌握索引的使用。
逻辑查询优化就是通过SQL等价变换提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高。

select @@session.profiling;    //会话，确定profiling是否开启，开启可以让MySql收集在SQL执行时所使用的资源情况   0就是没有开启

set profiling=1;     //设置为1，开启profiling

show profiles;    //查看所有的sql语句

show profile;      //可以查看最近一条sql执行的细节

show profile for query 1;     //查看指定sql语句执行的细节

show variables like 'innodb_buffer_pool_size';     //查看缓冲池大小

set global innodb_buffer_pool_size = 268435456;    //设置缓冲池大小

存储引擎

查看支持的存储引擎：

引擎介绍

InnoDB引擎：具备外键支持功能的事务存储引擎

MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务。可以确保事务的完整提交（Commit）和回滚（Rollback）。
除了增加和查询外，还需要更新/删除操作，那么，应优先选择InnoDB存储引擎。
除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
数据文件结构：表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）表名.ibd 存储数据和索引。
InnoDB是为了处理巨大数据量的最大性能设计。在以前的版本中，早点数据以元数据文件/非事务表等来存储。现在这些元数据文件被删除了。比如：.frm，.par，.trn，.isl，.db .opt等都在mysql8.0中不存在了。
对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

MyISAM引擎:主要的非事务处理存储引擎

MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务、行级锁、外键, 有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
5.5之前默认的存储引擎
优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用
针对数据统计有额外的常数存储。故而count(*)的查询效率很高
数据文件结构: 表名.frm存储表结构。表名.MYD存储数据(MYData) 。表名.MYI存储索引(MYIndex)
应用场景:只读应用或者以读为主的业务

索引的数据结构

什么是索引

MysQL官方对索引的定义为:索引(Index)是帮助MysQL高效获取数据的数据结构。

索引的本质:索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”,满足特定查找算法。这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法。

索引是在存储引擎中实现的,因此每种存储引擎的索引不一定完全相同,并且每种存储引擎不一定支持所有索引类型。同时,存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。

索引的优点

类似大学图书馆建书目索引，提高数据检索的效率,降低数据库的I0成本,这也是创建索引|最主要的原因。
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

索引的缺点

增加索引也有许多不利的方面，主要表现在如下几个方面:

创建索引和维护索引要耗费时间,并且随着数据量的增加，所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外,每一个索引还要占一定的物理空间，存储在磁盘上,如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
虽然索引大大提高了查询速度,同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

常见的索引概念

索引按照物理实现方式，索引可以分为两种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户纪录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

InnoDB的B+树索引的注意事项

1.根页面位置万年不动

每当为某个表创建一个B+树索引 (聚簇索引不是人为创建的，默认就有)的时候,都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录,也没有目录项记录。
随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值(也就是聚簇索弓中的主键值，二级索弓|中对应的索引列的值)的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是: 一个B+树索的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

2.内节点中目录项纪录的唯一性

对于二级索引来说，应该是二级索引+主键索引+页号，这样才能保证唯一性。

3.一个页面最少存储2条纪录

MyISAM与InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别:

在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录,虽然说也不慢，但还是比不上直接用地址去访问。
InnoDB要求表必须有主键( MyISAM可以没有)。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节，类型为长整型。

索引的代价

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成,那就是很大的一片存储空间。

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过, B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录,还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引,每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

Hash结构效率高，那为什么索引结构要设计成树型呢?

原因1: Hash 索引仅能满足(=) (<>) 和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为0(n);而树型的“有序”特性，依然能够保持0(log2N)的高效率。
原因2: Hash 索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。
原因3:对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
原因4:对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

B-树

B+树

B+树和B树的差异在于以下几点:

有k个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数+1。
非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大(或最小)。
非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

但是B+树和B树有个根本的差异在于，B+树的中间节点并不直接存储数据。这样的好处都有什么呢?

首先，B+ 树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。
其次，B+树的查询效率更高。这是因为通常B+树比B树更矮胖(阶数更大，深度更低)，查询所需要的磁盘I/0也会更少。同样的磁盘页大小，B+ 树可以存储更多的节点关键字。
不仅是对单个关键字的查询上,在查询范围上, B+ 树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，叶子节点之间会有指针,数据又是递增的，这使得我们范围查找可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

Hash索引与B+树索引的区别

我们之前讲到过B+树索引的结构，Hash 索引结构和B+树的不同，因此在索引使用上也会有差别。

Hash 索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
Hash 索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash 索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
Hash 索引不支持ORDER BY排序，因为Hash索弓|指向的数据是无序的，因此无法起到排序优化的作用，而B+树索弓数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE 后面后模糊查询(比如%结尾)的话就可以起到优化作用。
InnoDB不支持Hash索引

InnoDB的数据存储结构

1.数据库的存储结构：页

索引结构给我们提供了高效的索引方式,不过索引信息以及数据记录都是保存在文件上的，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的，MySQL服务器上的存储引擎负责对表中数据的读取和写入工作。
不同存储引擎中存放的格式一般是不同的，甚至有的存储引擎比如Memory都不用磁盘来存储数据。
由于InnoDB是MySQL的默认存储引擎，所以本章剖析InnoDB存储弓|擎的数据存储结构。

1.1磁盘与内存交互基本单位：页

InnoDB将数据划分为若干个页，InnoDB中页的大小默认为16KB。
以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page) ,数据库I/O操作的最小单位是页。一个页中可以存储多个行记录。

页的上层结构

另外在数据库中，还存在着区(Extent) 、段(Segment) 和表空间(Tablespace) 的概念。行、页、区、段、表空间的关系如下图所示：

区(Extent) 是比页大一级的存储结构,在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64* 16KB= 1MB。
段(Segment)由一个或多个区组成，区在文件系统是一个连续分配的空间(在 InnoDB中是连续的64个页)，不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在
当我们创建数据表、索引的时候，就会相应创建对应的段,比如创建一张表时会创建一个表段, 创建一个索引时会创建一个索引段。
表空间(Tablespace) 是一个逻辑容器，表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

页的内部结构

索引的创建与设计原则

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从功能逻辑上说，索引主要有4种，分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为2种:聚簇索引和非聚簇索引。
按照作用字段个数进行划分,分成单列索引和联合索引。

小结:不同的存储引擎支持的索引类型也不一样

InnoDB :支持B-tree、Full-text 等索引，不支持Hash索引; InnoDB：支持B-tree、全文等索引，不支持Hash索引；
MyISAM :支持B-tree、Full-text 等索引，不支持Hash索引; MyISAM：支持B-tree、全文等索引，不支持Hash索引；
Memory :支持B-tree、Hash 等索引,不支持Full-text索引; 内存：支持B-tree、Hash等索引，不支持全文索引；
NDB :支持Hash索引，不支持B-tree、Full-text 等索引; NDB：支持Hash索引，不支持B-tree、全文等索引；
Archive :不支持B-tree、Hash、 Full-text 等索引|; Archive :不支持B-tree、Hash、 Full-text 等索引;

索引的设计原则

哪些情况适合创建索引

1.字段的数值具有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

2.频繁作为WHERE查询条件的字段

某个字段在SELECT语句的WHERE条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3.经常GROUP BY和ORDER BY的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROUP BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

4.UPDATE/DELETE的WHERE条件列

对数据按照某个条件进行查询后再进行UPDATE或DELETE的操作，如果对WHERE字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据WHERE条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT字段需要创建索引

你能看到SQL查询效率有了提升,同时显示出来的student_ id还是按照递增的顺序进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

6.多表JOIN连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
最后，‘ 对用于连接的字段创建索引‘，并且该字段在多张表中的类型必须一致。比如course_ _id 在student_ jinfo 表和course表中都为int(11)类型，而不能一个为int另一个为varchar类型。

7.使用列类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。
我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、 INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。这是因为:

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录，从而减少磁盘I/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

8.使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:

B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

9.区分度高（散列性高）的列适合作为索引

列的基数指的是某一列中不重复数据的个数,比方说某个列包含值2，5，8， 2，5，8, 2，5，8,虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
可以使用公式select count(distinct a)/count(*) from t1计算区分度,越接近1越好，一般超过33%就
算是比较高效的索引了。
拓展:联合索引把区分度高(散列性高)的列放在前面。

10.使用最频繁的列放到联合索引的左侧

11.在多个字段都要创建索引的情况下，联合索引优于单值索引

哪些情况不适合创建索引

① 在where中使用不到的字段，不要设置索引

② 数据量小的表最好不要使用索引

③ 有大量重复数据的列上不要建立索引
结论：当数据重复度大，比如`高于 10% `的时候，也不需要对这个字段使用索引。

④ 避免对经常更新的表创建过多的索引

⑤ 不建议用无序的值作为索引

⑥ 删除不再使用或者很少使用的索引

⑦ 不要定义冗余或重复的索引

sql优化分析

1sql执行慢首先看能不能提升硬件，
2开启数据库的慢sql记录查询，或者德鲁伊上开启
3根据sql查看他的执行计划，有没有命中索引
4如果命中，查看检索的的行数，就比如在性别字段加上索引，虽然有索引但是数据还是很大
5有没有file sort，尽可能避免
6禁止超过3张超join
7根据sql建立有效索引，提高命中

#查看表中的索引
show index from student_info;
#student_id字段上没有索引
SELECT course_id,class_id,`name`,create_time,student_id
FROM student_info 
WHERE student_id=123110;  #362ms
#给student_id添加索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);   #2195ms
#student_id字段上有索引
SELECT course_id,class_id,`name`,create_time,student_id
FROM student_info 
WHERE student_id=123110;    #31ms
#student_id字段上有索引的：
SELECT student_id,COUNT(*) AS num 
FROM student_info
GROUP BY student_id LIMIT 100;  #29ms
#删除student_id索引
DROP INDEX idx_sid ON student_info;
#student_id字段上没有索引的：
SELECT student_id,COUNT(*) AS num 
FROM student_info
GROUP BY student_id LIMIT 100;  #2621ms

#再测试
#添加单列索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);

#
EXPLAIN SELECT student_id,COUNT(*) as num 
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;                  #3136ms

#添加联合索引
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id,create_time DESC);

#
EXPLAIN SELECT student_id,COUNT(*) as num 
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;                #380ms

#再进一步
ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC,student_id);

DROP INDEX idx_sid_cre_time on student_info;

SELECT student_id,COUNT(*) as num 
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;              #3697ms


UPDATE student_info SET student_id =10002
WHERE NAME = '462eed7ac6e791292a79';     #701ms
#添加索引
ALTER TABLE student_info
ADD INDEX idx_name(NAME);

UPDATE student_info SET student_id =10001
WHERE NAME = '462eed7ac6e791292a79';    #33ms

SELECT s.course_id,s.`name`,s.student_id,c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE s.NAME = '462eed7ac6e791292a79';  #32ms

DROP INDEX idx_name on student_info;


SELECT s.course_id,s.`name`,s.student_id,c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE s.NAME = '462eed7ac6e791292a79';   #397ms

性能分析工具的使用

查看系统性能参数

定位执行慢的SQL：慢查询日志

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_ query_time 值的SQL,则会被记录到慢查询日志中。long_ query _time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。
它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询,并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sq|执行超过5秒钟，我们就算慢SQL, 希望能收集超过5秒的sql,结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。

//查看慢查询日志是否开启
show variables like '%slow_query_log';
//查看慢查询时间
show variables like '%long_query_time%';
//查看慢查询的sql语句条数
show status like 'slow_queries';

慢查询日志分析工具：mysqldumpslow

#分析慢查询日志中最近的5条SQL纪录  /var/lib/mysql/localhost-slow.log：日志文件地址
mysqldumpslow -a -s t -t 5 /var/lib/mysql/localhost-slow.log;

查看SQL执行成本: SHOW PROFILE

#查看是否开启
show variables like'profiling';
#查看纪录
show variables like'profiling';
#查看最近一条SQL的执行成本
show profile;
#查看指定SQL的执行成本
show profile for query 2;

分析查询语句：EXPLAIN

定位了查询慢的SQL之后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。 DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。


#1. table：表名
#查询的每一行记录都对应着一个单表
EXPLAIN SELECT * FROM s1;

#s1:驱动表  s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
 SELECT * FROM s1 WHERE key1 = 'a';


 SELECT * FROM s1 INNER JOIN s2
 ON s1.key1 = s2.key1
 WHERE s1.common_field = 'a';


 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key3 FROM s2);


 SELECT * FROM s1 UNION SELECT * FROM s2;


 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 
 
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
 
 #Union去重
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 
 EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;
 
 
 #3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色
 
 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1;
 
 
 #连接查询也算是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 
 
 #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
 #查询的`select_type`值就是`PRIMARY`
 
 
 #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
 #以外，其余的小查询的`select_type`值就是`UNION`
 
 #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是
 #`UNION RESULT`
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
 
 #子查询：
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。
 #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，
 #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
 #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。
 
 
 #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
 #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
 
 
 #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
 EXPLAIN SELECT * 
 FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;
 
 
 #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
 #该子查询对应的`select_type`属性就是`MATERIALIZED`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表
 
 
 
 # 4. partition(略)：匹配的分区信息
 
 
 # 5. type：针对单表的访问方法
 
 #当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，
 #那么对该表的访问方法就是`system`。
 CREATE TABLE t(i INT) ENGINE=MYISAM;
 INSERT INTO t VALUES(1);
 
 EXPLAIN SELECT * FROM t;
 
 #换成InnoDB
 CREATE TABLE tt(i INT) ENGINE=INNODB;
 INSERT INTO tt VALUES(1);
 EXPLAIN SELECT * FROM tt;
 
 
 #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;
 
 EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
 
 
 #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
 #对该被驱动表的访问方法就是`eq_ref`
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
  
  
 #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 
 #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法
 #就可能是`ref_or_null`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
 
 
 #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、
 #`Sort-Union`这三种索引合并的方式来执行查询
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
 
 
 #`unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询
 #转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type`
 #列的值就是`unique_subquery`
 EXPLAIN SELECT * FROM s1 
 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';
 
 
 #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法
 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
 
 #同上
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
 
 
 #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`
 EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
 
 
 #最熟悉的全表扫描
 EXPLAIN SELECT * FROM s1;
 
 
 #6. possible_keys和key：可能用到的索引 和  实际上使用的索引
 
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
 

 
#7.  key_len：实际使用到的索引长度(即：字节数)
# 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;


 EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;


 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';


 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

 
 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
 
 EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';
 
#练习：
#varchar(10)变长字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

#varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

#char(10)固定字段且允许NULL    = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

#char(10)固定字段且不允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)
 
 
 
 # 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
 #比如只是一个常数或者是某个列。
 
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
 
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
 
 
 # 9. rows：预估的需要读取的记录条数
 # `值越小越好`
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
 
 
 
 # 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
 
 #如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用
 #到对应索引的搜索条件外的其他搜索条件的记录有多少条。
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
 
 
 #对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询
 #中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered)
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
 
 
 #11. Extra:一些额外的信息
 #更准确的理解MySQL到底将如何执行给定的查询语句
 
 
 #当查询语句的没有`FROM`子句时将会提示该额外信息
 EXPLAIN SELECT 1;
 
 
 #查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息
 EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
 
 
 #当我们使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`
 #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。
 EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
 
 
 #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中
 #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
 
 
 #当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中
 #的搜索条件的记录时，将会提示该额外信息
 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
 
 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'NlPros'; #NlPros 是 s1表中key1字段真实存在的数据
 
 #select * from s1 limit 10;
 
 #当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以
 #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息。比方说下边这个查询中只
 #需要用到`idx_key1`而不需要回表操作：
 EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';
 
 
 #有些搜索条件中虽然出现了索引列，但却不能使用到索引
 #看课件理解索引条件下推
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
 
 
 #在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为
 #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法`
 #见课件说明
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
 
 
 #当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，
 #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息
 EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
 
 
 #如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引
 #合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称；
 #如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询；
 #出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
 
 
 #当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
 EXPLAIN SELECT * FROM s1 LIMIT 0;
 
 
 #有一些情况下对结果集中的记录进行排序是可以使用到索引的。
 #比如：
 EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
 
 
 #很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）
 #进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。
 
 #如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示
 EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
 
 
 #在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们
 #在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成
 #查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行
 #计划的`Extra`列将会显示`Using temporary`提示
 EXPLAIN SELECT DISTINCT common_field FROM s1;
 
 #EXPLAIN SELECT DISTINCT key1 FROM s1;
 
 #同上。
 EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
 
 #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以
 #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可
 EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
 
#json格式的explain
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE s1.common_field = 'a';

SHOW WARNINGS可以查看最近一条SQL被优化之后的情况

淘宝数据库，主键如何设计

建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员,我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。

范式

范式简介

在关系型数据库中,关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
范式的英文名称是Normal Form ，简称NF。它是英国人E.F.Codd在上个世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。

范式都包括哪些

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是:第一范式(1NF)、第二范式(2NF) .第3范式(3NF) 、巴斯科德范式(BCNF) 、第四范式(4NF) 和第五范式(5NF, 又称完美范式)
数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求，满定最低要求的范式是第一范式(1NF) 。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF) ，其余范式以次类推。
一般来说，在关系型数据库设计中，最高也就遵循到BCNF ，普遍还是3NF。但也不绝对,有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。

第一范式

第一范式主要是确保数据表中每个字段的值必须具有原子性,也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。

第二范式

第二范式要求,在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键,不能只依赖主键的一部分。如果知道主键的所有属性的值，就可以检索到任何元组(行)的任何属性的任何值。(要求中的主键，其实可以拓展替换为候选键)

第三范式

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。 (即，不能存在非主属性A依赖于非主属性B,非主属性B依赖于主键C的情况，即存在“A→B→C”的决定关系)通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

数据库事务

事务是数据库区别于文件系统的重要特性之一，当我们有了事务就会让数据库始终保持一致性，同时我们还能通过事务的机制恢复到某个时间点,这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失。

事务的ACID特性

原子性（atomicity）

原子性是指事务是一个不可分割的工作单位,要么全部提交,要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间的状态。如果无法保证原子性会怎么样?就会出现数据不一致的情形， A账户减去100元，而B账户增加100元操作失败,系统将无故丢失100元。

一致性（consistency）

根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。

隔离性（isolation）

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行,从而使事务具有持久性。

数据并发问题

1.脏写（Dirty Write）

对于两个事务Session A、Session B,如果事务SessionA修改了另一下未提交事务Session B修改过的数据，那就意味着发生了脏写，示意图如下:

Session A和Session B各开启了一个事务， Session B中的事务先将studentno列为1的记录的name列更新为'李四'，然后Session A中的事务接着又把这条studentno列为1的记录的name列更新为张三'。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在,这种现象就称之为脏写。这时Session A中的事务就没有效果了，明明把数据更新了，最后也提交事务了，最后看到的数据什么变化也没有。这里大家对事务的隔离级比较了解的话，会发现默认隔离级别下，上面SessionA中的更新语句会处于等待状态。

2.脏读（Dirty Read）

对于两个事务SessionA、SessionB, SessionA 读取了已经被SessionB更新但还没有被提交的字段。之后若SessionB回滚，Session A读取的内容就是临时且无效的。

3.不可重复读（Non-Repeatable Read）

对于两个事务Session A、Session B, SessionA 读取了一个字段，然后SessionB更新了该字段。之后SessionA再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

4.幻读（Phantom）

对于两个事务Session A、Session B, Session A从一个表中读取了一个字段，然后Sebsion B在该表中插入了一些新的行。之后，如果SessionA再次读取同一个表，就会多出几行。那就意味着发生了幻读。

严重性排序：脏写>脏读>不可重复读>幻读

MySQL的事务日志

1.redo日志

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘( checkPoint机制),通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快。

InnoDB引擎的事务采用了WAL技术( Write-Ahead Logging) ，这种技术的思想就是先写日志，再写磁盘,只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷到磁盘的时候，可以通过redo log来恢复，保证ACID中的D，这就是redo log的作用。

REDO日志的好处和特点

1.好处

redo日志降低了刷盘频率
redo日志占用空间非常小

存储表空间ID，页号，偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

2.特点

1.redo日志是顺序写入磁盘的

在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的也就是使用顺序I0，效率比随机IO快。

2.事务执行过程中，redo log不断纪录

redo log跟bin log的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务, 对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录,而bin log不会记录,直到这个事务提交，才会一次写入到bin log文件中。

redo的组成

Redo log可以简单的分为以下两个部分：

重做日志的缓冲（redo log buffer），保存在内存中，是易失的。

在服务器启动时就向操作系统申请了一大片称之为redo log buffer的连续内存空间，翻译成中文就是redo日志缓冲区。这片内存空间被划分成若干个连续的redo log block。一个redo log block占用512字节大小。
参数设置：innodb_log_buffer_size:

redo log buffer大小，默认16M，最大值4096M，最小值为1M。

重做日志文件（redo log file），保存在硬盘中，是持久的。

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存(page cache)中去(这是现代操作系统为了提高文件写入效率做的一个优化)，真正的写入会交给系统自己来决定(比如page cache足够大了)。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了(虽然整个系统宕机的概率还是比较小的)。
针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit参数，该参数控制commit提交事务时，如何将redo log buffer中的日志刷新到redo log file中。它支持三种策略:

设置为0 :表示每次事务提交时不进行刷盘操作。 (系统默认master thread每隔1s进行一次重做日志的同步)
设置为1 : 表示每次事务提交时都将进行同步，刷盘操作(默认值 )
设置为2:表示每次事务提交时都只把redo log buffer内容写入page cache,不进行同步。由os自己决定什么时候同步到磁盘文件。

写入redo log buffer过程

1.补充概念：Mini-Transaction

MySQL把对底层页面中的一次原子访问的过程称之为一个Mini-Transaction,简称mtr ,比如，向某个索引对应的B+树中插入一条记录的过程就是一个Mini-Transaction. 一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo日志作为一个不可分割的整体。

2.redo日志写入log buffer

向log buffer 中写入redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写。当我们想往log buffer 中写入redo日志时，第一个遇到的问题就是应该写在哪个block的哪个偏移量处，所以InnoDB的设计者特意提供了一个称之为buf_ free 的全局变量，该变量指明后续写入的redo日志应该写入到log buffer 中的哪个位置，如图所示：

Undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入应该undo log。

Undo日志的作用

作用1：回滚数据

用户对undo日志可能有误解: undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

作用2：MVCC
undo的另一个作用是MVCC,即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo页的重用

锁

读-写或写-读情况

读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读、不可重复读、幻读的问题。
各个数据库厂商对SQL标准的支持都可能不一样。比如MySQL在REPEATABLE READ 隔离级别上就已经解决了幻读问题。

并发问题的解决方案

怎么解决脏读，不可重复读，幻读这些问题了？

方案一：读操作利用多版本并发控制（MVCC），写操作进行加锁

所谓的MVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本(历史版本由undo日志构建)。查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录
的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。

普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
●在READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个
ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象;
●在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView,之后的SELECT操作都复用这个ReadView,这样也就避免了不可重复读和幻读的问题。

方案二：读-写操作都采用加锁的方式

如果我们的一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本。比如，在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。
这样在读取记录的时候就需要对其进行加锁操作,这样也就意味着读操作和写操作也像写-写操作那样排队执行。
脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录,如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。
不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁,那么另一个事务就无法修改该记录,自然也不会发生不可重复读了。
幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录。采用加锁的方式解决幻读问题就有一些麻烦，因为当前事务在第一次读取记录时幻影记录并不存在，所以读取的时候加锁就有点尴尬(因为你并不知道给谁加锁)。

小结对比发现：

采用MVCC方式的话，读-写操作彼此并不冲突，性能更高。
采用加锁方式的话，读-写操作需要排队执行，影响性能。

一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行。

锁的不同角度分类

1.从数据操作的类型划分：读锁，写锁

对于数据库中并发事务的读-读情况并不会引起什么问题。对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以MySQL实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁(Shared Lock, S Lock)和排他锁(Exclusive Lock, X Lock),也叫读锁(readlock) 和写锁(write lock)。

读锁:也称为共享锁、英文用S表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响,相互不阻塞的。
写锁:也称为排他锁、英文用X表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入,并防止其他用户读取正在写入的同一资源。

需要注意的是对于InnoDB引擎来说，读锁和写锁可以加在表上，也可以加在行上。

读操作

写操作

平常所用到的写操作无非是DELETE、UPDATE、 INSERT 这三种:
●DELETE :
对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，再执
行delete mark 操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定
读。
●UPDATE :在对一条记录做UPDATE操作时分为三种情况:

情况1:未修改该记录的键值，并且被更新的列占用的存储空间在修改前后未发生变化。

则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。

情况2:未修改该记录的键值，并且至少有一个被更新的列占用的存储空间在修改前后发生变化。

则先在B+树中定位到这条记录的位置，然后获取一下记录的 X锁，将该记录彻底删除掉(就是把记录彻底移入垃圾链表)，最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。

情况3:修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。

●INSERT :

一般情况下，新插入一条记录的操作并不加锁，通过一种称之为隐式锁的结构来保护这条新插入的记录在本事务提交前不被别的事务访问。

2.从数据操作的粒度划分：表级锁，页级锁，行锁

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情(涉及获取、检查、释放锁等动作)。因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度(Lock granularity) ”的概念。
对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细;其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁。

1.表锁（Table Lock）

该锁会锁定整张表，它是MySQL中最基本的锁策略,并不依赖于存储引擎(不管你是 MySQL的什么存储引擎,对于表锁的策略都是一样的)，并且表锁是开销最小的策略(因为粒度比较大)。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

①表级别的S锁和X锁

在对某个表执行SELECT、INSERT、 DELETE、 UPDATE语句时， InnoDB存储弓|擎是不会为这个表添加表级别的S锁或者X锁的。在对某个表执行一些诸如ALTER TABLE、DROP TABLE 这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、 DELETE、 UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、 UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁(英文名: Metadata Locks ，简称MDL )结构来实现的。
一般情况下，不会使用InnoDB存储引擎提供的表级别的S锁和X锁。只会在一些特殊情况下，比方说崩溃恢复过程中用到。比如，在系统变量autocommit=0，innodb_ table _locks = 1 时，手动获取InnoDB存储引擎提供的表t的S锁或者X锁可以这么写:
●LOCK TABLES t READ : InnoDB存储引擎会对表t加表级别的S锁。
●LOCK TABLES t WRITE : InnoDB存储引擎会对表t加表级别的X锁。
不过尽量避免在使用InnoDB存储引擎的表上使用LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的行锁，关于InnoDB表级别的S锁和X锁大家了解一下就可以了。

②意向锁（intention lock）

InnoDB支持多粒度锁(multiple granularity locking) ，它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。
1、意向锁的存在是为了协调行锁和表锁的关系,支持多粒度(表锁与行锁)的锁并存。
2、意向锁是一种不与行级锁冲突表级锁，这一点非常重要。
3、表明“某个事务正在某些行持有了锁或该事务准备去持有锁”
意向锁分为两种: [
●意向共享锁(intention shared lock, IS) :事务有意向对表中的某些行加共享锁(S锁)
--事务要获取某些行的S锁，必须先获得表的IS锁
SELECT column FROM table .. ，LOCK IN SHARE MODE;
●意向排他锁(intention exclusive lock, IX) :事务有意向对表中的某些行加排他锁(X锁)
--事务要获取某些行的X锁，必须先获得表的IX锁。
SELECT column FROM table .. . FOR UPDATE;
即:意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在数据表的对应意向锁。
在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁,告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

③自增锁（AUTO-INC锁）

MySQL中采用了自增锁的方式来实现，AUTO-INC锁是当向使用含有AUTO_ INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_ INCREMENT修饰的列分配递增的值,在该语句执行结束后，再把AUTO-INC锁释放掉。一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。也正因为此，其并发性显然并不高，当我们向一个有AUTO_ INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的，所以innodb通过innodb_ autoinc_ lock_ mode的不同取值来提供不同的锁定机制，来显著提高SQL语句的可伸缩性和性能。

innodb_ autoinc_ lock_ mode有三种取值,分别对应与不同锁定模式:
(1) innodb_ autoinc. lock_ mode = 0(“传统” 锁定模式)
在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_ INCREMENT列的表。每当执行insert的时候，都会得到一个表级锁(AUTO-INC锁),使得语句中生成的auto increment为顺序，且在binlog中重放的时候，可以保证master与slave中数据的auto_ jincrement是相同的。因为是表级锁，当在同一时间多个事务中执行insert的时候，对于AUTO-INC锁的争夺会限制并发能力。

(2) innodb_ autoinc_ lock_ mode = 1("连续 ”锁定模式)
在MySQL 8.0之前,连续锁定模式是默认的。

在这个模式下，“bulk inserts"仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT ... SELECT,REPLACE .. SELECT和LOAD DATA语句。

同一时刻只有一个语句可以持有AUTO-INC锁。对于“Simple inserts”(要插入的行数事先已知)，则通过在mutex (轻量锁)的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simple inserts’等待AUTO-INC锁,如同它是一个“bulk inserts"。

④元数据锁（MDL锁）

2.InnoDB中的行锁

行锁(Row Lock)也称为记录锁，顾名思义,就是锁住某一行(某条记录row)。需要的注意的是，MySQL 服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。
优点:锁定力度小，发生锁冲突概率低，可以实现的并发度高。
缺点:对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。
InnoDB与MyISAM的最大不同有两点:一是支持事务 (TRANSACTION) ;二是采用了行级锁。

①纪录锁（Record Locks）

记录锁也就是仅仅把一条记录锁上,官方的类型名称为: LOCK_ REC_ NOT_GAP。

记录锁是有S锁和X锁之分的，称之为S型记录锁和X型记录锁。

当一个事务获取了一条记录的S型记录锁后,其他事务也可以继续获取该记录的s型记录锁，但不可以继续获取x型记录锁;
当一个事务获取了一条记录的x型记录锁后，其他事务既不可以继续获取该记录的s型记录锁，也不可以继续获取x型记录锁。

②间隙锁（Gap Locks）

MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC方案解决,也可以采用加锁方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为Gap Locks的锁，官方的类型名称为: LOCK_ GAP，我们可以简称为gap锁。

③临键锁（Next-key Locks）

有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，所以InnoDB就提出了一种称之为Next-Key Locks 的锁，官方的类型名称为: LOCK_ ORDINARY ，我们也可以简称为next-key锁。Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁，innodb默认的锁就是Next-Key locks。

next-key锁的本质就是一个记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

④插入意向锁（Insert Intention Locks）

3.从对待锁的态度划分：乐观锁和悲观锁

1.悲观锁（Pessimistic Locking）

注意: select ... for update语句执行过程中所有扫描的行都会被锁上，因此在MySQL中用悲观锁必须确定使用了索引，而不是全表扫描，否则将会把整个表锁住。

2.乐观锁（Optimistic Locking）

1.乐观锁的版本号机制

在表中设计一一个版本字段version, 第一-次读的时候，会获取version字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE ...SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改,修改就不会成功。

2.乐观锁的时间戳机制

时间戳和版本号机制一样,也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较,如果两者一致则更新成功,否则就是版本冲突。

你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳(版本号或者时间戳)，从而证明当前拿到的数据是否最新。

4.按照加锁的方式划分：显示锁和隐式锁

即:一个事务对新插入的记录可以不显式的加锁(生成一个锁结构) ，但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构,然后自己再生成一一个锁结构后进入等待状态。隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。

隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时,隐式锁转化为显式锁。

5.其他锁之：全局锁

锁的内存结构

所以决定在对不同记录加锁时，如果符合下边这些条件的记录会放到一个锁结构中。

在同一个事务中进行加锁操作
被加锁的记录在同一个页面中
加锁的类型是一样的
等待状态是一样的

InnoDB存储弓|擎中的锁结构如下:

结构解析：

1.锁所在的事务信息：

不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记录这个事务的信息。
此锁所在的事务信息在内存结构中只是一个指针,通过指针可以找到内存中关于该事务的更多信息，比方说事务id等。

2.索引信息：

对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

3.表锁/行锁信息：

表锁结构和行锁结构在这个位置的内容是不同的：

表锁：记载着是对哪个表加的锁，还有一些其他信息。

行锁：

记载了三个重要的信息：

Space ID:记录所在表空间。
Page Number :记录所在页号。
n_ bits: 对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位,这个n_ bits 属性代表使用了多少比特位。

n_ bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后也不至于重新分配锁结构

锁监控

多版本并发控制

1.什么是MVCC

MVCC (Multiversion Concurrency Control)，多版本并发控制。顾名思义, MVCC 是通过数据行的多个版本管理实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之,就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

MVCC没有正式的标准，在不同的DBMS中MVCC的实现方式可能是不同的，也不是普遍使用的(大家可以参考相关的DBMS文档)。这里讲解InnoDB中MVCC的实现机制(MySQL其它的存储引擎并不支持它)。

2.快照读和当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，而这个读指的就是快照读,而非当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

2.1快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的SELECT都属于快照读，即不加锁的非阻塞读;比如这样:
SELECT * FROM player WHERE....
之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC,它在很多情况下，避免了加锁操作，降低了开销。
既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读.

2.2当前读

隐藏字段和Undo log版本链

对于使用InnoDB存储引的表来说，他的聚簇索引纪录中都包含两个必要的隐藏列。

trx_id:每次一个事务对某条聚簇索引纪录进行修改时。都会把该事务的事务i赋值给trx_id隐藏列。
roll_pointer:每次对某个聚簇索引纪录进行改动时，都会把旧的版本写入到Undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该纪录修改前的信息。

MVCC实现原理之ReadView

MVCC的实现依赖于：隐藏字段，undo log，ReadVIew。

什么是ReadView

在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView'了，它帮我们解决了行的可见性问题。
ReadView就是事务A在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB 为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID (“活跃”指的就是，启动了但还没提交)。

ReadView的规则

MVCC整体操作流程

在隔离级别为读已提交（Read-Committed）时，一个事务中的每一次select查询都会重新获取一次Read View。这时因为Read View不同，就可能产生不可重复读或者幻读的情况。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次select的时候会获取一次Read View，而后面所有的select都会复用这个Read View。

其他数据库日志

日志的弊端

日志功能会降低MySQL数据库的性能。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL 数据库会花费很多时间记录日志。
日志会占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。

通用查询日志（genral query log）

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

#查看通用查询日志的开启状态
show variables like '%general%';
#临时性开启通用查询日志
set global general_log = on;

错误日志（error log）

错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。
通过错误日志可以查看系统的运行状态，便于即时发现故障、修复故障。如果MySQL服务出现异常,错误日志是发现问题、解决故障的首选。

启动日志

二进制日志（bin log）

binlog可以说是MySQL中比较重要的日志了，在日常开发及运维过程中，经常会遇到。
binlog即binary log,二进制日志文件，也叫作变更日志(update log)。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句(如数据查询语句select、show等)。
它以事件形式记录并保存在二进制文件中。通过这些信息，我们可以再现数据更新操作的全过程。

binlog主要的应用场景：

●一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改,然后根据二进制日志文件中的记录来恢复数据库服务器。
●二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog,需要依靠binlog来同步数据，保证数据一致性。

数据恢复

/usr/bin/mysqlbinlog --start-position=236 --stop-position=1087 --database=atguigudb2 /var/lib/mysql/binlog.000016 | /usr/bin/mysql -uroot -p123456 -v atguigudb2;


#  --start-position=起始位置
#  --stop-position=结束位置
#  --database=数据库名称
#  /var/lib/mysql/binlog.000016:bin log的全路径

主从复制

朝暮学java

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySql高级

首先进行linux版本的安装，我们选择的是使用centos7作为服务器。安装的mysql版本为8.0.28.本地连接mysql连不上可能得原因：端口未开放/防火墙未关/root用户一般只允许本机登录，需要修改服务器上mysql的数据库。mysql5.7默认字符集是latin1 mysql8.0默认字符集是utf8mb4修改字符集编码：windows下是在my.ini文件中，linux是在my.cnf文件中。字符集与比较规则...
复制链接

扫一扫