首页  思维导图  详情

数据库总结

2023-03-25 22:02:15   0  举报





AI智能生成

为你推荐

查看更多



数据库相关知识总结

mysql

索引

作者其他创作

大纲/内容

每个字段都是原子，不可再分割

关系型数据库最基本要求

第一范式

满足第一范式的前提下，每一行的数据只能与其中一列相关，记录需要有唯一标记

第二范式

满足第二范式的前提下，每个字段都是直接与主键过关系，其他字段之间不能存在传递关系

第三范式

范式

数据存储

关系型数据基础知识

表结构文件

FRM文件

索引数据+表数据

IBD文件

文件结构

支持行锁，表锁

通过锁定索引记录来实现；锁定某一个索引记录

只用通过索引条件查询数据的时候，才会使用到

使用场景

作用

记录锁

通过索引记录来实现

对一定返回内数据进行加锁，即使这数据不存在也是会加锁

Select * from emp where empid > 100 for update;会对empid大于100的都加上间隙锁，即使记录不存在也会添加

间隙锁

相当于一个记录锁+间隙锁

避免幻读

Next-key锁

插入数据之前，由insert操作设置的一种间隙锁

插入意向锁

行锁

给数据行加行级锁之前需要取道该表的意向共享锁

意向共享锁

给数据行家排他锁之前需要取到该表的意向排他锁

意向排他锁

表锁

锁

记录后事物执行之后的状态，用来记录未写入 datafile 的已成功的事物更新的数据；

redolog日志类容

在MySQL宕机时候，还有数据没有写入磁盘，可以通过重做日志重做，从而达到数据的持久化

日志作用

事物开始的时候就会有日志生成，随着事物的进行的过程中，日志逐渐写入redolog中

日志生成

当某个事物对应的数据写入 datafile中后，redolog的使命已经完成了，重做日志的空间就会被重用

日志清理

redolog（innerDB引擎独有）

undolog生成时间

记录信息修改之前和修改之后的数据

undolog日志内容

根据undolog日志做数据库的逆向操作，原来的insert操作变成delete操作，delete变成insert，update变成逆向update

undolog日志回滚操作

undolog

日志

innerDB

存储的是表结构信息

frm文件

存储的是数据行记录信息

MYD文件

存储索引对应的数据信息

MYI文件

根据索引

myisam工作流程

支持表锁，不支持行锁

Myisam

myisam索引的B+树的叶子节点存储的是该条数据的地址值

innerDB索引的B+树的叶子节点存储的是该条数据的全部字段值（主键索引）

innerDB索引的非主键索引叶子节点存储的是id值（非主键索引）

索引数据结构

innerDB支持行锁，表锁；myisam支持表锁

锁区别

innerDB支持事物；myisam不支持事物（原因其实主要是myisam不支持行锁，间接导致不能支持事物）

事物

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引

MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

是否支持全文索引

innerDB支持外键；myisam不支持外键

是否支持外键

innerDB支持必须有主键；myisam可以没有主键

主键是否必须

innerDB数据存储frm、ibd文件；frm是表定义文件，ibd是数据文件

Myisam是frm、MYD、MYI；frm是表定义文件，myd是数据文件，myi是索引文件

innerDB数据索引数据和表数据是存储在一起；而myisam引擎索引数据和表数据是分开存储在两个文件

数据存储不同

innerDB不存储表的具体行数；myisam存储表的具体行数

是否存储具体行数

innerDB不支持表数据压缩；myisam支持表数据压缩，压缩后支持只读，修改操作需要加压才能操作；

是否支持表压缩

innerDB和Myisam区别

在内存中存在表数据，数据库重启之后，该引擎对应的数据就会消失

默认是使用hash索引

memory

存储引擎允许将一组使用MyISAM存储引擎的并且表结构相同的数据表合并为一个表，方便了数据的查询

Merge

其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件）

csv

为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

ARCHIVE

用于临时禁止对数据库的应用程序输入

BLACKHOLE

能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

FEDERATED

可为快速创建定制的插件式存储引擎提供帮助

EXAMPLE

可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

BDB

MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性

Cluster/NDB

数据库引擎

根据主键排序做的索引，特殊的唯一索引

主键索引

强制让表中某个字段值不能重复

唯一索引

根据某一列字段建立的索引

普通单列索引

