面试必问的 MySQL 知识点,你还有哪些没准备好,赶紧收藏脑图!

Hello,大家好,我是鸭血粉丝,虽说今年的大环境不是很好,但是现在毕竟是金三银四,有些公司还是在招聘的。MySQL 作为我们 Java 工程师最常用的数据库,不管是在日常的工作中还是面试中,我们都必须要对 MySQL 常见的一些知识有很好的储备,这样在面试的过程中才可以做到得心应手。下面阿粉从 MySQL 最高频的几个知识点给大家介绍一下。

更详细的脑图大家可以关注公众号,在后台回复【MySQL脑图】获得。

InnoDB 与 MyISAM 引擎的区别

InnoDB 与 MyISAM 的区别阿粉在日常学习和面试中经常被问到,我们先来看看这个吧。

image-20200411130014384

15badbc42cf435dcb0875dc10fec0aeb

锁相关

  1. InnoDB 支持表级锁和行级锁,MyISAM 只支持表级锁。事务的并发在如今的互联网海量数据的场景下基本上是无法摆脱的,这种情况下对数据库的锁的要求就要更细粒度。新版本的 MySQL 默认的数据库引擎是 InnoDB 很大的原因就是 InnoDB 支持行级锁,在日常的业务场景下,我们是不能允许使用表级锁的,对业务的影响是很大的。
  2. InnoDB 的锁分为共享锁 S 和独占锁 X,意向共享锁 IS,意向独占锁 IX,记录锁,间隙锁以及 next-key 锁。关于锁的详细信息可以回顾公号之前发过的文章 面试你应该知道的 MySQL 锁

底层的文件结构

InnoDB 和 MyISAM 引擎底层存储数据的文件结构也不一样。InnoDB 引擎的数据表底层只有.frm 和 .ibd 两种格式的文件。MyISAM 引擎的表底层有 .MYD,.MYI 和 .frm 三种格式的文件。

image-20200411131607577

上图中表 other_table 的引擎采用的是 MyISAM,底层会出现三种格式的文件,分别是 .frm 用于存储表结构的文件;.MYD 全称为 .mydata 用于存放数据的文件;.MYI 全称为 .myIndex 用于存放索引的文件。而表 sys_log_op 表的引擎采用的是 InnoDB,底层只有两个文件,分别为 .frm 同样用于存放表的数据结构;.ibd 文件用于存储数据和索引的文件。

从上面我们可以看到,InnoDB 的数据和索引是存放在同一个文件中的而 MyISAM 的数据和索引是分开存储的,这也导致两者的索引的实现方式的不同

索引的实现方式

InnoDB 的索引采用 B+ 树实现,主键索引的叶子节点存储的是完整的数据,辅助索引的叶子节点存储的是主键的值。所以如果在使用辅助索引进行查询数据的时候是会有回表的操作,所以我们尽量用主键进行查询。另外如果建表的时候没有设置主键的话,InnoDB 引擎会自动生成一个 rowid 作为表的主键。

MyISAM 的索引也是采用 B+ 树实现的,与 InnoDB 不同的是,MyISAM 主键索引的叶子节点和辅助索引保存的都是数据的地址,主键索引并没有保存完整的数据,所以都需要根据数据地址再次查询数据。

事务的支持

InnoDB 与 MyISAM 另一个最大的区别就是 InnoDB 支持事务,而 MyISAM 不支持事务。事务也是 MySQL 另外一个常规问题,我们下面看下事务的相关知识。

事务

120e6395d4dc6355cbcc11912a7932d1

事务也是一个必问的知识点哦!

image-20200411134450923

事务的四大特性 ACID

  1. A: 原子性。原子性表示事务的操作是不可分割的,事务内的一系列操作全部成功事务才成功,任何一个操作失败事务都是失败,必须回滚。
  2. C: 一致性。一致性表示事务在操作前和操作后数据都是处于一致的状态。只表示在事务的操作前后的数据一致,但是并不是代表是正确。
  3. I:隔离性。事务的并发操作是完全隔离的,不同的事务之间不会相互有影响。事务的隔离级别有四种
    1. 读未提交:表示一个事务内可以读取到另一个事务未提交的数据内容,会出现脏读,不可重复度,幻读。
    2. 读已提交:表示一个事务内可以读取另一个事务已经提交的内容,会出现不可重复读和幻读。
    3. 可重复读:表示一个事务内两次相同条件读的内容一致,但是会出现幻读。
    4. 串行化:没有问题,但是效率低下。
  4. D:持久性。事务操作结束过后,对数据的更改是可以持久化的,不能事务的操作是成功还是失败,事务日志都能保证事务的持久性。

