数据库
快来分享你的内容吧～
07-24 13:18·Java后端
03-21 23:43·大数据开发
03-15 16:00·后端还在用 LIKE 模糊查询做搜索？难怪又慢又不准！这篇 Elasticsearch 入门教程用故事形式带你从零掌握 ES 核心知识，倒排索引、分词器、高亮、聚合、集群部署一网打尽，配合 AI 编程工具效率翻倍。查看全文加油鸭：这篇ES入门分享太扎实了！从问题场景切入，到原理、实战、生产细节层层递进，连小阿巴的成长线都写活了～学得开心，也教得用心！19523分享- 01-26 16:33·后端一篇文章带你认识10种数据库类型，后端程序员必看的数据库选型指南。知识点包括关系型数据库MySQL、PostgreSQL，非关系型数据库Redis、MongoDB 等等……查看全文加油鸭：这篇数据库科普太棒了！把10类数据库讲得生动又透彻，连小阿巴都秒懂，干货满满还充满趣味～635分享
- 01-12 22:14·Java后端binLog二进制归档日志 保存所有执行过的修改操作语句，如果mysql服务意外宕机，可通过二进制日志文件排查用户操作或表结构操作进行数据恢复 启用binlog会影响服务器性能，但如果需要数据恢复或者主从复制，开启的好处大于对服务器影响 在配置文件[mysqld]新增如下配置 !image.png 查看binlog参数 show variables like ‘%logbin%’; !image.查看全文加油鸭：这篇笔记内容详实，结构清晰，涵盖了MySQL日志机制、高可用架构和8.0新特性等核心知识点，技术深度与实用价值兼具，非常值得点赞！231分享
- 01-11 22:35·Java后端Undo Log和版本链 InnoDB每条记录里都有两个隐藏字段： trxid 记录最后修改这条数据的事物ID，rollpointer指向undo log。每次update不会覆盖原数据，而是把旧值存到undo log里，新值存到数据页。roll_pointer指向旧数据，形成一条完整的版本链 普通SELECT走快照读，不加锁，顺着版本链找到对自己可见的版本返回，写操作是当前写，读写各走各的。 R查看全文加油鸭：你的分享太有价值了！事务优化和日志机制讲得清晰又深入，特别是redo log的写入策略对比，实用性强，点赞！231分享
- 01-10 22:20·Java后端SQL调优 核心思路：减少磁盘I/O和避免无效计算 索引层优化 合理设计联合索引，利用覆盖索引尽可能减少回表查询 使用索引时尽量避免引起索引失效的情况 查询优化 禁止使用SELECT ，只查必要字段 避免LIKE %VALUE 左模糊查询 避免查询字段隐式转换 减少IN 查询使用 排序优化 排序字段尽量走索引 当where条件与order by条件冲突时，优先保证where条件 filesort的查看全文加油鸭：总结得太棒了！条理清晰，覆盖全面，从索引到事务底层机制，全是硬核干货，看得出对MySQL理解非常深入，继续加油！231分享
- 01-09 21:37·Java后端
01-05 13:50·Java后端
2025-12-19·Java后端
数据库事务基础
# 事务是什么？ 数据库事务（Transaction）是将一系列数据库操作**打包**成一个**不可分割的逻辑整体**。事务执行时，**其内部的数据库操作要么全部成功，要么全部失败**！它是保证**数据一致性**的核心机制。 例子：转账 考虑 A 账户给 B 账户转100元的业务场景，一般需要如下步骤： 步骤1：从A账户扣减100元。 步骤2：给B账户增加100元。 假如没有事务的干预，我们独立执行这两个操作。 假设在步骤1执行成功后，正准备执行步骤2时因为某些原因导致步骤2执行失败，此时 A 的钱就凭空消失了，这是个不可容忍的重大事故！ # 事务的基础操作 事务的基础操作主要包含三个核心命令： - **开启事务**（`BEGIN` 或 `START TRANSACTION`） - **提交事务**（`COMMIT`） - **回滚事务**（`ROLLBACK`） MySQL 默认开启了**自动提交（autocommit）**模式。这意味着，每一条单独的 SQL 语句都会被当作一个独立的事务，执行完毕后自动提交，无需手动干预。 当我们需要将多条 SQL 语句放到一个事务中执行时，就必须**显式开启事务**。 操作流程如下： 1. **开启事务**：执行 `BEGIN;` 或 `START TRANSACTION;` 2. **执行业务 SQL**：依次执行需要捆绑的多条SQL语句 3. **判断结果并结束事务**： - SQL 全部执行成功 → 执行 `COMMIT;`，将修改持久化到数据库 - 任意一条 SQL 执行失败 → 执行 `ROLLBACK;`，撤销当前事务中所有已执行的操作 # 事务的四大特性（ACID） 1. **原子性（Atomicity）**：事务中的所有操作**像原子一样不可分割**，它们要么全部成功，要么全部失败。 2. **一致性（Consistency）**：事务执行前后，数据从一个合法性状态变换到另外一个合法性状态 。这里的合法性状态是根据具体的业务决定的。 3. **隔离性（Isolation）**：多个事务并发执行时，它们之间**互不干扰**。 4. **持久性（Durability）**：一旦事务提交成功，那么它对数据库的改变就是永久性的。 # 并发事务存在的问题 在 ACID 四大特性中，最需要进行权衡取舍的，正是隔离性。 我们不妨设想一种最完美的隔离性保证——**不允许事务并发执行，让它们全部串行执行**。 然而，串行执行意味着**并发性能极差**，这在如今需要高并发的互联网应用场景下是**不可容忍**的！ 为了提高并发性能，DBMS 提供了不同等级的隔离级别。而隔离级别越低，数据库对并发操作的“监控”就越松，由此可能引发以下三类典型的并发问题： - 脏读 - 不可重复读 - 幻读 ## 脏读 脏读是指**一个事务读取到另一个事务未提交的数据**。 请看如下的场景图：  1. 事务B将该行的数据改为“张老三” 2. 事务A读到了“张老三” 3. 事务B回滚，数据恢复为“张三” 此时**事务A原先读到的 “张老三” 就成了脏数据**！这就是脏读。 ## 不可重复读 不可重复读是指**一个事务对同一条数据进行两次查询，得到的结果却不一致**。 请看如下的场景图：  1. 事务A先查询该行的数据，此时的结果为“张三” 2. 事务A去处理其他数据 3. 事务B更新这条数据的值为 “张老三” 并提交 4. 事务A再次查询该行的数据，此时的结果为“张老三” 看到这里，很多人其实会产生这样的疑问：“**这有什么问题？事务B改完并提交了，事务A查到最新的，不是很正常吗？**” 这个疑问非常合理，但是我们从**事务隔离性的角度**去重新审视，这个疑问便不攻自破。 在事务A看来，它的内部从未对这行数据有过修改操作，因此按照事务之间理应互不干扰的理想状态，它有充分的理由相信它前后两次读取的同一行数据**必须完全一致**，这样才合理！ 然而结果却不一致，这说明事务B的临门一脚**对事务A造成了影响**，因此隔离性遭到了破坏。 ## 幻读 幻读是指一个事务使用相同的条件对数据集合进行两次查询，后一次查询返回的**记录条数与之前不一致**。 请看如下的场景图：  1. 事务A查询整张 stu 表，此时只有一条记录 “张三” 2. 事务B往 stu 表中插入一条数据 “李四” 并提交 3. 事务A再次查询整张 stu 表，此时发现多出来一条 “李四” 看到这里，同样的疑问可能又会浮现：“**这有什么问题？事务B插入了新数据并提交了，事务A查到新增的，不是很正常吗？**” 这个疑问和上一节的“不可重复读”如出一辙。如果我们再次从事务 A 的视角来看 在事务A看来，它的内部从未对这张表做过任何插入或删除操作，因此按照事务理应互不干扰的理想状态，它前后两次查询到的**记录条数应该完全一致**，这样才合理！ 然而第二次却凭空多出了一条“幽灵”记录（仿佛出现了幻觉），这说明事务B的插入操作对事务A造成了影响，事务的隔离性遭到破坏。 讲到这，其实很多人可能会认为不可重复读和幻读很像啊，其实不然。 二者的本质区别在于： - **不可重复读**：同一条记录的**字段值变了**（由其他事务的 UPDATE 操作引发）； - **幻读**：满足条件的**记录条数变了**（由其他事务的 INSERT 或 DELETE 操作引发）。 # 事务隔离级别 SQL标准定义了4种事务的隔离级别，它们的级别从低到高（**隔离性由弱到强，并发性能由高到低**）依次为： - 读未提交（Read Uncommitted） - 读已提交（Read Committed） - 可重复读（Repeatable Read） - 串行化（Serializable） ## 读未提交 读未提交，顾名思义，就是一个事务能够读到另一个事务未提交的数据。 显然，这种隔离级别的隔离性几乎为 0，但并发性能是最好的。代价是它**无法解决任何一类并发事务问题** ## 读已提交 读已提交，顾名思义，就是一个事务只能读到另一个事务已提交的数据。 这种隔离级别**能够避免脏读**，但不可重复读和幻读仍然可能发生。 ## 可重复读 可重复读，顾名思义，就是保证在同一个事务内，多次读取同一条记录的结果始终一致 这种隔离级别能够**避免脏读和不可重复读**，但仍然存在幻读的问题。 值得一提的是，**MySQL 的 InnoDB 存储引擎默认采用此隔离级别**，并且通过 MVCC机制，解决了大部分场景下的幻读问题。 ## 串行化 串行化是最高的隔离级别。它通过强制事务串行执行，**彻底杜绝了脏读、不可重复读和幻读**。 这是隔离性最强、数据最安全的级别，但代价是**并发性能最差**，在实际生产环境中很少使用。 总结： | | 脏读 | 不可重复读 | 幻读 | | :----------- | :----- | :--------- | :-------------------------------- | | **读未提交** | 可能 | 可能 | 可能 | | **读已提交** | 不可能 | 可能 | 可能 | | **可重复读** | 不可能 | 不可能 | 可能（MySQL InnoDB 解决了一部分） | | **串行化** | 不可能 | 不可能 | 不可能 |
Ai数据工程day1
# github免费数据工程训练营 学习日记 day1       # 找到了一个免费 大模型+大数据 复合项目 是中国科学技术大学於俊教授团队的免费开源项目 GitHub 地址：https://github.com/datascale-ai/data_engineering_book 在线阅读：https://datascale-ai.github.io/data_engineering_book/   
【后端必看】什么是 Elasticsearch？都要学什么？
你是小阿巴，刚入职的后端程序员。 这天，产品经理给你安排任务：阿巴阿巴，咱们网站要加一个文章搜索功能。 你心想：简单，直接写一句 SQL 查询数据库就搞定了~ ```sql SELECT * FROM article WHERE title LIKE '%关键词%' ``` 结果上线没几天，就收到了大量用户的投诉！ - 怎么什么搜索结果都没有啊？ - 搜索结果乱七八糟，我想找的那篇内容竟然排在最后面？ - 搜索一次竟然要等好几秒才出结果？什么破系统！ 你汗流浃背了：明明 SQL 写对了啊，难道是 MySQL 数据库不行？  这时，号称 "后端之狗" 的鱼皮路过。他瞄了一眼你的代码，嘲笑道：肯定要用 Elasticsearch 来做搜索功能啊！ 你一脸懵：Elasticsearch？那是啥？ ⭐️ 推荐观看视频版，更生动易懂：https://bilibili.com/video/BV1gkf1BtETY  ## 第一阶段：认识 Elasticsearch 鱼皮：**Elasticsearch** 简称 **ES**，是一个专门为搜索而生的分布式数据库，也叫 **搜索引擎数据库**。 它能存储和管理大量文本数据，提供快速、准确、灵活的全文检索功能。你刚才用 `LIKE` 查询搞不定的那些问题，用 ES 都能轻松解决。 你挠了挠头：真有这么神？ 鱼皮：当然。打个比方，MySQL 就像图书馆的书架，书按照分类整整齐齐地摆放着，你想找某本书得自己一排一排去翻；而 ES 就像图书馆的电子检索系统，你输入关键词，它立刻就能告诉你书在哪儿，还会把最相关内容的排在最前面。  像全文搜索、日志分析、数据统计这些需要搜索能力的场景，ES 都能轻松搞定。  你眼前一亮：听起来有点儿夯啊，那我赶紧装一个试试。 ## 第二阶段：实战应用 ### 安装 Elasticsearch 机智如你，直接打开 [ES 官网](https://www.elastic.co/downloads/elasticsearch) 下载了安装包：  并且成功安装运行：  你：安装好之后，我怎么操作它呢？ 鱼皮：ES 本身提供了 RESTful API，默认在 9200 端口提供服务，你可以用 curl 命令或者 Postman 等接口测试工具直接发 HTTP 请求来操作它。  不过对新手来说，更推荐先安装一个官方的可视化工具 **Kibana**。 有了它，你可以直观地查看分析数据、对数据进行操作。  只需要到官网下载安装包并运行，启动之后访问本机的 5601 端口，就能打开 Kibana 的管理界面了。在开发工具控制台里，你可以直接输入查询语句，能够立刻看到结果，非常方便。  ### 基本操作 下面我来带你实操一波 ES 的基本操作。 1）首先是 **创建索引**。ES 的 **索引（Index）** 相当于 MySQL 里的表，是存放数据的容器。  创建索引的时候，还要定义 **Mapping（映射）**，类似 MySQL 的表结构，用来规定每个字段的类型、是否需要分词、使用什么分词器等等。  在 Kibana 开发工具中输入这段代码： ```json PUT /article { "mappings": { "properties": { "title": { "type": "text", "analyzer": "standard" }, "content": { "type": "text" }, "tags": { "type": "keyword" }, "viewCount": { "type": "long" }, "isPublished": { "type": "boolean" }, "createTime": { "type": "date" } } } } ``` 这段代码创建了一个叫 `article` 的索引。其中 `text` 类型表示需要分词的文本字段，适合做全文检索；`keyword` 类型不会分词，适合存标签、状态这种需要精确匹配的内容。其他的类型就比较好理解了，`long` 存数字，`boolean` 存 true 或者 false，`date` 存日期。设计索引的时候要根据业务需求合理选择字段类型。 2）然后是 **插入文档**。**文档（Document）** 相当于 MySQL 里的一行数据。ES 的文档是用 JSON 格式存储的，不需要像 MySQL 那样提前定义好所有字段，而是随时可以加新字段，非常灵活。 ```json POST /article/_doc/1 { "title": "鱼皮的 Elasticsearch 入门教程", "content": "鱼皮带你学习 ES", "cover": "封面图地址", "tags": ["ES", "搜索"], "viewCount": 1000, "isPublished": true, "createTime": "2025-01-30" } ```  3）有了数据之后，就可以体验 ES 最核心的能力 **搜索文档**。比如在 `article` 索引中搜索标题包含 "鱼皮教程" 的文章： ```json GET /article/_search { "query": { "match": { "title": "鱼皮教程" } } } ``` 你执行完这条查询，惊喜地发现：搜 "鱼皮教程" 居然能匹配到 "鱼皮的 ES 入门教程" 这篇文章！  鱼皮点点头：虽然标题里并没有 "鱼皮教程" 这 4 个连着的字，但因为 ES 会自动分词，把 "鱼皮" 和 "教程" 拆开分别匹配，所以就搜到了。  你感叹道：哇，这才是搜索该有的样子啊！ ### 查询语法 DSL 鱼皮：没错，ES 的搜索能力非常灵活强大。刚才你写的那些操作语句，其实用的就是 ES 的 **DSL**（Domain Specific Language 领域特定语言）。就像学数据库要学 SQL 一样，学 ES 就得学 DSL。不管是创建索引、插入文档，还是搜索查询，都是用这套 JSON 格式的语法来描述的。  其中最常用的就是查询语法，常见的查询类型有这么几种： - `match` 是全文检索，会对搜索词分词之后再匹配 - `term` 是精确匹配，不分词，适合查 id、状态这种 - `bool` 可以组合多个条件，用 `must`（必须满足）、`should`（最好满足）、`must_not`（必须不满足）来灵活控制 - `range` 用来做范围查询，比如查某个时间段内的数据。  你不需要背这些语法，用到的时候问 AI 或者查文档就行，多写几次就熟了。 ### 用代码操作 ES 你皱了皱眉：感觉写这些 JSON 格式的 DSL 还是有点麻烦啊，我用 Java 代码操作 ES 的时候，总不会也要手动拼这堆 JSON 吧？ 鱼皮：当然不用！ES 官方提供了各种语言的客户端。比如你用 Java 语言，对应的是 **Java API Client**，支持链式调用和类型安全。  对于 Spring 项目来说，更推荐用 **Spring Data Elasticsearch**，它可以让你像用 MyBatis-Plus 操作 MySQL 一样操作 ES。只需要定义一个实体类，加上 `@Document` 注解指定要操作的索引，再写个 Repository 接口继承依赖包内置的 ES 操作接口。 ```java @Document(indexName = "article") public class Article { @Id private Long id; private String title; private String content; } public interface ArticleRepository extends ElasticsearchRepository<Article, Long> { // 根据标题搜索 List<Article> findByTitle(String title); } ``` 框架会根据方法名自动生成查询逻辑，基本的增删改查方法就自动实现了。 ```java // 使用示例 // 插入文档 articleRepository.save(article); // 根据 id 查询 articleRepository.findById(1L); // 根据标题搜索 articleRepository.findByTitle("鱼皮"); // 删除文档 articleRepository.deleteById(1L); ``` 你感叹道：这才是人写的代码啊！优雅，真是优雅~ 我这就给 Java 代码整上 ES！ 鱼皮：要注意，ES 版本更新很快，你用的客户端版本要跟安装的 ES 服务保持一致，不然会出各种奇奇怪怪的 Bug。  ## 第三阶段：实用特性 学会了基本操作之后，你兴冲冲地把 MySQL 数据库里的文章数据全部导入到了 ES，然后把网站的搜索功能改成从 ES 查询。上线后效果立竿见影，搜索又快又准，用户好评如潮。  你非常开心：阿巴，俺可真厉害！  鱼皮：不错不错，你已经掌握了 ES 的基本操作，算是学会 80% 了。不过 ES 还有很多值得学习的实用特性，进一步优化你的搜索功能。 ### 倒排索引 鱼皮：先来考考你，你知道为什么 ES 能搜得又快又准么？ 你挠挠头：阿巴阿巴…… 鱼皮笑道：关键在于它使用了 **倒排索引** 来存储数据，这是 ES 最核心的特性。 举个例子，假设咱们要存 3 篇博客文档，用 MySQL 数据库的话，存储结构是这样的： | 文档 id | 文档内容 | | ------- | ---------------- | | 1 | 感谢关注鱼皮 | | 2 | 鱼皮是一名程序员 | | 3 | 感谢关注编程导航 | 这种结构下，如果用户搜 “鱼皮程序员”，MySQL 会傻乎乎地把它当成一整个词去匹配，结果可能啥也搜不到。  而 ES 的做法不一样。它会先把文档内容按照单词进行切分，这个过程叫 **分词**。然后再构建 **单词到文档 id 的映射关系**，也就是 **倒排索引**。  有了上述的倒排索引，当用户搜索 “鱼皮程序员” 时，搜索引擎数据库会先对搜索词进行分词，得到 “鱼皮” 和 “程序员”，然后根据这两个词汇就能找到文档 id 1、2 了。不用再一行一行遍历表内所有的数据，实现了更灵活、快速的 **模糊搜索** 。  你两眼放光：原来如此，牛啊牛啊！ 但是 ES 怎么知道一句话该拆成哪些词呢？ ### 分词器 鱼皮：好问题，这就要靠 **分词器** 了，它负责把一段文本拆成一个个词。 ES 内置了标准分词器，它基于 Unicode 文本分割算法设计，会按空格和标点符号等来切分文本。但这个规则只适合英文，对中文基本是一个字一个字地拆，效果很差。  所以如果你要做中文搜索，必须安装 [**IK 分词器**](https://github.com/infinilabs/analysis-ik)。它是专门为中文设计的，能够智能识别中文词汇的边界，把句子正确地拆分成有意义的词语。 IK 提供了两种分词模式： - `ik_smart` 是智能分词，尽量把词分得少一点，比如 "好学生" 就只会拆成 "好学生" 一个词 - `ik_max_word` 是最大化分词，能拆的都拆，"好学生" 会被拆成 "好学生"、"好学"、"学生" 三个词。  一般建议索引的时候用 `ik_max_word` 尽可能多分词，搜索的时候用 `ik_smart` 提高精确度。 此外，IK 还支持自定义词典。比如你想让 “程序员鱼皮” 作为一个完整的词不被拆开，加到词典里就行了。  ### 高亮显示 你好奇道：既然 ES 能分词，那能不能在搜索结果中把命中的关键词标红啊？  鱼皮：当然可以，ES 支持 **高亮显示** 功能。只需要在查询里加个 `highlight` 参数，指定要高亮的字段就行： ```json GET /article/_search { "query": { "match": { "title": "鱼皮教程" } }, "highlight": { "fields": { "title": {} } } } ``` 返回结果里，命中的关键词会自动被 `<em>` 标签包起来，前端拿到之后加个颜色样式就搞定了。  你两眼放光：这也太方便了吧！  不过还有个问题，现在虽然能够搜索到内容了，但怎么把最相关的结果排到前面呢？ ### 相关性评分 鱼皮：好问题。ES 会给每个搜索结果计算一个分数，放到 `_score` 字段中，分数高的排在前面。  你好奇道：这个分数是怎么算的呢？ 鱼皮：ES 默认用的是 **BM25 算法**，主要考虑三个因素： - **词频**，关键词在文档里出现的次数越多，分数越高。这很好理解，一篇文章里反复提到 "鱼皮"，说明它很可能就是在讲鱼皮相关的内容。 - **文档长度**，同样出现一次关键词，在短文档里占的比例更大，所以短文档的分数会更高一点。 - **稀有度**，如果一个词在所有文档里都很常见，比如 "的"、"是"，那它对搜索结果的区分度就不大。反过来，如果一个词很少见，只在少数文档里出现，那命中这个词的文档就更有价值，分数也更高。  ### 聚合分析 鱼皮：除了搜索，ES 还有个很实用的功能叫 **聚合分析**，有点像 MySQL 的 `GROUP BY` 分组查询。 比如你想统计每个标签下有多少篇文章，写个聚合查询就行： ```json GET /article/_search { "size": 0, "aggs": { "tag_count": { "terms": { "field": "tags" } } } } ```  除了分组统计数量，ES 的聚合还能做求和、求平均值、找最大最小值、甚至多层嵌套聚合，能够满足开发各类数据报表的需求。  ## 第四阶段：生产环境实践 用了一段时间 ES 后，你开始有点儿飘了。 没事儿就对着新来的实习生阿坤吹牛皮：ES 我闭着眼睛都能写！什么分词、高亮、聚合，我都玩得贼溜儿~  结果没多久，老板黑着脸找到你：有用户投诉，说明明改了自己文章的标题，但是搜索出来还是旧的，怎么回事？  你排查后发现：原来是 ES 里的数据和 MySQL 数据库里的不一样！当初俺只是把数据一次性导入 ES，后来文章在数据库里更新了，但 ES 里还是旧数据。 你有些头大：唉，ES 和 MySQL 是两套独立的系统，数据不会自动同步啊，咋办啊？  这时，旁边的阿坤突然鸡叫起来：我来！ ### 数据同步方案 阿坤一边打篮球一边说：MySQL 和 ES 的数据同步，一般有这么几种方案。 1）定时任务 每隔几分钟扫一遍数据库，把最近更新的数据同步到 ES。优点是实现简单，缺点是有一定延迟。适合数据更新不频繁、对实时性要求不高的场景。  2）双写 每次把数据写入 MySQL 的时候顺便也写一份到 ES。优点是能做到实时同步，缺点是会影响写入性能，而且如果 ES 写失败了还得处理数据不一致的问题。适合数据写入量不大的场景。  3）用 Logstash 它是 ES 官方提供的数据收集工具，可以配置从 MySQL 定时拉取数据同步到 ES。优点是不用写代码，全靠配置驱动，缺点是需要额外部署组件，灵活性也有限。  4）用 Canal 监听数据库 Canal 是阿里开源的一个工具，它会伪装成 MySQL 的从库，实时监听数据库的变更日志。数据库一有改动，Canal 立刻就能感知到，然后同步到 ES。优点是能做到实时同步，缺点是部署和运维相对麻烦一点。  像咱们这个文章系统，更新又不频繁，用户也能接受几分钟的延迟，用定时任务就完全够了。如果以后做电商那种对实时性要求高的系统，再考虑上 Canal。 ### 集群部署 鱼皮走过来拍了拍阿坤的肩膀：不错不错，我再考考你们，如果 ES 服务器挂了怎么办？ 你支支吾吾：重…… 重启？  鱼皮摇头：用户等得起吗？ 阿坤：生产环境肯定不能只部署一台 ES 啊，得搭建 **集群**。 ES 集群中有几种角色的节点。**主节点** 负责管理集群的状态，比如哪些节点在线、索引的元数据等等。**数据节点** 负责存储实际的数据，处理读写请求。一般生产环境至少部署 3 个节点，保证高可用。  鱼皮追问：那如果数据量特别大，一个节点存不下怎么办？ 你眼前一亮，终于等到自己会的问题了，抢答道：删除数据！ 阿坤用看流浪狗的眼神看了你一眼，回答道：这就要说到 **分片** 了。分片就是把一个索引的数据拆成多份，分别存到不同的节点上。这样单个节点存不下的海量数据，也能通过多节点分担。而且多个节点可以并行处理查询请求，性能也更好。  你有些不服气：那万一某个节点挂了，上面的数据不就丢了？ 阿坤：所以还需要 **副本**。副本就是分片的备份。每个分片可以配置若干个副本，存在其他节点上。万一某个节点挂了，副本可以顶上，这样数据就不会丢失，服务也不会中断。  ### 其他生产实践 鱼皮拍了拍阿坤的肩膀：小伙子年轻有为啊！ 这些都是 ES 在生产环境必须考虑的问题，此外还要学习： - 深度分页问题：ES 默认只允许查询前 10000 条数据，再往后翻就会报错。这是为了保护集群性能。如果确实需要给用户深度翻页，推荐使用更高效的 search_after。如果需要导出全量数据，可以结合 Point-in-Time API 使用。 - 性能调优技巧：合理设计 Mapping，该用 keyword 的别用 text；查询的时候多用 filter 少用 query，因为 filter 会缓存结果；还有控制返回字段的数量，别动不动就查全部字段。 - ELK 日志方案：ES 最经典的应用场景之一就是做日志系统。ELK 是三个组件的缩写，E 是 Elasticsearch 负责存储和搜索日志，L 是 Logstash 负责收集和处理日志，K 是 Kibana 负责可视化展示。大厂排查线上问题，基本都靠这一套。  你羞愧地抬不起头：我以为自己已经掌握了 ES，原来只是学了个皮毛…… 鱼皮：小阿巴，你还要好好跟阿坤学习啊。  ## 第五阶段：深入原理 被连环拷问后，你主动找到阿坤：坤哥，我想深入学习 ES 的底层原理，你是怎么学的？ 阿坤有些惊讶：咦？你不背八股文的么？去 [面试刷题网站 - 面试鸭](https://www.mianshiya.com/) 刷刷题就好了呀！  你震惊了：现在的实习生，竟然恐怖如斯！ 鱼皮笑了笑：阿坤你别逗他了。其实可以带着问题去学习，比如 **ES 为什么这么快**？ 你抢答道：因为倒排索引！ 鱼皮：没错，但这只是一方面。ES 底层是基于 Lucene 搜索引擎库的，它的倒排索引结构经过了高度优化。另外 ES 会把常用的数据缓存在内存里，查询时优先从内存读取，速度自然快。再加上 ES 是分布式的，可以把数据分片存储到多个节点，并行处理查询请求，几方面加起来，性能就上去了。  再比如数据是怎么写入的、查询请求是怎么执行的？ 从这些问题出发，去阅读相关的文章，或者像阿坤说的刷一刷 [ES 面试题](https://www.mianshiya.com/bank/1805423815382736897)，就能快速学会很多核心知识点。  如果想系统学习，推荐看 ES 官方文档，因为 ES 的更新太快了，很多书籍可能已经跟不上节奏了。  ## 结尾 若干年后，你已经成为了公司的 ES 搜索专家。不仅能熟练使用 ES 解决各种搜索问题，搭个集群架构也是手拿把掐的。 你也像鱼皮当时一样，耐心地给新人分享学习 ES 的经验，让他们谨记一句话：**ES 是实战型技术，一定要多动手实践！** 更详细的完整版 Elasticsearch 保姆级学习路线，可以在 [编程导航](https://www.codefather.cn/course/1789189862986850306/section/1990755182346022913) 查看。  再次遇到鱼皮是在一条昏暗的小巷，此时的他年过 35，灰头土脸。你什么都没说，只是给他点了个赞，投了 2 个币。 不打扰，是你的温柔~ 
除了MySQL，这 9 种数据库你竟然都不认识？
你是小阿巴，正在公司敲代码。 老板走过来说：小阿巴，给咱们网站加个商品搜索功能吧。 你拍拍胸脯：没问题，我直接用 MySQL 数据库的 LIKE 模糊查询实现搜索，1 小时上线~  结果上线后，用户点击搜索，卡了半天没反应，老板气得脸都绿了。 你急的汗流浃背，只能找到号称『后端之狗』的鱼皮求助：阿巴阿巴，俺用 MySQL 搞不定，咋办啊…… 鱼皮：不是哥们，又不是只有 MySQL 这一个数据库。 下面我来带你认识 10 种不同类型的数据库，让你知道什么场景该用什么数据库。  点个收藏，我们开始~ ⭐️ 推荐观看视频版，有动画更好理解：https://www.bilibili.com/video/BV1ChkjBsEzq ## 关系型数据库 首先是我们接触最多的、也是后端入门必学的 **关系型数据库**。 在关系型数据库中，数据以 **表** 的形式进行组织和存储，每个表就像一个 Excel 表格，包含多个 **行** 和多个 **列**。  比如你要做个学生管理系统，把学生信息存储到关系型数据库中，结构大概是这样的： | 学号 | 学生姓名 | 所属班级号 | | ---- | -------- | ---------- | | 1 | 小李 | 1 | | 2 | 小鱼 | 2 | | 3 | 小皮 | 3 | 上述学生表格中，每一行代表一个学生的信息，每一列代表学生的一个属性。  我们可以使用结构化查询语言 SQL 来对关系型数据库表的数据进行灵活地查询、选择、过滤等。  而关系型数据库最大的特点，就是表和表之间可以 **存在关系**。比如学生管理系统中还可以有班级表，结构如下： | 班号 | 班级名称 | | ---- | -------- | | 1 | 快乐班 | | 2 | 泰酷班 | | 3 | 躺平班 | 如果我想知道某个学生所属的班级信息，只需要在查询时将学生表的 **所属班级号** 和班级表的 **班号** 进行关联，而不用把所有表格的列存储在一起，非常灵活。  通过 SQL 可以连接查询多张表：  得到下面的查询结果： | 学号 | 学生姓名 | 所属班级号 | 班级名称 | | ---- | -------- | ---------- | -------- | | 1 | 小李 | 1 | 快乐班 | | 2 | 小鱼 | 2 | 泰酷班 | | 3 | 小皮 | 3 | 躺平班 | 此外，关系型数据库遵循 ACID 原则（原子性、一致性、隔离性和持久性），通过 **事务** 机制可以保证多个操作同时进行时，数据的状态保持一致。  举个例子，A 给 B 转账，A 扣钱的同时 B 也会加钱，不会出现 A 扣了钱 B 却没收到钱的情况。  正因为关系型数据库既能灵活查询、又能准确写入，所以它几乎可以被应用在任何项目中。比如各类管理系统、数据分析系统、金融银行系统等。 比较主流的关系型数据库产品有： - MySQL：开源易学，后端开发必学的数据库 - Oracle：人称 “甲方的数据库”，主要是大型企业和政府机构在用，功能强大但授权费用昂贵 - PostgreSQL：开源界的天花板，功能最全面 - SQL Server：微软出品，和 Windows 系统、.NET 生态集成度高 - SQLite：整个数据库就是一个文件，不需要服务器，非常轻量。被广泛应用在手机 APP、浏览器中，你的手机里可能有几十个 SQLite。  对于大多数项目，用 MySQL 等关系型数据库来存储数据就足够了。但如果要存储的数据间没有复杂关系、或者需要极致的性能时，它并不是最佳选择。 你点点头：俺知道，就好比俺要写一篇文章，没必要非得把内容塞进 Excel 表格里，直接放到 Word 文档里会更方便编辑和阅读。  鱼皮：没错，这时就需要与关系型数据库互补的 **非关系型数据库**。 ## 非关系型数据库 非关系型数据库又叫 NoSQL（Not Only SQL），适合存储关系不强的、结构灵活的、需要快速访问的数据。 打个比方，关系型数据库像图书馆，书籍分类明确、摆放有序、借阅有规矩；非关系型数据库像你的书桌，怎么顺手怎么放，拿取方便最重要。  在实际项目开发中，最常用的非关系型数据库是 KV 数据库和文档数据库。 ### KV 键值数据库 KV 即 Key-Value，数据是以 **键值对** 的方式存储在数据库中的，可以理解为一个超大的 HashMap，数据库中存储的每个键都 **唯一对应** 一个值。  比如存储用户信息和热门商品信息，结构是这样的： | Key 键 | Value 值 | | ----------- | --------------------------- | | user:1001 | {"name":"鱼皮", "age":25} | | product:hot | ["商品1", "商品2", "商品3"] | 键和值都可以是任意类型的数据，包括字符串、数字、数组、JSON 对象等，非常灵活。  由于 KV 存储的结构简单清晰，我们能够很轻松地根据某个键查找出对应的值，就像查字典一样，读写数据的性能都非常高。  此外，KV 数据库的可扩展性很强。因为数据间不存在直接关联，我们可以把键值对分散到多台机器上存储，通过数据分片、负载均衡等策略来支持海量数据的高并发访问。  由于高性能和高可扩展性，KV 数据库被广泛应用于缓存、分布式会话、分布式锁、实时统计等场景。 最经典的 KV 数据库肯定是 Redis，它是开源的、基于内存的数据库，不仅支持丰富的数据类型和功能，还有持久化等重要特性，也是后端必学的技术。其他的常用 KV 数据库有 Memcached、Etcd、LevelDB、RocksDB 等。  ### 文档数据库 文档数据库也属于非关系型数据库。顾名思义，它适用于存储和管理 **半结构化的** 文档数据，数据一般以 JSON（BSON）格式存储。  相比于关系型数据库中严格定义的表格行列，文档数据库的数据结构更像是一份份独立的文档，每个文档都可以包含不同类型和格式的数据，结构非常灵活。  比如存储博客文章，结构是这样的： | 文档 ID | 文档数据 | | ------- | ------------------------------------------------------------ | | 1 | {"_id": 1, "title": "文章标题1", "content": "这是文章1的内容"} | | 2 | {"_id": 2, "title": "文章标题2", "author": "程序员鱼皮"} | 你挠挠头：诶，文档 1 和文档 2 的字段都不一样啊，这也行？  鱼皮：没错，这就是文档数据库的灵活性。当我们要给某个文档新增一个字段时，不需要像关系型数据库那样先改表结构，直接加就完事了。  而且支持水平扩展，可以分散到多台服务器上存储，适用于内容管理系统、博客平台、电商商品详情页等场景。  推荐学习的文档数据库是 MongoDB，因为它存储的就是 JSON（BSON）格式数据，对前端同学很友好，入门难度也很低。  ## 特定场景的数据库 虽然关系型和非关系型数据库已经能够满足大部分场景，但在一些特殊场景下，使用专门设计的数据库会更高效。就像你可以用菜刀砍树，但用斧子会更快更省力。 ### 搜索引擎数据库 专门为搜索功能设计的数据库。它能存储和管理大量文本数据，提供快速、准确、灵活的全文检索功能。  你挠挠头：凭什么它能做到这些呢？ 鱼皮：秘密在于它使用了 **倒排索引** 的方式存储数据。 以存储博客文档为例，关系型数据库的存储结构是： | 文档 id | 文档内容 | | ------- | ---------------- | | 1 | 感谢关注鱼皮 | | 2 | 鱼皮是一名程序员 | | 3 | 感谢关注编程导航 | 我们能够根据 id 来查找到对应的单篇文档，也可以通过搜索精确的关键词，来查找到多篇文档。 比如搜索 "鱼皮"，能搜出文档 1、2。  但是，如果你搜索 "鱼皮程序员"，是无法得到搜索结果的，因为没有任何一个文档的内容完全包含 "鱼皮程序员" 这个词。  而在搜索引擎数据库中，首先会将文档内容按照单词进行分割，也就是 **分词**。然后 **建立倒排索引**，也就是构建 **单词到文档 id 的映射**。  有了上述的倒排索引，当用户搜索 "鱼皮程序员" 时，搜索引擎数据库会先对搜索词进行分词，得到 "鱼皮" 和 "程序员"，然后根据这两个词汇就能找到文档 id 1 和 2 了。不用再一行一行遍历表内所有的数据，实现了更灵活、快速的搜索。  此外，搜索引擎数据库还支持 **相关性排序**，能够根据用户的搜索词对所有搜索结果进行打分，把最相关的文档排到最上面，就像谷哥度娘那样。  主流的搜索引擎数据库技术有 Elasticsearch、Apache Solr 等，建议只学习 Elasticsearch 就够了，它的社区最活跃、学习资料最丰富。 ### 向量数据库 向量数据库是专门用于存储和处理 **高维向量数据** 的数据库，也是 AI 时代最火的数据库。 你好奇道：啥是向量？ 鱼皮：简单来说，向量是一个数字数组，每个数字代表一个 **特征** 维度。  举个例子，在人脸识别系统中，我们需要通过人脸的特征来判断是否为同一个人。每张人脸图像都可以通过 AI 模型转换成一个向量，这个向量可能包含成百上千个数字，每个数字代表图像的一个 **抽象** 特征维度。  实际上，很难说清楚每个数字代表什么，但便于理解，你可以 **想象** 下标 0 代表鼻子大小，下标 1 代表眼睛距离等等，以此类推。  之后通过余弦相似度等算法来计算两个向量的相似度，就能判断出两张人脸是不是同一个人。  向量数据库能够高效存储这些多维向量数据、快速计算向量的相似度、并实现各种不同算法的相似性搜索。适用于人脸识别、推荐系统、语义搜索等场景。  在 AI 时代，可以用向量数据库给 AI 提供特定领域的知识库，大大提升回答的准确性。  主流的向量数据库技术有 Milvus、Pinecone 等，像 PostgreSQL 关系型数据库也通过插件支持存储向量类型的数据。  ### 图数据库 图数据库是专门用于存储和处理 **图结构数据** 的数据库。 注意，这里的 “图” 可不是照片或图表，而是数学中的图论概念，由节点（Node）和边（Edge）构成的图形结构。  比如我们要存储一个社交网络的朋友关系，对应的图可能是由多个用户节点和好友关系边组成的。  在图数据库中，需要 2 个表格来存储。 1）节点信息表： | 节点 id | 节点名 | | ------- | -------- | | 1 | 鱼皮 | | 2 | 阿巴 | | 3 | 编程导航 | 2）边信息表： | 边 id | 边类型 | 起始节点 | 结束节点 | | ----- | ------ | -------- | -------- | | 1 | 好友 | 1 | 2 | | 2 | 好友 | 2 | 3 | 通过存储这些节点和边的信息，图数据库就能快速查询和分析复杂的关系网络。比如查找 “朋友的朋友”、计算两个用户之间的最短路径、发现社交圈子等等。  因此，图数据库非常适合构建社交网络、推荐系统、知识图谱等。 比较主流的图数据库有 Neo4j、TigerGraph 等，都支持复杂的图算法和分布式扩展，能够通过并行计算加速图形处理。 ### 时序数据库 时序数据库是专门用于高效存储和处理 **时间序列** 的数据库。 时间序列是指以时间作为主要维度的数据序列，也就是每个数据单元都带着 **时间戳**，按时间顺序排列。  举个例子，在服务器监控系统中，我们需要每分钟记录服务器的 CPU 使用率、内存使用率等指标，数据结构如下： | 时间戳 | 设备ID | CPU使用率 | 内存使用率 | | ---------------- | --------- | --------- | ---------- | | 2026-01-08 10:00 | Server001 | 45% | 60% | | 2026-01-08 10:01 | Server001 | 48% | 62% | | 2026-01-08 10:02 | Server001 | 52% | 65% | 有了这些数据，我们就能够按照时间范围进行高效查询、做聚合分析（比如计算过去 1 小时的平均 CPU 使用率）、进行数据可视化展示。  因此，时序数据库非常适用于物联网设备监控、服务器性能监控、金融交易数据分析等场景。 主流的时序数据库技术有 InfluxDB、TimescaleDB 等，一般会配合 Grafana 监控看板一起使用，实现数据存储 + 快速可视化。你在运维团队看到的那些酷炫的实时监控大屏，背后就是时序数据库在支撑。  ### 列存数据库 区别于传统的行式数据库，列存数据库 **以列作为基本的存储单位**，把每一列的数据存储在一起。 拿公司每天的收入来举个例子，传统的行式数据库是这么存储的： | 日期 | 销售额 | 成本 | 利润 | | ---------- | ------ | ---- | ---- | | 2026-01-01 | 500 | 600 | -100 | | 2026-01-02 | 280 | 450 | -170 | | 2026-01-03 | 290 | 480 | -190 | 而在列存数据库中，底层大概是这么存储的，看起来像是对矩阵做了一次转置： | 日期 | 2026-01-01 | 2026-01-02 | 2026-01-03 | | ------ | ---------- | ---------- | ---------- | | 销售额 | 500 | 280 | 290 | | 成本 | 600 | 450 | 480 | | 利润 | -100 | -170 | -190 |  如果我们要统计这 3 天公司的总利润，传统的行式数据库需要依次读取每一行的数据，然后再提取出利润这一列进行计算；而列存数据库直接读取利润这一列就行了，不用管其他列，大大提高了数据分析和聚合操作的效率。  而且从计算机底层来分析，把相同类型的数据在同一列中连续存储，可以实现更好的数据压缩效果、节约存储空间。  因此，列存数据库适用于报表生成、数据仓库、商业智能分析等场景。 主流的列存数据库技术有 ClickHouse、Apache HBase、Druid 等，都是大数据开发的必修课。 ## 融合型数据库 前面我们讲了关系型和非关系型数据库，要么强调灵活查询、要么强调性能和扩展，各有侧重。 但有些数据库偏偏 “既要又要”，要把两者的优点融合到一起，这就是接下来要讲的融合型数据库。 ### NewSQL 数据库 NewSQL 是一类新兴的数据库，它融合了传统关系型和非关系型数据库的优点。既有传统 SQL 数据库的 ACID 特性和事务支持，又有 NoSQL 的水平扩展能力和高性能。支持标准 SQL 查询、分布式架构、自动容错和故障恢复，可以替代传统的分库分表方案，特别适用于大厂的高并发系统。  主流的 NewSQL 数据库有 TiDB、CockroachDB、Google Spanner 等。其中 TiDB 是国产之光，支持 HTAP（混合事务分析处理），而且完全兼容 MySQL 协议，迁移成本低；CockroachDB 人称 “蟑螂数据库”，因为它像蟑螂一样顽强，集群中一个节点挂了，其他节点依然能正常服务，打不死。  ### 多模数据库 区别于前面所有存储单一数据模型的数据库，多模数据库能够直接在一个数据库里同时存储和处理 **多种不同类型** 的数据。比如关系型数据、文档数据、图形数据、键值对数据等等，非常灵活、省去了维护多个数据库的麻烦。  而且多模数据库还支持跨模型事务，能够更轻松地实现数据的一致性和完整性，不需要手动实现跨库事务、跨库数据同步这些复杂操作。  虽然听起来很厉害，但实际开发中很少用，因为样样通样样松，多模数据库的性能往往不如专注单一场景的数据库。 原生的多模数据库技术有 ArangoDB、OrientDB 等，它们从设计之初就是为多模式设计的。前面也提到，虽然 PostgreSQL 这样的老牌关系型数据库可以通过丰富的插件支持多种数据类型（比如 JSON 文档、向量、地理空间数据等），但它的核心始终是关系模型，多模支持算是锦上添花，不过这也体现了 PostgreSQL 的强大。  ## 怎么选择数据库？ 你一脸懵逼：阿巴阿巴，你讲了这么多数据库，俺头都大了，到底该选哪个？  鱼皮笑了笑：别慌，数据库选型其实没那么复杂。 优先选用 MySQL / PostgreSQL + Redis，能覆盖 90% 的项目需求。 有特定功能或优化需求时，再选择专业数据库。比如需要搜索功能了，再加 Elasticsearch；要做 AI 知识库了，再加向量数据库。 毕竟每多一个数据库，就多一份运维工作和故障风险。  你：学会了学废了！对了鱼皮，我还听说过数据湖、数据仓库，这些是啥啊？ 鱼皮：简单来说，数据仓库是用来存储和分析大量结构化数据的；数据湖更像个大水池，什么数据都能往里扔，包括日志、图片、视频这些非结构化数据。它们更多是大数据架构层面的概念。  感兴趣的话，点个关注，后面专门出一期讲讲~  ## 更多 💻 编程学习交流：[编程导航](https://www.codefather.cn/) 📃 简历快速制作：[老鱼简历](https://www.laoyujianli.com) ✏️ 面试刷题神器：[面试鸭](https://www.mianshiya.com) 📖 AI 学习指南：[AI 知识库](https://ai.codefather.cn/)
DAY4 SQL完结
### binLog二进制归档日志 > 保存所有执行过的修改操作语句，如果mysql服务意外宕机，可通过二进制日志文件排查用户操作或表结构操作进行数据恢复 启用binlog会影响服务器性能，但如果需要数据恢复或者主从复制，开启的好处大于对服务器影响 在配置文件[mysqld]新增如下配置 > >  > - 查看binlog参数 - show variables like ‘%log_bin%’;  - show binary logs; - bin log格式 - 使用bin_format设置bin_log日志记录格式 - **STATEMENT** 基于SQL语句复制，每一条修改数据的SQL都会记录到master机器的bin_log中，这种方式日志量小，节约IO开销，提升性能，但是对于语句中有一些只有执行过程才能确定结果的函数（如UUID()\SYSDATE()等）同步到slave中去，会导致与master机器执行结果不一致 - **ROW** 基于行的复制，日志中会记录每一行数据被修改的形式，然后在slave端对相同的数据进行修改，虽然可以解决函数、存储过程等在slave机器的复制问题，但是这种方式日志量大，性能更差。 - **MIXED** 混合模式，以上两种的结合，mysql根据执行的具体SQL来区分判断使用哪种方式记录日志。 - binlog 磁盘写入机制 - 由sync_binlog 参数控制 - 设置为0 每次提交只写入到page cache，依赖操作系统的同步机制写到磁盘，性能最好，但是机器宕机时会丢失数据 - 设置为1 每次提交事物之前写入到磁盘，数据最安全，保证事物操作不丢失，但是会损失性能 > 当innodb_flush_log_at_trx_commit=1且bin_log开始时，sync_binlog也应当设置为1 > - 设置为N(n >1) 每次提交事物都写入到page cache，收集到N个事物再写入磁盘，发生宕机时会丢失这N个事物 - 当发生以下事件时，bin_log会重新生成 - 服务器启动或重启 - 服务器日志刷新 flush logs - 日志文件大小达到max_binlog_size，默认为1GB - binlog文件删除 - 删除当前bin_log文件 reset master; - 删除指定日志文件之前的所有文件 purge master logs to ‘mysql-binlog.000006’;(删除6之前的所有文件) - 删除指定日期前的日志索引中bin_log文件 purge master logs before ‘2025-01-11 12:00:00’; - 查看binlog文件 - mysqlbinlog --no-defaults -v --base64-output=decode-rows +binlog绝对路径 - mysqlbinlog --no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25-winx64/data/mysql‐binlog.000007 start‐datetime="2023‐01‐21 00:00:00" stop‐datetime="2023‐02‐01 00:00:00" start‐position="5000" stop‐position="20000"  - 数据恢复 - 从binlog文件中找到数据丢失的起始和结束位置 > 起始位置一般找最先丢失数据的那个事物BEGIN之前的一个位置标识 结束位置一般找最后丢失数据的那个事物COMMIT之后的第一个位置标识 > - 执行恢复命令 - mysqlbinlog --no-defaults --start-position=219 --stop-position=701 --database=dbName D:/dev/mysql‐5.7.25-winx64/data/mysql‐binlog.000007 | mysql -u root -p password -v dbName (根据位置恢复) - mysqlbinlog --no-defaults --start-datetime=”yyyy-mm-dd hh:MM:ss(将binlog文件中的时间戳做转换)” —stop-timw=”yyyy-mm-dd hh:MM:ss” --database=dbName D:/dev/mysql‐5.7.25-winx64/data/mysql‐binlog.000007 | mysql -u root -p password -v dbName (根据位置恢复) > 由于bin log占用空间比较大，一般不会长时间保存，万一出现删库或者大量数据丢失的情况，只靠bin log无法恢复 正常来说需要数据库做备份，同时使用bin log做归档，采用两者结合的方式进行恢复 - 数据库备份与恢复 - mysqldump -u root dbName > backFileName; 备份整个库 - mysqldump -u root dbName tableName > backFileName; 备份整个表 - mysqldump - u root dbName < backFileName; 从备份文件恢复库 > **为什么需要redo log和bin log两份日志？** 一个属于InnoDB引擎层，一个属于MySQL Server层，是MySQL分层架构与功能设计分离的体现 两者职责不同 redo log 属于InnoDB运行的必需品，记录哪个属于页改了什么，是物理日志，循环复用磁盘空间，用于崩溃恢复 bin log则不是必须的（主从复制场景除外），记录执行了什么SQL，是逻辑日志，追加写入，可以配置保留时间，用于主从复制和数据归档/恢复 如果只有redo log，物理页变更无法在不同实例间通用，实现不了主从复制。 如果只有bin log，在InnoDB崩溃后，无法从bin log恢复未刷盘的数据页。 ### Undo Log回滚日志 InnoDB 对undo log采用段管理方式，也就是回滚段（rollback segment），每个回滚段记录1024个undo log segment,每个事物只使用一个 > MySQL5.6+，InnoDB支持最大128个回滚段，支持最大同时在线的事物数为128 * 1024 > **undo log什么时候回收？** 新增类型的在事物提交后进行回收，修改类型的在没有任何事物用到该版本信息的时候才进行回收 ### Doublewrite Buffer双写缓冲 解决部分页写入问题。InnoDB一页是16KB，但操作系统的IO通常是4KB，意味着写一个InnoBD页需要进行四次磁盘IO，假如写入的时候期间断电，那这个页就作废了。于是InnoDB引入双写缓冲区来解决这个问题。 InnoDB在每次将页数据写入数据文件前，先把数据写到双写缓冲区，只有当页面安全写入缓冲区内后，才会将最终的完整数据写入数据文件，这样即使刷盘断电，也能从缓冲区中找到有效的完整页面拿出来重新刷回去，保证数据不会损坏 - 补充参数 - innodb_thread_concurrency 并发线程数，默认值为0表示不限制，通常配置为与CPU核心数相同或两倍，超过配置线程数则排队等待，不宜配置太大，可能会导致锁竞争严重，影响性能 - innodb_buffer_pool_size 存储引擎buffer pool缓存池的大小，一般配置为物理内存的60%-70%.通常来说，InnoDB存储引擎的缓冲池命中率不应该小于99%  > 计算缓冲池命中比例 show global status like 'innodb%read%'\G; Innodb_buffer_pool_reads：表示从物理磁盘读取页的次数 Innodb_buffer_pool_read_ahead：预读的次数 Innodb_buffer_pool_read_ahead_evicted：预读的页，但是没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率 Innodb_buffer_pool_read_requests：从缓冲池中读取页的次数 Innodb_data_readsInnodb_rows_read：总共读入的字节数 Innodb_data_reads：发起读取请求的次数，每次读取可能需要读取多个页  innodb_lock_wait_timeout 行锁锁定时间 ### 错误日志 记录数据库启动和停止，以及运行过程中发生任何严重错误的相关信息，当数据库出现故障导致无法正常使用时查看 ### 通用查询日志 记录用户所有操作，包括启动和关闭MySQL服务、所有用户连接开始时间和截止时间，发给MySQL数据库服务器的所有指令等，不论语法正确还是错误，成功与否，都会记录下来。资源消耗大，一般不会启用。 > 为什么要做这种复杂设计，直接写磁盘不好吗？ 磁盘随机写性能相当差，高并发场景完全不能适用，这样设计虽然看起来复杂，但是可以保证每个更新请求都是更新内存buffer pool，然后顺序写日志文件，性能提高（更新内存快，顺序写也比随机写快）的同时还能保证数据的一致性。 ## MySQL8 ### 新特性 - 新增降序索引 联合索引可以指定索引值按倒序 - group by不再隐式排序，需手动order by - 增加隐藏索引 通过设置参数invisible=No，将索引隐藏对用户不可见，但数据库后台会维护索引，用户在使用隐藏索引时，即使使用force index，优化器也不会走该索引。 - 新增函数索引 设置索引时可以加到函数上 - 上悲观锁新增nowait（立即报错返回）、 skip locked（跳过锁定行）语法 - 新增innodb_dedicated_server自适应参数，根据服务器内存大小自动配置buffer pool的大小（如果服务器只用于搭建Mysql服务的话可以这么做） - 死锁检查控制 innodb_deadlock_detect 用于执行死锁检查，默认打开，比较消耗性能 - undo log不再使用系统表空间 - bin log日志过期时间精确到秒（以前是按天设置） - 新增窗口函数 over(partition by field order by field); > 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK() > > > 分布函数：PERCENT_RANK()、CUME_DIST() > > 前后函数：LAG()、LEAD() > > 头尾函数：FIRST_VALUE()、LAST_VALUE() > > 其它函数：NTH_VALUE()、NTILE() > - 默认字符集由latin1变为utf8mb4 - MyISAM系统表全部换成InnoDB表 - 元数据存储变动 表结构文件.frm全部存入了mysql.ibd里 - 自增变量持久化 > 8.0之前 自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，8.0之后会保持关机之前的值，当自增键发生更新时才会更新 > - 参数修改持久化 > set global 设置的变量参数在mysql重启后会失效。 > ### 主从复制 - **什么是复制** - MySQL Replication是官方提供的主从同步方案，也是用的最广的同步方案。Replication(复制)使来自一个 MySQL数据库服务器（称为源（Source））的数据能够复制到一个或多个 MySQL 服务器（称为副本（Replica））。默认情况下，复制是异步的；副本不需要永久连接即可从源接收更新。 - 复制的优势 - **高可用** 通过一定的机制，实现跨主机复制数据，从而获得一定的高可用能力； - **性能扩展** 由于复制机制提供了多个数据备份，可以通过配置一个或多个副本，将读写请求分散至各个节点，从而获得性能提升 - **异地灾备** 将副本节点部署到异地机房，就可以获得一定的异地灾备能力 - 复制的缺点 - 没有故障自动转移，容易造成单点故障 - 主从库之间有复制延迟问题，容易导致数据不一致 - 从库过多对主库的负载以及网络带宽都会带来很大负担 - **复制方式** - 基于源的二进制日志bin log复制 - 副本从源中读取二进制归档日志，并在副本的本地数据库上执行二进制日志中记录的事件 - 基于全局事物标识符(GTID)的方式 - 完全基于事物复制，很容易确定源和副本是否一致，只要在源上提交的所有事物也在副本上提交，能够保证主从数据一致性 - **复制数据同步方式** - 异步复制(默认方式) - 提交事务和复制这两个流程在不同的线程中执行，互相不会等待，这是异步复制。异步复制的劣势是，可能存在主从延迟，如果主节点宕机，可能会丢数据。  - 半同步复制(5.7新增) > 虽然提高了可用性，但是因为有主从节点的网络交互，以及从节点的刷盘消耗，性能会差一些 > - 主节点在收到客户端请求后，必须在完成本节点日志写入的同时，还需要至少等待一个从节点完成数据同步的响应之后才会响应请求 - 从节点只有在写入relay log并完成刷盘后才会向主节点响应 - 当从节点响应超时时，主节点会将同步机制退化为异步复制，在至少一个从节点恢复，并且完成数据追赶后，主节点会将同步机制恢复为半同步复制  - **设计理念：复制状态机** - 任何一个存储系统，无论它存储的是什么数据，用什么样的数据结构，都可以抽象成一个状态机。存储系统中的数据称为状态（也就是 MySQL 中的数据），状态的全量备份称为快照（Snapshot），就像给数据拍个照片一样。我们按照顺序记录更新存储系统的每条操作命令，就是操作日志（Commit Log，也就是 MySQL 中的 Binlog）。  ### 高可用集群 **InnoDB Cluster是MySQL官方实现高可用+读写分离的架构方案**,其中包含以下组件 - **MySQL Group Replication**,简称MGR,是MySQL的主从同步高可用方案,包括数据同步及角色选举 - **Mysql Shell** 是InnoDB Cluster的管理工具,用来创建和管理集群 - **Mysql Router** 是业务流量入口,支持对MGR的主从角色判断,可以配置不同的端口分别对外提供读写服务,实现读写分离 [查看笔记](https://share.note.youdao.com/s/2EjCTYwR)
Day 3 事物实现原理
### 事物优化 > **大/长事物影响** 并发大容易撑爆连接池 数据锁太久容易造成阻塞和锁超时 执行时间长容易造成主从延迟 出错回滚时间长 undo log膨胀 容易造成死锁 - 事物颗粒度最小化 - 事物中避免远程调用，能异步就异步 - 避免一次性处理大量数据，可拆分成多个小事物 - 涉及更新加锁操作尽可能放在事务靠后位置 - 应用侧保证数据一致性，非事物执行（并发是在太大的情况，尽可能减少事物操作，一般不推荐这么干，业务代码复杂度太高） ### Undo Log和版本链 - InnoDB每条记录里都有两个隐藏字段： trx_id 记录最后修改这条数据的事物ID，roll_pointer指向undo log。每次update不会覆盖原数据，而是把旧值存到undo log里，新值存到数据页。roll_pointer指向旧数据，形成一条完整的版本链 - 普通SELECT走快照读，不加锁，顺着版本链找到对自己可见的版本返回，写操作是当前写，读写各走各的。 - Read View一致性视图  ### Redo Log重做日志 - 重要参数 - innodb_log_buffer_size 指定redo log buffer 大小 - 查询 show variables like ‘%innodb_log_buffer_size%’ ; - 默认16M，最大值为4096M，最小值为1M - innodb_log_group_home_dir 指定redo log存储位置 - 查看 show variables like ‘%innodb_log_group_home_dir%’; - innodb_log_files_in_group 指定redo log文件个数 - show variables like ‘%innodb_log_files_in_group%’; - 默认两个，最大100个 - innodb_log_file_size 指定单个redo log文件大小 - show variables like ‘%innodb_log_file_size%’; - 默认48M，最大值为512G（是整个redo log文件之和） - 文件写入过程 > 顺序循环写（类似于带双指针的循环队列） > - write pos 是当前的位置 checkpoint 是当前要擦除的位置，两个指针之间的部分就是可写的位置 - innodb_flush_log_at_trx_commit控制写入策略 - 设成0 事物提交时只刷到redo log buffer里，由后台线程每秒刷一次到缓存里再转到硬盘，宕机会丢失1s数据 - 设成1(默认值) 每次提交都将redo log持久化到磁盘，数据最安全，但性能最差 - 设成2 每次提交记录redo log后，写到操作系统的page cache里，由后台线程每秒一次写入磁盘，库挂了数据还在，操作系统挂了没来得及写的话就会丢1s数据 
Day2 SQL调优
## SQL调优 核心思路：减少磁盘I/O和避免无效计算 - 索引层优化 - 合理设计联合索引，利用覆盖索引尽可能减少回表查询 - 使用索引时尽量避免引起索引失效的情况 - 查询优化 - 禁止使用SELECT *，只查必要字段 - 避免LIKE %VALUE 左模糊查询 - 避免查询字段隐式转换 - 减少IN 查询使用 - 排序优化 - 排序字段尽量走索引 - 当where条件与order by条件冲突时，优先保证where条件 > filesort的排序类型 > 单路排序：把所有select的字段都放进sort_buffer进行排序，排完直接返回 > 双路排序：只放排序字段和id排序，排完后回表取其他字段  - 分页查询优化 - 使用游标分页替代LIMIT（或者冷热数据分离，将历史数据挪到历史表） - 关联SQL优化 > MySQL 表关联算法 > > - 嵌套循环链接算法 Nested- Loop Join （NLJ） > - 一次一行循环从驱动表读行，拿到关联字段根据关联字段在被驱动表里取出满足条件的行，然后取出两表的结果合集 > - 基于块的嵌套循环算法Block Nested- Loop Join （BNL） > - 把驱动表的数据读取到join_buffer中，然后扫描被驱动表，把被驱动表的每一行取出来跟join_buffer中的数据做对比 - 小表驱动大表 - 关联字段加索引 - IN/EXISTS 优化 - 查询时小表驱动大表，主表数据量小于驱动表时，EXISTS的性能优于IN - count() 优化 - count(*) count(1) count(主键)扫描所有行，效率无差别 > 使用count(*)时，底层引擎会做优化，如果表上有二级索引，优化器通常走二级索引而不是主键索引，因为二级索引叶子节点只存主键值，数据量比聚簇索引小很多 count(1) 里面的1可以换成任何字符串和数字，只是一个占位符 > - count(field) 统计所有字段非null的行，如果字段是索引，效率与以上无差别，如果是非索引字段且有null值，效率会略低 - 大表count优化 - 取近似值 show table status like ‘field’; - 使用缓存维护计数，每次增删同步更新redis计数器，查询时读缓存，但是要注意缓存和数据库一致性问题 - 单独维护计数 ### 事物如何实现 靠四个核心组件：**Redo Log**、**Undo Log**、**锁**、**MVCC，最终保证一致性** Redo Log保证持久性。事物提交时，修改先写到redo log再写磁盘数据页，就算写数据页宕机，重启后也能根据redo log恢复数据。 Undo Log保证原子性。每次修改数据前，先把原值存到undo log里，事物回滚时，按undo log的记录恢复回去，要么全做完，要么全撤销。 锁机制保证隔离性。InnoDB支持行锁，两个事物修改同一行，必须等待另一个释放锁。 MVCC（Multi-Version Concurrency Control 多版本并发控制）保证隔离性的读写并发。写操作产生新版本，读操作根据事物启动时间去版本链上找到属于自己的那个版本。 > InnoDB每条记录里都有两个隐藏字段： trx_id 记录最后修改这条数据的事物ID，roll_pointer指向undo log。每次update不会覆盖原数据，而是把旧值存到undo log里，新值存到数据页。roll_pointer指向旧数据，形成一条完整的版本链 
打卡Day1 数据库索引及优化
## 数据库认识 - 数据库设计三范式 1. 对属性的原子性，要求属性具有原子性，不可再分解 2. 对记录的唯一性，要求记录有唯一标识，即实体的唯一性，不存在部分依赖 3. 建立在第二范式之上，必须满足第二范式，非主键属性之间不存在函数依赖 - 数据库的事物 - 特性ACID 1. [原子性](https://baike.baidu.com/item/%E5%8E%9F%E5%AD%90%E6%80%A7/0?fromModule=lemma_inlink)（Atomicity，或称不可分割性） 2. [一致性](https://baike.baidu.com/item/%E4%B8%80%E8%87%B4%E6%80%A7/0?fromModule=lemma_inlink)（Consistency） 3. [隔离性](https://baike.baidu.com/item/%E9%9A%94%E7%A6%BB%E6%80%A7/0?fromModule=lemma_inlink)（Isolation，又称独立性） 4. [持久性](https://baike.baidu.com/item/%E6%8C%81%E4%B9%85%E6%80%A7/0?fromModule=lemma_inlink)（Durability） - 事务隔离级别 | 隔离级别 | 脏读(Dirty Read) | 不可重复读(Non-repeatable Read) | 幻读(Phantom) | 描述 | | --- | --- | --- | --- | --- | | 读未提交 (Read Uncommitted) | 可能 | 可能 | 可能 | 允许一个事务读取另一个事务未提交的数据，隔离级别最低 | | 读已提交 (Read Committed) | 不可能 | 可能 | 可能 | 一个事务只能读取已经提交的数据，是大多数数据库的默认隔离级别 | | 可重复读 (Repeatable Read) | 不可能 | 不可能 | 可能 | 在同一事务中多次读取同样的数据结果一致，MySQL的默认级别 | | 串行化 (Serializable) | 不可能 | 不可能 | 不可能 | 最高隔离级别，完全串行化执行，性能最差 | ## 索引 **索引**是数据库中一种特殊的**数据结构**，用于提高数据库表的查询效率，可以理解为是一种排好序的快速查找数据结构。它类似于书籍的目录，可以帮助数据库系统快速定位和访问数据，而不需要进行全表扫描。 - 索引类型 - 唯一索引：表上一个字段或者多个字段的组合建立的索引，这些字段组合起来能够确定唯一，允许存在空值（只允许存在一条空值） - 非唯一索引：表上一个字段或者多个字段的组合建立的索引，可以重复，不需要唯一 - 主键索引：（主索引）根据主键pk_clolum（length）建立索引，不允许重复，不允许空值 - 聚合索引：表中记录的物理顺序与键值的索引顺序相同 - 非聚合索引：表中记录的物理顺序与键值的索引顺序无关 - 全文索引：在某个字段设置全文索引后，根据特定语法查找满足条件的字段; - 普通索引：用表中的普通列构建的索引，没有任何限制 - 组合索引：用多个列组合 构建的索引，但是在使用过程中有诸多规则，遵循最左前缀原则，顺序至关重要 - Hash索引（Memory存储引擎）是通过索引列的值计算出hashCode,之后在相应的物理位置存取索引列的值，由于hashCode的唯一性，因此Hash索引不能进行范围查找或者是顺序查找 - 索引优缺点 - 索引的优点： - 大大加快数据的检索速度 - 通过索引列对数据进行排序，降低数据排序的成本 - 在使用分组和联合查询时，可以显著减少查询中分组和排序的时间 - 索引的缺点： - 创建和维护索引需要耗费额外的存储空间 - 在进行增删改操作时，需要同时维护索引，会降低写操作的性能 - 索引过多会导致优化器在选择索引时需要耗费更多的时间 索引的底层实现通常采用B+树结构，它能够保持数据稳定有序，并且方便按一定范围快速查找。 - 索引创建 可以创建多个普通索引，多个唯一索引，多个候选索引，一个主索引 ```sql --MySQL创建索引 --主键索引 ALTER TABLE tableName ADD PRIMARY KEY(filedName); --唯一索引 CREATE UNIQUE INDEX indexName on tableName(fieldName[, fieldName ...]) --普通索引 CREATE INDEX indexName on tableName(fieldName[, fieldName ...]) ------------------------------------------------------------------------- --Oracle创建索引 --创建普通索引 CREATE INDEX PNN_GMA_APR_IDX_01 ON PNN_GMA_APR_T( PNN_PRC_NBR, PNN_APR_SID, PNN_APR_STA ) TABLESPACE TBS_CUR_IDX ONLINE; --创建唯一索引 CREATE UNIQUE INDEX PNN_GMA_APR_IDX_PK on PNN_GMA_APR_T( PNN_APR_PID ) GLOBAL TABLESPACE TBS_CUR_IDX ONLINE; ALTER TABLE PNN_GMA_APR_T ADD CONSTRAINT PNN_GMA_APR_IDX_PK PRIMARY KEY( PNN_APR_PID ) USING INDEX PNN_GMA_APR_IDX_PK ENABLE NOVALIDATE; ALTER TABLE PNN_GMA_APR_T MODIFY CONSTRAINT PNN_GMA_APR_IDX_PK ENABLE VALIDATE; --唯一索引创建解释 CREATE UNIQUE INDEX PNN_GMA_APR_IDX_PK on PNN_GMA_APR_T(PNN_APR_PID) GLOBAL TABLESPACE TBS_CUR_IDX ONLINE; 这条语句创建了一个名为PNN_GMA_APR_IDX_PK的唯一索引，该索引基于PNN_GMA_APR_T表的PNN_APR_PID字段。GLOBAL TABLESPACE TBS_CUR_IDX表示这个索引存储在全局表空间TBS_CUR_IDX中，ONLINE表示这个索引在创建后立即可用。 ALTER TABLE PNN_GMA_APR_T ADD CONSTRAINT PNN_GMA_APR_IDX_PK PRIMARY KEY(PNN_APR_PID) USING INDEX PNN_GMA_APR_IDX_PK ENABLE NOVALIDATE; 这条语句将PNN_GMA_APR_IDX_PK索引添加到PNN_GMA_APR_T表上，作为主键约束。ENABLE NOVALIDATE表示在添加约束时，不验证表中已有的数据是否满足这个约束。 ALTER TABLE PNN_GMA_APR_T MODIFY CONSTRAINT PNN_GMA_APR_IDX_PK ENABLE VALIDATE; 这条语句修改了PNN_GMA_APR_T表上的PNN_GMA_APR_IDX_PK主键约束，ENABLE VALIDATE表示在修改约束时，要验证表中已有的数据是否满足这个约束 ``` - 适合建索引的情形 - 每张表都需要创建主键索引 > 为什么？ > 底层索引树需要使用主键索引做聚簇，并且推荐使用自增ID做为主键 > 为什么是推荐自增ID做主键？ > 因为索引在底层实现里是有序的，如果是非自增ID，容易导致索引节点分裂重排。 > 主键不能是UUID吗？ > 理论上可以用UUID，但是UUID占用的字节长度为36，比8字节的bigint大了4倍，每个节点能存储的索引数量从1170降到390左右，三层B+树只能存储390*390*16=240万条，比使用bigint少了一个数量级，所以uuid做主键不仅仅占空间，还会让索引树变高，查询性能也会受影响，不推荐使用。 - 频繁作为查询条件的字段应该创建索引 - 查询中与其他表关联的字段，外键关系应当建立索引 - 频繁更新的字段不适合创建索引—更新不止需要更新记录还要更新索引 - 条件用不到的字段不创建索引 - 查询中排序的字段，尽量包含在索引当中 - 查询中统计或分组的字段，尽量包含在索引中 - 不适合建索引的情形 - 数据量太小的情况，有无索引区别不大 - 经常做增删改的表—建索引虽然提高了查询效率，但更新性能大大降低 - 数据大量重复且分布平均的字段，不适合当索引 - 指定索引的方式 - ORACLE 1. /*+ INDEX(表名， 索引名) */ 2. /**+ LEADING(小表) USE_NL(大表) INDEX(大表 大表的索引）*/ 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。 小表一般称为驱动表，可以是小表，也可以是小结果集；大表一般称为inner表，要求有索引。 3. /*+ USE_HASH(大表A , 大表B ) */ 工作方式是将一个大表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配 - MySQL USE INDEX - TDSQL 以上两种指定索引的方式都可以使用 - 导致索引失效的几种情况 - 没有where子句 - 使用IS NULL或IS NOT NULL - where子句中使用函数 - 使用like做左模糊查询（如果查询结果字段也在索引中的话，索引也是可以生效的） - where子句中使用不等于操作 - 等于和范围索引不会被合并使用 - 比较不匹配的数据类型，varchar类型会被隐式转换 - 索引不符合最左匹配原则 ## 索引的数据结构 ### MySQL - 所选数据结构 B+树，用多叉树尽量保证**磁盘I/O操作最少** - 非叶子节点只存储key和指针，不存数据，页号节点大小默认为16kb，能存储更多的索引项（可存储上千个），内存缓存的索引也更多，磁盘访问少 - 树矮，由于第一个特性，3层的树高即可存储两千多万数据，查找任意数据时最多仅需3次磁盘IO。 > 储存数据容量计算 > 假设主键是bigint占8字节，指针占6字节，那么单个非叶子节点可存储的索引数量是16*1024 / 14 ，假设每条数据记录占1kb，那单个叶子节点可存的记录数是16条，三层树高的B+树即可存1170*1170*16 = 2190万条； - 叶子节点使用双向链表串联，不用从根节点查找，范围查询时为顺序IO，速度比随机IO快。 - **为什么不选择其他数据结构** 1. 哈希表 等值查询O(1) 比B+树O(log n)块 - 不支持范围查询，只能全表扫 - 不支持排序，得查出全部结果再排序 - 存在hash冲突 2. 红黑树/AVL树 - 二叉树在数据量大时树高上涨快，同样的数据量树高比B+树多得多，磁盘IO增多查询很慢 3. B树 - 非叶子节点会存储全部数据，每个节点存储的数据量较少，导致相同数据量下也会有更大的层高，磁盘IO更多导致查询更慢 - 叶子节点间无关联，范围查询时需要回到上层节点再往下查找 ### ORACLE - 所选数据结构B*树 ## 执行计划 ### MySQL - explain SQL statement - 开启慢查询日志 `show variable like '%slow_query_log%; set global slow_query_log = 1` - 执行计划分析 | 字段 | 含义 | 重要说明 | | --- | --- | --- | | id | 查询标识符 | SQL执行顺序的标识，id值越大优先级越高，id相同则从上往下执行 | | select_type | 查询类型 | SIMPLE(简单查询), PRIMARY(主查询), SUBQUERY(子查询), DERIVED(派生表查询)UNION(联合查询),UNION RESULT(联合查询结果) | | table | 表名 | 显示这一行数据是关于哪张表的 | | type | 访问类型 | system > const > eq_ref > ref > range > index > ALL (从左到右性能降低) system:表只有一条数据 const:主键索引或唯一索引，只匹配一行数据 eq_ref:唯一性索引扫描 ref:非唯一性索引扫描 range:范围扫描 index:全索引扫描 all:全表扫描 | | possible_keys | 可能使用的索引 | 显示可能应用在这张表上的索引 | | key | 实际使用的索引 | 实际使用的索引，如果为NULL则没有使用索引 | | key_len | 索引使用的字节数 | 可能的最大长度，并非实际使用长度，越少越好 | | ref | 被使用的索引字段 | | | rows | 扫描行数 | 预计要读取的行数 | | filtered | 过滤百分比 | 表示符合查询条件的数据百分比 | | Extra | 额外信息 | 包含不适合在其他列中显示但很重要的额外信息 using filesort 说明使用外部索引排序，并不是使用表内索引排序 using temporary 使用临时表，性能损耗大 using index 使用覆盖索引，避免回表 using where 使用查询 using index condition 使用索引下推，减少回表 | ### Oracle 1. explain plan for SQL statement; 2. SELECT * FROM TABLE (dbms_xplan.display()); - 执行计划分析 | 类别 | 参数/列名/操作 | 说明 | 示例/备注 | | --- | --- | --- | --- | | **显示格式** | `BASIC` | 仅显示操作ID和名称 | `FORMAT=>'BASIC'` | | | `TYPICAL` | 默认格式，显示成本、谓词等 | 优化器标准输出 | | | `ALL` | 显示分区、别名、投影等完整信息 | 详细分析使用 | | | `ADVANCED` | ALL格式+OUTLINE数据 | 深度调试使用 | | **基本列** | Id | 操作序号，决定执行顺序 | 从0开始，子操作缩进显示 | | | Operation | 操作类型（如TABLE ACCESS） | 见下方操作类型详解 | | | Name | 对象名称（表/索引） | `EMPLOYEES`、`PK_EMP_ID` | | | Rows (E-Rows) | 预估返回行数（基数） | 对比A-Rows判断统计准确性 | | | Bytes | 预估返回字节数 | | | | Cost (%CPU) | 优化器估算成本值 | 成本越低越好 | | | Time | 预估执行时间 | | | | Pstart/Pstop | 分区访问起止编号 | 分区表范围扫描时使用 | | **扩展统计** | Starts | 操作执行次数 | `Starts>1`可能效率低 | | | A-Rows | 实际返回行数 | `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'))` | | | A-Time | 实际耗时（含子操作） | | | | Buffers | 逻辑读块数 | | | | Reads | 物理读块数 | | | | Writes | 物理写块数 | | | | OMem/1Mem | 最优/一次传递内存需求 | | | | Used-Mem | 实际PGA内存使用 | | | | Used-Tmp | 临时表空间使用量 | | | **计算公式** | 缓冲区命中率 | `(Buffers-Reads)/Buffers*100` | 理想值>95% | | | 基数准确率 | `A-Rows/E-Rows` | 接近1为准确 | | | 每执行行数 | `A-Rows/Starts` | 判断执行效率 | | **表访问** | TABLE ACCESS FULL | 全表扫描 | 适合大批量数据，小表可接受 | | | TABLE ACCESS BY INDEX ROWID | 索引回表 | 需搭配索引扫描 | | | TABLE ACCESS SAMPLE | 采样扫描 | 统计分析使用 | | **索引扫描** | INDEX UNIQUE SCAN | 唯一索引扫描 | 最高效，返回0-1行 | | | INDEX RANGE SCAN | 索引范围扫描 | 最常用，支持范围查询 | | | INDEX FULL SCAN | 索引全扫描 | 避免回表，覆盖索引 | | | INDEX FAST FULL SCAN | 索引快速全扫描 | 类似全表扫，读索引块 | | | INDEX SKIP SCAN | 跳跃扫描 | 复合索引前导列选择性低 | | **连接方式** | NESTED LOOPS | 嵌套循环连接 | 驱动表小，被驱动表有索引 | | | HASH JOIN | 哈希连接 | 大数据集，需内存支持 | | | SORT MERGE JOIN | 排序合并连接 | 已排序数据或需排序输出 | | | CARTESIAN JOIN | 笛卡尔积 | 应避免，检查连接条件 | | **谓词类型** | access | 访问谓词（定位数据） | 出现在索引扫描、连接操作 | | | filter | 过滤谓词（筛选数据） | 访问后过滤，消耗CPU | | **并行执行** | IN-OUT | 数据流方向 | `P->S`, `P->P`, `S->P` | | | PQ Distrib | 分布方式 | HASH, RANGE, BROADCAST | | | TQ | 表队列标识 | 并行进程间通信 | | | PCWC/PCWP | 并行合并操作 | 与子/父操作合并执行 | | **关键判断** | Starts>1且A-Rows小 | 可能存在低效操作 | 如NESTED LOOPS被驱动表重复扫描 | | | Buffers/A-Rows过大 | 逻辑读效率低 | 检查索引或过滤条件 | | | E-Rows与A-Rows差异大 | 统计信息过时 | 需收集统计信息 | | | 驱动表过大 | NESTED LOOPS性能差 | 考虑改为HASH JOIN | > 什么是覆盖索引？ > 是指二级索引中已经包含查询所需要的字段，直接从二级索引取数据，不用再回表查询。减少I/O操作，减少内存占用，查询更快 > 什么是回表？ > 回表是指使用二级索引查询时，因为二级索引中只存索引列的值和主键值，拿不到其他字段的数据，当需要查询其他字段时，就需要拿到主键再去聚簇索引中查询一遍数据行，这个过程就叫回表。 > 什么是索引下推？ > 把部分查询条件下推到存储引擎层，在引擎层就把不符合条件的数据过滤掉，减少回表查询 ## 锁机制 - 读锁/共享锁（S锁）阻塞写，不阻塞读 - 表加S锁 上锁的线程，不允许再对库的其他表进行操作，只允许读当前表；其他线程不允许对加锁的表进行更新，会线程等待。 - 手动添加共享锁 select * from tableName where id = 1 in share mode; - 写锁/排它锁（X锁）阻塞写和读 - 表加X锁 上锁的线程，可以对当前表进行操作；其他线程不允许对表操作，即使读也会线程等待 - 行加X锁 当前行只允许一个线程操作，其他线程被阻塞，允许读 - 间隙锁Next-Key锁 - 使用范围条件请求共享锁或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内并不存在的间隙记录，也会进行加锁 - 注意事项 - 索引失效会导致行锁升级为表锁 - 分析行锁 - show status like ‘innodb_row_lock%’; - innodb_row_lock_current_waits 当前等待锁定的数量 - innodb_row_lock_time 系统启动到现在锁定的总时间 - innodb_row_lock_time_avg 平均等待时间 - innodb_row_lock_time_max 最大等待时间 - innodb_row_lock_waits 等待总次数
04 MySQL面试八股看这一篇就够了——深度梳理MySQL面试问题
# 写在前面的话&内容总览 ## 写在前面的话 本文分部分对MySQL的高频考点和面试题进行了深度梳理，综合了面试鸭、八股网站和AI的答案整理而成，针对难以理解的地方，做了深度的梳理和资料整合，基本覆盖MySQL实习/校招面试95%以上的问题。 另外，本人已写 **Java后端完整版学习路线（仔细讲解每个技术栈怎么学，学到什么程度可以投实习/面试）+ 各大厂真实面试问题和参考答案 + Redis核心考点/面试真题深度梳理** 3篇高质量长文，如有需要欢迎大佬们自取（如果可以的话顺手点个赞👍），后续还会更新RabbitMQ、SSM等其他部分的面试高频八股梳理，欢迎关注。 [01 非科班转码拿下大厂Offer，花费一天整理的Java后端完整版学习路线](https://www.codefather.cn/post/2005544965425446914) [02 盘点2025遇到的各大厂面试真题总结](https://www.codefather.cn/post/2005573237194469377) [03 一文吃透 Redis 核心考点，面试真题深度梳理](https://www.codefather.cn/post/2006203053207732225) 下面的面试问题答案参考来源： [面试鸭](https://www.mianshiya.com/) [****](https://xiaolincoding.com/interview/java.html) 豆包 DeepSeek ## 内容速读导览（借助NoteBookLM生成） **思维导图：**  **信息图：**  **核心内容概括：** > 这份参考资料是针对 MySQL 数据库核心知识与面试高频问题 的全面总结，涵盖了基础数据类型、存储引擎及高级架构设计。内容重点对比了 CHAR 与 VARCHAR、DATETIME 与 TIMESTAMP 等字段类型的应用差异，并深入解析了 InnoDB 与 MyISAM 在事务支持及锁机制上的本质区别。文中详细阐述了 B+ 树索引结构 的优势、MVCC 并发控制 的原理以及 ACID 事务特性 的实现。针对性能优化，资料提供了 EXPLAIN 执行计划分析、慢查询治理及深度分页的具体解决方案。此外，文章还讨论了分库分表、主从复制以及雪花算法等分布式场景下的架构实践。最后，作者通过对 Redo Log、Binlog 和 Undo Log 的协同工作机制进行剖析，揭示了数据库保障数据持久性与崩溃恢复的核心逻辑。 # MySQL基础部分 ## 1.整数类型的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值可用 ## 2.CHAR和VARCHAR的区别是什么？ CHAR和VARCHAR是最常用到的字符串类型，两者的主要区别在于：**CHAR是定长字符串，VARCHAR是变长字符串。** CHAR在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR在存储时需要使用1或2个额外字节记录字符串的长度，检索时不需要处理。 CHAR更适合存储长度较短或者长度都差不多的字符串，例如Bcrypt算法、MD5算法加密后的密码、身份证号码。VARCHAR类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。 CHAR(M)和VARCHAR(M)的M都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。 ## 3.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这个长度来进行的，也就会消耗更多内存 ### 类似问题：INT(1)和INT(10)在MySQL中有什么不同？  ## 4.DECIMAL和FLOAT/DOUBLE的区别是什么？ DECIMAL和FLOAT的区别是：**DECIMAL是定点数，FLOAT/DOUBLE是浮点数。DECIMAL可以存储精确的小数值，FLOAT/DOUBLE只能存储近似的小数值。** DECIMAL用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。 在Java中，MySQL的DECIMAL类型对应的是Java类java.math.BigDecimal。 ## 5.为什么不推荐使用TEXT和BLOB？  ## 6.DATETIME和TIMESTAMP的区别是什么？ DATETIME类型没有时区信息，TIMESTAMP和时区有关。 TIMESTAMP只需要使用4个字节的存储空间，但是DATETIME需要耗费8个字节的存储空间。但是，这样同样造成了一个问题，Timestamp表示的时间范围更小。 * DATETIME：'1000-01-01'到'9999-12-31' * Timestamp：'1970-01-01'UTC到'2038-01 'UTC DATETIME类型存储的是**字面量的日期和时间值**，它本身**不包含任何时区信息**。当你插入一个DATETIME值时，MySQL存储的就是你提供的那个确切的时间，不会进行任何时区转换。 **这样就会有什么问题呢**？如果你的应用需要支持多个时区，或者服务器、客户端的时区可能发生变化，那么使用DATETIME时，应用程序需要自行处理时区的转换和解释。如果处理不当（例如，假设所有存储的时间都属于同一个时区，但实际环境变化了），可能会导致时间显示或计算上的混乱。 **TIMESTAMP和时区有关**。存储时，MySQL会将当前会话时区下的时间值转换成UTC（协调世界时）进行内部存储。当查询TIMESTAMP字段时，MySQL又会将存储的UTC时间转换回当前会话所设置的时区来显示。 这意味着，对于同一条记录的TIMESTAMP字段，在不同的会话时区设置下查询，可能会看到不同的本地时间表示，但它们都对应着同一个绝对时间点（UTC时间）。这对于需要全球化、多时区支持的应用来说非常有用。 ## 7.NULL和 ''的区别是什么？ **含义**: NULL代表一个不确定的值，它不等于任何值，包括它自身。因此，SELECT NULL = NULL的结果是NULL，而不是true或false。NULL意味着缺失或未知的信息。虽然NULL不等于任何值，但在某些操作中，数据库系统会将NULL值视为相同的类别进行处理，例如：DISTINCT,GROUP BY,ORDER BY。需要注意的是，这些操作将NULL值视为相同的类别进行处理，并不意味着NULL值之间是相等的。它们只是在特定操作中被特殊处理，以保证结果的正确性和一致性。这种处理方式是为了方便数据操作，而不是改变了NULL的语义。 '' 表示一个空字符串，它是一个已知的值。 **存储空间**: NULL的存储空间占用取决于数据库的实现，通常需要一些空间来标记该值为空。 '' 的存储空间占用通常较小，因为它只存储一个空字符串的标志，不需要存储实际的字符。 **比较运算**: 任何值与NULL进行比较（例如 =, !=, >, <等）的结果都是NULL，表示结果不确定。要判断一个值是否为NULL，必须使用IS NULL或 IS NOT NULL。 '' 可以像其他字符串一样进行比较运算。例如，'' = ''的结果是true。 **聚合函数**: 大多数聚合函数（例如 SUM, AVG, MIN, MAX）会忽略NULL值。 COUNT(*) 会统计所有行数，包括包含NULL值的行。COUNT(列名)会统计指定列中非NULL值的行数。 空字符串 ''会被聚合函数计算在内。例如，SUM会将其视为0，MIN和MAX 会将其视为一个空字符串。 ## 8.Boolean类型如何表示？ MySQL中没有专门的布尔类型，而是用TINYINT(1)类型来表示布尔值。TINYINT(1)类型可以存储0或1，分别对应false或true ## 9.手机号存储用INT还是VARCHAR？ 存储手机号，**强烈推荐使用VARCHAR类型**，而不是INT或BIGINT。主要原因如下： **格式兼容性与完整性：** 手机号可能包含前导零（如某些地区的固话区号）、国家代码前缀（'+'），甚至可能带有分隔符（'-'或空格）。INT或BIGINT这种数字类型会自动丢失这些重要的格式信息（比如前导零会被去掉，'+'和'-'无法存储）。 VARCHAR可以原样存储各种格式的号码，无论是国内的11位手机号，还是带有国家代码的国际号码，都能完美兼容。 非算术性：手机号虽然看起来是数字，但我们从不对它进行数学运算（比如求和、平均值）。它本质上是一个标识符，更像是一个字符串。用VARCHAR更符合其数据性质。 **查询灵活性：** 业务中常常需要根据号段（前缀）进行查询，例如查找所有"138"开头的用户。使用VARCHAR类型配合LIKE'138%'这样的SQL查询既直观又高效。 如果使用数字类型，进行类似的前缀匹配通常需要复杂的函数转换（如CAST或SUBSTRING），或者使用范围查询（如WHERE phone>=13800000000 AND phone<13900000000），这不仅写法繁琐，而且可能无法有效利用索引，导致性能下降。 **加密存储的要求（非常关键）：** 出于数据安全和隐私合规的要求，手机号这类敏感个人信息通常必须加密存储在数据库中。 加密后的数据（密文）是一长串字符串（通常由字母、数字、符号组成，或经过Base64/Hex编码），INT或BIGINT类型根本无法存储这种密文。只有VARCHAR、TEXT或BLOB等类型可以。 **关于VARCHAR长度的选择：** **如果不加密存储（强烈不推荐！）**：考虑到国际号码和可能的格式符，VARCHAR(20)到VARCHAR(32)通常是一个比较安全的范围，足以覆盖全球绝大多数手机号格式。VARCHAR(15)可能对某些带国家码和格式符的号码来说不够用。 **如果进行加密存储（推荐的标准做法）**：长度必须根据所选加密算法产生的密文最大长度，以及可能的编码方式（如Base64会使长度增加约1/3）来精确计算和设定。通常会需要更长的VARCHAR长度，例如VARCHAR(128),VARCHAR(256)甚至更长。 ## 10.MYSQL数据库的ACID **原子性**（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用； **一致性**（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的； **隔离性**（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间是独立的； **持久性**（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。 ## 11.视图 ### 为什么使用视图？ 视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。 ### 视图的理解 * 视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间。 * 视图建立在已有表的基础上，视图赖以建立的这些表称为基表。 * 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。 * 向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句。 * 视图是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。 ### 视图的优点  ### 视图的不足 如果我们在实际数据表的基础上创建了视图，实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建视图的SQL查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。 实际项目中，如果视图过多，会导致数据库维护成本的问题。 所以，在创建视图的时候，要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。 ## 12.存储过程 简单来说存储过程就像是封装好的函数，我们把一组经过预先编译的SQL语句封装成存储过程，我们想要使用的时候可以直接调用存储过程名来执行这些SQL语句。 ### 存储过程的优缺点 优点：  缺点：  ## 13.变量、流程控制与游标 ### 变量 在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。 在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。 系统变量属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件中的参数值。 系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。**如果不写，默认会话级别**。静态变量（在MySQL服务实例运行期间它们的值不能使用set动态修改）属于特殊的全局系统变量。 用户变量是用户自己定义的，根据作用范围不同，又分为会话用户变量和局部变量。会话用户变量：作用域和会话变量一样，只对当前连接会话有效。局部变量：只在BEGIN和END语句块中有效。局部变量只能在存储过程和函数中使用。 ### 流程控制 解决复杂问题不可能通过一个SQL语句完成，我们需要执行多个SQL操作。流程控制语句的作用就是控制存储过程中SQL语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程就分为三大类： * 顺序结构：程序从上往下依次执行 * 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行 * 循环结构：程序满足一定条件下，重复执行一组语句 针对于MySQL的流程控制语句主要有3类。注意：只能用于存储程序。 * 条件判断语句：IF 语句和 CASE 语句 * 循环语句：LOOP、WHILE 和 REPEAT 语句 * 跳转语句：ITERATE 和 LEAVE 语句 ### 游标 虽然我们也可以通过筛选条件WHERE和HAVING，或者是限定返回记录的关键字LIMIT返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。 这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让SQL这种面向集合的语言有了面向过程开发的能力。 在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。 MySQL中游标可以在存储过程和函数中使用。 使用流程：声明游标，打开游标，使用游标（从游标中获取数据），关闭游标 ## 14.触发器  触发器的优缺点 优点： 可以保证数据的完整性，举例，比如一张进货表，一张存货表，当我们进货表发生变动，进货表里面的进货数量就会发生变动，我们可以通过触发器规定当进货表有数据变动的时候同步去修改存货表里面的货物数目。 还有审计和日志记录的功能，比如我们修改商品的成本和售价，可能会因为粗心导致金额设置错误，比如售价低于成本，我们就可以通过触发器先进行校验。 日志的话就是我们可以每一次修改数据都触发一次日志记录的操作。 缺点： 调试困难：触发器执行是隐式的，错误可能难以追踪 性能影响：每个DML操作都会触发额外处理，增加开销，复杂触发器可能导致锁争用和事务时间延长；批量操作可能因触发器而变慢 可移植性问题：触发器语法在不同数据库系统中差异较大，迁移到其他数据库平台可能需要重写 事务管理问题：触发器中的失败会导致主操作回滚，可能意外创建长事务，影响系统整体性能 # MySQL高级知识（面试题） ## 1 SQL查询语句的执行顺序是怎么样的？  注：步骤6是对5中的列做去重处理，在这里没有去重所以没有6 ## 2 如何使用MySQL实现一个可重入的锁？   ## 3 执行一条SQL请求的过程是什么？  **1.连接阶段** 应用程序与MySQL服务器建立连接，MySQL验证用户名、密码和权限 **2.查询缓存** 查询语句如果命中查询缓存则直接返回，否则继续往下执行，MySQL 8.0已删除该模块。 **3.解析SQL** **解析器**会对SQL进行**词法分析**（将SQL语句分解为标记（tokens））和**语法分析**（构建语法树，方便后续模块读取表名、字段、语句类型） **4.执行SQL** **（1）预处理器**：检查表和列是否存在；将select _的_展开为所有列名；将视图转换为基表查询 **（2）查询优化器**：基于成本评估不同执行计划，**决定使用哪些索引**，**确定表连接顺序**，**重写查询以提高效率**，生成查询成本最低的执行计划 **（3）执行阶段：执行计划被传递给存储引擎**，**存储引擎（如InnoDB）通过索引或全表扫描定位数据**，**从磁盘或缓冲池读取数据**；构建结果集并发送回客户端； 如果启用了查询缓存，结果还会被缓存；连接可能被关闭或保持为持久连接 ### MySQL连接池作用 MySQL连接池核心作用是通过复用数据库连接、优化连接资源分配，解决直接频繁创建/关闭连接带来的性能问题，具体作用如下： **1.减少连接建立与关闭的开销** 建立MySQL连接需要经过TCP握手、权限验证（用户名/密码校验）、会话初始化等步骤，这些操作耗时且消耗网络和数据库资源。连接池会**预先创建一定数量的连接并维护**，当应用需要访问数据库时，直接从池内获取已建立的连接；使用完毕后，将连接归还给池（而非关闭）。这避免了频繁创建/关闭连接的重复开销，显著减少单次数据库操作的响应时间。 **2.控制并发连接数，保护数据库** MySQL服务器能支持的最大连接数是有限的（由max_connections参数限制）。若不限制连接数，高并发场景下可能出现“连接数爆炸”，导致数据库因资源耗尽（如内存、线程）无法响应新请求，甚至崩溃。连接池可通过配置最大连接数（如maxPoolSize）限制并发连接总量，确保数据库连接数始终在安全范围内，避免过载。 **3.资源复用，提高系统利用率** 连接是稀缺资源（每个连接对应数据库的一个线程和内存开销）。若每次操作都创建新连接，会导致大量资源被短暂占用后释放，造成浪费。连接池通过复用已有连接，减少了对CPU、内存、网络端口等系统资源的消耗，提高整体资源利用率。 **4.统一管理连接，确保可用性** 连接池通常内置连接健康检查、超时控制等机制： * 对长时间未使用的连接（空闲超时）进行回收，避免资源闲置； * 定期检测连接有效性（如执行ping命令），确保从池内获取的连接是可用的，减少因连接失效导致的业务错误； * 支持连接超时等待（当池内无可用连接时，请求可等待一定时间再获取），平衡并发压力。 **5.提升系统吞吐量与稳定性** 在高并发场景（如Web应用）中，连接池通过快速分配连接、减少阻塞，让更多请求能高效执行数据库操作，直接提升系统的吞吐量；同时，通过避免数据库过载和连接泄露，增强系统的稳定性。 ## 4 MySQL的存储引擎  ## 5 InnoDB和MyISAM引擎的区别？ **事务支持**：InnoDB支持事务，而MyISAM不支持事务，这也是MySQL选择InnoDB作为默认存储引擎的原因； **锁粒度**：InnoDB支持行级锁，而MyISAM最小的锁粒度是表级锁，一个增删改操作会锁住整张表，导致其他查询和更新都被阻塞，会影响并发性能； **崩溃恢复**：InnoDB可以通过redolog日志实现崩溃恢复，可以在数据库发生异常的时候（如断电），通过日志文件进行恢复，保证了数据的持久性和一致性，而MyISAM是不支持的； **索引结构**：InnoDB是聚簇索引，而MyISAM是非聚簇索引； **使用场景：** lnnoDB具体适用场景： 1、事务处理系统：如银行转账、电商订单处理等场景，利用InnoDB的事务处理能力保证数据的一致性和完整性； 2、高并发读写应用：如在线订票系统、社交媒体平台等，利用InnoDB的行级锁机制能有效减少锁冲突，提高并发处理能力； MylSAM具体适用场景： 1、读密集型应用：新闻网站，博客系统等，这类应用通常以读取数据为主，MylSAM表级锁在并发读操作时不会产生过多锁冲突，读取速度相对较快； 2、数据仓库和数据分析系统：在数仓中，数据通常是批量加载和更新的，且分析过程中主要是进行大量的读操作，MyISAM的全文索引功能在处理文本数据的搜索和分析时具有一定优势，能提高查询效率 3、嵌入式系统和移动应用：由于MylSAM相对简单，占用资源较少，在一些资源受限的嵌入式系统和移动应用中，如果对事务处理和并发控制求不高，MylSAM可以作为一种轻量级的数据存储方案。  ### 相关问题：为什么MyISAM读比InnoDB快?  ## 6 聚簇索引和非聚簇索引的区别？   7 三层B+树能存多少数据？ --------------  # 索引部分 ## 8 索引是什么，有什么好处？ --------------  ## 9 索引的分类？ --------       ### 相关问题0：什么是哈希索引？什么是全文索引？    ### 相关问题1：没有创建主键怎么办？  ### 相关问题2：什么是回表？  ## 10 联合索引（底层存储、最左匹配原则、失效判断） -------------------------    ### 具体联合索引的案例分析：    注：索引下推的字段一定是在联合索引中包含的字段 ## 11、MySQL分布式主键 ------------- ### （1）什么字段适合作为主键  ### （2）在分布式环境中，为什么不推荐使用自增长主键？ 首先，自增长主键依赖于数据库的自增序列，在高并发写入场景下会成为性能瓶颈。所有插入操作都需要获取下一个ID值，这在分布式系统中会导致严重的锁竞争。 其次，在分库分表场景下，每个分片都有自己的自增序列，无法保证全局唯一。数据迁移困难：当需要合并来自不同数据库的数据时，自增ID会产生大量冲突，需要额外处理。 安全问题：自增ID会暴露数据量和增长趋势，存在信息泄露风险。 ### （3）UUID可以用来在做主键吗？有什么问题？ 全局唯一性：理论上重复概率极低 安全性：相比自增ID，不会暴露数据量信息 但是也存在问题： UUID如果以二进制存储，占用16字节或以字符串形式存储就占36字节，占用的存储空间大，这在大数据量的表中会对存储和索引的性能产生一定影响。 更重要的一点是，数据是由UUID构建的B+树存储的，树的内部储存的数据是已经排好序的，但是新数据的UUID是随机生成的无序的，所以索引可能会定位到一个已经满了的页（默认一个页16KB大小），这种情况下就会发生页分裂，大量随机的插入就整个容易导致整个树索引的频繁裂变，增加了IO开销，从而降低了查询和写入的性能。 ### （4）雪花算法可以做主键吗？雪花算法原理，优缺点，怎么解决缺点？ 可以。 雪花算法64位二进制数（8字节），第一位符号位，始终为0，保证是正整数；前41位是时间戳，精确到毫秒（大约能用69年，因为默认是从1970年开始计数，所以我们一般使用会做一点小调整，用现在时间减去系统上线的时间）；中间10位是机器标识，确保分布式环境下生成的主键不重复；后面12位是序列号，用户区分同一毫秒内的多个请求ID（如果达到上限系统会等待下一毫秒），保证了能够应对高并发场景。因此雪花算法是全局唯一的，时间戳的设计保证了生成的ID是按照时间递增的，所以大致有序。 缺点：如果说回拨服务器的系统时钟，可能会导致生成重复ID，因此要保证时钟同步（网络时间校准；加容错机制，比如检测到时钟回拨可以采取等待、回退序列号等策略避免ID冲突；留出备用的时钟序列位，比如把机器码减少到7位，留下3位给序列号容错）；另外，在极端高并发的情况下，可能会有序列号溢出的风险（12位，2^11次方，4096个ID，方案等待下一毫秒）。 ## 12 性别字段适合加索引吗？ --------------  ## 13 MySQL中索引是怎么实现的？ ------------------   ## 14 B+树的特性？ ----------  ## 15 B+树和B树的区别？ -------------  ## 16 为什么选择B+树作为索引结构？ ------------------ 如果要设计一个方便查询数据的数据结构，首先会想到链表，但是链表每次查询都需要遍历整个链表； 所以又想到了二叉树，但是普通的二叉树也有问题，当我们顺序插入时会发现，二叉树退化成了链表，这主要就是因为这棵树并不是平衡的； 所以我们又想到了平衡的红黑树，但是红黑树也有问题，针对大数据量，红黑树会非常深，查询效率依然很低； 这个时候我们就想到了B树，也就是把二叉树最多两个子节点扩展从多个子节点，但是B树还是有一些问题，B树是每个节点既存储键也存储键对应的值，导致单个节点能够容纳的键数量有限，并且叶子节点之间彼此独立，范围查询的时候需要回溯到父节点； 而B+树针对这些问题进一步改进，非叶子节点只用来存储键，数据全部存储在叶子节点，进一步降低了树高，使得存储更紧凑；并且叶子节点用双向链表串联，范围查询只需要遍历链表，而无需从根节点重新查找，另外B+树还对键进行了冗余，在叶子节点也存储键，简化了分裂和合并操作。 简化了分裂和合并操作： 在B树中如果要进行分裂：需要从原节点中选出一个中间键，提升到父节点去，需要计算中间键的位置做调整，但是B+树可以直接复制要分裂的这个叶子节点的键，并且也不需要调整父节点的其他键。 哈希结构虽然等值查询的复杂度O(1)，但是哈希不适合做范围查询。 ### 相关问题：为什么MySQL不用跳表结构？  ## 17 如何提升查询效率？ ------------ 依赖覆盖索引和索引下推技术，减少回表次数 ### 覆盖索引： 通过创建组合索引，需要查询的字段都在这个组合索引里  ### 索引下推：  索引下推的核心思想是：将原本在Server层进行的部分数据过滤操作，“下推”到存储引擎的索引层面来完成。 这样做的最大好处是减少了存储引擎必须回表查询的次数和返回给Server层的数据行数，从而显著提升查询性能，尤其是对于包含模糊查询或范围查询的复合索引场景。 一个具体的例子：  ## 18 如果一个列既是单列索引，又是联合索引，查询会走哪个？ ----------------------------- <!-- 这是一张图片，ocr 内容为： -->  ## 19 MySQL中数据排序是怎么实现的？ --------------------  双路和单路排序：  超过双路，没超过单路 （**双路排序工作原理**： 1. 第一次**读取排序字段和行指针**（rowid） 2. 根据排序条件对排序字段进行排序 3. 第二次根据排序后的行指针**回表读取完整记录** **单路排序工作原理：** 1. **一次性读取所有需要的列**（包括排序字段和查询字段） 2. 在内存中完成排序 3. 直接返回结果，**无需回表** ） 相比双路排序，单路减少了回表的操作，效率更高。  ## 20 怎么决定建立哪些索引？ --------------  ## 21 索引失效的场景？怎么排查？ ----------------    ## 22 常见的索引优化方法有哪些？ ----------------  # 事务部分 ## 23 事务的特性是什么？如何实现？ -----------------  ## 24 MySQL可能出现什么和并发相关的问题？ -----------------------  ## 25 MySQL是怎么解决并发问题的？ -------------------  ## 26 事务的隔离级别以及实现方式？ ----------------- 事务的隔离级别：mysql默认是可重复读   ## 27 可重复读是如何避免不可重复读的，以及为什么仍然有幻读问题？ -------------------------------- 解决不可重复读：  幻读问题：   ## 28 什么是**MVCC**？ --------------- MVCC是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC会为该事务创建一个数据快照，而不是直接修改实际的数据行。 **1、读操作（SELECT）：** 当一个事务执行读操作时，它会使用快照读取。快照读取是基于事务开始时数据库中的状态创建的，因此事务不会读取其他事务尚未提交的修改。具体工作情况如下： * 对于读取操作，事务会查找符合条件的数据行，并选择符合其事务开始时间的数据版本进行读取。 * 如果某个数据行有多个版本，事务会选择不晚于其开始时间的最新版本，确保事务只读取在它开始之前已经存在的数据。 * 事务读取的是快照数据，因此其他并发事务对数据行的修改不会影响当前事务的读取操作。 **2、写操作（INSERT、UPDATE、DELETE）：** 当一个事务执行写操作时，它会生成一个新的数据版本，并将修改后的数据写入数据库。具体工作情况如下： * 对于写操作，事务会为要修改的数据行创建一个新的版本，并将修改后的数据写入新版本。 * 新版本的数据会带有当前事务的版本号，以便其他事务能够正确读取相应版本的数据。 * 原始版本的数据仍然存在，供其他事务使用快照读取，这保证了其他事务不受当前事务的写操作影响。 **3、事务提交和回滚：** * 当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。 * 当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。 **4、版本的回收：** 为了防止数据库中的版本无限增长，MVCC 会定期进行版本的回收。回收机制会删除已经不再需要的旧版本数据，从而释放空间。 MVCC通过创建数据的多个版本和使用快照读取来实现并发控制。读操作使用旧版本数据的快照，写操作创建新版本，并确保原始版本仍然可用。这样，不同的事务可以在一定程度上并发执行，而不会相互干扰，从而提高了数据库的并发性能和数据一致性 （实际并不是真的在数据库存了很多个版本的数据，而是通过undolog日志实现的，undolog日志会记录我们的反向操作，比如update一个记录，就会在日志里留下旧值，所以需要老版本时就顺着日志往回找；readview会记录哪个事务已经提交，哪个事务还在进行，来决定这个事务你能不能看到，自己改的一定能看，比你早提交的可以看，还未提交的不给看） ## 29 滥用事务，或者一个事务里有特别多sql的弊端（长事务）？ -------------------------------  # 锁部分 ## 30 MySQL锁机制，各种类型的锁详解 -------------------- MySQL的锁机制大致可以分为三个层次来理解： 1.锁的类型（共享/排他锁、意向锁） 2.锁的粒度（全局锁、表级锁、行级锁） 3.锁的实现（Record Lock, Gap Lock, Next-Key Lock） ### 1.锁的类型（按操作属性分）   ### 2.锁的粒度（按作用范围分）    ### 3.InnoDB行级锁的算法（实现方式）   ### 4. 隔离级别与锁的关系 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 锁的使用 | | --- | --- | --- | --- | --- | | 读未提交 | ✔ | ✔ | ✔ | 几乎不加锁 读已提交 | ✘ | ✔ | ✔ | 使用记录锁，但语句执行期间会释放不符合条件的记录的锁。没有间隙锁，所以可能发生幻读。 可重复读 | ✘ | ✘ | ✘ | 默认使用Next-Key Lock（记录锁+间隙锁），从而防止幻读。 串行化 | ✘ | ✘ | ✘ | 所有读操作自动转为SELECT ... FOR SHARE，强制加锁，并发度最低。 | ### 5、总结与实践建议 **引擎选择**：需要高并发写操作，务必选择**InnoDB**（支持行锁），避免使用MyISAM（只有表锁）。 **索引至关重要**：InnoDB的行锁是加在**索引**上的。**UPDATE/DELETE**的**WHERE**条件以及**SELECT ... FOR UPDATE**的条件必须用好索引，否则会**锁表**。 **控制事务大小**：尽快提交事务，让锁尽快释放。不要在事务内执行不必要的耗时操作（如网络调用、文件处理）。 **访问冲突**：理解**FOR UPDATE**（X锁，互斥）和**FOR SHARE**（S锁，共享）的使用场景。 **隔离级别**：默认的**RR**级别能满足大部分场景且避免了幻读。如果对一致性要求不是极端苛刻，但希望并发更高（减少间隙锁带来的死锁概率），可以考虑**RC**级别，但要接受幻读的可能性。 **死锁**：InnoDB能自动检测死锁并回滚其中一个代价最小的事务。应用程序需要能处理并重试因死锁而失败的事务。 ## 31 数据库的表锁和行锁分别有什么用？ -------------------  ## 32 关于几种加锁情况的分析 --------------  ## 33 一条update是不是原子性的，为什么？ -----------------------  ## 34 MySQL查询语句后面加for update是什么意思，加了什么锁？ ------------------------------------- SELECT FROM 表名 WHERE 条件 FOR UPDATE; 当这条语句执行时，MySQL会**锁住所有满足WHERE条件的行**；锁类型是**排它锁**，只对**InnoDB引擎**生效； 只有当前事务可以读取这些行，**其他事务不能更新、删除或再次加FOR UPDATE查询这些行**，直到当前事务提交或回滚。 **间隙锁（Gap Lock）** 为了防止幻读，InnoDB可能会对**间隙**也加锁； 特别是当查询条件使用范围查找（如>, <等）时，除了行锁，还会加**间隙锁**。 比如：SELECT FROM users WHERE age > 30 FOR UPDATE; ## 35 一致性非锁定读和锁定读 -------------- 对于**一致性非锁定读**（Consistent Nonlocking Reads）的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号+ 1或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见 在InnoDB存储引擎中，多版本控制 (multi versioning) 就是对非锁定读的实现。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读(snapshot read) 在Repeatable Read和Read Committed两个隔离级别下，如果是执行普通的select 语句（不包括select ... lock in share mode, select ... for update）则会使用一致性非锁定读（MVCC）。并且在Repeatable Read下MVCC实现了可重复读和防止部分幻读 **锁定读** 如果执行的是下列语句，就是**锁定读（Locking Reads）** * select ... lock in share mode * select ... for update * insert、update、delete操作 在锁定读下，读取的是数据的最新版本，这种读也被称为当前读（current read）。锁定读会对读取到的记录加锁： select ... lock in share mode：对记录加S锁，其它事务也可以加S锁，如果加x锁则会被阻塞； select ... for update、insert、update、delete：对记录加X锁，且其它事务不能加任何锁 # 日志部分 ## 36 MySQL日志包括哪几种？ ----------------   ## 37 binlog日志 -----------   MySQL主从复制通过主库记录数据变更并将binlog日志传输给从库，从库通过中继（relaylog）日志重放这些变更，从而实现数据同步。 复制过程中从库会启动两个线程： IO线程：与主库建立连接，持续抓取binlog日志并写入到中继日志中。 SQL线程：从中继日志中读取SQL语句并执行，将主库的变更应用到从库上。   解释：sysdate()和now()方法的不同是，now()返回的是sql语句开始执行时的时间，之后每次查询返回的结果都一样，但是sysdate()方法返回的是当前函数被调用的时间，所以两次调用返回的结果可能是不一致的。 由于SYSDATE()的非确定性行为（即每次调用可能返回不同结果），它在主从复制环境中可能会带来问题。如果主库上一个使用SYSDATE()的语句执行了很长时间，那么它在从库上重放时，SYSDATE()会取从库当前的时间，这可能导致主从数据不一致。 因此，MySQL提供了sysdate-is-now这个服务器系统变量。当这个变量被设置为ON时，SYSDATE()的行为会变得和NOW()一模一样，从而保证复制的安全性。 ### 相关问题：为什么只有binlog日志无法保证数据持久性？ **核心原因1：binlog的刷盘策略默认不保证“实时持久化”** 数据持久性的关键是“事务提交后，日志必须被强制刷到磁盘”（而非停留在内存/操作系统缓存）。但binlog的刷盘机制存在“延迟风险”，具体取决于sync_binlog参数：  可见： 仅当sync_binlog=1时，binlog才具备“事务级刷盘”能力；但**默认值是0**，此时binlog的持久化完全依赖操作系统，无法保证； 即使手动设置sync_binlog=1，仍有其他问题（见原因2），无法单独保证数据持久性。 **核心原因2：binlog无“崩溃恢复的完整性校验”（依赖两阶段提交）** 即使强行将sync_binlog=1（保证binlog刷盘），单独的binlog仍无法保证“数据与日志的一致性”——因为MySQL的事务提交是**两阶段提交（2PC）**，需要binlog与redo log协同才能确保崩溃后的数据完整： **两阶段提交的核心逻辑（InnoDB+binlog）：** **prepare 阶段**：InnoDB将事务的物理修改写入redo log，标记为“prepare状态”，并强制刷盘； **commit 阶段**：MySQL Server将事务的逻辑操作写入binlog，并强制刷盘（sync_binlog=1时）； 最后，InnoDB将redo log标记为“commit状态”，事务完成。 **若只有binlog（无redo log），会出现什么问题？** 假设事务执行到“binlog已刷盘，但数据页未刷盘”时宕机： * 重启后，MySQL只能看到binlog中的“逻辑操作”，但无法确认“该操作是否已应用到数据页”（因为没有redo log记录“数据页是否被修改”）； * 若重新执行binlog中的操作，可能导致“数据重复插入”（破坏一致性）；若不执行，又会导致“数据丢失”（破坏持久性）。 本质上，binlog缺乏“崩溃后校验操作是否已落地到数据页”的能力，必须依赖redo log的“prepare/commit状态”来判断——单独的binlog无法解决“日志与数据页的一致性校验”问题，自然无法保证持久性。 ## 38 Undolog日志 ------------   ## 39 redolog日志 ------------   ### 相关问题1：Redolog日志是怎么提高性能的？  ### 相关问题2：RedoLog怎么保证持久性的？  ### 相关问题3：只用binlog不用redolog可以吗？  ## 40 Binlog两阶段提交过程 ----------------   ## 41 为什么要写RedoLog，而不是直接写入B+树里？ ----------------------------  # 性能调优部分 ## 42、慢查询如何优化？ ----------- 当遇到慢SQL时，可以按这个清单排查： （1）开启慢日志：确认慢日志已开启，并设置合理的阈值（比如2s）。 （2）抓取慢SQL：可以使用MySQL自带的工具mysqldumpslow，可以汇总和排序慢查询日志（比如获取TOP 10） （3）EXPLAIN：在SQL语句前加EXPLAIN，查看数据库的执行计划。  **关于type类型：** **System**：这是最好的情况，表示表中只有一行数据（相当于系统表）。 出现场景：例如，查询一个只有一条数据的系统表，如： SELECT * FROM (SELECT * FROM t1 WHERE id = 1) AS derived_table;（如果子查询结果只有一行，外层查询的type可能是system）。 **Const**：性能极佳。表示通过索引一次就能找到唯一的一行记录。它用于比较**主键**或**唯一索引**的列与常数值。 出现场景：WHERE条件中使用主键或唯一索引的等值查询，如：SELECT * FROM users WHERE id = 10;（id是主键）。 因为索引保证了唯一性，MySQL最多只返回一行，所以速度非常快。 **eq_ref**:性能非常好。通常出现在多表连接（JOIN）查询中。对于前一个表的每一行，在当前表中只找到唯一的一行与之匹配。它使用主键或唯一索引进行关联。 出现场景：JOIN查询，其中驱动表（被连接的表）的关联字段是主键或唯一索引，如：SELECT * FROM users u JOIN orders o ON u.id = o.user_id;（假设o.user_id是orders表的主键或唯一索引）。 对于users表的每一行，MySQL在orders表中通过主键直接定位到一条记录，效率极高。 **Ref**：性能良好。使用非唯一性索引进行查找，或者只使用了唯一性索引的前缀部分。它可能返回多个符合条件的行。 出现场景：使用非唯一索引进行等值查询；使用唯一索引的“最左前缀”进行查询（即没有用到所有列），例如：SELECT * FROM users WHERE age = 30;（age字段上有一个普通索引）；SELECT * FROM users WHERE last_name = ‘Smith’;（有一个联合索引(last_name, first_name)，但只用了last_name。 **Range**：使用索引检索给定范围的行。这比全索引扫描要好，因为它只需要处理索引的某个范围。 出现场景：在索引列上使用BETWEEN、>、<、>=、<=、IN()、LIKE ‘prefix%’（前缀匹配）等操作，例如：SELECT * FROM users WHERE id BETWEEN 10 AND 20; SELECT * FROM users WHERE created_at > ‘2023-01-01’; 这是一个可以接受的性能类型，尤其是在需要范围查询时。 **Index**：全索引扫描，MySQL遍历整个索引树来获取数据。这比全表扫描好，因为索引文件通常比数据文件小。 出现场景：查询的列都包含在某个索引中（即覆盖索引）；需要对索引进行排序，而排序顺序与索引顺序一致。 **ALL**：全表扫描，这是最坏的情况，MySQL会读取整个表中的每一行来找到匹配的行。 出现场景：表没有建立索引；查询条件没有使用到索引；表数据量很小，MySQL优化器认为全表扫描比走索引更快（例如，表只有几行），例如：SELECT * FROM users WHERE name = ‘John’;（name字段上没有索引）。 **这是需要重点优化的信号！**对于大数据量的表，ALL类型会导致严重的性能问题。  （4）索引检查：是否缺少索引？索引是否失效？是否可以用覆盖索引？ （5）SQL写法检查：是否有SELECT *、深分页、函数操作字段等问题？ （6）表结构设计优化：选择合适的数据类型，可以进行适当的冗余字段设计，减少JOIN，以空间换时间；对冷数据进行归档，或者进行大表的分库分表。 （7）引入缓存，比如redis，存储热点数据和频繁查询的结果，降低数据库压力。  ## 43 如果explain用到的索引不正确的话，怎么干预？ ----------------------------  ## 44、深度分页问题？ ---------- 首先什么是深度分页问题：举个例子，比如我们要查询第1万页的10条数据，mysql会扫描10010条数据，然后返回后面10条，这就是深度分页。 针对深度分页查询可以采用：  **子查询：** 我们先查询出limit第一个参数对应的主键值，再根据这个主键值再去过滤并limit，这样效率会更快一些。 _# 通过子查询来获取 id 的起始值，把 limit 100000 的条件转移到子查询_ SELECT * ```SQL FROM t_order WHERE id >= (**SELECT** id FROM t_order where id > 1000000 **limit** 1) LIMIT 10; ``` 子查询(SELECT id FROM t_order where id > 1000000 limit 1)会利用主键索引快速定位到第1000001条记录，并返回其ID值。 主查询SELECT * FROM t_order WHERE id >= ... LIMIT 10将子查询返回的起始ID作为过滤条件，使用id >=获取从该ID开始的后续10条记录。 不过，子查询的结果会产生一张新表，会影响性能，应该尽量避免大量使用子查询。并且，这种方法只适用于ID是正序的。在复杂分页场景，往往需要通过过滤条件，筛选到符合条件的ID，此时的ID是离散且不连续的。   # 架构部分 ## 45 数据冷热分离？ ----------    ## 46 数据冷热库分库，如果冷库数据突然变热怎么办？ -------------------------      ## 47 分库分表 ------- ### 垂直分库分表 **核心思想：**“按业务模块拆分”或“按列拆分”。将一张宽表或者一个完整的数据库，拆分成多个不同的、更小的表或数据库。 它主要解决的是：**单库/单表因字段过多导致的业务耦合和性能问题**。 **1. 垂直分表** 基于**列**进行拆分。将一个包含很多字段的大表，根据访问频次和业务关联性，拆分成多个扩展表。 **常见做法：** **冷热分离**：将“常用字段（热数据）”和“不常用字段（冷数据）”分开。 **大字段分离**：将TEXT, BLOB等占用空间大的字段单独拆到一张扩展表中。 **例子**：一张t_user表有50个字段，其中id, name, email, phone是查询最频繁的，而personal_info, description等文本字段不常查询。 拆分后： t_user (核心表): id, name, email, phone, ... t_user_ext (扩展表): id, user_id, personal_info, description, ... **何时使用：** 表字段非常多，但每次查询只涉及其中一部分。 存在超长文本（TEXT/BLOB）字段，影响了核心表的查询和运维效率（如慢SQL、备份慢）。 **2.垂直分库** 基于**业务模块**进行拆分。将一个大而全的数据库，拆分成多个小而专的数据库。 **例子**：一个电商数据库shop_db包含了用户、商品、订单、支付等所有数据。 拆分后： user_db (用户库): 存放用户、会员等级等相关表。 product_db (商品库): 存放商品、品类、库存等相关表。 order_db (订单库): 存放订单、购物车等相关表。 pay_db (支付库): 存放支付、账户等相关表。 **何时使用：** 业务系统模块清晰，耦合度低。 应用程序本身已经按照微服务进行了拆分，数据库自然也需要跟随服务进行隔离。 希望不同的业务团队能独立拥有和管理自己的数据库，减少相互影响。 **垂直分库/分表的优点：** **解耦业务**：降低系统的复杂度和耦合度。 **提升性能**：减少单次I/O的数据量（垂直分表）、减少单库的连接数和处理压力（垂直分库）。 **便于维护**：不同团队可以专注于自己的数据库，升级、运维更灵活。 **垂直分库/分表的缺点：** **无法解决单表数据量过大的问题**。order_db里的订单表依然会无限增长。 **可能带来跨库Join的问题**。原本一个Join查询就能搞定的事，现在需要业务代码或中间件做多次查询和结果聚合，复杂度增加。 **分布式事务问题开始出现**。例如：下单操作需要同时操作order_db和pay_db，需要引入分布式事务方案。 ### 水平分库分表 **核心思想：**“按数据行拆分”。将同一个表中的数据，按照某种规则（分片键）分散到多个**结构相同**的数据库或表中。 它主要解决的是：**单库单表数据量过大、读写性能瓶颈**的问题。 **水平分表**：将一张表的数据分到**同一个数据库的多个表**中。例如：order_0,order_1, ...order_n。 **水平分库**：将一张表的数据分到**多个数据库的多个表**中。例如：db0.order_0, db1.order_1, ... dbn.order_n。（水平分库是水平分表的进阶，通常一起进行） **分片规则(Sharding Key)：** **哈希取模**：根据某个字段（如user_id）的哈希值对分片数量取模，决定数据落在哪个分片。**优点**：数据分布均匀。**缺点**：扩容时需要迁移大量数据（一致性哈希可以缓解， > _一致性哈希解释：_ > > _**1.核心思想：“环形哈希空间” + “就近路由”**_ > > _一致性哈希将整个哈希值空间映射为一个****环形结构****（称为“哈希环”），范围通常是0~2³² - 1（32位哈希值的取值范围，足够大且均匀），然后通过两步实现路由：_ > > _**步骤1：将“分表（存储节点）”映射到哈希环**_ > > _对每个分表的唯一标识（如表名t_user_0）计算哈希值（如MD5、SHA-1），并将结果映射到哈希环的某个位置，形成“节点位置”。_ > > _**步骤 2：将“数据” 映射到哈希环并路由**_ > > _对数据的唯一标识（如user_id）同样计算哈希值，映射到哈希环的“数据位置”；然后****顺时针遍历哈希环****，找到第一个比“数据位置”大的“节点位置”，该节点对应的分表就是数据的存储目标_ > > _**2.直观示例：3个分表的路由过程**_ > > _假设使用简单哈希函数，哈希环范围0~100，分表和数据的映射如下：_ > > _**分表映射到环**__：_ > > _Table1哈希值 → 20 → 环上位置20；_ > > _Table2哈希值 → 50 → 环上位置50；_ > > _Table3哈希值 → 80 → 环上位80；_ > > _**数据映射与路由**__：_ > > _数据A哈希值 → 40 → 顺时针找第一个节点：位置50（Table2）存到Table2；_ > > _数据B哈希值 → 60 → 顺时针找第一个节点：位置80（Table3）→ 存到Table3；_ > > _数据C哈希值 → 90 → 顺时针遍历到环末端后回到起点，找第一个节点：位置 20（Table1）→ 存到Table1。_ > >  > > _**三、一致性哈希的关键优势：解决扩缩容痛点**_ > > _传统取模方案的问题是“节点数量变化导致路由规则全变”，而一致性哈希的环形结构能大幅减少迁移量。_ > > _**示例：新增1个分表（扩容）**_ > > _原有3个分表，新增Table4，其哈希值→60（环上位置60）：_ > > _**新节点位置**__：60（介于table2（50）和table3（80）之间）；_ > > _**需迁移的数据**__：仅哈希值在50~60之间的数据现在需迁移到table；_ > > _**迁移量**__：仅占总数据的(60-50)/(100 = 10%，远低于传统取模的90%+。_ > > _）。_ **范围分片**：根据某个字段的范围进行分片，如按时间（月/年）、按id区间。**优点**：易于扩容。**缺点**：容易产生数据热点（最新的分片读写频繁）。 **地理分片**：根据用户所在地等地理位置信息分片。 **自定义规则**：根据业务特点自定义复杂规则。 **何时使用：** **单表数据量巨大**，预计未来会超过千万甚至亿级别，导致索引膨胀，查询性能下降。 **数据库的CPU、内存、I/O或连接数**即将达到瓶颈，且无法通过升级硬件解决。 需要解决**写操作的性能瓶颈**（因为读操作还可以通过读写分离缓解）。 **水平分库/分表的优点：** **根本性解决大数据量存储和性能问题**。 **大幅提升系统的扩展性和吞吐量**。 **水平分库/分表的缺点：** **复杂度急剧上升**： * **跨库Join**：几乎无法执行，需要在业务层模拟。 * **跨库聚合排序**：count(), order by, group by变得异常复杂。 * **全局主键ID**：不能再用数据库自增ID，需要分布式ID生成方案（雪花算法等）。 * **分片键的选择至关重要**，选错可能导致数据倾斜和热点。 * **运维复杂度高**：数据迁移、扩容、缩容都非常麻烦。 **总结与决策流程图** **核心原则：能不分则不分，优先选择简单的优化方案（如优化SQL、索引、读写分离、缓存），只有当这些手段都无法满足时，再考虑分库分表。** **如何选择？** | 维度 | 垂直分库/分表 | 水平分库/分表 | | --- | --- | --- | | 拆分角度 | 业务/字段维度 | 数据行维度 目标 | 解耦，专库专用 | 扩容，解决性能瓶颈 影响 | 表结构不同 | 表结构相同 适用场景 | 业务清晰，系统耦合度高；字段多且有冷热之分 | 单表数据量巨大，写操作频繁 | ## 48 MySQL主从复制（主从同步） ------------------  ### 相关问题：主从延迟的处理方法 强制走主库：对于大事务，或者资源密集型操作，直接在主库上执行，避免从库的额外延迟。 # 其他问题： ## 49 SQL查询，平时正常，但是偶发性的变慢SQL，可能是什么情况 **（1）数据量和统计信息的不稳定性（最常见）** 这是**最可能**的原因。MySQL的查询优化器依赖于对表的统计信息（如行数、索引分布、基数等）来决定使用哪个索引和执行计划。 这些统计信息不是实时更新的。当表中数据发生大量增删改（特别是UPDATE和DELETE）后，统计信息会变得过时。优化器基于过时的信息，可能选择了一个非最优的索引（甚至全表扫描），导致查询突然变慢。 **为什么是间歇性的**：UPDATE和DELETE操作积累到一定程度，或者触发了自动更新统计信息的阈值后，统计信息被更新，新的、可能更差的执行计划被生成，慢SQL就出现了。或者，当表数据量突破某个临界点时，原来的好计划突然就不好了。 之所以又可以自愈：  **排查方法**： * 在慢SQL出现时，使用EXPLAIN语句分析当前的执行计划。 * 与正常时的EXPLAIN结果进行对比，看是否使用了不同的索引（key字段）或访问方式（type字段）。 * 检查表的统计信息收集设置：SHOW CREATE TABLE your_table_name;查看STATS_PERSISTENT设置。 **（2）缓存失效** MySQL有多层缓存，缓存失效会导致查询需要重新处理。 **InnoDB缓冲池（Buffer Pool）**：这是最重要的缓存，缓存了表数据和索引。如果系统有重启、缓冲池大小设置不合理、或者突然有一个大型查询/报表任务扫走了大量缓存数据，那么你的查询所需的数据就可能被挤出缓存，导致必须从磁盘读取，速度急剧下降。 **查询缓存（Query Cache）**（注意：MySQL 8.0中已移除）：如果启用了QC，一旦表有任何修改，所有基于该表的查询缓存都会失效。后续的查询需要重新执行。如果你的表每隔一段时间才被更新一次，那么更新后的第一次查询就会变慢。 **排查方法**： * 监控缓冲池命中率：SHOW STATUS LIKE 'Innodb_buffer_pool_read%’;。理想情况下，Innodb_buffer_pool_read_requests（总请求数）远大于Innodb_buffer_pool_reads （从磁盘读取的次数），命中率应接近99%。 * 检查缓冲池大小：SHOW VARIABLES LIKE 'innodb_buffer_pool_size’;，确保它设置得合理（通常是系统内存的50%-70%）。 **（3）系统资源竞争** 数据库服务器本身可能正在经历周期性的资源瓶颈。 **周期性任务**：是否存在定时任务（如每日/每周报表生成、ETL作业、批量数据处理）？这些任务会消耗大量的CPU、内存和磁盘I/O资源，与你的常规查询争抢资源，导致其变慢。 **邻居吵闹**：如果数据库是部署在虚拟机或云服务器上，可能存在“邻居吵闹”问题。同一台物理机上的其他虚拟机在某段时间内资源使用激增，影响到了你的数据库性能。 **排查方法**： * 检查慢SQL发生时间点系统的监控指标：CPU使用率、内存使用率、磁盘I/O使用率（特别是await和util指标）、网络流量。看是否有明显的关联性。 * 检查MySQL的进程列表：在变慢时执行SHOW PROCESSLIST;，看看是否有其他大型查询正在运行。 **（4）锁竞争** 你的查询可能正在等待获取锁。 * **行锁/表锁**：你的应用程序可能在某些业务场景下（如定期结算、批量更新）持有锁的时间过长，导致其他查询被阻塞。虽然你的查询本身很快，但等待锁的时间被计入执行时间，从而成为“慢SQL”。 * **元数据锁（Metadata Lock）**：有一个长时间运行的事务（甚至是一个未提交的SELECT）或者正在执行ALTER TABLE等DDL操作，会阻塞其他需要获取元数据锁的查询。 **（5）应用程序层面的周期性模式** * **缓存穿透**：应用层的缓存（如Redis）可能定期失效。失效后，大量请求直接打到数据库，造成数据库瞬时压力过大，其中一些查询响应变慢。 * **特定时间的高流量**：例如，每周一的早上用户活跃度最高，并发请求数增加，数据库负载加重，导致个别查询性能下降。 **如何系统性地排查和解决？** **开启并监控慢查询日志** 1. 确保MySQL的慢查询日志（slow query log）是开启的，并设置一个合理的阈值（如long_query_time = 2秒）。 2. 使用工具（如mysqldumpslow, pt-query-digest）定期分析慢日志，找出TOP N的慢SQL及其出现的时间规律。 3. **在问题发生时现场排查** **第一步**：使用SHOW PROCESSLIST; 查看当前所有连接状态，有没有阻塞、有没有状态不正常的查询。 **第二步**：对变慢的SQL立即执行EXPLAIN，分析其执行计划。保存下来与正常时对比。 **第三步**：查看服务器资源状态（top, htop,iostat -x 1）和MySQL状态变量（SHOW GLOBAL STATUS LIKE 'Threads_running’; 查看并发数，SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%’; 查看行锁情况）。
2025-12-19 某某公司（实际是我忘了）线下面试
#### 早上还没睡醒，中电金信电话call叫床服务，纯八股开开胃，下午两点线下面试 1. 手写一个单例程序（我写了个枚举，不知道结果如何） 2. 编程：￥1101金额转中文表示（只写了思路） 3. web程序中，怎么输出指定编码的字符串 4. 什么是微服务，Spring Cloud 常用组件有哪些 5. 一级缓存二级缓存怎么定义的，一般怎么实现 6. error和Exception有什么区别 7. 什么是线程安全？怎么实现线程安全 8. synchronized底层原理 9. 什么是数据库事务？有什么特性 10. 数据库规范化是什么？三大范式？ 11. int 多少字节？数据范围怎么确定？ 12. Bean的生命周期；后置处理器了解吗；怎么控制Bean的初始化顺序 13. 反问环节 #### 继续在DY蹭线上模拟面试，兄弟们，下周还有三个面试 《我还是担心面试考算法，我理解，写的时候真记不起来😢》