在取经路上,你不得走走停停,你要一直向前😶
文章目录
- 在取经路上,你不得走走停停,你要一直向前😶
- 数据库索引
- 索引的数据结构
- 如何选择索引
- 为什么索引能提供查询速度
- 索引降低增删改的速度
- 最左匹配原则
- 数据库的锁
- 添加索引
- 哪些字段合适或不合适创建索引
- B树和B+树的区别
数据库索引
- 有点优点
- 加快检索速度
- 加快表之间的连接
- 减少排序和分组时间
- 保证唯一性 (唯一索引)
- 缺点
- 占物理空间
- 对数据的增删改需要动态维护索引
- 分类
- 聚簇索引： 索引结构和数据一起存放的索引
- 数据存放的物理位置为顺序，能提高多行检索的速度。(普通索引、唯一索引、主键索引和聚集索引)
- 数据和主键存放在一起
- 叶子节点存储数据
- 非叶子节点存储索引
- 优点
- 一定范围的数据获取比较好
- 查找数据快,少一次主键寻址的IO
- 缺点
- 插入速度依赖插入顺序
- 更改主键代价高,更新的行会移动
- 二级索引需两次所以查找(第一次查找主键,第二次查找行数据)
- 插入比非聚簇索引慢
- 非聚簇索引：索引结构和数据分开存放的索引(也称二级索引)
- 不按数据存放的物理位置顺序，对于单行的检索很快。
- 数据和索引分开存储
- 所有节点都是索引
- 叶节点有索引也有数据
- 回表操作: 在叶节点拿到主键,再去查找数据
- 聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录
索引的数据结构
- 哈希表 (少用)
- 底层是哈希表
- 合适单条记录查询
- B+Tree (推荐使用)
- 叶节点存放数据的地址 (相当于存储数据)
如何选择索引
- 仅仅是为了单行访问是不必要的
- 顺序访问数据是很快的
- 顺序IO不需要多次磁盘寻道
- 顺序读取避免额外的排序
- group by也避免不必要的排序和聚合计算
- 索引覆盖很快
- 一个索引包含（或者说覆盖）所有需要查询的字段的值
- 覆盖索引就是把要查询出的列和索引是对应的，不做回表操作
为什么索引能提供查询速度
- MySQL的存储结构
- 页是基本存储结构
- 文件头
- 页头
- 存储数据 (不确定大小)
- 空余空间 (不确定大小)
- 页的目录 (根据主键二分算法定位,非主键就是依次遍历,底层B+树实现)
- 文件尾
- 数据页可以组成一个双向链表
- 页中的记录又可以组成一个单链表
- 将无序的数据变得相对有序
索引降低增删改的速度
- B+树是一种平衡树,平衡树是左右子树高度差绝对值不超过1,左右子树也是一棵平衡树
- 平衡树不会退化成链表,符合矮胖(均衡)的结构,查询的时间复杂度就是O(log n)
- 增删改对平衡树的原有结构造成破环,需要再次调整就会有相当的开销
- 哈希索引没有办法完成排序,不支持最左匹配原则,哈希碰撞存在,不支持范围查询
最左匹配原则
- 只存在一个列的索引,存在多列的 联合索引,联合索引存在最左匹配
- 联合索引包含(col1, col2, col3), 你的索引会生效于(col1), (col1, col2), 以及(col1, col2, col3)
数据库的锁
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5ixM0mez-1585053475378)(C:\Users\Primer4\Documents\学习笔记\1585052594419.png)]
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FxxoFPM9-1585053475379)(C:\Users\Primer4\Documents\学习笔记\1585052626312.png)]
添加索引
ALTER TABLEtable_nameADD PRIMARY KEY (column)
哪些字段合适或不合适创建索引
合适
- 不为null
- 频繁查找
- 条件查询
- 表之间的连接
不合适
- 频繁更改
- 不被经常查找
- 考虑联合索引而不是单列索引 (单列索引是: 每个索引对应一颗B+树; 联合索引就是: 多列对应一颗B+树)
B树和B+树的区别
- B树
- 所有节点存放 键和数据
- 叶结点时独立的
- 检索时候对范围内的叶节点进行 二分查找
- B+树
- 只有叶节点存放 键和数据
- 非叶节点存放 键
- 叶节点有一条引用链
- 检索都是从根节点到叶节点 (稳定)
