数据库

1、问题 线上出现很多添加 key 锁超时的 mysql 日志。 https://apm.example.com/app/apm/services/<service_name>/transactions/view?rangeFrom=now-24h%2Fh&rangeTo=now&environment=&transactionName=%23 2、排查 1）查看 APM，发现了一个耗时的请求/sync，做语料同步的。后面插入语料 key 都显Lock wait timeout exceeded。 2）查看同步请求的具体链路，发现有多次批量 INSERT 操作，而且每次批量插入消耗的时间会逐步增加。最终也是执行成功的，花了24 分钟。后面其他事务的 Insert 操作都给阻塞住了。 3）查看锁日志，可以发现并不是死锁，而是普通的锁等待，最开始的批量操作（/sync）占用了大量的锁 4253982 row locks。后面的insert（添加 key）申请插入意向锁都被阻塞，等待一段时间之后上报超时错误给应用。 LOCK WAIT 8 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id <tid_1>, OS thread handle <handle_1>, query id <qid_1> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, description) VALUES (1001, 2001, 'xx-XX', 'default', 'key_1', '<text>', ''); ------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 83690 n bits 136 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_waiting> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ ---TRANSACTION <trx_long>, ACTIVE 1166 sec starting index read mysql tables in use 1, locked 1 102372 lock struct(s), heap size 10379472, 4253982 row lock(s), undo log entries 943 MySQL thread id <tid_long>, OS thread handle <handle_long>, query id <qid_long> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (2001, 3001, 'xx-XX', 'default', 'k1', 'v1', 2, 1), (2002, 3001, 'xx-XX', 'default', 'k2', 'v2', 2, 1); 4）查看代码，发现了调用 importJsonCorpus 进行多次批量操作，这跟 APM 看到的现象一致。这个方法上有事务注解，importJsonCorpus 方法的调用都在一个事务里，所以一次批量操作结束之后并不会释放锁。 ...

现象 补全翻译接口（填充空白的语料）接口执行失败，MySQL 检测到死锁快速抛异常，接口执行耗时 426ms。 无论是从接口、具体表现、根因，都与上个问题有明显区别。 分析 1、查看死锁日志，可以看到有两个事务，是对同一个项目的不同语言的批量插入更新操作（INSERT ON DUPLICATE KEY UPDATE）。 ------------------------ LATEST DETECTED DEADLOCK ------------------------ <timestamp> <os_thread_hex> *** (1) TRANSACTION: TRANSACTION <trx_1>, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id <tid_1>, OS thread handle <handle_1>, query id <qid_1> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (<item_id_1>, <corpus_id>, 'my-MM', 'default', '下一步', '', 0, 1) , (<item_id_2>, <corpus_id>, 'my-MM', 'default', '分组竞争', '', 0, 1) , (<item_id_3>, <corpus_id>, 'my-MM', 'default', '判断对错', '', 0, 1) , (<item_id_4>, <corpus_id>, 'my-MM', 'default', '完成', '', 0, 1) , (<item_id_5>, <corpus_id>, 'my-MM', 'default', '球球拼词', '', 0, 1) , (<item_id_6>, <corpus_id>, 'my-MM', 'default', '知识排序', '', 0, 1) , (<item_id_7>, <corpus_id>, 'my-MM', 'default', '知识配对', '', 0, 1) , (<item_id_8>, <corpus_id>, 'my-MM', 'default', '记忆卡片', '', 0, 1) , (<item_id_9>, <corpus_id>, 'my-MM', 'default', '试玩', '', 0, 1) , (<item_id_10>, <corpus_id>, 'my-MM', 'default', '超级分类', '', 0, 1) , (<item_id_11>, <corpus_id>, 'my-MM', 'default', '趣味分类', '', 0, 1) , (<item_id_12>, <corpus_id>, 'my-MM', 'default', '趣味选择', '', 0, 1) , (<item_id_13>, <corpus_id>, 'my-MM', 'default', '返回', '', 0, 1) , (<item_id_14>, <corpus_id>, 'my-MM', 'default', '选词填空', '', 0, 1) ON DUPLICATE KEY UPDATE status = IF(value = '', VALUES(status), status), value = IF(value = '', VALUES(value), value) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_1> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION <trx_2>, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 7 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id <tid_2>, OS thread handle <handle_2>, query id <qid_2> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (<item_id_a1>, <corpus_id>, 'ar-EG', 'default', '下一步', '', 0, 1) , (<item_id_a2>, <corpus_id>, 'ar-EG', 'default', '分组竞争', '', 0, 1) , (<item_id_a3>, <corpus_id>, 'ar-EG', 'default', '判断对错', '', 0, 1) , (<item_id_a4>, <corpus_id>, 'ar-EG', 'default', '完成', '', 0, 1) , (<item_id_a5>, <corpus_id>, 'ar-EG', 'default', '球球拼词', '', 0, 1) , (<item_id_a6>, <corpus_id>, 'ar-EG', 'default', '知识排序', '', 0, 1) , (<item_id_a7>, <corpus_id>, 'ar-EG', 'default', '知识配对', '', 0, 1) , (<item_id_a8>, <corpus_id>, 'ar-EG', 'default', '记忆卡片', '', 0, 1) , (<item_id_a9>, <corpus_id>, 'ar-EG', 'default', '试玩', '', 0, 1) , (<item_id_a10>, <corpus_id>, 'ar-EG', 'default', '超级分类', '', 0, 1) , (<item_id_a11>, <corpus_id>, 'ar-EG', 'default', '趣味分类', '', 0, 1) , (<item_id_a12>, <corpus_id>, 'ar-EG', 'default', '趣味选择', '', 0, 1) , (<item_id_a13>, <corpus_id>, 'ar-EG', 'default', '返回', '', 0, 1) , (<item_id_a14>, <corpus_id>, 'ar-EG', 'default', '选词填空', '', 0, 1) ON DUPLICATE KEY UPDATE status = IF(value = '', VALUES(status), status), value = IF(value = '', VALUES(value), value) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_2> lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_2> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1) 根据死锁日志画出时序图，从下面这个时序图可以看到死锁的形成过程。 ...

