按照我的理解,分区的目的是方便select,但是在insert上会增加开销在方便select方面,是不是分区越细,查找的速度越快呢?一般分区都分成多少个区?望解惑。
解决方案 »
- mysql中总用enum类型是不是不太好啊?
- 我要凌乱了!Table 'mysql.host' doesn't exist
- 求救关于mysql查询Sending data时间过长引起数据库卡死问题
- mysql 如何按周进行分区?
- 查询问题
- 关于MYSQL全文检索
- MYSQL中如何实现 like '[1,2,3,4]%'呢?
- 在WinXP上装完MySQL后无法启动MySQL服务
- 请问在SQL SERVER中建立一个表时,一个表最多可以建立多少个字段?在线等待.
- 根据条件判断更新,无数据的更新为零,如何构造,谢谢
- mysql中看看这个存储过程老是报错
- mysql数据库同步问题,纠结死了,请帮忙看一下!
CREATE TABLE `dsdvrdbe`.`recorddata` (
`id` bigint(20) unsigned NOT NULL,
`ChannelNO` tinyint(3) unsigned NOT NULL,
`BeginTime` decimal(18,0) NOT NULL,
PRIMARY KEY (`id`,`ChannelNO`,`BEGINTIME`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (ChannelNO)
SUBPARTITION BY HASH(begintime%1000000000 DIV 10000000)
SUBPARTITIONS 24
(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (4) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (5) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (6) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (7) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (8) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (9) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (10) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (11) ENGINE = MyISAM,
PARTITION p11 VALUES LESS THAN (12) ENGINE = MyISAM,
PARTITION p12 VALUES LESS THAN (13) ENGINE = MyISAM,
PARTITION p13 VALUES LESS THAN (14) ENGINE = MyISAM,
PARTITION p14 VALUES LESS THAN (15) ENGINE = MyISAM,
PARTITION p15 VALUES LESS THAN (16) ENGINE = MyISAM,
PARTITION p16 VALUES LESS THAN (17) ENGINE = MyISAM,
PARTITION p17 VALUES LESS THAN (18) ENGINE = MyISAM,
PARTITION p18 VALUES LESS THAN (19) ENGINE = MyISAM,
PARTITION p19 VALUES LESS THAN (20) ENGINE = MyISAM,
PARTITION p20 VALUES LESS THAN (21) ENGINE = MyISAM,
PARTITION p21 VALUES LESS THAN (22) ENGINE = MyISAM,
PARTITION p22 VALUES LESS THAN (23) ENGINE = MyISAM,
PARTITION p23 VALUES LESS THAN (24) ENGINE = MyISAM,
PARTITION p24 VALUES LESS THAN (25) ENGINE = MyISAM,
PARTITION p25 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p26 VALUES LESS THAN (27) ENGINE = MyISAM,
PARTITION p27 VALUES LESS THAN (28) ENGINE = MyISAM,
PARTITION p28 VALUES LESS THAN (29) ENGINE = MyISAM,
PARTITION p29 VALUES LESS THAN (30) ENGINE = MyISAM,
PARTITION p30 VALUES LESS THAN (31) ENGINE = MyISAM,
PARTITION p31 VALUES LESS THAN (32) ENGINE = MyISAM,
PARTITION p32 VALUES LESS THAN (33) ENGINE = MyISAM,
PARTITION p33 VALUES LESS THAN (34) ENGINE = MyISAM,
PARTITION p34 VALUES LESS THAN (35) ENGINE = MyISAM,
PARTITION p35 VALUES LESS THAN (36) ENGINE = MyISAM,
PARTITION p36 VALUES LESS THAN (37) ENGINE = MyISAM,
PARTITION p37 VALUES LESS THAN (38) ENGINE = MyISAM,
PARTITION p38 VALUES LESS THAN (39) ENGINE = MyISAM,
PARTITION p39 VALUES LESS THAN (40) ENGINE = MyISAM,
PARTITION p40 VALUES LESS THAN (41) ENGINE = MyISAM,
PARTITION p41 VALUES LESS THAN (42) ENGINE = MyISAM,
PARTITION p42 VALUES LESS THAN (43) ENGINE = MyISAM,
PARTITION p43 VALUES LESS THAN (44) ENGINE = MyISAM,
PARTITION p44 VALUES LESS THAN (45) ENGINE = MyISAM,
PARTITION p45 VALUES LESS THAN (46) ENGINE = MyISAM,
PARTITION p46 VALUES LESS THAN (47) ENGINE = MyISAM,
PARTITION p47 VALUES LESS THAN (48) ENGINE = MyISAM,
PARTITION p48 VALUES LESS THAN (49) ENGINE = MyISAM,
PARTITION p49 VALUES LESS THAN (50) ENGINE = MyISAM,
PARTITION p50 VALUES LESS THAN (51) ENGINE = MyISAM,
PARTITION p51 VALUES LESS THAN (52) ENGINE = MyISAM,
PARTITION p52 VALUES LESS THAN (53) ENGINE = MyISAM,
PARTITION p53 VALUES LESS THAN (54) ENGINE = MyISAM,
PARTITION p54 VALUES LESS THAN (55) ENGINE = MyISAM,
PARTITION p55 VALUES LESS THAN (56) ENGINE = MyISAM,
PARTITION p56 VALUES LESS THAN (57) ENGINE = MyISAM,
PARTITION p57 VALUES LESS THAN (58) ENGINE = MyISAM,
PARTITION p58 VALUES LESS THAN (59) ENGINE = MyISAM,
PARTITION p59 VALUES LESS THAN (60) ENGINE = MyISAM,
PARTITION p60 VALUES LESS THAN (61) ENGINE = MyISAM,
PARTITION p61 VALUES LESS THAN (62) ENGINE = MyISAM,
PARTITION p62 VALUES LESS THAN (63) ENGINE = MyISAM,
PARTITION p63 VALUES LESS THAN (64) ENGINE = MyISAM )*/;
如果为64,则提示:too MANY PARTITIONS (INCLUDING SUBPARTITIONS ) WERE DEFINED
show variables like 'open_files_limit';
看看是多少?
不一定,但如果你需要查找的记录都同一个小分区内,当然是这个分区越小越好。但并不绝对。 比如你可以设想一条记录一个分区,显然这种分区并无任何意义。一般分区都分成多少个区?
这个要看具体实现情况,并不统一的数字。可多可少。