Mysql高级篇

存储引擎

mysql体系结构

存储引擎简介

飞机、直升飞机、火箭，他们的动力都来自于引擎，但是因为应用类型的不同，所以将引擎也不会相同。不能将火箭的引擎放到直升飞机中，反之更不行，所以引擎的使用和应用场景有着十分重要的关系

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的而不是基于库的，所以存储引擎也可被称为表类型。

我们看一下emp的建表语句

1	show create table emp；

但是我们在建表时并没有引擎，为什么会是innnoDB？

在mysql 5.5版本后建表的默认引擎为 innoDB

在建表的时候就可以指定存储引擎

1 2	craete table 表名( ) ENGINE=XXXX;

查询该数据库所支持的存储引擎

1	show engines;

存储引擎特点

InnoDB

介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。

特点

DML操作遵循ACID模型，支持事务;
行级锁，提高并发访问性能;
支持外键FOREIGN KEY约束，保证数据的完整性和正确性;

文件

xc.ibd: xx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi)、数据和索引。

参数：innodb_file_per_table

1	show variables like 'innodb_file_per_table';

逻辑存储结构

Tablespece:表空间
segment:段
Extent:区
Page:页
Row:行

MyISAM

介绍

MylSAM是MySQL早期的默认存储引擎。

特点:

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件

XXx.sdi:存储表结构信息
XXX.MYD:存储数据
xXX.MYI:存储索引

memory

介绍

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点

内存存放
hash索引（默认)

文件

XXx.sdi:存储表结构信息

区别

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB:是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致程，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MYISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

索引概述

索引(index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引演示

索引的优缺点

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种;

B+树索引

正常二叉树

顺序插入后的二叉树

二叉树缺点∶顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

红黑树

红黑树:大数据量情况下，层级较深，检索速度慢。

B-Tree

若最大度数是3

按顺序插入 1-~15

以下网站时BTree的演变动画示例

B-Tree Visualization (usfca.edu)

B+Tree

所有数据都会出现在叶子节点

若最大度数是3则

相对于B-Tree区别:

所有的数据都会出现在叶子节点
叶子节点形成一个单向链表

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞)，可以通过链表来解决。

Hash索引的特点

Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，<，…)
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持

在MySQL中，支持hash索引的是Memory引擎，而innoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么InnoDB引擎选择用B+树来作为索引结构？

相对于二叉树，层级更少，搜索效率高;
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;
相对Hash索引，B+tree支持范围匹配及排序操作;

索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

InnoDB主键索引的B+tree高度为多少？

假设:

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。

若树的高度为2那么这个B+树可以存放多少行数据：

设一页中的主键有n个，那么对应的指针就会有n+1个
所以可列出等式 : n*8+(n+1)*6= 16*1024 解得 1170,所以一页中会有指针1171个
指针指向的是一页数据，而一页中恰好有16行数据所以有：
如果B+树高度为2 那么它可存储 1171*16=18736条数据

若树的高度为3那么这个B+树可以存放多少行数据：

和上述问题相似：
在第一层的一页中有1171个指针指向的是1171个页,1171个页中有1171*1171个指针指向叶子结点页（数据存放页）
所以结果为:
1171*1171*16=2193856

索引语法

创建索引

1	create [UNIQUE\|FULLTEXT] INDEX index_name on table_name (index_col_name,……);

查看索引

1	SHOW INDEX FROM table_name;

删除索引

1	DROP INDEX index_name on table_name;

SQL 性能分析

SQL执行频率

