- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章MySQL 普通索引和唯一索引的区别详解由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
。
普通索引可重复,唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键) 。
主键保证数据库里面的每一行都是唯一的,比如身份证,学号等,在表中要求唯一,不重复。唯一索引的作用跟主键的作用一样。 不同的是,在一张表里面只能有一个主键,主键不能为空,唯一索引可以有多个,唯一索引可以有一条记录为空,即保证跟别人不一样就行。 比如学生表,在学校里面一般用学号做主键,身份证则弄成唯一索引;而到了教育局,他们就把身份证号弄成主键,学号换成了唯一索引。 选谁做表的主键,要看实际应用,主键不能为空.
。
某居民系统,每人有唯一身份证号。如果系统需要按身份证号查姓名,就会执行类似如下sql:
1
|
select
name
from
cuser
where
id_card =
'ooxx'
;
|
然后你肯定会在id_card字段建索引。但id_card字段较大,不推荐将其做主键。于是现有俩选择:
假定业务代码已保证不会写入重复的身份证号,这两个选择逻辑上都正确。但从性能角度考虑,唯一索引还是普通索引呢?
再看如下案例:假设字段 k 上的值都不重复.
接下来分析性能.
。
1
|
select
id
from
t
where
k=4
|
通过b+树从树根开始层序遍历到叶节点,可认为数据页内部是通过二分法搜索.
看起来性能差距很微小.
innodb数据按数据页单位读写。即读一条记录时,并非将该一个记录从磁盘读出,而以页为单位,将其整体读入内存.
因此普通索引,要多做一次“查找和判断下一条记录”的操作,也就一次指针寻找和一次计算。 如果k=4记录恰为该数据页最后一个记录,那么要取下个记录,还得读取下个数据页,操作稍微复杂。 对整型字段,一个数据页可存近千key,因此这种情况概率其实也很低。因此计算平均性能差异时,可认为该操作成本对现在cpu开销忽略不计.
我们知道 mysql 有 change buffer.
。
现在来看往表中插入一个新记录(4,400),innodb会做什么?
需要区分该记录要更新的目标页是否在内存:
4.1 在内存 。
找到3和5之间位置,判断到没有冲突,插入值,语句执行结束.
找到3和5之间位置,插入值,语句执行结束.
普通索引和唯一索引对更新语句性能影响的差别,只是一个判断,耗费微小cpu时间.
4.2 不在内存 。
需将数据页读入内存,判断到没有冲突,插入值,语句执行结束.
将更新记录在change buffer,语句执行结束.
将数据从磁盘读入内存涉及随机io访问,是数据库里面成本最高操作之一。而change buffer减少随机磁盘访问,所以更新性能提升明显.
。
普通索引和唯一索引究竟如何抉择?这两类索引在查询性能上没差别,主要考虑对更新性能影响。所以,推荐尽量选择普通索引.
如果所有更新后面,都紧跟对该记录的查询,那么该关闭change buffer。 而在其他情况下,change buffer都能提升更新性能。 普通索引和change buffer的配合使用,对于数据量大的表的更新优化还是很明显的.
在使用机械硬盘时,change buffer机制的收效非常显著。 所以,当你有一个类似“历史数据”的库,并且出于成本考虑用机械硬盘时,应该关注这些表里的索引,尽量使用普通索引,把change buffer 开大,确保“历史数据”表的数据写速度.
。
wal 提升性能的核心机制,也是尽量减少随机读写,这两个概念易混淆。 所以,这里我把它们放到了同一个流程里来说明区分.
6.1 插入流程 。
1
|
insert
into
t(id,k)
values
(id1,k1),(id2,k2);
|
假设当前k索引树的状态,查找到位置后,k1所在数据页在内存(innodb buffer pool),k2数据页不在内存.
该更新做了如下操作:
之后事务完成。执行该更新语句成本很低,只写两处内存,然后写一处磁盘(前两次操作合在一起写了一次磁盘),还是顺序写.
6.2 怎么处理之后的读请求?
1
|
select
*
from
t
where
k
in
(k1, k2);
|
读语句紧随更新语句,内存中的数据都还在,此时这俩读操作就与系统表空间和 redo log 无关。所以在图中就没画这俩.
读page1时,直接从内存返回。 wal之后如果读数据,是不是一定要读盘,是不是一定要从redo log里面把数据更新以后才可以返回?其实不用。 看上图状态,虽然磁盘上还是之前数据,但这里直接从内存返回结果,结果正确.
要读page2时,需把page2从磁盘读入内存,然后应用change buffer里面的操作日志,生成一个正确版本并返回结果。 可见直到需读page2时,该数据页才被读入内存.
所以,要简单对比这俩机制对更新性能影响 。
。
由于唯一索引用不了change buffer的优化机制,因此如果业务可以接受,从性能角度,推荐优先考虑非唯一索引.
7.1 关于到底是否使用唯一索引 。
主要纠结在“业务可能无法确保”。本文前提是“业务代码已经保证不会写入重复数据”下,讨论性能问题.
如果业务不能保证,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。这种情况下,本文意义在于,如果碰上大量插入数据慢、内存命中率低时,多提供一个排查思路。 然后,在一些“归档库”的场景,可考虑使用唯一索引的。比如,线上数据只需保留半年,然后历史数据保存在归档库。此时,归档数据已是确保没有唯一键冲突。要提高归档效率,可考虑把表的唯一索引改普通索引.
7.2 如果某次写入使用change buffer,之后主机异常重启,是否会丢失change buffer的数据?
不会丢失。 虽然是只更新内存,但在事务提交时,我们把change buffer的操作也记录到redo log,所以崩溃恢复时,change buffer也能找回.
7.3 merge的过程是否会把数据直接写回磁盘?
该redo log包含数据的变更和change buffer的变更 。
至此merge过程结束。 这时,数据页和内存中change buffer对应磁盘位置都尚未修改,是脏页,之后各自刷回自己物理数据,就是另外一过程.
。
在构造第一个例子的过程,通过session a的配合,让session b删除数据后又重新插入一遍数据,然后就发现explain结果中,rows字段从10001变成37000多。 而如果没有session a的配合,只是单独执行delete from t 、call idata()、explain这三句话,会看到rows字段其实还是10000左右。这是什么原因呢?
如果没有复现,检查 。
为什么经过这个操作序列,explain的结果就不对了? delete 语句删掉了所有的数据,然后再通过call idata()插入了10万行数据,看上去是覆盖了原来10万行。 但是,session a开启了事务并没有提交,所以之前插入的10万行数据是不能删除的。这样,之前的数据每行数据都有两个版本,旧版本是delete之前数据,新版本是标记deleted的数据。 这样,索引a上的数据其实有两份.
然后你会说,不对啊,主键上的数据也不能删,那没有使用force index的语句,使用explain命令看到的扫描行数为什么还是100000左右?(潜台词,如果这个也翻倍,也许优化器还会认为选字段a作为索引更合适) 是的,不过这个是主键,主键是直接按照表的行数来估计的。而表的行数,优化器直接用的是show table status的值。 大家的机器如果io能力比较差的话,做这个验证的时候,可以把innodb_flush_log_at_trx_commit 和 sync_binlog 都设置成0.
以上就是mysql 普通索引和唯一索引的区别详解的详细内容,更多关于mysql 普通索引和唯一索引的资料请关注我其它相关文章! 。
原文链接:https://juejin.cn/post/6935324171589124104 。
最后此篇关于MySQL 普通索引和唯一索引的区别详解的文章就讲到这里了,如果你想了解更多关于MySQL 普通索引和唯一索引的区别详解的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
平时很少在jquery中用到this。查看代码时发现用到了,就调试出this的值,心想原来如此。还是挺有用的。这里总结一下this与$(this)的区别和使用。 $(this)生成的是什么?
使用单例类和应用程序范围的托管 bean 来保存应用程序数据有区别吗? 我需要查找某些 JNDI 资源,例如远程 bean 接口(interface),因此我为自己编写了一个单例来缓存我的引用并且只允
如果您仔细查看包含的图片,您会注意到您可以使用 Eclipse IDE 重构 Groovy 代码并将方法转换为闭包,反之亦然。那么,闭包到底是什么,它与方法有什么不同呢?有人可以举一个使用闭包的好例子
vagrant box repackage有什么区别( docs ) 和 vagrant package ( docs )? 我意识到 vagrant package仅适用于 VirtualBox 提
我想看看是否有人可以解释为什么以下代码适用于 valueOf 但不适用于其他代码。 import java.math.BigDecimal; public class Change { publ
这个问题已经有答案了: 已关闭12 年前。 Possible Duplicates: What is Closures/Lambda in PHP or Javascript in layman te
This question already has answers here: Vagrant, Docker, Puppet, Chef (3个答案) 2年前关闭。 docker和chef有什么共同
以下代码在95%的机器上产生相同的输出,但是在几台机器上却有所不同。在 Debug模式下,输出: Changing from New to Fin OK 但在 Release模式下: Changing
////Creating Object var Obj; // init Object Obj= {}; 它们之间有什么区别两个? 有没有可能把它变成一个单行? 这样使用有什么好处吗?
我想找出定时器服务之间的区别。我应该使用哪个以及何时使用。我正在使用 Jboss 应用服务器。 1) java.ejb.Schedule。 @Schedule注解或配置自xml。 2) javax.e
我发现在 C++ 中可以通过三种不同的方式将对象传递给函数。假设我的类(class)是这样的: class Test { int i; public: Test(int x);
有什么区别。 public class Test { public static void main(String args[]) { String toBeCast = "c
如果我有一列,设置为主索引,设置为INT。 如果我不将其设置为自动递增,而只是将唯一的随机整数插入其中,与自动递增相比,这是否会减慢 future 的查询速度? 如果我在主索引和唯一索引为 INT 的
这两种日期格式有什么区别。第一个给出实际时间,第二个给出时间购买添加时区偏移值。 NSDateFormatter * dateFormatter = [[NSDateFormatter alloc]
如果有一个函数,请说foo: function foo() { console.log('bar'); } 那么在 JavaScript 中,从另一个函数调用一个函数有什么区别,如下所示: f
关闭。这个问题是opinion-based 。目前不接受答案。 想要改进这个问题吗?更新问题,以便 editing this post 可以用事实和引文来回答它。 . 已关闭 4 年前。 Improv
代码是什么: class Time { private: int hours; int minutes; int seconds; pu
我知道这是非常基本的,但有人介意解释一下这两个数组声明之间的区别吗: #include array myints; ...和: int myints[5]; ...以及为什么 myints.size
我学会了如何根据 http://reference.sitepoint.com/css/specificity 计算 css 特异性但是,基于this reference,我不明白伪类(来自c)和伪元
为什么在运行 2) 时会出现额外的空行?对我来说 1 就像 2。那么为什么 2) 中的额外行? 1) export p1=$(cd $(dirname $0) && pwd) #
我是一名优秀的程序员,十分优秀!