MySQL面试题总结_面试如果 where子句中的组合比较多,应当挑选最常用、过滤效果最好的列的组合-CSDN博客

本文链接：https://blog.csdn.net/qq_41112238/article/details/108899857

MySQL

基本概念

数据库范式是什么？

范式是数据库设计规范，范式越高则数据库冗余越小，但查询也更复杂，一般只需满足第三范式。

第一范式，每列都是不可再分的数据单元。

第二范式，在第一范式的基础上消除部分依赖，非主键列完全依赖于主键列。

第三范式，在第二范式的基础上消除传递依赖，非主键列只依赖于主键列。

视图是什么？

视图是一个虚拟表，是存储在数据库中的查询 SQL 语句，视图只是一个逻辑，具体结果在引用视图时动态生成。

优点：① 具有安全性，可以进行权限控制，创建只读视图并公开给特定用户。② 可以简化复杂的查询，只保存其逻辑。

游标是什么？

游标是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

存储引擎

InnoDB 有什么特点？

① MySQL5.1 开始的默认引擎，最大的优点是支持事务和外键，InnoDB 的性能和自动崩溃恢复特性使它在非事务型需求中也很流行，一般应该优先考虑使用 InnoDB。

② 底层存储结构是 B+ 树，每个节点都对应 InnoDB 的一个页。非叶子节点只有 key 值，叶子节点包含完整的数据。

③ 支持行锁，采用 MVCC 支持高并发，实现了四个标准的隔离级别，默认级别是可重复读，通过间隙锁防止幻读。

④ 基于聚簇索引，对主键查询有很高的性能。

⑤ 内部做了很多优化，例如加速读操作的自适应哈希索引、加速插入操作的缓冲区等。

MyISAM 有什么特点？

① MySQL5.1及之前的默认引擎，提供的特性包括全文索引、空间索引等，不支持事务、行锁和外键。

② 最大的缺陷是崩溃后无法恢复，在插入和更新数据时需要锁定整张表，效率低。

③ 对于只读的数据或者表比较小、可以忍受修复操作的情况可以使用 MyISAM。

Memory 有什么特点？

① 如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，可以使用 Memory 表。

② 数据保存在内存，不需要磁盘 IO，表的结构在重启后会保留，数据会丢失。

③ 支持哈希索引，查找速度快。

④ 使用表锁，并发性能低。

索引

有哪些索引？

B-Tree：大多数引擎都支持这种索引，但底层使用不同结构，例如 NDB 使用 T-Tree，InnoDB 使用 B+ Tree。所有的值都是顺序存储的，并且每个叶子页到根的距离相同。B-Tree 索引能够加快访问数据的速度，存储引擎不再需要进行全表扫描来获取数据，而是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。叶子节点的指针指向的是被索引的数据，而不是其他节点页。

Hash：哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。

空间索引：MyISAM 的一个特殊索引类型，用作地理数据存储。

全文索引：MyISAM 的一个特殊的 B-Tree 索引，一共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的文档指针。用于通过关键字匹配进行查询。

聚簇索引是什么？

聚簇索引不是一种索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B 树索引和数据行。当表有聚簇索引时，它的行数据实际上存放在索引的叶子页中，由于无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点：可以把相关数据保存在一起；将索引和数据保存在同一个 B 树中，获取数据比非聚簇索引要更快。

缺点：如果数据全部在内存中会失去优势；更新代价高，强制每个被更新的行移动到新位置；插入行或主键更新时，可能导致页分裂，占用更多磁盘空间。

索引建立的规范？

① 控制数量：索引越多代价越高，对于 DML 频繁的表，索引过多会导致很高的维护代价。

② 使用短索引：假如构成索引的字段长度比较短，那么在储块内就可以存储更多的索引，提升访问索引的 IO 效率。

③ 建立索引：对查询频次较高且数据量比较大的表建立索引。如果 WHERE 子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

④ 使用前缀索引：对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引，MySQL 不允许索引这些列的完整长度。

⑤ 合适的索引顺序：当不需要考虑排序和分组时，将选择性最高的列放在前面。索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高。

⑥ 删除重复索引：MySQL 允许在相同列上创建多个索引，重复索引需要单独维护。

索引失效的情况有哪些？

① 隐式类型转换，常见情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。

② 如果条件中 OR 只有部分列使用了索引，索引会失效。

③ 执行 LIKE 操作时，最左匹配会被转换为比较操作，但如果以通配符开头，存储引擎就无法做比较，。索引失效

④ 如果查询中的列不是独立的，则 MySQL 不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

⑤ 对于多个范围条件查询，MySQL 无法使用第一个范围列后面的其他索引列，对于多个等值查询则没有这种限制。

⑥ 如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。

事务和锁

事务是什么？

事务是一组原子性的 SQL 语句，具有ACID四个特性。

