mysql数据库高级面试题-2024

my_styles

已于 2024-03-29 10:37:39 修改

阅读量1.2k

点赞数 24

文章标签： mysql 数据库

于 2024-03-27 10:40:54 首次发布

本文链接：https://blog.csdn.net/qq_36961226/article/details/137064606

版权

数据库三大范式是什么

   第一范式：列不可再分
   第二范式：行可以唯一区分，主键约束，要求表中的每个非主键列完全依赖于主键列
   第三范式：表的非主属性不能依赖与其他表的非主属性外键约束
   且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

MySQL 索引分类?

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

主键索引：用于唯一标识每一条记录，主键索引的值不允许重复且不能为空，并且一个表只能有一个主键索引。
唯一索引：用于保证索引列的值唯一，允许为空值，但是一个表可以有多个唯一索引。
普通索引：没有唯一性限制，允许重复值和空值，是最基本的索引类型。
组合索引：在多个字段上创建的索引，可以包含多个列。组合索引可以提高多列查询的性能，但查询条件必须符合最左前缀原则，即查询从左到右使用组合索引中的列。

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

MyISAM索引与InnoDB索引的区别？

MyISAM和InnoDB是MySQL中两种常见的存储引擎，它们在索引实现上存在以下区别：

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

什么是索引下推？

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，它利用了数据库引擎中的索引和过滤条件，将部分过滤工作下推到存储引擎层面进行处理，从而减少不必要的数据读取和传输。

在传统的查询执行过程中，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些行读取到内存中，然后再进行进一步的过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中。

具体实现方式可以是通过存储引擎提供的接口或者钩子函数，让存储引擎在读取索引页时就进行额外的过滤操作。

通过索引下推，数据库系统可以在存储引擎层面根据索引和过滤条件提前过滤掉不符合条件的数据，减少了需要传递给查询引擎的数据量和内存消耗。这样可以大大减少磁盘 I/O 和数据传输的开销，提升查询性能和整体系统效率。

需要注意的是，索引下推并不是对所有类型的查询都适用，它更适用于复杂查询条件、多列条件的查询中，能够有效地减少不必要的数据读取和传输。

并发事务带来哪些问题

并发事务可以带来以下几个问题：

脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据。假设事务A修改了一条数据但未提交，事务B却读取了这个未提交的数据，导致事务B基于不准确的数据做出了错误的决策。
不可重复读（Non-repeatable Read）：一个事务在多次读取同一数据时，得到了不同的结果。假设事务A读取了一条数据，事务B修改或删除了该数据并提交，然后事务A再次读取同一数据，发现与之前的读取结果不一致，造成数据的不一致性。
幻读（Phantom Read）：一个事务在多次查询同一范围的数据时，得到了不同数量的结果。假设事务A根据某个条件查询了一组数据，事务B插入了符合该条件的新数据并提交，然后事务A再次查询同一条件下的数据，发现结果集发生了变化，产生了幻觉般的新增数据。
丢失修改（Lost Update）：两个或多个事务同时修改同一数据，并且最终只有一个事务的修改被保留，其他事务的修改被覆盖或丢失。这种情况可能会导致数据的部分更新丢失，造成数据的不一致性。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

CHAR 和 VARCHAR 的区别？

CHAR和VARCHAR的区别可以总结如下：

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

综上所述，CHAR适合存储长度固定且固定大小的数据，而VARCHAR适用于长度可变的数据。

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

在SQL查询中，COUNT(1)、COUNT(*)和COUNT(列名)是用于计算行数的聚合函数，但它们在实际应用时有一些区别。

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

总体来说，COUNT(1)和COUNT(*)的性能较好且结果一致，而COUNT(列名)则对非空值进行计数。在实际使用时，可以根据具体的查询需求和性能要求选择适当的写法。

UNION 与UNION ALL 的区别？

UNION：UNION用于合并两个或多个查询结果集，并去除重复的行。它将多个查询的结果合并为一个结果集，并自动去除重复的行。在执行UNION操作时，数据库会进行额外的去重操作，这可能会带来一定的性能开销。（去重以及排序）
UNION ALL：UNION ALL同样用于合并查询结果集，但不去除重复的行。它将多个查询的结果简单地合并在一起，包括重复的行。相比于UNION，UNION ALL不进行去重操作，因此执行效率更高。(不去重不排序)

