MySQL 20 million data optimization & migration

A data table with a 2000W record recently needs to be optimized and migrated. 2000W data is awkward for MySQL, because it is very fast to create indexes reasonably, and how to optimize speed is not much improved. However, these data have a large number of redundant fields and error messages, it is very inconvenient to do statistics and analysis. So I need to create a new table, the data in the old table is taken out to optimize and put back the new table;

I. Clear redundant data, optimize the field structure

In 2000W data, we anticipate the fields that can be used as query criteria. So this part of the data to create a separate new field, for the rule of the data reasonable change the field structure, such as ID is varchar (18). For unimportant data we have a field with text structure after merging.

For some related data we need to calculate, common such as identity card can get accurate gender, birthplace, birthday, age.

Two. Data migration

We take an old piece of data out of the database, get the new data we want (Tengyun technology ty300.com), and finally insert the new data into the new table. However, the following issues were encountered when acquiring new data.

1. The amount of data is too large to get at once (2000W data thrown into memory pretty scary);

We can get it in batches via the MySQL limit syntax. For example, each time you get the 50000,sql statement:

SELECT * FROM table_name limit 15000000,50000;

This method can solve the problem of too much data, but as the first parameter of limit becomes bigger and larger, the query speed will be very slow (this SQL execution will take 35 seconds). Time is life, so we start to optimize the SQL statement, and then the optimization becomes the following:

Manuscripts: Diligent Learning qkxue.net

Full version of MySQL 20 million data Optimization & Migration Introduction

MySQL 20 million data optimization & migration