Mysql数据库高级特性【三】数据引擎和常见问题

目录

数据库引擎

数据库引擎的作用

数据库引擎的类型

数据库引擎的配置

使用场景

mysql数据库常见问题

1.更新丢失（Lost Update）

2.脏读（Dirty Read）

3.不可重复读（Non-repeatable Read）

4.幻读（Phantom Read）

解决方案

---

数据库引擎

数据库引擎是数据库管理系统（DBMS）的核心组件，负责存储、管理和检索数据

数据库引擎的作用

数据存储，数据访问和查询处理，事务处理，并发控制，索引和查询优化，数据安全和权限管理，备份和恢复

数据库引擎的类型

1.InnoDB：支持事务处理和行级锁定，适用于高并发和多表关联查询的应用。

2.MyISAM：不支持事务处理，适用于读密集型应用

3.Memory：将数据存储在内存中，适用于需要快速读写的临时数据和缓存

4.NDB(Cluster)：支持分布式存储和高可用性，适用于大规模的分布式系统

5.Archive：只支持插入和压缩，适用于存储大量历史数据

6.CSV：将数据存储为CSV文件，适用于导入导出数据

7.Blackhole：从写入的数据中丢弃数据，适用于日志传输和复制

数据库引擎的配置

#在MySQL配置文件中（通常是my.cnf或my.ini），可以通过设置以下参数来配置引擎

Default-storage-engine                #指定默认的存储引擎

Innodb_buffer_pool_size                #设置InnoDB的缓冲池大小

Key_buffer_size                #设置MyISAM的索引缓冲区大小

Innodb_file_per_table                #每个InnoDB表创建一个独立的表空间文件

Innodb_flush_log_at_trx_commit                #设置InnoDB事务提交时的日志刷新策略

使用场景

1. 网站博客：使用InnoDB作为默认引擎来存储用户数据、文章内容和评论。通过合理配置缓冲池大小和索引，提高读写性和并发性
2. 电子商务平台：使用InnoDB存储产品信息，订单信息和用户数据。通过设置合适的事务隔离级别和优化查询，提高读写性能和并发性
3. 数据分析平台：使用MyISAM或Memory引擎存储日志和临时数据，以提高查询和分析性能。通过合理的索引和分区策略，加速大规模数据的处理
4. 大规模分布式系统：使用NDB（Cluster）引擎作为存储引擎，以实现高可用性和容错性。通过水平分片和分布式查询，扩展数据库的存储和计算能力

mysql数据库常见问题

#在数据库中，特别是在多用户环境下，常常需要处理由于并发事务引起的各种问题

1.更新丢失（Lost Update）

更新丢失指的是当两个或多个事务读取同一条记录，并基于此独立修改它时，最后一个写操作可能覆盖其他事务所做的修改，导致一些更新效果丢失

案例：若事务1和事务2都读取了账户123的余额（如100），事务1将余额更新为150，事务2不知道事务1的更新，也将余额更新为120，如果事务2在事务1提交后提交，则事务1所做的更新将会丢失。

2.脏读（Dirty Read）

脏读发生在一个事务读取了另一个事务尚未提交的数据，如果这个数据被回滚，那么读到的数据就是无效的

案例：事务1从账户123中扣除了100，事务2读取123的余额，包括了未提交的更改，若事务1回滚更改，则事务2读取的数据是“脏”的，因为它包含了不应该读到的未提交的数据

3.不可重复读（Non-repeatable Read）

指在一个事务内部，多次读取同一数据集合时，由于其他事务的提交，后续读取的结果可能与前次不一致，即数据集合中的一些行被其他事务更新了

案例：事务1读取账户123的余额，事务2更新账户123的余额并提交，事务1再次读取同一个账户的余额，发现数据发生了变化。

4.幻读（Phantom Read）

幻读跟不可重复读类似，但它指的是在事务中执行相同的查询时，新增或删除的记录导致读取到不同的行。它通常发生在范围查询中，当其它事务插入或者删除了符合该范围的行。

案例：事务1读取账户123的余额，事务2更新账户123的余额并提交，事务1再次读取同一个账户的余额，发现数据发生了变化。

解决方案

(1)将相关的更新操作放在一个事务中，确保原子性和一致性

(2)锁定读，确保在读取数据、计算新值并执行更新操作期间，其他事务不会干扰数据

(3)隔离级别调整，为了处理这些并发问题，MySQL提供了不同的事务隔离级别，默认的可重复读（REPEATABLE READ）隔离级别可以防止“不可重复读”和“幻读”，但在某些情况下可能导致更新丢失。可以考虑使用更严格的隔离级别

READ UNCOMMITTED（读未提交）: 允许脏读，也就是可以读取尚未提交的事务数据。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ COMMITTED（读已提交）: 保证一个事务不可以读取其他未提交事务的数据，从而防止脏读。但是不保证不可重复读。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ（可重复读）: 防止脏读和不可重复读现象的发生，但可能出现幻读。这是MySQL的默认事务隔离级别。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SERIALIZABLE（可串行化）: 最严格的隔离级别，事务依次顺序执行，可以看作没有并发。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

(4)两阶段提交（2PC）

在分布式系统中，银行系统可能使用两阶段提交协议来协调多个节点之间的事务。

在第一阶段，所有参与的节点准备提交事务，在第二阶段，如果所有节点都准备好，那么事务就被正式提交；如果有任何一个节点无法准备，那么事务就会被回滚。