Shulou(Shulou.com)06/01 Report--

Data preparation:

CREATE TABLE `t_blog` ( `id` int(11) NOT NULL auto_increment, `title` varchar(50) default NULL, `typeId` int(11) default NULL, `a` int(11) default '0', PRIMARY KEY (`id`), KEY `index_1` USING BTREE (`title`,`typeId`,`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. The composite index loses the first column field:

2. Composite index, skipping the middle field:

Let's look at the results when the query condition only has the first column:

Next, execute the case of skipping the middle field:

At this time, the index does take effect, but the key_len is still 153. In theory, the higher the query precision, the larger the key_len will be. However, if it is not increased at this time, it means that the index on the condition [a='123'] is not used.

Composite indexes should follow the leftmost front row principle, that is, queries should start from the leftmost front row of the index and cannot skip the middle column. Once a column is skipped, the indexes on the columns after that column will be invalid.

3. Calculation, function, type conversion and other operations on the index lead to index invalidation:

Use the function:

Automatic type conversion, title definition is varchar type, but when querying it assigns int causes mysql to automatically type convert it, resulting in index failure.

Calculated using + - * /etc.

4. The index after the index is invalid due to the use of the range

Let's look at SQL execution without using scope:

As the query precision increases, key_len increases, indicating that the indexes of both conditions are valid. Now add another query condition after the range:

key_len remains 158, indicating that the index on the condition of ticket [a=1] is invalid.

5. Use is not equal to operation (!= or)

6. Use is not null

7. Use leading fuzzy queries

First look at the query:

At this point, the index is not invalid. Let's look at the fuzzy query before and after:

At this point the index has expired, so use the leading query alone:

The index is invalidated at this point, so using a leading query causes the index to be invalidated.

8, mysql does not take the index will have better query performance index failure

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.