mysql面试 3年 mysql面试内容

数据库架构

可以说下MySQL 的基础架构吗？

可以按照下图给面试官讲一下 MySQL 数据库的逻辑架构，有条件可以现场画图演示，图片来源于网络。

Mysql逻辑架构图主要分三层：

（1）第一层负责连接处理，授权认证，安全等等

（2）第二层负责编译并优化SQL

（3）第三层是存储引擎。

一条SQL查询语句在MySQL中的执行过程是什么样的？

1. 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限会先查询缓存(MySQL8.0 版本以前)。
2. 如果没有缓存，分析器进行词法分析，提取 sql 语句中 select 等关键元素，然后判断 sql
   语句是否有语法错误，比如关键词是否正确等等。
3. 最后优化器确定执行方案进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引擎接口，返回执行结果。

SQL 优化

说说日常工作中你是怎么优化SQL的？

可以从这几个维度回答这个问题：

1，优化表结构

（1）尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

（2）尽可能的使用 varchar 代替 char

变长字段存储空间小，可以节省存储空间。

（3）当索引列大量重复数据时，可以把索引删除掉

比如有一列是性别，几乎只有男、女、未知，这样的索引是无效的。

2，优化查询

1. 应尽量避免在 where 子句中使用!=或<>操作符
2. 应尽量避免在 where 子句中使用 or 来连接条件
3. 任何查询也不要出现select *
4. 避免在 where 子句中对字段进行 null 值判断

3，索引优化

1. 对作为查询条件和 order by的字段建立索引
2. 避免建立过多的索引，多使用组合索引

怎么看执行计划（explain），如何理解其中各个字段的含义？

在 select 语句之前增加 explain 关键字，会返回执行计划的信息。

（1）id 列：是 select 语句的序号，MySQL将 select 查询分为简单查询和复杂查询。

（2）select_type列：表示对应行是是简单还是复杂的查询。

（3）table 列：表示 explain 的一行正在访问哪个表。

（4）type 列：最重要的列之一。表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

（5）possible_keys 列：显示查询可能使用哪些索引来查找。

（6）key 列：这一列显示 mysql 实际采用哪个索引来优化对该表的访问。

（7）key_len 列：显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

（8）ref 列：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名。

（9）rows 列：这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。

（10）Extra 列：显示额外信息。比如有 Using index、Using where、Using temporary等。

关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

我们平时写Sql时，都要养成用explain分析的习惯。慢查询的统计，运维会定期统计给我们

优化慢查询思路：

1. 分析语句，是否加载了不必要的字段/数据
2. 分析 SQL 执行句话，是否命中索引等
3. 如果 SQL 很复杂，优化 SQL 结构
4. 如果表数据量太大，考虑分表

索引

使用索引查询一定能提高查询的性能吗？为什么？

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

1. 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEXRANGE SCAN)适用于两种情况:
2. 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
3. 基于非唯一性索引的检索

说说聚集索引与非聚集索引有什么区别？

可以按以下四个维度回答：

（1）一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。

（2）聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

（3）索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

（4）聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

何时使用聚簇索引与非聚簇索引？

联合索引是什么?为什么需要注意联合索引中的顺序?

MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.

具体原因为:

MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.

当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推.因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整.

为什么要用 B+ 树，为什么不用普通二叉树？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是普通二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是 B+ 树呢？

（1）为什么不是普通二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

（2）为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

（3）为什么不是 B 树而是 B+ 树呢？

B+ 树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

Hash 索引和 B+ 树索引区别是什么？你在设计索引是怎么抉择的？

1. B+ 树可以进行范围查询，Hash 索引不能。
2. B+ 树支持联合索引的最左侧原则，Hash 索引不支持。
3. B+ 树支持 order by 排序，Hash 索引不支持。
4. Hash 索引在等值查询上比 B+ 树效率更高。
5. B+ 树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

什么是最左前缀原则？什么是最左匹配原则？

最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

当我们创建一个组合索引的时候，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和(a1,a2,a3)三个索引，这就是最左匹配原则。

索引不适合哪些场景?

数据量少的不适合加索引
更新比较频繁的也不适合加索引 = 区分度低的字段不适合加索引（如性别）

索引有哪些优缺点？

(1) 优点：

1. 唯一索引可以保证数据库表中每一行的数据的唯一性
2. 索引可以加快数据查询速度，减少查询时间

(2)缺点：

1. 创建索引和维护索引要耗费时间
2. 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
3. 以表中的数据进行增、删、改的时候，索引也要动态的维护。

锁

MySQL 遇到过死锁问题吗，你是如何解决的？

遇到过。我排查死锁的一般步骤是这样的：

（1）查看死锁日志 show engine innodb status; （2）找出死锁Sql （3）分析sql加锁情况 （4）模拟死锁案发 （5）分析死锁日志 （6）分析死锁结果

说说数据库的乐观锁和悲观锁是什么以及它们的区别？

（1）悲观锁：

悲观锁她专一且缺乏安全感了，她的心只属于当前事务，每时每刻都担心着它心爱的数据可能被别的事务修改，所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。

（2）乐观锁：

乐观锁的“乐观情绪”体现在，它认为数据的变动不会太频繁。因此，它允许多个事务同时对数据进行变动。

实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

什么是死锁？怎么解决？

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

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
如果业务处理不好可以用分布式事务锁或者使用乐观锁

