首页  思维导图  详情



 



Mysql知识点

2021-04-26 10:20:25   11  举报





mysql

模板推荐

作者其他创作

大纲/内容

逻辑架构

整体图

第一层

处理连接

授权认证

第二层

查询缓存

解析器

优化器

第三层

存储引擎

存储数据

提取数据

开启事务

事务和锁

什么是事务

为了保证数据的一致性

事务的四大特性

原子

一致

隔离

持久

事务的隔离级别

隔离级别下可能的问题

脏读

一个事务读取了另一个事务未提交的数据。

不可重复读

一个事务读取同一行数据，多次读取结果不同。

幻读

一个事务读取到了别的事务插入/删除的数据。

Read uncommitted(未提交读)

Read Committed(已提交读，简称RC)

解决脏读

Repeatable Reads(可重复读，简称RR)

解决不可重复读

Serializable(串行化)

解决幻读

InnoDB默认的隔离级别是RR，但是通过MVCC和间隙锁来一定程度上的解决了幻读的问题。

锁机制（innodb）

表锁

行锁 （也称为临键锁） Next-Key Locks

MVCC

通过给每行记录后面保存两个隐藏的列来实现的。一个是保存行的创建时间，另一个保存了行的过期时间（或删除时间）。

每次开启一个事务，系统版本号都会递增。事务开始时，系统版本号会作为事务的版本号，用来和查询到的行的版本号进行比较。

说清楚增删改查的过程

具体可参考后面的undo log

索引

索引的分类

主键索引

唯一索引

普通索引

组合索引

全文索引

索引的原理

为什么不使用二叉树？

数据量较大时，深度过深

B Tree的性质？

平衡搜索多叉树

每个叶子结点的高度一样

每个非叶子结点由n-1个key和n个指针组成，key和指针相互隔离，结点两端一定是key

叶子结点指针为null；

非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的其它列的数据

对索引列是顺序存储的，所以很适合查找范围数据和ORDER BY操作。

B+ Tree的性质？

与B Tree的不同

非叶子结点不存储数据，只存储键值；

叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址

每个非叶子节点由n个键值key和n个指针point组成

与B Tree相比的优点

更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构

数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找。

B+Tree的非叶节点中不存储data，就可以存储更多的key。，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少

带顺序索引的B+Tree

在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

目的是为了提高区间访问的性能

TODO 这三种树的增删改查？

太深了，可以记一个页分裂

主键索引并不一定是自增的（比如uuid）

然后随着你将数据写入。就导致后一个数据页中的所有行并不一定比前一个数据页中的行的id大。

页分裂的目的就是保证：后一个数据页中的所有行主键值比前一个数据页中主键值大。

聚簇索引和非聚簇索引的区别

主键索引和非主键索引

聚簇

聚簇索引的数据和主键索引存储在一起。

聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。

缺点：聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂（BTree插入时的一个操作），严重影响性能。

覆盖索引

对于非主键索引，如果要查的列全部在索引上，就可以避免回表，提升性能

索引失效的场景

组合索引中的NULL

一个查询只使用一次

比如如果在WHERE中使用了，那么在ORDER BY中就不会再使用了。

LIKE操作中，'%xxx%'不会使用索引，也就是索引会失效，但是'xxx%'可以使用索引。

OR连接多个条件

使用表达式或者函数

可以创建基于函数的索引来解决

结果集太大

MySQL估计使用全表扫描要比使用索引快,则不使用索引。MySQL会使用一个叫基于数据访问路径的CBO成本计算模型来估计它。

多个范围查询

对于范围条件查询，MySQL无法再使用范围列后面的其它索引列了

如果范围不大，可以使用IN来代替

其它问题

InnoDB 物理文件结构

.frm 文件

与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等

.ibd 文件或 .ibdata 文件

这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

varchar的存储结构？

varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。

count(*) 和 count(1)和count(列名)区别

执行效果

