php mysql连接 单例 锁_PHP PDO与mysql的连接单例防止超时情况处理

这个数据库类主要处理了单例模式下创建数据库对象时,如果有两次较长时间的间隔去执行sql操作,再次处理会出现连接失败的问题,利用一个cache数组存放pdo对象与时间戳,把两次执行之间的时间进行了比较,如果间隔超过了10秒就再次new PDO创建连接,没有超过的情况下会继续使用原来的连接,并且因为每次使用后会使连接续期,cache数组里的时间戳也进行了续期.

每次执行操作都会从cache数组中获取下连接,多次执行不超过10秒的情况下,只会有一个连接

代码中实现读写分离,判断sql语句前面6个字符是select的就查询从库,其余操作查询主库.主库和从库就是分别在配置数组中0和1创建不同的PDO对象连接

代码如下:

class SinaPdoAdapter{

const MASTER = 0;

const SLAVE = 1;

const DEFAULT_CACHE_EXPIRETIME = 10;

private static $options = array(

PDO::ATTR_AUTOCOMMIT => true,

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

//PDO::ATTR_PERSISTENT => true,

);

private $dsn = null;

private $username = null;

private $password = null;

private $timeout = null;

private $charset = null;

private $conns = array();

private $conn = null;

private $stmt = null;

private static $obj=null;

private function __construct($dsn, $username, $password, $timeout = null, $charset = null){

$this->dsn = $dsn;

if (!is_array($username)) {

$this->username = array($username);

} else {

$this->username = $username;

}

if (!is_array($password)) {

$this->password = array($password);

} else {

$this->password = $password;

}

$this->timeout = intval($timeout);

$this->charset = $charset;

}

private function getConnection($id = self::MASTER){

if (!isset($this->dsn[$id])) {

$id = self::MASTER;

}

$conn = $this->getCachedConn($id);

if ($conn) {

return $conn;

}

$opts = self::$options;

if ($this->timeout > 0) {

$opts[PDO::ATTR_TIMEOUT] = $this->timeout;

}

$username = isset($this->username[$id]) ? $this->username[$id] : $this->username[self::MASTER];

$password = isset($this->password[$id]) ? $this->password[$id] : $this->password[self::MASTER];

$conn = new PDO($this->dsn[$id], $username, $password, $opts);

$this->cacheConn($id, $conn);

if ($this->charset) {

$conn->exec("set names " . $this->charset);

}

return $conn;

}

public function execute($sql, $params = array()){

$cmd = substr($sql, 0, 6);

if (strcasecmp($cmd, "select") === 0) {

$conn = $this->getConnection(self::SLAVE);

} else {

$conn = $this->getConnection(self::MASTER);

}

$stmt = $conn->prepare($sql);

$stmt->execute($params);

$this->stmt = $stmt;

$this->conn = $conn;

}

public function fetch(){

return $this->stmt->fetch();

}

public function fetchAll(){

return $this->stmt->fetchAll();

}

public function lastInsertId(){

return $this->conn->lastInsertId();

}

public function rowCount(){

return $this->stmt->rowCount();

}

public static function getInstance($conf){

if(self::$obj == null){

self::$obj = new self($conf->dsn,$conf->username,$conf->password,$conf->timeout,$conf->charset);

}

return self::$obj;

}

private function getCachedConn($id){

if (!isset($this->conns[$id])) {

return null;

}

list($conn, $timeout) = $this->conns[$id];

if (time() < $timeout) {

$this->cacheConn($id, $conn);

return $conn;

} else {

return null;

}

}

private function cacheConn($id, $conn){

$timeout = time();

if ($this->timeout) {

$timeout += $this->timeout;

} else {

$timeout += self::DEFAULT_CACHE_EXPIRETIME;

}

$this->conns[$id] = array($conn, $timeout);

}

}

$config=new stdClass();

$config->dsn=array(

"mysql:host=127.0.0.1;port=3306;dbname=surframe",//主库

"mysql:host=127.0.0.2;port=3306;dbname=surframe"//从库

);

$config->username=array(

"root", "root",

);

$config->password=array(

"taoshihan1", "taoshihan1",

);

$config->timeout=10;

$config->charset="utf8";

$db=SinaPdoAdapter::getInstance($config);

$db->execute("select * from admin_users");//使用的从库

$rows=$db->fetchAll();

var_dump($db);

$db=SinaPdoAdapter::getInstance($config);

$db->execute("select * from admin_users");//使用的从库

$rows=$db->fetchAll();

var_dump($db);

以上内容希望帮助到大家，很多PHPer在进阶的时候总会遇到一些问题和瓶颈，业务代码写多了没有方向感，不知道该从那里入手去提升，对此我整理了一些资料，包括但不限于：分布式架构、高可扩展、高性能、高并发、服务器性能调优、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点高级进阶干货需要的可以免费分享给大家，需要戳这里