脏读:指一个事务 A 读取到另一个事务 B 未提交的数据,也就是脏数据。

不可重复读:指一个事务 A 第一次根据条件查询某条数据,事务 B 修改了该数据并提交了,事务 A 第二次查询与第一次查询的不一致。

幻读:指事务 A 根据条件查询一批数据,事务 B 插入了新的满足条件的数据,事务 A 再次查询数据量变化了,两次查询的结果集不一致,出现了幻读。

image-20200411151615710

MySQL 的分区

image-20200411151719036

为什么分区

为什么会有分区,以及什么时候我们需要进行分区呢?我们都知道当 MySQL 单表的数量级达到一定的程度的时候,查询就会很慢。那么针对这种情况我们需要怎么处理呢?除了本身查询语句的优化之外,我们能想到的就是分库分表以及分区了。

分库分表是需要再应用层面上支持的,而分区是 MySQL 层面上支持的,在物理上将数据分成多个文件进行存储,从而提高查询速度。

image-20200411152215812

如上图表 partition_sys_log_op 是采用 InnoDB 引擎并且创建了八个分区的表结构。语句如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE `partition_sys_log_op` (
	`id` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`create_time` datetime DEFAULT NULL COMMENT '操作时间',
	`elapsed_time` INT ( 11 ) DEFAULT NULL COMMENT '耗用时间',
	`exec_sql` MEDIUMTEXT CHARACTER 
	SET utf8 COMMENT '执行的语句',
	`user_id` INT ( 11 ) DEFAULT NULL COMMENT '执行人id',
	`user_name` VARCHAR ( 45 ) CHARACTER 
	SET utf8 DEFAULT NULL COMMENT '执行人名称',
	`exec_type` VARCHAR ( 45 ) CHARACTER 
	SET utf8 DEFAULT NULL COMMENT '动作,delete update insert',
	`exec_table` VARCHAR ( 45 ) CHARACTER 
	SET utf8 DEFAULT NULL COMMENT '操作表',
	PRIMARY KEY ( `id` ),
	KEY `idx_index` ( `create_time`, `user_id`, `user_name`, `exec_table` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 COMMENT = '系统操作日志' PARTITION BY KEY ( ) PARTITIONS 8;

分区的类型

  1. Range:指定范围内分区: PARTITION p1 VALUES LESS THAN (100);
  2. List:离散值分区,数据在枚举范围内:partition p1 values in (2,4,6,8,0) ;
  3. Hash:计算单个列的 Hash值,然后指定分区数量,进行分区:PARTITION BY HASH(id) PARTITIONS 4;
  4. Key:计算单个或者多个列的 hash 值,然后指定分区数量,进行分区:PARTITION BY KEY(s1) PARTITIONS 10; 不传列名默认用主键。

Explain

image-20200411154105762

前面提到我们经常需要对 sql 进行优化,那么如何对一条 sql 进行优化呢?我们只要在普通的 sql 前面,加上关键字 explain 即可看执行计划。根据执行计划,我们可以看到该 sql 的执行情况,以及是否采用索引以及采用的是哪个索引。例如

EXPLAIN SELECT COUNT(*) FROM partition_sys_log_op WHERE create_time >= '2020-12-03 00:00:00';

image-20200411154519271

详细的 explain 的介绍可以看公号之前的文章。MySQL 之 Explain 输出分析,这里就不展开了,感兴趣的朋友可以去看看。

阿粉友情提示:更详细的脑图大家可以关注公众号,在后台回复【MySQL脑图】获得。

写在最后


最后邀请你加入我们的知识星球,这里有 1700+ 优秀的人与你一起进步,如果你是小白那你是稳赚了,很多业内经验和干活分享给你;如果你是大佬,那可以进来我们一起交流分享你的经验,说不定日后我们还可以有合作,给你的人生多一个可能。

image-20200205235453492

Java Geek Tech wechat
欢迎订阅 Java 极客技术,这里分享关于 Java 的一切。