When the user asks you if the index will be used for in in MySQL query criteria, how do you answer?

Answer: index may be used
Let's test it.

1. Create a table to index the field port

CREATE TABLE `pre_request_logs_20180524` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` char(16) NOT NULL COMMENT 'agent IP',
  `port` int(8) NOT NULL COMMENT 'Port number',
  `status` enum('Success','fail') NOT NULL COMMENT 'state',
  `create_time` datetime NOT NULL COMMENT 'Creation time',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_port` (`port`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='agent IP Request log';

Insert test data

INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (1, '192.168.1.199', 53149, 'fail', '2018-05-24 14:55:34', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (2, '192.168.1.100', 10653, 'Success', '2018-05-24 14:55:54', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (3, '192.168.1.112', 50359, 'fail', '2018-05-24 14:56:00', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (4, '192.168.1.67', 30426, 'fail', '2018-05-24 14:56:09', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (5, '192.168.1.209', 49323, 'fail', '2018-05-24 14:56:12', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (6, '192.168.1.209', 51161, 'Success', '2018-05-24 14:56:13', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (7, '192.168.1.12', 54167, 'Success', '2018-05-24 14:56:16', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (8, '192.168.1.64', 20462, 'Success', '2018-05-24 14:56:19', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (9, '192.168.1.53', 22823, 'fail', '2018-05-24 14:56:31', '2018-11-16 10:58:13');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (10, '192.168.1.85', 48229, 'Success', '2018-05-24 14:56:32', '2018-11-16 11:01:11');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (11, '192.168.1.85', 48229, 'Success', '2018-05-24 14:56:32', '2018-11-16 11:01:15');
INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (12, '192.168.1.85', 48229, 'Success', '2018-05-24 14:56:32', '2018-11-16 13:34:37');

2. test sql

explain select * from pre_request_logs_20180524 where port in (51161,20462,48229);

results of enforcement

From the result, the index is not used, but don't rush to the conclusion. Look at two more sql statements.

select * from pre_request_logs_20180524 where port in (51161,48229);
select * from pre_request_logs_20180524 where port in (51161,20462);

The results are as follows

You can see that index is used in the second sql. The difference between the two sql is that port values are different. One contains 48229 and the other contains 20462.

In fact, the MySQL optimizer will automatically determine whether the in goes through the secondary index, that is, the port field index.