mysql explain索引列举

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `mobile` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `depart_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_nm_age_mb` (`name`,`age`,`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

表中建了复合索引name,age,mobile三个字段

EXPLAIN SELECT * FROM user;

全表扫描，用不着索引

EXPLAIN SELECT * FROM user WHERE name='张三';

name是索引第一个字段，所有可以用到索引

EXPLAIN SELECT * FROM user WHERE age=10;

查询条件少了索引的第一个字段，所以失效

EXPLAIN SELECT * FROM user where mobile='18445754845';

同上，少了索引的第一个字段，所以失效

EXPLAIN SELECT * FROM user WHERE name='张三' AND age=10;

索引有效，且用到了两个字段name和age

EXPLAIN SELECT * FROM `user` WHERE name='张三' AND mobile='123456';

索引有效，但只用到了一个字段name，因为中间少了age

EXPLAIN SELECT * FROM user WHERE name='张三' and age= 10 and mobile='123456';

全部匹配，用到了全部三个字段，没啥好说的

EXPLAIN SELECT name FROM user WHERE age=10;

规定了查的字段是name，正好是建索引的字段，所以只搜索索引就可以了，索引没失效，用到了3个？为什么是3个？

EXPLAIN SELECT mobile FROM user WHERE age=10;

同上，覆盖索引

EXPLAIN SELECT name,age,mobile FROM user WHERE age=10;

还是同上，覆盖索引

EXPLAIN SELECT name,age,mobile FROM user WHERE mobile='1212412';

同上

EXPLAIN SELECT name,age,mobile,depart_id FROM user WHERE age=10;

查的字段里有没建索引的字段，条件少了name，造成索引失效

EXPLAIN SELECT * FROM user WHERE name='' and age>10;

没啥好说的

EXPLAIN SELECT * FROM user WHERE name='' and age>10 AND mobile='';

范围后面的索引失效，只用到了2个字段

EXPLAIN SELECT * FROM user WHERE name like '%三';

like %在最左边，失效了

EXPLAIN SELECT * FROM user WHERE name like '张%';

like %不在最左，没失效,范围查询

EXPLAIN SELECT * FROM user WHERE name like '张%' AND age>10;

用到两个字段

EXPLAIN SELECT name FROM user WHERE name like '%三';

覆盖索引，要查的name是索引字段，索引没失效

EXPLAIN SELECT name,age FROM user WHERE name like '%三';

同上

EXPLAIN SELECT name,depart_id FROM user WHERE name like '%三';

查的字段有未建索引的字段，失效

EXPLAIN SELECT name,depart_id FROM user where name = '张三';