mnode第三篇 在mysql之上搭建redis缓存

地址：https://github.com/zhengjinwei123/mnode/tree/master/mnode/mnode/plugin/mysqlRedisCache

此项目主要是为了解决mysql操作数据缓慢的问题，同时交互简单，数据落地安全可靠

项目还在完成中, 比较懒，我只贴上代码，有兴趣可以到github上把代码copy下来运行

数据模型封装：

/**
 * Created by zhengjinwei
 * 数据模型模板生成器
 */
var _ = require("lodash");
var Async = require("async");
var Util = require("util");

function Model() {
    this.updateFields = {};
    this.pk = null;//主键
    this.tableName = "";
    this.tablePrefix = "t_";

    this.mysql = null;
    this.redis = null;

    this.mode = 'c';//d || c      查询类型 d:直接操作mysql  c:缓存
}

Model.prototype.setTableName = function (n) {
    this.tableName = n;
};

Model.prototype.getTableName = function () {
    return this.tableName;
};

Model.prototype.initFields = function (fields) {
    this.fields = fields;
};

Model.prototype.getFullTableName = function () {
    return this.tablePrefix + this.tableName;
};


// 获取主键值
Model.prototype.getPKV = function () {
    if (this.pk) {
        return this.getField(this.pk);
    }
    return null;
};

// 设置主键值
Model.prototype.setPKV = function (pkv) {
    if (this.pk) {
        this.setField(this.pk, pkv);
    }
};

// 获取缓存键名
Model.prototype.getCK = function (pkV) {
    if (_.isUndefined(pkV)) {
        pkV = this.getPKV();
    }
    return this.tableName + ":" + pkV;
};


Model.prototype.byD = function () {
    this.mode = 'd';
};
Model.prototype.byC = function () {
    this.mode = 'c';
};

Model.prototype.setField = function (field, value) {
    if (!_.isUndefined(this.fields[field])) {
        this.fields[field] = value;
        this.updateFields[field] = value;
    }
};
Model.prototype.getField = function (field) {
    if (!_.isUndefined(this.fields[field])) {
        return this.fields[field];
    }
    return null;
};

Model.prototype.getFields = function () {
    return this.fields;
};

Model.prototype.getFieldChanged = function () {
    return this.updateFields;
};
Model.prototype.init_field_changed = function () {
    this.updateFields = [];
};

Model.prototype.setMysql = function (cfg) {
    this.mysql = require("../../utils/db-mysql/app")(cfg);
};

Model.prototype.setRedis = function (cfg) {
    var Redis = require("../../utils/db-redis/app");
    if (_.isUndefined(cfg.poolCnt) ||
        _.isUndefined(cfg.namePrefix) ||
        _.isUndefined(cfg.host) ||
        _.isUndefined(cfg.port) ||
        _.isUndefined(cfg.db) ||
        _.isUndefined(cfg.auth)) {
        throw new TypeError("redis cfg error");
    }
    this.redis = new Redis(cfg.poolCnt, cfg.namePrefix, cfg.host, cfg.port, cfg.db, cfg.auth);
};

Model.prototype.check = function () {
    if (!this.mysql || !this.redis) {
        return false;
    }
    return true;
};


