mysql 8.0高级特性，优化，索引，锁

mysql 8.0高级特性，优化，索引

文章目录

- - 🔥3.Mysql高级篇
  - - 3.1 MySQL架构篇(P96~P114)
    - - 3.1.1 基本概念
      - 3.1.1.1 查看字符集
        
        3.1.1.2 字符集与比较规则
      - 3.1.2 SQL规范与SQL_Mode
      - 3.1.2.1 基本概念
        
        3.1.2.1 SQL_Mode
      - 3.1.3 Mysql数据目录
      - 3.1.3.1 基本概念
      - 3.1.4 Mysql--用户管理（P105~P108略）
      - 3.1.5 Mysql--逻辑架构
      - 3.1.5.1 基本架构
        
        3.1.5.2 架构原理--内部执行流程
      - 3.1.6 Mysql存储引擎
      - 3.1.6.1基本概念
        
        3.1.6.2 MyIASM与Innodb比较
    - 3.2 Mysql索引及调优篇(p115-p160 *)
    - - 3.2.1 索引数据结构
      - 3.2.1.1 基本概念
        
        3.2.1.2 设计索引
      - 3.2.2 索引概念
      - 3.2.2.1 聚簇索引
        
        3.2.2.2 二级索引（辅助索引、非聚簇索引）
        
        3.2.3.3 联合索引
        
        3.3.3.4 Innodb注意事项
        
        3.3.3.5 索引代价
      - 3.2.3 Mysql底层存储相关数据结构
      - 3.2.3.1 基本概念
        
        3.2.3.2 Hash
        
        3.2.3.2 二叉搜索树、AVL树
        
        3.2.3.3 B树、B+树
        
        3.2.3.4 R树
      - 3.2.4 Innodb数据存储结构
      - 3.2.4.1 基本概念
        
        3.2.4.2 页内部结构
        
        3.2.4.3 Compact行格式
        
        3.2.4.4 Dynamic、Compressed行格式
        
        3.2.4.5 页、区、段、表
      - 3.2.5 索引创建与设计原则
      - 3.2.5.1 基本概念
        
        3.2.5.2 索引创建准则
      - 3.2.6 性能分析工具
      - 3.2.6.1 基本优化步骤
        
        3.2.6.2 慢查询日志
      - 3.2.7 Explain执行计划
      - 3.2.7.1 基本概念
        
        3.2.7.2 Explain-进阶、trance、sys schema（略）
      - 3.2.8 索引查询优化
      - 3.2.8.1 基本概念
        
        3.2.8.2 索引失效情况
        
        3.2.8.3 索引建议
        
        3.2.8.4 关联查询优化
        
        3.2.8.5 子查询优化
        
        3.2.8.6 排序索引
        
        3.2.8.7 Group by优化
        
        3.2.8.8 分页查询优化
        
        3.2.8.9 覆盖索引
        
        3.2.8.10 索引下推（ICP）
        
        3.2.8.11 其他优化策略
        
        3.2.8.12 Count(1)，count(*)，count(字段)
        
        3.2.8.13 Select *
        
        3.2.8.14 主键设计方案
      - 3.2.9 数据库设计
      - 3.2.9.1 范式、反范式化
        
        3.2.9.2 BCNF（巴斯范式）
        
        3.3.9.3 ER模型
        
        3.2.9.4 数据库对象编写
        
        3.2.9.5 数据库相关调优
        
        3.2.9.6 表结构优化
    - 3.3 Mysql-事务篇（P161-P172）
    - - 3.3.1 事务基本概念
      - 3.3.1.1 ACID
        
        3.3.1.2 4种隔离级别、3种并发问题
      - 3.3.2 事务日志
    - 3.4 Mysql-锁篇（P173~P182 *）
    - - 3.4.1 从数据操作的类型划分--读锁、写锁
      - 3.4.2 从数据操作的粒度划分--表级锁、页级锁、行锁
      - 3.4.2.0 基本概念
        
        3.4.2.1 表锁
        
        3.4.2.2 Innodb行锁
        
        3.4.2.3 页锁
      - 3.4.3 从对锁态度划分--乐观锁、悲观锁
      - 3.4.4 从加锁方式划分--显示锁、隐式锁
      - 3.4.5 其他锁--全局锁、死锁
      - 3.4.6 锁内部结构
      - 3.4.6.1 基本概念
      - 3.4.7 锁监控
      - 3.4.7.1 具体应用
    - 3.5. Mysql事务篇（P183~P186 *）
    - - 3.5.1前提概念
      - 3.5.2 MVCC基本概念
      - 3.5.3 MVCC流程
    - 3.6 Mysql日志与备份篇（p187-p199）
    - - 3.6.1 Mysql日志
      - 3.6.1.1 基本概念
        
        3.6.1.2二进制日志（P189~P190略）
        
        3.6.1.3 主从复制、备份（P191~P197略）
        
        3.6.1.4 数据恢复

🔥3.Mysql高级篇

3.1 MySQL架构篇(P96~P114)

3.1.1 基本概念

3.1.1.1 查看字符集

