首页  思维导图  详情

mysql

2024-09-18 16:26:51   45  举报





AI智能生成

mysql45讲学习比较总结

mysql笔记

mysql45讲

作者其他创作

大纲/内容

安装mysql

linux yum安装mysql

windows安装mysql

分层

Server层

连接器

查询缓存

分析器（对执行的sql进行词法分析和和语法分析，表或者列比存在在这一阶段会报错）

优化器（控制表的索引使用、表的连接顺序等）

执行器（先判断是否有表的操作权限，然后根据表的引擎定义调用存储引擎接口进行数据操作）

存储引擎层

存储引擎

表空间结构

独立表空间

表空间结构

组

一个组有256个区，每个组的第一个区中的第一页中存储了本组中所有区的一些信息，第一个组的第一个区中的第一个页还存储了表空间的一些信息

区

一个区有64个页，每个区有四种状态

FREE（空闲的区）

FREE_FRAG（有剩余空间的碎片区）

FULL_FRAG（没有剩余空间的碎片区）

FSEG（附属于某个段的区）

这种状态的区属于段

段

段是一个逻辑结构，由一些完整的区和零散的页构成，一个索引对应两个段

叶子节点段

非叶子节点段

为啥要引入段

以索引段为例，将索引分成叶子节点段和非叶子节点段是因为，B+ 树的非叶子节点同层的页会组成一个双向链表
当表数据量大的时候，相邻的两个页在磁盘中可能会隔得非常远

系统表空间

日志

redo log（InnoDB存储引擎独有）

在存储引擎层，InnoDB引擎独有，使得数据库拥有crash-safe能力

记录的是物理日志，在某个数据页上做了什么修改

redo log是循环写的，空间固定

redo log日志两阶段提交

1. 将要执行的操作更新到内存完毕后记录redo log日志，此时处于prepare阶段

2. server层写入binlog 日志

3. 存储引擎，提交事物（往redo logo中写入一条commit记录），此时才处于commit状态

binlog

在server层，所有引擎都可以使用，可通过binlog做数据恢复

记录的是逻辑日志，即DDL和DML语句，有三种记录模式：

statement 模式：记录执行的sql语句

row 模式：记录的是行上的变更（写入前和写入后都会被记录）

mixed 模式: 混合模式

binlog是可以追加写入的,不会覆盖以前的日志

binlog日志启用，查看，恢复数据

undo

undo 日志链表结构

undo log 页面链表

种类

普通表insert undo链表

普通表update undo链表

临时表insert undo链表

undo log 链表页面重用，需满足

1. 该链表中只包含一个 Undo页面（如果链表包含页面多，新的事务需要的页面少
链表中没有被使用的页面无法被其它事务使用，造成另一种浪费）

2. 该 Undo页面已经使用的空间小于整个页面空间的3/4

事物

四大特性（ACID）

原子性

一致性

隔离性

名词解释

一致性读视图（consistent read view）

作用

用来支持读提交和可重复读隔离级别的实现（注意与create view ...语句创建的视图进行区分，后者是个虚拟表）

实现原理

1. InnoDB里面每个事务有一个唯一的事务ID（transaction id），该id是递增生成

2. 每行数据都是有多个版本（即MVCC），每个版本都有自己的版本号（trx_id）

3. 在更新行时会将更新操作所处事物的transaction id作为行的trx_id生成一个新的版本

4. 在创建视图时会新建一个数组存储当前所有未提交的事物的transaction id

5. 在一致性读的时会判断读取行的trx_id是否小于等于（当前事物自己更新时会等于）当前事物transaction id且不在数组中，不符合条件则往前面的历史版本中继续查找

创建时机

可重复读下：在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图

读提交下：每一个语句执行前都会创建一个新的视图，这样每条语句看到的都是最新已提交的数据版本

一致性读（读取一致性视图中的版本数据）

普通select语句都是一致性读
读提交隔离级别下的select其实也是一致性读，只不过读提交隔离级别每个语句开始前都创建一致性视图。所以每次读到的都是当前已提交的最新数据。

当前读（读取当前已提交的最新数据）

可重复读隔离级别下update语句都是当前读

