MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

09 | 普通索引和唯一索引,应该怎么选择?

林晓斌 2018-12-03
今天的正文开始前,我要特意感谢一下评论区几位留下高质量留言的同学。
用户名是 @某、人 的同学,对文章的知识点做了梳理,然后提了关于事务可见性的问题,就是先启动但是后提交的事务,对数据可见性的影响。@夏日雨同学也提到了这个问题,我在置顶评论中回复了,今天的文章末尾也会再展开说明。@Justin 和 @倪大人两位同学提了两个好问题。
对于能够引发更深一步思考的问题,我会在回复的内容中写上“好问题”三个字,方便你搜索,你也可以去看看他们的留言。
非常感谢大家很细致地看文章,并且留下了那么多和很高质量的留言。知道文章有给大家带来一些新理解,对我来说是一个很好的鼓励。同时,也让其他认真看评论区的同学,有机会发现一些自己还没有意识到的、但可能还不清晰的知识点,这也在总体上提高了整个专栏的质量。再次谢谢你们。
好了,现在就回到我们今天的正文内容。
在前面的基础篇文章中,我给你介绍过索引的基本概念,相信你已经了解了唯一索引和普通索引的区别。今天我们就继续来谈谈,在不同的业务场景下,应该选择普通索引,还是唯一索引?
假设你在维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名,就会执行类似这样的 SQL 语句:
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(250)

  • 某、人 置顶
    先回答今天的问题,今天的答案应该在文章里就能找到
    1.change buffer有一部分在内存有一部分在ibdata.
    做purge操作,应该就会把change buffer里相应的数据持久化到ibdata
    2.redo log里记录了数据页的修改以及change buffer新写入的信息
    如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据
    情况又分为以下几种:
    (1)change buffer写入,redo log虽然做了fsync但未commit,binlog未fsync到磁盘,这部分数据丢失
    (2)change buffer写入,redo log写入但没有commit,binlog以及fsync到磁盘,先从binlog恢复redo log,再从redo log恢复change buffer
    (3)change buffer写入,redo log和binlog都已经fsync.那么直接从redo log里恢复。

    老师,我有几个问题想请教下:
    1.如果是针对非唯一索引和唯一索引的更新和delete而且条件是where 索引值=这种情况,
    是否二级索引和唯一索引就没有区别呢
    2.rr模式下,非唯一索引还会加gap,开销应该也不算小吧
    3.还有老师你是怎么判断内存命中率的,是hit rate嘛?
    4.ob好像就是这个思路来做的聚簇索引的insert优化,不知道是怎么判断的唯一性

    特别感谢老师,以前的知识都是很碎片化,没有深入的去思考。
    经过几期的学习以后,感觉思路越来越开阔,以前觉得很高深的知识点,现在也有点豁然开朗的感觉。

    作者回复: 分析很赞,把02篇和这篇文章贯通了.

    问题
    1. 这时候要“先读后写”,读的时候数据会读入内存,更新的时候直接改内存,就不需要change buffer了

    2. Gap锁取决于业务怎么用哈。我感觉主要是因为gap锁概念比较难说清,大家有点放大它的意思了哈哈

    3. Hit rate

    4. 我不够熟悉,不能乱回答误导你😄

    2018-12-03
    5
    77
  • 林晓斌 置顶
    抱歉做一个名词勘误,把change buffer应用到旧的数据页,得到新的数据页的过程,应该称为merge更合适。
    2018-12-04
    40
  • 虚爱凯平 置顶
    感觉今天这篇问题很严重啊, 首先说一下我是第一次接触 change buffer这个概念, 可能产生了什么误会..
    我理解的文中讲述change buffer的作用体现在 针对普通索引(非主键的都是二级索引, 二级索引又包括了唯一索引和普通索引)在有数据update操作(不包括insert)的时候,能有减少io写操作的功能, 而且这个操作是提现在更新表数据上的. 为什么我在这里会理解成update操作呢.. (InnoDB中表就是按索引的方式存放的, 即使我们不主动创建主键 也会生成一个默认的row_id来当做主键, 意味着表一定是有一个主键, 即唯一索引. insert操作 一定会涉及主键索引的变动, 所以change buffer针对 insert 是完全没有用的吗??)

    针对change buffer 我百度了一下, 有文章描述 change buffer 是针对表中包含普通索引的表在insert操作时, 优化 普通索引的更新(在insert时,不会立即更新普通索引 而是保存到change buffer延迟处理). 这么一个功能. 不知道 这个理解是否正确呢?

    作者回复: 第一段的理解不准确哈。
    insert的时候,写主键是肯定不能用change buffer了,但是同时也会要写其它索引,而其它索引中的“非唯一索引”是可以用的这个机制的;

    第二段,你搜出来的这个不太完整。是这样的,change buffer的前身是insert buffer,只能对insert 操作优化;后来升级了,增加了update/delete的支持,名字也改叫change buffer.



    2018-12-03
    4
    23
  • WL 置顶
    想请教一下老师系统表空间跟数据表空间这两个概念各是什么意思.

    作者回复: 系统表空间就是用来放系统信息的,比如数据字典什么的,对应的磁盘文件是ibdata1,
    数据表空间就是一个个的表数据文件,对应的磁盘文件就是 表名.ibd

    2018-12-09
    22
  • 约书亚 置顶
    早,请您看看我以下疑问:
    1. 看完后感觉牵扯到之前的内容,又糊涂了。change buffer相当于推迟了更新操作,那对并发控制相关的是否有影响,比如加锁?我一直以为加锁需要把具体的数据页读到内存中来,才能加锁,然而并不是?
    2. 在change buffer中有此行记录的情况下,再次更改,是增加一条还是原地修改?
    3. purge行为之后应该不会再产生redo log了吧?

    从应用开发的角度看,还是由数据库保证唯一好。

    作者回复: 1 3 好问题

    1. 锁是一个单独的数据结构,如果数据页上有锁,change buffer 在判断“是否能用”的时候,就会认为否

    2. 增加

    3. 是这样的,这个问题你分成两步来考虑。
    第一步,merge其实是从磁盘读数据页到内存,然后应用,这一步都是更新的内存,同时写redolog

    现在内存变成脏页了,跟磁盘数据不一样。之后就走刷脏页的流程。刷脏页也不用写。


    =====

    是否使用唯一索引,这个要看业务有没有保证,和性能是否有问题。

    有几位同学都提了,我加到文末说明一下。

    2018-12-03
    3
    13
  • 永光
    会导致change buffer丢失,会导致本次未完成的操作数据丢失,但不会导致已完成操作的数据丢失。
    1.change buffer中分两部分,一部分是本次写入未写完的,一部分是已经写入完成的。
    2.针对未写完的,此部分操作,还未写入redo log,因此事务还未提交,所以没影响。
    2.针对,已经写完成的,可以通过redo log来进行恢复。

    所以,不会对数据库造成影响。

    作者回复: 优秀

    2018-12-03
    42
  • 包子木有馅
    老师你好,我说下我的理解,不知道有没有问题
    1、changebuffer跟普通数据页一样也是存在磁盘里,区别在于changebuffer是在共享表空间ibdata1里
    2、redolog有两种,一种记录普通数据页的改动,一种记录changebuffer的改动
    3、只要内存里脏页(innodb buffer pool)里的数据发生了变化,就一定会记录2中前一种redolog
    (对数据的修改记录在changebuffer里的时候,内存里是没有这个物理页的,不存在脏页)
    3、真正对磁盘数据页的修改是通过将内存里脏页的数据刷回磁盘来完成的,而不是根据redolog

    作者回复: 非常好,尤其括号里那句和最后一句

    2018-12-06
    3
    36
  • Ivan
    回答一下melon的问题。
    change Buffer和数据页一样,也是物理页的一个组成部分,数据结构也是一颗B+树,这棵B+树放在共享表空间中,默认ibdata1中。change buffer 写入系统表空间机制应该和普通表的脏页刷新到磁盘是相同的机制--Checkpoint机制;
    之所以change buffer要写入系统表空间,是为了保证数据的一致性,change buffer做修改时需要写redo,在做恢复时需要根据redo来恢复change buffer,若是不进行change buffer写入系统表空间,也就是不进行持久化,那么在change buffer写入内存后掉电(也就是篇尾提出的问题),则无法进行数据恢复。这样也会导致索引中的数据和相应表的相应列中的数据不一致。
    change buffer 写入到了系统表空间,purge 的时候会先查询change buffer里对应的记录,然后进行purge,因为change buffer B+树的key是表空间ID,所以查询根据表空间ID 查询change buffer会很快。

    作者回复: 👍🏿

    2018-12-03
    21
  • 晨思暮语
    丁老师,有一个问题不是很明白!最后小结之前,而change buffer 主要节省的则是随机读磁盘的IO消耗。这句话怎么理解,因为看前面的讲解,change buffer对性能的主要提升,是在一个写多读少的系统中,使用普通索引的情况下,合并多次写为一次来更新磁盘!
    2018-12-04
    3
    16
  • 壹笙☞漂泊
    前两次学了之后没时间总结。。今天继续
    总结:
    选择普通索引还是唯一索引?
    对于查询过程来说:
    a、普通索引,查到满足条件的第一个记录后,继续查找下一个记录,知道第一个不满足条件的记录
    b、唯一索引,由于索引唯一性,查到第一个满足条件的记录后,停止检索
    但是,两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。
    对于更新过程来说:
    概念:change buffer
    当需要更新一个数据页,如果数据页在内存中就直接更新,如果不在内存中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中的与这个页有关的操作。

    change buffer是可以持久化的数据。在内存中有拷贝,也会被写入到磁盘上

    purge:将change buffer中的操作应用到原数据页上,得到最新结果的过程,成为purge
    访问这个数据页会触发purge,系统有后台线程定期purge,在数据库正常关闭的过程中,也会执行purge

    唯一索引的更新不能使用change buffer

    change buffer用的是buffer pool里的内存,change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%。

    将数据从磁盘读入内存涉及随机IO的访问,是数据库里面成本最高的操作之一。
    change buffer 因为减少了随机磁盘访问,所以对更新性能的提升很明显。

    change buffer使用场景
    在一个数据页做purge之前,change buffer记录的变更越多,收益就越大。
    对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

    反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发purge过程。
    这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。所以,对于这种业务模式来说,change buffer反而起到了副作用。

    索引的选择和实践:
    尽可能使用普通索引。
    redo log主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的IO消耗。

    思考题:
    change buffer不会丢失,因为change buffer是可以持久化的数据,在磁盘上占据了系统表空间ibdata,对应的内部系统表名为SYS_IBUF_TABLE。因此在异常关机的时候,不会丢失。

    作者回复: 赞👍🏿

    不会丢失还有redolog的功劳哈

    2018-12-03
    2
    15
  • 虚爱凯平
    有点疑惑: 主键id也是唯一索引吧? 那我们的新增操作如何利用 change buffer呢?

    作者回复: 所以主键索引用不上,都是对于那些二级索引的才有效。

    一个insert语句要操作所有索引的嘛,收益在二级索引

    2018-12-03
    1
    15
  • Ivan
    不会导致change buffer丢失。因为在更改change buffer 时也会写redo log,也需要持久化。
    change buffer 更新完成并且相应事务提交的情况下,首先要保证redo log落盘(二阶段提交),若此时掉电重启,则可以根据 redo 进行恢复;
    若change buffer 更新完成但是相应事务未提交的情况下,则redo 有可能落盘了(redo 的组提交),也有可能未落盘,若落盘了,读取redo发现没有commit标志(还会进行lsn,binlog的对比),则回滚;若redo未落盘则也就不会出现前滚和回滚的情况,数据依旧一致。

    作者回复: 👍🏿

    拆开了分析很好

    2018-12-03
    11
  • 静以储势·Shuke
    要理解change buffer还得先理解buffer pool是啥,顾名思义,硬盘在读写速度上相比内存有着数量级差距,如果每次读写都要从磁盘加载相应数据页,DB的效率就上不来,因而为了化解这个困局,几乎所有的DB都会把缓存池当做标配(在内存中开辟的一整块空间,由引擎利用一些命中算法和淘汰算法负责维护和管理),change buffer则更进一步,把在内存中更新就能可以立即返回执行结果并且满足一致性约束(显式或隐式定义的约束条件)的记录也暂时放在缓存池中,这样大大减少了磁盘IO操作的几率

    作者回复: 👍🏿

    2018-12-05
    10
  • 看不到de颜色
    时隔一段时间再来回顾一遍,突然有了一个小问题,还望老师可以解答一下。
    总觉得将 change buffer落盘意义不大。毕竟redo log中也会记录。当数据库崩溃时可以通过redo log将change buffer内容回放出来。如果说因为内存不足需要回收change buffer这部分内存,那也应当将数据merge后刷入磁盘吧。
    不知道理解的是不是有误,还望老师指点迷津。

    作者回复: “总觉得将 change buffer落盘意义不大。毕竟redo log中也会记录,当数据库崩溃时可以通过redo log将change buffer内容回放出来。” 是的,所以change buffer其实也是用了WAL机制。

    “内存不足需要回收change buffer这部分内存“,只需要让change buffer本身持久化可以,不需要执行merge操作。merge操作是在读数据页的时候做的

    赞回顾的习惯哈👍

    2019-01-26
    7
  • yy
    老师好 看评论看蒙了 评论里说的purge与merge是一个意思吗

    作者回复: 抱歉,这里说的都是merge哈,是因为我之前写错了,你在这里看到的讨论都当作merge

    2018-12-05
    1
    7
  • Scott
    这一篇看得糊里糊涂,有两个问题
    1. 真正把数据更新到磁盘,是由change buffer做还是redo log做?
    2. 插入新的一行的话,一定会有唯一primary key的啊,这样是不是插入就不能用change buffer?

    作者回复: 1. 数据更新到磁盘是这两个都不少,是内存直接写到磁盘的。
    2. 插入数据的时候,主键索引用不上,但是普通索引可以

    看得糊里糊涂的时候可以看看评论区哈

    2019-01-02
    4
    5
  • 二哥很猛
    老师,你好,数据更新时,写入change buffer,立马读取这一条 不是应该直接从内存页读取吗,有必要把整个页读入内存吗,而且innoDB怎么知道应该加载哪一页到内存,我这一条记录并没有在数据页上有任何标示位啊

    作者回复: 1. 就是数据页没在内存,才能用上change buffer

    2. B+树有序的。能找到(你想,数据库一开始启动的时候,要找一个磁盘上的记录是怎么找到,一样的过程)

    2018-12-05
    5
  • 无菇朋友
    老师 我想请教一个问题:
    事务里的update都是当前读,当前读是否就是要把需要更新的数据页读取到内存中?如果是这样的话,那么change buffer在这个场景下是否就失去作用了
    2019-03-13
    2
    4
  • 侯占山
    这是到目前为止我唯一一篇看不懂的文章
    2018-12-28
    4
  • 憶海拾貝
    唯一字段不加唯一索引, 墨菲定律会出来搞事. -- 记一次踩坑经验..

    作者回复: 嗯这里其实要说等是“业务要保证”
    当要性能问题的时候,多一个check的点

    2018-12-03
    4
收起评论
99+
返回
顶部