数据库应⽤之--Redis+mysql实现⼤量数据的读写，以及⾼并发
⼀、开发背景
在项⽬开发过程中中遇到了以下三个需求：
1. 多个⽤户同时上传数据；
2. 数据库需要⽀持同时读写；
3. 1分钟内存储上万条数据；
根据对Mysql的测试情况，遇到以下问题：
1. 最先遇到压⼒的是服务器，在写⼊2500-3000条数据时，服务器崩溃了；
2. 当数据库写⼊时，耗时太长，10000条数据，⼤概需要505.887s，相当于8分钟，如下：
a. 表结构：
b. 数据库Procedure:
DROP PROCEDURE IF EXISTS my_insert;
CREATE PROCEDURE my_insert()
BEGIN
DECLARE n int DEFAULT1;
loopname:LOOP
INSERT INTO car_pathinfo_driver_cpy(id, linkphone,cartype,carcolor,carnumber,drivername,pubtime
s)VALUES(n+500,'188********','雪弗兰','⽩','豫A190XS','siker','3');
SET n=n+1;
IF n=10000THEN
LEAVE loopname;
END IF;
END LOOP loopname;
END;
CALL my_insert();
c. 运⾏结果如下：
3. 不断的数据库写⼊导致数据库压⼒过⼤；
出现以上问题，是由于mysql是基于磁盘的IO，基于服务响应性能考虑，就需要给数据做缓存，所以决定使⽤Mysql+redis缓存的解决⽅案，将业务热数据写⼊Redis缓存，使得⾼频业务数据可以直接从内存读取，提⾼系统整体响应速度。
⼆、使⽤Redis+Mysql需要考虑的问题
使⽤redis缓存+mysql数据库存储能解决：
1. 数据读写的速度
2. 服务器的压⼒问题
同时，就需要考虑同步问题了，Redis和Mysql的同步问题
三、Redis+mysql同步解决⽅案
1.写Redis->redis写mysql，读Mysql。
以下是⼀个Redis+mysql同步的⽰例，该⽰例测试了写⼊100000条数据的效率，先向Redis写⼊1000
00条数据，再将数据读出，写⼊Mysql。 批量写⼊缓解了服务器的压⼒。
stdafx.h
// stdafx.h : 标准系统包含⽂件的包含⽂件，
// 或是经常使⽤但不常更改的
// 特定于项⽬的包含⽂件
//
#pragma once
#include "targetver.h"
#include <stdio.h>
#include <tchar.h>
#include <stdlib.h>
#include <string.h>
#include <iostream>
#include <assert.h>
#include <vector>
#include "hiredis.h"
#include <Windows.h>
#include "mysql.h"
#ifdef _DEBUG
#pragma comment(lib, "hiredis_d.lib")
#pragma comment(lib, "Win32_Interop_d.lib")
#else
#pragma comment(lib, "hiredis.lib")
#pragma comment(lib, "Win32_Interop.lib")
#endif
#pragma comment(lib, "AdvAPI32.Lib")
#pragma comment(lib, "DbgHelp.Lib")
#pragma comment (lib, "Shlwapi.lib")
#pragma comment(lib,"libmysql.lib")
using namespace std;
typedef struct testData
{
int iHeight;
int iWidth;
char szValue[64];
char szHValue[64];
}stTestData, *pstTestData;
test.h
#include "stdafx.h"
#include "DBHandle.h"
int main()
{
DBHandle *dbHandle = new DBHandle();
thread tWriteDataToRedis(&DBHandle::writeHsetToRedis, *dbHandle);
tWriteDataToRedis.join();
return0;
}
DBHandle.h
#pragma once
#include <mutex>
#include <thread>
class DBHandle
{
public:
DBHandle();
~
DBHandle();
bool connectRedis(string strIp, int iPort, string strPwd);
void freeRedis();
int getRedisDBSize();
bool writeHsetToRedis();
bool readDataFromRedis();
bool connectMysql();
void FreeMysqlConnect();
bool insertDataToMysql(string strData);
redisContext* m_pRedisContext;
MYSQL m_mysql;
MYSQL_RES *res; //⾏的⼀个查询结果集
};
DBHandle.cpp
#include "stdafx.h"
#include "DBHandle.h"
DBHandle::DBHandle()
{
m_pRedisContext = NULL;
}
DBHandle::~DBHandle()
{
if (m_pRedisContext != NULL)
{
m_pRedisContext = NULL;
}
}
bool DBHandle::connectRedis(string strIp, int iPort, string strPwd)
{
//redis默认监听端⼝为6387 可以再配置⽂件中修改
char szBuf[32] = {};mysql怎么读英语
strcpy_s(szBuf, sizeof(strIp), strIp.c_str());
m_pRedisContext = redisConnect(szBuf, iPort);
if (NULL == m_pRedisContext || m_pRedisContext->err)
什么是filter{
return false;
}
//输⼊Redis密码
strcpy_s(szBuf, sizeof(strPwd), strPwd.c_str());
redisReply *pRedisReply = (redisReply*)redisCommand(m_pRedisContext, "AUTH %s", szBuf); if (NULL != pRedisReply)
{
freeReplyObject(pRedisReply);
}
if (NULL == pRedisReply->str)
{
return false;
}
return true;
}
void DBHandle::freeRedis()
{
redisFree(m_pRedisContext);
if (m_pRedisContext != NULL)
{
m_pRedisContext = NULL;
}
}
int DBHandle::getRedisDBSize()
{
//查看list长度
int iListLen = 0;
//redisReply *pRedisReply = (redisReply *)redisCommand(m_pRedisContext, "LLen datalist"); redisReply *pRedisReply = (redisReply *)redisCommand(m_pRedisContext, "DBSIZE");
if (NULL != pRedisReply)
{
if (NULL == pRedisReply->integer)
{
return false;
}
iListLen = pRedisReply->integer;
freeReplyObject(pRedisReply);
}
if (NULL == pRedisReply)
{
printf("%s \r\n", m_pRedisContext->errstr);
return false;
}
return iListLen;
}
bool DBHandle::writeHsetToRedis()
{
bool bFlag = connectRedis("127.0.0.1", 6379, "123456");
if (false == bFlag)
{
return false;
}
time_t st = time(NULL);//秒
stTestData data = {};
int i = 1;
while (i<100000)
{
data.iHeight = i;
data.iWidth = 30;
char szBuf[64] = {};
sprintf_s(szBuf, "width%d", i);
strcpy_s(data.szValue, 64, szBuf);
sprintf_s(data.szHValue, "%s%d", "heighttest", i);
//向Redis写⼊数据hset location (interger)1 "width"
sprintf_s(szBuf, "hset location%d value %s", i, data.szValue);
redisReply *pRedisReply = (redisReply *)redisCommand(m_pRedisContext, szBuf);
if (NULL != pRedisReply)
{
freeReplyObject(pRedisReply);
}
i++;
}
printf("write finish");
readDataFromRedis();
time_t et = time(NULL);
int iUsed = st - et;
printf("used time is %d", iUsed);
怎么织梦
freeRedis();
return true;
}
bool DBHandle::readDataFromRedis()
{
/*bool bFlag = connectRedis("127.0.0.1", 6379, "123456");
if (false == bFlag)
{
return false;
}*/
printf("read start");
int iSize = getListSize();
if (iSize <= 0)
{
return false;
mongodb真的不需要分表吗}
bool bSuc = connectMysql();
if (bSuc == false)
{
return false;
}
int iCount = iSize;//计数
python使用pip安装numpy库
while (iCount > 0)
{
//⽤get命令获取数据
redisReply *pRedisReply = (redisReply*)redisCommand(m_pRedisContext, "RPOP datalist");
if (NULL == pRedisReply)
{
return false;
}
if (NULL != pRedisReply->str)
{
string str = pRedisReply->str;
insertDataToMysql(str);
freeReplyObject(pRedisReply);
}
iCount--;
}
static全局变量和局部变量printf("read finish");
return true;
}
bool DBHandle::connectMysql()
{
mysql_init(&m_mysql);
/
/ Connects to a MySQL server
const char host[] = "192.168.4.8";
const char user[] = "root";
const char passwd[] = "123456";
const char db[] = "topproductline";
unsigned int port = 3306;
const char *unix_socket = NULL;
unsigned long client_flag = 0;
/*A MYSQL* connection handler if the connection was successful,
NULL if the connection was unsuccessful. For a successful connection,
the return value is the same as the value of the first parameter.*/
if (mysql_real_connect(&m_mysql, host, user, passwd, db, port, unix_socket, client_flag)) { printf("The connection was successful.\n");
return true;
}
else {
printf("Error connecting to database:%s\n", mysql_error(&m_mysql));
return false;
}
}
void DBHandle::FreeMysqlConnect()
{
mysql_free_result(res);
mysql_close(&m_mysql);
}
bool DBHandle::insertDataToMysql(string strData)
{
char szQuery[256] = {0};
sprintf_s(szQuery, "insert into a_test (type) values ('%s');", strData.c_str());
if (mysql_query(&m_mysql, szQuery)) {
printf("Query failed (%s)\n", mysql_error(&m_mysql));
return false;
}
else {
printf("Insert success\n");
return true;
}
}
测试结果：
2.写redis->写mysql，读Redis->未到->读Mysql
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论