【面试宝典】MySQL 面试问题

一、MySQL 中有哪几种锁？

MySQL中的锁机制是数据库并发控制的重要组成部分，它用于管理多个用户对数据库资源的访问，确保数据的一致性和完整性。MySQL中的锁可以根据不同的分类标准进行分类，以下是一些常见的分类方式及对应的锁类型：

1、按锁的粒度分类

全局锁

• 描述：全局锁是锁定数据库中所有的表，即锁住的是整个数据库实例。全局锁主要应用于做数据备份。
• 使用场景：最典型的使用场景是做全库的数据备份，保证数据的一致性和完整性。
• 操作方式：通过FLUSH TABLES WITH READ LOCK命令加全局锁，通过UNLOCK TABLES命令释放全局锁。

表级锁

• 描述：表级锁每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 使用场景：应用在MyISAM、InnoDB等存储引擎中，尤其是在MyISAM引擎中较为常见。
• 类型：包括读锁（共享锁）和写锁（排他锁）。
• 操作方式：通过LOCK TABLES 表名 READ/WRITE命令加表锁，通过UNLOCK TABLES命令释放表锁。

行级锁

• 描述：行级锁每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率低，并发度高。
• 使用场景：在InnoDB存储引擎中较为常见，用于处理高并发的数据库操作。
• 类型：包括记录锁、间隙锁、临键锁等。
• 实现方式：InnoDB的行级锁并不是直接锁记录，而是锁索引。

2、按锁的属性分类

共享锁（S锁）

• 描述：又称读锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 使用场景：在需要读取数据但不修改数据的场景中使用。

排他锁（X锁）

• 描述：又称写锁，在当前写操作没有完成前，它会阻断其他写锁和读锁。
• 使用场景：在需要修改数据的场景中使用。

3、其他锁类型

意向锁

• 描述：意向锁是表级锁，分为意向共享锁（IS锁）和意向排他锁（IX锁）。它们是为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁。
• 使用场景：在InnoDB存储引擎中，当事务准备在某条记录上加S锁或X锁时，需要先在表级别加一个IS锁或IX锁。

元数据锁（MDL）

• 描述：MDL主要用于维护表结构的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
• 使用场景：当对一张表进行增删改查时，会自动加上MDL共享读锁；当对表结构进行变更操作时，会自动加上MDL写锁。

乐观锁

• 描述：乐观锁并不是真正的锁机制，而是通过某种方式（如版本号）在数据提交更新时进行检查，如果发现冲突则回滚操作。
• 使用场景：适用于写操作较少，冲突概率较低的场景。

悲观锁

• 描述：悲观锁认为冲突总是有可能发生的，因此在数据处理过程中就直接加锁。
• 使用场景：适用于写操作较多，冲突概率较高的场景。

二、MySQL 中有哪些常见的存储引擎？

1、MyISAM（常用）

• 描述：MyISAM是MySQL早期默认的存储引擎之一，它使用独立的数据文件（.MYD）和索引文件（.MYI）来存储数据和索引。MyISAM不支持事务处理、行级锁和外键约束。
• 用途：适用于读密集型的应用场景，如Web和数据仓库。

2、InnoDB（常用）

• 描述：InnoDB是MySQL的另一种常用存储引擎，它支持事务处理、行级锁和外键约束等高级数据库功能。InnoDB通过聚簇索引来存储数据，这意味着数据和索引是存储在一起的。
• 用途：适用于需要事务处理、高并发和复杂查询的应用场景。

3、Memory（HEAP）

• 描述：Memory存储引擎将数据存储在内存中，因此访问速度非常快。但是，由于数据存储在内存中，MySQL服务器重启后数据会丢失。
• 用途：适用于临时表或需要快速访问的数据集。

4、CSV

• 描述：CSV存储引擎以逗号分隔的值（CSV）格式存储数据，这使得数据导入和导出变得非常简单。
• 用途：适用于需要频繁导入和导出数据的应用场景。

5、Archive

• 描述：Archive存储引擎专为存储大量历史数据而设计，它使用压缩技术来减少存储空间的使用。Archive表只支持INSERT和SELECT操作，不支持UPDATE和DELETE操作。
• 用途：适用于日志记录、数据归档等场景。

6、Blackhole

• 描述：Blackhole存储引擎是一个“黑洞”引擎，它会丢弃所有插入的数据，不存储任何数据。但是，它会记录日志，并将SQL语句转发到另一个MySQL服务器或数据库。
• 用途：用于复制或数据分发等场景。

7. Federated

• 描述：Federated存储引擎允许MySQL数据库访问远程MySQL服务器上的表，就像访问本地表一样。
• 用途：适用于分布式数据库系统或需要跨服务器查询的场景。

三、MyISAM 和 InnoDB 区别

MyISAM和InnoDB是MySQL数据库中两种常用的存储引擎，它们在多个方面存在显著的区别。以下是对这两种存储引擎的详细比较：

