学习 mongodb laravel php

准备

• centos7.2 64bit
• mongodb3.6
• php7.1 & MongoDB extension version 1.3.4
• laravel5.5

---

环境安装配置

安装参考地址

root 用户登录服务器

关闭selinux

vim /etc/selinux/config添加SELINUX=disabled:wqsetenforce 0

防火墙开放27017端口

vim /etc/sysconfig/iptablesCOMMIT上一行添加-A INPUT -p tcp -m state --state NEW -m tcp --dport 27017 -j ACCEPT:wqservice iptables restart

添加mongodb安装路径到yum源

cd ~vim /etc/yum.repos.d/mongodb-org-3.6.repo添加[mongodb-org-3.6]name=MongoDB Repositorybaseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/3.6/x86_64/gpgcheck=1enabled=1gpgkey=https://www.mongodb.org/static/pgp/server-3.6.asc:wq

安装

yum install -y mongodb-org

获得最大磁盘分区，如/home分区最大

df -h

创建存储地址

mkdir -p /home/tools/mongodb/mongo

检查两文件的环境

ls -dZ /home/tools/mongodb/mongo/ls -dZ /var/lib/mongo/

复制环境

chcon -R --reference=/var/lib/mongo /home/tools/mongodb/mongo

配置mongod.conf（所有冒号后的接值都需要有个空格），设置可以创建连接的ip地址（本地局域网ip）

vim /etc/mongod.conf修改字段dbPath: /home/tools/mongodb/mongobindIp: 127.0.0.1,192.168.20.172:wq

解决启动日志文件中的warning

echo "never" > /sys/kernel/mm/transparent_hugepage/enabledecho "never" >  /sys/kernel/mm/transparent_hugepage/defrag

启动 停止stop 重启restart

service mongod start

验证是否成功启动

cat /var/log/mongodb/mongod.log

结尾出现下行表示成功启动

[initandlisten] waiting for connections on port 27017

开机自启

chkconfig mongod on

登录

mongo --port 27017

添加账号后退出
角色详情见mongodb创建角色

use admindb.createUser(  {    user: "root",    pwd: "root",    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]  })db.createUser(  {    user: "mintomax",    pwd: "max2min",    roles: [ { role: "dbOwner", db: "waf" } ]  })quit()

配置文件启用身份验证

vim /etc/mongod.conf修改security:  authorization: enabled :wqservice mongod restart

连接测试

mongo --port 27017 -u "root" -p "root" --authenticationDatabase "admin"

修改用户属性

use admindb.updateUser(  "mintomax",  {    pwd: "max2min",    roles:      [{        role: "dbOwner",        db: 'waf'      }]  })

查看用户，help

use adminshow users;

删除用户

use admindb.dropUser('mintomax')

mintomax用户插入记录

use wafdb.runCommand(   {      insert: "waf_log",      documents: [ { _id: 1, user: "abc123", status: "A" } ]   })

查询

db.waf_log.find( {} )

删除所有documents

db.waf_log.deleteMany({})

删除collection

db.waf_log.drop()

重命名collection

db.waf_log.renameCollection( "waf_log_new" )

确定waf_log集合的字段（与mysql对比）

建立索引并为索引取名

use wafdb.waf_log.createIndex({    time:1,    xffip:1,    url:1},{    name:"time_1_xffip_1_url_1",    background:false,    unique:false});db.waf_log.createIndex({    "tid":1,    "xffip":1,    "domain":1,    "url":1,    "time":1},{    name:"tid_1_xffip_1_domain_1_url_1_time_1",    background:false,    unique:false});db.waf_log.createIndex({    tid:1},{    name:"tid_1",    background:false,    unique:false});

一次建立多个索引 参考

db.runCommand({    createIndexes:"waf_log",    indexes:[{        key:{            time:1,            xffip:1,            url:1        },        name:"time_1_xffip_1_url_1",        background:false,        unique:false    },{        key:{            "tid":1,            "xffip":1,            "domain":1,            "url":1,            "time":1        },        name:"tid_1_xffip_1_domain_1_url_1_time_1",        background:false,        unique:false    }]});

查看索引

db.waf_log.getIndexes()

删除索引

db.waf_log.dropIndex("time_1_xffip_1_url_1");