单列索引

把建立索引的字段值全部兼容在一起组成的索引

组合索引

使用方式分类

索引的顺序和数据存储的顺序是保持一致

主键索引就是数据聚簇索引

聚簇索引（一级索引）

索引的存储顺序和数据的存储顺序不是一致

非主键索引全部都是属于聚簇索引

非聚簇索引（二级索引）

聚簇非聚簇

多条数据可以对应一条索引数据

非主键索引和有可能就是稀疏索引

稀疏索引

数据和索引是一一对应的

主键索引就是密集索引

密集索引

稀疏密集

索引分类

数据库引擎不同B+树存储的数据不完全相同

innerDB的的B+树叶子节点存储的是全部的数据或者是主键id；

myisam的B+树叶子节点存储的时候该条数据地址值（不管是主键索引还是其他索引）

B+树的数据存储结构

B+树的非叶子节点存储的是节点之间的前后指向关系，并不存储实际的数据值；B树的只要是节点不管是叶子节点还是非叶子节点都会存储数据。

B+树的节点存储兄弟节点的地址值，可以直接指向兄弟节点；而B树是不可以的。

在相同数据层级，B+树能够存储更多的数据；而B树存储的数据少很多；

B+树和B树区别

myisam和innerDB索引都是使用B+树

B+树

基于hash算法的散列索引

查询单条数据查询时间的复杂度为01;

范围查询速度比较低

特点

memory引擎默认使用该中索引

hash索引

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

使用

全文索引

创建和维护索引都需要耗费时间和磁盘空间

修改语句操作的时候，需要对索引进行重新构建

索引开销

查询的时候根据组合索引去查询，从索引中获取到的数据并未覆盖所需要查询的全部字段，所以就需要那到索引中的id再回到表中去查询需要的字段，这就是回表

回表查询

查询的时候，先根据组合索引中最左索引查询，拿到这些查询之后的数据的主键，再回表查询出整条数据，再从整条数据从按照其他条件做筛选；

非索引下推

如果查询条件中包含了组合索引，那么就会优先使用组合索引查询，查询满足组合索引中最左索引的值，从这些筛选一遍的数据中再过滤出其他符合条件的数据，不用再次回表查询；

索引下推

组合索引中，如果查询条件中并没有带上组合索引中最左边的索引，那么这个查询是不会先去查询组合索引

最左匹配原则

查询的是主键索引字段，也就是查询的数据本身就是索引，直接从索引中拿数据，不需要再去根据主键回表查询其他数据

覆盖索引

就是普通的select

一次性非锁定读

在select 语句后面中加 for update 或者是 lock in share mode

一次性锁定读

索引相关概念

查询需走索引

索引

数据库基础

表锁是由MySQL本身来实现

意向锁，锁是由innerDB自动添加，无需用户手动添加

概述

表锁具体实现

行锁（innerDB实现）

按照粒度划分

锁的分类

乐观锁，悲观锁都是在并发事物的一种控制方式，是一种思想，不是实实在在的锁

行锁，表锁也并非实实在在的锁，只是锁的粒度来划分的锁

锁分类说明

关系型数据库的一种加锁原则；把锁的操作划分为两个阶段，加锁阶段，解锁阶段

加锁阶段，解锁阶段互不相交，加锁阶段，只加锁，不解锁；解锁阶段，只解锁，不加锁

实际过程

二阶段锁

事物中发生多件事情，多件事情要么都成功，要么都失败

场景

原子性

一种状态到另外一种状态必须保证数据逻辑上的一致

A给B转1000块钱，A的账户扣了1000块，B的账户多了1000块

一致性

当前事物不受其他事物影响

隔离性

数据能够被持久化的存储在磁盘上

事物提交之后，该事物对应的修改数据都会被存储到磁盘上

持久性

基础概念

因为需要在失败的时候做数据回滚操作，所以原子性是通过undolog来实现的

redolog来实现

锁，mvcc来控制

通过原子性，持久性，隔离性来保证一致性

ACID实现

ACID

同时开启AB两个事物，A事物可以看到B事物没有做事物提交的数据

A事物读取B事物更新的数据，B又做了回滚，A事物读到的是脏数据；

脏读

同时开启AB两个事物，A事物只能等到B事物提交了才能看到B事物的数据

