mysql索引详解

数据结构分，有B-Tree索引（B+ Tree）、哈希索引、R-Tree索引等。按数据块的顺序和索引节点的逻辑顺序是否一致可以分为聚集索引和非聚集索引。聚集索引由于物理块连续，在范围扫描的时候可以减少磁头寻道时间，因而比非聚集索引高效。

 

几种索引类型的选择：

primary：主键索引。

unique：唯一索引。不允许重复，可以为null。

normal：普通索引。

FULLTEXT：只能对CHAR, VARCHAR和TEXT列编制索引，并且只能在MyISAM表中编制。

SPATIAL：只能对空间列编制索引，并且只能在MyISAM表中编制。

 

个人总结：

       一般使用主键+组合索引，主键是唯一值(一般用id)。组合索引常用于条件查询使用，比如where需要两个字段，或者where+order by。其中，需要注意一条sql只能使用1个索引，也就是，如果where使用了，order by不能和前者满足组合索引左前缀关系，order by就不使用索引index排序而使用filesort了，当然性能也就差了。

       聚集索引中，"聚集"的意思是指实际的数据行和相关的键值都保存在一起，但是每个表只能有一个聚集索引，不能把一行数据保存在两个地方。

MyISAM

1、非聚集索引，所以即使建立主键，也是非聚集的。

2、B+Tree，所有索引的叶节点是数据的物理地址。

3、如果唯一索引不允许存在NULL值，那与主键索引本质上一样。

4、第二(其他)索引，查找数据，找到物理地址，然后从中读取。

InnoDB

1、聚集索引(有且仅有一个)，如果有主键，主键就是聚集索引，若没有主键且有唯一索引，第一个唯一索引就是聚集索引，如果都没有，则表自动生成一个6字节的聚集索引来做排序ID。

2、B+Tree，聚集索引的叶节点是完整数据，其他索引叶节点是聚集索引的键值。

3、如果唯一索引不允许存在NULL值，查询却还是需要先找到数据的聚集索引唯一值，再去聚集索引中找对应数据。

4、第二(其他)索引，查找数据，找到后再根据聚集索引唯一值去聚集索引中找对应数据。

 

建议：当索引为字符串，而且字符串又比较长，可以使用前缀索引(即只使用该字段指定长度的部分字符)减少其索引空间，但是这样就不能用于order by和group by了。

对于多表索引：一定要在Join的字段上建立索引，而且该字段类型编码必须一致，才能优化sql。

 

 索引的官方介绍：

        MySQL为表把它的数据词典信息以.frm文件的形式存在数据库目录里，这对所有MySQL存储引擎都是真的。但是每个InnoDB表在表空间内的InnoDB内部数据词典里有它自己的条目。当MySQL移除表或数据库，它不得不删除.frm文件和InnoDB数据词典内的相应条目。这就是为什么你不能在数据库之间简单地移动.frm文件来移动InnoDB表。

        每个InnoDB表有专门索引，被称为clustered index，对行的数据被存于其中。如果你对你的表定义一个PRIMARY KEY，主键的索引是集束索引。

        如果你没有为表定义PRIMARY KEY，MySQL取第一个不允许NOT NULL列的UNIQUE索引作为主键，并且InnoDB把它当作集束索引来用。如果表中没有这样一个索引，InnoDB内部产生一个集束索引，其中用InnoDB在这样一个表内指定给行的行ID来排序行。行ID是一个6字节的域，它在新行被插入的时候单一地增加。因此被行ID排序的行是物理地按照插入顺序排的。

        通过集束索引访问一个行是较快的，因为行数据是在索引搜索引导的同一页面。如果表是巨大的，当对比于传统解决方案，集束索引构架经常节约磁盘I/O。（在许多数据库，数据传统地被存在与索引记录不同的页）。

        在InnoDB中，非集束索引里的记录（也称为第二索引）包含对行的主键值。InnoDB用这个主键值来从集束索引中搜索行。注意，如果主键是长的，第二索引使用更多空间。

InnoDB比较CHAR和VARCHAR字符串不同长度，以便在较短字符串中剩下的长度被处理视为用空格补上的。

 

15.2.13.1. 索引的物理结构

        所有InnoDB的索引是B数，其中索引记录被存储在树的树叶页。一个索引页的默认大小是16KB。当新记录被插入，InnoDB试着为将来索引记录的插入和更新留下十六分之一的空白页。

        如果索引记录以连续的顺序被插入（升序或者降序），结果索引页大约是15/16满。如果记录被以随机的顺序被插入，页面是从1/2到 15/16满。如果索引页的填充因子降到低于1/2，InnoDB试着搜索索引树来释放页。