【MySQL】3.MySQL核心概念解析：数据完整性、事务处理、索引及聚簇索引与非聚簇索引

探索MySQL的内部机制，理解数据完整性、事务处理、索引策略以及聚簇索引与非聚簇索引的区别是至关重要的。这些概念构成了数据库设计和优化的基础，对于确保数据的准确性、提高查询效率、维护数据的一致性和实现复杂的数据库操作至关重要。本文将逐一剖析这些核心概念。

一.数据完整性与约束

1.主键（PRIMARY KEY）

定义：
在MySQL中，主键是一个或多个列的组合，它们的唯一性约束保证了表中每个记录都可以被唯一标识。主键列不能包含 NULL 值，并且每个表只能有一个主键。

作用：

  1. 唯一性：确保列的值在表中是唯一的。
  2. 非 NULL：保证主键列中的所有值都是非 NULL 的。
  3. 数据完整性：作为识别和链接其他表中数据的关键字。

创建主键的SQL示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

2.外键约束（FOREIGN KEY）

定义：
外键约束是一种完整性约束，用于维护两个表之间的链接。它保证在一个表中的外键列中的所有值都必须在另一个表的相应列中存在。

作用：

  1. 保证数据的参照完整性：确保引用的数据在主表中是有效的。
  2. 维护数据的一致性：在主表中进行数据更新或删除操作时，外键约束可以自动更新或级联删除相关联的从表数据。

创建外键的SQL示例：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

在这个例子中，orders 表中的 user_id 是一个外键，它引用了 users 表中的 id 列。

3.不使用外键约束保证数据参照完整性

虽然外键约束是保证数据参照完整性的一种机制，但在某些情况下，可能由于性能考虑或使用不支持外键的数据库系统，你可能会避免使用外键。在这种情况下，可以采取以下措施：

  1. 应用逻辑：在应用程序层面实现外键约束的逻辑，例如，在删除或更新主表中的数据之前，检查从表中是否有相关联的记录。

  2. 触发器：使用数据库触发器（Trigger）来模拟外键的行为。例如，可以创建一个 BEFORE DELETE 触发器，当尝试删除 users 表中的记录时，自动检查 orders 表中是否有相关联的订单。

  3. 级联更新/删除：在业务逻辑中实现级联更新或删除的机制，确保当主表中的数据发生变化时，所有相关的从表数据也相应更新或删除。

  4. 定期数据校验：定期运行数据校验脚本来检查数据的一致性，并修复任何不匹配的记录。

以下是使用触发器来保证数据参照完整性的一个例子：

DELIMITER //

CREATE TRIGGER before_delete_user
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    DECLARE order_count INT;

    SELECT COUNT(*) INTO order_count FROM orders WHERE user_id = OLD.id;

    IF order_count > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete user with existing orders';
    END IF;
END; //

DELIMITER ;

这个触发器会在删除 users 表中的记录之前检查 orders 表中是否有相关联的订单，如果有，则阻止删除操作并给出错误信息。

二.事务处理

1.什么是事务

事务是数据库操作的一个单元，它由一个或多个数据库操作组成，这些操作要么全部成功，要么全部失败。事务保证了数据库的完整性和一致性，即使在系统崩溃或其他错误情况下也是如此。

事务的四个主要属性（ACID属性）

  1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个点。
  2. 一致性（Consistency）：事务必须使数据库从一个一致的状态转移到另一个一致的状态。
  3. 隔离性（Isolation）：并发执行的事务之间不会互相影响。
  4. 持久性（Durability）：一旦事务提交，则其结果永久保存在数据库中，即使系统发生故障。

2.如何使用MySQL实现事务

在MySQL中，可以通过以下步骤实现事务：

  1. 设置事务隔离级别（可选，但建议）。
  2. 开启事务。
  3. 执行事务中的SQL语句。
  4. 提交事务（如果所有操作都成功）或回滚事务（如果操作中有任何失败）。

示例：

-- 设置事务隔离级别为可串行化（最严格的隔离级别）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 开启事务
START TRANSACTION;

-- 执行SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'user1';
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'user2';

-- 如果操作成功，提交事务
COMMIT;

