MySQL的讲述及InnoDB&MyISAM引起的使用及管理_数据库中innodb和myisam,以及事务管理-优快云博客

MySQL是由瑞典的Michael Widenius、David Axmark和Allan Larsson创建的。他们在1994年共同开发了MySQL，并最初以开源项目的形式发布。Michael Widenius和David Axmark主要负责技术开发和设计，而Allan Larsson负责商业方面的运营。MySQL的名字是由Michael Widenius的女儿名字"My"和"SQL"（结构化查询语言）的组合而来。MySQL最早是基于C语言开发的，后来转换为使用C++编写以提供更好的性能和可扩展性。他们的目标是创建一个轻量级、高性能且易于使用的数据库管理系统，以满足不同规模和行业的应用需求。MySQL的开源性质使得它快速获得了广泛的用户和社区的支持，并迅速成为一种流行的数据库解决方案。由于MySQL的成功，Michael Widenius、David Axmark和Allan Larsson等人也受到了广泛的认可和赞誉。

最初的版本中，MySQL仅支持基本的数据库功能，但随着时间的推移，它不断发展和改进，引入了许多新功能和高级特性。MySQL的成功部分归功于其开放源代码的性质，使得用户能够自由获取、使用和修改MySQL的源代码。

2008年，MySQL被Sun Microsystems（后来被Oracle收购）收购。尽管有一些关于MySQL在Oracle的管理下将会面临闭源的担忧，但Oracle承诺继续支持和发展MySQL的开源版本，并推出了MySQL Community Edition和MySQL Enterprise Edition两个方案。

MySQL已经成为世界上最流行的关系型数据库之一，在许多不同规模和行业的应用程序中广泛使用。它的特点包括高性能、可靠性、扩展性和灵活性，适用于各种应用场景，从小型网站到大型企业级系统都可以使用MySQL来处理和管理数据。

1.2 是什么

MySQL是一种开源的关系型数据库管理系统(RDBMS)，它是由瑞典的Michael Widenius、David Axmark和Allan Larsson在1994年共同创建的。MySQL以其高性能、可靠性、可扩展性和易用性而闻名，并被广泛用于各种规模的应用程序。

Allan Larsson的照片

以下是MySQL的主要特点的详细介绍：

1. 开源性：MySQL以开源软件的形式发布，可以免费获取、使用和修改。这使得用户能够自由地访问和定制MySQL的代码，满足特定的需求，并享受开源社区带来的各种贡献和技术支持。

2. 可靠性和稳定性：作为一个成熟的数据库管理系统，MySQL经过广泛的测试和验证，具有高度可靠和稳定的性能。它能够处理高并发的访问，并提供数据的持久性和一致性。

3. 高性能：MySQL通过优化的存储引擎和查询处理机制，提供快速的读写速度和高效的查询性能。它可以处理大量的数据和复杂的查询，并支持索引、缓存和查询优化等技术，以提供卓越的性能。

4. 可伸缩性：MySQL可以轻松地在不同规模的应用程序中进行扩展。它支持主从复制、分布式架构和集群技术，可以实现负载均衡和水平扩展，以提高性能和可用性。

5. 多语言支持：MySQL支持多种编程语言的API和驱动程序，如Python、Java、PHP等。开发人员可以使用自己熟悉的编程语言和工具与MySQL进行交互和操作。

6. 安全性：MySQL提供了多种安全功能来保护数据的安全和完整性。它支持用户认证和访问控制，可以对数据进行加密和签名，并提供审计和日志记录功能。此外，MySQL还支持SSL/TLS加密传输，以确保数据在传输过程中的安全性。

7. 简单易用：MySQL的设计目标之一是易于使用。它提供了直观的命令行界面和图形化工具，使用户能够轻松地管理和操作数据库。此外，MySQL还提供了丰富的文档和社区支持，帮助用户解决问题并获取相关信息。

总结起来，MySQL是一种功能强大、可靠性高且易于使用的关系型数据库管理系统。它的开源性质和丰富的功能使得它成为全球最受欢迎的数据库解决方案之一，被广泛运用于各种类型和规模的应用程序。

1.3 作用

MySQL作为一种关系型数据库管理系统(RDBMS)，具有广泛的应用场景和作用。以下是MySQL的详细作用：

