mysql范围查询索引失效的情况演示

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL,
  `name` varchar(40),
  `age` int,
  `address` varchar(50),
  `deleted` tinyint,
  `created` datetime,
  PRIMARY KEY (`id`),
  INDEX `idx_name_age_address`(`name` ASC, `age` ASC, `address` ASC) 
) ENGINE = InnoDB;

INSERT INTO `user` VALUES (1, '5', 1000, '花果山', 0, '2022-02-24 00:42:01');
INSERT INTO `user` VALUES (2, '8', 800, '高老庄', 0, '2022-02-24 00:42:04');
INSERT INTO `user` VALUES (3, '3', 25, '长安', 0, '2022-02-24 00:42:06');
INSERT INTO `user` VALUES (4, '白龙马', 700, '龙宫', 0, '2022-02-24 00:42:09');

-- SELECT * FROM user; 
-- ;SHOW INDEX FROM user

分别执行如下语句观察所引长度字段即可得出结论

EXPLAIN SELECT * FROM user WHERE name = '5' AND age = 1 AND address = 'nihao';
EXPLAIN SELECT * FROM user WHERE name = '5';
EXPLAIN SELECT * FROM user WHERE name = '5' AND age = 1;
EXPLAIN SELECT * FROM user WHERE name = '5' AND age = 1 AND address = '天津';
EXPLAIN SELECT * FROM user WHERE age = 1 AND address = 'nihao';
EXPLAIN SELECT * FROM user WHERE name = '5' AND address = 'nihao';
EXPLAIN SELECT * FROM user WHERE name = '5' AND age > 1 AND address = '天津';
EXPLAIN SELECT * FROM user WHERE name = '5' AND age > 1 AND age < 5 AND address = '天津';
EXPLAIN SELECT * FROM user WHERE name LIKE '5%' AND age = 1 AND address = '天津';
EXPLAIN SELECT * FROM user WHERE name LIKE '5' AND age = 1 AND address = '天津';
EXPLAIN SELECT * FROM user WHERE name LIKE '%5' AND age = 1 AND address = '天津';
EXPLAIN SELECT * FROM user WHERE name LIKE '%5%' AND age = 1 AND address = '天津';