Analysis of the index problem of MySQL

In fact, SQL optimization is the optimization of the index, without index, a single table query how you check the time complexity is O (n), so the SQL optimization problem is the key on the index.

There are two types of MySQL indexes,hash and tree, but the limit of hash is too much, and the efficiency of multi-time hash index is falling fast, so let's say my view on the most commonly used Btree index.

MySQL's btree index or B +tree index is a binary search tree, and a pointer is added between the binary search leaf nodes to facilitate sorting or querying a range of data.

Index The biggest problem is that in many cases can not be effective, the index of the article on the Internet a lot, basically is such a case index can not take effect, and so on a large article;

But as a programmer, if it's really useless to memorize them, finding a liberal arts student is better than our backs. So I summed up a few of the reasons why the index does not work, those who say on the internet can be classified into these categories.

1, theSQL writes the nonstandard or is called the MySQL query engine not to be optimized causes the index not to take effect.

For example, if the field of a varchar type is indexed, and I write the condition in sql Columnvlaue = 1 (Note that this is a number ), this time the field type and parameter type do not match, Therefore, the index does not take effect at query time.

In fact, the query engine can be fully optimized, or the person writing SQL can completely change the SQL specification to avoid this problem.

2,mysql design btree cannot support such a query.

For example, there are two kinds of comments on a like query on the Internet, one is that using the like Query index cannot take effect, and one is that a query index that uses like for a non-leftmost prefix cannot take effect;

Of course, the latter is correct. MySQL's btree index only supports the leftmost prefix match. But you say it can be designed to match all, of course, like using the KMP algorithm for substring matching.

But I guess the mysql designers really don't want to add more complexity to the Btree index design, or fear to reduce the efficiency of the query (the substring length is m, the large string length is N, the matching time complexity will change from O (m) to O (n+m)).

3,Btree does not support this type of query at all.

For example, reverse query. You said my condition is which field is not equal to xx, you put this condition into the two search tree to search, it does not know the data you want to check on which subtree.

Therefore, even if the index is used, the worst case of complexity is O (n), which is no different from the use, so the index is not used in this case.

The above is a single-table case where the index does not take effect for the reason analysis.

There are also aggregate indexes and the use of indexes at multiple tables. Aggregate index The processing of MySQL is simple,

is to combine several fields together to index a field, so the first field in the index is important, if the first field is not in the query condition,

The aggregation index is not valid. As for the multi-table joint check, in fact, the most should be solved is the amount of data involved, this I studied thoroughly.

Analysis of the index problem of MySQL