时间过得好快呀

以下只针对Java岗。来源主要是牛客的Java实习面经。其中包括数据库的概念和常见的MySQL面试题，Redis下次写。下面的回答直接背就可以，需要一定的MySQL和数据库基础，适合春招实习的同学，但是我会在每个问题下把有助于理解的博客贴出来。如果发现有问题欢迎私聊我或留言我会在下面更新

数据库概念

1. ACID 讲一下

19年爱奇艺上海Java实习生

ACID是事物的四个特性。分别是 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability) 。

  • 原子性是指事物是一个不可分割的工作单位，事物中的操作要么都发生，要么都不发生。最经典的就是转账案例，我们把转入和转出当做一个事物的话，就需要在SQL中显式指定开启事务。
  • 一致性是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性 。我们可以从数据库层面和业务层面两方面来保证，数据库层面我们可以设置触发器，外键，表，行约束等来保证，业务层面就是我们Java工程师的工作啦
  • 隔离性指的是多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。 多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。 这个点又引申出了下面两道题，以及后边的加锁和阻塞
  • 持久性意味着即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中，不会被回滚

2. 说一下脏读，不可重复读和幻读

19年爱奇艺上海Java实习生

  • 脏读： 意味着一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。即这个事物读取的数据是不正确的

  • 不可重复读： 在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。即这个事物在读的过程中被修改了

  • 幻读：当一个事物对整个table进行修改之后，第二个事物向表中插入了一行数据，此时第一个事物发现了新插入的没有修改的数据行，好像发生了幻觉一样

3. 数据库的隔离级别

19年爱奇艺Java实习

  • 读未提交RU
    1. 一个事务还没提交时，它做的变更就能被别的事务看到
    2. 会出现幻读，不可重复读，脏读
    3. 更新数据时加上行级共享锁，事物结束即释放
  • 读已提交RC
    1. 一个事务提交之后，它做的变更才会被其他事务看到
    2. 会出现幻读，不可重复读，不会出现脏读
    3. 写数据加行级排他锁，这样写过程是无法读取的，直到事务处理完毕才释放排他锁，给读的数据加行级共享锁，这样读的时候也是无法写的，但是一旦读完该行就释放共享锁
    4. MySQL会在SQL语句开始执行时创建一个视图
  • 可重复读RR
    1. 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
    2. 会出现幻读，不会出现不可重复读，脏读
    3. 给写的数据加行级排他锁，事务结束释放，给读的数据加行级共享锁，事务结束后释放
    4. MySQL会在事物开始时创建一个一致性视图*(接下面的MVCC)*，事物结束时销毁
  • 可串行化S
    1. 当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
    2. 不会出现幻读，不可重复读，脏读
    3. 事务读数据则加表级共享锁，事务写数据则加表级排他锁
    4. 不区分快照度与当前读

其中，Oracle和SQLServer都是读已提交，但MySQL默认的隔离级别是可重复读 ，这是一个MySQL5.0之前的上古遗留版本问题。当时的binlog只有STATEMENT格式，用RC会出现bug。

Mysql为什么是可重复读

当面试官问我们项目用的哪个隔离级别，就我自己来说，如果看我之前的文章，我写过我搭建了一个博客，对于博客来说，我肯定要说读已提交。这样的并发度更高，同时，在我们的互联网项目中可以接受重复读带来的一些不便。

同时，既然问到了数据库的隔离级别，那么离下面的锁也不远了

4. 三大范式

19年华为实习生

  • 第一范式： 所有字段值都是不可分解的原子值 。例如有一个列是电话号码一个人可能有一个办公电话一个移动电话。第一范式就需要拆开成两个属性。
  • 第二范式：非主属性完全函数依赖于候选键。如PersonID，ProductID，ProductName，PersonName可以看到，PersonID和ProductID是联合主键，但是ProductName是依赖于ProductID的，只依赖了部分主键，没有依赖全部主键。需要拆分成三个表：PersonID, PersonName, ProductID, ProductName和PersonID, ProductID
  • 第三范式： 每一列数据都和主键直接相关，而不能间接相关 。如OrderID，ProductID，ProductName，OrderID是主键，但是ProductID依赖了OrderID，而ProductName依赖了ProductID，等于说是间接依赖了OrderID，所以需要拆分为两个表：OrderID, ProductID和ProductID, ProductName

