大量数据操作优化

# 大量数据操作优化
## 一、数据表优化

```
数据库优化：
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 、join涉及的列上建立索引。
2.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。最好将表字段设置为not null
4.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
5.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
6.in和 not in 也要慎用，否则会导致全表扫描
7.查询大数据量表最好不要用limit,因为limit在where、group by、order by之后执行，这会把所有数据查出来然后再获取置顶位置的数据。能用'>'、'<'来进行分页。
8.查询中不要select * ,需要什么查什么
9.慎用子查询。可以拆成多个语句查询。
```

```
SQL Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；（聚集函数AVG、COUNT、MAX、MIN、SUM）
5、使用having子句筛选分组； (having即对聚集函数运算结果的输出进行限制)
6、计算所有的表达式；
7、使用order by对结果集进行排序。

from>where>group by>having>select>order by>limit

# 下面是注册用户日uv统计
SELECT
    mydate,
    count(*)
FROM
    (
        SELECT
            date_format(create_t, '%Y%m%d') mydate,
            count(*),
            user_id
        FROM
            yx_user_action u
        LEFT JOIN yx_consumer c ON c.uid = u.user_id
        WHERE
            c.is_register = 1
        GROUP BY
            date_format(create_t, '%Y%m%d'),
            user_id
    ) AS a
GROUP BY
    mydate;

# 下面是注册用户日uv统计(优化后)
SELECT
    mydate,
    count(*)
FROM
    (
        SELECT
            day_time mydate,
            count(*),
            user_id
        FROM
            yx_user_action u
        LEFT JOIN yx_consumer c ON c.uid = u.user_id
        WHERE
            c.is_register = 1
        GROUP BY
            u.day_time,   //day_time字段为20180808类似的int类型字段，加索引
            user_id
    ) AS a
GROUP BY
    mydate

```

```
explain 查看sql语句执行效率
https://www.cnblogs.com/gomysql/p/3720123.html

```


## 二、 大量数据导出
用户表现在有600多万条数据。直接导出会内存超出。所以导出要分批导出。同时最好导成csv格式，csv比excel导出速度快的太多

```
	// 导出用户表信息
	public function exportPassport(){
        $file = 'passport.csv';
        if (file_exists($file)) {
            unlink($file);
        }
        $step = 10000;
        $model = D('Passport');
        $count = $model->count();
        $length = floor($count/$step) +1;
        $tableheader = $model->getDbFields();
        $phpCsv = new PhpCsv();
        $phpCsv->export_csv_2(array(), $tableheader, $file);

        for ($i = 0; $i < $length; $i++) {
            $data = $mode->limit($i*$step, ($i+1)*$step)->select();
            $phpCsv->export_csv_2($data, array(), $file);
        }
        die;
    }
	
# 如果通过页面导出超时，可以在命令行通过php脚本导出，然后下载文件。（通过php脚本执行没有超时时间）
php index.php /Test/Index/exportUc

```

## 三、大量数据导入
后台白名单用户导入。此操作需关联多个用户表做操作，如果导出的表格过大，会超时。
解决方法：通过定时任务执行。
excel表上传后，状态为‘未开始’，定时任务每5分钟执行一次，每次查询一条‘未开始’的数据，然后将状态改为‘已开始’，开始通过php脚本命令执行数据导入（脚本执行没有超时时间），完成后将状态改为‘已完成’。

最好用队列实现，营销中心没用主要是在在线上虚拟化机器上装实现队列的应用太麻烦


## 四、加缓存
对于经常访问并且变化不大的地方，可以加缓存。
```
    //7天最热
    public function hots() {
        $keyCache = 'home_learn_product_atricle_hot';
        $cache = S($keyCache);
        if ($cache !== false) {
            return $cache;
        }
        $where['a.status'] = self::ON_LINE; //上线
        $where['p.type'] = self::LEARN_ARTICLE_TYPE; //学习中心文章类别
        $seven = strtotime(date('Y-m-d', time())) - 86400 * 6;
        $where['p.ymd_time'] = array('gt', $seven);
        $hots = $this
            ->alias('a')
            ->field(array('a.id', 'a.title', 'SUM(p.pv) sum_pv'))
            ->join('left join yx_tj_puv p on p.tj_id= a.id')
            ->where($where)
            ->order('p.pv desc')
            ->group("tj_id")
            ->limit(8)
            ->select();
        S($keyCache, $hots, self::HOTS_CACHE_TIME);
        return $hots;
    }
```