1, what is the number of queries per second?
2, the length of each query
After confirmation, consider the optimization of the following factors.
1, the type of storage, SSD can improve the random reading and writing ability of ordinary disk a lot, generally 2 to 3 orders of magnitude, depending on the size of index and data block, which is more complicated.
2. First select the RAID type. If you choose raid0 and raid 10, the speed can be improved by about 1 times.
3. Using high bandwidth network speed can reduce network transmission delay. Theoretically, using 10g optical fiber ratio 1g optical cable can improve the throughput of 1 order of magnitude, which is especially effective for result sets with large data.
4, reasonable index, conditional search field plus an index.
5. Use large and wide tables, minimize multi-table related queries, and exchange space for time.
6. _ Master-slave cluster is adopted, and the number of concurrent queries is basically proportional to the number of servers.
7, the use of cache, such as memcached, especially for the promotion of static data.
8. Choose database field types reasonably, use fixed-length words, and don't change length, such as fixed-length int, char, decimal types, and don't use varchar, text, etc.
9. Configure more memory for the database.
10, check whether the bottleneck is in the CPU. If the query is complicated, please switch to a server with higher configuration.
The general principle is to use memory instead of touch panel as much as possible to improve IO speed and improve the configuration of network and CPU to reduce query time. As far as possible, increase the network speed, memory and the number of hosts to improve concurrency.
Let's discuss the implementation of non-high concurrency first.
Add an index for fields with high query frequency.
Index description:
1. It is best not to index those characters with long contents.
2. According to official documents, the number of indexes added to a single table cannot exceed 16, and the index length cannot exceed 256 bytes.
Adding indexes at will increase the burden of data maintenance.
In fact, partition can be introduced.
Precautions for partitioning:
1. Common partition types are range, list, hash, key, etc. Scope division is used more.
2. For the initial index, we often ignore a prerequisite, which leads to the failure of adding and reporting errors.
The premise here is that if the table has a primary key and the partition key is different from the primary key, then the partition key must also be the primary key.
After the introduction of partitions, when writing data, the database will automatically determine which partition to write.
For high concurrency, in addition to the above operations, we should also consider dividing the database into tables or adopting the mode of one master and many slaves.
In the future, I believe that this kind of problems need to be solved by using databases, such as NewSQl, such as TiDb and so on. At this time, we don't have to consider the problem of data partition, and we can realize the infinite expansion of data level and the dynamic distribution of hot data.