简体中文版

Mysql query will become slow when table contains millions of rows. A simple way of solving this probolem is spliting the big table into small ones. We use some method to decide which table each row should be moved into. A widely used method is "modulus", e.g. group rows by [ID mod 100]. This tool will help you to do this job easily.

• Move records from one table to another(or other) table(s) according to user specified rules.
• Dynamically create new tables during moving.
• Move records between different mysql servers.
• Check data integrity of the new tables.
• For records who have been moved to new tables, remove them from source table.
• Using it as a python module which means dynamically changing the behavior.

python2: pip install pymysql_split_tool

python3: pip3 install pymysql_split_tool

The following python version are supported and tested: 2.6, 2.7, 3.3, 3.4, 3.5, 3.6, 3.7

• Just create a split task file and run this tool with it:

python -m pymysql_split_tool --action split --task ./task.json

• Supported parameters:

• There are two type of action supported:

• A quick look at a sample task file:

{
  "src":{
    "mysql":{
      "host":"127.0.0.1",
      "port":"3306",
      "unix_socket":"/path/to/mysqld.sock",
      "user":"user_name",
      "password":"password"
    },
    "database":"name_of_database",
    "table":"name_of_the_big_table"
  },
  "dest":{
    "table":"small_table_[n]"
  },
  "rule":{
    "filter":"id>1000",
    "group_method":"modulus",
    "group_base":100,
    "group_column":"id"
  }
}

Check the test directory for more sample task files.

import json
import pymysql_split_tool

task_file = open('path/to/task.json')
task = json.loads(task_file.read())
task_file.close()

#init a split task with a specified task object whose type is dict
pymysql_split_tool.init("split", task)
#run the task
pymysql_split_tool.do_work()

Check here for a sample with monkey patch.

For action split, there are 4 different work flows:

• A: if dest.mysql is set, rule.group_int not set, it works in the following steps:

  1. make connection to "src" and "dest" mysql server.
  2. get data with this sql: select * from src.database.src.table where rule.filter order by rule.order_by limit offset,rule.page_size
  3. for each selected data, use rule.group_method on the rule.group_column, get a method result: n
  4. ensure table dest.table_[n] existance if dest.create_table_first exists.
  5. insert data into new table

• B: if dest.mysql is set, rule.group_int is set, it works like this:

  1. make connection to "src" and "dest" mysql server.
  2. generate an integer array according to rule.group_int.
  3. for each integer n, ensure table dest.table_[n] existance if dest.create_table_first exists.
  4. for each integer n, get data with this sql: select * from src.database.src.table where rule.filter and rule.group_column method = n order by rule.order_by limit offset,rule.page_size
  5. insert data into new table

• C: if dest.mysql is not set, rule.group_int not set, it works like this:

  1. make connection to "src" mysql server
  2. get group list with this sql: select n from src.database.src.table group by rule.group_column method result
  3. for each group n, copy data with this sql: replace into dest.database.dest.table select * from src.database.src.table where rule.filter where rule.group_column method result = n

• D: if dest.mysql is not set, rule.group_int is set, it works like this:

  1. make connection to "src" mysql server
  2. copy data with this sql: replace into dest.database.dest.table select * from src.database.src.table where rule.filter

For action check, here is the work flow:

1. make connection to mysql server.
2. if rule.group_int is not set, use this sql to get a group integer list: select n from src.database.src.table group by rule.group_column method result
3. for each integer in group list, select count() and sum() in both src and dest tables. do the comparation.

For action remove, here is the work flow:

1. make connection to mysql server.
2. if rule.group_int is not set, use this sql to get a group integer list.
3. for each integer in group list, delete records in src table.