使用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 实现 分页操作