MySQL客户端连接成功后，通过show [sessionlglobal|status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

1	SHOW GLOBAL STATUS LIKE 'com_______'

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认19秒）的所有5QL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf）中配置如下信息:

#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

在配置文件中修改后我们可以看到

查看慢日志

慢日志信息储存在 /var/lib/mysql目录中，日志文件为主机名-slow.log

读取最近更新的日志文件

1	tail -f 日志文件

profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作:

1	select @@have_profiling;

默认的profiling是关闭的，可以通过set语句在session/global级别开启profiling;

1	set profiling =1;

当开启profile后我们可以查看最近15条语句的执行速度

1	show profiles;

查看指定Query_id的SQL语句各个阶段的耗时情况

1	show profile for query query_id;

查看指定query_id的SQL语句CPU使用情况

1	show profile cpu for query query_id;

explain执行计划

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

1	explain\|desc select * from 表名 where 条件

其中

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。

select_type:表示SELECT的类型，常见的取值有SIMPLE〈简单表，即不使用表连接或者子查询)、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询）等

v2-edc6cb72b798d913218a5d941ebd1043_720w

v2-14bbb4943dbcfc8405f2582e0bc3a058_720w

type:

连接类型，有如下几种取值，性能从好到坏排序 如下：

system：该表只有一行（相当于系统表），system是const类型的特例
const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。
ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。

ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。

最左前缀原则，指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是：

WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引；
WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3就无法匹配该索引。

fulltext：全文索引
ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询
index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。
range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
- 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
- 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。
ALL：全表扫描，性能最差。

possible_keys

展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

key

表示MySQL实际选择的索引

key_len

索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。

如何计算？https://www.cnblogs.com/gomysql/p/4004244.html

ref

表示将哪个字段或常量和key列所使用的字段进行比较。

如果ref是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。

rows

MySQL估算会扫描的行数，数值越小越好。

filtered

表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。

索引使用

当我们的表中有1000W条数据时，查询一条数据是一件非常痛苦的事情

是的，的确非常痛苦，痛苦到能摸16s172ms的鱼

当我们对该表的该字段创建索引

1	create index index_sku_sn on tb_sku(sn);

很开心又摸了小一分钟的鱼。其实我们不难理解为何创建索引需要这么长时间，实际上创建索引是以已有的数据创建一个B+树，1000W条数据对于我们来讲已经很大了，所以需要很长时间

这时我们再去进行查询

5555555，没法摸鱼了。所以创建索引属于磨刀不误砍柴工了

索引的使用原则

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

例如：

我们创建时的索引字段顺序为 (phone,age,gender)

符合最左前缀法则，索引生效

不符合最左前缀法则，索引失效

为什么key_len与只有phone条件的长度一致？

因为跳跃了age字段，不符合最左前缀法则，索引部分失效了

这时我们将顺序打乱，然后发现

不对啊，怎么还是都走了索引？这到底是不是符合最左前缀法则

答案是符合的，因为只要我们定义索引的最左字段在，无关顺序，依旧符合最左前缀法则

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

索引部分失效了，但是key_len和一样，所以是gender部分失效了

如何规避？

在进行范围查询时，尽量使用>= 和<=

索引列运算

不要在索引列上进行运算操作,索引将失效。

索引失效

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

索引失效

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

索引失效，ID字段有主键索引，age并未有单独索引所以索引失效

索引失效 phone 符合索引原则，age不符合，所以失效

解决办法：给age也整个索引

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是SQL语句中加入一些人为的提示来达到优化操作的目的

use index 使用索引:

1	explain select * from 表名 use index(索引名) where 条件

use为推荐给MySQL使用，但如果推荐的索引性能与所要查询的数据并不好，MySQL就会选择不走索引

ignore index 忽略某个索引:

1	explain select * from 表名 ignore index(索引名) where 条件

force index 必须用这个索引:

1	explain select * from 表名 force index(索引名) where 条件

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *。

当我们的二级索引返回不了要查询的所有字段时，需要进行回表查询，用二级索引的查询到的id去聚集索引查找对应的列，然后找出对应的值

例如上图，我们要查询gender，但使用的是name的辅助索引（二级索引），查询到是arm后，拿到所对应的数据 id=2，就用id去聚集索引中查询id=2的行数据，然后返回行中的gender 数据，这个过程就叫做回表查询

using where; using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

若为null则需要回表查询

前缀索引

