【MySQL数据库】索引与事务

2024-06-02 13:41:59

  ????个人主页： 中草药

????专栏：【MySQL】探秘：数据库世界的瑞士军刀

目录

????️一.索引

????1.工作原理

????2.类型

????3.作用 

????4.优缺点

????5.使用

????6.索引的设计与管理

????二.事务

????1.事务的ACID特性*

????2.使用

????3.事务的隔离性

3.1. 脏读（Dirty Read）

3.2. 不可重复读（Non-Repeatable Read）

3.3. 幻读（Phantom Read）

3.4.隔离级别的解决

3.5.使用事务的场景

✉️三.总结与反思

????️一.索引

        索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引并指定索引的类型，各类索引有各自的数据结构实现。主要目的是为了加快数据检索的速度。索引使得数据库管理系统(DBMS)能够迅速地定位到表中特定的行，从而提升查询效率。下面是关于MySQL索引的一些关键点和详细说明：

????1.工作原理

  1. 数据结构：最常见的索引类型是B-Tree索引（B树）。在MySQL中，索引被实现为一种平衡的树形结构（通常是B+树），这种结构允许快速查找、插入和删除操作。索引中存储了表中一列或多列的值（索引键）和这些值对应的行在表中的物理位置（通常是行ID或指针）。

  2. 查询过程：当执行一个查询时，数据库首先查看索引，而不是直接遍历整张表。通过索引，数据库可以迅速定位到包含目标值的行，然后再通过行ID或指针访问实际的行数据。

????2.类型

  • B-Tree索引：是最通用的索引类型，适用于全值匹配、范围查询、排序和分组等操作。
  • Hash索引：适用于等值比较查询，通过哈希函数将索引列的值转换成哈希码，然后直接定位到相应行。不支持范围查询。
  • 全文索引：专为大文本字段设计，用于全文搜索，可以高效地处理LIKE '%keyword%'这类模糊查询。
  • R-Tree索引：用于空间数据类型的索引，如GIS地理坐标数据。

​

​

????3.作用 

  • 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
  • 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
  • 索引对于提高数据库的性能有很大的帮助。

????4.优缺点

优点

  • 提高查询速度：通过索引直接定位数据，减少全表扫描。
  • 加速排序和分组操作：如果排序或分组的列上有索引，数据库可以直接使用索引来避免额外的排序步骤。
  • 覆盖索引：如果查询所需的全部数据都在索引中（即索引包含了查询的所有列，无需回表查询），这将进一步提升查询性能。

缺点

  • 占用存储空间：每个索引都需要额外的磁盘空间来存储索引数据。
  • 影响写操作性能：插入、删除和更新操作需要维护索引结构，可能会减慢这些操作的速度。
  • 选择性问题：如果索引的选择性不高（即索引列的值重复度高），索引的效果会大打折扣。

使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

  • 数据量较大，且经常对这些列进行条件查询。
  • 该数据库表的插入操作，及对这些列的修改操作频率较低。
  • 索引会占用额外的磁盘空间。
满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

????5.使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。
查看索引 
show index from 表名;

案例：查看学生表已有的索引
show index from student;
创建索引 
create index 索引名 on 表名(字段名);


案例：创建班级表中，name字段的索引
create index idx_classes_name on classes(name);

删除索引

drop index 索引名 on 表名;

案例：删除班级表中name字段的索引
drop index idx_classes_name on classes;

????6.索引的设计与管理

  • 选择索引列：通常选择出现在WHERE子句、JOIN条件、ORDER BY和GROUP BY中的列作为索引。
  • 复合索引：当一个查询涉及多个列时，可以创建包含多个列的复合索引，但需注意索引列的排列顺序。
  • 监控和优化：定期分析索引的使用情况，移除无效或很少使用的索引，调整或新增索引来适应查询模式的变化。

