普通索引和唯一索引

基本概述

MySQL中可以创建普通索引与唯一索引，这两种索引的区别是：

普通索引（Non-Unique Index），也称为非唯一索引，它允许索引中的条目具有重复的键值。普通索引的主要目的是加快查询速度，它并不关心数据的唯一性。

普通索引的特点：

• 可以包含重复的索引键值。
• 适用于快速查找具有相同索引值的多个记录的场景。
• 不保证数据的唯一性。

普通索引创建方法：

CREATE INDEX idx_email ON customers(email);

唯一索引（Unique Index）是一种数据库索引，它要求索引中的所有键值都是唯一的。如果尝试插入或更新记录以产生重复的索引键值，数据库将拒绝这种操作。

唯一索引的特点：

• 保证索引中的键值是唯一的。
• 适用于确保数据表中某一列或列组合的唯一性，例如身份证号、用户名等。
• 可以有效地避免数据重复，保护数据完整性。
• 通常也会加快查询速度，尤其是在执行等值查询（例如，查询特定ID的记录）时。

唯一索引创建方法：

CREATE UNIQUE INDEX uidx_email ON customers(email);

使用场景

因为InnoDB引擎是按数据页为单位进行读写操作的，当需要读一条记录的时候，以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

所以在内存中进行查询时，两种索引的性能差别很小。

InnoDB存储引擎中存在Change Buffer机制。数据页目前如果在内存中，则直接更新，如果不在内存中，可以使用Change Buffer缓存对数据的更新操作，从而减少磁盘I/O操作，提高更新性能。

所以对于写多读少的场景，尤其是频繁更新场景下，Change Buffer可以显著提高性能。但如果写入后立即进行查询，Change Buffer的效果就会大打折扣。因为查询时，会强制将Change Buffer缓存Merge到磁盘。

但只有普通索引可以使用Change Buffer，而唯一索引由于需要检查唯一性约束（从磁盘加载数据后，再进行判断），不能使用Change Buffer。

所以在业务可以接受的情况下，优先考虑使用普通索引，尤其是频繁插入和更新场景。因为普通索引可以配合Change Buffer使用，从而优化更新操作。

普通索引改成唯一索引后，某个业务的库内存命中率突然从 99% 降低到了 75%，整个系统处于阻塞状态，更新语句全部堵住。是因为这个业务有大量插入数据的操作，而唯一索引没有Change Buffer，性能会降低。

Change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过以下参数来动态设置。设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

innodb_change_buffer_max_size

Buffer Pool 是一片内存空间，受制于内存空间大小。 可以通过参数来控制Buffer Pool 的大小。

innodb_buffer_pool_size

作者与版本更新计划

感谢您的阅读与支持！本文是《MySQL实战与优化》专栏中的一篇精选文章，该专栏共包含16篇文章，旨在为您提供实战中可直接应用的宝贵知识。

关注公众号【数舟】，获取作者最新动态，公众号后台回复【mysql2024】，即可免费领取这份包含16篇文章的完整的PDF专栏！

目前版本为v1.0，更新时间2024年4月10日。后续此文档更新与版本发布会同步到知识星球【数舟】中。

知识整理与创作不易，感谢大家理解与支持！

加入知识星球，您将获得更多独家内容、专栏更新以及与行业内专家和同行的互动交流机会。我们在知识星球等您，一起探索MySQL的深层次世界！

星球内目前包含300+精品文章，内容涵盖大数据、MySQL、运维、Python、调优、经验分享、数据分析等方向内容，会根据大家的学习需求更新更多方向的内容。

🔗 立即扫描下方二维码，加入知识星球，与行业精英共同成长，开启您的专属学习之旅！