create table test1(a int,b tinyint,c varchar(20),d int);
alter table test1 add primary key (a); --添加主键
alter table test1 drop primary key; --删除主键
alter table test1 add unique key (b,c); -- 添加唯一索引
alter table test2 drop key c_2; --删除索引
insert into test1 values(1,2,"333",22);
insert into test1 values(2,21,"333",22),(3,20,"333",22),(4,25,"333",22);
-- 最终表如下
Create Table: CREATE TABLE `test1` (
`a` int(11) NOT NULL DEFAULT '0',
`b` tinyint(4) DEFAULT NULL,
`c` varchar(20) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
explain select a from test1 where b>10\G;
:使用到覆盖索引(辅助索引的叶子节点上是主键),在索引上进行了范围查询
-
explain select a from test1 where b>10 and c="ff"\G;
使用到覆盖索引(辅助索引的叶子节点上是主键),在索引上进行了范围查询
-
explain select d from test1 where b>10\G;
这里虽然有可用的索引,但是mysql并没有用,因为需要d字段同时选择度太低,所以肯定需要到聚集索引上二次查询,权衡下mysql决定直接扫描表
-
explain select d from test1 where b>24\G;
: 使用索引进行扫描,相对全表扫描代价较小,具体可以使用optimizer_trace分析
-
explain select d from test1 where a>10\G;
:使用到了聚集索引(主键)进行范围查询
联合索引
create table test2 (a int auto_increment primary key, b int, c int ,d int ,e int,index(b,c,d));
insert into test2(b,c,d,e) values (1,2,3,4),(2,1,4,5),(2,3,4,5),(1,2,3,2),(3,4,2,1);
-- 换句话说建立了bcda索引。
-
explain select d from test2 where b=1 order by d
b=1用到了索引,但是排序没有,排序使用了文件
-
explain select d from test2 where b>1 order by d;
:索引覆盖、用文件进行排序
-
explain select d from test2 where b>1 order by c;
排序没有用到索引,所以采用临时文件进行排序Using filesort
-
explain select d from test2 where b=1 order by c\G;
:排序和查找都用到了索引
-
explain select d from test2 where c=1 order by b;
: 排序和查找都用到了索引,不过相对上面的效率较低。这里利用了索引覆盖
-
explain select * from test2 where c=3 order by b;
: 这条和上一条差别就是取的字段不同,上面使用了索引覆盖,但是这条需要到聚集索引上二次查询,所以mysql选择直接扫描表
-
select * from test2 where b=3 order by d;
: 搜索b用到了索引,但是回聚集索引上进行了查询,同时使用临时文件进行了排序
-
结论:在联合索引里需要满足最左原则,索引为(bcda);如果字段不连续只能使用一部分索引eg:(b=0 and d=10);字段中有范围查询也只能用一部分索引(b>0 and c=0)