Mysql知识体系总结梳理

最新推荐文章于 2024-11-01 14:31:36 发布

Nightselfhurt

最新推荐文章于 2024-11-01 14:31:36 发布

阅读量932

点赞数 19

本文链接：https://blog.csdn.net/guitumanman/article/details/142644565

版权

mysql事务的四大特性

原子性（Atomicity）

原子性确保事务中的所有操作要么全部完成，要么全部不完成。事务是一个不可分割的最小工作单元。

例子：假设有一个银行转账操作，事务包括从账户A中扣钱和向账户B中加钱。如果任何一个操作失败，整个事务都会回滚，账户A和账户B的余额不会发生变化。

一致性（Consistency）

一致性确保事务在完成后，数据库从一个一致状态转换到另一个一致状态。事务开始前和结束后，数据库的完整性约束没有被破坏。

例子：在银行转账操作中，一致性确保转账后总金额保持不变。如果账户A减少了100元，账户B就应该增加100元。

隔离性（Isolation）

隔离性确保并发执行的事务彼此隔离开来，避免相互干扰。不同的事务之间的操作是互不可见的，直到事务提交。

隔离级别：

读未提交（Read Uncommitted）：一个事务可以读到另一个未提交事务的数据，可能导致脏读。
读已提交（Read Committed）：一个事务只能读到已提交事务的数据，避免脏读。
可重复读（Repeatable Read）：一个事务在整个过程中看到的数据是一致的，避免不可重复读。
串行化（Serializable）：最高隔离级别，事务完全串行化执行，避免幻读。

关于隔离级别的详细只是内容可参照下一小节

持久性（Durability）

持久性确保事务一旦提交，其结果是永久性的，即使系统发生故障，数据也不会丢失。

例子：在银行转账操作中，一旦事务提交，转账操作的结果（账户A和账户B的余额变化）将永久保存在数据库中，即使之后系统崩溃，数据也不会丢失。

Mysql的事务隔离级别

读未提交（Read Uncommitted）
在这种隔离级别下，一个事务可以读取另一个未提交事务的数据。这种级别提供最少的数据保护，可能导致脏读（Dirty Read）。
脏读：一个事务读取了另一个事务未提交的数据。如果该事务回滚，那么读到的数据将是无效的。

SQL

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

读已提交（Read Committed）
在这种隔离级别下，一个事务只能读取已经提交的事务的数据。这样可以避免脏读，但可能会出现不可重复读（Non-repeatable Read）。
不可重复读：一个事务在读取同一行数据时，可能因为另一个事务的提交而得到不同的结果。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

可重复读（Repeatable Read）
在这种隔离级别下，一个事务在整个过程中多次读取同一行数据时，结果是相同的。这种级别可以避免脏读和不可重复读，但可能会出现幻读（Phantom Read）。
幻读：一个事务在读取某个范围内的行时，另一个事务在该范围内插入了新的行，导致前一个事务再次读取时发现了“幻影”行。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

串行化（Serializable）
这是最高的隔离级别，在这种级别下，事务完全串行化执行，避免了脏读、不可重复读和幻读。代价是并发性大大降低，事务可能会因为锁等待而阻塞。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

设置 MySQL 事务隔离级别的示例：

Plain Text复制代码

-- 设置全局事务隔离级别为可重复读SETGLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置当前会话的事务隔离级别为读已提交SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 开始事务START TRANSACTION;

-- 在事务内进行操作SELECT*FROM accounts WHERE account_id ='A';

-- 提交事务COMMIT;

Mysql脏读、幻读、不可重复读

脏读

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。例如：张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。与此同时，事务B正在读取张三的工资，读取到张三的工资为8000。随后，事务A发生异常，而回滚了事务。张三的工资又回滚为5000。最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

不可重复读

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如：在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。与此同时，事务B把张三的工资改为8000，并提交了事务。随后，在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

幻读

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如：目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。此时，事务B插入一条工资也为5000的记录。这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

不可重复读和幻读有什么区别

不可重复读的重点是修改：同样的条件，你读取过的数据，再次读取出来发现值不一样了幻读的重点在于新增或者删除：同样的条件，第 1 次和第 2 次读出来的记录数不一样。

存储引擎应该如何选择

选择 MySQL 的存储引擎时，应根据具体应用场景、性能需求、数据完整性要求等因素进行综合考虑。

事务支持：InnoDB 支持事务和 ACID 特性，适合需要事务支持的应用；MyISAM 不支持事务，适合对数据完整性要求不高的应用。
锁机制：InnoDB 使用行级锁，适合高并发和频繁写操作；MyISAM 使用表级锁，适合读多写少的场景。
外键支持：InnoDB 支持外键约束，保证数据的一致性和完整性；MyISAM 不支持外键。
全文索引：MyISAM 原生支持全文索引，适合需要全文搜索的应用；InnoDB 从 MySQL 5.6 开始支持全文索引，但性能可能不如 MyISAM。
崩溃恢复：InnoDB 支持自动崩溃恢复，通过重做日志和回滚日志实现快速恢复；MyISAM 只支持基于表的崩溃恢复，恢复过程较慢且不完全。
存储空间：InnoDB 可能占用更多存储空间，但支持更大的表（最大表大小可达 64TB）；MyISAM 存储空间效率较高，数据文件较小，但受文件系统限制（单个表最大可达 256TB）。
读写性能：InnoDB 在高并发和事务密集型场景下性能优越，写操作和并发处理能力强；MyISAM 在读多写少场景下性能优越，查询速度快，占用资源少。
数据备份和恢复：InnoDB 支持热备份和在线备份，适合需要不间断服务的应用；MyISAM 备份和恢复相对简单，但需要停机操作，适合对服务连续性要求不高的应用。
其他存储引擎：Memory 适合需要极高读写性能且数据不持久存储的场景；Archive 适合存储大量历史数据，支持高效的插入操作；NDB 适合需要高可用性和分布式存储的场景

mysql的innodb和myisam索引的区别

聚簇索引：InnoDB 使用聚簇索引，主键索引和行数据存储在一起，查询主键时效率高；MyISAM 使用非聚簇索引，索引和数据分开存储，主键索引只包含指向数据文件的指针。
辅助索引：InnoDB 的辅助索引存储索引列和主键值，通过主键找到实际数据；MyISAM 的辅助索引存储索引列和数据文件指针，直接通过指针找到实际数据。
全文索引：MyISAM 原生支持全文索引，适合需要全文搜索的应用；InnoDB 从 MySQL 5.6 开始支持全文索引，但在某些情况下性能可能不如 MyISAM。
索引大小：InnoDB 的索引由于包含行数据和事务日志，占用更多存储空间；MyISAM 的索引文件较小，存储空间利用率较高。
索引锁定机制：InnoDB 使用行级锁，允许更高的并发性和更细粒度的锁定；MyISAM 使用表级锁，索引操作时可能会锁定整个表，影响并发性能。
数据一致性：InnoDB 支持外键和事务，能够自动保证数据的一致性和完整性；MyISAM 不支持外键和事务，数据一致性需要通过应用层来处理。