MySQL普通索引和唯一索引的选择

关忆北.

发布于 2023-10-11 09:34:02

1770

发布于 2023-10-11 09:34:02

文章被收录于专栏：关忆北.

唯一索引和普通索引的区别？普通索引的字段内容是可以重复的，唯一索引的字段内容不可重复。

背景

假设你的项目中主要负责车辆管理系统，每辆车的车牌号在系统上唯一，在新增车辆时，业务层面会先判断待新增的车辆车牌号是否已存在系统中，产品功能中使用频率最高的是根据车牌号查询车辆信息，由于业务量和数据量的增加，现在需要考虑在车辆表车牌号字段增加索引，现有两种索引可供选择：唯一索引、普通索引。

create table vehicle
(
    id           varchar(32) not null primary key,
    brand        varchar(10) not null comment '车辆品牌',
    plate_number varchar(10) not null comment '车牌号'
) comment '车辆信息表';

接下来分析两种索引哪个更适合于当前业务。

查询

当用户使用车牌号查询车辆信息时，执行SQL如下：

select id,brand,plate_number from vehicle where plate_number = '鲁B 12345';

普通索引

假设现已在plate_number字段创建普通索引，那么InnoDB中执行的逻辑为：命中唯一索引，从yB+树的树根节点开始，查询到plate_number为’鲁B 12345’的节点，获取到该节点数据所在的行，查找到第一个满足条件的数据行后，继续查找下一个数据行，直到查找到第一个不满条件的数据，查找结束。

唯一索引

当plate_number是唯一索引时，查到第一个满足条件的数据行即可获得结果。

查询对比

由于在业务层面保证了车牌号的唯一性，那么数据库中有且只有一条车牌号为’鲁B 12345’的车辆信息，那么普通索引只会比唯一索引多一次指针寻址和一次计算，对于当前服务器的CPU性能来说，差距微乎其微，因此，在查询时，唯一索引和普通索引的性能差距很小。

InnoDB的数据是按数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB. 数据页：二级索引的数据页，并不是聚簇索引即主键树的数据页。数据库中耗时的操作为随机读写磁盘IO.

更新/插入

InnoDB的更新策略

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页没有在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新缓存在change buffer中，这样就不需要从磁盘读入这个数据页。下次查询时，需要访问这个数据页的时候，将数据页读入内存。 change buffer中的操作应用到原数据页，得到最新结果的过程成为merge。除了访问数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭(shutdown)的过程中，也会执行merge。此操作同样也会写入到redo log。

什么情况下可以使用change buffer

==对于唯一索引来说，每次插入/更新操作都会先判断这个操作是否违反唯一性约束。==即会对待插入的值是否已存在于数据表中，将数据读入内存再判断，数据在内存中更新会更快，没必要使用change buffer。唯一索引更新不使用change buffer，实际上只有普通索引可以使用。

插入一条数据，InnoDB的处理流程是怎样的

要更新的目标在内存中

对于唯一索引来说，找到待插入的位置，然后判断待插入的数据有无重复性冲突，插入值，语句结束。
对于普通索引来说，找到待插入位置，插入值，语句结束。

当目标页在内存中时，唯一索引和普通索引在插入时性能差距微乎其微。

要更新的目标不在内存中

对于唯一索引来说，需要先将数据页读入内存，查询待插入数据是否已存在，判断没有冲突，插入这个值，语句执行结束。
对于普通索引来说，将更新记录在change buffer，语句执行结束。

当目标数据页不在内存中时，唯一索引需要将数据从磁盘读入到内存，涉及到IO随机访问，是数据库成本最高的操作之一，普通索引只是需要将数据记录在change buffer，change buffer减少了磁盘随机访问，对性能提升明显。

change buffer的使用场景

change buffer将变更记录缓存后，只有触发merge时才会真正更新落库，所以在change buffer在落库前，change buffer记录变更越多，减少磁盘IO次数越多，收益越大。对于写多读少的业务来说，写入完成后被马上访问的概率较少，change buffer使用效果最好。对于读多写少的场景，更新记录在change buffer后，写入完成可能会很快被读取，触发merge，随机访问IO次数增加。