掌心男友遇到的坑和解决方案

背景

掌心男友 V1 上线后陆续出现用户存档数据丢失的问题，一开始以为是数据库主从同步延时导致的，就在更新查询的时候开启事务查询保证查询是在主库上，然后再进行用户选项 json 的增量拼接，但是。。。。问题依旧存在

message:[controllers/aibf/history.go:133] 存在选项未选择 c="{546 3 selection 1 [{\"type\":\"text\",\"content\":\"你不生气吗？\",\"point\":0,\"condition\":\"1\",\"condition_to\":547},{\"type\":\"text\",\"content\":\"吓我一跳，还以为你生气了……\",\"point\":2,\"condition\":\"1\",\"condition_to\":547},{\"type\":\"text\",\"content\":\"真是没想到，你居然没有生气啊。\",\"point\":-2,\"condition\":\"1\",\"condition_to\":547}] 1 553 0 0 43860 43980}" character_id=3 profile_id=1466251 selection="map[508:1 513:0 515:1 517:2 519:0 521:2 523:2 525:1 528:0 530:0 532:0 534:2 536:0 538:0 540:0 542:1 556:0 558:2 560:0 562:0 564:0 566:2 568:2 570:2 574:2 576:0 578:2 580:0 583:1 585:2 587:2 591:0 593:1 596:0 598:0 602:0 605:2 616:1 618:1 621:0]" _id:tV3RMHsBX-l6pBejdzEq _type:doc _index:log-2021.08.10 _score: -

悲观锁：

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像for update，再比如Java里面的同步原语 synchronized 关键字的实现也是悲观锁。

for update

for update 的作用是在查询的时候为行加上排它锁，当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。
它的典型使用场景是高并发并且对于数据的准确性有很高要求，比如金钱、库存等，一般这种操作都是很长一串并且开启事务的，假如现在要对库存进行操作，在刚开始读的时候是1，然后马上另外一个进程将库存更新为0了，但事务还没结束，会一直用1进行后续的逻辑，就会有问题，所以需要用for upate 加锁防止出错，记住一个原则：一锁二判三更新。

for update 使用方式

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

1	select * from table_name where ... for update;

行级锁？表级锁？

InnoDB 默认是行级锁，当有明确指定的主键时候，是行级锁，否则是表级锁。
假设表 user，存在有id跟name字段，id是主键

行级锁

明确指定主键，并且有此记录，行级锁

1 2	SELECT * FROM user WHERE id = 1 FOR UPDATE; SELECT * FROM user WHERE id = 1 and name = 'segon' FOR UPDATE;

进程 1	进程 2
begin;
SELECT * FROM user WHERE id = 1 FOR UPDATE;
	UPDATE user SET name = ‘test’ WHERE id = 2; – 成功
	UPDATE user SET name = ‘test’ WHERE id = 1; – 等待
commit;
	执行等待的任务，成功

表级锁

无主键，表级锁

1	SELECT * FROM user WHERE name = 'segon' FOR UPDATE;

主键不明确，表级锁

1 2	SELECT * FROM user WHERE id <> 3 FOR UPDATE; SELECT * FROM user WHERE id LIKE '3' FOR UPDATE;

进程 1	进程 2
begin;
SELECT * FROM user WHERE id LIKE ‘3’ FOR UPDATE;
	UPDATE user SET name = ‘test’ WHERE id = 1; – 等待
commit;
	执行等待的任务，成功

无锁

明确指定主键，若查无此记录，无锁

1	SELECT * FROM user WHERE id = -1 FOR UPDATE;

进程 1	进程 2
begin;
SELECT * FROM user WHERE id = -1 FOR UPDATE;
	UPDATE user SET name = ‘test’ WHERE id = 2; – 成功
commit;

掌心男友部分代码：

var saveData SaveData
// 查询主库判断当前主题是否完成等校验，同时拿到存档主键
err := servicedb.Tx(service.DB, func(tx *gorm.DB) error {
err := tx.Table(SaveData{}.TableName()).
Where("chat_id = ? AND theme_id = ? AND profile_id = ?", chatID, themeID, profileID).First(&saveData).Error
if err != nil {
return err
}
return nil
})
err := servicedb.Tx(service.DB, func(tx *gorm.DB) error {
// 加行锁再次查询存档数据
err := servicedb.ForUpdate(tx).First(&saveData, ID).Error
if err != nil {
return err
}

// 存档进度选项数据 json 累加处理

// 更新数据库
err = tx.Model(SaveData{}).Where("id = ?", charaSaveData.ID).Updates(updataSaveData).Error
// 提交事务，行锁释放
return nil
})

乐观锁：

顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

乐观锁一般来说有以下2种方式：

使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

声恋活动代码

var u UserData
// 开启事务保证查询是主库最新
err := servicedb.Tx(service.MiniGameDB, func(tx *gorm.DB) error {
return service.MiniGameDB.Where("user_id = ? AND character_id = ?", userID, characterID).
First(&u).Error
})

update := map[string]interface{}{
// 查询出来的数据做处理
"point": gorm.Expr("point + ?", u+addend),
"version": u.Version + 1,
}
// 判断版本号是否一致，如果一致则更新，不一致提示重试等处理
db := service.MiniGameDB.Table(u.TableName()).Where("id = ? AND version = ?", u.ID, u.Version).Update(update)

