• MySQL 事务、视图、索引


      一、事务(Transaction)

      1.1 什么是事务?

      SQL中,事务是指将一系列数据操作捆绑成为一个整体进行统一管理。

      如果一个事务执行成功,该事务中进行的所有数据均会提交,称为数据库中的永久组成部分;

      如果事务执行时遇到错误切必须取消or回滚,则数据将全部恢复到操作前的状态,所有更改会被清除。

      在数据库系统上执行并发操作时,事务是作为最小的控制单元来使用的,特别适用于多用户同时操作的数据库系统。

     

      1.2 事务的ACID属性

      原子性(Atomicity):事务中的所有元素必须作为一个整体提交或回滚。

      一致性(Consistency):事务完成时,数据必须处于一致状态。在事务进行中,可能会不一致。如转账操作中的金额总量。

      隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的,即事务是独立的。

      持久性(Durability):指不管系统是否发生了故障,事务处理的结果都是永久的。就是说一旦事务被提交,事务的效果会被永久地保留在数据库中。

     

      1.3 如何执行事务

      MySQL的默认存储引擎InnoDB支持事务,InnoDB主要通过UNDO和REDO日志实现

      UNDO日志:复制事务执行前的数据,用于在事务发生异常时回滚数据;

      REDO日志:记录在事务执行中,每条对数据进行更新的操作,当事务提交时,内容会一起被刷新到磁盘。

     

      1.4  事务的执行过程

      MySQL的默认设置下,每句SQL语句就是一个事务,即执行SQL语句后自动提交。

      (1)开始时,开启事务 or 关闭默认的自动提交:

      begin; 
      /*或者*/
      start transaction;

      这两个语句显示地标记一个事务的起始点.

      (2)提交事务

      commit;

      这个语句标志一个事务会成功提交,数据保存到数据库的数据文件中,并释放连接时占用的资源。

      (3)回滚事务

        rollback

      取消自事务起始点到该语句的所有数据更新操作,将数据状态回滚到事务开始前,并释放事务控制的资源。

    /*银行转账实例*/
    use mybank;
    set names gbk;
    begin;
    update bank set currentMoney=currentMoney-500
         where customerName='张三';
    /*如果在这里发现张三的账户中余额不足500,就需要rollback*/
    update bank set currentMoney=currentMoney+500
         where customerName='李四';
    commit;

     

      1.5 MySQL中事务的隔离级别

      MySQL中事务的默认隔离级别是repeatable-read(可重读)级别,意味着会话B中如果未关闭自动提交,在会话A中执行事务未结束时(没有commit或者rollback),会话B只能读取到未提交的数据。

      1.6 通过关闭自动提交执行事务

    /*关闭自动提交*/
    use mybank;
    set names gbk;
    set autocommit=0;
    /*一个事务开始,假设操作没有问题*/
    update bank set currentMoney=currentMoney-500
         where customerName='张三';
    update bank set currentMoney=currentMoney+500
         where customerName='李四';
    commit;
    /*一个事务已经结束,这是下一个事务的起点*/
    
    /*假设新的事务中,出现了问题,张三余额不足啦!*/
    update bank set currentMoney=currentMoney-500
         where customerName='张三';
    rollback;
    
    set autocommit=1;

     

      1.7 编写事务的原则

    • 事务尽可能简短,较大的事务占用的资源很多;
    • 事务中访问的数据量尽量少,这样事务之间对资源的争夺就少;
    • 查询数据不要使用事务;
    • 事务处理的过程中尽量不要出现等待用户输入的操作。

     

      二、视图

      2.1 为什么使用视图?

      安全——不希望用户看到整个数据库的结构,所以对不同权限的用户使用不同的视图隐藏必要数据;

      方便——让用户更容易获得、理解数据;

     

      2.2 什么是视图?

      视图是一种查看数据库中一个或多个表中数据的方法,是保存在数据库中的select查询。

      视图是一种虚拟表,它的行和列来自查询中引用的表,在执行时,它直接显示来自表中的数据。

     

      2.3 视图常用来进行下面三种操作

    • 筛选表中的行;
    • 防止未经许可的用户访问敏感数据;
    • 将多个物理数据表,抽象为一个逻辑数据表。

      

      2.4 创建、查看视图

    /*创建视图*/
    create view view_Name as <select子句>;
    
    /*如果数据库中已经存在同名视图,先删除*/
    drop view if exists view_student;
    create view view_student as 
        select studentNo,studentName,phone from student;
    
    /*查看视图*/
    select * from view_student;

     

      2.5 使用视图的注意事项

    • 每个视图中可以使用多个表
    • 与查询相似,一个视图可以嵌套另一个视图,但最好不要超过三层;
    • 对视图数据进行添加、更新和删除操作直接影响表中的数据;
    • 当数据来自多个表时,不允许添加和删除数据。

     

      三、索引——大大提高数据库的检索速度

      3.1 什么是索引

      索引提供指针以指向存储在表中指定列的数据值,再根据指定的排序次序排列这些指针。

      数据库使用指针的方式与使用书的目录很相似,通过搜索索引找到特定的值,再跟随指针到达包含该值的行。

      索引是创建在数据库表对象上的,由表中的一个字段 or 多个字段生成的键组成。

      这些键存储在数据结构(B-树或哈希表)中,因此根据索引的存储类型,B树索引和哈希索引。

      InnoDB和MyISAM存储引擎支持B-树索引。

     

      3.2 索引分类

      (1)普通索引

      允许在定义索引的列中插入重复值和空值,唯一任务是加快对数据的访问速度。

      因此,应该只为那些最常出现在查询条件或排序条件中的数据列创建索引。

      (2)唯一索引

      唯一索引不允许两行具有相同的索引值。

      若数据中存在重复的键值,一般情况下,所属数据库是不允许创建唯一索引;

      若已经创建了唯一索引,当新数据使得表中的键值重复时,数据库也拒绝接受此数据。

      创建了唯一索引的列允许有空值。

      (3)主键索引

      在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。

      主键索引要求主键中的每个值是非空、唯一的,当在查询中使用主键索引时,它还允许快速访问数据。

      (4)复合索引

      创建索引时,可以将多个列组合作为索引。

      需要注意的是,只有在查询中使用了组合索引最左边的字段时,索引才会被使用。

      (5)全文索引

      全文索引的作用是在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。

      全文索引可以在char、varchar或text类型的列上创建,主要用于在大量文本文字中搜索字符串,此时使用全文索引的效率将大大高于使用SQL的like关键字的效率。

      (6)空间索引

      对空间数据类型的列建立的索引,如geometry、point等。

      创建空间索引的列,必须为not null,且只能在存储引擎为MyISAM的表中创建。

     

      3.3 创建、删除索引

      在已存在的表上添加索引的基本语法:

    /*创建索引*/
    /*length为索引长度,只有字符串类型才能指定索引长度*/
    create [unique|fulltext|spatial|] index index_name
        on tableName(colum_Name[length]...);
    
    /*删除索引*/
    drop index tableName.index_name;

      删除表,该表的所有索引将同时被删除;

      删除表中的列,如果要删除的列为索引的组成部分,则该列也会从索引中删除;

      如果组成索引的列都被删除,则整个索引都将被删除。

     

      3.4  选择建立索引的列

    • 频繁搜索的列;
    • 经常用作查询的列;
    • 经常排序、分组的列;
    • 经常用作连接的列(主键/外键)

      不要使用下面的列创建索引:

    • 仅包含几个不同值的列;
    • 表中仅包含几行,因为为小型表创建索引并不实用;

     

      3.5 一点经验

      查询时减少使用*返回全部列,不要返回不需要的列;

      索引应该尽量小,在字节数小的列上建立索引;

      where子句中有多个条件表达式时,包含索引的表达式应置于其他表达式之前;

      避免在order by子句中使用表达式;

      根据业务数据发生频率,定期重新生成或重新组织索引,进行碎片整理。

     

      3.6 查看索引

    /*查看已创建的索引*/
    show index from tableName;
    
    /*查看学生表中的索引*/
    show index from `student`G;

      在查询语句后面加“G”,表示结果将按列排列。当表中列较多,需要看表中各列中的值时非常有用。

      在存储类型为InnoDB的表中,经常使用唯一索引、普通索引、组合索引来提高查询效率。

     

  • 相关阅读:
    单精度和双精度
    @Transactional注解用法
    JPA No EntityManager with actual transaction available for current thread
    上传文件Request Entity Too Large解决办法
    PG数据库查看当前会话和结束会话
    Chrome浏览器记不住密码也不提示保存密码win10
    ARM平台VMP保护开发入门
    关于我
    HDU7072:Boring data structure problem——题解
    HDU7067:Just another board game——题解
  • 原文地址:https://www.cnblogs.com/bigbigbigo/p/8415976.html
Copyright © 2020-2023  润新知