数据库基础知识总结

什么是数据库，数据库管理系统，数据库系统，数据库管理员

四个概念描述了从数据本身到管理整个体系的不同层次，这里用一个图书馆的例子来把它们串联起来理解。

  • 数据库(DataBase/DB): 它就像是图书馆里存放的所有书籍和资料。从技术上讲，数据库就是按照一定数据模型组织、描述和储存起来的、可以被各种用户共享的结构化数据的集合。数据库就是我们最终要存取的信息本身。
  • 数据库管理系统(Database Management System/DBMS): 它就像是整个图书馆的管理系统，包括图书的分类编目规则、借阅归还流程、安全检查系统等。从技术上讲是一种大型软件，比如常用的Mysql、PostgreSQL等。它的核心职责是科学地组织和存储数据、高效地获取和维护数据；为我们屏蔽了底层文件操作的复杂性，提供了一套标准接口(如SQL)来操纵数据，并负责并发控制、事务管理、权限控制等复杂问题。
  • 数据库系统(Database System/DBS): 它就是整个正常运转的图书馆。这是一个更大的概念，不仅包括书(DB)和管理系统(DBMS)，还包括了硬件、应用和使用的人。
  • 数据库管理员(Database Administrator/DBA): 他就是图书馆的馆长，负责整个数据库系统正常运行。他的职责非常宽泛，包括数据库的设计、安装、监控、性能调优、备份与恢复、安全管理等，确保整个系统的稳定、高效和安全。

DB和DBMS我们通常会搞混，这里再简单提一下：通常我们说“用MySQL数据库”，其实是用MySQL(DBMS)来管理一个或多个数据库(DB)。

DBMS有哪些主要的功能

主要提供四大核心功能：

  • 数据定义： 这是 DBMS 的基础。它提供了一套数据定义语言(Data Definition Language/DDL)，让我们能创建、修改和删除数据库中的各种对象。这不仅仅是定义表的结构(比如字段名、数据类型)，还包括定义视图、索引、触发器、存储过程等。
  • 数据操作： 这是我们作为开发者日常使用最多的功能。它提供了一套数据操作语言(Data Manipulation Language/DML)，核心就是我们熟悉的增、删、改、查(CRUD)操作。它让我们能够方便地对数据库中的数据进行操作和检索。
  • 数据控制： 这是保证数据正确、安全、可靠的关键。通常包含并发控制、事务管理、完整性约束、权限控制、安全性限制等功能。
  • 数据库维护： 这部分功能是为了保障数据库系统的长期稳定运行。它包括了数据的导入导出、数据库的备份与恢复、性能监控与分析以及系统日志管理等。

你知道哪些类型的DBMS

关系型数据库

除了我们最常用的关系型数据库(RDBMS)，比如MySQL(开源首选)、PostgreSQL(功能最全)、Oracle(企业级)，它们基于严格的表结构和 SQL，非常适合结构化数据和需要事务保证的场景，例如银行交易、订单系统。

近年来，为了应对互联网应用带来的海量数据、高并发和多样化数据结构的需求，涌现出了一大批NoSQL和NewSQL数据库。

NoSQL 数据库

它们的共同特点是为了极致的性能和水平扩展能力，在某些方面(通常是事务)做了妥协。

  1. 键值数据库，代表是 Redis
    • 特点： 数据模型及其简单，就是一个巨大的Map，通过Key来存取Value。内存操作，性能极高。
    • 使用场景： 非常适合做缓存、会话存储、计数器等对读写要求极高的场景。
  2. **文档数据库，代表是 MongoDB **
    • 特点： 它存储的是半结构化的文档(比如 JSON/BSON)，结构灵活，不需要预先定义表结构。
    • 使用场景： 特别适合那些数据结构多变、快速迭代的业务，比如用户画像、内容管理系统、日志存储等。
  3. 列式数据库，代表是 HBase, Cassandra
    • 特点： 数据是按列族而不是按行来存储的。这使得它在对大量行进行少量列的读取时，性能极高。
    • 适用场景： 专为海量数据存储和分析设计，非常适合做大数据分析、监控数据存储、推荐系统等需要高吞吐量写入和范围扫描的场景。
  4. 图形数据库，代表是 Neo4j
    • 特点： 数据模型节点(Nodes)和边(Edges)，专门用来存储和查询实体之间的复杂关系。
    • 适用场景： 在社交网络(好友关系)、推荐引擎(用户-商品关系)、知识图谱、欺诈检测(资金流动关系)等场景下，表现远超关系数据库。

NewSQL 数据库

由于NoSQL不支持事务，很多对于数据安全要求非常高的系统(比如财务系统、订单系统、交易系统)就不太适合使用了。但是这些系统往往有存储大量数据的需求，解决这个问题往往只能通过购买更好的计算机或者是通过数据库中间件来提高存储能力。不过，前者的金钱成本太高，后者的开发成本太高，NewSQL 因此而被设计出来。

简单来说，NewSQL就是：分布式存储+SQL+事务。NewSQL 不仅具有 NoSQL 对海量数据的存储管理能力，还保持了传统数据库支持 ACID 和 SQL 等特性。因此，NewSQL 也可以称为分布式关系型数据库。

NewSQL 数据库设计的一些目标：

  1. 横向扩展(Scale Out): 通过增加机器的方式来提高系统的负载能力。与之类似的是 Scale Up(纵向扩展)，升级硬件设备的方式来提高系统的负载能力。
  2. 强一致性(Strict Consistency): 在任意时刻，所有节点中的数据是一样的。
  3. 高可用(High Availability): 系统几乎可以一直提供服务。
  4. 支持标准 SQL(Structured Query Language): PostgreSQL、MySQL、Oracle 等关系型数据库都支持 SQL。
  5. 事务(ACID): 原子性(Atomicity)、一致性(Consistency)、 隔离性(Isolation)、 持久性(Durability)。
  6. 兼容主流关系型数据库: 兼容MySQL、Oracle、PostgreSQL等常用关系型数据库。
  7. 云原生(Cloud Native): 可在公有云、私有云、混合云中实现部署工具化、自动化。
  8. HTAP(Hybrid Transactional/Analytical Processing): 支持 OLTP 和 OLAP 混合处理。

NewSQL数据库代表: Google 的 F1/Spanner、阿里的 OceanBase、PingCAP 的 TiDB 。

什么是元组、码、候选码、主码、外码、主属性、非主属性

在关系型数据库理论中，理解这些核心观念对于数据库设计和规范化至关重要。这些概念构成了关系数据库的理论基础。

基础概念

  • 元组(Tuple)： 元组是关系数据库中的基本单位，在二维表中对应一行记录。每个元组包含了一个实体的完整信息。例如，在学生表中，每个学生的完整信息构成了一个元组。
  • 码(Key)： 码是能够唯一标识关系中元组的一个或多个属性的集合。码的主要作用是保证数据的唯一性和完整性。

码的分类

  • 候选码(Candidate Key)： 候选码是能够唯一标识元组的最小属性集合，其任何真子集都不能唯一标识元组。一个关系可能有多个候选码。例如，在学生表中，如果学号能唯一标识学生同时身份证号也能唯一标识的话，那么{学号}和{身份证号}都是候选码。
  • 主码/主键(Primary Key)： 主码是从候选码中选择的一个，用于唯一标识关系中的元组。每个关系只能有一个主码，但可以有多个候选码。选择主码时通常考虑：简单性、稳定性、无业务含义等因素。
  • 外码/外键(Foreign Key)： 外码是一个关系中的属性和属性组，它对应另一个关系的主码。外码用于建立和维护两个关系之间的联系，是实现参考完整性的重要机制。例如，在选课表中的学号如果引用学生表的主码学号，则选课表中的学号就是外码。

属性分类

  • 主属性(Prime Attribute)： 主属性是包含在任何一个候选码中的属性。如果一个关系有多个候选码，那么这些候选码中出现的所有属性都是主属性。例如，工人关系(工号，身份证号，姓名，性别，部门)中，如果{工号}和{身份证号}都是候选码，那么“工号”和“身份证号”都是主属性。
  • 非主属性(Non-prime Attribute)： 非主属性是不包含在任何候选码中的属性。这些属性完全依赖于候选码来确定其值。在上述工人关系中，“姓名”、“性别”、“部门”都是非主属性。

什么是ER图

经常被问到的一个点！！！

ER 图全称是Entity Relationship Diagram(实体联系图)，提供了表示实体类型、属性和联系的方法。

ER 图由下面3个要素组成：

  • 实体： 通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩阵框表示。
  • 属性： 即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。
  • 联系： 即实体与实体之间的关系，在 ER 图中用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体之间的联系。

