后端开发必备全网最全MySQL面试题(25秋招走起)

Java后端程序员知识库

于 2024-07-29 16:30:32 发布

阅读量333

点赞数 5

文章标签： java

本文链接：https://blog.csdn.net/Wangzc_1116/article/details/140774461

版权

以下Java看看弟弟网站整理

MySQL中InnoDB和MyISAM存储引擎的区别是什么？

InnoDB和MyISAM是MySQL中两种常见的存储引擎，它们的主要区别包括：

1、事务支持： InnoDB支持事务，而MyISAM不支持。

2、行级锁和表级锁： InnoDB提供行级锁，MyISAM提供表级锁。

3、外键支持： InnoDB支持外键约束，MyISAM不支持。

4、全文索引： MyISAM支持全文索引，而InnoDB在较早版本中不支持。

5、数据恢复： InnoDB提供事务日志，支持崩溃恢复。

6、存储限制： MyISAM支持较大的表，而InnoDB的表大小受到文件系统的限制。

MySQL中的索引类型及其使用场景。

MySQL中常见的索引类型包括：

1、B-Tree索引： 适用于全值匹配、匹配列前缀、匹配范围值、精确匹配和ORDER BY操作。

2、哈希索引： 适用于等值查询，但不支持范围查询。

3、全文索引： 用于全文搜索，主要在MyISAM引擎中。

4、空间索引： 用于地理数据存储。

5、复合索引： 包含多个列，适用于多列的查询条件。

根据不同的查询需求选择合适的索引类型可以显著提高查询效率。

MySQL中如何实现高效的分页查询？

在MySQL中实现高效分页查询的方法包括：

1、避免OFFSET的大数值： 使用LIMIT时，较大的OFFSET值会导致性能问题。

2、使用覆盖索引： 确保查询只需要访问索引，不需要回表。

3、查询缓存： 利用查询缓存来存储分页结果，适用于数据变化不频繁的场景。

4、记住上次查询的最后位置： 而不是使用OFFSET。

MySQL中的锁机制有哪些类型？

MySQL中的锁机制包括：

1、表级锁： 锁定整个表，实现简单，开销小，但并发能力弱。

2、行级锁： 锁定单独的行，开销大，但并发能力强。

3、页面锁： 锁定相邻的一组行，是表级锁和行级锁的折中。

4、意向锁： InnoDB特有，用于指示行级锁的存在。

5、共享锁和排他锁： 共享锁允许其他事务读取，排他锁不允许其他事务访问。

MySQL中的事务隔离级别及其特点。

MySQL支持四种事务隔离级别，每种级别都有其特点：

1、READ UNCOMMITTED（未提交读）： 最低级别，允许读取未提交的数据变更，可能会导致脏读、不可重复读和幻读。

2、READ COMMITTED（提交读）： 允许读取并发事务已提交的数据，可以避免脏读，但不可重复读和幻读仍然可能发生。

3、REPEATABLE READ（可重复读）： MySQL默认级别。保证在同一个事务中多次读取同样数据的结果一致，但幻读可能发生。

4、SERIALIZABLE（可串行化）： 最高隔离级别，强制事务串行执行，避免脏读、不可重复读和幻读，但并发性能最差。

根据应用的具体需求选择合适的隔离级别，以平衡一致性和性能。

MySQL如何处理死锁？

MySQL处理死锁的方法包括：

1、死锁检测： InnoDB引擎具有死锁检测机制，能够检测到死锁并回滚一部分事务来解除死锁。

2、超时机制： 设置锁等待超时时间（innodb_lock_wait_timeout），超过时间未获得锁则事务回滚。

3、避免策略： 如避免长事务，合理设计索引和查询逻辑，减少锁竞争。

4、手动干预： 分析锁等待和死锁日志，手动终止相关事务。

MySQL中的ACID是什么？

ACID是事务的四个基本特性：

1、原子性（Atomicity）： 事务作为一个整体被执行，要么全部完成，要么全部不执行。

2、一致性（Consistency）： 事务必须使数据库从一个一致性状态转换到另一个一致性状态。

3、隔离性（Isolation）： 事务的执行不被其他事务干扰。

4、持久性（Durability）： 一旦事务提交，其结果就是永久性的。

这些特性确保了数据库事务的可靠性和稳定性。

MySQL的索引覆盖是什么及其优点。

索引覆盖是指一个查询语句的所有字段都是从同一个索引中直接获取，而不需要回表查询原始数据。其优点包括：

1、减少IO操作： 由于数据可以直接从索引中获取，减少了对数据表的访问。

2、提高查询效率： 索引通常比数据行小，读取索引更快。

3、减少磁盘空间的使用： 因为避免了数据文件的访问。

4、优化查询计划： 使得数据库优化器可以选择更有效的执行计划。

使用索引覆盖是提高数据库查询性能的有效手段之一。

MySQL中的GROUP BY与ORDER BY有什么区别？

GROUP BY和ORDER BY在MySQL中用于不同的目的：

1、GROUP BY： 用于根据一个或多个列对结果集进行分组，通常与聚合函数（如SUM、COUNT等）一起使用，用于生成分组后的汇总数据。

2、ORDER BY： 用于对查询结果按照一个或多个列进行排序。可以指定升序（ASC）或降序（DESC）。

简而言之，GROUP BY用于数据分组，ORDER BY用于数据排序。

MySQL中的慢查询日志及其如何使用。

MySQL的慢查询日志是用来记录执行时间超过设定阈值的SQL语句。使用方法包括：

1、开启慢查询日志： 通过设置slow_query_log参数为1来开启。

2、设置阈值： 通过long_query_time设置执行时间阈值，超过这个值的查询将被记录。

3、日志分析： 使用工具如pt-query-digest分析慢查询日志，找出性能瓶颈。

慢查询日志是优化数据库性能的重要工具。

MySQL中的JOIN类型有哪些，它们是如何工作的？

MySQL支持多种JOIN类型，包括：

1、INNER JOIN（内连接）： 返回两个表中满足连接条件的行。

2、LEFT JOIN（左连接）： 返回左表的所有行，即使右表中没有匹配。

3、RIGHT JOIN（右连接）： 返回右表的所有行，即使左表中没有匹配。

4、CROSS JOIN（交叉连接）： 返回两个表的笛卡尔积。

5、SELF JOIN（自连接）： 表自己与自己的连接。

每种JOIN类型用于不同的查询场景，选择合适的JOIN可以提高查询效率。

MySQL中的二级索引是什么？

二级索引（Secondary Index）是指除了主键索引之外的索引。它的特点包括：

1、独立于主键： 二级索引基于表中非主键列创建。