当字段类型为字符串(varchar ， text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

1	create index 索引名 on 表名(字段(n))

创建一个索引在某个表的字段上，截取该字段字符串的前n为建立索引

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct email)/count(*) from tb_user;
#将表中所有email字段去重后比上权限的数量，可以得到email的不重复率
select count(distinct  substring(email,1,10))/count(*) from tb_user;
#上述语句，我们就可以设计出较为高效的前缀索引，利用substring函数对email字段进行截取，多次测试取出较为高的值，然后将截取到的长度作为前缀长度 
create index email_index on tb_user(email(10));

前缀索引的执行流程：

首先我们对email字段做一个前缀辅助索引，前缀长度为5，当我们在利用索引进行查找时，首先回利用这个新创建的前缀辅助索引进行搜索，由于叶子结点为链表所以搜索较为方便，查询到符合数据后直接返回id，利用id做聚集索引，查询到后将符合前缀查询的数据取出来，对其中的email和我们条件中的email进行比对，如果一致则返回结果集

索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件（where)、排序（order by)、分组 ( group by)操作的字段建立索引。尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

insert优化

批量插入:

1	insert into 表 values(1,'1'),(2,'2'),(3,'3');

倘若我们要插入上十万条百万条甚至是千万条（2000W以下）的数据，则更推荐使用批量插入的方式，将书插入数据分成多份，然后多次插入

手动提交事务

start transaction;
#以下省略号是insert语句
……
……
commit;

逐渐顺序插入

1 2	主键乱序插入：8,12,1,11,13,14,5,7,6 逐渐顺序插入：1,2,3,4,5,6,7,8,9,10,11

主键顺序插入效率高于主键乱序插入

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下:

#客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -uroot -p
#设置参数local_infile为1，开启本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令将准备好的数据加载到表结构中
load data local '索要导入的文件地址' into table 表名 fields terminated by ',' lines terminated by '/n';
#ps:fields terminated by 后面跟的是字段分隔符, lines terminated by 后面跟的是行分隔符

主键优化

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table lOT)。

至此再次复习MySQL的逻辑存储结构

Tablespece:表空间
segment:段
Extent:区
Page:页
Row:行

每页中可存放多条行数据

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。

如果是主键顺序插入，当一页数据满时，会直接使用另一页然后继续顺序插入行数据

但如果是乱序插入

这时会怎么办？

页分裂，会在页中二分之一处，进行分裂将分裂下来的数据进行和插入数据的组装，一起添加到新页中，然后对页进行排序

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(（flaged)为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

倘若我删除第二页的id为13 14 15 16数据后就会进行页合并，MERGE_THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。

order by 优化

Using flesot :通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

这时，我们使用建立索引联合索引后进行排序

#创建user表对age和phone的索引
create index idx_user_age_phone on tb_user(age,phone);
#查看排序方式详情
explain  select age, phone  from tb_user order by age asc, phone asc  ;
explain  select age, phone  from tb_user order by phone asc, age asc  ;

查询结果下图，说明该方式查询效率高

1 2	explain select age, phone from tb_user order by age asc, phone desc ; explain select age, phone from tb_user order by phone desc, age asc ;

查询结果如下图，说明该查询效率低

1	explain select age, phone from tb_user order by age desc, phone desc ;

查询结果如下图，两个字段都降序查询，使用索引后反向遍历，说明该方式效率也不是很高

但是，如果我就想对 age进行升序然后对desc进行降序查询呢？

我们可以在创建索引的时候开始定义排序方式

1
2
3

#创建索引 ，定义其字段排序
create index idx_user_ad_age_index on tb_user(age asc ,phone desc );
explain  select age, phone  from tb_user order by age asc,phone desc   ;

但是无法交换二者的顺序,发现仍然会出现Using filesort

1	explain select age, phone from tb_user order by phone desc ,age asc ;

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

展示排序缓存大小

1	show variables like 'sort_buffer_size';

group by 优化

Using temporary表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等原因，因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小

当我们对没有定义联合索引的两个字段profession,age进行分组查询时

1	explain select profession,age,count(*) from tb_user group by profession,age;

