一、存储引擎
1、Mysql体系结构
- 连接层
最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。 - 服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。 - 引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。 - 存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互
2、存储引擎简介
（1）介绍
- 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
- 存储引擎是基于表的，而不是基于库的，所以也被叫做表类型。
（2）语法
- 创建表时，指定存储引擎
CREATE TABLE 表名(
字段 类型
) ENGINE = INNODB;
- 查看当前支持的存储引擎
SHOW ENGINES;
3、存储引擎特点
（1）InnoDB
- 介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在Mysql5.5之后，InnoDB是默认的MySQL存储引擎 - 特点
DML操作遵循ACID模型，支持事务；
支持外键约束，保证数据的完整性和正确性；
行级锁，提高并发访问性能； - 文件
xxx.ibd： xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构、数据和索引。
参数：innodb_file_per_table
（2）MyISAM
- 介绍
MyISAM是MySQL早期的默认存储引擎。 - 特点
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快 - 文件
xxx.sdi ： 存储表结构信息
xxx.MYD ： 存储数据
xxx.MYI ： 存储索引
（3）Memory
- 介绍
Memory引擎的表数据存储在内存中，由于受到硬件问题、或断电问题影响，只能将这些表作为临时表或缓存使用。 - 特点
内存存放
hash索引 - 文件
xxx.sdi ： 存储表结构信息
（4）比较
4、存储引擎选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
- innoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么lnnoDB存储引擎是比较合适的选择。
- MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那
么选择这个存储引擎是非常合适的。 - MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
5、总结
二、索引
1、索引概述
（1）介绍
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上试下高级查找算法，这种数据结构就是索引。
（2）演示
（3）优缺点
| 优点 | 缺点 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引降低了更新表的速度，效率降低 |
| 通过索引对列数据进行排序，降低数据排序的成本，降低CPU的消耗 | 索引列也要占用空间 |
2、索引结构
Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构：
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-tree(空间索引) | MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引，快速匹配文档的方式。 |
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-Tree索引 | 不支持 | 支持 | 不支持 |
| Full-text索引 | 5.6之后支持 | 支持 | 不支持 |
我们平时说的索引，如果没有特别指明，都是B+树结构组织的索引。
（1）二叉树
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
红黑树：大数据量情况下，层级较深，检索速度慢。
（2）B-Tree（多路平衡查找树）
（3）B+Tree
（4）hash
hash索引特点
- Hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<）
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以，效率通常高于B+tree索引
存储引擎支持
在MySQL中，支持hash索引的是Memory索引，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
（5）为什么InnoDB存储引擎选择使用B+Tree索引结构？
- 相对于二叉树，层级更少，搜索效率高；
- 相对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大类数据，只能增加树的高度，导致性能降低；
- 相对hash索引，B+tree支持范围匹配及排序操作。
3、索引分类
（1）分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
（2）在InnoDB存储引擎中，根据索引的存储形式，又可以分为两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引 | 将数据存储和索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存多个 |
聚集索引的选区规则
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，InnoDB则会自动生成一个rowid作为隐藏的聚集索引。
4、索引语法
5、SQL性能分析
（1）SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______'; // Com_______ 7个下划线 或Com%
（2）慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位: 秒，默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件 (/etc/my.cnf)中配置如下信息:
#开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log
（3）profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持
profile操作：
SELECT @@have_profiling;
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:
SET profiling = 1
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:
#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
6、索引使用
(1) 最左前缀法则
联合索引，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。
（2）范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
explain select * from tb user where profession='软件工程’ and age>30 and status = '0';
explain select * from tb user where profession ='软件工程’ and age>=30 and status = '0';
备注：当大于等于时，走索引，status不会失效 ， 为什么？？？？？？
（3）索引失效
- 索引列运算
不要再索引列上进行运算操作，索引将失效 - 字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。 - 模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。 - or连接的条件
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 - 数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
is not null判断索引失效，根据表中字段是否为null,当所有数据不为空，is not null索引失效，当所有数据都是空，is not null走索引
（4）覆盖索引
尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *
（5）前缀索引
当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
create index idx_xxxx on table_name(column(n));
（6）单列索引与联合索引
单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
7、索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引。
- 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个
索引最有效地用于查询。
三、SQL优化
1、插入数据
（1）批量插入
insert into tb_test values(1, 'Tom'),(2, 'Cat'),(3,'Allen');
（2）手动提交事务
start transaction;
insert into tb_test values(1, 'Tom'),(2, 'Cat'),(3,'Allen');
insert into tb_test values(4, 'Tom2'),(5, 'Cat2'),(6,'Allen2');
insert into tb_test values(7, 'Tom3'),(8, 'Cat3'),(9,'Allen3');
commit;
（3）主键顺序插入
（4）大批量插入数据
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时使用load指令
#客户端连接服务端时，加上参 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n'
2、主键优化
（1）数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为素引组织表(index organized table lOT)。
（2）页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
取第一页50%位置后数据，和50一起放到第三页，然后修改指针，第一页指向第三页
（3）页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
（4）主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
- 业务操作时，避免对主键的修改
3、order by 优化
（1）判断方式
- Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
- Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
（2）设计原则
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
- 如果不可避免的出现filesot，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256K)
4、group by 优化
- 在分组操作时，可以通过索引来提高效率
- 分组操作时，索引的使用也是满足最左前缀法则
5、limit优化
一个常见又非常头疼的问题就是limit2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010
的记录，其他记录丢弃，查询排序的代价非常大。
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id:
6、count优化
7、update优化
四、视图/存储过程/触发器
略
五、锁
1、概述
（1）介绍
锁是计算机协调多个进程或线程并发访问某一资源的机制。
（2）分类
按照锁的粒度分：
- 全局锁：锁定数据库中的所有表。
- 表级锁：每次操作锁住整张表。
- 行级锁：每次操作锁住对应的行数据。
2、全局锁
（1）介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的写语句和提交语句都将被阻塞。
典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，保证数据完整性。
## 加锁
flush tables with read lock;
## 备份
mysqldump -uroot -p1234 itcast > itcast.sql;
## 解锁
unlock tables;
（2）特点
加全局锁是一个比较重的操作，存在以下问题：
- 如果主库备份，在备份期间都不能执行更新，业务基本停摆。
- 如果从库备份，在备份期间从库不能执行主库同步的二进制日志，会导致主从延迟。
在Innodb引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -uroot -p1234 itcast > itcast.sql;
3、表级锁
（1）介绍
每次操作锁住整张表。锁定粒度大，发生冲突的概率最高，并发度最低。
分为三类：
- 表锁
- 元数据所（MDL）
- 意向锁
（2）表锁
表锁分为两类：
- 表共享读锁（read lock）：不会阻塞其他客户端的读，但是会阻塞写，本身写操作报错。
- 表独占写锁（write lock）：本身可读可写，其他客户端的读写都阻塞。
## 加锁
lock tables 表名... read/write
## 释放锁
unlock tables
（3）元数据锁（MDL）
MDL加锁过程是系统自动控制，无需显示使用，在访问表时会自动加上。
当对表进行增删改查（DML）时，加MDL读锁；当对表结构进行变更操作（DDL）时，加MDL写锁。MDL主要作用是为了避免DML和DDL冲突，保证读写的正确性。（当进行增删改查时，不能修改表结构）。
| 对应SQL | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read/wirte | SHARED_READ_ONLY、SHARED_NO_READ_WRITE | |
| select、select…lock in share mode | SHARED_READ | 与SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| insert、update、delete、select…for update | SHARED_WRITE | 与SHARED_READ兼容，与EXCLUSIVE互斥 |
| alter tables … | EXCLUSIVE | 与其他的DML都互斥 |
查看元数据锁：
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
（4）意向锁
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB引入了意向锁，使得表锁不用检查每行数据是否加锁。
| 锁类型 | 语句 | 互斥情况 |
|---|---|---|
| 意向共享锁（IS） | 由select … lock in share mode添加 | 与表锁共享锁read兼容，与表锁排它锁write互斥 |
| 意向排他锁（IX） | 由insert 、update、delete、select… for update添加 | 与共享锁和排它锁都互斥 |
查看意向锁：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
4、行级锁
（1）介绍
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
lnnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:
- 行锁(Record Lock)：
锁定单个行记录的锁，防止其他事务对此行进行update和delete在RC、RR隔离级别下都支持 - 间隙锁(Gap Lock)：
锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
- 临键锁 (Next-Key Lock):
行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
（2）行锁
InnoDB实现了以下两种类型的行锁:
- 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
- 排他锁 (X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
| 排共享锁(S) | 排他锁 (X) | |
|---|---|---|
| 共享锁(S) | 兼容 | 冲突 |
| 排他锁 (X) | 冲突 | 冲突 |
SQL语句中加锁情况
| SQL | 行锁类型 | 说明 |
|---|---|---|
| INSERT… | 排他锁 | 自动加锁 |
| UPDATE… | 排他锁 | 自动加锁 |
| DELETE… | 排他锁 | 自动加锁 |
| SELECT(正常) | 不加任何锁 | |
| SELECT … LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE |
| SELECT … FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |
行锁-演示
- 索引上的等值查询(唯一索引)，对已存在的记录进行等值匹配时，将会自动优化为行锁。
- lnnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么lnnoDB将对表中的所有记录加锁，此时 就会升级为表锁。
可以通过以下SQL，查看意向锁及行锁的加锁情况
select object schema,object name,index name,lock type,lock mode, lock data from performance schema.data locks;
（3）间隙锁/临键锁
默认情况下，lnnoDB在 REPEATABLE READ事务隔离级别运行，nnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
- 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁。
- 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。
- 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
六、InnoDB引擎
1、逻辑存储结构
- 表空间（ibd文件）：一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
- 段：分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）,InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。
- 区：表空间的单元结构，每个区的大小为1M。页大小默认为16k，即一个区中有64个连续的页。
- 页：InnoDB存储引擎磁盘管理的最小单元，默认大小16k。为了保证页的连续性，InnoDB每次从磁盘中申请4~5个区。
- 行：数据是按行进行存放的。
2、架构
Mysql5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性。
InnoDB架构图：左侧是内存结构，右侧是磁盘结构。
（1）内存结构 (Memory Structures)
① Buffer Pool 缓冲池
缓冲池是内存中的一个区域，可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，page分为三种类型：
- free page：空闲page，未被使用。
- clean page：被使用page，数据没有被修改过。
- dirty page：脏页，被使用page，数据被修改过，与磁盘的数据不一致。
② 更改缓冲区（Change Buffer）
更改缓冲区，主要针对非唯一二级索引，在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在Chagne Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer pool中，再将合并后的数据刷新到磁盘中。
Chagne Buffer意义是什么？
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了Change Buffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO.
③自适应hash索引（Adaptive Hash Index）
用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成，参数：adaptive_hash_index。
④ 日志缓冲区 （Log Buffer）
用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为16M，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O
参数：
-
innodb_log_buffer_size：缓冲区大小
-
innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，值为0,1,2
1：日志在每次事务提交时写入，并刷新到磁盘；
0：每秒将日志写入，并刷新到磁盘一次。
2：日志在每次事务提交后写入，并每秒刷新到磁盘一次。
（2）磁盘结构 (Disk Structures)
① 系统表空间 （System Tablespace ）
系统表空间是更改缓冲区（Change Buffer）的存储区域。如果表是在系统表空间而不是独立表空间或通用表空间中创建的，它也可能包含表和索引数据。
参数：innodb_data_file_path
② 独立表空间（File-Per-Table Tablespaces）
包含单个innoDB表的数据和索引，并存储在文件系统上的单个数据文件中。
参数：innodb_file_per_table
③ 通用表空间（General Tablespaces）
需要通过CREATE TABLESPACE语法创建通用表空间，创建表时，可指定该表空间。
# 创建表空间
create tablespace ts_person add datafile 'myperson.ibd' engine = innodb;
# 建表
crate table person(id int, name varchar(20)) tablespace ts_person;
④ 撤销表空间 （Undo Tablespaces）
Mysql实例在初始化时会自动创建两个默认的undo表空间（初始大小16M）,用于存储undo.log日志。
⑤ 临时表空间 （Temporary Tablespaces）
InnoDB使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据。
⑥ 双写缓冲区 （Doublewrite Buffer Files）
innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入到缓冲区文件中，便于系统异常时恢复数据。*.dblwr
⑦ 重做日志区 （Redo Log）
用来实现事务的持久性。
（3）后台线程
后台线程作用：InnoDB存储引擎缓冲池当中的数据，在合适的时机，刷新到磁盘文件中。
① 核心后台线程 （Master Thread）
负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。
② IO线程（IO Thread）
大量使用AIO（异步IO）来处理IO请求，这样可以极大的提高数据库的性能，而IO Thread主要负责这些IO请求的回调。
| 线程类型 | 默认个数 | 职责 |
|---|---|---|
| Read Thread | 4 | 负责读操作 |
| Write Thread | 4 | 负责写操作 |
| Log Thread | 1 | 负责将日志缓冲区刷新到磁盘 |
| Insert Buffer Thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
③ Purge Thread
主要用于回收事务已经提交了的undo.log，在事务提交之后，undo log可能不用了，就用它来回收。
④ Page Cleaner Thread
协助Master Thread 刷新脏页到磁盘的线程，他可以减轻Master Thread的工作压力，减少阻塞
3、事务原理
（1）redo log（持久性）
重做日志，记录的是事务提交时数据页的物理修改，用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中。用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。
（2）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采用段的方式进行管理和记录，存放在前面介绍的rollbck segment回滚段中，内部包含1024个undo log segment.
- 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
- 当update、delete的时候，产生的undo log日志不仅仅在回滚时需要，在快照读时也需要，不会立即被删除。
4、MVCC
（1）基本概念
- 当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select.lock in share mode（共享锁），select…for update、update、insert、delete（排他锁）都是一种当前读。 - 快照读
简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed：每次select，都生成一个快照读。
Repeatable Read：开启事务后第一个selecti语句才是快照读的地方。
Serializable：快照读会退化为当前读。 - MVCC
全称Multi–Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
（2）隐式字段
| 隐式字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指向主键，将会生成改隐藏字段 |
（3）undo log版本链
不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。
（4）readView 读视图
ReadView 是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
ReadView中包含了四个核心字段：
| 字段 | 含义 |
|---|---|
| m_ids | 当前活跃的事务ID集合 |
| min_trx_id | 最小活跃事务ID |
| max_trx_id | 预分配事务ID，当前最大事务ID+1 |
| creator_trx_id | ReadView创建者的事务ID |
（5）实现原理
不同的隔离级别，生成ReadView的时机不同：
-
READ COMMITED（RC）：在事务中每一次执行快照读时生成ReadView。
第一次读
第二次读
-
REPEATABLE READ（RR）：尽在事务中第一次执行快照读时生成ReadView，后续服用该ReadView.
5、总结
七、MySQL管理
1、系统数据库
| 数据库 | 含义 |
|---|---|
| mysql | 存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等） |
| information_schema | 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
| performance_schema | 为MySQL服务器运行状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图 |
1039
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
