跳至主要內容

MySQL经典面试题

梁晨阳2022年1月1日大约 24 分钟MySQLmysql

MySQL面试题

MySQL经典面试题

内连接和外连接

内连接：
就是组合两个表中的记录，返回两个表中的交集部分，关键字就是inner join
例如 select 字段列表 from 表1 inner join 表2 on 条件语句。
这属于显式内连接，同时还有一个式隐式内连接，是通过where 判断语句来判断条件。
比如：select 字段列表 from 表1 ，表2 where 条件语句。

外连接：
外连接也分为两部分，是左外连接和右外连接
左外连接关键字是 left outer join 也可以缩写为 left join
查询的是左表的所有数据和左表与右表的交集部分，右表的数据符合条件的会显示出来，不符合的会显示为空。 相同，
右外连接的关键字是right outer join 也可以缩写为 right join
查询的是右表的所有数据和左右表交集部分，左表的数据符合条件的会显示出来，不符合会显示为空。
查询语句例如 select 字段列表 from 表1 right join 表2 on 条件语句。

全连接
mysql可以使用union实现全连接。
比如 左外连接语句 union 右外连接语句。
查询出来的是全部数据，就是左外连接查出来的数据和右外连接查出来的数据。

索引：
索引就是帮助数据库快速获取数据的一种数据结构，优点是快速查询数据，减少cpU消耗，缺点是占用内存，增删改效率降低。
使用创建索引就是用create index 索引名 on 表名（字段）；
删除就是使用drop index 索引名 on 表名。
查看索引就使用 show index from 表名。

一条SQL的执行过程是怎样的？

一条SQL的执行过程可以大致分为以下几个步骤：
1．连接器：
。客户端与数据库建立连接，并发送SQL语句给数据库服务。
。连接器验证客户端的身份和权限，确保用户有足够的权限执行该SQL语句。
2．查询缓存：
。连接器首先检查查询缓存，尝试找到与当前SQL语句完全相同的查询结果。
。如果在缓存中找到匹配的结果，查询缓存直接返回结果，避免了后续的执行过程。
3．分析器：
。若查询不命中缓存，连接器将SQL语句传递给分析器进行处理。
。分析器对SQL语句进行语法分析，确保语句的结构和语法正确。
。分析器还会进行语义分析，检查表、列、函数等对象的存在性和合法性，并进行权限验证。 4．优化器：
。分析器将经过验证的SQL语句传递给优化器。
。优化器根据统计信息和数据库的规则，生成多个可能的执行计划，这些计划包括不同的索引选择、连接顺序、筛选条件等。
。目的是选出最优的执行路径以提高查询性能。
5．执行器：
。优化器选择一个最优的执行计划，并将其传递给执行器。
。执行器根据执行计划执行具体的查询操作。
。它负责调用存储引擎的接口，处理数据的存储、检索和修改。
。执行器会根据执行计划从磁盘或内存中获取相关数据，并进行联接、过滤、排序等操作，生成最终的查询结果。
6．存储引擎：
。执行器将查询请求发送给存储引擎组件。
。存储引擎组件负责具体的数据存储、检索和修改操作。
。存储引擎根据执行器的请求，从磁盘或内存中读取或写入相关数据。
7．返回结果：
。存储引擎将查询结果返回给执行器。
。执行器将结果返回给连接器。
。最后，连接器将结果发送回客户端，完成整个执行过程。
需要注意的是，查询缓存在一些场景下可能不太适用，因为它有一定的缺陷和开销。MySQL8.0版本开始，默认情况下查询缓存已被废弃。因此，在实际应用中，需要权衡是否使用查询缓存。

分表后非分片键的查询，排序怎么处理？

  1. 联合查询：如果需要执行涉及多个分表的查询，可以使用联合查询（UNION）或者连接查询（JOIN）来合并结果。将查询条件应用到对应的分表，然后将结果联合起来返回给 用户。
  2. 扫描所有分表：如果查询涉及到的数据无法通过分片键进行过滤，那么可能需要扫描所有的分表来获取满足条件的数据。这样的查询会比较慢，因为需要访问所有的分表并合并结果，但在某些场景下是必要的。
  3. 手动分页和排序：如果需要对分表后的数据进行排序和分页，可以在每个分表上执行独立的排序和分页操作，然后将结果合并或者按需返回给用户。这需要在应用程序中进行额外的逻辑处理。
  4. 使用全局索引：如果有一些特定的查询需要在非分片键上执行，并且这些查询非常频繁，可以考虑创建全局索引。全局索引不关心分片键，而是在整个集群上创建索引，可以加速这类查询的执行速度。