当我们建立索引后

所以如果在进行分组优化时，最好将所分的字段建立索引，并且符合索引的使用规则

limit优化

当我们在进行分页查询时，经常能碰到这种情况，在小数据量的查询时，我们插叙的速度会非常快，但是数据量大起来后，查询速度会一落千丈

查询页数较少时

查询页数较多时

速度相差几十倍，这时我们要对其进行优化，我们知道，当在进行分页查询时，如果我们的分页查询语句时这样的

1	limit 900000,10

此时MySQL要排序前90000010条数据进行排序，然后再返回9000000-9000010条数据其余的记录丢弃，这样的查询代价是十分巨大的

所以我们可以使用连表查询，其中连接子表使用聚集索引的分页查询

1	select t1.* from tb_sku t1 ,(select id from tb_sku order by id limit 900000,10) t2 where t1.id=t2.id;

两表的连接操作要比对单表的查询操作快的多

这是优化前

优化后

limit在进行大量数据查询时，我们要根据所查询的内容去进行聚集索引覆盖和连表查询以提高查询效率

Count优化

MylSAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，但仅限于不加where语句的情况下
InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count 函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。
用法: count (* ) 、count（主键)、count(字段)、count ( 1)

count的几种用法

count(主键)
- InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为
count(字段)
- 没有not null约束: nnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count ( 1)
- InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(*)
- InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

update优化

MySQL 中对表中的数据进行操作语句一般都与事务相关，通常与锁也有着莫大的联系，在innodb存储引擎下，MYSQL不仅支持表锁也支持行锁，当所修改的字段并未加索引时，那么行锁就会自动升级为表锁，因为锁上的锁并不是针对记录的，而是针对索引的，所以在进行操作时尽量对所进行操作的字段加索引

视图、存储过程、触发器

视图

介绍：视图（View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句。

视图语句

创建

1	create [or replace] view 视图名 as select语句

查询

查询创建视图语句

1	show create view 视图名

查询视图内容

1	select * from 视图名

修改视图

方法一：

1	create [or replace] create [or replace] view 视图名 as select语句

方法二：

1	alter view 视图名称[(列表名)] as select语句

删除视图

1	drop view [if exists] 视图名称,[视图名称]……

插入视图数据

1	insert into 视图名(字段1，字段2) value(值1，值2);

但插入数据时，必须要符合我们建立视图的条件，否则不可插入

视图检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项;CASCADED和LOCAL，默认值为CASCADED。

cascaded

当创建视图时，要符合前一个视图的创建条件才能创建该视图，当在创建视图时加上视图检查选项 with cascaded check option,在视图插入信息时，必须要符合当前视图和之创建视图的条件；倘若不加视图选项，但在当前视图的前一个视图在创建的时候加上了视图选项，则在当前视图插入数据，可以不符合当前表的规则，但要符合之前视图的规则

Local

当选项为local时，则会递归调用之前视图的信息，在插入信息或修改信息时，倘若之前的视图在创建时没加 with cascaded check option则会不按照该视图的条件进行内容的修改

视图更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新;

聚合函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等)
DISTINCT
GROUP BY
HAVING
UNION或者UNION ALL

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据

库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点

封装，复用
可以接收参数，也可以返回数据
减少网络交互，效率提升

基本语法

#创建存储过程语句
create procedure 存储过程名称 ([参数列表])
begin
	--SQL语句
end;
#调用
call 名称([参数]);


#查询指定数据库的存储过程以及状态信息	
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='数据库名称';
#查询某个存储过程的定义
show create procedure 存储过程名称;

#删除存储过程

drop procedure 存储过程名称;

#SQL结束符
delimiter 结束符；

变量

系统变量

系统变量时MySQL服务器提供的，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)

查看系统变量

1
2
3

SHOW [SESSION|GLOBAL] variables; #查看所有系统变量
SHOW [SESSION|GLOBAL] variables like '……' #通过like模糊匹配的方式查找变量
SHOW @@[SESSION|GLOBAL] 系统变量名;  #查看指定变量的值

