MySQL 学习笔记（Schema、事物）

最新推荐文章于 2021-09-16 15:55:23 发布
最新推荐文章于 2021-09-16 15:55:23 发布
阅读量582 收藏 1
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/baidu_32452525/article/details/105759149
版权

MySQL 学习笔记

Schema 设计

良好的逻辑设计和物理设计是高性能的基石。

数据类型的选择

  • 更小的通常
  • 更好简单就好
  • 尽量避免 Null
整数
  • 整数类型： TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT 、 BIGINT；分别使用 8、16、24、32、64 位存储空间。存储的范围从 -2(N-1) 到 2(N-1)-1。
  • 整数类型有可选的 UNSIGNED，表示不允许负值。
  • 有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。
  • MySQL 可以为整数类型指定宽度，例如 INT(11)，这实际没有意义：它不会限制值的合法范围。对于存储和计算来说， INT(1) 和 INT(20) 是相同的。
实数
  • DECIMAL 类型用于存储精确的小数。CPU 不支持对 DECIMAL 的直接计算。
  • CPU 直接支持原生浮点计算，所以浮点运算明显更快。
  • MySQL 5.0 和更高版本中的 DECIMAL 类型运行最多 65 个数字。
字符串类型
  • VARCHAR:用于存储可变长字符串，比定长类型更节省空间
  • CHAR:定长，根据定义分配足够的空间

使用枚举（ENUM）代替字符串:

  • 枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。
BLOB和TEXT 类型
  • BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。
日期和时间类型
  • DATETIME: 保存大范围的值，从 1001 年到 9999 年，精度为秒。把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中，与时区无关。使用 8 个字节的存储空间。
  • TIMESTAMP: 保存从 1970 年 1 月 1 日午夜以来的秒数，和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间，范围是从 1970 年到 2038 年。

MySQL Schema 设计中的陷阱

  • 太多的列
  • 太多的关联
  • 全能的枚举
  • 变相的枚举
  • 非此发明的 NULL

范式和反范式

  • 第一范式(1NF)

符合1NF的关系中的每个属性都不可再分。1NF是所有关系型数据库的最基本要求。

  • 第一范式(2NF)

在1NF的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。B完全依赖于A，就是说A中的所有属性唯一决定B，属性少了就不能唯一决定，属性多了则有冗余（叫依赖不叫完全依赖）。举例：（学号，课程名）这个主属性集可以唯一决定成绩，但是对于学生姓名这个属性（学号，课程名）这个属性集就是冗余的，所以学生姓名不完全依赖于（学号，课程名）这一属性集

  • 第一范式(3NF)

3NF就是要消除传递依赖，方便理解，可以看做是“消除冗余”。传递依赖：如果C依赖于B，B依赖于A，那么C传递依赖于A；

不符合范式会出现哪些异常

  • 冗余数据：某些同样的数据多次出现（如学生姓名）；
  • 修改异常：修改了一个记录中的信息，另一个记录中相同的信息却没有修改；
  • 删除异常：删除一个信息，那么也会丢失其它信息（删除一个课程，丢失了一个学生的信息）；
  • 插入异常：无法插入（插入一个还没有课程信息的学生）

范式化通常带来的好处

  • 范式化的更新操作通常比反范式化要快。
  • 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
  • 范式化的表通常更小，可以更好地存放在内存里，所以执行操作会更快。
  • 很少有多余的数据意味着检索列表数据时，更少需要 DISTINCT 或者 GROUP BY 语句。
  • 范式化设计的 Schema 的缺点是通常需要关联。

反范式的优缺点

  • 反范式化的 Schema 因为所有数据都在一张表中，可以很好地避免关联。
  • 单独的表也能使用更有效的索引策略。

事务

事务是一组原子性的 SQL 查询，或者说是一个独立的工作单元。事务内的所有操作要么全部执行成功，要么全部执行失败。

