MySQL 学习笔记

2021-06-11 16:04:55   71  举报





AI智能生成

MySQL学习笔记主要记录了对MySQL数据库的基本操作、查询语句、数据类型、表结构设计等方面的理解和实践。通过学习，掌握了如何使用CREATE、DROP、ALTER等命令创建、删除和修改数据库和表；掌握了SELECT语句的基本语法和高级查询技巧，如连接查询、子查询、聚合函数等；了解了数据类型的概念和使用，如整数型、浮点型、字符型等；学会了如何设计合理的表结构，包括主键、外键、索引等。此外，还学习了事务处理、存储过程和触发器等高级功能。通过不断实践和总结，逐步提高了自己的MySQL技能水平。

mysql

索引

事务

InnoDB

InnoDB锁体系

读书笔记

作者其他创作

大纲/内容

MySQL学习笔记

事务

概念：事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成

哪些地方用到事务

DML

INSERT DELETE UPDATE

DDL

CREATE DROP

DCL

GRANT REVOKE

支持事务的存储引擎

InnoDB

NDB

事务的四大特性

原子性（Atomicity)

数据库的一系列操作要么成功，要么失败，不可能出现部分成功，部分失败。

Undo log 实现

一致性（Consistent)

数据库自身的完整性约束

用户自定义的完整性

隔离性（Isolation）

就是很多个事务，对表或者行的并发操作，是透明的，互相不干扰的。

持久性

对数据库的任意的操作，增删改，只要事务提交成功，那么结果就是永久的

持久性怎么实现呢？数据库崩溃恢复（crash-safe）是通过什么实现的？

持久性是通过 redo log 和 double write 双写缓冲来实现的，

我们操作数据的时候，会先写到内存的 buffer pool 里面，同时记录 redo log，如果在刷盘之前出现异常，在重启后就可以读取 redo log 的内容，写入到磁盘，保证数据的持久性。当然，恢复成功的前提是数据页本身没有被破坏，是完整的，这个通过双写缓冲（double write）保证

数据库什么时候会出现事务？

Nacvicat 等客户端工具

Java 等代码 JDBC 或者 @Transactional

最终都是发送一个指令到数据库去执行，Java 的JDBC只不过是把这些命令封装起来了

事务并发带来什么问题？

数据库的读一致性问题

具体提现：

脏读

两个事务，一个事务首先查询，返回一个结果，第二个事务更新这条记录，但是没提交，第一个事务再次查询可以查看更改后的值。

幻读

两个事务，第一个事务执行范围查询，这个时候只有一条数据满足条件，第二个事务，往这个条件插入一行数据并且提交了。此时第一个事务再次去查询，他发现多了一条数据。

不可重复读

两个事务，第一个事务执行查询，返回一个结果，第二个事务，对这条数据进行Update操作，并且提交了。此时第一个事务再次去查询，他发现结果值变了。

如何解决事务并发的问题？

SQL92标准

未提交读

没用解决任何问题

已提交读

解决脏读

可重复读

解决不可重复读

解决不可重复和幻读（InnoDB）

串行化

解决所有问题

解决方案

LBCC

基于锁的并发控制

适合场景：多读写少

MVCC

概念：在修改诗句的时候给它建立一个备份和快照，后面再来的读取读这个快照就可以了

核心思想

我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

实现方式

InnoDB 为每行记录都实现了两个隐藏字段：DB_TRX_ID 和 DB_ROLL_PTR

DB_TRX_ID，6 字节：插入或更新行的最后一个事务的事务 ID，事务编号是自动递增的（我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事务 ID）。

DB_ROLL_PTR，7 字节：回滚指针（我们把它理解为删除版本号，数据被删除或记录为旧数据的时候，记录当前事务 ID）。

查找规则

只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）

隔离级别的实现

Read Uncommited（未提交读）

没做处理

Serializable（串行化）

所有的 select 语句都会被隐式的转化为 select ... in share mode，会和 update、delete 互斥。

Repeatable Read（可重复读）

普通的 select 使用快照读(snapshot read)，底层使用 MVCC 来实现。

Read Commited（已提交读）

RC 隔离级别下，普通的 select 都是快照读，使用 MVCC 实现。

加锁的 select 都使用记录锁，因为没有 Gap Lock。

除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁封锁区间。

所以 RC 会出现幻读的问题。

事务隔离级别怎么选？

