Summary of the top ten problems of MySQL index failure

background

Recently, a slow SQL statement was produced because or and != were used, which caused the index to fail. So, I have summarized the top ten causes of index failure. I hope it will be helpful to you. Come on.

1. The query condition contains "or", which may cause the index to fail

Create a new user table, which has a common index userId and has the following structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

When executing a query SQL, it will go through the index, as shown in the following figure:

Adding the or condition + age without an index will not use the index, as shown in the figure:

Analysis & Conclusion:

• For the case of or+age without index, suppose it uses the index of userId, but when it comes to the age query condition, it still has to scan the whole table, which means it needs three steps: full table scan + index scan + merge
  
• If it starts with a full table scan, it will be done in one go.
  
• MySQL has an optimizer. For the sake of efficiency and cost, when encountering an OR condition, the index may become invalid, which seems reasonable.

Note: If the columns in the or condition are indexed, the index may fail. You can try it yourself.

2. If the field type is a string, be sure to enclose it in quotation marks when using where, otherwise the index will be invalid

Assume that the demo table structure is as follows:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

userId is a string type, which is a common index of the B+ tree. If a number is passed in the query condition, it will not go through the index, as shown in the figure:

If you add '' to the number, that is, pass a string, of course, it will go by index, as shown below:

Analysis and conclusion:

Why doesn't the first statement go through the index without single quotes? This is because when single quotes are not added, the comparison is between strings and numbers, and their types do not match. MySQL will do an implicit type conversion , converting them to floating-point numbers before comparing them.

3. Like wildcards may cause index invalidation.

It is not that the index will be invalid if the like wildcard is used, but that the like query starts with %, which will cause the index to be invalid.

Table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Like query starts with %, the index is invalid, as shown in the figure:

Put % at the end and find that the index is still working normally, as follows:

Add % back and change it to search only the indexed fields ( covering the index ). I found that the index is still used. Are you surprised?

in conclusion:

If the like query starts with %, the index will become invalid. There are two ways to optimize:

• Using covering indexes
• Put % at the end

Note: An index that contains all the data that meets the query requirements is called a covering index.

4. Combined index: If the condition column during query is not the first column in the combined index, the index will be invalid.

Table structure: (There is a joint index idx_userid_age , userId first, age second)

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

In a joint index, when the query condition meets the leftmost matching principle , the index is effective normally. Please see the demo:

If the condition column is not the first column in the joint index, the index becomes invalid, as follows:

Analysis and conclusion:

• When we create a joint index, such as (k1, k2, k3), it is equivalent to creating three indexes: (k1), (k1, k2) and (k1, k2, k3). This is the leftmost matching principle.
• The joint index does not meet the leftmost principle, and the index will generally become invalid, but this is also related to the MySQL optimizer.

5. Using MySQL's built-in functions on the index column will cause the index to become invalid.

Table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `loginTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE,
  KEY `idx_login_time` (`loginTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Although loginTime is indexed, the index is directly GG because the built-in function Date_ADD() of MySQL is used, as shown in the figure:

6. If operations are performed on index columns (such as +, -, *, /), the index will become invalid.

Table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Although age is indexed, the index is lost because it is being calculated. . . As shown in the figure:

7. When (!= or < >, not in) is used on the index field, the index may become invalid.

Table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Although age is indexed, it is used! = or < >, not in, the index is dummy. as follows:

8. Using is null and is not null on the index field may cause the index to fail.

Table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_card` (`card`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

If a single name field is indexed and the query for a non-empty name is performed, the index will be used, as follows:

If a single card field is indexed and the query for a non-empty name is performed, the index will be used, as follows:

But when it is connected with or, the index becomes invalid, as follows:

9. The encoding formats of the fields associated with left join query or right join query are different, which may cause the index to fail.

Create two new tables, one user and one user_job

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
 
CREATE TABLE `user_job` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

The name field of the user table is encoded in utf8mb4, while the name field of the user_job table is encoded in utf8.

Execute the left outer join query, and the user_job table still goes through the full table scan, as follows:

If you change them to have the same encoding as the name field, the index will still be used.

10. MySQL estimates that a full table scan is faster than an index, so it does not use the index.

• When a table's indexes are queried, the best index is used unless the optimizer finds a full table scan more efficient. The optimizer optimizes to a full table scan depending on whether the data found using the best index exceeds 30% of the data in the table.
• Don't add indexes to 'gender' etc. If a data column contains values ​​such as "0/1" or "Y/N", that is, it contains many duplicate values, even if an index is created for it, the index effect will not be very good and may even result in a full table scan.

For efficiency and cost considerations, MySQL estimates which is faster, a full table scan or using an index. This is related to its optimizer. Let's take a look at its logical architecture diagram (picture source: online)

Summarize

We have summarized the top ten causes of index failure. Let's analyze the slow SQL statement we produced. The simulated table structure and the causative SQL are as follows:

CREATE TABLE `user_session` (
  `user_id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,
  `device_id` varchar(64) NOT NULL,
  `status` varchar(2) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`device_id`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain 
update user_session set status =1
where (`user_id` = '1' and `device_id` != '2')
or (`user_id` != '1' and `device_id`='2')

analyze:

• The executed SQL uses or condition. Because of the composite primary key ( user_id , device_id ), it looks like each column is indexed, and the index will take effect.
• However, the presence of != may cause the index to become invalid. That is, the two major syndromes or + != lead to slow update of SQL.

Solution:

So, how to solve it? We remove or condition and divide it into two executions. At the same time, add a normal index to device_id .

Finally, the top ten causes of index failure are summarized. I hope that everyone can refer to these ten causes in their work and study, combine the execution plan expain with the scenario, and conduct specific analysis instead of following the routine and sticking to the rules to determine which scenario will cause the index to fail.

This is the end of this article about the top ten problems of MySQL index failure. For more information about the top ten problems of MySQL index failure, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!