2、含有主键列： 二级索引的每个索引项都包含对应行的主键值。

3、查找过程： 使用二级索引时，通常先找到主键，然后再通过主键索引找到数据。

二级索引对于优化非主键字段的查询非常重要。

MySQL中的正则表达式(RE)匹配是如何工作的？

MySQL支持使用正则表达式进行模式匹配。其工作方式如下：

1、使用REGEXP操作符： 在WHERE子句中使用REGEXP进行模式匹配。

2、模式匹配： 根据提供的正则表达式，匹配字段中的内容。

3、灵活性： 正则表达式提供了比LIKE更强大和灵活的字符串匹配能力。

正则表达式匹配在处理复杂的文本搜索时非常有用。

MySQL中的InnoDB和MyISAM存储引擎有什么区别？

InnoDB和MyISAM是MySQL中最常用的两种存储引擎，它们的主要区别包括：

1、事务支持： InnoDB支持事务处理，包括ACID特性，而MyISAM不支持。

2、行级锁定与表级锁定： InnoDB支持行级锁定，提高并发访问性能；MyISAM只支持表级锁定。

3、外键约束： InnoDB支持外键约束，MyISAM不支持。

4、崩溃恢复： InnoDB提供了崩溃后的自动恢复机制，而MyISAM则较为脆弱。

5、存储限制： MyISAM支持的表大小通常更大，适用于大型的数据仓库。

6、全文索引： MyISAM支持全文索引，InnoDB在较新版本的MySQL中也开始支持。

MySQL中的索引类型及其用途。

MySQL中的主要索引类型及其用途包括：

1、主键索引（PRIMARY KEY）： 用于唯一标识表中的每一行，并优化数据的访问速度。

2、唯一索引（UNIQUE）： 确保数据列中的所有值都是唯一的。

3、普通索引（INDEX）： 最基本的索引类型，用于加速对数据的查询。

4、全文索引（FULLTEXT）： 用于全文搜索，特别是在大量文本数据中搜索特定词汇。

5、组合索引（COMPOSITE INDEX）： 包含多个列，用于优化多列的查询条件。

6、空间索引（SPATIAL）： 用于地理空间数据的索引。

MySQL如何处理死锁？

MySQL处理死锁的方式通常包括：

1、死锁检测： MySQL会自动检测事务间的死锁，并尝试解决。

2、死锁解决： 当检测到死锁时，MySQL会选择并回滚一个事务来解决死锁，通常是回滚修改最少的事务。

3、避免策略： 使用合理的事务设计和索引策略来减少死锁的可能性。

MySQL中的二分查找树（B-Tree）索引和哈希（HASH）索引的区别。

B-Tree索引和哈希索引在MySQL中有以下区别：

1、结构差异： B-Tree索引是一种平衡树结构，适合范围查找；哈希索引基于哈希表，适合精确查找。

2、查找效率： 在等值查询中，哈希索引通常比B-Tree更快；但B-Tree更适合范围查询。

3、索引维护： B-Tree索引在插入和删除操作时需要保持平衡，而哈希索引不需要。

MySQL中的慢查询日志是什么，如何使用它？

MySQL中的慢查询日志是一种记录执行时间超过指定阈值的SQL语句的日志。使用慢查询日志的步骤包括：

1、启用慢查询日志： 通过配置文件或命令行参数启用。

2、设置阈值： 设定一个查询的最长执行时间。

3、分析日志： 通过日志文件分析慢查询，以优化这些查询或相关的表结构。

MySQL优化器是什么，它如何工作？

MySQL优化器是负责查询优化的组件。它的工作流程包括：

1、查询分析： 解析查询语句，生成查询树。

2、索引选择： 选择最适合的索引来检索数据。

3、查询计划生成： 基于可用索引和统计信息生成多个查询计划。

4、成本评估： 评估各查询计划的成本和效率，选择最佳的执行计划。

MySQL中的MVCC（多版本并发控制）机制是什么？

MVCC是MySQL中用于实现高并发的机制。它的基本原理包括：

1、数据版本： 对每个数据行存储多个版本。

2、并发控制： 允许多个事务同时读取同一数据的不同版本。

3、写操作处理： 更新数据时，不直接覆盖原数据，而是创建一个新的版本。

4、隔离级别： 支持不同的事务隔离级别，以平衡性能和一致性的需求。

MySQL的ACID特性是什么？

ACID特性是数据库事务处理的四个基本元素，它包括：

1、原子性（Atomicity）： 事务中的所有操作要么全部完成，要么全部不执行。

2、一致性（Consistency）： 事务执行的结果必须使数据库从一个一致性状态转换到另一个一致性状态。

3、隔离性（Isolation）： 一个事务的执行不应该被其他事务干扰。

4、持久性（Durability）： 一旦事务提交，其结果就是永久性的，即使系统故障也不会丢失。

MySQL中如何实现高可用架构？

在MySQL中实现高可用架构的常见方式包括：

1、主从复制： 一个主数据库服务器和一个或多个从数据库服务器。

2、集群部署： 如MySQL Cluster或Galera Cluster，提供故障转移和负载均衡。

3、读写分离： 通过分离读写操作来提高性能和可用性。

4、自动故障转移： 使用代理或中间件自动处理主服务器故障。

MySQL中什么是二阶段提交（2PC）？

二阶段提交（2PC）是一种确保分布式系统数据一致性的协议。在MySQL中，2PC的流程包括：

1、准备阶段： 事务协调器向所有参与节点发送准备消息，参与节点执行事务操作并锁定资源，然后返回准备就绪状态。

2、提交/中止阶段： 如果所有参与节点准备就绪，协调器发送提交请求；如果任一节点未就绪，则发送中止请求。

MySQL中的视图是什么？它有什么用途？

MySQL中的视图是一个虚拟表，其内容由查询定义。视图的主要用途包括：

1、简化复杂的SQL操作： 将复杂的查询封装在视图中。

2、安全性： 通过视图限制对特定数据的访问。

3、逻辑数据独立性： 改变视图不影响底层数据和其他视图。

MySQL中，如何优化大表的查询性能？

优化MySQL中大表的查询性能的方法包括：

1、使用合适的索引： 确保查询中使用到的列已经被索引。

2、分区： 将大表分割成多个物理较小的表。

3、查询优化： 优化SQL查询语句，避免使用全表扫描。

4、硬件优化： 增加内存和处理器速度，提高I/O性能。

MySQL中的存储过程和函数有什么区别？

存储过程和函数在MySQL中的主要区别在于：

1、返回值： 函数必须返回一个值，而存储过程不必。

2、调用方式： 函数可以作为查询的一部分被调用，存储过程需要独立调用。

