区别在于两个方面,查询和插入/更新
查询:
对于普通索引来说,查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到一个不满足条件的记录。
对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
故第一个区别在于,普通索引相对于唯一索引来说,普通索引可能会多查找一些记录。
除非一个索引键的key_len非常大(非常大则会超出一个数据页造成额外数据页读取IO),否则性能差距是微乎其微的。
因为一个数据页默认是16KB即能存的记录是非常多,就算普通索引可能会多扫描一些行记录,但这些行记录基本上是在同一个数据页中的,并不会造成额外的数据页读取IO。
插入:
在mysql中,插入和更新操作,若数据存在buffer pool中,则会直接更新buffer pool中的数据,待合适的机会再进行刷盘merge;
若数据不在buffer pool中,则会将更新或插入的数据,放在change buffer中,等到下次查询这些的时候,再将change buffer中新的数据对磁盘进行刷盘。
上述操作的好处是,每次更新数据,不需要立马进行IO,而是将新数据先放入change buffer,再在合适的机会进行刷盘,减少了IO次数。
普通索引更新插入,是可以使用到change buffer的,若数据存在于buffer pool,则更新buffer pool中的数据;
若数据不存在buffer pool,则将新数据存于change buffer中,再由合适的机会对磁盘进行merge操作。这样子的好处是能够减少IO。
唯一索引更新插入,若数据存于buffer pool,则需要新的数据与buffer pool中老的数据进行比较,保证数据的唯一性,
目前来说这点与普通索引的区别也只是多了个判断而已,差距微乎其微。但是若数据不存在于buffer pool中,因为要保证数据的唯一性,
则需要将数据页从磁盘中读取到内存,这就额外多了一次IO,这也不可避免。故无法使用change buffer,因为无论怎么样都需要磁盘进行IO,
与其加载到change buffer多这么一步,还不如直接进行磁盘IO。
结论:若业务已经指明了我们插入或者更新的字段不可能重复,则尽量选择普通索引
mysql普通索引和唯一索引的选择分析
于 2023-09-05 00:50:33 首次发布