【MySQL】索引与事务

目次

一.索引
1.1 概念
1.2 工作原理
1.3作用
1.4索引的优缺点
1.5适用场景
1.6索引的使用
1.7索引背后的数据布局
1.B+树
 2.B+树的上风
二.事务
1.事务的ACID特性
2.事务的隔离性
1.脏读（Dirty Read）
2.不可重复读（Non-Reapeatable Read）
3.幻读（Phantom Read）
3.事务的隔离级别
4.事务的使用
总结

---

一.索引

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）时，会主动创建对应列的索引。

• 查看索引
  

1. -- 查看索引
2. show index from 表名;
4. 示例：查看学生表中已有的索引
5. show index from student;

复制代码

• 创建索引
  

1. -- 创建索引
2. create index 索引名 on 表名(字段名);
4. 示例：创建学生表中name字段的索引
5. create index index_name on student(name);

复制代码

• 删除索引
  

1. -- 删除索引
2. drop index 索引名 on 表名;
4. 示例：删除学生表中name字段的索引
5. 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）打消在事务中的所有操作。
示例：

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

复制代码

总结

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

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。