MySQL字符集、存储引擎、主外键

最新推荐文章于 2024-01-02 19:10:16 发布

II Sole

最新推荐文章于 2024-01-02 19:10:16 发布

阅读量729

点赞数 1

分类专栏： Mysql学习

本文链接：https://blog.csdn.net/weixin_46022050/article/details/106479575

版权

Mysql学习专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.字符集

字符集：其实就是字符的集合
字符（Character）是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。
字符集（Character set）是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同。
常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。

几种常见的字符集:

ASCII:ASCII ((American Standard Code for Information Interchange): 美国信息交换标准代码）是基于拉丁字母的一套电脑编码系统，主要用于显示现代英语和其他西欧语言。

简体中文字符集: GB2312字符集、 GB18030字符集

繁体中文字符集： BIG5字符集

Unicode（统一码、万国码、单一码）是计算机科学领域里的一项业界标准，包括字符集、编码方案等。Unicode 是为了解决传统的字符编码方案的局限而产生的，它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。

UTF-8：是针对Unicode的一种可变长度字符编码。属于unicode编码,此外还有UTF-16、UTF-32，数字越大表示需要存放一个字符的空间就会多，能存放的字符的数量也会多。

注意：utf8编码：1个英文字母占1个字节，1个汉字占3个字节。我们一般会使用utf8，比其他字符集更加通用一点

这边可以使用show character set; 查看mysql支持的所有字符集
在这里插入图片描述
mysql总共支持41中字符集，这边可以看下Default collaction是每种字符集所对应的排序方式，这边去utf8为例，这边支持的utf8_general_ci其实一种不分大小写的排序，就好比一条字符串‘A a b c B’
插入数据库，这样的排序方式就将其排为‘A a b B c’,不会在乎其大小写，按照字母本身顺序进行排序

为什么utf8字符集汉字占三个字节，而字母只占一个字节？

注意这边还有要注意Maxlen列表名了字符集所支持的存储最大长度是多少，就比如常见汉字有5000种，这边就需要3位来存储汉字，“2^3*8”（一位等于8bit）来保证汉字不会超过存储的最大限度，而字母只有26种这边就只需要 2的8次方就足够存储了

库表字段与字符集之间的关系

在建库建表建字段的过程中，在不指定字符集时字符集默认是会进行继承的，但是这个可以进行修改，这里就说明列对应的字段的字符集优先级最高

MySQL默认字符集:

rpm方式安装的mysql默认的字符集是latin1，因为mysql作者是来自瑞典的，瑞典那边其实使用Latin1字符集，编译安装的时候因为我们指定了字符集为utf8，所有默认的字符集是utf8

怎么查看与建表时指定字符集创造

这边可以通过show create table + 已经创造好的表名; 这边可以发现我们这边的字符集为utf8
在这里插入图片描述
创造指定字符集表可以通过下面语句实现，这边其实就是在建表后面指定下字符集

create table t4(id int ,name varchar(20)) default charset = 'latin1'

在这里插入图片描述

2.存储引擎

什么是存储引擎？

首先确定一点，存储引擎的概念是MySQL里面才有的，不是所有的关系型数据库都有存储引擎这个概念，后面我们还会说，但是现在要确定这一点。存储引擎的本质就是存取数据
　　
在讲清楚什么是存储引擎之前，我们先来个比喻，我们都知道录制一个视频文件，可以转换成不同的格式，例如mp4，avi，wmv等，但是，给我们或者用户看懂实际视频内容都是一样的。直观区别是，占用系统的空间大小与清晰程度可能不一样。

但是对于用户和应用程序来说同样一张表的数据，无论用什么引擎来存储，用户能够看到的数据是一样的。不同储引擎存取，引擎功能，占用空间大小，读取性能等可能有区别。说白了，存储引擎就是在如何存储数据、提取数据、更新数据等技术方法的实现上，底层的实现方式不同，那么就会呈现出不同存储引擎有着一些自己独有的特点和功能，对应着不同的存取机制。
　　
　　因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即：对表的存储、操作等的实现方法不同)，表是什么，表本质上就是磁盘上的文件。

其实MySQL支持多种存储引擎，每种引擎有着一些自己独特的功能，用户在使用的时候，可以根据自己的业务场景来使用不同的存储引擎，其中MySQL最常用的存储引擎为：MyISAM和InnoDB。
在详细介绍这些存储引擎之前，我们先来看看MySQL的整个工作流程，看一下存储引擎在哪里，MySQL都做了哪些事情。