掌心男友修数据案例及优化

SELECT
`profile_id`
FROM
m_game_zxbf_savedata
WHERE
(chat_id = 8 AND theme_id = 63 AND `end` = 1)
OR
(chat_id = 1 AND theme_id = 64 AND `status` = 0)
GROUP BY profile_id HAVING (COUNT(id) = 2)

耗时：32111ms

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	m_game_zxbf_savedata	index	idx_profileid_themeid,idx_profileid_chatid_themeid	idx_profileid_themeid	18		9533122	Using where

SELECT profile_id FROM (
SELECT `profile_id` FROM m_game_zxbf_savedata WHERE (chat_id = 8 AND theme_id = 63 AND `end` = 1) UNION ALL
SELECT `profile_id` FROM m_game_zxbf_savedata WHERE (chat_id = 1 AND theme_id = 64 AND `status` = 0)
) t GROUP BY profile_id HAVING (COUNT(*) = 2)

耗时：7875ms

id	select_type	table	type	rows	Extra
1	PRIMARY	（派生表）	ALL	19066298	Using temporary; Using filesort
2	DERIVED	m_game_zxbf_savedata	ALL	9533149	Using where
3	UNION	m_game_zxbf_savedata	ALL	9533149	Using where
	UNION RESULT	<union2,3>	ALL		Using temporary

id

id是用来顺序标识整个查询中SELELCT 语句的，在嵌套查询中id越大的语句越先执行。该值可能为NULL，如果这一行用来说明的是其他行的联合结果。

Explain

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）
table	访问引用哪个表
type	数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL）
possible_keys	揭示哪一些索引可能有利于高效的查找
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	显示了之前的表在key列记录的索引中查找值所用的列或常量
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
Extra	额外信息，如using index、filesort等

table

对应行正在访问哪一个表，表名或者别名

关联优化器会为查询选择关联顺序，左侧深度优先
mysql的关联查询：总是从一个表开始一直嵌套循环、回溯完成所有表关联，是一个左侧深度优化的树。它决定了多个表关联时的顺序，通常多表关联的时候，可以有多种不同的关联顺序来获得相结果的执行结果。关联查询优化器通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

当from中有子查询的时候，表名是derivedN的形式，N指向子查询，也就是explain结果中的下一列
当有union result的时候，表名是union 1,2等的形式，1,2表示参与union的query id

注意：MySQL对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。

select_type

类型	说明
simple	简单子查询，不包含子查询和union
primary	包含union或者子查询，最外层的部分标记为primary
subquery	一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询
derived	派生表——该临时表是从子查询派生出来的，位于form中的子查询
union	位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived
union result	用来从匿名临时表里检索结果的select被标记为union result
dependent union	顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
subquery	子查询中第一个SELECT语句
dependent subquery	和DEPENDENT UNION相对UNION一样

Extra

Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

Using temporary：用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

Using where：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

Using filesort：MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。

Using temporary ：用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

UPDATE `m_game_zxbf_savedata` SET `status` = 2, `conversation_id` = 11751 WHERE `theme_id` = 64 AND `profile_id` IN (
SELECT profile_id FROM (
SELECT `profile_id` FROM m_game_zxbf_savedata WHERE (chat_id = 8 AND theme_id = 63 AND `end` = 1) UNION ALL
SELECT `profile_id` FROM m_game_zxbf_savedata WHERE (chat_id = 1 AND theme_id = 64 AND `status` = 0)
) t GROUP BY profile_id HAVING (COUNT(*) = 2)
);

If you have a slow ‘correlated’ subquery with IN, you can optimize it with a join to get around the bug described by Ryan and Stephen. After the optimization the execution time is no longer O(M×N).

如果您有一个与 IN 相关的缓慢“相关”子查询，您可以使用连接对其进行优化，以解决 Ryan 和 Stephen 描述的错误。优化后的执行时间不再是 O(M×N)。

MySQL UPDATE JOIN语法

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,
T2.C3 = expr
WHERE condition

让我们更详细地看看MySQL UPDATE JOIN语法：

首先，在UPDATE子句之后，指定主表(T1)和希望主表连接表(T2)。请注意，必须在UPDATE子句之后至少指定一个表。UPDATE子句后未指定的表中的数据未更新。
第二，指定一种要使用的连接，即INNER JOIN或LEFT JOIN和连接条件。JOIN子句必须出现在UPDATE子句之后。
第三，要为要更新的T1和/或T2表中的列分配新值。
第四，WHERE 子句中的条件用于指定要更新的行。

UPDATE `m_game_zxbf_savedata` AS s INNER JOIN (
SELECT profile_id FROM (
SELECT `profile_id` FROM m_game_zxbf_savedata WHERE (chat_id = 8 AND theme_id = 63 AND `end` = 1) UNION ALL
SELECT `profile_id` FROM m_game_zxbf_savedata WHERE (chat_id = 1 AND theme_id = 64 AND `status` = 0)
) t GROUP BY profile_id HAVING (COUNT(*) = 2)
) AS p ON s.profile_id = t.profile_id SET s.status = 2, s.conversation_id = 11751 WHERE s.theme_id = 64;