MySQL知识总结

2024 年 9 月 26 日 星期四(已编辑)
3

阅读此文章之前，你可能需要首先阅读以下的文章才能更好的理解上下文。

MySQL知识总结

MySQL

SQL基础

什么是关系型数据库？

关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

MySQL 字段类型

  • 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
  • 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
  • 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

整数类型的 UNSIGNED 属性有什么用？

MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

例如， TINYINT UNSIGNED 类型的取值范围是 0 ~ 255，而普通的 TINYINT 类型的值范围是 -128 ~ 127。INT UNSIGNED 类型的取值范围是 0 ~ 4,294,967,295，而普通的 INT 类型的值范围是 -2,147,483,648 ~ 2,147,483,647。

对于从 0 开始递增的 ID 列，使用 UNSIGNED 属性可以非常适合，因为不允许负值并且可以拥有更大的上限范围，提供了更多的 ID 值可用。

NULL 和 '' 的区别是什么？

NULL 跟 ''(空字符串)是两个完全不一样的值，区别如下：

  • NULL 代表一个不确定的值,就算是两个 NULL也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
  • ''的长度是 0，是不占用空间的，而NULL 是占用空间的。
  • NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
  • 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

NoSQL与SQL的区别

  • SQL关系新数据库，存储结构化数据，逻辑上以二维表的形式存在，列代表属性，行代表数据实体

  • NoSQL（not only sql）非关系型数据库，MongoDB、Redis。逻辑上提供了不同于二维表的存储方式，可以是json、哈希表或其他方式

    • 设计初衷是为了处理非结构化或半结构化数据

NoSQL与SQL的选择场景

  • 选择SQL
    • 数据结构固定、事务性强（例如需要严格的事务支持和数据一致性，比如银行、财务系统）、数据量适中，扩展需求不强
  • 选择NoSQL
    • 非结构化或半结构化数据
    • 大规模的数据集，时常高并发读写
    • 分布式架构，需要跨多个服务器节点存储和处理数据
    • 最终一致性可以结构，不要求过程的一致，能够接受结果一致性

三大范式

  • 是关系型数据库设计的基本准则。确保表结构的合理，减少数据荣誉，增强数据的完整性和一致性。
  • 第一范式：确保数据的原子性。
    • 要求数据库表的每一列都是不可分割的原子数据单元，即每个字段（列）只能存储单一值，而不能存储集合、数组等复杂结构
      • 例如：电话号码 列包含了多个电话号码，这违反了第一范式，因为一个字段中存储了多个值。
  • 第二范式：消除部份依赖
    • 在1NF的基础上，要求每个非主键字段必须完全依赖主键，而不能只依赖主键的一部分，即为消除部份依赖
      • 学生ID 课程ID 课程名称 学生年龄 1 101 数学 20 2 102 英语 22
      • 学生ID 和 课程ID 共同构成了主键，但 学生年龄 只依赖于 学生ID，而与 课程ID 无关，这就产生了部分依赖，违反了第二范式。拆分成两个表
  • 第三范式：消除传递依赖
    • 在2NF的基础上，要求每个非主键字段直接依赖于主键，而不能通过其他主键字段间接依赖主键，即为消除传递依赖
      • 员工ID 员名 部门ID 部门名称 1 张三 D01 市场部 2 李四 D02 财务部
        • 员工ID 员名 部门ID 1 张三 D01 2 李四 D02
      • 部门名称 依赖于 部门ID，而 部门ID 又依赖于 员工ID
  • 不遵循范式
    • 适当的数据冗余可以减少关联表查询，提高查询效率

连表查询

避免重复插入数据

    1. 使用UNIQUE约束，确保每个值在该列唯一不重复
    1. 使用INSERT ... ON DUPLICATE KEY UPDATE
    1. 使用INSERT IGNORE 会在插入记录时忽略因重复键而导致的插入错误

  • 保证全局唯一，使用UNIQUE约束； 需要插入和更新结合使用，使用ON DUPLICATE KEY UPDATE 快速忽略重复插入，INSERT IGNORE

