数据库基础知识-CSDN博客

本文链接：https://blog.csdn.net/qq_44716275/article/details/115552629

系列文章目录

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

以下为数据库相关知识

文章目录

系列文章目录
前言
一、数据库
二、常见数据库题目
- 1.表名为stu，里面有学生id，和3门课程成绩，求平均成绩最高的学生id
- 2.
总结

前言

数据库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
流程图
在这里插入图片描述