Mysql之案例分析（一） (4)

A、B两个用户同时关注对方，即使session2中select先于session1中insert操作，session2也无法感知其未提交的数据。从而两个session执行完后建立了双向关注，但未建立好友关系。如何解决？

方案1：按照规则，使AB互关映射到同一条数据上，通过行锁冲突+on duplicate key实现好友关系的建立

-- 增加互关关系字段 ALTER TABLE `user_like` ADD COLUMN `relation_ship` int NOT NULL AFTER `liker_id`; -- 按照用户编号正序排列，不关A关注B，还是B关注A，都会命中同一条数据，用relation_ship标识两者之间的关系 -- A关注B，若A=1、B=2 insert into user_like(user_id,liker_id,relation_ship) values(1,2,1) on duplicate key update relation_ship = relation_ship|1; -- A关注B，若A=2、B=1 insert into user_like(user_id,liker_id,relation_ship) values(1,2,2) on duplicate key update relation_ship = relation_ship|2; -- 查询AB之前的关系 select relation_ship from user_like where user_id=1 and liker_id=2; -- 以上两条insert执行后，上一步查询的relation_ship=1|2=3，可执行好友插入 insert ignore into user_friend(friend_1_id, friend_2_id) values(1,2);

on duplicate key需要建立在主键或者唯一键的基础上

insert ignore可保证好友插入的幂等性

好处在于两条数据记录了关注和好友关系

坏处在于不便于查询场景实现（可在异构数据源上进行查询）

查询场景复杂化，例如：查询用户A关注的用户，(user_id=A and relation_ship<>2) or (liker_id=A and relation_ship=3)

现有索引无法满足查询场景

分表的情况下，无法映射指定用户到单一的表上，例如：查询用户A关注的用户

方案2：新的事务中或者异步调用好友关系建立服务

begin; -- 验证双向关系是否存在，即存在两条数据 select couny(*) from user_like where user_id in (1,2) and liker_id in (1,2); -- 双向关系存在，插入两条双向好友关系 insert ignore into user_friend (friend_1_id,friend_2_id) select user_id,liker_id from user_like where user_id in (1,2) and liker_id in (1,2);

好处在于关注和好友关系明确，查询实现简单

坏处在于数据存储量翻倍，且关注和友好的建立不在同一个事务中，好友的建立有可能失败，需要提供补偿机制

更新中当前读问题 CREATE TABLE `t` ( `id` INT (11) NOT NULL, `a` INT (11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = INNODB; insert into t values(1,2); session A session B
begin;    
select * from t where id=1;    
  update t set a=3 where id=1;  
update t set a=3 where id=1;    
select * from t where id=1;    
update t set a=4 where id=1;    
select * from t where id=1;    

session A中后两次select返回结果是什么？

有疑问的在于第二次，由于session B中已经把a修改为了3，session A中update是当前读，就看是否可以感知a已变更为3。MySQL 8.0.11中已感知不会执行修改操作，第二次读取的快照读还是(1,2)。有说法是update中当前读读取的只是where条件中的列，无法感知a是否变更，执行了修改操作，第二次读取结果为(1,3)

随机显示N条数据

方案1：随机函数排序

-- 不建议采用：排序耗费资源 select * from t order by rand() limit n;

方案2：随机主键

-- 查询主键取值区间 select max(id),min(id) into @M,@N from t ; -- 随机一个主键区间的值 set @X=floor((@M-@N+1)*rand() + @N); -- 随机的主键值可能不存在，使用范围查找 select * from t where id >= @X limit 1;

缺点：

只适用取一条数据，多条数据返回查找可能不够

主键分布不均衡的情况下，不同行概率不一样，例如：1、2、3、40000、400001

方案3：随机行数

-- 获取总行数 select count(*) into @C from t; -- 设置随机显示数量 set @N = 1; -- 计算起始行数 set @Y = floor(@C * rand())-@N+1; -- 拼接sql set @sql = concat("select * from t limit ", @Y, ",", @N); -- 预处理语句 prepare stmt from @sql; -- 执行语句 execute stmt; -- prepare、execute、deallocate统称为prepare statement，称为预处理语句，deallocate用于释放资源 deallocate prepare stmt;

join优化 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; create table t2 like t1; create table t3 like t2; -- 初始化三张表的数据 insert into ... -- 以下查询需要加哪些索引来优化？ SELECT * FROM t1 JOIN t2 ON (t1.a = t2.a) JOIN t3 ON (t2.b = t3.b) WHERE t1.c >= X AND t2.c >= Y AND t3.c >= Z;

索引原则，尽量使用BKA算法，小表作为驱动表，假设第一个驱动表为：

t1：连接顺序为t1->t2->t3，要在被驱动表字段创建上索引，也就是 t2.a 和 t3.b 上创建索引

t2：连接顺序不确定，需要评估另外两个条件的过滤效果，都需要在t1.a、t3.b上创建索引

t3：连接顺序是 t3->t2->t1，需要在 t2.b 和 t1.a 上创建索引

同时，还需要在第一个驱动表的字段 c 上创建索引

自增主键是否连续

自增主键可能不连续，可能原因如下：

自增值保存策略