事务、索引、数据库三范式 - MySQL 6

最新推荐文章于 2023-05-17 11:38:19 发布

梦世

最新推荐文章于 2023-05-17 11:38:19 发布

阅读量185

点赞数

分类专栏： # MySQL基础

本文链接：https://blog.csdn.net/qq_34949782/article/details/106447843

版权

MySQL基础专栏收录该内容

7 篇文章 1 订阅

订阅专栏

存储引擎

数据库中的各表均被（在创建表时）指定的存储引擎（表的存储方式）来处理。在创建表的时候可以指定存储引擎也可以指定字符集，mysql默认使用的存储引擎是InnoDB。注：在oracle中也存在相同的机制：表的存储方式。

mysql中的存储引擎分类：（查看当前MySQL支持的存储方式：show engines \G）

*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.07 sec)

常见的存储引擎：

MyISAM（不支持事务）

      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO

InnoDB：支持事务、行级锁、外键等

      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES

MEMORY：

事务

事务的概念

一个事务（Transaction）是一个完整的业务逻辑单元，不可再分。例如：银行转账，从A账户向B账户10000，则需要执行两条update语句，这两条DML语句必须要求同时成功或者同时失败，这样就需要使用数据库的”事务机制“。事务的存在是为了保证数据的完整性与安全性，只有DML语句才支持事务：insert、delete、update（这三个语句都是和数据表中的数据相关的）。假设所有的业务只需要一条DML语句就能搞定，这时不需要事务，现实中这种情况不常见。有三个命令：

commit（提交）：将上句代码的执行结果，持久化到硬盘之中；
rollback（回滚）：语句执行后未提交，可以使用回滚到上次的提交点之后；
savepotint（保存点）：回滚的位置（不常用）；

事务是怎么控制安全性？假设一条事务：insert -> update -> delete，如下：

事务的特性

CAID

事务包括四大特性：CAID

A : 原子性 —— 事务是最小的工作单元不可再分；
C：一致性 —— 事务必须保证必须同时成功或同时失败；
I：隔离性 —— 事务A与事务B之间具有隔离；
D：持久性 —— 最终数据必须持久化到硬盘文件中，事务才算成功结束；

事务的隔离性

事务隔离性存在隔离级别，隔离级别包括4个：

第一级别：读未提交（read uncommitted），对方事务还未提交的数据我们能够读到对方未提交的数据，这样存在脏读（Dirty Read）现象 : 表示读到了脏数据（不稳定的在内存中）；
第二级别：读已提交（read commited），这种隔离级别解决了脏读现象。对方事务提交后的数据我们当前事务可以读取到，这样存在不可重复读的问题，在一段时间内数据可能发生改变，读到都是最新的数据，重复读结果不一样。
第三级别：可重复读（repeatable read），这种隔离级别解决了不可重复读问题。存在读取到的数据是幻象的（幻读）。比如：有人已经删除了数据，但当前事务还是读取到完整数据，这里存在备份。
第四级别：序列化读/串行化读，解决了所有问题。需要事务排队，效率低。

oracle数据库默认的隔离级别是读已提交，MySQL默认的隔离级别是可重复读。通过以下方式操作隔离级别：

设置全局的隔离级别：set global transaction isolation level 隔离级别（如：read uncommitted）;
查看全局隔离级别：select @@global.tx_isolation;

注意每次设置完需要退出重进；

MySQL事务在默认情况下是默认提交的，即：只要执行任意一条DML语句则提交一次，关闭自动提交的命令：

start transanction

索引

索引概述

我们知每本书都有自己的目录，通过目录可以很快速的找到内容；在数据库方面，查询一张表有两种查询方式：

全表扫描
根据索引检索

添加索引是给某一个字段或某些添加，并且主键和具有unique的字段会自动添加索引，显然根据主键检索效率更高。

创建索引对象：creat index 索引名（建议使用：表名_字段_index命名） on 表名（字段名）；
删除索引对象：drop index 索引名 on 表名；注;oracle后面没有on 表名；
查看语句的执行情况：explain 语句，结果中type表示扫描方式：ALL 、ref。

需要注意的是索引虽然可以提高检索效率，但是不能随意的添加索引，因为在数据库中索引也是对象，需要数据库进行维护。比如：表中数据经常改就不适合添加索引，因为数据一旦修改，索引需要重新排序。在下述情况下可以考虑给字段添加索引：

数据量庞大；（根据客户的需求与线上的环境）
该字段很少进行DML操作；
该字段经常出现在where后面；

索引分类：

单一索引：给单个字段添加索引；
复合索引：给多个字段联合起来添加索引；
主键索引：主键上回自动添加索引；
唯一索引：有unique约束的字段会自动添加索引；
......

索引情况：模糊查询的时候第一个通配符使用的是%

实现原理

索引底层采用的数据结构是B+ tree，通过B tree缩小了扫描范围，底层索引进行了排序分区，索引会携带表中的“物理地址”，最终通过索引检索到数据之后获得物理地址，然后根据物理地址找到数据。

视图

所以的视图（view）就是站在不同的角度去看待数据，同一张表的数据站在不同的角度去看，视图通过下面两个语句操作：

创建视图：create view 视图名（如：myview） as 查询结果（DQL）；
删除视图：drop view 视图名；

只有DQL语句才能以视图对象的方式创建出来，即as后面跟select语句，但是需要注意的是对视图进行增删改会影响原表数据。

范例：视图

create table emp1 as select * from emp;
create view myview as select empno,ename,sal from emp1;

update myview set ename='hehe',sal=0.1 where empno=7369;

通过视图修改原表数据

显然我们需要考虑视图的作用，比如，对于保密工作较高的通过视图操作。

DBA命令

在这部分需要知道两个命令：

数据库中的数据导出（在dos命令窗口中执行，别进）：mysqldump 数据库名表名（省略表示导库） > 输出路径 -uroot -p 密码；
导入数据：创建数据库 -> 使用数据库 - > source;

数据库设计三范式（重点）

按照三范式设计的表不会出现数据冗余。三范式包括以下内容：

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：所有非主键字段完全依赖主键，不能产生部分依赖，即没有复合主键。经验：多对多，三张表：关系表两个外键
第三范式：所有非主键字段直接依赖主键，不能产生传递依赖。经验：一对多，多的表加外键。

一对一设计，具有两种方案：

第一种：主键共享

第二种：外键唯一

范例：多对多

范例：一对多

在实际的开发中满足客户的需求为主，有的时候会拿冗余换速度。

梦世

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
事务、索引、数据库三范式 - MySQL 6

目录存储引擎事务事务的概念事务的特性CAID事务的隔离性索引索引概述实现原理视图DBA命令数据库设计三范式（重点）存储引擎数据库中的各表均被（在创建表时）指定的存储引擎（表的存储方式）来处理。在创建表的时候可以指定存储引擎也可以指定字符集，mysql默认使用的存储引擎是InnoDB。注：在oracle中也存在相同的机制：表的存储方式。mysql中的存储引擎分类：（查看当前MySQL支持的存储方式：show engines \G）*******
复制链接

扫一扫