[大数据]Openresty/Lua/Redis/Mysql实现静态化网页加载

场景

类似于秒杀活动，或者是有一些不常变动的网页，可以通过生成本地html文件，用户访问时直接通过nginx访问本地文件，不走或者减少操作数据库，以降低用户等待时间，提升用户体验。

实践

下载openresty

OpenResty - 下载

然后解压备用

新增lua工具

1、在解压目录下的lualib文件夹下新建myutil文件夹

2、新增并编辑redis_factory.lua文件，这个文件用来操作redis

local redis_factory = function(h)
    
    local h           = h

    h.redis           = require('resty.redis')
    h.cosocket_pool   = {max_idel = 10000, size = 10000}

    h.commands        = {
        "append",            "auth",              "bgrewriteaof",
        "bgsave",            "bitcount",          "bitop",
        "blpop",             "brpop",
        "brpoplpush",        "client",            "config",
        "dbsize",
        "debug",             "decr",              "decrby",
        "del",               "discard",           "dump",
        "echo",
        "eval",              "exec",              "exists",
        "expire",            "expireat",          "flushall",
        "flushdb",           "get",               "getbit",
        "getrange",          "getset",            "hdel",
        "hexists",           "hget",              "hgetall",
        "hincrby",           "hincrbyfloat",      "hkeys",
        "hlen",
        "hmget",             "hmset",             "hscan",
        "hset",
        "hsetnx",            "hvals",             "incr",
        "incrby",            "incrbyfloat",       "info",
        "keys",
        "lastsave",          "lindex",            "linsert",
        "llen",              "lpop",              "lpush",
        "lpushx",            "lrange",            "lrem",
        "lset",              "ltrim",             "mget",
        "migrate",
        "monitor",           "move",              "mset",
        "msetnx",            "multi",             "object",
        "persist",           "pexpire",           "pexpireat",
        "ping",              "psetex",            "psubscribe",
        "pttl",
        "publish",           "punsubscribe",      "pubsub",
        "quit",
        "randomkey",         "rename",            "renamenx",
        "restore",
        "rpop",              "rpoplpush",         "rpush",
        "rpushx",            "sadd",              "save",
        "scan",              "scard",             "script",
        "sdiff",             "sdiffstore",
        "select",            "set",               "setbit",
        "setex",             "setnx",             "setrange",
        "shutdown",          "sinter",            "sinterstore",
        "sismember",         "slaveof",           "slowlog",
        "smembers",          "smove",             "sort",
        "spop",              "srandmember",       "srem",
        "sscan",
        "strlen",            "subscribe",         "sunion",
        "sunionstore",       "sync",              "time",
        "ttl",
        "type",              "unsubscribe",       "unwatch",
        "watch",             "zadd",              "zcard",
        "zcount",            "zincrby",           "zinterstore",
        "zrange",            "zrangebyscore",     "zrank",
        "zrem",              "zremrangebyrank",   "zremrangebyscore",
        "zrevrange",         "zrevrangebyscore",  "zrevrank",
        "zscan",
        "zscore",            "zunionstore",       "evalsha",
        -- resty redis private command
        "set_keepalive",     "init_pipeline",     "commit_pipeline",      
        "array_to_hash",     "add_commands",      "get_reused_times",
    }

    -- connect
    -- @param table connect_info, e.g { host="127.0.0.1", port=6379, password="", timeout=1000, database=0}
    -- @return boolean result
    -- @return userdata redis_instance
    h.connect = function(connect_info)
        local redis_instance = h.redis:new()
        redis_instance:set_timeout(connect_info.timeout)
        if not redis_instance:connect(connect_info.host, connect_info.port) then 
            return false, nil
        end
        if connect_info.password ~= '' then
            redis_instance:auth(connect_info.password)
        end
        redis_instance:select(connect_info.database)
        return true, redis_instance
    end

    -- spawn_client
    -- @param table h, include config info
    -- @param string name, redis config name
    -- @return table redis_client
    h.spawn_client = function(h, name)

        local self = {}
        
        self.name           = ""
        self.redis_instance = nil
        self.connect        = nil
        self.connect_info   = {
            host = "",   port = 0,    password = "", 
            timeout = 0, database = 0
        }

        -- construct
        self.construct = function(_, h, name)
            -- set info
            self.name         = name
            self.connect      = h.connect
            self.connect_info = h[name]
            -- gen redis proxy client
            for _, v in pairs(h.commands) do
                self[v] = function(self, ...)
                    -- instance test and reconnect  
                    if (type(self.redis_instance) == 'userdata: NULL' or type(self.redis_instance) == 'nil') then
                        local ok
                        ok, self.redis_instance = self.connect(self.connect_info)
                        if not ok then return false end
                    end
                    -- get data
                    local vas = { ... }
                    return self.redis_instance[v](self.redis_instance, ...)
                end
            end
            return true
        end

        -- do construct
        self:construct(h, name) 

        return self
    end     



    local self = {}

    self.pool  = {} -- redis client name pool

    -- construct
    -- you can put your own construct code here.
    self.construct = function()
        return
    end

    -- spawn
    -- @param string name, redis database serial name
    -- @return boolean result
    -- @return userdata redis
    self.spawn = function(_, name)
        if self.pool[name] == nil then
            ngx.ctx[name] = h.spawn_client(h, name) 
            self.pool[name] = true
            return true, ngx.ctx[name]
        else
            return true, ngx.ctx[name]
        end
    end

    -- destruct
    -- @return boolean allok, set_keepalive result
    self.destruct = function()
        local allok = true
        for name, _ in pairs(self.pool) do
            local ok, msg = ngx.ctx[name].redis_instance:set_keepalive(
                h.cosocket_pool.max_idel, h.cosocket_pool.size
            )
            if not ok then allok = false end 
        end
        return allok
    end

    -- do construct
    self.construct() 
        
    return self