这里需要指明范式不是最好的，我们需要混合使用范式和反范式

  • 范式的优点：因为相对来说有较少的重复数据，范式化的更新操作要比反范式快。同时范式化需要更少的distinct和order by
  • 范式化缺点：通常需要关联，不仅代价昂贵，也可能会使的一些索引无效
  • 常用的反范式方法：
    1. 复制：在两个表中根据实际业务情况存储部分相同的字段列，即有利于查询，也不会把表搞的太大
    2. 缓存：对于需要多次join查询的表，可以在一个表中加入一个缓存列，用来缓存所join表的部分常用数据，如count等，我们需要实时更新该缓存

5. 说一下内连接和外连接

  • 内连接也叫自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取两个表中所匹配的数据，舍弃不匹配的数据 

    1
    select fieldlist from table1 [inner] join table2 on table1.column = table2.column

  • 内连接保证两个表中的所有行都满足条件，而外连接则不然，外连接不仅仅包含符合连接条件的行，而且还包括左表（左外连接），右表（右外连接），或者两个边表（全外连接）中的所有数据行 

    1
    select fieldlist from table1 left/ right outer join table2 on table1.column = table2.column

    6目前（8.0）MySQL不支持全外连接

参考文章：

互联网项目中MySQL应选哪个隔离级别

数据库复习指南

数据库设计三大范式

MySQL索引

18年阿里实习

索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。可以加快查的速度，但是会增加容量，降低增，删，改的速度

1. MySQL有哪几种索引类型，各自特点

腾讯云中间件19年春季实习招

19年盛大游戏实习

常见的MySQL索引结构有B-树索引，B+树索引，Hash索引和全文索引

  • B-Tree索引
    • 因为存储引擎不用进行全表扫描来获取数据，直接从索引的根节点开始搜索，从而能加快访问数据的速度
    • B-Tree对索引是顺序组织存储的，很适合查找范围数据
    • 适用于全键值、键值范围或者键前缀查找（根据最左前缀查找）
    • 限制：对于联合索引来说，如果不是从最左列开始查找，则无法使用索引；不能跳过索引中的列
  • B+Tree索引
    • 是B-Tree索引的变种，现在主流的存储引擎都不用单纯的B-Tree，而是其变种B+Tree或者T-Tree等等
    • 和B-Tree最主要的区别就是**B+Tree的内节点不存储data，只存储key，叶子节点不存储指针 **
  • Hash索引
    • 基于Hash表实现，只有Memory存储引擎显式支持哈希索引
    • 适合等值查询，如=、in()、<=>，不支持范围查询
    • 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序
    • Hash索引在查询等值时非常快
    • 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找
    • 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题
    • 程序员可以在B+Tree索引的基础上创建自适应Hash索引
  • 全文索引
    • MyISAM和InnoDB都支持全文索引
    • 有三种模式：自然语言模式，布尔模式和查询扩展模式
  • R-Tree索引
    • MyISAM支持R-Tree索引，这个和全文索引基本不问

2. B+树索引和hash索引的区别

阿里社招

  • B+树索引适合范围查找，而hash索引适合等值查询
  • hash索引无法利用索引完成排序，但是B+树索引可以
  • hash索引不支持多了联合索引的最左匹配规则，但是B+树索引支持
  • 如果有大量重复键值的情况下，因为存在hash碰撞，hash索引的效率会很低

3. B树和B+树的区别

腾讯云中间件19年春季实习招

这个题其实偏向于数据结构了，这里不多讲，只说一下针对数据库来说，面试该怎么回答。派生问题：二叉树，二叉搜索树，B树，B+树。遇到坑爹的面试官可能让你写一个B+树…

  • 二叉树

    任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值

  • 平衡二叉树/AVL树

    当二叉树非常极端，变成一个链表后，它就没有了二叉树的相关优秀性质了。所以我们在insert节点的时候，需要不断的旋转，来使二叉树平衡，最终使得其查询效率最高。调整一共分为四种情况：LL，RR，LR，RL

  • B-树

    因为数据库中大部分数据都存在于磁盘，但是IO一次磁盘的代价相对来说比较大，我们需要尽可能的减少AVL树的深度，即增加每个节点的数据量。这便是B-树的由来

    每一个节点称为页，也就是一个磁盘块。 B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点

  • B+树

    1. 是B-树的变形，相对于B-树来说，B+树最主要的不同之处就是其非叶子节点上是不存储数据的，数据全在叶子节点存储。这就意味着B+树比B-树更胖
    2. 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的

4. InnoDB为什么要使用B+树作为索引