下图是 ER 图的一个实例，展示的实体之间的关系有1对1(1:1)，1对多(1:N)，此外还有多对多(M:N)。

ER图示例

数据库范式

数据库范式有3种：

  • 1NF(第一范式)：属性不可再分。
  • 2NF(第二范式)：1NF的基础之上，消除了非主属性对于码的部分函数依赖。
  • 3NF(第三范式)：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。

1NF(第一范式)

属性(对应表中的字段)不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF是所有关系型数据库的最基本要求，也就是说关系型数据库中创建的表一定满足第一范式。

2NF(第二范式)

2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。如下图所示，展示了第一范式到第二范式的过渡。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。

第二范式示例

一些重要的概念：

  • 函数依赖(functional dependency)： 若在一张表中，在属性(或属性组) X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作X -> Y。
  • 部分函数依赖(partial functional dependency)： 如果X -> Y，并且存在 X 的一个真子集 Xo，使得Xo -> Y，则称 Y 对 X 部分函数依赖。比如学生基本信息表 R 中(学号，身份证号，姓名)当然学号属性取值是唯一的，在 R 关系中，(学号，身份证号) -> (姓名)，(学号) -> (姓名)，(身份证号) -> (姓名)；所以姓名部分函数依赖于(学号，身份证号)。
  • 完全函数依赖(full functional dependency)： 在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表 R(学号，班级，姓名)假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，(学号，班级) -> (姓名)，但是(学号) -> (姓名) 不成立，(班级) -> (姓名) 不成立，所以姓名完全函数依赖于(学号，班级)。
  • 传递函数依赖： 在关系模式 R(U)中，设 X，Y，Z是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，(X∪Y)∩Z={}，则称 Z 传递函数依赖(transitive functional dependency)于 X。传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。比如在关系 R(学号，姓名，系名，系主任)中，学号 -> 系名，系名 -> 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

3NF(第三范式)

3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 -> 系名，系名 -> 系主任，存在非主属性系主任对于学号的传递函数依赖，所以该表的设计不符合 3NF 的要求。

主键和外键的区别

从定义和属性上看，它们的区别是：

  • 主键(Primary Key)： 它的核心作用是唯一标识表中的每一行数据。因此，主键列的值必须是唯一的(Unique)且不能为空(Not Null)。一张表只能有一个主键。主键保证了实体完整性。
  • 外键(Foreign Key)： 它的核心作用是建立并强制两张表之间的关联关系。一张表中的外键列，其值必须对应另一张表中某行的候选键值(通常是主键，也可以是唯一键)，或者是一个NULL值。因此，外键的值可以重复，也可以为空。一张表可以有多个外键，分别关联到不同的表。外键保证了引用完整性。

用一个简单的电商例子来说明：假设我们有两张表：users (用户表) 和 orders (订单表)。

  • 在 users 表中，user_id 列是主键。每个用户的 user_id 都是独一无二的，我们用它来区分张三和李四。
  • 在 orders 表中，order_id 是它自己的主键。同时，它会有一个 user_id 列，这个列就是一个外键，它引用了 users 表的 user_id 主键。

这个外键约束就保证了：

  1. 你不能创建一个不属于任何已知用户的订单（ user_id 在 users 表中不存在）。
  2. 你不能删除一个已经下了订单的用户（除非设置了级联删除等特殊规则）。

为什么不推荐使用外键与级联？

【强制】不得使用外键和级联，一切外键概念必须在应用层解决。

解释：以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

问过 ai 的代码示例，大概就是字段仍然保留只不过是去掉外键约束，然后在代码层面进行手动检查，这样做的坏处就是维护成本加大，但好处很多：白盒环境、处理更加灵活、高并发等等。

为什么不用外键呢？

  1. 增加了复杂性： 每次做DELETE或者UPDATE都必须考虑外键约束，会导致开发的时候很痛苦, 测试数据极为不方便; 外键的主从关系是定的，假如哪天需求有变化，数据库中的这个字段根本不需要和其他表有关联的话就会增加很多麻烦。
  2. 增加了额外工作： 数据库需要增加维护外键的工作，比如当我们做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，保证数据的一致性和正确性，这样会不得不消耗数据库资源。如果在应用层面去维护的话，可以减小数据库压力。
  3. 对分库分表不友好： 因为分库分表下外键是无法生效的。

外键虽然存在一些问题但是也有很多好处(存在即有一定道理，系统不涉及到分库分表，并发量不是很高的情况下还是可以考虑使用的)：

  1. 保证数据库数据的一致性和完整性
  2. 级联操作方便，减轻了程序代码量

什么是存储过程

存储过程是数据库预编译的 SQL 语句集合，它将多条 SQL 语句和程序逻辑控制语句(如IF-ELSE、WHILE循环等)封装在一起，形成一个可重复调用的数据库对象。

  • 存储过程的优势： 在传统企业级应用中，存储过程具有一定的实用价值。当业务逻辑复杂时，需要执行大量 SQL 语句才能完成一个业务操作，此时可以将这些语句封装成存储过程，简化调用过程。由于存储过程在创建时就已经编译并存储在数据库中，执行时无需重新编译，因此相比动态 SQL 语句具有更好的执行性能。同时，一旦存储过程调试完成，其运行相对稳定可靠。

  • 存储过程的局限性： 然而，在现代互联网架构中，存储过程的使用越来越少。主要原因包括：调试困难，缺乏成熟的调试工具；扩展性差，修改业务逻辑需要直接修改数据库对象；移植性差，不同数据库系统的存储过程语法差异较大；占用数据库资源，增加数据库服务器负担；版本管理困难，不便于进行代码版本控制。

  • 行业规范： 基于以上原因，许多互联网公司的开发规范中明确限制或禁止使用存储过程。例如，《阿里巴巴 Java 开发手册》中明确规定禁止使用存储过程，推荐将业务逻辑放在应用层实现，保持数据库的简单和高效。

    【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

DROP、DELETE、TRUNCATE 的区别

在数据库操作中，DROP、DELETE、TRUNCATE是三个常用的数据删除命令，它们在功能、性能和使用场景上存在显著差异。

  • DROP 命令：
    • 语法：DROP TABLE 表名
    • 作用：完全删除整个表，包括表结构、数据、索引、触发器、约束等所有相关对象
    • 使用场景：当表不再需要时使用
  • TRUNCATE 命令：
    • 语法：TRUNCATE TABLE 表名
    • 作用：清空表中所有数据，但保留表结构
    • 特点：自增长字段(AUTO_INCREMENT)会重置为初始值(通常为1)
    • 使用场景：需要快速清空表数据但保留表结构时使用
  • DELETE 命令：
    • 语法：DELETE FROM 表名 WHERE 条件
    • 作用：删除满足条件的数据行，不带 WHERE 子句时删除所有数据
    • 特点：自增长字段不会重置，继续从之前的值递增
    • 使用场景：需要有选择地删除部分数据时使用

TRUNCATE和不带WHERE子句的DELETE、以及DROP都会删除表内的数据，但是**TRUNCATE和DELETE只删除数据不删除表的结构(定义)，执行DROP语句，此表的结构也会删除，也就是执行DROP之后对应的表不复存在**。

对表结构的影响

  • DROP：删除表结构和所有数据，表将不复存在
  • TRUNCATE：仅删除数据，保留表结构和定义
  • DELETE：仅删除数据，保留表结构和定义

触发器

  • DELETE 操作会触发相关的 DELETE 触发器
  • TRUNCATE 和 DROP 不会触发 DELETE 触发器

事务和回滚

  • DROP 和 TRUNCATE 属于DDL操作，执行后立即生效，不能回滚
  • DELETE 属于DML操作，可以回滚(在事务中)

执行速度

一般来说：DROP > TRUNCATE > DELETE（不一定准确，待实际验证)

  • DELETE命令执行的时候会产生数据库的binlog日志，因为日志记录是需要消耗时间的，但是也有一个好处方便数据回滚恢复。
  • TRUNCATE命令执行的时候不会产生数据库日志，因此比DELETE要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
  • DROP命令会把表占用的空间全部释放掉。

应该更关注使用场景而不是执行效率。

DML 语句和 DDL 的语句区别

  • DML 是数据库操作语言(Data Manipulate Language)的缩写，是指对数据库中表记录的操作，主要包括记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
  • DDL(Data Definition Language)是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员(DBA)所使用，一般的开发人员很少使用。

数据库设计的步骤

数据库设计步骤

需求分析阶段

目标： 深入了解和分析用户需求，明确系统边界

主要工作：

  • 收集和分析数据需求：确定需要存储哪些数据，数据量太小，数据更新频率
  • 明确功能需求：系统需要支持哪些业务操作，各操作的优先级
  • 定义性能需求：响应时间要求，并发用户数，数据吞吐量
  • 确定安全需求：数据访问权限，加密要求，审计要求

