高性能索引

1、索引的优点

根据创建索引的数据结构的不同，索引会带来一些其他的效果，总结下来索引主要有以下的好处：

1）索引大大减少了服务器需要扫描的数据量

2）索引可以帮助服务器避免排序和临时表

3）索引可以将随机I/O变成顺序I/O

那么问题来了，索引是最好的解决方案吗？主要还得根据数据量。

如果数据量很小，索引就没有太大的价值了，而且由于索引本身也需要一定的空间，所以当数据量很小的时候反而影响效率。对于中到大型的表，索引就非常有效了，但是对于特大型的表，建立索引的代价也随之增加，这里就需要别的技术进行查询优化了，比如分区技术。对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别的元数据技术替代索引。

2、高性能的索引策略

评价一个索引的好坏，经常使用”三星系统“（Three-Star System）：

索引将相关记录放在一起，则获得”一星“；如果索引中的数据顺序和查找中的排列顺序一致则获得”两星“；如果索引中的列包含了查询中需要的所有的列，则获得”三星“；

1）独立的列

如果查询的列不是独立的，会使MySql无法使用已有的索引，比如：

Select id From user where id + 1 = 5；

其实这里要查找的是id=4的，但MySql无法解析方程式，所以也就无法使用现有的索引。

2）前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得很大，而且很慢，比如BLOB、TEXT或者VARCHAR列，这里可以使用前缀索引。

3）多列索引

多列索引并不是说为查找的列每个都建立索引。

4）选择合适的索引列顺序（适用于B-Tree索引）

这里有一个经验法则：将选择性最高的列放到索引最前，以此类推。

5）聚簇索引

因为是存储引擎负责实现索引，所以并不是所有的都能实现聚簇索引。

下图展示了聚簇索引中的记录是如何存放的：

它有如下的一些有点：

a）可以把相关数据存放在一起。

b）由于索引和数据在一起，因而查询速度更快

c）使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

不过也有如下的缺点：

a）聚簇索引最大限度的提高了I/O密集型应用的效率，但是如果数据都放在内存中，顺序就没那么重要了

b）插入速度严重依赖于插入顺序

c）更新代价高

d）在插入新行或者更新的时候可能会导致页分裂

e）二级索引可能比想象的更大

6）覆盖索引

如果一个索引包含所有需要查询字段的值，我们就说其是覆盖索引。

考虑一下如果查询只需要查找索引而不需要回表，会有多少好处：

a）索引条目远小于数据条目，因而会大幅度的提高效率

b）因为索引是按照顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘一行读取的速度快得多

不是所有类型的索引都能成为覆盖索引的，覆盖索引必须要存储列的值，而哈希索引、全文索引和空间索引都不行，所以MySql只能使用B-Tree索引来做覆盖索引。

7）冗余和重复索引

重复索引是在相同列上按照相同的顺序创建的相同类型的索引。应该避免，发现后也应该立即删除。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是新建，但有时候出于性能方面的考虑会用到冗余索引，因为扩展已有的索引会使其变得很大。

8）索引和锁

索引对于InnoDB非常重要，因为它可以让查询锁更少的元组。这点十分重要，因为MySQL 5.0中，InnoDB直到事务提交时才会解锁。有两个方面的原因：首先，即使InnoDB行级

锁的开销非常高效，内存开销也较小，但不管怎么样，还是存在开销。其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。

InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做

（即达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。