数据库学习笔记
文章目录
本文部分内容出自Java全栈知识点汇总(https://pdai.tech/md/interview/x-interview.html)
一.MYSQL引擎
MyISAM和InnoDB的对比
InnoDB默认可以创建16个索引加粗样式
- InnoDB支持事务,MyIsam不支持事务,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放到begin 和 commit之间,组成一个事务;
- InnoDB支持外键,而MyIsam不支持,对一个包含外键的InnoDB表转成MyIsam表会失败
- InnoDB是聚集索引,数据文件和索引绑定在一块,必须要有主键,通过主键索引效率很高,但是辅助索引需要两次查询,先查询到主键,然后在通过主键查询到数据。因此主键不应该过大。主键过大的时候,其它索引也会很大。而MyIsam是非聚集索引,数据和文件是分离的,索引保存的是数据文件的指针,主键索引和辅助索引是独立的。
- InnoDB不支持全文检索,而MyIsam支持全文检索,查询效率上MyIsam要高
索引类型
主键索引(Primary Key)
数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键,并且主键不能为 null,不能重复。
在 MySQL 的 InnoDB 的表中,当没有显示的指定表的主键时,InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段,如果有,则选择该字段为默认的主键,否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
二级索引(辅助索引)
二级索引又称为辅助索引,是因为二级索引的叶子节点存储的数据是主键。也就是说,通过二级索引,可以定位主键的位置。
唯一索引,普通索引,前缀索引等索引属于二级索引。
PS:不懂的同学可以暂存疑,慢慢往下看,后面会有答案的,也可以自行搜索。
- 唯一索引(Unique Key) :唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。
- 普通索引(Index) :普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。
- 前缀索引(Prefix) :前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小, 因为只取前几个字符。
- 全文索引(Full Text) :全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引,5.6 之后 InnoDB 也支持了全文索引。
聚集索引与非聚集索引
#聚集索引
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
在 MySQL 中,InnoDB 引擎的表的 .ibd
文件就包含了该表的索引和数据,对于 InnoDB 引擎表来说,该表的索引(B+树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据。
#聚集索引的优点
聚集索引的查询速度非常的快,因为整个 B+树本身就是一颗多叉平衡树,叶子节点也都是有序的,定位到索引的节点,就相当于定位到了数据。
#聚集索引的缺点
- 依赖于有序的数据 :因为 B+树是多路平衡树,如果索引的数据不是有序的,那么就需要在插入时排序,如果数据是整型还好,否则类似于字符串或 UUID 这种又长又难比较的数据,插入或查找的速度肯定比较慢。
- 更新代价大 : 如果对索引列的数据被修改时,那么对应的索引也将会被修改,而且聚集索引的叶子节点还存放着数据,修改代价肯定是较大的,所以对于主键索引来说,主键一般都是不可被修改的。
#非聚集索引
非聚集索引即索引结构和数据分开存放的索引。
二级索引属于非聚集索引。
非聚集索引的叶子节点并不一定存放数据的指针,因为二级索引的叶子节点就存放的是主键,根据主键再回表查数据。
#非聚集索引的优点
更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了,非聚集索引的叶子节点是不存放数据的
#非聚集索引的缺点
- 跟聚集索引一样,非聚集索引也依赖于有序的数据
- 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。
覆盖索引
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中,如果不是主键索引,叶子节点存储的是主键+列值。最终还是要“回表”,也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的,不做回表操作!
覆盖索引即需要查询的字段正好是索引的字段,那么直接根据该索引,就可以查到数据了,而无需回表查询。
如主键索引,如果一条 SQL 需要查询主键,那么正好根据主键索引就可以查到主键。
再如普通索引,如果一条 SQL 需要查询 name,name 字段正好有索引, 那么直接根据这个索引就可以查到数据,也无需回表。
事务隔离级别总结
!
InnoDB 对 MVCC 的实现
undo-log
undo log
主要有两个作用:
- 当事务回滚时用于将数据恢复到修改前的样子
- 另一个作用是
MVCC
,当读取记录时,若该记录被其他事务占用或当前版本对该事务不可见,则可以通过undo log
读取之前的版本数据,以此实现非锁定读
在 InnoDB
存储引擎中 undo log
分为两种: insert undo log
和 update undo log
:
insert undo log
:指在insert
操作中产生的undo log
。因为insert
操作的记录只对事务本身可见,对其他事务不可见,故该undo log
可以在事务提交后直接删除。不需要进行purge
操作
事务基本特性