【MySQL】索引与事务

一.索引

1.1 概念

索引就是一种特殊的文件，包含着对数据表内所有记录的引用指针。可以对表内的一列或多列创建索引，并指定索引的类型，各类索引各有各自的数据结构实现。类似于书的目录，能够让数据库引擎迅速定位到数据数据所在的位置，从而大大提高查询效率。

1.2 工作原理

1.数据结构：常见的索引数据结构包括B树（B-Tree）、哈希表、位图索引等，B-Tree是一种自平衡的树结构，能够保持数据排序，支持高效的查找、插入和删除操作。每个节点可以包含多个键值对，这减少了树的高度，从而加快了查找速度不同的数据结构适用于不同的场景，以达到最佳的查询性能。

2.查询：通过索引对数据进行查询，能够提高查询效率，若通过select这样的查询，默认是按照遍历的方式来进行查询。例如，在使用where语句条件查询时，遍历每一行并代入到条件中，判断是否满足条件，满足的留下，不满足的就舍掉。通过这样遍历，时间复杂度为O(n).

注意：遍历每一行时，都需要在硬盘内进行读取，此处的O(n)与内存中的操作O(n)有着本质的区别。

1.3作用

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

1.4索引的优缺点

1.优点：

• 加快查询：通过索引直接定位数据，避免全表扫描，提高了查询效率。
• 支持分组和排序：索引可以帮助数据库更快地完成排序和分组操作，特别是在当排序或分组依据的列已经建立索引时。
• 覆盖索引：如果索引包含查询所需的所有字段，数据库可以直接从索引中读取数据而无需访问标，进一步提高效率。
• 减少磁盘I/O：通过减少需要读取的数据量，索引能有效降低磁盘I/O操作，对I/O密集型应用有去重要。

2.缺点：

• 占用存储空间：索引需要额外的存储空间，尤其是对于大型数据库，索引文件可能比原始数据还要大。
• 影响写操作性能：插入、更新和删除数据时，数据库不仅要修改数据本身，还需要相应地维护索引结构，这会增加操作的复杂度和耗时。
• 维护成本：随着数据的增删改，索引需要不断更新以保持其朱雀性，这会增加系统的维护负担。

1.5适用场景

在考虑对数据库的某列或某几列创建索引时，需要考虑：

• 数据量较大，且经常度这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段场景索引，以提高查询效率。若对于非条件查询列，或经常做插入、谢盖操作，或磁盘空间不足时，不考虑场景索引。

1.6索引的使用

在创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

• 查看索引

-- 查看索引
show index from 表名;

示例：查看学生表中已有的索引
show index from student;

• 创建索引

-- 创建索引
create index 索引名 on 表名(字段名);

示例：创建学生表中name字段的索引
create index index_name on student(name);

• 删除索引

-- 删除索引
drop index 索引名 on 表名;

示例：删除学生表中name字段的索引
drop index index_name on student;

注意：在删除索引的时候，只能删除自己创建的索引，不能删除删除自动生成的，删除索引也是个危险操作。

1.7索引背后的数据结构

在数据结构，具有查找功能的数据结构有两个：二叉搜索树、哈希表。

二叉搜索树：一种经典、基于比较的高效数据结构，适合对内存中的数据操作，但在数据库索引等等需要处理大量数据和磁盘I/O 的场景中，并不是最优选择。

高度问题：在最坏的情况下（树完全不平衡时）可能退化成链状结构，导致树的高度接近节点数量，大大增加了查询时磁盘I/O次数。

哈希表：哈希表是key值通过哈希函数映射到哈希表的一种数据结构。哈希表的查找时间复杂度为O（1），但最大的问题在于，哈希表只能进行“相等”查询，无法进行>、<这样的范围查询，也无法进行like模糊查询。

因此，需要用B+树

1.B+树

B+树是专门为数据库索引量身定做的一种特殊的数据结构。

2.B+树的优势

• N叉搜索树，高度较低，进行的磁盘I/O次数较少
• 叶子节点是全集，并且用链表结构连接，非常便于范围查询
• B+树所有的查询都是要落到叶子节点上完成的，任何一次查询，经历的I/O次数和比较次数都是差不多的，查询的开销稳定。
• 由于B+树，叶子节点是全集，非叶子节点上不比存储“数据行”，只需要存储索引列的key即可。使得非叶子节点消耗的空间比较少。

二.事务

MySQL中事务(Transaction)是数据库操作的基本单位，能确保一系列操作要么全部成功，要么全部失败。

1.事务的ACID特性