综上所述，索引是MySQL性能优化的重要手段，但需要根据实际情况合理设计和管理，以达到最佳的性能与存储空间的平衡。

????二.事务

        MySQL中的事务（Transaction）是数据库操作的基本单位，它能确保一系列操作要么全部成功，要么全部失败，以此来维护数据的一致性和完整性。事务是关系型数据库中的一个核心特性，特别是在处理金融、银行、电子商务等领域中对数据准确性要求极高的应用场景时尤为重要。以下是MySQL中事务的几个关键概念和操作：

????1.事务的ACID特性*

  1. 原子性（Atomicity）：事务被视为一个不可分割的最小工作单元，事务中的所有操作要么全部执行，要么都不执行。
  2. 一致性（Consistency）：事务执行前后，数据库的状态都必须保持一致，即符合所有的预定义规则。
  3. 隔离性（Isolation）：多个事务并发执行时，彼此之间互不影响，仿佛是在串行执行一样。MySQL提供了多种事务隔离级别来控制这一特性。下文做详细概述：
  4. 持久性（Durability）：一旦事务提交，其结果就会永久保存在数据库中，即使系统发生故障也不会丢失。

????2.使用

在MySQL中，可以通过以下命令来管理事务：

  • 开始事务：在默认的自动提交模式下，每条SQL语句都会作为一个单独的事务执行。要手动控制事务，首先需要关闭自动提交模式，使用SET autocommit = OFF;命令。
  • 执行事务中的操作：在此之后执行的SQL语句属于同一事务。
  • 提交事务：使用COMMIT;命令来确认并保存事务中的更改。
  • 回滚事务：如果事务中的某些操作失败，或者决定放弃已经做出的更改，可以使用ROLLBACK;命令撤销整个事务中的所有操作。
start transaction;

-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';

-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;

????3.事务的隔离性

        事务的隔离性问题主要体现在三种现象上：脏读（Dirty Read）、不可重复读（Non-Repeatable Read）和幻读（Phantom Read）。这些现象发生在并发事务处理时，不同的事务隔离级别会不同程度地解决这些问题。以下是这三种现象的具体解释：

3.1. 脏读（Dirty Read）

  • 定义：当一个事务读取了另一个事务尚未提交的数据时，如果后者进行了回滚，那么前者读取到的就是“脏数据”，即从未真正存在于数据库中的数据。
  • 示例：事务T1更新了一行记录，但还未提交。此时，事务T2读取了T1更新后的数据。如果T1随后因某种原因被回滚，那么T2之前读取的数据就是无效的，即发生了脏读。

3.2. 不可重复读（Non-Repeatable Read）

  • 定义：在一个事务内，两次读取同一行数据，由于另一个并发事务在这两次读取之间对该行数据进行了修改并提交，导致两次读取的结果不一致。
  • 示例：事务T1读取某一行数据，事务T2随后更新了该行数据并提交。当T1再次读取该行数据时，发现数据内容与第一次读取时不同，尽管T1本身没有对数据进行任何修改，这就是不可重复读。

3.3. 幻读（Phantom Read）

  • 定义：在一个事务内，两次执行相同的查询，第二次查询结果中出现了第一次查询结果中没有的新行，这是因为另一个事务在这两次查询之间插入了新数据。
  • 示例：事务T1执行一个查询，返回所有满足某个条件的行。在同一时间，事务T2插入了一行新数据，恰好也满足T1的查询条件，并提交了事务。当T1再次执行同样的查询时，发现多出了一行记录，就像幻影一样出现，这就是幻读。

3.4.隔离级别的解决

  • 读未提交（READ UNCOMMITTED）：允许所有三种问题发生。
  • 读已提交（READ COMMITTED）：解决了脏读问题，但不可重复读和幻读仍可能发生。
  • 可重复读（REPEATABLE READ）：解决了脏读和不可重复读的问题，但在标准的SQL隔离级别定义中，理论上幻读仍可能发生。然而，在MySQL的InnoDB引擎中，通过多版本并发控制（MVCC）机制，实际上很大程度上避免了幻读的发生，他同时也是MySQL的默认级别。
  • 串行化（SERIALIZABLE）：通过强制事务串行执行，解决了所有三种问题，但这是以牺牲并发性为代价的。