总结来说：在使用时，可以根据具体的需求来选择合适的操作符。如果需要去除重复的行，可以使用UNION；如果不需要去重，或者对性能要求较高，可以使用UNION ALL。需要注意的是，使用UNION或UNION ALL时，要求被合并的查询结果的列数和列类型保持一致。

如何快速定位慢SQL

要查询慢SQL产生的原因，可以采取以下4个步骤：

启用慢查询日志：在MySQL配置中启用慢查询日志，这样可以记录执行时间超过阈值的查询语句。通过分析慢查询日志，可以找到执行时间较长的SQL语句。
使用EXPLAIN分析执行计划：对于慢查询的SQL语句，使用EXPLAIN命令来查看其执行计划。通过分析执行计划，确定查询是否有效利用了索引以及是否存在性能瓶颈。
检查索引使用情况：确保查询中涉及的列都有适当的索引，并且查询条件能够充分利用索引。可以使用SHOW INDEX命令或查询表的索引信息来检查索引情况。
分析查询语句：仔细分析查询语句本身，检查是否存在冗余的操作、重复的子查询、不必要的排序、大量的JOIN操作等。

慢SQL你是怎么优化的

优化查询语句结构：检查是否存在冗余的操作、重复的子查询、不必要的排序、大量的JOIN操作等。优化查询语句的结构和逻辑，减少不必要的数据读取和计算。
添加合适的索引：确保查询中涉及的列都有适当的索引，并且查询条件能够充分利用索引。通过使用适当的索引，提高查询的性能。但是要避免过多的索引，因为过多的索引会增加写入操作的开销。
使用覆盖索引：如果查询只需要使用索引列的数据而不需要访问表的其他列，可以考虑使用覆盖索引。覆盖索引避免了访问表的额外IO操作，提高查询性能。
避免全表扫描：尽量避免全表扫描的情况，通过合适的索引或筛选条件来限制查询范围，减少数据读取量。
合理分页查询：对于大数据量的分页查询，可以通过使用LIMIT分页、使用游标、定期同步缓存等方式来提高性能。

索引失效的情况有哪些

索引失效是指在使用索引进行查询时，索引无法发挥作用，导致查询性能下降。常见的导致索引失效的情况有以下几种：

不满足索引列顺序：如果查询条件中的列顺序与索引列的顺序不一致，索引可能无法被使用。例如，一个联合索引（A, B），如果查询条件只包含了B列而没有A列，那么这个索引就无法被利用。
使用函数或表达式：当查询条件中对索引列应用了函数、数学运算、类型转换等操作时，索引可能无法被使用。因为索引的创建是基于原始列值的，无法直接使用函数计算后的结果进行索引匹配。
字符串比较问题：对于字符串类型的索引列，使用了不符合索引规则的比较方式。
数据类型不匹配：当查询条件的数据类型与索引列的数据类型不匹配时，索引可能无法被使用。尤其是在进行隐式数据类型转换、不同字符集的比较或编码问题时，需要特别留意。
数据量过小：当表中的数据量较小时，MySQL可能会选择全表扫描而非使用索引，因为全表扫描的成本较低。这种情况下，索引可能无法发挥作用。
使用了NOT、<>、OR等非优化的逻辑操作符：这些逻辑操作符在查询条件中的使用会导致索引失效，因为它们无法充分利用索引的特性。

综上所述，我们要解决索引失效的问题，可以通过合理设计索引、优化查询语句以及避免索引失效的情况发生来提升查询性能。

说下你对数据库事务的理解

数据库事务是指一系列数据库操作的逻辑单元，这些操作要么全部成功执行，要么全部回滚。它的目的是确保数据的一致性和完整性。事务具备4大特性，即原子性、一致性、隔离性和持久性：

原子性：事务中的所有操作要么全部执行成功，要么全部回滚到事务开始前的状态。如果在事务执行期间发生错误，系统将回滚所有已执行的操作，以保持数据的一致性。

一致性：事务的执行不会破坏数据库的完整性约束。在事务开始和结束时，数据库必须处于一致的状态。

如小李转账100元给小白，不管操作是否成功，小李和小白的账户总额是不变的。

隔离性：事务的执行是相互隔离的，即每个事务对其他事务是透明的。并发执行的多个事务不会相互干扰，每个事务感知不到其他事务的存在。

持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

以上就是我对数据库事务的理解。

事务的隔离级别有哪些

