mysql索引原理详解

一、什么是所有，为什么用索引

索引就和我们书的目录，字典的查找一样，帮助我们快速查找内容的一种数据结构。
MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。

二、索引的原理，树的演化

Mysql默认使用b+tree索引。下面看下索引的演化。

1、 二叉排序树

优点：将数据顺序排列，二分法查找数据，时间复杂度O(logn)
缺点：如果树不平衡，出现歪脖子树，时间复杂度O(n)

2、 AVL tree，平衡二叉树

优点：解决歪脖子树问题，插入删除时通过旋转来使树平衡
缺点：二叉树，层级很多，IO次数过多。
旋转分为4种情况：LL，RR，LR，RL

对于子树根节点，左右2个子树的深度差大于1，那么不是平衡二叉树。需要进行旋转操作，注意：旋转不是根节点，是最小的子树。
LL:

RR:

LR:

RL:

3、B-tree，平衡多路树

Mysql以页为单位读取磁盘。在同一个磁盘中的顺序数据能够帮助定位索引，减少磁盘读取次数。
优点：为了解决磁盘读写过多问题，对于一个m阶的树，一个节点可以有m个子树，
缺点：针对范围查询不友好。

4、 B+tree，优化平衡多路树

非叶子节点不存储数据，数据都在叶子节点。并且每个叶子节点和相邻的都有链接。
优点：对范围查询和分页友好

5、Innodb和MyISAM 对b+tree的实现：

MyISAM 引擎索引实现：


• 使用B+Tree作为索引结构
• 叶节点的data域存放的是数据记录的地址
• 主索引(图1)和辅助索引（图2）在结构上没 有任何区别,只是主索引要求key是唯一的，而辅助索引的key可以重复。(“非聚集”)
InnoDB引擎索引实现：


• 使用B+Tree作为索引结构
• 数据文件本身就是索引文件（聚集/聚簇索引）
a. 表数据文件本身就是按B+Tree组织的
一个索引结构这棵树的叶节点data域
保存了完整的数据记录
b. 索引的key是数据表的主键
InnoDB表数据文件本身就是主索引
• InnoDB要求表必须有主键
如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
• InnoDB的所有辅助索引都引用主键作为data域

三、 日常使用索引的注意事项

1、不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大
2、 非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效 类型最好是数字型 自增
Mysql自增id做索引和uuid做索引比较：
自增id：
优点：索引空间小，查询快，顺序插入时，效率高；缺点：不安全，可以被猜出有效信息
UUID：
优点：不仅仅是表唯一，而且是库唯一，再做分表分库时，表数据割接合并时，有天然优势
取中间：使用自增id做主键索引，uuid做外键索引

3、最左原则
建立联合索引时，最左边的索引是可以复用的，比如建立索引（a,b,c）则条件是a；a、b；a、b、c时能够使用索引
4、选择区别度高的字段做索引
5、索引上面不能有函数计算
6、like前缀
7、order by group by字段，时，select也要选择字段
Order by；group by要和where结合使用索引
8、索引覆盖，只访问索引字段的查询

参考文档：
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
https://blog.csdn.net/u013235478/article/details/50625677