A事物读取B事物更新的数据，B又做了回滚，A事物在B事物提交前后看到的数据是不一样的；

不可重复度

同时开启AB两个事物，A事物提交了，B事物还是看不到A事物更新的数据，只有B事物提交了才能看到A更新的数据；

A事物对某个数据做了修改，提交了事物，B事物也对某个事物做了修改，但是没有提交事物，这时候B事物在事物提交之前看到的是A事物修改的数值，只有等到B事物也提交了事物，才能看到B事物真正修改的值

幻读

同开启AB事物，A事物没有提交，B事物无法对A事物已经操作的过的任何其他数据进行操作

序列化

事物隔离级别

多个事物同时执行

并行

事物必须是一个一个的去执行，不能并发的去执行

串行

事物调度

每次访问数据的时候，都认为是查询操作，其他事物也能访问当前事物的数据；造成的数据不一致性，采取的方式是修改数据之后，对修改的数据进行回滚操作

概念

1.读操作过多的情况下，能够显著提高效率

2.采取时候回滚操作来来解决事物冲突问题

通过在需要更新的表中设立相关的数据版本号来做操作

具体实现

可在分布式场景下控制多个服务对相同数据的修改是线性执行的；

MySQL乐观锁使用场景

乐观控制

每次访问数据的时候，都会对数据进行加锁操作

1.采取的是提前预防冲突

2.严格的控制其他事物对当前事物的访问，避免大量的数据回滚

3.其他事物，在某些情况下还是可以访问当前数据

通过数据库本身的锁机制来实现悲观控制

单体服务的时候就实现对并发事物的悲观控制

mysql悲观锁使用场景

悲观控制

对并发（交叉）事物的控制的方式；数据本身对并发事物的控制都是悲观控制

并发事物控制

mysql基础概念

1.主机每次完成实事物提交，完成数据更新之前，都会把变更的数据记录在binlog日志中

2.从机开启一个IO线程去从主机的binlog日志中读取数据，记录到自己的中继日志中；

3.从机开启线程，解析中继日志的事件，并执行，来做数据主从复制

主从复制过程

主从复制；主从复制就是为了读写分离

数据库基于时间还原数据

读写分离；读写分离的前提就是主从复制

缓存一致性；模拟成从机，拉取binlog日志异步更新到缓存中的数据；

数据多备份

binlog日志作用

以mysql事件形式记录的是实际执行的sql语句

statment模式

5.0之前

以MySQL事件形式记录字段前后变化的值

row模式

5.0之后

binlog日志格式

日志文件的大小，默认1G

max_binlog_size

日志保留事件，默认是0，永久保留

expire_logs_days

binlog日志配置

记录所有的更新操作

binlog日志（二进制日志）

记录建立的客户端连接和执行的语句。

只记录查询相关的日志

查询日志

记录更改数据的语句。不赞成使用该日志。

5.1版本之前的日志，5.1版本之后被binlog日志替代

更新日志

记录mysql启动关闭，以及运行过程中发生的错误信息

记录内容

错误日志默认是关闭

设置

错误日志

记录的是查询时间超过10秒（默认值）的sql语句

慢查询日志默认是关闭

慢查询日志

场景用在主从复制中从服务器读取到主服务器的二进制日志存储起来就是中继日志，日志格式和二进制日志格式相同，都可以使用相同的binlog解析程序解析

中继日志

1、解决读写冲突；

2、保证在事物并发条件下的读操作的效率，写操作的准确性。

MVCC作用

读取记录的可见版本（可能是历史版本，也可能是当前版本）不用加锁

不加锁的select操作

快照读

当前读就是读取的最新数据，而且还要保证其他事物不能修改当前数据

select * from table where ? for update;

select * from table where ? lock in share mode;

加锁的读操作

当前读

加锁读取记录的最新版本，保证其他事物不会并发修改这条记录；

特殊的读操作

insert into table values (…);

update table set ? where ?;

delete from table where ?;

因为修改操作之前，都是先需要查询到目标记录；

插入/更新/删除

当前写

MVCC控制分类

子主题

当前读，快照读，Mvcc关系

依靠的时候undo日志，read view ，三个隐式字段

MVCC实现原理

MVCC版本控制

被用户定义的 sql语句的集合

存储过程

某个时间所触发的操作；一种特殊的存储过程；

