建表

CREATE TABLE `tindex` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT '10',
  `name` varchar(255) DEFAULT NULL,
  `a` int DEFAULT '1',
  `b` int DEFAULT '2',
  `c` int DEFAULT '3',
  PRIMARY KEY (`id`),
  KEY `age` (`age`),
  KEY `name` (`name`),
  KEY `a` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

结果

-- type值研究
explain SELECT * FROM tindex where id =1;#type=const   
explain SELECT * FROM tindex where age =10;#type=ref
explain SELECT * FROM tindex where name like '啊%';#type=range
explain SELECT * FROM tindex where name like '%啊';#type=ALL

-- extra 研究
explain SELECT id FROM tindex where id =1;#type=const extra = using index
explain SELECT age,`name` FROM tindex where id =1;#type=const extra = null
explain SELECT id FROM tindex where age =10;#type=ref extra = using index
explain SELECT id,`name` FROM tindex where age =10;#type=ref  extra = null
explain SELECT name FROM tindex where name like '啊%';#type=range extra = Using where; using index

-- 组合索引研究1
explain SELECT *  FROM tindex where a=1;#type=ref extra = null
explain SELECT *  FROM tindex where a=1 and c =3;#type=ref extra = Using index condition
explain SELECT *  FROM tindex where a=1 and b =2;#type=ref extra = null
explain SELECT *  FROM tindex where b=2 and c=3;#type=ALL extra = Using where
explain SELECT *  FROM tindex where b=2 and c=3 ORDER BY a;#type=ALL extra = Using where; Using filesort
explain SELECT *  FROM tindex where a=1 and b>2 and c=3;#type=range extra = Using index condition

-- 组合索引研究2
-- 索引覆盖时 因为联合索引非叶节点只存最左的字段值，叶节点存全部索引字段值，只要有条件a=xxx，而且查的是a，b，c任意组合，都走ref；
-- ，若没有条件a=xx，但查的是查的是a，b，c任意组合，走index
explain SELECT a  FROM tindex where a=1;#type=ref extra =  using index
explain SELECT a,c  FROM tindex where a=1 and c =3;#type=ref extra = Using where; using index
explain SELECT b,c  FROM tindex where a=1 and c =3;#type=ref extra = Using where; using index
explain SELECT b,c  FROM tindex where b=2 and c=3;#type=index extra = Using where; using index
explain SELECT a,b,c  FROM tindex ;#type=index   extra =  using index
explain SELECT c  FROM tindex where  c=3;#type=index  extra = Using where; using index
explain SELECT a,b  FROM tindex where  c=3;#type=index  extra = Using where; using index