##Mysql8.0之前默认是latin1，故而需要设置表的时候显示设置为utf8mb4。8.0之后是utf-8（用utf8mb4兼容特殊字符，比utf-8更好）
#查看服务器所有字符集
show variables like 'char%'
#查看数据库信息
show create database gulimall_admin;
#修改数据库字符集
alter database gulimall_admin character set 'utf8mb4'

3.1.1.2 字符集与比较规则

##一般默认字符集采用utf8mb4_general_ci：校对速度快，准确率差，如果库表结构有德、法、俄其他语言，必须采用utf8mb4_unicode_ci：准确率高，校对速度低
#查看服务器字符集与比较规则
show charset;
##由于Mysql查询的时候涉及到查询windows->mysql环境下编码的转化，所以需设置统一编码，Mysql给出了解决办法，这也是平时导出sql的定义
SET NAMES utf8mb4;

比较规则定义如下：

后缀	英文释义	描述
_ai	accent insensiteve	不区分重音
_as	accent sensiteve	区分重音
_ci	case insensiteve	不区分大小写
_cs	case sensiteve	区分大小写
_bin	binary	二进制比较

3.1.2 SQL规范与SQL_Mode

3.1.2.1 基本概念

1. 针对Mysql5.7或者8.0可以设置不同的大小写是否敏感。
1. 关键字、函数名大写，其余的小写，然后sql结束加分号。

3.1.2.1 SQL_Mode

1. 宽松模式：插入字符长度大于库表规定的大小时、格式不对时，不会报错
1. 严格模式：插入字符长度大于库表规定大小时，会报错（5.7之后默认）

# 正常select * 的时候group by 是会报错，原因就是因为设置了sql_mode，当去掉的时候就能group by了，但是数据就不准了。
select @@session.sql_mode
select * from user;
select * from user group by name;
set session sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3.1.3 Mysql数据目录

3.1.3.1 基本概念

#数据目录--/var/lib/mysql/
show variables like 'datadir';
#命令目录 /usr/bin##查看数据库
# mysql--mysql用户权限相关信息
# information_schema--存储触发器、存储过程、索引等库表信息
# performance_schema--监控mysql性能指标
# sys--视图管理information_schema、performance_schema
show databases;# 存储表结构数据 XXX.frm表结构 XXX.ibd表数据

3.1.4 Mysql–用户管理（P105~P108略）

3.1.5 Mysql–逻辑架构

3.1.5.1 基本架构

1. 连接层：连接管理1. connection pool：连接池，采用TCP连接池与线程池和外界交互
2. 服务层：解析优化1. SQL Interface：接受SQL指令并返回查询结构2. Cache & Buffers：缓存查询结果（8.0删除）3. Parser：解析器，把sql解析成语法树4. Optimizer：优化器，对sql进行优化，索引逻辑优化，物理结构优化等等
3. 引擎层：存储引擎1. Pluggable Storage Engine：插件式存储引擎，与底层文件交互

3.1.5.2 架构原理–内部执行流程

#查询mysql是否开启缓存
show global variables like '%query_cache_type%';
show status like '%Qcache%';

3.1.6 Mysql存储引擎

3.1.6.1基本概念

##查看存储引擎
#transactions：事务
#XA:是否支持分布式事务
#Savepoints：是否支持事务的回滚
show engines;

3.1.6.2 MyIASM与Innodb比较

Innodb：具备外键支持的事务默认的存储引擎、支持行锁
MyIASM：非事务的存储引擎，只支持表锁。

其他存储引擎：Archive,CSV,Memory

3.2 Mysql索引及调优篇(p115-p160 *)

3.2.1 索引数据结构

3.2.1.1 基本概念

索引目的：减少磁盘I/O次数，存在与存储引擎中，需要占用磁盘空间，维护索引会降低更新表的速度。可以提升分组、排序的查询速度。

建议：可以先删除索引，加入数据，然后再加入索引

3.2.1.2 设计索引

Compact行格式：

1. record_type：0普通记录 1：目录页 2最小记录 3最大记录

2. next_reocrd：下一条记录地址

大致模型

1. 对于数据库中每一页建立目录项：存储该页最小的记录(针对某个字段做索引就记录该字段)
1. 新增一条id = 2的时候可能会出现页分裂：新增一条新页，然后存储id=2
1. 然后由于要保证顺序性，要进行 记录移动，把id = 2移动到正确位置。
1. 目录页也组装成了数据页，用于查找各个数据页中内容。目录页与数据页之间也是双向链表连接。

#B+树为什么不超过4层？
答：层次越高，I/O次数越多（需要把目录页、数据页都加载进来），越耗时。且4层能存储的数据已经非常多。

3.2.2 索引概念

3.2.2.1 聚簇索引

聚簇索引：由上图可知，数据文件和索引文件放在一起。主键索引也属于聚簇索引。表只有一个聚簇索引，页内是属于主键顺序的单向链表。页之间是双向链表，叶子结点是库表的数据。若没有主键，则用非空唯一索引，若没有唯一索引，则给个默认的主键id。可大大节省IO操作。

3.2.2.2 二级索引（辅助索引、非聚簇索引）