RU 和 Serializable 肯定不能用。

RC 和 RR 主要有几个区别：

RR 的间隙锁会导致锁定范围的扩大。

条件列未使用到索引，RR 锁表，RC 锁行。

RC 的“半一致性”（semi-consistent）读可以增加 update 操作的并发性。

锁

锁锁定的是什么？

锁住的是索引

锁的粒度

行级别的锁

表级别的锁

行锁

共享锁（shard Locks）

概念：获取一行数据的读锁以后，可以用来读取数据，所以它也叫读锁。注意：不要再加上读锁以后去写数据，不然的话可能会出现死锁的情况

加锁方式

select .... lock in shard mode;

释放方式

只要事务结束，锁就会自动释放，包括提交事务和结束事务

排他锁（Exclusive Locks）

概念：它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排他锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

自动加锁

手动加锁

select * from table for update

表锁

意向锁

如果一张表上面至少有一个意向共享锁，说明有其他的事务给其他的某些数据行加上共享锁

如果一张表上面至少有一个意向排它锁，说明有其他的事务给其中某些数据加上了排它锁

意向共享锁

当我们给一行数据加上共享锁之前，数据库会自动在这张表上加上一个意向共享锁

意向排它锁

当我们给一行数据加上排它锁之前，数据库会自动在这张表上面加一个意向排它锁

锁的算法

记录锁

概念：当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁

使用不同的key 去加锁，不会冲突，它只会所锁住这个record

间隙锁（gap lock）【解决幻读问题】

当查询的记录不存在的时候，使用间隙锁

注意：间隙锁主要是阻塞插入insert。相同的间隙锁之间不冲突

临键锁（默认的行锁算法）

概念：当我们使用了范围查询，不仅仅命中了Record 记录，还包含了Gap 间隙，在这个情况下我们使用的就是临键锁

锁的退化

唯一性索引，等值查询匹配到一条记录的时候，退化成记录表

没有匹配到任何记录的时候，退化成间隙锁

死锁

概念：一个事务或者说一个线程持有锁的时候，会阻塞其他的线程获取锁，这个时候会造成阻塞等待，如果循环等待，就会有可能造成死锁。

锁的释放和阻塞

什么时候释放

事务结束（commit | rollback）;

客户端连接断开

阻塞多久

如果一个事务一直未释放锁，其他事务会被阻塞。 mysql 默认50s

死锁的发生和检测

条件

互斥锁

同一时刻只能一个事务持有这把锁

其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强制剥夺

当多个事务形成等待环路的时候，即发生死锁

日志

show status like 'innodb_rowPlock%'

select * from information_schema.INNODB_TRX;

当前运行的事务，还有具体的语句

select * from information_schema.INNODB_LOCKS; -- 当前出现的锁

select * from information_schema.INNODB_LOCK_WAITS; -- 锁等待的对应关系

如何杀死死锁

kill 4，kill 7，kill 8

以 kill 事务对应的线程 ID，也就是INNODB_TRX 表中的trx_mysql_thread_id

如何避免死锁

在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）；

批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）；

申请足够级别的锁，如果要操作数据，就申请排它锁；

尽量使用索引访问数据，避免没有 where 条件的操作，避免锁表

如果可以，大事务化成小事务；

使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的响。

性能优化

连接（配置优化）

引入连接池

Hikari 默认最大连接池 10

Druid 默认最大连接池 8

连接池最大公式

机器核心数*2+1

缓存（架构优化）

redis

主从复制

读写分离

分库分表

垂直分表

水平分表

优化器（SQL语句分析和优化）

首先要知道哪些SQL慢，慢在哪里？

慢日志分析

打开慢日志分析

永久设置：修改配置文件 my.cnf

slow_query_log = ON long_query_time=2 slow_query_log_file=/var/lib/mysql/localhost-slow.log

临时设置

set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效set @@global.long_query_time=3; -- mysql 默认的慢查询时间是10秒

慢sql 日志分析

show global status like 'slow_queries'; --查看有多少慢查询

show variable like '%slow_query%'; -- 获取慢日志目录

show variables like '%long_query%'; -- 查看设置的慢查询时间（秒）

mysqldumpslow 分析

例如：查询用时最多的20条慢SQL

mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log

count 代表这个SQL执行了多少次

Time 代表执行的时间，括号里面是累计时间

Lock 表示锁定的时间，括号里面是累计时间