CHAR和VARCHAR区别

  • CHAR固定长度，定义时需要指定长度，末尾补足空格。对于短字符效率高

  • VARCHAR可变长，定义时需要指定最大长度，实际存储根据实际长度占用空间。适合存储长度可变的数据

TEXT长度无限大？

  • TEXT：64kb MEDIUMTEXT： 16MB LONGTEXT： 4GB

外键约束

  • 作用：维护表与表之间的关系，确保数据的完整性和一致性

  • CREATE TABLE Enrollment ( 选课ID INT PRIMARY KEY, 学生ID INT, 课程ID INT, FOREIGN KEY (学生ID) REFERENCES Student(学生ID) ON DELETE CASCADE );

    • 如果 Student 表中的某个学生记录被删除，那么 Enrollment 表中所有与该学生相关的选课记录也会自动删除。这可以防止子表中出现“孤立数据”（即没有对应父表数据的记录）。

IN和EXIST

  • IN用于检查左边的表达式是否存在于右边的列表或者子查询的结果集中

  • EXIST判断子查询是否至少能返回一行数据，只关心是否有结果，而不关心返回什么数据

  • EXIST性能更好，因为查找到匹配项就会停止，而IN会扫描整个结果集

  • 子查询结果集较小或者不频繁变动，IN更直观易懂

  • IN能正确处理子查询中包含null，而exists只关注行的存在，而不是具体值

常见函数

  • SELECT CONCAT('Hello', ' ', 'World') AS Greeting;

  • SELECT LENGTH('Hello') AS StringLength;

  • SELECT SUBSTRING('Hello World', 1, 5) AS SubStr;

  • SELECT SUBSTRING('Hello World', 1, 5) AS SubStr;

  • SELECT ABS(-10) AS AbsoluteValue;

  • SELECT POWER(2, 3) AS PowerValue;

  • SELECT NOW() AS CurrentDateTime;

  • SELECT CURDATE() AS CurrentDate;

  • SELECT COUNT(*) AS RowCount FROM my_table;

查询语句的执行顺序

(9) SELECT 
(10) DISTINCT <column>,
(6) AGG_FUNC <column> or <expression>, ...
(1) FROM <left_table> 
  (3) <join_type>JOIN<right_table>
  (2) ON<join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(7) WITH {CUBE|ROLLUP}
(8) HAVING <having_condtion>
(11) ORDER BY <order_by_list>
(12) LIMIT <limit_number>;

MySQL 基础架构

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器： 按照 MySQL 认为最优的方案去执行。
  • 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
  • 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

MySQL 存储引擎

执行一条sql请求的过程

    1. 连接器：建立连接，管理链接，校验身份
    1. 查询缓存：命中则换回，否则往下执行。8.0已弃用此功能
    1. 解析sql：通过解析器进行词法分析、语法分析、构建语法树
    1. 执行sql

    • 4.1 预处理：检查表、字段是否存在；*扩展为所有列

    • 4.2 优化阶段：基于成本的考虑，选择查询成本最小的执行计划

    • 4.3 执行阶段： 根据执行计划执行sql查询，从存储引擎读取记录，返回给客户端

常见mysql的引擎

  • InnoDB

    • 默认的存储引擎，具有ACID事务支持、行级锁、外键约束等。适用于高并发的读写，支持较好的数据完整性和并发控制

  • MyISAM

    • 具有较低的存储空间和内存消耗，使用大量的读写操作。但不支持事务、行级锁、和外键约束，在并发方面和数据完整性有限制

  • Memory

    • 将数据存储在内存中，适用对性能要求较高的读操作，重启会丢失数据，不支持事务、行级锁、外键约束

