分布式查询:
今天分布式查询出现以下错误:
Caused by: java.sql.SQLException: UDAL - DBProxy internal error: invalid route in sql, multi tables found but datanode has no intersection sql:select pomi.offer_obj_inst_rel_id as memberinstid,pomi.offer_inst_id as prodofferinstid,pomi.obj_id as memberid,pomi.prod_offer_member_id,pomi.status_cd,pomi.status_date,pomi.create_date,pomi.eff_date,pomi.exp_date,pomi.lan_id as areaid,pomi.region_id as regioncd,pomi.create_staff,pomi.update_date,pomi.update_staff,pomi.role_id as rolecd,pomi.rec_update_date,pomi.bank_id,pomi.bank_account from offer_obj_inst_rel pomi,offer_obj_rel pom where pomi.prod_offer_member_id = pom.prod_offer_member_id and pom.class_id = '1' and pomi.obj_id in (107482) and pomi.status_cd != '1100' route map:{OFFER_OBJ_REL=[CONF], OFFER_OBJ_INST_REL=[ASSET47, ASSET29, ASSET46, ASSET28, ASSET45, ASSET27, ASSET44, ASSET26, ASSET25, ASSET24, ASSET23, ASSET48, ASSET22, ASSET20, ASSET01, ASSET21, ASSET02, ASSET03, ASSET42, ASSET04, ASSET43, ASSET05, ASSET40, ASSET06, ASSET41, ASSET07, ASSET08, ASSET09, ASSET38, ASSET37, ASSET39, ASSET34, ASSET10, ASSET33, ASSET36, ASSET35, ASSET13, ASSET30, ASSET14, ASSET31, ASSET11, ASSET32, ASSET12, ASSET17, ASSET18, ASSET15, ASSET16, ASSET19]}
按照以前的理解,sql语句并没有写错,询问导师之后才知道,这两个表一个是单库表,一个是分片表,分布式数据库中分片表和单库表是不允许联合查询的。
于是自行百度搜索了mysql关于分表分区,分库分表的含义与区别:
(一)什么是分表分区:
分表:分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名,然后去操作它。
分区:分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据。
(二)分表与分区区别:
mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完正的一张表,都对应三个文件,一个.MYD数据文件,.MYI索引文件,.frm表结构文件。
分区不一样,一张大表进行分区后,还是一张表,不会变成两张表,但是存放数据的区块变多了。
(三)分表的3种方法:
1、做mysql集群,例如:利用mysql cluster ,mysql proxy,mysql replication,drdb等等(后续加强学习)
2、自定义规则分表
大表可以按照业务的规则来分解为多个子表。通常为以下几种类型,也可自己定义规则。
Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
Hash(哈希)–这中模式允许通过对表的一个或多个列的Hash
Key
进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
Key
(键值)-上面Hash模式的一种延伸,这里的Hash
Key
是MySQL系统产生的。
List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。
Composite(复合模式) –以上模式的组合使用
如按照日期查找对应的表名:
var getTableName =
function
() {
var data = {
name
:
'tom'
,
money: 2800.00,
date
:
'201410013059'
};
var tablename =
'account_'
;
var
year
= parseInt(data.
date
.
substring
(0, 4));
if (
year
< 2012) {
tablename += 2011; // account_2011
}
else
if (
year
< 2013) {
tablename += 2012; // account_2012
}
else
if (
year
< 2014) {
tablename += 2013; // account_2013
}
else
if (
year
< 2015) {
tablename += 2014; // account_2014
}
else
{
tablename += 2015; // account_2015
}
return
tablename;
}
3、
利用merge存储引擎来实现分表
merge分表,分为主表和子表,主表类似于一个壳子,或者说是连接池,逻辑上封装了子表,实际上数据都是存储在子表中的。
子表1:c
CREATE TABLE `account_2011` (`id` int(11) NOT NULL AUTO_INCREMENT ,`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,`money` float NOT NULL ,`tradeDate` datetime NOT NULLPRIMARY KEY (`id`))ENGINE=MyISAMDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=2CHECKSUM=0ROW_FORMAT=DYNAMICDELAY_KEY_WRITE=0;
子表2:
CREATE TABLE `account_2012` (`id` int(11) NOT NULL AUTO_INCREMENT ,`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,`money` float NOT NULL ,`tradeDate` datetime NOT NULLPRIMARY KEY (`id`))ENGINE=MyISAMDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=2CHECKSUM=0ROW_FORMAT=DYNAMICDELAY_KEY_WRITE=0;
主表:
CREATE TABLE `account_all` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money` float NOT NULL ,
`tradeDate` datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MRG_MYISAM
DEFAULT
CHARACTER
SET
=utf8
COLLATE
=utf8_general_ci
UNION
=(`account_2011`,`account_2012`)
INSERT_METHOD=
LAST
ROW_FORMAT=
DYNAMIC
;
(四)分区的几种方式:
(1)Range:
create
table
range(
id
int
(11),
money
int
(11) unsigned
not
null
,
date
datetime
)partition
by
range(
year
(
date
))(
partition p2007
values
less than (2008),
partition p2008
values
less than (2009),
partition p2009
values
less than (2010)
partition p2010
values
less than maxvalue
);
(2)List
create
table
list(
a
int
(11),
b
int
(11)
)(partition
by
list (b)
partition p0
values
in
(1,3,5,7,9),
partition p1
values
in
(2,4,6,8,0)
);
(3)hash:
create
table
hash(
a
int
(11),
b datetime
)partition
by
hash (
YEAR
(b)
partitions 4;
(4)key:
create
table
t_key(
a
int
(11),
b datetime)
partition
by
key
(b)
partitions 4;