可重复读隔离级别下select语句是一致性读而不是当前读，要当前读的话可以通过select ... lock in share mode 或select ... for update语法进行当前读
如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待（根据两阶段锁协议，行锁只有在事物提交后才释放）

隔离级别

读未提交（Read-Uncommitted）

读提交（Read-Committed）

可重复读（Repeatable-Read，默认隔离级别）。注意：可重复读隔离级别下update语句读取的是最新提交的数据版本，而非开启事务时的数据版本

串行化（Seriaizable）

事务的并发问题

脏读

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致

幻读

事务A使用同一条件多次读取期间，事务B新增了数据，导致事务A后续读取到了之前没有读到的记录（幻读和不可重复读比，强调的是读读到了之前没有读到的数据行）

不同隔离级别下可能会出现的并发问题

读未提交

脏读

不可重复读

幻读

读提交

不可重复读

幻读

可重复读

幻读

事物隔离的实现

每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）

持久性

避免长事务对业务的影响

1. 排查项目框架事务操作方式是否使用了set autocommit=0，有则要关闭，因为这样会导致接下来的查询都在事务中（通过general_log 日志排查方式见备注）

2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。导致整个事务中只执行了几个select也开启了事务，而业务需求上可能并不需要事务。

3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间

事务的开启/提交/回滚

事物的启动方式

1. 自动提交模式下（set autocommit=1），

在未显示使用begin或start transaction开启事务时事务的开始是在执行update语句前
在执行完update语句后自动提交事务，select 语句不会启动事务

在显示使用begin或start transaction开启事务时，事务的开始是在begin的时候
（严格来讲是在begin之后的第一个操作 InnoDB 表的语句，事务才真正启动。可以使用 start transaction with consistent snapshot 这个命令立即开启一个事务）
事务的结束是手动执行commit或事务异常回滚或会话连接结束

2. 不自动提交模式下（set autocommit=0）

事务的开始是在会话连接后执行的第一个语句（即使是select语句）或手动commit后的第一个语句开启事务
事务的结束是手动执行commit或事务异常回滚或会话连接结束

事务的提交

显示方式

通过commit或ROLLBACK

隐式方式

在事务中执行DDL语句会隐式提交事务

在事务中再次执行START TRANSACTION 或者 BEGIN

在事务中执行MySQL 复制的一些语句，如START SLAVE 、 STOP SLAVE 、 RESET SLAVE 、 CHANGE MASTER TO

当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时

执行LOCK TABLES 、 UNLOCK TABLES

执行ANALYZE TABLE 、 CACHE INDEX 、 CHECK TABLE 、 FLUSH 、 LOAD INDEX INTO CACHE 、 OPTIMIZE、TABLE 、 REPAIR TABLE 、 RESET

事务回滚

回滚到事务开始点

ROLLBACK

回滚到某个保存点

设置保存点：SAVEPOINT xxx

回滚到某个保存点：ROLLBACK TO xxx

锁

全局锁（锁库）

使用场景

全局锁的典型使用场景是，做全库逻辑备份

实现方式

方式1：FTWRL（不推荐），会使得整库进入只读状态

加锁语句：Flush tables with read lock ;

解语句： UNLOCK TABLES ;

方式2：–single-transaction参数方式（推荐，需要引擎支持一致性读，如innodb），会先启动一个事物，由于MVCC的支持，这个过程中数据是可以正常更新的

示例：mysqldump –single-transaction

表锁

普通表锁

特点

需通过语法命令手动加解锁，加锁后除了会限制别的线程的读写（读读共享，读写互斥，写写互斥）外
也限定了本线程接下来的操作对象。是加读锁时，本线程只能读该表，是写锁时，本线程能读写该表

相关子查询

只能从外层查询中一行行的代入子查询中去执行了

对于派生表（跟在from后面的子查询）的优化

优先尝试把派生表和外层查询合并掉
如：SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1;
可合并为SELECT * FROM s1 WHERE key1 = 'a';

不符合合并则把派生表物化掉执行查询，不符合合并的情况有：

聚集函数，比如MAX()、MIN()、SUM()啥的
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或者 UNION ALL

talbe

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名

id值相同的行，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。

