建立索引的优缺点
索引在使用时虽然可以提高查询速度,降低服务器的负载,但索引的使用也会占用物理空间,给数据的维护造成很多麻烦,并且在创建和维护索引时,其消耗的时间会随着数据量的增加而增长,因此,是否建立索引需要依据字段的特点来决定
什么字段适合建立索引?
- 查询条件中频繁使用的字段
例如,在where
group by
order by
子句或表与表连接时
频繁使用的字段 - 数字型的字段
建立索引的字段类型也会影响查询和连接的功能,数字型字段与字符串字段在处理时,前者只需要比较一次,而后者需要比较字符串中的每一个字符 - 存储空间较小的字段
占用空间小有利于提高检索的效率
什么字段不适合建立索引?
- 更新频繁的字段
为了保证索引数据的准确性,对于建立了索引的字段,更新数据的同时也要更新索引,造成IO访问量增加,加重存储负担 - 重复值较高的字段
在建立索引时,如果字段中保存的数据重复值较高,即使该字段会被频繁使用也不适合建立索引
什么情况下MySQL不会使用索引?
- 查询谓词没有使用索引的主要边界,换句话说就是
select *
,可能会导致不走索引
比如,你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引,但是优化器会认为需要一行行的扫描会更有效,这个时候,优化器可能会选择TABLE ACCESS FULL,但是如果换成了SELECT Y FROM T WHERE Y = XXX,优化器会直接去索引中找到Y的值,因为从B树中就可以找到相应的值。 - 单键值的b树索引列上
存在null值
,导致COUNT()不能走索引
如果在B树索引中有一个空值,那么查询诸如SELECT COUNT() FROM T 的时候,因为HASHSET中不能存储空值的,所以优化器不会走索引,有两种方式可以让索引有效,一种是SELECT COUNT(*) FROM T WHERE XXX IS NOT NULL或者把这个列的属性改为not null (不能为空)。 - 索引列上
有函数运算
,导致不走索引
如果在T表上有一个索引Y,但是你的查询语句是这样子SELECT * FROM T WHERE FUN(Y) = XXX。这个时候索引也不会被用到,因为你要查询的列中所有的行都需要被计算一遍,因此,如果要让这种sql语句的效率提高的话,在这个表上建立一个基于函数的索引,比如CREATE INDEX IDX FUNT ON T(FUN(Y));这种方式,等于Oracle会建立一个存储所有函数计算结果的值,再进行查询的时候就不需要进行计算了,因为很多函数存在不同返回值,因此必须标明这个函数是有固定返回值的。