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

This article introduces the knowledge of "how to improve the efficiency of the index by mysql". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Description

1. Establish a table index with high query frequency and large amount of data.

2. For the selection of index fields, the best candidate column should be extracted from the conditions of the where clause.

If there are more combinations of where clauses, the combination with the most common use and the best filtering effect should be chosen.

3. Use a unique index.

The higher the degree of differentiation, the more efficient it is to use the index.

4. The number of indexes is not as large as possible.

The more indexes you have, the higher the cost of maintaining them. For watches with frequent DML operations such as insert, update and delete, if there are too many indexes, it will introduce high maintenance costs, reduce the efficiency of DML operations, and increase the time consumption of corresponding operations. In addition, if there are too many indexes, MySQL will also suffer from selection difficulties, although it will eventually find available indexes, but it will undoubtedly increase the cost of selection.

5. Use short index.

After the index is created, it is also stored in the hard disk, which can improve the efficiency of index access and the overall access efficiency. If the total length of the fields that make up the index is relatively short, more index values can be stored in the storage block of a given size, thus effectively improving the efficiency of MySQL accessing the index.

6. Use a combined index composed of leftmost prefix and N column.

This is equivalent to creating N indexes. If the first few fields that make up the index are used in the where clause when querying, the query SQL can use a combined index to improve query efficiency.

Example

Create a composite index: CREATE INDEX idx_name_email_status ON tb_seller (NAME,email,STATUS); it is equivalent to creating an index for name; creating an index for name, email; creating an index for name, email, and status; and "how to improve the efficiency of the index by mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.