这边给一张MySQL体系结构的图
在这里插入图片描述

如何查看表的存储引擎

这里说明一下，存储引擎默认是与表进行绑定的，这边可以通过下面命令查看表的存储引擎

show engines;  #查看MySQL所有的引擎

在这里插入图片描述
通过这个表其实就可以看到各种存储引擎的特点了，我们默认的存储引擎就是 InnoDB

几种常见的存储引擎

1、MyISAM

使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

（1）frm文件：存储表的定义数据
（2）MYD文件：存放表具体记录的数据
（3）MYI文件：存储索引

frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。下面这张图就是MYI文件保存的机制：
在这里插入图片描述
这边可以看出，myisam存储引擎，这边支持全局索引（索引的本质就是查询数据的数据），速度毫无疑问肯定是相当快的，有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎

MyISAM引擎特点：
        1.不支持事务
            事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。
        2.表级锁定
            数据更新时锁定整个表：其锁定机制是表级锁定，也就是对表中的一个数据进行操作都会将这个表锁定，其他人不能操作这个表，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
        3.读写互相阻塞
            不仅会在写入的时候阻塞读取，MyISAM还会再读取的时候阻塞写入，但读本身并不会阻塞另外的读。
        4.只会缓存索引
            MyISAM可以通过key_buffer_size的值来提高缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。
        
        5.读取速度较快
            占用资源相对较少
        6.不支持外键约束，但只是全文索引
        7.MyISAM引擎是MySQL5.5版本之前的默认引擎，是对最初的ISAM引擎优化的产物。
MyISAM引擎特点

2.InnoDB

InnoDB引擎
        介绍：InnoDB引擎是MySQL数据库的另一个重要的存储引擎，正称为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优点是支持兼容ACID的事务(类似于PostGreSQL)，以及参数完整性(即对外键的支持)。Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其他想将InnoDB结合到商业软件的团体获得授权。

InnoDB引擎特点：
        1.支持事务：支持4个事务隔离界别，支持多版本读。
        2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
        3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。
        4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。
        5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)
        6.所有SecondaryIndex都会保存主键信息。(了解)
        7.支持分区，表空间，类似oracle数据库。
        8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。
        9.和MyISAM引擎比较，InnoDB对硬件资源要求还是比较高的。
        
        小结：三个重要功能：Supports transactions，row-level locking，and foreign keys
InnoDB引擎特点

你正常使用数据库的话，InnoDB绝对是你的首选存储引擎，误差小，功能多，这就是他最大的优势点所在

3.Memory

将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。

（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；

（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；

（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

（5）默认使用hash索引。

（6）如果一个内部表很大，会转化为磁盘表。

在这里只是给出3个常见的存储引擎。使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

Memory存储引擎，适用于当你只需要创建临时表，而不需要讲表持久化保存的时候，这个绝对是不二选择
创建临时表方法

 create temporary table t5(id int,name varchar(20));

临时表存在内存里，这边直接使用select语法是查询不到临时表的，但是可以插入数据，但是你退出mysql后临时表就会自动销毁，其他用户连接mysql是访问不到临时表的，这边下图说明了这其中的关系
在这里插入图片描述

4.CSV

CSV存储引擎是MySQL专门处理csv文件的专属引擎，csv文件其实就是以逗号分隔的文件，存储格式就是下面的样子

id,name,age
1,jack,12
2,rose,13
3,pitter,14

5.BlackHole

Blackhole引擎–“黑洞”. 其作用正如其名字一样：任何写入到此引擎的数据均会被丢弃掉，不做实际存储；Select语句的内容永远是空。
下面是没有BlackHole主从复制结构图，这边当主服务器进行数据更新后生成了一个二进制日志，然后讲二进制日志给从服务器，从服务器按照日志复现主服务器执行的数据更新操作，从而达到与服务主服务器一摸一样的执行效果
在这里插入图片描述
在大规模的Mysql服务器集群中，如果是存在一台主服务，多台从服务器，在繁忙的业务中，意味着主服务器每操作一个事件，都要往自己的二进制日志中写数据，同时还要往多台从服务器发一次，N台服务器指向一台主服务器，那么需要主服务器发送N次，会启动N个线程，每个线程各自从线程里读二进制日志，那么会有大量的IO，本来是为主服务器减轻负担的，那么这样只能造成压力越来越大，那这样master主机就会为每台slave主机分配出一个binlog dump进程，这样的话会严重影响master的性能