为什么InnoDB是默认引擎

  • 在事务支持、并发性能、崩溃恢复等方面有优势

  • 事务支持：InnoDB提供事务支持，ACID原子性、一致性、隔离性、持久性，MyIsam不支持事务

  • 并发性能：采用行级锁定，更好的控制并发。MyIsam只支持表锁，锁的粒度较大

  • 崩溃恢复：使用redolog实现崩溃恢复，通过日志文件进行恢复，保证数据的持久性和一致性。Myisam不支持崩溃恢复

innodb与myisam的区别

  • 事务：前者支持，后者不支持

  • 索引结构：前者是聚簇索引，后者非聚簇。

    聚簇索引的文件存放在主键索引的叶子节点上，因此InnoDB必须有主键，通过主键索引的效率很高，但辅助索引需要两次查询，先查询主键，再通过主键查询到数据。因此主键不应该过大，否则会导致其他索引也很大。 而MyISam是非聚簇索引，数据文件分离，索引保存的是数据文件的指针。

  • 锁粒度：InnoDB最小的锁粒度是行锁，后者是表锁。一个更新语句会锁主整张表，因此并发访问受到限制。

  • count效率：InnoDB不保存表的具体行数，执行select count会扫描整张表，MyISam使用变量保存行数，速度很快

数据文件分为哪几种

  • 每创建一个数据库database，/var/lib/mysql/就会创建一个database目录，然后保存表结构和表数据的文件。

  • 例如，my_test数据库、t_order表

  • db.opt t_order.frm t_order.ibd

  • .opt存储当前数据库的默认字符集和字符校验规则

  • .frm保存表结构

  • .ibd保存表数据

索引

定义与作用

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

  • 类似书籍的目录，减少扫描的数据量，提高数据查询
  • 没用到索引就会全表扫描，O（n）
  • 用到索引，基于二分查找，通过索引快速定位到目标数据，索引数据结构一般是b+树，复杂度log(d*N) d表示节点允许的最大子节点个数
  • 有主键时，默认使用主键作为索引键
  • 无主键，选择第一个不包含null值的唯一列作为聚簇索引的索引键
  • 都没有的话，InnoDB自动生成一个隐士自增id列作为聚簇索引的索引键

索引的优缺点

优点：

  • 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 缺点：
  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

索引底层数据结构选型

索引类型总结

按照数据结构维度划分：

  • BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
  • 哈希索引：类似键值对的形式，一次即可定位。
  • RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式角度划分：

  • 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
  • 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

  • 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
  • 普通索引：仅加速查询。
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
  • 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
  • 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  • 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

MySQL 8.x 中实现的索引新特性：

  • 隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。
  • 降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
  • 函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引

二级索引（Secondary Index）的叶子节点存储的数据是主键的值，也就是说，通过二级索引可以定位主键的位置，二级索引又称为辅助索引/非主键索引。

唯一索引，普通索引，前缀索引等索引都属于二级索引。

  • 唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
  • 普通索引(Index):普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
  • 前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
  • 全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚簇索引与非聚簇索引

聚簇索引

聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

聚簇索引的优缺点

优点：

  • 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
  • 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

  • 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  • 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的

非聚簇索引

非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚簇索引的优缺点

优点：

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点：

  • 依赖于有序的数据:跟聚簇索引一样，非聚簇索引也依赖于有序的数据
  • 可能会二次查询(回表):这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

覆盖索引和联合索引

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为 覆盖索引（Covering Index） 。

在 InnoDB 存储引擎中，非主键索引的叶子节点包含的是主键的值。这意味着，当使用非主键索引进行查询时，数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配

选择合适的字段创建索引

  • 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

避免索引失效

  • SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
  • 创建了组合索引，但查询条件未遵守最左匹配原则;
  • 在索引列上进行计算、函数、类型转换等操作;
  • 以 % 开头的 LIKE 查询比如 LIKE '%abc';;
  • 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
  • IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
  • 发生隐式转换;

如何分析 SQL 语句是否走索引查询

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

MySQL 查询缓存

执行查询语句的时候，会先查询缓存，如果缓存中有对应的查询结果，就会直接返回。