1、数据存储结构

MyISAM：

• MyISAM在磁盘上存储为三个文件，以表名开头命名。这三个文件分别是：
  • .frm 文件：存储表结构定义。
  • .MYD 文件：存储数据文件。
  • .MYI 文件：存储索引文件。
• MyISAM的数据和索引是分开存储的，查询时，MyISAM的叶子节点存储的是数据所在的地址，而不是直接存储数据。
• MyISAM支持三种不同的存储格式：静态表（默认）、动态表、压缩表。

InnoDB：

• InnoDB在磁盘上的存储方式有两种：共享表空间存储和多表空间存储。
  • 如果使用共享表空间，所有表的数据文件和索引文件都保存在一个表空间里，一般名为ibdata1-n。
  • 如果使用多表空间，每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。
• InnoDB的数据和索引是存储在一起的，叶子节点存储的是整的数据。

2、锁机制

MyISAM：

• MyISAM使用表级锁，这意味着在对表进行写操作时，会锁定整张表，从而阻塞其他用户的读和写操作。
• 这种锁机制在读取密集的应用中可以提高并发性能，但在写操作较多的情况下，性能会受到影响。

InnoDB：

• InnoDB支持行级锁和表级锁，但主要通过行级锁来提高并发性能。
• InnoDB使用MVCC（多版本并发控制）技术来实现行级锁定，使得读操作不会阻塞写操作，同时保证数据的可重复读性。

3、事务支持

MyISAM：

• MyISAM不支持事务处理，这意味着无法使用回滚和提交等事务控制操作。

InnoDB：

• InnoDB是支持事务的存储引擎，通过ACID（原子性、一致性、隔离性、持久性）事务模型保证数据的完整性和一致性。
• 支持提交、回滚和崩溃恢复等功能，非常适合需要高可靠性和数据一致性的应用场景。

4、外键约束

MyISAM：

• MyISAM不支持外键约束，无法在关联表之间建立完整性约束。

InnoDB：

• InnoDB支持外键约束，可以在关联表之间建立完整性约束，保证了数据的一致性和正确性。

5、其他特性

MyISAM：

• 读取速度快，特别是在读取密集的应用中表现优异。
• 支持全文索引，可以对文本数据进行高效的搜索。
• 对磁盘空间的利用率高，可以压缩数据以减少磁盘空间的占用。

InnoDB：

• 支持热备份，允许在不停机的情况下做数据备份和恢复。
• 支持自适应哈希索引，可以根据查询频率自动调整哈希索引的大小，提高查询性能。
• 支持大事务和高并发性能，适合处理大型数据和复杂查询。

6、适用场景

MyISAM：

• 适用于读密集的应用场景，如Web和数据仓库。
• 适用于不需要事务支持和外键约束的应用。

InnoDB：

• 适用于需要事务处理、高可靠性和数据一致性的应用场景。
• 适用于高并发和复杂查询的应用场景。

综上所述，MyISAM和InnoDB在数据存储结构、锁机制、事务支持、外键约束等方面存在显著的差异。在选择存储引擎时，需要根据应用的实际需求和场景来选择合适的存储引擎。

四、什么是 MySQL 的事务？

MySQL中的事务是一个作为单个逻辑工作单元执行的一系列操作。事务具有ACID属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这些属性确保了事务的完整性和可靠性。

• 原子性：事务中的所有操作要么全部完成，要么全部不执行，不会结束在中间某个环节。
• 一致性：事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

五、MySQL 事务隔离级别有哪些？

MySQL支持四种事务隔离级别，它们逐渐增强了事务之间的隔离，防止了不同程度的并发问题：

1. READ UNCOMMITTED（读未提交）

   • 允许读取尚未提交的数据变更，可能导致脏读、不可重复读和幻读。
   • 最低级别，允许一个事务读取另一个事务未提交的数据。

2. READ COMMITTED（读已提交）

   • 只能读取已提交的数据，避免了脏读，但不可重复读和幻读仍然可能发生。
   • 保证一个事务不会读取到另一个事务未提交的数据。

3. REPEATABLE READ（可重复读）

   • MySQL的默认隔离级别。保证在同一个事务中多次读取同样数据的结果一致，但幻读可能发生。
   • 使用多版本并发控制（MVCC）或锁机制来保证，解决了脏读和不可重复读问题。

4. SERIALIZABLE（可串行化）

   • 完全串行化的读，防止脏读、不可重复读和幻读，但并发性能最差。
   • 强制事务串行执行，通过读写锁来避免并行访问。

六、MySQL事务的持久性是如何实现的？

MySQL事务的持久性是通过重做日志（redo log）来保证的。当事务提交时，事务所做的所有修改都会先写入重做日志中，并在事务提交时将其刷新到磁盘上。这样，即使系统发生故障，也可以通过重做日志来恢复事务的修改，确保数据的持久性。

七、事务的原子性是如何实现的？

