MySQL索引失效场景以及索引设计原则

 索引失效情况

1. 在索引列上进行运算操作（比如函数或者表达式计算），索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15'; 换成 explain select * from tb_user where phone = '17799990015';这是可以的。 【导致索引列的值发生变化，从而破坏了B+树索引的有序性】 【因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了】

2. 字符串类型字段使用时，字符串不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号 【隐式类型转换----CAST 函数实现】 【MySQL 要会自动把字符串转为数字。select * from t_user where CAST(phone AS signed int) = 1300000001;对phone索引使用函数了。】

3. 模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。explain select * from tb_user where profession like '软件%'; 这个是不会失效的，只有前面加了%才会失效。 【两边都加也没用】前面加了%导致无法确定字符，不知道索引在哪里 ---------- 【因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。】--------- 注意：使用左模糊匹配（like "%xx"）并不一定会走全表扫描，关键还是看数据表中的字段（可能走全扫描二级索引树(type=index)）。MySQL 使用 like “%x“，索引一定会失效吗？ | 小林coding

4. 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。【联合索引不适用于 or】 【必须都是索引列，index merge的意思就是对 id 和 age 分别进行了扫描，然后将这两个结果集进行了合并，这样做的好处就是避免了全表扫描。】

5. 数据分布影响：如果 MySQL 评估使用索引比全表更慢，则不使用索引。因为只要有一个没有索引，另外一个用不用索引都没有意义，都要进行全表扫描。所以就无需用索引。【比如where >= ,表中绝大多数都满足这个条件，效率还是全表扫描快些】

6. 联合索引最左匹配法则----【像like '%zz'一样，表中全是索引列可能走全扫描二级索引树，type = index， Using index覆盖索引】

• 联合索引范围查询出现> < ,右边的失效

联合索引优化

 select * from order where status = 1 order by create_time asc

• 不能只status建立索引，因为要避免文件排序filesort。因为create_time要排序

【更好的方式给 status 和 create_time 列建立一个联合索引】

索引设计原则

【避免索引失效的情况】

1. 针对于数据量较大，且查询比较频繁的表建立索引

2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引【联合索引】

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4. 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率

7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询