MySql数据库【理论篇】

1、引擎

1.1 引擎的区别

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

1.1.1 mysql5.7

InnoDB：默认的事务型存储引擎，支持事务处理、行级锁定和外键约束等特性。

MyISAM：不支持事务和行级锁，适用于主要进行读操作的应用场景。

MEMORY：将表数据存储在内存中，读写操作速度快，但不支持持久化存储。

1.1.2 mysql8.0

InnoDB：仍然是MySQL的默认存储引擎，但在MySQL 8.0中引入了一些新特性和改进。

MyISAM：与MySQL 5.7版本相同，在MySQL 8.0中仍然可用。

MEMORY：仍然保持与MySQL 5.7版本相同的特性。

此外，MySQL 8.0版本还引入了一个新的存储引擎：

RocksDB：RocksDB是一个开源的键值存储引擎，适用于需要高速写入操作的应用场景。它基于Google的LevelDB，并具有良好的写入性能和空间效率。

需要注意的是，MySQL的不同版本可能会在引擎支持方面有所变化。因此，使用特定版本的MySQL时，应该参考该版本的官方文档以确保了解支持的引擎，并根据应用需求进行选择。

1.2 常用引擎

MySQL常用的引擎包括以下几种：

1. InnoDB：InnoDB是MySQL的默认存储引擎，也是最常用的引擎之一。它支持事务处理、行级锁和外键约束等高级特性，具有良好的并发性和数据完整性保证。

2. MyISAM：MyISAM是另一个常用的存储引擎，具有快速的读取性能。它不支持事务处理和行级锁定，适合用于主要进行读取操作的应用场景，如静态内容存储。

3. MEMORY：MEMORY引擎将表数据存储在内存中，因此具有非常快的读取和写入速度。然而，MEMORY引擎不支持持久化存储，重启MySQL时表数据将丢失，适用于临时存储和缓存等场景。

4. Archive：Archive引擎以高压缩比和低存储成本为特点，适合用于存储和查询大量历史归档数据，但不支持索引和事务处理。

5. NDB Cluster：NDB Cluster引擎（也称为MySQL Cluster）是MySQL的分布式数据库引擎，具有高可用性和水平扩展性。它可以将数据分布在多个节点上，提供自动数据分片和故障恢复等功能，适用于对可扩展性和高可用性有较高需求的应用场景。

需要根据应用需求选择合适的引擎。特定的引擎可能具有不同的特性、性能和适用场景，所以根据应用的读写需求、事务支持、数据持久性和并发性要求等因素来选择最适合的引擎。

1.3 InnoDB

InnoDB是事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种隔离级别，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发情形）及外键支持（所有数据库引擎中独一份，仅有它支持外键）该引擎的设计目标便是处理大容量数据的数据库系统，MySQL在运行时InnoDB会在内存中建立缓冲池，用于缓存数据及索引。

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。InnoDB是默认的MySQL引擎。

1.3.1 ACID事务

在MySQL的InnoDB存储引擎中，ACID是一组事务特性的缩写，代表原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这些特性一起确保了数据库中的事务在并发处理和系统故障等情况下的可靠性和一致性。

1. 原子性（Atomicity）：原子性要求事务中的所有操作要么都执行成功，要么都不执行。如果一个操作失败，整个事务将被回滚到开始状态，保证数据的一致性。通过日志记录和回滚机制，InnoDB引擎能够确保事务的原子性。

2. 一致性（Consistency）：一致性要求事务在执行前后，数据库处于一致的状态。这意味着事务的执行不能破坏数据库中定义的完整性约束、触发器和约束。InnoDB引擎通过应用ACID规则来确保事务的一致性。

3. 隔离性（Isolation）：隔离性要求并发执行的事务之间互不干扰，每个事务都认为它是唯一在执行的事务。InnoDB引擎通过行级锁和多版本并发控制（MVCC）来实现不同的隔离级别，如读取未提交（Read Uncommitted）、读取已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

4. 持久性（Durability）：持久性要求事务一旦提交，其结果就要永久保存在数据库中，即使发生系统崩溃或故障。InnoDB引擎将事务日志写入磁盘，并使用已提交的信息进行恢复，以确保事务的持久性。

这些ACID事务特性的组合确保了事务的可靠性和数据的一致性，使得应用程序能够在高并发和故障情况下正确处理和管理数据。在使用InnoDB引擎时，数据库开发人员可以依赖ACID事务来保证数据的完整性和可靠性。

1.3.2 四种隔离级别

MySQL的InnoDB存储引擎支持以下四种隔离级别（Isolation Level）：

1. 读未提交（Read Uncommitted）：在该隔离级别下，事务可以读取其他事务未提交的数据。这可能导致脏读（Dirty Read）问题，即读取到未完成的、可能会被回滚的数据。

2. 读已提交（Read Committed）：在该隔离级别下，事务只能读取其他已提交事务的数据。这样可以避免脏读，但可能导致不可重复读（Non-repeatable Read）问题，即在同一个事务中，读取到的数据可能不一致。

