面试题目-MySQL（补充）

那些年背过的面试题：MySQL相关知识。

索引

索引有哪几种类型？

• 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键

• 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引

• 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值

• 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索

• 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行

• 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

索引有哪些优缺点？

优点：

• 唯一索引可以保证数据库表中每一行的数据的唯一性

• 索引可以加快数据查询速度，减少查询时间

缺点：

• 创建索引和维护索引要耗费时间

• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间

• 以表中的数据进行增、删、改的时候，索引也要动态的维护

创建索引的三种方式

• 在执行CREATE TABLE时创建索引

  CREATE TABLE `employee` (
  `id` INT ( 11 ) NOT NULL,
  `name` VARCHAR ( 255 ) DEFAULT NULL,
  `age` INT ( 11 ) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` INT ( 1 ) DEFAULT NULL,
  PRIMARY KEY ( `id` ),
  KEY `idx_name` ( `name` ) USING BTREE 
  ) ENGINE = INNODB DEFAULT CHARSET = utf8;

• 使用ALTER TABLE命令添加索引

  ALTER TABLE table_name ADD INDEX index_name (column);

• 使用CREATE INDEX命令创建

  CREATE INDEX index_name ON table_name (column);

索引失效的情况有哪些？

• 使用不等于操作符时

• 不等于操作符：!=、<>。类似的负向查询包括：NOT、!<、!>、NOT IN、NOT LIKE、IS NOT NULL等

• 前置模糊查询like

• 如果只查询索引列的内容，类似select a from table where a like ‘%a%’时，即索引列和查询列一致时，模糊查询双%索引生效，只要查询结果中存在非索引列，则双%索引失效

• 无论结果列是否包含索引列，后置%查询，索引都会生效

• like查询以%开头时，会导致索引失效。%在后面或使用双%覆盖查询时，索引不会失效

• 查询条件类型与数值不匹配（区分情况），数值类型不匹配时， MySQL对列值进行隐式类型转换

• 例如：索引列user_id为varchar类型，使用where user_id = 12查询时不会命中索引，索引失效，造成全表扫描

• 查询条件为int类型，也会将所查询的字段值转为int类型，再与查询条件进行匹配，会进行全表扫描，索引失效

• select * from user where name = 0;该查询会将name字段全部转换为int类型进行匹配

• 如果字符串条件中，前导没有数字，则将字符串条件转换为0，进行条件匹配，且索引不会失效

• 如果字符串条件中，前导存在数字，则截取字符串中所有前导数字作为条件进行匹配，且索引不会失效

• 例如where a = ‘aaa1’，MySQL将条件转为a=0进行匹配

• 例如where a = ‘2321aaa1’，MySQL将截取字符串前导数字2321作为条件进行匹配

• 列为int类型，查询条件为字符串类型，MySQL对查询条件进行优化及类型转换，且索引不会失效

• 列为字符串类型，查询条件为int类型

• OR连接条件

• 存在a，b，c三个索引，执行查询语句select * from aaa where b = 2 and (a = 1 or c =3)时，ac索引失效，b索引仍然生效

• 存在a, b两个索引，执行查询语句select * from aaa where a = 1 or b =3时，ab索引仍然生效

• 结论：使用or时只有两个字段都有索引情况下才能正常走索引，此时是索引合并，如果一个没有或者都没有就不会走索引，而是使用all进行全表扫描

• 在SQL语句的条件中，存在用OR连接的条件表达式时，不能使用索引，以下情况例外

• 相反使用AND连接条件的则能够使用索引

• 在条件表达式前使用函数或进行计算的

• 在表达式前使用函数的，无法命中索引，例如：where id+1 = 1

• 在表达式后使用函数的，能够使用索引，例如：where id = 1+1

• 表关联字段字符集不一致时

• 多个表关联字段的字符集不一致时，MySQL进行隐式的字符集转换并统一，会导致索引失效

• 组合索引违背最左匹配规则时，存在两种失效场景

• where条件中的查询字段，违背表中组合索引的最左匹配规则时，索引失效

• where条件中，存在范围条件查询时，范围右侧的索引全部失效（这里的范围指的是组合索引的范围）

• 索引列结果占比大于全表30%时，索引失效，会进行全表扫描

• 例如性别男女作为索引条件，查询优化器对性别字段查询条件做优化，索引失效

什么时候适合建立索引？

1、建立主键时，建立索引（主键索引）

2、频繁作为查询条件的字段应该建立索引

3、查询中与其他表关联的字段，建立索引，提高多表查询效率

4、单键/组合索引的选择，组合索引性能更好

5、查询中排序的字段，排序字段通过索引去查询，会大大提升排序效率

6、查询中统计或分组字段，建立索引

什么时候不适合建立索引？

1、数量量少的表

2、经常做增删改的表不适合，会占用磁盘空间，且降低 sql 执行效率

3、where 条件里用不到的字段不适合建索引

4、不能作为唯一区别的字段不适合建立索引（如性别字段）

聚集索引与非聚集索引的区别

聚集索引与非聚集索引，也叫做聚簇索引与非聚簇索引，参考文章：B+树索引

概念：

• 聚集索引（聚簇索引）：以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引

• 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引

区别：

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

• 聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序，即物理存储按照索引排序

• 非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，即物理存储不按照索引排序

• 索引是通过二叉树的数据结构来描述的，聚簇索引中索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块

何时使用聚集索引或非聚集索引？

InnoDB引擎中的索引策略，了解过吗？

• 覆盖索引：覆盖索引是指在普通索引树中可以得到查询的结果，不需要在回到主键索引树中再次搜索，优点有以下几个

• 减少树的搜索次数，显著提升查询性能

• 索引是按照值的顺序存储，所以对于 I/O 密集型的范围查询比随机从磁盘中读取每一行的 I/O 要少很多

• 索引的条目远小于数据的条目，在索引树上读取会极大的减小数据库的访问量

• 最左前缀原则：最左前缀原则是建立在联合索引之上的，如果我们建立了联合索引，我们不需要使用索引的全部定义，只要用到了索引中的最左边的那个字段就可以使用这个索引，这就是 B-tree 索引支持最左前缀原则

• 索引下推：索引下推优化是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

参考：这篇参考文章写的很不错

回表是什么？

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

非聚簇索引一定会回表查询吗？

不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（例如覆盖索引）。

例如，假设在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

组合索引是什么？为什么需要注意组合索引中的顺序？

组合索引，是指在多个列上建立索引，这种索引叫做组合索引。

因为InnoDB引擎中的索引策略的最左原则，所以需要注意组合索引中的顺序。

列值为NULL时，查询是否会用到索引？

列值为NULL也是可以走索引的。

计划对列进行索引，应尽量避免把它设置为可空，因为这会让 MySQL 难以优化引用了可空列的查询，同时增加了引擎的复杂度。

如何写sql能够有效的使用到复合索引

复合索引，也叫组合索引，用户可以在多个列上建立索引，这种索引叫做复合索引。

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

select * from table where k1=A AND k2=B AND k3=D

有关于复合索引，我们需要关注查询Sql条件的顺序，确保最左匹配原则有效，同时可以删除不必要的冗余索引。

InnoDB引擎的4大特性，了解过吗

• 插入缓冲(insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)

• 预读(read ahead)

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

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

• 当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则

索引原理

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

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数。

为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是B+树呢？

😐为什么不是一般二叉树？

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

😐为什么不是平衡二叉树呢？

在内存比在磁盘的数据，查询效率快得多。

如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。

但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

😐那为什么不是B树而是B+树呢？

1. B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据

• B+树的每个节点都对应InnoDB的一个Page，Page大小是固定的，一般被设为16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快

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

B树和B+树的区别，数据库为什么使用B+树而不是B树？

• 在B树中，键和值即存放在内部节点又存放在叶子节点；在B+树中，内部节点只存键，叶子节点则同时存放键和值

• 内部节点也就是非叶子节点

• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立的

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

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

B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？

覆盖索引不用说，满足覆盖索引的情况，都能获取到所有需要的数据，不需要回表查询。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此也不用再次进行回表查询。

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

• B+树可以进行范围查询，Hash索引不能

• B+树支持联合索引的最左侧原则，Hash索引不支持

• B+树支持order by排序，Hash索引不支持

• Hash索引在等值查询上比B+树效率更高

• B+树使用like 进行模糊查询的时候，like后面（比如%结尾）的话可以起到优化的作用，Hash索引根本无法进行模糊查询

数据库使用

日常工作中是怎么优化SQL的？（查询优化）

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

• 加索引

• 避免返回不必要的数据

• 适当分批量进行

• 优化sql结构

• 分库分表

• 读写分离

如果是查询优化，可以从下面入手

• 优化SELECT查询返回项，并指定字段对应类型：没有必要的返回项占用内存空间，影响网络传输性能，应该去掉非必要的字段，同时指定数据库字段与Java对象属性类型保持一致，避免隐式转换

• 优化子查询：使用连接(JOIN)来替代子查询。连接查询不需要在内存中建立临时表，其速度比子查询要快

• 如果子表数据大于主表时，使用exists优于in，相反子表数据小于主表时，使用in优于exists

• 如果查询结果只有一条或者求最大/小的一条记录，SQL语句结尾使用limit 1，找到数据时，就不继续扫描

• 如果查询条件是唯一索引时，无需添加limit 1，limit的作用主要是为了防止全表扫描

• 使用join连接查询时，优先使用inner join，使用left join时，保证左表数据结果尽量较少

• 优化where查询条件及order by排列，对where条件及order by条件所涉及的字段建立索引，避免全表扫描

• 使用Explain执行SQL分析，优化索引，检查查询条件字段索引的命中情况

• 使用批量保存SQL语句，替换大批量的单笔数据保存SQL语句

• 除了上述的之外，有条件的可以考虑分库分表、读写分离

• 了解SQL子句逻辑执行顺序：能使用前置查询的就不使用后置查询，SQL执行顺序如下，序号是执行顺序

  (6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
  (1)  FROM t1 JOIN t2
  (2)    ON (join_conditions)
  (3) WHERE where_conditions
  (4) GROUP BY col1, col2
  (5)HAVING having_condition
  (7) UNION [ALL]
     ...
  (8) ORDER BY col1 ASC,col2 DESC
  (9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

一条Sql的执行顺序是什么？

SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义。

• show status 命令了解各种 sql 的执行频率

• 通过慢查询日志定位那些执行效率较低的 sql 语句

• explain 分析低效 sql 的执行计划（这点非常重要，日常开发中用它分析Sql，会大大降低Sql导致的线上事故）

• 重点关注type字段

如果某个表有近千万数据，CRUD比较慢，如何优化

分库分表

某个表有近千万数据，可以考虑优化表结构，分表（水平分表，垂直分表），当然，你这样回答，需要准备好面试官问你的分库分表相关问题呀，如

• 分表方案（水平分表，垂直分表，切分规则hash等）

• 分库分表中间件（Mycat，sharding-jdbc等）

• 分库分表一些问题（事务问题？跨节点Join的问题）

• 解决方案（分布式事务等）

索引优化

除了分库分表，优化表结构，当然还有所以索引优化等方案~

缓存

可以考虑加缓存，memcached, redis，或者JVM本地缓存；

一条SQL语句在MySQL中如何执行的？

Mysql的逻辑架构图如下：

查询语句执行过程：

• 先检查该语句是否有权限

• 如果没有权限，直接返回错误信息

• 如果有权限，在 MySQL8.0 版本以前，会先查询缓存

• 如果没有缓存，分析器进行词法分析，提取 sql 语句select等的关键元素。然后判断sql 语句是否有语法错误，比如关键词是否正确等等。

• 优化器进行确定执行方案

• 进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引擎接口，返回执行结果。

MySQL数据库服务器性能分析的方法命令有哪些?

查询系统性能参数命令：show status like ‘value’，value为要查询的参数值，常用的如下：

• Connections：连接MySQL服务器的次数。

• Uptime：MySQL服务器的上线时间。

• Slow_queries：慢查询的次数。

• Com_select：查询操作的次数。

• Com_insert：插入操作的次数。

• Com_update：更新操作的次数。

• Com_delete：删除操作的次数。

• Bytesreceived和Bytessent 和服务器之间来往的流量。

• Created_*在查询执行期限间创建的临时表和文件。

• Handler_*存储引擎操作。

• Select_*不同类型的联接执行计划。

• Sort_*几种排序信息。

Show profiles 是MySql用来分析当前会话SQL语句执行的资源消耗情况

百万级别或以上的数据，你是如何删除的？

我们想要删除百万数据的时候，可以按以下步骤执行：

1. 可以先删除索引

2. 然后批量删除其中无用数据

3. 删除完成后重新创建索引

数据库是否支持emoji表情存储，如果不支持，如何操作？

更换数据库表的字符集：utf8–>utf8mb4

说说分库与分表的设计

分库分表方案，分库分表中间件，分库分表可能遇到的问题

分库分表方案:

• 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中

• 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中

• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中

• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中

常用的分库分表中间件：

• sharding-jdbc（当当）

• Mycat

• TDDL（淘宝）

• Oceanus(58同城数据库中间件)

• vitess（谷歌开发的数据库中间件）

• Atlas(Qihoo 360)

分库分表可能遇到的问题

• 事务问题：需要用分布式事务

• 跨节点Join的问题：解决这一问题可以分两次查询实现

• 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并

• 数据迁移，容量规划，扩容等问题

• ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制，最简单可以考虑UUID

• 跨分片的排序分页问题

limit 1000000加载很慢的话，你是怎么解决的呢？

• 方案一：如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit

  select id，name from employee where id>1000000 limit 10

• 方案二：在业务允许的情况下限制页数

• 方案三：order by + 索引（id为索引）

  select id，name from employee order by id  limit 1000000，10

• 方案四：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）

  SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

什么是数据库连接池?为什么需要数据库连接池呢?

连接池基本原理：数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。

应用程序和数据库建立连接的过程：

• 通过TCP协议的三次握手和数据库服务器建立连接

• 发送数据库用户账号密码，等待数据库验证用户身份

• 完成身份验证后，系统可以提交SQL语句到数据库执行

• 把连接关闭，TCP四次挥手告别

数据库连接池好处：

• 资源重用 (连接复用)

• 更快的系统响应速度

• 新的资源分配手段

• 统一的连接管理，避免数据库连接泄漏

数据库存储日期格式时，如何考虑时区转换问题？

一般建表时候，创建时间用datetime，更新时间用timestamp

• datetime类型：适合用来记录数据的原始的创建时间，修改记录中其他字段的值，datetime字段的值不会改变，除非手动修改它

• 占用4个字节，允许为空值，可以自定义值，系统不会自动修改其值

• 与时区无关

• 不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据

• 可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间

• timestamp类型适合用来记录数据的最后修改时间，只要修改了记录中其他字段的值，timestamp字段的值都会被自动更新

• 占用4个字节

• 允许为空值，但是不可以自定义值，所以为空值时没有任何意义

• TIMESTAMP值不能早于1970或晚于2037

• 值以UTC格式保存

• 时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区

• 默认值是CURRENT_TIMESTAMP常量值，其实也就是当前的系统时间

• 数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间

• 以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间

• 一个表可以存在多个TIMESTAMP列，但只有一个列会根据数据更新而改变为数据库系统当前值。因此，一个表中有多个TIMESTAMP 列是没有意义，实际上一个表只设定一个TIMESTAMP列

MySQL里记录货币用什么字段类型比较好？

• 货币在数据库中MySQL常用Decimal和Numeric类型表示，这两种类型被MySQL实现为同样的类型。他们被用于保存与金钱有关的数据

• salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99

• DECIMAL和NUMERIC值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度

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

如果是单机的话，选择自增ID；如果是分布式系统，优先考虑UUID吧，但还是最好自己公司有一套分布式唯一ID生产方案吧。

• 自增ID：数据存储空间小，查询效率高。但是如果数据量过大，会超出自增长的值范围，多库合并，也有可能有问题

• uuid：适合大量数据的插入和更新操作，但是它无序的，插入数据效率慢，占用空间大

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串，应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

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

排查过程：

• 使用top 命令观察，确定是mysqld导致还是其他原因

• 如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行

• 找出消耗高的 sql，看看执行计划是否准确，索引是否缺失，数据量是否太大

处理：

• kill 掉这些线程(同时观察 cpu 使用率是否下降)

• 进行相应的调整(比如说加索引、改sql、改内存参数)

• 重新跑这些sql

其他情况：

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

MySQL中TEXT数据类型的最大长度

• TINYTEXT：256 bytes

• TEXT：65,535 bytes(64kb)

• MEDIUMTEXT：16,777,215 bytes(16MB)

• LONGTEXT：4,294,967,295 bytes(4GB)

Blob和Text有什么区别？

• Blob用于存储二进制数据，而Text用于存储大字符串

• Blob值被视为二进制字符串（字节字符串），它们没有字符集，并且排序和比较基于列值中的字节的数值

• Text值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较

500台db，在最快时间之内重启

• 可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令

• 也可以使用 salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多线程工具同时操作多台服务

一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录

1. 如果A表TID是自增长，并且是连续的，B表的ID为索引

   例如查询语句：select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2. 如果A表的TID不是连续的，那么就需要使用覆盖索引。TID要么是主键，要么是辅助索引，B表ID也需要有索引

   例如查询语句：select * from b , (select tid from a limit 50000,200) a where b.id = a.tid;

MySQL中in 和exists的区别

例如如下两个语句

select * from A where deptId in (select deptId from B);
select * from A where exists (select 1 from B where A.deptId = B.deptId);

exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。

如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exists，这就是in和exists的区别。

varchar(50)中50的含义

• 字段最多存放 50 个字符

• 如 varchar(50) 和 varchar(200) 存储 “jay” 字符串所占空间是一样的，后者在排序时会消耗更多内存

MySQL中int(20)和char(20)以及varchar(20)的区别

• int(20) 表示字段是int类型，显示长度是 20

• char(20)表示字段是固定长度字符串，长度为 20

• varchar(20) 表示字段是可变长度字符串，长度为 20

delete、truncate与drop的区别

UNION与UNION ALL的区别？

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

• Union All：对两个结果集进行并集操作，包括重复行，不进行排序

结论

• Union去重并排序，Union All直接返回合并的结果，不去重也不排序

• Union All比Union性能好

MySQL高级

InnoDB与MyISAM的区别

• InnoDB支持事务、外键，MyISAM不支持事务、不支持外键

• InnoDB 支持 MVCC(多版本并发控制)，MyISAM 不支持

• select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描

• Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）

• InnoDB支持表、行级锁，而MyISAM支持表级锁

• InnoDB表必须有主键，而MyISAM可以没有主键

• Innodb表需要更多的内存和存储，而MyISAM可被压缩，存储空间较小

• Innodb按主键大小有序插入，MyISAM记录插入顺序是，按记录插入顺序保存

• InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引

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

MVCC：多版本并发控制，它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并发性能的一种机制。

MVCC需要关注这几个知识点：

• 事务版本号

• 表的隐藏列

• undo log

• read view

MySQL中InnoDB引擎的行锁是怎么实现的？

基于索引来完成行锁的。例如select * from t where id = 666 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将实行表锁。

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

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计

• 列名为主键，count(列名)会比count(1)快

• 列名不为主键，count(1)会比count(列名)快

• 如果表多个列并且没有主键，则 count(1)的执行效率优于 count(*)

• 如果有主键，则 select count(主键)的执行效率是最优的

• 如果表只有一个字段，则 select count(*)最优

Innodb的事务实现原理？

• 原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态

• 持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复

• 隔离性：通过锁以及MVCC，使事务相互隔离开

• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性

Innodb的事务与日志的实现方式

有多少种日志

innodb两种日志redo和undo。

日志的存放形式

• redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）

• undo：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通过设置 innodbundotablespaces 参数把 undo log 存放在 ibdata之外

事务是如何通过日志来实现的

• 因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘

• 当事务需要回滚时，因为有 undo，可以把数据页回滚到之前镜像的状态

• 崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前

• 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉

MySQL事务得四大特性以及实现原理

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行

• 一致性（Consistency）：指在事务开始之前和事务结束以后，数据不会被破坏

• 隔离性（Isolation）：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果

• 持久性（Durabilily）：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中

实现原理即InnoDB的事务实现原理。

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

• 读未提交（Read Uncommitted）

• 读已提交（Read Committed）

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

• 串行化（Serializable）

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

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

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

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

MySQL中有哪几种锁，列举一下？

如果按锁粒度划分，有以下3种：

• 表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低；不会出现死锁

• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

从锁的类别角度讲，MySQL都有哪些锁呢？

锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

• 排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

锁兼容性如下：

在高并发情况下，如何做到安全的修改同一行数据？

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案~

😐使用悲观锁

悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~ 比如，可以使用select…for update ~

select * from User where name=‘jay’ for update

以上这条sql语句会锁定了User表中所有符合检索条件（name=‘jay’）的记录。本次事务提交之前，别的线程都无法修改这些记录。

😐使用乐观锁

乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

select for update有什么含义，会锁表还是锁行还是其他。

select for update 含义

select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁呢，而且它是悲观锁。

• 至于加了是行锁还是表锁，这就要看是不是用了索引/主键

• 没用索引/主键的话就是表锁，否则就是是行锁

什么是死锁？怎么解决？

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

死锁有四个必要条件：互斥条件，请求和保持条件，环路等待条件，不剥夺条件。

解决死锁思路，一般就是切断环路，尽量避免并发形成环路。

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率

• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率

• 如果业务处理不好可以用分布式事务锁或者使用乐观锁

• 死锁与索引密不可分，解决索引问题，需要合理优化你的索引

MySQL的复制原理以及流程

主从复制原理，简言之，就三步曲，如下：

• 主数据库有个bin-log二进制文件，记录所有增删改Sql语句。（binlog线程）

• 从数据库把主数据库的bin-log文件的sql语句复制过来。（io线程）

• 从数据库的relay-log重做日志文件中再执行一次这些sql语句。（Sql执行线程）

如下图所示：

上图主从复制分了五个步骤进行：

• 步骤一：主库的更新事件(update、insert、delete)被写到binlog

• 步骤二：从库发起连接，连接到主库

• 步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库

• 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log

• 步骤五：还会创建一个SQL线程，从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

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

主从同步延迟的原因

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

主从同步延迟的解决办法

• 主服务器要负责更新操作，对安全性的要求比从服务器要高，所以有些设置参数可以修改，比如syncbinlog=1，innodbflushlogattrxcommit = 1 之类的设置等

• 选择更好的硬件设备作为slave

• 把一台从服务器当度作为备份使用，而不提供查询，那边他的负载下来了，执行relay log 里面的SQL效率自然就高了

• 增加从服务器，这个目的还是分散读的压力，从而降低服务器负载

MySQL的binlog有几种录入格式？分别有什么区别？

有三种格式：statement，row和mixed。

• statement，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制

• row，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大

• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row

总结不易，动动小手收藏、在看、点赞，在此谢过各位。

原文始发于微信公众号（乐码人生）：面试题目-MySQL（补充）