首页  思维导图  详情

Mysql数据库知识图谱

2022-06-23 16:59:22   4  举报





AI智能生成

为你推荐

查看更多



mysql的知识图谱整理，整理不易，点赞收藏

2022学霸笔记

mysql

数据库

java

作者其他创作

大纲/内容

子节点存储了整行数据，非主键索引存储的是主键

数据列不允许重复，不允许为NULL，一个表只能有一个主键

主键索引

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引

唯一索引

基本的索引类型，没有唯一性的限制，允许为NULL值

普通索引

是目前搜索引擎使用的一种关键技术

全文索引

索引类型

Memory引擎支持

Hash

直接把记录插入叶子节点中

叶节点未满，子节点未满

发生叶节点拆分

将中间节点放在子节点中

小于中间节点的记录放在左边

大于等于中间节点记录放在右边

叶节点满了，子节点未满

拆分叶节点

拆分子节点

中间节点放在上一层叶节点

叶节点满了，子节点满了

B+树的插入

填充因子=50%

直接将记录从叶子节点删除，如果该节点是子节点，则用右节点替换

叶节点和中间节点都大于填充因子

合并叶节点和兄弟节点，同时更新子节点

叶节点小于填充因子

合并叶子节点和兄弟节点

更新子节点

合并子节点和兄弟节点

叶节点和中间节点都小于填充因子

B+树的删除

操作

每个中间节点不保存数据，只用来索引，具体数据保存在子节点中

叶子节点本身存在顺序，每个page之间用双向链表存储

所有中间节点元素都存在子节点中

通常有两个指针：1个指向根节点，1个指向最小叶子节点，针对这两种查找运算是随机的

特点

只有叶子结点会带有指向具体记录的指针

所有的叶子结点通过指针连接在一起

一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定

非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率

叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易

B+树

所有节点都会带有指向具体记录的指针

不同的叶子之间没有连在一起

可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定

B-树

区别

B/B+树

数据结构

索引的原理很简单，就是把无序的数据变成有序的

创建了索引的列的内容进行排序

对排序结果生成倒排表

在倒排表内容上拼上数据地址链

在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

数据按照页来分开的，一页大约是16K

索引原理

出现在where子句中的列，或者连接子句中指定的列

散列效果较好的列

短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间

最左前缀匹配原则

更新频繁字段不适合创建索引

定义有外键的数据列一定要建立索引

索引设计

模–代表模糊查询

型–代表数据类型

数–代表函数

空–代表NULL

运–代表数值运算

最–代表最左原则

快–代表全表扫描最快

索引失效（模型数空运最快）

提高数据检索的效率，降低数据库的IO成本

创建唯一索引，可以保证数据库表中每一行数据的唯一性

有依赖关系的子表和父表联合查询时，可以提高查询速度

使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗

优点

创建索引和维护索引要耗费时间

索引需要占磁盘空间

降低更新表的速度

缺点

索引优缺点

从非主键索引树搜索回到主键索引树搜索的过程称为：回表

回表

从非主键索引中就能查到的记录

覆盖索引

对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数

索引下推

索引

基于磁盘的数据结构

记录的是数据库中每个页的修改

用来恢复提交后的数据页，并且只能恢复至最后一次提交的位置

使用WAL日志追加技术，先写日志再写磁盘

特征

客户端尝试修改数据时，Innodb会把记录下写在redo log中

再修改缓存池中数据，当事务提交时，调用fsync把redo log刷入磁盘

缓存池的数据何时刷入磁盘由后台线程异步处理

当Mysql宕机后，只要重试解析redo log的更改记录进行重放、刷盘即可

重做日志过程

固定大小，循环写入

redo log写满后从头开始写，形成一个环状

write pos表示redo log当前记录的日志序列号(LSN)，写入还未刷盘的记录，循环往后递增

write pos和check point之间的绿色部分表示空余部分，用来记录写的日志

check point到write pos之间是还未来得及刷盘的部分

擦除：write pos追上check point时，就需要推动check point前移，即进行刷盘空出位置进行记录新的日志

重放(刷盘)过程

通过innodb_flush_log_at_trx_commit进行设置写入策略

0：每秒提交 redo buffer -> os cache ->disk 可能丢失一秒内的事务数据

1(默认值):每次事务提交执行 redo buffer ->os cache ->disk 性能较差

写入机制

重写日志：redolog

用来回滚到某一个版本，是一种逻辑日志

undo log记录的是修改之前的数据

