mysql索引

一、索引简介

1:关键点:

索引是在存储引擎层实现的而不是在服务器层。

InnoDB和MYISAM都是用B+树作为索引存储结构(但是叶子节点存储的数据不同).

 

InnoDB 主键使用的是聚簇索引;

MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。

 

InnoDB二级索引的叶子节点关联的原表的主键ID

MYISAM不论主键还是二级索引的叶子节点关联的都是原表的行记录地址指针

 

InnoDB用原数据的格式进行存储。

MyISAM使用前缀压缩技术存储表记录;

 

2:索引的优点

 索引大大减少了服务器查询数据需要扫描的数据量。

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

 索引可以将随机IO转变成顺序IO。

 

3:覆盖索引

查找的目标数据(colum1)包含在索引中,如建立idx_colum1_colum2.

select colum1 from table where colum1 = ? and colum2 > ?

通过查询索引就能确定最终的数据，不用再利用叶子节点中存储的主键值去原表查询对应的数据。覆盖索引的性能是极高的。

 

4:索引分类(存储结构层次)

FULLTEXT,HASH,BTREE,RTREE(不常用)

FULLTEXT:

全文索引的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题.

只适用于MYISAM引擎,只能在char,varchar,text上建立全文索引

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 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

BTREE:

1:B-Tree上所有的值都按照顺序存储，并且每个叶子节点到根节点的距离相同。

2:BTREE在MyISAM里的形式和Innodb稍有不同:

a.在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息.

b.在MyISAM里，主键和二级索引并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息(数据行指针).

3:可使用B-Tree索引的查询：

.全值查询:对索引中的所有列进行匹配。

.匹配最左前缀

.匹配列前缀：只匹配某一列的开头部分。

.匹配范围查询

.精确匹配最左前缀，并范围匹配下一列

.只访问索引的查询：在查询中只需要访问索引，而不需要访问表数据行

引用:

https://www.cnblogs.com/yuan-shuai/p/3225417.html

http://blog.csdn.net/light_language/article/details/72650799?locationNum=9&fps=1

重点是innoDB表如何建立hash索引,及其用处

在B-Tree基础上创建一个伪hash索引，还是在使用B-Tree进行查找，只是，使用hash值而不是键本身进行索引查找，只需要在查询的where子句中手动指定使用hash函数。适用与对键是很长的字符串的情景下，使用hash索引可以将比较慢的字符串索引优化为快速的整数索引。

 

 

二、索引原理

引用:

http://blog.csdn.net/risingsun001/article/details/44408129  //索引概述

理解:

索引的原理:通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

B树:

1:一个节点代表一个数据块,可以存储多个值

2:所有的真实数据都是存储在叶子节点上的,分支节点数据只用来导向叶子节点的指针(其值在叶子节点不一定存在)

3:除非数据已经填满，否则不会增加新的层。也就是说，B树追求"层"越少越好

4:3层的b+树可以表示上百万的数据（如果换成二叉查找树，则需要20层！）

 

Mysql复合索引的建立和查找过程(原理剖析)

Ep:tab(name,age,sex), 查找(张三,20,F);

建立过程:

当创建一个mysql复合索引时,是一次性建立多个B+树完成的.

首先建立以name列为索引项的B+树,在B+树的叶子节点处再有一个指针指向这个name值对应的age列表建立的B+树(就是通过这个name值查找出的所有age建立的B+树);

age B+树的叶子节点再指向name+age等值查询出的所有sex建立的B+树(sex可能没必要,只有两个值这里是针对通用情况来说),最后走到sex B+树的叶子节点找到对应的主键ID,哪主键ID回原表的查询对应记录(聚簇索引)