编译安装php的mongodb最新版本扩展包，需要php.ini开放函数

vim /usr/local/php/etc/php.ini找到disable_functions，删掉以下函数proc_open,proc_get_status

安装php的mongodb扩展 下载地址库

cd ~wget http://pecl.php.net/get/mongodb-1.3.4.tgztar xvzf mongodb-1.3.4.tgzmv mongodb-1.3.4 /usr/local/ && cd /usr/local/mongodb-1.3.4/phpize可以用 find / -name php-config 查找正确的php-config路径./configure --with-php-config=/usr/local/php/bin/php-configmakemake testmake install安装正确结果为Installing shared extensions:     /usr/local/php/lib/php/extensions/no-debug-non-zts-20160303/

php.ini加入扩展

vim /usr/local/php/etc/php.iniextension=mongodb.so:wqservice php-fpm restart

查看扩展是否安装成功

php -m | grep mongodb

laravel5.5安装参考文档

确认安装了composer指令

composer global require "laravel/installer"

临时添加环境变量

export PATH=$PATH:/root/.composer/vendor/bin/

所有用户登录自动添加环境变量

vim /etc/profileexport PATH=$PATH:/root/.composer/vendor/bin/:wq

创建waf_new的web应用

cd /home/wwwrootcomposer create-project --prefer-dist laravel/laravel waf_new

web环境搭建与配置略

附lnmp中laravel项目的nginx解决200返回码空白页问题，nginx虚拟配置中php文件加上

fastcgi_param  SCRIPT_FILENAME $document_root$fastcgi_script_name;

附lnmp中laravel项目的nginx解决Warning: require(): open_basedir restriction in effect nginx

fastcgi_param PHP_ADMIN_VALUE "open_basedir=/home/wwwroot/waf_new/:/tmp/:/proc/";

nginx的laravel应用配置waf_new.conf示例

server {    listen 10099;    server_name www.waf_new.com;    root /home/wwwroot/waf_new/public;    add_header X-Frame-Options "SAMEORIGIN";    add_header X-XSS-Protection "1; mode=block";    add_header X-Content-Type-Options "nosniff";    index index.html index.htm index.php;    charset utf-8;    location / {        try_files $uri $uri/ /index.php?$query_string;    }    location = /favicon.ico { access_log off; log_not_found off; }    location = /robots.txt  { access_log off; log_not_found off; }    error_page 404 /index.php;    location ~ \.php$ {        fastcgi_split_path_info ^(.+\.php)(/.+)$;        fastcgi_pass unix:/tmp/php-cgi.sock;        fastcgi_index index.php;        include fastcgi_params;        fastcgi_param  SCRIPT_FILENAME $document_root$fastcgi_script_name;        fastcgi_param PHP_ADMIN_VALUE "open_basedir=/home/wwwroot/waf_new/:/tmp/:/proc/";        fastcgi_connect_timeout 180;        fastcgi_read_timeout 600;        fastcgi_send_timeout 600;    }    location ~ /\.(?!well-known).* {        deny all;    }    access_log  /home/wwwlogs/waf_new.log;}

laravel-mongodb组件安装配置 参考地址

cd /home/wwwroot/waf_new

laravel5.5默认安装version ^3.3 for jenssegers/mongodb

composer require jenssegers/mongodb

注册服务

vim config/app.phpproviders数组追加Jenssegers\Mongodb\MongodbServiceProvider::class,

添加 Facades

aliases数组追加后保存退出'Mongo'     => Jenssegers\Mongodb\MongodbServiceProvider::class,

修改数据库配置文件 config/database.php 中

'mongodb' => [    'driver'   => 'mongodb',    'host'     => '127.0.0.1',    'port'     => 27017,    'database' => 'admin',    'username' => 'mintomax',    'password' => 'max2min'],

默认db连接可改可不改,如不改使用时需指定db连接

'default' => env('DB_CONNECTION', 'mongodb'),

自增长id的model代码