外层查询id会比里面的子查询id值小，值越大优先级越高（如果外层查询和子查询的值相同，说明查询优化器将子查询转换为了semi-join，详见备注）

UNION 的两个表有不同的id，还会多出id为null的行,因为UNION会创建临时表对两个结果集去重，而UNION ALL不需要去重不会建临时表，也不会产生id为null的那行

select_type

SIMPLE

查询语句中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型，连表查询也是SIMPLE（子查询被优化器优化成了semi-join后也是SIMPLE）

PRIMARY

包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY

UNION

包含 UNION 或者 UNION ALL 的大查询来说，除最左侧的那个小查询其它的都是UNION

UNION RESULT

UNION 多个表对结果集去重会创建临时表，对临时表的查询就是UNION RESULT

SUBQUERY

不相关子查询以物化的方式来执行该子查询时select_type 就是 SUBQUERY
由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍

DEPENDENT SUBQUERY

相关子查询（无法使用物化方式）来执行该子查询时 select_type 就是 DEPENDENT SUBQUERY
select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

DERIVED

派生表不符合和外层查询合并时，选择对派生表物化后查询的 select_type 就是 DERIVED，示例见备注

MATERIALIZED

经实验：在转semi-join查询的时候，子查询的列没有索引的时候会出现这个

type

单表查询

const（主键索引或唯一索引上常量的等值查询）

如：主键列=xxx或唯一索引列=xxx
注意和is null不能算和常量等值查询，因为唯一索引允许有多个null值的
注意：xxx常量要和列的类型对应（比如列是varchar型，常量是整型是是不行的，下面ref和range同理）

ref（普通索引上常量的等值查询）

如：普通索引列=xxx 或（普通索引a上的列=xxx and 普通索引b上的列=xxx）

range（主键索引或普通索引上的范围查询）

如：主键列>xxx 或普通索引列>xxx

index（扫描整个普通索引）

在索引为复合索引的情况下使用非最左字段作为筛选条件，但返回的列都包含在这个复合索引中
按照最左匹配原则本来是用不上这个复合索引的，但由于复合索引比主键的聚簇索引要小，所以会选择遍历该索引
这种情况通过explain分析时，possible_keys为null,key是有值的，就是这个复合索引

all(扫描整个聚簇索引)

index_merge（索引合并，一个查询使用多个索引）
出现索引合并的情况时，要考虑下是否可以建成复合索引

Intersection合并

场景1

多个普通索引都是等值查询（如果普通索引是复合索引，则必须该符合索引的列都被等值查询）
如：select * from t where index1_c=xxx and index2_c=yyy

场景2

主键索引使用范围查询+普通索引使用等值查询
如：select * from t where primary_index_c > xxx and index1_c=yyy

Union合并

场景1

和 Intersection合并场景1一样，只是将 and 换成 or

场景2

和 Intersection合并场景2一样，只是将 and 换成 or

场景3

Intersection合并中的某个场景 + or的情况
select * from t where index1_c=xxx and index2_c=yyy or index3_c=zzz

Sort-Union合并

如：select * from t where index1_c<xxx or index2_c>yyy
这种情况是先要对index1c<xxx和index2_c>yyy的单个结果进行主键排序后进行Union合并

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory。innodb不是精确的

const

见单表查询->const

eq_ref

被驱动表是通过主键或者唯一索引列等值匹配的方式进行访问的，（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）

ref

和eq_ref类似，只不过被驱动表是通过普通索引列进行访问的

unique_subquery

类似于eq_ref，只不过被驱动表是in子查询

index_subquery

和ref类似，只不过被驱动表是子查询

index_merge

见单表查询->索引合并

range

使用索引获取某些范围区间的记录

index

见单表查询->index

all

possible_keys

可能用到的索引
possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间

key

实际使用到的索引（比较特殊的是type为index的时候，key有值但possible_keys为null）

key_len

表示使用索引记录的长度，假如使用到的是一个复合索引
只使用到了第一个索引列，则key_len就是该列的长度。一个列的长度主要由右侧部分构成

对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值（如int=4，utf8的char=3），对于指定字符集的
变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占
用的最大存储空间就是 100 × 3 = 300 个字节

