复习笔记4-数据库

事务四大特性（ACID）

原子性（atomicity)：要么都执行，要么都不执行
隔离性（isolation）：所有操作执行完以前其它会话不能看到过程（并发事务之间要相互隔离，互不干扰）（隔离性有三种问题，有四种级别来解决）
一致性（consistency)：事务前后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到,  
                    不论转账事务操作是否成功，两者的存款总额不变
                    事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
持久性（durability）：一旦事务提交，对数据的改变是永久的，（即使宕机重启也能恢复到事务成功结束时状态）

数据库隔离级别

多个事务读可能会有以下问题
脏读：		事务B读取事务A还没有提交的数据
不可重复读：	事务 A 读取同一数据两个，因为两次中间事务 B 修改了数据，导致 A 两次数据不一致
幻读: 		事务 A 查询表两次，因为两次中间事务 B增删了数据，导致 A 两次数据结果集（大小条数）不一致，
			这个不能像不可重复读通过记录加锁解决，因为对于新增的记录无法加锁。需要事务串行化 

事务隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

Mysql 默认 REPEATABLE_READ，Oracle 默认 READ_COMMITTED

参考 深刻理解mysql四种隔离级别及底层实现原理（MVCC和锁）

读未提交： 另一个事务修改了数据，但尚未提交，而本事务中的 SELECT 读到这些未被提交的数据脏读
		  原理：读不会加任何锁。而写会加排他锁，并到事务结束以后释放

读已提交： 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多
		  次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
		  
  原理： 写数据时，使用排它锁, 读取数据不加锁而是使用了 MVCC 版本机制，每次查询时生成版本。
  出现问题：事务 A 中多次执行 select， 查询间隙有其余事务更新了数据并提交，则出现不可重复读

可重复读： 在同一个事务里，SELECT 的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会
        是一致的（利用版本的原理，两次读，读的是相同的版本）。但会有幻读现象
        
   原理：MVCC版本的生成时机不一样了，一次事务中只在第一次 select 时生成版本，后续的查询都是在这
        个版本上进行，从而实现了可重复读。但 MVCC 的快照只对读操做有效，对写操做无效
   出现的问题：事务A依次执行以下3条sql，事务B在语句1和2之间，插入10条age=20的记录，事务A就幻读了。

串行化： 最高隔离级别，该级别，不会产生任何异常。
	原理：并发的事务一个个按照顺序执行，通过强制事务排序，在每个读的数据行上加上共享锁，将 select 
		转化为 select ... lock in share mode 执行，即针对同一数据的全部【读写】，都变成互斥的了，
		读-写，写-写均互斥。
    出现问题：可靠性大大提升，并发性大大下降

MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景

MYISAM	表级锁，不支持外键，不支持事务，非聚集性索引
INNODB	行级锁，支持表级锁，支持外键，支持事务，聚集性索引

索引有B+索引和hash索引

Hash	hash索引，等值查询效率高，不能排序,不能进行范围查询
B+		数据有序,范围查询

聚集索引和非聚集索引

聚集索引		数据按索引顺序存储，中子结点存储真实的物理数据
非聚集索引	存储指向真正数据行的指针

索引的优缺点，什么时候使用索引，什么时候不能使用索引

索引最大的好处是提高查询速度，
缺点是更新数据时效率低，因为要同时更新索引
对数据进行频繁查询建议建立索引，如果要频繁更改数据不建议使用索引。

InnoDB索引和MyISAM索引的区别

一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主

索引的底层实现（B+树，为何不采用红黑树，B树）重点

红黑树		增加，删除，红黑树会进行频繁的调整，来保证红黑树的性质，浪费时间
B树(B-树)	B树，查询性能不稳定，查询结果高度不致，每个结点保存指向真实数据的指针，相比B+树每一层每屋存储的元素更多，显得更高点
B+树		B+树相比较于另外两种树,显得更矮更宽，查询层次更浅

B+树的实现

一个m阶的B+树具有如下几个特征：
1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素

为什么使用B+Tree

索引查找过程中就要产生磁盘I/O消耗,主要看IO次数，和磁盘存取原理有关。
根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，
将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入
局部性原理与磁盘预读

Sql的优化

1.sql尽量使用索引,而且查询要走索引
2.对sql语句优化
子查询变成left join
limit 分布优化，先利用ID定位，再分页
or条件优化，多个or条件可以用union all对结果进行合并（union all结果可能重复）
不必要的排序
where代替having,having 检索完所有记录，才进行过滤
避免嵌套查询
对多个字段进行等值查询时，联合索引

索引分类，索引失效条件

索引类型	概念
普通索引	最基本的索引，没有任何限制
唯一索引	与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引	它是一种特殊的唯一索引，不允许有空值。
全文索引	针对较大的数据，生成全文索引很耗时好空间。
组合索引	为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

失效条件
条件是or,如果还想让or条件生效，给or每个字段加个索引
like查询，以%开发
内部函数
对索引列进行计算
is null不会用，is not null 会用

数据库的主从复制

复制方式	操作
异步复制	默认异步复制，容易造成主库数据和从库不一致,一个数据库为Master,一个数据库为slave,通过Binlog日志,slave两个线程，一个线程去读master binlog日志，写到自己的中继日志一个线程解析日志，执行sql,master启动一个线程,给slave传递binlog日志
半同步复制	只有把master发送的binlog日志写到slave的中继日志，这时主库,才返回操作完成的反馈，性能有一定降低
并行操作	slave 多个线程去请求binlog日志

long_query怎么解决

设置参数，开启慢日志功能，得到耗时超过一定时间的sql

varchar和char的使用场景

