《MySQL是怎样运行的》-读书笔记（一）

一条小鲤鱼
216 阅读9分钟

这本书口碑不错，重新翻一下，当做温习。也将阅读记录简单整理一下，供大家参考。 后面阅读另外一本进阶书《MySQL是怎样运行的》时候也会做一些整理。

MySQL数据类型

1.整数的类型以及占用的空间和含义

　在数学中，大家提到整数可能会有明显地意识知道正数和负数，在编程中可能下意识地认为就是正数。这主要是因为编程中处理负数时，可以很方便地使用 -正数 来表示 负数 。

　在编程语言中对于整数一般分为：

  • 无符号数(仅代表非负数） : 0 ~ 2n -1
  • 有符号数(代表正数和负数): -2n-1 ~ 2n-1 -1

整形的类型和空间

类型占用的存储空间
TINYINT1 字节
SMALLINT2 字节
MEDIUMINT3字节
INT4字节
BIGINT8 字节

每个字节 8位，也就是 28 的范围 ,大家经常看到 int(16) 或者tinyint(4) ,实际上括号里的数字只会影响从数据库中查出来的数据展示的数据长度。 对实际的数据存储没有任何影响。

2.浮点数类型

类型占用的存储空间
FLOAT4 字节
DOUBLE8 字节

虽然有的小数比如2.32 可以很容易地转换成二进制，但更多的是小数无法直接转换成二进制，比如 0.3 ，它转换成的二进制小数就是一个无限小数。但是我们现在只能用4个字节或者8个字节来表示这个小数，所以只能进行一些舍入来近似表示。

  • 常见定义格式
    • FLOAT(M,D)
    • DOUBLE(M,D) M: 有效位数
    D：小数保留小数点后十进制数字的个数
    比如 2.34 有效位为 3， D为 2位； 0.9 有效位为 1 ，D 为 1位

3.定点数类型

类型占用地存储空间
decimal(M,D)取决于M 和 D

上面有提到浮点数无法精准地表示小数的问题， MySQL为了解决这个问题，发明了一种新的存储方式。将一个小数分为两部分来存储 整数部分 + 小数点后面的小数部分 ，且这两部分都是按照整数的方式来存储。 比如 20.30 ，直接按照浮点数来存储时是无法精确存储的，换成定点数之后，可以存储一个 20 + 30 即可 ，需要使用到数据时按照一定的规则拼成20.30即可

可能有同学会有疑惑？ decimal(M,D) 在进行存储时，到底该如何确定整数部分 和 小数部分分别用多少字节来存储。比如 12 既可以用1字节存储，也可以用2字节存储。 是不是按照最小的存储？ 实际并不是的，MySQL存储定点数时，是从小数点位置开始，将每9个整数划分为一组。比如 999123456789.123456 ，那么在划分小数点左边部分时，将 12346789 划一个组， 999 划一个组 。第一组 999 包含3个十进制（最大999）要 2个（256* 256）字节存储 ；第二组包含 9个十进制，需要使用 4个字节存储

4.日期和时间类型

类型占用地存储空间取值范围含义
YEAR1 字节1901 ~ 2155年分值
DATE3 字节1000-01-01 ~ 9999-12-31日期值
TIEM3 字节-839:59:59 ~ 839:59:59时间值
DATETIME8 字节1000-01-01 00:00:00 ~ 9999-12-31 23:59:59日期和时间值
TIMESTAMP4 字节1970-01-01 00:00:01 ~ 2038-01-19 03:14:07时间戳

DATETIME 是不带时区的，比如 2022-02-21 23:22:01 在任何时区取出来都是 2022-02-21 23:22:01 。但是Timestamp 这种存储了时区信息的则不一样，比如我们在东八区，存储的是 2022-02-21 23:22:01 ，东七区取出来的就是 2022-02-21 22:22:01 。有时区差

5.字符串类型

类型最大长度
char(M)固定M个 字符
varchar(M)M个 字符

不同于上面的存储空间，字符串里的 M 表示的是字符数（这跟oracle是不相同的，oracle默认指定为字节数，只有指定了单位为 char的时候才表示字符数，比如 varchar(20 char) 20字符 ， varchar(20) 20字节）

另外不要忘记，MySQL中表的一行数据最大不能超过 65535个字节

主键和约束

1.主键

在mysql中一行数据就是一条记录。有时候在表中可以通过某一列，或者某几列来确定一条记录，那么我们就可以将这几列作为表的主键。被设置为主键的列都会被强制设为非空，不允许为null

CREATE TABLE teacher(
  id BIGINT(20) ,
  NAME VARCHAR(10),
  PRIMARY KEY(id, NAME)
)COMMENT '教师表';

上图中的组合主键：id 和 name 被强制设置为 not null

2.约束

约束就是一种规则，对某个事物施加约束即可以让该事物的行为符合规则。数据中的约束一般设置在某列或者某种行为上，比如对列A 设置唯一约束 unique ,表示在整个表中每条记录的列A的值都要唯一 。（列的值为NULL 时表示该列还没有设置过值，所以即使有唯一约束，在字段没有设置not null 的情况下，依然可以在唯一约束所在列上存在很多个 NULL值）

CREATE TABLE teacher1(
  id BIGINT(20) UNIQUE,
   NAME VARCHAR(20) ,
   age INT NOT NULL
)COMMENT '教师表1';

将表的id 列添加了唯一约束，这种是对值的约束；另外一种约束是对某种行为的约束，比如字段值更新的时候：


CREATE TABLE teacher3(
  id BIGINT(20) ,
  NAME VARCHAR(10),
  edit_time  DATETIME ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id, NAME)
)COMMENT '教师表';

