mysql 索引

最新推荐文章于 2024-09-20 21:26:51 发布

可以且有必要

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量121

点赞数

分类专栏： bilibili 文章标签： mysql 索引 B-Tree B+Tree 存储结构

本文链接：https://blog.csdn.net/qq_18615185/article/details/103866898

版权

bilibili 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

mysql索引

TEXT：

mysql 索引

概念
- 索引是帮助mysql高效获取数据的排好序的数据结构
  - 排好序的数据结构
- 索引存储在文件里
  - 电脑磁盘
- 索引概述
  - 磁盘存取原理
    - 寻道时间（速度慢，费时）
    - 旋转时间（速度较快）
- 数据结构可视化网址
  - 数据结构可视化 https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
索引结构
- 二叉树
  - ①→②→③→④→⑤→⑥
  - 每个节点存储数据是key:value
    - key
      - 索引字段，还是该字段对应的值？
    - value
      - 当前记录在磁盘的文件指针
  - 某些场景下存在的问题
    - 可能是单边增长树，树深度n，实际遍历次数等效于全表扫描
  - 二叉树每个节点只存一组key:value
- 红黑树
  - 相对平衡二叉树
  - jdk1.8后hashmap底层用红黑树做了优化
  - 树的深度太深的时候，n过大，比如叶子节点数量是500w，那么2的n次方=500w，n依然过大，少则几十次；每一次查询是一次磁盘io，而每一次磁盘io的速度不快和资源消耗是是多的
  - 红黑树每个节点依然只存一组key:value
- hash
  - 索引的一种官方实现方式
  - 存数据时，对这个值做一次hash，则存到对应位置
  - 取单条数据，对这个值做hash，然后取出值
  - 多条数据时，范围查找时候，需要做全表遍历
  - 数据过大还有hash冲撞的可能
- BTREE
  - 绝对平衡的树，所有叶子节点的高度相等
  - B-Tree
    - 度（degree）节点的数据存储个数
    - 叶节点有相同的深度
    - 叶节点的指针为空
    - 节点中的数据key从左到右依次递增排列
      - 当前节点数据或下一级叶子节点都满足此存储规则
  - 一次io会把节点上的所有数据都刷到内存；在内存中查询很快
  - B树每个节点可以存储多条数据，也就是多组key:value
- B+Tree（B-Tree变种）
  - 特点&特性
    - 非叶子节点不存储data，只存储key，可以增大度
      - 形象描述下，B+树是一颗又矮又胖的树
    - 叶子节点不存指针
    - 顺序访问指针，提高区间访问性能
  - 性能分析
    - 一般使用磁盘I/O次数评价索引结构的优劣
    - 预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
    - 局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用
    - B+Tree节点大小设等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，就实现了一个节点的载入只需一次I/O
    - B+Tree的度d一般会超过100，因此h非常小（一般为1~3之间）
    - 一般操作系统的最小存储单元为页，1页大小为4k
    - 查看mysql文件页大小（16k）
      - show global status like 'Innodb_page_size';
    - 查询版本、文件存储目录
      - show VARIABLES LIKE 'version';show VARIABLES LIKE 'basedir'
  - 采用B+Tree的优点
    - IO性能提高
    - 排序能力更强
    - 基于索引的扫库扫表能力了更强
    - 性能更加的稳定
      - 任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同
  - 存储数据量估算
    - bigint占8个字节
      - 1字节是8个二进制位，所以8字节就是64个二进制位，它能组成成的数字从-2^63 到 2^63-1，也就是从-9223372036854775808到9223372036854775807
    - 根节点，每个key需要配合一个指针产生作用，假设每个指针占用6B空间，则一个(key+指针=14B),16k/14B=1170
    - 第二级非叶子节点每个节点可存数据也是1170
    - 叶子节点每个叶子节点的每个元素占用1k，则一个叶子节点可存16条数据
    - 则此数存储总量为：1170*1170*16=21,902,400 约2000w数据
    - 根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储
