文章目录
MySQL事务与存储引擎
MySQL事务是一种机制，用于保证数据库操作的一系列动作要么全部执行成功，要么全部回滚不执行。它具有原子性、一致性、持久性、隔离性。
1. 原子性
原子性是指MySQL事务作为一个不可分割的工作单元，要么全部成功，要么全部失败回滚。
2.一致性
一致性是指MySQL数据从一个一致状态转变成另一个一致状态，保证数据完整。
3.持久性
持久性是指MySQL事务一经提交就会写到硬盘里
4隔离性
一个事务的执行不应影响其他并发事务，保证事务之间的隔离性。
MySQL事务的隔离级别
当多个客户端并发地访问同一个表时，可能出现一致性问题，如脏读、幻读、不可重复读、丢失更新。为了减少这些情况的发生，可以通过需求来调整MySQL事务隔离的策略。
多客户端隔离产生的影响
脏读
定义：一个事务读取了另一个未提交事务修改过的数据。
例子：事务A读取了事务B尚未提交的数据，如果事务B回滚，那么事务A读取的数据就是无效的。
例子事务A读取了事务B尚未提交的数据，如果事务B回滚，那么事务A读取的数据就是无效的。
幻读
定义：一个事务在读取满足某些条件的一组记录时，另一个事务插入了一些满足这些条件的记录，前一个事务再次读取时，会发现有新的满足条件的记录出现。
例子：事务A执行了一次范围查询，事务B插入了一些符合该查询条件的记录并提交，事务A再次执行同样的范围查询时，会发现有新的记录。
不可重复读
定义：一个事务在读取某些数据后，另一个事务修改了这些数据，前一个事务再次读取同样的数据时得到了不同的结果。
例子：事务A读取一条记录，事务B修改或删除了该记录并提交，当事务A再次读取该记录时，数据已经改变或丢失。
丢失更新
定义：两个事务读取同一数据，然后基于最初读取的数据进行计算，最后都尝试基于最初的数据进行更新，这样就会导致其中一个事务的更新丢失。
例子：事务A和事务B都读取了同一条记录，然后事务A和事务B都对这条记录进行了修改并提交，最后只有最后一个提交的事务的修改被保留，另一个事务的修改被覆盖。
隔离级别
未提交读
未提交读是数据库事务隔离级别中的一种，它是最宽松的隔离级别。在未提交读隔离级别下，一个事务可以读取另一个事务尚未提交的数据变更。
缺点：可能会出现脏读、幻读、不可重复读，数据一致性较低。
优点:由于不等待其他事务提交，未提交读可以提供较高的并发性能。
生产环境中一般不会选择
提交读
提交读是数据库事务隔离级别中的一种，它比未提交读更严格一些。在提交读隔离级别下，一个事务只能读取其他事务已经提交的数据变更。
优点：防止脏读、数据一致，性并发性和性能弱于未提交读但是任然提供了较好的性能
缺点：可能会出现 不可重复读、幻读。
在实际应用中，提交读隔离级别通常被认为是一种合理的中庸选择，因为它既能够防止脏读，又不会过度限制系统的并发能力。然而，如果应用需要更高的数据一致性，例如防止不可重复读和幻读，那么可能需要使用更严格的隔离级别
可重复读（MySQL默认事务隔离级别）
优点：防止脏读、防止不可重复读、数据具有一致性
缺点 ：幻读仍然可能发生、并发性和性能相较于提交读较低
在实际应用中，可重复读隔离级别是MySQL的默认事务隔离级别。它适用于需要较高数据一致性的场景，同时还能提供合理的并发处理能力。
串行读
优点：最高的一致性保证、防止所有并发问题
缺点：性能太弱，只能一个用户操作
在实际应用中，选择串行读隔离级别需要谨慎，因为它可能会对系统性能产生显著影响。通常，只有在其他更宽松的隔离级别无法满足业务需求时，才会考虑使用串行读。
MySQL存储引擎
InnoDB
InnoDB是MySQL数据库的一个流行的存储引擎，它由Innobase Oy公司开发，后被Oracle公司收购。InnoDB是一个事务安全的存储引擎，它支持事务处理、行级锁定和外键约束。InnoDB的目标是为提供高性能、高可靠性和自动崩溃恢复的特性。
以下是InnoDB的一些主要特点：
- 事务支持：InnoDB支持事务，并且符合ACID（原子性、一致性、隔离性和持久性）原则。这意味着InnoDB可以确保事务要么完全执行，要么完全不执行，从而保持数据的完整性。
- 行级锁定：InnoDB使用行级锁定机制，这可以提高多用户并发操作的性能，因为它只锁定受影响的行，而不是整个表。
- 外键约束：InnoDB支持外键约束，这可以帮助维护数据库的引用完整性。
- 崩溃恢复：InnoDB提供了自动崩溃恢复功能，这意味着即使在数据库崩溃的情况下，InnoDB也能恢复未提交的事务，并保持数据的一致性。
- 多版本并发控制（MVCC）：InnoDB使用MVCC来实现高并发，这允许读取操作不阻塞写入操作，并 vice versa，从而提高了性能。
- 缓冲池：InnoDB使用缓冲池来缓存数据和索引，这可以减少磁盘I/O操作，提高数据库的性能。
- 在线备份：InnoDB支持在线备份（热备份），这意味着可以在不关闭数据库的情况下进行备份。
- 性能优化：InnoDB包括许多性能优化特性，如索引优化、自适应哈希索引和动态缓存大小调整。
由于这些特点，InnoDB成为许多需要高性能和可靠性的在线事务处理（OLTP）系统的首选存储引擎。然而，InnoDB也有一些缺点，比如相对于其他存储引擎，它的性能开销可能更高，尤其是在写操作密集的场景下。此外，它的数据存储格式与其他MySQL存储引擎不同，这可能会影响数据迁移和备份的策略。
MyISAM
MyISAM是MySQL数据库管理系统中的一个存储引擎，它是在MySQL的早期版本中引入的，并且在很长一段时间内是MySQL的默认存储引擎。MyISAM以其高速读操作和简化管理的特性而闻名，但它不支持事务处理和行级锁定。以下是MyISAM存储引擎的一些主要特点：
- 高速读操作：MyISAM存储引擎对于只读或读操作远多于写操作的场景非常有效，因为它在处理读操作时通常比InnoDB更快。
- 表级锁定：MyISAM使用表级锁定，这意味着在执行写入操作时，整个表会被锁定，这可能会影响并发性能，特别是当有多个写入操作同时进行时。
- 不支持事务：MyISAM不支持事务处理，因此它不适合需要原子性、一致性、隔离性和持久性（ACID）保证的场景。
- 不支持外键约束：MyISAM存储引擎不支持外键约束，这可能会影响到数据库的引用完整性。
- 不支持崩溃恢复：MyISAM在崩溃恢复方面不如InnoDB，因为它不记录事务日志。如果数据库崩溃，可能会发生数据损坏，尽管MySQL提供了修复工具来尝试恢复损坏的表。
- 空间存储效率：MyISAM通常比InnoDB更节省空间，因为它不存储事务日志和回滚段信息。
- 全文索引：MyISAM支持全文索引，这是一种用于优化大型文本数据的搜索效率的索引类型。
- 数据压缩：MyISAM支持表级别的压缩，可以减少磁盘空间的使用。
由于MyISAM的这些特点，它适合于读密集型应用，如数据仓库或内容管理系统，这些系统通常不需要事务处理和复杂的并发控制。然而，对于需要高并发、事务支持和数据完整性的在线事务处理（OLTP）系统，InnoDB或其他支持事务的存储引擎通常是更好的选择。
随着MySQL的发展，InnoDB存储引擎得到了显著改进，并在很多方面超越了MyISAM，包括性能、可靠性和功能集。因此，InnoDB成为了MySQL 5.5及以后版本中的默认存储引擎。尽管如此，MyISAM仍然在某些特定的使用场景中有一席之地。
事务控制语句
1.关闭事务自动提交
set autocommit = 0
2.开启事务自动提交
set autocommit = 1
查看与修改隔离级别
查看
SELECT @@global.tx_isolation;
设置读未提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
设置提交读
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
设置可重复读
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
设置串行读
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
存储引擎
存储引擎是数据库系统的一个重要组成部分，它负责数据的存储、管理和访问。不同的存储引擎有不同的特点和适用场景。在数据库系统中，存储引擎通常作为一个插件或模块存在，可以根据需求进行选择和配置。
下面我将要介绍两个常用的存储引擎InnoDB和MyISAM
InnoDB和MyISAM是MySQL数据库中最常用的两种存储引擎，它们在设计理念、功能和适用场景上有着显著的不同。
InnoDB
InnoDB是MySQL数据库中一个高性能、事务安全的存储引擎，它在许多方面与MyISAM不同，特别是在事务处理、锁定粒度、数据完整性和并发性方面。以下是InnoDB存储引擎的详细介绍：
- 事务支持：InnoDB最显著的特点是它提供了完整的事务支持，包括ACID（原子性、一致性、隔离性和持久性）事务属性。这意味着对于一系列数据库操作，要么全部成功，要么全部失败，确保了数据的一致性和完整性。
- 行级锁定：与MyISAM的表级锁定不同，InnoDB使用行级锁定（row-level locking），这在多用户环境中提供了更高的并发性。行级锁定允许同时对同一表的不同行进行读写操作，而不会相互阻塞，从而提高了性能。
- 外键约束：InnoDB支持外键约束（foreign key constraints），这有助于维护引用完整性，确保相关表之间的一致性。外键约束可以防止非法数据插入，从而保护数据的完整性。
- MVCC：InnoDB使用多版本并发控制（MVCC）来实现高并发性。MVCC允许读取操作不阻塞写入操作，反之亦然，因为它可以为每个事务提供一个数据库的快照视图。
- 崩溃恢复：InnoDB包括自动崩溃恢复功能，这意味着在数据库崩溃或服务器故障后，InnoDB可以自动修复数据，确保数据的一致性和持久性。
- 聚簇索引：InnoDB使用聚簇索引（clustered index）来存储数据，这意味着数据行按主键顺序存储。如果表没有显式定义主键，InnoDB会生成一个隐藏的主键。聚簇索引可以显著提高具有主键查询的性能。
- 双写缓冲区：InnoDB使用双写缓冲区（doublewrite buffer）来提高数据的可靠性。在写入数据到实际位置之前，InnoDB先将数据写入到一个双写缓冲区，如果写入过程中发生崩溃，InnoDB可以使用这个缓冲区来恢复数据。
- 自适应哈希索引：InnoDB会根据访问模式自动创建哈希索引，这可以加快查询速度。这种索引是动态的，InnoDB会根据工作负载的变化调整哈希索引。
- 在线备份：InnoDB支持热备份，这意味着可以在不中断数据库操作的情况下进行备份，这对于需要高可用性的应用非常重要。
InnoDB存储引擎适合需要高并发、事务处理和数据完整性要求较高的应用场景，如在线事务处理（OLTP）系统。由于其复杂性，InnoDB在某些只读或插入操作为主的场景中可能不如MyISAM快，但随着MySQL的不断优化，InnoDB在很多方面的性能已经可以与MyISAM相媲美，甚至在某些情况下更优。
MyISAM
MyISAM是MySQL数据库中的一个存储引擎，它以其快速访问速度和简单的存储机制而闻名。MyISAM存储引擎不支持事务处理和外键约束，这使得它在处理大量只读操作或插入操作为主的场景中表现出色。以下是MyISAM存储引擎的详细介绍：
- 存储机制：MyISAM将表的数据和索引分开存储，数据文件以.MYD（MYData）为扩展名，索引文件以.MYI（MYIndex）为扩展名。此外，每个表还有一个以.frm为扩展名的文件，用于存储表结构的定义。
- 表级锁定：MyISAM使用表级锁定（table-level locking），这意味着在执行写入操作时，整个表会被锁定，从而防止并发写入。虽然这可能导致写入操作时的串行化，但在读取操作频繁的场景中，MyISAM可以提供快速的访问速度。
- 存储格式：MyISAM表支持三种不同的存储格式，以适应不同的数据存储需求：
- 静态表：这是默认的存储格式，适用于所有字段都是固定长度的场景。每个记录占用固定长度的空间，这有利于快速存储和缓存，并且在出现故障时容易恢复。缺点是可能占用更多的空间。
- 动态表：动态表适用于包含可变长度字段的场景，如VARCHAR类型。记录不是固定长度的，这样可以节省空间，但频繁的更新和删除可能会导致数据碎片，需要定期优化以提高性能。
- 压缩表：压缩表由myisampack工具创建，每个记录都是单独压缩的，因此可以极大地节省磁盘空间。虽然访问压缩表会有一些开销，但对于只读数据或归档数据来说，这是一个很好的选择。
- 性能特点：MyISAM在读取操作上通常表现优异，特别是在数据仓库和内容管理系统等以读取为主的场景中。它的表级锁定机制在大量读取操作中减少了锁定开销，因此在高读取压力下可以提供良好的性能。
- 全文索引：MyISAM支持全文索引，这是一种优化大型文本数据搜索的功能。全文索引可以提高搜索效率，特别是在处理像文章、博客帖子或产品描述这样的文本数据时。
总的来说，MyISAM存储引擎适合那些对事务完整性没有严格要求，且以读取和插入操作为主的应用场景。
查看与修改存储引擎
查看系统支持的搜索引擎
show engines ;
查看表使用的存储引擎
show table status from 库名 where name='表名'\G;
或者
show create table 表名;
修改存储引擎
alter table 表名 engine=你需要的存储引擎;
创建表时指定存储引擎
create table 表名(字段1 数据类型,...) engine=MyISAM;
