最近徒弟问我一个关于亿级数据的查询优化问题。

在亿级查询中，PHP+MySql显得有些吃力，
通常情况下，我们只能尽可能的在老项目中，减少`join`链表的次数，保证不超过`3`表关联的原则。

再把数据检索所需要的字段加上索引，尽可能的提供全表检索速度。

还有些朋友会在查询的过程中建立临时表，增加内存开销来获得更快的检索速度。

其实在亿级数据后，我们更大的是应该考虑日后递增数据的优化与管理方案。
所以在业内，一般达到亿级的数据都会使用分表分区方案。
将一个表按 **规则** 或者 **日期**，切割成**固定长度**的 **N个表**。

今天这个方案，原先我是打算在分表的前提下，再加上多进程或多线程来实现查询优化的，但有个很尴尬的地方，徒弟的服务器是windows环境的，PHP在Windows环境下不支持多进程，而多线程还得依靠一个不成熟的线程扩展组件，所以想想还是算了，再退一步使用伪线程开多个PDO实例跑吧。

具体的实现代码如下：
```php
<?php
// +----------------------------------------------------------------------
// | PHP+MySQL+原生多线程 - 处理亿级数据查询（优化到毫秒级）
// +----------------------------------------------------------------------
// | Copyright (c) 2018 https://blog.junphp.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: 小黄牛 <1731223728@qq.com>
// +----------------------------------------------------------------------
ini_set("memory_limit","1024M");

class Mysql{
    /*
     * 数据库参数
    */
    protected $dsn      = 'mysql:dbname=youtube;host=127.0.0.1;port=3306';
    protected $username = 'root';
    protected $password = 'root';
    /*
     * 数据库连接实例
    */
    protected $DB = null;
     /*
     * 全部表名
    */
    protected $table_list = [];

/**
     * 初始化数据库连接
     * @todo 无
     * @author 小黄牛
     * @version v1.0.0.1 + 2018.12.25
     * @deprecated 暂不弃用
     * @global 无
     * @return void
    */
    public function __construct() {
        if (empty($this->DB)) {
            try {
                $this->DB = new PDO($this->dsn, $this->username, $this->password);
                $this->get_table_list();
            } catch(PDOException $e) {
                die ('连接失败，错误信息：'.$e->getMessage());
            }
        }
    }

/**
     * 立即释放数据库连接
     * @todo 无
     * @author 小黄牛
     * @version v1.0.0.1 + 2018.12.25
     * @deprecated 暂不弃用
     * @global 无
     * @return void
    */
    public function __destruct() {
        $this->DB = null;
    }

/**
     * 读取数据库全部表名
     * @todo 无
     * @author 小黄牛
     * @version v1.0.0.1 + 2018.12.25
     * @deprecated 暂不弃用
     * @global 无
     * @return void
    */
    private function get_table_list() {
        $sql    = 'SHOW TABLES';
        $list   = $this->query($sql);
        $tables = [];
        foreach ($list as $key=>$value) {   
            $tables[$key]['name'] = $value[0];
            // Windows下没有多进程，多线程也只能安装第三方扩展，占用多个PDO代替了
            $tables[$key]['DB']   = new PDO($this->dsn, $this->username, $this->password);
        }
        $this->table_list = $tables;
    }

/**
     * 执行查询语句
     * @todo 无
     * @author 小黄牛
     * @version v1.0.0.1 + 2018.12.25
     * @deprecated 暂不弃用
     * @global 无
     * @param string $sql
     * @return mixed
    */
    private function query($sql = '') {
        $stmt = $this->DB->query($sql);
        $stmt->setFetchMode(\PDO::FETCH_NUM);
        $list = $stmt->fetchAll();
        return $list;
    }

/**
     * 执行循环写入
     * @todo 无
     * @author 小黄牛
     * @version v1.0.0.1 + 2018.12.25
     * @deprecated 暂不弃用
     * @global 无
     * @return void
    */
    public function add_test() {
        exit;
        // 500W一个表，这个方法不能乱用
        foreach ($this->table_list as $key=>$val) {
            if ($key <= 5) { continue; }
            $sql = '';
            $key = $key+1;
            $name = 'test'.$key.'.sql';
            $num = ($key*5000000);
            $i   = ($num - 4999999); 
            for ($k=$i; $k < $num; $k++) {
                $sql .= 'INSERT INTO '.$val['name'].' (channel) VALUES("UC4_b2E3PpCxnIPi5Lfe'.$k.'");';
            }

file_put_contents($name, $sql, FILE_APPEND);
            echo 'ok';
            break;
        }
    }

/**
     * 执行线程查询
     * @todo 无
     * @author 小黄牛
     * @version v1.0.0.1 + 2018.12.25
     * @deprecated 暂不弃用
     * @global 无
     * @param string $where 查询条件
     * @return bool|array
    */
    public function run($where) {
        $child = '';
        foreach ($this->table_list as $key=>$val) {
            $sql = 'SELECT uuid FROM '.$val['name'].' '.$where.';';
            $stmt = $val['DB']->query($sql);
            $stmt->setFetchMode(\PDO::FETCH_NUM);
            $list = $stmt->fetchAll();
            if ($list) {
                return $list;
            }
        }
        return false;
    }
}

echo '<pre>';
$start = microtime(true);

$obj = new Mysql();
# 执行检索
var_dump($obj->run("where channel = 'UC4_b2E3PpCxnIPi5Lfe29'"));

echo '<br/>耗时：'.round(microtime(true)-$start, 7).'S<br/>';
echo '内存开销：'.memory_get_usage() / 1024 .'KB';

# 三千万的全表扫描，Windows+8G+2核的机器下，大概是0.002 - 0.03秒之间。
```

当然这种方案的作用并不大，因为依旧是会对全表扫描，而我们应该实现的是，对单表进行扫描并精确的找到自己想要的答案。
这种将在下一篇博文里为大家介绍到。

测试的数据库结构与数据，这里下载：
`https://pan.baidu.com/s/1dE2QRXQGaBYPa_lxeHjNUA`

已有0条评论

添加新评论☺

如果认为本文对您有所帮助请赞助本站

支付宝扫一扫赞助

已有0条评论

添加新评论☺