Database__进阶

文章目录

😊 @ 作者：Lion J
💖 @ 主页： https://blog.csdn.net/weixin_69252724?spm=1000.2115.3001.5343
🎉 @ 主题： 数据库mysql（高级部分）
⏱️ @ 创作时间：2024年01月24日
————————————————

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、mysql的服务架构？

服务架构是mysql的骨架结构, 分为连接层, 服务层, 引擎层, 数据文件层

1. 连接层

连接层主要用于处理与客户的连接，授权认证，以及安全认证

2. 服务层

完成sql服务的核心服务, 如相关的sql接口, 以及数据的缓存, sql的分析, 以及内置函数的执行, 是否利用索引查询

3. 引擎

负责与文件层进行交互操作, 是否支持索引, 锁定水平…

4. 物理文件层

在文件系统(硬盘)上存储数据, 日志

---

二、引擎

1. 概述

在mysql中用到的存储机制, 是否支持索引, 支持的锁定水平等各种功能技术, 不同的技术与配置方式叫做引擎

2. 两种主要引擎
●InnoDB

●MyISAM

---

三、索引

在数据量大(万级数据)可以高效的查询数据

1.概念

数据库中维护的特定查找算法的数据结构叫做索引, 他是一种排好序的快速查找的数据结构

2.索引的原理

类似与书上的目录, 不用再寻找书中的数据时每次一页一页的翻, 可以直接通过目录来减小数据的范围,逐渐筛选出结果, 做到高效查找自己想要的数据;

图例:

将数据库中的数据与索引库中的数据一一对应, 提高检索效率

3. 索引的优劣势

●优势

1. 索引可以快速的提高检索效率，降低数据库的IO成本(从系统文件上读取)
2. 索引对查询纪录结果进行排序, 分组, 降低原本mysql结果排序对CPU的消耗

●劣势

1. 由于创建索引之后, 索引相当一张表, 保存主键与索引字段,以及指向实体表的纪录的物理地址, 会占用磁盘空间
2. 数据的增删改不仅会改变实体表的纪录, 还会改变索引关系表(索引树)的纪录, 所以会降低更新表数据的速度，造成实际业务的拖延，增加数据库压力

4. 索引的分类

这里以mysql数据库为例

1> 主键索引

顾名思义就是将表中的主键当作索引列, 数据库会自动再主键上添加索引

语句

#增加
alter table [表名] add primary key
#删除
alter table [表明] drop primary key

2>单值索引

可以给某一列添加索引, 就是最常用索引

语句

#添加
create index 索引名 on 表名(列名)
#删除
drop index 索引名 on 表明

如:create idnex admin_account_index on admin(account)

单值索引查询效率比主键索引性能低

原因: 主键索引的值在整个表中必须是唯一的，这使得数据库可以更快地定位到特定的行。相比之下，单值索引可能不保证唯一性，因此查询可能需要检查更多的行

3>唯一索引

唯一索引这里的唯一指的是这一列的纪录是唯一的, 可以想象成主键索引的低配版, 列里的值允许null

语句

create unique index on 表名(列名)
drop index 索引名 on 表名

唯一索引与前面例子类似,唯一就是要求

---

4>组合索引（复合索引）

●概念

组合索引可以想象为多个单值索引, 但是总体来说, 组合索引比多个单值索引的开销更小, 当行数远远大于列数时候, 可以适当的添加组合索引

语句

create index on 表名(列名,列名,列名)
drop index on 表明

●组合索引最左前缀原

假如有三个列abc,添加组合索引ab, 那么在将组合索引的列作为查询条件时, 必须要出现最左侧列, 不然组合索引不生效

演示
(应该叫组合索引, 图中口误🤣)

这是组合索引生效例子

组合索引失效

5>全文索引

由于mysql在模糊查询时候, 一般的索引都不生效, 所以有专门应对该问题产生了全文索引

语法

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH
create fulltext index 索引名 on 表明(列) preset

图示:

先使用单值索引, 索引列名name

再使用全文索引

注意：

在使用全文索引时,进行模糊查询语法稍微不一样
SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词’)

5. 索引的数据结构

索引数据结构是B+树的结构

为什么不能是二叉树?
因为数据库里的主键一般都是自增的, 而根据二叉树的性质知道, 此时的索引树将会向一边倾斜, 导致高度非常高(和链表一样), 反而查询效率更慢

为什么不能是红黑树?
红黑树虽然可以通过子旋转,但是还是会向一边来加元素, 问题同上

用B+树,非叶子结点上放数据的索引, B+树的性质刚好可以让一个非叶子结点放大量的索引;而将数据放到一个个的叶子结点里,所有叶子结点(数据之间有链指针)