Model.prototype.find = function (pKV) {
    var argsCnt = _.keys(arguments).length;
    var columns, expire, callback = null;
    if (argsCnt == 2) {
        columns = "*";
        expire = 86400;
        callback = arguments[1];
    } else if (argsCnt == 3) {
        columns = "*";
        expire = 86400;
        callback = arguments[2];
    } else {
        columns = arguments[1];
        expire = arguments[2];
        callback = arguments[3];
    }

    var self = this;


    if (!this.check()) {
        callback("please init connection")
    } else {
        if ((this.mode === 'c') && this.pk) {
            var results = null;
            Async.waterfall([
                function (cb) {
                    self.redis.exists(self.getCK(pKV), function (err, exists) {
                        if (!exists) {
                            cb("key:" + self.getCK(pKV) + " not exists");
                        } else {
                            if (columns === "*") {
                                self.redis.hGetAll(self.getCK(pKV), function (err, resp) {
                                    results = resp;
                                    cb(err);
                                });
                            } else {
                                columns = _.isArray(columns) ? columns : [columns];
                                self.redis.hmGet(self.getCK(pKV), columns, function (err, resp) {
                                    results = resp;
                                    cb(err);
                                });
                            }
                        }
                    });
                },
                function (cb) {
                    self.redis.expire(self.getCK(pKV), expire, function (err, resp) {
                        cb(err);
                    })
                }
            ], function (err, resp) {
                if (err || _.isEmpty(results)) {
                    var selFields = [];
                    if (columns === "*") {
                        selFields = "*";
                    } else {
                        selFields = columns.join(",");
                    }

                    var sql = Util.format("SELECT %s FROM %s WHERE %s=%s LIMIT 1", selFields, self.getFullTableName(), self.pk, pKV);

                    self.query(sql, [], function (err, results, fields) {
                        if (!err && (!_.isEmpty(results))) {
                            self.initFields(results[0]);
                            var temp = [];
                            _.forEach(self.getFields(), function (v, k) {
                                temp.push(k);
                                temp.push(v);
                            });
                            self.redis.hmSet(self.getCK(pKV), temp, function (err, resp) {
                                callback(err, self);
                            });
                        } else {
                            callback(err, null);
                        }
                    });
                } else {
                    self.initFields(results);
                    callback(err, self);
                }
            });
        } else {
            var selFields = [];
            if (columns === "*") {
                selFields = "*";
            } else {
                selFields = columns.join(",");
            }

            var sql = Util.format("SELECT %s FROM %s WHERE %s=%s LIMIT 1", selFields, this.getFullTableName(), this.pk, pKV);
            this.query(sql, [], function (err, results, fields) {
                if (!err && (!_.isEmpty(results))) {
                    self.initFields(results[0]);
                    callback(err, self);
                } else {
                    callback(err, results);
                }
            });
        }
    }
};

Model.prototype.insert = function (callback) {
    var fields = [], temp = [], values = [];
    _.forEach(this.fields, function (v, k) {
        fields.push(k);
        values.push(v);
        temp.push("?");
    });

    var self = this;
    var sql = Util.format("insert into %s (%s) values(%s)", this.getFullTableName(), fields.join(","), temp.join(","));

    this.query(sql, values, function (err, resp) {
        if (!err) {
            if (self.mode === "c") {
                var temp = [];
                _.forEach(self.getFields(), function (v, k) {
                    temp.push(k);
                    temp.push(v);
                });
                self.redis.hmSet(self.getCK(), temp, function (err, resp) {
                    callback(err, self);
                });
            } else {
                callback(err);
            }
        } else {
            callback(err);
        }
    });
};

Model.prototype.update = function (callback) {
    var keys = _.keys(this.updateFields);
    if (keys.length == 0) {
        callback(null);
        return 0;
    }

    if (this.pk == '') {
        callback(null);
        return 0;
    }

    if (this.mode === "c") {
        var self = this;

        self.redis.ttl(self.getCK(), function (err, ttl) {
            if (ttl != -1 && ttl < 3) {
                callback("data not exists ttl:" + ttl + self.getCK());
            } else {
                if (err) {
                    callback(err);
                } else {
                    self.redis.multi(function (multi, release) {
                        var temp = [];
                        _.forEach(self.getFieldChanged(), function (v, k) {
                            temp.push(k);
                            temp.push(v);
                        });
                        multi.hmset(self.getCK(), temp);
                        multi.expire(self.getCK(), 68400);
                        self.redis.exec(multi, release, function (err) {
                            if (!err) {
                                self.init_field_changed();
                            }
                            callback(err);
                        })
                    });
                }
            }
        });
    } else {
        var temp = [];
        _.forEach(this.getFieldChanged(), function (v, k) {
            temp.push(k + "=" + v);
        });

        var sql = Util.format("update %s set %s where %s=`%s`", this.getFullTableName(), temp.join(","), this.pk, this.getPKV());
        this.query(sql, [], function (err, resp) {
            callback(err);
        });
    }
};

