自学内容网 自学内容网
自学内容网 > 正文

多线程并发造成的数据重复问题解决方案参考(笔记记录)

🕗 发布于 2024-11-22 21:41 java  MySQL  组合索引  多线程并发  

一、添加 MySQL 组合唯一索引，需要注意什么坑？

在 MySQL 中，创建组合唯一索引（Composite Unique Index）时，需要注意以下一些容易踩的坑：

1. 字段顺序影响索引使用

问题：组合唯一索引的字段顺序会影响查询性能和匹配情况。例如：
UNIQUE KEY (column_a, column_b) 和 UNIQUE KEY (column_b, column_a) 是完全不同的索引。

  • 索引 (column_a, column_b) 可用于查询 WHERE column_a = ? AND column_b = ? 或 WHERE column_a = ?。
  • 它不能直接优化 WHERE column_b = ?。

解决：根据实际查询场景设计索引字段顺序，最常用的字段放前面。

2. 插入数据需符合唯一性约束

问题：插入或更新数据时，必须确保组合索引的所有字段组合的值是唯一的。如果其中某个字段允许 NULL，会带来意想不到的结果：

  • 在 MySQL 中，NULL 不参与唯一性比较。
  • 如果索引包含 NULL 值，如 (1, NULL) 和 (1, NULL)，MySQL 会认为它们是不同的。

解决：

  • 如果需要严格唯一，确保所有字段都设置为 NOT NULL。
  • 如果允许 NULL，检查业务逻辑是否能够处理上述情况。

3. 索引长度限制

问题：组合索引的长度受限，尤其是使用 VARCHAR 或 TEXT 类型时：

  • 索引长度在 InnoDB 表中，单个索引字段长度总和不能超过 3072 字节（MySQL 8.0 默认设置）。
  • 超过限制会报错：Specified key was too long; max key length is 3072 bytes。

解决：

  • 控制索引字段的长度，使用前缀索引（KEY(column(50))）。
  • 设计数据库时避免过长的 VARCHAR 或 TEXT 类型用于索引字段。

4. 隐式索引

问题：在创建外键时，MySQL 会自动为外键字段生成索引。如果这个字段已经是组合索引的一部分，可能导致多余的索引，影响性能。

解决：

  • 确认外键字段是否需要单独索引。
  • 避免不必要的重复索引。

5. 查询优化器可能不使用组合索引

问题：如果查询语句的条件只使用了部分组合索引字段，MySQL 查询优化器可能不会使用索引。例如：

  • 索引是 (column_a, column_b)。
  • 查询是 WHERE column_b = ?。

解决：

  • 确保查询条件尽量使用索引字段的前缀部分。
  • 为单独使用频繁的字段创建单独的索引。

6. 更新操作的性能问题

问题：组合索引中的字段发生变化时，MySQL 需要同时更新索引，可能导致性能问题。

解决：

  • 减少频繁更新索引字段的场景。
  • 如果索引字段需要经常更新，重新评估索引设计是否合理。

7. 唯一索引和业务逻辑冲突

问题：组合唯一索引依赖数据库层面约束，如果业务逻辑不匹配可能会引发冲突。例如：

  • 一个字段允许重复，但结合另一个字段需要唯一。
  • 复杂的业务规则可能需要动态约束。

解决：

  • 明确业务规则，确保数据库约束与业务逻辑一致。
  • 必要时，在业务逻辑中补充检查，避免直接依赖数据库约束。

8.总结以下注意事项：

组合唯一索引的坑主要体现在 字段顺序、NULL处理、索引长度、查询优化和业务匹配 上。在设计时：

  1. 优化字段顺序，优先考虑查询场景。
  2. 确保字段非空或处理好 NULL。
  3. 避免长字段造成的索引超长。
  4. 使用 EXPLAIN 检查查询是否命中索引。
  5. 定期审查索引设计，确保性能和业务需求匹配。

如果你有具体问题或场景，可以分享下，我可以帮你分析优化方案！

二、可以用加锁的方式来解决加组合唯一索引问题？

