MySQL

三大范式

  • 第一范式:数据库中的字段具有原子性,不可再分,并且是单一职责。
  • 第二范式:建立在第一范式的基础上,第二范式要求数据库中的每个实例或行必须可以被唯一的区分。为了实现区分通常需要为表加上一个列,以存储各个实例的唯一标识,这个唯一属性被称为主键。
  • 第三范式:建立在第二范式的基础上,确保每列都和主键列直接相关,而不是间接相关不存在其他表的非主键信息。

但是我们的日常开发中,并不是所有的表都满足三大范式,有时候冗余几个字段可以少关联几张表,带来的查询效率的提升有可能是质变的。

MyISAM和InnoDB区别

  • InnoDB支持事务,而MyISAM不支持。
  • InnoDB支持外键,而MyISAM不支持。
  • InnoDB是聚集索引,使用B+树作为索引结构,数据文件和索引是绑在一起的,必须要有主键,MyISAM是非聚集索引,也是使用B+树作为索引结构,索引和数据文件是分类的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  • InnoDB不保存表的具体行数,MyISAM用一个变量保存了整个表的行数。
  • InnoDB有redolog日志文件,而MyISAM没有。
  • InnoDB存储文件有frm,ibd,而MyISAM是frm,MYD,MYI。
    • InnoDB:frm是表定义文件,ibd是数据文件。
    • MyISAM:frm是表定义文件,myd是数据文件,myi是索引文件。
  • InnoDB支持表锁,行锁,而MyISAM之支持表级锁。
  • InnoDB必须有唯一索引,如果没有指定的话,InnoDB会自己生成一个隐藏列Row_id来充当默认主键,MyISAM可以没有。

为什么推荐使用自增ID作为主键

  • 普通索引的B+树上存放的是主键索引的值,如果该值较大,会导致普通索引的存储空间较大。
  • 使用自增ID做主键索引,新插入的数据只需要放在该页的最尾端就可以,直接按照顺序插入,不用可以维护。
  • 分页裂容易维护,当插入数据的当前页快满时,会发生分页裂的现象,如果主键索引不为自增id,那么数据就可以从页的中间插入,页的数据会频繁的变动,导致分页裂维护成本较高。

一条SQL是如何执行的

图片

  1. 通过连接器跟客户端建立连接
  2. 通过查询缓存查询之前是否有过该SQL
    • 有则直接返回结果
    • 没有则执行第3步
  3. 通过分析器分析该sql的语义是否正确,包括格式,表等。
  4. 通过优化器优化该语句,比如选择索引,join的连接顺序。
  5. 验证权限,验证是否有该表的查询权限
    • 没有则返回权限错误
    • 有则执行第6步
  6. 通过执行器调用存储引擎执行该SQL,然后返回执行结果。

使用InnoDB的情况下,一条更新语句是如何执行的

update T set c=c+1 where id=2; 
  1. 执行器先找引擎取id=2这一行,id是主键,引擎直接用树搜索找到这一行。
    • 如果id=2这一行所在的数据页本来就在内存中,就直接返回给执行器。
    • 如果不在内存从,就需要先从磁盘读入内存,然后再返回。
  2. 执行器拿到引擎给的行数据,把这个值+1,得到一行新的数据,再调用引擎接口,写入这行新的数据。
  3. 引擎将这行新数据更新到内存中,同时将这个更新操作记录的redolog里面,此时redolog处于prepare状态。然后告知执行器执行完成了,随时可以提交事务。
  4. 执行器生成这个操作的binlog,并把binlog写入磁盘。
  5. 执行器调用引擎的提交事务接口,引擎把刚刚写入的redolog改成提交commit状态,更新完成。

