精华内容
下载资源
问答
  • 2020-06-21 20:16:50

    Mysql数据库事务的四大特性

    事务:把一组密不可分的操作系列集合在一起,这些操作要么全部执行,要么全部不执行。
    1.原子性:事务是内定义的操作是一个整体,是不可分割的。
    2.一致性:同一个事务,多次读取数据库中的同一个数据,读取的内容应该是一致的,不变的。
    3.隔离性:不同事务之间相互独立,互不干扰。
    4.持久性:事务提交后,事务内的操作对数据库的修改被永久保存在数据库文件中。


    事务的开启

    Mysql的事务默认为自动提交,每执行一条DML语句,Mysql数据库会自动提交一次事务。
    将Mysql数据库事务提交方式修改为手动提交有两种方式:
    1、start transaction (手动开启一个事务,即关闭数据库事务的自动提交,每次事务提交后,事务自动提交又会自动打开,即每次都需要手动开启)
    2、set autocommit = off;(将当前会话的事务设置为手动提交,每次事务结束都需要commit或者rollback,下个事务不需要再次手动开启)
    事务提交或回滚(TCL语句)事务控制语句
    1、commit
    2、rollback


    事务的隔离性有四个级别

    1 读未提交 read uncommitted 一个事务可以读到别的事务尚未提交的数据,出现的问题是读到的数据为脏数据,称为脏读。
    2 读已提交 read committed 一个事务读不到别的事务还没提交的数据,可以读到已经提交的事务。出现的问题是不可重复复,即同一个事务每次读取的数据会不一致。
    3 可重复读 repeatable read 事务开启后,同一个事务每次读到的数据是一致的,就算别的事务提交了新的数据也是读取不到的,这就造成了读取的数据有可能跟数据库的数据不相同,这种方式叫做幻读。
    4 串行化 seriaziable 一个事务的执行,需要等待上一个事务执行完成。正在执行的事务会把当前表锁住(表级别的锁),别的事务无法访问当前的表。出现的问题,数据库的执行效率低。

    更多相关内容
  • 课程目录MySQL5.7版本新特性1-1初始化方式变更.mp41-2初始化方式变更实战演练.mp41-3旧版本支持为表增加计算列演练.mp41-4MySQL5.7支持为表增加计算列实际演练.mp41-5引入JSON列类型及相关函数.mp42-1支持多源复制....
  • MySQL 5.7新特性介绍

    2020-09-10 02:07:17
    主要为大家详细介绍了MySQL 5.7新特性,了解一下MySQL 5.7的部分新功能,需要的朋友可以参考下
  • 本文将首先介绍MySQL事务相关的基础概念,然后介绍事务的ACID特性,并分析其实现原理。MySQL博大精深,文章疏漏之处在所难免,欢迎批评指正。事务(Transaction)是访问和更新数据库的程序执行单元;事务中可能包含...
  • MySQL 8.0的新特性简述

    2021-08-30 14:27:30
    别找了,你要的MySQL 8.0的新特性已经整理好了,安心准备面试吧!!!!
  • 主要介绍了MySQL8.0.11版本的新增特性介绍,非常不错,具有参考借鉴价值,需要的朋友可以参考下
  • MySQL5.7新特性介绍

    2021-02-01 12:10:18
    身处MySQL这个圈子,能够切身地感受到大家对MySQL5.7的期待和热情,似乎每个人都迫不及待的想要了解、学习和使用MySQL 5.7。那么,我们不禁要问,MySQL5.7到底做了哪些改进,引入了哪些新功能,性能又提升了多少,...
  • 官方发布的mysql8.0新特性pdf
  • MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本,带来了一些新特性和增强功能。这篇文章主要介绍了MySQL 8.0 新特性之哈希连接(Hash Join),需要的朋友可以参考下
  • MySQL高级特性

    2017-06-14 21:44:38
    MySQL高级特性,尚硅谷的教学笔记
  • 官方发布的mysql8.0新特性PPT,官方发布的mysql8.0新特性PPT,官方发布的mysql8.0新特性PPT
  • 5.7版本的用户表mysql.user要求plugin字段非空,且默认值是mysql_native_password认证插件,并且不再支持mysql_old_password认证插件。5.7用户长度最大为32字节,之前最大长度为16字节,并且CREATE USER 和 DROP ...
  • 一.四大特性1.事务的概念事务(Transaction)指的是一个操作序列,...目前常用的存储引擎有InnoDB(MySQL5.5以后默认的存储引擎)和MyISAM(MySQL5.5之前默认的存储引擎),其中InnoDB支持事务处理机制,而MyISAM不支持。...

    一.四大特性

    1.事务的概念

    事务(Transaction)指的是一个操作序列,该操作序列中的多个操作要么都做,要么都不做,是一个不可分割的工作单位,是数据库环境中的逻辑工作单位,由DBMS中的事务管理子系统负责事务的处理。

    目前常用的存储引擎有InnoDB(MySQL5.5以后默认的存储引擎)和MyISAM(MySQL5.5之前默认的存储引擎),其中InnoDB支持事务处理机制,而MyISAM不支持。

    2. 事务的特性

    事务处理可以确保除非事务性序列内的所有操作都成功完成,否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列,可以简化错误恢复并使应用程序更加可靠。

    但并不是所有的操作序列都可以称为事务,这是因为一个操作序列要成为事务,必须满足事务的原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。这四个特性简称为ACID特性。

    bdf617759d8c345b1a8428202437c083.png

    1.原子性:

    原子是自然界最小的颗粒,具有不可再分的特性。事务中的所有操作可以看做一个原子,事务是应用中不可再分的最小的逻辑执行体。

    使用事务对数据进行修改的操作序列,要么全部执行,要么全不执行。通常,某个事务中的操作都具有共同的目标,并且是相互依赖的。如果数据库系统只执行这些操作中的一部分,则可能会破坏事务的总体目标,而原子性消除了系统只处理部分操作的可能性。

    2.一致性:

    一致性是指事务执行的结果必须使数据库从一个一致性状态,变到另一个一致性状态。当数据库中只包含事务成功提交的结果时,数据库处于一致性状态。一致性是通过原子性来保证的。

    例如:在转账时,只有保证转出和转入的金额一致才能构成事务。也就是说事务发生前和发生后,数据的总额依然匹配。

    3.隔离性:

    隔离性是指各个事务的执行互不干扰,任意一个事务的内部操作对其他并发的事务,都是隔离的。也就是说:并发执行的事务之间既不能看到对方的中间状态,也不能相互影响。

    例如:在转账时,只有当A账户中的转出和B账户中转入操作都执行成功后才能看到A账户中的金额减少以及B账户中的金额增多。并且其他的事务对于转账操作的事务是不能产生任何影响的。

    4.持久性:

    持久性指事务一旦提交,对数据所做的任何改变,都要记录到永久存储器中,通常是保存进物理数据库,即使数据库出现故障,提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障,如硬盘被损坏,那么之前提交的数据则有可能会丢失。

    二.事务的隔离级别

    事务的并发问题

    脏读(Dirty read)

    : 当一个事务正在访问数据并且对数据进行了修改,而这种修改还没有提交到数据库中,这时另外一个事务也访问了这个数据,然后使用了这个数据。因为这个数据是还没有提交的数据,那么另外一个事务读到的这个数据是“脏数据”,依据“脏数据”所做的操作可能是不正确的。

    6fd9f32586e3e89e64fc0861d7bf423d.png

    不可重复读(Unrepeatableread):

    指在一个事务内多次读同一数据。在这个事务还没有结束时,另一个事务也访问该数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。

    640bcc9424b6d4c352224ea5dc2f6b3f.png

    幻读(Phantom read):

    幻读与不可重复读类似。它发生在一个事务(T1)读取了几行数据,接着另一个并发事务(T2)插入了一些数据时。在随后的查询中,第一个事务(T1)就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。

    b3ff375a9177153c58405e457e5651a3.png

    不可重复度和幻读区别:

    不可重复读的重点是修改,幻读的重点在于新增或者删除。

    解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

    例1(同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ):事务1中的A先生读取自己的工资为 1000的操作还没完成,事务2中的B先生就修改了A的工资为2000,导 致A再读自己的工资时工资变为 2000;这就是不可重复读。

    例2(同样的条件, 第1次和第2次读出来的记录数不一样 ):假某工资单表中工资大于3000的有4人,事务1读取了所有工资大于3000的人,共查到4条记录,这时事务2 又插入了一条工资大于3000的记录,事务1再次读取时查到的记录就变为了5条,这样就导致了幻读

    事务的隔离级别

    事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的,如果多个用户同时开启事务并对同一数据进行读写操作的话,有可能会出现脏读、不可重复读和幻读问题,所以MySQL中提供了四种隔离级别来解决上述问题。

    事务的隔离级别从低到高依次为 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE,隔离级别越低,越能支持高并发的数据库操作。

    79bd5ba411880be6a20c83b3b39f1c1b.png

    2.在spring注解开发中,使用isolation 表示事务的隔离级别

    展开全文
  • 事务的四大特性3. 事务的使用4. 小结 事务 学习目标 能够知道事务的四大特性 1. 事务的介绍 事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行,要么完全地都不执行, 它是一个不可分割的工作...
  • 高性能mysql读书笔记四-MySQL高级特性

    万次阅读 2022-03-24 11:46:18
    本篇博文是高性能Mysql一书中 mysql高级特性分析。主要介绍了分区表,视图、存储代码三大模块,详细内容包含:分区表概念,优劣,类型,原理、使用、示例等, 视图的概念,示例,存储代码主要包含 触发器、事件、存储...

    mysql高级特性

    本篇博客有关mysql高级特性描述均在mysql版本 5.7上使用验证。

    1. 分区表

    1.1 分区表概念

    ​ 分区表就是按照某种规则将一个大表分成若干个小表,规则可能是某一个列的值分区分,或者涉及某个列的表达式。这样大量的数据被分散到不同表中,数据量变小查询性能自然变高。

    使用分区表的好处

    • 表数据非常大或者表数据有明显的冷热数据之分。
    • 分区表的数据更容易维护,比如批量删除 可以清除整个分区形式并对独立分区进行优化、检查、修复。
    • 分区表的数据可以维护在不同的物理设备,水平扩展性好。
    • 避免InnoDB的单索引互斥访问,减少锁竞争。

    使用分区表的限制

    • 所有分区必须使用相同的存储引擎。

    • 每张表的分区数有限,最大分区数目不能超过1024。

    • 分区表达式只能是整数或者返回整数的表达式(函数)。

    • 分区所用的字段,必须是唯一索引或者主键(主键也是唯一索引)。

    • 无法使用外键、全文索引。

    1.2 分区表类型

    查看mysql是否支持分区

    #5.6版本
    SHOW VARIABLES LIKE '%partition%'
    
    # 5.6版本之后
    show plugins;
    

    在这里插入图片描述

    • RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。

    • LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

    • HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

    • KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

    1.3 分区表使用

    range分区类型

    创建分区表

    -- 创建表
    DROP TABLE IF EXISTS `sales`;
    CREATE TABLE `sales` (
      `id` int(11) NOT NULL,
      `money` decimal(11,2) NOT NULL,
      `order_date` date NOT NULL,
      PRIMARY KEY (`id`,`order_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
     PARTITION BY RANGE (YEAR(order_date))
    (PARTITION p_2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
     PARTITION p_2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
     PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
    

    使用日期范围range 进行分区。,order_date 为分区字段 则必须包含在主键字段内,否则会报如下错误

    A PRIMARY KEY must include all columns in the table's partitioning function

    对于 MySQL 分区表,无法从数据库层面保证非分区列在表级别的唯一性,只能确保其在分区内的唯一性。

    插入数据

    -- 插入数据 
    INSERT INTO `sales` (`id`, `money`, `order_date`) VALUES('1','123.34','2021-12-01');
    INSERT INTO `sales` (`id`, `money`, `order_date`) VALUES('2','12132.33','2022-01-15');
    INSERT INTO `sales` (`id`, `money`, `order_date`) VALUES('3','32432.31','2020-03-22');
    

    查询分区统计

     -- 查询数据分区分布
    SELECT 
      PARTITION_NAME AS "分区",TABLE_ROWS AS "行数"
    FROM
      information_schema.partitions 
    WHERE table_schema = "cds-temp" 
     AND table_name = "sales"
    

    在这里插入图片描述

    list分区类型

    创建分区表

    -- 创建表
    DROP TABLE IF EXISTS `sales`;
    
    CREATE TABLE `sales` (
      `id` INT(11) NOT NULL,
      `money` DECIMAL(11,2) NOT NULL,
      `score` INT(2),
      `order_date` DATE NOT NULL,
      PRIMARY KEY (`id`,`score`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    PARTITION BY LIST(score)
    ( 
      PARTITION pEven VALUES IN (1,3,5,7,9),
      PARTITION pOdd VALUES IN (2,4,6,8,10)
    )
    

    插入数据

    -- 插入数据
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('1','123.34',1,'2021-12-01');
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('2','12132.33',2,'2022-01-15');
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('3','32432.31',3,'2020-03-22');
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('4','32432.31',4,'2020-03-22');
    

    查询分区统计

     -- 查询数据分区分布
    SELECT 
      PARTITION_NAME AS "分区",TABLE_ROWS AS "行数"
    FROM
      information_schema.partitions 
    WHERE table_schema = "cds-temp" 
     AND table_name = "sales"
    

    在这里插入图片描述

    HASH分区类型

    创建分区表

    使用hash函数进行分区,分区数为3 分为p0、p1、p2三个分区。

    DROP TABLE IF EXISTS `sales`;
    CREATE TABLE `sales` (
      `id` INT(11) NOT NULL,
      `money` DECIMAL(11,2) NOT NULL,
      `score` INT(2) NOT NULL,
      `order_date` DATE NOT NULL,
      PRIMARY KEY (`id`,`score`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    PARTITION BY HASH(score) PARTITIONS 3;
    

    插入数据

    -- 插入数据
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('1','123.34',1,'2021-12-01');
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('2','12132.33',2,'2022-01-15');
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('3','32432.31',3,'2020-03-22');
    INSERT INTO `sales` (`id`, `money`, `score`,`order_date`) VALUES('4','32432.31',4,'2020-03-22');
    

    查询分区统计

     -- 查询数据分区分布
    SELECT 
      PARTITION_NAME AS "分区",TABLE_ROWS AS "行数"
    FROM
      information_schema.partitions 
    WHERE table_schema = "cds-temp" 
     AND table_name = "sales"
    

    在这里插入图片描述

    key分区类型

    创建分区表

    -- 创建表
    DROP TABLE IF EXISTS `sales`;
    CREATE TABLE `sales` (
      `id` int(11) NOT NULL,
      `name` varchar(10) NOT NULL,
      `money` decimal(11,2) NOT NULL,
      `order_date` date NOT NULL,
      PRIMARY KEY (`id`,`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
     PARTITION BY key (name) PARTITIONS 3;
    

    插入数据

    -- 插入数据 
    INSERT INTO `sales` (`id`,`name`, `money`, `order_date`) VALUES('1','汽车','123.34','2021-12-01');
    INSERT INTO `sales` (`id`,`name`, `money`, `order_date`) VALUES('2','手机','12132.33','2022-01-15');
    INSERT INTO `sales` (`id`,`name`, `money`, `order_date`) VALUES('3','相机','32432.31','2020-03-22');
    

    查询分区统计

     -- 查询数据分区分布
    SELECT 
      PARTITION_NAME AS "分区",TABLE_ROWS AS "行数"
    FROM
      information_schema.partitions 
    WHERE table_schema = "cds-temp" 
     AND table_name = "sales"
    

    在这里插入图片描述

    1.4 分区表原理

    分区表是多个底层相关表组成,mysql分区后每个分区成了独立的文件,虽然从逻辑上还是一张表其实已经分成了多张独立的表,从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID, 由于Innodb数据和索引都是保存在".ibd"文件当中(从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和unique也不例外)),所以分区表的索引也是随着各个分区单独存储。

    -- 查看创建表的分区情况
    SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE "%sales%"
    

    在这里插入图片描述

    • select:查询一个分区表,分区层先打开并锁住所有底层表,优化器先过滤掉部分分区,然后再调用对应的存储引擎访问各个分区的数据。
    • insert:写入记录,分区层先打开并锁住所有底层,确定某个分区插入数据,释放锁并将记录写入对应的分区。
    • delete:删除记录,分区层先打开并锁住所有底层,确定数据对应的分区并删除对应分区数据。
    • update:更新记录,分区层先打开并锁住所有底层,确定需要更新的数据在哪个分区并取出更新,再判断更新后数据需要放在哪个分区并写入,如果更新后不再同一分区还需要将原分区数据删除。

    1.5 分区表使用

    使用分区表的前提是数据量特别大,在数据量特别巨大的时候,比如10亿条数据,使用索引会产生入下问题:

    大数据量的问题

    • 创建和维护索引的成本比较高。
    • 使用索引如果是覆盖索引还好不用回表。回表的可能会产生大量的随机IO。

    分区表的两种策略

    • 分区表的全表扫描

    ​ 使用简单分区方式存放表,不使用其他索引,则该策略假定不依赖于内存数据,则需要控制分区表的数量限制在很小的范围内。

    • 分离冷热数据,使用索引

    ​ 数据有明显的’冷热’数据之分,只有热数据被频繁访问,其他数据几乎不会被访问,热数据存放到一个分区中且该热数据尽可能的使用内存,大部分查询在该热点分区中,也可以正常使用索引。

    分区表的问题

    • null值问题

      Mysql允许分区键值为NULL,分区键可能是一个字段或者一个用户定义的表达式。一般情况下,Mysql在分区的时候会把NULL值当作零值或者一个最小值进行处理。

    ​ 如果分区键所在列没有notnull约束。

    ​ range分区表,那么null行将被保存在范围最小的分区。

    ​ list分区表,那么null行将被保存到list为0的分区。

    ​ HASH和KEY分区,任何产生NULL值的表达式mysql都视同它的返回值为0。

    为了避免这种情况的产生,建议分区键设置成NOT NULL。或者设置一个p_ null的“无用”分区用来存放一些null值字段。

    null值问题 此处笔者存疑,因为上述分区例子中,分区字段列均为主键,说明其不存在null值字段,即时使用分区表达式也没有输出null的情况

    • 分区列和索引列不匹配

      索引列和分区列不匹配是指你的sql语句where条件的包含带有索引的列但不包含分区列的情况,这时候不管分区列有没有索引都不会进行分区过滤。

    • 选择分区成本很高

      对应范围分区和list分区,所有的curd操作都需要进行入下符合条件的行属于那些分区,对于查找来说可能需要扫描所有分区才能判断出结果,所以分区数不宜设置的过多,小于100个分区是笔者的建议。HASH和KEY分区不会有该情况。

    • 分区查找锁表成本

    ​ curd访问分区表时都会出现锁住所有底层表的情况,这是一个硬性开销。该情况在分区过滤之前(所以不能使用分区过滤的形式减少开销),同时也与分区类型无关。

    • 维护分区成本

      就像创建索引能提高查询效率,但也增加了索引数据维护的开销,分区也有对应的维护成本,对于频繁的删除或者修改分区会产生一定的性能损耗。

    1.6 分区查询

    正常使用分区过滤

    -- where条件只有带有分区列才能进行分区过滤
    EXPLAIN PARTITIONS 
    SELECT 
      * 
    FROM
      sales 
    WHERE order_date > "2022-01-01" 
    

    在这里插入图片描述

    -- 分区表作为关联表的第二张表且关联条件是分区键,查找也是能过来分区的
    EXPLAIN [ PARTITIONS ] 
    SELECT 
      * 
    FROM
      commodity 
      JOIN sales commodity.date = sales.order_date 
    WHERE order_date > "2022-01-01" 
    

    不能使用分区过滤情况

    -- mysql只有在查询条件中分区列本身进行比较 才能过滤分区
    -- 如下的使用列的表达式进行筛选 则无法过滤分区,会扫描所有分区
    EXPLAIN PARTITIONS 
      SELECT 
        * 
      FROM
        sales 
      WHERE order_date > YEAR("2022-01-01")
    

    在这里插入图片描述

    1.7 合并表

    ​ 合并表和分区表类似,区别在于分区表时逻辑概念,我们无法直接访问底层表,但是合并表的所有字表都是真实存在的,只是通过使用语言将两张结构一致的表合并在一起处理。

    DROP TABLE IF EXISTS `user_one`;
    CREATE TABLE `user_one` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    
    DROP TABLE IF EXISTS `user_two`;
    CREATE TABLE `user_two` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL COMMENT '姓名',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    -- 插入数据
    INSERT  INTO `user_one`(`id`,`name`) VALUES (1,'张三');
    INSERT  INTO `user_two`(`id`,`name`) VALUES (1,'李四');
    
    -- union 必须是多个表结构一致且两个表引擎必须为MyISAM --
    -- 合并表
    DROP TABLE IF EXISTS `user_mrg`;
    CREATE TABLE `user_mrg` (
      `id` INT(11) NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MERGE 
    UNION = (user_one,user_two) INSERT_METHOD LAST
    ;
    
    INSERT INTO user_mrg() VALUE(NULL,"王五");
    
    SELECT * FROM user_mrg
    

    在这里插入图片描述

    合并表user_mrg与user_one和user_two表结构定义相同,但是主键出现了重复,说明虽然合并表结构要与子表一致,但是却不受主键限制(有点匪夷所思)。

    INSERT_METHOD 设置插入数据时插入到合并表中的哪个字表中,first 插入到第一张表,last 插入数据到最后一张表

    2. 视图

    2.1 视图概念

    MySQL 视图(View)是一种虚拟存在的表,数据库中只存放了视图的定义不存放数据,数据存放在定义视图查询涉及的真实表中。使用视图查询数据时,数据库会从真实表中取出对应的数据。

    好处

    简单性,简化用户对数据的理解和操作。对于经常查询的sql可以创建视图,简化复杂的sql操作。

    安全性,视图可以筛选用户需要的列数据而隐藏用户不需要的列,变向的进行了简单的权限过滤。

    相关语句

    -- 创建视图
    CREATE VIEW <视图名> [(列名 [, ...n])]
    AS
       SELECT语句
    
    -- 修改视图
    ALTER VIEW <视图名> [(<列名> [, ...n])]
    AS
       SELECT语句
       
    -- 删除视图
    DROP VIEW <视图名>
    

    2.2 视图原理

    -- 创建视图
    CREATE VIEW sales_view (money,order_date)
    AS
      SELECT money,order_date FROM sales WHERE money >500
      
    -- 查询视图
    SELECT * FROM sales_view WHERE order_date >"2020-05-01";
    

    mysql中实现视图的方式有两种方式。以上述视图例子为例

    • 合并算法:所谓合并算法将视图中的查询条件和查询视图追加的条件进行合并转换为完整的sql进行执行,合并算法性能较好,优化器也能对其进行优化,所以此处推荐尽量使用合并算法。上述视图查询使用合并算法
    -- 使用合并算法实现视图
    SELECT 
      money,
      order_date 
    FROM
      sales 
    WHERE money > 500   -- 原视图查询条件
      AND order_date > "2020-05-01" ;  -- 查询创建视图 新的查询条件
    
    • 临时表算法:顾名思义,创建视图即为创建一张临时表将视图查询的数据存储到临时表中。基于视图查询操作就对临时表进行查询。
    -- 使用临时表实现视图
    CREATE TEMPORARY TABLE sale_tmp 
    AS 
      SELECT 
        money,
        order_date 
      FROM
        sales 
      WHERE money > 500 
      
    SELECT * FROM sale_tmp WHERE order_date >"2020-05-01";
    

    在这里插入图片描述

    无法使用合并算法情况

    ​ group by、distinct,聚合函数、union、子查询等无法建立原表和视图表1对1映射场景 都会采用临时表形式进行视图实现。使用explain 分析视图 select_type 为DERIVED即使用临时表。

    EXPLAIN SELECT * FROM  {view_name}
    

    3. 存储代码

    mysql的存储代码有四种类型

    1. 触发器:在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句
    2. 事件:在MySQL 5.1中新增了一个特色功能事件调度器(Event Scheduler),简称事件。它可以作为定时任务调度器。
    3. 存储函数:MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。
    4. 存储过程:特定功能的SQL 语句集经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数来执行它。

    好处

    • 存储代码在服务内部执行会节省网络带宽降低网络延迟
    • 存储代码可重复使用,同时对于存储过程/函数,mysql会缓存其对应的执行计划,一次编译多次执行。
    • 将程序开发和和DBA职能分开,专人专职。

    缺陷

    • 存储代码功能依赖的函数较少,适于简单逻辑编写,灵活复杂的逻辑还需要依赖应用程序。

    • 存储代码可读性,可维护性差,没有相对完善的开发和调试工具(上百行的存储代码你维护一下试试)。

    • 存储代码没有完备的异常处理机制,一个小错误可能拖死服务。

    3.1 触发器

    创建触发器

    DELIMITER $
    CREATE TRIGGER 触发器名称
    BEFORE|AFTER  INSERT|UPDATE|DELETE
    ON 表名
    [FOR EACH ROW]  -- 行级触发器
    BEGIN
      触发器要执行的功能;
    END$
    DELIMITER ;
    

    场景:新增(删除、修改)用户(user)同时添加日志记录(user_log)

    DELIMITER $
    CREATE TRIGGER user_log_trigger
    AFTER  INSERT
    ON `user`
    FOR EACH ROW
    BEGIN
      INSERT INTO `user_log` VALUE(NULL,"add","插入用户数据");
    END$
    DELIMITER ;
    
    -- 插入数据
    INSERT  INTO `user`(`id`,`cust_no`,`first_name`,`last_name`,`alias`,`descption`,`birthday`) VALUES (20,'T000020','周杰伦','周杰伦','zjl','1983年9月20日出生于台湾省台',"1983-01-10");
    
    

    在这里插入图片描述

    3.2 事件

    -- 查看定时策略是否开启
    show variables like '%event_schedu%';
    
    -- 开启定时策略:
    set global event_scheduler=1;
    
    CREATE
    	[DEFINER={user | CURRENT_USER}]  -- 权限控制 只有在这里声明用户才可以使用该事件
    	EVENT [IF NOT EXISTS] event_name  
    	ON SCHEDULE schedule
    	[ON COMPLETION [NOT] PRESERVE]
    	[ENABLE | DISABLE | DISABLE ON SLAVE]
    	[COMMENT 'comment']
    	DO 事件要执行的功能;
    
    子句说明
    DEFINER可选,用于定义事件执行时检查权限的用户
    IF NOT EXISTS可选项,用于判断要创建的事件是否存在
    EVENT event_name必选,用于指定事件名,event_name的最大长度为64个字符,如果为指定event_name,则默认为当前的MySQL用户名(不区分大小写)
    ON SCHEDULE schedule必选,用于定义执行的时间和时间间隔
    ON COMPLETION [NOT] PRESERVE可选,用于定义事件是否循环执行,即是一次执行还是永久执行,默认为一次执行,即 NOT PRESERVE
    ENABLE | DISABLE | DISABLE ON SLAVE可选项,用于指定事件的一种属性。其中,关键字ENABLE表示该事件是活动的,也就是调度器检查事件是否必选调用;关键字DISABLE表示该事件是关闭的,也就是事件的声明存储到目录中,但是调度器不会检查它是否应该调用;关键字DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个,则在一个事件创建之后,它立即变为活动的。
    COMMENT ‘comment’可选,用于定义事件的注释
    DO event_body必选,用于指定事件启动时所要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句,可以使用BEGIN…END复合结构

    在ON SCHEDULE子句中,参数schedule的值为一个AS子句,用于指定事件在某个时刻发生,其语法格式如下:

    AT timestamp [+ INTERVAL interval] ...
      | EVERY interval
        [STARTS timestamp [+ INTERVAL interval] ...]
        [ENDS timestamp [+ INTERVAL interval] ...]
    

    参数说明:

    (1)timestamp:表示一个具体的时间点,后面加上一个时间间隔,表示在这个时间间隔后事件发生。

    (2)every子句:用于表示事件在指定时间区间内每隔多长时间发生一次,其中 STARTS子句用于指定开始时间;ENDS子句用于指定结束时间。

    (3)interval:表示一个从现在开始的时间,其值由一个数值和单位构成。例如,使用“4 WEEK”表示4周;使用“‘1:10’ HOUR_MINUTE”表示1小时10分钟。间隔的距离用DATE_ADD()函数来支配。

    interval参数值的语法格式如下:

    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
                  WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
                  DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
    

    创建事件

    -- 每隔1秒 调用事件添加用户日志
    CREATE EVENT `user_log_event` 
    ON SCHEDULE EVERY 1 SECOND 
    ON COMPLETION PRESERVE ENABLE
    DO 
       INSERT INTO `user_log` VALUE (NULL,"time_task","定时任务触发添加用户操作日志"
    );
    

    在这里插入图片描述

    3.3 存储过程

    • 增强SQL语言的功能和灵活性
    • 存储过程创建后可以多次调用
    • 存储过程执行速度快,存储过程一次编写后会将优化后的执行计划进行存储,每次不必进行优化直接执行。
    -- 创建存储过程
    CREATE PROCEDURE 存储过程名
    (IN|OUT|INOUT 参数名 参数类型,...)   -- IN输入参数  OUT输出参数 INOUT输入输出
    [characteristics ...]    -- characteristics 表示创建存储过程时指定的对存储过程的约束条件
    BEGIN
     存储过程体 
    END
    
    参数说明
    LANGUAGE SQL存储过程的实体是由SQL语句组成
    [NOT] DETERMINISTIC存储过程执行结果是否确定,所谓确定:相同的输入会得到输出
    CONTAINS SQL
    NO SQL
    READS SQL DATA
    MODIFIES SQL DATA
    指定子程序使用SQL语句
    CONTAINS SQL:包含的SQL语句是不进行读写的SQL语句
    NO SQL:不包含SQL语句
    READS SQL DATA:子程序包含读请求
    MODIFIES SQL DATA:子程序包含写请求
    SQL SECURITY{ DEFINER | INVOKER}执行存储过程的权限。DEFINER:存储过程的创建者才有权调用,默认值
    INVOKER:拥有当前存储过程访问权限的用户均有权调用。
    COMMENT ‘string’注释信息,存储过程描述
    DELIMITER $
    -- 创建储存过程 中运行  
    CREATE PROCEDURE sale_money_Procedure(IN s_money DECIMAL(11,2),OUT s_date DATE,INOUT s_num INT) 
    BEGIN 
    IF s_money IS NULL THEN 
     SELECT MAX(order_date) FROM sales INTO s_date; 
    ELSE 
    SELECT MAX(order_date)  FROM sales WHERE money > s_money INTO s_date; 
    SET s_num = s_num * 10;
    END IF; 
    END$
    DELIMITER ;
    
    -- 调用存储过程
    SET @s_num=7;  
    CALL sale_money_Procedure(200.00,@max_order_date,@s_num); 
    SELECT @max_order_date,@s_num;
    

    在这里插入图片描述

    3.4 存储函数

    MySQL存储函数,函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。

    CREATE FUNCTION func_name ([param_name type[,...]])
    RETURNS type
    [characteristic ...] 
    BEGIN
    	存储过程体 
    END;
    
    -- 调用存储过程
    SELECT func_name([parameter[,…]]);
    
    -- 查看存储过程和函数的状态信息
    SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
    

    存储函数和存储过程异同

    存储函数存储过程
    不能拥有输出参数可以拥有输出参数
    可以直接调用存储函数,不需要call语句需要call语句调用存储过程
    必须包含一条return语句不允许包含return语句
    DELIMITER $
    -- 创建储存函数  
    CREATE FUNCTION sale_money_Function(s_money DECIMAL(11,2)) 
    RETURNS DATE
    NOT DETERMINISTIC
    BEGIN 
     RETURN (SELECT MAX(order_date)  FROM sales WHERE money > s_money);
    END$
    DELIMITER ;
    
    -- 调用存储函数
    SELECT sale_money_Function(200.00); 
    

    在这里插入图片描述

    展开全文
  • MySQL8.0新特性

    2018-07-16 15:58:29
    MySQL8.0新特性MySQL8.0新特性
  • MySQL 8.0开始支持原子数据定义语言(DDL)语句。此功能称为原子DDL。这篇文章主要介绍了MySQL8.0新特性——支持原子DDL语句,需要的朋友可以参考下
  • MYSQL数据库的四大特性

    千次阅读 2019-07-10 09:51:23
    1、数据库中事务的四大特性(ACID) 1)、 原子性(Atomicity) 原子性是指事务包含的所有操作要么全部成功,要么全部失败进行回滚,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有...

    1、数据库中事务的四大特性(ACID)
    1)、 原子性(Atomicity)
    原子性是指事务包含的所有操作要么全部成功,要么全部失败进行回滚,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。

    2)、一致性(Consistency)
    一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。

        拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。
    

    3)、 隔离性(Isolation)
    隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。

        即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。
    

    4)、 持久性(Durability)
    持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

        例如我们在使用JDBC操作数据库时,在提交事务方法后,提示用户事务操作完成,当我们程序执行完成直到看到提示后,就可以认定事务以及正确提交,即使这时候数据库出现了问题,也必须要将我们的事务完全执行完成,否则就会造成我们看到提示事务处理完毕,但是数据库因为故障而没有执行事务的重大错误。
    

    2、数据库事务的隔离级别有4种,
    由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。
    而且,在事务的并发操作中可能会出现脏读,不可重复读,幻读。下面通过事例一一阐述它们的概念与联系。

    Read uncommitted

    读未提交,顾名思义,就是一个事务可以读取另一个未提交事务的数据。

    事例:老板要给员工发工资,员工的工资是13K/月。但是发工资时老板不小心多按了个零,按成16k/月,该钱已经打到员工的户口,但是事务还没有提交,就在这时,员工去查看自己这个月的工资,发现比往常多了3K,以为涨工资了非常高兴。但是老板及时发现了不对,马上回滚差点就提交了的事务,将数字改成13K再提交。

    分析:实际员工这个月的工资还是13K,但是员工看到的是16k。他看到的是老板还没提交事务时的数据。这就是脏读。

    那怎么解决脏读呢?Read committed!读提交,能解决脏读问题。

    Read committed

    读提交,顾名思义,就是一个事务要等另一个事务提交后才能读取数据。

    事例:员工拿着信用卡去享受生活(卡里当然是只有13K),当他埋单时(员工事务开启),收费系统事先检测到他的卡里有13K,就在这个时候!!员工的妻子要把钱全部转出充当家用,并提交。当收费系统准备扣款时,再检测卡里的金额,发现已经没钱了(第二次检测金额当然要等待妻子转出金额事务提交完)。员工就会很郁闷,明明卡里是有钱的…

    分析:这就是读提交,若有事务对数据进行更新(UPDATE)操作时,读操作事务要等待这个更新操作事务提交后才能读取数据,可以解决脏读问题。但在这个事例中,出现了一个事务范围内两个相同的查询却返回了不同数据,这就是不可重复读。

    那怎么解决可能的不可重复读问题?Repeatable read !

    Repeatable read

    重复读,就是在开始读取数据(事务开启)时,不再允许修改操作

    事例:员工拿着信用卡去享受生活(卡里当然是只有13K),当他埋单时(事务开启,不允许其他事务的UPDATE修改操作),收费系统事先检测到他的卡里有13K。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

    分析:重复读可以解决不可重复读问题。写到这里,应该明白的一点就是,不可重复读对应的是修改,即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作,而不是UPDATE操作。

    什么时候会出现幻读?

    事例:员工某一天去消费,花了2千元,然后他的妻子去查看他今天的消费记录(全表扫描FTS,妻子事务开启),看到确实是花了2千元,就在这个时候,员工花了5千买了一部手机,即新增INSERT了一条消费记录,并提交。当妻子打印员工的消费记录清单时(妻子事务提交),发现花了7千元,似乎出现了幻觉,这就是幻读。

    那怎么解决幻读问题?Serializable!

    Serializable 序列化

    Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。

    记住:设置数据库的隔离级别一定要是在开启事务之前!

    如果是使用JDBC对数据库的事务设置隔离级别的话,也应该是在调用Connection对象的setAutoCommit(false)方法之前。调用Connection对象的setTransactionIsolation(level)即可设置当前链接的隔离级别,至于参数level,可以使用Connection对象的字段:

    在JDBC中设置隔离级别的部分代码:

    后记:隔离级别的设置只对当前链接有效。对于使用MySQL命令窗口而言,一个窗口就相当于一个链接,当前窗口设置的隔离级别只对当前窗口中的事务有效;对于JDBC操作数据库来说,一个Connection对象相当于一个链接,而对于Connection对象设置的隔离级别只对该Connection对象有效,与其他链接Connection对象无关。

    展开全文
  • 项目中经常会用到自增id,比如uid,下面为大家介绍下利用mysql事务特性实现并发安全的自增ID,感兴趣的朋友可以参考下
  • mysql8 新特性总结

    千次阅读 2022-04-25 22:01:26
    mysql8 新特性总结
  • mysql8.0 新特性 官网英文文档,讲解的比较全面,适合开发人员和运维人员
  • MySQL是众多网站技术栈中的标准配置,是广受欢迎的开源数据库,已经推出了8.0的第一个候选发行版本。接下来通过本文给大家分享Mysql 8 新特性 window functions 的作用,需要的朋友参考下吧
  • 浅谈MySQL系统特性

    2021-01-26 04:47:00
    众所周知,MySQL是目前世界上最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。我们不得不承认,MySQL能够取得...
  • 数据库四大特性

    千次阅读 2021-03-03 23:50:02
    数据库系统必须维护事务的以下特性(简称ACID):原子性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability)⑴ 原子性(Atomicity)原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,因此...
  • MySQL8.0 GA版本发布了,展现了众多新特性,下面这篇文章主要给大家介绍了关于MySQL8新特性:自增主键的持久化的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考借鉴,下面随着小编来一起学习学习吧
  • MySQL8.0简介特性及性能

    万次阅读 2018-08-15 11:38:50
    MySQL 8.0 正式版 8.0.12 已发布,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能! 下载地址: https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12-winx64.zip 注意:从 ...
  • MySQL innodb 8.0 新特性

    2018-07-09 10:54:32
    MySQL innodb 8.0 新特性 Oracle原厂工程师52页PPT MySQL innodb 8.0 新特性
  • 主要介绍了MySQL8.0 DDL原子性特性及实现原理,本文给大家介绍的非常详细,具有一定的参考借鉴价值 ,需要的朋友可以参考下
  • MySQL8.0-新特性-role

    千次阅读 2018-01-21 10:44:46
    一、简介role可以看做一个权限的集合,这个集合有一个统一的名字role名。可以给多个账户统一的某个role的权限权限的修改直接通过...二、案例1、创建表mysql> create table t1(id1 int primary key,id2 int); Query OK

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 318,770
精华内容 127,508
关键字:

mysql特性

mysql 订阅