数据类型

时间类型

需要注意的是这里的TIMESTAMPDATETAME两种类型。

DATETAME

  • 占8个字节
  • 无时区检索
  • 两个该类型不可以相减,否则会把时间拼接成十进制直接相减,由于时间不是十进制,所以结果毫无意义
  • 当赋值为NULL,保存的内容就是NULL
  • 当插入无效值时,会保存 0000-00-00 00:00:00
  • 存储范围比TIMESTAMP更大一些

TIMESTAMP

  • 占4个字节
  • 存储的实际上是时间戳,所以可以直接相减获得秒数
  • 有时区差别,按照utc时间存储,如果储存时的时区和检索时的时区不一样,拿出来的数据也不一样
  • 当存储为NULL时,会保存为当前时间
  • 当插入无效值时,会保存 0000-00-00 00:00:00
  • 可以设置自动更新功能,详情见mysql的timestamp类型自动更新问题

Mysql索引

总的来说,Mysql索引是基于B+-Tree

B+Tree和B-Tree

见https://www.bilibili.com/video/BV1DE411i77d?t=1623

和https://www.bilibili.com/video/BV1n7411A7x3

两个视频,针对数据结构内容讲解的非常好。

为什么大多数的索引都基于这两种数据结构呢?主要原因是它们度很大,所以高度比较低。

对数据的索引操作来说,访问一个节点就需要一次IO操作,对于二叉查找树,它的度为2,所以高度会很大,一次查找就可能需要很多次IO操作才可以访问到想要的数据。而IO操作是需要访问磁盘的,时间花费会很大,如果访问失败惩罚也很大。而对于高度比较低的这两种数据结构,IO操作也会少很多。

另一方面,根据数据的局部性,磁盘会对接下来的数据进行预读放到内存之中。对于B树的一个节点来说,可以直接建一个页的大小,把内容都存在一个物理页之中,这样一次IO操作就可以读取到一整个节点。

这样的话,可以保证对B树的读取操作主要发生在内存之中。

Mysql为什么使用B±Tree

和B树相比较,B+树的内容全部存储在叶子节点之中,非叶子节点主要用来当作索引,同时,相连的叶子节点之间会使用链表相连,这样可以提高区间访问的性能。

比如说,我要查一下23-99之间的数据,只需要查一下23,再查一下99,然后串起来就ok了。如果要用B树,就会麻烦一些。

但是在特定场景下B树也是有优势的,B树的内容记录也在非叶子节点上,比如要查询单个数据,可能需要的IO数要比B+数少一些,如果没有区间查询的要求,B树也是不错的选择。

MySQL索引背后的数据结构及算法基础

一步步分析为什么B+树适合作为索引的结构 以及索引原理 (阿里面试)

MyISAM索引结构

MyISAM索引实现

看这个,讲的非常好了。

就是它叶子节点的Key对应的value其实是地址,指向一张表。

无论是主键的Key还是辅助索引的Key,所对应的都是地址,并没有本质的区别。

InnoDB索引结构

InnoDB索引实现

它的底层不一样,是聚集索引。

叶子节点对应的value直接就存一张表。

所以它要求表结构必须要有主键存在,如果没有mysql会选择适合作为主键的内容当作主键,如果没有适合当作主键的内容就会生成一个隐含的主键。

对于辅助索引,它的value就是主键,这样可以方便的定位到表,真不戳。

在了解了底层原理之后,可以分析出一些注意事项。

比如设置主键的时候最好选择自增的字段,然后不建议选择过长的字段作为主键(没听懂)

架构

连接层

最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关**的**安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会安全接入的每个客户端验证它所具有的数据权限。

服务层

第二层服务层,主要完成大部分的核心服务功能,包括查询解析、分析、优化、缓存、以及所有的内置函数,所有跨存储引擎的功能也都在这一层实现,包括触发器、存储过程、视图等。

引擎层

第三层存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,可以根据实际要求自己选取。

存储层

第四层为数据存储层,主要是将数据存储在运行于该设备的文件系统之上,并完成与存储引擎的交互

锁的分类

共享锁(读锁,S锁,Shared Lock)

若事务T对数据对象A加上S锁,则事务T只能读A;其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这就保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

排他锁(写锁,X锁,Exclusive Locks)

若事务T对数据对象A加上X锁,则只允许T读取和修改A,其它任何事务都不能再对A加任何类型的锁,直到T释放A上的锁。它防止任何其它事务获取资源上的锁,直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

行锁(Record Locks)

加在数据索引上的锁,而不是加在表上,InnoDb默认使用的就是行锁。

行锁会出现死锁,而表锁不会。行锁处理并发的能力强。

表锁

锁住一整张表,当索引失效或者where查询时没有索引的时候,行锁会退化成表锁,这里可以看下这个博客的演示:Mysql锁机制–索引失效导致行锁变表锁

