MySQL知识点总结 | 青训营

Yong690
131 阅读11分钟

最近在总结之前学过的知识点，这次正好借着青训营的机会和大伙分享一下我总结的知识点。其中主要包括：数据类型、三大范式、索引底层实现、索引最左匹配原则。

1、MySQL基础概念

1.1、什么是MySQL？

MySQL是当前最流行的关系型数据库管理系统之一，能够对数据进行持久化存储，且对这些数据进行管理。

1.2、MySQL基本数据类型

  • 数值类型

包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。以及 FLOAT、DOUBLE、DECIMAL 在内的小数类型

  • 字符串类型

包括 VARCHAR、CHAR、TEXT、BLOB，其中CHAR类型是定长的，而 VARCHAR 是可变长。

CHAR和VARCHAR的区别：

  1. CHAR 会根据声明的字符串长度分配空间，并会使用空格对字符串右边进行尾部填充。即除去存储的字节，其他剩余长度都填充为空格，且取出时会把尾部的空格去掉，如一开始存储的数据为‘char '，取出的结果也会变为'char'。又因为长度固定，所以存储效率高于 VARCHAR 类型。
  2. CHAR在存储英文字符时占用1字节，存储汉字时占用2字节，而VARCHAR所有字符都占用2字节
  3. 在MySQL5.0之后，VARCHAR会在头部额外使用1~2字节存储字符串长度信息（长度小于 255 字节时使用 1 字节表示，否则 2 字节），在结尾多使用1字节标识字符串结束。

如何选择CHAR / VARCHAR：

如果使用的是 Innodb 引擎的话，推荐使用 VARCHAR代替 CHAR ，因此一般都推荐使用VARCHAR

2、MySQL表的设计

2.1、什么是三大范式

三大范式是 Mysql 数据库设计表结构所遵循的规范和指导方法，目的是为了减少冗余，建立结构合理的数据库，从而提高数据存储和使用的性能。且三大范式之间是具有依赖关系的，比如第二范式是在第一范式的基础上建设的、第三范式是在第二范式的基础上建设的。当然在表结构设计的时候不止有三大范式，但三大范式是最常用的，所以本文只讲解三大范式。

  • 第一范式 - 1NF

要求字段满足最小不可分割单元，即体现原子性

以下举出一个简单例子：

id姓名年龄
1体育张老师32
2数学李老师38
3物理蒋老师29

上述表中姓名字段不为最小单元，不满足第一范式，仍可分割，如“体育张老师”可以拆分为“体育”、“张老师”，按照第一范式修改后为：

id学科姓名年龄
1体育张老师32
2数学李老师38
3物理蒋老师29

那是否遵循第一范式就一定是好的呢？观察下表：

员工编码姓名地址
001小张江西省南昌市东湖区
002小黄广东省佛山市禅城区
003小高湖北省武汉市新洲区

其中地址不是最小单元，仍可进行分割，分割后的表为：

员工编码姓名省市区
001小张江西省南昌市东湖区
002小黄广东省佛山市禅城区
003小高湖北省武汉市新洲区

虽然拆分后，看上去更符合第一范式了，但是如果项目要我们输出一个完整地址就需要进行拼接了，明显是表在没拆分的时候会更好用。

所以范式只是给了我们一个参考，我们更多的是要根据项目实际情况设计表结构。

  • 第二范式 - 2NF

在满足第一范式的情况下，遵循唯一性，消除部分依赖。即表中存在一个主键，该主键能确定所有其他非主键的值，一张表确定一件事。

举出一个经典的例子：

学号姓名年龄课程名称成绩学分
001小张28语文903
001小张28数学902
002小黄25语文903
002小黄25语文903
003小高22数学902

我们先分析一下表结构。

  1. 假设学号是表中的唯一主键，那由学号就可以确定姓名和年龄了，但是却不能确定课程名称和成绩。
  2. 假设课程名称是表中的唯一主键，那由课程名称就可以确定学分了，但是却不能确定姓名、年龄和成绩。
  3. 虽然通过学号和课程名称的联合主键，可以确定除联合主键外的所有的非主键值，但是基于上述两个假设，也不符合第二范式的要求。

因此我们可以基于第二范式进行拆分，拆分成三个表：

  1. 学生表 - 学号做主键
学号姓名年龄
001小张28
002小黄25
003小高22
  1. 课程表 - 课程名称做主键
课程名称学分
语文3
数学2
  1. 成绩表 - 学号和课程名称做联合主键
学号课程名称成绩
001语文90
001数学90
002语文90
002语文90
003数学90

拆分之后能大大降低数据的冗余，且每张表只有一个主键，不会发生数据插入时学号或者课程名缺失导致插入异常，更新数据也更方便了。

  • 第三范式 -3NF

在满足第二范式的情况下，消除传递依赖。

给出一个简单例子：

