Practice of misunderstanding of Mysql compound Index
Background: many dba often use the federated index of mysql in production and life, and the author often encounters it in his work. This article explains one of the misunderstandings: must the federated index in the sql statement be written in the order of the index fields after the where condition? The following examples will be explained in practice.
Environment: os:centos7.4 mysql_version:mysql5.7.21
1. Build the table T2 and create the composite index idx_con_update (realname,age)
CREATE TABLE `t2` (
`id` bigint (20) NOT NULL AUTO_INCREMENT
`realname` varchar (255) NOT NULL
`age`tinyint (1) NOT NULL DEFAULT'0'
`createdAt` datetime NOT NULL
`updatedAt` datetime NOT NULL
PRIMARY KEY (`id`)
KEY `idx_con_ update` (`realname`, `age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
2. Insert data
(root@localhost:mysql.sock) [test] > select * from T2
+-- +
| | id | realname | age | createdAt | updatedAt | |
+-- +
| | 1 | kitten | 20 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 2 | kitten1 | 21 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 3 | kitten2 | 22 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 4 | kitten2 | 22 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 5 | kitten3 | 23 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 6 | kitten4 | 24 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 7 | kitten5 | 25 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 8 | kitten6 | 26 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 9 | kitten7 | 27 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 10 | kitten8 | 28 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| | 11 | kitten9 | 29 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
+-- +
11 rows in set (0.00 sec)
3. View the execution plan
(root@localhost:mysql.sock) [test] > (root@localhost:mysql.sock) [test] > explain select * from T2 where realname='kitten5' and age=25\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: t2
Partitions: NULL
Type: ref
Possible_keys: idx_con_update
Key: idx_con_update
Key_len: 768
Ref: const,const
Rows: 1
Filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
Analyze the execution plan: it is obvious that the query statement has gone from the compound index idx_con_update
If you reverse the order of where conditions, will you still use the idx_con_update index? Look down there.
(root@localhost:mysql.sock) [test] > explain select * from T2 where age=25 and realname='kitten5'\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: t2
Partitions: NULL
Type: ref
Possible_keys: idx_con_update
Key: idx_con_update
Key_len: 768
Ref: const,const
Rows: 1
Filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01sec)
Analysis execution plan: the query is still indexed after the query condition order is changed
Conclusion: colleagues and friends often ask me about the index, so boldly use the composite index and do not have to worry about the order of the index fields.
