必须要了解MySQL索引的坑
本篇文章给大家带来了关于mysql中关于索引可能会遇到的问题，索引可以说是数据库中的一个大心脏了，如果说一个数据库少了索引，那么数据库本身存在的意义就不大了，希望对大家有
本篇文章给大家带来了关于mysql中关于索引可能会遇到的问题，索引可以说是数据库中的一个大心脏了，如果说一个数据库少了索引，那么数据库本身存在的意义就不大了，希望对大家有帮助。<p><img src="https://img.mryunwei.com/uploads/2023/04/20230416131857801.jpg"></p>索引可以说是数据库中的一个大心脏了，如果说一个数据库少了索引，那么数据库本身存在的意义就不大了，和普通的文件没什么两样。所以说一个好的索引对数据库系统尤其重要，今天来说说MySQL索引，从细节和实际业务的角度看看在MySQL中B+树索引好处，以及我们在使用索引时需要注意的知识点。
合理利用索引
在工作中，我们可能判断数据表中的一个字段是不是需要加索引的最直接办法就是：这个字段会不会经常出现在我们的where条件中。从宏观的角度来说，这样思考没有问题，但是从长远的角度来看，有时可能需要更细致的思考，比如我们是不是不仅仅需要在这个字段上建立一个索引？多个字段的联合索引是不是更好？以一张用户表为例，用户表中的字段可能会有用户的姓名、用户的身份证号、用户的家庭地址等等。
「1.普通索引的弊端」
现在有个需求需要根据用户的身份证号找到用户的姓名，这时候很显然想到的第一个办法就是在id_card上建立一个索引，严格来说是唯一索引，因为身份证号肯定是唯一的，那么当我们执行以下查询的时候：
「2.主键索引的陷阱」
既然问题是回表，造成了在两颗树都检索了，那么核心问题就是看看能不能只在一颗树上检索。这里从业务的角度你可能发现了一个切入点，身份证号是唯一的，那么我们的主键是不是可以不用默认的自增id了，我们把主键设置成我们的身份证号，这样整个表的只需要一个索引，并且通过身份证号可以查到所有需要的数据包括我们的姓名，简单一想似乎有道理，只要每次插入数据的时候，指定id是身份证号就行了，但是仔细一想似乎有问题。
这里要从B+树的特点来说，B+树的数据都存在叶子节点上，并数据是页式管理的，一页是16K，这是什么意思呢？哪怕我们现在是一行数据，它也要占用16K的数据页，只有当我们的数据页写满了之后才会写到一个新的数据页上，新的数据页和老的数据页在物理上不一定是连续的，而且有一点很关键，虽然数据页物理上是不连续的，但是数据在逻辑上是连续的。
也许你会好奇，这和我们说的身份证号当主键ID有什么关系？这时你应该关注连续这个关键字，身份证号不是连续的，这意味着什么？当我们插入一条不连续的数据的时候，为了保持连续，需要移动数据，比如原来在一页上的数据有1->5，这时候插入了一条3，那么就需要把5移到3后面，也许你会说这也没多少开销，但是如果当新的数据3造成这个页A满了，那么就要看它后面的页B是否有空间，如果有空间，这时候页B的开始数据应该是这个从页A溢出来的那条，对应的也要移动数据。如果此时页B也没有足够的空间，那么就要申请新的页C，然后移一部分数据到这个新页C上，并且会切断页A与页B之间的关系，在两者之间插入一个页C，从代码的层面来说，就是切换链表的指针。
总结来说，不连续的身份证号当主键可能会造成页数据的移动、随机IO、频繁申请新页相关的开销。如果我们用的是自增的主键，那么对于id来说一定是顺序的，不会因为随机IO造成数据移动的问题，在插入方面开销一定是相对较小的。
其实不推荐用身份证号当主键的还有另外一个原因：身份证号作为数字来说太大了，得用bigint来存，正常来说一个学校的学生用int已经足够了，我们知道一页可以存放16K，当一个索引本身占用的空间越大时，会导致一页能存放的数据越少，所以在一定数据量的情况下，使用bigint要比int需要更多的页也就是更多的存储空间。
「3.联合索引的矛与盾」
由上面两条结论可以得出：
每次额外的crc，导致需要更多cpu资源
额外的字段，虽然让索引的空间变小了，但是本身也要占用空间
crc会存在冲突的概率，这需要我们查询出来数据后，再根据id_card过滤一下，过滤的成本根据重复数据的数量而定，重复越多，过滤越慢。
关于联合索引存储优化，这里有个小细节，假设现在有两个字段A和B，分别占用8个字节和20个字节，我们在联合索引已经是[A,B]的情况下，还要支持B的单独查询，因此自然而然我们在B上也建立个索引，那么两个索引占用的空间为 8+20+20=48，现在无论我们通过A还是通过B查询都可以用到索引，如果在业务允许的条件下，我们是否可以建立[B,A]和A索引，这样的话，不仅满足单独通过A或者B查询数据用到索引，还可以占用更小的空间：20+8+8=36。
「4.前缀索引的短小精悍」
有时候我们需要索引的字段是字符串类型的，并且这个字符串很长，我们希望这个字段加上索引，但是我们又不希望这个索引占用太多的空间，这时可以考虑建立个前缀索引，以这个字段的前一部分字符建立个索引，这样既可以享受索引，又可以节省空间，这里需要注意的是在前缀重复度较高的情况下，前缀索引和普通索引的速度应该是有差距的。
