Detailed MySQL three practical development knowledge

In fact, the bottleneck of project application is at DB end, in the case of only a small amount of data and very few concurrent, it does not need much skill to get the results we want, but when the amount of data reaches a certain level, every detail of the program, the design of the database will affect the performance of the system. Here on the topic of database development and optimization to do a discussion and analysis, but also please improve, here on the following topics, I first express their views.

1. Storage Engine Selection

2. The design and use of the index

3. Optimization of SQL statements in mass insert

Storage Engine Selection

Disclaimer: This article is for the database version is MySQL 5 here I mainly for two storage engines for simple comparison is MyISAM and InnoDB, first of all compare the difference:

1. MyISAM does not support transactions, foreign keys are not supported, the advantage is high access speed, bulk insertion speed. It is recommended that the storage engine be used if a large number of operations are select and insert. However, in my practical application, there have been too much time for bulk inserts, when the amount of data reached a certain level, the situation of table damage.

2. InnoDB supports transaction processing, but it is less efficient compared with the former, and its indexes and data also occupy disk space. When we store some critical data and need to do business with it, we can choose InnoDB, and of course, I don't think he should be too much traffic.

The design and use of indexes

A table without an index is scary, unless there is not much data in it, but how to design the index is reasonable? I'm afraid not everyone knows it. This paper briefly analyzes the design and use of the index below.

1. Indexing is usually a column in the WHERE clause, which is meaningless if you set the column after the select. Of course, you need to sort a column, and the column in order can also be indexed.

2. Using a unique index, a primary key is the best example, assuming that the number of indexed columns you build is repetitive, such as gender, so indexing does not speed up the search. As for why, please understand how the index works.

3. Whenever possible, try to limit the length of the index, such as the index column char (100), where most of the first 10 characters are unique, set the index to a length of 10, and use a short index to speed up the query and save hard disk space.

4. The left prefix feature of the index, and the federated Index is essentially a number of indexes, is it better to establish a federated index or to build multiple indexes separately? Obviously the former is better, with the left prefix feature, so long as the leftmost column of the federated Index is used, the index will be used.

5. Of course, the last thing to say is, do not overuse the index, the more indexes, the slower the insertion, especially to the large amount of data, at the same time, a large number of indexes will consume a lot of hard disk space, resulting in unnecessary waste.