当前位置： 首页 > article >正文

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

article 2024/11/22 20:35:59

一、添加 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 值，避免查询结果和性能问题。

http://www.kler.cn/a/404790.html

相关文章：

  • 【AIGC】如何使用高价值提示词Prompt提升ChatGPT响应质量
  • Java中的TreeSet集合解析
  • 测试工程师如何在面试中脱颖而出
  • 设计模式之 享元模式
  • yolov5 数据集分享：纯干货
  • 内存（RAM）详细介绍
  • 小鹏汽车智慧材料数据库系统项目总成数据同步
  • Go 常量为什么只支持基本数据类型？
  • （C语言）文件操作
  • 如何在 Ubuntu 上安装 Anaconda 开发环境
  • 北京申请中级职称流程（2024年）
  • Python+Django框架江西南昌二手房数据可视化大屏系统网站作品截图和开题报告参考
  • 聊一聊Elasticsearch的索引数据搜索过程
  • 前端数据可视化思路及实现案例
  • 鸿蒙多线程开发——线程间数据通信对象01
  • Flink学习连载文档第一篇--Flink集群的安装
  • Ubuntu24.04下的docker问题
  • 开源Tacchi 视触觉传感器仿真器，为机器人与物体接触仿真提供高质量的Sim2Real性能！
  • 241121学习日志——[CSDIY] [InternStudio] 大模型训练营 [11]
  • leetcode-18-四数之和
  • 【PDFBox】-初识
  • Java八股-MyBatis延迟加载
  • 提交git仓库时，如何关闭lint校验
  • 数据结构 （1）基本概念和术语
  • Easyexcel（4-模板文件）
  • 【QT - 1 - 】什么是QT?