MySQL高频面试题

功名半纸_风雪千山

2024-08-22 197 阅读19分钟

Mysql调优（5个方面）

sql语句优化
1. 避免全表查询(SELECT *，LIKE ’%xxx%'等)；
2. 减少子查询(用jion代替)；
3. 合理限制查询结果数量(用LIMIT子句)
索引优化
1. 合理使用索引（主要用于经常查询的列）；
2. 避免过度使用索引（索引存储占据物理空间，影响写操作的性能。）
数据库结构优化
1. 使用适当的数据类型
2. 选择合适的存储引擎
3. 优化表的行格式
4. 以及合理配置缓存区大小
架构优化
1. 读写分离：利用主从复制
2. 数据库分片（水平分库/垂直分库）
3. 分布式数据库：ES
  1. 使用缓存：Redis/Memcached
硬件升级
1. 选择高性能CPU
2. 增加内存容量
3. 使用高速磁盘
4. 优化网络配置

Mysql数据库中的引擎？

InnoDB、MyIsam、Memory、Archive、CSV

为什么选择InnoDB?

事务支持：
1. InnoDB支持ACID（原子性、一致性、隔离性、持久性）事务，这对于需要保证数据完整性和并发控制的应用至关重要。
行级锁：
1. 与MyISAM的表级锁定相比，InnoDB的行级锁定可以显著提高数据库的并发性能。
外键约束：
1. InnoDB支持外键，可以维护表之间的引用完整性，这对于复杂的数据库关系和数据完整性非常重要。
崩溃恢复：
1. InnoDB具有崩溃恢复能力，即使在数据库服务器崩溃后也能保持数据的一致性。
高并发性：
1. InnoDB的MVCC（多版本并发控制）机制允许在不锁定资源的情况下读取数据，从而提高并发读取的性能。
可扩展性：
1. InnoDB设计用于高负载和大数据量的环境，具有良好的可扩展性。
支持全文索引：
1. InnoDB支持全文索引，可以用于实现高效的文本搜索功能。
在线 DDL 操作：
1. InnoDB支持在线（不锁定表）进行DDL（数据定义语言）操作，如添加索引、修改表结构等。
缓冲池：
1. InnoDB的缓冲池可以提高数据库的I/O性能，通过缓存频繁访问的数据页。
自动增长列 (自动扩展) ：
1. InnoDB的表可以自动扩展，适应数据量的增长。
高可用性：
1. InnoDB与MySQL的复制和集群功能良好集成，支持高可用性配置。

InnoDB和MyIsam的区别？

	事务	锁	外键约束	崩溃恢复	性能	全文索引	索引特性	适合场景
InnoDB	支持	行级锁	支持	有(日志文件)	好	支持	B+树	高并发、数据完整性强
MyIsam	不支持	表级锁	不支持	没有	差	不支持	B+树和hash索引	读密集

mysql事务？

指对数据库执行一批操作，在同一个事务中，这些操作要么全执行，要么全部不执行，保证数据的完整性。

事务的实现原理？

基于transaction log(事务日志)、redo log(重做日志)、 undo log(回滚日志)、事务日志、锁、MVCC机制展开，说明如何保证ACID属性。

分别说明transaction log(事务日志)、 redo log(重做日志)、undo log(回滚日志)？

transaction log：事务日志是实现事务原子性和持久性的核心。它记录了事务对数据库所做的所有修改，包括插入、更新和删除操作。

redo log：重做日志是事务日志的一部分，用于记录事务对页的修改，确保在系统崩溃后可以从日志中重做这些修改，从而保证事务的持久性。

undo log：回滚日志记录了事务进行的修改之前的状态，使得在事务失败或需要回滚时，可以利用这些日志将数据恢复到原始状态，保证原子性。

事务的ACID属性？

原子性：事务中的操作要么全部执行成功，要么全部执行失败。
一致性：数据的状态是一致的。
隔离性：多个事务并发执行，彼此互不影响。
持久性：事务一旦提交，对数据的修改永久保存。

InnoDB如何保证ACID？

原子性：通过undo log保证，如果事务失败或需要回滚，undo日志中的信息可以用来撤销事务的所有更改，将数据库状态恢复到事务开始之前。
一致性：InnoDB通过约束、触发器和MVCC（多版本并发控制）等机制来维护一致性。
隔离性：InnoDB通过锁机制（如行级锁和表级锁）和MVCC来实现事务的隔离性。
持久性：InnoDB通过redo log来保证事务的持久性。事务提交时，所有的修改操作记录在redo日志中，并在必要时将这些更改应用到数据库中以恢复事务的状态

事务的隔离级别，及每个级别能解决的问题？

读未提交
读已提交，解决脏读问题
可重复读，解决脏读、不可重复读问题
串行化，解决脏读、不可重复读、幻读问题

脏读、不可重复读、幻读？

脏读：当一个事务读取了其他食物中未提交的数据，并且这些数据最终可能被回滚，就会出现脏读。

不可重复读：一个事务先后读取同一条记录，而该数据在这两次读取期间被其他事务修改，导致读取的同一数据不一致。

幻读：指一个事务中多次执行相同的查询，但在多次查询期间，有其他事务修改了数据，导致查询结果不一致。

死锁及死锁产生的必要条件？

在两个及以上的事务中，因争夺资源而发生的相互等待情况，称为死锁。

死锁产生的条件：

互斥条件
占有和等待条件
不可剥夺条件
循环等待条件

死锁的避免方式？

银行家算法

通过动态地检测系统中资源分配情况和进程对资源的需求情况，来决定如何分配资源，并能在确保系统处于安全状态时才把资源分配给申请者，从而避免系统发生死锁。

如何在一个大批量数据插入中提高性能？

批处理插入： 使用单个INSERT INTO语句插入多行数据。mybatis中使用标签批量插入数据。
使用事务： 将一批数据的插入操作放在一个事务中，有助于确保数据一致性，并减少日志写入次数，进一步提升性能。
关闭索引更新：在插入大量数据之前，暂时禁用非唯一索引的更新，待数据插入完毕后再重新启用，这样可以避免在插入过程中频繁更新索引，显著加快插入速度。
利用 LOAD DATA INFILE：对于非常大的数据集，使用LOAD DATA INFILE命令直接从文件中快速加载数据到表中，通常比使用INSERT语句更为高效

共享锁/排他锁？

共享锁：允许多个事务同时获取读取权限，但没有写权限。
排他锁：只允许单个事务进行读写操作。

表级锁/行级锁？

表级锁（Table-Level Locks） ：
1. 表级锁是MySQL中最基本的锁类型，它锁定整个表，不允许其他用户对同一个表进行写操作，但可以进行读操作，除非明确禁止。
2. 表级锁的粒度较大，使用时会锁定整个表，适用于整个表只涉及少数行更新的情况。
3. MyISAM存储引擎使用表级锁。
行级锁（Row-Level Locks） ：
1. 行级锁是更细粒度的锁，它只锁定数据表中的一部分行记录，其他行仍然可以被其他用户读写。
2. 行级锁可以提高数据库的并发性能，因为它允许多个用户同时对不同行进行操作。
3. InnoDB存储引擎支持行级锁，这是它相比MyISAM的一个重要优势。

乐观锁/悲观锁？

乐观锁：总是默认多线程中，访问的数据不会被修改，不存在数据不一致等问题。所以不会上锁，只有在提交更新时，才会正式对数据的冲突与否进行检测。分为三个阶段：数据读取、写入校验、数据写入。如：CAS
悲观锁：认为在多线程中，访问的数据一定会被修改，所以需要加锁来保证数据的一致性。如：synchronized

为什么mysql要加锁？

MySQL中的锁机制是为了解决数据库在多用户并发访问时可能出现的问题，主要包括：

数据一致性：
1. 在事务过程中，为了保证数据的一致性和完整性，需要使用锁来确保事务操作的原子性。
隔离性：
1. 锁机制可以防止多个事务同时修改同一数据，从而避免事务间的干扰，保证事务的隔离性。
避免更新丢失：
1. 当多个用户试图同时修改同一数据时，锁可以确保一次只有一个用户能够进行修改，防止数据更新丢失。
防止死锁：
1. 虽然死锁本身是一个需要避免的问题，但锁机制内的死锁检测和解决策略可以帮助维持数据库操作的正常进行。
提高性能：
1. 适当的锁策略可以减少等待时间，提高数据库的并发处理能力，尤其是在高负载环境下。
顺序控制：
1. 锁可以控制事务的执行顺序，确保依赖于特定顺序的数据库操作能够按照预期的顺序执行。
实现乐观 并发 控制和悲观并发控制：
1. 乐观锁通常通过版本号或时间戳来实现，它假设冲突发生的可能性很小；悲观锁则在可能发生冲突的情况下直接锁定数据，以避免冲突。
满足ACID属性：
1. 在数据库事务中，ACID属性（原子性、一致性、隔离性、持久性）的实现很大程度上依赖于锁机制。
保护数据结构：
1. 锁可以保护数据库的数据结构，防止在结构变更（如添加或删除索引）时数据被并发访问。
遵守业务规则：
1. 在某些业务场景下，特定的业务规则需要通过数据库锁来强制执行，以保证业务逻辑的正确性。

mysql索引

主键索引：唯一标识表中每行数据的主键自动创建的索引。
唯一索引：保证列值的唯一性，可以有多行具有NULL值。
普通索引：普通的索引，没有唯一性要求。
全文索引：用于对文本数据进行全文搜索。
空间索引：用于地理空间数据类型，以优化空间查询。
复合索引：一个索引包含多个列，按照索引列的顺序进行查询，可以提高多列查询的效率。
覆盖索引：当一个查询只需要访问索引中的列，而不需要访问表中其他数据时，这种情况称为索引覆盖，可以提高查询效率。

哪些场景需要创建索引？

经常作为查询条件的列：
1. 如果某个列经常用于WHERE子句或其他条件表达式中，为该列创建索引可以加快查询速度。
参与JOIN操作的列：
1. 在执行连接查询时，如果连接条件的列上有索引，可以提高JOIN操作的效率。
排序操作：
1. 当使用ORDER BY子句对结果集进行排序时，如果涉及的列上有索引，可以加快排序过程。
分组操作：
1. 使用GROUP BY子句进行数据分组时，为分组依据的列创建索引可以提升性能。
聚合操作：
1. 对于需要使用COUNT、SUM、MAX、MIN等聚合函数的列，索引可以加速这些操作。
分页查询：
1. 在进行分页查询时，如果查询包含LIMIT和OFFSET，索引可以帮助数据库更快地定位到指定的数据范围。
外键列：
1. 外键列通常应该建立索引，以支持高效的外键约束检查和连接操作。
唯一性约束列：
1. 对于需要保证数据唯一性的列，唯一索引不仅可以强制唯一性，还可以提高查询效率。
经常用于比较操作的列：
1. 如果某些列经常用于比较操作（如>、<、=等），索引可以加快这些操作的速度。
搜索和模糊查询：
1. 对于需要执行搜索或模糊查询（如LIKE）的列，特别是当搜索模式不是以通配符开头时，索引可以提高查询性能。
数据量较大的表：
1. 对于数据量较大的表，索引可以显著减少查询所需的数据扫描范围。
报表和数据分析：
1. 在生成报表或进行数据分析时，如果涉及复杂的查询和多个查询条件，索引可以提高数据处理速度。

建立索引的原则？

mysql数据存储结构？

B+树

mysql为什么用B+树？

MySQL，特别是其默认的存储引擎InnoDB，使用B+树作为索引结构，主要是因为B+树提供了许多适合数据库系统的特性：

高扇出度：
1. B+树的每个内部节点可以拥有大量子节点，这减少了树的高度，从而减少了查找数据时的磁盘I/O次数。
减少I/O操作：
1. 由于B+树的高度较低，访问任何数据行所需的I/O操作次数大大减少，这对于基于磁盘的存储系统尤其重要。
快速范围查询：
1. B+树的叶子节点通过指针相互连接，形成了一个有序链表，这使得在叶子节点上进行范围查询非常高效。
高效的插入和删除操作：
1. B+树的插入和删除操作通常只影响叶子节点，并且可以通过树的分裂和合并来维护树的平衡，这使得B+树在动态数据集中表现良好。
锁机制：
1. B+树的结构使得行级锁定更加高效，因为锁定可以仅在叶子节点上进行，而不是整个树。
稳定性：
1. B+树的结构使得树的中间层即使在大量插入和删除操作之后也能保持相对稳定，这有助于维护查询性能。
适应性：
1. B+树可以很好地适应不同的数据类型和长度，因为它们可以根据数据的大小动态调整节点的分裂和合并。
顺序访问特性：
1. B+树的叶子节点形成了一个有序的序列，这不仅有助于快速定位数据，还支持高效的顺序访问。
事务支持：
1. B+树的结构与事务型数据库的ACID属性兼容，这对于需要事务支持的数据库系统非常重要。

综上所述，B+树为MySQL提供了一个高效的索引结构，能够满足数据库在读写性能、数据一致性、事务处理和存储效率方面的要求。

B+树和B-树的区别？

	B-树	B+树
节点结构	每个节点可以包含多个键值和多个子节点。键值在节点内部和叶子节点中都会出现	内部节点仅包含分割键（用于确定子树的界限）和指向子节点的指针。所有的数据记录都存储在叶子节点中。
叶子节点	叶子节点可能包含数据记录和/或指向数据记录的指针	所有数据记录都存储在叶子节点中，并且叶子节点通过指针相互连接，形成有序链表，便于范围查询。
查询性能	查询可能在内部节点或叶子节点完成，可能需要多次随机访问	查询通常直接在叶子节点完成，可以顺序访问，对于范围查询特别有利
存储空间	由于内部节点存储键值，可能需要更多的存储空间	内部节点仅存储分割键和子节点指针，因此可以存储更多的子节点，每个节点可以更高，减少树的高度

mysql的最左前原则和前缀原则？

最左前缀原则

最左前缀原则指的是，在使用多列索引时，索引的匹配过程会从索引的最左侧列开始检查。只有当最左侧列的值匹配成功后，MySQL才会继续检查索引的下一列。如果查询条件不满足最左列，即使后续列的值满足，索引也不会被使用。

前缀索引原则

前缀索引原则是指对于字符串类型的列，可以只为列的前几个字符创建索引，而不是为整个列创建索引。这种做法可以减少索引的大小，从而提高索引的效率，但同时也意味着可能无法利用索引进行精确匹配。

mysql调优？

mysql中的慢查询？

sql查询语句中执行顺序？

用到的关键字：select--from--where--group by--having--order by

执行顺序：from -> where -> group by -> having -> select -> order by

distinct和group by的区别？

两者都是SQL用于处理数据重复性的方法。

distinct：用于返回唯一不同的值。它会对查询结果集中的所有列进行去重操作，确保每一列都是唯一的。

Group by：用于结合聚合函数，根据一个或多个列对结果集进行分组。

区别：

	distinct	Group by
目的	去除重复行	数据聚合和分组
聚合函数	不与聚合函数一起使用	通常会结合聚合函数进行统计计算
结果集	去重后的记录	每个分组的聚合结果
特定场景	需要从查询结果中排除重复项时	需要对数据进行分组并计算每个分组的统计数据时

数据库三范式？

第一范式：数据库表的每一列都是不可分割的基本数据项。

第二范式：排除非主属性对码的部分依赖。

第三范式：排除非主属性对码的传递依赖。

分库分表？

分库：将一个数据库拆分成多个数据库，然后部署到不同的服务器上。

分表：把一个数据库拆分成多张表。

分库分表的原因：

让MySQL能够支撑大数据存储和高并发访问。以解决存储问题、大量请求阻塞、查询性能降低。

分片算法？

范围

直接选择指定范围内的数据，进行分片。

哈希

对分片数量N取模，但是不同的是，在取模之前对Sharding Key做一次hash计算。这样能让数据分布更均匀，且实现简单；缺点：扩容不方便，需要以2倍扩容，最少迁移50%数据到新分片。

一致性哈希

将所有的数据和服务器都映射到一个固定大小的哈希环上，通过对数据的哈希值进行计算，将其映射到环上的某个位置，然后选择离这个位置最近的服务器作为数据的存储位置。

分库分表会带来的问题和解决办法？

跨库关联查询
1. 解决办法：
2. 字段冗余：把需要关联的字段放到主表中，避免jion操作。
3. 数据抽象：通过ETL等将数据汇合聚集，生成新的表。
4. 全局表：一些基础表可以在每个数据库都放一份。
分布式事务

解决办法：基于MQ的解决方案、两阶段事务提交、柔性事务。

排序、分页、函数计算问题

解决办法：现在每个分片进行相应计算，再汇总，进行计算。

分布式ID

之前单表可以使用id自增作为主键，分库分表后，这样做会出现id重复。

解决办法：

UUID
Redis缓存
雪花算法
美团Leaf

数据库主从同步和读写分离？

主从同步：

主库记录bin log——只有数据库发生事务提交时，会将数据的操作细节记录在bin log中。
从库读取并写入relay log——从库通过网络连接持续监听主库的bin log，并将其读取后写入自身的relay log中。
从库重新执行时间更新数据——从库单独启动一个线程，负责执行relay log中的事件。这些事件按照在主库上发生的顺序依次执行，使从库的数据状态与主库保持一致。

读写分离：

搭建数据库主从集群，主库负责处理写操作，从库负责处理读请求。

MVCC原理？

多版本并发控制（MVCC），通过保存数据的多个版本，使得读操作可以读取旧版本的数据，而写操作则生成新版本的数据。使得读/写操作不被阻塞。从而提高系统的并发性。还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

工作原理：

版本链——每条记录在数据库中保存当前版本的数据和一个版本链，记录该数据的历史版本。
隐式字段——每条记录增加隐式字段，如trx_id(事务id)、roll_pointer(回滚指针)
当前读
快照读