MySQL进阶

MySQL进阶

select 
    * 
from 
    tb_name 
where 
    id = '1' 
group by 
    id 
having 
    id < 3 
order by 
    id asc
limit 
    2, 5;

MySQL体系结构

  • 连接层最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
  • 引擎层存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
  • 存储层主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎

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

InnoDB

  • 特点支持事务，行级锁，外键
  • 文件xxx.ibd:InnoDB的每张表都会对应一个这样的表空间文件，存储该表的表结构（frm、sdi）、数据和索引
逻辑存储结构

表段区页行

page 16k

extent 1M， 有64个page

MyISAM

MySQL早期默认的存储引擎

  • 特点不支持事务，不支持外键支持表锁，不支持行锁访问速度快
  • 文件xxx.sdi: 存储表结构信息 xxx.MYD:存储数据，（mydata）xxx.MYI:存储索引，（myindex）

Memory

表数据存放再内存中，受到硬件，断电问题等影响，只能将这些表作为临时表或缓存使用

  • 特点内存存放，访问速度快hash索引（默认）
  • 文件xxx.sdi: 存储表结构信息

存储引擎特点对比

存储引擎的选择

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

索引

索引是帮助MySQL高效获取数据的数据结构（有序）

索引结构

  • B+Tree索引最常见的索引类型，大部分引擎都支持
  • Hash索引底层数据结构是哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
  • R-Tree（空间索引）MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  • Full-text（全文索引）是一种通过建立倒排索引，快速匹配文档的方式。类似 Lucene，Solr，ES

BTree（多路平衡查找树）

可视化：B-Tree Visualization

B+Tree

可视化：B+ Tree Visualization

与BTree的区别

  • 所有的数据都会出现在叶子节点
  • 叶子节点形成一个单向链表
MySQL索引结构对B+Tree的优化

Hash
  • 特点
    • Hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<，...）
    • 无法利用索引完成排序操作
    • 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
  • 存储引擎支持在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
为什么InnoDB存储引擎选择使用B+Tree索引结构？
  • 相对于二叉树，层级更少，搜索效率高
  • 对于BTree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加数的高度，导致性能降低。
  • 相对于Hash索引，B+Tree支持范围匹配和排序操作。

索引分类

  • 主键索引针对于表中主键创建的索引
    • 特点：默认自动创建，只能有一个
    • 关键字：primary
  • 唯一索引避免同一个表中某数据列中的值重复
    • 特点：可以有多个唯一索引
    • 关键字：unique
  • 常规索引快速定位特定数据
    • 特点：可以有多个常规索引
    • 关键字：无
  • 全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值
    • 特点：可以有多个全文索引
    • 关键词：fulltext
存储形式分类

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

  • 聚集索引（Clustered Index）将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
    • 特点：必须有，且，只有一个
  • 二级索引（Secondary Index）（辅助索引，非聚集索引）将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
    • 特点：可以存在多个
聚集索引选取规则
  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
回表查询

比如有一张表是这样的，当执行

select * from user where name = 'Arm';

先走二级索引找到对应的主键值，再到聚集索引中拿到对应的行数据

InnoDB主键索引的B+Tree高度多高呢？

索引语法

创建索引
# 普通创建索引
create [unique | fulltext] index index_name on tb_name (index_col_name, ...);

# 前缀索引
create [unique | fulltext] index index_name on tb_name (index_col_name(n), ...);

# 指定字段的排序方式
create [unique | fulltext] index index_name on tb_name (index_col_name asc, ...);

