Mysql数据库学习汇总
文章目录
事务的四大特性ACID
原子性(Atomicity): 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
一致性(Consistency): 执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;
隔离性(Isolation): 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;
持久性(Durability): 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
并发事务产生的问题
-
脏读
-
不可重复读
-
幻读
具体可以参考我的另外一篇博客
数据库事物的四种隔离级别
-
Read Uncommitted(读未提交)
在该隔离级别,所有事务都可以看到其他未提交事务的执行结果,会产生脏读的现象。
-
Read Committed(读已提交)
只允许事务读取已经被其他事务提交的变更,虽然避免了脏读,但是会产生不可重复读。
-
Repeatable Read(可重读),这是mysql的默认隔离级别
确保事务可以多次从一个字段中读取相同的值,在事务期间,不允许其他事务对这个字段进行修改。可以避免脏读和不可重复读,但是会有幻读问题。
-
Serializable(可串行化)
确保事务可以从一个表中读取相同的行,在事务期间,禁止其他事务对对该表执行删除,修改,添加操作。可以避免所有问题,但是性能底下。
存储引擎
查看本地mysql的存储引擎
使用指令show engines;
,下图是我本地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的区别:
- InnoDB支持事务,MyISAM不支持。
对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务; - InnoDB支持外键,而MyISAM不支持。
- InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
- InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。
- InnoDB不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;5.7以后的InnoDB支持全文索引了。
- InnoDB支持表、行级锁(默认),而MyISAM支持表级锁。
- InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而MyISAM可以没有。
- InnoDB存储文件有frm、ibd,而MyISAM是frm、MYD、MYI。
InnoDB:frm是表定义文件,ibd是数据文件,MyISAM:frm是表定义文件,MYD是数据文件,MYI是索引文件。
InnoDB记录存储结构
InnoDB采用的是分页模式,将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为 16KB。也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。
我们平时是以记录为单位来向表中插入数据的,这些记录在磁盘上的存放方式也被称为行格式
。现在基本上存在4中行格式,分别是Compact
、Redundant
、Dynamic
和Compressed
,就拿我现在使用的数据库为例,使用Dynamic
作为行格式。、
Compact行格式
我们可以看到,我们存进去的每一条记录的信息并不是那么简单,除了存进去的真实数据之外,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还会插入其他的内容,用于事务的处理
列名 | 是否必须 | 占用空间 | 描述 |
---|---|---|---|
row_id |
否 |
6 字节 |
行ID,唯一标识一条记录, 只有当不设置主键时才会创立 |
transaction_id |
是 |
6 字节 |
事务ID |
roll_pointer |
是 |
7 字节 |
回滚指针 |
完整的数据
还有一点要注意的是非可变长度类型,比如CHAR(10)等等,这个字段如果为null,那么就会出现在NULL值列表中,如果不为空,那么如果是10字节,那么就一定会占10字节空间,空位补空格(0x20)。
Redundant行格式
整体结构
同样的两条数据的显示,更多比较请查看原文
Dynamic行格式
关于行溢出
我们知道InnoDB按照页存储数据,而页的一般是16KB,也就是16384字节,但是mysql中一条数据存储的内容很可能高达60000+个字节,(比如一个VARCHAR(M)
类型的列就最多可以存储65532
个字节),那么就会出现在一页中无法存下一条数据的问题。 那么对于Compact
和Reduntant
这两种行格式,采用的策略类似于链表,就是每一页只存储部分数据,然后指定一个地址,表示接下来的内容到这个地址去取。
而Dynamic这种格式,就不会在记录的真实数据处存储字符串的前768
个字节,而是把所有的字节都存储到其他页面中,只在记录的真实数据处存储其他页面的地址。
Compressed行格式
Compressed
行格式和Dynamic
不同的一点是,Compressed
行格式会把存储到其他页面的数据采用压缩算法进行压缩,以节省空间。
索引
参考博客