MySQL storage and query billion-tier data

The first stage: 1, be sure to correctly design index 2, it is important to avoid SQL statement full table scan, so SQL must go index (such as: all > <!! And so on will result in a full table scan) 3, you must avoid the limit 10000000,20 such a query 4, Be sure to avoid the left JOIN and other queries, do not leave such logical processing to the database 5, each table index do not build too much, big data will increase the write pressure of the database The second stage: 1, the use of sub-table technology (large table sub-table) a) Vertical sub-table: Separate part of the field, design the composition table, Based on primary Key Association B) Horizontal table: Splits the records in the same field table by a hash algorithm for multiple sub-table 2, The use of MySQL partitioning technology (must be 5.1 or more, this technology is completely anti-Oracle), and horizontal sub-table a bit similar, but it is in the logical layer of the level of the third stage (server side): 1, the use of memcached and other memory object cache system, reduce the database read operations 2 , using master-slave database design, separating the reading and writing pressure of the database 3, using the proxy server such as squid and Web cache server Technology PS: Because of the space problem, I simply say some basic concepts, in fact, each of the knowledge points related to a lot of content. Especially in the first stage, many programmers who have worked for several years are not fully understood. I think to really understand the index, the best way is to 1000w-more than billions of data, the test SQL statement, and then combined with the explain command to view the SQL statement index situation.

MySQL storage and query billion-tier data