类型	使用场景
varchar	字符长度经常变的
char	用字符长度固定的

数据库连接池的作用

维护一定数量的连接，减少创建连接的时间
更快的响应时间
统一的管理

分库分表，主从复制，读写分离

读写分离，读从库，写主库
spring配置两个数据库，通过AOP（面向切面编程），在写或读方法前面进行判断得到动态切换数据源。

数据库三范式

第一范式（1NF）：字段不可分；
第二范式（2NF）：有主键，非主键字段依赖主键；
第三范式（3NF）：非主键字段不能相互依赖。

关系型数据库和非关系型数据库区别

关系型数据库：二维表结构、支持sql、支持事务、不支持高并发、海量数据
非关系数据库：键值对、不支持sql、不支持事务（redis貌似支持）、适合分布式，并发高

关系型数据库

优点

1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
4、支持SQL，可用于复杂的查询。
5.支持事务

缺点
1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
2、固定的表结构；
3、不支持高并发读写需求；
4、不支持海量数据的高效率读写

非关系型数据库

1、键值数据库：Redis、Memcached
2、文档数据库：MongoDB
3、图形数据库：Neo4j、InfoGrid
4、列族数据库：Bigtable、HBase、Cassandra

1、使用键值对存储数据；
2、分布式；
优点
无需经过sql层的解析，读写性能很高
基于键值对，数据没有耦合性，容易扩展
存储数据的格式：nosql的存储格式是key,value形式
缺点
不提供sql支持

数据库中join的left join , inner join, cross join

1.以A，B两张表为例
A left join B
选出A的所有记录，B表中没有的以null 代替
right join 同理

2.inner join
A,B有交集的记录

3.cross join (笛卡尔积)
A中的每一条记录和B中的每一条记录生成一条记录
例如A中有4条，B中有4条，cross join 就有16条记录

有哪些锁,select时怎么加排它锁

锁		概念

行锁		作用于数据行
表锁		作于用表
共享锁	lock in share model 一个事务获取了共享锁，其他事务只能读，不能写。其他事务可获共享锁，不能获取排他锁
	    select * from zje where math>60 lock in share mode；
排他锁 	for update	如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获得排他锁的事务可以读写数据。
	    select * from zje where math >60 for update
	    insert ，delete ， update在事务中都会自动默认加上排它锁。
注意：MySQL innoDB 和 oracle 原理不一样，虽然都是 for update，但 oracle 是给这条数据行加锁，
 innoDB 是给索引加锁。简单的说就是：如果语句无法命中索引，innoDB 就会锁表

【针对非索引字段】 的增删改 是【表锁】，【针对索引字段】的增删改 是【行锁】，执行查询是不加任何锁

oralce 表锁

这是表锁，加 where 条件就是行锁
update ESTATE_DJ_AJXX t set ywh='3' 

这是表锁，加 where 条件就是行锁
delete from ESTATE_DJ_AJXX 

这是表锁，加 where 条件就是行锁
select * from ESTATE_DJ_AJXX  for update

死锁怎么解决

找到进程号，kill 进程

最左匹配原则

最左匹配原则是针对索引的
举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，
这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，
查询条件where name=‘xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，
按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换下。这个sql也能利用到索引了

如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；
如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

数据库视图

1、是一张虚拟表，视图只供查询，数据不可更改，来源于我们建立的实体表。
2、视图可以关联多个表，优化查询速度。

Create view vw1 as
Select st.学号,st.姓名,st.所属院系 from student as st,course as co,score as sc
Where co.课名=’心理学’ and sc.考试成绩>80 and st.学号=sc.学号 and co.课号=sc.课号

表的删除

drop命令：删除表结构和数据，释放掉所占用的空间
truncate命令：只删数据，不记日志，不可回滚，不会激活与表有关的删除触发器，速度快。
delete命令：只删数据，但是逐行删除、记日志可回滚，速度慢

sql优化中in与exists的区别

用 in 时先子表全表查询,然后将每一条数据作为条件以及索引拿去进行主表查询, 主表大于子表用 in 效率高

用 exists 会先进行主表全表查询,然后将每一条数据作为条件以及索引拿去进行子表查询,主表小于子表用 exists 效率高

1、select * from student s where s.stuid in(select stuid from score ss where ss.stuid = s.stuid)

2、select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid)

mysql 数据库连接超过8小时自动未使用自动回收

数据库连接池中连接超过8小时应用程序不去访问数据库，数据库就断掉连接 。再次访问就会抛出异常。

show variables like '%timeout%';

mysql w10 安装

w10安装mysql 分为 zip 压缩包安装和 msi 文件安装，mysql 版本从 5.7 跳到了 8.0
下载地址 https://dev.mysql.com/downloads/mysql/
选择需要的 MySQL Community Server 版本及平台后下载

zip 安装步骤参考 https://blog.csdn.net/qq_41107231/article/details/114481989
msi 安装步骤参考 https://cloud.tencent.com/developer/article/1744494

navicat 连接mysql 8.0报错

低版本的 navicat 连接mysql 8.0 报错（1251- Client does not support authentication protocol…）
mysql8 之前加密规则是 mysql_native_password，mysql8 之后，规则为caching_sha2_password

解决方法有两种：

方法1.升级navicat驱动；
方法2.把mysql用户登录密码加密规则还原成mysql_native_password.

方法 2 操作步骤如下：

1、登录 mysql ：打开命 cmd 命令行窗口，输入命令后回车:

mysql -u root -p

2、然后输入数据库 root 密码，再继续执行如下命令，注意带分号；

# 用旧加密规则更改密码，xxxx 是明文密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxxx';  
FLUSH PRIVILEGES;  #刷新权限