数据库CPU飙升，你会如何处理？

当MySQL数据库的CPU使用率飙升时，可能是由于以下几个原因导致的：

  1. 查询性能问题：某些查询可能没有被正确地优化，导致查询执行时间过长，从而占用大量的CPU资源。可以通过查看慢查询日志和执行计划来分析问题查询，并进行索引优化、重写查询语句或调整数据库配置等方式来改善查询性能。
  2. 数据库连接问题：如果存在大量的数据库连接并发访问，可能会造成CPU负载过高。可以检查应用程序连接池的配置情况、数据库连接数限制以及是否有闲置的连接未关闭等问题，并进行相应调整。
  3. 锁和死锁问题：并发事务之间的锁竞争或死锁可能导致CPU飙升。可以通过查看数据库的锁状态、死锁日志以及事务并发控制的设置来解决锁相关的问题。
  4. 配置问题：不合理的数据库配置可能导致CPU资源浪费和效率低下。可以检查MySQL的配置参数，如缓冲区大小、并发连接数、线程池大小等是否合理设置，并进行相应调整。
  5. 资源竞争：如果服务器的物理资源（如内存、磁盘I／O）不足或受限，可能会导致CPU过度使用。可以通过监控系统资源使用情况，调整或增加资源配置，以满足数据库的需 求。

此外，定期进行数据库性能优化和监控也是重要的措施，可以通过数据库性能分析工具、慢查询日志、系统监控工具等来识别和解决性能问题。

高并发场景下，如何安全修改同一行数据？

在高并发情况下，多事务安全修改同一行数据可以采用以下方法：

  1. 乐观锁：在数据表中添加一个版本号（或者时间戳）字段，每次更新数据时都会检查该字段的值。当多个并发的请求同时修改同一行数据时，只有一个请求能够成功执行更新 操作，其他请求需要重新检查数据是否被修改过。如果数据没有被修改，那么它们可以重新尝试更新；如果数据已经被修改，则这些请求需要触发重试等相应的冲突处理逻 辑。
  2. 悲观锁：在读取数据之前，使用数据库提供的锁机制，如SELECT．．．FOR UPDATE语句，将要修改的行数据进行加锁。这样，其他并发的请求在读取相同行数据时会被阻塞， 直到锁释放。这种方法能够确保同一时间只有一个请求在修改数据，但是会影响系统的并发性能。
  3. 分布式锁：通过使用分布式锁服务，如Redis的SETNX命令或ZooKeeper的临时节点，来实现对行级数据的互斥访问。在修改数据前先尝试获取锁，获取成功后执行数据修改 操作，修改完成后释放锁。其他请求在获取锁失败时可以等待或执行相应的冲突处理逻辑。
  4. 事务：将对同一行数据的修改操作封装在数据库事务中。在事务中，数据库会自动处理并发修改的冲突，通过锁定相应的数据行来确保数据的一致性和完整性。并发的请求会 被串行化执行，保证每个请求都能正确读取并修改数据。 需要注意的是，在进行高并发的数据操作时，要考虑到锁的粒度、性能开销以及可能出现的死锁问题，做好并发控制和冲突处理的容错设计。

如何优化深分页limit 1000000

深分页问题是MySQL中常见的性能问题，当你尝试获取大量数据的后续页面时，性能会显著下降。这是因为MySQL需要先扫描到指定的偏移量，然后再返回数据。 例如，以下查询可能会非常慢：

SELECT * FROM table ORDER BY id LIMIT 1000000,10;

这是因为MySQL需要先扫描1000000行数据，然后再返回后面的10行数据。

解决深分页问题的常见方法有以下几种：

  1. 使用覆盖索引：覆盖索引可以让MySQL在索引中获取所有需要的数据，而无需回表查询。这可以大大提高查询速度。
SELECT id FROM table ORDER BY id LIMIT 1000000,10;
  1. 记住上次的位置：如果你的应用程序可以记住上次查询的最后一个ID，那么你可以使用WHERE 子句来避免扫描大量数据。
SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT 10;

  1. 使用分页插件：有些数据库框架提供了分页插件，可以自动优化分页查询。

  2. 避免深分页：在设计应用程序时，尽量避免深分页。例如，你可以提供搜索功能，让用户快速找到他们需要的数据，而不是浏览大量的页面。

以上只是一些常见的解决方法，具体的解决方案需要根据实际情况来确定。

