MYSQL 面试题集

MySQL面试题集

面试题集寻求网上的资源，这里总结一下，便于自己巩固理解，希望也能帮到大家！

1、MySQL的逻辑架构？

第⼀层是服务器层，主要提供连接处理、授权认证、安全等功能。
第⼆层实现了 MySQL 核⼼服务功能，包括查询解析、分析、优化、缓存以及⽇期和时间等所有内置函数，所有跨存储引擎的功能都在这⼀层实现，例如存储过程、触发器、视图等。
第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信，这些接⼝屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的 InnoDB外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。

2、MySQL的读写锁？

在处理并发读或写时，可以通过实现⼀个由两种类型组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁和排它锁，也叫读锁和写锁。
读锁是共享的，相互不阻塞，多个客户在同⼀时刻可以同时读取同⼀个资源⽽不相互⼲扰。写锁则是排他的，也就是说⼀个写锁会阻塞其他的写锁和读锁，确保在给定时间内只有⼀个⽤户能执⾏写⼊并防⽌其他⽤户读取正在写⼊的同⼀资源。 在实际的数据库系统中，每时每刻都在发⽣锁定，当某个⽤户在修改某⼀部分数据时，MySQL 会通过锁定防⽌其他⽤户读取同⼀数据。写锁⽐读锁有更⾼的优先级，⼀个写锁请求可能会被插⼊到读锁队列的前⾯，但是读锁不能插⼊到写锁前⾯。(读我们都可以读，但是写只能一个人写，写的同时不能被读)

3、MySQL的锁策略有什么？

表锁是MySQL中最基本的锁策略，并且是开销最⼩的策略。表锁会锁定整张表，⼀个⽤户在对表进⾏写操作前需要先获得写锁，这会阻塞其他⽤户对该表的所有读写操作。只有没有写锁时，其他读取的⽤户才能获取读锁，读锁之间不相互阻塞。
⾏锁可以最⼤程度地⽀持并发，同时也带来了最⼤开销。 InnoDB 和 XtraDB 以及⼀些其他存储引擎实现了⾏锁。⾏锁只在存储引擎层实现，⽽服务器层没有实现。

4、数据库死锁如何解决？

死锁是指多个事务在同⼀资源上相互占⽤并请求锁定对⽅占⽤的资源⽽导致恶性循环的现象。 当多个事务试图以不同顺序锁定资源时就可能会产⽣死锁，多个事务同时锁定同⼀个资源时也会产⽣死锁。
为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，例如InnoDB 存储引擎，越能检测到死锁的循环依赖，并⽴即返回⼀个错误。这种解决⽅式很有效，否则死锁会导致出现⾮常慢的查询。还有⼀种解决⽅法，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种⽅式通常来说不太好。InnoDB ⽬前处理死锁的⽅法是将持有最少⾏级排它锁的事务进⾏回滚。
死锁发⽣之后，只有部分或者完全回滚其中⼀个事务，才能打破死锁。对于事务型系统这是⽆法避免的，所以应⽤程序在设计时必须考虑如何处理死锁。⼤多数情况下只需要重新执⾏因死锁回滚的事务即可。

5、事务是什么？

事务是⼀组原⼦性的 SQL 查询，或者说⼀个独⽴的⼯作单元。 如果数据库引擎能够成功地对数据库应⽤该组查询的全部语句，那么就执⾏该组查询。如果其中有任何⼀条语句因为崩溃或其他原因⽆法执⾏，那么所有的语句都不会执⾏。也就是说事务内的语句要么全部执⾏成功，要么全部执⾏失败。

6、事务有什么特性？

原⼦性 atomicity
⼀个事务在逻辑上是必须不可分割的最⼩⼯作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于⼀个事务来说不可能只执⾏其中的⼀部分。
⼀致性 consistency
数据库总是从⼀个⼀致性的状态转换到另⼀个⼀致性的状态。
隔离性 isolation
针对并发事务⽽⾔，隔离性就是要隔离并发运⾏的多个事务之间的相互影响，⼀般来说⼀个事务所做的修改在最终提交以前，对其他事务是不可⻅的。
持久性 durability
⼀旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

7、MySQL的隔离级别有哪些？

