标签归档:MySQL

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、事务等问题,当然目前方案是有的….

MySQL客户端工具

命令行
MySQL提供了一系列管理工具,比如mysql mysqladmin等

GUI
1) MySQL Workbench
地址:http://dev.mysql.com/downloads/workbench/
workbench
官方出品。社区版免费。对于GUI工具,如果你不想付费,Workbench社区版是我们不二的选择。

2) Navicat(付费)
地址:http://www.navicat.com
navicat
Navicat不仅仅支持MySQL,针对MySQL提供了Navicat for MySQL,界面非常友好,操作简便。
对于GUI工具,Navicat for MySQL是付费工具中不二的选择。

这个工具管理数据库系统不是它的强项,它强大的地方在于操作操作数据。我最初使用它是因为它非常便利的数据导入导出功能。比如我可以在查询分析器中运行一个查询,然后把这个查询的结果导出(可以是Excel表等多种格式),然后把这个结果导入到另个库。

3)SQLyog(付费)
地址:http://www.webyog.com/en/index.php
相比Navicat,视乎要轻量一些。

PHP WEB
1)phpMyAdmin
地址:http://www.phpmyadmin.net
phpmyadmin
phpMyAdmin是开源软件。如果你不希望开放数据库远程链接,希望使用一个WEB工具管理MySQL,那么phpMyAdmin就是我们不二的选择。

2)MySQLDumper
地址:http://www.mysqldumper.de/en
看其名字,就知道它是一个专用工具。

MySQL提供的命令行工具,对于一个DBA是需要熟悉掌握的。但是对已应用程序开发人员,使用命令行工具,显然比较低效,而选择一个GUI工具是必须的,官方的Workbench社区版虽然免费,但是使用上感觉笨重一些,视乎它是针对DBA的GUI工具。Navicat应该是一个针对开发人员做得最友好的工具。SQLyog比Navicat轻量,但是从易用性上看,个人感觉它比Navicat还是差很多。如果你觉得Workbench笨重,Navicat和SQLyog又要付费,那么可选的可能就只有phpMyAdmin了。

注:这里只是列举了本人实际使用过的工具。刚开始接触MySQL时,使用过Workbench(以前不叫这个名字),后来学习了命令行工具。开发中常常使用phpMyAdmin,后来试用了Navicat,感觉它非常不错,于是一直在试用。SQLyog看别人使用,于是自己也折腾了下,感觉不如Navicat,于是就不再试用了。MySQLDumper没有用过,这里基本是凑数了。

Zen-cart MySQL判断表的某字段是否存在

MySQL中可以通过如下语句把表的结构返回

SHOW COLUMNS FROM 表名;

mysql> show columns from countries;
+----------------------+-------------+------+-----+---------+----------------+
| Field                | Type        | Null | Key | Default | Extra          |
+----------------------+-------------+------+-----+---------+----------------+
| countries_id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| countries_name       | varchar(64) | NO   | MUL |         |                |
| countries_iso_code_2 | char(2)     | NO   | MUL |         |                |
| countries_iso_code_3 | char(3)     | NO   | MUL |         |                |
| address_format_id    | int(11)     | NO   | MUL | 0       |                |
| countries_name_cn    | varchar(64) | YES  |     |         |                |
+----------------------+-------------+------+-----+---------+----------------+

可以看到,这个格式是固定的,如果要判断某字段是否存在,只要循环判断就可以实现。

Zen-cart中对这个操作进行了封装,MySQL的工厂类($db)有一个函数:

  function metaColumns($zp_table) {
    $sql = "SHOW COLUMNS from :tableName:";
    $sql = $this->bindVars($sql, ':tableName:', $zp_table, 'noquotestring');
    $res = $this->execute($sql);    
    while (!$res->EOF) 
    {
      $obj [strtoupper($res->fields['Field'])] = new queryFactoryMeta($res->fields); 
      $res->MoveNext();
    }    
    return $obj;
  }

它把字段名作为下标,引用返回的一行数据。queryFactoryMeta类根据传入的对象的Type的值往该对象添加两个属性(type 和 max_length),用来表示字段的数据类型和长度。

class queryFactoryMeta {

  function queryFactoryMeta($zp_field) {
    $type = $zp_field['Type'];
    $rgx = preg_match('/^[a-z]*/', $type, $matches);
    $this->type = $matches[0];
    $this->max_length = preg_replace('/[a-z\(\)]/', '', $type);
  }
}

所以,如果在Zen-cart中要判断某个表的某字段是否存在,只要使用isset()就可以了,参考如下代码:

