1.首先查看表里的数据
2.创建标的索引
ALTER table test add index thisindex(`city`,`companyName`,`positionName`);
3.查看表里的索引
show index from test;
这个时候详细了解索引使用的原则
#1 第一列用到了索引
explain select * from test where city = "上海";
#2 第二列没有用到索引explain select * from test where companyname = "好未来";
#3 第二列和第三列都没有用到索引explain select * from test where companyname = "好未来" and positionName="php开发工程师";
#4 第一列和第二列用到索引explain select * from test where city = "上海" and companyname = "好未来" ;
#5 第一列用到索引,第三列没有用到explain select * from test where city = "上海" and positionName="php开发工程师";
#6 第一列,第二列,第三列都用到索引explain select * from test where city = "上海" and companyname = "好未来" and positionName="php开发工程师";
#7 第一列,第二列,第三列都用到索引 mysql会自己优化 顺序explain select * from test where createTime ="2018-12-29" and city = "上海" and positionName="php开发工程师" and companyname = "好未来";
#8 like不会用到索引explain select * from test where city like "%上海%" ;
#9 or不会用到索引explain select * from test where city = "上海" or companyname = "好未来";
4.接下来看>是否会使用
这是测试
explain select * from test where id =10;
explain select * from test where id >10;
然后开始正式测试
explain select * from test1 where id>10 and city = "上海" and companyname >40 and positionName = "php开发工程师";
这个以后测试看看为什么
select * from mytable where a=3 and b>7 and c=3;
a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引
5.接下来看order by
select * from test1 order by companyname desc;
explain select * from test1 order by companyname desc;
没有用到,但是网上说用到了(不知道为什么)
(1) select * from mytable where a=3 order by b;
a用到了索引,b在结果排序中也用到了索引的效果,前面说了,a下面任意一段的b是排好序的
(2) select * from mytable where a=3 order by c;
a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort
(3) select * from mytable where b=3 order by a;
b没有用到索引,排序中a也没有发挥索引效果