MySQL index failures and mysql index failures

MySQL index failures and mysql index failures
1. The index cannot store null values.

A. a single column index cannot store null values, and a composite index cannot store all null values.

B. When the is null condition is used for queries, indexes cannot be used and full table scans are only allowed.

Why can't index columns store Null values?

A. The index is ordered. When a NULL value enters the index, it cannot be determined where it should be placed. (Building index column values involves many comparison operations. null values are uncertain values.

　　Cannot determine the position of null on the leaf node of the index tree .)

B. If you need to store the NULL value in the index, there are two methods: first, convert the NULL value into a specific value, and use this specific value for search in the WHERE clause. Second, create a composite index. For example

Create index ind_a on table (col1, 1); by specifying a non-null Constant Value in the composite index, it is impossible to have a full null value in the combination of columns that constitute the index.

　　

2. Columns with fewer key values (columns with more duplicate data)

If the index column TYPE has five key values and 10 thousand data records, where type = 1 will access the 2000 data blocks in the table.

In addition, access index blocks require access to more than 200 data blocks.

If a full table scan contains 10 data blocks, you only need to access 1000 data blocks.

If the number is smaller, the index will certainly not be used.

3. indexes cannot be used for leading fuzzy queries (like '% xx' or like' % XX % ')

Assume that the values of such a column are 'aaa', 'aab', 'baa', and 'bab'. If the where code like '% AB' condition

Fuzzy, so the order of indexes cannot be used. You must search one by one to see if the conditions are met. This will cause full index scan or full table scan.

Description. If such A condition is where code like 'a % ', you can find the position of the CODE starting with A in the CODE.

You can stop searching for the data because the subsequent data does not meet the requirements. In this way, the index can be used.

4. Index failures

1. If there is or in the condition, it will not be used even if there is a condition with an index (this is why we try to use less or)

To use or and make the index take effect, you must add an index to each column in The or condition.

2. If multiple-column indexes are not the first part, they are not used.

3. like query starts with %

4. If the column type is string, you must reference the data in quotation marks in the condition. Otherwise, no index is used.

5. If mysql estimates that using full table scan is faster than using indexes, no indexes will be used.

5. MySQL provides two Indexing Methods: B-Tree index and Hash index.

B-tree index hasRange search and prefix search capabilitiesFor B trees with N nodes, the complexity of retrieving a record is O (LogN ). It is equivalent to binary search.

Hash indexes can only be used for equal searches. However, no matter how large a Hash table is, the search complexity is O (1 ).

Obviously, if the value difference is large and the equivalence search (=, <,>, in) is the main choice, the Hash index is more efficient, and it has O (1) the search complexity.

If the value difference is relatively poor and the range search is dominant, Tree B is a better choice. It supports range search.

 

 

From https://www.cnblogs.com/binyue/p/4058931.html