<?php/** author: */namespace App\Repository\MongoModels;use DB;// use Moloquent;use Jenssegers\Mongodb\Eloquent\Model as Moloquent;class AutoIncrement extends Moloquent{    //The connection name for the model.    protected $connection = 'mongodb';    //文档名    protected $collection = 'ids';    //设置id    protected $primaryKey = '_id';    //设置字段白名单    protected $fillable = [        '_id',      //主键        'name',     //文档名        'id',       //id值    ];    //文档自增id对应的collection名称，需要显示声明    public $name; //不能通过构造函数来传参; 设计bug?    /*    * 创建集合    */    private function create()    {        return $this->fill(['name' => $this->name, 'id' => 0])->save();    }    /*    * 获得id自增num后的id值，不存在集合则创建    * @param int num    * @return int $id;    */    public function getId(int $num = 1)    {        //increment return effected rows' count        $count = $this->where('name', $this->name)->increment('id', $num);        if ($count == 0) {            $this->create();            $count = $this->where('name', $this->name)->increment('id', $num);        }        return $this->where('name', $this->name)->first()->toArray()['id'];    }}

waf_log集合对应的model代码

<?php/** author: * refer: https://github.com/jenssegers/laravel-mongodb* refer: https://docs.mongodb.com/php-library/master/tutorial/crud/#complex-queries-with-aggregation*/namespace App\Repository\MongoModels\Waf;use DB;// use Moloquent;use Jenssegers\Mongodb\Eloquent\Model as Moloquent;class Waflog extends Moloquent{    //The connection name for the model.    protected $connection = 'mongodb';    //文档名    protected $collection = 'waf_log';    //设置id    protected $primaryKey = '_id';    //设置字段白名单    protected $fillable = [        '_id',      //主键        'time',     //插入时间        'tid',      //类型id        'xffip',    //ip地址        'domain',   //域名或ip地址        'url',      //uri地址        'data',     //注入内容    ];    //client实例    public $client;    //db实例    public $db;    /*    * 获取client实例    */    public function getClient()    {        if (!$this->client) {            $this->client = DB::connection($this->connection)->getMongoClient();        }        return $this->client;    }    /*    * 获取db实例    */    public function getDb()    {        if (!$this->db) {            $this->db = DB::connection($this->connection)->getMongoDB();        }        return $this->db;    }    /*    * 插入一条记录    */    public function insert($row)    {        foreach ($this->fillable as $key) {            $this->{$key} = $row[$key];        }        if ($this->_id) return false;        return $this->save();    }    /*    * 插入多条记录    */    public function bulkInsert($rows)    {        return DB::connection($this->connection)            ->collection($this->collection)            ->insert($rows);    }    /*    * 获取一个数据    */    public function getLastOne()    {        // return $this->first()->toArray();        return $this->orderBy('_id', 'desc')            ->skip(0)            ->take(1)            // ->get(['_id'])            ->get()            ->toArray();    }    /*    * 获取集合总数    */    public function total()    {        // return $this->count(); //execute slowly        $collection = $this->getClient()->waf->waf_log;        $cursor = $collection->count();        return $cursor;    }    /*    * 简单的统计查询（mongodb真的不适合做聚合排序之类的操作）    */    public function countByAttackTypes()    {        $res = [];        $collection = $this->getClient()->waf->waf_log;        $cursor = $collection->aggregate([            ['$group' => ['_id' => '$tid', 'c' => ['$sum' => 1]]],            // ['$sort' => ['c' => -1]],            ['$limit' => 1000],        ]);        foreach ($cursor as $row) {            $res[$row['_id']] = $row['c'];        }        return $res;    }}

路由代码

Route::group(['prefix' => 'Mongo'], function () {    Route::any('/Insert/insertBulk', 'Mongo\InsertController@insertBulk');    Route::any('/Select/countByAttackTypes', 'Mongo\SelectController@countByAttackTypes');});

模拟插入数据controller代码

