使用mysql 和redis实现分页优化排序

当论坛帖子数量十分庞大时，直接使用MySQL limit查询进行分页会变得十分缓慢，因此我们需要使用其他技术辅助进行分页处理。

假如我们帖子列表和发帖都是用Ajax的POST来提交数据的，请用PHP，并且结合MySQL和redis的ZSET编写程序，实现以下基本逻辑：

1、处理获取帖子列表页内容的AJax请求，AJax请求的参数为page(页码)，程序需要返回当前页码的所有帖子的数据，返回的数据类型是json格式的，请自行规定具体接口数据结构。

2、处理发表帖子的Ajax post请求，实现把数据写入到MYSQL等操作。

具体需求如下：

1、 分页是按发帖时间倒序排列，每页50条；

2、 不允许使用开源框架；

3、 进行必要的封装；

4、 假设Redis和MySQL服务器使用localhost，用户名及密码均为www.huchen.net；

5、 代码结构良好，PhpDoc注释清晰；

 

分析下需求 帖子的发帖时间倒序 就是帖子ID倒序

先来个redis操作类

<?php

/**
 * redis操作类
 * author by hu.chen 20191110
 * Class RedisDemo
 */
class RedisDemo
{
    private static $instance;
    private $objRedis;
    private $redisAddress = '127.0.0.1';
    private $redisPort = '6379';
    private $redisPassword = 'www.huchen.net';
    private $redisDbName = 16;
    private $redisPrefix = 'JJMS';
    private $key = 'List';

    /**
     * 构造方法 初始化redis对象
     * JJWXCRedisDemo constructor.
     * @param $config
     */
    private function __construct($config)
    {

        if (!empty($config['redisAddress'])){
            $this->redisAddress = $config['redisAddress'];
        }
        if (!empty($config['redisPassword'])){
            $this->redisPassword = $config['redisPassword'];
        }
        if (!empty($config['redisPort'])){
            $this->redisPort = $config['redisPort'];
        }
        if (!empty($config['redisDbName'])){
            $this->redisDbName = $config['redisDbName'];
        }
        if (!empty($config['redisPrefix'])){
            $this->redisPrefix = $config['redisPrefix'];
        }
        $this->objRedis = new Redis();
        //链接redis
        try{
            $connect = $this->objRedis->connect($this->redisAddress,$this->redisPort,30);
        }catch (Exception $e) {
            echo $e->getMessage();exit;
        }
        //认证密码
        try{
            $auth = $this->objRedis->auth($this->redisPassword);
        }catch (Exception $e) {
            echo $e->getMessage();exit;
        }
        //选择数据库
        $this->objRedis->select($this->redisDbName);
    }

