把数据库里的大表放到REDIS中（从Mysql将数据快速导入到redis中）

╰半夏微凉° 2024-02-18 15:04 58阅读 0赞

在把一个大表从 MySQL 迁移到 Redis 时，你可能会发现，每次提取、转换、导入一条数据是让人难以忍受的慢!这里有一个技巧，你可以通过使用管道把 MySQL 的输出直接输入到 redis-cli输入端，这可以使两个数据库都能以他们的最顶级速度来运行。  
t\_loan\_risk\_program (658.1461G), 9 9 9 9204295 条 MySQL 数据导入到 Redis 的时间从 12 小时缩短到了30分钟。  
  
MySQL数据表结构：  
CREATE TABLE \`t\_loan\_risk\_program\` (  
\`id\` bigint(20) NOT NULL AUTO\_INCREMENT,  
\`call\_histories\` longtext,  
\`contacts\` longtext,  
\`create\_time\` datetime DEFAULT NULL,  
\`current\_version\` varchar(32) CHARACTER SET utf8 DEFAULT NULL,  
\`customer\_id\` bigint(20) DEFAULT NULL,  
\`device\` varchar(10) CHARACTER SET utf8 DEFAULT NULL,  
\`device\_info\` longtext CHARACTER SET utf8,  
\`gps\` text CHARACTER SET utf8,  
\`id\_card\_correct\` bit(1) DEFAULT NULL,  
\`imei\` varchar(64) CHARACTER SET utf8 DEFAULT NULL,  
\`in\_black\_list\` bit(1) DEFAULT NULL,  
\`jailbreak\_status\` bit(1) NOT NULL,  
\`like\_screenshot\_score\` double DEFAULT NULL,  
\`live\_score\` double DEFAULT NULL,  
\`loan\_id\` bigint(20) DEFAULT NULL,  
\`messages\` longtext,  
\`packages\` longtext,  
\`simulator\_status\` bit(1) DEFAULT NULL,  
\`zhima\_black\_list\` bit(1) DEFAULT NULL,  
\`zhima\_score\` varchar(15) CHARACTER SET utf8 DEFAULT NULL,  
PRIMARY KEY (\`id\`),  
KEY \`cusIdAndloanId\` (\`customer\_id\`,\`loan\_id\`) USING BTREE,  
KEY \`cusId\` (\`customer\_id\`) USING BTREE,  
KEY \`loanId\` (\`loan\_id\`) USING BTREE  
) ENGINE=InnoDB AUTO\_INCREMENT=8289585 DEFAULT CHARSET=utf8mb4 ；  
  
Redis存储结构：  
HSET events\_all\_time \[action\] \[count\]  
下面是重点，能过下面SQL语句将MySQL输出直接变更成redis-cli可接收的格式：  
  
root@storm-master-01:/home\#more /home/t\_loan\_rask\_programredis.sql  
SELECT CONCAT(  
"\*4\\r\\n",  
'$', LENGTH(redis\_cmd), '\\r\\n',  
redis\_cmd, '\\r\\n',  
'$', LENGTH(redis\_key), '\\r\\n',  
redis\_key, '\\r\\n',  
'$', LENGTH(hkey), '\\r\\n',  
hkey, '\\r\\n',  
'$', LENGTH(hval), '\\r\\n',  
hval, '\\r'  
)  
FROM (  
SELECT  
'HSET' as redis\_cmd,  
't\_loan\_risk\_program ' AS redis\_key,  
id AS hkey,  
call\_histories AS hval,  
contacts as co,  
create\_time as crtim,  
current\_version as cuver,  
customer\_id as cuid,  
device as dev,  
device\_info as devinfo,  
gps as gps,  
id\_card\_correct as idcc,  
imei as im,  
in\_black\_list as inbli,  
jailbreak\_status as ja,  
like\_screenshot\_score as lik,  
live\_score as li,  
loan\_id as loid,  
messages as mess,  
packages as pak,  
simulator\_status as sim,  
zhima\_black\_list as zh,  
zhima\_score as zhsc  
FROM t\_loan\_risk\_program  
) AS ttt  
  
ok, 用下面的命令执行：  
mysql -h192.168.1.247 -uloan -ploan loan -P3309 --skip-column-names --raw < /home/t\_loan\_rask\_programredis.sql | redis-cli -h 192.168.1.247 --pipe  
　很重要的mysql参数说明：  
　　--raw: 使mysql不转换字段值中的换行符。  
　　--skip-column-names: 使mysql输出的每行中不包含列名。