二级索引：结果与聚簇索引类似，但是叶子结点存储建立索引的字段，比如name。外加主键值。会有回表即去聚簇索引找到具体记录值的操作。查询效率比聚簇索引底，但是insert, update , delete效率高，因为不涉及到实际记录的修改。

3.2.3.3 联合索引

联合索引：多个二级索引，比如name, phone，底层存储的先按索引建立的先后顺序排列，底层存储name,phone, 主键。

3.3.3.4 Innodb注意事项

根目录位置不变：上述描述的是先生成记录，然后由下自上生成目录，实际是先有数据页，然后复制数据页到新一页中，将原始数据页转换为目录页。
二级索引、联合索引非叶子结点记录唯一：由于聚簇索引是由主键自增保证了顺序性，但是二级索引却不一定有顺序性，所以二级索引非叶子结点会携带主键保证同样的目录能找到唯一存储位置，即无论叶子结点和非叶子结点都有主键。
一个页最少存储2条记录，为了构成二叉树呀！
（MyIsam都是非聚簇，数据文件与索引文件分离，索引记录存的是地址）

3.3.3.5 索引代价

空间代价：一个数据页默认是16KB，占用存储空间
时间代价：对表中数据增、删、改的时候，需要去改的时候非常花时间（之前由此开发的时候几百万和二十万数据联表查询非常快，但是批量更新状态的时候却非常花时间。）

3.2.3 Mysql底层存储相关数据结构

3.2.3.1 基本概念

由于索引和数据占用内存过大，不能一次加载到内存中，所以底层选择合适数据结构目的都是为了减少磁盘IO次数。

3.2.3.2 Hash

Hash算法种类：MD5、SHA1/2/3等
Hash不能被设计成索引结构原因：
1. 只支持=, !=, in查询，不支持范围查询，会退化成O(n)
2. Key值无法针对多个联合索引，且key值重复多的话Hash碰撞也多，且key值排列没有顺序，不支持排序
1. MYISAM，Innodb不支持hash。Memory支持hash
Redis核心：Hash表
自适应Hash索引：某个数据经常访问，则放入Hash索引中，类似于redis与Mysql，方便后续查找。

#Mysql默认开启自适应Hash索引
show variables like '%adaptive_hash_index'

3.2.3.2 二叉搜索树、AVL树

1. 二叉搜索树：左结点比根节点小，右结点比根结点大。
1. AVL树：左右两个子树高度差<= 1，且左右两子树都是平衡二叉树。

3.2.3.3 B树、B+树

1. B树：多路平衡查找树，可看成N叉树。左边比根小，中间介于根12, 20之间，右边比根大。所有结点都存储数据。

2. B+树：孩子数量=关键字数量（因为要一一对应查找），而B树是判断范围，所以孩子数量=关键数数量+1.

B+数查询更稳定（因为一定遍历树的阶数），范围查找也很快（找到2个数之后，叶子结点之间数据指针遍历即可），效率高（由于目录页不存在数据，所以16KB能存储更多数据，树也更矮胖，磁盘IO次数更少。）

3.2.3.4 R树

结点空间坐标问题：MyISAM，Innodb支持。Memory不支持

3.2.4 Innodb数据存储结构

3.2.4.1 基本概念

页：磁盘与内存数据交互的基本单位，默认16KB

show variables like '%innodb_page_size%';

行->页->区->段->表空间

3.2.4.2 页内部结构

文件头
页头
最小最大记录
用户记录
空闲空间
页目录
文件尾

一、文件头与文件尾

文件头：记录了文件页号、页类型（系统页、数据页、Undo日志页）、页之间的双向指针、校验和（为防止页刷盘的时候出错，需要回滚，即将长字符串经过算法转换为短字符串）、页最后被修改的日志文件位置。
文件尾：校验和、页最后被修改的日志文件位置。配合文件头进行使用校验页刷盘的完整性。

二、用户记录、空闲空间、最大最小记录

用户记录：实际存储的数据

最小最大记录：每一页中Mysql默认生成的2条记录

空闲空间：剩余的空间

三、页目录、页头部

页目录：对于页中记录分组存储，称为槽，每组的数量总和记录作为n_owned

页头部：页目录中槽数量、记录指的方向、记录数量等等。

四、B+树查询

#B+树按节点类型划分
叶子结点存储行记录，非叶子结点存目录页和页面指正。#B+树检索
1、从B+树根开始检索，逐层找到叶子结点，将数据页加载到内存中。
2、页目录的槽采用二分法找到记录分组，然后再在组内进行链表遍历查询。

3.2.4.3 Compact行格式

一、基本概念

#查看mysql默认行格式-dynamic
select @@innodb_default_row_format;

二 Compatc行格式内部结构

1. 记录的额外信息1. 变长字段长度列表：varchar(100)，对于变长字段表里实际占用的长度。2. Null值列表：对于定义的not null，主键这些非空与Null字段要分别标识出来。3. 记录头信息：见下面，主要是为了标识记录之间、页、记录本身所用到
2. 记录的真实数据1. 各个列的值2. row_id（隐藏的主键id）, transaction_id, roll_pointer（MVCC所用）

