目录
一、如何判断数据库的索引是否生效
二、如何评估索引创建的是否合理
三、索引是否越多越好
四、如何处理数据库索引失效
五、是否所有的字段都适合创建索引
一、如何判断数据库的索引是否生效
可以使用EXPLAIN语句查看索引是否正在使用。
例如,假设已经创建了book表,并已经在其year_publication字段上建立了普通索引。执行如下语句:
EXPLAIN SELECT * FROM book WHERE year_publication=1990;
EXPLAIN语句将为我们输出详细的SQL执行信息,其中:
①possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
②key行是MySQL实际选用的索引。
如果possible_keys行和key行都包含year_publication字段,则说明在查询时使用了该索引。
二、如何评估索引创建的是否合理
建议按照如下的原则来设计索引:
1. 避免对经常更新的表进行过多的索引,并且索引中的列要尽可能少。应该经常用于查询的字段创建索引,但要避免添加不必要的字段。
2. 数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
3. 在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值,因此就无须建立索引,如果建立索引不但不会提高查询效率,反而会严重降低数据更新速度。
4. 当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引需能确保定义的列的数据完整性,以提高查询速度。
5. 在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。
三、索引是否越多越好
索引并非越多越好,一个表中如有大量的索引,不仅占用磁盘空间,还会影响INSERT、DELETE、UPDATE等语句的性能,因为在表中的数据更改时,索引也会进行调整和更新。
四、如何处理数据库索引失效
可以采用以下几种方式,来避免索引失效:
1. 使用组合索引时,需要遵循“最左前缀”原则;
2. 不在索引列上做任何操作,例如计算、函数、类型转换,会导致索引失效而转向全表扫描;
3. 尽量使用覆盖索引(之访问索引列的查询),减少 select * 覆盖索引能减少回表次数;
4. MySQL在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描;
5. LIKE以通配符开头(%abc)MySQL索引会失效变成全表扫描的操作;
6. 字符串不加单引号会导致索引失效(可能发生了索引列的隐式转换);
7. 少用or,用它来连接时会索引失效。
五、是否所有的字段都适合创建索引
不是。
下列几种情况,是不适合创建索引的:
1. 频繁更新的字段不适合建立索引;
2. where条件中用不到的字段不适合建立索引;
3. 数据比较少的表不需要建索引;
4. 数据重复且分布比较均匀的的字段不适合建索引,例如性别、真假值;
5. 参与列计算的列不适合建索引。