自增主键会遇到什么问题？

  1. 插入性能问题：对于非常高并发的插入操作，自增主键可能会成为性能瓶颈。因为每次插入新记录时，都需要获取一个新的自增ID，这个操作是串行的，无法并发执行。
  2. 主键耗尽：如果表的记录非常多，可能会出现自增主键耗尽的情况。尤其是对于定义为整型的自增主键，如果插入的记录数超过了整形的最大值，就无法再插入新的记录。
  3. 分布式系统问题：在分布式系统中，如果多个数据库节点都需要生成自增主键，就需要保证生成的主键在全局是唯一的。这通常需要引入额外的机制或工具，比如分布式ID生成器。

union与union all的区别？

它们都是在sql中用于合并查询结果集的操作符，他们之间存在以下区别：

  1. union用于合并两个或多个查询结果集，并去除重复的行。在执行union操作时，数据库会进行额外的去重操作，这可能会带来一定的性能开销。
  2. union all同样用于合并查询结果集，但不去除重复的行。相比于union，union all不进行去重操作，因此执行效率更高。

char和varchar的区别？

  1. 存储方式：char是固定长度的字符类型，而varchar是可变长度的字符类型。
  2. 占用空间：char以固定的长度存储数据，不论实际存储的字符数目，而varchar则根据实际需要的空间动态分配存储。
  3. 尾随空格：char在存储时会用空格填充到指定长度，并在检索时需要删除尾随空格，而varchar没有这个问题。
  4. 长度限制：char的长度范围为1到255个字符，而varchar的长度范围也是255个字符但可以根据需求设定更长的长度。
  5. 访问效率：由于char是固定长度的，它在某些情况下可能会比varchar稍微快一些。

MySQL记录货币用什么字段类型？

一般推荐使用decimal字段类型。
decimal字段类型用于存储精确的定点数值，可以指定总共的位数和小数点后的位数。这使得它非常适合用于存储货币金额，因为货币金额通常需要精确到小数点后几位。

使用decimal的好处：

  1. 精确性：可以确保货币金额的精确性，避免由于浮点数运算带来的精度问题。
  2. 可控性：通过指定总位数和小数位数，可以精确控制存储的金额范围和精度。
  3. 计算准确性：decimal字段类型支持数值计算，如加法，减法和乘法等，保证计算结果的准确性。