实现mvcc机制:undo log中保存了未提交之前版本数据，作为旧版本数据的快照以便其他事务进行读取

实现事务原子性，利用Undo log进行回滚

作用

回滚日志：undolog

记录MySQL中增删改时的记录日志

show VARIABLES like '%log_bin%'查看是否开启binlog

二进制形式存储在磁盘中的逻辑日志

bin log采用追加的方式写入

默认情况下是关闭的

bin log和redo log 相辅相成，共同保证事务持久性

进行主从复制

基于时间点恢复数据

通过订阅bin log可以做很多事情，比如同步上游数据、和redis配合实现延时双删等

二进制日志：binlog

用于主从复制

Slave服务的I/O线程从主数据库Master服务的二进制日志中读取数据库的更改记录并写入

sync_relay_log设置如何同步中继日志到中继日志文件

0时，则MySQL服务不会对中继日志文件进行同步操作，依赖于操作系统来定期进行同步

sync_relay_log = N(N>0)，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，每N个sync_relay_log事件后对中继日志文件执行一次同步(调用fdatasync())

同步日志

relay 中继日志

记录MySQL在启动、关闭或者运行过程中的错误信息

show variables like \"%log_error%\" 查看错误日志的地址

默认开启

错误日志：error log

记录执行时间超过指定阈值的SQL语句

show variables like \"%slow_query%\"：查看慢查询日志是否开启以及日志的位置

默认情况下时关闭的，默认时间时10s

慢查询日志：slow query log

客户端连接信息以及执行的SQL语句信息

普通查询日志会记录增删改查的信息，因此一般是关闭的

一般查询日志：general log

日志

屏蔽一切可能违反数据完整性的操作

查询完数据的时候就把事务锁起来，直到提交事务

使用数据库中的锁机制

多写的情况，冲突概率大

适用场景

悲观锁

只在提交操作时检查是否违反数据完整性

乐观锁一般会使用版本号机制或CAS算法实现

修改少读多场景，冲突发生概率小，节省锁的开销，增加系统吞吐量

乐观锁

锁定粒度最大的一种锁，表示对当前操作的整张表加锁

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低

表共享读锁（共享锁）IS：打算在某行上设置共享锁

表独占写锁（排他锁）IX：打算在某行上设置排他锁

Insert 意向锁：Insert操作设置的间隙锁

意向锁：表明事务稍后要进行那种类型的锁定

为一个AUTO_INCREMENT列生成自增值前，必须先为该表加 AUTO_INC 表锁

为了提高并发插入的性能，自增锁不遵循二阶段锁协议，加锁释放锁不跟事务而跟语句走，insert开始时获取，结束时释放

自增值只要分配了就会+1，不管事务是否提交了都不会撤销，所以可能出现空洞

LOCK_AUTO_INC：自增锁

表级锁

最细的一种锁，表示只针对当前操作的行进行加锁

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高

允许事务对一条行数据进行读取

共享锁

允许事务对一条行数据进行删除或更新

排它锁

记录锁Record：始终锁定索引记录

间隙锁Gap：锁定一个范围

临键锁Next-key：记录锁+间隙锁的组合，可锁定表中不存在的记录

谓词锁Predicat：空间索引

行级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁

一次锁定相邻的一组记录

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

页级锁

指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象

系统资源不足

进程运行推进的顺序不合适

资源分配不当

产生死锁的原因

互斥条件：一个资源每次只能被一个进程使用

请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放

不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺

循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系

四个必要条件

破坏互斥条件：这个条件我们没有办法破坏，因为我们⽤锁本来就是想让他们互斥的（临界资源需要互斥访问）

破坏请求与保持条件：⼀次性申请所有的资源

破坏不剥夺条件：占⽤部分资源的线程进⼀步申请其他资源时，如果申请不到，可以主动释放它占有的资源

破坏循环等待条件：靠按序申请资源来预防。按某⼀顺序申请资源，释放资源则反序释放

如何避免

一次性锁定所有需要的资源

按照一致的顺序进行加锁

缩小锁冲突的范围

MySQL避免死锁的方法

死锁

show engine innodb status:查看当前库中有哪些事务或者锁

锁机制

第一范式：不可拆分列

第二范式，每个表只有一个主键

第三范式：消灭传递依赖，每列和主键有关

BC范式：3NF基础上消除主键对于码的部分与传递函数依赖

适当的增加冗余字段，提高查询效率

范式

mysql、oracle、pgSQl、sqlService

关系型

redis、mongoDB、VoltDB、Hbase

非关系型

常见数据库

