MySQL数据库索引的弊端及怎么使用

本文介绍了关于“MySQL数据库索引的缺点及使用方法”的知识。很多人在实际案例的操作中会遇到这样的困难。接下来，让边肖带领大家学习如何应对这些情况！希望大家认真阅读，学点东西！

00-1010在工作中，我们可能会判断数据表中的某个字段是否需要索引。最直接的方式是：这个领域会经常出现在我们的哪里条件？从宏观的角度来看，这样思考没有问题，但是从长远的角度来看，有时候可能需要更细致的思考，比如，我们是不是不仅需要在这个领域建立一个指数？多字段联合索引不是更好吗？以用户表为例。用户表中的字段可能包括用户的姓名、用户的身份证号、用户的家庭地址等等。

00-1010现在需要根据用户的身份证号找到用户名。此时，很明显，首先想到的方法是在id_card上设置一个索引，严格来说，这是一个唯一的索引，因为id号必须是唯一的，所以当我们执行以下查询时：

selectnamefrouser where id _ card=XXX其过程应该如下：

首先，搜索id_card索引树，找到id_card对应的主键id。

按id搜索主键索引以找到相应的名称。

从效果来看，结果没问题，但是从效率来看，这个查询似乎有点贵，因为它检索了两个B树，假设一棵树的高度是3，那么两棵树的高度是6。因为根节点在内存中(这里有两个根节点)，所以最终磁盘上的IO数是4倍，如果随机IO在磁盘上的平均时间是10ms，最终需要40ms。这个数字是平均值，不是很快。

00-1010既然问题是返回到表中，这就导致在两棵树中搜索，那么核心问题就是看我们是否只能在一棵树中搜索。从商业的角度来看，你可能已经找到了一个切入点。id号是唯一的，那么我们的主键可以设置为我们的id号而不是默认的ID号吗？这样整个表只需要一个索引，所有需要的数据包括我们的名字都可以通过ID号找到。简单想想似乎也有道理，只要身份证号是每次插入数据的身份证号，但仔细考虑后似乎有问题。

根据B-tree的特点，B-tree的数据都存在于叶节点中，数据采用逐页管理，一页16K。这是什么意思？即使我们现在是一行数据，也会占用16K个数据页。只有当我们的数据页已满时，我们才会写入新的数据页。新数据页和旧数据页在物理上不一定是连续的，一个关键点是，虽然数据页在物理上是不连续的，但数据在逻辑上是连续的。

你可能会好奇，这和我们的身份证号作为主键ID有什么关系？这时，你要注意关键词的连续。身份证号码不连续。这是什么意思？当我们插入一条不连续的数据时，为了保持连续性，我们需要移动数据。例如，一页上有1-5条数据，然后插入了一条3，所以我们需要将5移动到3的后面。也许你会说花不了多少钱，但是如果新数据3导致这个A页满了，那就要看后面的B页有没有空间了。如果有空间，那么页面B的起始数据应该是这个从页面a。

如果此时B页没有足够的空间，需要申请一个新的C页，然后将一些数据移动到这个新的C页，并切断A页和B页之间的关系，在它们之间插入一个C页，就是从代码层面切换链表的指针。

综上所述，不连续的ID号作为主键，可能会导致页面数据移动、随机IO、频繁申请新页面。如果我们使用自增主键，对于id来说必须是顺序的，不会出现随机IO带来的数据移动问题，插入成本也必须相对较小。

其实不建议用ID号作为主键还有一个原因：ID号作为数字太大了，必须用bigint保存。通常，一所学校的学生使用int就足够了。

够了，我们知道一页可以存放16K，当一个索引本身占用的空间越大时，会导致一页能存放的数据越少，所以在一定数据量的情况下，使用bigint要比int需要更多的页也就是更多的存储空间。

3.联合索引的矛与盾

由上面两条结论可以得出：

• 尽量不要去回表

• 身份证号不适合当主键索引

所以自然而然地想到了联合索引，创建一个【身份证号+姓名】的联合索引，注意联合索引的顺序，要符合最左原则。这样当我们同样执行以下sql时：

select name from user where id_card=xxx

不需要回表就可以得到我们需要的name字段，然而还是没有解决身份证号本身占用空间过大的问题，这是业务数据本身的问题，如果你要解决它的话，我们可以通过一些转换算法将原本大的数据转换成小的数据，比如crc32：

crc32.ChecksumIEEE([]byte("341124199408203232"))

可以将原本需要8个字节存储空间的身份证号用4个字节的crc码替代，因此我们的数据库需要再加个字段crc_id_card，联合索引也从【身份证号+姓名】变成了【crc32(身份证号)+姓名】，联合索引占的空间变小了。但是这种转换也是有代价的：

• 每次额外的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.前缀索引的短小精悍

