高性能MySQL
快来分享你的内容吧～
学习笔记-MySQL ORDER BY(排序) 语句
在 MySQL 中，**ORDER BY** 子句用于对查询结果进行排序。以下是详细的语法规则和使用场景： --- ### **基础语法** ```sql SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]; ``` - **ASC**：升序（默认） - **DESC**：降序 --- ### **核心功能** #### 1. **单列排序** ```sql -- 按 age 升序排列（默认 ASC） SELECT name, age FROM employees ORDER BY age; -- 按 salary 降序排列 SELECT * FROM products ORDER BY price DESC; ``` #### 2. **多列排序** ```sql -- 先按 department 升序，同部门按 salary 降序 SELECT name, department, salary FROM employees ORDER BY department ASC, salary DESC; ``` #### 3. **按表达式或函数排序** ```sql -- 按名字长度排序 SELECT name, LENGTH(name) AS len FROM users ORDER BY len DESC; -- 按计算字段排序 SELECT product, (price * stock) AS total_value FROM inventory ORDER BY total_value DESC; ``` #### 4. **自定义排序顺序** 使用 `FIELD()` 函数实现特定顺序： ```sql -- 按 status 自定义顺序：'active' > 'pending' > 'expired' SELECT id, status FROM orders ORDER BY FIELD(status, 'active', 'pending', 'expired'); ``` #### 5. **NULL 值处理** ```sql -- 默认 NULL 在升序中排在最前，可用以下方式调整 SELECT name, commission FROM sales ORDER BY commission IS NULL, commission DESC; -- NULL 值排在最后 ``` --- ### **高级特性** #### 1. **随机排序** ```sql -- 随机取 5 条记录（慎用，性能较差） SELECT * FROM articles ORDER BY RAND() LIMIT 5; ``` #### 2. **中文拼音排序** 需设置字符集和校对规则： ```sql SELECT name FROM employees ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci; ``` #### 3. **动态排序（结合 CASE）** ```sql -- 根据参数动态选择排序字段 SET @sort_by = 'price'; SELECT * FROM products ORDER BY CASE WHEN @sort_by = 'price' THEN price WHEN @sort_by = 'sales' THEN sales END DESC; ``` --- ### **性能优化建议** 1. **索引利用**：为排序字段添加索引（尤其是多列排序时创建组合索引）。 ```sql CREATE INDEX idx_dept_salary ON employees(department, salary); ``` 2. **避免全表扫描**：`WHERE` 条件应尽量缩小数据集。 3. **文件排序（Filesort）**：当无法使用索引时，MySQL 会进行磁盘排序，可通过 `EXPLAIN` 检查是否出现 `Using filesort`。 4. **Limit 优化**：分页时优先筛选后排序： ```sql SELECT * FROM large_table WHERE id > 1000 ORDER BY create_time LIMIT 20; ``` --- ### **典型错误示例** ```sql -- 错误：GROUP BY 后直接排序需注意聚合字段 SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY AVG(salary) DESC; -- 正确写法应在 ORDER BY 中重复聚合函数 ``` --- 通过合理使用 `ORDER BY`，可以实现灵活的数据排序逻辑，但需注意性能影响，尤其是在处理大数据集时。
踩坑MybatisPlus映射JSON字段
了解Mysql中text和json数据格式 --------------------- 在MySQL中，`TEXT` 和 `JSON` 是两种不同的数据类型，它们的主要区别在于存储的内容和使用方式。 ### 1. TEXT 类型 * **存储内容**：`TEXT` 类型用于存储纯文本数据，可以是任意长度的字符串。 * **用途**：适用于存储大段的文本内容，如文章、日志等。 * **操作**：`TEXT` 类型的数据可以通过字符串函数进行操作，如 `SUBSTRING`、`CONCAT` 等。 * **索引**：`TEXT` 类型的列不能直接创建索引，但可以创建前缀索引。 ### 2. JSON 类型 * **存储内容**：`JSON` 类型用于存储 JSON 格式的数据，JSON 是一种轻量级的数据交换格式，支持复杂的数据结构，如对象、数组等。 * **用途**：适用于存储结构化数据，如配置信息、API 响应等。 * **操作**：`JSON` 类型的数据可以通过专门的 JSON 函数进行操作，如 `JSON_EXTRACT`、 `JSON_SET`、`JSON_ARRAY` 等。 * **索引**：`JSON` 类型的列可以创建虚拟列，并在虚拟列上创建索引，以提高查询性能。 ### JSON 类型的使用示例 #### 创建表 ```sql CREATE TABLE example ( id INT PRIMARY KEY, data JSON ); ``` #### 插入 JSON 数据 ```sql INSERT INTO example (id, data) VALUES (1, '{"name": "Alice", "age": 25, "hobbies": ["reading", "traveling"]}'); ``` #### 查询 JSON 数据 ```sql -- 查询整个 JSON 字段 SELECT data FROM example WHERE id = 1; -- 查询 JSON 字段中的某个键值 SELECT JSON_EXTRACT(data, '$.name') AS name FROM example WHERE id = 1; -- 查询 JSON 字段中的数组 SELECT JSON_EXTRACT(data, '$.hobbies') AS hobbies FROM example WHERE id = 1; ``` #### 更新 JSON 数据 ```plsql -- 更新 JSON 字段中的某个键值 UPDATE example SET data = JSON_SET(data, '$.age', 26) WHERE id = 1; -- 向 JSON 字段中的数组添加元素 UPDATE example SET data = JSON_ARRAY_APPEND(data, '$.hobbies', 'cooking') WHERE id = 1; ``` #### 创建虚拟列并创建索引 ```sql -- 创建虚拟列 ALTER TABLE example ADD COLUMN name VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) VIRTUAL; -- 在虚拟列上创建索引 CREATE INDEX idx_name ON example (name); ``` ### 补充： MySQL 中，**虚拟列（Generated Column）** 是一种特殊的列，它的值是通过表达式计算得出的，而不是直接存储数据。虚拟列可以分为两种类型： 1. **VIRTUAL**：虚拟列的值在查询时动态计算，不占用存储空间。 2. **STORED**：虚拟列的值在插入或更新时计算并存储，占用存储空间。 虚拟列的主要用途是简化查询、优化性能以及支持对 JSON 或其他复杂数据类型的索引。以下是虚拟列的优缺点： #### 优点 ##### 1. 简化查询 * 虚拟列可以将复杂的计算逻辑封装在列定义中，查询时可以直接使用虚拟列，而不需要重复编写复杂的表达式。 * 例如，从 JSON 数据中提取某个字段并创建虚拟列后，可以直接查询虚拟列，而不需要每次都使用 `JSON_EXTRACT`。 ##### 2. 支持索引 * 虚拟列可以创建索引，从而显著提高查询性能。 * 对于 JSON 数据类型，MySQL 不支持直接对 JSON 字段创建索引，但可以通过虚拟列提取 JSON 中的某个字段并创建索引。 ##### 3. 数据一致性 * 虚拟列的值是通过表达式计算得出的，因此可以保证数据的一致性。 * 例如，如果虚拟列是基于其他列计算的，那么当基础列的值发生变化时，虚拟列的值会自动更新。 ##### 4. 节省存储空间（VIRTUAL 类型） * `VIRTUAL` 类型的虚拟列不存储实际数据，只在查询时动态计算，因此不会占用额外的存储空间。 ##### 5. 灵活性 * 虚拟列可以基于复杂的表达式创建，支持数学运算、字符串操作、JSON 函数等。 #### 缺点 ##### 1. 性能开销（VIRTUAL 类型）virtual * `VIRTUAL` 类型的虚拟列在每次查询时都需要动态计算，如果表达式复杂或数据量较大，可能会影响查询性能。 * 对于频繁查询的场景，`STORED` 类型的虚拟列可能更适合，因为它会预先计算并存储结果。 ##### 2. 存储开销（STORED 类型）stored * `STORED` 类型的虚拟列会占用存储空间，因为它的值是预先计算并存储的。 * 如果虚拟列的数据量较大，可能会增加表的存储需求。 ##### 3. 不支持所有表达式 * 虚拟列的表达式有一些限制，例如不能使用子查询、存储过程、用户定义函数等。 * 某些复杂的计算可能无法通过虚拟列实现。 ##### 4. 维护复杂性 * 如果虚拟列的表达式依赖于其他列，那么当表结构发生变化时（例如修改列名或删除列），可能需要同步更新虚拟列的定义。 * 虚拟列的表达式逻辑需要谨慎设计，避免出现性能瓶颈或逻辑错误。 ##### 5. 兼容性 * 虚拟列是 MySQL 5.7 及以上版本引入的功能，旧版本的 MySQL 不支持虚拟列。 #### 使用场景 ##### 1. JSON 数据索引 * 从 JSON 字段中提取某个键值并创建虚拟列，然后在该列上创建索引。 例如： ```sql ALTER TABLE example ADD COLUMN name VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) VIRTUAL; CREATE INDEX idx_name ON example (name); ``` ##### 2. 计算字段 例如，基于价格和数量计算总金额： ```sql ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10, 2) AS (price * quantity) STORED; ``` ##### 3. 数据格式化 例如，将日期字段格式化为字符串： ```sql ALTER TABLE events ADD COLUMN event_date_str VARCHAR(10) AS (DATE_FORMAT(event_date, '%Y-%m-%d')) VIRTUAL; ``` #### 总结 | 特性 | VIRTUAL 虚拟列 | STORED 虚拟列 | | --- | --- | --- | | 存储空间 | 不占用存储空间 | 占用存储空间 计算时机 | 每次查询时动态计算 | 插入或更新时计算并存储 性能 | 查询时可能有性能开销 | 查询性能较好，但写入时可能有开销 适用场景 | 数据变化频繁、存储空间有限 | 查询频繁、计算复杂 | 根据具体需求选择合适的虚拟列类型，可以显著提升数据库的性能和灵活性。 ### 总结 * `TEXT` 类型适合存储纯文本数据，而 `JSON` 类型适合存储结构化数据。 * `JSON` 类型提供了丰富的函数来操作 JSON 数据，并且可以通过虚拟列和索引来优化查询性能。 MybatisPlus如何使用JSON类型 --------------------- ### 数据库实体 <img src="https://pic.code-nav.cn/post_picture/1848659043884322817/i2tB2mn0oefaTDyc.webp" alt="" width="100%" /> ### 引入依赖 ```xml <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> <version>8.0.33</version> </dependency> ``` ### 创建实体 ```java @Data // 注意这里一定要开启注解映射 @TableName(autoResultMap = true) public class CaseInfo implements Serializable { private static final long serialVersionUID = 1L; /** * 配置id */ @TableId(type = IdType.ASSIGN_ID) private Long id; /** * 房间id */ @TableField("roomId") private Long roomId; /** * 配置 */ // 配置类型处理器可以自己写，也可以用其他的 JacksonTypeHandler.class 看自己 // 如果没有在类开头添加注解映射 ，当然你也可以用显示注解 看自己 @TableField(value = "caseInfo",typeHandler = HutoolJsonTypeHandler.class) @TableField(typeHandler = HutoolJsonTypeHandler.class) private CaseInfos caseInfo; } ``` ```java @Data public class CaseInfos implements Serializable { private static final long serialVersionUID = 1L; private String size; private String tag; } ``` ### 自己实现类型处理器 ```java public class HutoolJsonTypeHandler extends AbstractJsonTypeHandler<CaseInfos> { @Override public CaseInfos parse(String json) { // 将JSON字符串转换为CaseInfo对象 if (json == null || json.isEmpty()) { return null; } try { return JSONUtil.toBean(json, CaseInfos.class); } catch (Exception e) { System.err.println("JSON解析异常: " + e.getMessage()); return null; } } @Override public String toJson(CaseInfos obj) { // 将CaseInfo对象转换为JSON字符串 if (obj == null) { return null; } try { return JSONUtil.toJsonStr(obj); } catch (Exception e) { System.err.println("JSON转换异常: " + e.getMessage()); return null; } } } ``` ### 增删改查 ```java @SpringBootTest class CaseServiceTest { @Resource private CaseService caseService; // 生成增删改查 @Test void save() { CaseInfo aCase = new CaseInfo(); aCase.setRoomId(1L); CaseInfos caseInfo = new CaseInfos(); caseInfo.setSize("big"); caseInfo.setTag("恐怖"); aCase.setCaseInfo(caseInfo); caseService.save(aCase); } @Test public void updateById() { Long caseId = 1902930311949942786L; String newSizeValue = "big"; // 使用 LambdaUpdateWrapper 和 MySQL JSON 函数更新单个字段 LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>(); updateWrapper.eq(CaseInfo::getId, caseId) .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.size', '%s')", newSizeValue)); // 执行更新操作 boolean updateResult = caseService.update(updateWrapper); if (updateResult) { System.out.println("Update successful."); } else { System.out.println("Update failed."); } } /** * 更新JSON对象中的单个字段 * 示例1：更新size字段 */ @Test public void updateJsonField_Size() { // 1. 指定要更新的记录ID Long caseId = 1902930311949942786L; // 2. 设置新的size值 String newSizeValue = "medium"; // 3. 使用LambdaUpdateWrapper和MySQL的JSON_SET函数 LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>(); updateWrapper.eq(CaseInfo::getId, caseId) .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.size', '%s')", newSizeValue)); // 4. 执行更新操作 boolean updateResult = caseService.update(updateWrapper); System.out.println("更新size字段 " + (updateResult ? "成功" : "失败")); // 5. 验证更新结果 if (updateResult) { CaseInfo updatedCase = caseService.getById(caseId); System.out.println("更新后的记录: " + updatedCase); System.out.println("size字段新值: " + (updatedCase.getCaseInfo() != null ? updatedCase.getCaseInfo().getSize() : "null")); } } /** * 使用MySQL ->> 操作符更新JSON字段 * 这是一种更简洁的方式 */ @Test public void updateJsonField_UsingArrowOperator() { // 1. 指定要更新的记录ID Long caseId = 1902930311949942786L; // 2. 设置新的值 String newSizeValue = "超大"; // 3. 使用LambdaUpdateWrapper和MySQL的 ->> 操作符 LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>(); updateWrapper.eq(CaseInfo::getId, caseId) .setSql("JSON_SET(caseInfo, '$.size', '" + newSizeValue + "') -> '$'"); // 4. 执行更新操作 boolean updateResult = caseService.update(null, updateWrapper); System.out.println("使用 ->> 操作符更新字段 " + (updateResult ? "成功" : "失败")); // 5. 验证更新结果 if (updateResult) { CaseInfo updatedCase = caseService.getById(caseId); System.out.println("更新后的记录: " + updatedCase); System.out.println("size字段新值: " + (updatedCase.getCaseInfo() != null ? updatedCase.getCaseInfo().getSize() : "null")); } } /** * 查询JSON字段中特定值的记录 * 使用MySQL ->> 操作符 */ @Test public void queryUsingJsonOperator() { // 使用原生SQL进行查询 String sql = "SELECT * FROM case_info WHERE caseInfo->>'$.size' = 'medium'"; System.out.println("执行SQL: " + sql); // 获取所有记录并过滤 List<CaseInfo> allCases = caseService.list(); System.out.println("找到符合条件的记录:"); for (CaseInfo aCase : allCases) { if (aCase.getCaseInfo() != null && "medium".equals(aCase.getCaseInfo().getSize())) { System.out.println(aCase); } } } /** * 更新JSON对象中的单个字段 * 示例2：更新tag字段 */ @Test public void updateJsonField_Tag() { // 1. 指定要更新的记录ID Long caseId = 1902930311949942786L; // 2. 设置新的tag值 String newTagValue = "科幻"; // 3. 使用LambdaUpdateWrapper和MySQL的JSON_SET函数 LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>(); updateWrapper.eq(CaseInfo::getId, caseId) .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.tag', '%s')", newTagValue)); // 4. 执行更新操作 boolean updateResult = caseService.update(updateWrapper); System.out.println("更新tag字段 " + (updateResult ? "成功" : "失败")); // 5. 验证更新结果 if (updateResult) { CaseInfo updatedCase = caseService.getById(caseId); System.out.println("更新后的记录: " + updatedCase); System.out.println("tag字段新值: " + (updatedCase.getCaseInfo() != null ? updatedCase.getCaseInfo().getTag() : "null")); } } /** * 同时更新JSON对象中的多个字段 */ @Test public void updateMultipleJsonFields() { // 1. 指定要更新的记录ID Long caseId = 1902930311949942786L; // 2. 设置新的值 String newSizeValue = "small"; String newTagValue = "推理"; // 3. 使用LambdaUpdateWrapper和MySQL的JSON_SET函数同时更新多个字段 LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>(); updateWrapper.eq(CaseInfo::getId, caseId) .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.size', '%s', '$.tag', '%s')", newSizeValue, newTagValue)); // 4. 执行更新操作 boolean updateResult = caseService.update(updateWrapper); System.out.println("同时更新多个字段 " + (updateResult ? "成功" : "失败")); // 5. 验证更新结果 if (updateResult) { CaseInfo updatedCase = caseService.getById(caseId); System.out.println("更新后的记录: " + updatedCase); if (updatedCase.getCaseInfo() != null) { System.out.println("size字段新值: " + updatedCase.getCaseInfo().getSize()); System.out.println("tag字段新值: " + updatedCase.getCaseInfo().getTag()); } } } @Test void removeById() { caseService.removeById(1L); } @Test void list() { List<CaseInfo> list = caseService.list(); for (CaseInfo aCase : list) { System.out.println(aCase); } } } ``` 到这你就应该学会了该如何在OJ项目中使用JSON格式来处理判题信息等等JSON类型的字段了把 如果还有问题 那你有没好好看我的文档 **回答我！！！ look in my eyes tell me why why baby why**
MySQL查询缓存有什么弊端, 应该什么情况下使用, 8.0 版本对查询缓存有什么变更
MySQL 查询缓存（Query Cache）是一种机制，用于缓存 SELECT 查询的结果，以便当相同的查询再次执行时，可以直接从缓存中返回结果，而不需要重新执行查询。虽然查询缓存可以提高某些场景下的查询性能，但它也存在一些弊端，并且在 MySQL 8.0 版本中已经被移除。以下是详细的说明： 查询缓存的弊端 内存占用： 查询缓存会占用大量的内存，尤其是在高并发和复杂查询的场景下。这可能会导致系统内存不足，影响其他操作的性能。 更新开销： 每当表的数据发生变化时（如 INSERT、UPDATE、DELETE 操作），所有涉及该表的缓存查询都会被无效化。这会导致频繁的缓存清理操作，增加系统的开销。 缓存命中率低： 在实际应用中，很多查询都是动态生成的，包含不同的参数或条件。这使得缓存命中率较低，缓存的效果大打折扣。 并发问题： 查询缓存在高并发场景下可能会成为瓶颈，因为多个线程同时访问缓存时需要进行锁操作，这会影响性能。 应该在什么情况下使用 尽管查询缓存存在上述弊端，但在某些特定场景下仍然可以考虑使用： 读多写少的场景： 如果应用程序主要是读取数据，且数据更新不频繁，查询缓存可以显著提高查询性能。 查询结果集较小且查询频率高： 对于那些结果集较小且查询频率较高的查询，查询缓存可以有效减少数据库的负载。 查询条件固定： 如果查询条件固定且不经常变化，查询缓存可以提供较好的性能提升。 MySQL 8.0 版本对查询缓存的变更 MySQL 8.0 版本正式移除了查询缓存功能。主要原因如下： 性能问题： 查询缓存的性能问题在高并发和复杂查询的场景下尤为明显，移除查询缓存可以避免这些问题。 替代方案： MySQL 8.0 引入了其他优化机制，如 InnoDB 缓存池、查询优化器改进等，这些机制可以更好地提升查询性能。 维护成本： 查询缓存的维护成本较高，移除它可以简化数据库的管理和维护。 替代方案 在 MySQL 8.0 及更高版本中，可以考虑以下替代方案来优化查询性能： 使用 InnoDB 缓存池： InnoDB 缓存池可以缓存表数据和索引数据，提高查询性能。 查询优化： 通过优化查询语句、添加合适的索引等方式，提高查询效率。 使用外部缓存： 使用 Redis、Memcached 等外部缓存系统来缓存查询结果。 分区表： 对于大数据表，可以使用分区表来提高查询性能。 读写分离： 通过读写分离技术，将读操作和写操作分开，减轻主库的压力。
《高性能MySQl》读书笔记
<html> <head></head> <body> <div class="content ql-editor"> <p>#阅读#</p> <p><strong style="font-family: inherit;font-style;font-variant-ligatures;font-variant-caps; font-size: 1.5em; color: black;">一、MySQL架构</strong></p> <p><strong style="font-family: inherit;font-style;font-variant-ligatures;font-variant-caps; font-size: 1.5em; color: black;"><br></strong></p> <h3><strong style="color: black;">MySQL逻辑架构</strong></h3> <p><br></p> <p>第一章的内容主要是以MySql的架构来展开描述的。首先介绍MySql的逻辑架构:</p> <p class="ql-align-center"><img src="https://pic.code-nav.cn/planet_post_image/1608469635965911041/mqm80w9y.jpeg"></p> <p>逻辑架构可以分为三层：</p> <ol> <li data-list="bullet"><span class="ql-ui"></span>客户端：这一层不是MySQL独有的，大多数基于网络的客户端/服务器工具或者服务器都有，功能包括连接处理、身份验证、确保安全。</li> <li data-list="bullet"><span class="ql-ui"></span>Server层：大多数MySQL的核心功能都在这一层，包括连接器、查询缓存（MySQl5.7.20版本开始，查询缓存已经被官方弃用，并在8.0版本中被完全移除）、分析器、优化器、执行器等，以及所有内置函数(例如：日期、时间、数字和加密函数)，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。</li> <li data-list="bullet"><span class="ql-ui"></span>存储引擎层：负责MySql中数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。存储引擎层还包含了十几个底层函数，用于执行像“开始一个事务”或者“根据主键提取一个行记录”等操作。存储引擎不会去解析SQL（InnoDB除外），不同存储引擎不会去通信，只能简单的响应服务器的请求。</li> </ol> <h3><strong style="color: black;"><br></strong></h3> <h3><strong style="color: black;">并发控制</strong></h3> <p><br></p> <p>MySQl提供两个级别的并发控制：服务器级别和存储引擎级别。</p> <p><br></p> <p>解决并发控制的问题，可以通过实现一个由两个锁类型组成的锁系统。这两个锁通常被称为共享锁（读锁）和排它锁（写锁）。</p> <p><br></p> <p>MySQL提供多种选择，每种存储引擎都可以实现自己的锁策略和锁粒度。在设计存储引擎时，锁粒度固定在某个级别，可以提高某个场景下的性能，但是同时又不适应另外的一些场景。但是MySQl提供了多个存储引擎，使得可以适用于各个场景。</p> <p><br></p> <h4><strong style="font-size: 18px; color: black;">表锁</strong></h4> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>是最基本的也是开销最小的锁策略。它是锁定整张表，当客户端需要对表进行写操作时，需要先获取一个写锁，这会阻塞其他客户端对该表的读操作和写操作。只有没有写操作后，其他客户端才能获得读锁，读锁之间不会相互阻塞。</li> </ol> <p><br></p> <h4><strong style="font-size: 18px; color: black;">行级锁</strong></h4> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>可以很大程度的支持并发处理，但是带来了最大的开销。行级锁是锁定表中某一行数据，允许其他人编辑其他行，而不会发生阻塞。</li> </ol> <p><br></p> <h3><strong style="color: black;">事务</strong></h3> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>事务就是一组SQL语句。如果数据库引擎能够成功地对数据库应用整组语句，那么就执行这组语句，如果有其中任意一条语句无法执行，那么整组语句都不执行。简单理解：语句要么都执行，要么都不执行。</li> <li data-list="bullet"><span class="ql-ui"></span>一个事务处理系统，必须满足ACID。ACID分别为原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。</li> </ol> <p>原子性：</p> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>单个事务为一个不可分割的最小工作单元。整个事务中的所有操作要么全部commit成功，要么全部失败rollback，对于一个事务来说，不可能只执行其中的一部分SQL操作，这就是事务的原子性。</li> </ol> <p><br></p> <p>一致性：</p> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>数据库总是从一个一致性的状态转换到另外一个一致性的状态。</li> </ol> <p><br></p> <p>隔离性：</p> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。</li> </ol> <p><br></p> <p>持久性：</p> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。</li> </ol> <p><br></p> <p>ACID事务和InnoDB引擎提供的保证是MySQL中最强大、最成熟的特性之一。</p> <p><br></p> <h4><strong style="font-size: 18px; color: black;">隔离级别</strong></h4> <p><br></p> <p>一共有四个隔开级别：</p> <ol> <li data-list="bullet"><span class="ql-ui"></span>READ UNCOMMITTED（读未提交）</li> <li data-list="bullet" class="ql-indent-1"><span class="ql-ui"></span><span style="color: black;">在事务中可以查看其他事务中还没有提交的修改。（在实际应用中很少使用）</span></li> <li data-list="bullet"><span class="ql-ui"></span>READ COMMITTED（读已提交）</li> <li data-list="bullet" class="ql-indent-1"><span class="ql-ui"></span><span style="color: black;">大部分数据库系统默认的隔离级别就是READ COMMITTED（但是MySQl不是）。一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所作的任何修改对其他事务都是不可见的。但是这个级别还是允许不可重复读，这意味着同一个事务中两次执行相同的语句，可能看到不同的数据结果。</span></li> <li data-list="bullet"><span class="ql-ui"></span><img src="https://pic.code-nav.cn/planet_post_image/1608469635965911041/f8uxqzqq.jpeg" style="display: block; margin: auto;" width="559" align="center"></li> <li data-list="bullet"><span class="ql-ui"></span>REPEATABLE READ（可重复读）</li> <li data-list="bullet" class="ql-indent-1"><span class="ql-ui"></span><span style="color: black;">REPEATABLE READ是MySQL数据库默认的事务隔离级别。解决了READ COMMITTED级别不可重复读的问题，保证了在同一个事务中读取到的行数据的结果是一样的。但是无法解决幻读问题。所谓幻读指的是读取某一个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取到该范围的记录时，会产生幻行（InnoDB和XtraDB存储引擎通过多版本并发控制解决幻读问题）。</span></li> <li data-list="bullet"><span class="ql-ui"></span>SERIALIZABLE（可串行化）</li> <li data-list="bullet" class="ql-indent-1"><span class="ql-ui"></span><span style="color: black;">SERIALIZABLE是最高的隔离级别。SERIALIZABLE会在读取的每一条数据上加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少使用此隔离级别。</span></li> </ol> <p><br></p> <p>设置隔离级别</p> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>设置全局隔离级别：</li> </ol> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-keyword">set global </span>transaction isolation level 隔离级别 </div> </div> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>设置会话隔离级别：</li> </ol> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-keyword">set </span>session transaction isolation level 隔离级别 </div> </div> <h4><strong style="font-size: 18px; color: black;"><br></strong><img src="https://pic.code-nav.cn/planet_post_image/1608469635965911041/ksv3bwds.jpeg"></h4> <h4><strong style="font-size: 18px; color: black;">死锁</strong></h4> <p><br></p> <p>死锁是指两个或者多个事务相互持有和请求相同的资源上的锁，产生的循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。</p> <p>举例：</p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-keyword">START </span>TRANSACTION; </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">UPDATE</span> StockPrice <span class="ql-token hljs-keyword">SET close</span> <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-number">45.50</span> <span class="ql-token hljs-keyword">WHERE</span> stock id <span class="ql-token hljs-operator">=</span> <span class="ql-token hljs-number">4</span> <span class="ql-token hljs-keyword">and</span> <span class="ql-token hljs-type">date</span> <span class="ql-token hljs-operator">=2020-05-01';</span> </div> <div class="ql-code-block"><span class="ql-token hljs-string">UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date =2020-05-02;</span> </div> <div class="ql-code-block"><span class="ql-token hljs-string">COMMIT;</span> </div> </div> <p><br></p> <div class="ql-code-block-container"> <div class="ql-code-block"><span class="ql-token hljs-keyword">START TRANSACTION;</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = 2020-05-02;</span> </div> <div class="ql-code-block"><span class="ql-token hljs-keyword">UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = 2020-05-01';</span> </div> <div class="ql-code-block"><span class="ql-token hljs-string">COMMIT;</span> </div> </div> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>每个事务都开始执行第一个查询，在处理过程中会更新一行数据，同时在主键索引和其他唯一索引中将该行锁定。然后，每个事务将在第二个查询中尝试更新第二行数据，却发现该行已经被锁定。这两个事务将永远等待对方完成，除非有其他因素介入解除死锁。这就是一个典型的死锁。</li> <li data-list="bullet"><span class="ql-ui"></span>为了解决这个问题，数据库实现了各种死锁检测和锁超时机制。InnoDB目前处理死锁的方式是将持有最少行级写锁的事务回滚。</li> <li data-list="bullet"><span class="ql-ui"></span>锁的行为和顺序是和存储引擎有关。同样的一系列查询语句，有的存储引擎会产生死锁，有些则不会。</li> <li data-list="bullet"><span class="ql-ui"></span>死锁产生的原因（两个）：一个是真正的数据冲突。另一个是存储引擎的实现方式导致的。.</li> </ol> <p><br></p> <h4><strong style="font-size: 18px; color: black;">事务日志</strong></h4> <p><br></p> <p>事务日志有助于提高事务效率。存储引擎只需要更改内容中的数据副本，而不用每次更改磁盘中的表，然后再把更改记录写入事务日志中，事务日志会被持久化保存在硬盘中。最后会有一个后台进程在某个时间去更新硬盘中的表。</p> <p><br></p> <h4><strong style="font-size: 18px; color: black;">MySql中的事务</strong></h4> <p><br></p> <h5><strong style="color: black;">AUTOCOMMIT</strong></h5> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>默认情况下，单个INSERT、UPDATE或DELETE语会被隐式包装在一个事务中并在执行成功后立即提交，这称为自动提交 (AUTOCOMMIT) 模式。通过禁用此模式，可以在事务中执行一系列语句，并在结束时执行COMMIT提交事务或 ROLLBACK 回滚事务。</li> <li data-list="bullet"><span class="ql-ui"></span>在当前连接中，可以使用SET命令设置AUTOCOMMIT变量来启用或禁用自动提交模式。启用可以设置为1或者ON，禁用可以设置为0或者OFF。如果设置了AUTOCOMMIT=0，则当前连接总是会处于某个事务中，直到发出COMMIT或者ROLLBACK，然后MySQL会立即启动一个新的事务。</li> </ol> <p><br></p> <p>注意：</p> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>不要在同一个事务中混合使用存储引擎。失败的事务可能导致不一样的结果。因为某些部分可以回滚，而其他部分不可以回滚。</li> </ol> <p><br></p> <h5><strong style="color: black;">隐式锁定和显式锁定</strong></h5> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>InnoDB使用两阶段锁定协议(two-phase locking protocol)。在事务执行期间，随时都可以获取锁，但锁只有在提交或回滚后才会释放，并且所有的锁会同时释放。InnoDB会根据隔离级别自动处理锁。</li> <li data-list="bullet"><span class="ql-ui"></span>MySQL还支持LOCK TABLES 和UNLOCK TABLES命令，这些命令在服务器级别而不在存储引擎中实现。如果需要事务,应该使用支持事务的存储引擎。因为InnoDB支持行级锁，所以没必要使用LOCK TABLES。</li> <li data-list="bullet"><span class="ql-ui"></span>注意：</li> <li data-list="bullet" class="ql-indent-1"><span class="ql-ui"></span><span style="color: black;">除了在禁用AUTOCOMMIT的事务中可以使用LOCK TABLES之外，其他任何时候都不要显式地执行 LOCK TABLES，不管使用的是什么存储引擎。</span></li> </ol> <p><br></p> <h3><strong style="color: black;">多版本并发控制（MVCC）</strong></h3> <p><br></p> <p>MVCC（Multiversion Concurrency Control），多版本并发控制。MVCC是通过数据行的多个版本管理来实现数据库的并发控制。它的工作原理是使用数据在某个时间点的快照来实现的。这项技术使得在InnoDB的事务隔离级别下执行 一致性读操作有了保证。这意味着不同事务可以在同一个时间看到同一张表的不同数据，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。</p> <p>跨不同事务处理同一行多个版本的序列图：</p> <p><img src="https://pic.code-nav.cn/planet_post_image/1608469635965911041/5s3os01o.jpeg"></p> <ol> <li data-list="ordered"><span class="ql-ui"></span>InnoDB通过为每个事务在启动时分配一个事务ID来实现MVCC。该ID在事务首次读取任何数据时分配。在该事务中修改记录时，将向Undo日志写入一条说明如何恢复该更改的Undo记录，并且事务的回滚指针指向该 Undo日志记录。这就是事务如何在需要时执行回滚的方法。</li> <li data-list="ordered"><span class="ql-ui"></span>当不同的会话读取聚簇主键索引记录时，InnoDB会将该记录的事务ID与该会话的读取视图进行比较。如果当前状态下的记录不应可见(更改它的事务尚未提交)，那么Undo日志记录将被跟踪并应用，直到会话达到一个符合可见条件的事务ID。这个过程可以一直循环到完全删除这一行的Undo记录，然后向读取视图发出这一行不存在的信号。</li> </ol> <p><br></p> <p>注意：</p> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>MVCC仅适用于REPEATABLE READ和READ COMMITTED隔离级别。READ UNCOMMITTED与MVCC不兼是因为查询不会读取适合其事务版本的行版本，而是不管怎样都读最新版本。SERIALIZABLE与MVCC也不兼容，是因为读取会锁定它们返回的每一行。</li> </ol> <p><br></p> <h3><strong style="color: black;">复制</strong></h3> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>MySOL被设计用于在任何给定时间只在一个节点上接受写操作。这在管理一致性方面具有优势，但在需要将数据写入多台服务器或多个地区时，会导致需要做出取舍。MySQL 提供了一种原生方式来将一个节点执行的写操作分发到其他节点，这被称为复制。在MySQL中，源节点为每个副本节点提供一个线程，该线程作为复制客户端登录当写入发生时会被唤醒，发送新数据。</li> <li data-list="bullet"><span class="ql-ui"></span>对于在生产环境中运行的任何数据，都应该使用复制并至少有三个以上的副本，理想情况下应该分布在不同的地区用于灾难恢复计划。</li> </ol> <p><br></p> <h3><strong style="color: black;">InnoDB引擎</strong></h3> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>InnoDB是MySQL默认的通用存储引擎。默认情况下，InnoDB将数据存储在一系列的数据文件中，这些文件统被称为表空间 (tablespace)。表空间本质上是一个由InnoDB自己管理的黑盒。</li> <li data-list="bullet"><span class="ql-ui"></span>InnoDB使用MVCC来实现高并发性，并实现了所有4个SOL标准隔离级别。InnoDE默认为REPEATABLE READ隔离级别，并且通过间隙锁 (next-key locking)策略来防止在这个隔离级别上的幻读 :InnoDB 不只锁定在查询中涉及的行，还会对索引结构中的间隙进行锁定，以防止幻行被插入。</li> </ol> <p><br></p> <h4><strong style="font-size: 18px; color: black;">JSON文档支持</strong></h4> <p><br></p> <ol> <li data-list="bullet"><span class="ql-ui"></span>ISON类型在5.7版本被首次引人InnoDB，它实现了JSON文档的自动验证，并优化了存储以允许快速读取。</li> <li data-list="bullet"><span class="ql-ui"></span>InnoDB还引入了SQL函数来支持在JSON 文档上的丰富操作。MySOL8.0.7的进一步改进增加了在JSON数组上定义多值索引的能力。将常用访问模式匹配到可以映射JSON文档值的函数这一特性可以进一步加快对JSON类型的读取访问查询。</li> </ol> <p><br></p> <p><br></p> <p><br></p> </div> </body> </html>