Mysql-index summary

Index is a data structure that helps MySQL obtain data efficiently.

The following is a summary of the information I compiled and my own learning.

1. Is it really necessary to use indexes?

Not every performance problem can be solved by creating an index; there are many other ways to solve performance problems

a). Each application layer Cache,

b). Tuning database parameters and buffer size,

c). Tuning database connection pool size or thread pool size,

d). Adjust Database transaction isolation level,

f). Arrange batch deletions at night to avoid unnecessary table locks,

others, etc.

2. The difference between mysql index types normal, unique, and full text

1. PRIMARY, INDEX, UNIQUE These three types are the same

normal: represents a normal index

unique: represents a unique index that does not allow duplicates. If the field information is guaranteed not to be repeated, for example, when the ID number is used as an index, it can be set to unique. , special, the primary key primary_key is non-empty and unique by default

2.FULLTEXT is a full-text index, used to retrieve text information in an article.

full text: Represents the index for full-text search. FULLTEXT works best when searching for a long article. Used for relatively short text, if it is only one or two lines, ordinary INDEX can also be used.

Summary, the type of index is determined by the content characteristics of the indexed field, and normal is usually the most common.

Reference for detailed information: http://blog.sina.com.cn/s/blog_887d00920100wgf3.html

##3. Index summary

In order to make the use of indexes more efficient, when creating an index, you must consider which fields to create the index on and what type of index to create.

This section will introduce some index design principles to readers. Good stuff!

1. Choose a unique and monotonic index

The value of a unique index is unique, and a certain record can be determined more quickly through the index. For example, the middle school ID in the student table is a unique field. Establishing a unique index for this field can quickly determine a certain student's information. If you use names, there may be the same name, which will slow down the query speed. Monotonicity refers to the increasing or decreasing number, such as ID, which will be more efficient (7.mysql-Efficiency of random primary key in clustered index.note)

　2. Create indexes for fields that often require sorting, grouping, and union operations

For fields that often require operations such as ORDER BY, GROUP BY, DISTINCT, and UNION, sorting operations will waste a lot of time. If you index it, you can effectively avoid the sort operation.

　3. Create indexes for fields that are often used as query conditions

If a field is often used as a query condition, the query speed of this 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

The number of indexes is not always better. Each index requires disk space. The more indexes, the more disk space is required. When the table is modified, it is troublesome to reconstruct and update the index. The more indexes, the more time-consuming it becomes to update the table.

　5. Try to use an index with a small amount of data

If the index value is very long, the query speed will be affected. For example, a full-text search for a CHAR(100) type field will definitely take more time than a CHAR(10) type field. It’s unavoidable, see 6.

for the solution. 6. Try to use prefixes to index

If the value of the index field is very long, it is best to use the prefix of the value to index. For example, full-text search for TEXT and BLOG type fields will be a waste of time. If only the first few characters of the field are retrieved, the retrieval speed can be improved.

Good online article reference: (MySQL prefix index and index selectivity) http://www.cnblogs.com/gomysql/p/3628926.html

7. Pay attention to the joint index The order, follow the left matching property

It doesn’t matter the order of where conditions in select. The SQL kernel will sort and optimize, but it does matter when establishing a joint index. Follow the left prefix principle. 5.4 mysql-index data structure.note Final conclusion

　8． Delete indexes that are no longer used or rarely used

After the data in the table is updated a lot, or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should regularly find these indexes and delete them to reduce the impact of indexes on update operations.

9. More indexes are not always better. A maximum of 6

indexes are fine. It improves the efficiency of the corresponding select, but also reduces the efficiency of insert and update, because the index may be rebuilt when inserting or updating,

so how to build the index needs to be carefully considered, depending on the specific situation. It is best not to have more than 6 indexes on a table. If there are too many, you should consider whether it is necessary to build indexes on some columns that are not commonly used.

10.emun or fields with only single-digit level, there is no need to add an index

In the extreme case, with 900,000 data, the source has only two values: 0 and 1. To use the index, you must first read the index file, then perform a binary search to find the data disk pointer of the corresponding data, and then read again based on the read pointer. The corresponding data on the disk affects 450,000 result sets. In this case, it is faster than direct full table scan.

11. Index is effective for group by

The essence is also a sorting process, and the index helps it achieve 6 mysql-index optimization strategy.note The third item

I just looked at so many lines without realizing it. I really can’t remember them all at once. But if you think about each of the above items carefully, they are really useful in production applications. You should pay attention to it. If you can pay attention to the above points when you find some problems, it really makes sense. Therefore, the summary is as follows:

# Note: The ultimate purpose of selecting an index is to make the query faster. The principles given above are the most basic guidelines, but you cannot stick to the above guidelines.

Readers should continue to practice in their future studies and work. Analyze and judge based on the actual situation of the application, and select the most appropriate indexing method.

The above is the summary of Mysql-index. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!