有时候我们需要索引的字段是字符串类型的，并且这个字符串很长，我们希望这个字段加上索引，但是我们又不希望这个索引占用太多的空间，这时可以考虑建立个前缀索引，以这个字段的前一部分字符建立个索引，这样既可以享受索引，又可以节省空间，这里需要注意的是在前缀重复度较高的情况下，前缀索引和普通索引的速度应该是有差距的。

alter table xx add index(name(7));#name前7个字符建立索引
select xx from xx where name="JamesBond"

5.唯一索引的快与慢

在说唯一索引之前，我们先了解下普通索引的特点，我们知道对于B+树而言，叶子节点的数据是有序的。

假设现在我们要查询2这条数据，那么在通过索引树找到2的时候，存储引擎并没有停止搜索，因为可能存在多个2，这表现为存储引擎会在叶子节点上接着向后查找，在找到第二个2之后，就停止了吗？答案是否，因为存储引擎并不知道后面还有没有更多的2，所以得接着向后查找，直至找到第一个不是2的数据，也就是3，找到3之后，停止检索，这就是普通索引的检索过程。

唯一索引就不一样了，因为唯一性，不可能存在重复的数据，所以在检索到我们的目标数据之后直接返回，不会像普通索引那样还要向后多查找一次，从这个角度来看，唯一索引是要比普通索引快的，但是当普通索引的数据都在一个页内的话，其实也并不会快多少。在数据的插入方面，唯一索引可能就稍逊色，因为唯一性，每次插入的时候，都需要将判断要插入的数据是否已经存在，而普通索引不需要这个逻辑，并且很重要的一点是唯一索引会用不到change buffer（见下文）。

6.不要盲目加索引

在工作中，你可能会遇到这样的情况：这个字段我需不需要加索引？。对于这个问题，我们常用的判断手段就是：查询会不会用到这个字段，如果这个字段经常在查询的条件中，我们可能会考虑加个索引。但是如果只根据这个条件判断，你可能会加了一个错误的索引。我们来看个例子：假设有张用户表，大概有100w的数据，用户表中有个性别字段表示男女，男女差不多各占一半，现在我们要统计所有男生的信息，然后我们给性别字段加了索引，并且我们这样写下了sql：

select * from user where sex="男"

如果不出意外的话，InnoDB是不会选择性别这个索引的。如果走性别索引，那么一定是需要回表的，在数据量很大的情况下，回表会造成什么样的后果？我贴一张和上面一样的图想必大家都知道了：

主要就是大量的IO，一条数据需要4次，那么50w的数据呢？结果可想而知。因此针对这种情况，MySQL的优化器大概率走全表扫描，直接扫描主键索引，因为这样性能可能会更高。

7.索引失效那些事

某些情况下，因为我们自己使用的不当，导致mysql用不到索引，这一般很容易发生在类型转换方面，也许你会说，mysql不是已经支持隐式转换了吗？比如现在有个整型的user_id索引字段，我们因为查询的时候没注意，写成了：

select xx from user where user_id="1234"

注意这里是字符的1234，当发生这种情况下，MySQL确实足够聪明，会把字符的1234转成数字的1234，然后愉快的使用了user_id索引。 但是如果我们有个字符型的user_id索引字段，还是因为我们查询的时候没注意，写成了：

select xx from user where user_id=1234

这时候就有问题了，会用不到索引，也许你会问，这时MySQL为什么不会转换了，把数字的1234转成字符型的1234不就行了？ 这里需要解释下转换的规则了，当出现字符串和数字比较的时候，要记住：MySQL会把字符串转换成数字。也许你又会问：为什么把字符型user_id字段转换成数字就用不到索引了? 这又要说到B+树索引的结构了，我们知道B+树的索引是按照索引的值来分叉和排序的，当我们把索引字段发生类型转换时会发生值的变化，比如原来是A值，如果执行整型转换可能会对应一个B值（int(A)=B）,这时这颗索引树就不能用了，因为索引树是按照A来构造的，不是B，所以会用不到索引。

索引优化

1.change buffer

我们知道在更新一条数据的时候，要先判断这条数据的页是否在内存里，如果在的话，直接更新对应的内存页，如果不在的话，只能去磁盘把对应的数据页读到内存中来，然后再更新，这会有什么问题呢？

• 去磁盘的读这个动作稍显的有点慢

• 如果同时更新很多数据，那么即有可能发生很多离散的IO

为了解决这种情况下的速度问题，change buffer出现了，首先不要被buffer这个单词误导，change buffer除了会在公共的buffer pool里之外，也是会持久化到磁盘的。当有了change buffer之后，我们更新的过程中，如果发现对应的数据页不在内存里的话，也不去磁盘读取相应的数据页了，而是把要更新的数据放入到change buffer中，那change buffer的数据何时被同步到磁盘上去？如果此时发生读动作怎么办？首先后台有个线程会定期把change buffer的数据同步到磁盘上去的，如果线程还没来得及同步，但是又发生了读操作，那么也会触发把change buffer的数据merge到磁盘的事件。

