Introduction to the
Index for MySql is very important, reasonable use of index can improve query efficiency, MySql support all have primary key index, unique index, joint index, full text index, common index, this article briefly analyze MySql index failure several cases, the following figure summarized some index failure cases. Next, verify the 10 points in the figure.
MySql index validation
DDL and DML are used for testing
/* Navicat Premium Data Transfer Source Server : master Source Server Type : MySQL Source Server Version : 50732 Source Host : localhost:3316 Source Schema : order Target Server Type : MySQL Target Server Version : 50732 File Encoding : 65001 Date: 23/03/2021 22:53:53 */
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`create_time` datetime NOT NULL,
`number` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`status` tinyint(4) NOT NULL,
`product_id` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`total_amount` decimal(10.0) NOT NULL,
`count` int(4) NOT NULL,
`user_id` varchar(128) COLLATE utf8mb4_bin NOT NULL.PRIMARY KEY (`id`),
KEY `idx_1` (`number`,`status`,`total_amount`,`user_id`) USING BTREE COMMENT 'Test index'
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of order
-- ----------------------------
BEGIN;
INSERT INTO `order` VALUES (3.'the 2020-12-11 03:24:56'.'543995759748632576'.1.'1'.100.1.'10000');
INSERT INTO `order` VALUES (4.'the 2020-12-11 03:24:56'.'543995759748632576'.1.'1'.100.1.'10000');
INSERT INTO `order` VALUES (5.'the 2020-12-11 03:24:56'.'543995759748632576'.1.'1'.100.1.'10000');
INSERT INTO `order` VALUES (6.'the 2020-12-11 03:24:56'.'543995759748632576'.1.'1'.100.1.'10000');
INSERT INTO `order` VALUES (7.'the 2020-12-11 03:24:56'.'543995759748632576'.1.'1'.100.1.'10000');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
Copy the codeFull value match (best indexed)
All fields are matched in sequence, idX_1 is used, and the number of scanned rows is 1
mysql> explain select * from `order` where number='543995759748632576' and `status`=1 and total_amount=20 and user_id='10000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------------+------+------ ----+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------------+------+------ ----+-------+
| 1 | SIMPLE | order | NULL | ref | idx_1 | idx_1 | 602 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------------+------+------ ----+-------+
Copy the codeIf the index order is out of order, the full value match is not related to the index order. The underlying MySQL optimizer will perform the optimization to adjust the index order
mysql> explain select * from `order` where number='543995759748632576' and `status`=1 and user_id='10000' and total_amount=20;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------------+------+------ ----+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------------+------+------ ----+-------+
| 1 | SIMPLE | order | NULL | ref | idx_1 | idx_1 | 602 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------------+------+------ ----+-------+
Copy the codeViolate the leftmost rule
Failed to match the leftmost index of the union index, index invalid, full table scan
mysql> explain select * from `order` where `status`=1 and total_amount=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
Copy the codeDo any operation on the index column
Select * from varchar; select * from varchar; select * from number; The second SQL does a function on the number field and invalidates the index.
mysql> explain select * from `order` where number=543995759748632576 and `status`=1 and user_id='10000' and total_amount=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | idx_1 | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> explain select * from `order` where left(number,5)='543995759748632576' and `status`=1 and user_id='10000' and total_amount=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
Copy the codeThe index column to the right of the index range condition is invalidated
This SQL also matches the index, but the index column to the right of the index range condition is invalid
mysql> explain select * from `order` where number='543995759748632576' and `status`=1 and total_amount>20 and user_id='10000';
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- ----------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- ----------+
| 1 | SIMPLE | order | NULL | range | idx_1 | idx_1 | 88 | NULL | 1 | 20.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- ----------+
Copy the codeUse overwrite indexes whenever possible
The index column is the same as the query column. The index is invalid because it matches the leftmost rule
mysql> explain select number,`status`,total_amount,user_id from `order` where `status`=1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- -------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- -------------+
| 1 | SIMPLE | order | NULL | index | NULL | idx_1 | 602 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- -------------+
Copy the codeUse does not equal (! =, <>) (except overwrite index)
Mysql in use does not equal (! =, <>) will cause a full table scan
mysql> explain select * from `order` where number! ='543995759748632576' and `status`=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | idx_1 | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set.1 warning (0.00 sec)
mysql> explain select * from `order` where number<>'543995759748632576' and `status`=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | idx_1 | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set.1 warning (0.01 sec)
Copy the code- Here’s a counter example
The index index is not invalid because the query column overwrites the index
mysql> explain select number,`status`,total_amount,user_id from `order` where number<>'543995759748632576' and `status`=1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- -------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- -------------+
| 1 | SIMPLE | order | NULL | range | idx_1 | idx_1 | 82 | NULL | 4 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- -------------+
1 row in set.1 warning (0.00 sec)
Copy the codeLike begins with a wildcard (‘% ABC ‘)
The wildcard ‘% ABC ‘index is invalid, and the’ ABC %’ index is not invalid
mysql> explain select * from `order` where number LIKE '% 543995759748632' and `status`=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set.1 warning (0.00 sec)
mysql> explain select * from `order` where number LIKE '5759748632576%' and `status`=1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- ----------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- ----------+
| 1 | SIMPLE | order | NULL | range | idx_1 | idx_1 | 83 | NULL | 1 | 20.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------- ----------+
1 row in set.1 warning (0.00 sec)
Copy the codeIndex invalidation of strings without single quotes (data type implicit conversion)
mysql> explain select * from `order` where number =543995759748632576 and `status`=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | idx_1 | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set.3 warnings (0.00 sec)
Copy the codeThe OR connection index is invalid
If there is an index in the column before or and no index in the column after or, then the designed index will not be used, because there is no index in the list after or, then the following condition must go through the full table scan. In the case of a full table scan, it is not necessary to add I/O access by an additional index scan. One full table scan filter condition is sufficient.
mysql> explain select * from `order` where number ='543995759748632576' or `count`=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | order | NULL | ALL | idx_1 | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set.1 warning (0.00 sec)
Copy the codeorder by
Normal (index participates in sort), index is not invalidated, index has two functions, sort and find
mysql> explain select * from `order` where number ='543995759748632576' and `status`=1 ORDER BY total_amount;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+------- ----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+------- ----------------+
| 1 | SIMPLE | order | NULL | ref | idx_1 | idx_1 | 83 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+------- ----------------+
1 row in set.1 warning (0.00 sec)
Copy the codeSQL > select * from Extra Using filesort where order by does not match index
Explain select number,user_id from order where number =’543995759748632576′ order BY user_id; explain select number,user_id from order where number =’543995759748632576′ order BY user_id; Explain select number,user_id from order where number =’543995759748632576′ order by count;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- -----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- -----------------------------+
| 1 | SIMPLE | order | NULL | ref | idx_1 | idx_1 | 82 | const | 1 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- -----------------------------+
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- --------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- --------------------------+
| 1 | SIMPLE | order | NULL | ref | idx_1 | idx_1 | 82 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- --------------------------+
Copy the codegroup by
Resulting in temporary tables (which degrades performance)
mysql> explain select number,total_amount from `order` where number ='543995759748632576' GROUP BY total_amount;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- ----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- ----------------------------------------------+
| 1 | SIMPLE | order | NULL | ref | idx_1 | idx_1 | 82 | const | 1 | 100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- ----------------------------------------------+
1 row in set.1 warning (0.00 sec)
mysql>
mysql> explain select number,total_amount from `order` where number ='543995759748632576' GROUP BY total_amount,count;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- -------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- -------------------------------------------+
| 1 | SIMPLE | order | NULL | ref | idx_1 | idx_1 | 82 | const | 1 | 100.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+------------- -------------------------------------------+
1 row in set.1 warning (0.00 sec)
Copy the codeView index usage
SHOW STATUS LIKE 'Handler_read%'; Results: Handler_read_first3
Handler_read_key 563
Handler_read_last 0
Handler_read_next 62
Handler_read_prev 0
Handler_read_rnd 478
Handler_read_rnd_next 27722
Copy the codeIf the index is working, the value of Handler_read_key is high. This value represents the number of times a row is indexed. A low value indicates that the performance improvement from adding an index is not high because the index is not used very often.
A high value of Handler_read_rnd_next indicates that queries run inefficiently and should be indexed to remedy the problem. This value means the number of requests to read the next row in the data file. If a large number of table scans are being performed, a high value for Handler_read_rnd_next is usually a sign that the table is not indexed correctly or that queries are being written without taking advantage of the index.