3、使用场景： 函数适用于计算和处理数据，存储过程适用于执行复杂的业务逻辑。

4、权限要求： 存储过程的权限管理更为复杂。

MySQL中InnoDB和MyISAM存储引擎的区别是什么？

InnoDB和MyISAM是MySQL中最常用的两种存储引擎，它们的主要区别包括：

1、事务支持： InnoDB支持事务处理，包括提交和回滚，而MyISAM不支持事务。

2、表锁定和行锁定： InnoDB支持行级锁定和外键约束，适合高并发的操作，而MyISAM仅支持表级锁定。

3、数据完整性： InnoDB提供了对数据完整性的支持，包括外键约束，而MyISAM则不支持。

4、存储限制： MyISAM支持的文件大小通常比InnoDB要大。

5、内存使用： InnoDB通常需要更多的内存和存储空间来维护其事务日志和缓冲池。

6、恢复能力： InnoDB能够更好地处理崩溃后的恢复。

MySQL优化查询性能的常用方法有哪些？

优化MySQL查询性能的方法包括：

1、使用合适的索引： 为查询中常用的列和查询条件创建索引。

2、优化查询语句： 确保SQL语句尽可能高效，避免使用子查询和复杂的连接。

3、合理设计表结构： 避免过多的列，合理分割大表。

4、使用缓存： 利用MySQL的查询缓存机制。

5、配置优化： 调整MySQL服务器配置，优化内存使用和查询执行。

6、定期维护数据库： 定期进行数据库表的优化和重建索引。

MySQL中的事务及其ACID属性

事务是一系列操作，要么全部执行，要么全部不执行。MySQL中的事务遵循ACID属性：

1、原子性（Atomicity）： 事务中的所有操作要么全部完成，要么全部不执行。

2、一致性（Consistency）： 事务必须使数据库从一个一致性状态转移到另一个一致性状态。

3、隔离性（Isolation）： 一个事务的执行不应该被其他事务干扰。

4、持久性（Durability）： 事务一旦提交，其结果就是永久性的。

MySQL如何处理死锁？

MySQL处理死锁的方法通常包括：

1、死锁检测与解除： MySQL会自动检测到死锁并解除其中一个事务，以解放资源。

2、超时机制： 可以通过设置锁等待超时时间来防止死锁。

3、事务设计优化： 保持事务简短并按相同的顺序访问资源可以减少死锁的发生。

4、使用索引： 适当使用索引可以减少行级锁定，从而减少死锁的机会。

MySQL中，索引是如何工作的？不同类型的索引有哪些？

MySQL中索引的工作机制及其类型包括：

1、工作机制： 索引是用于快速检索数据库中的行的数据结构。MySQL中最常用的是B-Tree索引，它允许快速访问、插入、删除和更新操作。

2、不同类型的索引：

B-Tree索引： 适用于全键值、键值范围或键值前缀查找。
哈希索引： 适用于等值比较，如=, IN(), <>。
FULLTEXT（全文）索引： 用于全文搜索。
空间索引： 用于空间数据类型，如地理数据的存储。

MySQL中的事务以及ACID属性。

MySQL中的事务是一组操作，要么全部执行，要么全部不执行。它的ACID属性包括：

1、原子性（Atomicity）： 事务内的操作要么全部完成，要么全部不做。

2、一致性（Consistency）： 事务必须使数据库从一个一致性状态转换到另一个一致性状态。

3、隔离性（Isolation）： 一个事务的执行不能被其他事务干扰。

4、持久性（Durability）： 一旦事务提交，其结果就是永久性的。

MySQL如何优化查询性能？

优化MySQL查询性能的方法包括：

1、使用索引： 对常查询的列创建索引，特别是在WHERE子句中的列。

2、优化查询语句： 避免使用SELECT *，只选择需要的列；优化JOIN语句。

3、使用查询缓存： 利用MySQL的查询缓存来加快相同查询的速度。

4、数据库设计： 正规化数据库设计，但在必要时进行反正规化。

5、批量操作： 合并多个查询或使用批量INSERT和UPDATE。

6、配置优化： 调整MySQL配置，如增加缓冲区大小。

MySQL中的锁机制。

MySQL的锁机制是用来管理多个用户同时访问数据库时数据一致性和完整性的一种机制，主要包括：

1、表级锁： 锁定整个表，实现简单，开销小，但并发性差。

2、行级锁： 只锁定影响的行，提供最高的并发性，但管理开销大。

3、页面锁： 锁定数据存储的物理单位“页”，介于表锁和行锁之间。

4、意向锁： 表示事务打算对表中的行进行排他或共享锁定。

5、死锁处理： 当多个事务互相等待对方释放锁时，MySQL会自动检测并解决死锁。

MySQL中的主从复制是如何工作的？

MySQL的主从复制机制包括：

1、日志记录： 主服务器上的所有数据变更都会被记录到二进制日志中。

2、日志传输： 这些日志被传输到一个或多个从服务器。

3、应用日志： 从服务器读取并重放这些日志来更新自己的数据。

4、用途： 主从复制用于数据备份、读写分离、数据分析等。

MySQL中的存储引擎有哪些？它们各有什么特点？

MySQL的常用存储引擎及其特点包括：

1、InnoDB： 支持事务处理，支持外键，支持行级锁定，是MySQL的默认存储引擎。

2、MyISAM： 不支持事务，支持全文搜索，支持表级锁定，拥有较高的读取速度。

3、Memory： 使用内存存储数据，访问速度快，但数据不是持久的。

4、Archive： 用于存储大量的归档数据，支持高效的数据压缩。

5、Federated： 允许访问远程MySQL服务器上的表。

MySQL中InnoDB和MyISAM存储引擎的区别是什么？

InnoDB和MyISAM是MySQL中最常见的两种存储引擎，它们在多个方面有显著的不同：

1、事务支持： InnoDB支持事务，具有提交、回滚和崩溃恢复能力，适合处理高并发的事务。MyISAM不支持事务。

2、锁定机制： InnoDB支持行级锁定，而MyISAM只支持表级锁定。因此，在并发环境下，InnoDB的性能通常优于MyISAM。

3、数据完整性： InnoDB支持外键，可以用来维护数据的完整性。MyISAM不支持外键。

4、存储限制： MyISAM支持的最大数据容量要小于InnoDB，因此对于非常大的数据库，InnoDB更加合适。

5、全文索引： MyISAM原生支持全文索引，而InnoDB在早期版本中不支持，但在MySQL 5.6之后开始支持。

6、缓存机制： InnoDB不仅缓存索引，还缓存数据，而MyISAM只缓存索引。

