MySQL索引

索引分类

在MySQL中,主要有4种类型的索引,分别为：B-Tree索引、Hash索引、Fulltext索引和R-Tree索引.

在InnoDB存储引擎中,存在两种不同形式的索引,一种是Cluster形式的主键索引（Primary Key）,另外一种则是B+Tree索引实现的Secondary Index.

B+Tree在B-Tree的基础上,在每一个Leaf Node上面除了存放索引键的相关信息之外,还存储了指向与该Leaf Node相邻的后一个Leaf Node的指针信息,这主要是为了加快检索多个相邻Leaf Node的效率.

Cluster索引 和 Secondary Index区别

在InnoDB存储引擎中,存在两种不同形式的索引,一种是Cluster形式的主键索引（Primary Key）,另外一种则是B+Tree索引.

两种索引在Root Node和Branch Nodes方面完全一样.但它们会在Leaf Nodes方面出现差异.

在Primary Key中,Leaf Nodes存放的是表的实际数据,不仅仅包括主键字段的数据,还包括其他字段的数据,整个数据以主键值有序的排列.

而Secondary Index则和其他普通的B-Tree索引没有太大的差异,只是在Leaf Nodes除了存放索引键的相关信息外,还存放了InnoDB的主键值.

索引优缺点

• 优点
  极大地提高检索效率,加快检索时间,降低检索过程中须要读取的数据量.

降低数据的排序成本.每个索引中的数据都是按照索引键键值进行排序后存放的,所以,当Query语句中包含排序分组操作时,如果排序字段和索引键字段刚好一致,MySQL Query Optimizer就会告诉mysqld在取得数据后不用排序了,因为根据索引取得的数据已经满足客户的排序要求.

能够改善排序分组操作的性能.分组操作没办法直接利用索引完成.但是分组操作是须要先进行排序然后分组的,所以当Query语句中包含分组操作,而且分组字段也刚好和索引键字段一致,那么mysqld同样可以利用索引已经排好序的这个特性,省略掉分组中的排序操作.

• 缺点
  索引是完全独立于基础数据之外的一部分数据.假设在Table ta中的Column ca创建了索引idx_ta_ca,那么任何更新Column ca的操作,MySQL在更新表中Column ca的同时,都须要更新Column ca的索引数据.

何时创建索引

1.较频繁的作为查询条件的字段应该创建索引,可以减少须要访问的数据量.

2.唯一性太差(重复的值较多)的字段不适合单独创建索引,即使频繁作为查询条件.
即使创建了索引,MySQL Query Optimizer大多数时候也不会去选择使用.若使用了这种索引,由于索引字段中每个值都含有大量的记录,那么存储引擎在根据索引访问数据的时候会带来大量的随机IO,甚至有些时候还会出现大量的重复IO.

3.更新非常频繁的字段不适合创建索引
索引中的字段被更新的时候,不仅要更新表中的数据,还要更新索引数据,以确保索引信息是准确的.

4.不会出现在 WHERE 子句中的字段不该创建索引.

选择 单键索引 还是 组合索引

当有多个字段一起作为查询过滤条件存在于WHERE子句中.在这种时候,就必须要判断是该仅仅为过滤性最好的字段建立索引,还是该在所有字段（过滤条件中的）上建立一个组合索引.

而组合索引中因为有多个字段存在,理论上被更新的可能性肯定比单键索引要大很多,这样带来的附加成本也就比单键索引要高.

当WHERE子句中的查询条件含有多个字段时,通过这多个字段共同组成的组合索引的查询效率肯定比只用过滤条件中的某一个字段创建的索引要高.

若给WHERE子句中的每一个字段都创建一个单键索引.MySQL Query Optimizer大多数时候都只会选择其中的一个索引,然后放弃其他的索引.

创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中,还应该尽量让一个索引被多个Query语句利用,尽量减少同一个表上的索引数量,减少因为数据更新带来的索引更新成本,同时还可以减少因为索引所消耗的存储空间.

MySQL还提供了另外一个优化索引的功能,那就是前缀索引.在MySQL中,可以仅仅使用某个字段的前面部分内容做为索引键索引该字段,以达到减小索引占用的存储空间和提高索引访问效率的目的.当然,前缀索引的功能仅仅适用于字段前缀随机重复性很小的字段.如果须要索引的字段前缀内容有较多的重复,索引的过滤性自然也会随之降低,通过索引所访问的数据量就会增加,这时候前缀索引虽然能够减少存储空间消耗,但是可能会造成Query访问效率的极大降低.

索引限制

BLOB和TEXT类型的列只能创建前缀索引.

使用不等于（!=或者<>）的时候,MySQL无法使用索引.

使用LIKE操作的时候如果条件以通配符开始（如’%abc…’）时,MySQL无法使用索引.

使用索引的注意事项

使用索引时,有以下一些技巧和注意事项：

1.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的.所以我们在数据库设计时不要让字段的默认值为NULL.

2.使用短索引

对串列进行索引,如果可能应该指定一个前缀长度.例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引.短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作.

3.索引列排序

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的.因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引.

4.like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题.like “%aaa%” 不会使用索引而like “aaa%”可以使用索引.

5.不要在列上进行运算

select * from users where YEAR(adddate) < 2007;
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:
select * from users where adddate < ‘2007-01-01’;

6.不使用 NOT IN 和 <> 操作