读未提交：最低的隔离级别。事务可以读取到其他事务尚未提交的数据，可能会出现脏读、不可重复读和幻读问题。
读已提交：事务只能读取到已经提交的数据。但在同一事务中，多次读取同一行的数据结果可能会不一致，可能会出现不可重复读和幻读问题。
可重复读：在同一个事务内，多次读取同一行的数据结果始终保持一致。MySQL的默认隔离级别就是可重复读。通过使用MVCC机制来实现，在读操作期间不会看到其他事务的修改，有效地解决了不可重复读问题。
串行化：最高的隔离级别。事务串行执行，避免了脏读、不可重复读和幻读等问题。但是并发性能较差，可能导致大量的锁竞争和资源争用。

事务隔离级别

MySQL事务隔离级别是指在多个事务同时访问数据库时，数据库如何保证数据的一致性和隔离性。常见的隔离级别如下：

读未提交：最低的隔离级别，允许读取尚未提交的数据变更。
串行化：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。
可重复读：同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。
读已提交：允许读取并发事务已经提交的数据。

隔离级别	并发问题	适用场景	隔离级别从上往下，由低到高。隔离级别越高，事务的并发性能就越低。
读未提交（read-uncommitted）	可能会导致脏读、幻读或不可重复读	并发性要求不高
读已提交（read-committed）	可能会导致幻读或不可重复读	并发性要求较高
可重复读（repeatable-read）	可能会导致幻读	数据一致性要求较高
可串行化（serializable）	不会产生干扰	数据一致性要求非常高

讲讲你对MVCC的理解

MVCC是一种并发控制策略，它在多个事务同时执行时，确保数据库的一致性和隔离性。MVCC通过为每个事务创建数据的不同版本，避免了锁竞争问题。

它的工作原理如下：

每条数据行都有一个隐藏的版本号或时间戳，记录该行的创建或最后修改时间。
当事务开始，它会获取一个唯一的事务ID，作为其开始时间戳。
在读取数据时，事务只能访问在其开始时间戳之前已提交的数据。这个版本的数据在事务开始前就已存在。
当事务更新数据，会创建新版本的数据，将更新后的数据写入新的数据行，并将事务ID与新版本关联。
其他事务可以继续访问旧版本的数据，不受正在进行的更新事务影响。这种机制被称为快照读。
当事务提交，其所有修改才对其他事务可见。此时，新版本的数据成为其他事务读取的数据。

以上就是MVCC的工作原理。它是通过使用多个版本的数据来实现并发控制，提高了数据库的并发性能，并确保了事务之间的隔离性和数据一致性。

Undo log是如何回滚事务的

首先，获取事务的回滚指针或Undo Log的起始位置。

从Undo Log的末尾开始逆向扫描，按照事务操作的逆序依次处理每个日志记录。

然后，针对 INSERT 操作，执行 DELETE 操作来撤销插入的数据。对于 UPDATE 操作，使用Undo Log 中记录的旧值将数据还原到之前的状态。

在回滚过程中，对于已经提交的其他事务所做的修改需要跳过，只处理属于当前回滚事务的 Undo Log 记录。

按照逆序依次处理所有的日志记录，直到达到回滚指针位置或 Undo Log 的起始位置。

回滚完成后，清除或标记已回滚的 Undo Log 记录。

总体而言，事务回滚是通过执行 Undo Log 中记录的反向操作，将事务的修改操作撤销，恢复到事务开始前的状态。

讲讲主从复制原理与延迟

首先，主库将变更写入 binlog 日志。

从库连接到主库后，有一个 IO 线程负责将主库的 binlog 日志复制到自己本地，并写入到中继日志中。

然后，从库中有一个 SQL 线程会从中继日志读取 binlog，并执行其中的 SQL 内容，即在从库上再次执行一遍。

以上就是主从复制的原理。那么主从延迟的原因有哪些呢？

主库的从库太多，主库需要将 binlog 日志传输给多个从库，导致复制延迟。
在从库执行的 SQL 中存在慢查询语句，会导致整体复制进程的延迟。
如果主库的读写压力过大，会导致主库处理 binlog 的速度减慢，进而影响复制延迟。

为了优化主从复制的延迟，我们可以采取以下措施：

减少从库的数量，降低主库的负载，减少复制延迟。
优化慢查询语句，减少从库执行SQL 的延迟。
对主库进行性能优化，减少主库的读写压力，提高 binlog 写入速度。

