Mysql索引

InnoDB引擎中的索引分为两种：

1）Cluster形式（聚集索引）的主键索引(Primary Key)

2）B-Tree索引。称为Secondary index

其中B-Tree是和其他存储引擎如Myisam存放形式基本相同的普通索引。

聚集索引和非聚集索引：

A）聚集索引，表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
B）非聚集索引，表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。

在一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序，所有的新行将被添加的表的末尾位置。

下面是聚集索引的定义：

clustered index存储结构的表中数据行是保存在索引的叶子页中的，clustered意思是数据行和索引主键都保存在一起。每个表只有一个clusterd index，就是那个primary key，换句话说Innodb按主键进行聚集。如果没有定义primary key，Innodb会试着使用 Unique nonullable index列来代替，如果这都没有，会定义隐藏的主键然后再上面进行聚集。

Primary Key索引中，不仅仅存储了主键字段的数据，还存储了其他字段的数据。全部记录以主键值有序的排列。而Secondary index则是普通的B-Tree索引。仅仅在leaf node里面存放了索引键的相关信息和InnoDB的主键指针。所以，在InnoDB中，使用主键索引来查找速度是相当快的。但是用secondary index来查找的话，需要先根据普通索引找到主键值，再根据主键值和主键索引来找到相应记录。查询两次索引。

FullText索引

虽然fulltext索引也是使用b-tree来存储索引的，但是他并不是通过字段内容的完整匹配，而是通过特定的算法，将字段数据进行分割，分割之后在进行的索引。所以fulltext索引中，真正在b-tree中存放的并不是表结构中的原始数据，而是分词之后的索引数据。

最后是聚集索引的优劣：

clustered index的好处:

1）数据行是按主键顺序存储在一起的，读取少量的磁盘页面就可以把相邻主键的数据读出来。

2）索引和数据都保存在一棵B+ tree中，从索引中读取数据较快。

3）使用覆盖索引的查询可以使用包含在叶子节点的主键值。

clustered index缺点

1）更新clustered index列代价是昂贵的，因为要强制把每个更新的数据行移到新位置。

2）按主键顺序插入新行是一种好方法，否则更新主键或插入到随机位置插入性能开销比较大，数据行移动的时候还可能分页，其实道理和第一条一样。

3）辅助索引叶子节点包含的是主键列，而不是直接的数据行地址，所以辅助索引会相对较大。

4）通过辅助索引的查找先找到的是主键列，然后再在主键索引中进行第二次查找，需要2次查找索引，但是Innodb的自哈希索引功能能减少这种损失。

一个select查询，mysql会先让query optimizer来优化，之后在执行。optimizer根据系统的统计信息来选择出一个针对该查询最优的索引来完成查询。但是有些情况下统计信息不够准确完整，会造成optimizer并没有选择一个最优的索引，而是选择了其他查询效率较低的索引。此时，可以通过使用force index来强制mysql使用我们希望他使用的索引。

select * from group_message  force index(idx_group_message_author_subject) where user_id=3 and author='3' and subject like 'abc%'

可以通过mysqlslap来对mysql进行压力测试。不用force index的时候，和使用的时候，会发现查询时间多了一半。

但是使用索引的同时，也要注意mysql对索引的限制。

1、myisam存储引擎索引键长度综合不能超过1000字节。

2、blob和text类型的列只能创建前缀索引。

3、mysql目前不支持函数索引。

4、使用不等号（!=或者<>）的时候，mysql无法使用索引。

-----------------------------------------------------------分割线---------------------------------------------------------

刚刚结束了morgan的面试。问到了哈希索引和b树索引的选择问题。在一个具体的表上，是选择哈希索引还是b树索引。

参考了一篇文章：

http://database.51cto.com/art/201010/229525.htm

（1）MySQL Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 MySQL Hash索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）MySQL Hash索引无法被用来避免数据的排序操作。

由于 MySQL Hash索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）MySQL Hash索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）MySQL Hash索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）MySQL Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

------------------------------------------------------------------------------------------------------------------------------------------------

索引在使用的过程中有很多需要注意的地方。

《高性能mysql》中有提到。page97页开始，往后看。

其中提到了一点，感觉比较重要。
是这样的：

"the storage engine can not optimize accesses with any columns to the right of the first range condition.”

在一个查询中，如果有几个where条件，比如：

select * from staff where age=34 and name like 'a%' and birth='2012-2-2'，那么，索引如果是(age,name,birth)的话，此次查询是不可能用到birth这个字段的索引的。因为name like 'a%'是一个范围查询。此次查询仅仅使用了前两个字段，birth字段没有使用。

------------------------------------------------------------------------------------

覆盖索引

覆盖索引是一个比较重要的优化措施。具体可以参见这里