走过路过不要错过
点击蓝字关注我们
0 概念区分
普通索引和唯一索引
普通索引可以重复, 唯一索引和主键一样不能重复.
唯一索引可以作为数据的一个合法验证手段, 例如学生表的身份证号码字段, 我们人为规定该字段不得重复, 那么就使用唯一索引.
(一般设置学号字段为主键)
主键和唯一索引
主键保证数据库里面的每一行都是唯一的, 比如身份证, 学号等, 在表中要求唯一, 不重复.
唯一索引的作用跟主键的作用一样.
不同的是, 在一张表里面只能有一个主键, 主键不能为空, 唯一索引可以有多个, 唯一索引可以有一条记录为空, 即保证跟别人不一样就行.
比如学生表, 在学校里面一般用学号做主键, 身份证则弄成唯一索引;
而到了教育局, 他们就把身份证号弄成主键, 学号换成了唯一索引.
选谁做表的主键, 要看实际应用, 主键不能为空.
1 示例
一个市民系统, 每个人都有个唯一身份证号;
业务代码已保证不会写入两个重复的身份证号;
如果市民系统需要按照身份证号查姓名, 就会执行类似 SQL:
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
相信你一定会在 id_card 字段上建索引.
由于身份证号字段比较大, 不建推荐把身份证号做主键.
因此现在有两个选择
给 id_card 字段创建唯一索引
创建一个普通索引
如果业务代码已保证不会写入重复的身份证号, 那这两个选择逻辑上都正确.
但从性能角度考虑, 唯一索引还是普通索引呢?
假设字段 k 上的值都不重复.
InnoDB 的索引组织结构
查询语句
select id from T where k=5
该语句在索引树查找的过程:
先通过 B + 树从树根开始, 按层搜索到叶节点, 即图中右下角的数据页, 然后可认为数据页内部是通过二分法定位记录.
对普通索引, 查找到满足条件的第一个记录 (5,500) 后, 需查找下个记录, 直到碰到第一个不满足 k=5 条件的记录
对唯一索引, 由于索引定义了唯一性, 查找到第一个满足条件的记录后, 就会停止检索.
该不同点带来的性能差距会有多少呢?
微乎其微!
InnoDB 数据是按数据页为单位读写. 即当需读一条记录时, 并非将该记录本身从磁盘读出, 而是以页为单位, 将其整体读入内存.
InnoDB 中, 每个数据页的大小默认是 16KB.
因引擎按页读写, 所以, 当找到 k=5 记录时, 它所在数据页就都在内存了.
对普通索引, 要多做的那一次 "查找和判断下一条记录" 的操作, 就只需要一次指针寻找和一次计算.
如果 k=5 记录刚好是该数据页的最后一个记录, 那么要取下个记录, 必须读取下个数据页, 操作会稍微复杂.
对于整型字段, 一个数据页可存近千个 key, 因此这种情况概率很低. 所以, 计算平均性能差异时, 仍可认为该操作成本对现在的 CPU 可忽略不计.
需更新一个数据页时
若数据页在内存, 直接更新
若该数据页不在内存, 在不影响数据一致性前提下, InooDB 会将这些更新操作缓存在 change buffer, 无需从磁盘读入该数据页.
在下次查询需要访问该数据页时, 将数据页读入内存, 然后执行 change buffer 中与这个页有关的操作.
通过该方式就能保证这个数据逻辑的正确性.
虽然叫 change buffer, 实际上是可持久化的数据.
即 change buffer 在内存中有拷贝, 也会被写进磁盘.
将 change buffer 中的操作应用到原数据页, 得到最新结果的过程.
访问该数据页会触发 merge
系统有后台线程会定期 merge
在数据库正常关闭 (shutdown) 的过程中, 也会执行 merge.
若能将更新操作先记录在 change buffer, 减少读盘, 语句执行速度会明显提升.
且数据读入内存需要占用 buffer pool, 所以该方式还能避免占用内存, 提高内存利用率.
对于唯一索引, 所有更新操作要先判断该操作是否违反唯一性约束.
比如, 要插入 (4,400) 记录, 要先判断表中是否已存 k=4 记录, 而这必须要将数据页读入内存才能判断.
如果都已经读入到内存, 那直接更新内存会更快, 就没必要使用 change buffer.
因此, 唯一索引的更新就不能使用 change buffer, 实际上也只有普通索引可使用.
change buffer 用的是 buffer pool 里的内存, 因此不能无限增大.
change buffer 的大小, 可通过参数 innodb_change_buffer_max_size 动态设置.
参数设置为 50 时, 表示 change buffer 的大小最多只能占用 buffer pool 的 50%.
理解了 change buffer 机制, 看看要在这张表中插入一个新记录(4,400),InnoDB 处理流程.
分情况讨论该记录要更新的目标页是否在内存中:
唯一索引
找到 3 和 5 之间位置, 判断到没有冲突, 插入值, 语句执行结束.
普通索引
找到 3 和 5 之间位置, 插入值, 语句执行结束.
普通索引和唯一索引对更新语句性能影响的差别, 只是一个判断, 只会耗费微小 CPU 时间.
唯一索引
需要将数据页读入内存, 判断到没有冲突, 插入值, 语句执行结束
普通索引
将更新记录在 change buffer, 语句执行结束
将数据从磁盘读入内存涉及随机 IO 访问, 是数据库里面成本最高操作之一.
change buffer 因减少随机磁盘访问, 所以对更新性能提升明显.
问题案例: 某业务的库内存命中率突然从 99% 降低到了 75%, 整个系统处于阻塞状态, 更新语句全部堵住.
探究其原因, 发现该业务有大量插入数据操作, 而 DBA 在前天把其中的某个普通索引改成了唯一索引.
普通索引的所有场景, 使用 change buffer 都可加速吗?
因为 merge 才是真正进行数据更新时刻;
change buffer 主要目的是将记录的变更动作缓存下来;
所以在一个数据页做 merge 前, change buffer 记录变更越多(即该数据页上要更新的次数越多), 收益越大.
对写多读少业务, 页面在写完后马上被访问到的概率较小, change buffer 使用效果最好. 该类业务模型常见为账单, 日志类的系统.
反之, 假设一业务的更新模式是写后马上查询, 那么即使满足条件, 将更新先记录在 change buffer, 但之后由于马上要访问该数据页, 立即触发 merge.
这样随机访问 IO 的次数不会减少, 反而增加 change buffer 维护代价.
所以, 对于这种业务模式, change buffer 起副作用.
普通索引和唯一索引如何抉择.
这两类索引在查询性能上没差别, 主要考虑对更新性能影响.
所以, 推荐尽量选择普通索引.
如果所有更新后面, 都紧跟对该记录的查询, 那么该关闭 change buffer.
而在其他情况下, change buffer 都能提升更新性能.
普通索引和 change buffer 的配合使用, 对于数据量大的表的更新优化还是很明显的.
在使用机械硬盘时, change buffer 机制的收效非常显著.
所以, 当你有一个类似 "历史数据" 的库, 并且出于成本考虑用机械硬盘时, 应该关注这些表里的索引, 尽量使用普通索引, 把 change buffer 开大, 确保 "历史数据" 表的数据写速度.
WAL 提升性能的核心机制, 也是尽量减少随机读写, 这两个概念易混淆.
所以, 这里我把它们放到了同一个流程里来说明区分.
在表上
insert into t(id,k) values(id1,k1),(id2,k2);
假设当前 k 索引树的状态, 查找到位置后
k1 所在数据页在内存(InnoDB buffer pool),k2 所在的数据页不在内存中
带 change buffer 的更新状态图.
该更新语句涉及四部分:
内存
redo log(ib_log_fileX)
数据表空间(t.ibd)
系统表空间(ibdata1)
该更新语句做了如下操作(按图中数字顺序):
Page1 在内存, 直接更新内存
Page2 没有在内存中, 就在内存的 change buffer 区, 记录下 "我要往 Page2 插一行" 的信息
将前两个动作记入 redo log(图中的 3 和 4)
做完上面, 事务完成. 执行这条更新语句的成本很低, 就写两处内存, 然后写一处磁盘(两次操作合在一起写了一次磁盘), 还是顺序写.
图中两个虚箭, 是后台操作, 不影响更新的响应时间.
这之后的读请求, 怎么处理?
现在执行
select * from t where k in (k1, k2)
若读语句紧随在更新语句后, 内存中的数据都还在, 那么此时这俩读操作就与系统表空间 (ibdata1) 和 redo log(ib_log_fileX)无关. 所以在图中就没画这俩.
两个读请求的流程图(带 change buffer 的读过程)
从图中可见:
读 Page1 时, 直接从内存返回.
WAL 之后如果读数据, 是不是一定要读盘, 是不是一定要从 redo log 里面把数据更新以后才可以返回? 其实不用.
看上图状态, 虽然磁盘上还是之前数据, 但这里直接从内存返回结果, 结果正确.
要读 Page2 时, 需把 Page2 从磁盘读入内存, 然后应用 change buffer 里面的操作日志, 生成一个正确版本并返回结果.
可见直到需读 Page2 时, 该数据页才被读入内存.
所以, 要简单对比这俩机制对更新性能影响
redo log 主要节省随机写磁盘的 IO 消耗(转成顺序写)
change buffer 主要节省随机读磁盘的 IO 消耗
由于唯一索引用不了 change buffer 的优化机制, 因此如果业务可以接受, 从性能角度, 推荐优先考虑非唯一索引.
主要纠结在 "业务可能无法确保". 本文前提是 "业务代码已经保证不会写入重复数据" 下, 讨论性能问题.
如果业务不能保证, 或者业务就是要求数据库来做约束, 那么没得选, 必须创建唯一索引.
这种情况下, 本文意义在于, 如果碰上大量插入数据慢, 内存命中率低时, 多提供一个排查思路.
然后, 在一些 "归档库" 的场景, 可考虑使用唯一索引的. 比如, 线上数据只需保留半年, 然后历史数据保存在归档库. 此时, 归档数据已是确保没有唯一键冲突. 要提高归档效率, 可考虑把表的唯一索引改普通索引.
参考并整理自《MySQL 实战 45 讲》
往期推荐
大厂 Java 面试题解(45)- 来设计个高并发系统?
一看就懂的 MySQL 行锁
一看就懂的图文讲解事务隔离
使用私有构造器或枚举来强化单例属性吧
好文! 点个好看!
来源: http://www.jianshu.com/p/4bdbbb7b4d58