产出物： 需求规格说明书、数据字典初稿

概念结构设计阶段

目标： 将需求转化为信息世界的概念模型

主要工作：

  • 识别实体：确定系统中的主要对象
  • 定义属性：明确每个实体的特征
  • 建立联系：确定实体之间的关系(一对一、一对多、多对多)
  • 绘制 E-R 图(实体-关系图)

产出物： E-R 图、概念数据模型文档

逻辑结构设计阶段

目标： 将概念模型转换为特定 DBMS 支持的逻辑模型

主要目标：

  • E- R 图向关系模型转换：将实体转换为表，属性转换为字段
  • 规范化处理：通过范式化消除数据冗余和更新异常（通常达到3NF）
  • 定义完整性约束：主键、外键、唯一性约束、检查约束
  • 优化模型：根据性能需求进行适当的反规范化

产出物： 逻辑数据模型、表结构设计文档

物理结构设计阶段

目标： 确定数据的物理存储方案和访问方法

主要方法：

  • 选择存储引擎：如 MySQL 的 InnoDB、MyISAM 等
  • 设计索引策略：确定需要建立的索引类型和字段
  • 分区设计：对大表进行分区以提高性能
  • 确定存储参数：表空间大小、数据文件位置、缓冲区配置
  • 制定备份策略：全量备份、增量备份的频率和方式

数据库实施阶段

目标： 将设计转化为实际运行的数据库系统

主要工作：

  • 创建数据库和表结构：编写和执行 DDL 语句
  • 开发存储过程和触发器(如需要)
  • 编写应用程序接口
  • 导入初始数据
  • 系统集成测试：功能测试、性能测试、压力测试
  • 用户培训和文档编写

产出物： 数据库脚本、测试报告、用户手册

运行和维护阶段

目标： 确保数据库系统稳定高效运行

主要工作：

  • 日常监控：性能监控、空间监控、错误日志分析
  • 性能优化：查询优化、索引调整、参数调优
  • 数据备份和恢复：定期备份、恢复演练
  • 安全管理：权限管理、安全补丁更新、审计
  • 容量规划：预测数据增长，提前扩容
  • 变更管理：需求变更的评估和实施

产出物： 运维报告、优化方案、变更记录

设计原则

在整个设计过程中应遵循：数据独立性原则、完整性原则、安全性原则、可扩展性原则和标准化原则。

NoSQL 基础知识总结

什么是 NoSQL

NoSQL(Not Only SQL 的缩写)泛指非关系型的数据库，主呀针对的是键值、文档以及图形类型数据存储。并且，NoSQL 数据库天生支持分布式，数据冗余和数据分片等特性，旨在提供可扩展的高可用高性能的数据存储解决方案。

一个常见的误解是 NoSQL 数据库或关系型数据库不能很好地存储关系型数据。NoSQL 数据库可以存储关系型数据——它们与关系型数据库的存储方式不同。

NoSQL 数据库代表：HBase、Cassandra、MongoDB、Redis

NoSQL 例子

SQL 和 NoSQL 有什么区别

SQL 数据库 NoSQL 数据库
数据存储模型 结构化存储，具有固定行和列的表格 非结构化存储。文档：JSON 文档，键值：键值对，宽列：包含行和动态列的表，图：节点和边
发展历程 开发于1970年代，重点是减少数据重复 开发于2000年代后期，重点是提升可扩展性，减少大规模数据的存储成本
例子 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 文档：MongoDB、CouchDB，键值：Redis、DynamoDB，宽列：Cassandra、HBase，图表：Neo4j、Amazon Neptune、Giraph
ACID 属性 提供原子性、一致性、隔离性和持久性(ACID)属性 通常不支持 ACID 事务，为了可扩展性、高性能进行了权衡，少部分支持比如MongoDB，不过在对 ACID 事务的支持上和 MySQL 还是有所区别的
性能 性能通常取决于磁盘子系统。要获得最佳性能，通常需要优化查询、索引和表结构 性能通常由底层硬件集群大小、网络延迟以及调用应用程序来决定
扩展 垂直(使用性能更强大的服务器进行扩展)、读写分离、分库分表 横向(增加服务器的方式横向扩展，通常是基于分片机制)
用途 普通企业级的项目的数据存储 用途广泛比如图数据库支持分析和遍历连接数据之间的关系、键值数据库可以处理大量数据扩展和极高的状态变化
查询语法 结构化查询语言(SQL) 数据访问语法可能因数据库而异

NoSQL 数据库的优势

NoSQL 数据库非常适合许多现代应用程序，例如移动、Web和游戏等应用程序，它们需要灵活、可扩展、高性能和功能强大的数据库以提供卓越的用户体验。

  • 灵活性： NoSQL 数据库通常提供灵活的架构，以实现更快速、更多的迭代开发。灵活的数据模型是 NoSQL 数据库成为半结构化和非结构化数据的理想之选。
  • 可扩展性： NoSQL 数据库通常被设计为通过使用分布式硬件集群来横向扩展，而不是通过添加昂贵和强大的服务器来纵向扩展。
  • 高性能： NoSQL 数据库针对特定的数据模型和访问模型进行了优化，这与尝试使用关系数据库完成类似功能相比可实现更高的性能。
  • 强大的功能： NoSQL 数据库提供强大的 API 和数据类型，专门针对其各自的数据模型而构建。

NoSQL 数据库的类型

主要分为下面的四种类型：

  • 键值： 键值数据库是一种较简单的数据库，其中每个项目都包含键和值。这是极为灵活的 NoSQL 数据库类型，因为应用可以完全控制 value 字段中存储的内容，没有任何限制。Redis 和 DynanoDB 是两款非常流行的键值数据库。

  • 文档： 文档数据库中的数据被存储在类似于 JSON(JavaScript 对象表示法)对象的文档中，非常清晰直观。每个文档包含成对的字段和值。这些值通常可以是各种类型，包括字符串、数字、布尔值、数组或对象等，并且它们的结构通常与开发者在代码中使用的对象保持一致。MongoDB 就是一款非常流行的文档数据库。

  • 图形： 图形数据库旨在轻松构建和运行与高度连接的数据集一起使用的应用程序。图形数据库的典型使用案例包括社交网络、推荐引擎、欺诈检测和知识图形。Neo4j 和 Giraph 是两款非常流行的图形数据库。

  • 宽列： 宽列存储数据库非常适合需要存储大量的数据。Cassandra 和 HBase 是两款非常流行的宽列存储数据库。

PS： 面试题仍然考的是 SQL 语法的掌握程度，需要进行全面复习和记忆。

MySQL 常见面试题总结

MySQL 基础

关系型数据库是什么

顾名思义，关系型数据库(RDB/Rational Database)就是建立在一种关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系(一对一、一对多、多对多)。

关系型数据库中，我们的数据都被存放在了各种表中(比如用户表)，表中的每一行就存放着一条数据(比如一个用户的信息)。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

常见的关系型数据库: MySQL、PostgreSQL、Oracle、SQL Server、SQLite(微信本地的聊天记录的存储就是用的 SQLite)

SQL 是什么

SQL 是一种结构化查询语言(Structured Query Language)，专门用来和数据库打交道，目的是提供一种从数据库读写数据的简单有效的方法。

几乎所有的主流关系库数据库都支持 SQL，适用性非常强。并且，一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。

MySQL 是什么

MySQL 是一种关系型数据库，主要用于持久化存储我们系统中的一些数据比如用户信息。

由于 MySQL 是开源免费且比较成熟的数据库，因此，MySQL 被大量使用在各种系统中。任何人都可以在 GPL(General Public License)的许可下下载并根据个性化的需要对其进行修改。MySQL 的默认端口号是3306。

重点：MySQL 的优点

本质上是在问 MySQL 如此流行的原因。MySQL 成功可以归功于在生态、功能和运维这三个层面上的综合优势。

第一，从生态和成本角度看，它的护城河非常深。

  • 开源免费: 这是它得以广泛普及的基石。任何公司和个人都可以免费使用，极大地降低了技术门槛和初期成本。
  • 社区庞大，生态完善: 经过几十年的发展，MySQL 拥有极其活跃的社区和丰富的生态系统。这意味着你可以在网上找到各种问题的解决方案；同时市面上所有的主流编程语言、框架、ORM 工具、监控系统都对 MySQL 有完美的支持。它的文档也非常丰富，学习资源很多。