DQL：查询sql

DML：新增、修改删除的sql

DDL：对数据库表进行操作

sql语言

show full processlist 查看所有进程

show OPEN TABLES where In_use > 0 查看是否锁表

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;查看正在锁的事务

select * from performance.data_lock 查询所有的锁情况

查询相关命令

数据库基础

静态（固定长度）表

动态表

压缩表

存储格式

不支持事务，也不支持外键约束，只支持全文索引

访问速度快，对事务完整性没有要求

MyISAM 适合查询、插入为主的应用场景

表级锁定形式，数据在更新时锁定整个表

不需要事务的支持

单方面读取或写入数据比较多的业务

使用读写并发访问相对较低的业务

数据修改相对较少的业务

对数据业务一致性要求不是非常高的业务

服务器硬件资源相对比较差

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

MyISAM

支持事务，支持 4 个事务隔离级别

读写阻塞与事务隔离级别相关

能非常高效的缓存索引和数据

表与主键以簇的方式存储

支持分区、表空间，类似 oracle 数据库

支持外键约束，5.5 前不支持全文索引，5.5 后支持全文索引

行级锁定，但是全表扫描仍然会是表级锁定

业务需要事务的支持

行级锁定对高并发有很好的适应能力，但需要确保查询时通过索引来完成

业务数据更新较为频繁的场景

业务数据一致性要求较高

InnoDB 较好的缓存能力来提高内存利用率，减少磁盘 IO 的压

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

InnoDB

show engines;查看当前数据库引擎

alter table 修改

修改 /etc/my.cnf 配置文件

创建表时指定存储引擎

修改存储引擎

存储引擎

SQL执行的顺序的标识

查询中每个select子句的类型

select_type

这一行的数据是关于哪张表的

table

type

MySQL能使用哪个索引在表中找到记录

possible_keys

key列显示MySQL实际决定使用的索引

Key：key

索引中使用的字节数

key_len

上述表的连接匹配条件

ref

扫描行数

rows

解决查询的详细信息

Extra

执行计划explain

设计上：字段类型，存储引擎，范式

功能上：索引，缓存，分库分表

架构上：集群，主从复制，负载均衡，读写分离

优化方向

set global.max_connections=1000修改全局最大连接数

max_connections:最大连接数

wait_timeout：等待超时时间

interative_timeout：连接状态的超时时间

连接请求

key_buffer_size:缓冲的key的大小

query_cache_size:查询结果缓存的大小（8.0去除了）

max_connect_error:连接允许的最大错误数量

sort_buffer_size:排序的缓冲区大小，默认1M

缓冲区变量

innondb_buffer_pool_size:内存的缓存大小，默认128M

innodb变量

参数配置优化

where条件注意数据类型，避免类型转换导致不走索引

隐式转换

涉及范围的查询的索引不一定生效：优化器优化

联合索引建立需要根据实际查询情况，避免最左前缀原则导致联合索引不生效（可能生效最前的索引）

避免冗余索引

合适的索引

sql常见问题

大批量的写入，prepared statement减少sql解析

multiple values /add batch 减少交互

load data 直接导入

索引和约束问题

写入优化

注意gap lock问题

导致锁范围扩大

数据的范围更新

数据更新

semi-join Materialization 是用于semi-join的一种特殊的子查询物化技术

Materialization/lookup

Materialization/scan

子查询优化

like的问题，后%可走索引

全文检索，solr/es 实现组合查询

模糊查询

驱动表的选择问题

避免笛卡尔积

命中索引

连接查询

避免null、not、not in 函数等

减少使用or ，使用union

force index 强行走索引

查询where条件

查询优化

mysql优化

要么都发生，要么都不发生

原子性（Atomicity）

事务前后数据库的完整性约束没有被破坏

一致性（Consistency）

每个事务都有各自的完整数据空间

隔离性（Isolation）

事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

持久性（Durability）

事务特点

一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的

脏读

一个事务内两个相同的查询却返回了不同数据

不可重复读

A事务修改全表，B事务新增数据，A事务会出现幻读

幻读

A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。

丢失更新

事务问题

很少使用、不能保证一致性

read uncommitted : 读取尚未提交的数据：不解决脏读

每次查询设置和读取自己的新快照

只支持基于行的binlog

update 优化：半一致读

read committed：读取已经提交的数据：可以解决脏读

使用事务第一次读取时创建快照

使用了MVVC技术

repeatable read：重读读取：可以解决脏读和不可重复读 —mysql默认的

资源损耗最大

