An analysis of MySQL index

Why to build an index

In most cases, not using indexes, trying to improve performance by other means is a waste of time (from the inside of MySQL Technology Insider).

How does the index raise the performance?

• The index can get the end position of the data, thus skipping the other parts
• Positioning algorithm to quickly locate the first matching value

InnoDB always uses a B-tree to create an index, which is efficient when using <, <=,=,=>,!=, and BETWEEN operators.

Tip:between in the Django Orm corresponding to the range operator.

import= datetime.date(200511= datetime.date(2005331)Entry.objects.filter(pub_date__range=(start_date, end_date))

Equivalent

SELECTWHEREBETWEEN‘2005-01-01‘and‘2005-03-31‘;

How to build an index

• Summarize the business scenario and analyze the most commonly used fields that will appear in the Where

For example, for our projects, instance_name the user_id check_date frequency of occurrence is the highest, so these three fields must be indexed. With such an index, you can avoid full table lookups.

• Data Dimension Potential

Dimensions are the number of distinct values that are accommodated in the table. We should try to choose some high-differentiated, differentiated degrees = count(distinct col)/count(*) , according to the blog of the United States, the general need to join the field we are required to be more than 0.1, that is, the average 1 scan 10 records.

• Do not misuse the index

Because writing data is not only required to write to rows of data, it also affects all indexes. As a result, the more indexes are built, the slower the write speed will be. Additionally, the index occupies disk space.

• Index the prefix of a string

短Indexes can reduce index space and thus speed up.

• Composite Index

For example, address, province, city, indexed by these two worthy combinations.

• Leftmost prefix match

MySQL will always match right until it encounters a range query (>, <, between, like) to stop the match, such as a = 1 and B = 2 and C > 3 and D = 4 If the index of the (A,B,C,D) order is established, D is not indexed if it is built The A,b,d,c index can be used, and the order of the a,b,d can be adjusted arbitrarily.

• Do not add index columns to the calculation

Try not to calculate on the left side of the "=" in Where.

How to Query

1. Using indexes in Where,order by statements
2. Avoid using data dimension potential in where, such as sex,isdeleted, etc.
3. If it is a numeric field, use the number type
4. Try not to use! =, like or <, the engine may perform a full table search, consider replacing it with between,union.

Using explain to optimize SQL

Let's start by explaining the field names returned by explain.

Column	meaning
Select_type	Select Type
Table	Table of the display row
Type	Join type
Possible_keys	Possible values for an index
Key	The actual index used
Key_len	Using the length of the index
Ref	and index
Rows	Key indicators
Filtered
Extra

Refer to MySQL Explain for details.

So far, we are all a single-table query, and then look at the multi-table.

Let's review the left Join,right join, INNER JOIN, outer join:

REF:

1. Group Review Team
2. MySQL Technology Insider

An analysis of MySQL index