background
Recently, a slow sql burst in production because or and! =, Causes the index to fail. So, I summarized the top ten complications of index failure. I hope it can help you. Come on.
1, Query criteria contain or, which may cause index invalidation
Create a new user table with a common index userId. The structure is as follows:
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;
-
Execute a query sql, which is indexed, as shown in the following figure:
-
Add the or condition + age without index, and the index will not go, as shown in the figure:
Analysis & CONCLUSION:
-
In the case of or+ age without index, assume that it follows the userId index, but when it comes to the age query criteria, it also needs a full table scan, that is, a three-step process: full table scan + index scan + merge
-
If it starts with a full table scan, it will be finished with a direct one-time scan.
-
mysql has an optimizer. Considering efficiency and cost, it seems reasonable to invalidate the index when encountering or conditions.
Note: if the or condition columns are indexed, the index may go. You can try it yourself.
2, If the field type is a string, the where must be enclosed in quotation marks, otherwise the index will be invalid
Suppose 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 the query condition passes a number, it will not go through the index, as shown in the figure:
If '' is added to a number, that is, a string is passed. Of course, the index is used, as shown in the following figure:
Analysis and conclusion:
Why don't you index the first statement without a single quotation mark? This is because when single quotation marks are not added, strings and numbers are compared. Their types do not match. MySQL will perform implicit type conversion, convert them to floating-point numbers, and then compare them.
3, The like wildcard may invalidate the index.
It is not that the like wildcard is used, and the index will be invalid. It is because the like query starts with% that the index will 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%, and the index is invalid, as shown in the figure:
After% is placed, it is found that the index is still running normally, as follows:
Add back% and change it to only check the index fields (overwrite the index). It is not surprising to find or follow the index
Conclusion:
like queries that start with% will invalidate the index. There are two ways to optimize:
-
Use overlay index
-
Put% behind
Attachment: an index that contains all the data that meets the query needs is called a covering index.
4, For a federated index, the condition column in the query is not the first column in the federated index, and the index is invalid.
Table structure: (there is a joint index idx\u userId\u age, with userId first and age last)
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 federated index, the index is normally effective when the query criteria meet the leftmost matching principle. See the demo:
If the condition column is not the first column in the federated index, the index is invalidated as follows:
Analysis and conclusion:
-
When we create a joint index, such as (k1, K2, and K3), we create three indexes (k1), (k1,k2), and (k1, K2, and K3). This is the leftmost matching principle.
-
The union index does not meet the leftmost principle, and the index will generally fail. However, this is also related to the Mysql optimizer.
5, Using mysql's built-in functions on the index column will invalidate the index.
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 built-in mysql function date is used_ Add (), index direct GG, as shown in the figure:
6, For index column operations (such as, +, -, *, /), the index is 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 of its operation... The mountain is heavy and the river is full of doubts. The index really doesn't know the way. As shown in the figure:
7, When (! = or < >, not in) is used on an 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 or is not null on the index field may cause the index to become invalid.
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;
Add an index to a single name field and query the statements whose name is not empty. In fact, the index will be used, as follows:
Add an index to a single card field, and query the statement whose name is not empty. In fact, the index will also be used, as follows:
However, when they are connected by or, the index fails, as follows:
9, The coding formats of the fields associated with left join queries or right join queries are different, which may lead to index invalidation.
Create two 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 code of the user table is utf8mb4, and the user_ The name field of the job table is encoded as utf8.
Execute left external connection query, user_ The job table or the full table scan is as follows:
If you change them to the name field with the same code, you will still sing loudly, valiantly and proudly all the way to the index.
10, mysql estimates that using full table scanning is faster than using indexes, so indexes are not used.
-
When the index of a table is queried, the best index will be used unless the optimizer uses a full table scan more effectively. The optimizer optimizes the full table scan depending on whether the data found by using the best index exceeds 30% of the data of the table.
-
Do not index 'gender' etc. If a data column contains "0/1" or "Y/N" values, that is, it contains many duplicate values. Even if it is indexed, the indexing effect will not be very good, and it may also lead to a full table scan.
In consideration of efficiency and cost, Mysql estimates which of the full table scan and index use is faster. This is related to its optimizer. Let's take a look at its logical architecture diagram (picture source online)
summary
After summarizing the top ten complications of index failure, let's echo here and analyze the slow sql we produced. The simulated table structure and incident 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')
Analysis:
-
The or condition is used in the executed sql. Because the combined primary keys (user\u ID, device\u ID) look like indexes are added to each column, and the indexes will take effect.
-
But there is! =, May cause index invalidation. or+= Two syndromes lead to slow update of sql.
Solution:
So, how to solve it? We remove the or condition and implement it in two ways. Also give device_id plus a normal index.
Finally, the paper summarizes the ten complications of index failure. It is hoped that in your work and study, you can refer to the ten complications and make a specific analysis in combination with the implementation plan and scenarios, rather than going through the steps, sticking to the rules, and determining which scenario must have index failure.
Source: programmer DD, programmer Xiaohui, macrozheng