MVCC 熟悉吗，知道它的底层原理？

MVCC (Multiversion Concurrency Control)，即多版本并发控制技术。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

MyISAM和InnoDB存储引擎使用的锁

1. MyISAM采用表级锁(table-level locking)。
2. InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

行级锁，表级锁和页级锁对比

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

事务

事物的四大特性(ACID)介绍一下?

1. 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
2. 一致性：指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
3. 隔离性：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。
4. 持久性：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

1. 读未提交（Read Uncommitted）
2. 读已提交（Read Committed）
3. 可重复读（Repeatable Read）
4. 串行化（Serializable）

READ_UNCOMMITTED: 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读

READ_COMMITTED: 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

REPEATABLE_READ: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE: 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别.

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

Mysql默认的事务隔离级别是可重复读(Repeatable Read)

什么是幻读，脏读，不可重复读呢？

事务A、B交替执行，事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据，这就是脏读。

在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

实战

MySQL数据库cpu飙升到500%的话，要怎么处理呢？

排查过程：

（1）使用top 命令观察，确定是mysqld导致还是其他原因。（2）如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

（1）kill 掉这些线程(同时观察 cpu 使用率是否下降)， （2）进行相应的调整(比如说加索引、改 sql、改内存参数) （3）重新跑这些 SQL。

其他情况：

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

MYSQL的主从延迟，你怎么解决？

主从复制分了五个步骤进行：（图片来源于网络）

1. 步骤一：主库的更新事件(update、insert、delete)被写到binlog
2. 步骤二：从库发起连接，连接到主库。
3. 步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
4. 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5. 步骤五：还会创建一个SQL线程，从relay
   log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

主从同步延迟的原因

一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服务器的里面读取binlog的线程仅有一个，当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决办法

1. 主服务器要负责更新操作，对安全性的要求比从服务器要高，所以有些设置参数可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit
   = 1 之类的设置等。
2. 选择更好的硬件设备作为slave。
3. 把一台从服务器当度作为备份使用， 而不提供查询， 那边他的负载下来了， 执行relay log 里面的SQL效率自然就高了。
4. 增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载。

如果让你做分库与分表的设计，简单说说你会怎么做？

分库分表方案:

1. 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
2. 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
3. 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
4. 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

常用的分库分表中间件：

1. sharding-jdbc
2. Mycat

分库分表可能遇到的问题

1. 事务问题：需要用分布式事务啦
2. 跨节点Join的问题：解决这一问题可以分两次查询实现
3. 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
4. 数据迁移，容量规划，扩容等问题
5. ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID
6. 跨分片的排序分页问题

如果一个表数据量很大，已经影响线上业务了，你该怎么处理？

1. 分库分表
2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
3. 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
4. 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存

为什么要做读写分离？

随着数据量的增大，读写并发的增加，系统可用性要求的提升，单机 MySQL 出现危机：

1. 容量问题，难以扩容，考虑数据库拆分、分库分表
2. 读写压力，QPS 过大，特别是分析类需求会影响到业务事务，考虑多机集群、主从复制
3. 高可用性不足，易宕机，考虑故障转移、MHA/MGR/Orchestrator
4. 高峰时数据库连接数经常超过上限

一致性问题，考虑分布式事务，X/A 柔性事务

读写分离的实现是基于主从复制架构：一主多从，只写主库，主库会自动将数据同步到从库。

高并发场景下MySQL的一种优化方案，依靠主从复制使得MySQL实现了数据复制为多份，增强了抵抗 高并发读请求的能力，提升了MySQL查询性能同时，也提升了数据的安全性。当某一个MySQL节点，无论是主库还是从库故障时，还有其他的节点中存储着全量数据，保证数据不会丢失。

主库将变更写binlog日志，然后从库连接到主库后，从库有个I/O线程，将主库的binlog日志拷贝到本地，写入一个中继日志。接着从库中有一个SQL线程会从中继日志读取binlog，然后执行binlog日志中的内容。即在本地再次执行一遍SQL，确保跟主库的数据相同。

MySQL数据库作发布系统的存储，一天五万条以上的增量，预计 运维三年,怎么优化？

（1）设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
（2） 选择合适的表字段数据类型和存储引擎，适当的添加索引。
（3） 做mysql主从复制读写分离。
（4）对数据表进行分表，减少单表中的数据量提高查询速度。
（5）添加缓存机制，比如redis，memcached等。
（6）对不经常改动的页面，生成静态页面（比如做ob缓存）。
（7）书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE

数据库备份

mysqldump以及xtranbackup的实现原理

(1)备份计划
视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。

100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。高逼格企业级MySQL数据库备份方案，原来是这样…

(2)备份恢复时间
物理备份恢复快，逻辑备份恢复慢

这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考

20G的2分钟（mysqldump）

80G的30分钟(mysqldump)

111G的30分钟（mysqldump)

288G的3小时（xtra)

3T的4小时（xtra)

逻辑导入时间一般是备份时间的5倍以上

(3)备份恢复失败如何处理
首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。

(4)mysqldump和xtrabackup实现原理
mysqldump
mysqldump 属于逻辑备份。加入–single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。如果加上–master-data=1 的话，在刚开始的时候还会加一个数据库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务

Xtrabackup:
xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下来。最后完成 innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在5.6 上不做此操作会丢数据)，确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。这样就做到了完美的热备。