3. 可重复读（Repeatable Read）：在该隔离级别下，事务在执行期间可以多次读取相同的数据，并且保证所读取的数据集合在事务开始时保持不变。这可以避免脏读和不可重复读，但可能会出现幻读（Phantom Read）问题，即在同一个事务中，不同的查询可能返回不同数量的结果。

4. 串行化（Serializable）：在该隔离级别下，事务彼此完全隔离，每个事务必须按顺序执行，类似于串行执行。这可以避免脏读、不可重复读和幻读问题，但会牺牲并发性能，因为事务需要互斥地执行。

默认情况下，InnoDB使用的隔离级别是可重复读（Repeatable Read），这是最常见和广泛使用的隔离级别。可以使用SQL语句设置其他隔离级别，例如：

• 设置读未提交：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

• 设置读已提交：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

• 设置可重复读：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• 设置串行化：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

选择适当的隔离级别取决于应用程序的需求，需要权衡事务的并发性能和数据一致性要求。

• 脏读

脏读（Dirty Read）是指一个事务在读取到另一个事务未提交的数据的情况下。这意味着一个事务读取到了另一个事务中尚未持久化或回滚的临时数据。

在多个事务并发执行的情况下，脏读可能会导致数据不一致性和错误结果。例如，一个事务对数据进行了修改但最终未提交，在此期间另一个事务读取了这个尚未提交的数据，那么读取到的数据将是未经验证的、不一致的脏数据。

脏读是数据库事务隔离性级别低的一种问题，为了避免脏读，可以使用较高级别的隔离性级别，如读已提交（Read Committed）或可重复读（Repeatable Read）级别。这些级别保证事务只能读取到已提交的数据，避免了脏读带来的不一致性。

• 不可重复读

不可重复读（Non-repeatable Read）是指在一个事务内，同一个查询多次读取同一行数据时，得到的结果可能不一致的情况。这是由于其他并发事务对这行数据进行了修改或删除。

具体来说，当一个事务在执行读操作时，如果其他事务对被读取的数据进行了修改或删除，并且这些修改或删除操作已经提交，那么在同一个事务中再次读取相同的数据时，得到的结果可能与之前的读取结果不一致。

不可重复读与脏读（Dirty Read）不同之处在于，脏读是指一个事务读取到了另一个未提交的事务所修改的数据；而不可重复读是指一个事务读取到了另一个已经提交的事务所修改或删除的数据。

不可重复读可能会导致数据的不一致性和事务处理过程中的逻辑错误。为了避免不可重复读，可以使用较高级别的事务隔离性级别，如可重复读（Repeatable Read）或串行化（Serializable）。这些级别通过锁定或多版本并发控制来保证在同一个事务中多次读取的数据的一致性。

• 幻读

幻读（Phantom Read）是指在同一个事务内多次执行相同的查询，但得到的结果集却不一致的情况。这是由于其他并发事务在这期间插入或删除了符合查询条件的数据行。

具体来说，当一个事务在执行一个范围查询时，如果其他事务在这个范围内插入了新的数据行或删除了符合查询条件的数据行，并且这些操作已经提交，那么在同一个事务中多次执行相同的查询时，得到的结果集可能会出现新增或删除了的“幻影”数据行。

1.4 MyISAM

相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表Insert与update时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈，但MyIsam引擎独立与操作系统，可以在windows及linux上使用。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

1.4.1 缺点

MySQL中的MyISAM引擎具有以下一些缺点：

1. 不支持事务：MyISAM引擎不支持事务，这意味着无法执行具有ACID（原子性、一致性、隔离性、持久性）特性的复杂操作。如果在并发环境下使用MyISAM引擎，可能会导致数据不一致性或丢失。

2. 不支持行级别锁定：MyISAM引擎只支持表级别的锁定，这意味着在并发情况下，某个对表的写操作会阻塞其他对该表的读写操作，影响并发性能。

3. 不支持外键约束：MyISAM引擎不支持外键约束，这意味着无法在数据库层面实施引用完整性，需要在应用程序中手动维护数据的一致性。

4. 缺乏崩溃恢复机制：MyISAM引擎在崩溃时没有内置的恢复机制，因此当 MySQL 服务或主机发生故障时，可能会造成数据损坏或丢失。

5. 性能问题：MyISAM在读密集型操作表现良好，但对于频繁的写入和更新操作，性能较差。这主要是因为它的表级别锁定机制和不支持事务导致的。

6. 不支持并发性：MyISAM引擎不支持高度并发的操作，在高并发情况下容易出现锁冲突和性能瓶颈。

 

基于以上的缺点，对于大部分需要事务支持、并发性能要求较高、数据完整性要求严格的应用场景，建议使用InnoDB引擎代替MyISAM引擎。 InnoDB引擎支持事务、行级别锁定、外键约束和崩溃恢复，具有更好的性能和数据完整性。

1.4.2 适用场景

尽管MyISAM引擎在一些方面存在一些缺点，但在某些特定的场景下仍然可以考虑使用它。以下是一些适用MyISAM引擎的常见场景：

