数据库基础知识

6 篇文章 0 订阅
1 篇文章 0 订阅

系列文章目录

Java后端开发必掌握基础
1.Java基础
2.数据库—当前
3. .Web后端
4. Spring核心之IOC和AOP
5. 数据结构与算法基础


以下为数据库相关知识


前言

数据库DataBase基础知识


一、数据库

1.数据库是什么?相关基本概念?

数据库技术: 是信息系统的一个核心技术. 是一种计算机辅助管理数据的方法, 它研究如何组织和存储数据, 如何高效地获取和处理数据。
DBMS:数据库管理系统,又称为数据库软件或数据库产品
SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库具有的,而是几乎所有的主流数据库通用语言。

①数据库(DB–>database)—保存有组织的数据的容器(通常是一个文件或一系列的文件)。
②数据表(table)—某种特定类型数据的结构化清单。
③模式(schema)—关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储,包含存储什么样的数据,数据如何分解,各部分信息如何命名等信息。数据库和表都有模式。
④列(column)—表中的一个字段。所有表都是由一个或多个列组成的。⑤行(row)— 表中的一个记录。
⑥主键(primary key)– 一列或一组列,其值能够唯一标识表中每一行

2.SQL语句相关?

数据定义(DDL) 数据操纵(DML) 数据控制(DCL) 数据查询(DQL–>select)
数据定义:create Table, Alter Table, Drop Table, Craete/Drop Index 等
数据操纵:insert增,delete删,update改,select查
数据控制:grant, revoke

①增添语句:INSERT INTO face_recognition.user_list values(face_id,city)
②删除语句:delete from face_recognition.user_list where face_id=459123
清空表数据:truncate table face_recognition.user_list
更改语句:UPDATE face_recognition.user_list SET username=?,description=? WHERE face_id=123456

查询语句:select * from face_recognition.user_list where face_id=123456 加*表示所有字段
i.查询多字段 select 字段名, 字段名 from 表名;
ii.去重查询:select distinct 字段名 from 表名;①多字段去重不行 ②优化:在所有列上转换为GROUP BY,并与ORDER BY子句结合使用。

iii.+加法运算: 直接运算 select 数值+数值 结构都为null:select null+值
字符型转换为数值,若转换成功,则继续运算。否则转换成0,再做运算:select 字符+数值
iv.拼接字符 concat: select concat(字符1,字符2,字符3,……);
v.关于NULL:表示未知(unknown),不能把任何值与一个NULL值进行比较
select ifnull(xxx, 0) from 表名;字段或表达式是否为null,返回指定的值,否则返回原本的值
select isnull from 表名;字段或表达式是否为null,如果是返回1,否则返回0
SQL语法结构图
在这里插入图片描述

char和varchar两个字段: 存储和检索,char列长度固定为创建表时声明的长度,范围是1~255(1<<8 - 1);
①当char值被存储时,它们被用空格填充到特定长度
②检索 char值时需删除尾随空格。

查询语句相关:select查询列表 from 表名;
在这里插入图片描述

.MySQL其他

①如何查看某表的所有索引show index from <表名>

②fetch_array:将结果行作为关联数组或来自数据库的常规数组返回。
fetch_object :将结果行作为对象返回


ACS升序(从小到大)—正序排列
DESC降序(从大到小)—倒序排列
无用

.数据库三范式

1NF是对属性的原子性约束,要求属性具有原子性,不可再分解

2NF是对记录的唯一性约束,要求记录有唯一标识,即实体的唯一性;

3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。

数据库范式与反范式的优劣

范式:
优:减少数据冗余,使得更新快,体积小。
劣:对于查询需要多个表进行相关联,提高了写的效率 降低了读的效率,索引优化较难进行。
反范式:
优:可以减少表之间的关联,可以更好地进行索引优化。
劣:数据冗余以及数据异常,数据得修改需要更多的成本 。

.数据库的事务基本特性ACID?隔离级别?

A原子性指的是一个事务中的操作要么全部成功,要么全部失败
C一致性指的是一致性的状态,数据库总是从一个一致性的状态转换到另外一个一致性的状态。
比如A给B转账100块钱,假设中间sql执行过程中系统崩溃A也不会损失100块,因为事务没有提交,修改也就不会保存到数据库。
I隔离性指的是一个事务的修改在最终提交前,对其他事务是不可见的。
D持久性指的是一旦事务提交,所做的修改就会永久保存到数据库中。

read uncommit 读未提交read commit 读已提交repeatable read可重复读serializable串行

repeatable read可重复读,这是mysql的默认级别,就是每次读取结果都一样,但是有可能产生幻读。
read uncommit读未提交,可能会读到其他事务未提交的数据,也叫做脏读。
用户本来应该读取到id=1的用户age应该是10,结果读取到了其他事务还没有提交的事务,结果读取结果age=20,这就是脏读。
在这里插入图片描述

