python - 如何排序复合索引来防止插入死锁？

Question

我有一个mysql(实际上是MariaDB 5.5.52)数据库，大致描述如下:

CREATE TABLE table1 (
    id INT NOT NULL AUTOINCREMENT, 
    col1 INT, 
    col2 VARCHAR(32), 
    col3 VARCAHR(128), 
    PRIMARY KEY (ID), 
    UNIQUE KEY index1 (col1, col2, col3)
);

还有更多列，但全部都在 UNIQUE 键内，并且表中没有其他键。

我运行一个Python脚本的多线程来插入到这个数据库中。每个线程使用 mysql.connector 的 executemany 执行大约 100-1000 次插入，例如

ins_string = "INSERT IGNORE INTO table1 ({0}) VALUES ({1});"
cursor.executemany(ins_string.format(fields, string_symbols), values)

我遇到了持续的死锁问题。我认为造成这些问题的原因是每个线程根据 python 列表 values 的生成顺序以某种半随机顺序锁定 table1 的行之间。这在一定程度上通过测试得到了验证；当我使用 24 个线程从头开始构建新数据库时，每个 executemany() 语句的死锁率> 80%，但当数据库中有超过一百万行时，死锁率接近于零。

我曾考虑过死锁可能是线程竞争自动增量的结果，但在默认的 InnoDB“连续”锁定模式下，似乎不应该发生这种情况。每个线程应该得到一个 table level lock直到 INSERT 结束。然而，自动增量锁和插入锁交互的方式让我感到困惑，所以如果我有这个错误，请告诉我。

因此，如果问题是由唯一键的随机排序引起的，那么在将插入语句传递给 MySql 之前，我需要某种方法对 python 中的插入语句进行排序。 MySql 以某种方式对索引进行哈希处理，然后进行排序。如何在 python 中复制散列/排序？

我在这里询问我对问题的诊断的解决方案，但如果您发现我的诊断是错误的，请再次告诉我。

Answer 1

既然您有一个可以提升为PRIMARY的UNIQUE key ，为什么还要有ID？

无论如何，在构建 executemany 之前，对 (col1, col2, col3) 上的批量插入行进行排序。

如果这还不够，则减少每个 executemany 中的行数。 100 行与理论最佳值的误差约为 10%。如果 100 将死锁频率降低到以下(例如 10%)，那么您可能非常接近批量加载速度与重放死锁导致的速度减慢之间的最佳平衡。

你有多少个CPU核心？

还有其他索引没有向我们展示吗？ 每个 UNIQUE 索引都会影响此问题。非唯一索引不是问题。请提供完整的SHOW CREATE TABLE。