查询缓存不命中的情况：

  1. 任何两个查询在任何字符上的不同都会导致缓存不命中。
  2. 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。
  3. 缓存建立之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十 MB 比较合适。此外，还可以通过 sql_cache 和 sql_no_cache 来控制某个查询语句是否需要缓存：

SELECT sql_no_cache COUNT(*) FROM usr;

MySQL 日志

MySQL 中常见的日志类型主要有下面几类（针对的是 InnoDB 存储引擎）：

  • 错误日志（error log） ：对 MySQL 的启动、运行、关闭过程进行了记录。
  • 二进制日志（binary log，binlog） ：主要记录的是更改数据库数据的 SQL 语句。
  • 一般查询日志（general query log） ：已建立连接的客户端发送给 MySQL 服务器的所有 SQL 记录，因为 SQL 的量比较大，默认是不开启的，也不建议开启。
  • 慢查询日志（slow query log） ：执行时间超过 long_query_time秒钟的查询，解决 SQL 慢查询问题的时候会用到。
  • 事务日志(redo log 和 undo log) ：redo log 是重做日志，undo log 是回滚日志。
  • 中继日志(relay log) ：relay log 是复制过程中产生的日志，很多方面都跟 binary log 差不多。不过，relay log 针对的是主从复制中的从库。
  • DDL 日志(metadata log) ：DDL 语句执行的元数据操作。

二进制日志（binary log，binlog）

inlog(binary log 即二进制日志文件) 主要记录了对 MySQL 数据库执行了更改的所有操作(数据库执行的所有 DDL 和 DML 语句)，包括表结构变更（CREATE、ALTER、DROP TABLE…）、表数据修改（INSERT、UPDATE、DELETE...），但不包括 SELECT、SHOW 这类不会对数据库造成更改的操作。

binlog的格式

  • Statement 模式 ：每一条会修改数据的sql都会被记录在binlog中，如inserts, updates, deletes。
  • Row 模式 （推荐）: 每一行的具体变更事件都会被记录在binlog中。
  • Mixed 模式 ：Statement 模式和 Row 模式的混合。默认使用 Statement 模式，少数特殊具体场景自动切换到 Row 模式。

binlog的作用

binlog 最主要的应用场景是 主从复制 ，主备、主主、主从都离不开binlog，需要依靠 binlog 来同步数据，保证数据一致性。

  1. 主库将数据库中数据的变化写入到 binlog
  2. 从库连接主库
  3. 从库会创建一个 I/O 线程向主库请求更新的 binlog
  4. 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
  5. 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
  6. 从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）。

重新生成binlog的场景

● MySQL服务器停止或重启；
● 使用 flush logs 命令后；
● binlog 文件大小超过 max_binlog_size变量的阈值后。

日志

分类

  • redo log重做日志
    • 存储引擎层生成的日志，实现了事务的持久性，用于掉电故障后恢复
  • undo log回滚日志
    • 存储引擎层的日志，实现事务的原子性，用于事务的回滚
  • bin log二进制日志
    • server层的日志，用于数据备份和主从复制
  • relay log中继日志
    • 用于主从复制下，slave通过io线程拷贝master的bin log后本地生成的日志
  • 慢查询日志
    • 记录执行时间过长的sql，需要设置阈值后手动开启

讲一下binlog

  • 记录所有对数据库操作（不包括查询）。以二进制的形式存储

    1. 执行更新操作 2.记录在binlog中 3.若启用了主从复制，从库读取主库的binlog，在从库上从放这些操作，保持数据同步

  • 三种格式。 基于sql语句：记录sql语句，更节省空间，使用随机函数、now等会导致结果不一致 基于行的日志：记录修改前后的数据。结果请确，但占用空间大 混合日志模式：二者结合，自动选择。

undolog日志的作用是什么

  • 主要用于撤销回退，保证原子性。

