数据库进阶

李慢慢_

已于 2022-09-20 10:59:00 修改

阅读量747

点赞数 1

分类专栏：【架构师进阶】系列文章标签： java java-ee 开发语言 mysql

于 2022-03-30 22:03:07 首次发布

本文链接：https://blog.csdn.net/Eric20170621/article/details/123855728

版权

【架构师进阶】系列专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一：事务的并发问题

脏读：A事务可以读到B事务未提交的数据
不可重复读：A事务读到了B事务修改提交的数据
幻读：A事务修改数据时有包含B事务新增并且已提交的数据，这时候就会读到并修改它

二：事务的隔离级别

在这里插入图片描述

READ UNCOMMITTED（未提交读）

事务中的修改，即使没有提交，对其他事务也都是可见的

READ COMMITTED（提交读，也叫不可重复读）

一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的

REPEATABLE READ（可重复读）

该级别保证了在同⼀事务中多次读取同样记录的结果是⼀致的

三：Spring事务的传播行为

在这里插入图片描述

REQUIRED（v：需要; 依靠; 依赖; ）

事务默认默认值
如果方法运行时，已经处在一个事务中，那么这个时候就会加入到该事务中
如果当前没有事务环境的话，就会为⾃己创建⼀个新的事务

REQUIRES_NEW

该属性表明不管当前是否存在事务，业务方法总会为自己发起一个新的事务
如果方法已经运行在一个事务中，则原有事务会被挂起，新的事务会被创建，直到方法执行结束，新事务才算结束，原先的事务才会恢复执⾏。

SUPPORTS （支持）

如果业务方法在某个事务范围内被调用，则⽅方法成为事务的⼀部分。

如果业务方法A在事务范围外被调用，则⽅方法在没有事务的环境下执行

NOT_SUPPORTED

声明方法不需要事务。

如果方法没有关联到一个事务，容器不会为它开启事务。

如果方法在一个事务中被调用,该事务会被挂起，在方法调用结束后，原先的事务便会恢复执⾏。

MANDATORY（adj.强制性的; 强制的; 法定的; 义务的;）

该属性指定业务方法只能在一个已经存在的事务中执行，业务方法不能发起⾃己的事务

如果业务方法在没有事务的环境下调用，容器就会抛出异常

NEVER

指定业务方法绝对不能在事务范围内执行

如果业务方法在某个事务中执行，容器会抛出异常，只有业务方法没有关联到任何事务，才能正常执行

NESTED（嵌套事务）

如果一个活动的事务存在，则当前方法会运行在一个嵌套的事务中。内部事务的回滚不会对外部事务造成影响，外部事务回滚会导致内部事务的回滚
如果没有活动事务，就创建一个新的事务。它使⽤了一个单独的事务，这个事务拥有多个可以回滚的保存点

四：索引

概念

按照特定的数据结构把数据表中的数据放在索引文件中，以便快速查找
索引存在磁盘中，会占用物理空间

索引类型

①全文索引（FULLTEXT）

只有Myisam支持只有char、varchar、text列可以创建全文索引为了解决 like模糊查询效率低的问题

②HASH 以键值对形式存在只在"="和“in”条件下高效执行，对于范围查询、排序、组合索引效率不高

③BTREE
MySQL默认和最常用的索引结构一种将索引值按一定的算法，存入一个二叉树中（左边存放小于root的内容，右边存放大于root的内容），每次查询都是从Root开始，遍历节点，获取叶子。

索引种类：

主键索引
唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
普通索引
CREATE INDEX index_name ON table_name(col_name);
组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

常见问题：

问：创建索引的时候都会考虑哪些因素呢？
答：经常作为where条件的字段

问：什么是覆盖索引？
答：覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
	当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。
	
如：表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。
当我们通过SQL语句：select key2 from covering_index_sample where key1 = 'keytest';的时候，
就可以通过覆盖索引查询，无需回表。

问：你知道最左前缀匹配吗？
答：根据业务需求，where字句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循左前缀匹配的原则，在检索数据时从联合索引的最左边开始匹配。

例如：创建了一个联合索引：（key1 ，key2 ，key3），
相当于创建了（key1），（key1，key2）和（key1 ，key2 ，key3）三个索引

五：存储引擎

InnoDB引擎实现

聚簇索引（主键索引）

组成结构：根节点、非叶子节点、叶子节点

使用了B + Tree ：
	叶子节点存储着主键和数据
	非叶子节点不存储数据，只存指针，指向指定叶子节点，并且是双向链表形式

在这里插入图片描述

思维拓展：有B + Tree 相反会想到 B - Tree（非叶子节点存储指针之外，也存储数据）
问：为什么不使用B - Tree？
答：
减少IO 存储同等数据，
B - Tree非叶子节点既存储指针也存储数据，这样子会带来磁盘块容易满，树的层级会增加 B + Tree非叶子节点只存指针，这样子会减少磁盘块的占用。

非聚簇索引（普通索引）

特点：叶子节点中存储的是主键id，查询时需要回表再查询一次主键索引(聚簇索引)才能获取到数据
非聚簇索引

Myisam引擎实现

特点：叶子节点存储了数据的地址
在这里插入图片描述

六：SQL优化方式

参考：https://blog.csdn.net/weiwosuoai/article/details/100032141

用Explain来分析sql

概要描述：
	id:选择标识符
	select_type:表示查询的类型。
	table:输出结果集的表
	partitions:匹配的分区
	type:表示表的连接类型
	possible_keys:表示查询时，可能使⽤用的索引
	key:表示实际使⽤用的索引
	key_len:索引字段的⻓长度
	ref:列列与索引的⽐比较
	rows:扫描出的⾏行行数(估算的⾏行行数)
	filtered:按表条件过滤的⾏行行百分⽐比

尽量避免使用子查询
SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name=‘hechunyang’);
避免使用函数索引，会导致全表扫描
SELECT * FROM t WHERE YEAR(d) >= 2016;
⽤IN来替换OR（但是IN包含的值不应过多）
低效查询 > SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效查询 > SELECT * FROM t WHERE LOC_IN IN (10,20,30)
LIKE双百分号无法使用到索引
SELECT * FROM t WHERE name LIKE ‘%de%’
读取适当的记录LIMIT M,N（当只需要一条数据的时候，使用 limit 1）
禁止不必要的ORDER BY排序
正确使用组合索引 index （a,b,c），可以分析出三个索引，遵循最左前缀匹配原子 a |a,b |a,b,c|
报表sql优化：大sql拆小sql，需要加索引就加索引，小sql拆并行（如果前后数据没有依赖的话）

七：mysql锁

Myisam：支持表锁
InnoDB：支持行锁和表锁
悲观锁：

共享锁（读锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
排它锁（写锁）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

乐观锁：

用数据版本记录机制实现。

具体为：
即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version字段来实现。

当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加1。

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