腾讯云中间件19年春季实习招

这时说出B+树索引的优点即可，同时也可能会引出Hash索引和全文索引

InnoDB中索引即数据，数据即索引

转第二题

5. 怎么查看MySQL语句有没有用到索引

阿里数据技术产品部19年春季实习招

通过explain，如

1
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
id select_type table partitions type possible_keys key key_len ref filtered rows Extra
1 SIMPLE titles null const PRIMARY PRIMARY 59 const,const,const 10 1

面试官很有可能问一些字段的意思：

id：在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ，如explain select * from s1 where id = (select id from s1 where name = 'egon1'); 第一个select的id是1，第二个select的id是2。有时候会出现两个select，但是id却都是1，这是因为优化器把子查询变成了连接查询

select_type：select关键字对应的那个查询的类型，如SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION

table：每个查询对应的表名

type：执行查询的访问方法，如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),range (索引的范围查询)index(扫描全表索引的覆盖索引)

possible_key：查询中可能用到的索引*(可以把用不到的删掉，降低优化器的优化时间)*

key：查询中用到的索引

filtered：查询器预测满足下一次查询条件的百分比

extra：表示额外信息，如Using where,Start temporary,End temporary,Using temporary等

6. 聚簇索引与非聚簇索引

19年上海爱奇艺日常实习生

  • 聚簇索引和非聚簇索引是建立在B+树的基础上
  • 聚簇索引：key为主键，value为其余列的数据。一个表只能有一个聚簇索引
  • 非聚簇索引：除了聚簇索引外的都叫非聚簇索引
    1. 对于MyISAM的主键索引来说，它的非聚簇索引是key为主键，value为行号*(不一定)*
    2. 对于MyISAM的二级索引来说，它的非聚簇索引是key为其他列，value为行号*(不一定)*
    3. 对于InnoDB的二级索引来说，它的非聚簇索引是key为其他列，value是主键
  • 非聚簇索引也叫二级索引
  • 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
  • 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描
  • 通常情况下， 主键索引查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可
  • 注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引

7. 覆盖索引

阿里社招

如果一个索引包含（覆盖）所有需要查询字段的值，我们就称之为”覆盖索引”。如select id from tab where id = 1，并且id是tab的索引，这就是一个覆盖索引。

当非聚簇索引是覆盖索引的时候，就只用查询一次即可

8. 组合索引

19年微博

因为每个select只能选择一个索引，当where条件过多时，我们可以考虑建立联合索引，即把多个列作为索引：

1
create index inx_col1_col2 on tab (col1,col2);

问这个问题主要是引出下面的最左前缀原则

9. MySQL的最左前缀原则

这里主要是向面试官说明组合索引在B+树上如何被创建的，对于索引(a,b,c)，引擎会先按照a排序，当a相等时，再按照b排序，当b相等时，再按照c排序

对于索引(a,b,c)来说，能命中的where语句有

  1. where a = 1,where a = 1 and b = 1和where a = 1 and b = 1 and c = 1

  2. where a like '1%'，对于这个，可能会引出前缀索引

注意：

对于表tabid,name,gender来说：

  • 当SQL为select name, id from tab where name = 'stalern' ，索引为id, name，此时索引是有效的，走得是覆盖索引，但是命中率特别低，但是测试了一下explain不会走
  • 当SQL为select * from tab where id > 0 and id < 10 and name > 'stalern'，索引为id,name，此时并不会用到id,name，而是其中的id索引，没有name。但是当SQL为select * from tab where id = 0 and name > 'a' and name < 'c'时，这个索引是可以全部用到的

10. 前缀索引

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引

流程是：

  1. 先计算完整列的选择性

    1
    select count(distinct col_1)/count(1) from table_1

  2. 再计算不同前缀长度的选择性

    1
    select count(distinct left(col_1,4))/count(1) from table_1

  3. 找到最优长度之后，创建前缀索引

    1
    create index idx_front on table_1 (col_1(4))

11. 索引下推

阿里社招

MySQL 5.6引入了索引下推优化。默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

  • 有了索引下推优化，可以在减少回表次数
  • 在InnoDB中只针对二级索引有效

譬如，在 people_table中有一个二级索引(zipcode，lastname，firstname)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

  1. 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件
  2. 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉

12. 查询在什么情况下不走索引

19年CVTE实习