Rows 表示返回的记录数，括号里面是累计时间

show profile

查看SQL语句执行的时候使用的资源情况。例如CPU\\IO

查看是否开启

select @@profiling

set @@profiling =1

查看profile 统计

show profiles;

查看所有

shou profile;

最后一条

shou profile to query 1;

可以根据ID 查看执行详细信息

其他命令

显示用户运行线程

show processlist;

查表

select * from information_schema.processlist;

服务器运行状态

show status;

查看select 次数

show global status like 'com_select';

存储引擎运行信息

show engine;

show engine innodb status;

把监控信息输出到错误信息error log 中（15秒一次）

show variables like 'innodb_status_output%';

set global innodb_status_output=ON;

set global innodb_status_output_locks=ON;

然后根据EXPLAIN 执行计划，开始优化

id（查询顺序）

值相同

表的查询顺序是从上往下顺序执行

值不同

先查询id值大的（先大后小）

既有相同也有不同

ID不同的先大后小，ID相同的从上往下

select type(查询类型)

SIMPLE

简单查询，不包含子查询，不包含关联查询union

PRIMARY

子查询SQL语句中的主查询，也就是最外层那层查询

SUBQUERY

子查询中所有的内层查询都是SUBQUERY类型

DERVIED

衍生查询，表示在得到最终结果之前会用到临时表

UNION

用到了UNION 查询

UNION RESULT

主要是显示那些表之间存在UNION 查询

type (连接类型)

常用连接类型优先级：system > const > eq_ref_> ref > range > index > all

不要优化的类型

cost

system

er_ref

通常出现在多表join 查询，表示对于浅表的每一个结果，都只有一行结果。

一般是唯一性索引查询（UNIQUE 或 PRIMARY）

需要优化的类型

ref(可以不用优化)

查询用到了非唯一性索引，或者关联操作只使用到了索引的最左前缀。

range(最低这个标准)

索引范围扫描

index

Explain select tid from teacher;

all

Null

不用访问表或者索引就能得到结果

Explain select 1 from dual where 1=1;

possible_key(可能用到的索引)

key（使用的索引）

possible_key为空，key 可能有值吗？

是有可能的（这里是覆盖索引的情况）

key_len(索引的长度)

跟索引字段的类型、长度有关

rows(扫描的行数)

MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

filtered

这个字段表示存储引擎返回的数据在Server层过滤后，剩下多少满足查询的记录数量的比例，他是一个百分比。

ref

使用那个列或者常数和索引一起从表中筛选数据

Extra(额外说明)

using index

使用了覆盖索引

using where

使用了where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要server层进行过滤（跟是否使用索引没有关系）

using index condition

索引条件下推

需要优化的地方

using filesort （复合索引的前提）

不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）

order by 引起

using temporary

用到了临时表

可能出现的原因

distinct 非索引列

group by 非索引列

使用join 的时候，group 任意列

如果优化

例如：创建复合索引

存储引擎

存储引擎的选择

查询插入操作多的业务表，用MyISAM

临时数据用Memory

常规的并发大更新多的表用InnoDB

分区或分表

字段定义

原则：使用可以正确存储数据的最小数据类型

整数类型

Int 有8种类型

int

integer

bigint

bit

tinyint

smallint

mediumint

不同的类型最大存储范围是不一样的

字符类型

变长情况 varchar

固定长度 char

非空

Null 类型的存储、优化、使用都会存在问题

不要用外键、触发器、视图

降低了可读性

影响数据库性能，应该把计算的事情交给程序，数据库专心做存储

大文件存储

不要用数据库存储图片（比如 base64 编码）或者大文件

表拆分

把不常用的字段拆分出去，避免列数过多和数据量过大

应用层面

尽量减轻数据库的压力

比如：

限流

MQ削峰

架构分层

连接层

通信协议

TCP/IP

Unix Socket

Named Pipes

Share Memory

连接方式

长连接（连接池使用）

短链接

通信方式

单工

数据单向传输

半双工（MySQL）

数据双向传输，但不能同时传输

全双工

Server层

查询缓存（8.0弃用 Caches &Buffers）

分析器（解析器Parser）

步骤

语法解析把一段SQL 打碎成一个个单词

词法解析对SQL做一些语法检查比如单引号有没有闭合。

结果：生成解析树

预处理器

检查解析器生成的解析树，解决解析器无法解析的语义。

