mysql表缓存_有效地使用mysql表来缓存复杂的查询

我曾经在查询中拥有相当多的多个联接。

为了能够使用(至少)内建的MySql Cache功能，我编写了以下函数，

它只是将原始查询编码为base64，检查它是否存在且未过期。

这极大地提高了性能，并且我有利于在源代码中控制逐个查询的缓存时间查询。

但是在繁忙的时候，桌子由于删除或选择太长而变得不可用。有没有什么建议可以做得更快，避免前面提到的问题？

表：

CREATE TABLE `cachesql` (

`id` int(9) NOT NULL AUTO_INCREMENT,

`expire` int(15) NOT NULL,

`sql` text NOT NULL,

`data` mediumtext NOT NULL,

PRIMARY KEY (`id`,`sql`(360)),

KEY `sdata` (`sql`(767)) USING HASH

) ENGINE=InnoDB功能：

function fetchRows_cache($sql,$cachetime,$dba){

// internal function (called by fetchRows)

global $Site;

$expire = 0;

$this->connect($dba);

// check if query is cached

$this->q = mysql_query("SELECT `expire`,`data` from cachesql where `sql`='".base64_encode($sql)."' limit 1;", $this->con) OR $this->error(1, "query$".$sql."$".mysql_error());

$this->r = mysql_fetch_assoc($this->q);

$expire = $this->r['expire'];

$data = $this->r['data'];

if (($expire < time())||($cachetime =="0")) { // record expied or not there -> execute query and store

$this->query("DELETE FROM `cachesql` WHERE `sql`='".base64_encode($sql)."'",$dba); // delete old cached entries

$this->q = mysql_query($sql, $this->con) OR $this->error(1, "query$".$sql."$".mysql_error());

$this->r=array();

$this->rc=0;

while($row = mysql_fetch_assoc($this->q)){

$arr_row=array();

$c=0;

while ($c < mysql_num_fields($this->q)) {

$col = mysql_fetch_field($this->q, $c);

$arr_row[$col -> name] = $row[$col -> name];

$c++;

}

$this->r[$this->rc] = $arr_row;

$this->rc++;

}

$out = $this->r;

// write results into cache table

if ($cachetime != "0") {

// not store cache values for now (too many locks)

$this->query("INSERT INTO `cachesql` (`sql`,`data`,`expire`) VALUES ('".base64_encode($sql)."','".mysql_real_escape_string(serialize($out))."','".(time()+$cachetime)."')",$dba);

}

return $out;

}

else { // use Cached data

return unserialize($data);

}

}