有了undolog为什么还需要redolog

  • undolog主要用于回滚事务、mvcc。记录修改之前的数据

  • redolog主要用于保证数据的持久性，数据库崩溃也能恢复。

    • 数据修改后，innodb会讲这次修改记录优先记录到redolog中，而不是立即写入磁盘的数据库文件。是记录“未来“的操作。

redolog如何保证持久性

  • 事务提交之前，所做的修改操作记录到redolog，在将数据写入磁盘。写入磁盘之前即使宕机也能恢复

  • redolog采用追加写入，日志记录追加到末尾，而不是随机写入，减少磁盘的随机io操作

  • 定期将内存中数据刷新到磁盘，将最新的LSN记录到磁盘

能不能只用binlog不能relog

  • 不行，binlog是 server 层的日志，没办法记录哪些脏页还没有刷盘，redolog 是存储引擎层的日志，可以记录哪些脏页还没有刷盘，这样崩溃恢复的时候，就能恢复那些还没有被刷盘的脏页数据。

binlog的两阶段提交过程

update语句的具体执行过程

  • UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

      1. 执行器负责具体执行，调用存储引擎的接口，通过主键索引书搜索获取id=1这一行记录； 如果id=1这一行所在的数据也本来就在buffer pool中，直接返回给执行器更新； 如果不在，将数据页从磁盘读入到buffer pool中，返回记录给执行器
      1. 执行器得到聚簇索引后，会看一下更新前的记录和更新后的记录是否一样。 一样则不进行后续流程；不一样则把更新前的记录和更新后的记录都当作参数传给InnoDB层，让InnoDB执行更新记录的操作。
      1. 开启事务，InnoDB层更新记录前，首先要记录相应的undolog，因为这个更新操作，需要把被更新的列的旧值记下来，也就是要生成一条undolog，undolog会写入buffer pool的undo页面，不过在内存修改该undo页面后，需要记录对应的redolog

主从复制

定义

  • 允许将一个数据库服务器的数据同步到另一个数据库服务器上。

原理

  • 依赖于binlog，记录MySQL上所有变化并以二进制形式保存在磁盘上。

  • 复制的过程就是将binlog中的数据从主库传输到从库

过程

  • 写入binlog：主库写入binlog，提交事务，并更新本地存储数据

  • 同步binlog：把binlog复制到从库上，每个从库把binlog写到暂存日志中

  • 回放binlog：回访binlog并更新存储引擎中的数据

主从延迟

  • 强制走主库方案：对于大事务或者资源密集型操作，直接在主库上执行，避免从库的额外延迟

分库分表

  • 分库

    • 水平扩展数据库，将数据划分到多个独立的数据库中，每个数据库只负责存储部分数据

    • 主要是为了解决并发连接过度，单机sql扛不住

  • 分表

    • 单个表拆分成多个表，每个表只负责一部分数据。提高查询效率，减轻单个表的压力

性能调优

explain的作用

  • 查看sql的执行计划，主要用来分析sql的执行过程。比如有没有走索引、有没有外部排序、有没有索引覆盖

查询速度很慢的解决方案

  • 分析查询语句

    • 使用explain命令分析sql执行计划，找出慢查询的原因，比如是否使用了全表扫描，是否存在索引未利用的情况

  • 创建或优化索引

    • 根据查询条件创建适合的索引。尤其是经常where子句的字段，orderby groupby。查询中涉及多个字段，可以创建联合索引

  • 避免索引失效

    • 不使用做模糊匹配、函数计算、表达式计算等

  • 查询优化

    • 不适用select * 。只查询真正需要的列；使用索引覆盖，即索引包含所有查询的字段；连表查询最好要以小表驱动大表，并且被驱动表的字段要有索引。

  • 分页优化

    • 针对limit n,y深分页的查询优化，可以把limit查询转换成某个位置的查询

  • 优化数据库表

    • 数据超过千万级别，考虑是否需要将大表拆分成小表，减轻单个表的查询压力。

  • 使用缓存

    • 引入redis，存储热点数据和频繁查询的结果。需要考虑一致性的问题，对于读请求会选择旁路缓存策略，对于写请求会选择先更新db再删除缓存

