MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
220459 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

04 | 深入浅出索引(上)

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

索引的常见模型

索引的出现是为了提高查询效率,但是实现索引的方式却有很多种,所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多,这里我先给你介绍三种常见、也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树。
下面我主要从使用的角度,为你简单分析一下这三种模型的区别。
哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的键即 key,就可以找到其对应的值即 Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结
仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(624)

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

    作者回复: 这两个语句是都不用回表了,在“查找行”这个逻辑上是一样的, 但是select *要读和拷贝更多列到server,还要发送更多列给客户端,所以还是select id更快的。 好问题

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

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

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

    作者回复: 理论篇的先到这里,实践篇的还有六篇索引相关。 1. 我们文中有这样的例子了哦。like “张%” 就是对name 字段的范围查询/模糊查询。 age 就是你说的最后一个字段啦 😄 2. 我们有两篇的篇幅来讲order by, 第14和16(15是答疑) 3. 根据查询需要😓 4. 等《如何给字符串字段加索引》发布哈 嗯索引的内容其实很多,基础篇两篇是不可能写完的,而且怕理论类太多大家读着累,把一些知识点放到实践篇了 @all

    7
    100
  • 壹笙☞漂泊
    总结: 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.从性能和存储空间方面考量,自增主键往往是更合理的选择。 思考题: 如果删除,新建主键索引,会同时去修改普通索引对应的主键索引,性能消耗比较大。 删除重建普通索引貌似影响不大,不过要注意在业务低谷期操作,避免影响业务。

    作者回复: 优秀

    25
    821
  • 约书亚
    “N叉树”的N值在MySQL中是可以被人工调整的么?曾经面试被问到过这问题,当时就懵逼了...

    作者回复: 面试中题面越简单的问题越暗藏凶险,可见一斑… 可以按照调整key的大小的思路来说; 如果你能指出来5.6以后可以通过page大小来间接控制应该能加分吧 面试回答不能太精减,计算方法、前缀索引什么的一起上😄

    78
    344
  • 堵车
    请问没有主键的表,有一个普通索引。怎么回表?

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

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

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

    71
    243
  • 扬~
    但现在一般自增索引都设置为bigint,这点老师这么看

    作者回复: 特别合理,因为现在很多业务插入数据很凶残,容易超过int 上限, 实际上是建议设置bigint unsigned 好问题

    8
    139
  • Richie
    老师索引只能定位到page,page内部怎么去定位行数据

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

    13
    131
  • 橡皮泥boy
    【回答问题】 1. 直接删掉主键索引是不好的,它会使得所有的二级索引都失效,并且会用ROWID来作主键索引; 2. 看到mysql官方文档写了三种措施,第一个是整个数据库迁移,先dump出来再重建表(这个一般只适合离线的业务来做);第二个是用空的alter操作,比如ALTER TABLE t1 ENGINE = InnoDB;这样子就会原地重建表结构(真的吗?);第三个是用repaire table,不过这个是由存储引擎决定支不支持的(innodb就不行)。

    作者回复: 准确

    12
    113
收起评论
显示
设置
留言
99+
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部