MySQL事务、索引、视图、DBA和数据库设计三范式

晚风低吟浅

已于 2023-08-08 15:46:52 修改

阅读量56

点赞数

文章标签：数据库 mysql dba

于 2023-08-08 01:34:46 首次发布

本文链接：https://blog.csdn.net/m0_59023564/article/details/132138071

版权

1、事务(Transaction)

什么是事务?
一个事务是一个完整的业务逻辑单元，不可再分。
比如:银行账户转账，从A账户向B账户转账10000.需要执行两条update语句:

update t act set balance = balance - 10000 where actno = 'act-001' ;
update t act set balance = balance + 10000 where actno = 'act-002' ;

以上两条DM语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
奥想保证职王的需条中落句同雨成习取看同时天败，那么就需要使用数据库的“事务机制”。

和事务相关的语句只有: DM语句。(insert delete update )
为什么?因为它们这三个语句都是和数据库表当中的"数据"相关的。
事务的存在是为了保护数据的完整性，安全性。
事务的特性
事务包括四大特性：ACID
A:原子性：事务是最小的工作单元，不可再分。
C:一致性：事务必须保证多条DML语句同时成功或者同时失败
I: 隔离性：事务A和事务B必须要具有隔离。
D:持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功。
关于事务之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括4个:
第一个级别：读未提交（read uncommitted）
对方的事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读取未提交的数据存在脏读现象：表示读到了脏的数据。

第二个级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了：脏读现象没有了
读已提交存在的问题是：不可重复读。
已知有两个事务A和B，A 多次读取同一数据，B 在A多次读取的过程中对数据作了修改并提交，导致A多次读取同一数据时，结果不一致

场景：Tom拿着工资卡去消费，酒足饭饱后在收银台买单，服务员告诉他本次消费1000元，Tom将银行卡给服务员，服务员将银行卡插入POS机，POS机读到卡里余额为3000元，就在Tom磨磨蹭蹭输入密码时，他老婆以迅雷不及掩耳盗铃之势把Tom工资卡的3000元转到自己账户并提交了事务，当Tom输完密码并点击“确认”按钮后，POS机检查到Tom的工资卡已经没有钱，扣款失败，Tom十分纳闷，明明卡里有钱，于是怀疑POS有鬼，和收银小姐姐大打出手，300回合之后终因伤势过重而与世长辞，Tom老婆痛不欲生，郁郁寡欢，从此走上了不归路…
分析：上述情况即为不可重复读，两个并发的事务，“事务A：POS机扣款”、“事务B：Tom的老婆网上转账”

 第三个级别：可重复读（repeatable read）
	  这种隔离级别解决了：不可重复读的问题
	  这种隔离级别存在的问题是：读取到的数据是幻想，即为幻读
	  
第四个级别：序列化读/串行读
解决了所有的问题。
但是效率低，需要事务排队。

oracle数据库默认的隔离级别是:读已提交。
mysq1数据库默认的隔离级别是:可重复读。
3.6、演示事务
★mysq1事务默认情况下是自动提交的。
(什么是自动提交?只要执行任意一条DM语句则提交一次。)怎么关闭自动提交?

start transaction;

*准备表：
drop table if exists t_user;
create table t_user(
		id int primary key auto_increment,
		username varchar(255)
);

第一:演示read uncommitted
设置事务的全局隔离级别:

set global transaction isolation level read uncommitted ;

查看事务的全局隔离级别:

mysq1> select @@global. tx isolation;

2、索引

2.1、什么是索引?有什么用?
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式:
第一种方式:全表扫描
第二种方式:根据索引检索(效率很高)
索引为什么可以提高检索效率呢?
其实最根本的原理是缩小了扫描的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中
的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改
这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段，或者说某些字段添加索引。

select ename,sal from emp where ename = ' SMITH' ;

当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

2.2、怎么创建索引对象?怎么删除索引对象?

创建索引对象:
create index 索引名称 on 表名(字段名) ;
删除索引对象:
drop index  索引名称 on 表名;

2.3、什么时候考虑给字段添加索引? (满足什么条件)
★数据量庞大。(根据客户的需求，根据线上的环境)
★该字段很少的DM操作。(因为字段进行修改操作，索引也需要维护)
★该字段经常出现在where子句中。(经常根据哪个字段查询)
2.4、注意:主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高。尽量根据主键检索。
2.5、查看sql语句的执行计划:

mysq1> explain select ename,sal from emp where sal = 5000;

2.6、索引底层采用的数据结构是:
B+ Tree
2.7、索引的实现原理?
通过BTree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的\物理地址",
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率
是最高的。
select ename from emp where ename = ’ SMITH’ ;
通过索引转换为:
select ename from emp where物理地址= 0x3;
在这里插入图片描述
2.8、索引的分类?
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加1个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段上会自动添加索引
2.9、索引什么时候失效?
select ename from emp where ename like ‘%A%’ ;
模糊查询的时候，第一一个通配符使用的是%，这个时候索引是失效的。

3、视图(view)

3.1、什么是视图?
站在不同的角度去看到数据。(同一张表的数据，通过不同的角度去看待)
3.2、怎么创建视图?怎么删除视图?

create view myview as select empno , ename from emp;
drop view myview;

注意:只有DQI语句才能以视图对象的方式创建出来。

3.3、对视图进行增删改查，会影响到原表数据。(通过视图影响原表数据的，不是直接操作的原表)
可以对视图进行CRUD操作。
3.4、面向视图操作?
mysql> select * from mypiew;
3.5、视图的作用?
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员
只对视图对象进行CRUD。

4、DBA命令

4.1、将数据库当中的数据导出
在windows的dos命令窗口中执行:
(导出整个库)
mysqldump bjpowernode>D: \bjpowernode.sql -uroot -p333
在windows的dos命令窗口中执行:
(导出指定数据库当中的指定表)
mysqldump bjpowernode emp>D: \bjpowernode.sql -uroot -p123
4.2、导入数据
create database bjpowernode;
use bjpowernode ;
source D: \bjpowernode.sql