Massive data MySQL optimization steps

The first optimization of your SQL and index;

Second plus cache, Memcached,redis;

After the third has done, or slow, the decision from the copy or master copy, read and write separation, can be done in the application layer, high efficiency, can also use three-party tools, third-party tools recommended 360 Atlas, the other is either inefficient, or no one to maintain;

If the above are done or slow, do not want to do slicing, MySQL comes with partition table, first try this, for your application is transparent, no need to change the code, but the SQL statement needs to be optimized for the partition table, SQL conditions to take the partition condition of the column, so that the query to locate a small number of partitions, Otherwise, all the partitions will be scanned, and there will be some pits in the partition table, so there is not much to say;

If the above are done, then do the vertical split, in fact, according to the coupling degree of your module, a large system is divided into a number of small systems, that is, distributed systems;

The second is horizontal segmentation, for the data volume of the table, this step is the most troublesome, the most able to test the technical level, to choose a reasonable sharding key, in order to have a good query efficiency, table structure to change, do a certain amount of redundancy, the application should be changed, SQL as far as possible with sharding key, Locating the data in a restricted table, rather than scanning all the tables;

MySQL database is generally in accordance with this step to evolve, the cost is from low to high;

Someone might say that the first step is to optimize SQL and indexes. Indeed, we all know, but in many cases, this step is not in place, and even some have only done based on SQL to build the index, there is no SQL optimization (shot?). ), in addition to the simplest additions and deletions, want to implement a query, you can write many kinds of query statements, different statements, depending on the engine you choose, the distribution of data in the table, index situation, database optimization strategy, query lock strategy and other factors, the efficiency of the final query is very different; optimization to consider from the whole, Sometimes when you optimize a statement, the other query efficiency is reduced, so to take a balance point, even if proficient in MySQL, in addition to pure technical surface optimization, but also based on the business side to optimize the SQL statement, so as to achieve optimal results; Do you dare say that your SQL and index are already optimal?

Again, the optimization of different engines, MyISAM read the effect of good, write inefficient, this and its data storage format, index pointer and lock strategy, its data is stored sequentially (InnoDB data storage method is clustered index), The node on his index btree is a pointer to the physical location of the data, so it is fast to find, (the InnoDB index node is the primary key of the data, so it needs to be looked up two times according to the primary key); MyISAM locks are table locks, and only read and read are concurrent. Between writing and reading (between reading and writing can be concurrent, to set the Concurrent_insert parameter, the regular table optimization operation, update operation is no way) is serial, so it is slow to write, and the default write priority is higher than the read priority, high to write operation came, can be inserted immediately before the read operation, if bulk write, will cause the read request to starve, so to set read or write priority or set how many write operations after the read operation of the policy; MyISAM do not use SQL that queries too long, and if the policy is used improperly, it can also cause write starvation, So try to split the query with inefficient SQL,

InnoDB generally is a row lock, this generally refers to the SQL used to index, row locks are added to the index, not added to the data record, if SQL is not used to index, will still lock the table, MySQL read and write can be concurrent, normal Select does not need to lock, When the record of the query encounters a lock, using a consistent non-locked snapshot read, that is, according to the database isolation level policy, will read the snapshot of the locked row, the other update or lock read statement with the current read, read the original row, because the general read and write does not conflict, so InnoDB will not appear read-write starvation situation, Also because the use of the index is a row lock, the size of the lock is small, the situation of the same lock competition is less, the increase in concurrency processing, so the efficiency of concurrent reading and writing is very good, the problem is that the index after the query based on the primary key two search results in low efficiency;

PS: Very strange, why InnoDB Index leaf node is the primary key instead of the physical address pointer that saves the data like mysism? If there is a physical address pointer does not need two times to find out, this is also my doubts, according to Mysism and InnoDB data storage way of the difference to think, you will understand, I will not bother!

So innodb in order to avoid secondary lookups can use index overlay technology, unable to use the index overlay, and then extension is based on the index overrides to implement the deferred association; If you don't know what an index is, it is recommended that you find out what it is anyway!

Do your best to optimize your SQL! Said it is low cost, but also a time-consuming and laborious work, need in the technology and business are familiar with the situation, the intention to optimize to achieve optimal, the effect of optimization is immediate!

Massive data MySQL optimization steps