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

For some statistical problems, the overlay index is as follows:

MySQL > show create table test1\ gateway * 1. Row * * Table: test1Create Table: CREATE TABLE `test1` (`id` bigint (16) NOT NULL AUTO_INCREMENT, `order_ seq` bigint (16) NOT NULL, PRIMARY KEY (`id`), KEY `idx_ id` (`id`), KEY `idx_id_ ordseq` (`id`) `order_ seq`) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) MySQL > explain select count (*) from test1 where id > 10000 and id 1502131212577 and order_seq

Show create table test1\ CREATE TABLE * 1. Row * * Table: test1Create Table: CREATE TABLE `test1` (`id` bigint (16) NOT NULL AUTO_INCREMENT, `order_ seq` bigint (16) NOT NULL, PRIMARY KEY (`id`), KEY `idx_ id` (`id`), KEY `idx_id_ ordseq` (`id`) `order_ seq`) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) MySQL > explain select count (*) from test1 where order_seq > 15021312577 and order_seq < 202007080947244761 +-- + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + +-- + | 1 | SIMPLE | test1 | NULL | index | NULL | Idx_id_ordseq | 16 | NULL | 15068082 | 11.11 | Using where Using index | +-+- -+-- + 1 row in set 1 warning (0.00 sec)

Conditional order_seq queries are used here, and federated indexes are generally not available, but the query in this case takes advantage of overriding index information. Possible_keys is still null, but key is the occurrence of Using index in idx_id_ordseq,extra, which is represented as an overlay index.

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.