mysql多个唯一索引怎么分区_如何用两个索引对MySQL表进行分区

使用按范围分区.

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

从理论上讲,通过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