Investigation data, data exchange process using pipeline can bypass the third party, the data directly from the MySQL into the redis very quickly. In the testing environment of the author, 500W level data in about 40 seconds (of course, with the MySQL query time related).
Inaddition, due to the problem ofdesign ofMySQL table, most of the data stored in a different table structure in the same. For example, table A stored in the user startsthe application” ” this event data, table B stored in the user ” open the settings page ” event data, but the tables A and B structure are the same. At present, through the MYSQL to do data statistics, is operating in orderto finishA, statistics, and statistics B.
In redis, table A and table B data and to put in the same database, and then use the containers such as set stores the data classification of each event, traverse once can completeall eventdata statistics.
Therefore, the application scene now: batch export MySQL for each table in the same condition data (as a day of data), import redis, finally merge complete statistics. Import and export of specific code is as follows(mysql2redis.sh):
Examples of calls: /bin/bash mysql2redis.sh
#!/bin/bashmysql_host=192.168.x.xxmysql_user=xiaomomysql_pwd=xiaomo database=test_dbtbls_prefix="test_tbl_name_prefix"#When you call shell, incoming date parameterscur_dt="$1"#Traversal list, returns a table named listtable_list=$(mysql -h$mysql_host -u$mysql_user -p$mysql_pwd $database -A -Bse "show tables") function gen_sql(){src_tbl=$1mysql2redis="SELECT CONCAT(/'*10/r/n',/'$', LENGTH(redis_cmd), '/r/n',redis_cmd, '/r/n',/'$', LENGTH(redis_key), '/r/n',redis_key, '/r/n',/'$', LENGTH(hkey1), '/r/n', hkey1, '/r/n',/'$', LENGTH(hval1), '/r/n', hval1, '/r/n',/'$', LENGTH(hkey2), '/r/n', hkey2, '/r/n',/'$', LENGTH(hval2), '/r/n', hval2, '/r/n',/'$', LENGTH(hkey3), '/r/n', hkey3, '/r/n',/'$', LENGTH(hval3), '/r/n', hval3, '/r/n',/'$', LENGTH(hkey4), '/r/n', hkey4, '/r/n',/'$', LENGTH(hval4), '/r/n', hval4, '/r'/)/FROM (/SELECT/'HMSET' AS redis_cmd, uniq_id AS redis_key,/'f1' AS hkey1, f1 AS hval1,/'f2' AS hkey2, f2 AS hval2,/'f3' AS hkey3, f3 AS hval3,/'f4' AS hkey4, f4 AS hval4/FROM $src_tbl WHERE dt='$cur_dt'/) AS T"echo "$mysql2redis"}prefix_len=$(expr length $tbls_prefix)for arg in $table_listdoif [[ "${arg:0:$prefix_len}" == ${tbls_prefix} ]] # Table name is matching (table name beginning with the specified prefix)thenmysql2redisCmd=$(gen_sql $arg)echo $mysql2redisCmd | mysql -u$mysql_user -p$mysql_pwd -h$mysql_host $database --skip-column-names --raw | redis-cli -n 1 --pipefidone声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright © 2019-2025 51dongshi.com 版权所有
违法及侵权请联系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市万商天勤律师事务所王兴未律师提供法律服务
