第8章-6 索引失效的情况

        上一篇：《第8章-5 sql的执行顺序》

1，索引失效

什么情况下不会用到索引

数据： 

SELECT * FROM staff ;

1，索引选择性太差

索引选择性太差：

        1，重复的数据太多

        2，查询范围太大。

        字符串字段btree索引允许进行"前缀查询"  选择性太低，很多相同的时候，或者范围太大，也会失效。 比如性别字段：只有男女

explain select * from t_content 
where share_url like 'http://a.f.budejie.com/share/%';

2，<>/not in 无法使用索引

范围条件右边的索引失效

        当查询的范围右边（范围的上限）的条件使得索引失效时，可能会发生范围右边的索引失效问题。这通常发生在使用索引的范围查询（如BETWEEN、<、<=等）时，当查询的范围右边条件不能被索引完全覆盖时，MySQL可能会放弃使用该索引。

在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

EXPLAIN select * from staff where  id <> 13054 ;

EXPLAIN select * from staff where id <=13053 or id >=13055;

复合索引中如果有【>】，则自身和右侧索引全部失效。

3，is not null 不会使用索引(is null 会使用索引)

EXPLAIN SELECT * FROM staff WHERE  cus_id IS NOT NULL;

4，where 子句跳过左侧索引列，直接查询右侧索引字段

最佳左前缀法则

        如果索引的多列，要遵守最左前缀法则，指的就是从索引的最左列开始并且不跳过索引中的列。跳过第一个，索引失效；跳过前两个，索引失效；跳过中间一个，只有第一个生效。顺序可以乱。

CREATE INDEX idx_staff_name_dep_age(NAME, dep_id, age);

EXPLAIN SELECT * FROM staff WHERE dep_id = 5 AND age = 28;

5，对索引列进行计算或者使用函数

计算，函数，类型转换，会导致索引失效而转向全表扫描

EXPLAIN select * from staff where  id  = 1420 - 1;

EXPLAIN SELECT * FROM staff 
WHERE  CAST(dep_id AS CHAR) = '25';

6，类型不匹配，导致全部扫描

查询优化器会自动进行类型转换,但仍然建议使用与定义相符的类型。

EXPLAIN SELECT * FROM staff WHERE id LIKE '1576%';

7，like以 '%'开头，导致索引失效

like尽量以“常量”开头，不要以’%'开头，否则索引失效

EXPLAIN SELECT * FROM staff WHERE id LIKE '1576%';

8，使用or，导致索引失效

or很猛，会让自身索引和左右两侧的索引都失效。

EXPLAIN SELECT * FROM staff 
WHERE dep_id = 28 OR cus_id = 8;

改为用：

可以这样查询：

SELECT * FROM staff WHERE dep_id = 28
union all 
SELECT * FROM staff WHERE cus_id = 8;

尽量使用覆盖索引。

覆盖索引查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引

 小结：

        平时在写sql的时候，要注意索引失效的情况。对于like的模糊查询，确实要用，数据量大，这时候，就要考虑多加一些限制条件，缩小查询范围，再模糊查询；如果不行，就要考虑使用其它方案，比如使用ES查询了。

         上一篇：《第8章-5 sql的执行顺序》

         下一篇：《第8章-7 explain》