当然，加锁可以在一定程度上解决组合唯一索引的一些问题，但需要根据场景谨慎选择是否使用锁，因为加锁可能带来性能开销甚至死锁问题。

以下是一些场景中通过加锁解决问题的方式及其优劣势：

1. 插入或更新时的唯一性校验

问题：组合唯一索引未覆盖复杂的业务规则，导致违反唯一性要求。
解决方案：通过 事务和行级锁 保证并发下的唯一性。

  • 实现方法：
    在操作之前显式使用 SELECT ... FOR UPDATE 检查是否存在冲突数据，确保当前事务持有锁后再执行插入或更新。

    示例代码：

    START TRANSACTION;

SELECT id 
FROM your_table 
WHERE column_a = ? AND column_b = ? 
FOR UPDATE;

-- 如果上述查询无结果，说明不存在冲突，可以插入
INSERT INTO your_table (column_a, column_b, value) VALUES (?, ?, ?);

COMMIT;

  • 优点：

    • 通过锁住相关行，避免并发事务插入或更新冲突。
    • 保证业务层的唯一性校验逻辑不会因并发问题失效。

  • 缺点：

    • 性能开销较大，尤其在高并发场景下可能导致事务等待。
    • 如果事务未正确释放锁，可能引发死锁问题。

2. 处理 NULL 值的唯一性

问题：组合索引中 NULL 不参与唯一性约束，导致数据重复问题。
解决方案：
通过业务逻辑加锁或避免 NULL 值。

  • 实现方法：

    • 禁用 NULL 值，设置字段为 NOT NULL，并提供默认值。
    • 对组合字段加锁，比如在插入时对全表或相关行加锁。
    SELECT id 
FROM your_table 
WHERE (column_a = ? AND column_b IS NULL)
FOR UPDATE;

  • 替代方案：避免锁操作，使用触发器检查唯一性规则。

3. 批量插入或更新冲突问题

问题：批量操作可能引发唯一索引冲突。
解决方案：显式加锁限制并发，确保批量操作独占资源。

  • 实现方法：对整个表或特定范围加表级锁：

    LOCK TABLES your_table WRITE;

-- 批量插入
INSERT INTO your_table (column_a, column_b, value) VALUES (?, ?, ?);

UNLOCK TABLES;

  • 优点：

    • 批量操作中避免冲突。
    • 不需要额外业务逻辑检查。

  • 缺点：

    • 表级锁会阻塞其他事务，可能降低并发性能。

4. 索引覆盖范围不足的问题

问题：查询条件未覆盖组合索引的前缀部分，导致索引未生效，查询或更新效率低下。
解决方案：加行级锁配合补充逻辑。

  • 实现方法：
    通过锁定相关行，明确查询逻辑，即使未命中索引也能避免并发问题：

    SELECT * 
FROM your_table 
WHERE column_b = ? 
FOR UPDATE;

  • 优点：保障一致性。

  • 缺点：对性能要求较高的场景可能拖慢响应。

5.什么时候避免加锁？

  • 高并发场景：加锁可能导致大量事务等待，降低系统吞吐量。
  • 读多写少场景：过多锁操作可能浪费资源。
  • 简单规则场景：如果数据库的组合唯一索引已经可以解决问题，无需加锁。

