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

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.

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.