<>最左前缀法则

1）
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';

2）
explain select * from tb_user where profession = '软件工程' and age = 31;

3）
explain select * from tb_user where profession = '软件工程';

profession存在，索引就会生效，只不过索引的长度不同。

1）
explain select * from tb_user where age = 31 and status = '0';

2）
explain select * from tb_user where status = '0';

explain select * from tb_user where profession = '软件工程' and status = '0';

<>思考题

profession = '软件工程'； 时，是否满足最左前缀法则，走不走上述的联合索引，索引长度？

<>范围查询

explain select * from tb_user where profession = '软件工程' and age > 30 and status
= '0';

<>索引失效情况

<>1）不要在索引列上进行运算操作， 索引将失效。

explain select * from tb_user where phone = '17799990015';

explain select * from tb_user where substring(phone,10,2) = '15';

<>2）字符串不加引号

explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0'; explain select * from tb_user where profession = '软件工程' and age = 31 and
status= 0;

<>3）模糊查询

explain select * from tb_user where profession like '软件%'; explain select *
from tb_user where profession like'%工程'; explain select * from tb_user where
profession like'%工%';

<>4）or连接条件

explain select * from tb_user where id = 10 or age = 23; explain select * from
tb_user where phone= '17799990017' or age = 23;

<>5）数据分布影响

select * from tb_user where phone >= '17799990005'; select * from tb_user
where phone>= '17799990015';

GitHub

Gitee