技术学习

对于时间性比较强的数据，比如日志类型、登录日志这样的数据，一般我们会建立一个日志表来存储，而当日志量超过百万级，则一个表来存储，将会造成很大的查询问题，而且对于日志数据，我们查询常常使用的是倒序查询，每页也仅仅查询几十条到几百条，查询效率会很差，因此必然会分表，那如何分表？在分表后又如何解决多表查询的问题呢？下面我就来推荐一种另类的分表方式

假设有这样一个日志类：


class Logs
{
    private $id;
    private $contents;
    private $datetime;

    public function __construct($contents)
    {
        $this->contents = $contents;
        $this->datetime = date('Y-m-d H:i:s', time());
    }

    public function save()
    {
        $sql = "INSERT INTO `logs`(`contents`, `datetime`) VALUES('{$this->contents}', '{$this->datetime}')";
        # 执行sql，插入到数据库中，并返回插入的日志id, 此处省略过程
        $this->id = $insert_id;
        return $this->id;
    }
}

通过日志类的结构，我们建立一个数据表


CREATE TABLE `logs` (
	`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`contents` text,
	`datetime` datetime NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=`MyISAM` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

一般来说，单表情况下，我们的id为自增。现在我们来分析这个日志类的特点：

  • 1. 随着时间增常，日志量增加
  • 2. 查询热点集中在最近的时段，尤其是当月
  • 3. 日志会无限期增长，采用传统的固定分表方式会仍会使单表数据量大增

我们开始做分表，分表后，我们将不能再使用自增来做ID，可以采取其他方式来获取唯一ID，下面列举几种方式：

  • 1. 单独做一个表，如下： 
    
CREATE TABLE `log_id` (
        `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
) ENGINE=`MyISAM` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    增加一个获取id的方法

    
function get_log_id()
{
    $sql = "INSERT INTO `log_id` VALUES()";
    # 执行sql并返回insertid, 此处略过
    return $insert_id;
}
  • 2. 使用redis缓存的incrby，不建议使用memcache是因为redis有持久化而memcache没有。
  • 3. 其他可以产生自增的id方式。

因为日志有很强的时间性，所以建议将日志的ID使用时间挂钩，比如日志格式为2017041600000001代表2017-04-16的第一条日志，使用此方式的日志ID，可以很清晰的从日志ID中看出时间，而且可以直接通过ID来查询某天的第n条日志。
因此，我们需要修改上述日志表的ID为bigint(16)，产生日志方式也需要变化，推荐使用redis的incrby来产生日志，而生成之日的key可以使用当前日期做key，比如：


function get_log_id_by_redis()
{
    # 初始化redis链接，或使用单例引入一个redis的实例，此处省略
    # 生成一个当天的key，类似于 log_id_20170416|string
    $time = date('Ymd', time());
    $key = sprintf('log_id_%s|string', $time);
    return sprintf('%8d%08d', $time, $redis->incrby($key, 1));
}

这样就能生成一个与日志相关的ID
对于分表，我们采取按月分表，因此要增加一个获取表名的方法
修改类如下：


class Logs
{
    private $id;
    private $contents;
    private $datetime;

    public function __construct($contents)
    {
        $this->contents = $contents;
        $this->datetime = date('Y-m-d H:i:s', time());
        // 新增ID使用获取id的方法
        $this->id = get_log_id();
    }

    public static function getTableName($log_id = 0)
    {
        /* 若提供日志ID则截取前6位，这也是使用按天做日志ID的好处，若不提供则返回当月的表 */
        return sprintf('logs_%s', $log_id == 0 ? date('Ym', time()) : substr($log_id, 0, 6));
    }

    public function save()
    {
        $tablename = self::getTableName($this->id);
        $sql = "INSERT INTO `{$tablename}`(`id`, `contents`, `datetime`) VALUES({$this->id}, '{$this->contents}', '{$this->datetime}')";
        # 执行sql，插入到数据库中，此处省略过程
        return $this->id;
    }
}