6.更好的替代方案

  1. 通过分布式锁（如 Redis）控制跨数据库实例的并发操作：

    • 适用于分布式场景。
    • 性能高，但需小心锁的粒度和过期策略。

  2. 触发器：

    • 使用数据库触发器实现更灵活的唯一性校验。
    CREATE TRIGGER before_insert_unique_check 
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  IF EXISTS (
    SELECT 1 
    FROM your_table 
    WHERE column_a = NEW.column_a AND column_b = NEW.column_b
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry for combination';
  END IF;
END;

  3. 应用层校验：

    • 在应用代码中，插入或更新前先查询校验。
    • 适合中低并发场景，但仍需配合事务和加锁处理边界问题。

7.可以总结为：

加锁可以有效解决组合唯一索引的部分问题，尤其在并发写操作时。但加锁会带来性能开销，需结合业务需求权衡。如果可能：

  • 优先通过索引设计优化和字段规则（如 NOT NULL）规避问题。
  • 在高并发场景中，尝试使用分布式锁或应用层校验结合数据库事务。

三、那么对于组合索引，如果历史数据为空值，需要把空值补充完整么？

当然，在某些情况下需要补充完整历史数据的空值，但是否需要具体取决于业务需求和索引规则。以下是需要补充或不需要补充的情况分析及操作建议：

1.需要补充空值的情况

1. 避免索引唯一性约束失效

  • 原因：MySQL 在唯一索引中将 NULL 视为不同的值，这可能导致意外的重复数据。例如：

    • 如果组合索引是 (column_a, column_b)，column_b 包含 NULL 值：
      • 允许插入 (1, NULL) 和 (1, NULL)，索引约束没有生效。

  • 解决办法：

    • 将 NULL 替换为业务允许的默认值，例如 0、空字符串或 N/A。

    SQL 示例：

    UPDATE your_table
SET column_b = 'DEFAULT_VALUE'
WHERE column_b IS NULL;
2. 避免查询结果不一致
  • 原因：查询条件依赖组合索引时，如果字段中存在 NULL 值，可能导致索引无法完全匹配。例如：
    • 组合索引 (column_a, column_b)，查询 WHERE column_a = 1 AND column_b = 2。
    • 如果 column_b 是 NULL，查询可能无法命中索引。
  • 解决办法：
    • 补充默认值，确保索引字段能够完全匹配。
3. 满足新业务需求
  • 原因：新的业务规则要求字段不能为空，例如字段被用作主键的一部分。
  • 解决办法：
    • 确保所有历史数据符合新规则，替换掉 NULL。

2.不需要补充空值的情况

1. 允许 NULL 作为有效状态
  • 原因：如果业务逻辑允许某些字段为空（例如 column_b 表示可选值），且设计上 NULL 值有特定含义（如未定义）。
  • 解决办法：
    • 保持现状，但明确 NULL 的业务含义，并确保查询逻辑正确处理 NULL。
2. 唯一性约束对 NULL 的特性无影响
  • 原因：在特定场景中，组合索引中包含 NULL 可能正是业务所需。例如：
    • (column_a, column_b)，其中 column_b 是 NULL 时，允许 column_a 重复。
  • 解决办法：
    • 确保业务逻辑和索引设计一致，不需要额外处理。

3.如何判断是否需要补充空值

  1. 业务需求是否允许字段为空：

    • 如果字段 NULL 表示特殊状态（如未定义），可能无需补充。
    • 如果 NULL 是异常数据或无意义的数据，建议补充。

  2. 是否影响索引的唯一性：

    • 如果索引规则要求字段唯一，NULL 可能导致重复数据，需要补充。
    • 如果 NULL 不影响规则，可保持现状。

  3. 是否影响查询性能：

    • 如果查询频繁依赖组合索引，NULL 可能导致索引失效，建议补充。
    • 如果查询不依赖该字段的匹配，可能无需处理。

4.如何安全地补充空值

1. 确定补充规则
  • 根据业务含义定义合理的默认值。例如：
    • 对 VARCHAR 字段：补充空字符串 '' 或默认标识 N/A。
    • 对 INT 字段：补充 0 或其他默认值。
2. 分批更新历史数据

  • 避免一次性更新导致锁表或性能问题，使用分页更新：

    UPDATE your_table
SET column_b = 'DEFAULT_VALUE'
WHERE column_b IS NULL
LIMIT 1000;
3. 修改表结构，设置字段非空约束

  • 如果业务要求字段不能为空，在补充完数据后添加 NOT NULL 约束：

    ALTER TABLE your_table
MODIFY column_b VARCHAR(255) NOT NULL DEFAULT 'DEFAULT_VALUE';
4. 检查索引规则是否仍然适用

  • 如果组合索引因为字段补充发生变化，可能需要重新创建索引：

    DROP INDEX your_index ON your_table;
CREATE UNIQUE INDEX your_index ON your_table (column_a, column_b);

5.总结以下几点：

  • 需要补充的关键点：当 NULL 值会导致索引失效、查询不命中或者业务规则不一致时，应该补充默认值。
  • 无需补充的关键点：如果 NULL 值在业务中是有效状态，且不会导致功能性问题，可以保留。
  • 最佳实践：补充历史数据前先分析业务需求，分批更新数据，并在测试环境中验证后上线，确保操作安全可靠。

四、如果历史数据有字段为 null 值，允许添加组合索引么？

历史数据有字段为 NULL 时，MySQL 允许添加组合索引，但需要注意以下问题和潜在的坑点，确保索引能够正常工作并符合业务需求。

1. 添加索引的基本规则

  • MySQL 支持在包含 NULL 值的字段上添加索引（包括单字段索引和组合索引）。

  • 组合索引中的 NULL 值对唯一性约束的影响：

    • 如果是普通索引，NULL 值可以正常索引，并且不影响数据重复。

    • 如果是唯一索引，NULL 被视为不同值，因此不会违反唯一性约束。例如：

      CREATE TABLE example (
  column_a INT,
  column_b INT,
  UNIQUE KEY unique_index (column_a, column_b)
);

      • 插入以下数据是合法的：

        INSERT INTO example (column_a, column_b) VALUES (1, NULL);
INSERT INTO example (column_a, column_b) VALUES (1, NULL); -- 不报错

2. 添加索引是否成功

即使数据中存在 NULL 值，MySQL 不会阻止组合索引的创建。例如：

CREATE INDEX idx_combination ON table_name (column_a, column_b);

以上语句可以成功执行，即使 column_a 或 column_b 包含 NULL 值。

3.问题与注意点

1. 查询时索引可能失效

  • 如果字段中存在 NULL 值，某些查询条件可能无法完全利用索引：

    • 组合索引规则：只有查询条件覆盖了索引的最左前缀字段，且字段值不是 NULL，索引才会生效。

    • 示例：

      SELECT * FROM table_name WHERE column_b = 1; -- 索引失效，因为未用到 column_a
SELECT * FROM table_name WHERE column_a = 1 AND column_b = NULL; -- 结果不匹配，可能索引失效
2. 唯一性约束的潜在问题

  • 如果创建唯一索引（UNIQUE），NULL 值会被视为不同的值，可能导致意外行为：

    CREATE UNIQUE INDEX idx_unique_combination ON table_name (column_a, column_b);
INSERT INTO table_name (column_a, column_b) VALUES (1, NULL);
INSERT INTO table_name (column_a, column_b) VALUES (1, NULL); -- 不报错

    原因：MySQL 的唯一索引规则中，NULL 值不参与重复判断。

    • 解决方法：避免索引字段允许 NULL，用默认值（如 0、空字符串）替代。

4. 查询结果可能不符合预期

  • 对于包含 NULL 的组合索引，查询需要特别处理 NULL 值：

    SELECT * FROM table_name WHERE column_a = 1 AND column_b IS NULL;

    如果遗漏 IS NULL 条件，查询结果可能不完整。

5. 性能隐患

  • 如果大量字段值为 NULL，索引可能会被冗余信息占用，影响性能：
    • 数据量大时，NULL 值可能导致索引块中存储的数据分布不均。
    • 建议定期清理或优化索引字段。

6.操作建议

1. 添加索引前的检查

  • 清理历史数据：

    • 如果 NULL 不符合业务规则，补充默认值：

      UPDATE table_name SET column_b = 'DEFAULT_VALUE' WHERE column_b IS NULL;

  • 确认业务逻辑：

    • 如果业务允许 NULL，确保查询逻辑正确处理 NULL 值。
2. 创建索引时避免 NULL 值

  • 设置字段为 NOT NULL：

    • 如果历史数据清理完毕，更新字段为非空：

      ALTER TABLE table_name MODIFY column_b INT NOT NULL;

  • 使用默认值替代 NULL：

    • 适合组合索引中使用。例如，将 NULL 替换为 0 或业务上其他默认值。
3. 查询逻辑优化

  • 明确处理 NULL 值的查询条件：

    SELECT * FROM table_name WHERE column_a = 1 AND (column_b = 2 OR column_b IS NULL);

  • 避免直接依赖索引命中 NULL 的字段。

4. 唯一索引的规避方案
  • 如果需要严格控制唯一性，避免 NULL 值：
    • 清理历史数据并设置字段为非空。
    • 或者在应用层增加唯一性校验逻辑。

7.最后的总结

  • 允许添加组合索引：即使字段包含 NULL 值，MySQL 允许添加组合索引。
  • 注意业务影响：NULL 值可能导致查询索引失效或唯一性约束行为异常。
  • 优化建议：
    • 根据业务需求决定是否清理 NULL 数据。
    • 对字段设置 NOT NULL 或替换 NULL 为默认值以规避问题。
    • 确保查询逻辑正确处理 NULL 值，避免查询结果和性能问题。

原文地址：https://blog.csdn.net/qq_35971258/article/details/143954943

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

  • reactflow 中 useNodesState 模块作用

    useNodesState`是一个关键的钩子函数，用于专门管理节点（Nodes）的状态。节点是流程图的核心元素，它们可以代表各种实体，如流程中的任务、系统中的组件或者数据结构中的元素。`useNode

    阅读更多2024-11-23
  • Web网络安全

    此时用户访问一个恶意网站，这个网站可自动向银行网站发送请求，比如隐藏域post，image点击，浏览器在向银行网站发送请求时会带上它还没有过期的cookie信息，这样银行站点就区分不出这个请求时从哪里

    阅读更多2024-11-23
  • ROS机器视觉入门：从基础到人脸识别与目标检测

    从本文开始，我们将开始学习ROS机器视觉处理，刚开始先学习一部分外围的知识，为后续的人脸识别、目标跟踪和YOLOV5目标检测做准备工作。我采用的笔记本是联想拯救者游戏本，系统采用Ubuntu20.04

    阅读更多2024-11-23
  • 招商蛇口|在低密园林里，开启生活的“任意门”

    在自然氛围营造中，招商雲蘭突破平面化的植被景观，以富有层次感的立体化植物造景，勾勒自然的丰盛韵律。建面约169㎡奢境洋房，客厅宽达到约6.2m，主套卧约4m，更是突破尺度奢享，为主人公打造一方自在无拘

    阅读更多2024-11-23
  • Vue3.2+TS+arco-design报表封装，所有的报表页面皆可用一个组件进行完成

    Vue3.2+TS+arco-design报表封装，所有的报表页面皆可用一个组件进行完成

    阅读更多2024-11-23
  • 无人机无刷电机核心算法！

    自动控制器是无人机飞行控制的核心部分，负责接收来自无人机传感器和其他系统的信息，并根据预设的算法和逻辑对无人机的姿态、速度、位置等进行控制。无刷电机的工作原理基于电磁感应和换向技术，通过电子换向器（即

    阅读更多2024-11-23
  • Java面试题分享

    jdk1.8及以后采用数组加链表加红黑树，链表超阈值且数组满足条件会转红黑树，哈希函数先取键 hashCode 值再运算使分布均匀，键值对以含键、值等信息的内部类存储，当元素数量达到负载因子与容量的乘

    阅读更多2024-11-23
  • 如何利用Java爬虫一键获取店铺的所有商品技术解析

    在当今的互联网时代，数据的获取和分析变得越来越重要。对于电商领域来说，获取竞争对手的商品信息是市场分析和策略制定的关键步骤。本文将详细介绍如何使用Java编写爬虫程序，一键获取店铺的所有商品信息。

    阅读更多2024-11-23
  • SQL SELECT 语句：基础与进阶应用

    SQL（Structured Query Language）是一种用于管理关系数据库的编程语言。在SQL中，SELECT语句是最常用的命令之一，用于从数据库表中检索数据。本文将详细介绍SELECT语句

    阅读更多2024-11-23
  • 基于Java Springboot电影院订票管理系统

    项目编号：springbootA146电影院订票信息管理系统是为了满足现代电影放映行业的需求，提供一种高效、便捷的在线购票体验而设计。不仅方便了观众快速获取电影信息并完成订票，同时也帮助电影院优化了服

    阅读更多2024-11-23
自学内容网
Copyright © 2015-2024