<?phpnamespace App\Http\Controllers\Mongo;use App\Http\Controllers\Controller;use App\Repository\MongoModels\Waf\Waflog;use App\Repository\MongoModels\AutoIncrement;class InsertController extends Controller{    /**     * Create a new controller instance.     *     * @return void     */    public function __construct()    {        set_time_limit(0);        ignore_user_abort(true);        ini_set('memory_limit', '128M');        $this->logfile = storage_path() . DIRECTORY_SEPARATOR . 'logs' . DIRECTORY_SEPARATOR . 'insert.log';        $this->ai = new AutoIncrement();        $this->ai->name = 'waf_log';    }    /*    * 批量插入    */    public function insertBulk()    {        $num = 500 * 10000; //将要插入的数据总数        $c = 0; //此次插入过程中的已经插入数据总数        $maxNum = 1000; //一次插入的最大个数        $waflog = new Waflog();        file_put_contents($this->logfile, date('Y-m-d H:i:s') . "\tinsert start num $num" . PHP_EOL, FILE_APPEND);        while ($c < ($num - $maxNum)) {            $maxid = $this->ai->getId($maxNum);            $rows = $this->getRows($maxid, $maxNum);            $waflog->bulkInsert($rows);            $c += $maxNum;            // file_put_contents($this->logfile, date('Y-m-d H:i:s') . "\t$maxid\t$maxNum" . PHP_EOL, FILE_APPEND);        }        $thisNum = $num - $c;        $maxid = $this->ai->getId($thisNum);        $rows = $this->getRows($maxid, $thisNum);        $waflog->bulkInsert($rows);        file_put_contents($this->logfile, date('Y-m-d H:i:s') . "\t$maxid\t$thisNum\tend" . PHP_EOL, FILE_APPEND);    }    /*生成一条数据*/    private function getRow(int $id)    {        $row = [];        $row['_id'] = $id;        $row['time'] = time();        $row['tid'] = rand(1, 8);        $xffip = rand(0, 255) . '.' . rand(0, 255) . '.' . rand(0, 255) . '.' . rand(0, 255);        $domain = 'www.test';        $url = '/test.swf?xml=/test.xml&&id=';        $row['xffip'] = ip2long($xffip);        $row['domain'] = $domain . rand(0, 200) . '.org';        $row['url'] = $url . rand(0, 10000);        $row['data'] = '<script>http request sql injection</script>';        return $row;    }    /*生成多条数据*/    private function getRows(int $maxId, int $num)    {        $rows = [];        $minId = $maxId - $num + 1;        for ($id = $minId; $id <= $maxId; $id ++) {            $rows[] = $this->getRow($id);        }        return $rows;    }}

查询controller代码

<?phpnamespace App\Http\Controllers\Mongo;use App\Http\Controllers\Controller;use App\Repository\MongoModels\Waf\Waflog;class SelectController extends Controller{    /**     * Create a new controller instance.     *     * @return void     */    public function __construct()    {        set_time_limit(0);        ignore_user_abort(true);        ini_set('memory_limit', '1280M');        $this->logfile = storage_path() . DIRECTORY_SEPARATOR . 'logs' . DIRECTORY_SEPARATOR . 'select.log';    }    /*    * 按攻击类型统计查询    */    public function countByAttackTypes()    {        $waflog = new Waflog();        $res = $waflog->countByAttackTypes();        file_put_contents($this->logfile, json_encode($res));        // $res = $waflog->getLastOne();        var_dump($res);    }}

统计

db.waf_log.count()

以下示例中的explain, allowDiskUse, hint好像只有第一个参数有效(hint只在mongodb3.6版本及以上支持)

重点是虽然指定了索引，但这种查询还是无法使用索引，总量才2000w，性能就别提了

db.waf_log.aggregate([    {        $match: {time: {"$gt": 1513136656}}    }, {        $project: {_id: 1, xffip:1, url:1}    }, {        $group: {            _id: {"xffip": "$xffip", "url": "$url"},            c: {$sum: 1},        }    }, {        $sort: {c: -1}    }, {        $skip: 20    }, {        $limit: 10    }], {explain: true}, {allowDiskUse: true}, {hint: "time_1_xffip_1_url_1"});

结论

mongodb不适合做有关联查询的聚合统计查询，nosql之类的估计都不适合
插入性能还是可观的，mongodb的默认配置用以上代码单进程插入

量级 速度 0~100w 3.33w/s 100w~500w 1.50w/s 500w~1000w 1.34w/s 1000w~2000w 1.30w/s

由于项目上有需要，一些关系操作实在避免不了，也想不出有什么更优的集合设计，只能说不适合。
如果只是针对某列来查询，创建单列索引mysql单表做partition设计总量上亿查询也是毫无压力。