记录头信息

#实际表记录被删并不是将后续地址移动，然后标记为删除，变更链表结构
delete_mask：是否被删除#个人感觉没有什么用处，了解即可。
min_rec_mask：目录项中最小的主键id=1，其他数据项都是0#重要
record_type：0普通记录、1目录项、2最小记录、3最大记录#heap_no
heap_no：由于每页会默认插入2条最小与最大记录，分别对应0,1。其他插入记录则从2开始计算#存储页中每组数据的总和记录
n_owned：页中每组记录的总和#next_record
next_record：指向下一条记录的地址偏移量

3.2.4.4 Dynamic、Compressed行格式

#65533 + 2个字节变长字段长度 + 1NULL值标识
create table vachar_size_demo(c varchar(65533) not null
)ROW_FORMAT = compact;

两者原理和Compact类似，不同之处在于对上面行格式溢出的处理。

Compact：对于行溢出的行格式数据存储一部分到其他页中。

Dynamic：行溢出的数据全部移到另外一个溢出页中，然后用个指针指向。

Compressed：对于行溢出数据进行压缩。也是全部移到另一个溢出页中，然后用指针指向。

3.2.4.5 页、区、段、表

页：数据页，存储索引与数据（16KB）

区：各个页尽量保证顺序，可以减少IO(64个页)

段：将区分成叶子段和非叶子段存储。

表：系统表空间、独立表空间。

3.2.5 索引创建与设计原则

3.2.5.1 基本概念

1. 功能逻辑划分：普通索引、唯一索引、主键索引、全文索引（FULLTEXT）
1. 物理实现方式划分：聚簇索引、非聚簇索引
1. 作用字段个数划分：单列索引、联合索引

索引的数据结构即是B+树

3.2.5.2 索引创建准则

一、适合创建索引情况

唯一字段创建索引
CRUD频繁的where条件查询
针对Group by，Order by的2个字段，需要建立联合索引且groupby字段在前，Orderby字段在后。
Distinct字段添加索引，因为B+树存储该字段会存储在一起，然后查询效率就会提升。
多表Join：连接表不要超过3张、where添加索引、连接字段添加索引。
使用字符串前缀对varchar创建索引：

#对于varchar上创建索引，要指定索引长度，即查询前一部分，但是导致的问题就是排序不准确
create table shop(addrass varchar(120) not null);
#指定字符串索引前缀部门
alter table shop add index(addrass(12));
#查看区分度，越高越好
select count(distinct addrass) / count(*) from shop;

尽量使用联合索引、使用频繁的列放在最左侧。、
索引尽量不要超过6个。索引太多会占用磁盘空间，CUD需要维护索引。优化器也要进行多次选择。

二、避免创建索引情况

1. 重复数据多的字段：高于10%的重复度就不适合创建。
1. 频繁更新的字段
1. 不建议对无序字段添加索引（要对id生成尽量保证自增）

3.2.6 性能分析工具

3.2.6.1 基本优化步骤

查询系统参数show status like …

3.2.6.2 慢查询日志

1. 默认运行时间超过10秒的long_query_time会被记录到慢查询日志中。
1. 慢查询相关命令

#查询慢查询是否开启
show variables like '%slow_query_log';
#全局开启慢查询
set global slow_query_log = on;
#慢查询与其日志位置
show variables like '%slow_query_log%';
#超时查询阈值
show variables like '%long_query_time%';#设置与查看全局和当前会话的超时时间。
set global long_query_time = 1;
show global variables like '%long_query_time%';
set long_query_time = 1;
show variables like '%long_query_time%';#关闭慢查询
set global slow_query_log = off;

3. 根据日志查看具体sql

#日志来源从上面sql可以找到，docker需要进入容器里面，正常部署登录linux即可访问
mysqldumpslow -a -s t -t 5 /var/lib/mysql/bbdb2f5315d0-slow.log

3.2.7 Explain执行计划

3.2.7.1 基本概念

至少是range，要求是ref，最好是const

Id：id相同的一组执行。会分成多组情况：子查询、union嵌套

select_type：simple、union查询会出现Primary主要和union的类型

partitions：未分区统一为NULL，sql可以自定义分区数量（小于多少在一个分区里面）

type：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

system：精准只有一条记录匹配（在MyIsam，memory中）
const：针对主键、唯一索引查询。（当类型不对时，）

eq_ref：被驱动表通过主键或唯一索引进行等值匹配

explain select * from user u1 join user u2 on u1.id = u2.id

ref：普通索引（当类型不对应时，会进行隐式转换，注入函数，则不会使用索引，改为ALL查询）

ref_or_null：在查询条件后加入了or null

explain select * from user where name = '123' or name is null;

index_merge：两个二级索引or

explain select * from user where name = '123' or last_qr_user_id = '123';

unique_subquery：两张表关联查询，主键id管理

explain select * from user u1 where id in 
(select id from user u2 where u1.last_qr_user_id = u2.last_qr_user_id)

range：范围查询in，>

explain select * from user u1 where name in ('123'); # >