-- 如果操作失败，在某些情况下可能需要回滚事务
ROLLBACK;

3.MySQL中的事务隔离级别及影响

MySQL支持以下四个事务隔离级别：

  1. READ UNCOMMITTED：未提交读。事务可以读取未提交的数据，可能导致脏读、幻读或不可重复读。
  2. READ COMMITTED：提交读。事务只能读取已提交的数据，解决了脏读问题，但幻读和不可重复读仍可能发生。
  3. REPEATABLE READ（默认级别）：可重复读。在一个事务的执行期间，其他事务不能修改或插入事务开始时未查询到的行，解决了幻读问题。
  4. SERIALIZABLE：串行化。最高隔离级别，事务串行执行，避免了脏读、幻读和不可重复读，但会降低并发性能。

设置事务隔离级别的SQL示例

-- 查看当前隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 设置隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置隔离级别为串行化
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

选择不同的隔离级别对应用的影响包括：

  • 数据的一致性和准确性。
  • 并发性能和系统资源的使用。
  • 系统设计的复杂性，如需要在应用层面实现额外的锁机制。

在设计数据库应用时，需要根据应用的需求和性能考虑，权衡选择合适的事务隔离级别。

三.索引

1.索引是什么

在数据库中，索引是一种数据结构，它提高了数据库查询数据的效率。索引类似于书籍的目录，它允许数据库管理系统（DBMS）在不扫描整个表的情况下快速找到记录的位置。

2.索引如何提高查询性能

  1. 快速定位：索引允许数据库使用高效的搜索算法（如二分搜索、哈希）快速定位到表中的数据，而不需要扫描整个表。

  2. 减少数据访问：索引可以减少数据库需要读取的数据量，因为索引通常存储在内存中或者磁盘上单独的地方，访问速度比随机读取表数据快。

  3. 排序优化：索引本身就可以是有序的，这有助于优化ORDER BY查询，减少数据排序的时间。

  4. 避免复杂操作：对于涉及多个表的查询，索引可以减少连接操作的开销。

  5. 使用统计信息：数据库查询优化器可以使用索引的统计信息来制定更快的查询执行计划。

3.索引的类型：

  1. 主键索引：自动创建的，保证主键列的唯一性。

  2. 唯一索引：保证列的值是唯一的。

  3. 复合索引：在两个或更多列上创建的索引。

  4. 全文索引：用于对文本数据进行全文搜索。

  5. 空间索引：用于地理空间数据类型，以优化地理空间数据的查询。

4.创建索引示例

-- 创建简单的索引
CREATE INDEX index_name ON table_name (column1, column2);

-- 创建唯一索引
CREATE UNIQUE INDEX unique_index_name ON table_name (column);

-- 创建复合索引
CREATE INDEX composite_index_name ON table_name (column1, column2, column3);

-- 创建全文索引（MySQL 5.6及以上版本支持）
CREATE FULLTEXT INDEX ft_index_name ON table_name (text_column);

5.注意事项：

  • 虽然索引可以提高查询性能，但它们也可能降低数据插入、删除和更新操作的性能，因为索引需要维护。
  • 过多的索引可能导致查询优化器选择困难，因此需要合理设计索引。
  • 索引占用额外的磁盘空间。

在创建索引时，应该考虑查询需求和索引的成本，选择对性能提升最有帮助的列进行索引。同时，定期对索引进行维护和优化也是非常重要的。

四.聚簇索引和非聚簇索引

在数据库系统中，索引是一种提高数据检索速度的数据结构。聚簇索引（Clustered Index）和非聚簇索引（Non-Clustered Index）是两种不同类型的索引，它们在存储方式、性能影响以及使用场景上存在一些关键的区别：

1.聚簇索引（Clustered Index）

  1. 存储方式：聚簇索引决定了表数据在磁盘上的物理存储顺序。表中的每一行数据都存储在索引树的叶子节点上。
  2. 唯一性：一个表只能有一个聚簇索引，因为它决定了数据的物理存储方式。
  3. 性能：由于聚簇索引直接反映了数据的物理存储顺序，对于范围查询（如ORDER BY）非常高效。
  4. 主键索引：通常，主键会自动创建一个聚簇索引（除非明确指定为主键创建非聚簇索引）。

