如何用两个索引对MySQL表进行分区

由 发布于 MySQLinnodbdatabase-partitioningpartitioning
 收藏

提问

我有一个表game_log,其中包含字段id,game_id和几个varchar字段.

id是主键,game_id是非唯一键.

有两个常见查询：

SELECT * FROM game_log ORDER BY id DESC LIMIT 20
SELECT * FROM game_log WHERE game_id = <value> ORDER BY id DESC

该表很大(6.1GB和3,200万行). InnoDB.其中的行是随机添加的(每个查询一个).另外,某些游戏也被删除.

我需要减少磁盘IO和提高响应速度.

我应该使用键分区还是范围分区？如果是范围,则按ID还是按game_id？有什么理论吗？

最佳答案

使用按范围分区.

如果按键分区,则两个示例查询都必须接触每个分区.

从理论上讲,通过KEY进行分区就像通过哈希进行分区一样,因为主键的连续值必须绑定存储在单独的分区中.通过查询一系列id值,可以破坏分区修剪.

演示：

CREATE TABLE `game_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL DEFAULT '0',
  `xyz` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`,`game_id`)
) 
PARTITION BY KEY ()
PARTITIONS 13;

INSERT INTO game_log (game_id) VALUES (1), (2), (3), (4), (5), (6);

EXPLAIN PARTITIONS SELECT * FROM game_log ORDER BY id DESC LIMIT 3\G
           id: 1
  select_type: SIMPLE
        table: game_log
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12

EXPLAIN PARTITIONS SELECT * FROM game_log WHERE game_id = 4 ORDER BY id DESC LIMIT 3\G
           id: 1
  select_type: SIMPLE
        table: game_log
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12

而如果您按game_id上​​的范围进行分区,则至少在查询特定的game_id时,可以进行分区修剪以帮助您.但是您通过id desc查询的任何game_id订单仍然绑定到每个分区.

CREATE TABLE `game_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL DEFAULT '0',
  `xyz` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`,`game_id`)
)
PARTITION BY RANGE (game_id)
(PARTITION p1 VALUES LESS THAN (3),
 PARTITION p2 VALUES LESS THAN (6),
 PARTITION p3 VALUES LESS THAN MAXVALUE);

INSERT INTO game_log (game_id) VALUES (1), (2), (3), (4), (5), (6);

EXPLAIN PARTITIONS SELECT * FROM game_log ORDER BY id DESC LIMIT 3\G
           id: 1
  select_type: SIMPLE
        table: game_log
   partitions: p1,p2,p3

EXPLAIN PARTITIONS SELECT * FROM game_log WHERE game_id = 4 ORDER BY id DESC LIMIT 3\G
           id: 1
  select_type: SIMPLE
        table: game_log
   partitions: p2
评论