Database field matching

Now there are two table (mysql) Table 1 fields: province, city, mobile phone number, name, and other fields Table 2 fields: province, city, mobile phone number, the databases of other field Tables 1 and 2 are large, and millions of rows of data are added daily. Table 1 and Table 2 are now partitioned. Requirement: according to the two tables, there are two tables (mysql)
Table 1 has the following fields: province, city, mobile phone number, name, and other fields.
Table 2 fields are as follows: province, city, mobile phone number, and other fields

The databases in Tables 1 and 2 are large, and millions of rows of data are added daily.
Table 1 and Table 2 are now partitioned.
The requirement is: match the data in the two tables based on the mobile phone number.
The problem is:
1. how to store matched data is stored in the third table, or add a field to table 1 to keep the id of the row matching table 2.
2. how to query unmatched records.

Reply content:

There are now two tables (mysql)
Table 1 has the following fields: province, city, mobile phone number, name, and other fields.
Table 2 fields are as follows: province, city, mobile phone number, and other fields

The databases in Tables 1 and 2 are large, and millions of rows of data are added daily.
Table 1 and Table 2 are now partitioned.
The requirement is: match the data in the two tables based on the mobile phone number.
The problem is:
1. how to store matched data is stored in the third table, or add a field to table 1 to keep the id of the row matching table 2.
2. how to query unmatched records.

create table zzz(id int not null,xx ...,primary key(id)) as (select id, xx from table where ..)

Http://dev.mysql.com/doc/refm...

For unmatched records, use a table as the left table to associate with another table and check whether the right table has null.
For example

select * from tb1left join tb2 on tb1.mobi=tb2.mobiwhere tb2.mobi is null

The result is that all records in tb1 are not found in tb2.

However, in this case, if a tb1 record does not exist in tb2, the unmatched tb1 record cannot be found.
Check whether all the two tables match.full joinBut from the perspective of your data volume, we recommend that you do not use full join, even if it is used, do not check all.

See this figure for join usage.

Table optimization
1. index the mobile phone number field

2. table Sharding is recommended based on the first few digits of the mobile phone number.

About Storage

We recommend that you add fields to Table 1 and Table 2 to store rows in the other table. During the next update process, you can skip partial processing with field values.

The field is null.

In addition

We recommend that you add updates directly to the record and check whether the updates exist in table 2 when adding data to Table 1.

Yes
Select * from t1, t2 where t1.mobile = t2.mobile
After query, you can create a new one.