index：索引覆盖，联合索引中，查询字段和在索引中，where条件却只在联合索引右边
```
explain select name from user u1 where last_qr_user_id = '123';
```
ALL：全表查询

possible_keys、key：索引优化器对可能用到的索引分析使用最终要用到的索引

key_len：一般联合索引用越大越好（2个变长+1个NULL）

ref：一般结合tyep来看是const等值比较、或者为列、或者为某个函数

rows：预估读取记录条数

filtered：rows预估读取出来的记录满足要求比例。（除开索引后其他条件的记录）

select * from user where name = 'asdf' and time > now();

extra：

1. using everything：全表扫描
1. no tables use:没用用到表
1. using index:覆盖索引（二级索引中查询字段等于where条件）

3.2.7.2 Explain-进阶、trance、sys schema（略）

#以Json格式读取sql
Explain format = JSON select * from user u1
join user u2 on u1.name = u2.name;

3.2.8 索引查询优化

3.2.8.1 基本概念

1. SQL语句的优化（逻辑层面优化）
1. 索引优化（物理层面优化）
1. 数据库参数设置优化
1. 分库分表

3.2.8.2 索引失效情况

最左匹配原则
1. where条件中顺序无关，但一定要有联合索引第一个。后面第二个、第三个有没有都行，与之代价就是后面的索引字段无法使用。
计算、函数、类型转换导致索引失效
1. 因为索引底层是采用实际字段存储，而函数无法带入到实际存储中，故而必须一个个比较，即全表扫描
2. char、int类型不匹配失效：采用了隐式函数，导致索引失效。不过实际mybatis中会进行类型强校验，开发中一般不会出现这个问题，不过需要注意一下。
范围条件右边索引失效
1. 索引是可以使用范围查询的，也可以使用group, order，但是在联合索引中，采用范围后会使得索引后面字段全失效。解决办法就是把范围查询字段放到索引最后面
!=、is not null索引失效：因为需要遍历整个B+树(is null不会导致索引失效)
like以%开头会失效，结尾不会。
or前后存在非索引的列会导致索引失效：因为非索引列本身就是全表扫描，采用并集更是如此
比较数据库字符集编码格式不一样，导致字符转换采用函数导致索引失效

3.2.8.3 索引建议

1.联合索引采用过滤性强的放在前面：比如uid，status这种放在后面

3.2.8.4 关联查询优化

1. 内连接：有索引的数据量大的表作为被驱动表供没有索引的数据量小的驱动表查询。（给关联字段添加索引）
1. Join查询时，使用查询结果集(行 * 单行容量)小的驱动表嵌套大的驱动表

3.2.8.5 子查询优化

1. 禁止使用not in，not exists子查询，改用left join … where b.x is null / = ‘’;

3.2.8.6 排序索引

1. 对应索引顺序不能错，否则不会使用索引
1. 对于排序数据优化器会综合考虑全加载到内存进行fileSort更快还是使用索引排序更好。尽量使用上索引排序
1. 当使用where ... order by时，也能用上索引

3.2.8.7 Group by优化

1. 使用group by，order by，distinct时，尽量保证where过滤结果集在1000以内

3.2.8.8 分页查询优化

1. select … from … limiit 20000, offset 10改为select …from where id > 20000 limit 10。保证往聚簇索引上靠

3.2.8.9 覆盖索引

名词：即查询字段和where条件内索引恰好对应，字段可以少，但是不能多。

1. !=、'%a%'这些在上面说会失效，是因为查询select * ，但是在覆盖索引中优化器认为不需要回表更好，故而会使用索引。
1. 避免了回表查询，另外回表会导致随机IO，但是只查二级索引就是顺序IO，故而使用顺序IO加快了查询效率

3.2.8.10 索引下推（ICP）

针对联合索引(a, b)select … where a = ‘’ and b like ‘%A%’，原本由于索引失效只会查a = ''100条，然后全部回表查询。但是优化器实际却是对100条进行b字段过滤，查到10条，再进行回表查询，这就是ICP索引下推

3.2.8.11 其他优化策略

1. 大表in小表。小表exists大表，因为小表是一条数据送入大表查询。

3.2.8.12 Count(1)，count(*)，count(字段)

count(1)，count(*)本身没太大区别

count(字段)尽量使用二级索引，因为字段较少。

3.2.8.13 Select *

避免采用select *，Mysql会查询数据字典解析select * 成具体列会消耗资源和时间。另外也会导致无法使用覆盖索引

3.2.8.14 主键设计方案

淘宝：订单id可能是时间 +去重字段 + 用户id尾号6位
mysql8.0有改进的主键id

3.2.9 数据库设计

3.2.9.1 范式、反范式化

1.Normal Form（NF）

反范式化：由于范式会导致表太多、性能业务下降，所以为减少联表查询而避免范式化。比如id ,name，部门name

3.2.9.2 BCNF（巴斯范式）

在3NF基础上消除了主属性对候选码的部分依赖或传递依赖。就类似于studentid，科目，老师。科目可能会重复，就要把studentid，科目拆开。

3.3.9.3 ER模型

