小心陷入MySQL索引的坑

索引可以说是数据库中的一个大心脏了，如果说一个数据库少了索引，那么数据库本身存在的意义就不大了，和普通的文件没什么两样。所以说一个好的索引对数据库系统尤其重要，今天来说说MySQL索引，从细节和实际业务的角度看看在MySQL中B+树索引好处，以及我们在使用索引时需要注意的知识点。

合理利用索引

在工作中，我们可能判断数据表中的一个字段是不是需要加索引的最直接办法就是：这个字段会不会经常出现在我们的where条件中。从宏观的角度来说，这样思考没有问题，但是从长远的角度来看，有时可能需要更细致的思考，比如我们是不是不仅仅需要在这个字段上建立一个索引？多个字段的联合索引是不是更好？以一张用户表为例，用户表中的字段可能会有用户的姓名、用户的身份证号、用户的家庭地址等等。

「1.普通索引的弊端」

现在有个需求需要根据用户的身份证号找到用户的姓名，这时候很显然想到的第一个办法就是在id_card上建立一个索引，严格来说是唯一索引，因为身份证号肯定是唯一的，那么当我们执行以下查询的时候：

SELECT name FROM user WHERE id_card=xxx

它的流程应该是这样的：

1. 先在id_card索引树上搜索，找到id_card对应的主键id

2. 通过id去主键索引上搜索，找到对应的name

从效果上来看，结果是没问题的，但是从效率上来看，似乎这个查询有点昂贵，因为它检索了两颗B+树，假设一颗树的高度是3，那么两颗树的高度就是6，因为根节点在内存里（此处两个根节点），所以最终要在磁盘上进行IO的次数是4次，以一次磁盘随机IO的时间平均耗时是10ms来说，那么最终就需要40ms。这个数字一般，不算快。

「2.主键索引的陷阱」

既然问题是回表，造成了在两颗树都检索了，那么核心问题就是看看能不能只在一颗树上检索。这里从业务的角度你可能发现了一个切入点，身份证号是唯一的，那么我们的主键是不是可以不用默认的自增id了，我们把主键设置成我们的身份证号，这样整个表的只需要一个索引，并且通过身份证号可以查到所有需要的数据包括我们的姓名，简单一想似乎有道理，只要每次插入数据的时候，指定id是身份证号就行了，但是仔细一想似乎有问题。

这里要从B+树的特点来说，B+树的数据都存在叶子节点上，并数据是页式管理的，一页是16K，这是什么意思呢？哪怕我们现在是一行数据，它也要占用16K的数据页，只有当我们的数据页写满了之后才会写到一个新的数据页上，新的数据页和老的数据页在物理上不一定是连续的，而且有一点很关键，虽然数据页物理上是不连续的，但是数据在逻辑上是连续的。

也许你会好奇，这和我们说的身份证号当主键ID有什么关系？这时你应该关注「连续」这个关键字，身份证号不是连续的，这意味着什么？当我们插入一条不连续的数据的时候，为了保持连续，需要移动数据，比如原来在一页上的数据有1->5，这时候插入了一条3，那么就需要把5移到3后面，也许你会说这也没多少开销，但是如果当新的数据3造成这个页A满了，那么就要看它后面的页B是否有空间，如果有空间，这时候页B的开始数据应该是这个从页A溢出来的那条，对应的也要移动数据。如果此时页B也没有足够的空间，那么就要申请新的页C，然后移一部分数据到这个新页C上，并且会切断页A与页B之间的关系࿰