分库分表的问题
- 查询不可避免的出现全表查询
- 分布式事务问题
- 分页查询的问题
- 对于数据的统计
- 表之间的join
什么是异构索引表？
也就是应用在创建或更新一条订单ID为分库分表键的订单数据时，也会再保存一份按照买家ID为分库分表键的订单索引数据，其结果就是同一买家的所有订单索引表都保存在同一数据库中，这就是给订单创建了异构索引表。
分页查询
- 通过异构索引表查询出订单id
select order_id from order_index where buver_id = 1 limit 0,10;
- 在通过订单id到订单表中查询
select * from order where order_id in (order_id);
统计查询
select count(*) from order where buver_id = 1;
分布式事务
分布式事务问题一般出现在分库之后；一旦事务出现问题，回滚只会回滚一个库。
这个问题，用 XA 事务就能解决，下面来个示例代码，准备内容：
| 数据库 | 数据表 | 字段 |
|---|---|---|
| mytest | t | id 、 name |
| mytest1 | orders | id 、amount(金额类型，保留两位小数) |
| mytest2 | order_index | id 、order_id |
xa4.php：
<?PHP
require "db.php";
//定义三个库
$dbtest1 = new db("192.168.29.105","slave","root","mytest");
$dbtest2 = new db("192.168.29.105","slave","root","mytest2");
$dbtest3 = new db("192.168.29.105","slave","root","mytest3");
//为XA事务指定一个id，xid 必须是一个唯一值。
$xid1 = uniqid("");
$xid2 = uniqid("");
$xid3 = uniqid("");
//三个库指定同一个事务id，表明这两个库的操作处于同一事务中
$dbtest1->exec("XA START '$xid1'");//准备事务1
$dbtest2->exec("XA START '$xid2'");//准备事务2
$dbtest3->exec("XA START '$xid3'");//准备事务3
try {
//$dbtest1
$return = $dbtest1->exec("UPDATE t SET name='33333' WHERE id=1") ;
echo "xa1:"; print_r($return);
if(!in_array($return,['0','1'])) {
throw new Exception("库1执行sql操作失败！");
}
//$dbtest2
$return = $dbtest2->exec("UPDATE orders SET amount=99.00 WHERE id=1");
echo "xa2:"; print_r($return);
if(!in_array($return,['0','1'])) {
throw new Exception("库2执行sql操作失败！");
}
//$dbtest3
$return = $dbtest3->exec("UPDATE order_index SET order_id=1 WHERE id=1");
echo "xa3:"; print_r($return);
if(!in_array($return,['0','1'])) {
throw new Exception("库3执行sql操作失败！");
}
//阶段1：$dbtest1提交准备就绪
$dbtest1->exec("XA END '$xid1'");
$dbtest1->exec("XA PREPARE '$xid1'");
//阶段1：$dbtest2提交准备就绪
$dbtest2->exec("XA END '$xid2'");
$dbtest2->exec("XA PREPARE '$xid2'");
//阶段1：$dbtest3提交准备就绪
$dbtest3->exec("XA END '$xid3'");
$dbtest3->exec("XA PREPARE '$xid3'");
//阶段2：提交三个库
$dbtest1->exec("XA COMMIT '$xid1'");
$dbtest2->exec("XA COMMIT '$xid2'");
$dbtest3->exec("XA COMMIT '$xid3'");
}
catch (Exception $e) {
//阶段2：回滚
$dbtest1->exec("XA ROLLBACK '$xid1'");
$dbtest2->exec("XA ROLLBACK '$xid2'");
$dbtest3->exec("XA ROLLBACK '$xid3'");
die("Exception:".$e->getMessage());
}
echo "执行完毕";exit;
/*
$dbtest1->close();
$dbtest2->close();*/
?>
db.php：
<?php
/* auther:chenduansheng; createtime:2016-08-17 */
/**
其他未实现的
1、绑定列到php变量请使用 $db->statement->bindColumn(1,$name);
*/
class db {
private $pdo = null;
public $statement = null;
private $is_addsla = false;
public $options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES ",
);
public function __construct($host,$user="root",$pass="",$dbname="",$no_persistent=false){
$this->options[PDO::MYSQL_ATTR_INIT_COMMAND] .= "utf8";
if($no_persistent){
$this->options[PDO::ATTR_PERSISTENT] = false;
}
$dsn = "mysql:host={$host};dbname={$dbname}";
try {
$this->pdo = new PDO($dsn,$user,$pass,$this->options);
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/* 下面2行为避免查出的int字段自动变string */
$this->pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br>";
die();
}
}
/**
全局属性设置，包括：列名格式和错误提示类型 可以使用数字也能直接使用参数
*/
public function setAttr($param,$val=''){
if(is_array($param)){
foreach($param as $key=>$val){
$this->pdo->setAttribute($key,$val);
}
}else{
if($val!=''){
$this->pdo->setAttribute($param,$val);
}else{
return false;
}
}
}
/**
生成一个编译好的sql语句模版 你可以使用 ? :name 的形式
返回一个statement对象
*/
public function doPrepare($sql=""){
addLog(array('file_name'=>'sqlPrepare','content'=>$sql));
if($sql==""){
return false;
}
try{
$this->statement = $this->pdo->prepare($sql);
return $this->statement;
}catch(PDOException $e){
$exception = $e->getMessage();
$array = array('exception' => $exception);
$this->showSqlError($array);
}
}
/**
执行Sql语句，一般用于 增、删、更新或者设置 返回影响的行数
*/
public function exec($sql){
if($sql==""){
return false;
}
try{
return $this->pdo->exec($sql);
}catch(Exception $e){
return $e->getMessage();
}
}
/**
执行有返回值的查询，返回PDOStatement 可以通过链式操作，可以通过这个类封装的操作获取数据
*/
public function query($sql){
if($sql=""){
return false;
}
try{
$this->statement = $this->pdo->query($sql);
return $this->statement;
}catch(Exception $e){
return $e->getMessage();
}
}
/**
开启事务
*/
public function beginTA(){
return $this->pdo->beginTransaction();
}
/**
提交事务
*/
public function commit(){
return $this->pdo->commit();
}
/**
事务回滚
*/
public function rollBack(){
return $this->pdo->rollBack();
}
public function lastInertId(){
return $db->lastInsertId();
}
//** PDOStatement 类操作封装 **//
/**
让模版执行SQL语句，1、执行编译好的 2、在执行时编译
*/
public function execute($param=""){
if(is_array($param)){
try{
return $this->statement->execute($param);
}catch (Exception $e){
//return $this->errorInfo();
return $e->getMessage();
}
}else{
try{
return $this->statement->execute();
}catch(Exception $e){
/* 返回的错误信息格式
[0] => 42S22
[1] => 1054
[2] => Unknown column 'col' in 'field list'
return $this->errorInfo();
*/
return $e->getMessage();
}
}
}
/**
参数1说明：
PDO::FETCH_BOTH 也是默认的，两者都有（索引，关联）
PDO::FETCH_ASSOC 关联数组
PDO::FETCH_NUM 索引
PDO::FETCH_OBJ 对象
PDO::FETCH_LAZY 对象 会附带queryString查询SQL语句
PDO::FETCH_BOUND 如果设置了bindColumn，则使用该参数
*/
public function fetch($fetch_style=PDO::FETCH_BOTH){
if(is_object($this->statement)){
return $this->statement->fetch($fetch_style);
}else{
return false;
}
}
/**
参数1说明：
PDO::FETCH_BOTH 也是默认的，两者都有（索引，关联）
PDO::FETCH_ASSOC 关联数组
PDO::FETCH_NUM 索引
PDO::FETCH_OBJ 对象
PDO::FETCH_COLUMN 指定列 参数2可以指定要获取的列
PDO::FETCH_CLASS 指定自己定义的类
PDO::FETCH_FUNC 自定义类 处理返回的数据
PDO_FETCH_BOUND 如果你需要设置bindColumn，则使用该参数
参数2说明：
给定要处理这个结果的类或函数
*/
public function fetchAll($fetch_style=PDO::FETCH_BOTH,$handle=''){
if($handle!=''){
return $this->statement->fetchAll($fetch_style,$handle);
}else{
return $this->statement->fetchAll($fetch_style);
}
}
/**
以对象形式返回 结果 跟fetch(PDO::FETCH_OBJ)一样
*/
public function fetchObject($class_name){
if($clss_name!=''){
return $this->statement->fetchObject($class_name);
}else{
return $this->statement->fetchObject();
}
}
/**
public function bindColumn($array=array(),$type=EXTR_OVERWRITE){
if(count($array)>0){
extract($array,$type);
}
//$this->statement->bindColumn()
}
*/
/**
以引用的方式绑定变量到占位符(可以只执行一次prepare，执行多次bindParam达到重复使用的效果)
*/
public function bindParam($parameter,$variable,$data_type=PDO::PARAM_STR,$length=6){
return $this->statement->bindParam($parameter,$variable,$data_type,$length);
}
/**
返回statement记录集的行数
*/
public function rowCount(){
return $this->statement->rowCount();
}
public function count(){
return $this->statement->rowCount();
}
/**
关闭编译的模版
*/
public function close(){
return $this->statement->closeCursor();
}
public function closeCursor(){
return $this->statement->closeCursor();
}
/**
返回错误信息也包括错误号
*/
private function errorInfo(){
return $this->statement->errorInfo();
}
/**
返回错误号
*/
private function errorCode(){
return $this->statement->errorCode();
}
//简化操作--------------------------------------------------
/*
$select：要获取的属性
$table：数据表名称
$condition：获取条件
$order：数据排序
$limit：获取几条数据
*/
public function select($array) {
$array = array_merge(array('field'=>'*','table'=>'','where'=>'','group'=>'','order'=>'','limit'=>''),$array);
if(!$array['table']){
$array['table'] = $this->getTable();
}
if( is_array($array['order']) && isset($array['order']['order']) && ( $array['order']['order'] == 'desc' || $array['order']['order'] == 'asc' ) && isset($array['order']['field']) ) {
$array['order'] = "order by ".$array['order']['field']. $array['order']['order'];
} elseif( $array['order'] && is_string($array['order']) ) {
$array['order'] = " order by {$array['order']}";
} else $array['order'] = '';
if($array['group']) {
$array['group'] = " GROUP BY ".$array['group'];
}
if($array['limit']) {
$array['limit'] = " LIMIT ".$array['limit'];
}
$this->sql =
"SELECT ". $array['field'].
" FROM ". $array['table'].
" WHERE 1 ". $array['where'].
$array['group'].
$array['order'].
$array['limit'];
addLog(array('file_name'=>'sqlSelect','content'=>$this->sql));
if($this->result = $this->doPrepare($this->sql)) {
return $this;
}
return $this;
}
//获取所有查询到的数据$select,$table,$condition = '',$order = array(), $limit = ''
public function getAll($array) {
$this->select($array);
$arrData = array();
if( $this->statement ) {
try{
$this->result = $this->statement->execute();
$arrData = $this->statement->fetchAll(PDO::FETCH_ASSOC);
}catch(PDOException $e){
$this->showSqlError(array('exception'=>$e->getMessage()));
}
}
return $arrData;
}
//获取一条查询到的数据
public function getOne($array) {
$array['limit'] = '0,1';
$this->select($array);
$arrData = array();
if( $this->statement ) {
try{
$this->statement->execute();
$arrData = $this->statement->fetch(PDO::FETCH_ASSOC);
}catch(PDOException $e){
$this->showSqlError(array('exception'=>$e->getMessage()));
}
}
return $arrData;
}
public function add($array){
$array = array_merge(array('table'=>''),$array);
if(!is_array($array['data'])){
die('data必须是数组！');
}
if(!$array['table']){
$array['table'] = $this->getTable();
}
$cols = array();
$vals = array();
foreach($array['data'] as $key=>$val){
$cols[]=$key;
$vals[]="'".$this->addsla($val)."'";
}
$sql = "INSERT INTO {$array['table']} (";
$sql .= implode(",",$cols).") VALUES (";
$sql .= implode(",",$vals).")";
addLog(array('file_name'=>'sqlInsert','content'=>$sql));
return $this->exec($sql);
}
public function update($array){
$array = array_merge(array('table'=>''),$array);
if(!is_array($array['data'])){
die('data必须是数组！');
}
$set = array();
foreach($array['data'] as $key=>$val){
$set[] = $key."='".trim($this->addsla($val))."'";
}
if(!$array['table']){
$array['table'] = $this->getTable();
}
$sql = "UPDATE {$array['table']} SET ";
$sql .= implode(",",$set);
$sql .= " WHERE 1 ".$array['where'];
addLog(array('file_name'=>'sqlUpdate','content'=>$sql));
//echo 44;exit;这里需要增加报错!!!!!!!!!!!!!
return $this->exec($sql);
}
public function delete($array){
$sql = "DELETE FROM {$array['table']} WHERE 1 ".$array['where'];
addLog(array('file_name'=>'sqlDelete','content'=>$sql));
return $this->exec($sql);
}
private function addsla($data){
if($this->is_addsla){
return trim(addslashes($data));
}
return $data;
}
/* 输出sql报错方法 */
function showSqlError($array){
die(PHP_EOL.$this->sql.PHP_EOL.$array['exception'].PHP_EOL);
}
/* 获得表名 */
function getTable(){
$CDS = CDS_Controller::get_instance();
if($CDS->table){
return $CDS->table;
}else{
die('没有输入table表名');
}
}
}
db.php 是一些配置代码
xa4.php 是主要代码
里面大致的意思介绍：
- 首先配置MySQL库的信息；
- 然后指定一个事务ID，事务 ID 必须唯一；
- 三个库指定对应的事务 ID；
- 使用 try 语法，执行MySQL语句；
- 阶段1：提交准备，例如开启事务 begin；
- 阶段2：三个MySQL执行没有问题，则执行提交，例如提交事务：commit；
- 阶段3：如果三个MySQL执行其中一个出现问题，则统一执行回滚操作，然后报错错误信息
Join 查询问题与搜索问题
分库分表后Join无法使用了，针对这种情况，一般有下面几种方案解决
- 不在数据库层做Join，单用单表查询，在代码层做结果拼装；
- 宽表，新建立一个表，提前把Join后的视图写入这个表，重写轻读（类似物理视图）；
- 使用搜索引擎ES；
扫一扫
5994
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
