高效使用mysql表来缓存复杂查询

Question

我曾经在查询中使用过很多个连接。

为了能够使用（至少）内置的MySql缓存功能，我编写了以下函数，它将原始查询编码成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); 
    }
}

- Nullx8

---

您所说的校验和，是指MD5或其他哈希码吗？ MD5存在加密问题，但对于您的目的来说应该可以使用。 - HeatfanJohn

@HeatfanJohn，通过校验和，我指的是可以在非常短的引用中识别出原始查询的任何内容，最好是数字形式，这样我就可以存储它而不是原始查询，并将主索引设置为“sql”字段（这就是我的想法，但我也不确定MD5是否能够以快速的方式完成，因为我永远不会解码它，所以它可以是任何形式的校验和生成（越快越好）。 - Nullx8

使用memcached怎么样？ - Barmar

@nullx8：自 MySQL 5.0.3 起，varchar 可以达到 64k 长度。然而，对于这么大的 varchar ，索引查询速度会比较慢。 - Marc B

我用一个非常短的校验和来解决了这个问题...现在获取时间非常快。 - Nullx8

Answer 1

感谢@HeatfanJohn - 他指出了一些非常简单和高效的东西。

由于原始查询没有用于任何事情（除了匹配缓存条目），因此仅存储检验和即可唯一标识所涉及的查询。

新结构只需存储原始查询的MD5哈希值（16字节）、过期Unix时间和序列化行集。

新结构：

CREATE TABLE `cachesql` (
`sql` varchar(32) NOT NULL,
`expire` int(11) NOT NULL,
`data` text NOT NULL,
PRIMARY KEY (`sql`),
UNIQUE KEY `sql` (`sql`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='cache for db->fetchRows'

sql列的主索引反应非常迅速，因为它非常短，并且可以更好地进行搜索索引。

我在使用BLOB或TEXT字段进行数据集时没有获得不同的速度结果。

- Nullx8

---

你的问题得到了解答吗？如果是，请在你的回答左侧打勾标记为已解答。如果没有，请编辑你的问题并添加额外信息，然后删除回答。谢谢。 - fancyPants

我已经尝试使用二进制字段，但匹配时间更长。使用varchar（32）似乎是存储MD5哈希的最佳且最快速的解决方案。 - Nullx8

Answer 2

我认为主要的减速点在于您在缓存表中使用了InnoDB。

我发现您应该将InnoDB用于除读取密集型缓存表之外的所有内容；）

MyISAM特别适用于读取密集型（select）表格。

- rubo77

---

MyISAM是快速的，因为它是一个非事务性存储引擎。但如果您需要事务和完全的ACID兼容性，则不应使用它。 - edze

同意！MyISAM在锁定问题上有巨大的缺陷。由于表格由临时数据填充并在同一进程中进行清理，使用MyISAM会将表格锁定基本上永远，数据被读取约50-100次直到过期，因此DELETE命令仍然占据了1%以上的事务。MyISAM->糟糕的选择！ - Nullx8

Answer 3

让我们尝试使用内存表来加快速度。

- user1646191

---

内存表不支持文本字段，对于大行结果，64K varchar 大小不足够。 - Nullx8