指定字段的排序方式，会在[优化order by](####order by 优化)时用到

查看索引
show index from tb_name;
删除索引
drop index index_name on tb_name

SQL性能分析

SQL执行频率

7个_

show global status like 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，

// 查看慢查询日志是否开启
show variables like 'slow_query_log';

// 查询文件所在
show variables like 'slow_query_log_file';

需要在MSQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_log=2

配置完毕之后，通过指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

systemctl restart mysql

var/lib/mysql/localhost-slow.log

profile详情

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

select @@have_profiling;

select @@profiling;

set profiling=1;


# 查看每一条SQL的耗时基本情况
show profiles;

# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain执行计划

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

语法：

# 直接再select语句之前加上关键字 explain/desc
explain select * from tb_user;

字段含义
  • idselect查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
  • select_type表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
  • ⭐⭐⭐type表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。主键或唯一索引 -> const非唯一索引 -> ref
  • ⭐possible_key显示可能应用在这张表上的索引，一个或多个。
  • ⭐key实际使用的索引，如果为NULL，则没有使用索引。
  • ⭐key_len表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
  • ⭐rowsMySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
  • filtered表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
  • ⭐extra额外信息

索引使用

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。

最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

注意！！ 不是说写sql的顺序，是只要出现就可以，优化器会优化顺序

索引失效
范围查询

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

在业务运行的情况下，尽量使用 大于等于，小于等于，来规避索引失效。

索引列运算

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

explain select * from tb_user where substring(phone,10,2)='15';

字符串不加引号

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

模糊查询

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

or连接的条件

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

数据分布影响

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

SQL提示

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

  • use index(idx_name):
  • ignore index(idx_name):
  • force index(idx_name):

select * from tb_user use index(idx_user_phone) where phone = '13300000000';

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *，因为很容易出现回表查询。

在extra列中：

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

思考题

建立username和password的联合索引，走的就是二级索引，并且叶子节点下挂的是id主键，不需要回表查询。

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。

此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法
create index idx_xxx on tb_name(column(n));

# eg phone字段的前五位
create index idx_xxx on tb_name(phone(5));
前缀长度

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

#计算email字段的索引选择性
select count(distinct email)/count() from tb_user;

#计算email字段前5位的索引选择性
select count(distinct substring(email,1,5))/count(*) from tb_user

#创建索引
create index idx_email_5 on tb_name(email(5));
单列索引 与 联合索引

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

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

SQL优化

插入数据

insert优化

  • 批量插入 （500-1000条）
  • 手动提交事务执行insert前开启事务，多条数据insert之后，再提交事务
  • 主键顺序插入
  • 大批量插入数据如果一次性需要插入大批量数据，使用inserti语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

mysql --local-infile -u root -p

select @@local_infile;

set global local_infile = 1;

load data local infile '/temp/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键优化

  • 数据组织方式在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table， IOT）逻辑存储结构

  • 页分裂页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据多大，会行溢出），根据主键排列。主键顺序插入的情况，不会发生页分裂主键乱序插入的情况，会发生页分裂50来的时候，本来要插入到47后面，但是插不进去，此时就会开启一个新的数据页page#3，50不会写在这个新的数据页中，而是会找到47所在页（page#1）的50%的地方，移动23、47到page#3中，50移动到47后面。此时需要重新设置指针

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

MERGE_THRESHOLD：合并页的阈值，在创建表或者索引时指定

主键设计原则
  • 满足业务需求的情况下，尽量降低主键的长度。
  • 插入数据时，尽量选择顺序插入，选择使用AUTO INCREMENT自增主键。
  • 尽量不要使用UUD做主键或者是其他自然主键，如身份证号，无规则的。

order by 优化

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

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

group by 优化

优化思路
  • 在分组操作时，可以通过索引来提高效率。
  • 分组操作时，索引的使用也是满足最左前缀法则的。

limit 优化

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000－2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count 优化

count统计的是 not null 的数据

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*）的时候会直接返回这个数，效率很高，前提是不含where语句

InnoDB引擎就麻烦了，它执行count(*）的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路

自己计数

update 优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

索引失效的一种情况：比如name是索引， 一个会话里update 修改了某个id的name

update tb_user set name = ‘小明’ where name = ‘小强’；

那么另一个会话里，此时修改name字段就会阻塞，因为此时行锁在第一会话里已经升级了表锁，锁住了整张表。

锁

概述

MySQL中的锁，按照锁的粒度分，分为以下三类：

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

全局锁

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

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

加锁

flush tables with read lock;

备份，注意是命令行指令，不是mysql内的命令

mysqldump -uroot -p123456 > ./backup.sql

解锁

unlock tables；

特点

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

  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

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

mysqldump --single-transaction -uroot -p123456 itcast >itcast.sql

表级锁

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

主要分三类

  • 表锁
  • 元数据锁（meta datalock，MDL）
  • 意向锁
表锁
  • 表共享读锁（read lock）：加锁客户端能读，不能写。其他客户端可以读，写会被阻塞。
  • 表独占写锁（write lock）：只有加锁的客户端即可读又可写，其他客户端读写都被阻塞。

语法

加锁

lock tables 表名... read/write

释放锁

unlock tables / 客户端断开连接

元数据锁

meta data lock，MDL

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

意向锁

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

  • 意向共享锁（IS）：由语句select... lock in share mode添加。与表锁共享锁（read）兼容，与表锁排它锁（write）互斥
  • 意向排他锁（lX）：由insert、update、delete、select... for update添加。与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥

可以通过以下SQL，查看意向锁加锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

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

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

  • 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
  • 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
  • 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的那个间隙Gap。在RR隔离级别下支持。

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

行锁

行锁又分为：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
  • 排它锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

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

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

可以通过以下SQL，查看意向锁及行锁的加锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

间隙锁
id age
1 1
3 3
7 7
8 8

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

  • 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁。没有id为5的行

update tb set age = 10 where id=5；

此时会根据id来锁住3和7之间的gap，（3，7）

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

select * from tb where age=3 1ock in share mode;

此时3上了行锁，同时1和3之间的gap有临键锁，（即，3被上了临键锁，行锁和间隙锁的组合），3和7之间的gap有间隙锁（临键锁变成了间隙锁）

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

select * from tb where id>=7 1ock in share mode;

给id=7的行加了行锁，7后面，也就是下一条记录的8，加了临键锁，正无穷之前也加了一个临键锁，在这里就是正无穷到8也加了临键锁。

唯一索引新元素出现的位置固定在后面，而普通索引，新元素加入的地方可能在锁记录前的间隙，也可能在锁记录后的间隙，所以前后，加上自己都要上锁。自己上行锁，前上临键锁（行锁锁自己+间隙锁锁前面），后上间隙锁。

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

（69， 30）（41，50）能插入，用二级索引还要考虑主键，看上去都是二级索引的边界值，但是在索引中物理位置是不一样的。参考知乎

临键锁

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

InnoDB引擎

逻辑存储结构

表段区页行

  • 表空间（ibd文件）：一个mysql实例可以对应多个表空间，用于存储记录，索引等数据。
  • 段：分为数据段，索引段，回滚段。InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个区。
  • 区：表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
  • 页：是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16K。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。
  • 行：InnoDB存储引擎，数据是按行进行存放的。

架构

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

侧为磁盘结构。

内存结构
Buffer Pool

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

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

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

Change Buffer

（8.0之后，5.x只有Insert Buffer）

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

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

Adaptive Hash Index

自适应hash索引l，用于优化对BufferPool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

自适应哈希索引，无需人工干预，是系统根据情况自动完成。
参数：adaptive_hash_index

Log Buffer

日志缓冲区，用来保存要写入到磁盘中的log日志数据（redolog、undolog），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘IO。

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

  • 0：每秒将日志写入并刷新到磁盘一次
  • 1：日志在每次事务提交时写入并刷新到磁盘
  • 2：日志在每次事务提交后写入，并每秒刷新到磁盘一次

磁盘结构

后台线程

事务原理

特性
  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（lsolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
redolog （持久性）

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

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

undolog （原子性）

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC(多版本并发控制）。

undolog和redolog记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update录。当执行rollback时，就可以从undolog中的逻辑记录读取到相应的内容并进行回滚。

Undolog销毁：undolog在事务执行时产生，事务提交时，并不会立即删除undolog，因为这些日志可能还用于MVCC。

Undolog存储：undolog采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undolog segment。

MVCC

多版本并发控制

  • 当前读读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。
  • 快照读
    简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
    • Read Committed：每次select，都生成一个快照读。
    • Repeatable Read：开启事务后第一个select语句才是快照读的地方。
    • Serializable：快照读会退化为当前读。

MVCC
全称Multi-VersionConcurrencyControl，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undolog日志、readView。

隐式字段

DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。

DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undolog，指向上一个版本。

DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undolog日志及版本链

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。当insert的时候，产生的undolog日志只在回滚时需要，在事务提交后，可被立即删除。而update、delete的时候，产生的undolog日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

  • undolog 版本链

    不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview

ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

ReadView中包含了四个核心字段：这里的第三点应该是大于等于，第四点是小于max_trx_id

RC级别

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

RR级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

posted @ 2024-12-15 16:11  chendsome  阅读(5)  评论(0)  编辑  收藏  举报