MySQL在日常工作中的使用频率是相当高的,对于SQL优化大家都不陌生,很多小的优化就可以带来性能的大幅提升,今天我们来简单分析一下普通索引和唯一索引该如何选择。
我们先来看看普通索引和唯一索引各自都有哪些特性。
普通索引:
- 允许重复值:普通索引不会限制列中的值必须是唯一的,因此同一列中可以有多个相同的值。
- 提高查询速度:通过创建索引,数据库可以在该列上更快地查找、排序或过滤数据。
- 适用场景:适用于那些需要加速查询但不要求唯一性的字段,如状态码、类别标签等。
唯一索引:
- 强制唯一性:唯一索引确保了被索引的列或组合列中的所有值都是唯一的。如果尝试插入一个已经存在的值,数据库将抛出错误。
- 保证数据完整性:除了提供查询优化外,还充当了一种约束机制,防止数据冗余和不一致。
- 适用场景:通常用于主键(Primary Key)、外部键(Foreign Key)或其他应该保持唯一的字段,例如身份证号、用户名等。
两种索引在查询和更新方面的性能对比,查询性能:因为innodb引擎是按照数据页的形式保存数据的,普通索引可能只比唯一索引多了寻址和计算的操作,性能相差不大,几乎可以忽略不记。主要还是看更新方面的性能。
影响更新操作比较大的就是是否使用Change Buffer,Change Buffer是InnoDB引擎中的一个重要优化特性,主要用于改进辅助索引的更新性能,通过延迟写入和批量写的手段来减少随机I/O操作,从而提高数据库的更新性能。
Change Buffer工作原理
- 延迟写入:当有新的插入、更新或者删除操作影响到辅助索引的时候,如果相关的索引页已经在缓冲池中,则直接在内存中完成变更,如果相关索引页不在内存中,则不会立即从磁盘中加载该页,InnoDB会将变更记录在Change Buffer中。
- 批量合并:多个变更操作可以一次性地应用到同一个索引页上,减少随机I/O操作。
- 合并时机:当系统空闲时间或者Change Buffer容量达到配置比例的时候就会进行合并。在读取时也会触发合并,当需要读取的一个索引页被标记为有未合并的变更的时候,将索引页加载到缓冲池的时候,就会立即进行相关变更,确保数据的一致性。
通过上述工作原理,我们可以知道,Change Buffer并不适用于唯一索引和主键索引,唯一索引由于必须保证每条数据在整个表范围内是唯一的,在对表做插入或者更新操作的时候,必须立即检查是否存在冲突,因此不适用Change Buffer来延迟操作。主键索引是由于任何对主键的更改都会直接影响到数据行本身的位置,不适用延迟操作。
Change Buffer主要适用与普通索引和大表上访问不频繁的索引,普通索引允许数据重复,且不需要即时校验唯一性。大表访问不频繁的索引,使用Change Buffer可以避免不必要的页面加载节省资源。
配置Change Buffer参数
- innodb_change_buffering:控制哪些类型的变更可以进入 Change Buffer。默认值为 all,表示所有符合条件的变更都将被缓存。其他选项包括 none, inserts, deletes, 和 changes(包含 inserts 和 deletes)。
- innodb_change_buffer_max_size:设置 Change Buffer 占用缓冲池的最大比例,默认为 25%。根据实际情况调整此参数可以帮助平衡内存使用和性能需求。
在日常使用中,非必要不使用唯一索引,合理使用索引。
最后
有什么想分享或者交流的欢迎在文章下方留言。