When the query becomes very slow and indexing cannot improve the query speed. So, the most common MySQL optimization solution, do you know? Six programs for SQL query statement optimization:
1. Use indexes
2. Use explain to select better indexes and optimize query statements
3. Do not use SELECT * FROM statements anywhere.
4. Do not perform operations or use functions on indexed columns
5. If possible, use limit to reduce the number of rows returned
6, use the query cache, and allocate as much memory as possible to MYSQL cache
Mysql, which has its own master-slave function, can realize automatic synchronization between master and slave database data, based on binary log replication. Write operations performed on the primary database create a binary log, which is asynchronously synchronized to the secondary database. The secondary database automatically executes the binary log, and the data is consistent with the primary database.
Database sub – table, can immediately improve the performance of the system!
Compared with the preceding two types of routing policies, a common routing policy is as follows:
Intermediate variable = user_id% (number of libraries * number of tables per library); 2, library number = round (intermediate variable/number of tables per library); 3, table number = intermediate variable % number of tables per library;
There are many kinds of strategies to choose, and the above is a relatively simple one according to the user ID. Other methods include using number segments for partitioning or using hash for routing. Advantages and disadvantages of the Hash routing policy are as follows: Data is evenly distributed. Disadvantages: data migration is troublesome, can not be allocated according to machine performance data.
After dividing the database and table, if we need to further expand the lineup of the system (change of routing policy), it will become very inconvenient and we need to carry out data migration again.
Finally, it should be pointed out that there are many middleware to choose from at present, the most common one is Cobar, the middleware of Taobao. In addition, Spring can also implement read/write separation of the database.
Note: the article has reference.