count(1),count(*)与count(列名）的区别？

都是用于计算行数的聚合函数，但他们在实际应用时有一些区别。
count(1)
这种写法中，1表示一个常量值，它会被用于计算查询结果集的行数。由于1是一个常量，在执行count(1）时，数据库不会去访问或读取任何实际的数据，仅仅是对满足条件的行数进行计数，因此执行速度相对较快。
count(*)
这种写法中，表示选取所有列，他会对满足条件的行进行计数。与count(1)不同的是，执行count()时，数据库需要读取每一行的数据，然后进行计数操作，因此它可能会比count(1)稍微慢一些。不过，在大多数数据库中，优化器会对count（）进行特殊处理，可以通过索引等方式进行优化，使得性能相对较好。
count(列名)
这种写法中，列名表示具体的表列，它会对非空（null）值的行进行计数。相比于count（1）和count（）,count（列名）会跳过值为null的行，只统计非空值的行数。这在某些特定的情况下可能更符合实际需求，例如统计某个列的非空值的个数。

为什么需要数据库连接池呢？

数据库连接池是一种管理和维护数据库连接的技术。它在应用程序和数据库之间建立了一个连接的缓冲池，用于存储和复用已经建立好的数据库连接。
好处：

  1. 提高性能：数据库连接的建立和断开是比较耗时的操作，频繁的创建和销毁会增加系统的负担。通过使用连接池可以避免频繁的创建和关闭连接，减少了连接的开销，提高了系统的性能。
  2. 资源管理：数据库连接是有限的资源，如果每个请求都创建一个新的连接，可能导致连接过多而耗尽资源。连接池通过对连接的管理和复用，能够更有效地管理数据库连接，避免资源的浪费。
  3. 并发处理：在高并发的场景下，如果每个请求都去单独的连接数据库，可能会导致数据库连接数量过多，从而限制了系统的扩展性。连接池允许多个请求共享连接，减少了数据库连接的数量，提高了并发处理能力。
  4. 连接可靠性：数据库连接可能会因为网络问题或服务器故障而中断，当发生这种情况时，连接池能够检测到连接的失效，并重新创建一个可用的连接，确保应用程序的可靠运行。

什么是最左前缀原则？

最左前缀原则（leftmost Prefix Rule)是索引在数据库查询中的一种使用规则。
它指的是在使用复合索引时，索引的最左边的连续几个列会被用于过滤条件的匹配。 具体来说，比如在表中创建了一个复合索引，包含多个列 A，B，C，那么最左前缀原则则要求查询中的过滤条件 必须从索引的最左边开始，并且不能跳过中间的列。只有当查询中的过滤条件与索引的最左前缀完全匹配时，索引才会被充分利用。

例如：考虑以下复合索引（A，B，C）和查询语句：

select * from my_table where A = 'value1' and C = 'value2';

在这种情况下，最左前缀原则要求查询条件中必须包含列A，而不能直接使用列C进行过滤。因为只有满足最左前缀条件，索引（A，B，C,）才能被有效的利用。

遵循最左前缀原则的好处包括：

  1. 提高查询性能：通过使用索引的最左前缀，可以最大限度的减少索引扫描的数据量，提高查询的效率和响应时间。
  2. 减少索引占用空间：在某些情况下，使用最左前缀原则可以减少创建多个索引的需求，节省磁盘空间和索引维护的开销。

MySQL多表查询时有哪些连接方式？

  1. 内连接 inner join
  2. 左外连接 left outer join
  3. 右外连接 right outer join
  4. 全外连接 union
  5. 自连接
  6. 交叉连接

MySQL中in和exists的区别？

使用的区别 ：子查询表小的用in，子查询表大的用exists。根据小表驱动大表的思想，用in内表是驱动表，用exists外表是驱动表。
索引上的区别 ：exists只有内表可以用上索引，外层循环必须要走一个遍历过程；而in内表和外表都可以用上索引，因为in本质上属于多个条件查询的并集(or)。

select * from t1 where exists (select 1 from t2 where t1.id = t2.id);
select * from t1 where id in (select id from t2);

● exists子句会对外表（即t1）用loop逐条记录查询，每次查询都会查看exists中的select语句，如果select子句返回记录行（无论返回记录行是多少，只要能返回），exists就会返回true，则外表中的当前记录就会被检索出来；如果select子句没有返回记录行，exists就会返回false，则外表中的当前记录就会被丢弃。——exist子句循环每次取出外表中的一条记录用来执行exists中的语句查内表，是先查外表，再查内表（相关子查询）。

● in查询相当于多个or条件的叠加。in子句需要先将子查询的记录全部查出来。注意in子句中的子查询返回的结果集必须只有一个字段。假设子查询返回的结果集有m条记录，在进行m次查询。——in子句是先执行in中的子句查出来内表的结果，然后外表针对内表查出来的结果一个个遍历匹配。即先查内表，再查外表（不相关子查询）。 基于以上的认识：

exists只有内表可以用上索引，外层循环必须要走一个遍历过程；而in内表和外表都可以用上索引，因为in本质上属于多个条件查询的并集(or)。

如何选用exists和in？
当两个表的大小相当时，用exists和in的效率差别不大。
如果两个表一个大一个小，则子查询表(即内表)大的用exists，子查询表(即内表)小的用in。 其实就是”小表驱动大表“的思想：用exist时外表是驱动表，用in时内表是驱动表。 MySQL的外连接就利用了”小表驱动大表“的思想做自动优化，因此有时候会发现LEFT JOIN左侧的不是驱动表而是被驱动表，其实就是MySQL优化器的功能。同理，内连接也有类似的情况。

MyISAM索引与InnoDB索引的区别？

在MySQL数据库中，InnoDB和MyISAM是两种常用的存储引擎，它们各有特点和适用场景。了解它们之间的区别对于数据库设计和性能优化至关重要。

  1. 存储方式：MyISAM使用非聚簇索引，索引文件和数据文件是分开的；而InnoDB使用也是用了聚簇索引，将索引和数据一起存储在同一个文件中。
  2. 锁机制：MyISAM采用表级锁定，意味着当对表进行写操作时，整个表都会被锁定，因此可能导致并发写操作的性能较差。而InnoDB采用行级锁定，只锁定需要修改的行，可以提供更好的并发性能和多用户写入的支持。
  3. 事务支持：MyISAM不支持事务处理，而InnoDB支持事务和ACID特性（原子性、一致性、隔离性和持久性），可以进行事务管理、回滚和恢复操作。
  4. 外键方面：MyISAM不支持外键约束，而InnoDB支持外键约束，可以设置关联关系来保证数据的完整性。
  5. 性能特点：MyISAM在读取频繁、插入和更新较少的场景下性能较好，特别适合于读密集型应用；而InnoDB在并发写入和更新较多的情况下性能较好，适合于写入密集型应用或需要事务支持的场景。

MySQL索引分类？

在MySQL中，索引按照索引列的类型可以分为以下几种：

  1. 主键索引：用于唯一标识每一条记录，主键索引的值不允许重复且不能为空，并且一个表只能有一个主键索引。
    ○ 主键索引是特别的唯一索引。
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
  PRIMARY KEY (column_name) # 主键索引
);
  1. 唯一索引：用于保证索引列的值唯一，允许为空值，但是一个表可以有多个唯一索引。
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
  UNIQUE (column_name)
);
  1. 普通索引：没有唯一性限制，允许重复值和空值，是最基本的索引类型。