事务的原子性是通过回滚日志（undo log）来保证的。当事务执行过程中发生错误或需要回滚时，可以通过回滚日志来撤销事务中所做的修改，将数据库恢复到事务开始前的状态，从而保证事务的原子性。

八、在什么情况下即使设置了数据库索引也会失效？

在数据库中，即使设置了索引，也可能在某些情况下失效，导致查询性能并未得到预期的提升。以下是一些导致索引失效的常见情况：

1、索引列未包含在查询条件中

• 情况描述：如果查询条件中没有使用到索引列，那么索引将不会被利用。
• 示例：假设在user_id上创建了索引，但查询条件是SELECT * FROM users WHERE name = 'John'，此时user_id上的索引将不会被使用。

2、索引列上进行了函数操作

• 情况描述：在查询条件中对索引列使用了函数或表达式，导致索引无法直接利用。
• 示例：查询SELECT * FROM users WHERE LOWER(username) = 'john'，如果username上有索引，但由于使用了LOWER()函数，索引将失效。

3、索引列与查询条件数据类型不匹配

• 情况描述：当查询条件中的数据类型与索引列的数据类型不一致时，数据库可能需要进行类型转换，从而无法利用索引。
• 示例：索引列是字符串类型，但查询条件使用的是数值类型，如SELECT * FROM users WHERE age_str = 30（假设age_str是存储年龄的字符串类型字段）。

4、使用了不等于（!= 或 <>）操作符

• 情况描述：使用不等于操作符时，数据库通常无法利用索引进行范围查找，因为索引是按顺序排列的，而不等于操作无法确定范围。
• 示例：SELECT * FROM users WHERE age != 30，如果age上有索引，该查询可能导致索引失效。

5、LIKE操作符的模糊查询且前缀为通配符

• 情况描述：使用LIKE进行模糊查询时，如果通配符（%）出现在查询字符串的开始位置，索引将失效。
• 示例：SELECT * FROM users WHERE username LIKE '%john'。

6、复合索引的列顺序不匹配

• 情况描述：复合索引（多列索引）需要按索引定义的列顺序使用，如果查询条件中的列顺序与索引定义不一致，索引可能无法被有效利用。
• 示例：复合索引按（A, B）顺序创建，但查询条件是SELECT * FROM table WHERE B = 'value'。

7、索引列上存在大量重复值

• 情况描述：如果索引列上的数据重复度很高，数据库可能会认为使用索引并不能显著提高查询性能，从而选择不使用索引。
• 示例：在性别字段（仅有“男”和“女”两个值）上创建索引，其效果会很差。

8、索引列上进行了隐式类型转换

• 情况描述：当查询条件中的数据类型与索引字段的数据类型不一致时，数据库会进行隐式类型转换，导致索引失效。
• 示例：索引字段是字符串类型，但查询条件使用的是数值类型，如SELECT * FROM table WHERE varchar_column = 123。

9、查询条件中包含OR连接多个条件且部分列无索引

• 情况描述：当使用OR连接多个条件时，如果OR条件中的部分列没有索引，整个查询可能无法有效利用索引。
• 示例：SELECT * FROM table WHERE column1 = 'value1' OR column2 = 'value2'，如果只有column1有索引而column2没有。

10、数据库统计信息过时或不准确

• 情况描述：数据库的查询优化器依赖统计信息来选择是否使用索引。如果统计信息过时或不准确，优化器可能会做出错误的决策，导致索引失效。
• 示例：表的统计信息长时间未更新，导致优化器认为全表扫描比使用索引更有效。

11、数据量较小或索引选择性差

• 情况描述：在数据量非常小的表上创建索引，索引的效果可能不明显，甚至可能导致性能下降。此外，索引选择性差（即索引列中不同值的比例较低）也会导致索引效果不佳。
• 示例：一个只有几百行记录的表，即使创建了索引，查询性能的提升也非常有限。

12、索引碎片过多

• 情况描述：频繁的插入、更新和删除操作会导致索引碎片过多，影响索引的性能。
• 示例：长期未进行索引重建或维护的数据库表。

为了避免索引失效，可以采取以下措施：

• 在创建索引时，合理选择索引列，避免在不需要索引的列上创建索引。
• 在查询条件中避免对索引列使用

九、简单描述MySQL中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

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

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

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

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

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

索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，
因为在执行这些写操作时，还要操作索引文件。

十、MySQL 中的事务回滚机制概述

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个
不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤
销。

要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修
改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依
旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和
第二个表都要回到未修改的状态，这就是所谓的事务回滚.

十一、SQL 优化有哪些？

• 1、Where 子句中：where 表之间的连接必须写在其他 Where条件之前，那些可
  以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
• 2、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。
• 3、避免在索引列上使用计算。
• 4、避免在索引列上使用IS NULL和 IS NOT NULL。
• 5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 orderby 涉
  及的列上建立索引。
• 6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃
  使用索引而进行全表扫描。
• 7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用
  索引而进行全表扫描。