如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节

对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、
unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的内容

rows

全表扫描查询的时是表的行数，使用索引查询时是预计扫描的索引记录行数

filtered

rows表示的是符合索引列条件的预计条数，filtered指的是符合索引列的条件中，还符合其它搜索条件的记录占用百分比

extra

Impossible WHERE

查询语句的 WHERE 子句永远为 FALSE

Using index

查询条件的列和返回的列都在一个普通索引上时，有两种情况：

情况1：有使用到索引，此时type不为index

情况2：使用不到索引，但扫描普通索引比扫描聚簇索引代价更小的情况，此时type为index

Using index condition

一般使用普通索引后进行还需要进行回表会出现这个

Using where

包含未使用索引的搜索条件

情况1：全表扫描

情况2：条件中包含有索引条件和无索引条件

Using join buffer

当被驱动表不能有效的利用索引快访问速度，会为驱动表分配一块名叫join buffer 的内存块来加快查询速度，原理见备注

Using intersect(...) 、 Using union(...) 和 Using sort_union(...)

索引合并的情况，此时type为index_merge（没有演示出来这种效果）

Using filesort

排序的字段没有使用到索引的时候就会出现这个，通过内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序

Using temporary

当查询中使用了DISTINCT 、 GROUP BY 、 UNION等函数，在不能有效使用索引的情况下需要创建临时表去辅助查询时
注意：使用GROUP BY的时候输出Using temporary的同时还看到了Useing filesort是因为GROUP BY 后默认会加上order by
可以通过在 GROUP BY 后手动加上 order by null 的方式去除排序

Start temporary, End temporary

IN子查询转semi-join时如果使用DuplicateWeedout策略（新建只有主键列的临时表辅助去重操作）会出现这个

LooseScan

IN子查询转semi-join时如果使用LooseScan策略会出现这个

FirstMatch

IN子查询转semi-join时如果使用FirstMatch策略会出现这个

explain format=json

单表查询成本计算
有多个索引会选成本最小那个

用不到索引，全表扫描查询时，成本=
select * from s1 where common_field='b'

IO读取成本=

s1表页数 * 1

s1表页数=

页数=表大小/每页大小=表大小/（16*1024）
SHOW TABLE STATUS LIKE 's1' 可查看s1表大小（聚簇索引大小）

读取一个页默认IO成本为1

cpu检测成本=

s1表总行数 * 0.2

s1表总行数

0.2

读取1行默认cpu成本为 0.2

用到普通索引，但需要回表时，成本=
select * from s1 where key1 > 'a' and key1<'c' and common_field='b'
（假如表上有多个索引可用，会选择使用后成本最小的那个索引）

IO读取成本=

idx_key1索引上范围查找IO成本
=idx_key1索引上的区间数 * 1

