MySQL中普通索引和独一索引的区别详解
MySQL中一般索引和独一索引的不同详解
一、查询和更新上的不同
这两类索引在查询能力上是没差异的,主要思考的是对更新机能的影响。倡议尽量选中一般索引。
(免费学习视频教程引荐:mysql视频教程)
1.1 MySQL 的查询操纵
■ 一般索引
查寻到第一个知足前提的记载后,连续向后遍历,直到第一个不知足前提的记载。
■ 独一索引
由于索引定义了独一性,查寻到第一个知足前提的记载后,直接休止连续检索。
一般索引会多检索一次,几乎没有影响。由于 InnoDB 的数据是依照数据页为单位停止读写的,需要读取数据时,并不是直接从磁盘读取记载,而是先把数据页读到内存,再去数据页中检索。
一个数据页默许 16 KB,关于整型字段,一个数据页可以放近千个 key,除非要读取的数据在数据页的最后一笔记录,就需要再读一个数据页,这种状况很少,对CPU的耗损根本可以忽略了。
因此说,在查询数据方面,一般索引和独一索引没差异。
1.2 MySQL 的更新操纵
更新操纵并不是直接对磁盘中的数据停止更新,是先把数据页从磁盘读入内存,再更新数据页。
■ 一般索引
将数据页从磁盘读入内存,更新数据页。
■ 独一索引
将数据页从磁盘读入内存,推断可否独一,再更新数据页。
由于 MySQL 中有个 change buffer 的机制,会致使一般索引和独一索引在更新上有必然的不同。
change buffer的作用是为了落低IO 操纵,幸免系统负载过高。change buffer将数据写入数据页的历程,叫做merge。
假如需要更新的数据页在内存中时,会直接更新数据页;假如数据不在内存中,会先将更新操纵记入change buffer,当下一次读取数据页时,顺带merge到数据页中,change buffer也有按期merge战略。数据库正常关闭的历程中,也会触发merge。
关于独一索引,更新前需要推断数据可否独一(不克不及和表中数据反复),假如数据页在内存中,就可以直接推断并且更新,假如不在内存中,就需要去磁盘中读出来,推断一下可否独一,是的话就更新。change buffer是用不到的。即便数据页不在内存中,还是要读出来。
change buffer 用的是 buffer pool 里的内存,因此不克不及无穷增大。change buffer 的大小,可以通过参数 innodb_change_buffer_max_size 来动态设定。这个参数设定为 50 的时候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
结论:独一索援用不了change buffer,只要一般索引可以用。
二、change buffer 和 redo log的不同
2.1 change buffer 的适用处景
change buffer 的作用是落低更新操纵的频率,缓存更新操纵。这样会有一个缺陷,就是更新不及时,关于读操纵比力频繁的表,不倡议使用 change buffer。
由于更新操纵刚记载进change buffer中,就读取了该表,数据页被读到了内存中,数据立刻就merge到数据页中了。这样不仅不会落低机能耗损,反而会增添保护change buffer的成本。
适用于写多读少的表。
2.2 change buffer 和 redo log 不同
我们举一个例子用来懂得 redo log 和 change buffer。我们施行以下 SQL 语句:
mysql> insert into t(id,k) values(id1,k1),(id2,k2);
假设,(id1,k1) 在数据页 Page 1 中,(id2,k2) 在数据页 Page 2 中。并且 Page 1 在内存中,Page 2 不在内存中。
施行历程如下:
直接向 Page 1 中写入 (id1,k1);
在change buffer 中记下"向 Page 2 中写入(id2,k2)"这条信息;
将以上两个动作记入redo log。
做完上面这些,事务就可以完成了。施行这条更新语句的成本很低,就是写了两处内存,然后写了一处磁盘(两次操纵合在一起写了一次磁盘),并且还是次序写的。
这条更新语句,触及了四个部分:内存、redo log(ib_log_fileX)、 数据表空间(t.ibd)、系统表空间(ibdata1)。
假如要读数据的话,历程是怎样的?
mysql> select * from t where k in (k1, k2);
假设读操纵在更新后不久,此时内存中还有 Page 1,没有 Page 2,那么读操纵就和 redo log 乃至 ibdata1 无关了。
从内存中猎取到 Page 1 上的最新数据 (id1,k1);
将数据页 Page 2 读入内存,施行merge 操纵,此时内存中的 Page 2 也有最新数据(id2,k2);
需要留意的是:
redo log中的数据,大概还没有 flush 到磁盘,磁盘中的 Page 1 和 Page 2 中并没有最新数据,但我们仍然可以拿到最新数据(内存中的 Page 1 就是最新的,Page 2 虽然不是最新的,但是从磁盘读到内存中后,施行了merge操纵,内存中的 Page 2 就是最新的了。)
假如此时 MySQL 非常宕机了,比方效劳器非常掉电,change buffer 中的数据会不会丢?
change buffer 中的数据分为两部分,一部分是已经merge到ibdata1中的数据,这部分数据已经耐久化,不会丧失。另一部分数据,还在 change buffer 中,没有merge 到ibdata1,分 3 种状况:
(1)change buffer 写入数据到内存,redo log 也已经写入(ib-log-filex),但是未 commit,binlog中也没有fsync到磁盘,这部分数据会丧失;
(2)change buffer 写入数据到内存,redo log 也已经写入(ib-log-filex),但是未 commit,binlog 已写入到磁盘,这部分不会多丧失,非常重新启动后会先从 binlog 复原 redo log,再从 redo log 复原 change buffer;
(3)change buffer 写入数据到内存,redo log 和 binlog 都已经fsync,直接从redo log 复原,不会丧失。
redo log 主要节约的是随机写磁盘的 IO 耗损(转成次序写),而 change buffer 主要节约的则是随机读磁盘的 IO 耗损
更多MySQL相关教程,请关注PHP中文网!
以上就是MySQL中一般索引和独一索引的不同详解的具体内容,更多请关注百分百源码网其它相关文章!