MySQL索引的坑怎么处理

2024-07-24 12:33:41

字体：大中小

来源：转载

供稿：网友

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

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

　　SELECT name FROM user WHERE id_card=xxx
　　它的流程应该是这样的：

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

　　MySQL索引的坑怎么解决

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

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

　　总结来说，不连续的身份证号当主键可能会造成页数据的移动、随机IO、频繁申请新页相关的开销。如果我们用的是自增的主键，那么对于id来说一定是顺序的，不会因为随机IO造成数据移动的问题，在插入方面开销一定是相对较小的。

　　其实不推荐用身份证号当主键的还有另外一个原因：身份证号作为数字来说太大了，得用bigint来存，正常来说一个学校的学生用int已经足够了，我们知道一页可以存放16K，当一个索引本身占用的空间越大时，会导致一页能存放的数据越少，所以在一定数据量的情况下，使用bigint要比int需要更多的页也就是更多的存储空间。

（编辑：武林网）

上一篇：MySQL持久化和回滚该怎么领会

下一篇：MYSQL中锁的模式与类别有哪些