| 联合索引 |
这里加了KEY `idx_code_age_name` (`age`,`name`,`info`,),索引 |
|
|
| 最左匹配原则 |
explain select * from user where age=21; |
用到了 |
第一个用了第一个age |
| 最左匹配原则 |
explain select * from user where age=21 and name="小明" |
用到了 |
第一个用了第一个age |
| 最左匹配原则 |
explain select * from user where age=21 and name="小明" and info="101"; |
用到了 |
第一个用了第一个age |
| 最左匹配原则 |
explain select * from user where age=21 and info="101"; |
用到了 |
第一个用了第一个age |
| 最左匹配原则 |
explain select * from user where name="小明"; |
失效了 |
第一个没用了第一个age |
| 最左匹配原则 |
explain select * from user where info="101"; |
失效了 |
第一个没用了第一个age |
| 最左匹配原则 |
explain select * from user where name="小明" and info="101"; |
失效了 |
第一个没用了第一个age |
|
|
|
|
| 有计算 |
explain select * from user where id=1; |
用到了 |
无计算 |
| 有计算 |
explain select * from user where id+1=2; |
失效了 |
有计算 |
|
|
|
|
| 函数 |
explain select * from user where height=22; |
用到了 |
|
| 函数 |
explain select * from user where SUBSTR(height,1,2)=22; |
失效了 |
|
|
|
|
|
|
code的类型是varchar |
|
|
| 字段类型不同 |
explain select * from user where code="101"; |
用到了 |
|
| 字段类型不同 |
explain select * from user where code=101; |
失效了 |
|
|
|
|
|
| like左边包含% |
explain select * from user where code like "10%"; |
用到了 |
|
| like左边包含% |
explain select * from user where code like "%1"; |
失效了 |
%在前面 |
| like左边包含% |
explain select * from user where code like "%1%"; |
失效了 |
%在前面 |
|
|
|
|
| 列对比 |
explain select * from user where id=height |
失效了 |
|
|
|
|
|
|
id、height都有索引 |
|
|
| or关键字 |
explain select * from user where id=1 or height="175"; |
用到了 |
|
| or关键字 |
explain select * from user where id=1 or height="175" or address="广州"; |
失效了 |
3个索引都要加 |
|
|
|
|
| in关键字 |
|
用到了 |
|
| exists关键字 |
|
用到了 |
|
| not in关键字 |
|
失效了 |
|
| not exists关键字 |
|
失效了 |
|
|
|
|
|
| order by |
explain select * from user order by code limit 100; |
用到了 |
因为有limit |
| order by |
explain select * from user order by code; |
失效了 |
因为无limit |
| order by |
explain select * from user order by code,age limit 100; |
用到了 |
因为有limit |
| order by |
explain select * from user order by code,age; |
失效了 |
因为无limit |
| order by |
explain select * from user order by code,age,name limit 100; |
用到了 |
因为有limit |
| order by |
explain select * from user order by code,age,name; |
失效了 |
因为无limit |
| order by |
explain select * from user order by code, height limit 100; |
失效了 |
code、height不是联合索引 |
| order by |
explain select * from user order by code desc,age desc limit 100; |
用到了 |
都是降序/升序 |
| order by |
explain select * from user order by code asc,age desc limit 100; |
失效了 |
一升一降 |