InnoDB事务为什么要分两阶段提交

  • 先写redolog后写binlog。假设在redolog写完,binlog还没有写完的时候,MySQL进程异常重启,这时候binlog里面就没有记录这个语句,然后你会发现,如果需要用这个binlog来恢复临时库的话,由于这个语句的binlog丢失,这个临时库就会少了这一次更新,恢复出来的这一行c的值为0,和原来的库的值不同。
  • 先写binlog后写redolog。如果binlog写完之后crash,由于redolog还没有写完,崩溃恢复之后这个事务无效,所以这一行c的值为0,但是binlog里面记录了把c变成1这个日志,如果使用binlog来恢复,就会多一个事务出来,恢复出来的这个c的值为1,与原库不同。

所以如果不使用两阶段提交,那么数据库的状态就可能和用它的日志恢复出来的库状态不一致。

什么是索引

索引相当于字典中的目录,可以极大的提高我们在数据库中的查询效率

索引失效的场景

为什么采用B+树而不是B-树

B+树只在叶子结点存储数据,非叶子结点不存具体的数据,只存key,查询更稳定,增大了广度,而一个结点就是磁盘的一个内存页,内存也大小固定,那么相比B树,B-树这些可以存更多的索引结点,宽度更大,树高矮,节点小,拉取以此数据的磁盘IO次数较少,并且B+树只需要取遍历叶子结点就可以实现整棵树的遍历。而且在数据库中基于范围的查询时非常频繁的,效率更高。

WAL是什么

WAL就是Write-Ahead Logging,其实就是所有的修改都先被写入到日志中,然后再写磁盘,用于保证数据操作的原子性和持久性。

好处:

  • 读和写可以完全并发执行,不会相互阻塞
  • 先写入log中,磁盘写入从随机写变为顺序写,降低了client端的延迟,并且由于顺序写大概率是在一个磁盘块内,这样产生的io次数也大大降低。
  • 写入日志当数据库崩溃的时候,可以用日志来恢复磁盘数据。

什么是回表

回表就是先通过数据库索引扫描出该索引树中数据所在的行,取到主键id,再通过主键id,取出主键索引中的数据,即基于非主键索引的查询需要多扫描一颗索引树。

什么是索引下推

如果存在某些被索引的列的判断条件时,MySQL将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

什么是覆盖索引

索引覆盖指一个查询语句的执行只用从索引库中就能够取得,不必从数据表中读取,可以减少回表的次数。比如:

select id from t where age = 1;

id为主键索引,age为普通索引,age这个索引树存储的就是主键信息,可以直接返回。

什么是最左前缀原则

最左前缀其实说的是,在where条件中出现的字段,如果只有组合索引中的部分列,则这部分列的触发索引顺序,是按照定义索引的时候的顺序从前到后触发,最左面一个列触发不了,之后的所有列索引都无法出发。

比如由一个(a,b,c)的组合索引:

where a=1 and b=1;

此时,a,b会命中该索引组合。

where a=1 and c=1

此时a会命中该索引组合,c不会。

where b=a and c=1

此时a,b不会命中该索引组合。

普通索引和唯一索引如何选择

  • 查询
    • 当普通索引为条件时,查询到数据会一直扫描,直到扫描完整张表。
    • 当唯一索引为查询条件时,查到该数据会直接返回,不会继续扫描表。
  • 更新:
    • 普通索引会直接将操作更新到cahnge buffer中,然后结束。
    • 唯一索引需要判断数据是否冲突。

所以唯一索引更加实话查询的场景,普通索引更加适合插入的场景。

什么是事务及时特性

事务时指程序中的一系列操作必须全部成功完成,有一个失败则全部失败。

  • 原子性:要么全部执行成功,要么全部不执行。
  • 一致性:事务前后的数据的完整性必须保持一致。
  • 隔离性:隔离性是当前多个事务同时触发时,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
  • 持久性:事务完成之后的改变是永久的。

事务的隔离级别

  • 读提交:能够读取到已经提交的数据。
  • 读未提交:能够读取到没有被提交的数据。
  • 可重复读:指的是在一个事务内,最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。
  • 可串行化:最高事务隔离级别,不管多少是位于,都是依次按序一个一个执行。
