这一篇文章分享mysql的面试知识,涵盖点比较多。下面我们来从总体到局部来看完mysql相关的面试知识。预告下一篇是网络面试知识,用图解的方式呈现给大家。希望大家多多支持,点赞,转发,在看 三连。
mysql整体架构大概可以分为:网络连接层、服务层、存储引擎层和系统文件层。 关于mysql官方的架构图如下,虽然经历多个版本迭代,但整体架构还是差不多
1)网络连接层 Connectors组件,是mysql向外提供的交互组件,如java,.net,php等语言可以通过该组件来操作SQL语句,实现与SQL的交互。 2)服务层 服务层是mysql Server的核心。主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存Cache&Buffer六个部分。
3)存储引擎层(Pluggable Storage Engines) 存储引擎负责mysql中数据的存储与提取,与底层系统文件进行交互。mysql存储引擎是插件式的,服务器中的查询执行引擎通过接口与存储引擎进行通信,接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎,各有各的特点,最常见的是MyISAM和InnoDB。 4)系统文件层(File System) 该层负责将数据库的数据和日志存储在文件系统之上,并完成与存储引擎的交互,是文件的物理存储层。主要包含日志文件,数据文件,配置文件,pid 文件,socket 文件等。
关于mysql的架构,最底层的只有主从模式,关于主从模式是简单灵活,能满足多种需求,比较主流的用法,但是写操作高可用需要自己考虑。我们常见还有双主模式,该模式从主从模式演变为双主模式,有双主双写、双主单写两种方式,一般建议使用双主单写。关于主从架构的模式参考下面的图。
下面我们图解mysql一条查询语句是怎么运行的:
mysql查询图解
mysql插入的过程如下
mysql插入流图解
类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
tinint | 1 | 有符号 -128;无符号 0 | 有符号 127;无符号 255 |
smallint | 2 | 有符号 -2^16;无符号 0 | 有符号 2^16-1;无符号 2^17-1 |
mediumint | 3 | 有符号 -2^24;无符号 0 | 有符号 2^24-1;无符号 2^25-1 |
int/interger | 4 | 有符号 -2^32;无符号 0 | 有符号 2^32-1;无符号 2^33-1 |
bigint | 8 | 有符号 -2^64;无符号 0 | 有符号 2^64-1;无符号 2^65-1 |
float | 4 | ||
double | 5 | ||
del(m,d)/decimal(m,d) | M+2 | 描述:最大值取值范围与DOUBLE相同,给定DECIMAL的有效取值范围由M和D决定 | |
bit(m) | 1~8 | bit(1) | bit(8) |
date | 4 | 1000-01-01 | 9999-12-31 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp | 4 | 19700101080001 | 2038年某个时刻 |
time | 3 | -838:59:59 | 838:59:59 |
year | 1 | 1901 | 2155 |
char(m) | 描述:m为0~255之间的整数 | ||
varchar(m) | 描述:为0~65535之间的整数,值的长度+1个字节 | ||
tinyblob | 描述:允许长度0~255字节,值的长度+1个字节 | ||
blob | 描述:允许长度0~65535字节,值的长度+2个字节 | ||
mediumblob | 描述:允许长度0~167772150字节,值的长度+3个字节 | ||
longblob | 描述:允许长度0~4294967295字节,值的长度+4个字节 | ||
tinytext | 描述:允许长度0~255字节,值的长度+1个字节 | ||
text | 描述:允许长度0~65535字节,值的长度+2个字节 | ||
mediumtext | 描述:允许长度0~167772150字节,值的长度+3个字节 | ||
longtext | 描述:允许长度0~4294967295字节,值的长度+4个字节 | ||
varbinary(m) | 描述:允许长度0~m个字节的变长字节字符串,值的长度+1个字节 | ||
binary(m) | 描述:允许长度0~m个字节的定长字节字符串 | ||
json | 描述:支持数组和对象 |
关于mysql的数据类型主要以上几种,一般我们创建表结构都是使用innodb引擎,后面我们会深入innodb引擎的知识。特别我们需要注意的是,为了获取更好的兼容性,建议使用utf8mb4字符集,主要是用来兼容四字节的unicode。
mysql在5.5.3版本之后增加了utf8mb4编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。其实,utf8mb4是utf8的超集,理论上原来使用utf8,然后将字符集修改为utf8mb4,也不会对已有的utf8编码读取产生任何问题。mysql支持的utf8编码最大字符长度为3字节,如果遇到4字节的宽字符就插入异常。
char 表示定长,长度固定,varchar表示变长,即长度可变。char如果插入的长度小于定义长度时,则用空格填充;varchar小于定义长度时,还是按实际长度存储,插入多长就存多长。
char的存取速度还是要比varchar要快得多,方便程序的存储与查找;但是char也为此付出的是空间的代价,因为其长度固定,所以会占据多余的空间,可谓是以空间换取时间效率。varchar则刚好相反,以时间换空间。
对 char 来说,最多能存放的字符个数 255,和编码无关。而 varchar 呢,最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。
innodb,myisam,memory,merge,archive,ndb innodb引擎,从mysql5.5版本之后,mysql的默认内置存储引擎已经是innodb了
什么是索引?
优点: 1)检索:可以提高数据检索的效率,降低数据库的IO成本,2)排序:通过索引列对数据进行排序,降低了CPU的消耗 缺点: 1)占磁盘空间,2)降低更新表的效率 下面我们看下不同的索引的含义和解释
事务:事务是逻辑上的一组操作,要么都执行,要么都不执行。
允许读取尚未提交的数据变更,最低的隔离级别,可能导致脏读、幻读或不可重复读。
允许读取并发事务已经提交的数据,可以避免脏读,可能导致幻读或不可重复读。
同一个事务下多次读取结果都是一致的,除非数据是被自己的事务所修改,可以避免脏读、不可重复读,但可能导致幻读。
脏读指的是读到了其他事务未提交的数据,未提交意味着这些数据可能会回滚,也就是可能最终不会存到数据库中,也就是不存在的数据。读到了并一定最终存在的数据,这就是脏读
一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。
一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,另一个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,操作前一个事务的用户会发现表中还有没有修改的数据行,就好象发生了幻觉一样。
START TARNSACTION |BEGIN:显式地开启一个事务。
COMMIT:提交事务,使得对数据库做的所有修改成为永久性。
ROLLBACK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
mysql的锁按照范围可以分为全局锁、表锁、行锁,其中行锁是由数据库引擎实现的,并不是所有的引擎都提供行锁。我这里只讲innodb引擎的锁。
mysql提供全局锁来对整个数据库实例加锁。
// 锁表
FLUSH TABLES WITH READ LOCK
// 解锁
unlock tables
上面语句一般都是用来备份的,当执行这条语句后,数据库所有打开的表都会被关闭,并且使用全局读锁锁定数据库的所有表,同时,其他线程的更新语句(增删改),数据定义语句(建表,修改表结构)和更新类的事务提交都会被阻塞。
mysql 8.0 以后,对于备份,mysql可以直接使用备份锁
// 加锁
LOCK INSTANCE FOR BACKUP
// 解锁
UNLOCK INSTANCE
备份锁范围更广,会阻止文件的创建,重命名,删除,包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及账户的管理都会被阻塞。当然这些操作对于内存临时表来说是可以执行的,为什么内存表不受这些限制呢?因为内存表不需要备份,所以也就没必要满足这些条件。
表级别锁分为两类,一类是元数据锁(Metadata Lock,MDL),一种是表锁。
表锁分为读锁和写锁,读锁不互斥,但是获取读锁不能写入数据,其他没有获取到读锁的session也是可以读取表的,所以读锁的目的就是限制表被写。
写锁被获取后可以对表进行读写,写锁是互斥的,一旦某个session获取到表的写锁,另外的session无法访问这个表,直到写锁被释放。
表的解锁可以使用unlock tables解锁,也可以客户端口自动解锁。
元数据锁(MDL) 不需要显式使用,在访问一个表的时候会被自动加上。这个特性需要MySQL5.5版本以上才会支持,当对一个表做增删改查的时候,该表会被加MDL读锁;当对表做结构变更的时候,加MDL写锁。
MDL锁有一些规则
共享锁能允许事务获取到锁后进行读操作,共享锁是不互斥的,一个事务获取到共享锁后,另外一个事务也可以获取共享锁,获取共享锁后不能进行写操作
排他锁允许事务获取到锁后进行更新一行或者删除某一行操作,排他锁顾名思义是互斥的,一个事务获取到排他锁后,其他事务不能获取到排他锁,直到这个锁被释放。
innodb支持多种粒度的锁,允许行锁和表锁共存,这里说的意向锁其实是一种表级别的锁,但是我把它放在行锁里面是因为它不会单独存在,它的出现肯定会伴随着行锁(共享锁或者排他锁),它主要的目的就是表示将要锁定表中的行或者正在锁定表中的行。意向锁的获取必须在行锁获取之前,也就是说获取共享锁之前必须先要获取共享意向锁,对于排他锁也是一样的道理。
是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的innodb。
多版本并发控制技术的英文全称是 Multiversion Concurrency Control,简称 MVCC。 多版本并发控制(MVCC) 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说,不管事务执行多长时间,事务内部看到的数据是不受其它事务影响的,根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。 多版本并发控制的思想是保存数据的历史版本,通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来,读取数据的时候不需要加锁也可以保证事务的隔离效果。 MVCC解决了哪些问题?
innodb 的MVCC是如何工作的? 事务版本号:每开启一个事务,我们都会从数据库中获得一个事务 ID(也就是事务版本号),这个事务 ID 是自增长的,通过 ID 大小,我们就可以判断事务的时间顺序。 行记录的隐藏列:innodb的叶子段存储了数据页,数据页中保存了行记录,而在行记录中有一些重要的隐藏字段:
undo log 将行记录快照保存在里面,我们可以在回滚段中找到它们。 在可重复读的隔离级别下: 查询:符合下面两个条件的记录作为返回结果:1)innodb只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。2)行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。 插入:innodb为新插入的每一行保存当前系统版本号作为行版本号。 删除:innodb为删除的每一行保存当前系统版本号作为行删除标识。删除在内部被视为更新,行中的一个特殊位会被设置为已删除。 更新:innodb为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。
什么是主从复制? mysql主从复制是指数据可以从一个mysql数据库服务器主节点复制到一个或者多个从节点。mysql默认使用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。 主从复制实现原理
基于gtid的复制模式 什么是gitd? gtid(Global Transaction ID)对于一个已提交事务的编号,并且是一个全局唯一的编号。GTID 实际上 是由UUID+TID组成的。其中UUID是一个mysql实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。 gtid的复制原理
总结:保证redo log和bin log可以持久化到磁盘,并且确保mysql在异常重启后进行数据恢复。 bin log的写入机制:
redo log的写入机制:
MRR,全称「Multi-Range Read Optimization」。 官方说法:MRR 通过把「随机磁盘读」,转化为「顺序磁盘读」,从而提高了索引查询的性能。 其实MRR是基于索引的查询做的一个优化,对于innodb,则会按照聚簇索引键值排好序,在内存上索引和磁盘上的索引存储也是有序的,通过顺序的读取聚簇索引。索引本身就是为了减少磁盘 IO,加快查询,而 MRR,则是把索引减少磁盘 IO 的作用,MRR在本质上是一种用空间换时间的算法。
分库分表主要解决IO瓶颈,CPU瓶颈。 分库分表:水平分库分表,垂直分库分表等 具体分库分表的方式经验是:日志类的拆分策略是按照日期,另外拆分策略就是hash法。
参考文献
《高性能mysql第三版》
《Mysql技术内幕 Innodb存储引擎》
https://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html