4. 数据库知识点

数据库知识点

一、事务的4个特性(ACID)

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二、事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

三、MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交(read-uncommitted)	是	是	是
不可重复读(read-committed)	否	是	是
可重复读(repeatable-read)	否	否	是
串行化(serializable)	否	否	否

四、七种事务的传播机制

REQUIRED（默认）：支持使用当前事务，如果当前事务不存在，创建一个新事务。

SUPPORTS：支持使用当前事务，如果当前事务不存在，则不使用事务。

MANDATORY：中文翻译为强制，支持使用当前事务，如果当前事务不存在，则抛出Exception。

REQUIRES_NEW：创建一个新事务，如果当前事务已经存在，把当前事务挂起。

NOT_SUPPORTED：无事务执行，如果当前事务不存在，把已存在的当前事务挂起。

NEVER：无事务执行，如果当前有事务则抛出Exception。

NESTED：嵌套事务，如果当前事务存在，那么在嵌套的事务中执行。如果当前事务不存在，则表现跟REQUIRED一样。

spring支持事务的两种方式

编程式事务 当系统需要明确的，细粒度的控制各个事务的边界，应选择编程式事务

声明式事务 当系统对于事务的控制粒度较粗时，应该选择申明式事务无论你选择上述何种事务方式去实现事务控制，spring都提供基于门面设计模式的事务管理器供选择

原文链接：https://blog.csdn.net/qq_32465815/article/details/82710940

五、 MySQL,Oracle的默认隔离级别

Mysql 数据库: 可重复读

Oracle 数据库: 读已提交

六、MySQL和 Oracle 的分页 和方言区别

MySQL 是一种方言，Oracle 也是一种方言，MSSQL 也是一种方言

要实现分页查询需要知道两个参数：

currentPage： 表示当前页（cp）

lineSize：表示每页显示的数据量（ls）

mysql的分页更简单，直接使用 LIMIT 关键字就可以实现了。

SELECT  *  FROM emp LIMIT 0,3//第一页  索引从0 开始

Oracle的分页是使用伪列 ROWNUM（rownum） 结合子查询实现

显示第一页，每页显示三条数据

SELECT  *

　　FROM (SELECT  ROWNUM rn,empno,ename,job,mgr,hiredate,comm,deptno

　　FROM emp

　　WHERE ROWNUM<=3) temp

　　WHERE temp.rn>=1;

七、 SQL语句的种类

1.DDL:Data Definition Language，数据定义语言(知道英文单词意思其实很好记)，包括了：create，drop，alter，truncate(这是删除并新建表，可消除自增的历史最大值)；

2.DML:Data Manipulation Language，数据操作语言，包括了：insert，delete，update，select(其实就是增删改查，其中select也是DQL)；

3.DQL:Data Query Language(我唯一能背下来的英文形式，哈哈哈)，数据查询语言，仅包括select(数据库的重点，重点中的重点)；

4.TCL:Transaction Control Language，事务控制语言，包括begin，savepoint xxx，rollback，rollback to xxx，commit；

5.DCL:Data Control Language，数据控制语言，包括分配数据库用户权限相关的sql语句。

链接：http://www.imooc.com/article/details/id/286761

八、MySQL中的两种引擎有什么区别

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。 MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要 update 一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的.

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它 支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如:事务。（项目中常用）事务、行锁、表锁.

九、数据库SQL语句优化

1. Where 子句只能接收from 子句输出的数据(分组前使用) 而having子句则可以接收来自group by或者from子句的输入(分组后使用). 一般放在group by 后面使用,作为相当于where ,表示对分组统计后的数据再次过滤

2. 应尽快避免在where 子句中使用!= 或者<> 操作符, 否则引擎将放弃使用索引而进行全表扫描

3. 应尽量避免在where 子句中使用or 来连接提交,否则将导致引擎放弃使用索引而进行全表扫描

4. 尽量避免使用in和not in:, 否则将导致引擎放弃使用索引而进行全表扫描

5. 索引并不是越多越好,索引固然可以提高相应的select 的效率, 但同时也降低了insert 及update 的效率,因为insert或update 时 有可能会重建索引, 所以怎样建索引需要慎重考虑,视具体情况而定,一个表的索引数最好不要超过6个.若 太多则考虑一些不常使用到的列上建的索引是否有必要

6. 在查询条件表达式的左侧尽量不要使用函数,否则索引失效

7. 根据查询条件, 建立索引,如果查询条件不止一个时,使用组合索引

8. 如果有like 话, 尽量避免%xxx% 两侧都是%的条件,单侧%(后面)可以使用索引,多侧不可以

9. 尽量避免使用游标；任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

10. 尽可能的使用 varchar/nvarchar 代替 char/nchar。尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

11. 建立索引时字段不能有null值

十、Mysql 性能优化举例

1.当只要一行数据时使用 LIMIT 1

当你查询表的有些时候，你已经知道结果只会有一条结果，在这种情况下，加上 LIMIT 能。这样一样，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查下一条符合记录的数据。

2.选择正确的存储引擎
在 MySQL 中有两个存储引擎 MyISAM和InnoDB，每个引擎都有利有弊。
MyISAM适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要 update 一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它 支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如:事务。

3.用 Not Exists 代替 Not In

