索引的数据结构及其优化策略

为什么使用B+树而不是红黑树作为索引？
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
数据库系统的设计者巧妙的利用了磁盘预读原理，将一个结点的大小设为等于一个页面，这样每个结点只需要一次I/O就可以完全载入。红黑树实际上是一颗二叉树，即使逻辑上很近的结点（父子结点）物理上可能离得很远，无法利用局部性原理。而且如果存储相同数量的数据，红黑树的高度要比B+数高的多，所以查找效率明显比B+Tree差得多。
虽然B树解决了磁盘IO问题，但是B树的非叶子节点也存储着数据，所以没有解决元素遍历效率低下的问题，B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

一般在数据库系统或文件系统中使用的B+Tree结构都在经典 B+Tree的基础上进行了优化，增加了顺序访问指针，每个叶子结点增加一个指向相邻叶子结点的指针，目的是为了提高区间访问的性能只需顺着结点和指针顺序遍历就可以一次性访问到所有数据结点，极大提到了区间查询效率。

索引优化：
联合索引及最左前缀原理
联合索引（复合索引）
相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。它的原理也很简单，比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，因此，联合索引的特点就是：
第一个字段一定是有序的当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B值得所有C字段
是有序排列的
| A | B | C |
| 1 | 1 | 3 |
| 1 | 2 | 2 |
| 1 | 4 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 6 |
| 2 | 5 | 5 |
其实联合索引的查找就跟查字典是一样的，先根据第一个字母查，然后再根据第二个字母查，或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。
前缀索引
前缀索引就是 用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
一般来说以下情况可以使用前缀索引：
字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
字符串本身可能比较长，而且前几个字符就开始不相同。

比如我们对中国人的姓名使用前缀索引就没啥意义，
因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开
头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。
相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是
一个可以使用前缀索引的字段。
前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，
而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候
前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。
MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。
索引优化策略
最左前缀匹配原则，上面讲到了
1.主键外检一定要建索引对 where,on,group by,order by 中出现的列使用索引尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
2.对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
3.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time =unix_timestamp(’2014-05-29’);
4.为较长的字符串使用前缀索引尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
5.对于like查询，”%”不要放在前面。
SELECT * FROM houdunwang WHERE uname LIKE' 后盾 %' --  走索引
SELECT * FROM houdunwang WHERE uname LIKE "% 后盾 %" --  不走索引
6.查询where条件数据类型不匹配也无法使用索引
字符串与数字比较不使用索引;
CREATE TABLE a ( a char(10));
EXPLAIN SELECT * FROM a WHERE a ="1" – 走索引
EXPLAIN SELECT * FROM  a WHERE  a =1 – 不走索引
7.正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因。

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？
索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好.例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。
什么情况下需要创建索引?
1.哪些情况需要创建索引
　　1).主键自动建立唯一索引
　　2).频繁作为查询查询条件的字段应该创建索引
　　3).查询中与其它表关联的字段，外键关系建立索引
　　4).频繁更新的字段不适合创建索引
　　5).where条件里用不到的字段不创建索引
　　6).单键/组合索引的选择问题(在高并发下倾向创建组合索引)
　　7).查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
　　8).查询中统计或者分组字段
2.哪些情况不要创建索引
　　1).表记录太少
　　2).经常增删改的表(因为不仅要保存数据，还要保存一下索引文件)
　　3).数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。