索引研究

建表

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


发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注