复合索引的使用

复合索引的使用

create table `test1` (
`id` int(11) not null,
`name` char(11),
`age` int(11),
`del_flag` varchar(22),
primary key(id,age) 
)COLLATE='utf8_bin'
       ENGINE=InnoDB;
       
1、EXPLAIN SELECT id,name FROM test1 WHERE id = 1 //可以看出使用的是索引
================================================================
1	SIMPLE	test1		ref	PRIMARY	PRIMARY	4	const	1	100.00	

2、EXPLAIN SELECT id,name FROM test1 WHERE age = 20//此处并没有走索引
=======================================================
1	SIMPLE	test1		ALL					3	33.33	Using where
3、EXPLAIN SELECT id,name FROM test1 WHERE id = 1 or age = 12 //or会使索引失效
==========================================================
1	SIMPLE	test1		ALL	PRIMARY				3	66.67	Using where
4、EXPLAIN SELECT id,name FROM test1 WHERE id = 1 and age = 12//此处走了索引
============================================================
1	SIMPLE	test1		const	PRIMARY	PRIMARY	8	const,const	1	100.00