explain用到的索引不正确，如何干预

  • 使用force index，强制走索引

事务

四种特性

  • 原子性Atomicity
    • 一个事务的所有操作，要么全部完成要么全部不完成，不会结束在中间的某个环节。而且食物中发生错误，会被回滚到事务开始前的状态。
  • 一致性Consistency
    • 实务操作前后，数据满足完整性约束，数据库保持一致性状态。
      • 例如A100，B200转账50。一致性要求，事务开始前后总余额都是300。若一方扣了钱，另一方为涨钱，则破坏了一致性。
  • 隔离性Isolation
    • 数据库允许多个事务同时对其数据进行读写和修改，隔离性防止多事务由于交叉执行而导致数据的不一致。多个事务同时使用数据，不会相互干扰。
      • 例如消费者购买商品，不会影响其他消费者购买商品
  • 持久性Durability
    • 事务处理接触，对数据的修改是永久的，即使系统故障也不会丢失数据

InnoDB使用什么技术保证ACID

  • 持久性：redolog，重做日志
  • 原子性：undolog回滚日志
  • 隔离性：MVCC多版本并发控制或者锁机制
  • 一致性：持久性+原子性+隔离性

并发相关问题。脏读、幻读、不可重复读

  • 多个客户端同时连接，同时处理多个事务，就可能出现脏读、幻读、不可重复读

  • 脏读Dirty Read

    • 一个事务读到了另一个事务尚未提交的数据。如果该事务之后回滚了，读取的数据就是无效的或者是不正确的、设置read commited

      • 银行系统：一个余额正在被调整，但尚未提交，另一个事务读取了临时的余额，导致看到错误的记录

      • 库存管理系统：商品数量更新尚未提交，另一个事务读取了临时的数量

  • 不可重复读Non-repeatable Read

    • 不可重复读指的是同一事务内两次读取同一数据，结果却不一致。因为在两次读取之间，另一个事务修改了该数据并提交了、设置repeated read

  • 幻读Phantom Read

    • 同一事务中，第一次查询时返回了一组记录，而在该事务进行插入或者删除后，再次查询相同的数据时，却多了一些“幻影记录”。（别的事物插入删除了一些记录）、设置串行化

解决脏幻不可重复读

  • 锁机制。锁可以看作是悲观控制的模式

    • 行级锁、表级锁、页级锁。读写时加锁，确保同时只有一个操作能够访问或者修改
    • 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
    • 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

  • 事务隔离级别

    • 多种级别，读未提交、读已提交、可重复读、串行化。并发执行时，设置合适的事务隔离级别，控制事务的隔离程度

  • MVCC多版本并发控制。可以看作是乐观控制的模式

    • 即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。
      • 通过数据库中保存不同版本的数据，实现不同事务的隔离。读取时，MySQL会根据事务的隔离级别来选择合适的数据版本，从而保证数据的一致性

事务的隔离级别

  • 读未提交read uncommited

    • 事务还没提交，做的变更就能被其他事务看到。最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  • 读已提交read commited

    • 事务提交之后，变更才能被其他事务看到。允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

  • 可重复读repeatable read

    • 一个事务执行过程中看到的数据，一直跟着这个事务启动时看到的数据保持一致。默认隔离级别
    • 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  • 串行化serializable

    • 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
    • 对记录加上读写锁。多个事务对此纪录进行读写时，发生读写冲突需要等到前一个事务处理完成

默认隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

可重复读隔离级别下，A事务提交的数据，在B事务能看见吗？

  • 不能。B在开始后创建一个快照，即为读取的数据版本，事务B只能读到这一版本的数据

保证不幻读

  • 设置串行化隔离级别。本质是通过行级锁实现。是最高级别的隔离。强制事务逐个穿行执行，防止其他食物插入、修改、删除记录。但是会限制并发性。