edit_time 会在记录进行update 时，自动将值更新为 当前时间（CURRENT_TIMESTAMP）

注意： 如果对某条记录执行update操作，但实际上并没有改动记录的任何列的值，实质上依然没有对该条记录做任何操作。此时是不会触发 update 操作。 这一点跟oracle是不一样的

3.唯一约束 转 主键

如果表中为设置主键， 那么会将第一个设置为 not null unique的列设置为默认主键； 如果既没有主键，也没有设置not null unique的列，那么mysql 会将隐藏列 rowid 设置为隐藏主键。

主键对应的索引叫聚簇索引

4.AUTO_INCREMENT

auto_increment:自增 ，列的属性 ，

  • 必须定义在int类型或者bigint 这种整型相关的类型字段上，否则会报错

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT

  • 设置该属性的列，必须建立索引 ，否则会报错

Incorrect table definition; there can be only one auto column and it must be defined as a key

5c68acbac55e8bc4700a7b8053bb0fc9.jpeg 这里我们先留一个问题: 为什么一定要建立索引？

  • 每个表只允许给一个列设置自增属性

  • 可以指定自增值，默认从 1开始

  • 插入sql时，不同的值，数据库是如何处理的？ 一个正确地自增列设置：

    CREATE TABLE teacher4(
    id BIGINT AUTO_INCREMENT,
    NO INT ,
    INDEX id_idx(id)
    )
    -- 三大要求
    -- 自增属性要设置在 整型相关类型的字段上
    -- 一个表只允许设置一个自增属性
    -- 被添加自增属性的列必须建立索引

 - 自增列如果送了值，将使用送进来的值  
 
  INSERT INTO teacher4(id,NO) VALUE(100,1)

image.png

 - 自增列没送值,将使用自增机制新增的值
 
 INSERT INTO teacher4(NO) VALUE(1)

image.png

 - 自增列送了null ，将使用自增机制新增的值
 
INSERT INTO teacher4(id,NO) VALUE(NULL,1)

image.png

 - 自增列送了 0 ，将使用自增机制新增的值

 INSERT INTO teacher4(id,NO) VALUE(0,1)

image.png

  • 如何确定自增的值？

    其实从上面已经可以初步看到自增值是如何确定的。

    • 如果最开始没指定值，从1开始 ；指定了初始值就从初始值开始

    • 如果一开始插入的记录中，自增列是设置了值，那么该值即为初始值，比如我们测试使用的初始值 100

    确定初始值之后，如何确定下一个值? 简单来看有两种方案，一种是直接取该列的最大值进行+1 操作， 一种是最后一次自增产生的值 或者初始值进行 + 1；

    mysql 正常情况下选用的是 第二种

    验证：

  1. 首先跳跃插入一条 id = 200 的记录
INSERT INTO teacher4(id,NO) VALUE(200,1)

image.png

  1. 再插入一条 id = 150 的记录

    INSERT INTO teacher4(id,NO) VALUE(150,1)

image.png

3.插入一条需要自增的记录

INSERT INTO teacher4(NO) VALUE(1)

image.png

此时发现，虽然最后一次自增操作 id =150 ,但是新插入的值是上一次最大值 200 进行 +1

4.此时如果不是通过插入记录的方式来改变最大值，而是直接将表中某条记录的id值进行修改

  • 新修改的值小于最大值 ，那么新自增产生的值依然是 上一次的最大值 +1

将 103 改成 105

image.png

进行一次新增后

INSERT INTO teacher4(NO) VALUE(1)

image.png

现在将某个列值改为超过 自增产生的最大值

将 105 改成 300

继续进行新增后

image.png

发现是在上一次自增后的最大值的基础上进行 +1

通过测试我们可以发现 ，正常情况下自增属性实质上就是对该列目前的最大值进行 +1 ，这很好地解释了为什么要建立索引，因为整型类型的索引可以快速排序找到最大值 ；也同时解释了为什么只允许建立一个列上添加自增属性，因为如果允许两个列的话，那么需要分别查询每个列现有的最大值，这会极大地降低处理速度。

最后需要注意的是：`如果通过update的方式修改了自增列的值，不会影响自增列下一次新产生的值。但可能会在某一刻出现相同值的情况。这是需要避免的，尤其是列添加了唯一约束的时候是肯定会报错的

avatar
文章
阅读
粉丝