index specification

1. [Mandatory] Fields with unique business characteristics, even combined fields, must be built into unique indexes.

Note: Don’t think that the unique index affects the insert speed. This speed loss can be ignored, but the improvement in search speed is obvious; In addition, even if the application layer has done very With perfect checksum control, as long as there is no unique index, according to Murphy's Law, dirty data will inevitably be generated.

#2. [Mandatory] Joining more than three tables is prohibited. The data types of the fields that need to be joined must be absolutely consistent; when performing multi-table related queries

, ensure that the related fields need to have indexes.

Note: Even if you join two tables, you must pay attention to table index and SQL performance.

3. [Mandatory] When creating an index on a varchar field, you must specify the index length. It is not necessary to index the entire field. It is determined based on the

actual text distinction. Index length.

Note: The length and discrimination of index are a pair of contradictions. Generally, for string type data, the index with a length of 20, the discrimination will be as high as 90 % or more, you can use the distinction of count(distinct left(column name, index length)) / count(*) to determine.

4. [Mandatory] Left blur or full blur is strictly prohibited in page search. If necessary, please use the search engine to solve the problem.

Note: The index file has the leftmost prefix matching feature of B-Tree. If the value on the left is undetermined, then this index cannot be used.

5. [Recommendation] If there is an order by scenario, please pay attention to the orderliness of the index. The last field in order by is part of the combined

index and is placed at the end of the index combination order to avoid file_sort situations and affect query performance.

Positive example: where a =? and b =? order by c; Index: a _ b _ c

Counterexample: If there is a range search in the index, the ordering of the index cannot be used, such as: WHERE a >10 ORDER BY b; Index a _ b cannot be sorted.

6. [Recommended] Use covering indexes to perform query operations to avoid table return operations.

Explanation: If a book needs to know the title of Chapter 11, will it turn to the page corresponding to Chapter 11? Just browse the directory . This directory serves as a covering index.

Positive example: Types of indexes that can be created: primary key index, unique index, ordinary index, and covering index is a effect of a query. With the result of explain, the extra column will Appears: using index .

7. [Recommended] Use delayed correlation or subquery to optimize multi-page scenarios.

Explanation: MySQL does not skip the offset row, but takes the offset N rows, then returns the offset row before giving up, and returns N rows, when the offset is particularly large , the efficiency is very low, either control the total number of pages returned, or rewrite the SQL for the number of pages exceeding a specific threshold.

Positive example: Quickly locate the id segment that needs to be obtained, and then associate:

SELECT a.* FROM table 1 a, (select id from Table 1 where condition LIMIT 100000,20 ) b where a.id=b.id

8. [Recommendation] The goal of SQL performance optimization: at least reach the range level, The requirement is ref level. It is best if it can be consts

.

Note:

1) There is at most one matching row (primary key or unique index) in a single consts table, and the data can be read during the optimization phase.

2) ref refers to using a normal index (normal index).

3) range performs range retrieval on the index.

Counterexample: Explain the result of the table, type = index, full scan of the index physical file, the speed is very slow, this index level is still low compared to the range, and is different from the full table scan A small witch meets a big witch.

9. [Recommendation] When building a combined index, the most differentiated index should be on the far left.

Positive example: If where a =? and b =? , column a is almost close to a unique value, then you only need to build an index of idx _ a .

Note: When there is a mixed judgment condition of non-equal sign and equal sign, when building the index, please put the column of the equal sign condition in front. For example: where a >?and b =? Then even if a has a higher degree of distinction, b must be placed at the forefront of the index.

10. [Reference] Avoid the following extreme misunderstandings when creating an index:

1) Mistaking it for a query You need to create an index.

2) Mistakenly believe that indexes will consume space and seriously slow down updates and new additions.

3) It is mistakenly believed that unique indexes must be solved at the application layer through the "check first and then insert" method.