mysql 索引

mysql索引

TEXT:

mysql 索引

 

  • 概念
    • 索引是帮助mysql高效获取数据的排好序的数据结构
      • 排好序的数据结构
    • 索引存储在文件里
      • 电脑磁盘
    • 索引概述
      • 磁盘存取原理
        • 寻道时间(速度慢,费时)
        • 旋转时间(速度较快)
    • 数据结构可视化网址
  • 索引结构
    • 二叉树
      • ①→②→③→④→⑤→⑥
      • 每个节点存储数据是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的方案如何基于业务合理的设计
      • 消息中间件
      • 高并发 高可用 ...
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值