MySQL索引实现

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构
索引的本质：索引是数据结构

![$GZW0LE36NN15G7MYT$MNX.jpg
在MySQL中 索引属于存储引擎级别的概念 不同存储引擎对索引的实现方式是不同的
这里主要讨论 MyISAM 和 InnoDB 两个存储引擎的索引实现方式

MyISAM索引实现

MyISAM索引文件(.MYI)和数据文件(.MYD)是分离的 索引文件仅保存数据记录的地址
MyISAM的索引方式也叫做“非聚集”的** 之所以这么称呼是为了与InnoDB的聚集索引区分**

主键索引

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

如上表一共有三列 假设我们以Col1为主键 则上图是一个MyISAM表的主索引（Primary key）示意
可以看出MyISAM的索引文件仅仅保存数据记录的地址

辅助索引（Secondary key）

在MyISAM中 主索引和辅助索引（Secondary key）在结构上没有任何区别
只是主索引要求key是唯一的 而辅助索引的key可以重复
如果我们在Col2上建立一个辅助索引 则此索引的结构如下图所示：

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

InnoDB索引实现

InnoDB也使用B+Tree作为索引结构 但具体实现方式却与MyISAM截然不同
第一个重大区别是InnoDB的数据文件本身就是索引文件
选择InnoDB执行引擎创建表：

create table teacher(
    tid int(10) primary key,
    tname varchar(20),
    tsex varchar(4),
    tage int(3)
)engine=InnoDB character set utf8;
辅助索引的创建
ALTER TABLE teacher ADD INDEX idx_t_nameSexAge (TNAME, TSEX,TAGE);

主键索引

MyISAM索引文件和数据文件是分离的 索引文件仅保存数据记录的地址
而在InnoDB中 表数据文件本身就是按B+Tree组织的一个索引结构 这棵树的叶节点data域保存了完整的数据记录
这个索引的key是数据表的主键 因此InnoDB表数据文件本身就是主索引（.ibd后缀）

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

InnoDB的辅助索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址 辅助索引是非聚集索引
换句话说 InnoDB的所有辅助索引都引用主键作为data域
例如 下图为定义在Col3上的一个辅助索引：

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

InnoDB索引和MyISAM索引的区别：

1）主索引的区别
InnoDB的数据文件本身就是索引文件 而MyISAM的索引和数据是分开的
2）辅助索引的区别
InnoDB的辅助索引data域存储相应记录主键的值而不是地址 而MyISAM的辅助索引和主索引没有多大区别

总结

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助

• InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引
• MyISAM 不支持事务、也不支持外键，优势是访问的速度快。对事务的完整性没有要求、以 SELECT 和 INSERT 为主的应用可以使用这个存储引擎
• MySQL 5.6 以前的版本，InnoDB 不支持全文索引，MyISAM 支持；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
• InnoDB 不保存表的总行数，执行 select count(*) from table 时

需要全表扫描；MyISAM 用一个变量保存表的总行数，查总行数速度很快

• 例如，知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键

因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

• 例如，用非单调的字段作为主键在InnoDB中不是个好主意

因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效
而使用自增字段作为主键则是一个很好的选择