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，然后取出值
  - 多条数据时，范围查找时候，需要做全表遍历
- BTREE
  - 红黑树的基础上做改进
  - B-Tree
    - 度（degree）节点的数据存储个数
    - 叶节点有相同的深度
    - 叶节点的指针为空
    - 节点中的数据key从左到右依次递增排列
      - 当前节点数据或下一级叶子节点都满足此存储规则
  - 一次io会把节点上的所有数据都刷到内存；在内存中查询很快
  - B树每个节点可以存储多条数据，也就是多组key:value
- B+Tree（B-Tree变种）
  - 特点&特性
    - 非叶子节点不存储data，只存储key，可以增大度
    - 叶子节点不存指针
    - 顺序访问指针，提高区间访问性能
  - 扩展
    - B+树，每个非叶子节点只存储key（mysql规定单独的节点存储上限是16k，只存储key，剩余的节点空间可以存储更多的key）
    - 形象描述下，B+树是一颗又矮又胖的树
    - show global status like 'Innodb page size';
    - 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，则可以根据指针关联的将右侧所有数据遍历出来
联合索引
- 联合索引的底层存储结构长什么样
最佳实践
- 网址
链接地址

mysql 索引

相关推荐