Shulou(Shulou.com)06/01 Report--

What are the indexing skills of mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

1. Select the 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.

2. Index fields (columns) that often require sorting, grouping, and federation operations

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 (columns) that often need to be queried

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. Use indexes with less data as much as possible

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. Try to select a highly differentiated column as the index.

The formula for distinguishing degree is count (distinct col) / count (*), which indicates the proportion of non-repetitive fields. The larger the proportion, the less the number of records we scan. The discrimination degree of the only key is 1, while some status and gender fields may have a discrimination degree of 0 in front of big data. Some people may ask, is there any empirical value for this ratio? With different scenarios, this value is also difficult to determine. Generally, we need more than 0.1 for the fields that need join, that is, an average of 10 records are scanned.

8. Index columns cannot participate in the calculation, keeping the columns "clean".

For example, the index cannot be used when from_unixtime (create_time) = '2014-05-29'. The reason is simple: all the field values in the b + tree are stored in the data table, but when searching, all elements need to be compared by applying functions, which is obviously too expensive. So the statement should be written as create_time = unix_timestamp ('2014-05-29')

9. Expand the index as much as possible, do not create a new index.

For example, if you already have an index of an in the table, and now you want to add the index of (aforme b), you only need to modify the original index.

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.

Thank you for reading! After reading the above, do you have a general understanding of the indexing skills of mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.