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

04 | 深入浅出索引(上)

林晓斌 2018-11-21
提到数据库索引,我想你并不陌生,在日常工作中会经常接触到。比如某一个 SQL 查询比较慢,分析完原因之后,你可能就会说“给某个字段加个索引吧”之类的解决方案。但到底什么是索引,索引又是如何工作的呢?今天就让我们一起来聊聊这个话题吧。
数据库索引的内容比较多,我分成了上下两篇文章。索引是数据库系统里面最重要的概念之一,所以我希望你能够耐心看完。在后面的实战文章中,我也会经常引用这两篇文章中提到的知识点,加深你对数据库索引的理解。
一句话简单来说,索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。一本 500 页的书,如果你想快速找到其中的某一个知识点,在不借助目录的情况下,那我估计你可得找一会儿。同样,对于数据库的表而言,索引其实就是它的“目录”。

索引的常见模型

索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多,这里我先给你介绍三种常见、也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树。
下面我主要从使用的角度,为你简单分析一下这三种模型的区别。
哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的值即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(386)

  • JackPn 置顶
    老师我可不可以理解为:每一张表其实就是一个B+树,树结点的key值就是某一行的主键,value是该行的其他数据。新建索引就是新增一个B+树,查询不走索引就是遍历主B+树。

    作者回复: 每一个表是好几棵B+树(应该是你理解对了但是手误),
    其它的完全正确

    2018-11-27
    6
    73
  • wuxue_123 置顶
    老师,回表只是普通索引才会有的吗?主键和数据放在同一个树中,根据主键查询的时候,就可以直接获得数据了。
    那select *from table where id=xx
    和select id from table where id=xx
    的效率是一样的吗?(id是主键)

    作者回复: 这两个语句是都不用回表了,在“查找行”这个逻辑上是一样的,

    但是select *要读和拷贝更多列到server,还要发送更多列给客户端,所以还是select id更快的。

    好问题

    2018-12-20
    3
    50
  • Christain 置顶
    老师,索引篇结束了么?
    有几个问题
    1 :三个字段联合索引时,如果中间的字段使用了范围查询或者模糊查询,最后一个字段还会用到索引么?
    2:在order by时,索引是如何使用的
    3:新建一张表,如何界定其索引的数量,有没有选择或者公式
    4:能不能再详细介绍下字符串前缀索引

    作者回复: 理论篇的先到这里,实践篇的还有六篇索引相关。
    1. 我们文中有这样的例子了哦。like “张%” 就是对name 字段的范围查询/模糊查询。 age 就是你说的最后一个字段啦 😄
    2. 我们有两篇的篇幅来讲order by, 第14和16(15是答疑)
    3. 根据查询需要😓
    4. 等《如何给字符串字段加索引》发布哈

    嗯索引的内容其实很多,基础篇两篇是不可能写完的,而且怕理论类太多大家读着累,把一些知识点放到实践篇了 @all

    2018-11-23
    1
    47
  • 壹笙☞漂泊
    总结:
    1.索引的作用:提高数据查询效率
    2.常见索引模型:哈希表、有序数组、搜索树
    3.哈希表:键 - 值(key - value)。
    4.哈希思路:把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置
    5.哈希冲突的处理办法:链表
    6.哈希表适用场景:只有等值查询的场景
    7.有序数组:按顺序存储。查询用二分法就可以快速查询,时间复杂度是:O(log(N))
    8.有序数组查询效率高,更新效率低
    9.有序数组的适用场景:静态存储引擎。
    10.二叉搜索树:每个节点的左儿子小于父节点,父节点又小于右儿子
    11.二叉搜索树:查询时间复杂度O(log(N)),更新时间复杂度O(log(N))
    12.数据库存储大多不适用二叉树,因为树高过高,会适用N叉树
    13.InnoDB中的索引模型:B+Tree
    14.索引类型:主键索引、非主键索引
    主键索引的叶子节点存的是整行的数据(聚簇索引),非主键索引的叶子节点内容是主键的值(二级索引)
    15.主键索引和普通索引的区别:主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值,再到主键索引树搜索一次(回表)
    16.一个数据页满了,按照B+Tree算法,新增加一个数据页,叫做页分裂,会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并,合并的过程是分裂过程的逆过程。
    17.从性能和存储空间方面考量,自增主键往往是更合理的选择。

    思考题:
    如果删除,新建主键索引,会同时去修改普通索引对应的主键索引,性能消耗比较大。
    删除重建普通索引貌似影响不大,不过要注意在业务低谷期操作,避免影响业务。


    作者回复: 优秀

    2018-11-21
    1
    314
  • 张良
    结合王争的数据结构与算法看正好
    2018-11-21
    3
    106
  • 约书亚
    “N叉树”的N值在MySQL中是可以被人工调整的么?曾经面试被问到过这问题,当时就懵逼了...

    作者回复: 面试中题面越简单的问题越暗藏凶险,可见一斑…

    可以按照调整key的大小的思路来说;

    如果你能指出来5.6以后可以通过page大小来间接控制应该能加分吧

    面试回答不能太精减,计算方法、前缀索引什么的一起上😄



    2018-11-21
    10
    76
  • 堵车
    请问没有主键的表,有一个普通索引。怎么回表?

    作者回复: 没有主键的表,innodb会给默认创建一个Rowid做主键

    2018-11-21
    75
  • 张先先森森森
    drop主键索引会导致其他索引失效,但drop普通索引不会。
    2018-11-21
    2
    60
  • 路过
    顺序应是先删除k列索引,主键索引。然后再创建主键索引和k列索引。
    2018-11-21
    45
  • 高枕
    我来回答这个问题
    你可以想象一下一棵 100 万节点的平衡二叉树,树高 20。一次查询可能需要访问 20 个数据块。

    这里有点不理解,为什么树高20就是20个数据块?


    每个叶子结点就是一个块,每个块包含两个数据,块之间通过链式方式链接。树高20的话,就要遍历20个块

    作者回复: 👍🏿,多谢

    多补一句,因为是二叉树结构,每次指针查找很大概率是触发随机磁盘读(比如很难刚好碰上一个节点和他的左右儿子刚好相邻)

    2018-11-21
    4
    35
  • 唐堂
    @约书亚 的问题:““N叉树”的N值在Mysql是否可以被调整?” ,老师能否在稍微详细点指导下? 谢谢老师了
    2018-11-21
    33
  • Richie
    二级索引重建应该新建索引再做删除,如果有查询用到这个索引,此时索引已被删除,会导致业务抖动.主键重建不能采用drop这种方式去按操作,因为所有数据都是以主键组织的,删了主键后,InnoDB会自己找一个主键组织数据,再次添加主键又会重新组织数据,重建表的次已达二次,我们可以直接Optimiz这个表
    2018-11-21
    31
  • 大王拍我去巡山
    老师你好:之前看过一遍文章,一直有疑惑:一个innoDB引擎的表,数据量非常大,根据二级索引搜索会比主键搜索快,文章阐述的原因是主键索引和数据行在一起,非常大搜索慢,我的疑惑是:通过普通索引找到主键ID后,同样要跑一边主键索引,还望老师解惑。。。

    作者回复: 问出这个问题表示你今天的文章看明白了👍🏿

    看完明天的(下),我估计你就有答案了

    2018-11-21
    3
    25
  • Richie
    老师索引只能定位到page,page内部怎么去定位行数据

    作者回复: 内部有个有序数组,二分法

    2018-11-21
    1
    23
  • jacket
    【今日收获】
    1. 主键索引的叶子结点存储了整一行的内容(聚簇索引),使用主键可以快速获取到整行的数据。
    2. 非主键索引的叶子结点存储的是主键的值,所以主键字段占用空间不宜过大。同时,其查找数据的过程称为“回表”,需要先查找自己得到主键值,再在主键索引上边查找数据内容。
    3. 索引的实现由存储引擎来决定,InnoDB使用B+树(N叉树,比如1200叉树),把整颗树的高度维持在很小的范围内,同时在内存里缓存前面若干层的节点,可以极大地降低访问磁盘的次数,提高读的效率。
    4. B+树的插入可能会引起数据页的分裂,删除可能会引起数据页的合并,二者都是比较重的IO消耗,所以比较好的方式是顺序插入数据,这也是我们一般使用自增主键的原因之一。
    5. 在Key-Value的场景下,只有一个索引且是唯一索引,则适合直接使用业务字段作为主键索引。

    作者回复: 赞👍🏿

    2018-11-22
    20
  • 滩涂曳尾
    老师我有2个问题,问题1是上一节遗留的没来得及问:
    1. 在“读提交”隔离级别下,这个视图是在每个 SQL 语句开始执行的时候创建的。这句话能具体说明吗?
    2. 访问磁盘和内存索引涉及磁盘(sata,ssd,nvm)读写性能,以及内存读写性能,可否给一些数值方便直观认识?

    作者回复: 1. 就是语句开始执行前创建一个read-view

    2. 我估计你要的是这个:
    T his group of numbers is from a presentation Jeff Dean gave at a Engineering All-Hands Meeting at Google.

    L1 cache reference 0.5 ns
    Branch mispredict 5 ns
    L2 cache reference 7 ns
    Mutex lock/unlock 100 ns
    Main memory reference 100 ns
    Compress 1K bytes with Zippy 10,000 ns
    Send 2K bytes over 1 Gbps network 20,000 ns
    Read 1 MB sequentially from memory 250,000 ns
    Round trip within same datacenter 500,000 ns
    Disk seek 10,000,000 ns
    Read 1 MB sequentially from network 10,000,000 ns
    Read 1 MB sequentially from disk 30,000,000 ns
    Send packet CA->Netherlands->CA 150,000,000 ns

    2018-11-21
    20
  • 天天向上
    去面试,遇到面试官问我innodb B+树主键索引的叶子节点存的是什么,我说是行,面试官说错,存的是页,
    老师讲的就是行啊,我……

    作者回复: B+树的叶子节点是page (页),一个页里面可以存多个行

    2019-03-23
    6
    16
  • 老师请问下:
    1、如果插入的数据是在主键树叶子结点的中间,后面的所有页如果都是满的状态,是不是会造成后面的每一页都会去进行页分裂操作,直到最后一个页申请新页移过去最后一个值
    2、还有之前看到过说是插入数据如果是在某个数据满了页的首尾,为了减少数据移动和页分裂,会先去前后两个页看看是否满了,如果没满会先将数据放到前后两个页上,不知道是不是有这种情况

    作者回复: 1. 不会不会,只会分裂它要写入的那个页面。每个页面之间是用指针串的,改指针就好了,不需要“后面的全部挪动

    2. 对,减为了增加空间利用率

    2018-11-21
    16
  • skyun
    评论区里面关于B+树节点N等于1200的疑惑,我觉得应该是这么算的:
    16*1024/(8+6)≈1200
    其中数据页默认大小为16kb,mysql bigint为8个字节,指针大小在InnoDB源码中设置为6字节。
    关于如果层高为四层,应该是1200*1200*1200再乘以叶子节点中一个数据页存储记录的条数,文中应该默认了每个叶子节点中只存储一条数据吧,也就是每条数据大小的为16k,讲道理现实中一般没这么大的数据的。
    2018-11-28
    2
    12
  • 某、人
    先回答老师的问题:
    二级索引可以通过删除添加来消除索引的碎片化,而且开销不大。只会修改表的元数据,不会有重建表的操作
    聚簇索引不适合用这种方式来重建。drop primary key只能用copy方式,这是极大的开销,而且不允许在线的DML操作
    add primary key可以用inplace方式,效率相对copy高了不少。不过inplace也是会在内部重建表,但是不会生成redo和undo,这点就比copy效率高很多,允许在线DML(只会持有短暂的MDL锁)
    可以采用alter table engine=innodb或者optimize table来重建表,也可以用导入导出的方式,不过效率相对更差
    老师我有两个问题:
    1.如果业务里只有很少的等值连接查询,是否可以把AHI关掉?
    2.通过explain extend看到in会被转换为or。但是mysql里in是会先将()列表里面排序,然后在进行二分查找的方式去匹配是否满足,时间复杂度为O(logn).请问这里是in里面得值一个个取出来,然后再去索引的叶子节点上利用二分查找法去匹配该值?
    or的时间复杂度为O(n),我理解的是根据or上的值去索引扫描到叶子节点得到该值,然后返回根节点继续扫描得到第二个值。如果按照我的理解那么or的效率应该比in高啊,请问老师是哪里出错了?
    哈哈,感觉这68花得太值了。感谢老师,干货满满啊


    作者回复: 1. AHI不是hash索引的意思,这个结构是帮innodb 快速找到叶子结点,所以对于range 查询也有优化作用的

    2. 不是,这样其实就已经是拿到结果了,不论是也就是说a in () 还是 “a=X or a=Y” 你说的这个过程就没有用上a的索引了。只是在拿到值以后做的判断

    3.

    2018-11-21
    1
    12
收起评论
99+
返回
顶部