    /**
     * 单例
     * @param $config
     * @return JJWXCRedisDemo
     */
    public static function getInstance($config){
        if(self::$instance == NULL){
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    /**
     * 添加hash数据
     * @param $id
     * @param $info
     */
    public function setHash($id,array $info)
    {
        if (!is_numeric($id) || !is_array($info) || empty($info)){
            return false;
        }
        $redisHashKey = $this->redisPrefix.'_'.$id;
        $this->objRedis->hMSet($redisHashKey,$info);
    }

    /**
     * 获取hash数据
     * @param $keys
     * @return array
     */
    public function getHash($keys)
    {
        $res = array();
        if (is_string($keys)){
            $res = $this->objRedis->hGetAll($this->redisPrefix.'_'.$keys);
        }
        if (is_array($keys)){
            foreach ($keys as $v){
                $res[$v] = $this->objRedis->hGetAll($this->redisPrefix.'_'.$v);
            }
        }
        return $res;
    }

    /**
     * 向某个有序集合添加数据
     * @param $score
     * @param $value
     * @param $key
     * @return bool
     */
    public function setZSet($score,$value,$key)
    {
        if (empty($score) || empty($value)){
            return false;
        }
        if (!empty($key)){
            $this->key = $key;
        }
        $this->objRedis->zAdd($this->redisPrefix.$this->key,array(),$score,$score);
    }

    /**
     * 获取有序集合长度
     * @param $key
     * @return int
     */
    public function getZSetTotal($key)
    {
        if (!empty($key)){
            $this->key = $key;
        }
        $name = $this->redisPrefix.$this->key;
        $total = $this->objRedis->zCard($name);
        return $total;
    }

    /**
     * 获取有序集合数据
     * @param $key
     * @param $offset
     * @param $end
     */
    public function getZSetList($key,$offset,$end)
    {
        if (!empty($key)){
            $this->key = $key;
        }
        $name = $this->redisPrefix.$this->key;
        $list = $this->objRedis->zRange($name,$offset,$end);
        return $list;
    }
}

 

再来个数据库简单的操作类

<?php

/**
 * mysql操作类
 * author by hu.chen 20191221
 * Class MysqlDemo
 */
class MysqlDemo
{
    private static $instance = NULL;
    private $objMysql;
    private $charset = "utf8";
    private $username = "www.huchen.net";

private $password = "www. huchen.net";

    private $port = 3306;
    private $database = 'test';
    private $host = '127.0.0.1';
    private $table = 'board';
    private $sql;

    /**
     * 构造方法 初始化mysql对象
     * JJWXCMysqlDemo constructor.
     * @param $config
     */
    private function __construct($config)
    {
        if (!empty($config['host'])){
            $this->host = $config['host'];
        }
        if (!empty($config['username'])){
            $this->username = $config['username'];
        }
        if (!empty($config['password'])){
            $this->password = $config['password'];
        }
        if (!empty($config['port'])){
            $this->port = $config['port'];
        }
        if (!empty($config['charset'])){
            $this->charset = $config['charset'];
        }
        if (!empty($config['database'])){
            $this->database = $config['database'];
        }

        $this->objMysql = new mysqli($this->host,$this->username,$this->password,$this->database,$this->port);
        if($this->objMysql->connect_error){
            die("连接失败: " . mysqli_connect_error());
        }
        mysqli_set_charset($this->objMysql,"utf8");
    }

    /**
     * 单例模式
     * @param $config
     * @return JJWXCMysqlDemo|null
     */
    public static function getInstance($config){
        if(self::$instance == NULL){
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    /**
     * 添加数据
     * @param $data
     * @return bool|int|string 成功返回id
     */
    public function add(array $data)
    {
        if(!is_array($data))
        {
            return false;
        }
        $fieldStr = '';
        $valueStr = '';
        foreach($data as $key =>$value)
        {

            if(isset($value))
            {
                $fieldStr .= "`".addslashes($key)."`,";
                $valueStr .= "'".addslashes($value)."',";
            }

        }
        $fieldStr = rtrim($fieldStr,",");
        $valueStr = rtrim($valueStr,",");
        $this->sql = "INSERT INTO {$this->table} ({$fieldStr}) VALUES({$valueStr})";
        if($this->objMysql->query($this->sql))
        {
            return mysqli_insert_id($this->objMysql);
        }else{
            return mysqli_errno($this->objMysql);
        }
    }

    /**
     * 更新数据
     * @param $data
     * @param $where //条件
     * @return bool|int 成功返回收影响行数
     */
    public function update($data,$where)
    {
        if(!is_array($data))
        {
            return false;
        }
        $field = "";
        foreach($data as $k=>$v){
            $field .= "`".addslashes($k)."`='".addslashes($v)."',";
        }
        $field = rtrim($field,",");
        $this->sql = "UPDATE {$this->table} SET {$field}";
        if(!empty($where))
        {
            $this->sql .= " WHERE ".$where;
        }
        if($this->objMysql->query($this->sql))
        {
            return mysqli_affected_rows($this->objMysql);
        }else{
            return mysqli_errno($this->objMysql);
        }
    }

    /**
     * 删除数据
     * @param $where //条件
     * @return bool|int 成功返回收影响行数
     */
    public function del($where)
    {
        if(empty($where))
        {
            return false;
        }
        $this->sql = "DELETE FROM {$this->table} WHERE ".$where;
        if($this->query())
        {
            return mysqli_affected_rows($this->link);
        }else{
            return mysqli_errno($this->objMysql);
        }
    }
}

 

 下面是操作类 可以理解为控制器吧

<?php
require_once "mst1.php";
require_once "JJWXCRedisDemo.php";
require_once "JJWXCMysqlDemo.php";
require_once "Tools.php";

/**
 * author by hu.chen 20191221
 * Class Index
 */
class Index
{
    /**
     * 新增帖子
     */
    public function addBBS()
    {
        //判断变量是否为空
        if (!$_POST){
            self::echoExit([],'错误请求方式',false);
        }
        if (empty($_POST['subject'])){
            self::echoExit([],'题目不可为空',false);
        }
        if (empty($_POST['body'])){
            self::echoExit([],'主体',false);
        }
        if (empty($_POST['author'])){
            self::echoExit([],'发帖人',false);
        }
        //数据组装
        $info = array(
            'subject'=>$_POST['subject'],
            'author'=>$_POST['author'],
            'ldate'=>date('Y-m-d H:i:s',time()),
            'replies'=>0,
            'ndate'=>date('Y-m-d H:i:s',time()),
            'ip'=>getIp()
        );
        //数据入mysql
        $addMysqlId = JJWXCMysqlDemo::getInstance([])->add($info);
        if (!$addMysqlId){
            self::echoExit([],'发帖失败',false);
        }
        //插入数据库成功后 向redis插入相同数据
        JJWXCRedisDemo::getInstance([])->setHash($addMysqlId,$info);
        JJWXCRedisDemo::getInstance([])->setZSet($addMysqlId,$addMysqlId,'');

        self::echoExit([],'发帖成功',true);
    }

    /**
     * 获取列表数据
     * @return array
     */
    public function getList()
    {
        $page = !empty($_REQUEST['page'])?$_REQUEST['page']:1;//页数
        $size = !empty($_REQUEST['size'])?$_REQUEST['size']:50;//每页数据量
        //区间
        $offset = ($page-1) * $size;
        $end = ($offset + $size) - 1;

        $total = JJWXCRedisDemo::getInstance([])->getZSetTotal('List'); //数据总量

        $allPage = ceil($total/$size); //总页码

        $tmpList = JJWXCRedisDemo::getInstance([])->getZSetList('List',$offset,$end);
        $list = array();
        if (!empty($tmpList)){
            foreach ($tmpList as $v){
                $list[$v] = JJWXCRedisDemo::getInstance([])->getHash($v);
            }
        }
        //数据组装
        $res = array(
            'listInfo' => $list,
            'pageInfo' => array(
                'allPage' => $allPage,
                'total'=>$total,
                'page'=>$page,
                'size'=>$size
            )
        );
        return $res;
    }

    /**
     * 返回json数据
     * @param array $data
     * @param string $msg
     * @param bool $status
     */
    public function echoExit($data = array(),$msg = "",$status = true)
    {
        $returnParam = [
            'code' => $status?200:500,
            'error_msg'  => $msg,
            'data'    => $data,
        ];
        $res = json_encode($returnParam);
        header("Content-type:application/json;charset=utf-8");
        die($res);
    }
}
?>

 

最后加上一个Tools文件  里面放公用方法了

/**
 * 打印函数
 * author by hu.chen
 * @param $t
 * @param bool $i
 */
function hc_echo_end($t,$i=false)
{
    static $si=0;
    if($si==0) {
        echo"<pre>\r\n";
    }else{
        echo"\r\n";
    }if(!$i){
    print_r($t);exit;
}
    print_r($t);
    $si++;
    if($i!==true&&$si>=$i)
    {
        exit;
    }
}

/**
 * 获取ip函数 从网上抄的
 * @return mixed|string
 */
function getIp()
{
    if ($_SERVER["HTTP_CLIENT_IP"] && strcasecmp($_SERVER["HTTP_CLIENT_IP"], "unknown")) {
        $ip = $_SERVER["HTTP_CLIENT_IP"];
    } else {
        if ($_SERVER["HTTP_X_FORWARDED_FOR"] && strcasecmp($_SERVER["HTTP_X_FORWARDED_FOR"], "unknown")) {
            $ip = $_SERVER["HTTP_X_FORWARDED_FOR"];
        }else{
            if ($_SERVER["REMOTE_ADDR"] && strcasecmp($_SERVER["REMOTE_ADDR"], "unknown")) {
                $ip = $_SERVER["REMOTE_ADDR"];
            }else {
                if(isset ($_SERVER['REMOTE_ADDR']) && $_SERVER['REMOTE_ADDR'] && strcasecmp($_SERVER['REMOTE_ADDR'], "unknown")) {
                    $ip = $_SERVER['REMOTE_ADDR'];
                }else{
                    $ip = "unknown";
                }
            }
        }
    }
    return $ip;
}

 

以上就实现了redis的zset与mysql 实现 分页操作