MySQL Index Notes

1. The index does not store null values.

More precisely, a single-column index does not store null values, and composite indexes do not store all null values. The index cannot store null, so the IS null condition for this column, because the index is simply

No null value, cannot be used to index, only full table scan.

Why can't I save a null value for an indexed column?

The index column values are made, which inevitably involves a lot of comparison operations. The particularity of a null value is that most of the participating operations have null values. In this case, the null value is actually not

The process of participating in the index build. In other words, the null value does not appear on the leaf node of the index tree as other values.

2. Not suitable for columns with less key values (columns with more data).

If the index column TYPE has 5 key values, and if there are 10,000 data, WHERE type = 1 Accesses 2000 blocks of data in the table.

In addition to accessing the index block, a total of more than 200 data blocks are accessed.

If the full table is scanned, assuming 10 data blocks, then only 1000 blocks of data can be accessed, since the data blocks accessed by the full table scan

Less, the index will certainly not be used.

3. The leading fuzzy query cannot take advantage of the index (like '%XX ' or like '%xx% ')

If there is such a list of code values for ' AAA ', ' AAB ', ' BAA ', ' BAB ', if where code like '%ab ' condition, because the front is

Fuzzy, so can not use the order of the index, must go to find each, to see if the conditions are met. This results in a full index scan or full table sweep

Stroke If this is the condition where code like ' A% ', you can find the code in code where a is beginning with the

Data, you can stop looking because the data that follows must not meet the requirements. This makes it possible to use the index.

4. The index fails in several cases

1. If there is an or in the condition, it will not be used even if there is a conditional index (which is why the use of or is minimized)

If you want to use or, and you want the index to take effect, you can only index each column in the OR condition

2. For multi-column indexes, not the first part of use, the index is not used

3.like queries are preceded by%

4. If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise you will not use the index

5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used

4.MySQL mainly provides 2 ways of indexing: B-tree index, hash index.

The B-Tree index has the ability to range lookup and prefix lookup, and for a B-tree with n nodes, the complexity of retrieving a record is O (Logn). Equivalent to two points to find.

Hash indexes can only do equals lookups, but no matter how large the hash table is, the lookup complexity is O (1).

Obviously, if the value is very different and is based on the equivalent lookup (=, <, >, in), the hash index is a more efficient choice and it has an O (1) lookup complexity.

B-trees are a better choice if the value is relatively poor and the range lookup is the primary, and it supports range lookups.

MySQL Index Notes