Use of indexes

Use of indexes

1. When the field data Update frequency is low, the query is used more frequently and there are a large number of duplicate values is recommended to use the clustered index
2. Multiple columns are frequently accessed simultaneously, and each column contains duplicate values to consider a composite index
3. The leading column of the composite index must be well controlled, otherwise the effect of the index cannot be played. The composite index is not used if the leading column is not in the query condition when queried. The leading column must be the most frequently used column

      4. Multi-table operations before they are actually executed, the query optimizer lists a number of possible connection scenarios based on the connection criteria and finds the best scenario for minimizing the system overhead. Join conditions to fully consider the table with the index, the number of rows of tables; The selection of the internal and external tables can be determined by the formula: number of matching rows in the outer table * the number of times each lookup in the inner-layer table, the product is minimized as the best scheme  
      5. Any action on a column in the WHERE clause results in a column-wise calculation at the SQL runtime, so it has to perform a table search without using the index above the column, and if the results are available at query compile time, it can be optimized by the SQL optimizer, using the index, and avoiding table searches (example: select  * from record where substring (card_no,1,4) = ' 5378 '   
&&  select * from record where card_no like  ' 5,378% ') Any action on a column will cause a table scan, which includes database functions, Evaluate expressions and so on, to move the operation to the right of the equals sign whenever possible   the ' in ' of the
      6.where condition is logically equivalent to ' or ', so the parser will in  (' 0 ', ' 1 ') converted to column= ' 0 '  or column= ' 1 ' to execute. We expect it to look for each or clause separately, and then add the result so that it can take advantage of the index on column, but in fact it takes an "or policy", that is, the row that satisfies each or clause is first taken out of the worksheet in the staging database, and a unique index is created to remove the duplicate rows. Finally, the result is calculated from this temporary table. Therefore, the actual process does not take advantage of the column index, and the completion time is affected by the tempdb database performance. The IN, or clauses often use worksheets to invalidate the index; If you do not produce a large number of duplicate values, you can consider taking the sentence apart; the disassembled clause should contain the index  
     7. To be good at using stored procedures, It makes SQL more flexible and efficient

Index usage principles:
1. Use the index to traverse the table more quickly.
The index established by default is a non-clustered index, but sometimes it is not optimal. Under a non-clustered index, the data is physically randomly stored on the data page. A reasonable index design should be based on the analysis and prediction of various queries. Generally speaking: A. Have a large number of duplicate values, and often have a range of queries (>,<,> =,< =) and order BY, the group by the column, you can consider the establishment of a clustered index;
B. Frequent simultaneous access to multiple columns, and each column contains duplicate values to consider the establishment of a composite index;

C. Composite indexes to make the key query as much as possible to form an index overlay, its leading column must be the most frequently used column. Indexes can help improve performance but not as many indexes as possible, but too many indexes in the opposite direction cause the system to be inefficient. Each index is added to the table, and maintenance of the index collection will be done with the corresponding update work.

2, in the vast number of queries as far as possible to use the format conversion.
3, order BY and Gropu by using the order by and the group by phrase, any index helps to improve the performance of select.
7. Any action on a column will cause a table scan, which includes database functions, calculation expressions, and so on, to move the operation to the right of the equals sign whenever possible.
4, IN, or clauses often use worksheets to invalidate the index. If you do not produce a large number of duplicate values, you can consider taking the sentence apart. The disassembled clause should contain an index.

Principles of Index Establishment:
If the prefix value of the data in a column is very small, we'd better just index the prefix. MySQL supports this index. The index method I used above is to index the leftmost 6 characters of username. The shorter the index, the more
The smaller the disk space, the less time is spent in the retrieval process. This method can index up to 255 characters in the left.
On many occasions, we can build multiple columns of data to index.
The index should be based on the fields that are compared in the query criteria, not on the fields we want to find and display

Use of indexes