MySQL 面试题: 索引什么时候会失效

表定义

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO user (id, code, age, name, height) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO user (id, code, age, name, height) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO user (id, code, age, name, height) VALUES (3, '103', 23, '苏三', 174,'成都');

索引可能失效的情况

下面的法则不要死记，本质是系统通过计算查找数据开销的结果，哪种方式快使用哪种。

1. 使用联合索引时不满足最左匹配原则。

    # 联合索引 code,age,name

    # 使用索引的情况，索引先排第一列的序再排第二列的序，以此类推
    explain select * from user where code='101';
    explain select * from user where code='101' and age=21
    explain select * from user where code='101' and age=21 and name='周星驰';

    # 索引失效的情况
    explain select * from user where age=21;
    explain select * from user where name='周星驰';
    explain select * from user where age=21 and name='周星驰';

2. 使用了 select *

  # 全表扫描
  explain  select * from user where name='苏三';
  # 索引扫描
  explain  select code,name from user  where name='苏三';

3. 索引上有计算或函数( 可能）

    # 全表扫描 ALL
    explain  select * from user where id+ 1=2;

    # 索引扫描 idx_code_age_name
    explain  select name from user where id + 1=2;

4. like 左边包含%

     # 全表扫描 ALL
    explain select * from user where name like '李%';

    # 索引扫描 idx_code_age_name
    explain  select name from user where id + 1=2;