第1章 MySQL索引
索引: 相当于一本书的目录 用于加速查询
例如 我有100个箱子 其中一个有数据 箱子上有数字 这时想找到这个数据 就需要一些算法
遍历算法 一个一个的进行查找 找到为止 非常慢 对查找数值不平衡
二分法算法 询问一个数值 答复大小 进行查找 这种方法凭运气 有的查找快 有的太慢
1.1 索引算法的演变
MySQL索引算法的演变: 二叉树 – 红黑树 – BTREE – B+TREE(B*TREE)
这些算法是为了快速的确认范围
其他索引方法
B+TREE HASH RTREE FullTEXT GIS索引 都是MySQL支持的索引方法
我们主要使用BTREE
1.2 BTREE查找算法
存储数据时 首先会将数据均匀的 从大到小的 存储到PAGEL(页)中
存储后将每个pagel生成子节点(leaf node)
然后向上层生成一个支节点 存储下层节点中最小值-最大值的范围(1-3,2-6,)
支节点再向上生成一个根节点 存储支节点的最小值-最小值范围(1-6)
B-TREE
算法首先会遍历根节点找对应支节点 – 找支节点找对应子节点 – 子节点找到数据 不论什么数值都是三次找到
B+TREE
当遍历小范围数据时 (大于xx小于xx) 每个数值都需要遍历三次 这样也很费事
当在子节点上 过滤出第一个数值后 将相邻的第几个数值也跟随发送到缓存中 这样只需要三次就可以过滤出范围(理论上)
B*TREE
遍历大范围数据时 还是不方便 比如 in( ) or( )
在支节点生成关联 这样过滤出数据后 将相邻的支节点数据放到缓存中
1.2.1 区别 B*TREE B+TREE
BTREE 节点逐个向上查找
B+TREE叶子点(leaf节点) 有相邻的指针
B*TREE叶子节点和支节点(no-leaf节点) 有相邻的指针
1.3 MySQL影响索引树高度的因素
表内行数多 解决办法 拆分表 : 归档表, 分区表, 分布式系统存储
索引列值过长 解决想法 : 前缀索引
数据类型影响
a) 选择尽量简短的数据类型替代繁琐的数据类型
b) 变长数据最好是vatvhar()
c) 灵活使用enum()类型
第2章 MySQL中BTREE如何构建
MySQL中有两类BTREE构建
使用索引的原因 为的是优化io读取的次数 减少回表次数
2.1 聚簇索引 或者叫做(聚集索引,集群索引)
自动选择主键列(PK),没有主键时 会自动选择唯一键(UK) 都没有的话会会自动生成隐藏列(聚簇索引)
注: 只有在 InnoDB才有聚簇索引.
聚簇索引特点: 原表数据所在行就是leaf节点
功能 数据存储时 按照聚簇索引列的顺序 在磁盘上有序的存储在连续数据页上 我们称之为 索引组织表
聚簇索引 会将整行的数据当作一个leaf(叶)节点
生成树时 会将聚簇列当中索引列 索引时基于聚簇列查找时 才会加快速度
聚簇索引 : 查找根节点 – 支节点 – 叶节点
当查找范围时 值为相邻的两张表 相邻表之间有关联 会直接一起取出数值
2.1.1 小结 聚簇索引构建:
当创建表的时候就会自动建立聚簇索引
将有序的正表数据行所在数据行,作为叶子节点
按照聚簇索引列值, 向上生成子节点和根节点
查询 前提是按照 聚簇索引列值 做查询条件时 理论上等值查询时 会发生三次IO 即可获得数据行
如果是范围查询时 利用叶子节点的双向指针继续优化查询
2.2 辅助索引: (MUL)
尽可能地减少索引数的高度
首先辅助索引会自动提取出聚簇列 + 辅助索引列进行排序
会基于索引列进行排序 然后将索引项+聚簇列 均匀的 有序的 存储在叶子节点上 在生成支节点 根节点 其中支节点存储的是辅助索引列的最大-最小值
查询辅助索引时 会先通过根节点 – 支节点 – 叶节点 找到查找数据对应聚簇列 然后回表 取出聚簇列数值 进行聚簇索引 三层结构最少6次io查找
小结 : 辅助索引构建
构建: alter table ti add index idx (name);
过程
提取辅助索引列 + 聚簇索引列, 按照辅助索引列的升序排列
将排好序的数值 均匀的 有序的存储到叶子节点中
通过辅助索引列的值 生成支节点 – 根节点
查询:
按照辅助索引列作为查询条件时 遍历辅助索引树 得到聚簇索引列值
拿着聚簇索引列值进行回表查询
注意:
减少查询行数
减少IO的次数
尽量回表的次数 得出的聚簇索引值越少越好\
解决之道 联合索引:减少ID 的个数 (搜索时多个条件) 覆盖索引: 搜索内容就在辅助索引中
查询时尽量等值查询 缩小范围
尽量使用聚簇索引查询
