High-Performance MySQL chapter 5th create highly available indexes

To some extent, MySQL has only b-tree indexes. He has no bitmap index. There is also a hash index, only in the memory storage engine.

The B-tree index is similar to that in Oracle.

MySQL restrictions on B-tree:

Only the full value blend is done or matched to the left prefix. I guess because MySQL doesn't have an cost-based optimizer, it doesn't operate on the index full scan. Because it is not possible to measure whether this full scan is zoned. So the prefix can only be matched, there is no suffix or intermediate matching this logic.

In the case of multi-column indexes, the order is important and the index will not be used if the query is not started from the first column of the index.

For example, the index is based on the A,b,c column.

• What if it's b=? and C =? Unable to use index
• If you have A =? and C =? can only be a =? The use of indexes is effective.

Always place the indexed column on one side of the query, such as a + 1 = 2, which does not use the index and should be write a = 1. (A + 1 is handled as a hidden function, possibly because of MySQL processing). A If you are an argument to a function, you cannot use an index.

Prefix index:

The prefix index uses the prefix of the string as the index on the large or long string. Pass in a parameter that represents the length of the Intercept.

Grammar:

1. Create index idx_t_test_c_char1 on T_test (C_char (3)), CreateIndex idx_t_test_c_char1 on T_test (C_char (3));
2. Explain select * from T_test t where T.c_char = '12455 '

Results:

The index is used.

Defect in prefix index: Because only the prefix is stored, it cannot be manipulated as data, such as the Order by and group by sections, which cannot be optimized using this index.

High-Performance MySQL chapter 5th create highly available indexes