第二，从核心技术功能上看，它非常强大且均衡。

  • 强大的事务支持: 这是它作为关系型数据库的立身之本。值得一提的是，InnoDB 默认的可重复读(REPEATABLE-READ)隔离级别，通过 MVCC 和 Next-Key Lock 机制，很大程度上避免了幻读问题，这在很多其他数据库中都需要更高的级别才能做到，兼顾了性能和一致性。

  • 优秀的性能和可扩展性: MySQL 本身经过了海量互联网业务的严酷考验，单机性能非常出色。更重要的是，它围绕着水平扩展，形成了一套非常成熟的架构方案，比如主从复制、读写分离、以及通过中间件实现的分库分表。这让它能够支撑从初创公司到大型互联网平台的各种规模的业务。

第三，从运维和使用角度看，它非常“亲民”。

  • 开箱即用，上手简单: 相比于 Oracle 等大型商业数据库，MySQL 的安装、配置和安装使用都非常简单直观，学习曲线平缓，对于开发者和初级 DBA 非常友好。
  • 维护成本低: 由于其简单性和庞大的社区，找到相关的运维人才和解决方案都相对容易，整体的维护成本也更低。

最近几年， PostgreSQL 的使用量和普及度都大幅增加，需要深入了解。

MySQL 字段类型

下面一张图进行了总结：

summary-of-mysql-field-types

重点：整数类型的 UNSIGNED 属性有什么用

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

例如，TINYINT UNSIGNED 类型的取值范围是 0～255，而普通的 TINYINT 类型的值范围是 -128～127。INT UNSIGNED 类型的取值范围是 0～4294967295，而普通的 INT 类型的值范围是 -2147483648～2147483647。

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

CHAR 和 VARCHAR 的区别是什么

CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VACHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中午，每个都只占用一个字符。

VARCHAR(100) 和 VARCHAR(10) 的区别是什么

VARCHAR(100) 和 VARCHAR(10) 都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR(100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10) 存储超过 10 个字符时，就需要修改表结构才可以。

虽说 VARCHAR(100) 和 VARCHAR(10) 能存储的字符范围不同，但二者存储相同的字符串，所占用的磁盘的存储空间是一样的。

不过，VARCHAR(100) 会消耗更多的内存，这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100) 是按照 100 这个长度来进行的，也就会消耗更多内存。

DECIMAL 和 FLOAT/DOUBLE 的区别是什么

DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类java.math.BigDecimal。

为什么不推荐使用 TEXT 和 BLOB

TEXT 类型类似于 CHAR(0-255 字节) 和 VARCHAR(0-65535 字节)，但可以存储更长的字符串，即长文本数据，例如博客内容。

类型 可存储大小 用途
TINYTEXT 0-255 字节 一般文本字符串
TEXT 0-65535 字节 长文本字符串
MEDIUMTEXT 0-166772150 字节 较大文本数据
LONGTEXT 0-4294967295 字节 极大文本数据

BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

类型 可存储大小 用途
TINYBLOB 0-255 字节 短文本二进制字符串
BLOB 0-65 KB 二进制字符串
MEDIUMBLOB 0-16 MB 二进制形式的长文本数据
LONGBLOB 0-4 GB 二进制形式的极大文本数据

在日常开发中，很少使用 TEXT 类型，但偶尔会用到，而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足，建议避免使用 TEXT。

数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如：

  • 不能有默认值。
  • 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表(《高性能 MySQL》书中有提到)。
  • 检索效率较低。
  • 不能直接创建索引，需要指定前缀长度。
  • 可能会消耗大量的网络和 IO 带宽。
  • 可能导致表上的 DML 操作变慢。

DATETIME 和 TIMESTAMP 的区别是什么，如何选择

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，TIMESTAMP 表示的时间范围更小。

  • DATETIME：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

  • Timestamp：’1970-01-01 00:00:01.000000’ UTC 到 ‘2038-01-19 03:14:07.999999’ UTC

TIMESTAMP 的核心优势在于其内建的时区处理能力。数据库负责 UTC 存储和基于会话时区的自动转换，简化了需要处理多时区应用的开发。如果应用需要处理多时区，或者希望数据库能自动管理时区转换，TIMESTAMP 是自然的选择(注意其时间范围限制，也就是 2038 问题)。

如果应用不涉及时区转换，或者希望应用程序完全控制时区逻辑，并且需要表示 2038 年之后的时间，DATETIME 是更稳妥的选择。

NULL 和 ‘’ 的区别是什么

NULL和 ''(空字符串) 是两个完全不同的值，它们分别表示不同的含义，并在数据库中有着不同的行为。NULL代表缺少或未知的数据，而''表示一个已知存在的空字符串。它们的区别主要如下：

  1. 含义:
    • NULL代表一个不确定的值，它不等于任何值，包括它自身。因此，SELECT NULL = NULL的结果是NULL，而不是true或false。NULL意味这确实或未知的信息。虽然NULL不等于任何值，但在某些操作中，数据库系统会将NULL值视为相同的类别进行处理，例如：DISTINCT、GROUP BY、ORDER BY。需要注意的是，这些操作将NULL值视为相同的类别进行处理，并不意味着NULL值之间是相等的。它们只是在特定操作中被特殊处理，以保证结果的正确性和一致性。这种处理方式是为了方便数据操作，而不是改变了NULL的语义。
    • ''表示一个空字符串，它是一个已知的值。
  2. 存储空间:
    • NULL的存储空间占用取决于数据库的实现，通常需要一些空间来标记该值为空。
    • ''的存储空间占用通常较小，因为它只存储一个空字符串的标志，不需要存储实际的字符。
  3. 比较运算:
    • 任何值与NULL进行比较(例如=、!=、>、<等)的结果都是NULL，表示结果不确定。要判断一个值是否为NULL，必须使用IS NULL或IS NOT NULL。
    • ''可以像其他字符一样进行比较运算。例如，'' = ''的结果是true。
  4. 聚合函数:
    • 大多数聚合函数(例如SUM、AVG、MIN、MAX)会忽略NULL值。
    • COUNT(*)会统计所有行数，包括包含NULL值的行。COUNT(列名)会统计指定列中非NULL值的行数。
    • 空字符串''会被聚合函数计算在内。例如，SUM会将其视为 0。MIN和MAX会将其视为一个空字符串。

重要：Boolean 类型如何表示

MySQL 中没有专门的布尔类型，而是用TINYINT(1)类型来表示布尔值。TINYINT(1)类型可以存储 0 或 1，分别对应 false 或 true。

重要：手机号存储 INT 还是 VARCHAR

存储手机号，强烈建议使用 VARCHAR 类型，而不是 INT 或 BIGINT。主要原因如下：

  1. 格式兼容性与完整性:
    • 手机号可能包含前导零(如某些地区的固化区号)、国家代码前缀(‘+’)，甚至可能带有分隔符(‘-‘ 或空格)。INT 和 BIGINT 这种数字类型会自动丢失这些重要的格式信息(比如前导零会被去掉，’+’ 和 ‘-‘ 无法存储)。
    • VARCHAR 可以原样存储各种格式的号码，无论是国内的 11 位手机号，还是带有国家代码的国际号码，都能完美兼容。
  2. 非算数性:
    • 手机号虽然看起来是数字，但我们从不对它进行数学运算(比如求和、平均值)。它本质上是一个标识符，更像是一个字符串。用 VARCHAR 更符合其数据性质。
  3. 查询灵活性:
    • 业务中常常需要根据号段(前缀)进行查询，例如查找所有 “138” 开头的用户。使用 VARCHAR 类型配合 LIKE '138%' 这样的 SQL 查询既直观又高效。
    • 如果使用数字类型，进行类似的前缀匹配通常需要复杂的函数转换(如 CAST 或 SUBSTRING)，或者使用范围查询(如 WHERE phone >= 13800000000 AND phone < 13900000000)，这不仅写法繁琐，而且可能无法有效利用索引，导致性能下降。
  4. 加密存储的需求(非常关键):
    • 出于数据安全和隐私合规的要求，手机号这类敏感个人信息通常必须加密存储在数据库中。
    • 加密后的数据(密文)是一长串字符串(通常由字母、数字、符号组成，或经过 Base64/Hex 编码)，INT 或 BIGINT 类型根本无法存储这种密文。只有 VARCHAR、TEXT 或者 BLOB 这些类型才可以。

关于 VARCHAR 长度的选择:

  • 如果不加密存储(强烈不推荐！): 考虑到国际号码和可能的格式符，VARCHAR(20) 到 VARCHAR(32) 通常是一个比较安全的范围，足以覆盖全球绝大多数手机号格式。VARCHAR(15) 可能对某些带国家码和格式符的号码来说不够用。
  • 如果进行加密存储(推荐的标准做法): 长度必须根据所选加密算法产生的密文最大长度，以及可能的编码方式(如 Base64 会使长度增加约 1/3)来精确计算和设定。通常会需要更长的 VARCHAR 长度，例如 VARCHAR(128)，VARCHAR(256)甚至更长。

