Mysql数据库学习汇总

事务的四大特性ACID

原子性(Atomicity): 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;

一致性(Consistency): 执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;

隔离性(Isolation): 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;

持久性(Durability): 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。

并发事务产生的问题

  • 脏读

  • 不可重复读

  • 幻读

具体可以参考我的另外一篇博客

数据库事物的四种隔离级别

  • Read Uncommitted(读未提交)

    在该隔离级别,所有事务都可以看到其他未提交事务的执行结果,会产生脏读的现象。

  • Read Committed(读已提交)

    只允许事务读取已经被其他事务提交的变更,虽然避免了脏读,但是会产生不可重复读。

  • Repeatable Read(可重读),这是mysql的默认隔离级别

    确保事务可以多次从一个字段中读取相同的值,在事务期间,不允许其他事务对这个字段进行修改。可以避免脏读和不可重复读,但是会有幻读问题。

  • Serializable(可串行化)

    确保事务可以从一个表中读取相同的行,在事务期间,禁止其他事务对对该表执行删除,修改,添加操作。可以避免所有问题,但是性能底下。

存储引擎

查看本地mysql的存储引擎

使用指令show engines;,下图是我本地mysql的情况

Mysql数据库学习汇总

关于上图几个参数的解释

Engine参数指存储引擎名称;
Support参数说明MySQL是否支持该类引擎,YES表示支持;
Comment参数指对该引擎的评论;
Transactions 参数表示是否支持事务处理,YES表示支持;
XA参数表示是否分布式交易处理XA规范,YES表示支持;
Savepoints参数表示是否支持保存点,以便事务回滚到保存点,YES表示支持

一般常用的就两种,InnoDB和MyISAM

MyISAM :默认表类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,而且不支持外键,如果执行大量的select,insert MyISAM比较适合。

InnoDB :支持事务安全的引擎,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。注: 在MySQL 5.5之前的版本中,默认的搜索引擎是MyISAM,从MySQL 5.5之后的版本中,默认的搜索引擎变更为InnoDB。

MyISAM和InnoDB的区别:

  1. InnoDB支持事务,MyISAM不支持。
    对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;
  2. InnoDB支持外键,而MyISAM不支持。
  3. InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  4. InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。
  5. InnoDB不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;5.7以后的InnoDB支持全文索引了。
  6. InnoDB支持表、行级锁(默认),而MyISAM支持表级锁。
  7. InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而MyISAM可以没有。
  8. InnoDB存储文件有frm、ibd,而MyISAM是frm、MYD、MYI。

InnoDB:frm是表定义文件,ibd是数据文件,MyISAM:frm是表定义文件,MYD是数据文件,MYI是索引文件。

InnoDB记录存储结构

大神博客

InnoDB采用的是分页模式,将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为 16KB。也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。

我们平时是以记录为单位来向表中插入数据的,这些记录在磁盘上的存放方式也被称为行格式。现在基本上存在4中行格式,分别是CompactRedundantDynamicCompressed,就拿我现在使用的数据库为例,使用Dynamic作为行格式。、

Mysql数据库学习汇总

Compact行格式

Mysql数据库学习汇总

我们可以看到,我们存进去的每一条记录的信息并不是那么简单,除了存进去的真实数据之外,mysql还会往记录中添加一些额外的信息。

变长字段长度列表

针对VARCHAR(M)这类的可变数据类型,我们必须知道所占的长度,不然在记录的真实数据我们怎么知道哪些数据是属于自己的呢。

注意的是,这里只存储变长且为非null的字段

下面是大神的例子,创建一个表,并添加数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e3cit4B2-1584260905633)(https://i.loli.net/2020/03/15/BKRDW9kHvSLVUts.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-izgPzcba-1584260905639)(https://i.loli.net/2020/03/15/KeZH5C6YyvislDu.png)]

那么对可变长数据分析如下

列名 存储内容 内容长度(十进制表示) 内容长度(十六进制表示)
c1 'aaaa' 4 0x04
c2 'bbb' 3 0x03
c4 'd' 1 0x01

最后存储的就是内容长度,因为要求必须逆序放置,所以040301就要变成010304

另外原文中还有关于是使用1个字节还有2个字节比埃是内容长度的篇幅,可以区阅读。

NULL值列表

为了优化空间,所以设立NULL值列表以表示一条记录中哪些字段是null。

注意的是,这里只统计所有列的值允许为NULL的情况

还是刚才表的例子,有c1,c3,c4的列是允许为null的,前面添0是因为长度要是以字节为单位,而且还是和前面一样需要逆序存储

Mysql数据库学习汇总
Mysql数据库学习汇总

记录头信息

存储一些记录数据需要的内容,更多细节可以查看原文

Mysql数据库学习汇总

记录的真实数据

除了我们插入的列数据以外,mysql还会插入其他的内容,用于事务的处理

列名 是否必须 占用空间 描述
row_id 6字节 行ID,唯一标识一条记录,
只有当不设置主键时才会创立
transaction_id 6字节 事务ID
roll_pointer 7字节 回滚指针

完整的数据

Mysql数据库学习汇总

还有一点要注意的是非可变长度类型,比如CHAR(10)等等,这个字段如果为null,那么就会出现在NULL值列表中,如果不为空,那么如果是10字节,那么就一定会占10字节空间,空位补空格(0x20)。

Redundant行格式

整体结构

Mysql数据库学习汇总

同样的两条数据的显示,更多比较请查看原文

Mysql数据库学习汇总

Dynamic行格式

关于行溢出

我们知道InnoDB按照页存储数据,而页的一般是16KB,也就是16384字节,但是mysql中一条数据存储的内容很可能高达60000+个字节,(比如一个VARCHAR(M)类型的列就最多可以存储65532个字节),那么就会出现在一页中无法存下一条数据的问题。 那么对于CompactReduntant这两种行格式,采用的策略类似于链表,就是每一页只存储部分数据,然后指定一个地址,表示接下来的内容到这个地址去取。

Mysql数据库学习汇总

而Dynamic这种格式,就不会在记录的真实数据处存储字符串的前768个字节,而是把所有的字节都存储到其他页面中,只在记录的真实数据处存储其他页面的地址。

Mysql数据库学习汇总

Compressed行格式

Compressed行格式和Dynamic不同的一点是,Compressed行格式会把存储到其他页面的数据采用压缩算法进行压缩,以节省空间。

索引

参考博客