学号姓名学院院长
1小明计算机学院黄院长
2小红资源环境学院曾院长
3小李区块链学院陈院长

上述表中，这个表中，学号是主键，它可以唯一确定姓名、学院、院长，符合了第二范式。但其中学院依赖于学号，而院长依赖学院，存在传递依赖，不符合第三范式。

我们可以进行修改：

  1. 学生表
学号姓名学院
1小明计算机学院
2小红资源环境学院
3小李区块链学院
  1. 学院表
学院院长
计算机学院黄院长
资源环境学院曾院长
区块链学院陈院长

如此，就消除了表中的传递依赖，满足第三范式

2.2、范式和反范式的对比

名称优点缺点
范式范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。查询时通常需要多表关联查询，更难进行索引优化
反范式反范式的过程就是通过冗余数据来提高查询性能，可以减少表关联和更好进行索引优化存在大量冗余数据，并且数据的维护成本更高

3、索引

3.1、什么是索引

当我们想要在书籍中查询一些特定的信息，最快的方法就是查询目录，根据目录标出的页码到相应的地方进行查询。那么我们在数据库当中查询信息时也可以使用这种方法，而不是从第一页开始到最后一页结束一条数据一条数据的对比。索引在数据库中就是充当“目录”的角色，即索引就是数据库的目录，能加快数据的查询。

3.2、索引分类

为了方便理解记忆，这里只简单地进行分类，可以分为聚簇索引（主键索引）和非聚簇索引（二级索引）

  • 聚簇索引

    在创建表时，InnoDB存储引擎会根据不同的情况选择不同的列作为聚簇索引：

    1. 如果有主键，默认将主键作为聚簇索引的索引键（key）
    2. 如果没有主键，则会将表中的第一个不包含NULL值的唯一列作为聚簇索引的索引键（key）
    3. 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）

  • 非聚簇索引

    所有不是聚簇索引的索引都被称之为非聚簇索引

而这两种索引在InnoDB中都是通过B+Tree构建出来的，其中的区别为：聚簇索引会在叶子节点存在实际数据；而非聚簇索引在叶子节点存放的是主键的值，若想通过非聚簇索引获取详细信息，需要回表查询，即查两个B+Tree才能获取到完整数据。若是通过非聚簇索引获取主键id，则可以直接通过“覆盖索引”，获取主键id，不需要回表。

  • B+Tree存储构造索引的优点

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

除了上面的两个索引，还可以按照字段特性进行分类，如：

  1. 主键索引

    主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

    在创建表时，创建主键索引的方式如下：

    CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

  2. 唯一索引

    唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

    在创建表时，创建唯一索引的方式如下：

    CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

    建表之后可以对表使用命令进行索引的创建：

    CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

  3. 普通索引

    普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

    在创建表时，创建普通索引的方式如下：

    CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

    建表后，如果要创建普通索引，可以使用这面这条命令：

    CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

  4. 前缀索引

    前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

    使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

    在创建表时，创建前缀索引的方式如下：

    CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

    建表后，如果要创建前缀索引，可以使用这面这条命令：

    CREATE INDEX index_name
ON table_name(column_name(length));

3.3、最左匹配原则

当一张表里需要通过多个字段来进行检索时，我们可以创建一个联合索引检索数据，如我们创建一个a,b两个字段的联合索引：

CREATE INDEX index_ab ON product(a, b);

假设存在一下数据：

image-20230819182053898.png

我们可以看到，当a相等时，b是有序的（a = 1, b = 1, 2; a = 2, b = 1, 4），而当a不相等时，全局的b是无序的（1, 2, 1, 4, 1, 2）。因此我们可以得出结论：b是全局无序，局部有序的，因此当我们查询条件为 a = 1 and b = 2或 a=1 又或 b = 2 and a = 1 就可以通过联合索引进行查询，而当条件为b=2时，索引是失效的。即以最左边为起点任何连续的索引都能匹配上--最左匹配原则

值得注意的是，当联合索引在向右匹配的过程中碰到范围查询就会停止索引（>, <），如：select * from t_table where a > 1 and b = 2中只有a字段进行了索引查询。但是对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

3.4、索引下推

在执行 select * from table where a > 1 and b = 2 语句的时候，为了减少回表次数，MySQL5.6引入了索引下推，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

个人总结

总结这份资料花了我一下午的时间，还是挺不容易的，想要理解知识点的同时转换成自己的话再输出为文本给大家一起理解，这个过程挺复杂。同时MySQL的知识还不止这么一些，其中索引的优化，索引具体的例子，以及B+Tree的图例等，我都偷懒没有画或者详细讲解，因为这些都太花时间了。还有MySQL的事务，语句执行流程，优化器，执行器，执行计划等，太多太多知识点了，希望以后有机会能再总结出来和大家分享

avatar
文章
阅读
粉丝