数据库总结
2023-03-25 22:02:15 0 举报
AI智能生成
数据库相关知识总结
作者其他创作
大纲/内容
每个字段都是原子,不可再分割
关系型数据库最基本要求
第一范式
满足第一范式的前提下,每一行的数据只能与其中一列相关,记录需要有唯一标记
第二范式
满足第二范式的前提下,每个字段都是直接与主键过关系,其他字段之间不能存在传递关系
第三范式
范式
数据存储
关系型数据基础知识
表结构文件
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表产生的中间结果集维表
<subqueryN>
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
系统变量和状态信息
相关操作命令
MYISAM引擎下面的索引缓存所占内存大小
key_buffer_size
mysql同时打开表的数量
table_cache
线程的缓冲区,注意不是线程的栈内存;
对GROUP BY或ORDER BY读取数据的时候会暂存读取的数据,
read_rnd_buffer_size
默认151
最大连接数
可以适当设置大一些
max_connections
线程池线程大小
thread_cache_size
默认为2MB
sort_buffer_size
为排序或者分组的线程设置的缓冲区大小,可以提高排序分组的效率
排序缓冲区
默认为8Mb
join_buffer_size
为每个链接做联合查询操作的时候缓冲区的大小,提高关联查询效率
关联查询缓冲区
默认64k
read_buffer_size
每个线程扫描每一个表的锁分配的缓冲区大小
表的缓冲区
查询相关
可以缓存表数据还有索引数据
innerDB引擎下表和索引的最大缓存
innodb_buffer_pool_size
innerDB日志刷盘策略
innodb_flush_log_at_trx_commit
innerDB的失误日志缓冲区
innodb_log_buffer_size
innerDB相关
优化核心参数
3.系统配置优化
单个磁盘每秒大约1000次搜索
将数据分布在多个磁盘上,提高单位时间内可以并行搜索磁盘次数
提高搜索效率
提高读写吞吐量
数据分散到多个磁盘
提高cpu频率
提高cpu缓存大小
4.硬件优化
概述:以上四个方向,效果最好的是SQL调优,以此往下效果越来越差,成本越来越高
调优方向
select* from test_news limit 10000,10;
查询每页是10条,查询低10000页的数据;这种查询完全就是没有用到索引;需要扫描到10000条数据,再开始查询
原始查询方式
2.拿到查询出来的id,获取到后面10个id,再用这10个id直接可以直接回表拿到所有的数据;
解析
优化方式1
相当于自关联的方式获取到相应的id;
再通过id回表获取所有的数据
优化方式2
limit优化
分组取前几条
having
sql优化实战
SQL自动优化
MySQL调优
mysql实战问题
从服务器本身也可以当做主服务器
主从复制中,从机又是另外一台的主机;
mysql链式复制
mysql异样工作场景
MySQL客户端程序和实用工具
oneProxy
canal
sharding-jdbc
mycat
Cobar
TDDL
atlas
分库分表
数据库中间件
数据库总结
0 条评论
回复 删除
下一页