【数据库MySQL】-- 索引（三）（最左前缀法则、索引失效情况、SQL提示）

一、最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

例：

第一个语句可以索引。

第二个语句不能索引，因为profession最左侧的索引被跳过，后面的不能索引。

第三个语句部分索引，只有profession可以索引，status不能。

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

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

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

如果前面的索引使用了范围查询（><），则后面的索引会失效。

如何解决这个问题，只需要加上>=或<=就可以，也就是尽量使用>=或<=的范围查询。

例：status索引失效

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

二、索引失效的情况

1、索引列运算

不能在索引列上进行运算操作，索引将失效。

例：

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

2、字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

3、模糊查询

如果仅仅是尾部模糊匹配，索引不会失效

如果是头部模糊匹配，索引会失效。

例：

第一个不会失效，第二个会失效。

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

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

4、or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

例：

age没有索引，所以id的索引也会失效。

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

5、数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

三、SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1、use index（为MySQL提供建议，MySQL会进行评估，是否合理）

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

2、ignore index（忽略索引）

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

3、force index（强制使用索引）

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';