首先，我们可以说通过explain去排查一个慢查询，进而找到它的索引*(参看第五题)*，当创建索引却不走索引时，我们就需要考虑到优化器的问题。

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下：

  1. 根据搜索条件，找出所有可能使用的索引
  2. 计算全表扫描的代价
  3. 计算使用不同索引执行查询的代价
  4. 对比各种执行方案的代价，找出成本最低的那一个

那么，有哪几种情况明明设了索引却不走索引呢？

假设索引为(a,b,c)

  • ASC和DESC索引混合使用的排序：select * from tab order by a, b desc limit 10;
  • 违背最左前缀原则：select * from tab where b = '1';
  • WHERE⼦句中出现非排序使⽤到的索引列：select * from tab where d = '1' order by a limit 10;
  • 排序列包含非同⼀个索引的列：select * from tab order by a, d limit 10;
  • WHERE子句中出现计算：select * from tab where a * 4 = 2;
  • WHERE子句中出现null值：select * from tab where a = null;
  • WHERE子句中使用!=或<>操作符：select * from tab where a != 1;
  • 同时类型不匹配，譬如char = 1就不会走索引

13. MySQL如何为表字段添加索引

下面是添加索引的SQL语法

1.添加主键索引

1
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加唯一索引

1
ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加普通索引

1
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加全文索引*(适用于MyISAM，InnoDB 5.6+)*

1
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加联合索引

1
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

14. 如何选择索引

  1. 只为⽤于搜索、排序或分组的列创建索引
  2. 考虑列的基数 ，基数越大，创建索引的效果越好
  3. 索引列的类型尽量⼩，这样B+树中每个页存储的数据就会更多
  4. 写多读少尽量不要建立索引
  5. 可以使用倒叙索引或者hash索引
  6. InnoDB的主键尽量用MySQL的自增主键

15. 唯一索引和普通索引选择哪个？

  • 唯一索引和普通索引在读取的时候效率基本差不多，普通索引差了一点点。主要是判断和特殊情况下的一次IO
  • 写入的时候，普通索引可以利用change buffer，适合写多读少，比唯一索引要快

以业务为前提，如果要求唯一，就要选择唯一索引。如果已经保证列的唯一，我们尽量选择普通索引，然后把change buffer调大

参考文章：

MySQL索引原理及查询优化

MySQL索引完全解读

我以为我对索引很了解，却遇到了阿里的面试官

B树与B+树

平衡二叉树的实现原理

MySQL的优化

1. MySQL的查询优化

19年商汤实习

  • 上文中的如何选择索引
  • 使用连接代替子查询
  • 尽量用IN代替OR, OR的效率是n级别，IN的效率是log(n)级别，IN的个数建议控制在200以内
  • 能用BETWEEN不用IN
  • 尽量用LIMIT，同时尽量不用count *
  • 切分一个连接时间很长的查询，或返回数据量很大的查询
  • 分解关联查询，在应用层做关联，可以更容易对数据库进行拆分，减少锁的竞争，减少冗余记录的查询

2. MySQL大数据量下的优化

腾讯云中间件19年春季实习招

这个题可说的点实在太多了，面试官很有可能跟据下面说的优化进而来问问题

  • 建表时

    • 此处考察如何建立索引
    • 字段类型尽量精确，尽量小，能用int不要用bigint
    • 尽量不要用null，声明not null，如果是null用0代替
    • 尽量使用TIMESTAMP而非DATETIME
    • 用整型来存ip
    • 注意反范式和范式的优化

  • 查询时

    • 参考查询优化
    • 参考前面的查询在什么情况下不走索引

  • 加缓存NoSQL

    • Memcached
    • Redis

  • 分区

    • MySQL内置的是水平分区，分为range，list，hash，key
    • 在分区的基础上还可以有子分区，一个分区创建子分区，其他分区也要创建子分区；每个分区的子分区数必须相同；所有分区必须使用相同的存储引擎
    • 分区表的数据还可以分布在不同的物理设备上

  • 分表 & 分表

    • 垂直分表：把表中的一些字段移到其他表或者新建表

    • 水平分表：和分区类似

    • 垂直分库：把不同数据表分到不同库，不同服务器上

    • 可以使用MyCat等中间件来实现

  • 换Hadoop家族

MySQL对于千万级的大表该怎么优化

MySQL存储引擎

1. MySQL常用的引擎

MySQL常见的存储引擎主要有三个，分别是InnoDB，Memory和MyISAM

2. InnoDB和MySIAM的区别

