Any open business talk about big Data volume SQL optimization is nonsense

Wednesday go to an online travel company for an interview. Was asked about an optimization problem with a large amount of data. The problem is: A primary foreign key association table, the main table has 1 million data, the Foreign Key Association table has 10 million of the data, requires a connection.

I have been exposed to the largest single-table data is nearly 200 million lines of historical data (a carrier one year of business data) to do the query, all query related columns must be indexed, but also to ensure that no full table scan situation. I've never tried to put so much data out in memory. Had to answer that "how to do optimization estimates are not, this data volume is too large, performance certainly eat not sell." I can only tell as far as possible to add filter conditions, do not use so much data at once to do the connection, can be done in batches in batches do it ".

The interviewer told me, for example, our ticket business, we only put the north of the popular cities in the cache, real-time refresh. Every time you go to query the database, you don't have to connect all the data to the memory at once.

I can only hehe, no business let me to optimize a SQL, this is not nonsense.

On this big data volume optimization problem, let me understand the most profound is the sub-table approach. Because our company has a business need to upload data in real time, small millions of data per day, but also to do queries. So the table to do, generate a daily table, and then the previous day to add the table index, query can be based on the date to get the table name. Query the day's data as little as possible, because no indexes are slow. If you add an index, because the data is inserted in real time, the index maintenance cost is relatively large, so select the next day to add the previous table's index.

Any open business talk about big Data volume SQL optimization is nonsense