MyISAM只有表锁。

Gap Locks和Next-Key Locks(都是间隙锁)

前者开区间,后者闭区间,会锁住两个索引之间的区域

这样组合起来就有,行级共享锁,表级共享锁,行级排它锁,表级排它锁。

意向锁(IX,IS)

如果对一个结点加意向锁,则说明该结点的下层结点正在被加锁;对任一结点加锁时,必须先对它的上层结点加意向锁。意向锁是放置在资源层次结构的一个级别上的锁,以保护较低级别资源上的共享或排它锁。

引进意向锁后,系统对某一数据对象加锁时不必逐个检查与下一级结点的封锁冲突了。

锁的互斥关系

当前读和快照读

当前读

读取记录的最新版本,同时保证其它的并发事务不能修改当前的记录,所以会对读取的记录进行加锁

select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)

快照读

像不加锁的select操作就是快照读,即不加锁的非阻塞读;快照读的前提是隔离级别不是串行级别串行级别下的快照读会退化成当前读;之所以出现快照读的情况,是基于提高并发性能的考虑,快照读的实现是基于多版本并发控制,即MVCC,可以认为MVCC是行锁的一个变种,但它在很多情况下,避免了加锁操作,降低了开销;既然是基于多版本,即快照读可能读到的并不一定是数据的最新版本,而有可能是之前的历史版本

MVCC

是啥

MVCC,全称Multi-Version Concurrency Control,即多版本并发控制。MVCC是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存。

说白了MVCC就是为了实现读-写冲突不加锁,而这个读指的就是快照读, 而非当前读,当前读实际上是一种加锁的操作,是悲观锁的实现

原理

MVCC如何实现数据库读已提交和可重复读这两种隔离级别?

太多了没怎么看,先占个坑。

隔离级别及其原理

下文的演示可以看这篇文章:MySQL事务(脏读、不可重复读、幻读)

虽然我自己也测试了一遍,但是截图太麻烦了~~(其实是我懒)~~

READ UNCOMMITTED(读未提交)

机制

写操作会加排他锁,读操作不会加锁,注意,排他锁会阻止其它事务再对其锁定的数据加读或写的锁,但是对不加锁的读就不起作用了。

阻塞情况

查-写:查不会阻塞写,即查的操作没有加锁。

写-写:被阻塞,说明写操作会加排他锁。

问题

会出现脏读,幻读不可重复读的问题。

脏读就是在并发下,一个事务读到了另一个事务未提交的记录。

这个机制会带来脏读的问题,一个事务进行写操作之后,假如没有提交,锁也就仍然存在,但是由于读不加共享锁,所以仍然可以被其它事务读到。

READ COMMITTED(读已提交)

大多数数据库的默认隔离级别,但不包括mysql

机制

写操作仍然会加排他锁,读操作仍然不加锁。和读未提交不一样的地方在于,读取数据使用了MVCC机制,通过提供历史版本从而实现读取被锁的数据的历史版本,从而避免了互斥等待。

因此,在读已提交的级别下,都会通过MVCC获取当前数据的最新快照,不加任何锁,也无视任何锁(因为历史数据是构造出来的,身上不可能有锁)。

阻塞情况

由于未新添加锁,和读提交相同。

问题

根据MVCC原理可以知道解决了脏读的问题,但是仍然会存在不可重复读和幻读的问题。

MVCC版本的生成时机: 是每次select时。这就意味着,如果我们在事务A中执行多次的select,在每次select之间有其他事务更新了我们读取的数据并提交了,那就出现了不可重复读,即:重复读时,会出现数据不一致问题

REPEATABLE READ(可重复读)

mysql的默认隔离级别。

机制

READ COMMITTED级别不同的是MVCC版本的生成时机,即:一次事务中只在第一次select时生成版本,后续的查询都是在这个版本上进行,从而实现了可重复读

问题

还会出现幻读的问题。

但是因为MVCC的快照只对读操作有效,对写操作无效,举例说明会更清晰一点: 事务A依次执行如下3条sql,事务B在语句1和2之间,插入10条age=20的记录,事务A就幻读了。

1
2
3
4
5
6
7

1. select count(1) from user where age=20;
-- return 0: 当前没有age=20的
2. update user set name=test where age=20;
-- Affects 10 rows: 因为事务B刚写入10条age=20的记录,而写操作是不受MVCC影响,能看到最新数据的,所以更新成功,而一旦操作成功,这些被操作的数据就会对当前事务可见
3. select count(1) from user where age=20;
-- return 10: 出现幻读

SERIALISABLE(可串行化)

机制

读使用当前读,即给读上共享锁,此时其它事务就无法进行写操作,完美解决幻读问题。

但是代价是牺牲了很多并行能力。

阻塞情况

仍然存在写-写阻塞,同时由于共享锁的特性,还会存在读-写阻塞。