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个外键
第三范式：建立在第二范式上，所有非主键字段直接依赖主键，不能传递依赖
一对多，二张表，多的表加外键

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