比如检查表和列名是否存在，检查名字和别名，保证没用歧义。

结果生成新的解析树。

查询优化器（Optimizer）

概念：根据解析树生成执行计划，然后选择一种最优的执行计划

优化过程是一个JSON类型数据，包含三个部分

准备阶段

优化阶段

执行阶段

结果：生成执行计划

优化器优化过程

启用优化器跟踪：

SHOW VARIABLES LIKE 'optimizer_trace';set optimizer_trace='enabled=on';

执行sql 语句：

查看优化器分析过程 select * from information_schema.optimizer_trace\\G

分析完记得关闭 set optimizer_trace=\"enabled=off\";SHOW VARIABLES LIKE 'optimizer_trace';

expanded_query 优化后的SQL语句

considered_execution_plans 里面列出了所有的执行计划。

查看执行计划 EXPLAIN select name from user where id=1;

执行器（操作存储引擎，返回结果到客户端）

是谁使用执行计划去操作存储引擎？执行引擎，他利用存储引擎提供的相应的API来完成操作。

为什么修改了表的存储结构，操作方式不需要做任何改变？不同功能的存储引擎实现的API是相同的。

存储引擎层

查看存储引擎 show table status from `数据库名称`;

数据库存放数据路径 show variables like 'datadir';

存储引擎类型

文件

frm表结构

ibd数据和索引

mysql 5.7 默认存储引擎

特点

支持事务，支持外键，因此数据的完整性、一致性更高

支持行级别锁和表级别锁

支持读写并发，写不阻塞读（MVCC）

适合场景

经常更新的表，存在并发读写或者有事务处理的业务系统

MyISAM

概念：利用索引，顺序存取数据的方式

MYD索引

MYI数据

支持表级别锁（插入和更新会锁表）。不支持事务

拥有较高的插入和查询速度

存储表的行数（count 速度更快）

场景

适合只读之类的数据分析的项目

Memory

概念：将所有数据存储都在内存，以便在需要快速查找非关键数据的环境中快速访问

特点：将数据放在内存，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。知识和做临时表

CSV

Archive

如何选择存储引擎

对数据一致性要求比较高，需要事务支持，可以选择InnoDB

数据查询多更新少，对查询性能要求比较高，可以选择MyISAM

需要一个用于查询的临时表，可以选择Memory

文件系统（磁盘）

概念：InnoDB的数据都是放在磁盘上的，InndDB 操作数据有一个最小逻辑单位，叫做页（索引页和数据页）

内存结构（缓冲池）

Buffer Pool

概念：对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了，InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。

第一次把磁盘把读取到数的页到缓冲池里面。

下一次读取相同的页，就先判断是否在缓冲池里面。有就直接读取，不在访问磁盘

修改数据，先修改缓冲池里面的页。内存数据和磁盘数据不一致的事情，这个叫脏页。InnoDB 里面有专门的后台线程把Buffer Pool数据写道磁盘，每隔一段时间就一次性把多个修改写入到磁盘，这个动作就做刷脏。

缓存的内容

数据页

索引页

内存的缓冲池写满了怎么办？

InnoDB 用LRU 算法来管理缓冲池

Chnage Buffer

概念：数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一检查）。可以先把修改记录在内存的缓冲池中，从而提升更新语句（INSERT、UPDATE、DELETE）的执行速度

适合场景：如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。

Log Buffer

概念：为了避免Buffer Pool 里面的脏页还没刷到磁盘就宕机或者重启导致数据丢失的问题。InnoDB把多页的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作。（实现crash-safe）--用他来实现事务的持久性

red log

WAL技术

日志和磁盘配合的过程

关键点是先写日志，再写磁盘

Adaptive Hash Index

自适应哈希索引

磁盘结构

系统（共享）表空间

数据字典

存储表和索引的元数据（定义信息）

双写缓冲

是为了解决存储引擎写入页的数据到磁盘是发生宕机，出现部分写失效，导致数据丢失的且内存red log页本身损坏无法恢复的问题

double write分两步

一部分内存的double write

一部分磁盘的double write

因为是顺序写入，不会带来很大的开销

独占表空间

每张表独占一个表空间

存储表的索引和数据

通用表空间

存储不同数据库的表

临时表空间

存放临时表的数据，包括用户创建的临时表和磁盘的内部临时表

Redo log tablespace

undo log tablespace

存储撤销日志和回滚日志

