MySql索引分析&&比较

MySql理论知识点：

索引

索引是帮助mysql高效获取数据排好序的数据结构，索引数据结构包括二叉树，红黑树，Hash表和B-tree。

二叉树：数据存储为key-value，key是所查询字段的值，value是整个数据对象磁盘所对应的指针。
红黑树：平衡版的二叉树，数据量大时候也不合适。
B-Tree: 通过解决数的深度问题，避免磁盘IO查询。

MySql底层是B+Tree索引结果 (B-tree变种)，非叶子节点存储指针，叶子节点存储data，根节点事先存储与内存之中，搜索更快，避免磁盘IO。

MySql数据库中，针对于MyIsam索引类型，数据文件（MYD）和索引文件（MYI）是分离的（非聚集）; InnoDb索引类型（聚集），聚集索引就是主键索引， 一般都要添加主键索引，否则也会默认生成一个类似RowId的主键索引，主键设置成整型的自增，效率快。

MySql数据库中Hash表索引结构：是针对字段值做hash运算，然后通过hash值去hash表中查询对应的指针，拿到这一行数据。这样的效率固然比B+Tree高，性能消耗也低，hash碰撞也可以忽略，不用的原因就是不支持范围查找。

MySql文件页大小（非叶节点所占用的内存）：

show  GLOBAL status like 'INNODB_page_size';

索引类型

Normal：表示普通索引

Unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

索引方法

主要包括两种方法 B-Tree 和 Hash

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到叶子节点这样多次访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引，但是实际运用到的确实B-Tree偏多为什么呢？主要是Hash索引结果有以下局限性：

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

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

（2）Hash 索引无法被用来做数据排序操作。

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

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

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

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

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

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

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

主键索引和唯一索引的区别

主键约束（PRIMARY KEY）:

1.主键用于唯一的标识表中的每一条记录，可以定义一类或多列为主键。
2.表里面只能有一个主键约束，但可以有多个唯一约束。
3.主键列上没有任何两行具有相同值（即重复值），不允许空（null）。
4.主键可作外键，唯一索引不可。

唯一约束（UNIQUE）:

1.唯一约束用来限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段，一个表上可以防止多个唯一性约束。
2.只要唯一就可以更新。
3.表中任意两行在指定列上都不允许有相同的值，允许空（NULL）。
4.一个表上可以放置多个唯一约束。

有关mysql执行计划explian结果详情说明参考 Explain