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讲
登录|注册

45 | 自增id用完怎么办?

林晓斌 2019-02-25
MySQL 里有很多自增的 id,每个自增 id 都是定义了初始值,然后不停地往上加步长。虽然自然数是没有上限的,但是在计算机里,只要定义了表示这个数的字节长度,那它就有上限。比如,无符号整型 (unsigned int) 是 4 个字节,上限就是 232-1。
既然自增 id 有上限,就有可能被用完。但是,自增 id 用完了会怎么样呢?
今天这篇文章,我们就来看看 MySQL 里面的几种自增 id,一起分析一下它们的值达到上限以后,会出现什么情况。

表定义自增值 id

说到自增 id,你第一个想到的应该就是表结构定义里的自增字段,也就是我在第 39 篇文章《自增主键为什么不是连续的?》中和你介绍过的自增主键 id。
表定义的自增值达到上限后的逻辑是:再申请下一个 id 时,得到的值保持不变。
我们可以通过下面这个语句序列验证一下:
create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/
insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'
可以看到,第一个 insert 语句插入数据成功后,这个表的 AUTO_INCREMENT 没有改变(还是 4294967295),就导致了第二个 insert 语句又拿到相同的自增 id 值,再试图执行插入语句,报主键冲突错误。
232-1(4294967295)不是一个特别大的数,对于一个频繁插入删除数据的表来说,是可能会被用完的。因此在建表的时候你需要考察你的表是否有可能达到这个上限,如果有可能,就应该创建成 8 个字节的 bigint unsigned。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(115)

  • Continue
    跟着学了三个多月,受益匪浅,学到了很多新的知识和其中的原理!

    作者回复: 早🤝

    2019-02-25
    31
  • 克劳德
    本人服务端工程师,在学习这门课之前数据库一直是我的短板,曾听朋友说MySQL或数据库中涉及了很多方面的知识点,每一个拿出来展开讲几乎都能出一本书了,对数据库是越来越忌惮,同时也因为工作上并没有过多接触,水平便一直停留在编写简单SQL层面。
    在面试中被问到数据库问题,只能无奈的说这块不太清楚,也曾在网上自学过,但网上的文章知识点比较零散,很多都是给出一些结论性的观点,由于不了解其内部原理,记忆很难深刻。
    老实说,当初报这门课的时候就像买技术书籍一样,我相信大家都有这样的体会,以为买到了就等于学到了,所以有一段时间没有点开看过,以至于后面开始学的时候都是在追赶老师和大家的进度,唯一遗憾的地方就是没能跟老师及时留言互动。
    这门课虽然是文字授课,但字里行间给我的感觉就是很亲切很舒服,为什么呢,因为老师可以把晦涩的知识变得通俗易懂,有时我在思考,如果让我来讲一个自己擅长的领域是否也能做到这一点,如果要做到的话需要什么样的知识储备呢。
    最后真要感谢老师的这门课,让我从心里不再惧怕数据库问题,不管是工作还是面试中信心倍增,现在时不时都敢和我们DBA“切磋切磋“了,哈哈。
    祝好~

    作者回复: 👍“切磋切磋“

    留言不会“过时”哈,在对应的章节下面提出相关的问题,我会持续关注评论区

    2019-02-25
    27
  • 夜空中最亮的星(华仔)
    不知道是最后一篇,否则的话就慢些读完了;
    我是一名运维,公司也没有DBA,所以MySQL库也归我收拾;
    读了老师的专栏,操作起数据库来,心情更好了;
    老师的课,让我有了想看完《高性能MySQL》的兴趣;
    听了老师的课,开发都来问我数据库的问题了,高兴;
    老师你会有返场吗?我猜会 😄
    可否透漏下接下来的安排,会有续集吗?进阶吗?
    不想这一别就是一生。
    您的从未谋面的学生。

    作者回复: 谢谢你

    “开发都来问我数据库的问题了”,当年我也是这么开始“入坑”,加油

    2019-02-25
    17
  • 三胖
    老师,我才学了四分之一的课程,但是这门课已经更新完了,我是直接跑到最后一节技术篇来留言的!很想知道,后来者比如我在学到后面的课程时遇到问题留言,老师还会看会回复吗?(老师的课程超值!!)

    作者回复: 会看的

    后台系统是按照留言时间显示的
    而且我在这事情上有强迫症,一定会让“未处理问题”变成0的😆

    只是说如果是其他同学评论区问过的问题,我可能就不会重复回复了

    2019-02-25
    8
  • 坚持去学习
    菜鸟一枚才学浅
    走马观花看专栏
    一生运维甚可叹
    点滴积累玩不转
    老师功底很深厚
    尔等求学莫欺骗
    谦虚从容学知识
    十年寒窗磨一剑
    运维坎坷路漫长
    风雨无阻永相伴
    尔等成名归来时
    扶摇直上亦无怨
    感谢老师传知识
    功德无量真佛现
    2019-04-28
    7
  • 某、人
    很遗憾没能坚持到最后,但是也很庆幸能遇到这么好的专栏。以前了解mysql都是一些零散的知识点,通过学习完专栏,不论是mysql整体架构还是基础的知识点,都有了更深的认识。以后就把老师的文档当官方文档查,出现问题先来看看专栏。
    感触特别深的是,老师对于提到的每一个问题,都会严谨又认真的去回答,尽量帮助每一位同学都能有所收获。要做到这一点,是特别耗费精力的。
    感谢老师的传道授业解惑,希望以后有机会能当面向老师请教问题。期待老师下一部杰作

    作者回复: 刚过完年都是很忙的, 找时间补上哈,等你的评论区留言^_^

    2019-02-26
    6
  • 张珂
    我觉得是这样的,人的记忆是结构化的。

    如果用纯文字做读书笔记,那么一段时间之后,再来看笔记,还得根据文字重建该结构。
    倒不如直接看结构化的读书笔记,省去大脑再次重建的繁琐过程。

    真是文不如表,表不如图,图不如动画啊。

    下面是我的《MySQL实战》的PPT形式的读书笔记,如果想复习,就快速浏览PPT,就能快速重建记忆。
    https://github.com/zhangkekf/reading-notes/tree/master/MySQL%E5%AE%9E%E6%88%98

    目前才更新到了39小节,当然会持续更新,如果有时间会做成动画。再次感谢林老师!

    作者回复: 这也太棒了吧,👍

    2019-06-19
    1
    5
  • NoDBA
    低版本thread_id超过2^32-1后,在general log显示是负数,高版本貌似没有这个问题,是否高版本的thread_id是8字节呢?

    作者回复: 主要不是定义的问题,而是打印的时候代码问题,按照这个代码输出的:
    "%5ld ", (long) thread_id

    是个bug, 超过2^31就变成负数了,
    新版本改了

    好问题😆

    2019-02-27
    4
  • IceGeek17
    感谢老师,课程受益匪浅,
    课程结束后,如果有问题,是继续在这里的评论区提问,还是会有另外一条答疑通道?

    另外,在第35篇我提了几个问题,老师还没有回答,我这里再贴一下,老师看一下
    问题一:
    对于BKA算法的流程理解,用文中的例子,先把t1表(小表)中查询需要的字段放入join_buffer, 然后把join_buffer里的字段值批量传给t2表,先根据索引a查到id,然后得到一批主键id,再根据主键id排序,然后再根据排完序的id去主键索引查数据(这里用到MRR)
    理解是否正确?
    这里对于主键id排序是在哪里做的,是在join_buffer里,还是另外再开辟一块临时内存?如果在join_buffer里,那join_buffer里的每行内容是不是:t2.id + t1查询必须的字段,并且join_buffer里是根据id排序的?

    问题二:
    虽然MySQL官方没有支持hash join,但是之前看到文章说,MariaDB已经支持hash join,能不能后续在答疑文章中简单总结下mariaDB支持的join算法

    问题三:
    在实际项目中,一个比较困惑的问题,看到过这样的类似写法:
    select xxx from t1 join t2 on t1.id = t2.id for update (目的是获取几个表上最新的数据,并且加上锁,防止数据被更新)
    这里有几个问题:
    1) 像这样 join + for update,表上的加锁规则是怎么样的?是不是在需要join的两个表上根据具体的查询执行过程都加上锁?
    2)像这样 join + for update 的用法是否合理?碰到这样的场景,应该怎么去做?

    问题四:
    看过阿里输出的开发手册里,强调 “最多不超过三表join”,实际项目中,给我感觉很难做到所有业务都不超过三表join,那这里的问题就是,有什么相关的经验方法,可以尽量降低参与join的数据表?
    比如,在数据表里添加冗余字段,可以降低参与join的数据表数量,还有什么其他好的方法?

    作者回复: 就在我们评论区,提跟文章相关的内容,会继续关注。

    问题一、前面的过程理解正确,MRR过程用的是read_rnd_buffer

    问题二、其实我们文中最后那个过程,你把他设想成在MySQL内部执行。。

    问题三、这种复杂的语句,你要把我们两部分知识点连起来看。一个原则:for update的话,执行语句过程中扫到的间隙和记录都要加锁。 当然最好是不这么做,拆成两个语句会好些。

    问题四、还是我文中的建议,如果都用NLJ或BKA算法的join其实还好,所以看看explain。
    降低join表数量的方法,基本上行就是冗余字段和拆成多个语句这两个方向了

    2019-02-25
    4
  • Dkey
    当前系统并无其他事务存在时,启动一个只读事务时(意味没有事务id),它的低高水位是怎么样的老师。

    作者回复: 假设当前没有其他事务存在,假设当前的max_trx_id=N,
    这时候启动一个只读事务,它的高低水位就都是N。

    2019-02-25
    4
  • MrTrans
    一度想放弃,一度又再拿起,看到这里如释重负,一刷刷到28讲,就停了,因为当时感觉总是没跟上,心浮气躁,二刷从第一讲又开始刷,一个月我就刷完了,而且还能看得懂,对于一个小白来说不容易,曾经留言想放弃,没想到,晓斌老师竟然留言回我叫我加油,当时老脸一红,硬着头皮,再刷一次。而后,也坚持回答问题,虽然回答不怎么样,有时候看了评论,感觉大神太多了,真的,路漫漫兮及其修远兮,我欲上下而求索。谢谢老师,以后面试MySQL的问题我都不会怎么害怕了,遇到不懂的问题我就回来看,回来刷,成长在于点滴,细水才能长流。始终养得根深,枝繁叶茂。

    作者回复: 👍 坚持不易

    2019-09-04
    3
  • ArtistLu
    相遇恨晚😆,安慰下自己,种树的最好时机是十年前,其次是现在!!!谢谢老师

    作者回复: 🤝

    2019-03-08
    3
  • 长杰
    感谢老师,通过本课程的学习,加深了mysql原理上的理解,特别是间隙锁,nextkeylock,join操作上,事物的一致性以及binlog和redolog的配合。感觉还意犹未尽,希望后续还能在这里和老师互动,为我们答疑解惑,再次感谢老师!

    作者回复: 会的,
    也感谢你们一路相伴🤝

    2019-02-26
    3
  • shawn
    受益匪浅,最后几讲还想了解下null值如何建立索引,由于null直接不能比较和排序,MySQL能区分出每一个null值吗

    作者回复: 可以,因为普通索引上都有主键值对吧,

    所以其实是 (null, id1), (null, id2) ....

    2019-02-25
    3
  • H建楼
    谢谢老师!
    2019-04-23
    2
  • 老师,sql 的where里 < 10001 和 <= 10000有什么区别吗?

    作者回复: 这要看你关注的是什么
    你这么问,应该这个字段是整型吧?

    从查询结果可能是一样的,
    不过锁的范围不同,你可以看下21篇

    2019-02-25
    2
  • gerry pang
    今天看完第二遍,一边看一遍总结,感觉收获满满,看后面有时间还要继续加深印象学习

    作者回复: 👍👍

    2019-08-29
    1
  • godtrue
    第一遍到今天就结束了,感谢老师的辛勤付出。
    专栏的买的多,怕这个太长没时间学别的,也怕它太短让人意犹未尽。看评论的数量和质量,就能清晰的分辨一个专栏的优劣,老师的这个无疑是佼佼者中的佼佼者。
    这个专栏学起来好像看《少年包青天》一样,
    提出问题——谁是问题的凶手
    分析问题——寻找问题的凶手
    解决问题——找出问题的凶手
    总结问题——记录抓住问题凶手的始末
    真是精彩绝伦,我们程序员都是问题的终结者,发现问题、解决问题、总结问题是我们的责任。老师的指导,让我们的见识和技能得到了提升,这样便能解决更多的问题创造更多的价值。
    而且我觉得技术的存在也是为了解决各种问题的,
    数据库——解决数据存储的问题
    WAL——解决数据一致性问题
    多线程——解决性能差异的问题
    锁——解决多线程并发导致数据不一致的问题
    索引——解决数据查询或者操作慢的问题
    日志——解决数据备份、同步、恢复等问题
    数据库主备——解决数据高可用的问题
    数据库读写分离——解决数据库压力的问题
    数据库分库分表——解决数据量大的问题
    从简单到复杂,解决一个问题就会引入一些新的问题,然后再想办法解决新的问题,事情就变得越来越复杂啦!但主体没变,附加值在一直增加,并且衍生出了许多新的东西,东西一多就需要分一下类,否则很难理解。所以,数据库按公司有分类,按存储引擎特点有分类,按功能特点有分类等等。
    它的核心就是存储数据,剩下的就是怎么操作舒服怎么操作快的问题啦!想必其他工具也是如此?

    作者回复: 赞总结能力

    2019-08-10
    1
  • Little何
    第二遍完整的看完,好多不理解的知识点,开始慢慢的理解了

    作者回复: 👍👍

    2019-08-01
    1
  • kissrain
    丁奇老师6年的老粉丝了,每一篇读下来都受益匪浅,感谢老师!!!

    作者回复: 🤝🤝

    2019-07-29
    1
收起评论
99+
返回
顶部