四个基本特性

  • Atomicity（原子性）：事务是一个不可分割的整体，事务内所有操作要么全部提交成功，要么全部失败回滚。
  • Consistency（一致性）：事务执行前后，数据从一个状态到另一个状态必须是一致的（A向B转账，不能出现A扣了钱，B却没收到）。
  • Isolation（隔离性）：多个并发事务之间相互隔离，不能互相干扰。或者说一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  • Durablity（持久性）：事务完成后，对数据库的更改是永久保存的，不能回滚。

什么是脏读？幻读？不可重复读

  • 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。(读了别人丢弃的东西)
  • 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。（一个事物两次查询不一致）
  • 幻读(Phantom Read):当同一查询多次执行时，由于其它事务在这个数据范围内执行了插入操作，会导致每次返回不同的结果集（和不可重复读的区别：针对的是一个数据整体/范围；并且需要是插入操作）(多读了)

事务隔离级别

Read Uncommitted(读取未提交)

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

Read Committed(读取已提交)

大多数数据库系统的默认隔离级别都是 Read Committed。Read Committed 满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。有时也叫不可重复读(Nonrepeatable Read)。

Repeatable Read(可重复读)

Repeatable Read 解决了脏读的问题。但是还是无法解决领一个幻读(Phantom Read)问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB 和 XtraDB 存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)解决了幻读的问题。

Repeatable Read(可重复读)在读的过程中数据始终是事务启动时的数据状态，未提交之前其他事物的增删改操作提交后都不会影响读的结果。读的是快照结果。

Serializable(可串行化)

Serializable 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读问题。简单来说，Serializable 会在读取的每一行数据上都加锁，所以导致大量的超时和锁争用的问题。实际中，极少使用。

注意:Repeatable Read(可重复读) 是 MySQL 默认事务隔离级别

关系

在这里插入图片描述

引擎

MySQL存储引擎MyISAM与InnoDB区别

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

常用的存储引擎有以下：

  • Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
  • MyIASM引擎(原本MySQL的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
  • MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

隔离级别与锁的关系

  • 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
  • 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁
  • 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁
  • SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成

锁类别

按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )

行级锁，表级锁和页级锁对比

  • 行级锁: MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

    • 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

  • 表级锁:MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

    • 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

  • 页级锁:MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

    • 特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
共享锁和排他锁对比
  • 共享锁/S锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
  • 排他锁/X锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。
封锁协议之相容性矩阵

在这里插入图片描述

两段封锁协议
  • 读写数据之前要获得锁,每个事务中所有封锁请求先于任何一个解锁请求
  • 两阶段：加锁段，解锁段。加锁段中不能有解锁操作，解锁段中不能有加锁操作。事务必须严格分为两个阶段对数据进行加锁和解锁的操作，第一阶段加锁，第二阶段解锁。也就是说一个事务中一旦释放了锁，就不能再申请新锁了
什么是乐观锁和悲观锁
  • 悲观锁：认为数据随时会被修改，因此每次读取数据之前都会上锁，防止其它事务读取或修改数据；应用于数据更新比较频繁的场景；
    • 不需要程序员自己实现
  • 乐观锁：操作数据时不会上锁，但是更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试；适用于读多写少的场景。乐观锁的实现方式有：
    • 加一个版本号或者时间戳字段，每次数据更新时同时更新这个字段
    • 先读取想要更新的字段或者所有字段，更新的时候比较一下，只有字段没有变化才进行更新
    • CAS（Compare And Swap）算法：非阻塞的轻量级的乐观锁，通过CPU指令实现，在资源竞争不激烈的情况下性能高，
    • 需要程序员自己实现
两种锁的使用场景
  • 从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
  • 但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

死锁

死锁是指两个或者多个事务再同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同事锁定相同的资源时，也会产生死锁。

InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。