$cnt = $db->metaColumns(TABLE_GEO_ZONES);
if(!isset($cnt['GEO_ZONE_TYPE_ID'])){
	$db->Execute("ALTER TABLE ".TABLE_GEO_ZONES." ADD geo_zone_type_id INT( 11 ) NOT NULL DEFAULT 0");
}
if(!isset($cnt['COST'])){
	$db->Execute("ALTER TABLE ".TABLE_GEO_ZONES." ADD cost VARCHAR( 64 ) NULL DEFAULT ''");
}

这段代码判断某字段,如GEO_ZONE_TYPE_ID是否存在,如果不存在,就添加这个字段,这个为自动扩展系统提供了基础代码。

永久链接:http://blog.ifeeline.com/846.html

PHP PDO快速入门(实例)

1 使用DBM数据库
2 使用SQLite数据库
SQLite与PHP 5捆绑,不需要运行独立服务器的数据库。
3 连接到SQL数据库

$mysql = new PDO(‘mysql:host=db.example.com’,$user, $password);
$mysql = new PDO(‘mysql:host=db.example.com;port=31075’,$user, $password);
$mysql = new PDO(‘mysql:host=db.example.com;port=31075;dbname=food’,$user, $password);
$mysql = new PDO(‘mysql:unix_socket=/tmp/mysql.sock’,$user, $password);

PDO构造函数会返回一个用于查询相应数据库的新对象。如果出现问题则会抛出一个PDOException异常。(默认如果链接MySQL时host是指定为localhost,那么PDO会使用socket和MySQL尝试链接)

4 查询一个SQL数据库
使用PDO::query()将SQL查询发送到数据库,然后使用foreach循环抽取结果集。(这个是一个简化方式,其实有一个通用方式)

$st = $db->query(‘select symbol, planet from zodiac’);
foreach($st->fetchAll() as $row){
	echo $row[‘symbol’].’----’.$row[‘planet’];
}

query()方法返回一个PDOStatement对象。该对象的fetchAll()方法提供了对一个查询所返回的每一行记录进行操作的简洁方式。而fetch()方法每次只返回一行记录。每次调用fetch()都返回结果集中的下一条记录。如果下一条记录不存在,fetch()返回false。

在默认情况下,fetch()会返回数组($firstRow = $row->fetch()):
[0] =>Ram [1]=>Mars [‘symbol’]=>Ram [‘planet’]=>Mars,混合索引数组,内容有重复。

要想让fetch()返回的记录具有不同的格式,需要给query()传递传递PDO::FETCH_*常量作为第二个参数。
常量
PDO::FETCH_BOTH
PDO::FETCH_NUM
PDO::FETCH_ASSOC
PDO::FETCH_OBJ
PDO::FETCH_LAZY

在与bindColumn()组合使用的时候,PDO::FETCH_BOUND取出模式可以让你设置变量,这些变量的值会在每次调用fetch()时自动更新。

$row = $db->query(‘select symbol, planet from zodia’, PDO::FETCH_BOUND);
$row->bindColumn(‘symbol’, $symbol); //将symbol列的值放到$symbol变量中
$row->bindColumn(2, $planet); //将第二列的值放到$planet变量中
while($row->fetch()){
	print “$symbol goes with $planet. 
\n”; }

5 不通过循环抽取记录
使用fetchAll()一次性取得查询返回的所有结果记录。

6 修改SQL数据库中的数据
使用PDO::exec()来发送一个INSERT DELETE或UPDATE命令。

$db->exec(“insert into family (id, name) values(1, ‘vito’)”);

还可以使用PDO::prepare()来准备一条查询语句,并通过PDOStatement::execute()来执行该查询。

$st = $db->prepare(‘insert into family (id, name) values(?,?)’);  //返回一个PDOStatement对象
$st->execute(array(1,’vito’));

$st = $db->prepare(‘delete from family where name like ?’);  //返回一个PDOStatement对象
$st->execute(array(’Fredo’));

PDO::exec()方法会把传递给它的任何内容都发送给数据库。对于INSERT UPDATE和DELETE查询,该方法返回的是被相应查询影响的行数。

而prepare()和execute()方法则对于想要多次执行的查询非常有用。

$st = $db->prepare(‘delete from family where name like ?’);
$st->execute(array(‘Fredo’));
$st->execute(array(‘Sonny’));

7 有效地重复查询
用PDO::prepare()来建立查询,然后对由prepare()返回的,准备好的语句调用execute()来执行。
使用命名的占位符

$st = $db->prepare(“select sign from zodiac where element like :element OR planet like :planet”);
$st->execute(array(‘planet’ => ‘Mars’, ‘element’ => ‘earth’));
$row = $st->fetch();

除了?和命名占位符以为,prepare()还提供了在查询语句中插入值的第三种方式:bindParam()。该方法可以自动地将变量中的值关联到特定的占位符。