通过以上措施可以帮助降低主从复制的延迟，提高复制的效率和一致性。

如何优化深分页limit 1000000

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

例如，以下查询可能会非常慢：

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

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

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

使用覆盖索引： 覆盖索引可以让 MySQL 在索引中获取所有需要的数据，而无需回表查询。这可以大大提高查询速度。

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

记住上次的位置： 如果你的应用程序可以记住上次查询的最后一个 ID，那么你可以使用 WHERE 子句来避免扫描大量数据。

SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT 10;

使用分页插件： 有些数据库框架提供了分页插件，可以自动优化分页查询。
避免深分页： 在设计应用程序时，尽量避免深分页。例如，你可以提供搜索功能，让用户快速找到他们需要的数据，而不是浏览大量的页面。

MySQL 有哪些高可用方案

主从复制：这是最常见的高可用方案。主库负责处理写操作，并将数据变更记录到binlog日志。从库将主库的binlog复制到自己的中继日志，然后执行中继日志中的事件，以达到与主库数据一致的目的。当主库出现故障时，可以将从库提升为新的主库，实现服务的高可用。
集群：MySQL集群是一个高可用、高性能的数据库集群解决方案。它使用了共享无关的架构，可以在节点之间自动分割和复制数据，实现了数据的高可用和高性能。

如何解决MySQL死锁问题

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

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

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

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

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

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

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

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

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

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

分享7种SQL的进阶用法

前言

还只会使用SQL进行简单的insert、update、detele吗？本文给大家带来7种SQL的进阶用法，让大家在平常工作中使用SQL简化复杂的代码逻辑。

1.自定义排序（ORDER BY FIELD）

在MySQL中ORDER BY排序除了可以用ASC和DESC之外，还可以使用自定义排序方式来实现。