CoLiuRs
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql调优（三）--schema(模式)
qq_36741186的博客
09-02 232
schema(模式) 思维导图 一、合理使用范式和反范式 1.何为原子性？ 程序的原子性指：整个程序中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。原子性在一个操作是不可中断的，要么全部执行成功要么全部执行失败，有着“同生共死”的感觉。及时在多个线程一起执行的时候，一个操作一旦开始，就不会被其他线程所干扰。 2.范式有哪些？ 1．第一范式(确保每列保持原子性) 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。 例如：
MySQL Schema 优化（设计优化）
步步为赢的专栏
07-27 632
逐步对 schema 做修改— 一个小的变化将产生的巨大的影响。在开发环境测试所有 schema 变动，而不是在生产环境的镜像上去做。   1、保证你的数据库的整洁性。   2、不要过度使用索引，评估你的查询。   3、尽量不要使用text 和 blob 数据类型, 压缩 text 和 blob 数据类型—为了节省空间，减少从磁盘读数据。(则将 blob/text 数据域
mysql schema 定义_数据库中Schema（模式）概念的理解
weixin_31021619的博客
02-26 2462
在学习SQL的过程中，会遇到一个让你迷糊的Schema的概念。实际上，schema就是数据库对象的集合，这个集合包含了各种对象如：表、视图、存储过程、索引等。为了区分不同的集合，就需要给不同的集合起不同的名字，默认情况下一个用户对应一个集合，用户的schema名等于用户名，并作为该用户缺省schema。所以schema集合看上去像用户名。如果把database看作是一个仓库，仓库很多房间(sche...
MySQL（二）Schema与数据类型优化
weixin_46158390的博客
09-16 251
在数据库中，schema（发音 “skee-muh” 或者“skee-mah”，中文叫模式）是数据库的组织和结构，schemas和schemata都可以作为复数形式。模式中包含了schema对象，可以是表(table)、列(column)、数据类型(data type)、视图(view)、存储过程(stored procedures)、关系(relationships)、主键(primary key)、外键(foreign key)等。数据库模式可以用一个可视化的图来表示，它显示了数据库对象及其相互之间的.
关于mysql 中schema的相关操作
冰冻非一日之寒
08-28 8131
一、mysql中schema指的是什么？ 在mysql中基本认为schema和数据库(database)是相同的，也就是说schema名称和数据库实例的名称是相同的，一个数据库只拥有一个schema。但是其他数据库产品会有所不同，在oracle数据库中，schema是数据库database的一部分。 二、关于schema和数据库database的sql语句操作 1.查看数据库 SHOW DATABASES; 2.创建数据库 CREATE DATABASE db_name;　　//db_name为
mysql学习笔记之基础知识
09-09
MySQL是世界上最受欢迎的关系型数据库管理系统之一，用于存储和管理数据。在MySQL的学习过程中，了解基础命令是至关重要的。本文将介绍一些基本的MySQL操作...希望这篇笔记能为你在MySQL的学习之旅提供有价值的参考。
MySQL学习笔记
03-19
这份"MySQL学习笔记"涵盖了几个关键的知识领域，对于深入理解和高效使用MySQL至关重要。 1. **MySQL性能监控**：性能监控是确保数据库健康运行的基础。通过监控MySQL的系统资源利用率（如CPU、内存、磁盘I/O），...
Mysql学习笔记.pdf
02-13
本学习笔记将围绕MySQL的基础知识、体系架构、性能优化等方面进行深入探讨。 首先，MySQL的学习需要掌握基本的SQL语法，如数据查询、插入、更新和删除操作。推荐的书籍包括《MySQL必知必会》和《MySQL深入浅出》，...
MySQL 学习笔记.zip
02-22
这篇学习笔记将深入探讨MySQL的核心概念、安装与配置、数据类型、SQL语句、表的设计、索引优化、事务处理、视图、存储过程、触发器以及备份与恢复等关键知识点。 一、MySQL基础 1. 数据库概念：理解数据库的基本...
MySQL学习笔记.pdf
11-25
在 MySQL 学习笔记中，介绍了数据库的三大设计：数据 normalization、数据 denormalization 和数据库 schema 设计。 * 数据 normalization：是指将数据分解成更小的独立单元，以减少数据冗余和依赖性。 * 数据 ...
mysql+Spring数据库隔离级别与性能分析
09-10
数据库隔离级别与Spring配置事务的联系及性能影响，以下是个人理解，如果有瑕疵请及时指正
mysql8导入不同schme_mysql8 参考手册-INFORMATION_SCHEMA TABLES表
weixin_39851918的博客
01-19 204
TABLES表提供有关数据库中表的信息。TABLES代表表统计信息的 列包含缓存的值。该 information_schema_stats_expiry 系统变量定义缓存表统计到期之前的一段时间。默认值为86400秒(24小时)。如果没有缓存的统计信息或统计信息已过期，则在查询表统计信息列时将从存储引擎检索统计信息。要随时更新给定表的缓存值，请使用ANALYZE TABLE。要始终直接从存储引擎检...
mysql跨库事务java_java跨库事务Atomikos
weixin_28875281的博客
01-28 144
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/...
MySQL学习之schema类型
ppaii123的博客
03-24 262
MySQL学习之schema类型 schema设计原则 选择能够存储正确数据的最小数据类型 简单数据类型的操作通常需要更少的CPU周期。如:整型<字符串。推荐MySQL内建类型存储日期和时间，使用整型存储IP地址。 尽量避免使NULL的列，因为NULL的列会使索引、索引统计和值比较更为复杂。（性能影响较小） 整数类型 整数 类型有两种:整数和实数 类型名 TINYINT SMAL...
MySQL通过performance_schema定位未提交事务所执行的SQL
cq377078944的博客
09-19 153
经常会遇到这样一个场景： 业务那边觉得数据库“很慢”，上去通过show processlist查看发现大量State为在等待lock，如： Waiting for table metadata/level lock等 ...
MySQL实战——Schema与数据类型优化
西木风落
05-26 308
1. 如何选取Schema的数据类型 MySql支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。数据类型的选取原则： 1） 更小的通常更好。更小的数据类型通常更快，因为占用更小的磁盘空间、内存和CPU缓存，并且处理需要的CPU周期更少； 2）简单就好。简单的数据类型通常需要更少的CPU周期，比如，整型比字符串代价更低，内建的日期类型比字符串存储日期和时间更好； 3）尽量避免...
Mysql跨数据库事务
cxu123321的博客
06-15 1042
Mysql跨数据库事务 一个努力的菜鸟 跨库事务问题 在一个mysql实例中,现有A库和B库,在一个事务里同时向两库各表插入一条数据,这时就涉及一个事务跨不同库的问题 1、首先要确保mysql开启XA事务支持 SHOW VARIABLES LIKE ‘%XA%’ 2.如果innodb_support_xa的值是ON就说明mysql已经开启对XA事务的支持了。 如果不是就执行： SET innodb_support_xa = ON <?PHP $dbtest1 = new mysqli("1
[MySQL]数据库中如何处理多事务
哈罗德Android笔记
03-28 4729
0.README 本文介绍了事务的基本概念，常用的四个命令，事务的四个特性，最后介绍多事务处理中的可能出现的三种问题，以及对应的四个隔离等级。 1.什么是事务 事务处理用来维护数据库的完整性，保证成批的MySQL操作要么完全执行，要么完全不执行。 比如，假设有两个表，账户表money，和支出表pay，那么每一笔支出记录对应就要从余额表中删除一定金额，如果pay插入成功，mo...
mysql中的schema
最新发布
04-08
在MySQL中，Schema是数据库中的一个重要概念，它可以理解为数据库的逻辑容器，用于组织和管理数据库对象，比如表、视图、存储过程等。一个数据库可以包含多个Schema，每个Schema又可以包含多个表和其他对象。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

CoLiuRs

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值