MySQL
快来分享你的内容吧～
- 05-06 19:09·Java后端实现步骤： 1.引入依赖 2.配置并连接数据库 3.创表结构、实体类 4.插件生成代码 ，给启动类加上mapper扫描 5.自定义mysql对话存储 6.使用mysql对话存储 1: !image.png 注意：官方MP说要用4版本，但是经过我的测试，4不行 !image.png 2....... 3.表结构： 实体类： 4....... 6.注意： 因为@ Resource注解生效在构造方法之后查看全文加油鸭：太棒了！从建表到自定义 MySQL 对话存储，逻辑清晰、代码规范，还踩坑验证了 MP 版本兼容性，这份扎实的实践力真让人佩服！432分享
01-26 16:33·后端一篇文章带你认识10种数据库类型，后端程序员必看的数据库选型指南。知识点包括关系型数据库MySQL、PostgreSQL，非关系型数据库Redis、MongoDB 等等……查看全文加油鸭：这篇数据库科普太棒了！把10类数据库讲得生动又透彻，连小阿巴都秒懂，干货满满还充满趣味～635分享
01-19 22:28·Java后端
01-05 13:50·Java后端- 2025-12-02·后端
2025-11-26·Java后端- 2025-11-26·Java后端
- 2025-11-17·Java后端约束约束：作用在表中的字段上，保证字段的数据满足要求，用于保证数据的正确型、有效性、安全性。分类| 约束 | 作用 | 关键字|| --- | --- | --- || 主键 | 该值必须唯一、不重复、不能为 NULL | PRIMARY KEY || 外键 | 该值必须在指定表中存在 | FOREIGN KEY || 唯一 | 该值必须唯一、不重复 | UNIQUE || 非空 | 该值不能为空查看全文加油鸭：整理得非常清晰！SQL约束这块你讲得很系统，尤其是外键规则的分类很实用，对初学者特别有帮助。211分享
- 2025-11-17·Java后端
- 2025-11-09·Java后端
自定义MySQL对话存储
实现步骤： 1.引入依赖 2.配置并连接数据库 3.创表结构、实体类 4.插件生成代码 ，给启动类加上mapper扫描 **5.自定义mysql对话存储 ** 6.使用mysql对话存储 1:  注意：官方MP说要用4版本，但是经过我的测试，4不行  2....... 3.表结构： ```-- 创建mysql对话存储表 CREATE TABLE chat_memory_message ( id BIGINT PRIMARY KEY AUTO_INCREMENT, conversation_id VARCHAR(128) NOT NULL, message_index BIGINT NOT NULL, message_type VARCHAR(32) NOT NULL, message_text TEXT NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_conversation_id (conversation_id), INDEX idx_conversation_id_idx (conversation_id, message_index) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; ``` 实体类： ```/** * MySQL对话存储实体类 */ @Data @NoArgsConstructor @AllArgsConstructor @TableName("chat_memory_message") public class ChatMemoryMessage { @TableId(type = IdType.AUTO) private Long id; /** * 对话id */ private String conversationId; /** * 对话序号 */ private Long messageIndex; /** * 对话类型 */ private String messageType; /** * 具体文本 */ private String messageText; /** * 创建时间 */ private LocalDateTime createTime; } ``` 4....... 5. ```/** * 基于MySQL的对话存储(MP) */ @Component public class MySQLChatMemory implements ChatMemory { private final ChatMemoryMessageMapper mapper; public MySQLChatMemory(ChatMemoryMessageMapper mapper) { this.mapper = mapper; } @Override @Transactional public void add(String conversationId, List<Message> messages) { if(messages==null || messages.isEmpty()){ return; } //查询当前下一条对话的位置 Long nextIndex=getNextIndex(conversationId); //批量插入message for(Message message:messages){ ChatMemoryMessage entity = toEntity(conversationId, message, nextIndex++); mapper.insert(entity); } } @Override public List<Message> get(String conversationId, int lastN) { if(lastN<=0){ return List.of(); } //返回最后N个 List<ChatMemoryMessage> all = mapper.selectList( new LambdaQueryWrapper<ChatMemoryMessage>() .eq(ChatMemoryMessage::getConversationId, conversationId) .orderByAsc(ChatMemoryMessage::getMessageIndex) ); if (all == null || all.isEmpty()) { return List.of(); } return all.stream() .skip(Math.max(0, all.size() - lastN)) .map(this::toMessage) .toList(); } @Override public void clear(String conversationId) { mapper.delete( new LambdaQueryWrapper<ChatMemoryMessage>() .eq(ChatMemoryMessage::getConversationId, conversationId) ); } //计算下一条消息的序号 private Long getNextIndex(String conversationId) { //查询到最新一条消息 LambdaQueryWrapper<ChatMemoryMessage> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(ChatMemoryMessage::getConversationId, conversationId) .orderByDesc(ChatMemoryMessage::getMessageIndex) .last("LIMIT 1"); ChatMemoryMessage lastMessage = mapper.selectOne(queryWrapper); //返回下条消息的序号 return lastMessage == null ? 0L : lastMessage.getMessageIndex() + 1; } //Spring AI的message-->>ChatMemoryMessage private ChatMemoryMessage toEntity(String conservationId, Message message, long index) { ChatMemoryMessage chatMemoryMessage = new ChatMemoryMessage(); chatMemoryMessage.setConversationId(conservationId); chatMemoryMessage.setMessageIndex(index); chatMemoryMessage.setMessageType(message.getMessageType().getValue()); chatMemoryMessage.setMessageText(message.getText()); return chatMemoryMessage; } //ChatMemoeyMessage--->>Spring AI的message private Message toMessage(ChatMemoryMessage chatMemoryMessage){ MessageType messageType = MessageType.valueOf(chatMemoryMessage.getMessageType().toUpperCase()); //根据消息类型返回具体子类对象 return switch (messageType) { case SYSTEM -> new SystemMessage(chatMemoryMessage.getMessageText()); case USER -> new UserMessage(chatMemoryMessage.getMessageText()); case ASSISTANT -> new AssistantMessage(chatMemoryMessage.getMessageText()); default -> throw new IllegalArgumentException("未知的消息类型: " + chatMemoryMessage.getMessageType()); }; } } ``` 6.**注意：** 因为@ Resource注解生效在构造方法之后，而我们是用构造方法构建的客户端，所以我们不能用注解来注入MySQLChatMemory,我们只能在构造方法上加一个参数，然后调用。 ```@Component public class RockKindomApp { private final ChatClient chatClient; private final String SYSTEM_PROMPT = "XXXXXXXXXXXXXXXXXXXXX; public RockKindomApp(ChatModel dashscopeModel,MySQLChatMemory chatMemory) { //创建基于内存的记忆 // InMemoryChatMemory chatMemory = new InMemoryChatMemory(); //创建基于文件的记忆 // String fileDir = System.getProperty("user.dir") + "/chat-memory"; // FileBasedChatMemory chatMemory = new FileBasedChatMemory(fileDir); chatClient = ChatClient.builder(dashscopeModel) .defaultSystem(SYSTEM_PROMPT) .defaultAdvisors( new MessageChatMemoryAdvisor(chatMemory), new MyAdvisor() )//内存记忆顾问 .build(); } ```
除了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/)
MySQL八股汇总-我的近20天成果
# 第一部分 | 索引 ## 1. 什么是索引下推？ 1. 它是在5.6及之后版本才有的 2. 索引下推是指在server层将索引和过滤条件一块传给存储引擎层，存储引擎层通过索引找到数据后使用过滤条件过滤掉不符合条件的数据，减少了返回的数据量，也就减少了回表次数 ## 2. 索引的最左前缀原则是什么？ 1. 在使用联合索引时，必须从最左边的索引开始依次往后，否则就用不上联合索引，这就是最左匹配原则 2. 比如联合索引abc，a/ab/abc是生效的，而b/c/ac不生效，但对于ac，在Mysql5.6版本引入了索引下推，即将部分查询条件一块传到存储引擎层，由存储引擎层进行过滤，减少返回的数据量 3. 与顺序无关，比如acb等同于abc 4. 为什么会这样呢？因为Mysql的底层存储是先对a进行排序，在a相等的情况下再根据b排，以此类推。所以说如果前面缺少一个或者前面的使用不带等号的范围查询，得到的数据是无序的，也就无法使用后续的索引了，这也是索引失效场景之一 5. 其中Mysql8有一个特性，就是对于联合索引ab，查询条件只有b，在a的基数很小的情况下，Mysql会自动拼上a，从而使用联合索引。比如a的值只有1和2，在查询的时候会直接使用a=1 and b=?和a=2 and b=?进行查询（Using Index for skip scan） ## 3. 索引失效场景有哪些？ 1. 使用联合索引，但未遵循最左前缀原则 2. 索引使用了运算 3. 索引使用了函数 4. 对于联合索引，范围查询右边的索引会失效 5. 左模糊匹配 6. or左右两边有一边无索引 7. 字符串类型不加引号（隐式类型转换） 8. order by后面不是主键或覆盖索引，Mysql可能选择全表扫描再排序，不走索引 ## 4. 索引类型有哪些? 1. 按数据结构：B+树索引、哈希索引、倒排索引、R-树索引 2. 按索引性质：主键索引、唯一索引、普通索引、联合索引、全文索引、空间索引 3. 按物理存储：聚簇索引、非聚簇索引 ## 5. 聚簇索引与非聚簇索引的区别？ 1. 聚簇索引：一般是表的主键，叶子结点存储的是完整的行数据，一张表只能有一个 2. 非聚簇索引：叶子结点存储的是索引列和主键，一张表可以有多个，若查询的数据不在该索引列中，需要根据ID进行回表查询 ## 6. 建索引时应注意什么? 1. 不要在有大量重复数据的字段上建索引，比如性别字段（也有特例，比如定时任务成功状态远大于失败状态时可以考虑建索引） 2. 不要在长类型（text，longtext）字段上建索引，因为加载到内存上花的时间很长，其次数据量大，会把其他字段数据踢出内存，下次还要重新加载 3. 不要在频繁更新的字段上建索引，因为会减慢更新的效率 4. 如果查询sql中一个条件用的很频繁，那么可以考虑建索引，比如where后面的，如果是多条件，可建立联合索引，注意最左前缀原则就行 5. order by/group by/distinct后面字段可以建立索引，提高排序，分组，去重的效率 6. 最后明确一点，索引并不是越多越好，因为索引本身也会占据空间，并且增删改的时候除了对主键索引更新外，还会对其他有关索引进行更新，索引越多，花费时间越多 ## 7. Mysql为什么使用B+树作为索引结构？ 1. 从性能上来讲，B+树是一种自平衡的二叉树，2000w条数据大约才3层，增删查的时间复杂度为O(logn)，具有极短的响应时间，且查询时间更加均匀 2. 从数据存储上来讲，B+树的非叶子结点只存储索引列和指针，使得每个非叶子节点可存储的数据量很大，相应的树高增长也就不会很快，内存中可存放的数据个数很多，无需频繁的磁盘IO 3. 从查询方式上来讲，B+树相邻叶子结点之间使用双向链表连接，定位到起点后，顺序遍历即可，顺序IO效率远大于随机IO，范围查询效率大大增加 # 第二部分 | 事务 ## 1. Mysql的事务隔离级别有哪些？ 1. 读未提交：事务可以读取到另一个事物未提交的数据，会导致脏读、不可重复读、幻读 2. 读已提交：只能读取到已提交的数据，解决了脏读，但依然会出现不可重复读和幻读 3. 可重复读：Mysql默认的隔离级别，同一事务中多次读取同一数据的结果是一致的，解决了脏读、不可重复读，但会存在幻读 4. 串行化：最高的隔离级别，串行化操作，可避免所有的并发问题，但开销很大 ## 2. 脏读、不可重复读、幻读指的是什么？ 1. 脏读：一个事物读取到另一个事务未提交的数据，如果另一个未提交的数据最终回滚，那么这条数据就是脏的 2. 不可重复读：同一事务中多次读取同一数据，得到的结果不一致（针对内容） 3. 幻读：同一事务中多次执行相同的查询操作，前后得到的数据集合不一致（针对数量） ## 3. 事务的ACID指的是什么？（四大特性） 1. ACID分别对应原子性、一致性、隔离性、持久性 2. 原子性：一个事务内的操作要么全部成功，要么全部失败 3. 一致性：事务执行前后，必须保证数据是合法的（满足业务规则和完整性约束），在事务执行期间可以处于中间态 4. 隔离性：每个事务之间相互隔离，互不干扰，根据事务隔离级别的不同，可能会出现脏读、不可重复读、幻读的问题 5. 持久性：事务一但提交，数据变更一定是永久的，不会发生重启导致的数据丢失 ## 4. Mysql是如何实现事务的？ 1. 对于事务的原子性，通过undo log实现，undo log用于记录当前操作的反操作，以便于事务失败后进行回滚 2. 对于事务的隔离性，通过锁+mvcc实现 3. 对于事务的持久性，通过redo log实现，在服务宕机/重启后通过重放redo log进行数据的恢复 4. 最后对于事务的一致性，是通过原子性、隔离性、持久性共同保证的，以此达到一致性的目的 ## 5. Mysql事务的两阶段提交是什么？ 1. 分为准备阶段和提交阶段，用于保证redolog和binlog之间的一致性 2. 准备阶段：事务提交时，Mysql的InnoDB引擎会先写入redolog中，并将状态标记为prepare，此时redolog是预提交状态，还未真正提交 3. 提交阶段：当redolog的状态变为prepare后，Mysql的server层会将数据写入binlog，写入成功后会通知InnoDB，将redolog的状态标记为commit，redolog进行提交，至此二阶段提交结束 4. 好处：当redolog写完但还未提交（binlog可能写入，也可能没写入），重启后InnoDB会检查状态为parpare的日志，拿到他的XID，去binlog中找，如果找到，说明binlog已经写入，直接提交，否则回滚 5. 那么问题来了，为什么不是先写binlog后写redolog做两阶段提交呢？因为binlog没有事务状态标记，也不参与崩溃后恢复；而redolog可以标记事务状态并作为恢复依据 ## 6. 长事务可能会导致哪些问题？ 1. 长事务会长期持有行锁/间隙锁，导致其他事务阻塞，线程堆积，连接池耗尽，从而会引发应用层面的雪崩或不可用 2. 死锁风险大大增加，事务执行时间越长，和其他事务产生循环等待的概率就越大 3. undolog膨胀，多个长事务一直不提交，这个版本链就不能被清理，导致文件体积变大 4. 主从延迟，长事务在从库执行时，可能会在执行期间进行数据的查询，那么这期间数据是不一致的 5. 回滚代价大，长事务执行了好长时间，最终被回滚了，就白干了，而且回滚的时间跟事务执行时间差不多，也很大 # 第三部分 | 日志 ## 1. Mysql的日志类型有哪些，以及他们之间有什么区别？ 1. Mysql的日志类型主要有3种，分别是binlog，redolog，undolog，下面我将从日志类型、主要用途、删除策略来回答 2. binlog，逻辑日志；记录的是二进制日志，包括所有的DDL、DML语句，用于数据恢复、主从复制，它可以跨平台使用；按文件保留，可配置定期删除 3. redolog，物理日志；记录页号/页偏移量/修改长度/新值，用于Mysql发生崩溃时进行数据恢复；覆盖写 4. undolog，逻辑日志；记录表空间ID/行的主键/被修改字段的旧值/指向前一个版本的指针，用于事务失败后的回滚和mvcc一致性读；事务提交后延迟删除 5. 其中binlog和redolog都可以进行数据恢复，但他们的侧重点不同，binlog是server层的逻辑日志，侧重于某个时间点的恢复；而redolog是InnoDB存储引擎层的物理日志，侧重点是保证崩溃后已提交数据的不丢失，两者通过两阶段提交保证一致性，彼此不可替代 # 第四部分 | 锁 ## 1. 介绍一下MVCC？ 1. MVCC是多版本并发控制，在不加锁的情况下实现一致性读，解决读写冲突问题 2. 里面有个版本链的概念，数据库中的每条记录还有两个隐藏字段，`trx_id`和`roll_pointer`，分别表示事务id和指向哪个旧版本，对于新增、修改和删除，会在undolog中记录操作之前的数据，使更新完成的这条数据的`roll_pointer`指向这条日志，以此类推，就形成了一条链，undolog会在没有任何ReadView引用时进行删除 3. 查询数据时，会生成ReadView，里面有四个关键字段： * `creator_trx_id`（当前事务id，只读事务为0） * `m_ids`（已启动还未提交的事务集合） * `min_trx_id`（m_ids集合中最小的值） * `max_trx_id`（生成ReadView时，下一个即将被分配的事务id） 判断规则如下： * `trx_id`=`creator_id`，说明是自己改的，可见 * `trx_id`<`min_trx_id`，说明改这条数据的事务早就提交了，可见 * `trx_id`>=`max_trx_id`，说明改这条数据的事务是在生成ReadView之后进行的，不可见 * `min_trx_id`<=`trx_id`<`max_trx_id`，看trx_id是不是在m_ids里，如果在说明事务还未提交，不可见；如果不可见就顺着版本连往回找，直到找到一个可见的版本 4. 读已提交（RC）和可重复读（RR）的区别是，前者在一个事务中每次查询时都会生成ReadView，后者是在初次查询时生成ReadView，后面公用这一个。这也就说明了为什么读已提交会产生不可重复读的问题，而可重复读将这个问题解决了，因为可重复读读的是同一个ReadView，数据肯定是一致的 5. 还有两个概念，快照读和当前读。快照读走MVCC，读的是历史快照，不加锁；当前读是读取最新版本并且加锁，锁住记录和间隙，避免其他事务的影响。这也说明了为什么可重复读不能完全解决幻读问题，因为快照读和当前读混用的时候，就会出现幻读 6. 最后一点，MVCC并不能解决写写冲突，还是需要加锁 ## 2. 二级索引有没有MVCC快照？ 1. 没有，二级索引不存在`trx_id`和`roll_pointer`，无法进行判断，当需要某个历史版本的时候，会查看当前页的`page_max_trx_id`（最后修改这个索引页的最大id），如果他比当前事务启动时的最小活跃事务id还小并且未被标记删除，说明是可见的，直接用二级索引的数据即可；否则需要回表查聚簇索引 2. 聚簇索引更新时是直接覆盖旧值，使用`roll_pointer`指向undolog的版本链；而非聚簇索引则是将旧值打上删除的标记并插入新数据，等待purge线程来清理 ## 3. Mysql中有哪些锁类型？ 1. 共享锁（S）：允许多个事务同时读取同一资源，共享锁持有期间不允许加排他锁，共享锁之间不冲突 2. 排他锁（X）：只有获取到排他锁的事务才能对该资源进行读写 3. 行级锁：对行记录的索引进行加锁，不存在索引时可能会锁全表，可以同时持有共享锁，但排他锁互斥 4. 表级锁：对整张表进行加锁，在MyISAM中主要使用表锁，但在InnoDB中几乎不用，用表锁的场景只有DDL时 5. 间隙锁：锁住两个记录之间的间隙，在RR下，防止其他事务在这个间隙中插入数据，防止幻读，间隙锁之间不冲突 6. 临键锁：记录锁+间隙锁，锁住对应的行数据和其前面的间隙 7. 意向锁：分为意向共享锁（IS）和意向排他锁（IX），用于快速判断表中是否存在行锁，避免逐行检查。当需要对某条记录上S锁的时候，先在表上加个IS锁，代表此时表内有S锁；同样对记录上X锁的时候，也加个IX锁。当需要对表上S锁时，会检查是否存在IX锁，存在就不能上锁；同样对表加X锁时，看是否有IS和IX锁，存在也不能上锁 8. 插入意向锁：间隙锁锁住间隙后，其他事务要想在他们的间隙进行插入，必须等到间隙锁进行释放，在此期间，会对该事物加上插入意向锁，用于表示我正在等着你释放，如果有多个事务等待同一间隙，谁先抢到谁先来，如果一个事务达到指定时间还未抢到资格，则返回失败，防止饥饿现象 9. 自增锁：对表的主键进行自增操作，保证自增值的唯一性，在插入一条数据时，对表加自增锁，插入结束后，再释放这个锁（5.1.22版本之后，又加了个互斥量，性能高于自增锁，插入数据时，只要获得了递增值，就可以释放锁）【Mysql有个字段可以配置这些策略，默认值是1，即当已知插入数量时，用互斥量，不知道具体插入数量时，用自增锁；0-只用自增锁；2-只用互斥量】 10. 元数据锁：防止DDL和DML的冲突，当执行DDL时，对表加元数据写锁；执行DML时，加元数据读锁；还有一个作用是保护元数据的一致性，确保在执行DDL时，其他事务不能同时修改表结构 11. 其中表级锁、意向锁、自增锁、元数据锁是表锁；行级锁、间隙锁、临键锁、记录锁属于行锁；共享锁和排他锁既能做行锁也能做表锁 ## 4. 说一说Mysql的悲观锁和乐观锁？ 1. 悲观锁：假设一定会冲突，会在读取时进行加锁，适用于写多读少的场景，通过`select …… for update`或`select …… lock in share mode`实现 2. 乐观锁：不对数据进行加锁，而是通过版本号机制判断数据是否被别人改过，适用于读多写少的场景 3. 需要注意的是，如果使用悲观锁，`select …… for update`的时候，如果没走索引，会锁全表的所有行，因此必须要走索引 4. 乐观锁的version字段可以用int类型，因为int类型最大是21亿，即使每秒修改1000次，也能用60年 5. 在分布式系统下，如果是主从读写分离，那么可能出现版本号更新不及时的情况，这种情况下就要考虑使用redis，可以使用setnx，更新前检查是不是自己设置的值即可，更新完删除，其次redisson封装了RLock可以直接使用 ## 5. 避免死锁的方法有哪些？ 1. 固定访问顺序，比如规定先访问A表再访问B表，不要乱序 2. 避免大事务，可将大事务分为几个小事务，避免占用太多锁的时间 3. 建立索引，确保where字段走索引，如果字段没有索引，会对表中每一行依次进行加锁，变为全表锁 4. 不要再事务中进行耗时操作，比如大规模计算，减少锁的持有时间，尽快提交事务 ## 6. 发生死锁的解决办法？ 1. mysql InnoDB自带死锁检测，当发现死锁时，通常选择持有资源最小的那个事务回滚 2. 会有锁超时机制，当达到阈值时，自动释放锁回滚 3. 手动终止发生死锁的线程，使用 `show engine innodb status` 查看死锁信息，然后使用 `kill <thread_id>` 命令终止线程即可，这是紧急处理手段，生产环境尽量依赖于自动检测机制 # 第五部分 | 调优 ## 1. 如何使用explain语句进行查询分析？ 1. 主要分析几个字段，分别是type、key、rows、extra 2. type：访问类型，查询效率 const>eq_ref>ref>range>index>all （下面所指的结果是存储引擎层返回给Server层的，不是返回给应用的） * const：主键或唯一索引等值查询，结果最多一行 `SELECT * FROM user WHERE id = 10;` * eq_ref：连接查询里通过主键或唯一索引关联，外表每给一个值，InnoDB就返回一行 `SELECT * FROM order o JOIN user u ON o.user_id = u.id;` * ref：非唯一索引，可能返回多行 `SELECT * FROM user WHERE city = 'beijing';` * range：索引范围扫描 `SELECT * FROM orders WHERE create_time BETWEEN t1 AND t2;` * index：扫描整颗索引树，比all好点 `SELECT id FROM user;` * all：全表扫描 `SELECT * FROM user;` 3. key：实际用到的索引，最终被优化器选中的索引 4. rows：预估要扫描的行数，越少越好 5. extra：额外信息 * using index：走覆盖索引，完全不用回表 * using index condition：用了索引下推，减少了回表次数 * using where：引擎层返回的数据，server层仍需进行where判断（不管走不走索引下推，都要再校验一遍） * using filesort：无法利用索引进行排序，要额外排序 * using temporary：生成了临时结果集 * using index for skip scan：补齐左列，使用上联合索引 ## 2. explain都有哪些字段，各自的作用是什么？ 1. type：上述提到 2. key：上述提到 3. rows：上述提到 4. extra：上述提到 5. select_type：查询类型 * simple：简单查询，没有子查询，也没有union * primary：主查询，最外层查询 * subquery：子查询，select或where里的子查询 * derived：from里的子查询 * union：union里第二个及之后的select * union result：union合并后的结果 6. table：所要查询的表 7. id：层级+优先级，同一id表示同一查询同一层级，值越大越优先执行 8. partitions：实际访问了哪些分区 9. possible_keys：可能用到的索引 10. key_len：用到的索引字节长度 11. ref：索引列与什么值进行比较 * const：常量，如a=1 * func：函数，如a=NOW() * db.table.col：另一个表中的列 * NULL：没有用到索引 12. filtered：根据查询条件过滤掉行的百分百，越大越好 ## 3. 走了索引但还是很慢，可能是什么原因？ 1. 索引字段选择性低，比如性别字段，查询时几乎还要扫描半张表 2. 索引树太大，内存装不下需要进行频繁的磁盘IO 3. 回表次数太多，虽然走了二级索引，但如果查询出的数据很多，回表也占据大量时间 4. 返回数据量大，网络传输耗时，可能本身sql很快，是结果集过大拖慢的速度 5. 方案1：查看rows值的大小和extra有没有filesort或temporary 6. 方案2：对于选择性，可以使用`count(distinct column)`/`count(*)`【简单来说就是 有几个值/总行数】看它的值，如果低于0.1就不推荐作为索引 ## 4. 如何进行sql调优？ 1. 可以分为3步，首先查看慢查询日志定位慢sql；然后使用explain分析执行计划，主要关注type、key、rows、extra这几个字段，看是否用到了索引，索引是否失效等；最后定向进行调优，可以从以下几个方面调优 2. 第一，覆盖索引尽量包括要查询的数据，避免回表，还要保证覆盖索引不会失效 3. 第二，关注索引的失效场景，确保索引有效（比如最左前缀原则，隐式类型转换，在索引列上使用了函数/运算，左模糊匹配等，范围查询，or左右两边有一侧无索引等） 4. 第三，尽量避免 select * ,仅查找必须字段，减少网络传输 5. 第四，连表查询时检查字段字符集是否一致，比如utf8和utf8mb4字段join时会导致隐式转换，索引就没用了 6. 第五，避免深分页，如确实需要，可以考虑使用游标 7. 第六，单表超过2000w就要考虑分库分表提高读写性能 # 第六部分 | 缓冲区 ## 1. Buffer Pool的作用？ 1. 主要用来缓存数据页和索引页，查询时先看buffer pool中有没有，有的话直接从内存返回，否则去磁盘加载；更新时会将数据写入buffer pool同时生成redolog，等待后台线程定期刷盘/数据库正常关闭/checkpoint 推进/buffer pool空间不足 ## 2. Change Buffer是什么，他有什么作用？ 1. 它是InnoDB存储引擎中的一块内存区域，位于Buffer Pool中，用来记录二级索引的数据变更，当进行增删改时，如果涉及到的索引页不在内存中，不会从磁盘读到内存，而是将变更的数据记录到redolog和change buffer里，后续当这个索引列因查询被读进内存，或者后台线程做flush时（把内存中的数据刷到磁盘），把积攒的变更一次性合并进去 2. 好处：这样做提升了性能，不需要等到从磁盘加载数据页而是直接写到change buffer中，减少了IO阻塞，返回更快；其次批量变更相比于每次变更不需要频繁的磁盘读取，效率更高 3. 但需要注意的是，change buffer只对普通的二级索引生效，对于主键索引，索引页等同于数据页，修改时必须立即生效；对于唯一索引，要判断是否是唯一值，肯定要查数据，所以索引页一定会加载到内存中，可以直接更改；对于全文索引和空间索引，结构特殊，也不能延时更新 4. 对于数据丢失问题，每次写change buffer时也会写redolog，而redolog可以在数据崩溃时进行恢复，不需要担心 ## 3. Doublewrite Buffer的作用？ 1. 位于InnoDB的系统表空间ibdata1中 2. 解决数据页损坏问题，一个页是16kb，而操作系统IO单位是4kb，有可能在刷盘时突然断电，导致这个页的数据只传输了一部分，而redolog只能解决数据不丢失，对于数据损坏就无能为力了。而doublewrite就可以很好的解决这个问题。刷盘时使用顺序IO写入doublewrite buffer，再使用随机IO写入真正的数据文件 ## 4. Log Buffer的作用？ 1. 与Buffer Pool并列 2. 用来缓存redolog，将多次小写入合并成一次大写入，减少磁盘IO。它有三种刷盘策略，0-提交时不同步刷盘，靠后台线程每秒刷；1-提交时写入并同步刷盘；2-提交时写入操作系统缓存，不同步刷盘 # 第七部分 | 应用场景 ## 1. 如何避免单点故障？ 1. 使用主从集群（半同步）+读写分离：主库写，从库读，如果主库挂了将从库升为主节点，最常用的手段 2. 主备架构：备库平时不暴露身份，只有主库挂了后才使用替代主库，但资源利用率极低，好处就是实现简单 3. 主主架构：两台机器都能读写数据，不推荐，容易出现数据冲突问题 4. MRG集群（MySQL Group Replication）：Mysql官方提供，由多个节点组为一个集群，自动故障转移，保证强一致性 5. 第三方工具：如MHA，由Manager节点和Node节点组成，最强大功能在于主库挂了，切换到从库后使用ssh登录主库把binlog拷贝出来，应用到新主库上，最大限度减少数据丢失 6. 最重要的还是定期备份！可以定时全量备份，基于binlog做增量备份，异地备份等 ## 2. 读写分离场景下如何处理主从延迟？ 1. 首先明确一点，读写分离场景下无法彻底消除主从延迟，只能通过主库直读、复制优化等方面解决 2. 对一致性要求强的业务，强制走主库 3. 写操作结束后记录时间戳，短时间内的请求走主库，过了延迟窗口再走从库 4. 半同步复制，至少等一个从库返回确认收到binlog时才返回 5. 二次查询兜底，从库查不到再向主库查询一遍，但会有恶意攻击的风险 6. 另外，值得注意的是，如果事务里有读有写，大多数框架的做法是直接走主库 ## 3. Mysql的主从同步机制是怎样的？ 1. 主从复制是基于binlog的，他有三种类型，分别是statement、row、mixed。sratement是记录sql语句，row则是记录更新前后数据的变化，mixed是两种的混合策略，由Mysql自行判断选择；虽然statement体积比row小，但生产环境建议用row，保证数据一致性 2. 首先从库发起复制连接后，会在主库开启一个dump线程，根据从库请求的binlog文件名和position，将数据持续发送给从库 3. 从库通过IO线程接收主库的数据，把收到的binlog记录到relaylog中继日志中 4. 最后从库的sql线程读relaylog，逐条执行 5. 在Mysql5.6版本后支持并行复制，在8版本后引入WriteSet，支持更精细的行级别并行复制 ## 4. 什么是级联复制？ 1. 主库只同步给一级从库，二级从库再从一级从库获取数据，这样主库的dump线程压力就小了，适合于从库特别多的场景。比如多机房容灾，每个机房挂一个一级从库即可，机房内再挂多个二级从库，但这样延迟就叠加了 ## 5. 什么是分库分表，它有哪些类型？ 1. 分库分表就是将一张表中的数据拆分到多张表中，将一个库的数据拆分为多个库，分担读写压力，提高数据库性能 2. 分库分表的策略有两大类，水平拆分和垂直拆分，下面我分别说一下这两种模式的特点 3. 顾名思义，水平分表就是把表横着切一刀，按行划分数据，每张表的字段都相同；水平分库也相同，多库同结构，分担读写压力 4. 垂直分表就是竖着切一刀，按列划分数据，把一张表的字段分为多个表；垂直分库亦是如此，按服务类型进行拆分 5. 分表解决的是单表数据量过大导致的查询慢的问题；分库则是解决的是单机性能瓶颈问题 6. 常用的分库分表的中间件有：ShardingSphere、MyCat等，也可以用第三方封装的框架 ## 6. 分库分表应该考虑哪些问题？ 1. 主键ID选取 * 分库分表后一定不能用自增ID了，会冲突，可以选择雪花算法，Leaf，UUID这类分布式ID，但注意UUID是无序的，索引效率差，且占用字节数多，尽量不要选UUID 2. 事务一致性 * 使用分布式层面的两阶段提交，Mysql的XA事务就是这个，准备阶段先锁资源，提交阶段才真正写入（预提交+全体投票+统一决策），但性能较差 * TCC（Try、Confirm、Cancel）模式，通过业务代码自己实现，但开发成本高 * 本地消息表，一个事务成功之后发送消息到另一个库执行，搭配重试机制保证最终一致性 3. 跨库join失效 * 代码层面，先查主表拿到关联字段，再批量查从表，最后进行整合 * 字段冗余，比如将订单表冗余用户昵称，省掉一次查询 * 按同一个键分片，这样join查询都能落到同一个库 * 使用ES 4. 排序分页复杂，比如要查1000001-1000010条数据，就要查询所有分片，最后在内存中进行排序，取10条 * 禁止跳页查询，使用游标 * ShardingSphere、MyCat这些中间件实现了流式归并，边查询边排序 5. 聚合麻烦 * 单独维护一张计数表 * 各分片先count再累加，查询数等于分片数 * 使用ES ## 7. 如果让你主导项目的分库分表任务，大致的思路是什么？ 1. 分库分表不能盲目，要先评估必要性，如果说数据量较小就没必要分库分表，尝试从索引优化层面解决 2. 首先进行评估，看单表数据量有没有到达2000w，单机qps有没有到达5000，根据数据增长趋势预估未来几年的数据量，如果根本达不到瓶颈，直接优化索引即可 3. 然后进行方案设计，选取哪个字段作为分片的键、路由策略是什么、分片数量是多少。对于分片的键，首选查询量大的字段，比如user_id等；对于路由策略，推荐哈希取模，这样分布更均匀；对于分片数量，要根据增长趋势计算未来的增长量，最好一步到位，避免后续频繁扩容 4. 下面就到了技术选型，一类是客户端分片，有业务侵入但性能好，如ShardingSphere-JDBC；另一类是代理层分片，独立部署服务，业务无侵入但多一层网络开销，如MyCat，ShardingSphere-Proxy，我比较倾向于如ShardingSphere-JDBC 5. 其次进行数据迁移，可以使用canal监听旧库binlog同步数据，但在完成后要进行校验，比如行数对不对，关键字段值对不对，最好能跑一遍业务 6. 最后进行灰度切换，如果是上线业务，必须保证可用性，可以先切读请求到新库，一段时间稳定后再将写切换到新库，期间要维持双写，万一新库有问题可以随时使用旧库 # 第八部分 | 其他 ## 1. Mysql的存储引擎有哪些，默认使用哪个，MyISAM和InnoDB的区别是什么？ 1. InnoDB、MyISAM、Memory、CSV等，mysql5.5之前默认的存储引擎是MyISAM，5.5之后默认的存储引擎是InnoDB 2. InnoDB支持事务、外键、以及行级锁；MyISAM不支持事务、外键、只支持表级锁，并且锁的是整张表 3. 所以说对于读密集型任务，可以使用MyISAM；对于写密集型任务，应该用InnoDB，因为它使用的是行级锁，锁的粒度更细 4. 其次对于要保证事务一致性的场景，必须用InnoDB来保证数据的一致性 ## 2. Mysql中数据排序是如何实现的？ 1. Mysql的排序分为两种，内存排序和利用磁盘进行归并排序 2. 首先，如果排序字段命中索引，直接按索引顺序读取返回即可，不需要额外的排序（extra无using filesort）；但要注意order_by的顺序必须一致，不能一个升序一个降序，8.0版本之后可以在建立索引时指定排序方式 3. 如果没命中索引，根据数据量的大小判断是用内存排序还是磁盘归并排序，通过`sort_buffer_size`判断 4. 对于内存排序，又分为单路和双路排序，单路排序指把所有数据放到sort_buffer中，而双路排序只放排序字段和id，然后进行回表，通过`max_length_for_sort_data`判断 5. 最后就是磁盘进行归并排序，因为如果数据量太大，sort_buffer放不开，就会分批放在sort_buffer中进行排序，然后把排序的结果放到磁盘，等所有数据都到达磁盘，进行归并排序 ## 3. 一条sql语句的执行过程？ 1. 会依次经过连接器->分析器->优化器->执行器 2. 连接器：客户端通过TCP与数据库连接时会进行账号密码校验以及权限校验，整个连接周期内的sql都复用这份权限，如果权限修改了需要刷新或重连 3. 分析器：进行词法分析和语法分析，首先将sql语句拆分为多个token，进行词法解析，看看有没有词法错误；然后进行语法分析，看看sql语句格式是否正确，最后形成一颗抽象语法树（AST） 4. 优化器：基于语法树指定执行计划，包括索引的选取，访问表的顺序，是否使用临时表等，选择最优的方案 5. 执行器：按照执行计划，调用存储引擎层的接口，完成数据的存取；而对于非查询需求；会在存储引擎层写redolog和undolog、在server层写binlog，进行二阶段提交（执行器还会做行级权限校验且一行一行的从存储引擎层拿取数据，因为要判断该用户对特定的列是否有权限，只能在执行器阶段才能得到具体的列） 6. 再补充一点，Mysql8.0之前连接器后还会进行缓存的查询，如果有相同sql的缓存结果就直接返回了，但由于每次变更所有缓存都失效了，并且对于大量读的需求Redis就可以解决，因此没必要再在数据库层面进行缓存，所以8版本之后彻底移除了缓存 ## 4. 讲一下Mysql的B+树查询数据的全过程？ 1. 第一阶段：树的垂直查找，通过对页内索引记录的key进行二分查找，逐层向下，直到到达叶子结点 2. 第二阶段：页内查找，叶子结点就是一个数据页，数据页中很多槽，每个槽的指针指向了不同组的最后一个记录，因为不同组是连接成链表的，所以可以通过槽（二分）找到对应的分组，再进行遍历即可快速找到所需数据 3. 总结一下就是根节点->中间节点->叶子结点->页目录二分->组内链表遍历 ## 5. Mysql中int(11)表示什么？ 1. 首先明确一点，他并不是表示数值的存储范围，只要是相同数据类型，存储范围都是固定的；这里的11表示显示宽度，配合zreofill属性将左侧补0 2. 在mysql8之后将这个特性弃用了，因为现在有很多格式化工具，根本不需要格式化对齐 3. 但对比varchar(11)，他代表的是真真正正的长度为11的字符，不要搞混 4. 最后拓展一点，建字段时要选择合适的索引类型，以及是否需要符号，无符号比有符号数据范围可是整整提升了一倍 ## 6. inner join/left join/right join的区别？ 1. inner join：取交集 2. left join：返回左表所有行，右表如果无数据，填充null 3. right join：返回右表所有行，左表如果无数据，填充null 4. 如果团队规定必须用left join，对于right join只需交换两表位置即可；对于inner join，在where条件里加一个右表字段is not null的条件就行 ## 7. 数据库的三大范式？ 1. 1NF：字段必须是原子的，不可再分。比如省市区要拆开 2. 2NF：在1NF的基础上消除部分依赖，针对联合主键的场景，非主键字段必须完全依赖主键，不能只依赖主键的一部分。比如订单ID+商品ID作为联合主键，如果把订单时间也放进来，就是部分依赖，因为订单时间只依赖于订单ID 3. 3NF：在2NF的基础上消除传递依赖，比如订单表里存了用户ID和用户呢称，而用户昵称可以通过用户ID查用户表得到，这就是传递依赖 4. 但在大多数实际场景中，3NF基本用不到，对于订单表来说，除了存用户ID还可以吧用户昵称冗余进来，这样就减少了一次join查询，提高查询效率，属于以空间换时间。其次在分库分表的场景下，跨库join代价太大，更需要冗余 ## 8. Mysql中text类型你了解多少？ 1. 存储容量：tinytext-255字节、text-65535字节、mediumtext-约1600w字节、longtext-约42亿字节 2. 存储机制：在行内只存20字节的指针，真正的内容放到独立的溢出页里，这意味着查text字段时要多一次磁盘IO去溢出页找数据（行内放不下才采用这种方法，比如要存储的数据<8kb，可以直接放行内） 3. 是否能建索引：可以，但必须是前缀索引，根本原因是索引条目放不下这么大的数据，只能取一小部分（索引最大3072字节） 4. 模糊匹配：使用fulltext全文索引或者es，但fulltext中文分词效果一般，业界常用es做搜索，mysql只负责存储 ## 9. Mysql中varchar类型你了解多少？ 1. varchar()是变长字符，传入多少就占用多大的存储空间。但要注意的是在内存层面，比如排序、分组时会按预定的大小进行分配，因此并不是预设值的越大越好，此外单行的最大字节数是65535，所以varchar最大可分配的范围就是65535，前提是这一行没有其他列。还要提一点，就是varchar存储数据时由实际数据和长度标识组成，<=255时，长度标识占用1字节，大于255占用2字节，这也是为什么一些教程中255出现几率高的原因 2. 修改varchar大小时，如果不跨越255这个节点，通常不会导致锁表，只有在由100扩大到300，长度标识由1字节变为2字节，这就会重新建表，会锁表 ## 10. sql中各关键字的执行顺序是什么？ 1. from->join->where->group by->having->select->order by->limit 2. from：确定数据源，加载表数据 3. join：表连接，合并多表数据 4. where：分组前的行过滤，不能有聚合函数 5. group by：按指定列分组 6. having：分组后组过滤，可以用聚合函数 7. select：要返回的列 8. order by：排序 9. limit：返回行数 ## 11. 为什么不推荐使用多表join？ 1. 如果查询关联的表过多且每张表的数据量巨大，会占用大量cpu和内存资源，影响其他查询 2. 执行join时，Mysql会选择驱动表和被驱动表，首先遍历驱动表中每一行拿到关联字段，然后去被驱动表中查询；如果关联字段还没有索引，查询的次数=驱动表行数×被驱动表行数，这就导致查询量巨大，这也是为什么对于数据量大的表避免使用join的原因 3. 如果非要使用join，要遵循小表驱动大表的原则，把数据量小的表作为驱动表，数据量大的为被驱动表。因为查询次数的计算公式为 A×2log2B ，小表放在前面能大幅减少查询次数。Mysql优化器也会选择小表作为驱动表，但有时会选错（但一般不会出错），可以使用`straight_join`强制指定驱动表，这也是Mysql对join查询优化下的一种调优手段 4. 在表的数量不超过3个且每张表数据量在百万级以内且关联字段有索引的情况下可以使用join，但还是推荐在应用层分多次查询然后进行聚合，因为在关联查询的情况下分次查询速度不一定比一次查询慢，并且单表查询结果还可以进行缓存，第二次请求直接命中 5. 还有一点，join在分库查询场景中实现困难，如果最后进行分库操作，之前的join语句就全失效了，所以还是推荐在应用层处理 ## 12. 如何处理Mysql的深度分页问题？ 1. 先说一下深度分页导致的问题，通常情况下是使用 `limit offset , count` 的方式进行分页查询，但如果offset是百万甚至亿的级别，即使命中索引，也会扫描前offset条数据并丢弃，最后只取少的可怜的count个数据，这样性能非常低 2. 方式一，使用游标，在每次查询时向前端返回一个游标字段（主键/时间等），下一次查询带着这个字段拼接到sql里即可，但这样没法适用于跳页的查询情况 3. 方式二，使用子查询/join，先用子查询在二级索引上定位起始id，然后根据这个id去主键索引查数据。这样就把offset扫描成本控制在二级索引层，避免在主键索引上进行大量回表操作 4. 方式三，使用es做数据检索，但也会有es深度分页问题，一般使用 `search_after` 解决，本质依然是游标分页 5. 方式四，在应用层面限制，比如使用滚动式翻页适应游标分页策略，限制最大翻页数，热点数据放Redis等 ## 13. Mysql中存金额数据，用什么数据类型？ 1. 首先明确一点，不能用float/double，会存在精度丢失问题 2. 方案一：bigint存分，运算快，存储空间占用小，没有BigDecimal的性能开销，但在汇率计算等精度高的场景，无法处理 3. 方案二：decimal(总位数,小数)，可以处理精度高的场景，但性能开销大，处理复杂 4. Java中使用BigDecimal时需要注意要用字符串构造器，更精确；除法必须指定精度和舍入模式；比较用compareTo不要用equals，防止1.0和1.00不相等的情况出现 ## 14. 逻辑删除中唯一索引冲突问题？ 1. 场景：有一张报名表，user_id+activity_id作为唯一索引，用户先报名，又取消报名，再次报名的时候索引就冲突了 2. 方案一：将逻辑字段改为delete_at记录删除时间，然后和user_id+activity_id+delete_at做唯一索引 3. 方案二：is_delete默认值为0，删除时不是将这个值改为1而是改为主键ID，因为主键ID不可能重复 4. 方案三：使用同一数据，user_id+activity_id作为唯一索引，报名时为0，删除时为1，再次报名再改为1，不是新增而是更新，使用额外的一张流水表（只增）记录用户行为
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%’; 查看行锁情况）。
MySQL 从入门到删库跑路，保姆级教程！傻子可懂
你是小阿巴，刚入行的程序员。  这天，你接到一个私活：帮学校做个学生管理系统，要能管理学生信息、记录成绩、统计数据。 你一听，这不简单吗？用 Java 写个程序，把数据存到 Map 里就搞定了。 ```java public class StudentManagementSystem { // 使用 Map 存储学生信息，key 为学号，value 为学生信息字符串 private Map<Integer, String> studentMap = new HashMap<>(); // 添加学生 public void addStudent(int studentNo, String name, double score, int classId) { String studentInfo = name + "," + score + "," + classId; studentMap.put(studentNo, studentInfo); } // 查询学生 public String getStudent(int studentNo) { return studentMap.get(studentNo); } // 查询所有学生 public void listAllStudents() { for (Map.Entry<Integer, String> entry : studentMap.entrySet()) { System.out.println("学号：" + entry.getKey() + "，信息：" + entry.getValue()); } } } ``` 结果一周后，甲方指着你的鼻子骂道：狗阿巴，我录入的 500 个学生信息怎么全没了？！ 你一查，原来昨晚服务器重启了，导致内存里的数据全部丢失！ 你汗流浃背了：看来我得把数据保存到硬盘上…… 于是你连夜改代码，把数据存到了文本文件里，这下数据就不会丢失了。  但接下来，甲方提出了各种不同的查询数据需求，每个需求你都得写一堆代码逻辑，让你越来越头大。  于是你找到号称 “后端之狗” 的鱼皮求助：有没有更好的办法管理数据啊？ 鱼皮笑了笑：当然要用数据库啦！ 你一脸懵：数据库？那是啥？ ⭐️ 推荐观看本文对应视频版：https://bilibili.com/video/BV1iJSLBbEyD ## 第一阶段：认识数据库 鱼皮：数据库就像一个超级 Excel 表格，可以存储管理海量数据、快速灵活地查询和筛选数据、在多个服务器间共享数据、并且能够精确控制数据的读写权限。  你恍然大悟：啊，所以我应该用数据库来管理学生信息。🤡 鱼皮：没错，绝大多数项目的数据都存在数据库里，因此数据库是后端程序员的必备技能。 数据库主要分 2 大类： 1）关系型数据库，比如 MySQL、Oracle、PostgreSQL，适合存储相互之间有关联的数据，比如学生和班级、订单和商品。  2）非关系型数据库，比如 Redis（主要用于缓存和高速读写）、MongoDB（文档型数据库），它们在数据结构和使用场景上更灵活。  你：这么多数据库，我该学哪个呢？ 鱼皮：**建议新手从 MySQL 开始**，因为它是主流的、容易入门的、开源的关系型数据库。 你：那还等什么，MySQL，启动！ 鱼皮：别急，在学之前，得先了解几个数据库的基本概念。 1）数据库管理系统（DBMS）：顾名思义就是用来管理数据库的系统，比如 MySQL。它把数据存储在硬盘上，断电也不会丢失。 2）数据库：一个 MySQL 系统可以管理多个数据库，比如每个项目一个库（学生系统一个库、订单系统一个库），互不干扰。 3）表：一个数据库可以有多张数据表，用来存储某一类数据。 4）记录：表由一行行记录组成，每一行就是一条数据 5）字段：每一列对应一个字段，具有名称和类型。比如姓名字段是文本类型、年龄字段是数字类型。  6）关联 这是关系型数据库的核心，表和表之间是有联系的，主要有 3 种关系。 1 对 1：比如学生表和学生档案表，一个学生对应一份档案  1 对多：比如班级表和学生表，一个班级有多个学生  多对多：比如学生表和课程表，一个学生可以选多门课，一门课也可以被多个学生选  这些表和表之间的关联形成了完整的业务系统。 你想了想：对哦，也就是说我可以根据学生关联查询到所属的班级和选修的课程。 那怎么操作数据库呢？ 鱼皮：这就要用到 SQL 了。 SQL 是专门用来操作数据库的 **结构化查询语言**（Structured Query Language）。 你可以用 SQL 实现各种查询，比如： 1）查询所有学生：`SELECT * FROM student;` 2）只查询属于某个班级的学生：`SELECT * FROM student WHERE class_id = 1;`  3）分组统计每个班级中所有学生的平均成绩：`SELECT class_id, AVG(score) FROM student GROUP BY class_id;`  4）还可以同时查询学生表和班级表，并把结果关联在一起：`SELECT s.name, c.class_name FROM student s JOIN class c ON s.class_id = c.id;`  此外，不同的数据库管理系统（比如 MySQL、Oracle、SQL Server）有自己的 “方言”，操作这些数据库的 SQL 会有一点点小差别。  你：天呐，相当于我要多学一门语言，而且还要学方言！ 鱼皮：别担心，SQL 的核心语法都是通用的。  而且你现在只需要知道 SQL 是用来操作数据库的就够了， 有时间再到我开发的 [免费 SQL 学习网站](https://sqlmother.yupi.icu/) 边练边学，而且后续我还会出视频专门讲 SQL 哦。  你：关注了关注了~ 鱼皮：那接下来咱们就开始实战学习吧，先把 MySQL 装上并且操作一波。 ## 第二阶段：实战应用 ### 基础操作 机智如你，直接打开 [官网](https://dev.mysql.com/downloads/mysql/) 下载了 MySQL 数据库，并且成功安装运行。  但是怎么操作 MySQL 呢？ 鱼皮：可以使用官方提供的 [命令行工具](https://dev.mysql.com/doc/refman/8.4/en/mysql.html)，先输入命令，输入默认设置的用户名和密码，就能连上数据库并进行操作了。  你：root 是什么？ 鱼皮：root 是数据库的超级管理员账号，拥有所有权限。 不过命令行看着不直观，我建议你装个 MySQL 可视化工具（比如 [Navicat](https://www.navicat.com/en/products/navicat-premium-lite)、DataGrip），能像使用 Excel 一样管理数据库，数据一目了然。  你：哇，确实方便多了！ 鱼皮：接下来我带你实际操作一遍，用数据库来管理学生信息。 1）首先连接数据库 点击左上角创建连接，选择数据库的类别，然后输入数据库配置信息，点击确认，就连接成功了。  2）然后来创建一个数据库 右键点击数据库连接，选择 “新建数据库”，命名为 school，点击确认，就创建成功了。 对应的 SQL 语句是 `CREATE DATABASE school;`  3）接下来我们要创建表 鱼皮：想一想，如果要设计一个存储学生信息的表，需要哪些字段？ 你：学号、姓名、成绩、班级。 鱼皮：没错，每个字段还要指定类型。 - 学号用 int 整数类型 - 姓名用 varchar 文本类型 - 成绩用 decimal 小数类型（保留 2 位小数） - 班级用 int 整数类型（表示班级编号）  除了类型，创建表时还要设置一些 **约束**，比如： - 主键：每条数据的唯一标识，一般建议每个表单独加一列 id 字段，作为主键 - 非空：某些字段不能为空，比如姓名不能为空 - 唯一：值不能重复，比如每个学号都是唯一的 - 外键：建立表之间的关联关系，比如学生表的 class_id 可以设为外键，关联到班级表的 id，保证数据的完整性。但实际开发中外键存在性能问题，用的没那么多。  设计好表结构之后，点击保存，数据表就创建成功了。 对应的 SQL 语句类似这样： ```sql CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, # 主键，自动递增 student_no INT UNIQUE NOT NULL, # 学号，唯一且非空 name VARCHAR(50) NOT NULL, # 姓名，非空 score DECIMAL(5,2), # 成绩，最多 5 位数字，2 位小数 class_id INT # 班级编号 ); ``` 4）然后我们就可以操作数据表了。主要有 4 类核心操作 —— 增删改查。 先插入几条学生数据： ```sql INSERT INTO student (student_no, name, score, class_id) VALUES (2024001, '小阿巴', 95.5, 1); ``` 然后查询所有学生数据： ```sql SELECT * FROM student; ```  修改某条数据，把你的成绩改成满分： ```sql UPDATE student SET score = 100 WHERE student_no = 2024001; SELECT * FROM student; ``` 最后删除某条数据： ```sql DELETE FROM student WHERE student_no = 2024001; SELECT * FROM student; ```  鱼皮：注意，删除掉的数据就再也找不回来了！ 因此实际项目中建议使用 **逻辑删除**，就是加个 `is_deleted` 字段来标记数据是否失效，而不是真的删除数据，这样即使误删也能恢复。  ### 客户端操作 你很是兴奋：用可视化工具真方便啊，但是我怎么用代码操作数据库呢？ 鱼皮：主流编程语言都有操作数据库的 SDK，比如使用 Java 的 JDBC，需要先加载对应数据库的驱动、然后获取连接、编写 SQL 语句并执行、最后收集结果并关闭连接。  你挠了挠头：我就查询 1 次数据库，都要写这么多代码么？而且我根本不会写 SQL 语句啊！ 鱼皮笑道：没关系，实际开发中我们会使用 **ORM 对象关系映射框架**，可以把数据库的表映射成 Java 对象，像操作对象一样操作数据库。  比如 Java 的 MyBatis 框架，可以让你用更简洁的方式执行 SQL： ```java // MyBatis 方式：只需要写 SQL，框架自动处理连接和结果映射 Student student = studentMapper.selectByStudentNo(2024001); System.out.println(student.getName()); ``` 还有它的增强版 MyBatis Plus 和 MyBatis Flex，提供了更多开箱即用的功能，连 SQL 都不用写！直接调用现成的方法，几行代码就能实现增删改查，告别 SQL Boy！  你感叹道：这才是人写的代码啊！优雅，真是优雅~ 鱼皮提醒道：但是，框架不是万能的，有些复杂查询的 SQL 还要自己手写，不过现在有 AI 了，写个 SQL 还不是手拿把掐的？ 你：明白了，我这就用框架重写学生管理系统。 ## 第三阶段：数据库特性 ### 索引 一个月后，你重写的学生管理系统正式上线，不仅得到了甲方的好评，而且很快火遍了全国高校。 你内心暗爽：数据库也不过如此嘛~  但你没想到，随着学生数据量的暴涨和表结构的扩展，你的数据库查询速度也越来越慢，光是查询某个班级的学生竟然都要等好几秒！ 你有些惊讶：我这 SQL 也不复杂啊，怎么查询这么慢？ 鱼皮：加个索引试试？ 你：索引？那是啥？ 鱼皮：索引就是数据库的目录。你现在查数据，数据库得一行一行全表扫描，数据量越大速度越慢。加了索引后，数据库可以通过索引快速定位到数据，就像通过书的目录快速翻到某一页。  你恍然大悟：对啊，老师经常按班级查询学生，不妨给班级字段添加索引。 ```sql CREATE INDEX idx_class_id ON student(class_id); ``` 添加索引后，你再次测试查询，竟然只要几十毫秒！ 你激动得跳了起来：索引太香了，我要给所有字段都加索引~ 鱼皮摆摆手：别，索引可不是越多越好！ 你愣住了：为啥？ 鱼皮：索引虽然能加快查询，但也有代价。 - 一方面会增加写入数据（增删改）的开销。因为每次写入数据，索引也要更新。 - 此外，索引本身也是数据，要占用更多的存储空间。  所以，要在经常用来查询、并且数据区分度高的字段上合理添加索引。比如班级 ID、学号这种字段适合加索引，但性别字段（只有男女两个值）就不适合。  ### 事务 又过了几天，甲方又提新需求了。 甲方：小阿巴，有老师反馈题目判错了，需要批量把所有学生的成绩都加 5 分。 你信心满满：简单，不就是个批量更新操作么？ 你写了一段代码，循环更新所有学生的成绩。  没想到，越自信 Bug 越多，功能刚刚上线，就收到了投诉：怎么有些学生的成绩改了，有些没改？ 你查看了下日志，发现前面学生的成绩更新成功了，但由于网络波动，导致后面的学生都没更新。  你急坏了：咋办，这样就不公平了啊！ 鱼皮看了看你的代码：这是典型的数据一致性问题，你需要用 **事务** 来解决。 你：啥是事务？ 鱼皮：事务就是把多个操作捆绑在一起，要么全部成功，要么全部失败。  就像你给别人投币，你扣除硬币和对方增加硬币必须同时成功，不能让硬币凭空消失对吧？ 你：对对对，那批量改成绩也是，要么所有学生都加 5 分，要么都不加。 鱼皮：没错，数据库事务有 4 个特性，简称 ACID： - 原子性（**A**tomicity）：要么全做，要么全不做。 - 一致性（**C**onsistency）：数据从一个正确状态到另一个正确状态。比如转账时，A 扣钱和 B 加钱的总和不变。 - 隔离性（**I**solation）：多个事务互不干扰。 - 持久性（**D**urability）：事务完成后，数据永久保存。  你：那怎么使用事务呢？ 鱼皮：如果直接用原始的 JDBC，需要手动控制事务，比较麻烦。 但如果在 Spring 框架中，只需要加一个 `@Transactional` 注解就搞定了：  Spring 会自动帮你管理事务。如果中间任何一步出错，抛出了指定的异常，整个事务会自动回滚，将数据恢复到原来的状态，保证要么所有学生都加分成功，要么都不加。  你：原来这么简单啊，我这就用事务重写代码！ ### 其他 鱼皮：MySQL 还有一些其他特性，比如视图可以用来简化复杂查询、存储过程可以批量执行 SQL、触发器可以自动触发操作。 1）视图：视图是一个虚拟表，把复杂查询封装起来重复使用。比如你经常要统计每个班级的平均成绩，每次都写一长串 SQL 太麻烦，可以创建一个视图，以后直接查视图就行。 2）存储过程：可以批量执行 SQL。比如每天定时同步数据，写个存储过程，定时调用就行。 3）触发器：可以自动触发操作。比如每次添加用户，自动生成一条操作日志，不用手动写代码。  你感叹道：原来数据库还有这么多功能，我学不完了啊…… 鱼皮笑了：别担心，这些在实际开发中用得不多，先简单了解就好。 ## 第四阶段：生产环境实践 几年后，你已经成为小有名气的 “学生管理系统大师”，还成立了自己的工作室。 没事儿就对着新来的实习生阿坤吹牛皮：你阿巴哥我啊，精通数据库，索引、事务耍的贼溜儿~  然而某天上午，学校的运维同学打来电话：阿巴阿巴，系统出问题了，所有操作都一直 **转圈、超时**！ 好像是数据库卡死了！  你大惊：什么？！我都加索引了，还能卡死？  emmm…… 对了，我想到了！ 赶紧重启数据库，重启解决所有问题！嘿嘿嘿哈哈哈哈~  结果重启没多久，数据库又卡死了！ 你彻底懵了：呜呜呜，怎么办啊！ 这时，你身旁的阿坤突然鸡叫起来：我来！  ### 为什么系统会崩溃？ 阿坤：我们先看看为什么系统会崩溃？ 开启 MySQL 的慢查询日志功能，有两种方式。 1）通过 SQL 命令（临时开启） ```bash # 开启慢查询日志功能 SET GLOBAL slow_query_log = 1; # 设置慢查询阈值（超过几秒算是慢查询） SET GLOBAL long_query_time = 5; # 修改日志文件位置（可选） SET GLOBAL slow_query_log_file = '/path/to/slow.log'; ``` 2）修改配置文件（永久生效） ```bash # 在 [mysqld] 部分设置参数 slow_query_log = 1 long_query_time = 5 slow_query_log_file = /path/to/slow.log ``` 你看，有些 SQL 语句执行了几十秒！  使用 Explain 命令分析下这些查询，原来没有正确使用索引，导致了全表扫描，把数据库拖垮了。  这些慢查询就是罪魁祸首，它们会 **长时间霸占数据库连接和 CPU 资源**。当大量用户同时执行慢查询，数据库连接池很快被耗尽，新的请求因为无法获取连接、全部阻塞，导致数据库 “卡死”。  你惭愧地低下头：我知道了，生产环境一定要开启慢查询日志，及时发现慢 SQL 并优化。 ### 数据丢失了怎么办？ 屋漏偏逢连夜雨，你很快又收到了投诉，说是有的学生数据丢失了！ 你很是疑惑：MySQL 不是有日志（Redo Log 和 Binlog）来保证数据持久性吗？怎么会丢数据呢？  经过排查，原来是服务器的硬盘被一个学生不小心踹坏了！ 你难受得像持矢了一样：真是人在家中坐，Bug 天上来。  唉，我怎么把数据找回来啊？ 阿坤：别怕，幸好鱼皮哥之前设置了备份。一方面定期做 **全量备份**，比如利用 `mysqldump` 工具每天凌晨备份一次并发送到其他服务器上；再配合 **增量备份**，利用 Binlog（二进制日志）记录每次的数据修改操作，这样即使数据库崩了，也能恢复到最近的状态。  ### 怎么保证系统不会再崩？ 你松了一口气：那怎么保证数据库不会再宕机呢？ 阿坤：可以搭建 MySQL 高可用集群，典型的是 **一主多从** 架构。 主库（Master）专门负责写操作（增删改），并通过 Binlog 记录每一次数据变更操作。 从库（Slave） 拉取主库的 Binlog 到本地文件中，然后回放数据变更操作，实现数据同步。我们可以利用从库来承担绝大部分的读操作，这叫 **读写分离**，能大大提升并发能力。  当主库出现故障时，利用 **MHA** 等工具，自动将一个从库提升为新的主库，并调整其他从库的指向，实现故障的自动切换。  ### 数据量大了怎么办？ 你很是惊讶：之前完全没听说过这些啊…… 阿坤用看流浪狗的眼神看了你一眼：阿巴哥哥，如果数据量特别大，一个库存不下怎么办？ 你哑口无言：阿巴阿巴…… 阿坤笑了：当然是 **分库分表** 啦！比如 1）水平拆分：把同一张表的数据分散到多个表或多个库中。比如根据学生 ID 的尾号，奇数的存到表 1，偶数的存到表 2。  2）垂直拆分：按业务功能来拆分库，比如把学生基本信息、成绩信息、选课信息分别存到不同的库中。  你彻底服了：妙啊，这样就能存储海量数据了！ ### 其他实践 这时鱼皮走过来拍了拍阿坤的肩膀：小伙子年轻有为啊！ 小阿巴，MySQL 生产环境实践的知识点还有很多。比如： 1）权限管理：不要所有人都用 root 账号，风险太大，要给不同的人分配不同的权限。 2）云数据库服务：提供了现成的 MySQL 集群架构、监控系统、自动备份、数据迁移等，比自己运维省心多了。  3）调优技巧：硬件优化、数据库配置优化、库表设计优化、SQL 优化、连接池优化等等。 4）常见问题：还有死锁排查、大表的在线变更、数据迁移、主从延迟处理等等，遇到了再去解决。 你羞愧地抬不起头：我以为自己已经掌握了数据库，原来只是学了个皮毛……  ## 第五阶段：深入底层原理 于是，你主动找到阿坤：我想深入学习 MySQL，不能只停留在会用的层面，请问怎么学习底层原理啊？ 阿坤有些惊讶：咦？你不 [背八股文](https://www.mianshiya.com/) 的么？刷刷题就好了呀！ 你震惊了：现在的实习生，竟然恐怖如斯！  鱼皮：阿坤你别逗他了，其实我们可以带着问题学习。比如 **MySQL 是如何实现高效查询的** ？ 你想了想：加索引？ 鱼皮：对，但这只是使用层面。底层实现有很多技术，比如高效的存储引擎（InnoDB）、优秀的索引结构（B+ 树）、缓冲池机制、查询优化器等等。  比如我考考你，下面两个 SQL 语句哪个执行更快？ ```sql # SQL 1: 使用 OR SELECT * FROM student WHERE class_id = 1 OR class_id = 2; # SQL 2: 使用 IN SELECT * FROM student WHERE class_id IN (1, 2); ``` 你：额…… 第 2 个？因为它更简短。 鱼皮：哼哼，答案是 **几乎一样快**！因为 MySQL 的查询优化器非常智能，它会分析语句、将它们处理成相同的逻辑结构，再去执行。 这就是 MySQL 能高效查询的原因之一，带着这些问题去阅读相关文章，或者直接像阿坤说的刷一刷 MySQL 面试题，就能快速学会很多核心知识点。  如果想系统学习，可以看看《MySQL 是怎样运行的》、《高性能 MySQL》这几本书。  要记住，学习底层原理不只是为了应付面试，而是为了更好地使用 MySQL，遇到问题时能够快速定位和解决。 你：好的，我这就去学！ ## 结尾 若干年后，你已经成为了大厂的数据库专家。不仅能熟练设计库表、优化性能，搭个 MySQL 集群也是手拿把掐的。  你也像鱼皮当时一样，耐心地给新人分享学习数据库的经验：数据库是实战型技术，一定要多动手实践。  再次遇到鱼皮是在一条昏暗的小巷，此时的他年过 35，灰头土脸。你什么都没说，只是给他点了个赞，不打扰，是你的温柔。  更详细的 [MySQL 数据库学习路线](https://www.codefather.cn/course/1789189862986850306/section/1789190581420793858)，可以在编程导航免费阅读哦。 ## 更多 💻 编程学习交流：[编程导航](https://www.codefather.cn/) 📃 简历快速制作：[老鱼简历](https://www.laoyujianli.com) ✏️ 面试刷题神器：[面试鸭](https://www.mianshiya.com) 📖 AI 学习指南：[AI 知识库](https://ai.codefather.cn/)
MySQL 事务
## 事务 事务：事务是一组操作的集合，该集合中的所有操作被视为一个整体，要么全部执行成功，要么全部执行失败。若任意一个操作执行失败，则事务`回滚`，`所有操作全部撤销`。 MySQL 中默认开启事务，但是该事务只是针对一条 SQL 语句，而不是一组。如果想要给一组 SQL 语句开启事务，则需要手动编写 SQL 语句，使用 `BEGIN` 开启事务，使用 `COMMIT` 提交事务，使用 `ROLLBACK` 回滚事务。 ## 事务的操作 | 操作 | 描述 | | --- | --- | | SELECT @@AUTOCOMMIT; | 查看当前数据库是否自动提交，1 表示自动提交，0 表示手动提交 | | SET AUTOCOMMIT = 0 / 1; | 关闭 / 开启自动提交 | | BEGIN; / START TRANSACTION; | 开启事务 | | COMMIT; | 提交事务 | | ROLLBACK; | 回滚事务 | ```sql CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) NOT NULL COMMENT '姓名', money INT NOT NULL COMMENT '余额' ) COMMENT '账户表'; INSERT INTO account(name, money) VALUES ('张三', 2000), ('李四', 2000); -- 没有事务 -- 转账操作，一共需要三步 -- 1. 查询张三的余额，判断是否大于 1000 SELECT account.money FROM account WHERE name = '张三'; -- 2. 将张三的余额减少 1000 UPDATE account SET money = money - 1000 WHERE name = '张三'; -- 假设程序出现异常，步骤 3 没有执行，那么张三的余额减少 1000，而李四的余额并没有增加 1000 -- 3. 将李四的余额增加 1000 UPDATE account SET money = money + 1000 WHERE name = '李四'; ``` ```sql -- MySQL 默认自动提交事务 SELECT @@AUTOCOMMIT; -- 改为手动提交 -- 此时执行完 SQL 语句后，需要手动执行 COMMIT 命令提交事务 -- 若不执行 COMMIT 命令，即使 SQL 执行了，数据也不会有改动 SET AUTOCOMMIT = 0; -- 使用事务 -- 转账操作，一共需要三步 -- 1. 查询张三的余额，判断是否大于 1000 SELECT account.money FROM account WHERE name = '张三'; -- 2. 将张三的余额减少 1000 UPDATE account SET money = money - 1000 WHERE name = '张三'; -- 假设程序出现异常，步骤 3 没有执行，那么张三的余额减少 1000，而李四的余额并没有增加 1000 -- 3. 将李四的余额增加 1000 UPDATE account SET money = money + 1000 WHERE name = '李四'; -- 当全部 SQL 执行成功后，提交事务 COMMIT; -- 当有任意 SQL 执行失败，回滚事务 ROLLBACK; ``` ```sql -- MySQL 默认自动提交事务 SELECT @@AUTOCOMMIT; -- 改为自动提交 SET AUTOCOMMIT = 1; -- 使用事务 -- 开启事务，虽然是自动提交，但是开启事务后，MySQL 就不会提交了 START TRANSACTION; -- 转账操作，一共需要三步 -- 1. 查询张三的余额，判断是否大于 1000 SELECT account.money FROM account WHERE name = '张三'; -- 2. 将张三的余额减少 1000 UPDATE account SET money = money - 1000 WHERE name = '张三'; -- 假设程序出现异常，步骤 3 没有执行，那么张三的余额减少 1000，而李四的余额并没有增加 1000 -- 3. 将李四的余额增加 1000 UPDATE account SET money = money + 1000 WHERE name = '李四'; -- 当全部 SQL 执行成功后，提交事务 COMMIT; -- 当有任意 SQL 执行失败，回滚事务 ROLLBACK; ``` ## 事务四大特性 ACID 1. 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么全部完成，要么全部不完成，不会只完成一部分。 2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。一致性状态：指事务执行前后数据库中数据的完整性和正确性。 3. 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据，对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 4. 持久性（Durability）：事务执行完成之后，对数据库中数据的改变就是永久的，即数据改变之后，其他事务可以访问这些数据。 ## 并发事务 当多个事务并发执行时，会出现并发问题，比如多个事务同时对同一个数据进行修改，那么就会产生数据不一致的情况。 | 问题 | 描述 | | --- | --- | | 脏读 | B 事务修改了一条数据，但没有提交，此时 A 事务查询该值，读到了 B 事务未提交的数据，然后 B 事务遇到异常回滚了，那么 A 事务查询到的数据就是脏数据 | | 不可重复读 | A 事务两次读取到同一个数据，两次结果不一致 | | 幻读 | B 事务插入一条数据但没提交，A 事务查询时没有这条数据，但是 A 在插入数据时，B 提交了事务，导致 A 插入数据失败 | ## 隔离级别 隔离级别越高越安全，但是并发性能越差。 | 隔离级别 | 描述 | 可能出现问题 | | --- | --- | --- | | READ UNCOMMITTED | 读未提交 | 脏读、不可重复读、 幻读 | | READ COMMITTED | 读已提交 | 不可重复读、幻读 | | REPEATABLE READ（默认） | 可重复读 | 幻读 | | SERIALIZABLE | 串行化 | | 1. 查询当前数据库的隔离级别 > SELECT @@TRANSACTION_ISOLATION; 2. 修改数据库的隔离级别 > SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL 隔离级别; ### 脏读 ```sql -- 查询当前的隔离级别 SELECT @@TRANSACTION_ISOLATION; -- REPEATABLE-READ -- 脏读 -- 1. 使用命令提示符打开两个窗口并登录 -- A: 设置隔离界别为 读未提交 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- A: 开启事务 START TRANSACTION; -- A: 查询账户表数据 SELECT * FROM account; -- B: 开启事务 START TRANSACTION; -- B: 修改数据 UPDATE account SET money = money - 1000 WHERE name = '张三'; -- A: 再次查询账户表数据 -- 虽然 B 未提交事务，但是已经能查询出 B 事务更新后的数据 SELECT * FROM account; -- 设置隔离级别为 读已提交，重复上述操作，可以解决脏读问题 -- 只有当 B 提交事务后，A 才能查询到 B 修改后的数据，但是这会引发不可重复读问题 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` ### 不可重复读 ```sql -- 不可重复读取 -- 1. 使用命令提示符打开两个窗口并登录 -- A: 设置隔离界别为 读已提交 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- A: 开启事务 START TRANSACTION; -- A: 查询账户表数据 SELECT * FROM account; -- B: 开启事务 START TRANSACTION; -- B: 修改数据 UPDATE account SET money = money - 1000 WHERE name = '张三'; -- B: 提交事务 COMMIT; -- A: 再次查询账户表数据 -- 因为 B 的修改已经提交，所以 A 可以查询到 B 的修改 SELECT * FROM account; -- 设置隔离级别为 可重复读，重复上述操作，可以解决不可重复读问题 -- 在事务 A 中，永远读取的是事务 A 中查询的数据，不会被事务 B 改变，但是这会引发幻读问题 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` ### 幻读 ```sql -- 幻读 -- 1. 使用命令提示符打开两个窗口并登录 -- A: 设置隔离界别为 可重复读 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- A: 开启事务 START TRANSACTION; -- A: 查询账户表数据 SELECT * FROM account; -- B: 开启事务 START TRANSACTION; -- B: 修改数据 INSERT INTO account VALUES (3, '王五', 2000); -- B: 提交事务 COMMIT; -- A: 再次查询账户表数据，但是因为不可重复读的原因，A 无法查询到 id 为 3 的数据 SELECT * FROM account; -- A: 也插入 id 为 3 的数据 -- B 已经插入了数据，但是因为不可重复读的原因导致 A 并不知道 B 插入了数据 -- 所以此时 A 会插入失败 INSERT INTO account VALUES (3, '王五', 2000); -- 设置隔离级别为 串行化，重复上述操作，可以解决幻读问题 -- 如果事务 A 一直未结束，则其他事务无法操作表数据，必须等待事务 A 结束后才能继续执行，一次会导致并发效率低问题 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ```
MySQL 多表查询
## 多表关系 在实际开发中，每个表之间都存在一些关系，基本上分为三种：一对一、一对多(多对一)、多对多。 ### 一对一 一对一关系，比如：`学生表`和`档案表`，对于学生来说，一个学生只能拥有一份档案，而档案只能属于一个学生，这就是`一对一`。 ```sql CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名' ) COMMENT '学生表'; CREATE TABLE profile ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', phone CHAR(11) UNIQUE COMMENT '电话号', address VARCHAR(100) COMMENT '家庭住址', -- 一份档案之能属于一个学生，所以必须添加 UNIQUE 关键字 student_id INT UNIQUE COMMENT '学生ID', CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student (id) ) COMMENT '档案表'; ``` ### 一对多(多对一) 一对多关系，比如：`学生表`和`班级表`，对于学生来说，一个学生只能属于一个班级，而班级可以有多个学生，这就是`一对多`。反过来站在班级这个角度看，一个班级可以有多个学生，但一个学生只能属于一个班级，这就是`多对一`。 在建表时，需要在`多`的这一方添加外键，用来保存`一`的这一方的主键。 ```sql -- 学生表(多) -- student: id, name, class_id -- 班级表(一) -- class: id, name ``` ```sql CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名', class_id INT COMMENT '班级ID'， -- 添加外键 CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id) ) COMMENT '学生表'; CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '班级名' ) COMMENT '班级表'; ``` ### 多对多 多对多关系：比如：`老师表`和`班级表`，对于老师来说，一个老师可以教多个班级，而班级也可以有多个老师，这就是`多对多`。 在建表时，需要额外创建一张`中间表`，该表拥有自己的主键，和这两张表的外键，用来保存两方的主键。 ```sql -- 教师表(多) -- teacher: id, name -- 班级表(多) -- class: id, name -- 教师班级中间表 -- teacher_class: id, teacher_id, class_id ``` ```sql CREATE TABLE teacher ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '教师ID', name VARCHAR(10) COMMENT '教师姓名' ) COMMENT '教师表'; CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '班级ID', name VARCHAR(10) COMMENT '班级名' ) COMMENT '班级表'; INSERT INTO teacher(name) VALUES ('张老师'), ('李老师'), ('王老师'), ('赵老师'), ('周老师'); INSERT INTO class(name) VALUES ('一班'), ('二班'), ('三班'), ('四班'), ('五班'); CREATE TABLE teacher_class ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '中间表ID', teacher_id INT COMMENT '教师ID', class_id INT COMMENT '班级ID', CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher (id), CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id) ) COMMENT '教师班级中间表'; INSERT INTO teacher_class(teacher_id, class_id) VALUES (1, 1), (2, 1), (3, 2), (4, 2), (5, 3), (1, 3), (2, 4); ``` ## 多表查询 1. 直接查询，默认做`笛卡尔积`，会展示出两个表中所有数据的组合。 > SELECT * FROM student, class; 2. 根据外键查询，消除无效的数据 > SELECT * FROM student WHERE student.class_id = class.id; ```sql CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '班级名' ) COMMENT '班级表'; CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名', class_id INT COMMENT '班级ID', -- 添加外键 CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id) ) COMMENT '学生表'; INSERT INTO class(name) VALUES ('一班'), ('二班'), ('三班'), ('四班'), ('五班'); INSERT INTO student(name, class_id) VALUES ('李白飞', 1), ('孔勇', 2), ('韩芬怡', 3), ('薛云', 4), ('阎菊媛', 5), ('郝凤嘉', 1), ('蔡彪坚', 2), ('史世', 3), ('杜德', 4), ('余东', 5); -- 直接查询两个表的数据，不加任何条件，默认做笛卡尔积，五个班级，10个人，一共 5 * 10 = 50 条数据 SELECT * FROM student, class; -- 使用外键，消除无效的数据 SELECT * FROM student, class WHERE student.class_id = class.id; ``` ## 连接查询 连接查询：查询两个表共有的数据 | 连接类型 | 作用 | | --- | --- | | 内连接 | 查询两个表共有的数据 | | 左外连接 | 查询左表所有数据，右表有数据则显示，没有则显示 NULL | | 右外连接 | 查询右表所有数据，左表有数据则显示，没有则显示 NULL | | 自连接 | 查询自己表中的数据，并且和自身进行比较 | ## 内连接 内连接：查询两个表共有的数据 ### 隐式连接 > SELECT 字段列表 FROM 表1, 表2 WHERE 连接条件/筛选条件; ### 显式连接 > SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 筛选条件 WHERE 筛选条件; ### 内连接示例 ```sql -- 1. 查询所有学生的姓名和班级名称 -- 隐式连接 SELECT student.name, class.name FROM student, class WHERE student.class_id = class.id; -- 显式连接 SELECT student.name, class.name FROM student INNER JOIN class ON student.class_id = class.id; ``` ## 外连接 ### 左外连接 左外连接：查询左表所有数据，右表有数据则显示，没有则显示 NULL > SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 筛选条件 WHERE 筛选条件; ### 右外连接 右外连接：查询右表所有数据，左表有数据则显示，没有则显示 NULL > SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 筛选条件 WHERE 筛选条件; ### 外连接示例 ```sql INSERT INTO class(name) VALUES ('一班'), ('二班'), ('三班'), ('四班'), ('五班'), ('六班'), ('七班') INSERT INTO student(name, class_id) VALUES ('李白飞', 1), ('孔勇', 2), ('韩芬怡', 3), ('薛云', 4), ('阎菊媛', 5), ('郝凤嘉', 1), ('蔡彪坚', 2), ('史世', NULL), ('杜德', 4), ('余东', NULL); -- 1. 使用内连接查询 -- 因为 史世 和 余东 的 class_id 为 NULL，所以使用 内连接 查询时，他们俩人的信息并不会被检索出来 SELECT * FROM student JOIN class ON class_id = class.id; -- 2. 使用外连接查询 -- 左外连接会查询出 主表 的全部数据 -- 若右表有满足条件的数据则显示，没有则显示 NULL -- 因此 史世 和 余东 两个人会查出，但是他们的 class_id、class.id、class.name 都是 NULL SELECT * FROM student LEFT JOIN class ON class_id = class.id; -- 右外连接会查询出 右表 的全部数据 -- 若主表有满足条件的数据则显示，没有则显示 NULL SELECT * FROM student RIGHT JOIN class ON class_id = class.id; -- 右连接可以改成左连接 SELECT * FROM class LEFT JOIN student ON class_id = class.id; ``` ## 自连接 自连接：查询自己表中的数据，并且和自身进行比较 > SELECT 字段列表 FROM 表1 AS 别名1 JOIN 表1 AS 别名2 ON 筛选条件 WHERE 筛选条件; ```sql CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE COMMENT '姓名', leader_id INT COMMENT '领导ID' ) COMMENT '员工表'; INSERT INTO employee(name, leader_id) VALUES ('李白飞', 8), ('孔勇', 10), ('韩芬怡', 8), ('薛云', 10), ('阎菊媛', 8), ('郝凤嘉', 10), ('蔡彪坚', 8), ('史世', NULL), ('杜德', 10), ('余东', NULL); -- 1. 查询全部员工和他对应的 leader 名 SELECT e1.name, e2.name FROM employee e1 LEFT JOIN employee e2 ON e1.leader_id = e2.id; ``` ## 联合查询 联合查询：将多次查询的结果进行合并，关键字：`UNION` 、 `UNION ALL` > 查询语句1 UNION [ALL] 查询语句2; 查询语句1 和 查询语句2 的结果集必须相同，即`字段个数相同`，`字段类型相同`。 ```sql -- 1. 查询出 年龄 低于 24 的员工 SELECT * FROM employee WHERE age < 24; -- 2. 查询 薪资 低于 5000 的员工 SELECT * FROM employee WHERE salary < 5000; -- 3. 查询出 年龄 低于 24 和 薪资 低于 5000 的员工 -- UNION ALL 将两次查询结果直接合并会有重复的部分 SELECT * FROM employee WHERE age < 24 UNION ALL SELECT * FROM employee WHERE salary < 5000; -- UNION 将两次查询结果合并后去重 SELECT * FROM employee WHERE age < 24 UNION SELECT * FROM employee WHERE salary < 5000; ``` ## 子查询 子查询：嵌套查询，嵌套查询的查询结果作为外查询的筛选条件 根据子查询的结果，可将子查询分为三类： 1. 标量子查询：即子查询的返回结果为`一个值` 2. 列子查询：即子查询的返回结果为`一列` 3. 行子查询：即子查询的返回结果为`一行` 4. 表子查询：即子查询的返回结果为`一个表（多行多列）` ### 标量子查询 ```sql -- 1. 查询 一班 的全部学生 SELECT * FROM student WHERE class_id = (SELECT id FROM class WHERE class.name = '一班'); -- 2. 查询比平均薪资低的所有员工 SELECT * FROM employee WHERE salary < (SELECT AVG(salary) FROM employee); ``` ### 列子查询 | 操作符 | 描述 | |:----|:----| | IN | IN 运算符用于判断某个值是否在指定的列表中。 | | NOT IN | NOT IN 运算符用于判断某个值是否不在指定的列表中。 | | ANY | ANY 运算符用于判断某个值是否满足给定条件的任意一个值。 | | SOME | SOME 运算符用于判断某个值是否满足给定条件的任意一个值。 | | ALL | ALL 运算符用于判断某个值是否满足给定条件的所有值。 | ```sql -- 1. 查询 市场部 和 营业部 的全体员工 SELECT * FROM employee WHERE department_id IN (SELECT id FROM department WHERE department.name IN ('市场部', '营业部')); -- 2. 查询出薪资比 技术部 全体员工都高的员工 SELECT * FROM employee WHERE salary > ALL (SELECT salary FROM employee WHERE department_id IN (SELECT department.id FROM department WHERE department.name = '技术部')); -- 2. 查询出薪资比 技术部 任意员工高的员工 SELECT * FROM employee WHERE salary > SOME (SELECT salary FROM employee WHERE department_id IN (SELECT department.id FROM department WHERE department.name = '技术部')); -- ANY 效果同 SOME SELECT * FROM employee WHERE salary > ANY (SELECT salary FROM employee WHERE department_id IN (SELECT department.id FROM department WHERE department.name = '技术部')); ``` ### 行子查询 ```sql -- 1.查询出和 李白飞 相同领导，相同薪资的员工 SELECT * FROM employee WHERE (salary, leader_id) = (SELECT salary, leader_id FROM employee WHERE name = '李白飞'); ``` ### 表子查询 ```sql -- 1. 查询出与 薛云 和 韩芬怡 两个人相同部门，相同薪资的员工 SELECT * FROM employee WHERE (salary, department_id) IN (SELECT salary, department_id FROM employee WHERE name = '薛云' OR name = '韩芬怡'); -- 2. 查询入职时间在 2025-10-02 之后入职的员工及其部门名 SELECT * FROM (SELECT * FROM employee WHERE join_date > '2025-10-02') AS e1 LEFT JOIN department ON e1.department_id = department.id; ``` ## 综合练习 ```sql -- 1. 查询员工的姓名、年龄、职位、部门信息 SELECT employee.name, employee.age, employee.job, department.name FROM employee, department WHERE department_id = department.id; -- 2. 查询年龄小于 24 的员工的姓名、年龄、职位、部门信息(没有部门就不查询出来) SELECT employee.name, employee.age, employee.job, department.name FROM employee INNER JOIN department ON department_id = department.id WHERE age < 24; -- 3. 查询部门信息（该部门下必须有员工） SELECT * FROM department WHERE (SELECT COUNT(department_id) FROM employee WHERE department_id = department.id GROUP BY department_id) > 0; -- 4. 查询年龄小于 24 的员工的姓名、年龄、职位、部门信息(没有部门也要查询出来) SELECT employee.name, employee.age, employee.job, department.name FROM employee LEFT OUTER JOIN department ON department_id = department.id WHERE age < 24; -- 5. 查询所有员工的薪资等级 SELECT name, salary, grade, min, max FROM employee INNER JOIN salary_grade ON salary BETWEEN min AND max; -- 6. 查询技术部员工的薪资等级 SELECT employee.name, salary, department.name, grade, min, max FROM employee INNER JOIN department ON department_id = department.id AND department.name = '技术部' INNER JOIN salary_grade ON salary BETWEEN min AND max; -- 7. 查询技术部员工的平均薪资 SELECT AVG(employee.salary) FROM employee INNER JOIN department ON department_id = department.id AND department.name = '技术部'; -- 8. 查询出薪资比 孔勇 高的员工信息 SELECT e1.* FROM employee e1 WHERE e1.salary > (SELECT e2.salary FROM employee e2 WHERE e2.name = '孔勇'); -- 9. 查询出比平均薪资高的员工信息 SELECT e1.* FROM employee e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employee e2); -- 10. 查询出低于本部门平均工资的员工 SELECT e1.* FROM employee e1 WHERE salary < (SELECT AVG(salary) FROM employee e2 WHERE e2.department_id = e1.department_id); -- 11. 查询部门信息以及部门人数 SELECT d1.*, COUNT(e1.id) FROM department d1 LEFT OUTER JOIN employee e1 ON d1.id = e1.department_id GROUP BY d1.id; -- 12. 查询出所有老师的课程情况 SELECT teacher.name, class.name FROM teacher_class LEFT JOIN teacher ON teacher_class.teacher_id = teacher.id LEFT JOIN class ON teacher_class.class_id = class.id; ```
MySQL 约束
## 约束 约束：作用在表中的字段上，保证字段的数据满足要求，用于保证数据的正确型、有效性、安全性。 ## 分类 | 约束 | 作用 | 关键字| | --- | --- | --- | | 主键 | 该值必须唯一、不重复、不能为 NULL | PRIMARY KEY | | 外键 | 该值必须在指定表中存在 | FOREIGN KEY | | 唯一 | 该值必须唯一、不重复 | UNIQUE | | 非空 | 该值不能为空 | NOT NULL | | 默认值 | 若为指定该值，则使用默认值 | DEFAULT | | 检查 | 检查字段的值 | CHECK | ## 约束代码示例 ```sql CREATE TABLE users ( -- 主键，并且自增 id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', -- 唯一，并且不能为空 name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名', -- 检查，年龄必须大于 0 且小于 120 age INT CHECK ( age > 0 AND age < 120) COMMENT '年龄', -- 默认值，默认状态为 1 status CHAR(1) DEFAULT 1 COMMENT '状态', -- 无约束 gender CHAR(1) COMMENT '性别' ) COMMENT '用户表'; ``` ## 外键约束 外键约束只发生在表与表之间，我们可以指定一个字段的值必须存在于另一个表中的某个字段中。比如：现在有一个班级表和学生表，学生表中有一个 `class_id` 字段，用于保存学生所属的班级的 ID，那么 `class_id` 字段就可以设置外键约束，保证 `class_id` 字段保存的班级 ID 必须存在于班级表中的 `id` 字段中。 ## 添加/修改外键约束 - 建表时添加外键约束 ```sql CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名', class_id INT COMMENT '班级ID', CONSTRAINT class_id_keys FOREIGN KEY (id) REFERENCES class (id) ) COMMENT '学生表'; ``` - 修改表结构时添加外键约束 ```sql ALTER TABLE student ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id); ``` - 删除外键约束 ```sql ALTER TABLE student DROP FOREIGN KEY fk_student_class_id; ``` ## 外键约束示例 ```sql CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名', class_id INT COMMENT '班级ID' ) COMMENT '学生表'; CREATE TABLE class ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', name VARCHAR(10) UNIQUE NOT NULL COMMENT '班级名' ) COMMENT '班级表'; INSERT INTO class(name) VALUES ('一班'), ('二班'), ('三班'), ('四班'), ('五班'); -- 没有约束，虽然没有 六班 但是 class_id 为 6 的数据仍可插入 INSERT INTO student(name, class_id) VALUES ('Tom1', 6); -- 添加约束 -- 1. 建表时 # CREATE TABLE student # ( # id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID', # name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名', # class_id INT COMMENT '班级ID', # CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class (id) [删除/更新规则] # ) COMMENT '学生表'; -- 2. 建表后修改 ALTER TABLE student ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id) [删除/更新规则]; -- 再次添加数据时，就会检查 class_id 的值在 class 表中的 id 字段中是否存在 -- 报错：Cannot add or update a child row: a foreign key constraint fails (`mysql_study`.`student`, CONSTRAINT `fk_student_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)) INSERT INTO student(name, class_id) VALUES ('Tom2', 6); -- 只有存在才能添加 INSERT INTO student(name, class_id) VALUES ('Tom3', 5); -- 因为 student 表中存在 class_id 为 5 的数据 -- 所以当我们要删除 class 表中 id 为 5 的数据时，同样也会报错 -- 报错：Cannot delete or update a parent row: a foreign key constraint fails (`mysql_study`.`student`, CONSTRAINT `fk_student_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)) DELETE FROM class WHERE id = 5; -- 删除外键 ALTER TABLE student DROP CONSTRAINT fk_student_class_id; ``` ## 外键约束删除规则 | 删除规则 | 作用 | | --- | --- | | NO ACTION | 默认值，当删除/修改父表的字段值时，会先检查该字段值是否存在外键，如果有则不允许操作| | RESTRICT | 同 NO ACTION | | CASCADE | 当删除/修改父表字段值时，会同步删除/修改子表字段值 | | SET NULL | 当删除/修改父表字段值时，会设置子表字段值为 NULL | | SET DEFAULT | 当删除/修改父表字段值时，会设置子表字段值为默认值(InnoDB 不支持) | ## 外键约束删除规则示例 ```sql -- 1. 添加外键并制定规则 ALTER TABLE student ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id) ON UPDATE CASCADE ON DELETE CASCADE; -- 更新父表数据，子表也会被修改 UPDATE class SET id = 10 WHERE id = 5; -- 删除父表数据，子表也会被删除 DELETE FROM class WHERE id = 10; -- 2. 添加外键并制定规则 ALTER TABLE student ADD CONSTRAINT fk_student_class_id FOREIGN KEY (class_id) REFERENCES class (id) ON UPDATE SET NULL ON DELETE SET NULL; -- 删除父表的数据，子表中对应的字段值会被设置为 NULL DELETE FROM class WHERE id = 5; ```
MySQL 函数
## 函数 函数是一段已经被编写好，可以直接使用的代码片段。MySQL 中内置了很多的函数，我们可以使用这些函数来完成一些简单的任务。 ## 字符串函数 ### 常用字符串函数 | 函数 | 描述 | | --- | --- | | CONCAT(str1, str2, ...) | 连接字符串 | | LOWER(str) | 将字符串转换为小写 | | UPPER(str) | 将字符串转换为大写 | | LPAD(str, len, pad_str) | 左填充字符串 | | RPAD(str, len, pad_str) | 右填充字符串 | | TRIM(str) | 去掉字符串两侧的空格 | | SUBSTRING(str, start, len) | 截取字符串 | | REPLACE(str, old_str, new_str) | 替换字符串 | | LENGTH(str) | 返回字符串的长度 | | LOCATE(substr, str) | 返回子字符串在字符串中的位置 | | INSTR(str, substr) | 检测子字符串在字符串中的位置 | | REVERSE(str) | 反转字符串 | ### 字符串函数示例 ```sql -- 1. 字符串拼接 SELECT CONCAT('Hello', 'MySQL'); -- Hello MySQL -- 2. 转小写 SELECT LOWER('HELLO'); -- hello -- 3. 转大写 SELECT UPPER('hello'); -- HELLO -- 4. 左填充 SELECT LPAD('1', 5, '0'); -- 00001 -- 5. 右填充 SELECT RPAD('1', 5, '0'); -- 10000 -- 6. 去除空格 -- 只去除两侧的空格 SELECT TRIM(' Hello MySQL '); -- Hello MySQL -- 7. 截取 -- 索引从 1 开始，从 2 开始，截取 4 个 SELECT SUBSTRING('Hello MySQL', 2, 4); -- ello -- 左截取 SELECT LEFT('Hello MySQL', 5); -- Hello -- 右截取 SELECT RIGHT('Hello MySQL', 5); -- MySQL -- 8. 替换 SELECT REPLACE('Hello MySQL', 'l', 'A'); -- HeAAo MySQL -- 9. 字符串长度 SELECT LENGTH('Hello MySQL'); -- 11 -- 10. 查询字符在字符串中位置 SELECT LOCATE('l', 'Hello MySQL'); -- 3 SELECT INSTR('Hello MySQL', 'l'); -- 3 -- 11. 反转字符串 SELECT REVERSE('Hello MySQL'); -- LQSyM olleH ``` ### 字符串函数练习 ```sql -- 1. 更新员工工号，不足五位用 0 补足 UPDATE tb_employee SET work_no = LPAD(work_no, 5, '0'); ``` ## 数值函数 ### 常用数值函数 | 函数 | 描述 | | --- | --- | | CELL(x) | 向上取整 | | FLOOR(x) | 向下取整 | | ROUND(x, d) | 四舍五入 | | MOD(x, y) | 返回 x 除以 y 的余数 | | RAND() | 返回一个 0 ~ 1 的随机数 | | ABS(x) | 返回 x 的绝对值 | | POW(x, y) | 返回 x 的 y 次方 | | SQRT(x) | 返回 x 的平方根 | | LOG(x) | 返回 x 的自然对数 | ### 数值函数示例 ```SQL -- 1. 向上取整 SELECT CEIL(1.1); -- 2 -- 2. 向下取整 SELECT FLOOR(1.9); -- 1 -- 3. 四舍五入 SELECT ROUND(1.4); -- 1 SELECT ROUND(1.5); -- 2 -- 4. 加减乘除取余 SELECT 5 + 3; SELECT 5 - 3; SELECT 5 * 3; SELECT 5 / 3; SELECT 5 % 3; SELECT MOD(5, 3); -- 5. 随机数 SELECT RAND(); -- 6. 绝对值 SELECT ABS(-1); -- 1 -- 7. 幂次 SELECT POW(3, 2); -- 9 -- 8. 平方根 SELECT SQRT(9); -- 3 -- 9. 自然对数 SELECT LOG2(4); -- 2 ``` ### 数值函数练习 ```sql -- 1. 生成 6 位的随机数值 SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0'); ``` ## 日期函数 ### 常用日期函数 | 函数 | 描述 | | --- | --- | | CURDATE() | 返回当前日期 | | CURTIME() | 返回当前时间 | | NOW() | 返回当前日期和时间 | | YEAR(date) | 获取日期的年份 | | MONTH(date) | 获取日期的月份 | | DAY(date) | 获取日期的日 | | HOUR(time) | 获取时间中的小时 | | MINUTE(time) | 获取时间中的分钟 | | SECOND(time) | 获取时间中的秒钟 | | DATE_ADD(date, INTERVAL x UNIT) | 添加时间间隔 | | DATE_SUB(date, INTERVAL x UNIT) | 减去时间间隔 | | DATE_FORMAT(date, format) | 格式化日期 | | DATEDIFF(date1, date2) | 计算两个日期之间的天数 | ### 日期函数示例 ```sql -- 1. 当前日期 SELECT CURDATE(); SELECT CURRENT_DATE; -- 2. 当前时间 SELECT CURTIME(); SELECT CURRENT_TIME; -- 3. 当前日期+时间 SELECT NOW(); -- 4. 年月日时分秒 SELECT YEAR(CURRENT_DATE); SELECT MONTH(CURRENT_DATE); SELECT DAY(CURRENT_DATE); SELECT HOUR(CURRENT_TIME); SELECT SECOND(CURRENT_TIME); SELECT MINUTE(CURRENT_TIME); -- 5. 时间增减 SELECT DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY); SELECT DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH); SELECT DATE_SUB(CURRENT_TIME, INTERVAL 20 HOUR); SELECT DATE_SUB(CURRENT_DATE, INTERVAL 20 YEAR); -- 6. 格式化 SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s'); -- 7. 想差天数 SELECT DATEDIFF(CURRENT_DATE, '2000-11-28'); ``` ### 日期函数练习 ```sql -- 1. 根据入职时间计算天数，并按天数降序排序 SELECT name, DATEDIFF(CURRENT_DATE, join_date) AS days FROM tb_employee ORDER BY days DESC; ``` ## 流程函数 ### 常用流程函数 | 函数 | 功能 | | --- | --- | | IF(expr1, expr2, expr3) | 如果expr1为真，则返回expr2，否则返回expr3 | | IFNULL(expr1, expr2) | 如果expr1为NULL，则返回expr2，否则返回expr1 | | CASE WHEN expr1 THEN result1 [WHEN expr2 THEN result2 ...] [ELSE result_final] END | 根据条件返回相应的结果，相当于 if...else... | | CASE expr [WHEN value THEN result [WHEN value THEN result ...]] [ELSE result_final] END | 根据表达式的值返回相应的结果，相当于 switch...case... | ### 常用流程函数示例 ```sql -- 1, IF SELECT IF(age > 18, '成年', '未成年') FROM tb_employee; -- 2. IFNULL SELECT IFNULL(id_card, '没有身份证') FROM tb_employee; -- 3. CASE SELECT CASE WHEN work_address = '北京' THEN CONCAT(work_address, '-首都') WHEN work_address IN ('上海', '深圳') THEN CONCAT(work_address, '-一线城市') ELSE work_address END FROM tb_employee; SELECT CASE work_address WHEN '北京' THEN '首都' WHEN '上海' THEN '一线城市' WHEN '深圳' THEN '一线城市' ELSE work_address END FROM tb_employee; ``` ### 流程函数练习 ```sql INSERT INTO score VALUES (1, '林洁', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (2, '邱慧', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (3, '蔡淑', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (4, '孟巧', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (5, '贺云', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (6, '程良', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (7, '白兰', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (8, '贾保', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (9, '韩欣霞', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); INSERT INTO score VALUES (10, '汤静珍', CEIL(RAND() * 100), CEIL(RAND() * 100), CEIL(RAND() * 100)); -- 1, 根据学生成绩对学生进行评价 -- x > 90 优秀 -- 90 > x > 80 良好 -- 80 > x > 70 一般 -- 70 > x > 60 合格 -- 60 > x 不合格 SELECT name, CASE WHEN math > 90 THEN '优秀' WHEN math BETWEEN 80 AND 90 THEN '良好' WHEN math BETWEEN 70 AND 80 THEN '一般' WHEN math BETWEEN 60 AND 70 THEN '合格' ELSE '不合格' END AS '数学成绩', CASE WHEN english > 90 THEN '优秀' WHEN english BETWEEN 80 AND 90 THEN '良好' WHEN english BETWEEN 70 AND 80 THEN '一般' WHEN english BETWEEN 60 AND 70 THEN '合格' ELSE '不合格' END AS '英语成绩', CASE WHEN chinese > 90 THEN '优秀' WHEN chinese BETWEEN 80 AND 90 THEN '良好' WHEN chinese BETWEEN 70 AND 80 THEN '一般' WHEN chinese BETWEEN 60 AND 70 THEN '合格' ELSE '不合格' END AS '语文成绩' FROM score; ```
MySQL SQL
## SQL 通用语法 1. SQL 语句可以单行或多行编写，以`分号`结尾。 2. SQL 中应使用空格来格式化语句，提高可读性。 3. SQL 语句大小写不敏感，但建议使用大写。 4. 注释： - 单行注释：`--`、`#` - 多行注释：`/* */` ## SQL 分类 | 分类 | 全称 | 描述 | | --- | --- | --- | | DDL | Data Definition Language | 数据定义语言，用于`定义数据库`对象，如表、索引、视图、存储过程、函数等 | | DML | Data Manipulation Language | 数据操作语言，用于对数据库中的数据进行`增删改` | | DQL | Data Query Language | 数据查询语言，用于`查询`数据库中的数据 | | DCL | Data Control Language | 数据控制语言，用于`控制数据库的访问权限` | ## DDL ### 操作数据库 | 功能| SQL 语法| 描述| | --- | --- | --- | |查询|SHOW DATABASES;|查询全部数据库| |查询|SELECT DATABASE();|查询当前数据库| |创建|CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER SET 字符集] [COLLATE 排序规则];|[如果数据库不存在]，则创建数据库，[并指定字符集]和[排序规则]| |删除|DROP DATABASE [IF EXISTS] 数据库名;|[如果数据库存在]，则删除数据库| |使用|USE 数据库名;|使用数据库/切换到指定数据库| ```sql MySQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | MySQL | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) ``` ```sql MySQL> CREATE DATABASE MySQL_study; Query OK, 1 row affected (0.02 sec) MySQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | MySQL | | MySQL_study | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) -- 不能创建同名的数据库 MySQL> CREATE DATABASE MySQL_study; -- ERROR 1007 (HY000): Can't create database 'MySQL_study'; database exists -- 使用 IF NOT EXISTS 创建数据库更加安全，只有当数据库不存在时才创建 MySQL> CREATE DATABASE IF NOT EXISTS MySQL_study; Query OK, 1 row affected, 1 warning (0.00 sec) -- 创建数据库，并指定字符集 -- 推荐使用 utf8mb4，不要使用 utf8mb3，utf8mb3 最大长度是 3 个字节，utf8mb4 最大长度是 4 个字节，能兼容更多的字符 MySQL> CREATE DATABASE IF NOT EXISTS MySQL_study01 DEFAULT CHARACTER SET utf8mb4; Query OK, 1 row affected (0.00 sec) ``` ```sql MySQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | MySQL | | MySQL_study | | MySQL_study01 | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) -- 使用 IF EXISTS 安全删除数据库 MySQL> DROP DATABASE IF EXISTS MySQL_study01; Query OK, 0 rows affected (0.00 sec) MySQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | MySQL | | MySQL_study | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) ``` ```sql -- 切换至 MySQL_study 数据库 MySQL> USE MySQL_study; Database changed -- 显示当前数据库 MySQL> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | MySQL_study | +-------------+ 1 row in set (0.00 sec) ``` ### 操作表 | 功能| SQL 语法| 描述| | --- | --- | --- | |查询|SHOW TABLES;|查询当前数据库中的所有表| |查询|DESC 表名;|查询表结构| |查询|SHOW CREATE TABLE 表名;|查询创建表语句| ```sql -- 数据库中没有表 MySQL> SHOW TABLES; Empty set (0.00 sec)、 -- 切换至 sys 数据库再查询 MySQL> USE sys; Database changed MySQL> SHOW TABLES; +-----------------------------------------------+ | Tables_in_sys | +-----------------------------------------------+ | host_summary | -- ...... | x$waits_global_by_latency | +-----------------------------------------------+ 101 rows in set (0.01 sec) ``` ### 创建表 建表语句格式： ```sql CREATE TABLE [表名]( 字段1 字段类型 [注释], ...... 字段n 字段类型 [注释] )[注释]; ``` ```sql -- 切换数据库 MySQL> USE MySQL_study; Database changed -- 查询数据库中的表 MySQL> SHOW TABLES; Empty set (0.00 sec) -- 创建表 MySQL> CREATE TABLE tb_user( -> id INT COMMENT '编号', -> name VARCHAR(32) COMMENT '姓名', -> age INT COMMENT '年龄', -> gender VARCHAR(1) COMMENT '性别' -> ) COMMENT '用户表'; Query OK, 0 rows affected (0.02 sec) -- 再次查询数据库中的表 MySQL> SHOW TABLES; +-----------------------+ | Tables_in_MySQL_study | +-----------------------+ | tb_user | +-----------------------+ 1 row in set (0.00 sec) ``` ```sql -- 查询表结构 MySQL> DESC tb_user; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | age | int | YES | | NULL | | | gender | varchar(1) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) ``` ```sql -- 查看建表语句 MySQL> SHOW CREATE TABLE tb_user; +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_user | CREATE TABLE `tb_user` ( `id` int DEFAULT NULL COMMENT '编号', `name` varchar(32) DEFAULT NULL COMMENT '姓名', `age` int DEFAULT NULL COMMENT '年龄', `gender` varchar(1) DEFAULT NULL COMMENT '性别' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -- 存储引擎：ENGINE=InnoDB -- 默认字符编码：CHARSET=utf8mb4 -- 默认排序规则：COLLATE=utf8mb4_0900_ai_ci ``` ### 修改表 | 功能| 语法| 描述| | --- | --- | --- | | 添加| ALTER TABLE 表名 ADD 列名 新数据类型 [COMMENT 注释] [约束]| 添加新的列| | 修改| ALTER TABLE 表名 MODIFY 列名 新数据类型| 修改列的数据类型| | 修改| ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型 [COMMENT 注释] [约束]| 修改列的名称和数据类型| | 删除| ALTER TABLE 表名 DROP 列名| 删除列| | 修改| ALTER TABLE 表名 RENAME TO 新表名| 修改表名| | 删除| DROP TABLE [IF EXISTS] 表名;| 删除表| | 删除| TRUNCATE TABLE 表名;| 清空表| ```sql MySQL> ALTER TABLE tb_employee ADD nick_name VARCHAR(10) COMMENT '昵称'; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL> DESC tb_employee; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | work_no | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | gender | char(1) | YES | | NULL | | | age | tinyint unsigned | YES | | NULL | | | id_card | char(18) | YES | | NULL | | | join_date | date | YES | | NULL | | | nick_name | varchar(10) | YES | | NULL | | +-----------+------------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) ``` ```sql MySQL> ALTER TABLE tb_employee CHANGE nick_name user_name VARCHAR(20) COMMENT '用户名'; Query OK, 0 rows affected (0.73 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL> DESC tb_employee; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | work_no | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | gender | char(1) | YES | | NULL | | | age | tinyint unsigned | YES | | NULL | | | id_card | char(18) | YES | | NULL | | | join_date | date | YES | | NULL | | | user_name | varchar(20) | YES | | NULL | | +-----------+------------------+------+-----+---------+-------+ ``` ```sql MySQL> ALTER TABLE tb_employee DROP user_name; Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL> DESC tb_employee; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | work_no | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | gender | char(1) | YES | | NULL | | | age | tinyint unsigned | YES | | NULL | | | id_card | char(18) | YES | | NULL | | | join_date | date | YES | | NULL | | +-----------+------------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) ``` ```sql MySQL> ALTER TABLE tb_employee RENAME TO tb_empl; Query OK, 0 rows affected (0.48 sec) MySQL> SHOW TABLES; +-----------------------+ | Tables_in_MySQL_study | +-----------------------+ | tb_empl | | tb_user | +-----------------------+ 2 rows in set (0.00 sec) ``` ```sql -- 删除表 MySQL> DROP TABLE IF EXISTS tb_user; Query OK, 0 rows affected (0.05 sec) MySQL> SHOW TABLES; +-----------------------+ | Tables_in_MySQL_study | +-----------------------+ | tb_empl | +-----------------------+ 1 row in set (0.00 sec) -- 清空表 MySQL> TRUNCATE TABLE tb_empl; Query OK, 0 rows affected (0.01 sec) MySQL> SHOW TABLES; +-----------------------+ | Tables_in_MySQL_study | +-----------------------+ | tb_empl | +-----------------------+ 1 row in set (0.00 sec) ``` ## 数据类型 ### 数值 | 类型|大小|有符号(SIGNED)范围|无符号(UNSIGNED)范围|描述| | --- | --- | --- | --- | --- | |TINYINT|1字节|-128~127|0~255| 8位整数| |SMALLINT|2字节|-32768~32767|0~65535| 16位整数| |MEDIUMINT|3字节|-8388608~8388607|0~16777215| 24位整数| |INT / INTEGER|4字节|-2147483648~2147483647|0~4294967295| 32位整数| |BIGINT|8字节|-9223372036854775808~9223372036854775807|0~18446744073709551615| 64位整数| |FLOAT|4字节|-3.402823466E+38~3.402823466E+38|0~3.402823466E+38| 单精度浮点数| |DOUBLE|8字节|-1.7976931348623157E+308~1.7976931348623157E+308|0~1.7976931348623157E+308| 双精度浮点数| |DECIMAL(M,D)|可变长度|-10^(M-D)~10^(M-D)|0~10^M| 高精度浮点数| ### 字符串 | 类型|大小|描述| | --- | --- |---| |CHAR| 0-255字节|定长字符串| |VARCHAR| 0-65535字节|变长字符串| |TINYBLOB| 0-255字节|二进制字符串| |TINYTEXT| 0-255字节|文本字符串| |BLOB| 0-65535字节|二进制字符串| |TEXT| 0-65535字节|文本字符串| |MEDIUMBLOB| 0-16777215字节|二进制字符串| |MEDIUMTEXT| 0-16777215字节|文本字符串| |LONGBLOB| 0-4294967295字节|二进制字符串| |LONGTEXT| 0-4294967295字节|文本字符串| ### 日期时间 | 类型|大小|范围|格式|描述| | --- | --- |---|---|----| |DATE|3字节|1000-9999年1-12月1-31日|YYYY-MM-DD|日期| |TIME|3字节|00:00:00-23:59:59|HH:MM:SS|时间| |YEAR|1字节|1901-2155|YYYY|年| |DATETIME|8字节|1000-9999年1-12月1-31日 00:00:00-23:59:59|YYYY-MM-DD HH:MM:SS|日期和时间| |TIMESTAMP|4字节|1970-01-01 00:00:01到2038-01-19 03:14:07|YYYY-MM-DD HH:MM:SS|时间戳| ### 练习 ```sql MySQL> CREATE TABLE IF NOT EXISTS tb_employee( -> id INT COMMENT '员工编号', -> work_no VARCHAR(10) COMMENT '工号', -> name VARCHAR(10) COMMENT '姓名', -> gender CHAR(1) COMMENT '性别', -> age TINYINT UNSIGNED COMMENT '年龄', -> id_card CHAR(18) COMMENT '身份证号', -> join_date DATE COMMENT '入职日期' -> ) COMMENT '员工表'; Query OK, 0 rows affected (0.01 sec) MySQL> SHOW TABLES; +-----------------------+ | Tables_in_MySQL_study | +-----------------------+ | tb_employee | | tb_user | +-----------------------+ 2 rows in set (0.00 sec) MySQL> DESC tb_employee; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | work_no | varchar(10) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | gender | char(1) | YES | | NULL | | | age | tinyint unsigned | YES | | NULL | | | id_card | char(18) | YES | | NULL | | | join_date | date | YES | | NULL | | +-----------+------------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) ``` ## 图形化界面工具 1. Sqlyog 2. Navicat 3. [DataGrip](https://www.jetbrains.com/zh-cn/datagrip/)(推荐) ### DataGrip 1. 连接数据库：+ -> Data Source -> MySQL -> 填写连接信息 -> 下载驱动 -> 测试连接 -> OK 2. 创建数据库：右键连接 -> New -> Schema -> 填写数据库名称 -> OK 3. 创建表结构：右键数据库 -> New -> Table -> 填写表结构 -> Execute 4. 修改表结构：右键表 -> Modify Table -> 修改表结构 -> Execute ## DML DML: Data Manipulation Language，用于对数据库中表的数据进行操作，包括`增加`、`删除`、`修改`，分别对应三个命令：`INSERT`、`DELETE`、`UPDATE` ### INSERT 1. 添加指定字段 > `INSERT INTO 表名(字段1，字段2，...) VALUES (值1, 值2, ...);` 2. 添加所有字段 > `INSERT INTO 表名 VALUES (值1, 值2, ...);` 3. 添加多行数据： >`INSERT INTO 表名 VALUES (值1, 值2, ...),(值1, 值2, ...),(值1, 值2, ...);` #### 注意 1. 添加数据时，`值`的个数和`字段`的个数、顺序一致 2. 字符串和日期类型的值需要用`单引号`括起来 3. 值的大小必须在字段的大小范围内 #### 示例 ```sql -- 指定字段添加数据 INSERT INTO tb_employee(id, work_no, name, gender, age, id_card, join_date) VALUES (1, '1', '张三', '男', 12, '123456789011121314', '20251104'); -- 添加所有字段 INSERT INTO tb_employee VALUES (2, '2', '李四', '男', 13, '123456789011121315', '20251104'); -- 添加多行数据 INSERT INTO tb_employee VALUES (3, '3', '王五', '男', 14, '123456789011121316', '20251104'), (4, '4', '赵六', '男', 15, '123456789011121317', '20251104'), (5, '5', '孙七', '男', 16, '123456789011121318', '20251104'); ``` ### UPDATE `UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2，...... [WHERE 条件];` #### 注意 1. 如果没有`WHERE`条件，则更新所有数据 #### 示例 ```sql -- 将 id 为 1 的员工的年龄 +1 UPDATE tb_employee SET age = age + 1 WHERE id = 1; -- 将 id 为 1 的员工的年龄 +1 并把性别修改为 女 UPDATE tb_employee SET age = age + 1, gender = '女' WHERE id = 1; -- 将所有员工的入职时间修改为 2025-01-01 UPDATE tb_employee SET join_date = '2025-01-01'; ``` ### DELETE `DELETE FROM 表名 [WHERE 条件];` #### 注意 1. 如果没有`WHERE`条件，则删除所有数据 2. 删除语句是删除整条数据，而不是删除字段的值，如果删除字段的值，则需要使用`UPDATE`语句 #### 示例 ```sql -- 删除女员工 DELETE FROM tb_employee WHERE gender = '女'; -- 删除所有员工 DELETE FROM tb_employee; ``` ## DQL DQL: Data Query Language，用于对数据库进行查询，对应命令：`SELECT` ### SELECT `SELECT 字段 FROM 表名 [WHERE 条件] [GROUP BY 分组字段] [HAVING 分组条件] [ORDER BY 排序字段 [ASC | DESC]] [LIMIT 开始索引, 获取数量];` #### 基本查询 1. 查询指定字段 > `SELECT 字段1, 字段2,... FROM 表名;` 2. 查询所有字段 > `SELECT * FROM 表名;` 3. 设置别名 > `SELECT 字段1 AS 别名1, 字段2 AS 别名2,... FROM 表名;` 4. 去重 > `SELECT DISTINCT 字段1, 字段2,... FROM 表名;` #### 基本查询示例 ```sql -- 基本查询 -- 1. 查询指定字段，姓名和年龄 SELECT name, age FROM tb_employee; -- 2. 查询全部字段(尽量不使用，使用 * 号无法直观的看出查询了哪些字段) SELECT * FROM tb_employee; -- 可以直观看出查询了哪些字段 SELECT id, work_no, name, gender, age, id_card, work_address, join_date FROM tb_employee; -- 3. 别名 SELECT work_address AS '工作地址' FROM tb_employee; -- AS 可省略 SELECT work_address '工作地址' FROM tb_employee; -- 4. 去重 SELECT DISTINCT work_address AS '工作地址' FROM tb_employee; ``` #### 条件查询 1. 在基础查询的基础上，使用 `WHRER` 关键字添加条件，多个条件之间使用 `逻辑运算符` 进行连接 > `SELECT 字段 FROM 表名 WHERE 条件列表;` #### 运算符 - 比较运算符 | 运算符 | 描述 | | --- | --- | | = | 等于 | | <> 、!= | 不等于 | | > | 大于 | | < | 小于 | | >= | 大于等于 | | <= | 小于等于 | | BETWEEN ... AND ... | 区间查询 | | IN(...) | 集合查询 | | LIKE 占位符 | 模糊查询(_ 表示任意一个字符，% 表示任意多个字符) | | IS NULL | 空 | - 逻辑运算符 | 运算符 | 描述 | | --- | --- | | AND、&& | 与 | | OR、\|\| | 或 | | NOT、 ! | 非 | #### 条件查询示例 ```sql -- 条件查询 -- 1. 查询年龄为 22 的员工 SELECT * FROM tb_employee WHERE age = 22; -- 2. 查询年龄大于 22 的员工 SELECT * FROM tb_employee WHERE age > 22; -- 3. 查询年龄大于等于 22 的员工 SELECT * FROM tb_employee WHERE age >= 22; -- 4. 查询没有身份证号的员工 SELECT * FROM tb_employee WHERE id_card IS NULL; -- 5. 查询有身份证号的员工 SELECT * FROM tb_employee WHERE id_card IS NOT NULL; -- 6. 查询年龄不是 22 的员工 SELECT * FROM tb_employee WHERE age != 22; SELECT * FROM tb_employee WHERE age <> 22; -- 7. 查询年龄在 22 和 24 之间的员工 SELECT * FROM tb_employee WHERE age >= 22 AND age <= 24; -- 使用 BETWEEN SELECT * FROM tb_employee WHERE AGE BETWEEN 22 AND 24; -- ，BETWEEN 后的值为 最小值，最大值和最小值的位置不可颠倒 SELECT * FROM tb_employee WHERE AGE BETWEEN 24 AND 22; -- 8，查询性别为女且年龄小于 22 的员工 SELECT * FROM tb_employee WHERE gender = '女' AND age < 22; -- 9，查询年龄为 22 或 24 的员工 SELECT * FROM tb_employee WHERE age = 22 OR age = 24; -- 使用 IN SELECT * FROM tb_employee WHERE age IN (22, 24); -- 10, 查询名字是两个字的员工 SELECT * FROM tb_employee WHERE name LIKE '__'; -- 11. 查询身份证号最后一位是 3 的员工 SELECT * FROM tb_employee WHERE id_card LIKE '%3'; -- 一个 _ 匹配一个字符，需要 17 个 SELECT * FROM tb_employee WHERE id_card LIKE '_________________3'; ``` #### 聚合函数 | 函数 | 描述 | | --- | --- | | COUNT(字段) | 计算字段非空的行数 | | SUM(字段) | 计算字段的和 | | AVG(字段) | 计算字段的平均值 | | MAX(字段) | 获取字段的最大值 | | MIN(字段) | 获取字段的最小值 | #### 聚合函数示例 ```sql -- 聚合函数 -- 1. 统计员工个数 -- 使用全部字段统计个数 SELECT COUNT(*) FROM tb_employee; -- 使用 id 统计个数 SELECT COUNT(id) FROM tb_employee; -- 使用 id_card 统计个数，因为有一个员工的 id_card 是 null, 所以不会被统计在内 SELECT COUNT(id_card) FROM tb_employee; -- 2, 求平均年龄 SELECT AVG(age) FROM tb_employee; -- 3. 求最大年龄 SELECT MAX(age) FROM tb_employee; -- 4. 求最小年龄 SELECT MIN(age) FROM tb_employee; -- 5. 求上海员工的年龄和 SELECT SUM(age) FROM tb_employee WHERE work_address = '上海'; ``` #### 分组查询 1. 分组查询就是在基础查询的基础上，使用 `GROUP BY` 分组，多个分组之间使用 `,` 进行连接，且分组后仍可以使用 HAVING 进行条件查询 > `SELECT 字段 FROM 表名 [WHERE 条件] [GROUP BY 分组字段] [HAVING 分组后过滤条件];` 2. WHERE 和 HAVING 的区别 - WHERE：在分组前过滤数据，被过滤的数据不会参与分组，不可用聚合函数 - HAVING：在分组后过滤数据，且可以使用聚合函数 3. 分组查询时，所返回的字段，只能返回`用于分组的字段`和`聚合函数` #### 分组查询示例 ```sql -- 分组查询 -- 1. 统计男、女员工的个数 SELECT gender, COUNT(*) AS '人数' FROM tb_employee GROUP BY gender; -- 2. 统计男、女员工的平均年龄 SELECT gender, AVG(age) AS '平均年龄' FROM tb_employee GROUP BY gender; -- 3. 查询年龄小于 24 的员工，并根据工作地址分组，获取人数大于 1 的工作地址 SELECT work_address, COUNT(work_address) FROM tb_employee WHERE age < 24 GROUP BY work_address HAVING COUNT(work_address) > 1; -- 使用别名 SELECT work_address, COUNT(work_address) AS address_count FROM tb_employee WHERE age < 24 GROUP BY work_address HAVING address_count > 1; ``` #### 排序查询 1. 排序查询就是在基础查询的基础上，使用 `ORDER BY` 排序，多个排序之间使用 `,` 进行连接 > `SELECT 字段 FROM 表名 [WHERE 筛选条件] [ORDER BY 排序字段 [ASC | DESC]];` 2. 排序规则 - ASC：升序(默认) - DESC：降序 3. 排序时要注意该字段的类型，数值类型排序时，会按照数值大小排序，字符串类型排序时，会按照字符串字典序排序，如：`20 > 3`，但是`'20' < '3'` #### 排序查询示例 ```sql -- 排序查询 -- 1. 年龄升序 -- 默认升序，ASC 可省略 SELECT * FROM tb_employee ORDER BY age; -- 降序 SELECT * FROM tb_employee ORDER BY age DESC; -- 2. 根据工号降序 SELECT * FROM tb_employee ORDER BY work_no DESC; -- 3. 先按年龄升序排序，若年龄相同，按照工号降序排序 SELECT * FROM tb_employee ORDER BY age, work_no DESC; ``` #### 分页排序 1. 分页查询就是在基础查询的基础上，使用 `LIMIT` 分页 > `SELECT 字段 FROM 表名 [WHERE 筛选条件] [ORDER BY 排序字段 [ASC | DESC]] [LIMIT 开始索引, 获取数量];` 2. 开始索引 = (当前页 - 1) * 获取数量 3. LIMIT 在其他数据库中不支持 #### 分页排序示例 ```sql -- 分页查询 -- 1. 每页显示 2 条数据，查询第 1 页的数据 -- LIMIT 0, 2 0 可省略 SELECT * FROM tb_employee LIMIT 2; -- 2. 每页显示 2 条数据，查询第 3 页的数据 -- 开始索引 = (当前页 - 1) * 获取数量 = (3 - 1) * 2 = 4 SELECT * FROM tb_employee LIMIT 4, 2; ``` ### 综合练习 ```sql -- 练习 -- 1. 查询年龄为 22 24 的女性员工 SELECT * FROM tb_employee WHERE age IN (22, 24) AND gender = '女'; -- 2. 查询年龄在 22 ~ 24 的男员工 SELECT * FROM tb_employee WHERE age BETWEEN 22 AND 24 AND gender = '男'; -- 3. 统计年龄小于 23 的男、女员工个数 SELECT gender, count(gender) AS '人数' FROM tb_employee WHERE age < 23 GROUP BY gender; -- 4. 查询年龄小于 23 员工姓名和年龄，并按年龄进行升序排序，如果年龄相同，按照工号降序排序 SELECT name, age FROM tb_employee WHERE age < 23 ORDER BY age, work_no DESC; -- 5. 查询性别为女，年龄在 20 ~ 23 之间的前 4 个员工，并按年龄进行升序排序，如果年龄相同，按照工号降序排序 SELECT * FROM tb_employee WHERE gender = '女' AND age BETWEEN 20 AND 23 ORDER BY age, work_no DESC LIMIT 4; ``` ## DCL DCL（Data Control Language）数据控制语言，用于对数据库的权限进行控制 ### 用户管理 1. 查询用户 > USE MySQL; SELECT * FROM user; 2. 创建用户 > CREATE USER '用户名'@'主机' IDENTIFIED BY '密码'; 3. 修改用户密码 > ALTER USER '用户名'@'主机' IDENTIFIED WITH caching_sha2_password BY '密码'; 4. 删除用户 > DROP USER '用户名'@'主机'; ### 用户管理示例 ```sql USE MySQL; SELECT * FROM user; -- 1. 创建用户给 admin，只能在当前主机访问 MySQL -- 该用户只能访问 MySQL，不能访问其他数据库 CREATE USER 'admin'@'localhost' IDENTIFIED BY '123456'; -- 2. 创建用户给 admin1，可以在任意主机访问 MySQL CREATE USER 'admin1'@'%' IDENTIFIED BY '123456'; -- 3. 修改密码 -- MySQL 8.0 使用 caching_sha2_password ALTER USER 'admin1'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; -- 4. 删除用户 DROP USER 'admin1'@'%'; ``` ### 权限控制 | 权限名称 | 权限描述 | | --- | --- | | ALL、ALL PRIVILEGES | 所有权限 | | SELECT | 查询权限 | | INSERT | 插入权限 | | UPDATE | 更新权限 | | DELETE | 删除权限 | | ALTER | 修改权限 | | DROP | 删除权限 | | CREATE | 创建权限 | 1. 查询用户权限 > SHOW GRANTS FOR '用户名'@'主机'; 2. 授权 > GRANT 权限名称 ON 数据库.表 TO '用户名'@'主机'; 3. 撤销授权 > REVOKE 权限名称 ON 数据库.表 FROM '用户名'@'主机'; ### 权限控制示例 ```sql USE MySQL; SELECT * FROM user; -- 1. 查询权限 SHOW GRANTS FOR 'admin'@'localhost'; -- 2. 授权 GRANT ALL ON MySQL_study.tb_employee TO 'admin'@'localhost'; -- 3. 撤销权限 REVOKE ALL ON MySQL_study.tb_employee FROM 'admin'@'localhost'; ```