这样可以在全表扫描时,查询某一索引时,快速锁定该索引, 再向下获取该索引对应数据
在sql范围查询时, 也可以通过链指针快速得到范围内的数据

图示:

6. 聚簇索引与非聚簇索引

●聚簇索引:

只在执行sql查询操作时, 可以通过索引直接获取数据, 比如InnoDB里的主键索引(一级索引)

聚簇是设计: InnoDB,索引与数据如下

●非聚簇索引:

指的是,索引存储与数据存储是分离的, 比如在Myisam引擎,
＞索引与数据关系如下

innodb与myisam虽然聚簇式设计与非聚簇式索引, 但是并不代表查询时候的索引都是聚簇索引与非聚簇索引,仅仅是根据两种思想设计的

● innodb反例:

如一个表 字段 a(主键),b,c,字段c(单值索引)

selelct b,c from test where c = 33
该sql通过c的索引树查询到的索引结点数据是主键id, 再需要通过主键id重新查询一下,该sql查询中, c索引就是非聚簇索引
● myisam反例:
例子同上
select id from test where id = 1
该种查询就直接查询到对应的索引结点,再通过索引结点直接获取内存里的数据物理地址

7. 回表查询

回表查询就是指:使用非聚簇索引查询数据时候，在获取到编号索引时，该索引节点存储的是主键id, 然后需要再通过主键id来再次查询到需要的数据

举个例子

-- 创建一个数据表
create table test(
id int not null primary key,
num int unique key 'test_num_index',
name varchar(30) not null
)

--查询
select num ,name from test where num = 2

在上面表结构中, id与num加了索引, 查询时候通过num查询了num与name, 由于name并没有加索引,所以获取到编号索引时,该索引编号下的节点存储的是主键id; 再通过主键id再次查询, 查询到对应的name

四、事务

●概述

事务是将一次操作的多条sql作为一个整体, 要么都成功, 要么都失败;
在mysql数据中,只有InnoDB的引擎才支持事务, 在做增删改操作时,我们就可以在有需求的情况下为其加上事务

●加事务的优点

1. 事务可以在一次操作中保证数据的完整性
2. 保证对数据操作的原子性

1. 事务特性

事务满足四个特性, 原子性(Atomiity),持久性(Durability),隔离性(Isalation), 一致性(Consistency) , 简称ACID

原子性: 保证在一次操作中的几条sql是一个整体, 哟啊么都成功,要么都失败
持久性: 指的是在事务提交之后,数据固定, 即使系统宕机也不会改变
隔离性: 在大并发量情况下, 由于线程交叉执行sql,会导致数据不一致问题

事务的隔离又分为四种级别:
读_未提交 ,读_已提交, 可重复读, 串行化

一致性: 前面三个性质就是来满足一致性, 保证数据完整可靠

2. mysql的事务配置方式

mysql里面默认是自动提交事务, 执行DML操作时, 就会提交事务(是一种隐式提交事务的方式)

1>自动提交方式

set global/session autocommit = 0  
0--禁止自动提交	1--表示开启自动提交
global--表示本地配置  session--表示是会话级别(即连接服务器)

2> 通过手动来实现事务

begin --开启事务
	sql内容
rollback --回滚操作
commit --提交操作

*3.事务隔离级别

mysql是服务器/客户端的软件, 我们在多个会话连接时, 这几个会话可能会对同时操作, 最终由mysql来处理多个事务, 这样就会导致多个事务访问到的数据一样; 可以在一个事务提交完成之后, 之后的事务再进行提交,但这样又使得再高并发情况下大大降低mysql性能, 所以提出事务隔离, 提出不同的处理方案, 叫做隔离级别

●读未提交

事务A查询操作时, 事务B对数据进行修改后还没有进行提交, 但是事务A可以查询到事务B修改的数据;
这种是又极大的安全漏洞, 会出现脏读,不可重复读, 幻读
并发量最高, 同时不安全

●读已提交

事务A查询操作, 事务B对数据进行修改之后, 且提交之后, 事务才可以查询到事务B修改后的内容跟
虽然解决了脏读问题, 但是还是存在不可重复读, 幻读问题

●可重复读

这是mysql数据库默认的事务隔离级别, 他根据事务的底层结构, 可以保证在一个事务中, 只要事务没有结束, 那么数据总是一致的,不会存在两次查询数据不一致问题, 解决了不可重复读的问题, 而且解决幻读问题(如果在查询后面加上for update 就会产生幻读问题)

●串行化

实际就是在一个事务执行完毕之后再后，后续的事务才可以继续执行
即使事务A再读, 事务B再写也不行
并发量最低

4.事务实现原理

mysql的InnoDB引擎中, 有两种日志文件, redo log(重做日志),undo log(回滚日志), 前者是实现事务持久性, 后者是隔离性实现的基本

●原子性:

