原文《MySQL实战45讲》
假如你在维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名,就会执行类似的SQL语句:
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
所以,你一定会考虑在id_card字段上建索引。 由于身份证号字段比较大,我不建议你把身份证号当作主键,那么现在你有两个选择,要么给id_card字段创建唯一索引,要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号,那么这两个选择,逻辑上都是正确的。 现在我要问你的是,从性能的角度考虑,你选择唯一索引呢?还是普通索引?选择的依据是什么?
假设,我们有一个主键列为 ID 的表,表中有字段 k,并且在k上有索引。 这个表的建表语句是:
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;
表中R1~R5的(ID,k) 值分别为 (100, 1) 、 (200, 2) 、 (300, 3) 、 (400, 4) 、 (500, 5) 和 (600, 6) , 两棵树的示意图如下 : 接下来,我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。
假设,执行查询的语句是 select id from T where k = 5。这个查询语句在索引书上查找的过程,是先通过B+树丛树根开始,按层搜索都叶子节点,然后可以认为数据页内部通过二分法来定位记录。
但是这个不同会带来多少的性能差距呢?答案是,微乎其微。 因为,InnoDB的数据是按数据页为单位来读写的。也就是说,当需要都一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在InnoDB中,每个数据页的大小默认为16KB。所以,“查找和判断下一条记录”的操作,就只需要一个指针寻找和一次计算。当然,如果k=5这个记录刚好是这个数据也的最后一条记录,那么要取下一个记录,就必须读取下一页,这个操作会稍微复杂一些。但是,对于整形字段,一个数据页可以放近千个key,因此出现这种情况的概率很低。
一个数据页可以放多少个整形key呢? 答案是 16*1024/(8+6)≈1200。 其中,数据也 默认大小为16k,MySQL的bigint为8个字节,指针大小在InnoDB元宝吗中设置为6字节。
当需要更新一个数据页时,如果数据页在内存中,就直接更新,如果这个数据页没有在内存中,在不影响数据一致性的前提下,InnoDB会将更新操作缓存在change buffer 中,这样就不需要从磁盘中读入这个数据页了。若下次查询 需要访问该数据页,将数据页读入内存,然后执行change buffer中与这个页有关的操作,就能得到正确的数据。 现在,我们一起来看看,如果要在这张表中插入一个新纪录(4, 400)的话,InnoDB的处理流程是怎么样的。 第一种情况,要更新的记录所在的数据页在内存中,这种情况下普通索引和唯一索引对更新语句性能的差别,只是一个判断,只会耗费微笑的CPU时间。
第二中情况,要更新的记录所在的数据页不在内存中。
将数据从磁盘读入内存,涉及随机IO的访问,是数据库里面成本最高的操作之一。change buffer因为减少了随机磁盘访问,所以对更新性能的提升是很明显的。
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。