最新公告
  • 欢迎您光临AA分享网,一个高级程序员的学习、分享的分享平台!立即加入我们
  • MySQL普通索引和唯一索引有哪些区别?

    数据库是一种以某种方式存储在一起的数据集合,可以与多个用户共享,具有最小的可能冗余,并且独立于应用程序。可以将其视为电子文件柜-存储电子文件的地方,用户可以在文件中添加,查询,更新和删除数据。数据库的种类有很多,其中MySQL数据库中有普通索引和唯一索引两种,很多人都不知道二者之间的区别。

      MySQL普通索引和唯一索引有哪些区别?

      一、查询和更新上的区别

    这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响。建议尽量选择普通索引。

      1.1MySQL的查询操作

    ▶普通索引

    查找到第一个满足条件的记录后,继续向后遍历,直到第一个不满足条件的记录。

    ▶唯一索引

    由于索引定义了唯一性,查找到第一个满足条件的记录后,直接停止继续检索。

    普通索引会多检索一次,几乎没有影响。因为InnoDB的数据是按照数据页为单位进行读写的,需要读取数据时,并不是直接从磁盘读取记录,而是先把数据页读到内存,再去数据页中检索。

    一个数据页默认16KB,对于整型字段,一个数据页可以放近千个key,除非要读取的数据在数据页的最后一条记录,就需要再读一个数据页,这种情况很少,对CPU的消耗基本可以忽略了。

    因此说,在查询数据方面,普通索引和唯一索引没差别。

      1.2MySQL的更新操作

    更新操作并不是直接对磁盘中的数据进行更新,是先把数据页从磁盘读入内存,再更新数据页。

    ▶普通索引

    将数据页从磁盘读入内存,更新数据页。

    ▶唯一索引

    将数据页从磁盘读入内存,判断是否唯一,再更新数据页。

    由于MySQL中有个changebuffer的机制,会导致普通索引和唯一索引在更新上有一定的区别。

    changebuffer的作用是为了降低IO操作,避免系统负载过高。changebuffer将数据写入数据页的过程,叫做merge。

    如果需要更新的数据页在内存中时,会直接更新数据页;如果数据不在内存中,会先将更新操作记入changebuffer,当下一次读取数据页时,顺带merge到数据页中,changebuffer也有定期merge策略。数据库正常关闭的过程中,也会触发merge。

    对于唯一索引,更新前需要判断数据是否唯一(不能和表中数据重复),如果数据页在内存中,就可以直接判断并且更新,如果不在内存中,就需要去磁盘中读出来,判断一下是否唯一,是的话就更新。changebuffer是用不到的。即使数据页不在内存中,还是要读出来。

    changebuffer用的是bufferpool里的内存,因此不能无限增大。changebuffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示changebuffer的大小最多只能占用bufferpool的50%。

    结论:唯一索引用不了changebuffer,只有普通索引可以用。

      二、changebuffer和redolog的区别

    2.1changebuffer的适用场景

    changebuffer的作用是降低更新操作的频率,缓存更新操作。这样会有一个缺点,就是更新不及时,对于读操作比较频繁的表,不建议使用changebuffer。

    因为更新操作刚记录进changebuffer中,就读取了该表,数据页被读到了内存中,数据马上就merge到数据页中了。这样不仅不会降低性能消耗,反而会增加维护changebuffer的成本。

    2.2changebuffer和redolog区别

    我们举一个例子用来理解redolog和changebuffer。我们执行以下SQL语句:

    mysql>insertintot(id,k)values(id1,k1),(id2,k2);

    假设,(id1,k1)在数据页Page1中,(id2,k2)在数据页Page2中。并且Page1在内存中,Page2不在内存中。

    执行过程如下:

    ▶直接向Page1中写入(id1,k1);

    ▶在changebuffer中记下"向Page2中写入(id2,k2)"这条信息;

    ▶将以上两个动作记入redolog。

    做完上面这些,事务就可以完成了。执行这条更新语句的成本很低,就是写了两处内存,然后写了一处磁盘(两次操作合在一起写了一次磁盘),而且还是顺序写的。

    这条更新语句,涉及了四个部分:内存、redolog(ib_log_fileX)、数据表空间(t.ibd)、系统表空间(ibdata1)。

    如果要读数据的话,过程是怎样的?

    mysql>select*fromtwherekin(k1,k2);

    假设读操作在更新后不久,此时内存中还有Page1,没有Page2,那么读操作就和redolog以及ibdata1无关了。

    ▶从内存中获取到Page1上的最新数据(id1,k1);

    ▶将数据页Page2读入内存,执行merge操作,此时内存中的Page2也有最新数据(id2,k2);

      需要注意的是:

    ▶redolog中的数据,可能还没有flush到磁盘,磁盘中的Page1和Page2中并没有最新数据,但我们依然可以拿到最新数据(内存中的Page1就是最新的,Page2虽然不是最新的,但是从磁盘读到内存中后,执行了merge操作,内存中的Page2就是最新的了。)

    ▶如果此时MySQL异常宕机了,比如服务器异常掉电,changebuffer中的数据会不会丢?

    changebuffer中的数据分为两部分,一部分是已经merge到ibdata1中的数据,这部分数据已经持久化,不会丢失。另一部分数据,还在changebuffer中,没有merge到ibdata1,分3种情况:

    ▶changebuffer写入数据到内存,redolog也已经写入(ib-log-filex),但是未commit,binlog中也没有fsync到磁盘,这部分数据会丢失;

    ▶changebuffer写入数据到内存,redolog也已经写入(ib-log-filex),但是未commit,binlog已写入到磁盘,这部分不会多丢失,异常重启后会先从binlog恢复redolog,再从redolog恢复changebuffer;

    ▶changebuffer写入数据到内存,redolog和binlog都已经fsync,直接从redolog恢复,不会丢失。

    redolog主要节省的是随机写磁盘的IO消耗(转成顺序写),而changebuffer主要节省的则是随机读磁盘的IO消耗。

    以上就是关于MySQL普通索引和唯一索引有哪些区别的全部内容介绍,想了解更多关于数据库的信息,请继续关注。

    AA分享网一个高级程序员的学习、分享的IT资源分享平台
    AA分享网-企业网站源码-PHP源码-网站模板-视频教程-IT技术教程 » MySQL普通索引和唯一索引有哪些区别?
    • 277会员总数(位)
    • 6130资源总数(个)
    • 0本周发布(个)
    • 0 今日发布(个)
    • 784稳定运行(天)

    提供最优质的资源集合

    立即查看 了解详情