有了BlackHole存储引擎之后的结构图
在这里插入图片描述
这个存储引擎解决了主从复制架构里，让很多的从服务器直接到配置了blackhole存储引擎的master上拿二进制日志，让最上层的master的负载降低

BLACKHOLE总结
BLACKHOLE支持所有类型的索引
BLACKHOLE 表不存储数据，如果复制基于SBR，语句可以记录并在从库执行；如果复制为RBR、MBR，UPDATE及DELETE操作将会跳过，不会记录也从库不执行。
Insert触发器可以正常使用，Update、Delete触发器因为不存储数据不能触发，FOR EACH ROW 也不能触发。
BLACKHOLE 表Auto Increment字段不会自动递增,也不保留自增字段的状态
结合复制replicate-do和replicate-ignore规则，可使用BLACKHOLE当做一个分发主服务器
可用来验证转储文件语法
测试binlog的开销量，通过对比 BLACKHOLE 与 不启动 binlog的性能
可能被用来查找与存储引擎自身不相关的性能瓶颈

3.主外键

什么是主外键

假设这边存在几个表

学生表(学号，姓名，性别，班级)                      
课程表(课程编号,课程名,学分)                           
成绩表(学号,课程号,成绩)

主键：关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键
那么这边学生表里面学号以及课程表的课程编号就是主键了，成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以学号和课程号的属性组是一个主键，这种主键叫做联合主键

外键：A表里的主键被引用到B表里做一个字段，这个字段就是B表里的外键，外键在另外表里要是是主键，但是外键不是必须的存在
成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键，同理课程号也是

主键、外键和索引的区别

在这里插入图片描述
主键本质上其实就是唯一标识加不可重复（not null + unique）

如何创建主键

create table stu3(id int  comment '编号',name varchar(20) not null comment '姓名',primary key(id));

create table stu2(id int primary key comment '编号',name varchar(20) not null comment '姓名');

如何创造外键

这边先创建一个子父表，外键在子表中肯定为父表的主键


创建父表
CREATE TABLE dept(
  deptid INTEGER,
  dname VARCHAR(20),
  PRIMARY KEY(deptid) 
);

在这里插入图片描述
插入两条数据

创建子表

CREATE TABLE emp(
  id INT PRIMARY KEY,
  NAME VARCHAR(20),
  deptid INT,
  FOREIGN KEY(deptid) REFERENCES dept(deptid)  # 建立名字叫做deptid的外键 ，外键来自于dept表的deptid 
);

外键命名最好与父表主键相同，这样很好查看到此外键是来自于dept的deptid字段
在这里插入图片描述
这边外键就构建成功了

数据库中主键和外键的设计原则

主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。

主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。

必须将数据库模式从理论上的逻辑设计转换为实际的物理设计，而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于了生产环境，就很难对这些键进行修改，所以在开发阶段就设计好主键和外键就是非常必要和值得的。

两个表建立主外键规则后，其里面有关系的字段就不能随便删除以及插入，子表插入字段时，必须考虑是否存在父表的主键字段，例如这边deptpid只有 10 和 20 ，你如果在子表的deptpid插入30，那就会报错

如何解除字父表主外键规则

ON DELETE CASCADE：级联删除

创建子表

CREATE TABLE emp8(
  id INTEGER PRIMARY KEY,
  NAME VARCHAR(20),
  deptid INTEGER,
   FOREIGN KEY(deptid) REFERENCES dept(deptid) 
   ON  DELETE  CASCADE
);

插入数据
在这里插入图片描述
这边执行DELETE FROM dept WHERE deptid = 10; 发现成功，查看emp8里的文件发现不存在了，这就是级联删除

ON DELETE SET NULL方式

ON DELETE SET NULL：当删除当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么不删除中，而是将子行的外键列设置为null
这边操作方式与上面相似这里就不重复操作了

借鉴文章

https://www.cnblogs.com/clschao/articles/9953550.html#part_1 存储引擎总结
https://baijiahao.baidu.com/s?id=1655327558614401593&wfr=spider&for=pc 存储引擎总结
https://www.cnblogs.com/wt645631686/p/8086682.html BlackHole总结