触发器

基于基表的一种逻辑表或者虚拟表

视图

其他组件

示意图

管理用户对数据库的连接

用户权限管理

连接器

根据语句对sql语句中能够做优化的SQL自动做优化

基于优化成本来做优化（主流的数据库都是这样）

CBO

基于规则，SQL语句经过分析器之后，会有多种执行方式，程序会判断选择一个效率最高的SQL

RBO

优化器分类

优化器

包括词法分析，语法分析

分析器

8.0之后就移除缓存模块

1.缓存命中率低

2.内存空间宝贵

移除原因

mysql缓存查询到的数据永远是最新的数据，如果中件表有发生变化，则该缓存相关的数据就会被清空

缓存命中率比较低，最理想的情况下命中率最高13%

查询必须是完全相同的（逐字节相同）才能被认为是相同的

同样的字符串由于其他原因也可能会被认为是不一样的

缓存工作

query_cache_type系统变量的值是ON或DEMAND，查询结果被缓存

前提是缓存是出于开启的状态

指定从缓存中查询

指定不从缓存中查询

缓存相关查询

表示当前是否有使用缓存

have_query_cache

如果设置为0，则认为是禁用缓存

查询缓存大小

query_cache_size

默认值1MB

被缓存的查询结果最大值

query_cache_limit

默认值是4KB

系统变量给查询缓存分配最小值

query_cache_min_res_unit

缓存相关参数

mysql缓存

缓存模块

SQL语句真正的执行者

执行器

五大组件

server层

主要负责数据的读取和存储，采用可替代式插件架构，支持各种数据库引擎。

存储引擎

MySQL主要分为server层和存储引擎层

MYSQL架构

mysql的主从复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)

并且主服务器开启了二进制日志

实现原理

复制过程中有三个线程在执行任务：一个线程在主服务器，负责将二进制日志的内容发送到从服务器（binlog dump线程）；

主从复制开始；从服务器上会创建一个io线程，连接主线程并记录二进制日志中的数据到中继日志；中间需要识别binlog dump线程以获取二进制日志

从服务器会开启一个sql线程（第三个线程），从中继日志中读取日志，并执行日志，以同步数据；

实现过程

1、主服务器开启了二进制日志

1、二进制日志格式不一样

2、字符集，函数，时区处理不一样

由于历史原因：

2、主从服务器的mysql版本之间是可以相互兼容

3、主服务器上设置了相应的从服务器

主从复制条件

mysql主从复制

left join 做关联，以左表为主表，去右表当中匹配，如果左表中的数据未能匹配到右表中的数据，结果集中依旧会将左表的数据展示出来，未能匹配到的字段全部为空

right join 做关联，以右表为主表，去左表当中匹配，如果右表中的数据未能匹配到左表中的数据，结果集中依旧会将右表的数据展示出来，未能匹配到的字段全部为空

inner join 做关联，最明显就是左右两个表中只有关联上的数据才会展示出来，没有关联上的数据不会展示出来；

原始数据

.inner join

.left join

.right join

full join

SQL语句

性能模式

系统模式

mysql模式

压缩MyISAM表以产生更小的只读表

myisampack

交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具

mysql

检查访问主机名、用户名和数据库组合的权限的脚本

mysqlaccess

执行管理操作的客户程序，例如创建或删除数据库，重载授权表，将表刷新到硬盘上，以及重新打开日志文件

mysqladmin

从二进制日志读取语句的工具。在二进制日志文件中包含的执行过的语句的日志可用来帮助从崩溃中恢复

mysqlbinlog

检查、修复、分析以及优化表的表维护客户程序

mysqlcheck

将MySQL数据库转储到一个文件（例如SQL语句或tab分隔符文本文件）的客户程序

mysqldump

当服务器在运行时，快速备份MyISAM或ISAM表的工具

mysqlhotcopy

使用LOAD DATA INFILE将文本文件导入相关表的客户程序

mysql import

显示数据库、表、列以及索引相关信息的客户程序

mysqlshow

显示系统或MySQL错误代码含义的工具

perror

更改文件中或标准输入中的字符串的实用工具

replace

mysql客户端脚本和使用工具

Mysql事件

ReadView

MYSQL其他知识点

1、对原始表加写锁

2、按照原始表和执行语句的定义，重新定义一个空的临时表