设置系统变量

1 2	SET [SESSION\|GLOBAL] 系统变量名=值; SET @@[SESSION\|GLOBAL] 系统变量名 =值;

设置系统变量名后重启MySQL一开始设置的变量名都会恢复默认值

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf 中配置。

用户自定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

用户自定义变量赋值

set @变量名 = 变量值
set @变量名 := 变量值
select 	@变量名:=变量值
select 字段名 into 变量名 from 表名

使用用户变量

1	select @变量名

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN…END块。

1	declare 变量名变量类型 [default xxx]

变量类型就是数据库字段类型: INT 、 BIGINT、 CHAR 、 VARCHAR 、 DATE 、 TIME等

赋值

1
2
3

set 变量名 =值；
set 变量名 :=值;
select 字段名 into 变量名 from 表名;

判断语句

if  条件 then
 	else if 条件2 then 
 	
 	else ...
 	end if;

参数

create procedure  zf(in score int,out result varchar(10))
begin

  if
      score>=85 then
      set result:='优秀哦';

  elseif 85>score>=60
                then set result:='及格咯';
    else
        set result:='没及格啊';

    end if;

  end;

1
2
3

call zf(99,@result);

select @result;

create procedure tansforms(inout score int)
begin
     set score:=score*0.5;
end;

怎么使用参数为inout的方法？

1
2
3

set @score=190;
call tansforms(@score);
select @score;

case语句

语法

case 
 	when 条件1   then  SQL语句1
 	when 条件2   then  SQL 语句2
 	else 语句3

例如按输入月份决定第几季度

create procedure  transform(in mouth int)
begin
    declare result varchar(15);

    case
        when mouth>=1 and mouth<=3
            then set result:='第一季度';
        when mouth>=4 and mouth<=6
            then set result:='第二季度';
        when mouth>=7 and mouth<=9
            then set result:='第三季度';
        when mouth>=10 and mouth<=12
            then set result:='第四季度';
        else
             set result:='输入的月份有问题';
        end case ;
    select result;
end;


call transform(1);
call transform(4);
call transform(8);
call transform(11);
call transform(18);

while循环

while 循环条件
do 
执行SQL语句
end;

例如:查询1到指定数的累加

create procedure a(in s int)
begin

    declare  total int  default  0;

    while s>0 do
        set total:=total+s;
        set s:=s-1;
        end while;
    select total;
end;

repeat循环

repeat是有条件的循环控制语句,当满足条件的时候退出循环。先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环

具体语法为：

repeat
	执行的SQL语句
util
	终止条件
	end repeat;

和上述案例一样

create procedure b(in s int)
begin
    declare sum int default  0;
    repeat
        set sum:=sum+s;
        set s:=s-1;
    until
        s=0
        end repeat;
    select sum;
end;

loop循环

LOOP实现简单的循环，如果不在sqL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LoOP可以配合一下两个语句使用:

LEAVE:配合循环使用,退出循环。
ITERATE:必须用在循环中的作用是跳过当前循环剩下的语句，直接进入下一次循环。

语句:

循环名:loop
	
	SQL语句
		
	end loop 循环名;

案例，查询1 到指定数的奇数和

create procedure c(in s int)
begin
    declare total int default 0;
    sum:loop
        if s<=0
            then leave sum;
            end if;

        if s%2=0
            then
                set  s:=s-1;
                iterate sum;
        end if;

        set total:=total+s;

        set s:=s-1;
    end loop sum;

        select total;
end;

游标

游标〈CURSOR）〉是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

#声明游标
declare 游标名称 cursor for 查询语句
#打开游标
open 游标名称
#获取游标记录
fetch 游标名称 into 变量

条件处理程序

条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:

declare handler_action handler for condition_value…… SQL 语句handler_action

CONTINUE:
继续执行当前程序EXIT:终止执行当前程序
condition_value
SQLSTATE sqlstate_value:状态码，如02000
SQLWARNING:所有以01开头的SQLSTATE代码的简写
NOT FOUND:所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

