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

Question

我有一个表 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 和 32M 行)。创新数据库。其中的行是随机添加的(每个查询一个)。此外，一些游戏正在被删除。

我需要减少磁盘 IO 并提高响应能力。

我应该使用key 还是range 分区？如果是 range，那么是 id 还是 game_id？有什么理论吗？

Answer 1

使用范围分区。

如果您按键分区，则您的两个示例查询都必须触及每个分区。

理论上，按 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