end


return redis_factory

3、新增并编辑mysql_factory.lua文件，这个文件用来操作数据库

local mysql = require("resty.mysql")  
 
local mysql_pool = {}  
  
--[[  
    先从连接池取连接,如果没有再建立连接.  
    返回:  
        false,出错信息.  
        true,数据库连接  
--]]  
function mysql_pool:get_connect(cfg)  
    if ngx.ctx[mysql_pool] then  
        return true, ngx.ctx[mysql_pool]  
    end  
  
    local client, errmsg = mysql:new()  
    if not client then  
        return false, "mysql.socket_failed: " .. (errmsg or "nil")  
    end  
  
    client:set_timeout(10000)  --30秒  
  
    local options = {  
        host = cfg.mysqlConfig.prod.host,  
        port = cfg.mysqlConfig.prod.port,  
        user = cfg.mysqlConfig.prod.user,  
        password = cfg.mysqlConfig.prod.password,  
        database = cfg.mysqlConfig.prod.database  
    }  
  
    local result, errmsg, errno, sqlstate = client:connect(options)  
    if not result then  
        return false, "mysql.cant_connect: " .. (errmsg or "nil") .. ", errno:" .. (errno or "nil") ..  
                ", sql_state:" .. (sqlstate or "nil")  
    end  
  
    local query = "SET NAMES " .. "utf8"  
    local result, errmsg, errno, sqlstate = client:query(query)  
    if not result then  
        return false, "mysql.query_failed: " .. (errmsg or "nil") .. ", errno:" .. (errno or "nil") ..  
                ", sql_state:" .. (sqlstate or "nil")  
    end  
  
    ngx.ctx[mysql_pool] = client  
  
    -- 测试，验证连接池重复使用情况  
    --[[ comments by leon1509  
    local count, err = client:get_reused_times()  
    ngx.say("xxx reused times" .. count);  
    --]]  
  
    return true, ngx.ctx[mysql_pool]  
end  
  
--[[  
    把连接返回到连接池  
    用set_keepalive代替close() 将开启连接池特性,可以为每个nginx工作进程，指定连接最大空闲时间，和连接池最大连接数  
 --]]  
function mysql_pool:close()  
    if ngx.ctx[mysql_pool] then  
        -- 连接池机制，不调用 close 而是 keeplive 下次会直接继续使用  
        -- lua_code_cache 为 on 时才有效  
        -- 60000 ： pool_max_idle_time ， 100：connections  
        ngx.ctx[mysql_pool]:set_keepalive(60000, 80)  
        -- 调用了 set_keepalive，不能直接再次调用 query，会报错  
        ngx.ctx[mysql_pool] = nil  
    end  
end  
  