游标与条件处理程序的结合使用

创建一个存储过程要求将大于所输入年龄的员工信息导入到一个新表（tb_emp_pro）中

create procedure d(in uage int)
begin
	#创建两个变量用于接收游标中的值
    declare uname varchar(50);
    declare ujob varchar(20);
    #创建游标
    declare c_cursor cursor for  select name,job from emp where age>uage;
    #创建条件处理
    declare exit handler for sqlstate '02000' close c_cursor;


    drop table if exists tb_emp_pro;
    create table database_user.tb_emp_pro(
        id int primary key auto_increment,
        ujob varchar(20),
        uname varchar(50)
    );
	#打开游标
    open c_cursor;

    while true do
    	#获取游标中的值
        fetch c_cursor into uname,ujob;
        insert into tb_emp_pro values (null,ujob,uname);
        end while;
end;

存储函数

create function 存储函数名称 ([参数列表])
returns type [characteristic ....]
begin
	SQL 语句;
	return ……;
end;

characteristic说明：
 DETERMINISTIC : 相同的输入参数总是产生相同的结果
 NO SQL: 不包含SQL语句
 READS SQL DATA :包含读取数据的雨具但不包含写入数据的语句

实例：查询1到指定数的整数和

#创建函数
create function e( n int)
#创建返回值类型
returns int deterministic
begin
    declare sum int default 0;
    s:loop
        if n<=0
        then return sum;
        end if;
         set sum:=sum+n;
        set n:=n-1;
end loop s;
    end;

在使用时可以直接select 函数名（参数）来调用

1	select e(100);

锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MlySQL中的锁，按照锁的粒度分，分为以下三类:

全局锁:锁定数据库中的所有表。
表级锁:每次操作锁住整张表。
行级锁：每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都
将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性.

没有锁的情况下：

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

首先要对数据库加上全局锁，只允许查询操作但不允许增添修改删除等操作，在导出后释放锁

添加全局锁

1	flush tables with read lock;

进行数据备份

1	mysqldump -u用户名 -p密码所指定的数据库名>生成的sql文件

解锁

1	unlock tables;

特点

数据库中加全局锁，是一个比较重的操作，存在以下尚题:

1.如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

2．如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（(binlog)，会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份。

1	mysqldump --single-transaction -u用户名 -p密码所指定的数据库名>生成的sql文件

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表锁，分为两类:
1.表共享读锁( read lock )
2.表独占写锁( write lock )

语法:
1.加锁:lock tables表名… read/write。
2．释放锁:unlock tables /客户端断开连接。

读锁

写锁

元数据锁（MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

查看元数据锁

1	select OBJECT_TYPE,OBJECT_NAME,OBJECT_SCHEMA,LOCK_TYPE,LOCK_DURATION from performance_schema.metadata_locks;

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

1．意向共享锁（IS)∶与表锁共享锁(read）兼容，与表锁排它锁( write）互斥。
2．意向排他锁（IX)∶与表锁共享锁（read)及排它锁（write)都互斥。意向锁之间不会互斥。

查看意向锁

1	select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;

当事务开启时，我们倘若要开启意向共享锁那么就需要使用select语句在语句的末尾加上 lock in share mode

例如：

1
2
3

begin ;
select * from emp where id =1 lock in share mode ;
select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;

但如果要开启意向排他锁的话，只需要在事务处理中使用DML语句即可自动开启意向排他锁

1
2
3

begin ;
update emp set  salary=20000 where id=1;
select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

1.行锁（Record Lock)︰锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

2.间隙锁(Gap Lock)∶锁定索引记录间隙（不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert,产生幻读。在R隔离级别下都支持。

3.临键锁(Next-Key Lock)∶行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

行锁

lnnoDB实现了以下两种类型的行锁:
1．共享锁(S)∶允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

2．排他锁(X)∶允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

间隙锁

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

1．索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。