3、对临时表进行添加索引（如果有

4、再将原始表中的数据逐条Copy到临时表中

5、当原始表中的所有记录都被Copy临时表后，将原始表进行删除。再将临时表命名为原始表表名

5.6之前版本

2、按照原始表和执行语句的定义，重新定义一个空的临时表。并申请rowlog的空间

3、拷贝原表数据到临时表，此时的表数据修改操作（增删改）都会存放在rowlog中。此时该表客户端可以进行操作的

4、原始表数据全部拷贝完成后，会将rowlog中的改动全部同步到临时表，这个过程客户端是不能操作的

5、当原始表中的所有记录都被Copy临时表，并且Copy期间客户端的所有增删改操作都同步到临时表。再将临时表命名为原始表表名

5.6版本以之后版本

mysql添加表字段会不会锁表

肯定是会的

前期设计的时候想好设计表的索引

解决方式

myslq添加索引会不会锁表

1、从机读取主机binlog日志的线程只有一个，如果主机有大量更新操作。从机是无法快速将这些binlog读取到从机

2、从机如果有执行慢的sql，也会导致无法快速处理读取到的binlog日志。

以上两点导致主从不一致的原因

1、延迟原因

1、将从机的binlog日志取消，提高从机sql执行效率

2、将主从服务器放在同一个网络下，减少网络延迟

3、具体的硬件优化

4、调参

2、解决方案

mysql主从同步延迟解决方案

id相同，从上到下顺序执行

id不同，id值越大执行优先级越高，越先被执行

id（sql执行顺序）

简单的单表select查询

SIMPLE

子主题 1

PRIMARY

表示select或者是where中含有子查询

SUBQUERY

表示where后面的in条件有子查询

MATERIALIZED

SUBQUERY/MATERIALIZED

被关联查询的语句；也就是关联查询的union中的第二个语句或者是union后面的select语句

UNION

表示关联查询的结果

UNION RESULT

select_type查询类型

直接显示表明

有N表产生的中间结果集维表

table（查询涉及到的表）

查询的是系统表

system

单表查询直接命中主键查询

explain select * from user where id=1;

demo

const

关联查询用到主键索引做为关联字段

eq_ref

关联查询用到了非主键索引作为关联字段

ref

在索引上的范围查询

range

查询是在所引述中扫描

explain count (*) from user;

index

全表扫描

ALL

type （重点，查询性能指标）

查询的结果只的好坏顺序system > const > eq_ref > ref > range > index > ALL

查询过程中有可能用到的索引

possible_key

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

key

展示索引字段的实际长度

key_len

显示该表的索引字段关联了哪个表的哪个字段

估算查询所需要的记录需要读取的行数

rows

返回的结果集行数占读取行数的百分比，越大越好

filtered

展示其他重要信息

extra

执行计划详解

1.在经常当做查询条件的字段上设置索引

查询尽量覆盖到索引

2.前模糊查询

3.查询语句中在索引字段上做函数操作

4.关联字段虽然都是索引字段，如果数据类型不一致，走不了索引

5.where后面不带有效查询条件

6.建立联合索引，查询的时候不满足最左原则；

7.查询中对索引字段做运算操作

未覆盖到索引情况

在字符串类型的字段上建立索引，需要指定索引的长度，一般10个就够了；

索引长度限制

1、必须要有主键索引

表的关联字段建立索引的时候确保两个表的数据类型是一样的

2、在经常做为查询条件，排序，分组，关联的字段上建立索引；

3、索引建立的字段应该是区分度非常高的字段；

索引建立字段选取

1、索引的数量不是越多越好，多了会增加索引构建维护成本；

2、不再使用或者使用非常少的索引需要删除索引

3、新建单列索引的时候，考虑和其他字段一起建立联合索引，减少索引个数

索引数量

索引建立原则

查询需要覆盖到索引

1.sql调优

1、表的字段类型，字段长度合理

2、创建高性能的索引

2.调整库，表结构

mysqld --verbose --help命令生成所有mysqld选项和可配置变量的列表

查询服务器默认缓存区大小

查看当前运行的mysql服务器实际运行的值

SHOW VARIABLES;

运行服务器的统计和状态指标

SHOW STATUS；

mysqladmin variables

mysqladmin extended-status

系统变量和状态信息