2.非聚簇索引（Non-Clustered Index）

  1. 存储方式：非聚簇索引是一种独立的数据结构，它存储索引列的值和指向数据行的逻辑指针（如行号或数据页地址）。
  2. 唯一性：一个表可以有多个非聚簇索引，它们不改变数据的物理存储方式。
  3. 性能：非聚簇索引对于点查询（如精确匹配）非常有效，但对于范围查询可能需要回表查询，因为索引和数据是分开存储的。
  4. 包含列：非聚簇索引可以包含额外的列，这些列不作为索引键，但可以被索引用于查询优化。

3.区别

  • 存储顺序：聚簇索引的顺序就是数据的物理存储顺序，而非聚簇索引不是。
  • 索引与数据位置：聚簇索引的叶节点直接包含数据，非聚簇索引的叶节点包含指向数据的指针。
  • 表的主键：通常，表的主键会自动创建聚簇索引，除非特别指定为非聚簇索引。
  • 数量限制：一个表只能有一个聚簇索引，但可以有多个非聚簇索引。
  • 维护成本：由于聚簇索引影响数据的物理存储，其维护成本可能更高，特别是在大量数据插入或删除时。

4.示例

-- 创建聚簇索引（通常为主键）
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 创建非聚簇索引
CREATE INDEX idx_username ON users (username);

在这个例子中，users 表的 id 列自动拥有一个聚簇索引。另外，我们为 username 列创建了一个非聚簇索引 idx_username。

5.使用场景

  • 聚簇索引：适用于经常通过索引键进行范围查询的场景，如时间序列数据。
  • 非聚簇索引：适用于需要快速定位特定值的场景，尤其是当查询不经常涉及索引键的范围时。

五.结语

过本文的深入探讨，你现在应该对MySQL中的几个关键概念有了全面的认识：

数据完整性：通过主键和外键约束来保证数据的准确性和一致性，即使在复杂的数据库关系中也能保持数据的引用完整性。
事务处理：掌握了事务的ACID属性和如何在MySQL中实现事务，包括设置隔离级别和处理事务的提交与回滚。
索引：了解了索引的类型、如何创建索引以及索引如何提升查询性能，同时也认识到了索引的维护成本和对写操作可能带来的影响。
聚簇索引与非聚簇索引：理解了这两种索引的存储方式、性能影响和适用场景，为选择合适的索引类型提供了指导。
这些知识为高效地使用MySQL数据库、优化数据库性能和维护数据完整性奠定了坚实的基础。在实际应用中，合理地应用这些概念可以显著提高数据库应用的稳定性和响应速度。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/603186.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

韦东山嵌入式Liunx驱动大全二

韦东山嵌入式Liunx驱动大全二

文章目录 一、LCD1-1 不同接口的LCD硬件操作原理1-2 LCD驱动程序框架1-3 结合APP分析LCD驱动程序框架1-4 LCD硬件时序图1-5 分析内核自带的LCD驱动程序1-6 编程LCD驱动程序框架_使用设备树1-7 LCD驱动程序框架_引脚配置1-8 LCD驱动程序框架_时钟配置1-9 LCD驱动程序框架_LCD控制…
阅读更多...
1069 微博转发抽奖

1069 微博转发抽奖

solution #include<iostream> #include<string> #include<map> using namespace std; int main(){int n, m, s, loop 0, have 0;string id;map<string, int> mp;cin >> m >> n >> s;for(int i 1; i < m; i){//编号从1开始cin…
阅读更多...
专题五_位运算(3)

专题五_位运算(3)

目录 137. 只出现一次的数字 II 解析 题解 面试题 17.19. 消失的两个数字 解析 题解 137. 只出现一次的数字 II 137. 只出现一次的数字 II - 力扣&#xff08;LeetCode&#xff09; 解析 注意这里指的是比特位上的01来进行统计的 题解 class Solution { public:int sin…
阅读更多...
Unity与C#的关系

Unity与C#的关系