MySQL 基础架构

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。

MySQL 架构图

主要由下面几部分构成：

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

MySQL 存储引擎

MySQL 核心在于存储引擎，想要深入学习 MySQL，必定要深入研究 MySQL 存储引擎。

MySQL 支持哪些存储引擎，默认使用哪一个

MySQL 支持多种存储引擎，你可以通过SHOW ENGINES命令来查看 MySQL 支持的所有存储引擎。

MySQL 引擎查询

从上图我们可以查看出，MySQL (version: 9.5.0)当前的默认引擎是 InnoDB。并且所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

如果想要深入了解每个存储引擎以及它们之间的区别，推荐阅读以下 MySQL 官方文档对应的介绍(面试不会问这么细，了解即可，问一下 ai 也行)：

MySQL 存储引擎架构了解吗

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

下图展示了具有可插拔存储引擎的 MySQL 架构：

mysql-architecture

还可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎，区别于官方存储引擎。像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎，后面由于过于优秀，其被 Oracle 直接收购了。

重点：MyISAM 和 InnoDB 有什么区别

简单对比两者：

  1. 是否支持行级锁

    MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁。

    也就是说，MyISAM 是直接锁住了一整个表，在并发性能上远低于 InnoDB。

  2. 是否支持事务

    MyISAM 不提供事务支持。

    InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ(可重读)隔离级别是可以解决幻读问题发生的(基于 MVCC 和 Next-Key Lock)。

  3. 是否支持外键

    MyISAM 不支持，而 InnoDB 支持。

    外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！(参考之前外键部分)

  4. 是否支持数据库异常崩溃后的安全恢复

    MyISAM 不支持，而 InnoDB 支持。

    使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于redo log。

  5. 是否支持 MVCC

    MyISAM 不支持，而 InnoDB 支持。

    MVCC 是多并发版本控制，具体实现方式给每一个事务建立一个临时表，可以看作行级锁的一个升级，能够有效减少加锁操作，提高性能。

  6. 索引实现不一样

    虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

    InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM 索引文件和数据文件是分离的，InnoDB 表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

  7. 性能有差别

    InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系

    innodb-myisam-performance-comparison

  8. 数据缓存策略和机制实现不同

    InnoDB 使用缓冲池(Buffer Pool)缓存数据页和索引页，MyISAM 使用键缓存(Key Cache)仅缓存索引页而不缓存数据页。

总结: 最后用一张表来总结常见的几种 MySQL 存储引擎。

comparison-of-common-mysql-storage-engines

MyISAM 和 InnoDB 如何选择

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。

《MySQL 高性能》上面有一句话这样写到：

不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

也就是基本上不需要考虑 MyISAM 了。

重点：MySQL 索引

索引是什么

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

索引的作用就相当于书的目录。打个比方：我们在查字典的时候，如果没有目录，那我们就只能一页一页地去找我们需要查的那个字，速度很慢；如果有目录了，我们只需要先去目录里查找子的位置，然后直接翻到那一页就好了。

索引底层数据结构存在很多种类型，常见的索引结构有：B 树、B+ 树和 Hash、红黑树。在 MySQL 中，无论是 InnoDB 还是 MyISAM，都使用了 B+ 树作为索引结构。

索引的优点:

  1. 查询速度快(主要目的): 通过索引，数据库可以大幅减少需要扫描的数据量，直接定位到符合条件的记录，从而显著加快数据检索速度，减少磁盘 I/O 次数。
  2. 保证数据唯一性: 通过创建唯一索引(Unique Index)，可以确保表中的某一列(或几列组合)的值是独一无二的，比如用户 ID、邮箱等。主键本身就是一种唯一索引。
  3. 加速排序和分组: 如果查询中的 ORDER BY 或 GROUP BY 子句涉及的列键有索引，数据库往往可以直接利用索引已经排好序的特性，避免额外的排序操作，从而提高性能。

索引的缺点:

  1. 创建和维护耗时: 创建索引本身需要时间，特别是对大表操作时。更重要的是，当对表中的数据进行增删改(DML 操作)时，不仅要操作数据本身，相关的索引也必须动态更新和维护，这会降低这些 DML 操作的执行效率。
  2. 占用存储空间: 索引本质上也是一种数据结构，需要以物理文件(或内存结构)的形式存储，因此会额外占用一定的磁盘空间。索引越多、越大，占用的空间也就越多。
  3. 可能被误用或失效: 如果索引设计不当，或者查询语句写得不好，数据库优化器可能不会选择使用索引(或者选错索引)，反而导致性能下降。

那么，用了索引就一定能提高查询性能吗？

不一定。 大多数情况下，合理使用索引确实比全表扫描快得多。但也有例外：

  • 数据量太小：如果表里的数据非常少(比如就几百条)，全表扫描可能比通过索引查找更快，因为走索引本身也有开销。
  • 查询结果集占比过大：如果要查询的数据占了整张表的大部分(比如超过 20%-30%)，优化器可能会认为全表扫描更划算，因为通过索引多次回表(随机 I/O)的成本可能高于一次顺序的全表扫描。
  • 索引维护不当或统计信息过时：导致优化器做出错误判断。

索引为什么快

索引之所以快，核心原因是它大大减少了磁盘 I/O 的次数。

它的本质是一种排好序的数据结构，就像书的目录，让我们不用一页一页地翻(全表扫描)。

在 MySQL 中，这个数据结构是B+树。B+ 树结构主要从两方面做了优化：

  1. B+ 树的特点是“矮胖”，一个千万数据的表，索引树的高度可能只有 3-4 层。这意味着，最多只需要 3-4 次磁盘 I/O，就能精确定位到我想要的数据，而全表扫描可能需要成千上万次，所以速度极快。
  2. B+ 树的叶子节点是用链表连起来的。找到开头后，就能顺着链表顺序读下去，这对磁盘非常友好，还能触发预读。

MySQL 索引底层数据结构是什么

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都使用 B+Tree 作为索引结构。

为什么 InnoDB 没有使用哈希作为索引的数据结构

无论提问还是回答这个问题都要区分好存储引擎，不能想当然的认为 MySQL 底层并没有使用哈希或者 B 树作为索引的数据结构。比如 MEMORY 引擎就同时支持哈希和 B 树。

哈希索引的底层是哈希表。它的优点是在进行精确的等值查询时，理论上时间复杂度是 O(1)，速度极快。比如WHERE id = 114514。

但是，它有几个对于通用数据库来说致命的缺点：

  1. 不支持范围查询: 这是最主要的原因。哈希函数的一个特点是它会把相邻的输入值映射到哈希表中完全不相邻的位置。这种顺序的破坏，使得我们无法处理像WHERE age > 30或BETWEEN 100 AND 200这样的范围查询。要完成这种查询，哈希索引只能退化为全表扫描。
  2. 不支持排序: 同理，因为哈希值是无序的，所以我们无法利用哈希索引来优化ORDER BY子句。
  3. 不支持部分索引键查询: 对于联合索引，比如(col1, col2)，哈希索引必须使用所有索引列进行查询，它无法单独利用col1来加速查询。
  4. 哈希冲突问题: 当不同的键产生冲突时，需要额外的链表或开放寻址来解决，这会降低性能。

鉴于数据库查询中范围查询和排序是极其常见的操作，一个不支持这些功能的索引结构，显然不能作为默认的、通用的索引类型。

为什么 InnoDB 没有使用 B 树作为索引的数据结构

B 树和 B+ 树都是优秀的多路平衡搜索树，非常适合磁盘存储，因为它们都很“矮胖”，能最大化地利用每一次磁盘 I/O。

但 B+ 树是 B 树的一个增强版，它针对数据库场景做了几个关键优化：

  1. I/O 效率更高: 在 B+ 树中，只有叶子节点才存储数据(或数据指针)，而非叶子节点只存储索引键。因为非叶子节点不存数据，所以它们可以容纳更多的索引键。这意味着 B+ 树的“扇出”更大，在同样的数据量下，B+ 树通常会比 B 树更矮，也就意味着查找数据所需的磁盘 I/O 次数更少。
  2. 查询性能更稳定: 在 B+ 树中，任何一次查询都必须从根节点走到叶子节点才能找到数据，所以查询路径的长度是固定的。而在 B 树中，如果运气好，可能在非叶子节点就找到了数据，但运气不好也得走到叶子，这导致查询性能不稳定。
  3. 对范围查询极其友好: 这是 B+ 树最核心的优势。它的所有叶子节点之间通过一个双向链表连接。当我们执行一个范围查询(比如 WHERE id > 100)时，只需要通过树形结构找到 id=100 的叶子节点，然后就可以沿着链表向后顺序扫描，而无需再回溯到上层节点。这使得范围查询的效率大大提高。