CREATE TABLE table_name (
  column1 datatype,
  INDEX index_name (column1)
);
  1. 组合索引：在多个字段上创建的索引，可以包含多个列。组合索引可以提高多列查询的性能，但查询条件必须符合最左前缀原则，即查询从左到右使用组合索引中的列。
CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  INDEX index_name (column1, column2)
);

以上就是MySQL常见的四种索引，这些不同类型的索引在数据库中起到了加速数据检索操作的作用，可以根据具体的需求和使用场景选择适当的索引类型。同时，需要注意索引的创建对写操作（如插入、更新、删除）可能会产生额外的开销，因此需要权衡索引的使用与数据操作的平衡。

MySQL为什么默认引擎是InnoDB?

因为InnoDB在性能，事务支持，和容错能力等方面具有较好的特性，适合大多数应用场景。（就是InnoDB的优点）

  1. 支持事务：保证数据库操作的原子性，一致性和完整性
  2. 并发控制：支持行级锁定，在高并发的环境下可以最大程度的减少锁冲突，提高并发性能，而另一个存储引擎MyISAM只支持表级锁定，并发性能较低。
  3. 外键约束：支持外键约束，可以保证数据的完整性。
  4. 崩溃恢复：InnoDB具有自动崩溃恢复的能力。即使在发生意外故障或系统崩溃时，InnoDB引擎也能够自动进行崩溃恢复，保障数据的一致性。
  5. 支持热备份：支持在线热备份，可以在不停止数据库服务的情况下进行备份操作。

数据库的三大范式是什么？

第一范式（1nf），保证数据的一致性，原子性。要求数据库表中的每个列都是不可再分的原子值，即每个列都不能包含多个值或值的列表。
第二范式（2nf）, 在保证第一范式的情况下，要求每个非主键列完全依赖主键列。
第三范式（3nf）, 在第二范式的基础上要求，每个非主键列不能相互依赖。

如何解决MySQL死锁问题？

MySQL在并发环境下可能会出现死锁问题。
死锁是指两个或多个事务互相等待对方释放资源，导致无法继续执行的情况。

解决死锁问题的方法通常有以下几种：

  1. 调整事务隔离级别：通过将事务隔离级别降低为读未提交（或读已提交，可以减少死锁的发生概率。但是要注意隔离级别的降低可能引发脏读，不可重复读，幻读等数据一致性问题，在选择时要权衡利弊）
  2. 优化查询和事务逻辑：分析造成死锁的原因，优化查询和事务逻辑，尽量缩短事务持有锁的时间，减少死锁的发生可能性。比如按照相同的顺序获取锁，避免跨事务的循环依赖。等。
  3. 使用行级锁：行级锁可以更好的限制锁的范围，从而减少死锁的可能性。将锁的粒度调整为行级别，可以减少事务之间的冲突。
  4. 设置合理的超时时间和重试机制：当发生死锁时，可以设置适当的超时时间，在一定时间内尝试解锁操作。如果超时设定的时间仍未成功，则进行死锁处理，如终止较早请求的事务或进行回滚等。

了解Mysql锁升级吗？

MySQL锁升级是指在并发事务执行过程中，当某个事务无法满足当前所需的锁级别时，系统会自动将该事务的锁升级为更高级别的锁。
通常情况下，MySQL会根据事务的需求和操作的对象自动选择合适的锁级别。然而，在某些情况下，事务无法获取所需的锁级别，就会促发锁升级。
例如：

  1. 当某个事务需要获取的是行级锁，但由于并发冲突或其它原因无法满足事务的要求时，系统会将该事务的锁级别升级为表级锁，从而保证数据的一致性和并发性能。
  2. 当某个事务操作的对象不符合行锁加锁的规则，比如没有走索引或非唯一索引记录数达到一定数量，系统也会将该事务的锁级别升级为表级锁，以确保数据的完整性和一致性，所以在实际应用中，合理的索引设计，适当的事务隔离级别设置可以帮助减少锁升级的放生，从而提高并发性能和系统的吞吐量。
Copyright © 2025 梁晨阳