Detailed explanation of how to implement secondary cache with MySQL and Redis

Redis Introduction

Redis is completely open source and free, complies with the BSD protocol, and is a high-performance key-value database.

Redis and other key-value cache products have the following three characteristics:

Redis supports data persistence. It can save the data in memory to disk and load it again when restarting.
Redis not only supports simple key-value data, but also provides storage for list, set, zset, hash and other data structures.
Redis supports data backup, that is, data backup in master-slave mode

Advantages

High performance - Redis can read 110,000 times/s and write 81,000 times/s
Rich data types – Redis supports Strings, Lists, Hashes, Sets and Ordered Sets data type operations in binary cases
Atomic – All Redis operations are atomic, meaning they either succeed or fail and are not executed at all. Individual operations are atomic. Multiple operations also support transactions, i.e. atomicity, by wrapping them in MULTI and EXEC instructions

Download and Installation

Download and unzip

wget http://download.redis.io/releases/redis-5.0.3.tar.gz
tar xzf redis-5.0.3.tar.gz

Move the folder to /usr/local/

mv redis-5.0.3 /usr/local/

Go into the folder and compile the test

cd /usr/local/redis-5.0.3
sudo make test

Compile and install

sudo make install

Start redis

redis-server

MySQL and redis as secondary cache

For data with a large number of visits, we need to cache the data obtained from the database in order to obtain the data faster.

Using Redis cache process in the project

When querying, query from the cache first
If there is no data in the cache, query it from the database and save the data in the cache
If the data is found in the cache, it is returned directly without querying the database.

Data caching should consider synchronization issues: if data is cached, when querying data, if there is data in the cache, the cached data will be returned directly without querying the database. When the database data changes, database inconsistency may occur. You can consider deleting the corresponding cached data every time you modify the database, so that when you query again, you will query the database and cache it.

Steps to implement

Create the redisPool.go file for initializing the connection pool

package redigo_pool

import (
 "flag"
 "github.com/garyburd/redigo/redis"
 "time"
)
var (
 Pool *redis.Pool
 RedisServer = flag.String("redisServer", ":6379", "")
 
)
func init() {
 Pool = &redis.Pool{
  MaxIdle: 3, //Maximum number of idle links, indicating that even if there is no redis connection, N idle links can still be maintained without being cleared MaxActive: 3, //Maximum number of active connections, indicating the maximum number of links at the same time IdleTimeout: 240 * time.Second, //Maximum idle link waiting time, exceeding this time, the idle will be closed Dial: func() (redis.Conn, error) {
   c, err := redis.Dial("tcp", *RedisServer)
   if err != nil {
    return nil, err
   }
   return c, err
  },
  TestOnBorrow: func(c redis.Conn, t time.Time) error {
   if time.Since(t) < time.Minute {
    return nil
   }
   _, err := c.Do("PING")
   return err
  },
 }
}

Create main.go file to implement secondary cache

package main

import (
 "database/sql"
 "encoding/json"
 "fmt"
 "github.com/garyburd/redigo/redis"
 _ "github.com/go-sql-driver/mysql"
 "strconv"
 "web/redis/redigo_pool"
 _ "web/redis/redigo_pool"
)

type Person struct {
 Id int `db:"id"`
 Name string `db:"name"`
 Age int `db:"age"`
 Rmb int `db:"rmb"`
}

func main() {
 var cmd string
 for{
  fmt.Println("Enter command")
  fmt.Scan(&cmd)
  switch cmd {
  case "getall":
   getAll()
  default:
   fmt.Println("Cannot recognize other commands")
  }
  fmt.Println()
 }
}

func getAll() {
 //Get the connection from the connection pool conn := redigo_pool.Pool.Get()
 //First check whether there is data in redis//conn,_ :=redis.Dial("tcp","localhost:6379")
 defer conn.Close()
 values, _ := redis.Values(conn.Do("lrange", "mlist",0,-1))

 if len(values) > 0 {
  //If there is data fmt.Println("Get data from redis")
  //Get directly from redis for _, key := range values{
   pid := string(key.([]byte))
   id ,_:= strconv.Atoi(pid)
   results,_ := redis.Bytes(conn.Do("GET",id))
   var p Person
   err := json.Unmarshal(results,&p)
   if err != nil {
    fmt.Println("json deserialization error")
   }else {
    fmt.Printf("name = %s\n",p.Name)
   }
  }
 }else {
  fmt.Println("Get from mysql")

  //Query database db,_ := sql.Open("mysql","root:Szt930708@tcp(localhost:3306)/mydb")
  defer db.Close()

  var persons []Person

  rows,_ := db.Query("select id,name,age,rmb from person")
  for rows.Next() {
   var id int
   var name string
   var age int
   var rmb int
   rows.Scan(&id,&name,&age,&rmb)
   per := Person{id,name,age,rmb}
   persons = append(persons,per)

  }
  //Write to redis: Write person to redis in hash mode for _,p := range persons{

   p_byte,_ := json.Marshal(p)
   _,err1 := conn.Do("SETNX",p.Id,p_byte)
   _,err2 := conn.Do("lpush","mlist",p.Id)
   // Set the expiration time conn.Do("EXPIRE",p.Id,60*5)
   if err1 != nil || err2 != nil {
    fmt.Println("Write failed")
   }else {
    fmt.Println("Written successfully")
   }
  }
  conn.Do("EXPIRE","mlist",60*5)
 }
}

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:

MySQL Query Cache and Buffer Pool
Research on Buffer Pool Pollution Caused by mysqldump
Implementation of lnmp architecture cache in mysql under redis server environment
Detailed explanation of the use of MySQL select cache mechanism
Detailed explanation of query and clear commands for MySQL cache
How to use redis to calculate the pressure of MySQL cache in PHP
MySQL cache startup method and parameter details (query_cache_size)
Possible methods to clear MySQL query cache
A brief analysis of the principle and caching process of using Memcache to cache MySQL database operations
Detailed explanation of the buffer pool in MySQL

<<: Steps to build the vite+vue3+element-plus project

>>: Nginx high concurrency optimization practice