覆盖索引是什么

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

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

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

请解释一下 MySQL 的联合索引及其最左前缀原则

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

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

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

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

最左匹配原则会一直向右匹配，直到遇到范围查询(如 >、<)为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配(相关阅读：联合索引的最左匹配原则全网都在说的一个错误结论)。

假设有一个联合索引 (column1, column2, column3)，其从左到右的所有前缀为 (column1)、(column1, column2)、(column1, column2, column3)(创建 1 个联合索引相当于创建了 3 个索引)，包含这些列的所有查询都会走索引而不会全表扫描。

在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

简单演示一下最左前缀匹配的效果：

  1. 创建一个名为 student 的表，这张表只有 id、name、class 这 3 个字段。

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE `student` (
      `id` int NOT NULL,
      `name` varchar(100) DEFAULT NULL,
      `class` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name_class_idx` (`name`,`class`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  2. 下面是三条不同的 SQL 语句的测试：

    leftmost-prefix-matching-rule

    可以看到SELECT * FROM student WHERE name = 'Anne Henry';和SELECT * FROM student WHERE name = 'Anne Henry' AND class = 'lIrm08RYVk';都可以命中索引，但是SELECT * FROM student WHERE class = 'lIrm08RYVk';没有命中索引。

  3. 一道常见的面试题：如果有索引 联合索引（a，b，c），查询 a=1 AND c=1 会走索引么？c=1 呢？b=1 AND c=1 呢？ b = 1 AND a = 1 AND c = 1 呢？

    答案如下：

    1. 查询 a=1 AND c=1：根据最左前缀匹配原则，查询可以使用索引的前缀部分。因此，该查询仅在 a=1 上使用索引，然后对结果进行 c=1 的过滤。
    2. 查询 c=1：由于查询中不包含最左列 a，根据最左前缀匹配原则，整个索引都无法被使用。
    3. 查询 b=1 AND c=1：和第二种一样的情况，整个索引都不会使用。
    4. 查询 b=1 AND a=1 AND c=1：这个查询是可以用到索引的。查询优化器分析 SQL 语句时，对于联合索引，会对查询条件进行重排序，以便用到索引。会将 b=1 和 a=1 的条件进行重排序，变成 a=1 AND b=1 AND c=1。

MySQL 8.0.13 版本引入了索引跳跃扫描(Index Skip Scan，简称 ISS，了解即可)，它可以在某些索引查询场景下提高查询效率。在没有 ISS 之前，不满足最左前缀匹配原则的联合索引查询中会执行全表扫描。而 ISS 允许 MySQL 在某些情况下避免全表扫描，即使查询条件不符合最左前缀。

SELECT * 会导致索引失效吗

SELECT *不会直接导致索引失效(如果不走索引大概率是因为 where 查询范围过大导致的)，但它可能会带来一些其他的性能问题：造成网络传输和数据处理的浪费、无法使用索引覆盖等。

哪些字段适合创建索引

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

索引失效的原因

  1. 创建了组合索引，但查询条件未遵守最左匹配原则
  2. 在索引列上进行计算、函数、类型转换等操作
  3. 以 % 开头的 LIKE 查询
  4. 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到
  5. IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同)
  6. 发生隐式转换

MySQL 查询缓存

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

MySQL 可以执行以下命令来开启查询缓存：

1
2
set global  query_cache_type=1;
set global  query_cache_size=600000;

查询缓存会在同样的查询条件和数据情况下，直接返回缓存中的结果。但需要注意的是，查询缓存的匹配条件非常严格，任何细微的差异都会导致缓存无法命中。这里的查询条件包括查询语句本身、当前使用的数据库、以及其他可能影响结果的因素，如客户端协议版本号等。

查询缓存不命中的情况:

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

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

1
SELECT sql_no_cache COUNT(*) FROM usr;

MySQL 8.0 开始，已经不再支持查询缓存了。

重要：MySQL 日志(三大日志需要掌握)

重要：MySQL 事务

什么是事务

何为事务？ 一言蔽之，事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作，这两个操作必须都成功或者都失败。

  1. 将小明的余额减少 1000 元
  2. 将小红的余额增加 1000 元。

事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。这样就不会出现小明余额减少而小红的余额却并没有增加的情况。

什么是数据库事务

大多数情况下，我们在谈论事务的时候，如果没有特指分布式事务，往往指的就是数据库事务。

数据库事务在我们日常开发中接触的最多了。如果项目属于单体架构的话，接触到的往往就是数据库事务了。

数据库事务的作用:

简单来说，数据库事务可以保证多个对数据库的操作(也就是 SQL 语句)构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

1
2
3
4
5
6
# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

另外，关系型数据库(例如：MySQL、SQL Server、Oracle)事务都有 ACID 特性：

  1. 原子性(Atomicity)：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性(Consistency)：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  3. 隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性(Durability)：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

Atomicity, isolation, and durability are properties of the database, whereas consis‐
tency (in the ACID sense) is a property of the application. The application may rely
on the database’s atomicity and isolation properties in order to achieve consistency,
but it’s not up to the database alone.

翻译过来的意思是：原子性，隔离性和持久性是数据库的属性，而一致性(在 ACID 意义上)是应用程序的属性。应用可能依赖数据库的原子性和隔离属性来实现一致性，但这并不仅取决于数据库。因此，字母 C 不属于 ACID。

并发事务带来了哪些问题

脏读(Dirty read)

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A=19,事务 1 回滚导致对 A 的修改并未提交到数据库，A 的值还是 20。

丢失修改(Lost to modify)

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 先修改 A=A-1，事务 2 后来也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。

不可重复读(Unrepeatable read)

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同。

幻读(Phantom read)

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

例如：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据。

不可重复读和幻读有什么区别

  • 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改。
  • 幻读的重点在于记录新增比如多次执行同一条查询语句(DQL)时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把幻读区分出来的原因主要是解决幻读和不可重复读的方案不一样。

举个例子：执行delete和update操作的时候，可以直接对记录加锁，保证事务安全。而执行insert操作的时候，由于记录锁(Record Lock)只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁(Gap Lock)。也就是说执行insert操作的时候需要依赖 Next-Key Lock(Record Lock+Gap Lock)进行加锁来保证不出现幻读。

并发事务的控制方式有哪些

MySQL 中并发事务的控制方式无非就两种：锁和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制(MVCC，Multiversion concurrency control)可以看作是乐观控制的模式。

锁控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过读写锁来实现并发控制。

  • 共享锁(S 锁): 又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取(锁兼容)。
  • 排他锁(X 锁): 又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁(锁不兼容)。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为表级锁(table-level locking)和行级锁(row-level locking)。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可(对一行或者多行记录加锁)，所以对于并发写入操作来说，InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁(Share Lock，S 锁)和排他锁(Exclusive Lock，X 锁)这两类。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本，通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是：隐藏字段、read view、undo log。

  • undo log: undo log 用于记录某行数据的多个版本的数据。
  • read view 和 隐藏字段: 用来判断当前版本数据的可见性。

SQL 标准定义了哪些事务隔离级别

SQL 标准定义了四种事务隔离级别，用来平衡事务的隔离性(Isolation)和并发性能。级别越高，数据一致性越好，但并发性能可能越低。这四个级别是：

  • READ-UNCOMMITTED(读取未提交): 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。这种级别在实际应用中很少使用，因为对数据的一致性保证太弱。

  • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。这是大多数数据库(如 Oracle，SQL Server)的默认隔离级别。

  • REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

    MySQL InnoDB 存储引擎的默认隔离级别正是 REPEATABLE-READ。并且，InnoDB 在此级别下通过 MVCC(多版本并发控制) 和 Next-Key Locks(间隙锁+行锁) 机制，在很大程度上解决了幻读问题。

  • SERIALIZABLE(可串行化): 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别 脏读 (Dirty Read) 不可重复读 (Non-Repeatable Read) 幻读 (Phantom Read)
READ-UNCOMMITTED √ √ √
READ-COMMITTED × √ √
REPEATABLE-READ × × √(标准) / ≈× (InnoDB)
SERIALIZABLE × × ×

MySQL 的默认隔离级别是什么

MySQL InnoDB 存储引擎的默认隔离级别是 REPEATABLE-READ。可以通过以下命令查看：

MySQL 默认隔离级别

MySQL 的隔离级别是基于锁实现的吗

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL 锁

锁是一种常见的并发事务的控制方式。

表级锁和行级锁

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

行级锁的粒度更小，仅对相关的记录上锁即可(对一行或者多行记录加锁)，所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比:

  • 表级锁: MySQL 中锁定粒度最大的一种锁(全局锁除外)，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
  • 行级锁: MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

行级锁的使用有什么注意事项

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，即仅锁住索引本身，而不是范围。

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁(Share Lock，S 锁)和排他锁(Exclusive Lock，X 锁)这两类：

  • 共享锁(S锁): 又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取(锁兼容)。
  • 排他锁(X锁): 又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁(锁不兼容)。

由于 MVCC 的存在，对于一般的SELECT语句，InnoDB 不会加任何锁。不过，你可以通过以下语句显示加共享锁或排他锁。

1
2
3
4
5
6
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用

如果需要用到表锁的话，如何判断表中的记录有没有行锁呢，不可能对每一行进行遍历，所以引入了意向锁的设计。

意向锁是表级锁，共有两种：

  • 意向共享锁(Intention Shared, IS 锁): 事务有意向对表中的某些记录加共享锁(S 锁)，加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁(Intention Exclusive Lock, IX 锁): 事务有意向对表中的某些记录加排他锁(X 锁)，加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

意向锁和共享锁和排它锁互斥(这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥):

IS 锁 IX 锁
S 锁 兼容 互斥
X 锁 互斥 互斥

当前读和快照读有什么区别

快照读(一致性非锁定读)就是单纯的SELECT语句，但不包括下面这两类SELECT语句：

1
2
3
4
5
SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

快照即记录的历史版本，每行记录可能存在多个历史版本(多版本技术)。

快照读的情况下，如果读取的记录正在进行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR(可重读) 下，InnoDB 才会使用一致性非锁定读：

  • 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
  • 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读(一致性锁定读)就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

1
2
3
4
5
6
7
8
9
10
# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

什么是自增锁(不太重要)

关系型数据库设计表的时候，通常会有一列作为自增主键。InnoDB 中的自增主键会涉及一种比较特殊的表级锁——自增锁(AUTO-INC Locks)。

1
2
3
4
5
6
CREATE TABLE `sequence_id` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stub` CHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

更准确一点来说，不仅仅是自增主键，AUTO_INCREMENT的列都会涉及到自增锁，毕竟非主键也可以设置自增长。

如果一个事务正在插入数据到有自增列的表时，会先获取自增锁，拿不到就可能会被阻塞住。这里的阻塞行为只是自增锁行为的其中一种，可以理解为自增锁就是一个接口，其具体的实现有多种。具体的配置项为 innodb_autoinc_lock_mode (MySQL 5.1.22 引入)，可以选择的值如下：

innodb_autoinc_lock_mode 介绍
0 传统模式
1 连续模式(MySQL 8.0 之前默认)
2 交错模式(MySQL 8.0 之后默认)

交错模式下，所有的“INSERT-LIKE”语句(所有的插入语句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT、LOAD DATA等)都不使用表级锁，使用的是轻量级互斥锁实现，多条插入语句可以并发执行，速度更快，扩展性也更好。

不过，如果你的 MySQL 数据库有主从同步需求并且 Binlog 存储格式为 Statement 的话，不要将 InnoDB 自增锁模式设置为交叉模式，不然会有数据不一致性问题。这是因为并发情况下插入语句的执行顺序就无法得到保障。

如果 MySQL 采用的格式为 Statement ，那么 MySQL 的主从同步实际上同步的就是一条一条的 SQL 语句。

MySQL 性能优化

能用 MySQL 直接存储文件(比如图片)吗

可以自然是可以的，直接存储二进制文件对应的二进制数据即可。不过，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。

可以选择使用云服务厂商提供的开箱即用的文件存储系统服务(比如OSS，NAS)，成熟稳定，价格也比较低。

也可以选择自建文件存储服务，实现起来也不难，基于 FastDFS、MinIO(推荐)等开源项目就可以实现分布式文件服务。

数据库只存储文件地址信息，文件由文件存储服务负责存储。

MySQL 如何存储 IP 地址

可以将 IP 地址转换成整型数据存储，性能更好，占用空间也更小。

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

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

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

MySQL 性能怎么优化

MySQL 性能优化是一个系统性工程，涉及多个方面，在面试中不可能面面俱到。因此，建议按照“点-线-面”的思路展开，从核心问题入手，再逐步扩展，展示粗你对问题的思考深度和解决能力。

  1. 抓住核心：慢 SQL 定位与分析

    性能优化的第一步永远是找到瓶颈。面试时，建议先从慢 SQL 定位和分析入手，这不仅能展示你解决问题的思路，还能体现你对数据库性能监控的熟练掌握：

    • 监控工具: 介绍常用的慢 SQL 监视工具，如 MySQL 慢查询日志、Performance Schema 等，说明你对这些工具的熟悉程度以及如何通过它们定位问题。
    • EXPLAIN 命令: 详细说明EXPLAIN命令的使用，分析查询计划、索引使用情况，可以结合实际案例展示如何解读分析结果，比如执行顺序、索引使用情况、全表扫描等。

  2. 由点及面：索引、表结构和 SQL 优化

    定位到慢 SQL 后，接下来就要针对具体问题进行优化。这里可以重点介绍索引、表结构和 SQL 编写规范等方面的优化技巧：

    • 索引优化: 这是 MySQL 性能优化的重点，可以介绍索引的创建原则、覆盖索引、最左前缀匹配原则等。如果能结合你项目的实际应用来说明如何选择合适的索引，会更加分一些。

    • 表结构优化: 优化表结构设计，包括选择合适的字段类型、避免冗余字段、合理使用范式和反范式设计等等。

    • SQL 优化:

      • 查询性能优化:

        • 减少请求的数据量
          • 只返回必要的列：最好不要使用SELECT *语句。
          • 只返回必要的行：使用LIMIT语句来限制返回的数据。
          • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
        • 减少服务器端扫描的次数: 最有效的方式是使用索引来覆盖查询。

      • 重构查询方式:

        • 切分大查询

          一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

          1
          DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
          1
          2
          3
          4
          5
          rows_affected = 0
          do {
              rows_affected = do_query(
              "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
          } while rows_affected > 0

        • 分解大连接查询

          将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

          • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的查询，即是其中一个表发生变化，对其他表的查询缓存依然可以使用。
          • 分解成多个单表查询，这些单表查询的缓存结果更可能被其他查询使用到，从而减少冗余记录的查询。
          • 减少锁竞争。
          • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
          • 查询本身效率也可能会有所提升。例如下面的例子中，使用IN()代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。
          1
          2
          3
          4
          SELECT * FROM tag
          JOIN tag_post ON tag_post.tag_id=tag.id
          JOIN post ON tag_post.post_id=post.id
          WHERE tag.tag='mysql';
          1
          2
          3
          SELECT * FROM tag WHERE tag='mysql';
          SELECT * FROM tag_post WHERE tag_id=1234;
          SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

  3. 进阶方案：架构优化

    当面试官对基础优化知识比较满意时，可能会深入探讨一些架构层面的优化方案。以下是一些常见的架构优化策略：

    • 读写分离: 将读操作和写操作分离到不同的数据库实例，提升数据库的并发处理能力。
    • 分库分表: 将数据分散到多个数据库实例或数据表中，降低单表数据量，提升查询效率。但要权衡其带来的复杂性和维护成本，谨慎使用。
    • 数据冷热分离: 根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在低成本、低性能的介质中，热数据存储到高性能存储介质中。
    • 缓存机制: 使用 Redis 等缓存中间件，将热点数据缓存到内存中，减轻数据库压力。这个非常有用，提升效果非常明显，性价比极高！

  4. 其他优化手段

    除了慢 SQL 定位、索引优化和架构优化，还可以提及一些其他优化手段，展示你对 MySQL 性能调优的全面理解：

    • 连接池配置: 配置合理的数据库连接池(如连接池大小、超时时间等)，能够有效提升数据库连接的效率，避免频繁的连接开销。
    • 硬件配置: 提升硬件性能也是优化的重要手段之一。使用高性能服务器、增加内存、使用 SSD 硬盘等硬件升级，都可以有效提升数据库的整体性能。

如何分析 SQL 的性能

我们可以使用EXPLAIN命令来分析 SQL 的执行计划。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化后具体的执行方式。

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

EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句，我们一般分析 SELECT 查询较多。

EXPLAIN 的输出各个字段如下：

列名 含义
id SELECT 查询的序列标识符
select_type SELECT 关键字对应的查询类型
table 用到的表名
partitions 匹配的分区，对于未分区的表，值为 NULL
type 表的访问方法
possible_keys 可能用到的索引
key 实际用到的索引
key_len 所选索引的长度
ref 当使用索引等值查询时，与索引做比较的列或常量
rows 预计要读取的行数
filtered 按表条件过滤后，留存的记录数的百分比
extra 附加信息

读写分离和分库分表了解吗

什么是读写分离

根据读写分离的名字，我们就可以知道：读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

如何实现读写分离

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

  1. 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。

  2. 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。

  3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

落实到项目本身的话，常用的方式有两种：

  1. 代理方式

    我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。

    提供类似功能的中间件有 MySQL Router(官方， MySQL Proxy 的替代方案)、Atlas(基于 MySQL Proxy)、MaxScale、MyCat。

    关于 MySQL Router 多提一点：在 MySQL 8.2 的版本中，MySQL Router 能自动分辨对数据库读写/操作并把这些操作路由到正确的实例上。这是一项有价值的功能，可以优化数据库性能和可扩展性，而无需在应用程序中进行任何更改。

  2. 组件方式

    在这种方式中，我们可以通过引入第三方组件来实现读写请求的路由。

    这也是我比较推荐的一种方式。这种方式目前在各种互联网公司中用的最多的，相关的实际的案例也非常多。如果你要采用这种方式的话，推荐使用 ShardingSphere-JDBC ，直接引入 jar 包即可使用，非常方便。同时，也节省了很多运维的成本。

还有很多东西，但是稍微了解一下就行，这里就不展开了。

深度分页如何优化

什么是深度分页，怎么导致的

查询偏移量过大的场景我们称之为深度分页，这会导致查询性能较低，例如：

1
2
# MySQL 在无法利用索引的情况下跳过1000000条记录后，再获取10条记录
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

当查询偏移量过大时，MySQL 的查询优化器可能会选择全表扫描而不是利用索引来优化查询。

深度分页变慢的根本原因在于 MySQL 的执行机制：对于 LIMIT offset, N，MySQL 并非直接跳到 offset 处，而是必须从头扫描 offset + N 条记录。如果查询依赖二级索引且不满足覆盖索引，这意味着 MySQL 需要对前 offset 条记录执行毫无意义的回表查询(产生海量的随机 I/O)，最后再将这些辛苦查出的数据丢弃。即便优化器最终因代价过高退化为全表扫描，顺序扫描百万行的成本依然巨大。

MySQL 的查询优化器采用基于成本的策略来选择最优的查询执行计划。它会根据 CPU 和 I/O 的成本来决定是否使用索引扫描或全表扫描。如果优化器认为全表扫描的成本更低，它就会放弃使用索引。不过，即使偏移量很大，如果查询中使用了覆盖索引(covering index)，MySQL 仍然可能会使用索引，避免回表操作。

深度分页优化建议

基于 MySQL 8.0 + InnoDB 存储引擎，不同版本优化器行为可能存在差异

范围查询(游标分页)

通过记录上一页最后一条记录的 ID，使用WHERE id > lsat_id LIMIT n获取下一页数据：

1
2
# 通过记录上次查询结果的最后一条记录的 ID 进行下一页的查询
SELECT * FROM t_order WHERE id > 100000 ORDER BY id LIMIT 10

游标分页的核心优势：不依赖 ID 的连续性。MySQL 只需要在 B+ 树上定位到 last_id 的位置，然后顺序向后读取 n 条记录即可，中间是否有断层(如 ID 被删除)完全不影响结果的准确性和性能。

这种方式的限制：

  1. 不支持跳页：无法直接跳转到第 N 页，只能逐页向后(或向前)翻页。
  2. 排序字段受限：如果查询需要按照其他字段(如创建时间)排序而非 ID 排序，需使用联合游标 (sort_field, id) 保证唯一性和顺序。
  3. 并发场景：当分页查询期间有新数据插入或删除时，可能出现：
    • 数据遗漏：查询第二页时，有新数据插入到第一页范围内，导致该数据被”挤”到第二页，但第二页查询已基于旧的最后 ID 跳过它。
    • 数据重复：查询第二页时，第一页末尾有数据被删除，原第二页的第一条数据”升”到第一页末尾，导致第二页查询再次返回它。
子查询

我们先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit，这样效率会更快一些。

阿里巴巴《Java 开发手册》中也有对应的描述：

利用延迟关联或者子查询优化超多分页场景。

子查询

1
2
3
4
5
-- 先通过子查询在主键索引上进行偏移，快速找到起始ID
SELECT * FROM t_order
WHERE id >= (
    SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1
) ORDER BY id LIMIT 10;

工作原理:

  1. 子查询 SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1 利用主键索引扫描并跳过前 1000000 条记录，返回第 1000001 条记录的主键值。

  2. 主查询 SELECT * FROM t_order WHERE id >= ... ORDER BY id LIMIT 10 以该主键为起点，获取后续 10 条完整记录。

不过，某些情况下子查询可能会产生临时表，影响性能，因此在复杂查询中建议优先考虑延迟关联。

复杂过滤场景: 在包含复杂过滤条件的分页场景中(如WHERE status = 1 ORDER BY id LIMIT 1000000, 10)，符合条件的 ID 往往是离散的。此时子查询的优势更加明显：通过在子查询中利用联合索引(如(status, id))实现覆盖索引扫描，可以高效地跳过前100万条符合条件的记录，定位到目标 ID 后，主查询只需回表 10 次。

当然，我们也可以利用子查询先去获取目标分页的 ID 集合，然后再根据 ID 集合获取内容，但这种写法非常繁琐，不如使用 INNER JOIN 延迟关联。

延迟关联

延迟关联与子查询的优化思路类似，都是通过将 LIMIT 操作转移到主键索引树上，减少回表次数。相比直接使用子查询，延迟关联通过 INNER JOIN 将子查询结果集成到主查询中，避免了子查询可能产生的临时表。在执行 INNER JOIN 时，MySQL 优化器能够利用索引进行高效的连接操作(如索引扫描或其他优化策略)，因此在深度分页场景下，性能通常优于直接使用子查询。

1
2
3
4
5
6
7
8
-- 使用 INNER JOIN 进行延迟关联
SELECT t1.*
FROM t_order t1
INNER JOIN (
    -- 这里的子查询可以利用覆盖索引，性能极高
    SELECT id FROM t_order ORDER BY id LIMIT 1000000, 10
) t2 ON t1.id = t2.id
ORDER BY t1.id;

工作原理:

  1. 子查询 SELECT id FROM t_order ORDER BY id LIMIT 1000000, 10 利用主键索引扫描并跳过前 1000000 条记录，返回目标分页的 10 条记录的 ID。
  2. 通过 INNER JOIN 将子查询结果与主表 t_order 关联，获取完整的记录数据。

除了使用 INNER JOIN 之外，还可以使用逗号连接子查询：

1
2
3
4
5
-- 使用逗号进行延迟关联
SELECT t1.* FROM t_order t1,
(SELECT id FROM t_order ORDER BY id LIMIT 1000000, 10) t2
WHERE t1.id = t2.id
ORDER BY t1.id;

注意: 虽然逗号连接子查询也能实现类似的效果，但为了代码可读性和可维护性，建议使用更规范的 INNER JOIN 语法。

数据冷热处理

什么是数据冷热处理

数据冷热分离是指根据数据的访问频率和业务重要性，将数据划分为冷数据和热数据，并分别存储在不同的性能和成本的存储介质的架构策略。

这种架构的核心目标有三个：

  1. 提升查询性能: 热数据存储在高性能介质(如 SSD、内存)中，保障核心业务的响应速度。
  2. 降低存储成本: 冷数据迁移至低成本介质(如 HDD、对象存储)，大幅削减存储开支。
  3. 满足合规要求: 部分行业(如金融、医疗)要求数据长期归档，冷热分离可兼顾合规与成本。
冷数据和热数据

热数据是指被频繁访问和修改、且需要快速响应的数据；冷数据是指访问频率极低、对当前业务价值较小、但需要长期保留的数据。

冷热数据的区分方法主要有两种：

  1. 时间维度区分：按照数据的创建时间、更新时间或过期时间划分。例如，订单系统将一段时间前(如 90 天或 1 年)的订单数据标记为冷数据。该方法适用于数据访问频率与时间强相关的场景，实现简单、成本低。
  2. 访问频率区分：将高频访问的数据视为热数据，低频访问的数据视为冷数据。例如，内容系统将浏览量低于阈值的文章标记为冷数据。该方法需要额外记录访问频率，适用于访问频率与数据本身特性强相关的场景。

如何选择区分策略？

  • 若业务数据天然具有时效性(如订单、日志、账单)，优先选择时间维度，实现成本最低。
  • 若数据价值与时间无关(如文章、商品、用户画像)，需结合访问频率进行判定。
  • 实际项目中，可将两者结合使用：以时间维度为主、访问频率为辅，覆盖更多业务场景。

还有分层策略之类的需要了解，这里就不展开来讲了。