原子性：一个事务在逻辑上是必须不可分割的最小单元，整个事务中的所有操作要么全部成功，要么全部失败。

一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性：针对并发事务而言，要隔离并发运行的多个事务之间的影响，数据库提供了多种隔离级别。

持久性：一旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

事务的隔离级别有哪些？

未提交读：事务中的修改即使没有提交，对其他事务也是可见的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还存在不可重复读和幻读，很少使用。

提交读：多数数据库的默认隔离级别，事务只能看见已提交事务的修改。存在不可重复读，两次执行同样的查询可能会得到不同结果。

可重复读：是 MySQL 的默认隔离级别，解决了不可重复读，保证同一个事务中多次读取同样的记录结果一致，InnoDB 通过 MVCC 解决。但无法解决幻读，幻读指当某个事务在读取某个范围内的记录时，会产生幻行。

可串行化：最高隔离级别，通过强制事务串行执行避免幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际很少使用，只有非常需要确保数据一致性时考虑。

乐观锁和悲观锁有什么区别？

乐观锁：每次读数据时都认为别人不会修改，所以不会加锁，但在更新时会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。适用于多读场景，可以提高吞吐量。

悲观锁：每次读数据时都认为别人会修改，所以每次读数据时都会加锁，这样别人想拿数据就会阻塞直到拿到锁。比如 MySQL 的行锁、表锁、读锁、写锁等，都是在做操作前先加锁。

什么是读写锁？

读锁即共享锁（S 锁），写锁即排它锁（X 锁），读锁是共享的，相互不阻塞，多个客户在同一时刻可以读取同一资源。写锁是排它的，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入。

写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但读锁不能插入到写锁前面。

表锁和行锁有什么区别？

表锁会锁定整张表，加锁快，开销小，不会出现死锁，但并发度低、锁冲突概率高。适合以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如 Web 应用。

行锁可以最大程度地支持并发，锁冲突概率低，但开销大，会出现死锁。行锁只在存储引擎层实现，而服务器层没有实现，适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如在线事务处理系统。

死锁是什么，产生的原因？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

锁的行为和顺序和存储引擎相关，以同样的顺序执行语句，有些存储引擎会产生死锁有些则不会。

如何解决和避免死锁？

发生死锁后，InnoDB 一般都能自动检测到并使一个事务释放锁并回滚，另一个事务获得锁继续完成事务。但在涉及外部锁或表锁时，InnoDB 并不能完全自动检测到死锁，需要设置锁等待超时参数 innodb_lock_wait_timeout 解决。

MyISAM：在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，不会出现死锁。

InnoDB：① 如果要更新记录，应该直接申请足够级别的锁，即排它锁。② 多个事务应尽量约定以相同的顺序来访问表。

MVCC 是什么？

多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞读，写也只锁定必要行。

InnoDB 通过在每行记录保存两个隐藏列来实现，这两个列分别保存了行的创建和过期时间，但不是实际时间而是系统版本号，每开始一个新的事务系统版本号会自动递增，事务开始时的系统版本号会作为事务版本号，用来和查询到的每行记录的版本号比较。

MVCC 只能在提交读和可重复读两个级别工作，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行，而可串行化则会对所有读取的行都加锁。

SQL

内连接、左连接和外连接的区别？

左连接：关键字 left join on，以左表为主表，可以查询左表存在而右表为 null 的记录。

右连接：关键字 right join on，以右表为主表，可以查询右表存在而左表为 null 的记录。

内连接：查询左右表同时满足条件的记录，两边都不可为 null。

如何定位低效 SQL？

可以通过慢查询日志定位到已经执行完毕的 SQL 语句。还可以使用 SHOW PROCESSLIST 命令查看正在执行的线程，包括线程状态、是否锁表等，可以实时查看 SQL 的执行情况，同时对一些锁表操作进行优化。

定位到低效语句后，就可以通过 EXPLAIN 或 trace 进一步处理。

EXPLAIN 有哪些字段？

执行计划是 SQL 调优的重要依据，可以通过 EXPLAIN 命令查看 SQL 的执行计划，如果作用在表上，该命令相当于 DESC。

字段	含义
id	SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id 一样时，执行顺序从上往下。
select_type	查询中每个 SELECT 子句的类型，例如 SIMPLE 表示简单查询，PRIMARY 表示复杂查询的最外层查询。
type	访问类型，性能由差到好：ALL、index、range（至少达到）、ref（要求）、const、system、NULL。
possible_keys	查询时可能用到的索引，列出大量可能索引时意味着备选索引太多。
key	查询时实际使用的索引，没有则为 NULL。
key_len	所用索引字段的长度，对于确认索引有效性以及多列索引中用到的列数目很重要。
ref	表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows	估算找到所需记录所需要读取的行数。
Extra	额外信息，例如 Using temporary 表示需要使用临时表存储结果集；Using index 表示只需使用索引就可满足查询要求，说明表正在使用覆盖索引。