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

This article mainly explains "what are the design principles of MySQL database index". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what are the design principles of MySQL database indexes?"

In order to make the use of the index more efficient, when creating the index, you must consider which fields to create the index and what type of index to create. This section introduces the reader to some of the design principles of the index.

1. Select uniqueness index

The value of the uniqueness index is unique, and a record can be determined more quickly through the index. For example, the student number in the student table is a unique field. Establishing a unique index for this field can quickly determine the information of a student. If a name is used, there may be a phenomenon of the same name, which slows down the query speed.

two。 Index fields that often require sorting, grouping, and federation

Fields for ORDER BY, GROUP BY, DISTINCT, and UNION operations are often required, and sorting operations can waste a lot of time. If you index it, you can effectively avoid sorting operations.

3. Index fields that are often used as query criteria

If a field is often used as a query condition, the query speed of that field will affect the query speed of the entire table. Therefore, indexing such fields can improve the query speed of the entire table.

4. Limit the number of indexes

It is not as many indexes as possible. Each index takes up disk space, and the more indexes you have, the more disk space you need. When modifying a table, it is troublesome to reconstruct and update the index. The more indexes you have, the more time it takes to update the table.

5. Try to use indexes with a small amount of data

If the value of the index is very long, the speed of the query will be affected. For example, a full-text search of a field of type CHAR (100) certainly takes more time than a field of type CHAR (10).

6. Try to use prefixes to index

If the value of the index field is long, it is best to use the prefix of the value to index it. For example, for fields of TEXT and BLOG types, full-text retrieval can be a waste of time. If you retrieve only the first few characters of the field, you can improve the retrieval speed.

7. Delete indexes that are no longer in use or are rarely used

After the data in the table is updated massively, or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should periodically find these indexes and delete them, thereby reducing the impact of indexes on update operations.

Note: the ultimate goal of selecting an index is to make the query faster. The principles given above are the most basic principles, but we should not stick to them. Readers should continue to practice in their future study and work. Analyze and judge according to the actual situation of the application, and choose the most appropriate index method.

At this point, I believe you have a deeper understanding of "what is the design principle of MySQL database index". You might as well do it in practice. 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.