Model.prototype.delete = function (callback) {
    if (this.pk == '') {
        callback(null);
        return 0;
    }

    if (this.mode === "c") {
        var self = this;
        this.redis.del(this.getCK(), function (err, resp) {
            if (!err) {
                var sql = Util.format("delete from %s where %s=%s", self.getFullTableName(), self.pk, self.getPKV());
                self.query(sql, [], function (err, resp) {
                    callback(err);
                })
            } else {
                callback(err);
            }
        });
    } else {
        var sql = Util.format("delete from %s where %s=`%s`", this.getFullTableName(), this.pk, this.getPKV());
        this.query(sql, [], function (err, resp) {
            callback(err);
        })
    }
};


Model.prototype.query = function (sql, params, callback) {
    params = (_.isArray(params)) ? params : [];
    if (!this.mysql) {
        throw new Error("please call setMysql");
    }
    this.mysql.query(sql, params, {
        failed: function (err) {
            callback(err);
        },
        success: function (rows, fields) {
            callback(null, rows, fields);
        }
    })
};

module.exports = Model;

通过xml自动生成数据模型脚本，模型与xml，xml与数据库绑定：

/**
 * Created by zhengjinwei
 * mysql-redis 缓存 模型化插件
 * 解决操作mysql的复杂度和速率，利用redis作为缓存，redis定时落地到mysql
 */
var XmlParser = require("../../utils/xml-parser/app");
var FileUtil = require("../../utils/file-utils/app");
var _ = require("lodash");
var Util = require("util");

var MysqlRedisCache = function (xmlPath,ModelPath, callback) {
    if (!FileUtil.isFile(xmlPath)) {
        throw new Error(xmlPath + " file not exits");
    }

    genDBCode(xmlPath, function (err, result, modeList) {
        if (!err && result) {
            var fileName = xmlPath.replace(".xml", ".sql");
            FileUtil.writeSync(fileName, result);

            if (modeList) {
                genModels(ModelPath,modeList,callback);
            }
        }
    });
};

function firstUppercase(str){ // 正则法
    str = str.toLowerCase();
    var reg = /\b(\w)|\s(\w)/g; //  \b判断边界\s判断空格
    return str.replace(reg,function(m){
        return m.toUpperCase()
    });
}

function genModels(ModelPath,dataList,callback) {
    var template = FileUtil.readSync(Path.join(__dirname,"./template/model.js"));

    _.forEach(dataList,function(v,tableName){
        var temp = template.replace(/MODELNAME/g,firstUppercase(tableName));
        temp = temp.replace(/tablename/,tableName);
        temp = temp.replace(/\{\}/,JSON.stringify(v['fields']));
        temp = temp.replace(/t_/,v['tablePrefix']);
        temp = temp.replace(/pkv/,v['pk']);
        temp = temp.replace(/genTime/,new Date().toLocaleString());

        //console.log(temp)
        FileUtil.writeSync(Path.join(ModelPath,"/"+tableName+".js"), temp);
    });
    callback(null);
}

var genDBCode = function (xmlPath, callback) {
    XmlParser(xmlPath, function (err, results) {
        var databaseObj = {};
        if (!err && results && results['database']) {
            var db = results['database']['$']['name'];
            var dbCharacter = results['database']['$']['character'];
            var dbCollate = results['database']['$']['collate'];

            databaseObj.dbName = db;
            databaseObj.dbCharacter = dbCharacter;
            databaseObj.dbCollate = dbCollate;
            databaseObj.tables = [];

            if (!results['database']['tables']) {
                callback("xml format error");
            } else {

                var tables = results['database']['tables'];

                if (!_.isArray(tables)) {
                    tables = [tables];
                }
                parseTables(tables, databaseObj);
                genSql(databaseObj, callback);
            }
        } else {
            callback("xml format error");
        }
    });
};