Entity-Relationship定义：实体(行)、属性（列）、联系集(实体之间的关系)。实体集就是表

1. 实体转为一个表、属性转为字段
2. 多对多关系转化为表

3.2.9.4 数据库对象编写

1. 临时库：tmp_XXX_20220315
1. 备份库：bak_XXX_20220315
1. 字段都有默认值
1. 唯一索引uk_，普通索引 inx_。单表索引个数不超过6个
1. 不允许select *，改为字段。insert也要指明字段。
1. join不要超过5个表

3.2.9.5 数据库相关调优

选择合适的DBMS：逻辑优化（SQL）、物理优化（索引）
增加redis缓存
库级优化：读写分离、分库分表

一、优化Mysql参数(修改mysql缓存区大小)：

innodb_flush_log_at_trx_commit：表示何时将缓冲区数据写入日志文件。将1改为2可以减少CPU使用
调整Innodb_buffer_pool_size：值调大可以增加索引和数据缓冲区大小，减少磁盘IO
调整innodb_buffer_pool_instances：调大后即把缓冲区分成多个区。提高系统并行处理能力

3.2.9.6 表结构优化

冷热数据分离：频繁操作的字段和不频繁的分离。比如会员基本信息和会员拓展信息
增加中间表：用户，部门、增加用户部门表。
时间用timestamp、decimal代替float,double

#cardinality区分度
show index from user;
#刷新上面的区分度
analyze table user;#检查表
check table user;
#优化表：优化大量更新、删除后varchar text的碎片;（每次数据库手动更新后刷新、优化一下表。）
optimize table student;

4. 尽量不要对数据分片，会带来逻辑、部署、运维等复杂度。支持千万级别数据没问题。

3.3 Mysql-事务篇（P161-P172）

3.3.1 事务基本概念

3.3.1.1 ACID

show engines：查询支持事务的引擎。

事务：让数据从一种状态到另一种状态

A(Atomicity)原子性：事务要么成功，要么失败回滚

C(Consistency)一致性：钱的变化多少最后一定要一样

I(Isolation)隔离性：事务之间互不干扰。

D(durability)持久性：对数据库修改永久性

# Mysql默认开启隐藏式事务，即每条sql默认提交。显示事务就是 begin...rollback/commit
SHOW VARIABLES LIKE 'autocommit'

3.3.1.2 4种隔离级别、3种并发问题

脏写：一个事务提交后读取到了另一个事务回滚前的数据（4种隔离级别都避免了脏写）

脏读：一个事务读取到了另一个事务还没提交的数据

不可重复读：一个事务读取到了一个事务更新了的数据

幻读：一个事务读取到了另一个事务插入的行

3.3.2 事务日志

事务的原子性、一致性、持久性由事务的redo、undo日志保证

bin log：主从复制使用

redo log：重做日志，保证事务持久性。记录物理级别的页号等

undo log：回滚日志，保证事务的原子性、一致性。用于事务回滚与一致性非锁定读（回滚到指定版本MVCC）

因为内存和磁盘交互时间过长，为保证CPU-内存-磁盘数据一致，在CPU-内存中时，先写入redo日志。

redo log：在事务中不断写入，而bin log是事务提交之后一次性写入，两阶段提交比较也是如此

Redo log组成：

1. 重做日志缓存redo long buffer

#默认16m
SHOW VARIABLES LIKE '%innodb_log_buffer_size%'

2. 重做日志文件 redo log file

事务在写入内存buffer的时候也会写会redo logbuffer，然后往redo log file 以及磁盘中写.

刷盘到redolog file的策略默认是事务提交就刷盘。字段对应：innodb_flush_log_at_trx_commit

Undo log：采用回滚段。事务会使用对应回滚段

3个隐藏列：row_id，transaction_id：事务id roll_pointer：回滚指针。

row_id：没有主键唯一索引，就用这个默认的隐藏列做主键

transaction_id：事务id

rollback_pointer：回滚指针，指向undo log的指针

3.4 Mysql-锁篇（P173~P182 *）

3.4.1 从数据操作的类型划分–读锁、写锁

锁定读
- 采用加锁（X锁、S锁）的方式读取数据
写操作
- Delete：先在B+树中定位到记录，先获取到X锁，再执行delete mark操作。
- Update：
  - 不更改主键值，不改字段：未进行任何修改，即类似于在B+树获取记录位置的一种锁定读
  - 不更改主键值，更改字段：获取记录X锁，先Delete后Insert
  - 更新记录：就是先执行Delete后执行Insert操作
- Insert：由于找不到B+树中对应记录，所以实际是采用一种隐式锁来保证事务提交前不会让其他事务访问。

3.4.2 从数据操作的粒度划分–表级锁、页级锁、行锁

3.4.2.0 基本概念

数据库解决脏读，不可重复读，幻读方案如下：

1. 读操作采用MVCC，写操作加锁。1. MVCC：生成readView，通过readView找到正确的记录版本（历史记录版本记录在undo log）
2. 读写操作都加锁：select lock in share mode/for update,insert（底层采用next-key lock）

3.4.2.1 表锁