1. 静态数据表：如果表的数据很少或者基本上是静态的，很少进行更新操作，而且并发性要求不高，使用MyISAM引擎可以获得较好的性能。例如，系统配置表、只读的参考数据表等。

2. 大型读密集型应用：如果应用程序主要是进行大规模的读取操作，而写入操作非常有限，或者写入操作的一致性要求较低，MyISAM引擎可以提供较好的读取性能。例如，数据仓库或报表生成系统等。

3. 基于全文检索的应用：MyISAM引擎支持全文索引功能，适用于对文本进行搜索和匹配的应用场景。全文索引能够快速定位到包含特定关键字的记录，提供高效的搜索功能。

4. 简单的数据库应用：对于简单的应用，无需事务支持或外键约束，并发性要求相对较低的情况下，MyISAM引擎可以作为一种轻量级的解决方案。例如，个人博客、论坛的简单数据存储等。

需要注意的是，由于MyISAM引擎的一些限制，例如锁定机制和缺乏事务支持，对于需要数据一致性、并发性或复杂事务处理的应用，建议使用更强大的引擎，例如InnoDB。在选择使用引擎之前，应该充分评估应用的需求，并根据需求权衡不同引擎的优缺点。

2、管理

2.1 关键字

MySQL是一种常用的关系型数据库管理系统（RDBMS），它包含了一系列关键字和功能，下面是一些MySQL常见的关键字及其说明：

1. SELECT：用于从数据库表中检索数据记录。

2. INSERT：用于将新的数据记录插入到数据库表中。

3. UPDATE：用于修改数据库表中已存在的数据记录。

4. DELETE：用于从数据库表中删除数据记录。

5. CREATE：用于创建数据库、表、索引等对象。

6. ALTER：用于修改数据库表结构，如添加、修改或删除表的列。

7. DROP：用于删除数据库、表、索引等对象。

8. WHERE：用于在SELECT、UPDATE 或 DELETE 语句中指定条件，用来限制返回的数据行。

9. JOIN：用于合并相关联的两个或多个表中的行，根据其在表中的关联键进行匹配。

10. ORDER BY：用于按照指定的列对结果进行排序，默认为升序排序。

11. GROUP BY：用于将结果分组，通常与聚合函数（如SUM、COUNT、AVG等）一起使用。

12. HAVING：用于对GROUP BY子句进行过滤，通过条件来筛选分组后的结果。

13. DISTINCT：用于返回唯一不重复的结果集，从查询结果中排除重复的记录。

14. LIMIT：用于限制返回结果的数量，并可配合OFFSET指定起始位置。

15. UNION：用于合并两个或多个SELECT语句的结果集，并返回一个包含所有结果的单一结果集。

以上仅涵盖了一部分MySQL的关键字和功能，MySQL还有许多其他的关键字和语法，用于支持更复杂的查询和操作。可以参考MySQL的官方文档或其他相关资源获取更详细的信息。

2.2 账户管理

    MySQL 在安装时，会默认创建一个名为 root 的用户，该用户拥有超级权限，可以控制整个 MySQL 服务器。

       MySQL 提供了以下 创建用3 种方法户。

• 使用 CREATE USER 语句创建用户
• 在 mysql.user 表中添加用户
• 使用 GRANT 语句创建用户

2.2.1 增加用户

增加一个用户，名称为CloudJun。

create user CloudJun;

2.2.2 修改密码

把名称为CloudJun的用户设置/修改密码为123456；

ALTER USER 'CloudJun'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

2.2.3 授权用户

将名称为CloudJun的用户授权mybatis_ssm数据库中的t_mvc_book表的查询权限；

grant SELECT on mybatis_ssm.t_mvc_book to CloudJun@'%';

将名称为CloudJun的用户授权mybatis_ssm数据库中的t_mvc_book表的修改权限；

grant UPDATE on mybatis_ssm.t_mvc_book to CloudJun@'%';

将名称为CloudJun的用户授权mybatis_ssm数据库中的所有表的查询权限；

grant SELECT on mybatis_ssm.* to CloudJun@'%';

将名称为CloudJun的用户授权mybatis_ssm数据库中所有表的所有权限；

grant ALL on mybatis_ssm.* to CloudJun@'%';

2.2.4 查看用户

SELECT user, host FROM mysql.user;

执行上述语句后，将会返回一个结果集，其中包含了当前数据库中的所有用户及其相应的主机信息。

2.2.5 用户撤权

将用户名为CloudJun的，撤销mybatis_ssm数据库中的t_mvc_book表的删除权限。

revoke DELETE on mybatis_ssm.t_mvc_book from CloudJun@'%';

将用户名为CloudJun的，撤销mybatis_ssm数据库中的t_mvc_book表的查询权限。

revoke SELECT on mybatis_ssm.t_mvc_book from CloudJun@'%';

将用户名为CloudJun的，撤销mybatis_ssm数据库中的t_mvc_book表的所有权限。

revoke all on mybatis_ssm.t_mvc_book from CloudJun@'%';

将用户名为CloudJun的，撤销mybatis_ssm数据库中的所有表所有的权限。

revoke all on mybatis_ssm.*from CloudJun@'%';