索引优化是对查询性能优化最有效的手段
一、索引的类型
1.B-Tree索引
B-Tree索引能加快数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针像下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层叶子节点，这些指针实际上定义了子节点页的值的上限和下限。最终存储引擎要么找到要查询的值，要么是该值不存在。叶子节点比较特殊，他们的指针指向的是被索引的数据，而不是其他的节点页。B-tree索引列是顺序组织存储的，所以很适合查找范围数据。
假如有如下数据表：
create table people (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m','f') not null,
key(last_name,first_name,dob)
);
对于每一行数据，索引中包含了last_name,first_name和dob列的值。
注意：索引对多个值进行牌排序的依据、索引对多个值进行排序的依据是 create table语句中的定义索引的时列的顺序。
B-tree的数据结构如下图所示：
B-tree索引的一些限制：
1).如果不是按照索引的最左列开始查找，则无法使用索引。无法直接直接查找名字为Bill的数据。
2).不能跳过索引中的列.比如前面索引无法查找姓为 Smith 出生日期为某个特定日期的人。如果不指定名 fisrt_name mysql只能查询特定的某以列。
3).如果查询中有某个列的范围查询，则其右面所有列都无法使用索引优化查找。列如：有查询 select * from people where last_name=' Smith ' AND first_name like '%J%' AND dob='1976-12-23',这个查询只能用于查询索引的前两列，因为like 是一个范围条件。如果查询范围列值有限，那么可以通过使用多个条件来代替。
2.哈希索引
哈希索引基于哈希表实现的，只有精确地匹配索引所有的列才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，而且不同的键值计算的哈希码也是不一样的。哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在mysql中 只有Memory显示的支持哈希索引，也是他的默认索引，还支持B-tree和非唯一哈希索引。如果出现多个列的哈希值相同，索引会以链表的方式存放多个指针到同一个哈希条目中。
因为索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。
哈希索引的限制：
01).哈希索引只包含哈希值和行地址，而不存储字段值，所以不能使用索引的值来避免读取行。因为对行的读取非常快，所以这一点对性能的影响不明显。
02).哈希索引并不是按照索引的值顺序来存储的，所以无法用于排序。
03)哈希索引也不支持索引列的匹配查找。因为哈希索引始终是使用索引列的全部内容来计算哈希值的。列如，在数据列(A,B)上建立哈希索引，如果查询只有数据列A,则无法使用该索引。
04)哈希索引只支持等值比较查询，包括=、IN()、<=>。也不支持任何范围查询。
05)访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列却有相同的哈希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐个进行比较，直至找到所有的符合条件的行。
06)如果哈希冲突很多的话，一些索引维护操作的代价会很高。列如，如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值得链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
因为这些限制，哈希索引只适用于某些特定的场合，而且一旦适合这个索引，则他带来的性能提升就会很显著。
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用的非常频频繁的时候，它会在内存中基于B-Tree索引之上再创建一个索引，这样就让B-Tree索引具备哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的，内部的行为，用户无法控制或者配置，不过有必要的话，完全可以关闭该功能。
在B-Tree思路上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要的就是在查询的where字句中手动指定使用哈希函数。
举列如下：
创建如下表：
create table pseudohash(
id int UNSIGNED not null auto_increment,
url varchar(255) not null,
url_crc int UNSIGNED not null DEFAULT 0,
PRIMARY KEY(id)
);
然后在创建触发器，在数据进行insert或者update的时候，进行哈希索引列的维护。
create trigger pseudohash_crc_ins BEFORE INSERT on pseudohash for EACH ROW BEGIN
set NEW.url_crc = crc32(NEW.url);
end;
create trigger pseudohash_crc_upd BEFORE UPDATE on pseudohash for each row BEGIN
set NEW.url_crc = crc32(NEW.url);
end;
然后检验一下触发器的工作；
insert into pseudohash (url) VALUES ('http://www.mysql.com');
select * from pseudohash;
update pseudohash set url = 'http://www.mysql.com/' where id=1;
select * from pseudohash;
使用哈希索引查询如下：在where字句中手动指定使用哈希函数。
select id,url from pseudohash where url = 'http://www.mysql.com/' and url_crc = CRC32("http://www.mysql.com/")
如果采用这种方式，请不要采用SHA1()和MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量的空间，比较时候也会比较慢。SHA1()和MD5()是强加密函数，设计目标是最大限度的消除冲突，但是这里并没有那么高的要求。
如果说数据非常大的时候，使用CRC32就会出现大量的哈希冲突，可以使用MD5()作为哈希函数。要避免冲突问题，必须在where条件中带入哈希值和对应的列值。
二、高性能的索引策略
1.独立的列指的是索引列不能是表达式的一部分，也不能是函数的参数。
列如:下面这个就无法使用actor_id列的索引
select * from actor where actor_id+1 = 5;
2.前缀索引
有时候需要索引很长的字符串列，这会让索引变得大而且慢。一个策略是前面提到的哈希索引，但是这么做还不够，通常还可以索引部分字符，这样就可以大大节约索引空间。这个需要注意要选择足够长的前缀以保证较高的选择性，同时又不能太长，以便于节约空间。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。
3.聚簇索引
平时习惯逛图书馆的童鞋可能比较清楚，如果你要去图书馆借一本书，最开始是去电脑里面查书名然后根据书名来定位藏书在那个区，哪个书柜，哪一行，第多少本。。。清晰明确，一目了然，因为藏书的结构与图书室的位置，书架的顺序，书本的摆放顺序与书籍的编号都是从大到小一致的顺序摆放的，所以很容易找到。比如，你的目标藏书在C区2柜3排5仓，那么你走到B区你就很快知道前面就快到了C区了，你直接奔着2柜区就能找到了。 这就是雷同于聚簇索引的功效了，聚簇索引，实际存储的循序结构与数据存储的物理机构是一致的，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引，当然有人说我不想拿主键作为聚簇索引，我需要用其他字段作为索引，当然这也是可以的，这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键，这样就木有问题啦。
总而言之，聚簇索引是顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条；
聚簇索引的优点：
01）可以把相关的数据保存在一起。列如：实现电子邮件的时候，可以根据用户ID来聚集数据，这样就可以从磁盘读取少量的数据也就能获取到用户的所有的全部邮件。如果没有实现聚簇索引，则每一封邮件都需要导致一次磁盘IO。
数据访问速度快。
3.在innoDB中按照主键顺序插入行
最好避免随机的(不连续值且值得分布范围非常大)聚簇索引，特别是对于I/O密集型的应用。例如:从性能的角度考虑，使用UUID来作为聚簇索引就会非常的槽糕，他使得聚簇索引的插入变得非常随机。
下面进行演示：
创建两个表，第一个表的主键使用整数ID,第二张表使用UUID作为主键。对这两个表进行数据的插入测试，发现使用UUID作为主键的表插入行不仅花费时间更长，而且索引占用的空间也更大。当向第一张表插入数据的时候，索引发生的变化如图：
而当向第二张表插入数据的时候所发生的变化，因为新行的主键值不一定比之前插入的大，索引InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新行寻找合适的位置--通常是已有数据的中间位置--并进行空间分配。这会增加很多的额外的工作，而且导致数据的分布不顾优化。
这样做的缺点：
写入的目标页可能已经刷新到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这样会导致大量的随机I/O.
因为写入时乱序的，InnoDB不得不频繁的做分页操作，以便为新的行分配空间。页分裂会导致移动大量的数据，一次插入最少需要修改三个页而不是一个页。
4.压缩索引
MyISam使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下面可以极大的提高性能。InnoDB使用压缩索引。MyISAM压缩每个索引快的方法是：先完全保存索引快中的每一个值，然后将其他值和第一个值比较得到前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引快中的第一个值是"perform"，第二个值是"performance"，那么第二个值得前缀索引压缩后存储的是类似"7,ance"这样的形式。
压缩快使用更小的空间，代价是某些操作会变得更慢，因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引快使用二分法查找而只能从头开始查询。正序扫描还可以，如果是倒序扫面则会变慢。所有的在块中查找某一行的操作平均都需要扫描半个索引块。
5.冗余和重复索引
MYSQL允许在相同的列上面创建多个索引，无论是有意的还是故意的。MYSQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个进行考虑，这个会影响性能。
重复索引在指的是在相同的列上面建立相同类型的索引，不同类型的索引不是重复索引。
列如：
create table test(
id int not null primary key,
a int not null,
b int not null,
UNIQUE(id),
INDEX(id)
)ENGINE=InnoDB
一些经验不足的用户可能加上主键，唯一约束和加上索引，以供查询。事实上MYSQL的主键和唯一约束都是通过索引实现的，索引在这里就存在了三个重复的索引。
冗余索引和重复索引有一些不同。如果创建了索引(A,B)，在创建索引(A)就是冗余索引。因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当索引(A)来使用(这种冗余索引是针对于B-Tree索引来说的)。但是如果在创建索引(B,A)就不是冗余索引，索引(B)也不是，因为不是左前缀索引。大多数情况下面都不要冗余索引，应该尽量扩展已有的索引而不是创建索引。但是也有需要冗余索引的情况出现，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的性能。