未提交读 READ UNCOMMITTED
在该级别事务中的修改即使没有被提交，对其他事务也是可⻅的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还会导致不可重复读和幻读，性能没有⽐其他级别好很多，很少使⽤。
提交读 READ COMMITTED
多数数据库系统默认的隔离级别。提交读满⾜了隔离性的简单定义：⼀个事务开始时只能"看⻅"已经提交的事务所做的修改。换句话说，⼀个事务从开始直到提交之前的任何修改对其他事务都是不可⻅的。也叫不可重复读，因为两次执⾏同样的查询可能会得到不同结果。
可重复读 REPEATABLE READ（MySQL默认的隔离级别）
可重复读解决了不可重复读的问题，保证了在同⼀个事务中多次读取同样的记录结果⼀致。但还是⽆法解决幻读，所谓幻读指的是当某个事务在读取某个范围内的记录时，会产⽣幻⾏。InnoDB 存储引擎通过多版本并发控制MVCC 解决幻读的问题。
可串⾏化 SERIALIZABLE
最⾼的隔离级别，通过强制事务串⾏执⾏，避免幻读。可串⾏化会在读取的每⼀⾏数据上都加锁，可能导致⼤量的超时和锁争⽤的问题。实际应⽤中很少⽤到这个隔离级别，只有⾮常需要确保数据⼀致性且可以接受没有并发的情况下才考虑该级别。

8、MVCC是什么？

MVCC 是多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行。
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存了行的过期时间。不过存储的不是实际的时间值而是系统版本号，每开始一个新的事务系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
MVCC只能在READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE 则会对所有读取的行都加锁。

9、Memory是？

如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表示非常有用的。Memory表至少要比MyISAM表快一个数量级，因为所有数据都保存在内存，不需要磁盘IO，Memory表的结构在重启后会保留，但数据会丢失。
Memory表适合的场景：查找或者映射表、缓存周期性聚合数据的结果、保存数据分析中产生的中间数据。

10、查询执行的流程是什么？

简单来说分为五步：1、客户端发送一条查询给服务器。2、服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一个阶段。3、服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。4、MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。5、将结果返回给客户端。

11、VARCHAR 和 CHAR 的区别?

VARCHAR 用于存储可变字符，是最常见的字符串数据类型。它比CHAR更节省空间，因为它仅使用必要的空间。VARCHAR需要1或2个额外字节记录字符串长度，如果列的最大长度不大于255字节则只需要1个字节。VARCHAR不会删除末尾空格。
VARCHAR使用场景：字符串列的最大长度比平均长度大很多、列的更新很少、使用了UTF8这种复杂字符集，每个字符都使用不同的字节数存储。
CHAR 是定长的，根据定义的字符串长度分配足够的空间，CHAR会删除末尾空格。
CHAR适合存储很短的字符串，或所有值都接近同一个长度，例如存储密码的MD5值。对于经常变更的数据，CHAR比VARCHAR更好，因为定长的CHAR不容易产生碎片。对于非常短的列，CHAR在存储空间上也更有效率，例如用CHAR来存储只有Y和N的值只需要一个字节，但是VARCHAR需要两个字节，因为还有一个记录长度的额外字节。

12、DATATIME 和TIMESTAMP的区别？

DATATIME 能保存大范围的值，从1001-9999年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用8字节存储空间。
TIMESTAMP 和UNIX时间戳相同，只是用4字节的存储空间，范围比DATATIME小得多，只能表示1970-2038 年，并且依赖于时区。

13、索引有什么作用？

索引也叫键，是存储引擎⽤于快速找到记录的⼀种数据结构。 索引对于良好的性能很关键，尤其是当表 中数据量越来越⼤时，索引对性能的影响愈发重要。在数据量较⼩且负载较低时，不恰当的索引对性能 的影响可能还不明显，但数据量逐渐增⼤时，性能会急剧下降。
索引⼤⼤减少了服务器需要扫描的数据量、可以帮助服务器避免排序和临时表、可以将随机 IO 变成顺 序 IO。但索引并不总是最好的⼯具，对于⾮常⼩的表，⼤部分情况下会采⽤全表扫描。对于中到⼤型的 表，索引就⾮常有效。但对于特⼤型的表，建⽴和使⽤索引的代价也随之增⻓，这种情况下应该使⽤分 区技术。
在MySQL中，⾸先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据⾏。索引可以包括 ⼀个或多个列的值，如果索引包含多个列，那么列的顺序也⼗分重要，因为 MySQL 只能使⽤索引的最 左前缀。

