【MySQL】MySQL 存储引擎，ACID特性，外键约束

2024-08-25 07:04
文章标签 mysql 特性 引擎 存储 外键 约束 database acid

本文主要是介绍【MySQL】MySQL 存储引擎，ACID特性，外键约束，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL 存储引擎

1. InnoDB

InnoDB 是 MySQL 5.5 之后的默认存储引擎，也是最常用的引擎。

特点：

  • 支持事务（ACID）
  • 行级锁定
  • 外键约束
  • 崩溃恢复能力强
  • 支持大型数据库

适用场景：

  • 需要事务支持的应用
  • 高并发环境
  • 需要外键支持的数据库设计

2. MyISAM

MyISAM 是 MySQL 5.5 之前的默认引擎。

特点：

  • 不支持事务
  • 表级锁定
  • 全文索引支持
  • 读取速度快

适用场景：

  • 读密集的应用
  • 不需要事务支持的场合
  • 全文检索

3. Memory (HEAP)

数据存储在内存中，用于需要快速访问数据的场合。

特点：

  • 数据存在内存，速度极快
  • 重启后数据丢失
  • 表级锁定

适用场景：

  • 临时表
  • 缓存表
  • 小型查找表

4. Archive

用于存储大量的历史、归档或安全审计信息。

特点：

  • 高度压缩
  • 只支持 INSERT 和 SELECT 操作
  • 不支持索引

适用场景：

  • 日志和数据归档

5. CSV

数据以逗号分隔值格式存储。

特点：

  • 数据以 CSV 格式存储
  • 方便数据导入导出

适用场景：

  • 数据交换

如何选择存储引擎

  1. 默认选择：对于大多数应用，InnoDB 是最佳选择。

  2. 考虑因素：

    • 是否需要事务支持
    • 并发访问的级别
    • 数据完整性要求
    • 恢复能力需求
    • 特殊功能需求（如全文索引）

  3. 查看当前使用的存储引擎：

    SHOW TABLE STATUS FROM database_name WHERE Name = 'table_name';

ACID 特性

ACID 是数据库事务的四个基本特性，它们保证了数据库在并发操作和故障情况下的可靠性和一致性。让我详细解释一下 ACID 的含义：
ACID 是指 Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）和 Durability（持久性）的首字母缩写。这四个特性共同确保了数据库事务的可靠执行。

1. Atomicity（原子性）

  • 定义：事务中的所有操作要么全部完成，要么全部不完成。

  • 解释：事务被视为一个不可分割的最小工作单元。

  • 示例：
    假设有一个银行转账事务，从账户 A 转 100 元到账户 B：

    START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
COMMIT;

如果任何一步失败，整个事务都会回滚，保证账户 A 和 B 的余额不会出现不一致。

2. Consistency（一致性）

  • 定义 ：事务必须使数据库从一个一致性状态变换到另一个一致性状态。

  • 解释：事务执行前后，数据库的完整性约束没有被破坏。

  • 示例： 假设有一个规则：账户余额不能为负。那么任何导致账户余额为负的事务都应该失败：

START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 'A';
-- 如果 A 的余额小于 200，这个事务会被回滚
COMMIT;

3. Isolation（隔离性）

  • 定义：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  • 解释：每个事务应该感觉像在独占数据库。
  • 示例： 两个事务同时操作同一账户：
-- 事务 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
COMMIT;-- 事务 2
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 'A';
COMMIT;

隔离性确保事务 2 要么看到事务 1 完成前的余额，要么看到完成后的余额，而不是中间状态。

4. Durability（持久性）

  • 定义：一旦事务提交，其所做的修改就会永久保存在数据库中。
  • 解释：即使系统崩溃，提交的事务也不会丢失。
  • 示例： 当一个事务被提交后，即使数据库立即崩溃，重启后数据仍然存在

ACID 的重要性

  • 数据完整性：保证数据库中的数据始终保持一致和准确。
  • 并发控制：允许多个用户同时访问数据库而不会互相干扰。
  • 错误恢复：在系统故障时能够恢复到一致的状态。
  • 可靠性：确保关键业务操作的可靠执行。

实现 ACID

数据库系统通过各种机制来实现 ACID 特性：

  • 使用日志和检查点来实现原子性和持久性
  • 通过约束和触发器来维护一致性
  • 使用锁和多版本并发控制（MVCC）来实现隔离性

支持 ACID 事务的数据库（如使用 InnoDB 引擎的 MySQL）能够在复杂的应用场景中保证数据的一致性和可靠性，这对于金融、电商等对数据准确性要求极高的领域尤为重要。

外键约束

定义

外键约束是指在一个表中定义一个字段（或字段组合），该字段引用另一个表的主键或唯一键。外键用于确保数据的参照完整性，防止无效数据的插入。

特点

  1. 参照完整性：

    • 外键约束确保子表中的值必须在父表中存在。
    • 例如，如果在订单表中有一个客户ID字段，它必须对应于客户表中的有效客户ID。

  2. 级联操作：

    • 外键可以设置级联删除和级联更新。
    • 例如，如果删除父表中的记录，所有引用该记录的子表记录也会被自动删除（级联删除）。

  3. 防止孤立记录：

    • 外键约束防止在子表中存在没有对应父表记录的记录。

语法示例

创建表时定义外键

CREATE TABLE Customers (CustomerID INT PRIMARY KEY,CustomerName VARCHAR(100)
);CREATE TABLE Orders (OrderID INT PRIMARY KEY,OrderDate DATE,CustomerID INT,FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

在这个例子中，如果删除某个客户（Customers 表中的记录），与该客户相关的所有订单（Orders 表中的记录）也会被自动删除。
如果更新某个客户的 ID，所有引用该客户 ID 的订单记录也会自动更新。

添加外键约束到已存在的表

ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

这篇关于【MySQL】MySQL 存储引擎，ACID特性，外键约束的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1104879。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
poj 3159 （spfa差分约束最短路） poj 1201

poj 3159 （spfa差分约束最短路） poj 1201

poj 3159: 题意： 每次给出b比a多不多于c个糖果，求n最多比1多多少个糖果。 解析： 差分约束。 这个博客讲差分约束讲的比较好： http://www.cnblogs.com/void/archive/2011/08/26/2153928.html 套个spfa。 代码： #include <iostream>#include <cstdio>#i
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2