CREATE TABLE movies (  
  id INT PRIMARY KEY AUTO_INCREMENT,  
  movie_name VARCHAR(255),  
  actors VARCHAR(255),  
  price DECIMAL(10, 2) DEFAULT 50,  
  release_date DATE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO movies (movie_name, actors, price, release_date) VALUES
('咱们结婚吧', '靳东', 43.2, '2013-04-12'),
('四大名捕', '刘亦菲', 62.5, '2013-12-21'),
('猎场', '靳东', 68.5, '2017-11-03'),
('芳华', '范冰冰', 55.0, '2017-09-15'),
('功夫瑜伽', '成龙', 91.8, '2017-01-28'),
('惊天解密', '靳东', 96.9, '2019-08-13'),
('铜雀台', null, 65, '2025-12-16'),
('天下无贼', '刘亦菲', 44.9, '2004-12-16'),
('建国大业', '范冰冰', 70.5, '2009-09-21'),
('赛尔号4：疯狂机器城', '范冰冰', 58.9, '2021-07-30'),
('花木兰', '刘亦菲', 89.0, '2020-09-11'),
('警察故事', '成龙', 68.0, '1985-12-14'),
('神话', '成龙', 86.5, '2005-12-22');

用法如下：

select * from movies order by movie_name asc;

select * from movies ORDER BY FIELD(movie_name,'神话','猎场','芳华','花木兰',
'铜雀台','警察故事','天下无贼','四大名捕','惊天解密','建国大业',
'功夫瑜伽','咱们结婚吧','赛尔号4：疯狂机器城');

2.空值NULL排序（ORDER BY IF(ISNULL）)

在MySQL中使用ORDER BY关键字加上我们需要排序的字段名称就可以完成该字段的排序。如果字段中存在NULL值就会对我们的排序结果造成影响。
这时候我们可以使用 ORDER BY IF(ISNULL(字段), 0, 1) 语法将NULL值转换成0或1，实现NULL值数据排序到数据集前面还是后面。

select * from movies ORDER BY actors, price desc;

select * from movies ORDER BY if(ISNULL(actors),0,1), actors, price;

3.CASE表达式（CASE···WHEN）

在实际开发中我们经常会写很多if ··· else if ··· else，这时候我们可以使用CASE···WHEN表达式解决这个问题。
以学生成绩举例。比如说：学生90分以上评为优秀，分数80-90评为良好，分数60-80评为一般，分数低于60评为“较差”。那么我们可以使用下面这种查询方式：

CREATE TABLE student (
  student_id varchar(10) NOT NULL COMMENT '学号',
  sname varchar(20) DEFAULT NULL COMMENT '姓名',
  sex char(2) DEFAULT NULL COMMENT '性别',
  age int(11) DEFAULT NULL COMMENT '年龄',
  score float DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

INSERT INTO student (student_id, sname, sex, age , score)
VALUES ('001', '张三', '男', 20,  95),
       ('002', '李四', '女', 22,  88),
       ('003', '王五', '男', 21,  90),
       ('004', '赵六', '女', 20,  74),
       ('005', '陈七', '女', 19,  92),
       ('006', '杨八', '男', 23,  78),
       ('007', '周九', '女', 20,  55),
       ('008', '吴十', '男', 22,  91),
       ('009', '刘一', '女', 21,  87),
       ('010', '孙二', '男', 19,  60);

select *,case when score > 90 then '优秀'
			when score > 80 then '良好'
			when score > 60 then '一般'
			else '较差' end level
from student;

4.分组连接函数（GROUP_CONCAT）

分组连接函数可以在分组后指定字段的字符串连接方式，并且还可以指定排序逻辑；连接字符串默认为英文逗号。
比如说根据演员进行分组，并将相应的电影名称按照票价进行降序排列，而且电影名称之间通过“_”拼接。用法如下：

select actors,
GROUP_CONCAT(movie_name),
GROUP_CONCAT(price) from movies GROUP BY actors;

select actors,
GROUP_CONCAT(movie_name order by price desc SEPARATOR '_'),
GROUP_CONCAT(price order by price desc SEPARATOR '_') 
from movies GROUP BY actors;

5.分组统计数据后再进行统计汇总（with rollup）

在MySql中可以使用 with rollup在分组统计数据的基础上再进行数据统计汇总，即将分组后的数据进行汇总。

SELECT actors, SUM(price) FROM movies GROUP BY actors;

SELECT actors, SUM(price) FROM movies GROUP BY actors WITH ROLLUP;

6.子查询提取（with as）

如果一整句查询中多个子查询都需要使用同一个子查询的结果，那么就可以用with as将共用的子查询提取出来并取一个别名。后面查询语句可以直接用，对于大量复杂的SQL语句起到了很好的优化作用。
需求：获取演员刘亦菲票价大于50且小于65的数据。

with m1 as (select * from movies where price > 50),
 m2 as (select * from movies where price >= 65)
select * from m1 where m1.id not in (select m2.id from m2) and m1.actors = '刘亦菲';

7.优雅处理数据插入、更新时主键、唯一键重复

在MySql中插入、更新数据有时会遇到主键重复的场景，通常的做法就是先进行删除在插入达到可重复执行的效果，但是这种方法有时候会错误删除数据。
1.插入数据时我们可以使用IGNORE，它的作用是插入的值遇到主键或者唯一键重复时自动忽略重复的数据，不影响后面数据的插入，即有则忽略，无则插入。示例如下：

select * from movies where id >= 13;

INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(13, '神话', '成龙', 100, '2005-12-22');

INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(13, '神话', '成龙', 100, '2005-12-22');

INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14, '神话2', '成龙', 114, '2005-12-22');

2.还可以使用REPLACE关键字，当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入，即有则删除+插入，无则插入，示例如下：

REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14, '神话2', '成龙', 100, '2005-12-22');

REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(15, '神话3', '成龙', 115, '2005-12-22');

3.更新数据时使用on duplicate key update。它的作用就是当插入的记录遇到主键或者唯一键重复时，会执行后面定义的UPDATE操作。相当于先执行Insert 操作，再根据主键或者唯一键执行update操作，即有就更新，没有就插入。示例如下：

INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(15, '神话3', '成龙', 115, '2005-12-22') on duplicate key update price = price + 10;

INSERT INTO movies (id, movie_name, actors, price, release_date) VALUES
(16, '神话4', '成龙', 75, '2005-12-22') on duplicate key update price = price + 10;

my_styles

关注

24
点赞
踩
29

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库高级面试题-2024

索引下推（Index Condition Pushdown，简称ICP）是一种数据库查询优化技术，它利用了数据库引擎中的索引和过滤条件，将部分过滤工作下推到存储引擎层面进行处理，从而减少不必要的数据读取和传输。在传统的查询执行过程中，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些行读取到内存中，然后再进行进一步的过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中。
复制链接

扫一扫