Mysql 存储引擎
存储引擎就是数据库底层的表的存储方式,所有每一个存储引擎都是针对特定的需求实现所需要的的功能(比如我们汽车引擎,有耗油的、速度快的、马力强的等等,最终的选择都是导致开车的效果)
表示层面向用户,而逻辑层就是真实面对机器
数据库底层是依赖于文件系统实现存储,所有数据库的记录都是存储在文件里
系统查看mysql自带的引擎
- InnoDB是mysql默认引擎
- Transactions:是否支持事务
- XA :是否支持分布式事务
- Savepoints:是否支持事务的回退点
常见的3种引擎
MyISAM引擎
- 不支持事务
MyISAM采用3个文件组织一张表
- *.frm 表定义,是描述表结构的文件
- *.MYD 保存了表的数据记录 数据信息文件,是表的数据文件
- *.MYI 索引信息文件,是表数据文件中任何索引的数据树
- *.opt 记录数据库的选项,数据库的字符集设置
优点:支持压缩,节省空间,可以转化为只读表,提高检索效率
缺点:不支持事务
InnoDB引擎
- 优点:支持事务、行级锁、外键等, InnoDB引擎对数据的安全性最好
表结构存储在*.frm文件中
数据和索引存储在tablespace这样的表空间中(逻辑概念,其实还是以文件存储),无法被压缩,无法转换只读表
-
共享表空间: 某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认的文件名为:ibdata1
-
独立表空间: 每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个.frm表描述文件,还有一个.ibd文件。
其中这个文件包括了单独一个表的数据内容以及索引内容,默认情况下它的存储位置也是在表的位置之中show variables like "innodb_file_per_table"; ON代表独立表空间管理,OFF代表共享表空间管理
InnoDB引擎提供数据库崩溃后自动恢复机制
InnoDB引擎支持级联删除和级联更新(一张表删除级联会删除另一张表)
MEMORY引擎
- 优点:查询速度最快,因为数据存储在内存中
- 缺点:不支持事务,断电会导致数据丢失
数据库表结构
表的组成
- 列:字段
- 行:记录/数据
结构说的就是字段,记录是根据字段定义的类型产生的
MySQL 数据类型
数据类型用于指定特定字段所包含数据的规则,它决定了数据保存在字段里的方式,包括分配给字段的宽度,以及值是否可以是字母、数字、日期和时间等
- 字符串类型(CHAR(0-255固定长度),VARCHAR(0-255可变长度)
- 数值类型(INT(整数型)、FLOAT(浮点型))
- 日期和时间类型(DATE(年月日)、TIME(时分秒))
MySQL 约束类型
约束是一种限制,它通过对表的行或列的数据做出限制,来确保表的数据的完整性、唯一性
- 主键约束(Primary key):唯一约束+非空约束的组合,可以设置多个字段组合(不建议),一张表主键只能有一个
- 作用:确定记录的唯一标识
- 外键约束(foreign key):外键是与之另一个表的主键或者unique引用,也就是外键值必须是引用字段里的值,设为外键的表叫子表,被引用的表叫父表,存在上下级的关系
- 作用:保证数据的一致性、合法性和实现一些级联操作
- 唯一约束(unique):字段下插入的记录不能出现重复,但是可以重复null,可以设置多个字段组合
- 非空约束(not null):字段下记录不能为空
- 默认约束(default):插入记录的默认值(不指定值的情况下)
主键约束
1、添加主键约束
id int primary key;
#列级字段会面加上primary key
primary key(字段)
#表级主键方式
2、主键分类
根据主键的字段数量划分:
- 单一主键:单个字段(推荐)
- 复合主键:多个字段组合(不推荐使用,违背了三范式)
根据主键的性质划分:
- 自然主键:主键值最好跟业务没有任何的关系的自然数(推荐使用)
- 业务主键:主键值跟业务的系统挂钩(身份号、卡号。。),不推荐使用,因为当主键字段业务发生改变整个表就要发生改变,发生改变就可能导致主键值相同
3、主键自增
自然主键是自然数,那么就可以实现自增,无需人工维护
id int primary key auto_increment;
#字段会面加auto_increment
外键约束
设置为外键约束的字段的值不能随便写了,只能是引用的另一个表的字段值
1、外键值可以为null
2、外键字段引用其他的表字段,被引用的字段可以是主键约束或者唯一性约束(一般都是主键约束)
3、外键约束sql
foreign key(字段) references 表(字段)
#中文标注的都是可以改变的
Mysql事务
- 一个事务就是完整的业务逻辑单元,不可再分。也就是多条sql语句执行要么都成功要么都失败,不允许出现一条成功一条失败
- 只有DML语句才和事务有关系,才支持(insert delete update)
- 作用:保证数据的完整性和安全性
- 一条DML语句就没有必要事务机制
事务原理
1、开始事务
2、执行的语句是先存储在记录操作里(缓存),而没有改变硬盘文件数据
3、可以提交事务或者回滚事务(提交和回滚都会结束事务,清空记录操作),提交后一次性执行sql语句改变真实硬盘上的数据
提交语句:commit(sql默认分号结尾就是一次提交)
回滚语句:rollback
事务的特性
- 原子性:最小工作单元,不可再分
- 一致性:多条DML语句执行要么都成功要么都失败
- 隔离性:事务A和事务B之间具有隔离
- 持久性:数据持久化到硬盘上才算结束事务
事务的隔离性级别(理论上分4个级别)
第一级别:读未提交,对方事务没有提交的我可以读到
- 存在问题:脏数据,数据记录不稳定,因为没有提交
第二级别:读已经提交,对方事务提交后我可以读到
- 解决了脏数据
- 存在问题:不可重复读,因为对方每次提交我就读到不同数据
第三级别:可重复读,mysql默认级别
- 解决了不可重复读
- 存在问题:读取到的数据可能不是真实的
第四级别:事务排队
- 解决了所有问题
- 存在问题:效率低,事务需要排队
索引
索引就类似一本书的目录,通过目录可以快速找到对应的资源
1、什么时候添加索引
索引存在维护成本,数据变更后,索引需要重新排序进行维护
- 数据量庞大
- 该字段DML操作少
- 该字段经常出现在where 子句中
主键和unique约束自动添加索引
2、创建删除索引
create index 索引名 on 表名(字段名)
drop index 索引名 on 表名
#通过expain sql语句查看执行计划的type和rous变化
数据库设计三范式
按照三范式设计的表不会出现数据冗余
第一范式:任何一张表都要有主键,字段不可再分
第二范式:建立在第一范式上,所有非主键字段完全依赖主键,不能部分依赖(不能设置字段组合)
多对对,三张表,关系表2个外键
第三范式:建立在第二范式上,所有非主键字段直接依赖主键,不能传递依赖
一对多,二张表,多的表加外键
实际中可以拿冗余换执行速度,表链接多就导致执行变慢