MySQL普通索引和唯一索引的深入讲解

深入理解MySQL索引之普通索引与唯一索引：应用场景与选择策略

在这个场景中，我们面对的是一个市民系统，其中身份证号是关键字段。我们不仅需要保证数据的快速查询，还要确保数据的唯一性和准确性。那么，如何选择普通索引和唯一索引呢？让我们深入一下。

一、普通索引（INDEX 或 KEY）：适用于快速查询需求

普通索引是最基本的索引，没有任何限制。如果你的主要需求是快速查询，例如通过身份证号查询用户姓名，那么普通索引可以满足你的需求。在这种场景下，使用普通索引能够提高查询速度，优化数据库性能。

二、唯一索引（UNIQUE）：适用于确保数据唯一性

三、身份证号的选择策略

身份证号比较大，不适合作为主键。在实际应用中，我们可以选择将身份证号设置为唯一索引，以保证数据的唯一性。而对于查询需求，可以根据实际情况选择是否建立普通索引。如果查询需求非常频繁，那么建立普通索引是有必要的。

假设字段k上的值都是唯一的且不重复。

查询过程

当执行查询语句`SELECT id FROM T WHERE k=5`时，数据库通过B+树结构从树根开始按层搜索，直至到达包含数据页的叶子节点。这一过程涉及到在数据页内部通过二分法来定位具体的记录。

性能差异

对于普通索引和唯一索引，查询性能的差异微乎其微。InnoDB的数据是以数据页为单位进行读写的，默认为16KB。当需要读取一条记录时，并不是将这个记录本身从磁盘读出来，而是以数据页为单位进行读取的。当找到k=5的记录时，它所在的数据页已经在内存里了。对于普通索引而言，只需要多一次指针寻找和多一次计算，CPU消耗很低。

如果k=5这个记录恰好是所在数据页的一个记录，那么如果要取下一个记录，就需要读取下一个数据页。这种情况的概率相对较低。对于整型字段索引，一个数据页（16KB）可以存放大概745个值。

Change Buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新。如果数据页不在内存中，InnoDB会将这些更新操作缓存在change buffer中，而不需要从磁盘读入这个数据页。这样可以减少随机读磁盘的次数，提升语句的执行速度。Change buffer是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上。

使用change buffer的更新过程大致如下：首先将更新操作记录在change buffer中；当再次访问该数据页时，将其读入内存，然后执行change buffer中与这个数据页有关的操作（merge）。触发merge的时机包括访问这个数据页、系统后台线程定期merge以及数据库正常关闭。

使用条件与场景

对于唯一索引来说，所有的更新操作需要先判断这个操作是否违反唯一性约束，因此无法使用change buffer。只有普通索引可以使用change buffer，但也有一些例外情况，例如主键的更新也无法使用change buffer。

使用change buffer的一个典型场景是写多读少的业务，例如账单类或日志类系统。如果一个业务的更新模式是写入后马上查询，虽然更新操作被记录到change buffer，但紧接着的查询会触发merge过程，这种情况下使用change buffer可能并不会带来性能提升。

更新过程与索引选择

在某些情况下，建议关闭change buffer的使用，例如所有的更新后面都伴随着对这个记录的查询。也要注意到change buffer的前身是insert buffer，早期版本只能对insert操作进行优化。

Change Buffer + Redolog

当渲染引擎启动，Cambrian的“body”悄然现身。它的外观，宛如一幅精美的画卷，呈现出无尽的想象与创意。这里的色彩丰富多变，既有深邃的蓝色，也有温暖的橙色，更有神秘的紫色。这些色彩交织在一起，构成了一幅绚丽多彩的画卷。

在这个世界里，每一个元素都充满了生命力。它们跳跃、旋转、舞动，仿佛在诉说着自己的故事。这些元素之间相互作用，形成了一个和谐而充满活力的生态系统。无论是静态的景物，还是动态的动画效果，都让人感受到Cambrian的独特魅力。

在这里，你可以看到各种各样的图形和图像。有些宛如抽象的艺术品，让人感受到无尽的创意与想象；有些则逼真地展现了现实世界的美丽景色，让人仿佛置身于一个梦幻般的世界。这些图形和图像共同构成了Cambrian丰富多彩的世界，让人流连忘返。

Cambrian的世界不仅仅是一个静态的展示，更是一个充满互动的体验。你可以通过简单的操作，这个神秘的世界，感受其中的乐趣。每一个细节都经过精心设计和打磨，确保你能在这里找到乐趣和惊喜。

Cambrian是一个充满创意和想象力的世界。它的魅力不仅仅在于其精美的画面和丰富的元素，更在于其独特的互动体验和无尽的乐趣。无论你是设计师、开发者还是普通用户，都可以在这里找到属于自己的乐趣和灵感。

让我们一同走进Cambrian的世界，感受其独特的魅力吧！在这里，你可以放飞自己的想象力，创造出无限的可能。