这样我们就可以根据时间将日志做分表保存在不同月份的表内了
我们可以做一个mysql错误的识别，来判断是否是表不存在，然后自动创建表
修改上面类的save方法


    public function save()
    {
        $tablename = self::getTableName($this->id);
        $sql = "INSERT INTO `{$tablename}`(`id`, `contents`, `datetime`) VALUES({$this->id}, '{$this->contents}', '{$this->datetime}')";
        # 执行sql，插入到数据库中，此处省略过程
        if ($insert_failed) {
            $not_exists = preg_grep("/Table\s+'([^']+\.)?([^']+)'\s+doesn't\s+exist/i", $insert_error);
            if (!$not_exists) {
                return false;
            } else {
                $create_sql = <<id;
    }

这样就可以自动的来创建日志的月表
因为基于MyISAM引擎，我们可以使用MRG_MYISAM引擎来创建多个表的联合表，这样我们就可以根据需求查询不同的联合表
创建联合表必须与日志表结构相同，例如：


CREATE TABLE `union_logs_all` (
	`id` int(16) UNSIGNED NOT NULL,
	`contents` text,
	`datetime` datetime NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=`MRG_MYISAM` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci INSERT_METHOD=NO UNION=(`logs_201701`, `logs_201702`, `logs_201703`, `logs_201704`);

以上创建的就是一个2017年1-4月的联合表
当然，我们不能每次都手动去建立联合表，毕竟日志的月份分表示程序去自动创建的，那么我们就要在每次创建新表时去创建或更新联合表，我们增加一个创建联合表的方法


    public function createUnionTable($tablename, array $union_list)
    {
        $delete_sql = "DELETE TABLE `{$tablename}`";
        # 执行删除sql，删除现有的联合表，此处略过
        $table_list = implode('`,`', $union_list);
        $create_sql = <<

我们增加两个工具函数用于后面的联合表计算


function numlist($end, $start = 0, $contain_start = false)
{
    $return = [];
    if (!$contain_start) {
        $start ++;
    }
    for ($i=$start; $i <= $end; $i++) {
        $return[] = $i;
    }
    return $return;
}
function format_to_logs($month)
{
    return sprintf('logs_%4d%02d', date('Y', time()), $month);
}

我们可以根据需求来创建多个联合表，假设我们规划，创建一个总表（包含所有日志表）、年表（包含当年的所有日志）、半年表（包含半年的所有日志）、季度表（包含当季度的所有日志），我们可以使用迭代器来返回需要的不同表，在日志类中增加如下方法:


    public function getAllLogTables()
    {
        $sql = "SHOW TABLES";
        $tables = [];
        # 执行sql，并遍历结果集，拿到当前所有的日志表 此处略过执行sql
        while ($res = mysqli_fetch_array($query)) {
            if (preg_match("/logs_\d{6}/i", $res[0])) {
                $tables[] = $res[0];
            }
        }
        return $tables;
    }

    public function getUnionTableByIterator()
    {
        $tables = $this->getAllLogTables();
        $month = date('m', time());
        $rules = [
            /* all 全表包含所有的日志表 */
            'all' => [],
            /* 年表，类似于 2015 2016 */
            date('Y', time()) => array_map('format_to_logs', numlist($month, 0, false)),
            /* 半年表，类似于 2015H1 2016H0 1表示下半年，0表示上半年 */
            date('Y', time()) . 'H' . ($month > 6 ? 0 : 1) => array_map('format_to_logs', numlist($month, ($month > 6 ? 6 : 0), false)),
            /* 季度表， 类似与 2016Q1 2016Q2 1表示第一个季度，2表示第二个季度，以此类推 */
            date('Y', time()) . 'Q' . intval($month % 3 == 0 ? $month / 3 : (($month / 3) + 1)) => array_map('format_to_logs', numlist($month, $month - ($month % 3 == 0 ? 3 : $month % 3), false)),
        ];
        foreach ($rules as $rule => $list) {
            yield [
                $rule,
                $rule == 'all' ? $tables : array_intersect($tables, $list)
            ];
        }
    }

这样就可以按规则来返回表的集合，然后我们修改save方法，在新增表中做联合表的更新


    public function save()
    {
        $tablename = self::getTableName($this->id);
        $sql = "INSERT INTO `{$tablename}`(`id`, `contents`, `datetime`) VALUES({$this->id}, '{$this->contents}', '{$this->datetime}')";
        # 执行sql，插入到数据库中，此处省略过程
        if ($insert_failed) {
            $not_exists = preg_grep("/Table\s+'([^']+\.)?([^']+)'\s+doesn't\s+exist/i", $insert_error);
            if (!$not_exists) {
                return false;
            } else {
                $create_sql = <<getUnionTableByIterator() as $rule) {
                    $this->createUnionTable('union_logs_' . $rule[0], $rule[1]);
                }
            }
        }
        return $this->id;
    }

这样联合表也可以通过程序自动创建和更新了，下面我们只要根据查询的时间段来做不同的联合表查询就可以了，我们可以在日志类中增加这样的一个查询方法


    public function find($offset, $start_timestamp, $end_timestamp, $limit = 10, $ifDesc = true)
    {
        $union_table = $this->getQueryUnionTable($start_timestamp, $end_timestamp);
        $sql = "SELECT `id`, `contents`, `datetime` FROM `{$union_table}` ORDER BY " . ($ifDesc ? 'DESC' : 'ASC') . ' LIMIT {$offset}, {$limit}';
        # 执行查询语句并返回查询结果，此处略过
    }

然后我们只要根据查询的起始结束时间戳来决定去查哪个联合表就可以了。

以上就是一个另类的分表方案，可以根据业务需求做不同的联合表规则，可以考虑再增加最近三月，最近半年的规则，然后通过一个计划任务来维护这种最近三月、最近半年这种时效性表（到下一个月后就不再使用），这样查询就可以通过业务，去查询不同的表，来解决数据量大时，查询所有表造成的负担。

Related posts:

Default Thumbnail构建一个自己的PHP框架（一）—— 你需要准备的 Default Thumbnail构建一个自己的PHP框架（二）—— 底层搭建之单例模式

Powered by YARPP.

3 个评论

评论功能已关闭。