1. 数据存储和管理：MySQL可以用来存储和管理大量的数据，包括结构化数据、文本数据、图片和多媒体等。它提供了灵活的数据模型和表结构，使用户能够组织和管理数据。

2. 数据查询和检索：MySQL支持结构化查询语言（SQL），可以通过编写SQL查询语句来检索、过滤和排序数据。用户可以使用简单的SELECT语句进行基本查询，也可以使用复杂的JOIN、GROUP BY和HAVING等语句进行高级查询和数据分析。

3. 数据修改和更新：除了查询，MySQL还支持数据的插入、修改和删除操作。用户可以使用INSERT语句将数据插入到表中，使用UPDATE语句修改表中的数据，使用DELETE语句删除表中的数据。

4. 数据安全和权限控制：MySQL提供了安全性控制机制，包括用户身份验证、访问控制和权限管理等。用户可以创建和管理不同的用户账号，并为每个账号分配特定的权限，以确保数据的安全性和保密性。

5. 数据备份和恢复：MySQL支持数据备份和恢复，用户可以使用工具或命令来创建数据库备份，并在需要时恢复数据。这种数据备份机制可以帮助用户保护数据免受意外删除、硬件故障或系统崩溃等问题的影响。

6. 并发控制和事务管理：MySQL具有并发控制和事务管理的能力，可以处理多个用户同时访问数据库的情况，并确保数据的一致性和完整性。MySQL支持ACID（原子性、一致性、隔离性和持久性）特性，可以保证事务的原子性和持久性。

7. 应用程序的数据后台支持：MySQL可以作为应用程序的数据库后台，为应用程序提供数据存储和管理的功能。它可以与各种编程语言和框架集成，并通过API和驱动程序来实现与应用程序的数据交互。

总结起来，MySQL作为一个功能强大的关系型数据库管理系统，主要用于数据的存储、查询、修改和管理。它具有广泛的作用，可以应用于各种规模和类型的应用程序，提供数据管理和支持，确保数据的安全性、一致性和可靠性。

二、引擎

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;

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

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	可能	可能	可能
Read Committed	不可能	可能	可能
Repeatable Read	不可能	不可能	可能
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。在选择使用引擎之前，应该充分评估应用的需求，并根据需求权衡不同引擎的优缺点。

三、管理

3.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的官方文档或其他相关资源获取更详细的信息。

3.2 管理

3.2.1 增加用户

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

create user CloudJun;

3.2.2 修改密码

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

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

3.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@'%';

3.2.4 查看用户

SELECT user, host FROM mysql.user;

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

3.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@'%';

给我们的收获

学习MySQL可以给我们带来以下收获：

1. 数据库技能：掌握MySQL让我们具备了数据库管理和操作的核心技能。这对于从事软件开发、数据分析、数据工程等与数据库相关的职业非常重要。掌握MySQL可以提升个人的就业竞争力，拓宽职业发展的可能性。

2. 数据管理能力：学习MySQL可以让我们有效地管理和组织大量数据。了解数据库的原理和概念，能够合理地设计数据库模式、规划数据表结构，从而提升数据的存储和检索效率。这对于处理大数据、构建复杂的应用系统等都非常关键。

3. 数据分析能力：MySQL提供了强大的查询语言（SQL），学习MySQL可以使我们掌握数据查询、过滤、排序、聚合的技能。这对于进行数据分析、生成报表、提取信息等非常有帮助。MySQL支持复杂的数据操作，可以应对各种数据分析需求。

4. 数据安全和隐私保护：学习MySQL可以让我们掌握数据库的安全管理。了解如何创建用户、设置权限、进行访问控制，可以保护数据库的数据免受恶意攻击和非授权访问。这对于企业中的数据安全和隐私保护非常重要。

5. 解决问题的能力：学习MySQL要求我们学会提出问题、分析问题、解决问题的能力。在使用MySQL过程中，我们会遇到各种各样的问题，例如性能问题、数据错误、权限管理等，通过学习MySQL，我们可以学会分析和解决这些问题，培养自己的问题解决能力。

6. 数据库社区和资源：MySQL是一个广泛使用和支持的数据库系统，有庞大的社区和丰富的资源。学习MySQL可以让我们接触到这个活跃的社区，获取到各种优质的学习资料、文档、教程、论坛等，能够与其他开发者交流和分享经验。