MVCC多版本并发控制实现原理

  • 核心思想

    • 为每个事务提供数据的不同版本，允许多个事务并发地读取和写入数据库，而不会相互阻塞。当一个事务正在读取数据时，不会立即看到其他书屋正在写入的数据，而是读取数据的历史版本。

  • 基本原理

    • 使用Undo log回滚日志和事务版本控制来实现mvcc

一条update是不是原子性的

  • 是。锁+undolog。 执行update，会加行级锁，保证事务更新记录不会被其他事务干扰。 事务执行中，会生成undolog，事务执行失败进行回滚

滥用事务，或者一个事务内很多sql的弊端

  • 事务的资源在提交后才会被释放。比如存储资源、锁

    • sql多，锁定的数据太多，造成大量的死锁和锁超时

    • 回滚记录会占用大量的存储空间，事务回滚时间长。每条记录在更新时，都会同时记录一条回滚操作。

    • 执行时间长，容易造成主从延迟，主库上必须等事务执行完成才会写入binlog，再传给备库。

锁

分类

  • 按操作类型
    • 共享锁S锁
      • 一个事务对一个记录上S锁，其他事务可以读取该记录，但不能进行修改。
      • 允许多个事务同时读取同一条记录，但不能修改
    • 排他锁X锁
      • 当一个事务对一条记录加上排他锁后，其他事务即不能读取也不能修改此纪录。只有持有排他锁的事务才能对其进行读写
      • 排他锁禁止任何事务同时访问该记录，只当前事务可以独占读写
  • 按锁的范围
    • 表锁
      • 对整张表加锁，其他事务对此表不能进行任何操作
      • 表锁的粒度较大，容易导致并发冲突，但管理开销小
    • 行锁
      • 对一条特定记录加锁，允许其他事务同时对其他记录进行操作。并发性好，但管理开销大
  • 意向锁
    • 特殊的锁，用于在表级别和行级别之间协调，目的是提高锁的效率。
      • 意向共享锁IS锁
        • 表示事务打算在某些行上加共享锁
      • 意向排他锁IX锁
        • 表示事务打算在某些行上加排他锁
  • 读写锁
    • 读锁，类似共享锁，其他食物可以同时读取该数据，但不能修改
    • 写锁，类似排他锁，其他食物不能同时读取或写入
  • 并发控制策略
    • 悲观锁
      • 假设每次对数据的操作都有冲突，因此在读取数据时就上锁，阻止其他事务对该数据进行修改，确保数据的一致性
    • 乐观锁
      • 假设并发操作冲突是少数，事务提交前不加锁，旨在更新数据时才检查是否有冲突
      • 通常比较版本号、时间戳来检测冲突，有冲突则回滚

行级锁使用注意事项

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！！

不过，很多时候即使用了索引也有可能会走全表扫描，这是因为 MySQL 优化器的原因。

InnoDB 有哪几类行锁

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

  • 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
  • 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
  • 临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

MySQL两个线程的update语句同时处理一条数据

  • 发生阻塞，会加行级锁

如果2个范围不是主键或索引？还会阻塞吗？

  • 查询的字段不是索引会触发全表扫描，全部索引都加行级锁。

MySQL 性能优化

MySQL 如何存储 IP 地址？

MySQL 提供了两个方法来处理 ip 地址

  • INET_ATON()：把 ip 转为无符号整型 (4-8 位)
  • INET_NTOA() :把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可

什么是分库？

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

什么是分表？

分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。

举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。

水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

什么情况下需要分库分表？

  • 单表的数据达到千万级别以上，数据库读写速度比较缓慢。
  • 数据库中的数据占用的空间越来越大，备份时间越来越长。
  • 应用的并发量太大（应该优先考虑其他性能优化方法，而非分库分表）。
  • Loading...
  • Loading...
  • Loading...
  • Loading...
  • Loading...