蚂蚁金服19年春季实习招

  • 面试官常常会问到如何选择MyISAM和Innodb，这其实是一个早期DBA的问题，但是现在在事实上早已经不存在这个问题了，Innodb不断完善，从各个方面赶超了MyISAM，成为了MySQL默认的存储引擎

  • 相关差异

    差异 MyISAM InnoDB
    文件格式 数据和索引是分别存储的，数据.MYD，索引.MYI 数据和索引是集中存储的.ibd
    文件能否移动 能，一张表就对应.frm、MYD、MYI3个文件 否，因为关联的还有data下的其它文件
    记录存储顺序 按记录插入顺序保存 按主键大小有序插入
    空间碎片（删除记录并flush table table_name之后，表文件大小不变） 产生。定时整理：使用命令optimize table table_name实现 不产生
    事务 不支持 支持
    外键 不支持 支持
    锁支持 表级锁定 行级锁定、表级锁定

  • 锁差异

    • MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待
    • MyISAM因为是表锁，只有读读之间是并发的，写写之间和读写之间是串行的
    • 而在InnoDB中，锁是逐步获得的，就造成了死锁的可能
    • Innodb的行级锁不是直接锁记录，而是锁索引，即行锁是锁索引的，如果没有索引，InnoDB也会锁表

  • MyISAM和InnoDB的B+树索引实现机制不一样

    • MyISAM使用前缀压缩技术使得索引变得更小，但InnoDB则按照原数据格式进行存储
    • MyISAM索引通过数据的物理位置引出被索引的行，但InnoDB则根据主键引用被索引的行；即MyISAM索引文件和数据文件是分离的，但是InnoDB主键索引的key就是数据表的主键，data就是表中的数据
    • MyISAM的二级索引和主索引结构没有区别，但是二级索引的key可以不唯一；InnoDB二级索引data域存储相应记录主键的值而不是地址
    • 对于InnoDB来说，其主键尽量使用MySQL自带的与业务无关的自增主键

  • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数，这是因为InnoDB的MVCC

  • MyISAM的一般查询效率比InnoDB高，但是InnoDB的主键索引查询除外

  • InnoDB独有redo log，但MyISAM之后binlog，下面有详细讲这两种log

  • 如何选择

    • 默认Innodb
    • MyISAM：以读为主的应用程序，比如博客系统、新闻门户网站
    • Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。

3. InnoDB如何实现悲关锁的？

各种锁

MySQL内部的锁管理是极为透明的。

同时锁与并发关系较大，这篇博客扯的比较少，后续的并发面经会有说到

1. 乐观、悲观锁，行、表锁，读、写锁，间隙锁

19年爱奇艺上海Java实习生

18年阿里实习

根据加锁范围

  • 全局锁：是对整个数据库实例加锁。常见的场景是全库逻辑备份 *(也就是把整库每个表都select出来存成文本)*，对于InnoDB可以用可重复读这一隔离级别进行备份，但是对于MyISAM只能用全局锁
  • 表级锁
    • 表锁：lock tables t1 read, t2 write;那么该线程只能读t1，写t2，其他线程只能写t1，读t2
    • 元数据锁：即MDL，MySQL5.5版本引入。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。读锁之间不互斥，读写锁之间、写锁之间是互斥的
    • MySQL所有引擎都支持表锁
  • 行级锁
    • 由各个引擎自己实现
    • 即锁定某个表中的特定行，并发度高，锁粒度低
    • 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议
      • 行锁容易产生死锁，此时需要使用InnoDB的主动死锁检测
    • 在InnoDB中，行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住
  • 间隙锁*(GAP)*
    • 是一个范围小于表锁，大于行锁的锁，主要是为了防止幻读
    • 如果查询条件没有建立索引或者不是唯一索引，则会加上间隙锁*(普通查询是快照读，这里不考虑)*
    • 加锁范围是查询条件的两侧

根据锁的读写方式

  • 共享锁又称为读锁，简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
  • 排他锁又称为写锁，简称X锁。排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改
  • SS锁不互斥，SX和XX锁都互斥

根据锁的特征

  • 悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现

  • 乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号，如果不是，则重新更新。在JDK中的一些包里，如java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的

  • 乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能

  • 乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方

2. CAS和MVCC

19年爱奇艺上海Java实习生

18年阿里实习

CAS

即compare and swap，交换比较。CAS涉及到了3个操作符：

  • 需要读写的内存值V

  • 需要比较的值A

  • 拟写入的新值B

