MySQL--IndexAnalysis

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据库表所有记录的引用指针。

1聚集索引 与 非聚集索引
聚集索引：
数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
就比如，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，每个字存放的页码就是我们的数据物理地址，我们如果要查询一个字，我们只需要查询该字对应在新华字典拼音目录对应的页码就可以了，就可以查询到对应的子所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字顺序A-Z也是一样的，如果中文出来一个字以B开头，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，

如图：第一列的地址标识该行数据在磁盘中的物理地址，后面三列才是我们sql里面的表里面的列，其中id是主键，建立了聚集索引。数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且物理排列方式与聚集索引的顺序相同，所以也就能建立一个聚集索引了。
SqlServer默认主键为聚集索引，也可以指定为非聚集索引，MySQL主键就是聚集索引。

Finding Rows in a Clustered Index:

从上图可看，聚集索引的好处，索引的叶子节点就是对应的数据节点（MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了一个唯一约束，其他没有区别），可以直接获取到对应的全部列数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询，索引在查询方面，聚集索引的速度更加具有优势

非聚集索引：
该索引中的逻辑顺序与磁盘上的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，索引分成普通索引，唯一索引，全文索引，如果非要把聚集索引类比现实生活中的东西，聚集索引就像新华字段里面的偏旁字典，他结构顺序与实际存放顺序不一定一致。

Finding Rows in a Heap with a Nonclustered index.

非聚集索引叶节点任然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次查询，查询节点上对应的数据行的数据。

探讨非聚集索引的二次查询问题：
如下表：

聚集索引clustered index(id), 非聚集索引index(username)。
使用一下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就就可以查询到数据列的数据。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是如果使用一下语句进行查询，就需要二次的查询去获取原始数据行的score：

select username, score from t1 where username = '小明'

sql server 如何处理 二次查询问题，sqlServer有自动索引优化：

但MySqL里面就算表里的数据量少且查询了非键列，也不会使用聚集索引去全索引扫描，但如果强制使用聚集索引去查询，性能反而比非聚集索引查询要查。这就是两种sql数据库的不同之处。

特别注意： 非聚集索引其实叶子节点除了会存储索引覆盖的数据，也会存放聚集索引所覆盖的列数据。

如何解决二次查询问题：
建立两列以上的索引，即查询复合索引里面的列的数据而不需要进行回表而喳喳寻，如index（col1,col2），如下语句：

select col1, col2 from t1 where col1 = '213';

要注意符合索引要满足做左侧原则，也就是查询时候如果where条件里面没有最左侧的一到多列，索引就不会起作用。

左侧原则：
建立组合索引，相当于分别建立了下面两组组合索引：

–title,time

–title

//使用到上面的索引
select * from article where title= '测试' and time =123123333；
select * from article where title='测试'；
//不适用上面的索引
select * from article where title= '测试'；

MySQL 几种索引类型的区别以及使用情况：
FullText(全文索引)：
目前只有MyISAM引擎支持，在5.6之后的innodb引擎也支持fulltext索引，可以在create table，alter table，create index 使用，
不过只有在char，varchar，text列上可以创建全文索引，值得一提，在数据量较大的时候，现将数据放入一个没有全局索引的表中，然后在用create index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的数据快很多。

全局索引不是和MyISQM一起诞生的，它的数显是为了解决where name like “%word%”这类针对文本的模糊查询效率较低的问题，在没有全文索引之前，这样一个查询语句是要进行遍历数据库表的操作的，在数据量较大时，及其耗时没有异步IO处理，进程将被挟持，很浪费时间。

全文索引的使用方法并不复杂：

创建ALTER TABLE table ADD INDEX FULLINDEX USING FULLTEXT(cname1[,cname2…]);

使用SELECT * FROM table WHERE MATCH(cname1[,cname2…]) AGAINST (‘word’ MODE );

其中， MODE为搜寻方式（IN BOOLEAN MODE ，IN NATURAL LANGUAGE MODE ，IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION）。

关于这三种搜寻方式，愚安在这里也不多做交代，简单地说，就是，布尔模式，允许word里含一些特殊字符用于标记一些具体的要求，如+表示一定要有，-表示一定没有，*表示通用匹配符，是不是想起了正则，类似吧；自然语言模式，就是简单的单词匹配；含表达式的自然语言模式，就是先用自然语言模式处理，对返回的结果，再进行表达式匹配。

fulltext索引也是按照分词原理建立索引的，西文中，大部分为字母文字，分词可以很方便的按照空格进行分割。mysql中文分词插件mysqlcft，可以对中文进行分词。

为某几列 建立hash索引，就会利用这一列or几列的值通过一定的算法计算出一个hash值，对应一行or几行的数据。
由于hash索引可以一次定位，但同样需要树形索引：
1 hash索引仅仅能满足 = in <=>查询，不能使用范围查询，
由于hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值过滤，不能基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

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

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

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

5Hash索引遇到大量的Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录质证信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能低下。

4,5 补充：
为某一列or某几列创建hash索引时（memory引擎显式的支持这种索引），会在硬盘上申城类似如下的文件：

hash值即为通过特定算法由指定列数据计算出来，磁盘地址即为所在数据行存储在硬盘上的地址（也有可能是其他存储地址，其实memory 会将hash表导入内存）
进行where age=18 时，会将28 通过相同的算法计算出一个hash值==》在hash表对应的存储地址==》根据存储地址取到数据。

所以，每次查询时候，都要遍历hash表，直到找到对应的hash值，如4，数据量大了之后，hash表会变得庞大起来，性能下降，遍历耗时增加 如 5

BTREE
btree索引就是一种将索引值按一定的算法，存入一个树形的数据结构中
BTREE在MyISAM里的形式和Innodb稍有不同

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

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

RTREE

RTREE在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找.

小结：
（1）对于BTREE这种Mysql默认的索引类型，具有普遍的适用性

（2）由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法，至少愚安我是经常这么做的。

（3）对于一些搜索引擎级别的应用来说，FULLTEXT同样不是一个好的处理方法，Mysql的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。真要碰到这种问题，Apache的Lucene或许是你的选择。

（4）正是因为hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。如mysql数据库的内存版本Memsql，使用量很广泛的缓存工具Mencached，NoSql数据库redis等，都使用了hash索引这种形式。当然，不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。

（5）至于RTREE，愚安我至今还没有使用过，它具体怎么样，我就不知道了。有RTREE使用经历的同学，到时可以交流下！

（6）唯一索引，允许有null值。