SQL 必知必会
陈旸
清华大学计算机博士
73338 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 50 讲
第一章:SQL语法基础篇 (19讲)
SQL 必知必会
15
15
1.0x
00:00/00:00
登录|注册

29丨为什么没有理想的索引?

总结
针对SQL查询的理想索引设计:三星索引
索引片和过滤因子
索引设计

该思维导图由 AI 生成,仅供参考

我之前讲过页这个结构,表和索引都会存储在页中,不同的 DBMS 默认的页的大小是不同的,同时我们也了解到 DBMS 会有缓冲池的机制,在缓冲池里需要有足够多的空间,存储经常被使用到的页,尽可能减少直接的磁盘 I/O 操作。这种策略对 SQL 查询的底层执行来说非常重要,可以从物理层面上最大程度提升 SQL 的查询效率。
但同时我们还需要关注索引的设计,如果只是针对 SQL 查询,我们是可以设计出理想的索引的,不过在实际工作中这种理想的索引往往会带来更多的资源消耗。这是为什么呢?今天我们就来对这部分内容进行学习,内容包括以下几个部分:
什么是索引片?如何计算过滤因子?
设计索引的时候,可以遵循哪些原则呢?
为什么理想的索引很难在实际工作中应用起来?

索引片和过滤因子

索引片就是 SQL 查询语句在执行中需要扫描的一个索引片段,我们会根据索引片中包含的匹配列的数量不同,将索引分成窄索引(比如包含索引列数为 1 或 2)和宽索引(包含的索引列数大于 2)。
如果索引片越宽,那么需要顺序扫描的索引页就越多;如果索引片越窄,就会减少索引访问的开销。比如在 product_comment 数据表中,我们将 comment_id 设置为主键,然后执行下面的 SQL 查询语句:
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

数据库索引设计是一个复杂而具有挑战性的技术问题。本文介绍了索引片和过滤因子的概念,以及如何通过宽索引避免回表,以及过滤因子的作用。同时,文章还探讨了针对SQL查询的理想索引设计——三星索引,以及为什么很难存在理想的索引设计。三星索引通过在索引片中添加索引的方式来提升效率,但也存在索引变宽、增加索引维护成本等问题。读者可以从中了解到数据库索引设计的复杂性和挑战性,以及如何在实际工作中权衡各种因素进行索引设计。 在实际应用中,三星索引作为理想的索引设计方式,能够有效减少回表查询,从而降低磁盘I/O的次数。然而,其缺点也显而易见,会导致频繁的页分裂和页合并,从而降低数据插入和更新的效率。因此,在进行索引设计时,需要权衡索引效率和维护成本。建议在新建索引时,优先考虑在原有的索引片上增加索引,采用复合索引的方式,而不是新建一个新的索引。此外,定期检查索引的使用情况,及时删除很少使用到的索引,也是提高效率的有效途径。 在索引片中,需要控制索引列的数量,通常情况下将WHERE条件列添加到索引中,而SELECT中的非条件列则不需要添加。此外,单列索引和复合索引的长度也需要控制,避免超出系统默认的限制。文章还提到了字符列会占用较大的空间,因此在数据表设计时,建议尽量采用数值类型替代字符类型,避免使用字符类型做主键,同时最好只建前缀索引。 最后,文章提出了一个思考题,针对给定的SQL语句,探讨了创建三星索引的方式以及使用和不使用三星索引在查询效率上的区别。这为读者提供了一个实际问题,引发了对索引设计的深入思考和讨论。 通过本文,读者可以深入了解数据库索引设计的复杂性和挑战性,以及在实际工作中如何权衡各种因素进行索引设计,为其在实践中做出更明智的决策提供了有益的参考。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《SQL 必知必会》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(30)

  • 最新
  • 精选
  • 书痕
    感觉三星索引简直是面向查询新建了个表嘛~

    作者回复: 差不多 可以这么理解

    2019-11-06
    18
  • LJK
    有个问题不太明白,宽索引就是联合索引吗?复合索引又是啥意思?

    作者回复: 宽索引就是联合索引,因为索引列数大于1了,联合索引也就是复合索引,英文composite indexes

    2019-08-16
    2
    13
  • NIXUS
    通过宽索引来避免回表,指的是覆盖索引吗?按照文中对宽索引的描述,感觉宽索引就是联合索引(复合索引)

    作者回复: 对的 宽索引就是联合索引(复合索引)。避免回表是因为索引覆盖了,所以才会避免回表

    2019-08-24
    7
  • 雪飞鸿
    在 MySQL InnoDB 中,系统默认单个索引长度最大为 767 bytes。这里的索引长度指的是索引中所包含字段的大小吗?

    作者回复: 对的

    2019-10-24
    4
  • ahazxc
    试用三星索引,可以减少磁盘i/o,避免回表查询。但同时,有利也有弊,三星索引,如果查询列,过滤列字段过多,会导致索引变大,一个索引页能存储的索引变少,所以遍历索引页花的时间就会多,得不偿失

    作者回复: 对的 凡事都有利弊,当我们了解更多的时候,也会了解每种方法的优缺点,选择适合的来使用

    2019-08-16
    4
  • wonderq_gk
    如何设置宽索引啊???不告诉方法???

    作者回复: 宽索引,窄索引都是相对于建立的索引包括的列数而言的,当索引的列数超过2列时就是宽索引。

    2019-08-29
    5
    3
  • DZ
    按照对课程的理解,我觉得应该只为use_id字段建立一个窄索引。 理由如下: 如果不加思索地套用三星索引,应该这样做: 1. 有范围谓词,把user_id加到索引中; 2. 无排序操作,无需添加排序字段到索引中; 3. 为避免回表,把comment_text添加到索引中。 这样实际形成宽索引: comment_id + comment_text + user_id 但是,这样真的划算吗? comment_text有可能是长字符串,不但占用较大的索引空间,且有高昂的索引维护成本;如果仅仅建立前缀索引,又无法避免回表操作。 更重要的是,此索引满足不了长文本的查询需求,左匹配原则限制了它的用途——我们不大可能使用起始字符串去搜索一段评论——全文索引更为适用。 因此,comment_text不能发挥索引的本来作用。如果仅仅为了避免回表,就将字段一股脑儿塞到索引中,可说是本末倒置了。 综上,为user_id建立索引,形成comment_id + user_id的窄索引,是应对题中SQL查询的正确方法。
    2019-08-16
    1
    68
  • 空知
    联合过滤条件那是不是多写了个 0.1%呀?
    2019-08-30
    14
  • 真名不叫黄金
    谢谢老师,讲得很好。 顺便推荐一本书,详细地讲解了索引设计思想:《数据库索引设计与优化》,文中的很多概念都是来自书中,浅显易懂、值得一读。
    2020-05-20
    8
  • Coool
    下面的0.1%应该换成100%吧。。 100% x 14% x 54%=8%
    2019-10-25
    8
收起评论
显示
设置
留言
30
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部