S,X锁可以加在表或者行上

表级别的S锁、X锁（实体）

元数据锁（MDL）：当进行CRUD操作时，进行DDL操作会阻塞，反之亦然。

##表锁-概念
#表锁-S锁
lock tables t read
#表锁-X锁
lock tables t write
##表锁-使用-实际innodb默认不会为表使用表锁
#表锁-使用
show open tables;
show open tables where in_use > 0;
lock tables table_lock read;
lock tables table_lock write;
unlock tables;SELECT * FROM `table_lock`update table_lock set name = 'huyuqiao' where id = 3

意向锁（抽象）
1. 含义：为了协调行级锁与表锁。分为意向共享锁（IS）与意向排他锁（IX）
2. 原理：行级加s,x锁时，会自动给表加一个对应的意向s,x锁。该意向锁会导致在本事务结束前后续无法加入表X锁（local table加表X锁的话会阻塞，如果行表分别加S锁则不会阻塞），但是互相不影响（比如不同行for update并不冲突，只是对应前面说的无法加表锁）。
自增锁(Auto-increase，抽象)
- 插入数据模式
  1. 简单插入：知道插入数据的sql，如：insert…values
  2. 批量插入：不知道插入的行数，如：insert…select
  3. 混合模式插入：字段类型不指定、或insert … on duplicate key update变插入为更新。
- innodb_autoinc_lock_mode：防止多行数据对表自增锁的竞争
  1. innodb_autoinc_lock_mode=0：不同insert会并发争夺
  2. innodb_autoinc_lock_mode=1（mysql8.0之前默认）：
    1. 对于批量插入，会从执行到结束持有auto_inc锁
    2. 对于简单插入，通过mutex轻量级锁获取指定递增数量，在分配过程中持有，而不是直到语句结束
  3. innodb_autoinc_lock_mode=2（mysql8.0之后默认）：
    - 没有了auto_incre锁，所有insert类交替竞争递增值。
元数据锁(MDL锁，抽象)

在MDL层面，也可以分成MDL读锁与MDL写锁，用于协调DML与DDL操作。
1. MDL读锁：针对CRUD操作。与意向锁类似，每一个CRUD操作会自动加入MDL读锁，故而多个MDL读锁并不冲突
2. MDL写锁：针对DDL操作。与MDL读锁操作互斥，故而做CRUD操作时操作表结构会阻塞，反之亦然
```
show processlist;
```

3.4.2.2 Innodb行锁

记录锁（实体）

针对表中存在的id，加行级别的S、X锁（for update, lock in share mode, CUD操作）

间隙锁（实体）

针对表中不存在的id,加的S、X锁实际为间隙锁，即锁住2个id范围(开区间)。目的是为了防止幻读（因为无法对不存在的数据添加锁）。可以对表中不存在的数据添加间隙X,S锁
- 特性：对于间隙内加X,S锁都可以，但是针对同一条id的insert则会阻塞
死锁：由于间隙锁在S、X锁对行加锁时不冲突，故而会导致死锁。
- 解决办法：Innodb对死锁会设置超时时间，或者rollback某个事务。

临键锁（Next-key lock）

Innodb：默认Next-key lock（即记录锁与间隙锁混合）

for update：当扫描到索引和主键时，只锁某行，没能扫描到就会扫描全表，即表锁

#库中有id=3无id=2数据时，可以视为临键锁。对区间内数据可以加任意X锁，但是对id=3只能加一个X锁。
select * from user where id <= 3 and id > 1 for update;

插入间隙锁（Gap间隙锁的一种特殊形式）
- 当一个事务插入时，若插入未知已被添加Gap锁，则要生成一个锁结构的等待。Gap锁释放后，多个插入的事务可以正常提交。

3.4.2.3 页锁

一页上具有有个行，所以页锁介于表锁（无死锁）与行锁（有死锁）之间，会有死锁情况

3.4.3 从对锁态度划分–乐观锁、悲观锁

乐观锁：适合读操作多的场景，当并发时，会存在某个事务更新不了版本号，但不影响实际读取。（atomic就是使用CAS实现乐观锁）。当主从复制时，由于主库复制到从库存在时间差，所以需要强制读取主库
- 采用版本号实现：update …set version = version + 1 where version = version。
- 采用时间错实现：update …set … where update = update_time
悲观锁：适合写操作多的场景，用实际代码进行加锁
- select … for update：注意要使用索引，避免全表扫描。

3.4.4 从加锁方式划分–显示锁、隐式锁

显示锁：SQL语句中显示加X、S锁
隐式锁：

脏读、脏写：原始如果没有对应加锁读取的话，会导致脏读，如果更新的时候多个事务更新的话会发生脏写。

聚簇索引：其他插入事务发现当前事务的transaction_id是活跃事务的的话就会为获取事务创建一个锁结构(is_waiting=false)，自己创建一个is_waiting=true。

二级索引：二级索引页有PAGE_MAX_TRX_ID属性，若该值小于当前活跃事务，则说明该事务已经提交，否则走上述聚簇索引逻辑。