实现原子性的基本就是要能实现对数据的回滚,撤销操作;
在undo log中, 会对每一次执行的sql进行纪录, 当需要回滚操作时, 就可以执行与纪录相反的操作来进行回滚
insert–delete update–update
●持久性:
redo log重做日志, 在执行操作时, 会将执行sql写入到redo log中, 当出现宕机, 因外界条件导致事务还没来得及提交,可以在下一次重启mysql时候, 再次将上次失败的事务进行提交, 保证数据安全

*5.事务级别实现的原理

MVCC(多版本并发控制), 是mysql提高事务执行性能的一中方式, 配合undo log与版本链对事务的 读写,进行并发执行

在每一行数据纪录中, 有两列来纪录数据的索引id 与 上一次的版本指针
在更新数据时候, 会把事务编号, 与旧的数据的undo log纪录的地址赋给该两列
每有数据进行更新时, 会旧数据写入到undo log日志文件中, 许多个旧数据的地址会被放入roll_pt列中, 并通过链表相连接, 每个版本链还包括了生成该版本事务的id
链表头就是最新的数据

▶1>读已提交

在每次读取时, 会每次获取版本链上生成快照(read view), 后去版本链的最新数据

▶2>可重复读

为了保证在一个事务的数据同一, 在事务第一次访问中, 会从版本链中生成快照, 在第二次查询中, 依然从数据快照中获取, 从而使得数据重复

五、锁机制

锁机制是为了保证两个事务的写操作, 隔离性要求只能由一个写操作
锁机制原理:主要是事务在对数据进行修改之前，先后去对应数据的锁，在其间其他事务不能对该数据修改，　除非该事务进行事务回滚或者事务提交

1. 行锁

行锁指对本行数据进行加锁, 可以大大减小数据库操作的冲突, 但是加锁开销大, 发生锁冲突的概率最小,并发量最高,锁粒度最小
分共享锁 排他锁

2. 间隙锁

指在条件是一个范围,而不是一个固定的值, 数据库引擎会对该条件里的所有数据进行加锁,

3. 表锁

给整个表加锁;innodb与myisam都是支持给表加锁, myisam中是默认给表加锁
分共享锁 排他锁

●共享锁:

读锁, 事务操作后去共享锁之后, 其他事务不能从该数据上获取排他锁
比如事务A给数据对象加了共享锁, 其他事务只能给数据对象加共享锁(读取数据),不能加排他锁
加共享锁可以使用 select … lock in share mode 语

●排他锁

写锁, 在给事务加排他锁时, 其他事务不能再给该事务加共享锁与排他锁, 即其他数据必须等待该事务的回滚或者提交
如果加排他锁可以使用 select …for

增删改是默认给数据加上排他锁, 查询不加任何锁

*六、sql优化

1. 查询 SQL 尽量不要使用 select *，而是具体字段
节省资源、减少开销。
2. 避免在 where 子句中使用 or 来连接条件
反例:SELECT * FROM user WHERE id=1 OR salary=5000
正例:使用 union all 把两个两个 SQL 结果合并
使用 or 可能会使索引失效，从而全表扫描；
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary
查询条件时，它还得全表扫描；
3. 尽量使用数值替代字符串类型
正例
主键（id）：primary key 优先使用数值类型 int
性别（gender）：0 代表女，1 代表男；数据库没有布尔类型，mysql
推荐使用 tinyint
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
而对于数字型而言只需要比较一次就够了；
字符会降低查询和连接的性能，并会增加存储开销；
4. 使用 varchar 代替 char
varchar 变长字段按数据内容实际长度存储，可以节省存储空间；
char 按声明大小存储，不足补空格；
其次对于查询来说，在一个相对较小的字段内搜索，效率更高；
5. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by
group by 涉及的列上建立索引
6. 应尽量避免索引失效
6.1 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引
而进行全表扫描，如:select id from t where num=10 or num=20
6.2 in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num
in(1,2,3),对于连续的数值，能用 between 就不要用 in ,select id from t where
num between 1 and 3
6.3 模糊查询也将导致全表扫描
select id from t where name like '%abc%' 6.4 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进
行全表扫描。如: select id from t where substring(name,1,3)='abc' 
7. 提高 group by 语句的效率
反例:先分组，再过滤
正例:先过滤，后分组
8. 清空表时优先使用 truncate
truncate table 比 delete 速度快，且使用的系统和事务日志资源少. delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate
table 通过释放存储表数据所用的数据页来删除数据. 9. 表连接不宜太多，索引不宜太多，一般 5 个以内
联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
9. 深度分页问题
反例
select id,name from account limit 100000,10;
正例
select id,name FROM account where id > 100000 order by id limit 10;
10. 使用 explain 分析 SQL 执行计划