Click above SQL database development, pay attention to get SQL video tutorial
SQL column
Summary of SQL basic knowledge
SQL advanced knowledge summary
When it comes to indexing, you all know that it is born to improve query efficiency. But in the query process, how can we make our query statement use the index? We all have this problem more or less. Today we’re going to answer that question.
1
Clustered indexes and non-clustered indexes
Indexes are generally divided into clustered indexes and non-clustered indexes.
Clustered indexes are fast, but you can only build one, so try to build the most frequently used columns into clustered indexes.
Non-clustered indexes are not as fast as clustered indexes, but multiple indexes can be created and are faster than full table scans.
2
How to build efficient indexes
A. Create indexes on association conditions
Such as:
SELECT * FROM T1
JOIN T2 ON T1.ORDER_ID=T2.ORDER_ID;
The two columns following the association condition ON can be indexed separately, which will quickly query the data that meets the association condition.
B. Create indexes on the conditional query ****
Such as:
SELECT * FROM T1
WHERE T1.PRICE>20;
You can build indexes on the WHERE condition PRICE column.
Note: Indexes are not used in the following cases
-
Where the operator is used on the index column,
For example: t1. PRICE*0.5>20, this does not use the index
-
Where a function is used on an index column,
For example: UPPER(t1.address)=’NEWYORK’, the index is not used either
-
If there is a NULL value when using an index,
For example, if t1. ADDRESS IS NULL, the query will not follow the index
-
Character data is not quoted or indexed
For example, if ORDER_ID is a character, t1. ORDER_ID=’112′ will use the index, but if the quotation marks are removed, t1. ORDER_ID=112 will not be used, but the index will not be used.
-
OR (OR) and unequal (<>,! =) and NOT IN will NOT use indexes either
-
The oft-used LIKE does not follow the index except for leading matches
For example: t1. ADDRESS LIKE ‘NEW%’, this index, but LIKE
T1.ADDRESS LIKE ‘%NEW%’; t1. ADDRESS LIKE ‘%NEW%’
-
Finally, the query optimizer will not use the index if it determines that a full table scan is faster than an index walk.
C. Principles for creating indexes ****
- Columns that are infrequently written and updated are good candidates for indexing
- Frequently queried columns are good candidates for indexing
- Indexes can be created for those with less duplicate data
D. The clever use of joint index ****
A federated index is a combination of several columns to form an index, which can be surprisingly effective in a WHERE condition compared to a single-column index.
Such as:
SELECT * FROM T1 WHERE t1. CITY= ‘Beijing’ AND t1. DISTR=’ haidian ‘;
It is better to create a joint index for the columns CITY and DISTR at this point.
Note: the union index must be in order. If one of the middle indexes is not available, the columns following it will not be indexed.
Such as:
SELECT * FROM T1
WHERE T1.CITY= ‘Beijing’
AND LEFT(t1. DISTR,3)=’ DISTR ‘
AND T1.ROAD=’#10′
If we create a joint index for CITY, DISTR, and ROAD, we will only make CITY go through the index because of the pre-index rules. The DISTR index will fail because of the DISTR function, and the ROAD column will fail because of the DISTR function. \
3
What is a bad case for indexing
Because it takes time to create and maintain indexes, the time increases proportionally as the data increases; Need to take up physical space; When the data in the table is maintained, the index is also maintained, which slows down the data maintenance. Based on these shortcomings, the following situations are not suitable for indexing
- Indexes should not be created for columns that are rarely used or referenced during queries.
- Indexes should not be created for columns that have very few data values, such as gender.
- Columns defined as image, text, and bit data types should not be indexed.
- Indexes should not be built when the modification performance is much greater than the retrieval performance.
- A number of duplicate values are not suitable for indexing.
Well, that’s all for today’s index. If you are interested in optimization, you can join our wechat group to communicate and learn together.
-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Recommended reading spicy chicken code writing guidelines5After we parachuted in an expert, half of the programmers went crazy... GitHub is the most inspiring computer self-study tutorial on GitHub, a free database management tool that is even more useful than NavicatCopy the code