--[[  
    查询  
    有结果数据集时返回结果数据集  
    无数据数据集时返回查询影响  
    返回:  
        false,出错信息,sqlstate结构.  
        true,结果集,sqlstate结构.  
--]]  
function mysql_pool:query(sql, flag)  
    local ret, client = self:get_connect(flag)  
    if not ret then  
        return false, client, nil  
    end  
  
    local result, errmsg, errno, sqlstate = client:query(sql)  
  
    while errmsg == "again" do  
        result, errmsg, errno, sqlstate = client:read_result()  
    end  
  
    self:close()  
  
    if not result then  
        errmsg = "mysql.query_failed:" .. (errno or "nil") .. (errmsg or "nil")  
        return false, errmsg, sqlstate  
    end  
  
    return true, result, sqlstate  
end  
  
return mysql_pool

4、新增并编辑config_constant.lua文件，这个文件用来配置redis和mysql的连接信息

config = {}

config.redisConfig = {
    redis_a = {
        --ip
        host = '127.0.0.1',
        --端口
        port = 6379,
        --密码
        password = 'qweasdzczs',
        --超时时间，如果是测试环境debug的话，这个值可以给长一点；如果是正式环境，可以设置为200
        timeout = 2000,
        --redis的库
        database = 0,
    },
    }

config.mysqlConfig = {
    prod = {
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = '!@#QWEasdzxc',
        database = 'file',
    }
}
return config

5、新增并编辑article.lua文件，这个是业务文件，目的实现文章的静态化访问

--平台公共的配置文件常量
local config = require "myutil.config_constant"
--redis连接池工厂
local redis_factory = require('myutil.redis_factory')(config.redisConfig) -- import config when construct
--获取redis的连接实例
local ok, redis_a = redis_factory:spawn('redis_a')
local json = require("cjson")

--获取URL链接中的文章ID，例、http://testdomain.com/article/66666666，http://testdomain.com/article/66666666.html，key为66666666或者66666666.html
local key = ngx.re.sub(ngx.var.uri, "^/article/(.*)", "$1", "o")
if (string.len(key) == 0) then 
    -- 传参有误，根目录下要有404.html
    ngx.req.set_uri('/404.html')
end

--在redis中获取key对应的值
local va = redis_a:get("article:"..key)

local file = nil

if(va and va  ~= ngx.null) then
    --redis中存在数据
    file = json.decode(va)
else
    --mysql连接池
    local mysqlUtil = require "myutil.mysql_factory"

    --组装sql语句
    local sql = "SELECT id, article_name articleName, html_path htmlPath, create_time createTime, user_id userId FROM article_info where id = "..key

    --执行sql语句
    local ret, res, sqlstate = mysqlUtil:query(sql, config);
    --判断查询结果
    if((not ret) or res ==nil or #res<1) then
        local hasSplit, endSplit = string.find(key,'%.',1)
        if(hasSplit)then
            local keySplit = string.sub(key, 0, hasSplit - 1)
            sql = "SELECT id, article_name articleName, html_path htmlPath, create_time createTime, user_id userId FROM article_info where id = "..keySplit
            ret, res, sqlstate = mysqlUtil:query(sql, config);
        end
    end
    --判断查询结果
    if(ret and res ~=nil and #res>0) then
        --取出最新的值
        file = res[1]
        local fileStr = json.encode(file)
        redis_a:set("article:"..key,fileStr)
    end
end
if (file) then 
    -- 根据数据库中配置的地址加载文件
    local du = file.htmlPath
    ngx.req.set_uri(du)
else
    ngx.req.set_uri('/404.html')
end

配置Nginx

nginx通过rewrite + lua的方式访问

worker_processes  1;

events {
    worker_connections  1024;
}

http {
    include       mime.types;
    default_type  application/octet-stream;

    sendfile        on;

    keepalive_timeout  65;
    autoindex on;# 显示目录
    autoindex_exact_size on;# 显示文件大小
    autoindex_localtime on;# 显示文件时间

    server {
        listen       80;
        server_name  localhost;

        location /article {
            root d:/;
            #这里的lua文件的路径为绝对路径，请根据自己安装的实际路径填写
            #记得斜杠是/这个，从window中拷贝出来的是\这样，这样是有问题的，务必注意
            rewrite_by_lua_file D:/openresty-test/lualib/myutil/article.lua;
        }
    }

}