此处区间查找条件为key1 > 'a' and key1<'c'，故只有一个区间
每个区间算 1 个页的IO成本，所以成本=1*1
如果是key1 in ('a','b',''c') 则有3个区间

回表后在主键索引上的IO成本
= 回表记录数 * 1

回表记录数就是所有区间数中记录总和，计算方式见右侧
每回表一条记录算 1 个页的IO成本

cpu检测成本=

idx_key1索引上的cpu成本
=所有区间总记录数 * 0.2

主键索引上通过回表记录id找到对应记录后监测common_field条件的成本
= 回表总记录数 * 0.2

两表连接查询成本计算

1. 对于左连接或右连接，它们的驱动表是固定的，只需要分别为驱动表和被驱动表计算成本，然后各自选择成本最低的索引方式

2. 对于内连接，需要每个表作为驱动表去计算成本，哪个表作为驱动表成本低就选哪个表作为驱动表
然后按照 1 中的相同的方式选择驱动表和被驱动表的索引方式

optimizer trace（sql 优化器日志）

查询方式

SET optimizer_trace="enabled=on";
SELECT * from student; --查询语句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE

注意这三句要一起执行

返回字段

QUERY

被追踪的SQL语句文本

TRACE

追踪信息，以JSON形式表现

MISSING_BYTES_BEYOND_MAX_MEM_SIZE

当TRACE信息超过了optimizer_trace_max_mem_size 的值时丢失的字节数

INSUFFICIENT_PRIVILEGES

如果被追踪的SQL使用了没有其权限的view或routines，则TRCE栏位为空且此栏位会显示为1

TRACE结果分析

http://blog.itpub.net/28218939/viewspace-2658978/

analyze table

当统计信息不准时，手动重新统计，语法：analyze table T

force index

当索引使用不符合预期时，强制使用某个索引，语法：select * from T force index(a)

慢sql日志

set global slow_query_log = 'ON';

开启慢sql

set global slow_query_log_file='xxx'

慢sql文件存储位置，window默认在“C:\ProgramData\MySQL\MySQL Server 5.7\Data”

set global long_query_time = 0.5;

慢操作的时间设置(单位是秒)

set global log_queries_not_using_indexes = 'ON';

是否开启没有使用索引的操作

慢sql日志结果

# Time: 2021-09-21T03:05:10.616223Z
# User@Host: root[root] @ localhost [::1] Id: 10
# Query_time: 0.005758 Lock_time: 0.000062 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1632193510;
select word from words order by rand() limit 3;

其中Rows_sent表示返回行数
Rows_examined表示扫描行数

缓存

buffer pool

free 链表

flush链表

LRU链表

多实例的buffer pool

将buffer pool拆分成多个实例，每个实例独立的去申请内存空间，独立的管理各种链表（实例不是越多越好，会有额外开销）
通过innodb_buffer_pool_instances可用设置实例数（当innodb_buffer_pool_size小于1G设置多实例是无效的，会重置为1）
每个实例大小计算公式为：innodb_buffer_pool_size/innodb_buffer_pool_instances

动态调整buffer pool大小（5.7.5 之前之后）

不再为某个实例一次性申请一大片连续空间，而是以chunk申请空间，一个chunk是一片连续内存空间，里面包含若干控制块和缓存页
chunk块大小由innodb_buffer_pool_chunk_size参数（该参数只能在mysql服务启动的时候）指定
注意：innodb_buffer_pool_size 必须是 innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的倍数（为了保证每个实例中的chunk块相同）

查看Buffer Pool的状态信息

语法

SHOW ENGINE INNODB STATUS

输出信息（只看BUFFER POOL段的关键信息）

Total memory allocated

代表 Buffer Pool 向操作系统申请的连续内存空间大小，包括全部控制块、缓存页、以及碎片的大小。

Dictionary memory allocated

为数据字典信息分配的内存空间大小，注意这个内存空间和 Buffer Pool没啥关系，不包括在 Total memory allocated 中。

Buffer pool size

代表该 Buffer Pool 可以容纳多少缓存页，注意，单位是页！

Free buffers

代表当前 Buffer Pool 还有多少空闲缓存页，也就是 free链表中还有多少个节点。

Database pages

代表 LRU 链表中的页的数量，包含 young 和 old 两个区域的节点数量。

Old database pages

代表 LRU 链表 old 区域的节点数量。

odified db pages

代表脏页数量，也就是 flush链表中节点的数量

Buffer pool hit rate

表示在过去某段时间，平均访问1000次页面，有多少次该页面已经被缓存到Buffer Pool 了。

young-making rate

表示在过去某段时间，平均访问1000次页面，有多少次访问使页面移动到 young 区域的头部了。

not (young-making rate)

表示在过去某段时间，平均访问1000次页面，有多少次访问没有使页面移动到 young 区域的头部。

LRU len

代表 LRU链表中节点的数量。

变量

用户变量@

用户变量与数据库连接有关，在这个连接中声明的变量，在连接断开的时候，就会消失。在此连接中声明的变量无法在另一连接中使用

设置方式

set @xxx = xxxVal

查询方式

select @xxx

系统变量@@

会话变量

会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
注意：会话连接后其它会话改了全局变量，当前会话的会话变量不会变更，后续新创建的会话连接才会读到新设置的全局变量）

查看方式：show session variables

设置方式：set session xxx=xxxVal（和set @@session.xxx=xxxVal等价）

全局变量

全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改

查看方式：show global variables

设置方式：set global xxx=xxxVal（和set @@global.xxx=xxxVal等价）