read commit读已提交,两次读取结果不一致,叫做不可重复读,解决了脏读的问题,他只会读取已经提交的事务。
用户开启事务读取id=1用户,查询到age=10,再次读取发现结果=20,在同一个事务里同一个查询读取到不同的结果叫做不可重复读。
在这里插入图片描述

serializable串行,一般是不使用,它会给每一行读取的数据加锁,会导致大量超时和锁竞争的问题。

.事务的基本特性ACID由什么保证?

A原子性undo log日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经执行成功的sql
C一致性一般由代码层面来保证
I隔离性由MVCC来保证
D持久性由内存+redo log来保证,mysql修改数据同时在内存和redo log记录这次操作,事务提交的时候通过redo log刷盘,宕机的时候可以从redo log恢复


.关系型数据库和非关系型数据库?

关系型数据库MySQL Oracle非关系型数据库MongoDB Redis

数据存储方式不同:
关系型数据库表格式的,存储在数据表的行和列中。数据表可以彼此关联协作存储,也很容易提取数据。
非关系型数据库是存储在数据集中,就像文档、键值对或者图结构。数据及其特性是选择存储和提取方式的主要因素。

扩展方式不同:
关系型数据库要支持更多并发量,是纵向扩展的(即提高处理能力),尽管有很大的扩展空间,但是纵向扩展是有上限的。
非关系型数据库存储是分布式的,只需横向扩展通过给资源池添加更多普通的**数据库服务器(节点)**来分担负载即可。

③对事务性的支持不同:
关系型数据库:对于高事务性或者复杂数据查询需要控制执行计划,性能和稳定性较好。支持对事务原子性细粒度控制,并且易于回滚事务
非关系型数据库:稳定性不如关系型数据库,所以它们真正的价值是在操作的扩展性和大数据量处理方面

.数据库的锁?锁的分类

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。 类似于java的synchronized同步锁,对数据加锁使事务对数据对象有了控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

读写方面来看,分为:mysql锁分为共享(读)锁排他(写)锁读锁是共享的,可以通过lock in share mode实现,这时候只能读不能写。写锁是排他的,它会阻塞其他的写锁和读锁。

颗粒度来区分:
表级锁开销小,加锁快不会出现死锁锁定粒度大,发生锁冲突的概率最高,并发度最低
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
行级锁开销大,加锁慢出现死锁锁定粒度最小,发生锁冲突的概率最低并发度最高
行锁又可以分为乐观锁和悲观锁,悲观锁可以通过for update实现乐观锁则通过版本号实现

.索引谈一下?

数据结构来区别:主要包含B+树Hash索引



无用

.myisam(米桑)和innodb两大表格的区别?

MySQL共有5种表格:①myisam ②innodb ③heap ④merge ⑤isam

①myisam是5.1之间的默认引擎,支持全文检索、压缩、空间函数等,但是不支持事务、行级锁、外键,且索引和数据是分开存储的,所以一般用于大量查询少量插入的场景来使用。

②innodb是基于聚簇索引建立的,和myisam相反它支持事务、外键,并且通过MVCC来支持高并发索引和数据存储在一起
无用

.聚簇和非聚簇索引是什么?

聚簇索引B+树是左小右大的顺序存储结构**,节点只包含id索引列**,而叶子节点包含索引列和数据,这种数据和索引在一起存储的索引方式叫做聚簇索引,一张表只能有一个聚簇索引。
假设没有定义主键,InnoDB会选择一个唯一的非空索引代替,如果没有的话则会隐式定义一个主键作为聚簇索引。

create table user(
 id int(11) not null,
  age int(11) not null,
  primary key(id),
  key(age)
);

图示
在这里插入图片描述

非聚簇索引(二级索引)保存的是主键id值,这一点和myisam保存的是数据地址是不同的。
图示
在这里插入图片描述

InnoDB和Myisam聚簇和非聚簇索引的区别
在这里插入图片描述

.什么是覆盖索引和回表吗?

覆盖索引:指的是在一次查询中,如果一个索引包含或覆盖所有需要查询的字段的值,我们就称之为覆盖索引,而不再需要回表查询。 确定一个查询是否是覆盖索引,我们只需要explain sql语句看Extra的结果是否为“Using index” 即可。
以下为举例:

以上面的user表来举例,再增加一个name字段,然后做一些查询试试。

explain select * from user where age=1; // 查询的name无法从索引数据获取
explain select id,age from user where age=1; // 可以直接从索引获取

.MySQL的优化




无用

.如何较好地分库分表?