因此，在选择存储引擎时，需要根据应用的具体需求来决定使用InnoDB还是MyISAM。

MySQL中的ACID属性。

ACID是数据库管理系统进行事务处理的四个基本元素：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1、原子性（Atomicity）： 保证事务中的所有操作要么全部成功，要么全部失败。事务是不可分割的工作单位。

2、一致性（Consistency）： 事务必须使数据库从一个一致性状态转换到另一个一致性状态，保证数据的完整性。

3、隔离性（Isolation）： 保证多个事务同时执行时，一个事务的操作不会影响其他事务的执行。

4、持久性（Durability）： 一旦事务提交，则其所做的修改会永久保存在数据库中，即使系统崩溃也不会丢失。

在MySQL中，InnoDB存储引擎支持ACID属性，确保了事务处理的可靠性和安全性。

MySQL优化查询的常用方法有哪些？

优化MySQL查询的常用方法包括：

1、使用索引： 适当地创建和使用索引，特别是对于经常查询和排序的列。

2、避免全表扫描： 尽量使用索引来进行数据查找，避免全表扫描。

3、优化查询语句： 编写高效的SQL语句，避免复杂的子查询，减少不必要的数据处理。

4、使用Explain命令： 使用Explain命令分析查询的执行计划，找出性能瓶颈。

5、合理设计表结构： 正规化和反正规化表结构，平衡查询效率和数据冗余。

6、调整配置参数： 根据系统资源和实际需求调整MySQL的配置参数，如缓存大小、线程数等。

7、减少数据返回量： 通过限制返回的数据量（如使用LIMIT）来减少网络传输和数据处理的负担。

通过这些方法可以显著提升MySQL数据库的查询性能和整体效率。

MySQL中的存储过程和函数有什么区别？

MySQL中存储过程（Stored Procedure）和函数（Function）都是存储在数据库中的SQL代码集合，但它们有一些关键区别：

1、返回值： 函数必须返回一个值，而存储过程不必。

2、调用方式： 函数可以被嵌入到一个SQL语句中作为一个部分进行调用，而存储过程需独立调用。

3、事务处理： 存储过程可以控制事务（commit和rollback），而函数不可以。

4、输出参数： 存储过程可以有输出参数，函数则不行。

5、用途： 一般来说，函数用于计算和返回值，存储过程用于执行需要逻辑和流程控制的复杂操作。

根据不同的使用场景和需求，开发者可以选择使用存储过程或函数。

MySQL中的主键（Primary Key）和唯一键（Unique Key）的区别。

MySQL中主键（Primary Key）和唯一键（Unique Key）都用于保证数据的唯一性，但它们有以下区别：

1、唯一性： 主键和唯一键都确保列中的值唯一，但主键列不能有NULL值，而唯一键列可以包含NULL值（但只能有一个）。

2、数量限制： 一个表只能有一个主键，但可以有多个唯一键。

3、索引创建： 主键自动创建主索引，唯一键创建唯一索引。主索引和唯一索引在内部实现上有细微差别。

4、角色意义： 主键是表的主要标识，通常用于表的主要查询；唯一键用于保证其他列的数据唯一性。

5、外键约束： 主键可以作为外键被其他表引用，虽然唯一键也可以被引用，但主键是更常见的做法。

根据数据的重要性和应用场景，合理地选择使用主键或唯一键。

MySQL中的JOIN类型有哪些，它们各自的作用是什么？

MySQL中的JOIN类型主要包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN，各自的作用如下：

1、INNER JOIN（内连接）：

返回两个表中满足连接条件的匹配行。
仅当左表和右表都有匹配时，结果才会出现在查询结果中。

2、LEFT JOIN（左连接）：

返回左表所有行，即使在右表中没有匹配的行。
对于右表中没有匹配的行，结果中的右表部分为NULL。

3、RIGHT JOIN（右连接）：

返回右表所有行，即使在左表中没有匹配的行。
对于左表中没有匹配的行，结果中的左表部分为NULL。

4、FULL OUTER JOIN（全外连接）：

返回左表和右表中所有行的组合。
如果左表中的行在右表没有匹配，则右表中的列为NULL，反之亦然。

不同类型的JOIN用于实现不同的数据表连接需求，有助于实现复杂的查询。

MySQL中的索引是什么，它是如何提高查询性能的？

MySQL中的索引是一种特殊的数据结构，存储在数据库表中，帮助快速查询和检索数据。索引提高查询性能的方式如下：

1、高效数据检索： 索引提供了快速查找数据的路径，大大减少了数据库需要扫描的数据量。

2、排序和分组加速： 索引可以加速ORDER BY和GROUP BY操作，因为索引本身是有序的。

3、唯一性验证： 唯一索引可以快速验证数据的唯一性。

索引虽然提高了查询效率，但也增加了插入、删除和更新操作的成本，因为索引本身也需要维护。

MySQL中的事务是什么，如何在MySQL中使用事务？

MySQL中的事务是一系列操作，要么全部执行，要么全部不执行，确保数据库的完整性和一致性。使用事务的步骤包括：

1、开始事务： 使用START TRANSACTION或BEGIN语句开始一个新的事务。

2、执行操作： 执行SQL语句（如INSERT、UPDATE、DELETE）。

3、提交或回滚：

如果操作成功，使用COMMIT语句提交事务，使更改永久化。
如果操作失败或需要撤销，使用ROLLBACK语句回滚事务，撤销所有更改。

4、自动提交： MySQL默认开启自动提交模式，每个SQL语句都作为一个单独的事务执行。可以通过SET autocommit=0;关闭自动提交。

事务控制是关系数据库管理系统的核心功能，对于维护数据的完整性至关重要。

MySQL中的Normalization（规范化）是什么，为什么要进行Normalization？

Normalization（规范化）是数据库设计中的一个过程，旨在组织数据以减少冗余和提高数据完整性。规范化的主要目的包括：

1、减少冗余： 避免数据被多次存储，减少数据存储空间。

2、提高数据完整性： 通过设置数据库约束（如外键）确保数据的一致性和准确性。

3、提升查询性能： 在某些情况下，规范化的表结构可以提高查询效率。

4、简化数据结构： 使得数据库结构更加清晰，易于维护和扩展。

规范化通常包括几个不同的范式（Normal Forms），每个范式有其特定的规则。

MySQL中的存储过程（Stored Procedure）是什么，使用它有哪些优缺点？

MySQL中的存储过程是一组为了完成特定功能的SQL语句集，存储在数据库中，可以被重复调用。使用存储过程的优缺点包括：

优点：

