标签归档:分表

MySQL 分表测试 与 分表模型

例子:

<?php
$server="localhost";
$user="root";
$pwd="";
$db="test";
$port=3306;
$sock='/var/lib/mysql/mysql.sock';
$charset='utf8';

function mysqlidb($server,$user,$pwd,$db,$charset,$port,$sock){
	$link = false;

	$connectionRetry = 10;

	while (!isset($link) || ($link == FALSE && $connectionRetry !=0) ){
		$link = mysqli_connect($server,$user,$pwd,$db,$port,$sock);
		$connectionRetry--;
	}

	if($link) {
		if (@mysqli_select_db($link, $db)) {
			if ((trim($charset) != '') && version_compare(@mysqli_get_server_info(), '4.1.0', '>=')) {
				@mysqli_query($link, "SET NAMES '" . trim($charset) . "'");
				if (function_exists('mysqli_set_charset')) {
					@mysqli_set_charset($link, trim($charset));
				} else {
					@mysqli_query($link, "SET CHARACTER_SET_CLIENT = '" . trim($charset) . "'");
					@mysqli_query($link, "SET CHARACTER_SET_RESULTS = '" . trim($charset) . "'");
				}
			}
		}
	}
	return $link;
}

$db = mysqlidb($server,$user,$pwd,$db,$charset,$port,$sock);

function getTableNum($id) {
	$cap = 200;
	return (int)($id / $cap);
}

function hasTable($table = '') {
	global $db;
	if(empty($table)) {
		return FALSE;
	}
	
	$r = mysqli_query($db,"SELECT table_name FROM information_schema.TABLES WHERE table_name ='$table';");
	$row = mysqli_fetch_assoc($r);
	
	if(!empty($row)) {
		return TRUE;
	}	
	
	return FALSE;
}

function createTable($table = '') {
	global $db;
	
	if(empty($table)) {
		return FALSE;
	}
	
	if(!hasTable($table)) {
		$dd = mysqli_query($db,'
			CREATE TABLE `'.$table.'` (
			`id`  int(11) NOT NULL ,
			`body`  text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
			PRIMARY KEY (`id`)
			)
			ENGINE=InnoDB
			DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
			ROW_FORMAT=COMPACT
			;
		');
		if((int)$dd < 1) {
			$find = FALSE;
		}		
	}
	return TRUE;
}

/**/
for($i = 1; $i < 1000; $i++) {
	//echo $i . " - ". getTableNum($i)."\n";
	$num = getTableNum($i);
	$table = "t_".$num;
	
	if(!hasTable($table)) {
		createTable($table);
	}
	
	$sql = "INSERT INTO `".$table."` (`id`,`body`) values(".$i.",'123456789')";
	//echo $sql;
	$r = mysqli_query($db,$sql);
}


//for($i = 800; $i < 900; $i++) {
	$i = mt_rand(1, 1000);

	$num = getTableNum($i);
	$table = "t_".$num;
	
	if(hasTable($table)) {
		$sql = "SELECT * FROM `".$table."` WHERE id='".$i."' ";
		$r = mysqli_query($db,$sql);
		
		$row = mysqli_fetch_assoc($r);
		echo "From Table(".$table."):" . $row['id']."\n";
		
	} else {
		echo $table."表不存在,记录也就不存在。";
	}
//}

以下试着用一个具体模型来说明:
mysql-table-model

关联用户的订单表是一对多关系,根据订单关联用户这个维度,使用用户的ID,把订单放入各个分表中存放。比如:

function getTableNum($id) {
	$cap = 200;
	return (int)($id / $cap);
}

比如用户的ID从1-200,放入订单表_1,用户的ID从201-400,放入订单表_2等,这样的实现方式最简单高效,做一个除法运算就可以定位到分表号。

情况一:
知道订单ID,要到订单分表获取信息,那么首先要到订单主表去获取这个ID的订单,然后取回用户ID号,然后用这个用户ID号定位到分表,然后到分表中根据订单的ID定位到订单。这个过程说明,分表中需要保持订单ID。这个表现为主表与分表之间的一对一关系。

情况二:
知道用户ID,要定位用户所有的订单,可以根据用户ID定位到分表号,然后到分表中获取所有具有这个用户ID的订单。这个过程说明,分表中需要保存用户ID。这个表现为分表跟用户表之间的一对多关系。

情况三:
要获取一段时间内的所有订单。这个必须是查询订单主表,如果需要到保存在订单详情中的信息,需要循环结果集,分别到订单分表中获取。这个过程说明,主表需要保存尽可能多的元数据,这些元数据也要保存到分表中,分表中保存的是大数据。

这个是典型的一对多模型进行水平扩展的方案,可以满足一定数据存储的需求。不过缺点也明显,主表虽然只保存元数据,容量可能不会太大,但是记录的数量可能很大,比如千万级别的订单。如果一天10w,一个月就是300w,一年就是3600w,不用三年就到达亿级。

所以订单主表还需要进一步进行处理,可以按照时间对其进行归档处理,比如按照年来进行归档,当要查询某个时间段的订单时,可以定位到年归档表查询(如果时间跨越了一年,需要分别进行查询,然后合并结果)。

另外,用户表数量也可能非常大,那么可以继续对用户表进行水平扩展,不过这个扩展跟上面的根据用户ID对应订单进行水平扩展的方式是不一样的。用户表只保存元数据,根据ID定位到分表号(还是一样的函数):

function getTableNum($id) {
	$cap = 200;
	return (int)($id / $cap);
}

一般应该使用一样的分表算法。

以上的两个分表方式,实际是差不多相同,只不过订单的分表是间接根据用户来来分的,而用户表的分表则直接根据用户ID进行拆分,间接分表这种方式是比较常见的,它应该满足一对多的关系模型,在遇到其它需要分表的情况,对照这个模型思考一下就可以。

以上的分表方案可以说是最简单的了,可以看到,由于引入了分表,所有相关的程序也会变得复杂一些。经常可能碰到的概念是分库,一般策略应该是垂直分库(根据业务模型拆分,类似多个子系统,子系统实现水平分表),水平分库(每个库都一样的结构)看起来会比较复杂,需要解决全局ID、事务等问题,当然目前方案是有的….