function genSql(databaseObj, callback) {
    if (databaseObj['dbName']) {
        var modelList = {};//用于记录数据模型，之后用这份数据自动生成数据库模型脚本文件

        var sql = Util.format("CREATE DATABASE IF NOT EXISTS `%s` character set %s collate %s;", databaseObj['dbName'], databaseObj['dbCharacter'] || 'utf8', databaseObj['dbCollate'] || 'utf8_general_ci');
        sql += Util.format('\r\nUSE `%s`\r\n', databaseObj['dbName']);

        if (databaseObj['tables']) {
            _.forEach(databaseObj['tables'], function (t, k) {
                sql += Util.format('%s \r\n%s\r\n%s\r\n', '-- ------------------', '-- ' + t['table']['desc'], '-- ------------------');
                sql += Util.format('CREATE TABLE IF NOT EXISTS `%s` (\r\n', t['tablePrefix'] + t['table']['name']);
                if (!t['table']['fields']) {
                    return 0;
                }

                modelList[t['table']['name']] = modelList[t['table']['name']] || {};
                modelList[t['table']['name']].tablePrefix = t['tablePrefix'];

                var auto = null;
                var fieldsSql = [];

                modelList[t['table']['name']].fields = {};

                _.forEach(t['table']['fields'], function (f, k) {
                    var tempSql = "";
                    if (f['autoincr']) {
                        auto = f['start'] || 1;
                    }
                    if (f['name']) {
                        if (!f['length']) {
                            tempSql += Util.format("\t`%s` %s", f['name'], f['type']);
                        } else {
                            tempSql += Util.format("\t`%s` %s(%s)", f['name'], f['type'], f['length']);
                        }

                        if (f['default']) {
                            tempSql += " NOT NULL";
                            tempSql += " DEFAULT " + f['default'];
                        }
                        if (f['autoincr']) {
                            tempSql += ' AUTO_INCREMENT';
                        }
                        if (f['comment']) {
                            tempSql += ' COMMENT ' + "'" + f['comment'] + "'";
                        }
                        fieldsSql.push(tempSql);

                        if (f['default']) {
                            modelList[t['table']['name']].fields[f['name']] = f['default'];
                        } else {
                            if (f['type'].toLowerCase() == 'varchar' || f['type'].toLowerCase() == 'char') {
                                modelList[t['table']['name']].fields[f['name']] = '';
                            } else {
                                modelList[t['table']['name']].fields[f['name']] = 0;
                            }
                        }
                    }
                });

                if (fieldsSql.length) {
                    sql += fieldsSql.join(",\r\n");
                }

                var indexSQL = [];
                if (t['table']['indexs']) {
                    _.forEach(t['table']['indexs'], function (i, k) {
                        var tempSql = "";
                        var type = i['type'].toUpperCase();
                        if (type == 'PRIMARY KEY') {

                            modelList[t['table']['name']].pk = i['field'];//记录唯一索引

                            tempSql += '\tPRIMARY KEY (' + i['field'] + ')'
                        } else if (type == 'KEY' || type == 'UNIQUE KEY') {
                            var keyFields = i['field'].split(",");

                            tempSql += Util.format("\t%s `%s`(", type, i['name']);

                            var tempKeys = [];
                            _.forEach(keyFields, function (v, k) {
                                tempKeys.push(Util.format("`%s`", v));
                            });
                            tempSql += tempKeys.join(",");
                            tempSql += ") USING " + (i['using'] || 'BTREE');
                        }
                        indexSQL.push(tempSql);
                    });
                }
                if (indexSQL.length) {
                    sql += ",\r\n";
                    sql += indexSQL.join(",\r\n");
                }

                sql += "\r\n)";
                if (t['table']['engine']) {
                    sql += 'ENGINE=' + (t['engine'] || 'InnoDB');
                }
                if (auto) {
                    sql += ' AUTO_INCREMENT=' + auto;
                }
                if (t['table']['row_format']) {
                    sql += ' ROW_FORMAT=' + t['table']['row_format'];
                }
                if (t['table']['key_block_size']) {
                    sql += ' KEY_BLOCK_SIZE=' + t['table']['key_block_size'];
                }
                if (t['table']['charset']) {
                    sql += ' DEFAULT CHARSET=' + t['table']['charset'];
                }

                sql += ";\r\n\r\n";
            });
        }

        callback(null, sql, modelList);
    } else {
        callback("xml format error");
    }
}