14、SHOW PROFILE 的作用？

通过 SHOW PROFILE 可以分析SQL语句性能消耗，例如查询到SQL会执行多少时间，并显示CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY N 分别查询 id 为N 的SQL语句的CPU、内存及IO的消耗情况。

15、有哪些优化SQL的策略？

1、优化 COUNT 查询
COUNT 是⼀个特殊的函数，它可以统计某个列值的数量，在统计列值时要求列值是⾮空的，不会统计 NULL 值。如果在 COUNT 中指定了列或列的表达式，则统计的就是这个表达式有值的结果数，⽽不是 NULL。
COUNT 的另⼀个作⽤是统计结果集的⾏数，当 MySQL 确定括号内的表达式不可能为 NULL 时，实际 上就是在统计⾏数。当使⽤ COUNT() 时， 不会扩展成所有列，它会忽略所有的列⽽直接统计所有的 ⾏数。某些业务场景并不要求完全精确的 COUNT 值，此时可以使⽤近似值来代替，EXPLAIN 出来的优化器估 算的⾏数就是⼀个不错的近似值，因为执⾏ EXPLAIN 并不需要真正地执⾏查询。
通常来说 COUNT 都需要扫描⼤量的⾏才能获取精确的结果，因此很难优化。在 MySQL 层还能做的就 只有覆盖扫描了，如果还不够就需要修改应⽤的架构，可以增加汇总表或者外部缓存系统。
2、优化关联查询
确保 ON 或 USING ⼦句中的列上有索引，在创建索引时就要考虑到关联的顺序。
确保任何 GROUP BY 和 ORDER BY 的表达式只涉及到⼀个表中的列，这样 MySQL 才有可能使⽤索引 来优化这个过程。
在 MySQL 5.5 及以下版本尽量避免⼦查询，可以⽤关联查询代替，因为执⾏器会先执⾏外部的 SQL 再 执⾏内部的 SQL。
3、优化 GROUP BY
如果没有通过 ORDER BY ⼦句显式指定要排序的列，当查询使⽤ GROUP BY 时，结果***⾃动按照分组 的字段进⾏排序，如果不关⼼结果集的顺序，可以使⽤ ORDER BY NULL 禁⽌排序。
4、优化 LIMIT 分⻚
在偏移量⾮常⼤的时候，需要查询很多条数据再舍弃，这样的代价⾮常⾼。要优化这种查询，要么是在 ⻚⾯中限制分⻚的数量，要么是优化⼤偏移量的性能。最简单的办法是尽可能地使⽤覆盖索引扫描，⽽ 不是查询所有的列，然后根据需要做⼀次关联操作再返回所需的列。
还有⼀种⽅法是从上⼀次取数据的位置开始扫描，这样就可以避免使⽤ OFFSET。其他优化⽅法还包括 使⽤预先计算的汇总表，或者关联到⼀个冗余表，冗余表只包含主键列和需要做排序的数据列。
5、优化 UNION 查询
MySQL 通过创建并填充临时表的⽅式来执⾏ UNION 查询，除⾮确实需要服务器消除重复的⾏，否则⼀ 定要使⽤ UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整 个临时表的数据做唯⼀性检查，这样做的代价⾮常⾼。
6、使⽤⽤户⾃定义变量
在查询中混合使⽤过程化和关系化逻辑的时候，⾃定义变量可能会⾮常有⽤。⽤户⾃定义变量是⼀个⽤ 来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使⽤表达式的地⽅使⽤ ⾃定义变量。例如可以使⽤变量来避免重复查询刚刚更新过的数据、统计更新和插⼊的数量等。
7、优化 INSERT
需要对⼀张表插⼊很多⾏数据时，应该尽量使⽤⼀次性插⼊多个值的 INSERT 语句，这种⽅式将缩减客 户端与数据库之间的连接、关闭等消耗，效率⽐多条插⼊单个值的 INSERT 语句⾼。也可以关闭事务的 ⾃动提交，在插⼊完数据后提交。当插⼊的数据是按主键的顺序插⼊时，效率更⾼