Not Exists 允许用户使用相关子查询已排除一个表中能够与另一个表成功连接的所有记录。Not Exists 用到了连接，能够发挥已经建好的索引的作用，而 Not In 不能使用索引。Not In 是最慢的方式，要同每条记录比较，在数据量比较大的查询中不建议使用这种方式。

Select a.mobileidfrom Log_user a where not exists(select b.mobileid from magazineitem b where b.mobileid=a.mobileid);

4.对操作符的优化 尽量不采用不利用索引的操作符****

如:in ，not in ， is nul， is not null，<>等 某个字段你总要会经常用来做搜索，为其建立索引:

Mysql 中可以使用 alter table 语句来为表中的字段添加索引的基本语法是:

ALTER TABLE <表名> ADD INDEX (<字段>);
例如：

mysql> alter table test add index(t_name);`

5.mysql 分库分表:

分库分表有垂直切分和水平切分两种。

垂直切分:即将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数据库 workDB、商品数据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等。

水平切分:当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如 userID 散列，进行划 分，然后存储到多个结构相同的表，和不同的库上。

主键自增和uuid有什么区别

引言
之前有段时间用postgresql 数据库，在上云之后，从自增主键变为uuid，感觉uuid全球唯一，很方便。

最近用mysql，发现mysql主键都是选择自增主键，仔细比较一下，为什么mysql选择自增主键，有什么不同。

在mysql5.0之前，如果是多个master复制的环境，无法用自增主键，因为可能重复。在5.0以及之后的版本通过配置自增偏移量解决了整个问题。

什么情况下我们希望用uuid

1. 避免重复，便于scale，这就是我们做cloud service的时候选择uuid的主要原因

2. 入库之前可以知道id

3.相对安全，不能简单的从uuid获取信息，但是如果自增，则容易暴露信息，如果一个客户id是123456，很容易猜到有客户id是123456.

UUID有什么问题
1.uuid有16个字节，比int（4 byte）和bigint（8 byte）占用更多存储空间

2.由于size和无序性，可能引起性能问题

Mysql的uuid原理
mysql的innodb存储引擎处理storage的方式是靠聚集索引。

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况

1.为什么要使用uuid做主键
(1).其实在innodb存储引擎下，自增长的id做主键性能已经达到了最佳。不论是存储和读取速度都是最快的，而且占的存储空间也是最小。

(2).但是在我们实际到项目中会碰到问题，历史数据表的主键id会与数据表的id重复，两张自增id做主键的表合并时，id一定会有冲突，但如果各自的id还关联了其他表，这就很不好操作。

(3).如果使用UUID，生成的ID不仅是表独立的，而且是库独立的。对以后的数据操作很有好处，可以说一劳永逸。

2.UUID优缺点
缺点： 1. 影响插入速度， 并且造成硬盘使用率低

2. uuid之间比较大小相对数字慢不少， 影响查询速度。

3. uuid占空间大， 如果你建的索引越多， 影响越严重

优点：出现数据拆分、合并存储的时候，能达到全局的唯一性

3.最优方案
(1).InnoDB引擎表是基于B+树的索引组织表。

(2).B+树：B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。

(3).InnoDB主索引：叶节点包含了完整的数据记录。这种索引叫做聚集索引。InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引

(4).聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

综合上述可得：

(1).如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。为了存储和查询性能应该使用自增长id做主键。

(2).对于InnoDB的主索引，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用UUID做物理主键，可以把它作为逻辑主键，物理主键依然使用自增ID。为了全局的唯一性，应该用uuid做索引关联其他表或做外键。

4.如果非要使用uuid做主键，下面是小建议：
如果是主从即M-S模式，最好是不使用mysql自带函数uuid来生成唯一主键，因为主表生成的uuid要再关联从表时，需要再去数据库查出这个uuid，需要多进行一次数据库交互，而且在这个时间差里面主表很有可能还有数据生成，这样就很容易导致关联的uuid出错。如果真要使用uuid，可以在Java中生成后，直接存储到DB里，这时主从的uuid就是一样的了！

补充：mysql的uuid()主键重复

1、mysql的uuid()主键重复
mysql使用了navicat客户端，某次执行了如下sql

1
select replace(uuid(), ‘-’, ‘’) as id, u.user_id from t_user u;
结果发现，生成的uuid重复了，

经过排查，发现是navicat的问题，需要将该sql语句做如下调整:

1
select replace(convert(uuid() using utf8mb4), ‘-’, ‘’), u.user_id from t_user u;
结果如下：

2、使用其他方案：
将uuid再进行一次md5：

1
select md5(uuid()) as id, u.user_id from t_user u;
以上为个人经验，希望能给大家一个参考

在mysql中查看sql语句的执行情况

们常常用到 explain 这个命令来查看一个这些 SQL 语句的执行情况，查看该 SQL 语句有没有使用上了索引，有没有做全表扫描，这都可以通过 explain 命令来查看。所以我们深入了解 MySQL 的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行 SQL 语句时哪种策略预计会被优化器采用。

使用方法

在 select 语句前加上 explain 就可以了,如

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。

type

以下排序从上到下，性能由坏到好。

a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树

c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找

e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

所以由type可以进行分析，如果是ref、eq_ref那么索引命中且性能较好。

原文链接：https://blog.csdn.net/weixin_39997310/article/details/113215740