需要注意的是并不是所有的索引都能用到changer buffer，像主键索引和唯一索引就用不到，因为唯一性，所以它们在更新的时候要判断数据存不存在，如果数据页不在内存中，就必须去磁盘上把对应的数据页读到内存里，而普通索引就没关系了，不需要校验唯一性。change buffer越大，理论收益就越大，这是因为首先离散的读IO变少了，其次当一个数据页上发生多次变更，只需merge一次到磁盘上。当然并不是所有的场景都适合changer buffer，如果你的业务是更新之后，需要立马去读，changer buffer会适得其反，因为需要不停地触发merge动作，导致随机IO的次数不会变少，反而增加了维护changer buffer的开销。

2.索引下推

前面我们说了联合索引，联合索引要满足最左原则，即在联合索引是[A,B]的情况下，我们可以通过以下的sql用到索引：

select * from table where A="xx"
select * from table where A="xx" AND B="xx"

其实联合索引也可以使用最左前缀的原则，即：

select * from table where A like "赵%" AND B="上海市"

但是这里需要注意的是，因为使用了A的一部分，在MySQL5.6之前，上面的sql在检索出所有A是“赵”开头的数据之后，就立马回表（使用的select *），然后再对比B是不是“上海市”这个判断，这里是不是有点懵？为什么B这个判断不直接在联合索引上判断，这样的话回表的次数不就少了吗？造成这个问题的原因还是因为使用了最左前缀的问题，导致索引虽然能使用部分A，但是完全用不到B，看起来是有点“傻”，于是在MySQL5.6之后，就出现了索引下推这个优化（Index Condition Pushdown）,有了这个功能以后，虽然使用的是最左前缀，但是也可以在联合索引上搜索出符合A%的同时也过滤非B的数据，大大减少了回表的次数。

3.刷新邻接页

在说刷新邻接页之前，我们先说下脏页，我们知道在更新一条数据的时候，得先判断这条数据所在的页是否在内存中，如果不在的话，需要把这个数据页先读到内存中，然后再更新内存中的数据，这时会发现内存中的页有最新的数据，但是磁盘上的页却依然是老数据，那么此时这条数据所在的内存中的页就是脏页，需要刷到磁盘上来保持一致。所以问题来了，何时刷？每次刷多少脏页才合适？如果每次变更就刷，那么性能会很差，如果很久才刷，脏页就会堆积很多，造成内存池中可用的页变少，进而影响正常的功能。所以刷的速度不能太快但要及时，MySQL有个清理线程会定期执行，保证了不会太快，当脏页太多或者redo log已经快满了，也会立刻触发刷盘，保证了及时。

在脏页刷盘的过程中，InnoDB这里有个优化：如果要刷的脏页的邻居页也脏了，那么就顺带一起刷，这样的好处就是可以减少随机IO，在机械磁盘的情况下，优化应该挺大，但是这里可能会有坑，如果当前脏页的邻居脏页在被一起刷入后，邻居页立马因为数据的变更又变脏了，那此时是不是有种多此一举的感觉，并且反而浪费了时间和开销。更糟糕的是如果邻居页的邻居也是脏页...，那么这个连锁反应可能会出现短暂的性能问题。

4.MRR

在实际业务中，我们可能会被告知尽量使用覆盖索引，不要回表，因为回表需要更多IO，耗时更长，但是有时候我们又不得不回表，回表不仅仅会造成过多的IO，更严重的是过多的离散IO。

select * from user where grade between 60 and 70

现在要查询成绩在60-70之间的用户信息，于是我们的sql写成上面的那样，当然我们的grade字段是有索引的，按照常理来说，会先在grade索引上找到grade=60这条数据，然后再根据grade=60这条数据对应的id去主键索引上找，最后再次回到grade索引上，不停的重复同样的动作...， 假设现在grade=60对应的id=1，数据是在page_no_1上，grade=61对应的id=10，数据是在page_no_2上，grade=62对应的id=2，数据是在page_no_1上，所以真实的情况就是先在page_no_1上找数据，然后切到page_no_2，最后又切回page_no_1上，但其实id=1和id=2完全可以合并，读一次page_no_1即可，不仅节省了IO，同时避免了随机IO，这就是MRR。当使用MRR之后，辅助索引不会立即去回表，而是将得到的主键id，放在一个buffer中，然后再对其排序，排序后再去顺序读主键索引，大大减少了离散的IO。

“MySQL数据库索引的弊端及怎么使用”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！