选择合适的事务隔离级别需要权衡数据一致性和系统性能之间的关系。在大多数应用中，可重复读（REPEATABLE READ）是一个平衡的选择。

3.5.使用事务的场景

  • 当你需要确保一系列数据库操作要么全部成功，要么全部失败时，应该使用事务。
  • 在转账、库存管理、订单处理等需要精确数据一致性的场景中，事务是必不可少的。

理解并正确使用事务，对于开发高性能、高可靠性的数据库应用程序至关重要。

✉️三.总结与反思

任凭怎样脆弱的人，只要把全部的精力倾注在唯一的目的上，必能使之有所成就。——西塞罗

        在深入学习MySQL的索引与事务后，我深刻体会到这两个概念不仅是数据库技术的核心组成部分，也是构建高性能、高可用数据库应用的关键。以下是我对这两个主题的学习总结与反思。

索引

重要性：索引是数据库优化的利器，它通过减少数据检索的时间复杂度，极大地提升了查询效率。理解索引的工作原理，特别是B-Tree结构，对于合理设计索引至关重要。

索引设计的艺术：选择合适的列建立索引，考虑列的选择性、查询频率以及是否涉及范围查询等因素。复合索引的顺序安排也是优化查询的关键。此外，认识到索引并非越多越好，过多的索引会占用额外的存储空间，并可能降低写操作的性能。

监控与调优：通过EXPLAIN分析查询计划，了解索引的实际使用情况，定期评估并调整索引策略。学会利用MySQL的性能监控工具，如SHOW INDEX、INFORMATION_SCHEMA库等，来辅助决策。

反思：在实践中，我意识到初期对索引的过度依赖有时会导致忽视了对查询逻辑本身的优化。未来，我应更加注重平衡索引的利弊，结合查询优化和合理的数据库设计来提升整体性能。

事务

事务的基石：ACID：理解事务的ACID属性（原子性、一致性、隔离性、持久性）是掌握事务管理的基础。每个属性都有其在保证数据完整性和一致性方面的关键作用。

隔离级别与并发控制：深入学习了四种事务隔离级别（读未提交、读已提交、可重复读、串行化）及其权衡，认识到不同的业务场景需要不同的隔离级别来平衡并发性和数据一致性。特别是InnoDB引擎的MVCC机制如何在可重复读级别上减少了幻读现象。

事务的实战应用：实践操作中，明确事务边界，适时使用BEGIN、COMMIT、ROLLBACK命令来控制事务流程，是防止数据不一致和错误累积的有效手段。同时，对死锁的理解和预防也是在并发环境下不可或缺的知识点。

反思：事务管理在实际应用中往往比理论学习更为复杂，尤其是在高并发场景下，正确选择隔离级别和处理事务冲突成为一大挑战。我需要更多地通过实战案例来深化理解，尤其是如何在特定场景下灵活运用SAVEPOINT、锁机制等高级特性，以提高系统的稳定性和响应速度。

        总之，学习MySQL的索引与事务是一个不断实践、反思和优化的过程。通过理论学习结合实际应用，我不仅加深了对这些核心概念的理解，也意识到了在实际工作中持续优化和学习的重要性。未来，我将继续探索更深层次的数据库知识，不断提升数据库设计和优化的能力

????????????????????????????????????????????????????????????????????????????????????????????????????????????????

以上，就是本期的全部内容啦，若有错误疏忽希望各位大佬及时指出????

  制作不易，希望能对各位提供微小的帮助，可否留下你免费的赞呢????

上一篇：ETCD 备份与还原


下一篇：SpringBoot读取json文件