第一&#xff0c;我们首先需要知道Unity与C#的关系是什么&#xff1f; 第二&#xff0c;我们要明白为什么Unity会使用C#&#xff0c;而不是C&#xff1f; 第三&#xff0c;我们需要知道Unity是怎么使用C#的&#xff1f; 第一点&#xff1a; 先说结论&#xff1a;C#是Unity用…
阅读更多...
windows如何安装python框架

windows如何安装python框架

windows如何安装Python框架&#xff1f;以安装Django框架为例&#xff0c;具体如下&#xff1a; 在idea上运行Python项目时&#xff0c;出现了如下错误&#xff0c;这是因为系统中只安装了Python&#xff0c;没有安装Django。 既然报错的原因就是缺少Django&#xff0c;那我们…
阅读更多...
深度解析 Spring 源码：揭秘 AbstractAutowireCapableBeanFactory 的 Bean 生命周期处理

深度解析 Spring 源码：揭秘 AbstractAutowireCapableBeanFactory 的 Bean 生命周期处理

文章目录 一、AbstractAutowireCapableBeanFactory 概述1.1 详细分析1.2 设计思想 二、深入解析AbstractAutowireCapableBeanFactory 的实现细节2.1 Bean 实例化过程分析2.1.1 createBean()2.1.2 createBeanInstance() 2.2 Bean 属性注入的实现机制2.2.1 populateBean()2.2.2 a…
阅读更多...
560. 和为 K 的子数组 974. 和可被 K 整除的子数组 【前缀和】

560. 和为 K 的子数组 974. 和可被 K 整除的子数组 【前缀和】

题目链接 ​​​​​​​974. 和可被 K 整除的子数组 560. 和为 K 的子数组 今天刷题的时候&#xff0c;刷了这两题&#xff0c;感觉挺有意思的。代码写起来挺简单的&#xff0c;但是思路和其中的细节以及涉及到的知识点确实让我挺意外的。这里写个博客解析一波&#xff0c;也是…
阅读更多...
Charles抓包工具

Charles抓包工具

Charles是一个HTTP代理工具&#xff0c;使开发人员能够查看客服端和服务器之间的所有HTTP/ HTTPS/SSL网络请求。 Charles是在PC环境下常用的网络抓包截取工具&#xff0c;在做移动开发时&#xff0c;我们为了调试客户端与服务端的网络通讯协议&#xff0c;常常需要截取网络请求…
阅读更多...
MCP3008-I/SL 模数转换器ADC SPI接口 模拟信号采集

MCP3008-I/SL 模数转换器ADC SPI接口 模拟信号采集

MCP3008-I/SL 模数转换器ADC 贴片SOIC16 MCP3008-I/SL 是一款模数转换器&#xff08;ADC&#xff09;&#xff0c;属于 SAR&#xff08;逐次逼近寄存器&#xff09;架构的 ADC。它具有以下特点&#xff1a; 8 通道单 ADC 最大采样率&#xff1a;200ksps&#xff08;千样点每秒…
阅读更多...
鸿蒙内核源码分析(编译环境篇) | 编译鸿蒙防掉坑指南

鸿蒙内核源码分析(编译环境篇) | 编译鸿蒙防掉坑指南

几点说明 kernel_liteos_a_note | 中文注解鸿蒙内核 是在 OpenHarmony 的 kernel_liteos_a 基础上给内核源码加上中文注解的版本.与官方源码按月保持同步,同步历史如下: 2021/10/09 – 增加性能优化模块perf,优化了文件映射模块2021/09/14 – common,extended等几个目录结构和M…
阅读更多...
文献速递：深度学习医学影像心脏疾病检测与诊断--基于深度学习的低剂量SPECT心肌灌注图像去噪：定量评估与临床表现

文献速递：深度学习医学影像心脏疾病检测与诊断--基于深度学习的低剂量SPECT心肌灌注图像去噪：定量评估与临床表现

Title 题目 Deep learning–based denoising of low‑dose SPECT myocardialperfusion images: quantitative assessment and clinical performance 基于深度学习的低剂量SPECT心肌灌注图像去噪&#xff1a;定量评估与临床表现 01 文献速递介绍 单光子发射计算机断层扫描&a…
阅读更多...
uniapp + vue3 设置 axios proxy 代理，并重写路径

uniapp + vue3 设置 axios proxy 代理，并重写路径