serializable：串行化：可以解决脏读不可重复读和虚读—相当于锁表

事务隔离级别

事务

原来存储在一个库表中的数据拆分到不同的库/表中。

业务的不断发展和数据的不断递增，未来场景下可能出现瓶颈

分布式部署、单机服务性能瓶颈、数据处理能力瓶颈、数据存储瓶颈等

分库分表原因

按照功能模块、关系密切程度将不同的业务表划分出来，部署到不同的库上

垂直拆分——主要是字段的拆分

按照某种规则把一张表的数据拆分成多个结构相同的表

切分简单，根据uid，按照范围，很快能够定位到数据在哪个库上

扩容简单，如果容量不够，只要增加user-db2即可

uid必须要满足递增的特性

数据量不均，新增的user-db2，在初期的数据会比较少

请求量不均，一般来说，新注册的用户活跃度会比较高，故user-db1往往会比user-db0负载要高，导致服务器利用率不平衡

范围法

切分策略简单，根据uid取模，根据取模结果很快能够定位到数据在哪个库上

数据量均衡，只要uid是均匀的，数据在各个库上的分布一定是均衡的

请求量均衡，只要uid是均匀的，负载在各个库上的分布一定是均衡的

扩容麻烦，如果容量不够，要增加一个库，重新hash可能会导致数据迁移，如何平滑的进行数据迁移，是一个需要解决的问题

哈希法

根据不同的业务特征进行拆分，按照不同的租户直接分离数据，数据独立

单个租户数据量快速增大后依旧是瓶颈

城市/租户-有明显业务特征的分表

拆分规则

水平拆分——表结构不变，数据分表;

实施策略

跨库join的问题：全局表、字段冗余、数据同步、系统层组装

跨库事务（分布式事务）的问题：分布式事务

垂直分库

分布式全局唯一ID：id生成规则不再依赖于数据库

分片字段该如何选择：片键，表中最频繁被使用，或者最重要的字段来作为分片字段

数据迁移，容量规划，扩容等问题：历史数据、扩容

跨分片的排序分页：业务处理

跨分片的函数处理：业务处理

跨分片join ：全局表、ER分片、内存计算

水平分库

拆分带来的问题

开源的分布式数据库中间件解决方案组成的生态圈

提供标准化的数据分片、读写分离、柔性事务和数据治理功能

定位为轻量级Java框架，在Java的JDBC层提供的额外服务

适用于任何基于JDBC的ORM框架

支持任何第三方的数据库连接池

支持任意实现JDBC规范的数据库

性能很好、支持跨数据库jdbc

增加了开发难度、不支持跨语言（java）

Sharding-JDBC

透明化的数据库代理端

向应用程序完全透明，可直接当做MySQL使用

适用于任何兼容MySQL协议的的客户端

sharding-ui，直接的管理sharding-proxy，在上面动态修改配置等信息

性能问题：作为数据库的代理层，使用java与阻塞式的驱动

Sharding-Proxy

Sharding-sphere

开源的，面向企业应用开发的大数据库集群

拦截了SQL语句，做分片分析、路由分析、读写分离分析、缓存分析等执行真实数据库，并将返回的结果做适当的处理，最终再返回给用户

建议线上使用双机热备环境

单纯的读写分离，此时配置最为简单，支持读写分离，主从切换

分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片

多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化

报表系统，借助于Mycat的分表能力，处理大规模报表的统计

替代Hbase，分析大数据

海量数据实时查询的一种简单有效方案

应用场景

Mycat的默认端口是:8066

mycat

分库组件

主库执行DDL和DML操作，按照修改顺序以此写入bin log

从库的IO线程连接上主库并请求读取指定位置position的日志内容

主库收到请求后，将制定位置position之后的内容日志、主库bin log文件名称以及在日志中的位置推送给从库

从库IO线程收到数据后，将日志内容以此写入relay log文件最末端，并将bin log文件名和位置position记录到master-info文件中，以遍下次使用。

从库的sql线程检测到relay log中内容更新后，读取日志并解析成可执行的sql语句进行主从同步

主从过程

读写分离

数据备份

高用性

为什么要主从同步

主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库

从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库

半同步复制：解决主库数据丢失问题

从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行

并行复制：解决主从同步延时问题

节点执行完客户端提交的事务后立即提交事务并返回给客户端

不关心 log dump 线程是否成功地将将此次事务写进 binglog 并且发送给从库

异步模式:默认模式

同步机制

show slave status/show master status 查看主从同步状态

start/stop slave开启或者关闭同步