Due tothe current only through the MySQL fordatastorage, to complete thecomplexdata is time-consuming.Thereforeconsider part of the data into redis,completesthe data statistics, and then the result in MySQL.
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