减少网络交互： 存储过程在数据库服务器上执行，减少了客户端和服务器之间的数据传输。
提高性能： 存储过程可以优化和编译，提高执行效率。
重用和封装： 可以封装复杂的业务逻辑，实现代码的重用。
提高安全性： 通过限制对数据库直接访问，提高数据的安全性。

缺点：

维护困难： 存储过程的调试和维护比一般的SQL语句复杂。
数据库依赖性： 存储过程依赖于特定的数据库，降低了应用程序的可移植性。
复杂性： 对于复杂的业务逻辑，存储过程可能变得难以管理和优化。

MySQL索引的底层实现原理是什么？

MySQL索引的底层实现主要基于B+树数据结构。B+树是一种平衡多路查找树，具有以下特点：

1、树的所有叶子节点都位于同一层： 这确保了从根节点到每个叶子节点的路径长度相同，保证了查询效率的一致性。

2、节点中的数据按键值大小有序排列： 这样可以在查找数据时，通过二分查找快速定位数据位置。

3、非叶子节点存储键值和子节点的指针： 而不是实际的数据，这样可以让更多的键值放入一个节点，减少树的高度，提高查询效率。

4、叶子节点之间是相互链接的： 这使得范围查询变得高效，因为可以通过叶子节点的链接顺序访问数据。

通过B+树实现索引，MySQL可以有效地支持大量数据的快速查询、插入、删除和更新操作。

MySQL中InnoDB和MyISAM存储引擎的区别是什么？

InnoDB和MyISAM是MySQL中两种常用的存储引擎，它们在多个方面存在显著差异：

1、事务支持： InnoDB支持事务处理，具备完整的ACID特性。MyISAM不支持事务。

2、行级锁与表级锁： InnoDB提供行级锁，这样可以在高并发条件下提供更好的性能。MyISAM只支持表级锁，可能导致在写操作时锁定整个表。

3、数据恢复： InnoDB支持崩溃后的数据恢复能力，而MyISAM在系统崩溃后恢复数据的能力较弱。

4、数据完整性和外键约束： InnoDB支持外键约束，有助于维护数据库的数据完整性。MyISAM不支持外键。

5、存储结构： InnoDB使用聚簇索引，将数据直接存储在索引的叶子节点中。MyISAM使用非聚簇索引，数据和索引是分开存储的。

这些差异使得InnoDB和MyISAM适用于不同的应用场景。

MySQL中如何优化大表的性能？

优化MySQL中大表的性能可以采取以下策略：

1、合理设计和使用索引： 创建适当的索引可以大幅度提高查询性能。同时，避免过多索引，因为它们会增加写操作的成本。

2、分区表： 通过分区，可以将大表分解成多个物理上独立的小表，每个分区可以单独查询和维护。

3、归档旧数据： 定期移除或归档不再频繁访问的旧数据可以减少表的大小，提高效率。

4、优化查询语句： 确保查询语句高效，避免全表扫描，使用合理的连接和过滤条件。

5、调整配置参数： 调整MySQL配置参数，如缓冲池大小，以适应大表的需求。

6、定期维护表： 使用OPTIMIZE TABLE等命令定期维护表，可以优化表的存储和索引结构。

张洪亮

MySQL尼恩

Mysql面试题来自飞书

MySQL中有哪些存储引擎？

InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务，应该优先考虑InnoDB引擎。

MylSAM存储引擎

在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且崩溃后无法安全恢复。MyISAM对整张表加锁，很容易因为表锁的问题导致典型的的性能问题。

Mrg_MylSAM

Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。

Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用，Archive引擎是一个针对高速插入和压缩做了优化的简单引擎。

Blackhole引擎

Blackhole引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。可以在一些特殊的复制架构和日志审核时发挥作用。但这种引擎在应用方式上有很多问题，因此并不推荐。

CSV引擎

