我的知识海洋

What are you following

  • 首页
  • 标签
  • 分类目录
  • 文章归档
  • 行路万里
  • 读书万卷
  • About Me

  • 搜索
面经 解决方案 操作系统 Java源码 开源 GSoC 哲学 中间件 回溯 链表 书 top 数据库 分布式 滑动窗口 配置 动态规划 前缀树 并查集 Redis 总结 年终总结 面试 算法基础

【MySQL 45讲-5】第5讲-索引下

发表于 2022-02-16 | 分类于 学习 | 阅读次数 638
# 数据库
【MySQL 45讲-4】第4讲-索引上
【MySQL 45讲-6&7】第6&7讲 数据库的锁

总览图

请添加图片描述

05 | 深入浅出索引(下)

例子

select * from T where k between 3 and 5需要执行几次树的搜索操作?

  1. 在k索引树上找到k=3的记录,取得ID=300;

  2. 在主键索引树上查到ID=300对应的R3;

  3. 在k索引树上取下一个值k=5,取得ID=500;

  4. 再回到主键索引树上查到ID=500对应的R4;

  5. 在k索引树上取下一个值k=6,不符合条件,循环结束。

查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。

  • 在查询里,索引k已经覆盖了查询需求

    • 比如根据k索引查询出主键ID

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段

  • 是否有必要将身份证和名字建立联合索引?

    • 如果有高频请求,要根据身份证号查询姓名,这个联合索引就有意义了

最左前缀原则

  • B+树这种索引,可利用索引的”最左前缀“,来定位记录

  • 在建立联合索引时,如何安排索引内的字段顺序?

    • 评估标准是,索引的复用能力

    • 优先考虑

        1. 建立联合索引可以少维护一个索引
        • 如果有索引(a,b), 就可以不单独建立索引(a)

        • 如果有(a,b)的联合查询,又有基于a,b的查询?

          • 这时得维护两个索引:(a,b)和 (b)
        1. 考虑空间
        • 如(name,age)的联合索引,name字段比较大

          • 建议建两个索引:index(name,age)和index(age)

索引下推

MySQL 5.6 之前,没有优化

MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数

前提:联合主键index(name,age),查询姓名以张开头,且年龄为10

  • 无索引下推过程

    • 根据最左前缀,查询到“张”时,就回表
  • 有索引下推过程

    • 查询到“张”时,继续判断年龄,符合了才回表

举个栗子

  • 查询语句
    select *from geek where c=N order by a limit 1;
    select *from geek where c=N order by b limit 1;
    为何要新建三个索引(a, b),(c,a), (c,b)? 创建(a, b)和(c)不够吗?

对于主键(a,b)
先按a排序,再按b排序,c无序

对于索引(c,a)
先按c排序,再按a排序, 同时记录主键b

对于索引(c,b)
先按c排序,再按b排序, 同时记录主键a

InnoDB会把主键字段放到索引定义字段后面,
当然同时也会去重。
所以,当主键是(a,b)的时候,
定义为c的索引,实际上是(c,a,b);
定义为(c,a)的索引,实际上是(c,a,b)

定义为(c,b)的索引,实际上是(c,b,a)

综上,ca可以去掉,cb需要保留

# 数据库
【MySQL 45讲-4】第4讲-索引上
【MySQL 45讲-6&7】第6&7讲 数据库的锁

  • 文章目录
  • 站点概览
erdengk

erdengk

91 日志
5 分类
24 标签
RSS
Github E-mail
Creative Commons
友链
  • 星球球友
  • Joey
  • 北松山(itwaix)-TP在职
  • JooKS' Blog-GSoC 2022 Mentor
  • Chever-John-Shein在职
  • 一堆网页小游戏
  • 飞鸟记
0%
© 2019 — 2025 erdengk
由 Halo 强力驱动
陕ICP备2021015348号-1
川公网安备 51011202000481号
轻点广告,请我喝水,非常感谢 (。・ω・。)ノ(*/ω\*)