MySQL索引底层数据结构与算法
1、索引数据结构红黑函数 Hash B+树详解 2、千万级数据表如何用B+树索引快速查找 3、聚集索引&聚簇索引&稀疏索引到底是什么 4、为什么DBA总推荐使用自增主键做索引 5、联合索引底层数据结构又是怎么样的 6、MySQL最左前缀优化原则是怎么回事
慢查询 正常几十ms 索引底层是什么情况
索引是帮助MySQL高效获取数据的排好序的数据数据结构
索引数据结构
二叉树 红黑树 Hash表 B-Tree
Select * from t where t.col2=89
没有索引 一行一行的找 MySQL的表磁盘上不一定在一起 I/O读取数据
数据放入二叉树如上图所示 ： 左边比右边小
常用数据结构算法在线展示： www.cs.usfca.edu/~galles/vis…
二叉树缺点：对单向增长的数据没有提高索引效率
红黑树： 会自动平衡树 （二叉平衡树） 树的高度可能会很高
B树：横向数据扩张 如下图
叶节点具有相同的深度 叶节点的指针为空
所以索引元素不重复
节点中的数据索引从左向右递增排序
B+Tree(B-Tree变种)
非叶子节点不存储data ,只存储索引（冗余），可以放更多的索引
叶子节点包含所有索引字段
叶子节点用指针链接 提高区间访问的性能
非叶子节点 取部分数据冗余 ；每一个元素从左到右递增排序。
叶子相邻的存储 有指针
从根节点查询
解决了高度层级问题
非叶节点大小默认： SHOW GLOBAL STATUS LIKE 'Innodb_page_size' 16384
16KB
bigint 8字节 地址 6字节
16*1024/(8+6) 约等于 1170
叶子节点： data : 所在行的地址 或 所在行的所有其他链 一行1K 16348 B/1KB 16个元素
MySQL 有可能B+Tree的根节点常驻内存 也有可能 非叶子节点常驻内存
data挪到叶子节点 在非叶子节点可以存储更多元素
MyISAM InnoDB 存储引擎是 形容数据库表的 表级别的
MyISAM
- .frm 表结构文件
- .myd 表数据文件
- .myi 表索引文件
InnoDB .frm存放数据库的表结构 .ibd存放数据内容
MyISAM索引文件和数据文件是分离的 （非聚集）
MYI文件B+Tree查找数据地址 根据地址去MYD文件去取数据
InnoDB
frm
ibd
整张表都放入 ibd 叶子节点直接放的数据。
聚集索引和非聚集索引
聚集索引： 叶节点包含了完整的数据记录 非聚集索引：索引文件和数据文件分离的 理论上聚集索引比非聚集索引查找速度要快。
为什么建议InnoDB表必须建主键 并且推荐使用整型的自增主键
B+Tree组织
1、没有主键的情况下，MySQL会选择一列都不相等的数据作为B+Tree ，选不到会创建选择一列隐藏列作为索引。
2、uuid既不是整型也不是自增。
每次查找都经历过多次比大小，整型相对字符串效率高。整型存储的空间小。
节约硬盘存储空间。
为什么自增主键 Hash索引
MySQL 不只是b+tree索引 还有hash索引
对索引的key进行一次hash计算就能定位出数据存储的位置
很多时候Hash索引比B+树索引更高效
仅能满足 "=" "IN" 不支持范围查询
hash冲突问题
B+Trees 叶子节点双向指针 可以能直接查到 范围 B树范围没有B+Tree简单。
自增 索引结构不会动
字符串索引 B+Tree 从左到右 叶子节点放主键 没有主键放rowid
为什么非主键索引叶子节点存储的是主键值
一致性 和 节省存储空间 减少复杂度
每添加一个索引 会多一个B+Tree索引
二级索引 也是非聚簇索引 会回表
联合索引的底层存储长什么样
索引最左前缀原理
联合索引
key id_name_age_postion (name,age,postion) USING BTREE
EXPLAIN SELECT * FROM employees where name=‘bill’ and age=31;//走索引
EXPLAIN SELECT * FROM employees where age=31 and postion='dev';//不会走索引
EXPLAIN SELECT * FROM employees where postion='dev';//不会走索引
因为根据索引顺序排好序的 索引最左前缀原理 违背排好序的原则