CSV引擎可以将普通的CSV文件(逗号分割值的文件）作为MySQL 的表来处理，但这种表不支持索引。因此CSV引擎可以作为一种数据交换的机制，非常有用。

Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。默认是禁用的。

Memory 引擎

Memory表至少比MyISAM 表要快一个数量级，数据文件是存储在内存中。Memory表的结构在重启以后还会保留，但数据会丢失。

Memroy表在很多场景可以发挥好的作用:

用于查找(lookup）或者映射(mapping）表，例如将邮编和州名映射的表。

用于缓存周期性聚合数据(periodically aggregated data)的结果。

用于保存数据分析中产生的中间数据。

Memory表支持 Hash索引，因此查找操作非常快。Memroy表是表级锁，因此并发写入的性能较低，每行的长度是固定的，可能导致部分内存的浪费。

NDB集群引擎

使用MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing 的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（(MySQL Cluster)。

MyISAM和InnoDB的区别是什么?

MyISAM引擎是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且MyISAM不支持外键，并且索引和数据是分开存储的。

InnoDB是基于聚簇索引建立的，和MyISAM相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

请概述下数据库的范式设计

目前关系数据库有六种范式，常见范式：第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。。

范式化设计优缺点:

优点:可以尽量得减少数据冗余，使得更新快，体积小；缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

反范式化:

优点:可以减少表得关联，可以更好得进行索引优化；缺点:数据冗余以及数据异常，数据得修改需要更多的成本，常见的反范式设计有缓存、冗余等等。

数据库表设计时，字段你会如何选择？

更小的通常更好，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。但是要确保没有低估需要存储的值的范围。

简单就好，简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，比如应该使用MySQL内建的类型而不是字符串来存储日期和时间。

尽量避免NULL，如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

mysql里记录货币用什么字段类型好？

MySQL既支持精确类型的存储DECIMAL类型，也支持不精确类型存储FLOAT和 DOUBLE类型。对于货币记录，应该选择DECIMAL类型，但是DECIMAL类型是以字符串形式存放的，所以性能会有影响。

作为替代方案，可以在数据量比较大的而且要求精度时，虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

谈谈MySQL里的字符串类型

MySQL里的字符串类型有：SET、BLOB、ENUM、VARCHAR、CHAR、TEXT。VARCHAR和 CHAR是两种最主要的字符串类型。VARCHAR类型用于存储可变长字符串，大部分的业务情况下比定长类型更节省空间，CHAR类型是定长的，CHAR适合存储很短的字符串，或者所有值定长或都接近同一个长度。

使用BLOB和TEXT则要慎重，一般把 BLOB或TEXT 列分离到单独的表中，还可以对BLOB或TEXT 列使用合成的(Synthetic)索引，就是根据大文本字段的内容建立一个散列值并单独存储在数据列中，可以通过检索散列值找到数据行。如果表中的字段的取值是固定几个字符串，可以使用枚举列代替常用的字符串类型。

VARCHAR(M)最多能存储多少数据？

对于VARCHAR(M)类型的列最多可以定义65535个字节。其中的M代表该类型最多存储的字符数量，但在实际存储时并不能放这么多。

MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。所以MySQL服务器建议我们把存储类型改为TEXT或者BLOB的类型。这个65535个字节除了列本身的数据之外，还包括一些其他的数据，从行记录格式我们可以得知，为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：真实数据、真实数据占用字节的长度、NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间。

我们假设表中只有一个VARCHAR字段的情况：

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节。

如果VARCHAR类型的列有NOT NULL属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识。

如果VARCHAR(M)类型的列使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字节，那在该字符集下，M的最大取值就是32766（也就是：65532/2），也就是说最多能存储32766个字符；utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就是21844，就是说最多能存储21844（也就是：65532/3）个字符。

不管如何，请牢记：MySQL一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。

什么是虚拟生成列？

虚拟生成列又叫GeneratedColumn，是MySQL 5.7引入的新特性，就是数据库中这一列由其他列计算而得。在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column（虚拟生成的列）和Stored Generated Column（存储生成的列），二者含义如下：

1、Virtual Generated Column（虚拟生成的列）：不存储该列值，即MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，而是当读取该行时，触发触发器对该列进行计算显示。

2、Stored Generated Column（存储生成的列）：存储该列值，即该列值在插入或更新行时进行计算和存储。所以相对于Virtual Column列需要更多的磁盘空间，与Virtual Column相比并没有优势。因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column

在表中允许Virtual Column和Stored Column的混合使用

提高效率：由于mysql在普通索引上加函数会造成索引失效，造成查询性能下降，Generated Column（函数索引）刚好可以解决这个问题，可以在Generated Column加上索引来提高效率。但是不能建立虚拟列和真实列的联合索引，同时虚拟列是不允许创建主键索引和全文索引。

创建虚拟生成列的语法：

CREATE TABLE triangle (

a double DEFAULT NULL,

b double DEFAULT NULL,

sidec double GENERATED ALWAYS AS (SQRT(a * a + b * b))

) ;

alter table triangle add column sided tinyint(1) generated always as (a*b) virtual;

请说下事务的基本特性

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性指的是一个事务中的操作要么全部成功，要么全部失败。

一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。

隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。

持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

事务并发可能引发什么问题？

当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读。

当事务内相同的记录被检索两次，且两次得到的结果不同时，此现象称为不可重复读。

在事务执行过程中，事务2将新记录添加到正在读取的事务1中，导致事务1按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，发生幻读。

事务2中是删除了符合的记录而不是插入新记录，那事务1中之后再根据条件读取的记录变少了，在MySQL中这种现象不属于幻读，相当于对每一条记录都发生了不可重复读的现象。

请描述下MySQL中InnoDB支持的四种事务隔离和区别

read uncommitted：未提交读，可能发生脏读、不可重复读和幻读问题。

read committed：提交读，可能发生不可重复读和幻读问题，但是不会发生脏读问题。

repeatable read：可重复读，在SQL标准中可能发生幻读问题，但是不会发生脏读和不可重复读的问题，但是MySQL通过MVCC基本解决了幻读问题。这也是MySQL的缺省隔离级别。

serializable：串行化读，脏读、不可重复读和幻读问题都不会发生。

MySQL有哪些索引类型

从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）；

从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）；

从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一索引等等。

简单描述MySQL各个索引的区别

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。

主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。

索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。

MySQL的索引对数据库的性能有什么影响

索引（Index）是帮助MySQL高效获取数据的数据结构，所以索引可以极大的提高数据的查询速度。

但是每建立一个索引都要为它建立一棵B+树，一棵很大的B+树由许多数据页组成会占据很多的存储空间。

而且每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引，同时这些操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影响。

为什么MySQL的索引要使用B+树而不是B树？

答案见下一小节

InnoDB一棵B+树可以存放多少行数据？

当然在实际的数据库中，一个节点可以存储的数据可以很多，为什么？

计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是 4k，而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。Innodb 的所有数据文件（后缀为 ibd 的文件），他的大小始终都是 16384（16k）的整数倍。

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。

对于B+树而言，只有叶子节点存放数据，非叶子节点存放的是只保存索引信息和下一层节点的指针信息。一个非叶子节点能存放多少指针？

其实这也很好算，我们假设主键 ID 为常用的bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170个。

那么可以算出一棵高度为2的B+树，存在一个根节点和若干个叶子节点能存放 1170*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为 3 的 **B+ **树可以存放： 1170117016=21902400 **条这样的记录。

所以在 InnoDB 中 B+ 树高度一般为 1-3 层，就能满足千万级的数据存储。

那么为什么MySQL****的索引要使用B+树而不是B树？

而 B 树和B+树的最大区别就是，B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。

HashMap适合做数据库索引吗？

1、hash表只能匹配是否相等，不能实现范围查找；

2、当需要按照索引进行order by时，hash值没办法支持排序；

3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了阿和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引；

4、当数据量很大时，hash冲突的概率也会非常大。

InnoDB中只有B+树索引吗？

InnoDB存储引擎不仅仅有B+树索引，它还支持全文索引、哈希索引。

InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)。使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。我们对这个自适应哈希索引能够干预的地方很少，只能设定是否启用和分区个数。

从MySQL5.6.x开始，InnoDB开始支持全文检索，内部的实现机制就是倒排索引。但是MySQL整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，所以如果有大批量或者专门的全文检索需求，还是应该选择专门的全文检索引擎。

什么是密集索引和稀疏索引？

密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。

稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键。

mysam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引，innodb存储引擎：有且只有一个密集索引。

所以，密集索引就是innodb存储引擎里的聚簇索引，稀疏索引就是innodb存储引擎里的普通二级索引。

为什么要用自增列作为主键？

1、如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。

如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

2、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放

因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

3、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

4、如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

主键和唯一键有什么区别？

主键不能重复，不能为空，唯一键不能重复，可以为空。

建立主键的目的是让外键来引用。

一个表最多只有一个主键，但可以有很多唯一键

说说对SQL语句优化有哪些方法？（选择几条）

（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。

（3）避免在索引列上使用计算

（4）避免在索引列上使用IS NULL和IS NOT NULL

（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

（7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

如何提高insert的性能？

答：有如下方法：

a)合并多条 insert 为一条，即： insert into t values(a,b,c), (d,e,f) ,,,

原因分析：主要原因是多条insert合并后日志量（MySQL的binlog和innodb的事务日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

b)修改参数bulk_insert_buffer_size，调大批量插入的缓存；

c)设置 innodb_flush_log_at_trx_commit = 0 ，相对于 innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度；

