解析MYSQL索引的底层原理~

在介绍MYSQL所索引的底层原理之前，首先简单说说为什么MYSQL需要一个主键

主键：表中每一行都应该有可以唯一标识自己的一列（或一组列）。一个顾客可以使用顾客编号列，而订单可以使用订单ID，雇员可以使用雇员ID 或 雇员社会保险号。

主键（primary key） 一列（或一组列），其值能够唯一区分表中的每个行。 唯一标识表中每行的这个列（或这组列）称为主键。没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只设计相关的行。

虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表有一个主键，以便于以后数据操纵和管理。

表中的任何列都可以作为主键，只要它满足以下条件：

1、任何两行都不具有相同的主键值 2、每个行都必须具有一个主键值（主键列不允许NULL值）

总之：不应该使用一个具有意义的column（id 本身并不保存表 有意义信息） 作为主键，并且一个表必须要有一个主键，为方便扩展、松耦合，高可用的系统做铺垫。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

一、B-树

m阶B-Tree满足以下条件：
1、每个节点至多可以拥有m棵子树。
2、根节点，只有至少有2个节点（要么极端情况，就是一棵树就一个根节点，单细胞生物，即是根，也是叶，也是树)。
3、非根非叶的节点至少有的Ceil(m/2)个子树(Ceil表示向上取整，如5阶B树，每个节点至少有3个子树，也就是至少有3个叉)。
4、非叶节点中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示该节点中保存的关键字个数，K为关键字且Ki<Ki+1，A为指向子树根节点的指针。
5、从根到叶子的每一条路径都有相同的长度（叶子节点在相同的层）

B-树的特性：

1、关键字集合分布在整颗树中；
2、任何一个关键字出现且只出现在一个节点中；
3、每个节点存储date和key；
4、搜索有可能在非叶子节点结束；
5、一个节点中的key从左到右非递减排列；
6、所有叶节点具有相同的深度，等于树高h

二、B+树

B+Tree与B-Tree的差异在于：
1、B+Tree非叶子节点不存储data，只存储key；
2、所有的关键字全部存储在叶子节点上；
3、每个叶子节点含有一个指向相邻叶子节点的指针，带顺序访问指针的B+树提高了区间查找能力；
4、非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字；

三、B-树和B+树的性能分析

依据：使用磁盘I/O次数评价索引结构的优劣
主存和磁盘以页为单位交换数据，将一个节点的大小设为等于一个页，因此每个节点只需一次I/O就可以完全载入。

根据B树的定义，可知检索一次最多需要访问h个节点，渐进复杂度:O(h)=O(logdN)
 dmax=floor(pagesize/(keysize+datasize+pointsize))

一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3，3层可存大约一百万数据）
B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存）
B+Tree内节点不含data域，因此出度d更大，则h更小，I/O次数少，效率更高，故B+Tree更适合外存索引。

四、为什么MYSQL最终使用B+数而不是B-树，他们在存储结构上有什么不同？
1、B-树的每一个节点，存储的是关键字和对应的数据地址，而B+树的非叶子节点存储关键字，
因此B+树的每一个非叶子节点存储的关键字数量远远多于B-树，B+树叶子节点存储关键字和数据
因此：从树的高度来说，B+树的高度要小于B-树，使用磁盘的次数少
2、B-树由于每一个节点存储关键字和数据，因此离根节点进的数据查询比较快，
离根节点比较远的数据，查询就比较慢
B+树上所有的数据都存储在叶子节点上，因此B+树查询关键字，找到对应数据的时间上是比较均匀的
没有快慢之分
3、在B-树如果做区间查找，遍历的节点比较多的，B+树所有的叶子节点被连接成有序链表结构，
因此做整表遍历和区间查找是非常容易的

五、MYSQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

innodb和myisam的区别

	InnoDB	MyISAM
简介	由Innobase Oy公司开发。 支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。	默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。
使用场景	在线事务处理(OLTP)型应用	在线分析处理(OLAP) 型应用
锁差异	Innodb支持事务和行级锁，是innodb的最大特色。 事务的ACID属性，并发事务带来的几个问题：更新丢失，脏读，不可重复读，幻读。 事务隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)	myisam只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。
数据库文件差异	innodb属于索引组织表 innodb有两种存储方式，共享表空间存储和多表空间存储 两种存储方式的表结构和myisam一样，以表名开头，扩展名是.frm。 如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。 如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。	myisam属于堆表 myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。 .frm 用于存储表的定义 .MYD 用于存放数据 .MYI 用于存放表索引 myisam表还支持三种不同的存储格式： 静态表(默认，但是注意数据末尾不能有空格，会被去掉) 动态表 压缩表
索引差异	1、关于自动增长 myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。 innodb引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。 2、关于主键 myisam允许没有任何索引和主键的表存在， myisam的索引都是保存行的地址。 innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见) innodb的数据是主索引的一部分，附加索引保存的是主索引的值。 3、关于count()函数 myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值 innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre       条件后，myisam和innodb处理的方式都一样。 4、全文索引 myisam支持 FULLTEXT类型的全文索引 innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx   是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询） 5、delete from table 使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最       好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限) 6、索引保存位置 myisam的索引以表名+.MYI文件分别保存。 innodb的索引和数据一起保存在表空间里。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一棵B+树，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，上图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

设计表索引的时候，需要遵循以下的设计原则：
1、给区分度高的字段创建索引 eg：学号、省份证号
2、给经常需要排序，分组和多表联合操作的字段创建索引
3、经常作为查询条件的字段创建索引
4、索引的数据不宜过多
5、使用数据量少的索引（如前缀索引，主要针对字符串索引，字符串类型尽量创建前缀索引）
6、对于多列索引，优先指定最左边的列集
7、删除不在使用或者很少使用的索引