function parseTables(tables, databaseObj) {
    _.forEach(tables, function (t, k) {
        var tablePrefix = t['$']['tablePrefix'];
        var table = t['table'];

        if (!table) {
            return 0;
        }
        if (!_.isArray(table)) {
            table = [table];
        }
        parseFields(tablePrefix, table, databaseObj);
    });
}

function parseFields(tablePrefix, table, databaseObj) {
    _.forEach(table, function (t, k) {
        var tt = {
            tablePrefix: tablePrefix,
            table: {}
        };

        if (!t['$']['name']) {
            return 0;
        }

        tt.table.name = t['$']['name'];

        if (t['$']['engine']) {
            tt.table.engine = t['$']['engine'];
        }
        if (t['$']['charset']) {
            tt.table.charset = t['$']['charset'];
        }

        if (t['$']['desc']) {
            tt.table.desc = t['$']['desc'];
        }

        if (t['$']['row_format']) {
            tt.table.row_format = t['$']['row_format'];
        }
        if (t['$']['key_block_size']) {
            tt.table.key_block_size = t['$']['key_block_size'];
        }

        var fields = t['field'];
        if (!fields) {
            return 0;
        }
        if (!_.isArray(fields)) {
            fields = [fields];
        }

        tt.table.fields = [];
        parseField(tt, fields);

        if (t['index']) {
            var indexs = t['index'];
            if (!_.isArray(t['index'])) {
                indexs = [t['index']];
            }
            tt.table.indexs = [];
            parseIndex(tt, indexs);
        }

        databaseObj.tables.push(tt);
    });
}

function parseIndex(tt, indexs) {
    _.forEach(indexs, function (i, k) {
        var $ = i['$'];
        tt.table.indexs.push($);
    });
}

function parseField(tt, fields) {
    _.forEach(fields, function (f, k) {
        var $ = f['$'];
        tt.table.fields.push($);
    });
}

module.exports = MysqlRedisCache;


//var Path = require("path");
//var m = new MysqlRedisCache(Path.join(__dirname, "/template/db.xml"), Path.join(__dirname, "/template"),function () {
//
//});

数据落地：

/**
 * Created by zhengjinwei
 * 数据落地 从redis到mysqldb
 */
var _ = require("lodash");
var Async = require("async");
var Util = require("util");

function Cache2DB(redisCfg, mysqlCfg, cacheInterval) {
    this.mysql = require("../../utils/db-mysql/app")(mysqlCfg);

    var Redis = require("../../utils/db-redis/app");
    if (_.isUndefined(redisCfg.poolCnt) ||
        _.isUndefined(redisCfg.namePrefix) ||
        _.isUndefined(redisCfg.host) ||
        _.isUndefined(redisCfg.port) ||
        _.isUndefined(redisCfg.db) ||
        _.isUndefined(redisCfg.auth)) {
        throw new TypeError("redis cfg error");
    }
    this.stat = false;
    this.redis = new Redis(parseInt(redisCfg.poolCnt), redisCfg.namePrefix, redisCfg.host, parseInt(redisCfg.port), redisCfg.db, redisCfg.auth);
    this.startCache(cacheInterval || (3 * 1000 * 60));
}

//获取所有的缓存key
Cache2DB.prototype.getCKS = function (callback) {
    this.redis.keys("updatecachelist:*", callback);
};

Cache2DB.prototype.startCache = function (cacheInterval) {
    if (cacheInterval) {
        var self = this;
        setInterval(function () {
            if (self.stat == false) {
                self.cacheRun(function (err) {
                    self.stat = false;
                });
            }
        }, cacheInterval);
    } else {
        throw new Error("cacheInterval undefined");
    }
};

