1、MyISAM与InnoDB的区别
物理文件上的区别
InnoDB:
- InnoDB在数据表库中,一个表只有一个xxx.frm文件(存储表结构),以及上级目录下的ibdata1(用来储存文件的数据)
MyISAM:
- *.frm:表结构定义文件
- *.MYD:存储数据的文件
- *.MYI:索引文件
下表:
MyISAM | InnoDB | |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
自带统表使用 | Y | N |
关注点 | 性能:节省资源、消耗少、简单业务 | 事务:并发写、事务、更大资源 |
默认安装 | Y | Y |
默认使用 | N | Y |
关于锁机制:
- MyISAM:支持表级锁。
- InnoDB:支持行级别和行级锁,默认为行级锁。
总结:
MysQL5.5之前的默认存储引擎是MylSAM,5.5之后改为了InnoDB。首先对于InnoDB存储引擎,提供
了良好的事务管理、崩溃修复能力和并发控制。
因为InnoDB存储引擎支持事务,所以对于要求事务完整性的场合需要选择InnoDB,比如数据操作除了插入和查询以外还包含有很多更新、删除操作,像财务系统等对数据准确性要求较高的系统。缺点是其读写效率稍差,占用的数据空间相对比较大。
其次对于MyISAM存储引擎,如果是小型应用,系统以读操作和插入操作为主,只有很少的更新、删除操作,并且对事务的要求没有那么高,则可以选择这个存储引擎。MyISAM存储引擎的优势在于占用空间小,处理速度快;缺点是不支持事务的完整性和并发性。这两种引擎各有特点,当然你也可以在MySQL中,针对不同的数据表,可以选择不同的存储引擎。
1、连表查询
- 左连:
基表 left join 连接表 on 连接条件 [where 筛选条件]
先从左表中查询全部数据,再从右表中查询对应的,如果右表中没有对应的,则右表字段会显示为null(相当于左表的数据全部查询了出来,再去右表找对应的,没有则对应nul) - 右连:
连接表 right join 基表 on 连接条件 [where 筛选条件]
同理,先从右表中查询全部数据,再从左表中查询对应的,如果左表中没有对应的,则左表字段显示为null…(相当于把右表全部查询了出来,去左表找对应了,没有则对应为null) - 内联:
基表 inner join 连接表 on 连接条件 [where 筛选条件]
左表与右表向匹配,才返回行
以下语句用了内连接
select customer.id, nickname, customer.name, money,product.id, product.name, product.price, sex from customer inner join product on customer.nickname = product.seller
2、子查询
略
3、事务的特征:ACID原则
原子性、一致性、隔离性、持久性 (会产生的问题:脏读、幻读…)
- 原子性(Atomicity):针对同一个事务,要么都完成,要么都不完成。
- 一致性(Consistency):针对一个事务,前后的数据完整性要保证一致。
- 隔离性(Isolation):多个用户并发访问数据库时,数据库为每个用户开启一个事务,事务之间的操作数据不能相互干扰,所以事务之间要隔离。
- 持久性(Durability):事务没有提交,数据应该恢复到原状;如果数据提交,就应该持久化到数据库。
事务的隔离级别:
- 读未提交(
Read uncommitted
):一个事务读取了另一个未提交事务的数据,可能导致脏读
。 - 读已提交(
Read committed
):为了解决脏读,一个事务读取时应该在另一个事务提交后才能读取。但是,当读事务
在写事务
开启前读取一次,然后读事务
在写事务
提交事务后再读取一次,这两次读取的结果可能不一致,就是不可重复读
的问题。 - 可重复读(
Repeatable read
):为了解决不可重复读问题,就要在读事务
开启时,不再允许修改数据(update
操作),称之为可重复读。但是,如果其他事务进行insert操作,可能会导致幻读
。 - 可串行化(
Serializable
) :最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读
。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。
隔离所导致的一些问题(事务的隔离级别可以对应解决):
- 脏读:一个事务读取了另一个事务未提交的数据。
- 不可重复度:在一个事务内读取表中某一行,多次读取结果不同。(这个不一定错误,只是某些场合不对)
- 虚度(幻读):在一个事务中,读取到了别的事务插入的数据,导致前后数据不一致。
4、索引
4.1 索引背后的数据结构原理
参考文章:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
在InnoDB中
聚簇索引:
- 优点
- 数据访问更快 ,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
- 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
- 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的io操作 。
- 缺点
插入速度严重依赖于插入顺序
,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
更新主键的代价很高
,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新二级索引访问需要两次索引查找
,第一次找到主键值,第二次根据主键值找到行数据
非聚簇索引(二级索引):
在二级索引中存储主键,查到之后再回表查找具体数据
小结:
- 聚簇索引的
叶子节点
存储的就是我们的数据记录
, 非聚簇索引的叶子节点存储的是数据位置
。非聚簇索引不会影响数据表的物理存储顺序。 - 一个表
只能有一个聚簇索引
,因为只能有一种排序存储的方式,但可以有多个非聚簇索引
,也就是多个索引目录提供数据检索。 - 使用聚簇索引的时候,数据的
查询效率高
,但如果对数据进行插入,删除,更新等操作,效率会比非聚簇索引低。
联合索引:
- 联合索引其实是一种非聚簇索引
4.2 MyISAM中的索引方案
- MyISAM引擎使用 B+Tree 作为索引结构,叶子节点的data域存放的是 数据记录的地址 。
- MyISAM中都是非聚簇索引,没有聚簇索引
MyISAM 与 InnoDB对比
-
在InnoDB存储引擎中,我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录,而在 MyISAM 中却需要进行一次 回表 操作,意味着MyISAM中建立的索引相当于全部都是 二级索引 。
-
InnoDB的数据文件本身就是索引文件,而MyISAM索引文件和数据文件是 分离的 ,索引文件仅保存数 据记录的地址。
-
InnoDB的非聚簇索引data域存储相应记录 主键的值 ,而MyISAM索引记录的是 地址 。换句话说, InnoDB的所有非聚簇索引都引用主键作为data域。
-
MyISAM的回表操作是十分 快速 的,因为是拿着地址偏移量直接到文件中取数据的,反观InnoDB是通 过获取主键之后再去聚簇索引里找记录,虽然说也不慢,但还是比不上直接用地址去访问。
-
InnoDB要求表 必须有主键 ( MyISAM可以没有 )。如果没有显式指定,则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列,则MySQL自动为InnoDB表生成一个隐 含字段作为主键,这个字段长度为6个字节,类型为长整型。
4.3 MySQL数据结构选择的合理性
4.3.1 全表查询
4.3.2. Hash查询
4.3.3. 二叉搜索树
4.3.4. AVL树
4.3.5. B-Tree
4.3.6. B+Tree
B+树也是一种多路搜索树,基于B树做出的改进,主流的DBMS都B+树的索引方式,比如MySQL,相比于B-Tree,B+Tree适合文件索引系统。
B+ 树和 B 树的差异?
-
有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数,而 B 树中,孩子数量 = 关键字数 +1。
-
B+树中非叶子节点的关键字也会同时存在在子节点中,并且是在子节点中所有关键字的最大(或最 小)。所以B+数会重复。
-
非叶子节点仅用于索引,不保存数据记录,跟记录有关的信息都放在叶子节点中。而 B 树中, 非 叶子节点既保存索引,也保存数据记录 。
-
B+树中所有关键字都在叶子节点出现,叶子节点有一个指针指向下一个节点,构成一个有序链表,而且叶子节点本身按照关键字的大小,从小到大顺序链接。
-
B树的节点中没有重复元素,B+树有(因为B树的节点是储存信息的)。
-
B树的中间节点会存储数据指针信息,而B+树只有叶子节点才存储。
-
B+树最大的区别就是所有的数据都是存储在叶子节点上的,而非叶子节点中存储的都是数据索引。并且所有的叶子结点再连接成一个链表!
B树例子
B+例子:
思考题:为了减少IO,索引树会一次性加载吗?
思考题:B+树的存储能力如何?为何说一般查找行记录,最多只需1~3次磁盘IO
思考题:为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引?
思考题:Hash 索引与 B+ 树索引的区别
思考题:Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗?
4.3.7. R树
4.3.8.
索引分类
- 主键索引(PRIMARY KEY):唯一标识,主键不能重复,主键只有一列。
- 唯一索引(UNIQUE KEY): 避免重复的列出现;唯一索引可以重复,因为多个列都可以标识为唯一索引。
- 常规索引(KEY / INDEX):默认的。KEY或者INDEX关键字来设置。
- 全文索引(FULLTEXT):在特定的数据库引擎下才有(MYISAM)。全文索引可以快速定位数据。
创建索引:create index 索引名 on 表(字段);
总结:索引在数据量小的时候没什么区别,数据量大的时候可以大大提升查询速度。
索引原则
- 索引不是越多越好。
- 不要对经常变动的数据添加索引。
- 小数据量的表不用添加索引。
- 索引一般加在常用来查询的字段上。
5、数据库三大范式
三大范式主要规范数据库的设计。
- 第一范式:数据库的每一列都是不可拆分的原子数据项。
- 第二范式:满足第一范式为前提下,属性完全依赖于主键,不能只与主键的某一部分相关(主要针对联合主键而言)。
- 第三范式:满足第二范式为前提,非主键列只依赖于主键,不依赖于其他非主键。
某些时候,三大设计范式的规范可能会影响性能问题,因为三大范式需要拆分表。所以某些时候可能会给表增加一些冗余字段。
阿里的规范是:关联查询的表不能超过三张表。
6、删除
TRUNCATE:将表删除,然后再重新创建一个。
delete :一条一条的删除。
7、约束
-
主键约束(
primary key
):该字段的数据不能重复。 -
自增约束(
auto_increment
):针对int型的主键,不用手动给值,自动给值递增,给的值是当前这个列的最大值+1。
特性:
①:只有整型才能自增长。
②:只给主键加自增长。
③:自增长的列一般不给值。
④:初始值是1,增量是1(自增值与初始值可以自己设置)。
⑤:默认的存储引擎下,自增长的数据一旦使用就不会再出现。MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力(将值存在redolog中),也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”,具体情况是:自增修改机制:
①: 如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;
②:如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。
③:根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。
- 假设,某次要插入的值是 X,当前的自增值是 Y:
- 如果 X<Y,那么这个表的自增值不变;
- 如果 X≥Y,就需要把当前自增值修改为新的自增值。导致自增值不连续的原因:
①:唯一键冲突(插入的时候就会报错
Duplicate entry
,虽然数据没有入库,但是下一次插入数据的时候主键会+1)。具体流程:发现用户没有指定自增id的值,获取表的自增值,给传入的指定获取的值,执行插入操作。②:事务回滚:(回滚的时候不会回滚自增id,因为如果回滚自增id的话会出现性能问题)。
③:批量写操作:
insert ······ select ······
此类的语句,MySQL不知道要申请多少自增id,所以就采用批量申请的策略(每申请到的自增id数是上一次的两倍)。 -
非空约束(
not null
):该列数据不能为空。 -
唯一约束(
unique
): 该列的值不能重复。可以不给值,不给值为空。 -
默认约束(
default
):不给值的情况下用默认值填充,给了值的情况则用给的值填充。 -
外键约束(
foreign key
):表与表之间的关系。
8、触发器
1语法:
CREATE TRIGGER 触发器名 触发时机 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表;
END
记得使用delimiter $
定义结束标志。
例:
DELIMITER $
CREATE TRIGGER tri2 AFTER DELETE
ON tb_class FOR EACH ROW
BEGIN
INSERT INTO test VALUE(NOW(),CONCAT('删除了一个', old.cname));
INSERT INTO tri_student VALUE(NOW(),CONCAT('删除了一个', old.cname));
END$
以上例子为在tb_class
表中进行delete
操作之后触发。
MySQL 中定义了 NEW 和 OLD关键字,用来表示触发器的所在表中,触发器所操作的那一行数据的内容,new和old来引用触发器中发生变化的那一条数据,具体地:
- 在INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
- 在UPDATE型触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据;
- 在DELETE型触发器中,OLD用来表示将要或已经被删除的原数据;
9、存储过程
存储过程类似于函数
定义语法:
CREATE PROCEDURE sp_name (参数类型 参数名 数据类型 ,...)
BEGIN
SQL语句集合;
END
参数类型可以定义为三种:[ IN | OUT | INOUT ]。
批量删除时:334267条数据
经测试where in比用for循环一条一条的删要快。2022-7-6 20:49:08
平时总结
1. 执行顺序
2. 函数
-
IFNULL(expr1,expr2)
假如expr1不为null,则返回expr1,否则返回expr2 -
IF(expr1,expr2,expr3)
如果expr1为TRUE,则IF()返回值为expr2,否则返回值为expr3 -
count(*)
和count(1)
和count(列名)
的执行效果区别
10、InnoDB数据存储结构
参考尚硅谷宋红康老师
https://www.bilibili.com/video/BV1iq4y1u7vj?p=121&spm_id_from=pageDriver&vd_source=0efb29cccf699117b860f026f17b4657
10.1. 数据库的存储结构:页
-
概述
页
作为磁盘和内存之间交互的基本单位
,也就是一次最少从磁盘中读取16KB的内容到内存中,一次最少把内存中的16KB内容刷新到磁盘中。也就是说,**在数据库中,不论读一行,还是读多行,都是将这些行所在的页进行加载。也就是说,数据库管理存储空间的基本单位是页(Page),数据库I/O操作的最小单位是页。一个页中可以存储多个行记录 -
页的大小
InnoDB将数据划分为若干个页,InnoDB中页的大小默认为 16KB
show variables like '%innodb_page_size%';
可以查看 -
页的上层结构
在数据库中,还存在区(Extent)、段(Segment)和表空间(Tablespace)的概念。行、页、区、段、表空间的关系如下图所示:
- 区(Extent)是比页大一级的存储结构,在InnoDB存储引擎中,一个区会分配
64个连续的页
。因为InnoDB中的页大小默认是16KB,所以一个区的大小是64*16KB= 1MB
。 - 段(Segment)由一个或多个区组成,区在文件系统是一个连续分配的空间(在InnoDB中是连续的64个页),不过在段中不要求区与区之间是相邻的。
段是数据库中的分配单位,不同类型的数据库对象以不同的段形式存在。
当创建数据表、索引的时候,就会相应创建对应的段,比如创建一张表时会创建一个表段,创建一个索引时会创建一个索引段。 - 表空间(Tablespace)是一个逻辑容器,表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间。数据库由一个或多个表空间组成,表空间从管理上可以划分为系统表空间,
用户表空间
、撤销表空间
、临时表空间
等。
- 区(Extent)是比页大一级的存储结构,在InnoDB存储引擎中,一个区会分配
10.2 页的内部结构
页如果按类型划分的话,常见的有数据页(保存B+树节点)
、系统页
、Undo页
和事务数据页
等。数据页是我们最常使用的页。
数据页的16KB
大小的存储空间被划分为七个部分,分别是文件头(File Header)、页头(Page Header)、最大最小记录(Infimum+supremum)、用户记录(User Records)、空闲空间(Free Space)、页目录(Page Directory)和文件尾(File Tailer) 。
页结构的示意图如下所示:
这7个部分作用分别如下,简单梳理如下表所示:
名称 | 占用大小 | 说明 |
---|---|---|
File Header | 38字节 | 文件头,描述页的信息 |
Page Header | 56字节 | 页头,页的状态信息 |
lnfimum-Supremum | 26字节 | 最大和最小记录,这是两个虚拟的行记录 |
User Records | 不确定 | 用户记录,存储行记录内容 |
Free Space | 不确定 | 空闲记录,页中还没有被使用的空间 |
Page Directory | 不确定 | 页目录,存储用户记录的相对位置 |
File Trailer | 8字节 | 文件尾,校验页是否完整 |