隔离级别 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读提交 不可能 可能 可能
可重复读 不可能 不可能 可能
串行化 不可能 不可能 不可能
  • 脏读:指的是读到了其他事务没有提交的数据,未提交意味着这些数据可能会回滚,也就是可能最终不会存到数据库中,也就是不存在的数据,读到了并不一定最终存在的数据,就是脏读。
  • 不可重复读:指的是在同一个事务内,不同的时刻读到的同一批数据可能是不一样的。
  • 幻读:幻读是针对数据插入操作来说的,假设事务A对某些行的内容作了更改,但是还未提交,此时事务B插入了与事务A更改前的记录相同的记录行,并且在事务A提交之前先提交了,而这是,在事务A中查询,会发现好像刚刚的更改对于某些数据未起作用,但其实是事务B刚插入进来的。

binlog

binlog是归档日志,属于Server层的日志,是一个二进制格式的文件,用于记录用户对数据库更新的SQL语句信息。

主要作用:

  • 主从复制
  • 数据恢复

undolog

undolog是InnoDB存储引擎的日志,用于保证数据的原子性,保存了事务发生前后的数据的一个版本,也就是说记录的是数据是修改之前的数据,可以用于回滚,同时可以提供多版本并发控制下的读(MVCC)。

主要作用:

  • 事务回滚
  • 实现多版本控制(MVCC)

relaylog

relaylog是中继日志,在主从同步的时候使用,它是一个中介临时的日志文件,用于存储从master结点同步过来的binlog日志内容。

master主节点的binlog传输到slave结点后,被写入relaylog中,salve结点的slavesql线程从relaylog中读取日志,然后应用到slave节点本地。slave服务器IO线程将master服务器的binlog读取过来,记录到slave服务器本地文件,然后sql线程会读取relaylog日志将内容应用到slave服务器,从而使slave和master数据保持一致。

redolog

redolog是InnoDB存储引擎所特有的一种日志,用于记录事务操作的变化,记录的是数据修改之后的指,不管事务是否提交都会记录下来。

可以做数据恢复并且提供crash-safe能力。

当有增删改相关的操作时,会先记录到InnoDB中,并修改缓存页中的数据,等到mysql闲下来的时候,才会真正的将redolog中的数据写入到当前磁盘中。

redolog是如何记录日志的

InnoDB的redolog是固定大小的,比如可以配置为一组4个文件,每个文件的大小是1G,那么总共就可以记录4G的操作,从头开始写,写到末尾就又回到开头循环写。

所以,如果数据写满了但是还没有来得及将数据写入到磁盘当中,就会发生内存抖动现象,从肉眼的角度观察会发现MySQL宕机了一会,此时就是正在刷盘了。

redolog和binlog区别

  • redolog是InnoDB独有的日常,而binlog是server层的,所有的存储引擎都有用到。
  • redolog记录了具体的数值,对某个页做了什么修改,binlog记录的是操作内容。
  • binlog大小达到上限或者flush log会产生一个新的文件,而redolog有固定的大小,只能循环利用。
  • binlog日志没有crash-safe的能力,只能用于归档,而redolog有crash-safe能力。

MVCC的作用

MVCC:多版本并发控制,是现代数据库引擎视线中常用的处理读写冲突的手段,目的在于提高数据库高并发场景下的吞吐性能。

在MVCC协议下,每个读操作会看到一个一致性的快照,这个快照是基于整个库的,并且可以实现非阻塞的读,用于支持读提交和可重复读隔离级别的实现。

MVCC允许数据具有多个版本,这个版本可以是时间戳或者是全局递增的事务ID,在同一个时间点,不同的事物看到的数据是不同的,这个修改的数据是记录在undolog中的。

SQL慢查询可能原因

  • 没有用到索引:比如函数导致的索引失效,或者本身就没有加索引。
  • 表数据库量太大:考虑分库分表
  • 优化器选错了索引:使用force index强制走索引