用于修改数据时出现异常，可以用来实现回滚操作（保持原子性）

后台线程

master thread 负责刷新缓存数据到磁盘并协调调度其他后台线程

IO thread 分为 insert buffer、log、 read、write 进程，分别处理inser buffer、重做日志（red log）、读写请求的IO回调

purge thread 用来回收 undo 页

page cleaner thread 用来刷新脏读

Binlog

概念： MySQL 的Server 层的日志文件，叫做binlog以事件的形式记录所有DDL和DML语句（记录的是操作而不是数据值，属于逻辑日志）

场景：用于做主从复制和数据恢复

red log （重做日志）

存储引擎层：内存、磁盘

物理日志，记录页做了什么改动

两个指针writer pos当前写的位置，check point当前要覆盖的位置

写满了怎么办？写满了同步到磁盘 red log

undo log

执行流程

1. 先把记录写内存，再写日志文件

2.记录red log 分两个阶段

prepare状态

提交事务时，red log 设置成commit状态。

3. 存储引擎和Server 记录不同日志（双写缓冲）

索引

概念：数据库索引，是数据库管理系统钟一个排序得数据结构，以协助快速查询、更新数据库表中数据。

作用：是为了提高查询效率

InnoDB 逻辑存储结构

表空间（table space）

概念：表空间可以看做是InnoDB存储引擎逻辑结构得最高层，所有得数据都存放在表空间中。

五大类：

系统表空间

双写缓冲区

Undo表空间

段(segment)

概念：表空间的组成部分

段的分类

数据段（non-leaf node segment）

管理叶子节点的数据

索引段（leaf node segment）

管理非叶子节点数据

回滚段

一个表的段数就是索引个数乘以2

簇(区extent)

概念

段的组成部分一个段至少有一个簇一个簇大小为1MB（64个连续的页）

一个簇的所有页都被用完，会从当前页面的段新分配一个簇。

如果数据数据不是连续的，往以写满的页中插入数据，会导致页的分裂。

页(page)

概念：为了高效管理物理空间，对簇进一步细分，得到了页，簇是有连续的页组成的空间，一个簇有64个连续的页

行(row)

索引的实现模型

1. 哈希表

适应场景：适合只有等值查询的场景。

局限：数据不是有序的，做区间查询的速度很慢

优势：查询快，修改快

2. 有序数组

优势：有序数组在等值查询和范围查询场景中的性能就都非常优秀

局限：往中间插入一条记录就必须得挪动后面所有得记录，成本太高

适合场景：只适用于静态存储引擎

3. 树

BST(二叉搜索树)

概念：每个节点左子树小于父节点，右子树大于父节点

AVL树（平衡二叉树）

概念：左右子树深度差绝对值不能超过1，左子树深度是2，右子树深度（只能是1或者3）

实现：在插入和更新数据的时候执行了一系列的计算和调整的操作。左-左型，会发生右旋。右-右型，会发生左旋

局限：树得深度过高，访问一个树节点，进行一次IO一个树的节点是16K大小，一个节点存储键值、数据磁盘地址、子节点引用。浪费大量的空间

B-Tree(多路平衡查找树)

分叉树（路数）永远比关键字数多1比如：每个节点存储两个关键字那么就会有三个指针指向三个子节点节点存储磁盘地址分裂合并

节点存储磁盘地址

分裂

合并

B+Tree(加强版多路平衡查找树)

概念：关键字的数量是跟路数相等的。非子节点不存储数据。叶子节点存储数据

单表，binint类型，深度为2的B+tree.可以存储2000W作用数据

单表可以存储多少条数据呢？

假设一条记录是1K，一页大小16K（16384字节），一页能存储16条数据binint 8个字节，指针6个字节，1页大小16384字节，非叶子节点一页可以存储 16384/14=1170 个指针对于第二层来源 1170个指针相当于1170个页，1170*11701170（1层的指针树）*1170（页）*16（一页存储的条数）=21902400条记录

特点：

每个节点存储更多的关键字，路数更多

扫库、扫表能力更强

排序能里更强因为叶子节点上有下一个数据区的指针，数据形成了链表

效率更加稳定B-Tree 永远是在叶子节点拿到数据，所以IO次数是稳定的

B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）

红黑树

约束条件

1、节点分为红色或者黑色。

2、根节点必须是黑色的。

3、叶子节点都是黑色的 NULL 节点。

4、红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）。