网站集约化建设的讲话,建设一个视频网站需要什么条件,面包屑导航 wordpress,菏泽网站建设公司排名1 概念区分
普通索引和唯一索引
普通索引可重复#xff0c;唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段#xff0c;例如学生表的身份证号码字段#xff0c;我们人为规定该字段不得重复#xff0c;那么就使用唯一索引。#xff08;一般设置学号字…1 概念区分
普通索引和唯一索引
普通索引可重复唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段例如学生表的身份证号码字段我们人为规定该字段不得重复那么就使用唯一索引。一般设置学号字段为主键
主键和唯一索引
主键保证数据库里面的每一行都是唯一的比如身份证学号等在表中要求唯一不重复。唯一索引的作用跟主键的作用一样。 不同的是在一张表里面只能有一个主键主键不能为空唯一索引可以有多个唯一索引可以有一条记录为空即保证跟别人不一样就行。 比如学生表在学校里面一般用学号做主键身份证则弄成唯一索引而到了教育局他们就把身份证号弄成主键学号换成了唯一索引。 选谁做表的主键要看实际应用主键不能为空。
2 案例引入
某居民系统每人有唯一身份证号。如果系统需要按身份证号查姓名就会执行类似如下SQL
select name from CUser where id_card ooxx;然后你肯定会在id_card字段建索引。但id_card字段较大不推荐将其做主键。于是现有俩选择
给id_card字段创建唯一索引创建一个普通索引
假定业务代码已保证不会写入重复的身份证号这两个选择逻辑上都正确。但从性能角度考虑唯一索引还是普通索引呢
再看如下案例假设字段 k 上的值都不重复。
InnoDB的索引组织结构 接下来分析性能。
3 查询性能
select id from T where k4通过B树从树根开始层序遍历到叶节点可认为数据页内部是通过二分法搜索。
普通索引查找到满足条件的第一个记录(4,400)后需查找下个记录直到碰到第一个不满足k4的记录唯一索引由于索引具备唯一性查找到第一个满足条件的记录后就会停止检索 看起来性能差距很微小。
InnoDB数据按数据页单位读写。即读一条记录时并非将该一个记录从磁盘读出而以页为单位将其整体读入内存。
因此普通索引要多做一次“查找和判断下一条记录”的操作也就一次指针寻找和一次计算。 如果k4记录恰为该数据页最后一个记录那么要取下个记录还得读取下个数据页操作稍微复杂。 对整型字段一个数据页可存近千key因此这种情况概率其实也很低。因此计算平均性能差异时可认为该操作成本对现在CPU开销忽略不计。
我们知道 MySQL 有 change buffer。
4 更新性能
现在来看往表中插入一个新记录(4,400)InnoDB会做什么
需要区分该记录要更新的目标页是否在内存
4.1 在内存 唯一索引 找到3和5之间位置判断到没有冲突插入值语句执行结束。 普通索引 找到3和5之间位置插入值语句执行结束。
普通索引和唯一索引对更新语句性能影响的差别只是一个判断耗费微小CPU时间。
4.2 不在内存 唯一索引 需将数据页读入内存判断到没有冲突插入值语句执行结束。 普通索引 将更新记录在change buffer语句执行结束。
将数据从磁盘读入内存涉及随机IO访问是数据库里面成本最高操作之一。而change buffer减少随机磁盘访问所以更新性能提升明显。
5 实践中的索引选择
普通索引和唯一索引究竟如何抉择这两类索引在查询性能上没差别主要考虑对更新性能影响。所以推荐尽量选择普通索引。
如果所有更新后面都紧跟对该记录的查询那么该关闭change buffer。 而在其他情况下change buffer都能提升更新性能。 普通索引和change buffer的配合使用对于数据量大的表的更新优化还是很明显的。
在使用机械硬盘时change buffer机制的收效非常显著。 所以当你有一个类似“历史数据”的库并且出于成本考虑用机械硬盘时应该关注这些表里的索引尽量使用普通索引把change buffer 开大确保“历史数据”表的数据写速度。
6 change buffer 和 redo log
WAL 提升性能的核心机制也是尽量减少随机读写这两个概念易混淆。 所以这里我把它们放到了同一个流程里来说明区分。
6.1 插入流程
insert into t(id,k) values(id1,k1),(id2,k2);假设当前k索引树的状态查找到位置后k1所在数据页在内存(InnoDB buffer pool)k2数据页不在内存。
带change buffer的更新流程图图中两个箭头都是后台操作不影响更新响应。 该更新做了如下操作
Page1在内存直接更新内存Page2不在内存就在change buffer区缓存下“往Page2插一行记录”的信息将前两个动作记入redo log
之后事务完成。执行该更新语句成本很低只写两处内存然后写一处磁盘前两次操作合在一起写了一次磁盘还是顺序写。
6.2 怎么处理之后的读请求
select * from t where k in (k1, k2);读语句紧随更新语句内存中的数据都还在此时这俩读操作就与系统表空间和 redo log 无关。所以在图中就没画这俩。
带change buffer的读过程 读Page1时直接从内存返回。 WAL之后如果读数据是不是一定要读盘是不是一定要从redo log里面把数据更新以后才可以返回其实不用。 看上图状态虽然磁盘上还是之前数据但这里直接从内存返回结果结果正确。
要读Page2时需把Page2从磁盘读入内存然后应用change buffer里面的操作日志生成一个正确版本并返回结果。 可见直到需读Page2时该数据页才被读入内存。
所以要简单对比这俩机制对更新性能影响
redo log 主要节省随机写磁盘的IO消耗转成顺序写change buffer主要节省随机读磁盘的IO消耗
7 总结
由于唯一索引用不了change buffer的优化机制因此如果业务可以接受从性能角度推荐优先考虑非唯一索引。
7.1 关于到底是否使用唯一索引
主要纠结在“业务可能无法确保”。本文前提是“业务代码已经保证不会写入重复数据”下讨论性能问题。
如果业务不能保证或者业务就是要求数据库来做约束那么没得选必须创建唯一索引。这种情况下本文意义在于如果碰上大量插入数据慢、内存命中率低时多提供一个排查思路。 然后在一些“归档库”的场景可考虑使用唯一索引的。比如线上数据只需保留半年然后历史数据保存在归档库。此时归档数据已是确保没有唯一键冲突。要提高归档效率可考虑把表的唯一索引改普通索引。
7.2 如果某次写入使用change buffer之后主机异常重启是否会丢失change buffer的数据
不会丢失。 虽然是只更新内存但在事务提交时我们把change buffer的操作也记录到redo log所以崩溃恢复时change buffer也能找回。
7.3 merge的过程是否会把数据直接写回磁盘
merge执行流程
从磁盘读入数据页到内存老版本数据页从change buffer找出该数据页的change buffer 记录(可能有多个依次应用得到新版数据页写redo log
该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左右。这是什么原因呢
如果没有复现检查
隔离级别是不是RRRepeatable Read可重复读创建的表t是不是InnoDB引擎
为什么经过这个操作序列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。
