MySQL 09 普通索引和唯一索引，应该怎么选择？

深夜风尘 發表於 2025-7-9 12:58:00

MySQL 09 普通索引和唯一索引，应该怎么选择？

唯一索引：字段值不能重复。
普通索引：字段值可以重复。
假设数据如下图，且字段k上的值都不重复：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250709125436363-1359423734.png" width="50%"></div>
接下来，从两种索引对查询语句和更新语句的性能影响来分析。
<h3 id="查询过程">查询过程</h3>
假设查询语句为<code>select id from T where k=5</code>。那么首先会在字段k的索引树上找到<code>k=5</code>所在的数据页，然后在数据页内二分查找记录。对于普通索引和唯一索引，区别为：
<ul>
<li>
普通索引，查找到满足条件的第一个记录后，会继续查找，直到找到第一个不满足<code>k=5</code>的记录。
</li>
<li>
唯一索引，找到第一个满足条件的记录后，就停止查找。
</li>
</ul>
这个区别带来的性能差距其实是不大的。由于InnoDB的数据按数据页为单位进行读写，当找到<code>k=5</code>的那条记录时，它所在的数据页就都在内存里了，因此普通索引多进行的一次查找并没有多大开销。尽管有可能<code>k=5</code>刚好是数据页的最后一条记录，查找下一条记录需要新读取一页，但这种可能出现的概率很低，均摊到所有可能后成本可以忽略不计。
<h3 id="更新过程">更新过程</h3>
在分析更新过程之前，先介绍change buffer。
当需要更新一个数据页，如果数据页在内存中，就直接更新；如果数据页在磁盘中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在changebuffer中。当下次查询需要访问该数据页，会将该数据页读入内存，执行change buffer中与该页有关的操作来保证数据逻辑的正确性。
change buffer在内存中，也会被持久化到磁盘上。
将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge，系统有后台线程会定期merge。在数据库正常关闭的过程中，也会执行merge操作。
可以看出，如果能将更新操作先记录在change buffer，减少读磁盘的次数，语句执行速度将会加快。而且，数据读入内存需要占用buffer pool，使用change buffer还能避免占用内存，提高内存利用率。
但并不是所有条件下都可以使用change buffer。对于唯一索引，所有更新操作都必须判断该操作是否违反唯一性约束，比如要插入(4,400)，就需要先判断表中是否已经有<code>k=4</code>的记录，因此每次更新都需要将数据页读入内存。而如果本身有读入内存的操作，那么直接更新内存即可，不需要再使用change buffer。而对普通索引，change buffer是可以使用的。
change buffer用的是buffer pool里的内存，不能无限增大。其大小可以通过<code>innodb_change_buffer_max_size</code>来动态设置，参数值表示占用buffer pool的百分比。
那么来看看，如果想要插入一条<code>k=4</code>的新纪录，InnoDB的处理流程：
<ul>
<li>
对于唯一索引，找到3和5之间的位置，判断到没有冲突，插入值；
</li>
<li>
对于普通索引，找到3和5之间的位置，插入值。
</li>
</ul>
看起来两者只相差一个判断，但若该记录要更新的目标页不在内存中，再对两者进行比较：
<ul>
<li>
对于唯一索引，需要将数据页读入内存，判断到没有冲突，插入值；
</li>
<li>
对于普通索引，将更新记录在change buffer即可。
</li>
</ul>
可以看到，这种情况下相差了一次随机磁盘IO，性能相差较大。
<h3 id="change-buffer的使用场景">change buffer的使用场景</h3>
尽管change buffer能用于普通索引的场景，但并不是在所有情况下它都能起到加速作用。
这是由于merge是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页merge前，change buffer记录的变更越多，收益越大。
对于写多读少的业务场景，页面写完后马上被访问的概率较小，意味着change buffer马上merge的概率较小，此时使用效果较好，场景业务模型如账单类、日志类系统。
而对于一个写入之后马上做查询的场景，merge的频率较高，这样随机访问IO的次数并不会减少，反而增加了change buffer的维护代价。因此，对于这种业务模式来说，change buffer反而起到了副作用。
<h3 id="索引选择和实践">索引选择和实践</h3>
回到之前的问题：如何选择普通索引和唯一索引。
经过上面的分析可以看到，两者在查询过程的能力几乎无差别，主要区别在更新过程。因此，建议尽量选择普通索引。如果是写入后马上查询的场景，建议关闭change buffer。
普通索引 + change buffer，对于数据量大的表的更新优化是比较明显的。特别地，在使用机械硬盘时，change buffer机制收益非常显著，因此使用机械硬盘时考虑加大change buffer。
<h3 id="change-buffer和redo-log">change buffer和redo log</h3>
理解了change buffer的原理，可能会发现这和redo log有一些相似。
让我们回顾redo log的WAL机制：MySQL的更新用到了WAL（Write-Ahead Logging）技术，关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新时，InnoDB引擎先将记录写到redo log并更新内存，这时更新就可以算完成了。之后，InnoDB会在适当的时候将这个操作记录更新到磁盘里。
接下来，使用一个例子来区分这两个概念。假设要在表上执行下面这个插入语句：
<pre><code class="language-sql">insert into t(id,k) values(id1,k1),(id2,k2);
</code></pre>
假设在k索引树找到位置后，k1所在的数据页在内存中，而k2所在的数据页不在内存中。下图是带change buffer的更新状态图：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250709125559001-1516810693.png" width="50%"></div>
分析这条更新语句：
<ul>
<li>
发现Page 1在内存中，直接更新内存；
</li>
<li>
发现Page 2没有在内存中，就在内存的change buffer区域，记录“我要往Page 2插入一行”这个信息；
</li>
<li>
将上述两个动作记入redo log。
</li>
</ul>
做完上述动作，事务就完成了。可以发现，对于该次更新，只是写了两次内存+一次磁盘。图中的虚线箭头是后台操作，不会影响更新的响应时间。
那么此次更新之后的查询请求，会怎么处理呢？
比如要执行<code>select * from t where k in (k1,k2)</code>，其读请求的流程图如下：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250709125635601-591109804.png" width="50%"></div>
分析这条查询语句：
<ul>
<li>
读Page 1时，直接从内存返回。
</li>
<li>
读Page 2时，需要先将Page 2从磁盘读入内存，然后应用change buffer，生成一个正确版本并返回结果。
</li>
</ul>
因此，如果简单对比change buffer和redo log在提升更新性能上的收益的话：
<ul>
<li>
redo log主要节省了随机写磁盘的IO消耗，将随机写转为了顺序写；
</li>
<li>
change buffer节省了随机读磁盘的IO消耗。
</li>
</ul>
最后，做一个思考：如果某次写入使用了change buffer，如果之后主机异常重启，是否会丢失change buffer和数据？
答案是不会，因为在写完change buffer后，会把该动作也记录到redo log后，之后崩溃恢复也可以通过redo log找回。 
来源：https://www.cnblogs.com/san-mu/p/18974654

頁: [1]

琼殿技术论坛's Archiver

MySQL 09 普通索引和唯一索引，应该怎么选择？