从数据结构理解 MySQL 联合索引 前言 索引的本质是一种通过特定数据结构来优化数据检索速度的机制。是我们开发岗接触 MySQL 最重要的概念之一，与我们的应用开发息息相关。 结合应用思考 1）在语料平台中的 Item 表中，假设我们的目标是快速搜索 key，只考虑完全匹配的情况下，如何建立索引？ 索引的字段（业务驱动、是否要覆盖） 索引顺序（最左前缀匹配、高选择性或离散性） 2）考虑大批量写的情况下，怎样的索引可以减少性能损耗？ 3）高选择性的字段是不是一定排最前？ 4）如何在离散性与随机性之间抉择？ 1、MySQL 索引的数据结构种类 索引类型 存储引擎 底层数据结构 特点与用途 普通索引 / 主键 / 唯一索引 InnoDB B+Tree 默认且绝对主流。聚簇索引叶子节点存数据，二级索引叶子节点存主键。 普通索引 / 主键 / 唯一索引 MyISAM B+Tree 非聚簇索引，叶子节点存数据行的物理地址。 哈希索引 Memory Hash Table 默认类型，等值查询极快，用于临时表和缓存。 自适应哈希索引 InnoDB Hash Table 内部自动功能，自动缓存热点数据，加速等值查询。 全文索引 (FULLTEXT) InnoDB, MyISAM 倒排索引 解决 LIKE ‘%…%’ 性能问题，用于全文检索。 空间索引 (SPATIAL) InnoDB, MyISAM R-Tree 专用于地理空间数据类型查询。 其他索引数据结构：Skip List跳表、红黑树、BitMap 位图、Trie 前缀树 2、B+树 B+Tree 是多路平衡搜索树，一种专为磁盘等外部存储设备设计的、多路的、平衡的搜索树。它的主要目标是减少磁盘 I/O 次数，从而高效地支持大规模数据的增删改查，尤其是范围查询。 ...