uniapp vue2 设置代理如下&#xff1a; 已生成的项目架构里面找到manifest.json文件&#xff0c;通过源码视图的方式打开文件&#xff0c;在文件中添加一下代码即可完成代理&#xff1a; "h5": {"devServer": {"disableHostCheck": true, //禁…
阅读更多...
基于StatefulSet控制器在Kubernetes上部署MySQL一主多从

基于StatefulSet控制器在Kubernetes上部署MySQL一主多从

一、前提--StatefuSet特性 1.1 有状态的节点控制器 -- StatefulSet 及其网络状态 容器的解决方案是针对无状态应用场景的最佳实践&#xff0c;但对于有状态应用来说&#xff0c;就并非如此了。Kubernetes 用 StatefulSet 解决了有状态应用编排的问题&#xff0c;本文我们就来…
阅读更多...
GitHub介绍，GitHub如何订阅充值？

GitHub介绍，GitHub如何订阅充值？

一、GitHub介绍 GitHub是一个面向开源及私有软件项目的托管平台&#xff0c;因为只支持git 作为唯一的版本库格式进行托管&#xff0c;故名Github。 GitHub于2008年4月10日正式上线&#xff0c;除了git代码仓库托管及基本的Web管理界面以外&#xff0c;还提供了订阅、讨论组、…
阅读更多...
爬取深圳2024年链家二手房数据，共3000条数据（其他城市也可）

爬取深圳2024年链家二手房数据，共3000条数据（其他城市也可）

文章目录 专栏导读1.目标2.导入相关库3.获取每个二手房的链接4.获取每个链接中的相关数据5.保存数据6.数据展示 专栏导读 ✍ 作者简介&#xff1a;i阿极&#xff0c;CSDN 数据分析领域优质创作者&#xff0c;专注于分享python数据分析领域知识。 ✍ 本文录入于《python网络爬虫…
阅读更多...
探索数据结构

探索数据结构

什么是数据结构 数据结构是由&#xff1a;“数据”与“结构”两部分组成 数据与结构 数据&#xff1a;如我们所看见的广告、图片、视频等&#xff0c;常见的数值&#xff0c;教务系统里的&#xff08;姓名、性别、学号、学历等等&#xff09;&#xff1b; 结构&#xff1a;当…
阅读更多...
Pandas进阶

Pandas进阶

文章目录 第1关&#xff1a;Pandas 分组聚合第2关&#xff1a;Pandas 创建透视表和交叉表 第1关&#xff1a;Pandas 分组聚合 编程要求 使用 Pandas 中的 read_csv() 函数读取 step1/drinks.csv 中的数据&#xff0c;数据的列名如下表所示&#xff0c;请根据 continent 分组并…
阅读更多...
VMware 虚拟机自定义规范 - 更优雅的虚拟机开局

VMware 虚拟机自定义规范 - 更优雅的虚拟机开局

介绍 虚拟机自定义规范可以在你克隆虚拟机的时候在vCenter 的Web界面设定虚拟机的主机名、单/多网卡IP的IP和网关、DNS服务器、唯一标识符重置&#xff08;SID等&#xff09;、硬盘分区自动扩容、设定密码、密钥、时区等信息。 让管理员不需要进入虚拟机系统内部进行配置&…
阅读更多...
10000字讲解IoC 思想以及五大注解

10000字讲解IoC 思想以及五大注解

文章目录 IoC 思想通过案例讲解 IoC1.传统的开发方式 SpringIoC 和 DI五大注解ControllerServiceComponentRepositoryConfiguration 为什么要有这么多的类注解类注解之间的关系方法注解 Bean重命名 bean扫描路径 IoC 思想 什么是 Spring 呢&#xff1f; 我们经常听到的都是说…
阅读更多...
Android 13 aosp 默认关闭SELinux

Android 13 aosp 默认关闭SELinux

通过adb修改 adb root adb shell setenforce 0 // 开SELinux&#xff0c;设置成模式permissive adb shell setenforce 1 // 关SELinux&#xff0c;设置成模式enforce adb shell getenforce // 获取当前SELinux状态源码修改 Android_source/system/core/init/selinu…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站