• 原子性(Atomicity):事务被视为一个不可分割的最小工作单位，事务中的操作要么全部成功，要么全部不执行。如果事务中的某一部分失败，则整个事务都会被回滚，仿佛从未开始过。
• 一致性(Consistency):事务执行前后，数据库从一种一致状态转换到另一种一致状态。即使在事务执行期间出现错误，数据库也必须保持其完整性约束不变。
• 隔离性(Isolation):多个事务并发执行时，每个事务都感觉像是在单独操作数据库一样，不受其他事务影响。这是通过不同的隔离级别来实现实现的。如：读未提交（Read uncommitted)、读已提交（Read committed）、可重复读（Repeatable Read）和串行化（Serializable****）.
• ** 持久性(Durability):一旦事务被提交，其结果就是永久性的，即使系统发生故障也不会丢失，这意味着事务对数据库的修改被持久化存储。**

2.事务的隔离性

在事务并发处理时可能会出现三种典型异常现象，即事务的并发异常。

1.脏读（Dirty Read）

定义：当一个事务读取了另一个事务未提交的数据时，若后者进行了回滚操作，那么前者读的数据就是无效的数据，这种情况就称为脏读。

示例：事务A读取了事务B未提交的数据，但事务B此时由于某种原因，发生了回滚，那么事务A读到的数据就是无效的，就发生了脏读。

2.不可重复读（Non-Reapeatable Read）

定义：在同一个事务中，如果两次读取统一数据，期间由于其他事务提交了对该数据的修改或删除操作，导致这两次读取的结果不同，这种现象就称为不可重复读。

示例：事务A读取了事务B的数据，但随后事务B又由于某种原因，对数据进行了修改，当事务A再次读取时，就会发现与第一次读取的结果不同。这就是不可重复读。

3.幻读（Phantom Read）

定义：幻读发生在当一个事务按某个条件第一次查询数据库时获取到一些记录，之后另一个事务插入了符合该查询条件的新记录并提交，当第一个事务再次按照相同条件查询时，会发现多出了之前不存在的记录，就像幻觉一样，故称为幻读。

示例：事务A执行查询操作，返回所有满足条件的数据，同时，事务B插入了一行新的数据，这一行数据同时满足事务A的查询条件，并提交了数据。当事务A再执行查询时，就会发现多出了一行新的数据，这就是幻读。

3.事务的隔离级别

为了解决上述三种情况，数据库系统提供了几种不同的事务隔离级别，以平衡事务之间的隔离性和系统性能。

1.读未提交(Read uncommitted):一个事务可以读取另一个事务未提交的数据。此时三种情况都可能会发生。但同时，多个事务并发执行程度是最高的，执行速度也是最快的。

2.读已提交(Read committed):一个事务只能读取另一个事务已经提交之后的数据，解决了脏读问题，但不可重复读、幻读可能会发生。此时，并发程度降低，执行速度变慢，事务之间的隔离性提高了。

3.可重复读（Reapeatable Read):解决了脏读和不可重复读问题，但仍可能发生幻读。

4.串行化(Serializable):解决了三种情况，避免了所有并发问题。此时并发程度最低，执行速度最慢。隔离性最高。

在设计系统时，需要根据实际需求权衡利弊来选择合适的隔离级别。

4.事务的使用

1.开启事务：在MySQL中，我们可以使用（START TRANSACTION）来开启事务。大多数数据库系统默认采用自动提交模式，即每条单独的SQL语句执行后都会自动提交。使用 START TRANSACTION 可以暂时关闭自动提交，直到事务被显式提交或回滚。

2.提交：使用（COMMIT）命令，可以将事务中的修改进行保存。

3.回滚事务：如果事务中的某些操作失败，或放弃已更改的数据，可以使用（ROLLBACK）撤销在事务中的所有操作。

示例：

start transaction;
-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit

总结

索引

在数据库中，使用索引能帮助我们快速进行查找数据，但与此同时，索引也会占用额外的空间，在删除索引或者新增索引时，需要谨慎考虑。若在大型表中，创建一个索引，可能会消耗大量的CPU和I/O资源，导致数据库性能下降，甚至会影响起亚查询和写入操作。删除索引比场景索引快，但同样需要数据库资源，可能会影响某些查询的效率。

事务

1. 明确事务边界：确保每个逻辑操作单元都被正确的包裹在事务之中，明确事务的开始（可以使用BEGIN TRANSACTION）和结束（通过COMMIT或ROLLBACK）;
2. 选择合适的隔离级别：根据业务需要，选择合适的隔离级别，以平衡并发性能和数据一致性风险。
3. 资源管理：确保事务完成后正确关闭数据库连接或释放资源，避免资源泄露。
4. 监控和日志：记录事务的开始、结束及异常信息，便于追踪和审计。监控事务的执行时间和成功率，及时发现并解决性能瓶颈或错误。