mysql - 索引介绍/索引失效情景/索引检验/索引优化

目录

1.如何判断是否需要创建索引

2.mysql 索引设计原则

3.索引失效场景

1.如何判断是否需要创建索引

• 较频繁地作为查询条件的字段
• 唯一性太差的字段不适合建立索，如状态字段、类型字段等
• 更新太频繁地字段不适合创建索引
• 不会出现在where条件中的字段不该建立索引

 

2.mysql 索引设计原则

• （1）对于经常查询的字段，建议创建索引。
• （2）索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。
• （3）避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源。
• （4）数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源。
• （5）不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。
• （6）当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度。
• （7）在频繁进行跑排列分组（即进行 group by 或 order by操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引。

 

3.索引失效场景

(1)索引失效情景

• 1)使用or ，若or连接多个条件会使索引失效，除非or中各列都加了索引或者改成union all连接；
• 2)使用like，like查询中，(前导模糊)like "%aa%"会使索引失效，但like "aa%"不会使索引失效；
• 3)使用regexp，类似like ，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
• 4)使用<>，!= 不走索引，会全表扫描，可改为 key>0 or key<0 样式。
• 5)使用not in ，in ， exists ，not exists，not in 不走索引而全表扫描，可用not exists代替。
• 6)使用 >，>= 、 <、<= ，不确定会不会走索引
• 7)使用between .. and ，对于range类型，选择性小于17%了，就会走全表扫描，而非range
• 8)使用is null ,is not null ， B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走 
• 11)索引列上使用表达式或者函数会使索引失效而进行全表扫描
• 12)组合索引，不是使用第一列索引，索引失效。
• 13)数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
• 14)组合索引中不能有列为null，如果存在则该列索引将会失效；

(2)join/left join索引失效情景

• 两表字符集不一致
• 两表的存储引擎不一致