MySQL索引总结

1.你真的懂索引吗？

（1）索引定义以及使用误区

​ 索引定义：索引（index）是高效获取数据的数据结构。本质上也是一种数据结构。

​ 误区1：索引越多越好？

​ 多数情况下索引能够大幅度的提升查询的效率，但需注意 ：

​ a.数据的变更（增删改）都需要维护索引，更多的索引意味着更多的维护成本

​ b.更多的索引意味着更多的存储空间

​ c.表的记录比较少，个人经验以2000条记录为分割点建立索引，大于20000的选择建立索引

​ 误区2：所有操作都会使用索引吗？

​ 数据库中添加索引后确实会让查询的速度分起来，但是前提必须是正确使用索引来查询，否则即使建立索引也不会生效：

- like '%xx'    select * from tb1 where name like '%cn';    特别的，"xxx%"是可以用到索引的- 使用函数    select * from tb1 where reverse(name) = 'wupeiqi';- or    select * from tb1 where nid = 1 or email = '[e-mail]';    特别的：当or条件中有未建立索引的列才失效，以下会走索引            select * from tb1 where nid = 1 or name = 'seven';            select * from tb1 where nid = 1 or email = '[e-mail]' and name = 'alex'- 类型不一致    如果列是字符串类型，传入条件是必须用引号引起来，不然...    select * from tb1 where name = 999;- !=    select * from tb1 where name != 'alex'    特别的：如果是主键，则还是会走索引        select * from tb1 where nid != 123- >    select * from tb1 where name > 'alex'    特别的：如果是主键或索引是整数类型，则还是会走索引        select * from tb1 where nid > 123        select * from tb1 where num > 123- order by    select email from tb1 order by name desc;    当根据索引排序时候，选择的映射如果不是索引，则不走索引    特别的：如果对主键排序，则还是走索引：        select * from tb1 order by nid desc;- 组合索引最左前缀    如果组合索引为：(name,email)    name and email       -- 使用索引    name                 -- 使用索引    email                -- 不

​ 误区3：什么字段都可以建立索引？

​ a.索引的选择性较低是不适合建立索引的，索引的选择性（selectivity），是指不重复的索引值（Cardinality）与表记录数（Count(*)）的比值：

​ index selectivity = Cardinality/Count(*)

​ 取值范围是（0,1]，一般来说选择性越高索引的价值就越大，这是由B+Tree性质决定的。比如性别就

​ 不适合建立索引。

​ b.太长的列，可以只选择部分索引（比如只去前8位），这与B+会存储整个索引列的值有关，太长的列值会导致整个索引文件过大。

​ 注意： 部分索引的缺点是不能使用ORDER BY和GROUP BY操作，也不能用于覆盖索引（Covering index，如果索引本身包含查询所需要的全部数据时，不再访问数据文件本身）

​ c.更新非常频繁的数据不适合建立索引，创建索引时如果是BLOB和TEXT类型，必须指定length。

​ d. myisam存储引擎索引键长度总和不能超过1000字节。

（2）索引的分类及作用

索引类型 作用 普通索引 加速查询 唯一索引 加速查询+ 列值唯一（可以为null) 主键索引 加速查询+ 列值唯一 + 表中只有一个（不可以为null） 组合索引 多列值组成一个索引，专门用于组合搜索，效率大于索引合并 全网索引 对全文的内容进行分词，进行搜索

索引合并： 使用多个单列索引组合搜索

覆盖索引： selectde 数据列只用从索引中就能取得，不必全表查询，这个与B+Tree的性质有关。

（3）总结

相关命令总结：

- 查看表结构    desc 表名- 查看生成表的SQL    show create table 表名- 查看索引    show index from  表名- 查看执行时间    set profiling = 1;    SQL...    show profiles;

其他注意事项：

- 避免使用select *- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引（经常使用多个条件查询时）- 尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值（重复少）不适合建索引，例：性别不适合

2.索引背后的事情

（1）背景介绍——B-Tree和B+Tree

​ 目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B-Tree

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度。

h为一个正整数，称为B-Tree的高度。

每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

所有叶节点具有相同的深度，等于树高h。

key和指针互相间隔，节点两端是指针。

一个节点中的key从左到右非递减排列。

所有节点组成树结构。

每个指针要么为null，要么指向另外一个节点。

如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。

如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。

如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)。

下图是一个d=2的B-Tree示意图。

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。B-Tree上查找算法的伪代码如下：

BTree_Search(node, key) {    if(node == null) return null;    foreach(node.key)    {        if(node.key[i] == key) return node.data[i];            if(node.key[i] > key) return BTree_Search(point[i]->node);    }    return BTree_Search(point[i+1]->node);}data = BTree_Search(root, my_key);

关于B-Tree有一系列有趣的性质，例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构。

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

每个节点的指针上限为2d而不是2d+1。

内节点不存储data，只存储key；叶子节点不存储指针。

下图是一个简单的B+Tree示意。

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关。

带顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

如上图所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

（2）MySQL存储引擎的实现

MyISAM索引实现

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

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

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

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

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的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

3.参考资料

• MySQL索引背后的数据结构及算法
• 最全面的MySQL索引详解
• mysql索引需要了解的几个注意

笔者这篇文章主要参考了以上三篇文章，尤其是MySQL索引背后的数据结构及算法这篇，作者总结的非常到位，非常感谢作者的分享。