您的位置: 首页  >  文章  >  java后端开发--数据库 精简总结

java后端开发--数据库 精简总结

分类: 文章 • 2025-03-29 20:05:45

数据库

 

三大范式

关系型数据库：

    表示由表头（字段名：用来规定数据的名字）和数据部分组成（实际存储的数据单元）

 

查看数据表

（1）查看所有表：show tables;

（2）查看部分表：show table like‘pattern’

（3）查看表的创建语句：show create table表名;

 

数据操作

1新增数据

Insert into 表名 values（值列表）[，值列表];

Insert into表名（字段列表）values（值列表）[，值列表];

2查看数据

Select */字段列表from表名[where条件];

3更新数据

Update表名set字段=值[where条件];  --建议都有where

4删除数据

Delete from表名[where条件];

 

数据类型分成了三大类：数值类型，字符串类型和时间日期类型

java后端开发--数据库 精简总结

范式

1NF（第一范式）是对属性具有原子性的要求，不可再分

2NF（第二范式）是对记录有唯一性的要求，即实体的唯一性，不存在部分依赖，每一列与主键都相关

3NF（第三范式）对字段有冗余性的要求，任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在依赖传递

数据库索引

索引原理https://www.cnblogs.com/aspwebchh/p/6652855.html

索引数据结构b+ https://www.cnblogs.com/bypp/p/7755307.html

Mysql索引总结 https://blog.****.net/V_victor/article/details/52232685

    索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。 索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。

     主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

 

为什么使用数据索引能提高效率

  • 数据索引的存储是有序的

  • 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的

  • 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

索引的缺点： 在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。 即索引能让数据库查询数据的速度上升， 但使写入数据的速度下降

    

  底层数据结构是B+树：优点：查找速度快、效率高，在查找的过程中，每次都能抛弃掉一部分节点，减少遍历个数。

  分类：

  • 唯一索引：唯一索引不允许两行具有相同的索引值

  • 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

  • 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

  • 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

事务

我们在写Java程序，遇到并发问题时，会想到用锁来解决。数据库遇到并发问题怎么解决呢？答案就是事务，事务的本质就是锁和并发的结合体

 

事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败；作为单个逻辑工作单元执行的一系列操作，满足四大特性：

  1. 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行

  2. 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态

  3. 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行

  4. 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存

事务的并发问题有哪几种？

  1. 丢失更新：两个事务T1和T2读入同一数据并修改，T2提交的结果覆盖了T1提交的结果。

  2. 脏读：事务T1读取到事务T2修改但未提交的数据，之后事务T2又回滚其更新操作，导致事务T1读到的是脏数据。（事务T1读取到了事务T2未提交的数据）

  3. 不可重复读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读取数据时，得到与前一次不同的数据。 不可重复读的重点是修改。

  4. 幻读： 如事务T1按一定条件从数据库中读取某些数据记录后，事务T2删除了其中部分数据记录，当事务T1再次按相同条件读取数据时，发现某些数据记录不存在了。 幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。

事务的隔离级别有哪几种？

隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是：

为此我们需要通过提供不同类型的“锁”机制针对数据库事务进行不同程度的并发访问控制，由此产生了不同的事务隔离级别：隔离级别（低->高）

 

1.读未提交（Read Uncommitted） 

含义解释：只限制同一数据写事务禁止其他写事务。解决”更新丢失” 

名称解释：可读取未提交数据 

所需的锁：排他写锁

2.读提交（Read Committed） 

含义解释：只限制同一数据写事务禁止其它读写事务。解决”脏读”和”更新丢失” 

名称解释：必须提交以后的数据才能被读取 

所需的锁：排他写锁、瞬间共享读锁

3.可重复读（Repeatable Read） 

含义解释：限制同一数据写事务禁止其他读写事务，读事务禁止其它写事务(允许读)。解决”不可重复读”、”更新丢失”和”脏读”。 注意：没有解决幻读，解决幻读的方法是增加范围锁（range lock）或者表锁。 

名称解释：能够重复读取 

所需的锁：排他写锁、共享读锁

4.序列化（Serializable） 

         含义解释：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务            序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。 

         名称解释：限制所有读写事务都必须串行化实行。 

下表是各隔离级别对各种异常的控制能力：

java后端开发--数据库 精简总结

MySQL：可重复读（Repeatable Read） 

Oracle：读提交（Read Committed） 

SQLServer：读提交（Read Committed） 

DB2：读提交（Read Committed） 

PostgreSQL：读提交（Read Committed）

什么是视图？以及视图的使用场景有哪些？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