当且仅当 V 的值等于 A时，CAS通过原子方式用新值B来更新V的值，需要通过自旋锁不断重试

  • 优点：是非阻塞的轻量级乐观锁

  • 缺点：会出现ABA问题，即如果一个值被修改后又被重新修改回来，我们能确定它没有被修改过吗？

当问到CAS的时候，基本上就转到Java并发了

MVCC

  • 数据库多版本并发控制，即每一行数据都是有多个版本的，每个版本有自己的row trx_id，即当时修改该行的transaction_id

  • 需要用到一致性读视图，即consistent read view，用于支持RC和RR隔离级别的实现，它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”，它其实是一个视图数组，和数据库中显式创建的create view ...不一样

  • 一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

    1. 版本未提交，不可见；
    2. 版本已提交，但是是在一致性视图创建后提交的，不可见；
    3. 版本已提交，而且是在一致性视图创建前提交的，可见

  • 在MVCC中有两种读，上面三种是快照读，还有一种是当前读

    1. 当普通的select是快照读
    2. 插入，删除，更新属于当前读，需要加锁，遵从两阶段锁协议

3. MySQL如何加锁

18年滴滴寒假实习生

这个问题是十分的宽泛啊，如何加锁，基本就是关于MySQL的事物，锁，两阶段锁协议，以及不同引擎对锁和事物的处理，当然本题主要也是问MySQL如何锁如何实现事物的不同隔离级别，面试官会看你主要说什么东西，进而继续往下提问，所以这个地方尽量往自己懂的地方说就完事了。

  • MySQL普通读和修改等读是不一样的，普通读是利用了MVCC机制，而修改读是利用锁
  • 当前读 (select * from tab where a = 1 lock in share mode; )加S锁，而insert, update, delete加排他锁
  • 我们要注意，聚簇索引和非聚簇索引加锁的次数是不一样的

参加文章：

MySQL加锁过程详解

其他

1. MySQL中一条语句的执行过程

  • 我们首先要知道MySQL处理不同语句，如DDL，DML等是不一样的
  • MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行计划、执行器，同时还有binlog，引擎层包括了InnoDB，Memory，MyISAM等引擎插件，其中，InnoDB还有redo log

一条查询语句的执行过程

  1. client先发送一条查询给服务器，*#连接器#* (此时的通信协议的半双工的)
  2. 服务器先检查缓存，如果命中缓存，则返回结果，如果没有，进入下一阶段*(查询缓存是通过大小写敏感的hash表实现的，但是MySQL8.0之后把缓存删除了)*
  3. 进行SQL解析*#解析器#，预处理#预处理器#，再由查询优化器#优化器#*生成对应的查询执行计划
  4. 根据优化计划来调用存储引擎的API来查询，并将结果返回给客户端

譬如如下查询：

1
select * from tb_student  A where A.age='18' and A.name=' 张三 ';

结合上面的说明，我们分析下这个语句的执行流程：

  1. 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

  2. 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id=’1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

  3. 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

    • 先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18
    • 先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生

    优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）

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

一条更新语句的执行过程

常考的一般是含有redo log的InnoDB引擎，所以我们主要说的是InnoDB的更新流程。这里会涉及到两阶段提交的概念

  1. 先按照上述步骤，分析SQL语句，然后拿到要修改的行

  2. 执行器修改拿到的行，同时调用引擎把修改后的行写入表中

    • 引擎将这行数据更新到内存中，同时使redo log进入prepare状态

    • 告知执行器执行完毕，随时可以提交事物

  3. 执行器生成操作的binlog，并写入磁盘

  4. 执行器调用引擎的提交事物接口，引擎把刚才的redo log改成提交状态，更新完成

2. redo log和binlog的区别

  1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用
  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
  4. binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用

3. Mysql搭建集群，主机宕机之后怎么办

腾讯19年秋招

4. MySQL5.5和5.7版本的一个区别

网易19年秋招本科

5.7多了个json类型

5. 别的

  1. 使用force index可以强制执行索引
  2. 可以打开explain加慢查询日志来查看慢查询

博客全篇参考文章：

高性能MySQL

MySQL是怎么运行的：从根上理解MySQL

MySQL实战45讲

关于两阶段提交

  1. redolog和binlog
  2. producer和broker
  3. 分布式事物

关于undo，redo，bin

undologo是写之前的，保证原子性，和mvcc的版本号有关

redolog是物理写入，保证持久性

binlog有利于主从复制

关于索引

最左前缀原则

最左前缀原则要符合等值匹配