【MySQL 面试题】

MySQL 的逻辑架构

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

查询执行流程是什么？

① 客户端发送一条查询给服务器。
② 服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一阶段。
③ 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
④ MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
⑤ 将结果返回给客户端。

MySQL 的读写锁

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

MySQL 的锁策略有什么

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

锁的优化策略

读写分离 , (常见方案：应用程序根据业务逻辑来判断，增删改等写操作命令发给主库，查询命令发给备库)
分段加锁 ,
减少锁持有的时间 ,
多个线程尽量以相同的顺序去获取资源,不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

数据库死锁如何解决

• 死锁是指多个事务在同一资源上相互占用并请求锁定对方占用的资源而导致恶性循环的现象。
  当多个事务试图以不同顺序锁定资源时就可能会产生死锁，多个事务同时锁定同一个资源时也会产生死锁。
  为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。例如InnoDB 存储引擎，能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方法，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。
  InnoDB 目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。
  死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁。
• 对于事务型系统这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁:
    降低事务的隔离级别, 减少事务并发度, 优化SQL语句和索引, 使用数据库的死锁检测和超时机制

MVCC 是什么

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

谈一谈 InnoDB

①InnoDB 是 MySQL 的默认事务型引擎，用来处理大量短期事务。InnoDB 的性能和自动崩溃恢复特性使得它在非事务型存储需求中也很流行，除非有特别原因否则应该优先考虑 InnoDB。
②InnoDB 引擎中，其数据文件本身就是索引文件。
③InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ，并通过间隙锁策略防止幻读。

Innodb的日志的

  redo log让InnoDB存储引擎拥有了崩溃恢复能力。 binlog保证了MySQL集群架构的数据一致性

• redo log 是物理日志，记录内容是“在某个数据⻚上做了什么修改”。
  是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。 比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写
• bin log, 是逻辑日志，记录内容是语句的原始逻辑。binlog 日志有三种格式可以指定：statement，row，mixed
• InnoDB使用两阶段提交，为了解决两份日志之间的一致问题。
  将redo log的写入拆成了两个步骤prepare和commit。使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据 redo log日志恢复数据时，发现redo log还处于prepare阶段，就会回滚该事务

数据类型有哪些优化策略？

①简单数据类型的操作通常需要更少的 CPU 周期。
    例如整数比字符操作代价更低，因为字符集和校对规则使字符相比整形更复杂。
    应该使用 MySQL 的内建类型 date、time 和 datetime 而不是字符串来存储日期和时间，
    另一点是应该使用整形存储 IP 地址。
②尽量避免 NULL,除非需要存储 NULL值
    可为 NULL 的列被索引时，每个索引记录需要一个额外字节，在MyISAM 中还可能导致固定大小的索引变成可变大小的索引。因此包含可为 NULL 的列对 MySQL 来说更难优化，可为 NULL 的列使索引、索引统计和值比较都更复杂，并且会使用更多存储空间

索引是什么

①索引也叫键，是存储引擎用于快速找到记录的一种数据结构。
②索引大大减少了服务器需要扫描的数据量、可以帮助服务器避免排序和临时表、可以将随机 IO 变成顺序 IO。

索引类型总结,

• 按照数据结构维度划分:
  B+Tree :MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。
  哈希索引:类似键值对的形式，一次即可定位。
  全文索引:对文本的内容进行分词，进行搜索。通常使用 ElasticSearch 代替。
• 按照底层存储方式⻆度划分:
  聚簇索引:索引和数据一起放，InnoDB 中的主键索引就属于聚簇索引。 （一步到位）
  非聚簇索引:索引和数据分开放，辅助索引就属于非聚簇索引。MyISAM 不管主键还是非主键，使用的都是非聚簇索引。
• 按照应用维度划分:
  主键索引:加速查询 + 列值唯一(不可以有 NULL)+ 表中只有一个。
  普通索引:仅加速查询。
  唯一索引:加速查询 + 列值唯一(可以有 NULL)。
  覆盖索引:一个索引包含(或者说覆盖)所有需要查询的字段的值。
  联合索引:多列值组成一个索引，专⻔用于组合搜索。

什么是聚簇索引？

聚簇索引不是一种索引类型，而是索引的一种数据存储方式。索引和数据放一起，InnoDB 中的主键索引就属于聚簇索引。

MySQL 的 B+Tree 索引

MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但两者的实现方式不一样。

索引使用原则

建立索引：
  对查询频次较高且数据量比较大的表建立索引。
索引字段的选择：
  最佳候选列应当从 WHERE 子句的条件中提取，如果 WHERE 子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
索引顺序:
  当不需要考虑排序和分组时，将选择性最高的列放在前面。(索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高，唯一索引的选择性是 1，因此也可以使用唯一索引提升查询效率。)
删除无用索引:
  删除重复索引，MySQL 允许在相同列上创建多个索引，重复的索引需要单独维护，并且优化器在优化查询时也需要逐个考虑，这会影响性能。
  删除冗余索引，如果创建了索引 (A,B) 再创建索引 (A) 就是冗余索引
  服务器永远不用的索引。

索引失效的情况有哪些？

①创建了组合索引，但查询条件未遵守最左匹配原则;
②在索引列上进行计算、函数、类型转换等操作;
③ 以 % 开头的 LIKE 查询比如 like ‘%abc’;
④查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到

MySQL 主从复制的作用？

 复制解决的问题：数据分布、负载均衡、备份、高可用性和故障切换、MySQL 升级测试。
 复制解决的基本问题是让一台服务器的数据与其他服务器保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
 MySQL 支持两种复制方式：基于行的复制 和 基于语句的复制(/逻辑复制) ,这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。因此同一时刻备库的数据可能与主库存在不一致，并且无法包装主备之间的延迟。
 MySQL 复制大部分是向后兼容的，新版本的服务器可以作为老版本服务器的备库，但是老版本不能作为新版本服务器的备库，因为它可能无法解析新版本所用的新特性或语法，另外所使用的二进制文件格式也可能不同。

MySQL 主从复制的步骤？

① 在主库上把数据更改记录到二进制日志中。
② 备库将主库的日志复制到自己的中继日志中。
③ 备库读取中继日志中的事件，将其重放到备库数据之上。

说说分库与分表的设计

定位低效 SQL？

①一种是通过慢查询日志定位，可以通过慢查询日志定位那些已经执行完毕的 SQL 语句。
②一种是使用 SHOW PROCESSLIST 查询，慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志不能定位问题，此时可以使用 SHOW PROCESSLIST 命令查看当前 MySQL 正在进行的线程，包括线程的状态、是否锁表等，可以实时查看 SQL 的执行情况。
③SHOW PROFILE 可以分析 SQL 语句性能消耗，例如查询到 SQL 会执行多少时间，并显示 CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY N 分别查询 id 为 N 的 SQL 语句的 CPU、内存以及 IO 的消耗情况。
④从 MySQL5.6 开始，可以通过 trace 文件进一步获取优化器是是如何选择执行计划的，在使用时需要先打开设置，然后执行一次 SQL，最后查看 information_schema.optimizer_trace 表而都内容，该表为联合i表，只能在当前会话进行查询，每次查询后返回的都是最近一次执行的 SQL 语句。
⑤ 可以通过 EXPLAIN 命令查看 SQL 语句的执行计划

有哪些优化 SQL 的策略？

  尽量避免子查询，可以用关联查询代替；
 最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
  GROUP BY 和 ORDER BY 的表达式只涉及到一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程;
  MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要服务器消除重复的行，否则一定要使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。
  需要对一张表插入很多行数据时，应该尽量使用一次性插入多个值的 INSERT 语句，这种方式将缩减客户端与数据库之间的连接、关闭等消耗，效率比多条插入单个值的 INSERT 语句高。也可以关闭事务的自动提交，在插入完数据后提交。当插入的数据是按主键的顺序插入时，效率更高。

主键使用自增ID还是UUID，为什么？

如果是单机的话，选择自增ID；如果是分布式系统，优先考虑UUID吧，但还是最好自己公司有一套分布式唯一ID生产方案吧。
1、 自增ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长的值范围，多库合并，也有可能有问题。
2、 uuid：适合大量数据的插入和更新操作，但是它无序的，插入数据效率慢，占用空间大。

VARCHAR 和 CHAR 的区别？

VARCHAR 用于存储可变字符串,不会删除末尾空格。
CHAR 是定长的，根据定义的字符串长度分配足够的空间, 会删除末尾空格。

DATETIME 和 TIMESTAMP 的区别？

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

UNION与UNION ALL的区别？

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；