Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "MySQL coverage index how to use", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MySQL coverage index how to use" this article.

View the structure of the test table:

Mysql > show create table im_message\ gateway * 1. Row * * Table: im_messageCreate Table: CREATE TABLE `im_ message` (`id`int (11) NOT NULL AUTO_INCREMENT, `from_ id`varchar (40) COLLATE utf8_bin NOT NULL, `from_ type`tinyint (1) NOT NULL DEFAULT'0' `to_ id`varchar (40) COLLATE utf8_bin NOT NULL, `to_ type` tinyint (1) NOT NULL DEFAULT '0synchronization, `content` varchar (2048) COLLATE utf8_bin DEFAULT'', `create_ date`bigint (20) NOT NULL, `update_ date`bigint (20) NOT NULL, `message_ id`varchar (40) COLLATE utf8_bin NOT NULL, `is_ sync`tinyint (1) DEFAULT'0' COMMENT 'whether 0 is not synchronized and 1 is synchronized' `is_ read` tinyint (1) DEFAULT'0' COMMENT 'whether read 0 unread 1 read', `is_ withdrawal `tinyint (1) DEFAULT'0' COMMENT 'whether 0 not withdrawn 1 has been withdrawn', `is_ lastest` tinyint (1) DEFAULT'0' COMMENT'is the latest reply message 0 is not 1 is', PRIMARY KEY (`id`), UNIQUE KEY `uidx_message_ id` (`message_ id`), KEY `idx_ date` (`create_ date`) KEY `idx_to_ id` (`from_ id`), KEY `idx_to_ id` (`to_ id`), KEY `idx_is_ sync` (`is_ sync`), KEY `idx_update_ date` (`update_ date`), KEY `idx_fid_ tid` (`from_ id`, `to_ id`) ENGINE=InnoDB AUTO_INCREMENT=13264365 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC1 row in set (0.00 sec)

View the execution effect of SQL:

Select max (id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id; | 8547247 | | 7152294 | | 6897129 | | 12874034 | | 10011290 | | 8027198 | | 7852741 | | 9960496 | | 6059399 | | 10860981 | | 9963172 | | 13253445 | +-+ 27827 rows in set (0.91 sec) |

View the execution plan:

Mysql > explain select max (id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id

+-+-- +-+ -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-+-- +-+ -+

| | 1 | SIMPLE | im_message | NULL | index_merge | idx_from_id,idx_to_id,idx_fid_tid | idx_fid_tid,idx_to_id | 122122 | NULL | 168680 | 100.00 | Using sort_union (idx_fid_tid,idx_to_id); Using where; Using temporary; Using filesort |

+-+-- +-+ -+

1 row in set, 1 warning (0.00 sec)

Create an override index:

Mysql > alter table im_message add index idx_from_id_id (from_id,id); Query OK, 0 rows affected (1 min 1.94 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > alter table im_message add index idx_to_id_id (to_id,id); Query OK, 0 rows affected (1 min 9.79 sec) Records: 0 Duplicates: 0 Warnings: 0

Re-view the execution effect of SQL:

Select max (id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id; | 8547247 | | 7152294 | | 6897129 | | 12874034 | | 10011290 | | 8027198 | | 7852741 | | 9960496 | | 6059399 | | 10860981 | | 9963172 | | 13253445 | +-+ 27827 rows in set (0.63 sec) |

View the execution plan:

Mysql > explain select max (id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id +- -+- -- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | +- -- + -+ | 1 | SIMPLE | im_message | NULL | index_merge | idx_from_id Idx_to_id,idx_fid_tid,idx_from_id_id,idx_to_id_id | idx_from_id_id,idx_to_id_id | 122122 | NULL | 162106 | 100.00 | Using union (idx_from_id_id,idx_to_id_id) Using where; Using temporary Using filesort | +- -+ -- + 1 row in set 1 warning (0.00 sec)

It is found that the optimizer has selected the two new overlay indexes.

After creating an overlay index, select max (id) can quickly get the maximum id by making use of the order of the index.

The above is all the contents of the article "how to use the overlay Index in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.