目录
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索引失效情景
- 两表字符集不一致
- 两表的存储引擎不一致