SQL查询偶尔慢的可能原因

  • 数据库在刷新脏页,比如redolog写满了,内存不够用了,释放内存如果是脏页也需要刷新,MySQL正常空闲状态刷脏页。
  • 没有拿到锁。

MySQL主从之间如何同步数据

  1. master主库将此次更新的事件类型写入到主库的binlog日志中。
  2. master创建log dump线程通知slave需要更新数据。
  3. slave向master节点发送请求,将该binlog文件内容存到本地的relaylog中。
  4. slave开启sql线程,读取relaylog中的内容,将其中的内容在本地重放一遍,完成主从的同步。

有两种同步策略:

  • 全同步复制:主库强制同步日志的从库,等全部从库执行完才返回客户端,性能差。
  • 半同步复制:主库收到至少一个从库确认就认为操作成功,从库写入日志成功返回ACK确认。

主从延迟如何解决

  • MySQL 5.6版本之后,提供了并行复制的方式,通过将SQL线程转换成多个work线程来进行重放。
  • 提高机器配置。
  • 在业务初期就选择合适的分库,分表策略,避免单表单库过大,带来的额外的复制压力。
  • 避免长事务。
  • 避免让数据库进行各种大量的运算。
  • 对于一些对延迟敏感的业务,直接使用主库读。

删除表数据后表的大小却没有变动是为什么

在使用delete删除数据时,其实对应的数据行并没吧真正的删除,而是逻辑删除,InnoDB仅仅是将其标记为可复用的状态,所以表空间不会变小。

为什么varchar建议不超过255

当定义varchar长度小于等于255时,长度标识需要一个字节,utf-8编码。

当大于255时,长度标识需要两个字节,并且建立的索引也会失效。

分布式事务怎么实现

  • 本地消息表
  • 消息事务
  • 二阶段提交
  • 三阶段提交
  • TCC
  • 最大努力通知
  • Seata框架

MySQL有哪些锁

属性分类:共享锁,排他锁

细粒度分类:表锁,行锁,记录锁,间隙锁,临键锁

状态分类:意向共享锁,意向排他锁,死锁

为什么不能使用长事务

  • 并发情况下,数据库连接池容易被撑爆。
  • 容易造成大量的阻塞和锁超时,长事务还占用锁资源,也可能拖垮整个库。
  • 执行时间长,容易造成主从延迟。
  • 回滚需要的时间比较长,事务越长整个时间段内的事务也就越多。
  • undolog日志越来越大,长事务意味着系统里面会存在很多的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。

buffer pool是做什么的

buffer pool是一块内存区域,为了提高数据库的性能,当数据库操作数据的时候,把硬盘上的数据加载在buffer pool,不直接和硬盘打交道,操作的是buffer pool里面的数据,数据库的增上改查都是在buffer pool中进行的。

buffer pool里面缓存的数据内容也是一个个数据页,其中有三大双向链表:

  • free链表:用于帮助我们找到空闲的缓存页。
  • flush链表:用于找到脏缓存页,也就是需要刷盘的缓存页。
  • lru链表:用来淘汰不太经常被访问的缓存页,分为热数据区和冷数据区,冷数据区主要存放那些不常被用到的数据。

预读机制:

Buffer Pool有一个特性就预读,存储引擎的接口在被Server层调用时,会在响应的同时进行预判,将下次可能用到的数据和索引加载到Buffer Pool。

SQL优化思路

  • 表结构优化
    • 拆分字段
    • 字段类型的选择
    • 字段类型大小的限制
    • 合理的增加冗余字段
    • 新建字段一定要有默认值
  • 索引优化
    • 索引字段的选择
    • 利用好MySQL支持的索引下推,覆盖索引等功能
    • 唯一索引和普通索引的选择
  • 查询语句优化
    • 避免索引失效
    • 合理的书写where条件字段顺序
    • 小表驱动大表
    • 可以使用force index防止优化器选错索引
  • 分库分表