Shulou(Shulou.com)05/31 Report--

This article mainly explains "what knowledge points need to be mastered in MySQL database development", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "MySQL database development needs to master what knowledge points" it!

Selection of storage engine

Disclaimer: the database version for this article is MYSQL 5. Here, I mainly compare the two storage engines: MyISAM and InnoDB. First of all, compare the differences:

1. MyISAM does not support transactions and foreign keys. It has the advantages of high access speed and fast batch insertion speed. Assuming that a large number of operations are select and insert, it is recommended to use this storage engine. But in my practical application, when the batch insertion is too frequent, when the amount of data reaches a certain level, the table is damaged.

2. InnoDB supports transaction processing, but it is less efficient than the former, and its indexes and data take up more disk space. When we store some critical data and need to transact on it, we can choose innodb, of course, I don't think it should be visited too much.

Design and use of index

A table without an index is scary unless there is not much data in it, but how to design an index is reasonable? I'm afraid not everyone will understand. Here is a brief analysis of the design and use of the index.

What do MySQL database developers need to know

1. The index usually sets the column in the where clause, which doesn't make any sense if you set the column after select. Of course, you need to sort a column, and the columns after order by can also be indexed.

two。 Using a unique index, the primary key is the best example, assuming that a large number of index columns you build are duplicated, such as gender, then such an index 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, for example, the index column char (100), most of the first 10 characters are unique, please set the length of the index to 10, using a short index can speed up the query and save hard disk space.

4. Because of the left prefix of the index, the federated index essentially establishes multiple indexes, so is it better to build a federated index or multiple indexes separately? Obviously the former is better, using the left prefix feature, as long as the leftmost column of the federated index is used, then 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 speed, especially when the amount of data is large, at the same time, a large number of indexes will consume a lot of hard disk space, resulting in unnecessary waste.

At this point, I believe that everyone on the "MySQL database development needs to master what knowledge points" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.