展示：比如利用间隙锁入库时，另一个事务select 查全表，则发现无法入库。

3.4.5 其他锁–全局锁、死锁

全局锁：对整个库加锁，让整个库只读
- Flush tables with read lock
死锁：互相占用资源并等待对方释放

处理死锁方式：
1. 等待超时
  - ```
  show VARIABLES like 'innodb_lock_wait_timeout';
```
2. 死锁检查–rollback回滚量最小的事务

3.4.6 锁内部结构

3.4.6.1 基本概念

锁所在事务信息：指针
索引信息：针对行锁来看是属于那个指针
表锁/行锁信息：哪个表行加的锁
type_mode：锁类型（is_waiting，IX，IX）
其他信息
比特位

3.4.7 锁监控

3.4.7.1 具体应用

#总体查询阻塞事务
show status like 'innodb_row_lock%';#查询具体阻塞事务
select * from information_schema.INNODB_TRX\G

3.5. Mysql事务篇（P183~P186 *）

3.5.1前提概念

快照读（一致性读）：基于MVCC，查询正在被其他事务更新的行，能读到被更新之前的值。

举例：普通select都属于快照读,即不加锁的非阻塞读

当前读：最新版本记录

举例：加锁的读(for update, lock in share mode，insert, update, delete )

3.5.2 MVCC基本概念

MVCC：为解决读写问题而衍生出来。

名词解释：多版本并发控制。针对读已提交和可重复读。因为读为提交与可串行化都无法读到历史版本。
实现原理：隐藏字段(trx_id,roll_pointer)、undoLog版本链、readView
名词解释：
1. trx_id：transaction_id，当前事务id
2. roll_pointer：rollback_pointer，用于记录事务数据变更的指针（只在事务回滚时起作用，提交了就会被回收）
3. undoLog版本链：上面roll_pointer依次指向所链接起来的就叫版本链
4. readView：creator_trx_id，trx_ids,up_limit_id,low_limit_id
  1. creator_trx_id：创建readview的事务id，即上述trx_id（不变动的语句默认为0，insert,delete,update这些才分配事务id）
  2. trx_ids：事务id列表
  3. up_limit_id：trx_ids中最小的事务id
  4. low_limit_id：系统中下一个分配的事务id
    
    举例：现在有事务1,3,5开启了事务，则trx_ids为{1,3,5}，up_limit_id就是1，下一个low_limit_id就是5+1=6。

3.5.3 MVCC流程

大致流程如下：读已提交与可重复读有区别，下面细分

读取事务获取当前事务id
获取readView（数据库所有事务，但是版本链(undo log)只针对某行的事务）
比较当前事务id与readView，不符合规则则从Undo log中获取历史快照，符合就返回对应事务id的数据

规则

能访问到对应数据：up_limit_id > 事务的trx_id 或事务的trx_id不在trx_ids中。

不能访问到对应数据：事务的trx_id >= low_limit_id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kibo771v-1647340824826)(https://leviathan.vip/2019/03/20/InnoDB%E7%9A%84%E4%BA%8B%E5%8A%A1%E5%88%86%E6%9E%90-MVCC/trans_visible.jpg)]

读已提交：为防止不可重复读与幻读，同一事务，每次查询都会生成readView。

举例：事务10,20依次来更新某行数据，先读取版本链，然后根据readView规则判断依次该读取哪行记录

1. readeView:{10, 20}，版本链如下，在来个读取操作，则先看版本链->宋八，由于前4个都在readView中，故而读取张三

如果事务10提交了，则会重新读readView{20}，按规则再次读取则会读取到王五这行数据

可重复读：同一事务，只在第一次select 获取readView，后续都复用这个ReadView

举例：

1. 同样是上述事务，readView{10, 20}，先读取的会是张三
1. 若事务10提交，readView{10, 20}，则再次读取也是张三。

总结：比较读已提交和可重复读可发现，读已提交确实无法解决不可重复读（两次事务之间有数据更新也读到了），而可重复读解决了这个问题。

3.6 Mysql日志与备份篇（p187-p199）

3.6.1 Mysql日志

3.6.1.1 基本概念

慢查询日志：记录超过规定执行时间的查询
通用查询日志：记录数据库所有指令
错误日志：Mysql启动运行停止报错的日志
二进制日志binlog：记录更改数据的日志（非查询）
中继日志：主从服务器中从服务器，保证从服务器与主服务器数据一样。

3.6.1.2二进制日志（P189~P190略）

binlog：只有数据变更，没有查询，以二进制存储

有mysqlbinglog，show binlog等命令

3.6.1.3 主从复制、备份（P191~P197略）

3.6.1.4 数据恢复

FlashBack恢复删除行delete

对于删表数据，取最近一次（比如2点，删除是在2.30）全量备份恢复出一个临时库，然后取2点到2.30的binlog日志，先应用到临时库，然后恢复到主库

posted @ 2023-03-24 15:32 大树2 阅读(558) 评论(0) 编辑收藏举报

刷新页面返回顶部

大树的Blog 程序员猴哥 微信 chendashu618

记录学习过程，总结工作经验，探究底层运行逻辑。