（备注：innodb_flush_log_at_trx_commit 参数对 InnoDB Log 的写入性能有非常关键的影响。该参数可以设置为0，1，2，解释如下：

0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file 的刷新或者文件系统到磁盘的刷新操作;

1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步;

2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。）

d）手动使用事务

因为mysql默认是autocommit的，这样每插入一条数据，都会进行一次commit；所以，为了减少创建事务的消耗，我们可用手工使用事务，即START TRANSACTION;insert 。。,insert。。 commit；即执行多个insert后再一起提交；一般1000条insert 提交一次。

什么是覆盖索引？什么是回表查询？

InnoDb存储引擎有两大类索引聚集索引和普通（辅助/二级）索引，聚簇索引的叶子节点存储行记录，因此InnoDb必须要有聚簇索引且仅有一个聚簇索引，而普通索引的叶子节点只存储索引值和主键值，所以，通过聚簇索引一次性能获取所有列的数据，普通索引一般不行。

当我们SQL语句的中列无法在普通索引中获得时，就需要主键值到聚簇索引中获取相关的数据，这个过程就被称为回表。

而如果我们使用联合索引，使得SQL所需的所有列数据在这个索引上就能获得时，我们称为发生了索引覆盖或者覆盖索引。

什么是三星索引？

对于一个查询而言，一个三星索引，可能是其最好的索引。

如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。

一个查询相关的索引行是相邻的或者至少相距足够靠近的则获得一星；

如果索引中的数据顺序和查找中的排列顺序一致则获得二星；

如果索引中的列包含了查询中需要的全部列则获得三星。

三星索引在实际的业务中如果无法同时达到，一般我们认为第三颗星最重要，第一和第二颗星重要性差不多，根据业务情况调整这两颗星的优先度。

大表关联查询优化

一个6亿的表a，一个3亿的表b，通过tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

1、如果A表TID是自增长,并且是连续的,B表的ID为索引

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

[SELECT *] 和[SELECT 全部字段]有何优缺点？

1>.前者要解析数据字典，后者不需要

2>.结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。

3>.表字段改名，前者不需要修改，后者需要改

4>.后者可以建立索引进行优化，前者无法优化

5>.后者的可读性比前者要高

请概述下什么是MySQL的分区表

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

1、表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

2、表分区的好处？

1）存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据

2）优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

3）分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

3、分区表的限制因素

一个表最多只能有1024个分区

如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

分区表中无法使用外键约束

MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

4、分区表的类型

RANGE分区：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区

LIST分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。

HASH分区：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

KEY分区：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

Column分区：需要和RANGE和List结合，支持字符串和日期的分区，也支持多列分区。

复合分区/子分区：分区之下还可以再分区。

5、在实际工作中用分区表比较少

1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

2）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么操作的，不太可控

3）分区表无论怎么分，都是在一台机器上，天然就有性能的上限。

说几条MySQL对SQL的执行做的优化手段

1、对SQL语句的优化，MySQL会对我们的SQL语句做重写，包括条件化简，比如常量传递、除没用的条件等等；还会将一些外连接转换为内连接，然后选择成本最低的方式执行；对IN子查询会进行物化、物化表转连接查询、转换为半连接等方式进行。

2、在SQL语句的执行过程中，MySQL引入了索引条件下推。比如where后面的多个搜索条件都使用到了一个二级索引列，这些搜索条件中虽然出现了索引列，有些却不能使用到索引，像like ‘%...’查询，MySQL为了避免不必要的回表，从二级索引取得的索引记录，先做条件判断，如果条件不满足，则该二级索引记录不会去回表，这样可以大量的减少回表操作的随机IO成本。

3、在回表操作上，因为每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机IO带来的性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR，多范围读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作。

4、MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，也可能在一个查询中使用到多个二级索引，称之为：索引合并，比如Intersection交集合并、Union索引合并等等。

InnoDB引擎的三大特性是什么？

InnoDB的三大特性是：Buffer Pool、自适应Hash索引、双写缓冲区。

自适应Hash索引，InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据。

InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。

Buffer Pool，为了提高访问速度，MySQL预先就分配/准备了许多这样的空间，为的就是与MySQL数据文件中的页做交换，来把数据文件中的页放到事先准备好的内存中。数据的访问是按照页（默认为16KB）的方式从数据文件中读取到 buffer pool中。Buffer Pool按照最少使用算法（LRU），来管理内存中的页。

Buffer Pool实例允许有多个，每个实例都有一个专门的mutex保护。Buffer Pool中缓存的数据页类型有: 索引页、数据页、undo页、插入缓冲（insert buffer)、自适应哈希索引、InnoDB存储的锁信息、数据字典信息（data dictionary)等等。

双写缓冲区，是一个位于系统表空间的存储区域，在写入时，InnoDB先把从缓冲池中的得到的page写入系统表空间的双写缓冲区。之后，再把page写到.ibd数据文件中相应的位置。如果在page写入数据文件的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。

doublewrite是顺序写，开销比较小。所以在正常的情况下, MySQL写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是从doublewrite buffer写到真正的数据文件中。

它的主要作用是为了避免partial page write（部分页写入）的问题。因为InnoDB的page size一般是16KB，校验和写入到磁盘是以page为单位进行的。而操作系统写文件是以4KB作为单位的，每写一个page，操作系统需要写4个块，中间发生了系统断电或系统崩溃，只有一部分页面是写入成功的。这时page数据出现不一样的情形，从而形成一个"断裂"的page，使数据产生混乱。

redolog和binlog的区别是什么？

答案见下一题

MySQL崩溃后的恢复为什么不用binlog？

1、这两者使用方式不一样

binlog 会记录表所有更改操作，包括更新删除数据，更改表结构等等，主要用于人工恢复数据，而 redo log 对于我们是不可见的，它是 InnoDB 用于保证 crash-safe 能力的，也就是在事务提交后MySQL崩溃的话，可以保证事务的持久性，即事务提交后其更改是永久性的。

一句话概括：binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性。

2、redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。

3、redo log是物理日志，记录的是“在某个数据页上做了什么修改”，恢复的速度更快；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这的c字段加1 ”，binlog有三种日志记录格式Row、SQL、混合模式。

4、redo log是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。

5、最重要的是，当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有。

比如，binlog 记录了两条日志：

给 ID=2 这一行的 c 字段加1

在记录1入表后，记录2未入表时，数据库 crash。重启后，只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘，哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 ID=2 这行数据来说，都不对。

但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

MySQL如何实现事务的ACID？

参见下个问题。

InnoDB事务是如何通过日志来实现的？

