MySQL分区是一个真实世界的例子

Question

我想在一个足球幻想游戏中尝试MySQL分区，在这个游戏中，用户分布在联赛中，每个联赛都有一个市场，用户可以在其中出售或购买球员。当很多用户同时玩游戏时，我在这个表中遇到了一些死锁（在撰写本文时，大约有50万个联盟，每个联盟大约有20名玩家，每两天更新一次），所以我考虑使用MySQL分区，这是我以前从未使用过的。

这是我想分区的表：

CREATE TABLE `market` (
  `leagueID` int(10) unsigned NOT NULL,
  `playerID` smallint(5) unsigned NOT NULL,
  `userID` int(10) unsigned DEFAULT,
  `price` int(10) unsigned NOT NULL ,
  `date` int(10) unsigned NOT NULL,
  UNIQUE KEY `league_player` (`leagueID`,`playerID`),
  KEY `user_date` (`userID`,`date`)
);

您推荐哪种方法（列、范围、分区数等）？

这是我最初的方法：

ALTER TABLE market
    PARTITION BY HASH(leagueID)
    PARTITIONS 10;

Answer 1

为什么要分区？

我之所以这么问，是因为大多数分区尝试都一无所获。没有性能提升；有时性能下降。特别是BY HASH很少有帮助。

你在使用MyISAM吗？如果是，请切换到InnoDB。既然你提到了"死锁"，也许你已经在使用InnoDB了？分区对事务死锁没有帮助；我们需要查看这两个事务中的查询。该解决方案可以像对IN列表进行排序一样简单。

但是。。。不管我们是否"解决"了您今天遇到的死锁，您都需要检查错误，并回放中止的整个事务。这是解决死锁的唯一可靠方法。

Datacharmer的幻灯片为您提供了血腥的细节；我的博客列出了PARTITIONing有用的极少数案例，从而使他的大多数幻灯片变得毫无用处。

其他问题。。。

• 我没有看到PRIMARY KEY。建议您将UNIQUE密钥更改为PRIMARY KEY。InnoDB确实需要PK
• 存在DATE数据类型；它的使用可能没有一些CCD_ 8那么笨拙

Answer 2

我已经看到类似的东西好几次了，我不相信分区能解决你的问题。

您在唯一索引上看到的死锁可能是由于使用锁所在的同一数据库页的两个非冲突插入造成的。由于索引的B树结构，如果单个联盟在特定时间窗口中写得很重（似乎是合理的），则该页上的互斥锁被命中的竞争条件的可能性更高。

我会考虑通过更改唯一约束中两个字段的顺序来物理地分布索引"热点"，并将现有索引添加为常规索引（出于查找原因）。

ALTER TABLE `market` 
ADD UNIQUE KEY player_league (`playerID`,`leagueID`),
DROP UNIQUE KEY `league_player`,
ADD KEY league_player (`leagueID`,`playerID`);

在这一点上，由于您还没有主键（但InnoDB无论如何都会在幕后创建一个主键），因此从逻辑上讲，新密钥扮演这个角色是有意义的。

ALTER TABLE `market` 
ADD PRIMARY KEY (`playerID`,`leagueID`),
DROP UNIQUE KEY `league_player`,
ADD KEY league_player (`leagueID`,`playerID`);