Cache2DB.prototype.cacheRun = function (callback) {
    this.stat = true;
    var self = this;
    this.getCKS(function (err, cacheKeys) {
        if (!err && cacheKeys) {
            if (!_.isArray(cacheKeys)) {
                cacheKeys = [cacheKeys];
            }
            console.time("cache");
            Async.each(cacheKeys, function (cacheKey, cb) {
                self.cache(cacheKey, cb);
            }, function (err, resp) {
                callback(err);
                console.timeEnd("cache");
            });
        } else {
            callback(err);
        }
    });
};

Cache2DB.prototype.cache = function (key, callback) {
    var self = this;
    this.redis.lRange(key, 0, 999, function (err, list) {
        if (!err && list) {
            if (!_.isArray(list)) {
                list = [list];
            }
            if (list.length <= 0) {
                callback(null);
            } else {
                var updateList = {};
                var cnt = 0;
                _.forEach(list, function (v, k) {
                    var data = JSON.parse(v);
                    if (_.isUndefined(updateList[data['ck']])) {
                        updateList[data['ck']] = data;
                    } else {
                        updateList[data['ck']]['fields'] = _.merge(updateList[data['ck']]['fields'], data['fields']);
                    }
                    cnt++;
                });

                //sql合并
                if (cnt) {
                    var updateKeys = _.keys(updateList);
                    var sqlArray = [];
                    Async.each(updateKeys, function (key, cb) {
                        self.redis.exists(key, function (err, exists) {
                            if (!exists) {
                                console.error("cache miss key:" + key);
                                cb(null);
                            } else {
                                var fields = updateList[key]['fields'];
                                self.redis.hmGet(key, _.keys(fields), function (err, result) {
                                    if (!err && result) {
                                        var sql = parseSql(updateList[key], _.keys(fields), result);
                                        sqlArray.push(sql);
                                    }
                                    cb(null);
                                });
                            }
                        });
                    }, function (err, resp) {
                        if (!err) {
                            self.execSql(sqlArray, function (err) {
                                self.redis.lTrim(key, list.length, -1, function (err, resp) {
                                    callback(null);
                                })
                            });
                        } else {
                            callback(null);
                        }
                    });
                }
            }
        } else {
            if (err) {
                console.error("cache key:" + key + " error:" + err);
            }
            callback(null);
        }
    });
};

Cache2DB.prototype.execSql = function (sqlArray, callback) {
    if (sqlArray.length) {
        var start = 0;
        var len = 20;

        var sqlGroup = [];
        while (sqlArray.length) {
            var sqls = sqlArray.splice(start, len);
            sqls = sqls.join(";");
            sqlGroup.push(sqls);
        }

        var self = this;
        Async.each(sqlGroup, function (sqls, cb) {
            console.log(sqls);
            self.query(sqls, [], function (err, resp) {
                if (err) {
                    console.error("execSql error:", err);
                }
                cb(null);
            });
        }, function (err, resp) {
            callback(null);
        });
    } else {
        callback(null);
    }
};

Cache2DB.prototype.query = function (sql, params, callback) {
    params = (_.isArray(params)) ? params : [];
    this.mysql.query(sql, params, {
        failed: function (err) {
            callback(err);
        },
        success: function (rows, fields) {
            callback(null, rows, fields);
        }
    })
};

function parseSql(stru, fields, data) {
    if (data) {
        var updateFields = [];
        _.forEach(data, function (v, k) {
            if (_.isNaN(parseFloat(v))) {
                updateFields.push(fields[k] + "=" + "'" + v + "'");
            } else {
                updateFields.push(fields[k] + "=" + parseFloat(v));
            }
        });
        if (updateFields) {
            return Util.format("update %s set %s where %s=%s", stru['tbn'], updateFields.join(","), stru['pk'], stru['pkv']);
        }
    }
    return null;
}

module.exports = Cache2DB;