分库分表分为垂直和水平,一般而言先垂直后水平
垂直分库:基于目前的微服务拆分来说,都是基本做到了垂直分库
图示
在这里插入图片描述

垂直分表:如果表字段比较多,将不常用的、数据较大的等等做拆分
在这里插入图片描述
水平分表:根据业务场景来决定使用什么字段作为分表字段(sharding_key)
如:
现在日订单1000万,我们大部分的场景来源于C端,我们可以用user_id作为sharding_key,数据查询支持到最近3个月的订单,超过3个月的做归档处理,那么3个月的数据量就是9亿,可以分1024张表,那么每张表的数据大概就在100万左右。

比如用户id为100,那我们都经过hash(100),然后对1024取模,就可以落到对应的表上了。

.分表后的ID怎么保证唯一性的呢?

①设定步长,比如1-1024张表我们设定1024的基础步长,这样主键落到不同的表就不会冲突了。
分布式ID,自己实现一套分布式ID生成算法或者使用开源的如雪花算法这种
③分表后不使用主键作为查询依据,而是每张表单独新增一个字段作为唯一主键使用,比如订单表订单号是唯一的,不管最终落在哪张表都基于订单号作为查询依据,更新也一样。

.分表后非sharding_key的查询如何处理?

①做一个mapping表,比如这时候商家要查询订单列表怎么办呢?不带user_id查询的话你总不能扫全表吧?所以我们可以做一个映射关系表,保存商家和用户的关系,查询的时候先通过商家查询到用户列表,再通过user_id去查询。

②打宽表,一般而言,商户端对数据实时性要求并不是很高,比如查询订单列表,可以把订单表同步到离线(实时)数仓,或者基于其他如es提供查询服务。

③数据量不大的话,比如后台的一些查询之类的,可以异步的形式来做或通过多线程扫表,后再聚合结果的方式来做。

java代码示例

List<Callable<List<user>>> taskList = Lists.newArrayList();
for(int shardingIndex = 0; shardingIndex < (1 << 10); shardingInedx++) {
	taskList.add() -> (userMapper.getProcessingAccountList(shardingIndex));
}
List<ThirdAccountInfo> list = null;
try{
	list = taskExecutor.executeTask(taskList);
} catch () {
	// do something
}

public class TaskExecutor{
	public <T> List<T> executeTask(Collection<? extends Callable<T>> tasks) throws Exception {
		List<T> result = List.newArrayList();
		List<Future<T>> futures = ExecutorUtil.invokeAll(tasks);
		for(Future<T> future : futures){
			result.add(future.get());
		}
		return result;
	}
}

.什么是MVCC?说说什么是幻读?

MVCC:多版本并发控制,实际上就是保存了数据在某个时间节点的快照。
我们每行数实际上隐藏了两列,创建时间版本号,过期(删除)时间版本号,每开始一个新的事务,版本号都会自动递增。
图示
在这里插入图片描述

什么是幻读呢?举例:用户注册时,我们先查询用户名是否存在,不存在就插入,假定用户名是唯一索引。

在这里插入图片描述

.什么是间隙锁?

隔离级别为可重复读 才会出现间隙锁,结合MVCC和间隙锁可以解决幻读的问题
下图为举例示范:
在这里插入图片描述

.MySQL的主从同步?

主从同步的基本原理?
①master提交完事务后,写入binlog ②slave连接到master,获取binlog
③master创建dump线程,推送binglog到slave
④slave启动一个IO线程读取同步过来的master的binlog,记录到relay log中继日志中
⑤slave再开启一个sql线程读取relay log事件并在slave执行,完成同步 ⑥slave记录自己的binglog
流程图
在这里插入图片描述

由于mysql默认的复制方式是异步的,主库把日志发送给从库后不关心从库是否已经处理,这样会产生一个问题就是假设主库挂了,从库处理失败了,这时候从库升为主库后,日志就丢失了。由此产生两种复制方式:

全同步复制
主库写入binlog后强制同步日志到从库,所有的从库都执行完成后才返回给客户端,但是很显然这个方式的话性能会受到严重影响。

半同步复制
和全同步不同的是,半同步复制的逻辑是这样,从库写入日志成功后返回ACK确认给主库,主库收到至少一个从库的确认就认为写操作完成。

.主从的延迟怎么解决呢?

这是一个相对无解的问题,只能由开发者自己来判断了,如:需要走主库的强制走主库查询


二、常见数据库题目

1.表名为stu,里面有学生id,和3门课程成绩,求平均成绩最高的学生id

在这里插入图片描述

SELECT u_id FROM stu GROUP BY u_id ORDER BY AVG(score) DESC LIMIT 1

2.


总结

这就是我的数据库相关知识总结!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值