mysql存储引擎
- 存储引擎是对应表的，不是数据库，navicat每创建一张表都可以指定对应的存储引擎
- 常见种类
  - MyISAM
    - MyISAM索引（非聚集索引）
      - 索引文件和数据文件是分离的
    - 存储文件种类
      - 表名.frm
        表定义
      - 表名.MYD
        表数据data
      - 表名.MYI
        表索引index
    - 查询过程
      - 1，MYI中查询到where id = xx,存储的该记录的磁盘指针
      - 2，根据指针在MYD中查询到数据
  - InnoDb
    - InnoDB索引实现（聚集索引）
      - 表数据本身就是按B+Tree组织的一个索引结构文件
      - 聚集索引-叶节点包含了完整的数据记录（聚簇索引）
        innodb的主键索引就是聚集索引
        数据和索引存储在一起
      - 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
        必须有主键：B+Tree结构要求数据要有主键，即使没有手动创建，mysql也会给你默认创建一列作主键使用，如row_id类
        整型：如果采用uuid，则uuid占用存储空间多，二叉树中存储过程中需要做比较，uuid比较要转ascii码再比较，效率低下
        自增主键：新增元素往B+Tree的叶子节点往右存储，效率高。如果不是主键自增，则可能在插入数据时，让前面已经排满的节点分裂，并进行再平衡，这些操作都会消耗服务器资源，效率低下。
      - 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
    - 支持事物
    - 存储文件种类
      - 表名.frm
        表定义文件
      - 表名.ibd
        索引和数据文件
    - 查询过程
      - B+Tree中遍历到叶子节点后，直接获取数据（叶子节点包含了完整的数据记录）
    - 叶子节点间指针
      - 用于相邻节点左边到右边的指针关联
      - 范围查找时，如col>30，则可以根据指针关联的将右侧所有数据遍历出来
索引
- 联合索引
  - 单列索引是一种特殊的联合索引
    - index_name(name)，name=张三；关键字内容：张三
  - 联合索引的底层存储结构长什么样
    - index_name_age(name,age)，name=张三,age=23；关键字内容：张三,23
    - 关键字内容：多个部分组成
  - 范围查找后索引失效
- 覆盖索引
  - 通过索引项的信息可直接返回所需的查询列（不用回表），则该索引称之为查询sql的覆盖索引
  - where后的查询条件用到了索引，且select查询的列是（用到的索引查询的）结果的子集或子集+主键(辅助索引（非主键索引）的叶子节点数据也存储了innodb的主键)
- 三星索引
  - where后条件匹配的索引关键字列越多扫描的数据将越少
  - 避免再次排序
    - B+Tree的辅助索引 index_name(name)本来是有序的数据，若再增加order by id desc 则会在内存中再次排序
  - 尽可能运用到覆盖索引进行数据的扫描，减少回表IO操作
- 打油诗
  - 全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；Like百分写最右，覆盖索引不写星；不等空值还有or，索引失效要少用。
最佳实践
- 网址
- 链接地址正确的创建合适的索引是数据库优化的基础
面试标准
- 扎实的java基础
  - jvm
  - 并发
  - 常见集合 IO 反射
  - OOD/oop 设计模式
  - 框架技术ssm aop ioc
  - mysql
  - git maven/docker
- 服务化的体系要求
  - spring clound netflix springcloud alibaba
  - 缓存中间件产品
    - redis
      - redis数据类型几种，各自特性，应用场景
      - 集群方案，3.0版本前后发生了什么变化
      - 数据路由方案与一般的有什么，为什么这么设计，优劣势是什么
      - AOF和RDB机制是如何实现的，他的过期键的策略底层的机制是什么
      - 5.0的版本的string的数据结构为什么会提出来，借鉴了kafka的哪些特性和机制
      - 实际魂村设计中先存db还是缓存，缓存和db的方案如何基于业务合理的设计
  - 消息中间件
  - 高并发高可用 ...