当事务开启，我们使用update语句条件为id=5但id为5的行并不在，所以锁就变为了间隙锁，就会在距离该id最近的两个id中间设置间隙锁

2．索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。

当事务开启时，我们使用查询语句时，若遍历后没有发现满足请求的值，临键锁就会退化为间隙锁，去除了行锁

3．索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

InnoDB引擎

逻辑存储结构

表空间（ibd文件）：一个mysql实例可以对应多个表空间，用于存储记录、索引等数据

段，分为数据段（Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment)， InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent(区)。

区，表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

页，是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。

行，InnoDB存储引擎数据是按行进行存放的。

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

架构

MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构

内存结构

Buffer Pool:缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。

根据状态，将Page分为三种类型:

free page:空闲page，未被使用。
clean page:被使用page，数据没有被修改过。
dirty page:脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

Change Buffer:更改缓冲区（针对于非唯一二级索引页)，在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区Change Buffer 中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义是什么?
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

Adaptive Hash lndex:自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成。

参数: adaptive_hash_index

查看自适应哈希索引参数

1	show variables like '%adaptive_hash_index%';

Log Buffer:日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log . undo log)，默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。
参数:

innodb_log_buffer_size:缓冲区大小
innodb_flush_log_at_trx_commit:日志刷新到磁盘时机

show variables  like '%innodb_log_buffer_size%';
#展示innodb日志缓冲区的大小
show variables like '%innodb_flush_log_at_trx_commit%';
#日志刷新到磁盘的时机
#1:日志在每次事务提交时写入并刷新到磁盘
#0:每秒将日志写入并刷新到磁盘一次。
#2:日志在每次事务提交后写入，并每秒刷新到磁盘一次。

磁盘结构

System Tablespace:系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)

参数:innodb_data_file_path

value值为：系统表空间文件：大小：自动扩张

File-Per-Table Tablespaces:每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。
参数:innodb_file_per_table

GeneralTablespaces:通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时，可以指定该表空间。

创建表空间

1	create tablespace 表空间名 add datafile 表空间文件名 engine = innodb;

我们可以在建表的时候指定表空间文件

create table 表名 (
    id int primary key ,
    name varchar(20) not null
)tablespace 表空间名;

Undo Tablespaces:撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间〈初始大小16M)，用于存储undo log日志。

Temporary Tablespaces: InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据

Doublewrite Buffer Files:双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中,便于系统异常时恢复数据。

Redo Log:重做日志，是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件（redo log),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复使用。

以循环方式写入重做日志，涉及到两个文件

后台线程

Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收

IO Thread

在InnoDB存储引擎中大量使用了AIO（异步非阻塞）来处理lO请求,这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回调。

查看innodb引擎的状态

1	show engine innodb status ;

然后找到File I/O

--------
FILE I/O
--------
#一个 insert buffer thread
I/O thread 0 state: wait Windows aio (insert buffer thread)
#一个 Log thread 
I/O thread 1 state: wait Windows aio (log thread)
#四个 Read thread
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
#四个 Write thread
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
#四个读线程在等待，四个写线程也在等待
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0]

Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

Page Cleaner Thread

协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞

事务的原理

事务的实现实际上就是事务对于其四大特性的保证

对于ACID四大特性而言， innodb存储引擎对于事务的原子性、一致性、持久性的保证为redo_log与undo_log

对于隔离性的保证依靠的是锁与MVCC机制

redo log

redo log保证了事务的持久性，它是如何做的的呢？

概述

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成:重做日志缓冲（redo log buffer)以及重做日志文件（redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。

undo log

用于解决事务的原子性，它是如何做到的呢

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和MVCC(多版本并发控制)。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁: undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
Undo log存储: undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo logsegment。

MVCC

多版本并发控制

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:
select … lock in share mode(共享锁)，select…for update、update、insert、delete(排他锁)都是一种当前读。

快照读

简单的select (不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
心
. Read Committed:每次select，都生成一个快照读。
. Repeatable Read:开启事务后第一个select语句才是快照读的地方。
. Serializable:快照读会退化为当前读。