总的来说，事务的原子性是通过 undo log 来实现的，事务的持久性性是通过 redo log 来实现的，事务的隔离性是通过读写锁+MVCC来实现的。

事务的一致性通过原子性、隔离性、持久性来保证。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。同时一致性也需要应用程序的支持，应用程序在事务里故意写出违反约束的代码，一致性还是无法保证的，例如，转账代码里从A账户扣钱而不给B账户加钱，那一致性还是无法保证。

至于InnoDB事务是如何通过日志来实现的，简单来说，因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo，然后修改数据页，再记数据页修改的redo。 Redo（里面包括 undo 的修改）一定要比数据页先持久化到磁盘。

当事务需要回滚时，因为有undo，可以把数据页回滚到前镜像的状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

更详细的回答是：

redo通常是物理日志，记录的是页的物理修改操作，用来恢复提交事务修改的页操作。而undo是逻辑日志，根据每行记录进行记录，用来回滚记录到某个特定的版本。

当事务提交之后会把所有修改信息都会存到redo日志中。redo日志由两部分组成，一个是在内存里的redo log buffer，另一个是在磁盘里的redo log文件。

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

系统重启后读取redo log恢复最新数据。虽然redo log会在事务提交前做一次磁盘写入，但是这种IO操作相比于buffer pool这种以页（16kb）为管理单位的随机写入，它做的是几个字节的顺序写入，效率要高得多。

redo log buffer中的数据，会在一个合适的时间点刷入到磁盘中。

这个合适的时间点包括：

1、MySQL 正常关闭的时候；

2、MySQL 的后台线程每隔一段时间定时的讲 redo log buffer 刷入到磁盘，默认是每隔 1s 刷一次；

3、当 redo log buffer 中的日志写入量超过 redo log buffer 内存的一半时，即超过 8MB 时，会触发 redo log buffer 的刷盘；

4、当事务提交时，根据配置的参数 innodb_flush_log_at_trx_commit 来决定是否刷盘。要严格保证数据不丢失，必须得保证 innodb_flush_log_at_trx_commit 配置为 1。

redo log 在进行数据重做时，只有读到了 commit 标识，才会认为这条 redo log 日志是完整的，才会进行数据重做，否则会认为这个 redo log 日志不完整，不会进行数据重做。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行回滚时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

而事务的隔离性，也可以通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，帮助用户实现一致性非锁定读取，这也是MVCC的实现机制的组成部分。

什么是当前读和快照读？

当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。是一种悲观锁的实现。

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC。

什么是MVCC？

MVCC (Multi-Version Concurrency Control) ，叫做基于多版本的并发控制协议。他是和LBCC（Lock-Based Concurrency Control）基于锁的并发控制概念是相对的。MVCC是乐观锁的一种实现方式，它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS包括MySQL，都支持了MVCC。

MVCC的底层实现原理是什么？

MVCC实现原理主要是依赖记录中的隐式字段，undo日志，Read View 来实现的。

MySQL中每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段。DB_TRX_ID是最近修改(修改/插入)事务ID，记录创建这条记录/最后一次修改该记录的事务ID。DB_ROLL_PTR，回滚指针，用于配合undo日志，指向这条记录的上一个版本。

不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，也就是版本链。

事务进行快照读操作的时候产生一个Read View，记录并维护系统当前活跃事务的ID，因为当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大。

Read View主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本。

RC,RR级别下Read View生成时机的不同，造成RC,RR级别下快照读的结果的不同。RC隔离级别下，是每个快照读都会生成并获取最新的Read View，也就是说事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见。

什么是锁？MySQL 中提供了几类锁？

锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁，MyISAM 只支持表级锁。

什么是全局锁、共享锁、排它锁？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份。这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。

排他锁 exclusive lock（也叫 writer lock）又称写锁。

若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。排它锁是悲观锁的一种实现。

若事务 1 对数据对象 A 加上 X 锁，事务 1 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。

MySQL中的表锁有哪些？

MySQL 里表级锁有两种：普通表级锁、元数据锁（meta data lock）简称 MDL和AUTO-INC锁。表锁的语法是 lock tables t read/write。

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL：不需要显式使用，在访问一个表的时候会被自动加上。

MDL 的作用：保证读写的正确性。

在对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。

AUTO-INC锁，也就是在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值。

InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁，在锁的算法实现上有三种：

· Record lock：单个行记录上的锁

· Gap lock：间隙锁，锁定一个范围，不包括记录本身

· Next-key lock：record+gap 锁定一个范围，包含记录本身

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生，innodb对于行的查询使用next-key lock，Next-locking keying是Record lock和Gap lock的组合。当查询的索引含有唯一属性时，将next-key lock降级为record key。

有两种方式显式关闭gap锁，第一种. 将事务隔离级别设置为RC ；第二种. 将参数innodb_locks_unsafe_for_binlog设置为1。

谈一下MySQL中的死锁

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁。

如何查看死锁？

使用命令 show engine innodb status 查看最近的一次死锁。

InnoDB Lock Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。

对待死锁常见的两种策略：

通过 innodblockwait_timeout 来设置超时时间，一直等待直到超时；

发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

简述下MySQL8中的新增特性有哪些

MySQL8在功能上的我们需要关注增强主要有：1、账户与安全；2、索引3、InnoDB 增强。

主要表现在：

1、用户的创建与授权需要两条单独的SQL语句执行。认证插件更新。密码管理和角色管理发生变化。

2、隐藏索引，被隐藏的索引不会被优化器使用，但依然真实存在，主要用于软删除，可以根据需要后续真正删除或者重新可视化。

开始真正支持降序索引，以往的MySQL虽然支持降序索引，但是写盘的时候依然是升序保存。MySQL8.0中则是真正的按降序保存。

不再对group by操作进行隐式排序。

索引中可以使用函数表达式，创建表时创建一个函数索引，查询的时候使用同样的函数就可以利用索引了。

3、原子ddl操作，MySQL5.7执行drop命令 drop table t1,t2; 如果t1存在，t2不存在，会提示t2表不存在，但是t1表仍然会被删除，MySQL8.0执行同样的drop命令，会提示t2表不存在，而且t1表不会被删除，保证了原子性。

自增列持久化，解决了之前的版本，主键重复的问题。MySQL5.7及其以前的版本，MySQL服务器重启，会重新扫描表的主键最大值，如果之前已经删除过id=100的数据，但是表中当前记录的最大值如果是99，那么经过扫描，下一条记录的id是100，而不是101。MySQL8.0则是每次在变化的时候，都会将自增计数器的最大值写入redo log，同时在每次检查点将其写入引擎私有的系统表。则不会出现自增主键重复的问题。

林秋地整理