MySQL Index Establishment principle and matters needing attention

First, several principles of index establishment: 1) the leftmost prefix matching principle, very important principle, MySQL will always match to the right until a range query (>, <, between, like) stops matching, 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, and if the index of establishment (A,B,D,C) can be used, the order of a,b,d can be arbitrarily adjusted. 2) = and in can be disorderly, such as a = 1 and B = 2 and c = 3 build (a,b,c) index can be in any order, the MySQL query optimizer will help you optimize the form that the index can recognize. 3) Try to choose a high-sensitivity column as the index, the formula for the degree of sensitivity is count (distinct col)/count (*), indicating the scale of the field is not repeated, the larger the proportion of the number of records we scan, the difference between the unique key is 1, and some states, The gender field may be 0 in front of big data, and one might ask, what is the empirical value of this ratio? Using different scenarios, this value is also difficult to determine, generally need to join the field we are required to be more than 0.1, that is, the average 1 scan 10 records 4) Index column can not participate in the calculation, keep the column "clean", such as from_unixtime (create_time) = ' 2014-05-29 ' can not be used to the index, the reason is very simple, B + tree is stored in the Data table field values, but for retrieval, all elements need to be applied to the function to compare, obviously the cost is too large. So the statement should be written create_time = Unix_timestamp (' 2014-05-29 '); 5) Expand the index as far as possible and do not create a new index. For example, the table already has an index of a, now to add (A, b) of the index, then only need to modify the original index. 6) The data column that defines the foreign key must be indexed. 7) For columns that are rarely involved in those queries, duplicate values are not indexed for more columns. 8) do not index columns for data types that are defined as text, image, and bit. 9) for frequently accessed columns avoid index two, the index uses the attention point: 1.In general, the index should be based on those fields that will be used for join,where judgment and order by ordering. Try not to index a field in the database that contains a large number of duplicate values. For a field of an enum type, there is a high likelihood of a large number of duplicate values. 2.You should try to avoid null values for the field in the Where clause, or it will cause the engine to abandon using the index for a full table scan. Such as:

Null

It is best not to leave the database null, and to populate the database with not NULL as much as possible. Comments, descriptions, comments and the like can be set to NULL, others, preferably do not use NULL. Do not assume that NULL does not require space, such as: char (100) type, when the field is established, the space is fixed, regardless of whether the insertion value (NULL is also included), is occupied 100 characters of space, if it is varchar such a variable length field, NULL does not occupy space. You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query: 3.Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index. 4.You should try to avoid using or in the WHERE clause to join the condition, and if a field has an index and one field is not indexed, it will cause the engine to abandon using the index for a full table scan. Such as:

Select ID from t where num=10 or Name = ' xiaoming '

This can be queried to make full use of the index:

Select ID from t where num = ten= ' xiaoming '

5.In and not are also used with caution, otherwise the full table scan will result.

and the negative query (not, not-in, not-like, <>! =,!>,!<) does not use the index

Select ID from t where num in

For consecutive values, you can use between instead of in:

Select ID from t where num between 1 and 3

Many times using exists instead of in is a good choice, of course exists does not run the index.

Select num from a where num in (select num from B)

Just above, replace with the following statement:

Select num from a where exists (select 1 from b where num=a.num)

6.)The following fuzzy query will also cause a full table scan:

Select ID from t where name like '%abc% '

It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use indexes, while like "aaa%" can use indexes. To be more efficient, consider full-text indexing. Now that we're talking about using the index under Fuzzy queries, let's talk about it in detail. 1. The like%keyword index fails with a full table scan. But you can flip function +like before fuzzy query + build rollover function index = Go Flip function index, do not go full table Scan 2. The like keyword% index is valid. 3. The like%keyword% index fails, and you cannot use a reverse index.

// You can use the explain test to see if you have to go. Index SELECT * FROM table where  code like ' classify_description% ' * from table Where code like '%classify_description%  ' * from table where code like '%classify_description '  

7.)If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan:

Select ID from t where num = @num

You can force the query to use the index instead:

Select ID from the T with (index name) where num = @num

You should try to avoid expression operations on the fields in the WHERE clause, which causes the engine to discard full table scans using the index. Such as:

Select ID from t where NUM/2 = 100

The above should read:

Select ID from t where num = 100*2

8.)You should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. Such as:

Select ID from t where substring (name,1,3) = ' abc '       //name starts with ABC IDselect ID from t where DateDiff (DA Y,createdate, ' 2005-11-30′ ' = 0    -' 2005-11-30 '    // generated ID

should read:

Select ID from t where name like ' abc% '>= ' 2005-11-30 ' and CreateDate < ' 2005-12-1 '

9.Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system. 10.When using an indexed field as a condition, if the index is a composite index (multi-column index), you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible. 11.Indexing is not as good as it is, indexes can improve the efficiency of the corresponding select, but it also reduces the efficiency of insert and update, because the index may be rebuilt at insert or update, so it is important to consider how to build the index, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary. 12.You should avoid updating the clustered index data columns as much as possible, because the order of the clustered index data columns is the physical storage order of the table records, which can cost considerable resources once the column values change to the order in which the entire table is recorded. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index. 13.Try to avoid returning large amounts of data to the client, and if the amount of data is too large, you should consider whether the demand is reasonable. 14.The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

MySQL Index Establishment principle and matters needing attention