count(*)和count(1)不会忽略NULL，count(列名）会

执行效率

跟存储引擎有关，Myisam存了总行数

各种join的区别？

直接上图

各种日志

错误日志

查询日志

慢查询日志

二进制日志 binlog

记录对数据库执行更改的所有操作

逻辑日志，可以简单理解为sql语句

由server层记录

任何mysql引擎都会记录binlog

通过追加的方式写入，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

刷盘时机（innodb）

事务提交才会记录binlog，此时在内存中

mysql通过sync_binlog参数控制binlog的刷盘时机

0：不去强制要求，由系统自行判断何时写入磁盘；

1：每次commit的时候都要将binlog写入磁盘；（mysql 5.7后的默认）

N：每N个事务，才会将binlog写入磁盘。

日志格式

STATMENT （5.7.7前默认）

基于SQL语句

优点：减少了binlog日志量，节约IO。

缺点：某些情况会导致数据不一致：比如sysdate()等

ROW（5.7.7后默认）

基于行

优点：数据正确

缺点：产生大量日志，尤其是alter table的时候会让日志暴涨

MIXED

基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

为什么不是默认?

既然是自动，就不能保证完全符合每个业务场景，除非Server层面能做到绝对安全。

使用场景

主从复制

数据恢复

数据同步

搜索器

数仓

跨团队DB（不推荐）

中继日志 relay log

也是一种二进制日志，用来给slave库恢复

见下方的“主从复制原理”

事务日志

redo

目的

具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。因为事务要满足持久性

概念

内存中的日志缓冲(redo log buffer)

磁盘上的日志文件(redo log file)

这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术

用户空间无法直接写到磁盘。所以实际先写到内核空间的缓冲器OS Buffer，再通过系统调用fsync()将其刷盘

时机 innodb_flush_log_at_trx_commit参数

大约每秒写一次（可能丢失1秒数据）

实时写，实时刷（性能差）

实时写，延迟刷

每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到redo log file。

四个日志，循环写。不会一直创建新的日志

启动innodb的时候，先会检查会恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。

undo

保证事务的原子性

是MVCC实现的关键

事务id

回滚指针

undo日志地址

对该条记录的修改日志串联起来就形成了一个版本链，版本链的头节点就是当前记录最新的值

性能瓶颈定位

show status ——显示状态信息（扩展show status like ‘XXX’）

show variables ——显示系统变量（扩展show variables like ‘XXX’）

show innodb status ——显示InnoDB存储引擎的状态

show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

mysqladmin variables -u username -p password——显示系统变量

mysqladmin extended-status -u username -p password——显示状态信息

explain字段解释

type

从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引，如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中，不出现在possible_keys

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref

哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数

Extra

using filesort 

使用了文件排序，无法利用索引完成排序的时候会出现

Using temporary

使用了临时表保存中间结果

using index

表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作

using where

使用了where过滤

using join buffer

使用了连接缓存

impossible where

where子句的值总是false，不能用来获取任何元祖

select tables optimized away

没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

distinct

优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

分库分表后的问题

根据某字段返向查找

新建mapping表

上搜索器

查询非id字段

上搜索器

排序等

业务允许的话，可以另建单表，定时清理数据

扩容/缩容

迁移

需要停止服务

免迁移（双倍机器）

先把新增的机器作为slave，同步数据

同步完毕后，设置新的分片规则。

解除数据库的主从同步关系

<span style="color: rgba(0, 0, 0, 0.75); font-family: -apple-system, "SF UI Text", Arial, "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei", "WenQuanYi Micro Hei", sans-serif; font-size: 16px; font-variant-ligatures: common-ligatures;">四个节点的数据都已完整，只是有冗余。找时间清除冗余就行

分布式事务

用中间件

主从复制原理

原理图

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；

salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;

lave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

三范式

1 原子性

2 唯一性

3 冗余性

大量数据如何删除？