【实现】mysql索引
innoDB B+树的存储结构结构 决定了B-Tree索引支持一下查询类型：
key(last_name,firset_name,job)
1.全值匹配
和索引中的所有列进行匹配：查找姓l 名cc 职业工程师的人
2.匹配最左列
可查找所有姓为l的人，即用索引的第一列
3.匹配列前缀
查找所有以J开头的姓的人。这里也只使用了索引的第一列
4.匹配范围值
查找姓名在Allen和Barrymore之间的人。这里只使用了索引的第一列
5.精确匹配某一列并范围匹配另一列
第一列last_name精确匹配，第二列firt_name范围匹配
6.只访问索引的查询
即查询只需访问索引，而无需访问数据行
因为索引树中的节点是有序的，所以出了按值查找之外，索引还可以用于查询中的order by操作，一般来说如果b—Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序，所以，如果order by子句满足前面列出的几种查询类型。则这个索引也可以满足对应的排序需求
限制：
1.如果不是按照索引的最左列开始查找，则无法使用索引
2.不能跳过索引中的列
3.如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找
其他索引类型：
- 哈希索引：只有精确匹配索引所有列的查询才有效。每一行对应一个hashcode存在表中。无法用于排序，只支持等值比较(=,in,<=>).innodb自适应hash索引，不能手动指定，可以新增hash列，不过要自己维护。memory和NDB支持
- 空间数据索引(R-Tree)(MyISAM)，用作地理数据存储。
- 全文索引：查找的是文本中的关键词，而不是直接比较索引中的值。更类似于搜索引擎做的事情，而不是简单的where条件匹配
innoDB引擎还有一个特殊的功能叫做“自适应哈希索引”当InnoDB注意到某些索引值被使用得非常频繁时，他会在内存中基于B-Tree索引之上再创建一个哈希索引，可以进行快速的哈希查找。
不支持哈希索引的存储引擎伪哈希：在查询的where子句中手动指定哈希函数。场景：存储URL。select id from url where url='http://www.mysql.com' and url_cc=CRC32("http://www.mysql.com");
索引优点：
1.大大减少服务器需要扫描的数据量
2.帮助服务器避免排序和临时表
3.将随机I/O变为顺序I/O
当数据量非常庞大时，分区和块级别元数据技术代替索引。
索引策略：
1.独立的列：索引列不能是表达式的一部分，也不能是函数的参数
2.前缀索引和索引选择性：
选择性：不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高。因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
冗余一个使选择性与原选择性相近的列作为“前缀索引”，前缀索引是一种能使索引更小，更快的有效方法，但另一方面也有其缺点：mysql无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描。
3.多列索引：
select film_id,actor_id from film_actor where actor_id=1 or film_id=1
在mysql5.0及以后中，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：or条件的联合(union),and条件的相交(intersection),组合前两种情况的联合及相交。通过explain的extra列可以看到这点。
索引合并策略有时候是一种优化结果，但实际上更多时候说明了表上的索引建得很糟糕。
如果在explain中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的，也可以通过参数optimizer_switch来关闭索引合并功能，也可以使用ignore index提示让优化器忽略掉某些索引。
4.选择合适的索引列顺序：
对于如何选择索引的列顺序有一个经验法则（也没有想象的重要，可以作为思路之一）：1.避免随机I/O和排序。2.将选择性最高的列放到索引最前列（不考虑排序和分组时）
聚簇索引：
innoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
InnoDB通过主键聚集数据。
缺点：二级索引(非聚簇索引)访问需要两次索引查找，而不是一次。
myISAM没有聚簇索引，其主键索引和其他二级索引一样 叶子结点直接存放的是指向行的指针，而innodb则是主键的值，innodb的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，innoDB在移动行时无需更新二级索引中的这个“指针”。
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。因为新行的主键值不一定比之前插入的要大，所以innoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找何时的位置-通常是已有数据的中间位置--并且分配空间。这回增加很多额外的工作，并导致数据分布不够优化：
1.写入的目标页可能已经刷到磁盘并从缓存中移除，或是还没有被加载到缓存中，innnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O
2.因为写入是乱序的，InnoDB不得不频繁地做也分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
3。由于频繁的页分裂，页会变得稀疏并被不规则地填充。所以最终数据会有碎片。
随意使用innodb时应尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。
覆盖索引：
不是所有类型的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。
当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。
关于执行计划中extra 比较好的讨论。
