Mysql 重要基础概念

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、什么时候添加索引

索引存在维护成本,数据变更后,索引需要重新排序进行维护

  1. 数据量庞大
  2. 该字段DML操作少
  3. 该字段经常出现在where 子句中

主键和unique约束自动添加索引

2、创建删除索引

create index 索引名 on 表名(字段名)
drop index 索引名 on 表名
#通过expain sql语句查看执行计划的type和rous变化

数据库设计三范式

按照三范式设计的表不会出现数据冗余

第一范式:任何一张表都要有主键,字段不可再分
第二范式:建立在第一范式上,所有非主键字段完全依赖主键,不能部分依赖(不能设置字段组合)
多对对,三张表,关系表2个外键
第三范式:建立在第二范式上,所有非主键字段直接依赖主键,不能传递依赖
一对多,二张表,多的表加外键

实际中可以拿冗余换执行速度,表链接多就导致执行变慢

©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页