原创文章，会不定时更新，转发请标明出处：http://www.cnblogs.com/janehoo/p/5382475.html
TIPS：
1.建立索引，是为了快速检索、排序。
2.索引按就够可以分为：B+树索引、hash索引、全文索引、空间索引
3.B+树索引按数据聚集方式可以分为：普通索引、聚集索引、辅助索引，按索引所包含的内容又可以分为：前缀索引、复合索引（最左前缀索引）
4.myisam和innodb都不支持hash索引，innodb支持自适应hash索引。memory表默认使用hash索引。
5.每个myisam表最大索引数是64。 这可以通过重新编译来改变。每个索引最大的列数是16个。最大的键长度是1000字节。这也可以通过编译来改变。对于键长度超过250字节的情况，一个超过1024字节的的键块被用上
6.前缀索引的长度跟存储引擎有关，对于myisam存储引擎的表，索引的前缀长度最长可以达到1000字节长，而对于innodb的存储引擎的表，索引的前缀最长是767字节。前缀的限制应以字节为单位进行测量，而create table语句中的前缀长度解释为字符数，在为其使用多字节字符集的列指定前缀长度时一定要加以考虑
7.聚族索引。对于innodb存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，按主键顺序保存，如果没有主键，但是有唯一索引，那么就按照唯一索引的顺序保存。如果既没有主键也没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或内部列的顺序访问是最快的，所以innodb的表尽量自己指定主键。当表中同时有几个列是唯一的，会选择第一个出现的唯一索引列作为主键。Innodb表的索引都会保存主键的键值，所以主键尽可能选择较短的数据类型。可以有效的减少索引的磁盘占用，提高索引的缓存效果
索引缺点:
1. 数据内容的变更（增、删、改）都需要修订索引，索引存在而外的维护成本
2. 索引系统需要一个地方来存放，索引存在额外的空间成本
索引创建规范：
1.只给大表建索引；
2.尽量只给更新频率少的字段加索引；
3.只建必要索引；
4.只建索引基数高的索引；
5.合理建立联合索引；
6.长字段建立前缀索引；
7.慎用hash索引；
8.不用外键；
查看索引使用效率:
show global status like 'handler_read%';
........
| Handler_read_first | 261 |
| Handler_read_key | 22542789 |
| Handler_read_last | 19 |
| Handler_read_next | 14863439517 |
| Handler_read_prev | 50741626 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 21018305873 |
.........
handler_read_key值:一行被索引值读的次数,低则说明增加索引得到的性能改善不高
handler_read_rnd_next:在数据文件中读取下一行的请求数,值高说明查询效率低，表索引不正确或写入的查询没有用到索引
Handler_read_rnd_next/Com_select 得出了表扫描比率 如果该值超过4000，就应该查看read_buffer_size,例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！
B+树索引：
B树把它的存储块组织成一棵树。这棵树是平衡的，即从树根到树叶的所有路径都一样长（高度差不会超过一层，否则就不是一棵B+树），同层级的节点间有指针相互链接。通常B树有三层：根、中间层和叶，但也可以是任意多层。B树能自动地保持与数据文件大小相适应的索引层次。对所使用的存储块空间进行管理，使每个块的充满程度在半满与全满之间，这样的索引不再需要溢出块。对应于每个B树索引都有一个参数n，它决定了B树的所有存储块的布局。每个存储块存放n个查找键值和n+ 1个指针。在存储块能容纳n个键和n+ 1个指针的前提下，我们把n取得尽可能大。假定我们的存储块大小为4 0 9 6字节（4K），且整数型键值占4字节，指针占8字节。要是不考虑存储块块头信息所占空间，那么我们希望找到满足4n+ 8 (n+ 1 )≤4096的最大整数值n。这个值是n=340。(注：innodb默认的索引页大小是16k，可以算出n是1364.)我们当时确定每块可容纳示例数据的340个键-指针对。假若一般的块充满度介于最大和最小中间，即一般的块有255（340*0.75）个指针。一个根结点，有255个子结点，有65025个叶结点；在这些叶结点中，我们可以有255的立方，即约1 . 6 6×1 0的7次方个指向记录的指针。也就是说，记录数小于等于1 . 6 6×1 0的7次方 (一千六百万行)文件都可以被3层的B树容纳。（注：在innodb中，低于1/2就会合并，所以如果按(1364/2)的来算，3层的B树在innodb中至少可容纳682的3次方约合 31.72千万行记录的键）
索引基数：
查询优化器会依据索引的基数高低来判断是否选择该索引，索引的基数来源于数据库定时对随机的8个数据页进行采样分析而得。可以通过参数来调整采页数量，采样结果的存储及在什么样的情况下进行采样。
聚集索引：
innodb是索引聚集的表，数据是按照主键的顺序存放,主键和数据存放在同一叶子节点里，所以通过主键查询记录的效率会非常高。
辅助索引：
innodb除聚集索引外的索引都是辅助索引。辅助索引的叶子节点包含的并不是其对应数据页的指针而是对应的主键的键值。通过辅助索引查询需要先遍历辅助索引二叉树，得到对应的主键值，再根据对应的主键值遍历主键二叉树。所以同高度的索引二叉树，通过辅助索引的查询效率会比聚集索引、主键索引、非主键索引低。为了避免通过辅助索引查询，引起大量的聚集索引的随机读取，在查询结果数量非常大的时候，不走辅助索引直接走PRIMARY。但是如果服务器本身的硬盘是支持高随机读写的，那也可以强制（force index）优化器走辅助索引。为了提高辅助索引的查询效率，mysql 5.6引入了MRR技术。遍历辅助索引树时，对得到的结果进行（辅助索引键总大小）/(read_rnd_buffer_size)次主键排序，再对每次排好序的主键进行一次范围扫描，这样可以大大降低聚集索引的随机读取次数。MRR还可以将某些范围查询拆分为多个键值对，进行批量数据查询。通过拆分后的批量查询可以避免对很多非必须页的读取，降低了缓冲池的脏页被刷出的概率。因为辅助索引的叶子节点记录的是对应聚集索引的键值，所以辅助索引维护的成本很低。
ICP：
ICP技术改善的是联合索引查询中，其它列无法使用到联合索引的情况。ICP之前，如果是这样的情况，直接将查询结果返回服务器层，并在服务器层完成对where条件的过滤。ICP之后，where条件列可以直接在联合索引中进行过滤，再根据过滤后的联合索引进行数据页的读取，并将结果返回给服务器层。ICP技术的引进，对于某些范围查询，提高了查询效率，减低服务器层和存储引擎层的网络开销还可以降低innodb缓冲池中的脏页被刷新的概率。
FIC:创建索引的时候不建临时表，只加S锁
在线修改表结构：索引的创建/删除、外键的创建/删除、列的重命名、修改自增长值不会堵塞该表的其它操作
全文索引：
能够利用分词技术等多种算法分析出文本文字中关键词的频率和重要性，然后按一定的算法规则筛选出我们想要的搜索结果。全文索引的实现策略是倒排索引（通过值查找记录），它在辅助表中记录了关键词以及关键词在文档中的位置。全文索引的的变更先会存放在innodb_ft_cache中，等它满了的时候再刷新到辅助表中。指定全局变量innodb_ft_aux_table为要查看的表时，就可以在视图information_schema.innodb_ft_index_table，information_schema.innodb_ft_DELETED中查看分词信息。文档中的DML操作并不删除全文索引中的数据，所以需要手工执行optimize table将已经删除的记录从索引中删除。可以通过stopword列表来控制哪些单词不进入辅助表的关键词。每张表只能有一个全文索引，全文索引可以包含多个列，但是列的字符集和校验规则必须相同。不支持单词界定的语言，如中文等。如果需要对中文文档做全文索引，需要结合sphinx技术。
hash索引：
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
1.如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
2.如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
3.哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
4.哈希索引也不支持多列联合索引的最左匹配规则；
5.在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。