如下两种场景一般会使用到视图：

  1. 不希望访问者获取整个表的信息，只暴露部分字段给访问者，所以就建一个虚表，就是视图。

  2. 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。

注意：这个视图是在数据库中创建的 而不是用代码创建的。

drop,delete与truncate的区别？

drop 直接删除表；truncate 删除表中数据，再插入时自增长id又从1开始 ；delete 删除表中数据，可以加where字句。

drop,truncate删除速度快，delete删除速度慢 需要逐行

 

使用简要说明：

  • 不再需要一张表的时候，用drop

  • 想删除部分数据行时候，用delete，并且带上where子句

  • 保留表而删除所有数据的时候用truncate

触发器的作用？

触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。

数据库的乐观锁和悲观锁是什么？

    数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

  • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

悲观锁是一种利用数据库内部机制提供的锁的方式，也就是对更新的数据加锁，这样在并发期间一旦有一个事务持有了数据库记录的锁，其他的线程将不能再对数据进行更新了，这就是悲观锁的实现方式。

 

MySQL InnoDB中使用悲观锁：

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。 set autocommit=0;

//0.开始事务

begin;/begin work;/start transaction; (三者选一就可以)

//1.查询出商品信息

select status from t_goods where id=1 for update;

//2.根据商品信息生成订单

insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2

update t_goods set status=2;

//4.提交事务

commit;/commit work;

    上面的查询语句中，我们使用了 select…for update 的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

    上面我们提到，使用 select…for update 会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

优点与不足：

    悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

  • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

乐观锁是一种不会阻塞其他线程并发的控制，它不会使用数据库的锁进行实现，它的设计里面由于不阻塞其他线程，所以并不会引起线程频繁挂起和恢复，这样便能够提高并发能力，所以也有人把它称为非阻塞锁。一般的实现乐观锁的方式就是记录数据版本。

    数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

使用版本号实现乐观锁：

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

1.查询出商品信息

select (status,status,version) from t_goods where id=#{id}

2.根据商品信息生成订单

3.修改商品status为2

update t_goods

set status=2,version=version+1

where id=#{id} and version=#{version};

优点与不足：

      乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

超键、候选键、主键、外键分别是什么？

    超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一 个超键。超键包含候选键和主键。

    候选键：是最小超键，即没有冗余元素的超键。

    主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

    外键：在一个表中存在的另一个表的主键称此表的外键。

 

候选码和主码：

例子：邮寄地址（城市名，街道名，邮政编码，单位名，收件人）

  • 它有两个候选键:{城市名，街道名} 和 {街道名，邮政编码}

  • 如果我选取{城市名，街道名}作为唯一标识实体的属性，那么{城市名，街道名} 就是主码(主键)

SQL 约束有哪几种？

  • NOT NULL: 用于控制字段的内容一定不能为空（NULL）。

  • UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。

  • PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

  • FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

  • CHECK: 用于控制字段的值范围。

MySQL存储引擎中的MyISAM和InnoDB区别详解

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然MyISAM性能极佳，但却有一个显著的缺点： 不支持事务处理。不过，MySQL也导入了另一种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

InnoDB是MySQL的数据库引擎之一，其由Innobase oy公司所开发，2006年五月由甲骨文公司并购。与传统的ISAM、MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能

 

MyIASM和Innodb两种引擎所使用的索引的数据结构是什么

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

 

varchar和char的区别

char是一种固定长度的类型，varchar是一种可变长度的类型，例如：

定义一个char[10]和varchar[10]，如果存进去的是 'test'，那么char所占的长度依然为10，除了字符 'test' 外，后面跟六个空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的

char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储于查找

char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。

 

varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

两者的存储数据都非unicode的字符数据。

主键、自增主键、主键索引与唯一索引概念区别

主键：指字段 唯一、不为空值 的列；

主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；

自增主键：字段类型为数字、自增、并且是主键；

唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键。

主键就是聚集索引吗？主键和索引有什么区别？

主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引。在SQLServer中，主键的创建必须依赖于索引，默认创建的是聚集索引，但也可以显式指定为非聚集索引。InnoDB作为MySQL存储引擎时，默认按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引。

实践中如何优化MySQL

实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面，如下图所示：

java后端开发--数据库 精简总结

SQL语句的优化：

⒈优化insert语句：一次插入多值；

⒉应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；

⒊应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；

⒋优化嵌套查询：子查询可以被更有效率的连接(Join)替代；

⒌很多时候用 exists 代替 in 是一个好的选择。

⒍选择最有效率的表名顺序：数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处

相关推荐