$pairs = array();
$st = $db->prepare(“select sign from zodiac where element like :element OR planet like :planet”);
$st->bindParam(‘:element’, $element);
$st->bindParam(‘:planet’, $planet);
foreach($pairs as $planet => $element){
	$st->execute();  //不需要传递任何值给execute()
	$row = $st->fetch();
}

如果在prepare()中使用的是?占位符,那么bindParam()的第一个参数应该是一个表示占位符位置的数字,而不是一个占位符名(占位符位置起始于1)。

8 确定查询返回的行数
使用PDOStatement::rowCount()来取得被更新的行数。如果发送的是一个SELECT语句,那么确定返回了多少行的唯一可靠方式是用fetchAll()来抽取返回的全部数据。

$st = $db->query(‘select symbol, planet from zodiac’);
$all = $st->fetchAll(PDO::FETCH_COLUMN, 1);
echo count($all);

数据库后台可以给PDO提供有关SELECT查询返回行数的信息,以使得rowCount()可以使用,但不是所有数据库都提供。 不多,抽取全部记录效率很低,可以使用SQL函数count(*)来返回结果集。

9 转义引号
使用占位符方法来组织所有的查询语句,这样prepare()和execute()就可以转义字符串。如果需要自己处理转义,那么可以使用PDO::quote()方法。

10 记录调试信息和错误
在执行某次操作失败后使用PDO::errorCode()或PDOStatement::errorCode()方法获得相应的错误代码。对应的errorInfo()方法则会返回有关该错误的更多信息。

$st = $db->prepare(‘select * form imaginary_table’);
if(! $st){
	$error = $db->errorInfo();
	print “Problem ({$error[2]})”;
}

errorCode()方法会返回一个5个字符的错误代码。00000的含义是没有错误,因此当调用errorCode()返回00000时,表示成功。

errorInfo()方法返回的是一个包含三个元素的数组。第一个元素包含5个字符的SQLSTATE代码(与errorCode()返回的代码相同)。第二个元素时具体的数据库专有的错误代码。第三个元素是具体的数据库专有的错误信息。

这个规则的一个特例发生在创建一个新的PDO对象时。如果创建失败,PDO会抛出一个异常。此时没有PDO对象,所以就无法调用errorCode()或errorInfo()方法。如果想让PDO每次遇到错误时都抛出一个异常,可以在创建该PDO对象后在其上调用setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)。这样就可以采用统一的方式来处理数据库错误:

try{
	$db = new PDO(‘sqlite:/usr/local/zodiac.db’);
	//让所有数据库错误都抛出异常
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$st = $db->prepare(“select * from zodiac”);
	$st->execute();
	while($row = $st->fetch(PDO::FETCH_NUM)){

}
}catch(Exception $e){
	
}

11 创建唯一的标识符
使用PHP的uniqid()函数生成一个标识符。在传递到md5()函数。
一般使用数据库特征,比如MySQL支持AUTOINCREMENT列。

$db->exec(<<<_SQL_
	creae table users (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255),
	PRIMARY KEY(id)
)
_SQL_
);
// 不需要为id插入值   MySQL会自动为它赋值
$st = $db->prepare(“insert into users(name) values(?)”);
//插入的行都会给id列赋值
foreach(array(‘Jacob’, ‘Ruby’) as $name){
	$st->execute(array($name));
}

如果是通过数据库自动创建的ID值,那么就可以用PDO::lastInsertId()方法来取得相应的ID值。也就是说,在相应的PDO对象上调用lastInsertId()可以取得最后插入的哪一行记录所自动生成的ID值。

12 以程序化的方式建立查询
13 为连续的记录生成分页链接
14 缓存查询和结果
使用PEAR的Cache_Lite包。
15 在程序中任何地方都能访问数据库链接

class DBCxn{
	public static $dsn = array('sqlite'=>'sqlite:/usr/local/sqliete.db', 'mysql' => array('mysql:host=db.mysql.com','root','root'));	
	private static $db = array();
	final private function __construct(){}
	final private function __clone(){}
	
	public static function get($key){
		if(! isset(self::$dsn[$key])){
			throw new Exception("Unknown DSN: $key");	
		}
		//如果不存在连接,则进行连接
		if(! isset(self::$db[$key])){
			if(is_array(self::$dsn[$key])){
				$c = new ReflectionClass('PDO');
				self::$db[$key] = $c->newInstanceArgs(self::$dsn[$key]);
			}else{
				self::$db[$key] = new PDO(self::$dsn[$key]);
			}
		}
		return self::$db[$key];
	}
}
需要处理与PDO构造函数相关的多个可变参数,某些数据库,如SQLite只需要一个参数,而有的数据库则需要两个,甚至四个参数,使用ReflectionClass::newInstanceArgs()方法可以通过数组来传递参数到构造函数

永久链接:http://blog.ifeeline.com/329.html