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

  • Post author:
  • Post category:mysql



目录


存储引擎


事务


事务的概念


事务的特性


CAID


事务的隔离性


索引


索引概述


实现原理


视图


DBA命令


数据库设计三范式(重点)


存储引擎

数据库中的各表均被(在创建表时)指定的存储引擎(表的存储方式)来处理。在创建表的时候可以指定存储引擎也可以指定字符集,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;

数据库设计三范式(重点)

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

  • 第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。

  • 第二范式:所有非主键字段完全依赖主键,不能产生部分依赖,即没有复合主键。经验:

    多对多

    ,三张表:关系表两个外键

  • 第三范式:所有非主键字段直接依赖主键,不能产生传递依赖。经验:

    一对多

    ,多的表加外键。


一对一

设计,具有两种方案:

第一种:主键共享

第二种:外键唯一

范例:多对多

范例:一对多

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



版权声明:本文为qq_34949782原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。