MySQL索引

概念

索引是数据库管理系统中一个经过排序的数据结构

目的

提高查询效率

索引类型

1、普通索引：经过设计的数据结构； 2、唯一索引：唯一约束； 3、主键索引（唯一索引的一种）：唯一约束+非空约束； 4、全文索引：创建的时候要制定索引长度。

索引储存模型推演

二分法查找

• 有序数组
• 链表

二叉查找树（BST)

• 能快速查出和插入
• 缺陷：插入有序数组，会变成斜树，树的深度相差过大，查找效率很低

平衡二叉树（AVL）

• 左子树与柚子树的深度差绝对值不超过1，超过1的时候会自动左（右）旋
• InnoDB每次获取16K的数据，AVL每个数据太小，为了不浪费空间就衍生出了BTREE
• 缺陷：空间利用率太低

多路平衡树（BTREE)

• 分支数=16384b（1个数据页的大小）/ 16b（一个单元的大小）+1

B+TREE

• 关键字数和分支数相同
• 叶子节点有双向指针
• 地址数据只放在叶子节点中
• 叶子节点有双向指针，全表检索能力更强
• 地址数据存放在叶节点，内节点数据量更小，读写更加快速稳定
• 每个字节储存更多关键字；路数更多
• 全表扫描更快，只需要遍历叶子节点就可以

创建索引的原则

• 索引并不是越多越好，索引维护需要消耗时间
• 列的离散度越高，索引作用越大（离散度 = 列的不同值/总行数 （离散度约接近1离散度越高）
• 联合索引的最左匹配规则

索引失效场景

• 出现函数、表达式或运算符
• 隐式转换（字段是字符串，但是sql中条件写的是数字）
• 模糊匹配like 前面有%（like ‘%aa’）
• 负向查询（有计算逻辑：not in）

编码格式

utf8：每个字符3字节，无法储存表情或部分繁体字，不是真正的utf8编码格式utf8mb4（推荐）：每个字符4字节，可以正常储存表情

储存引擎

InnoDB（5.5版本后默认引擎）

• 支持事务（提交、回滚和崩溃恢复）
• 支持行锁和表锁
• 支持读写并发

MyISAM

• 支持表级锁
• 不支持事务
• 查询和插入速度较快，适合以读为主的表

Memory

• 数据放到内存中，查找速度快
• 重启服务或服务崩溃，数据会丢失

CSV

• 不支持空行和索引
• 作为不同数据库间导出导入方式

Archive

• 用于检索少量引用的历史数据
• 不支持update和delete，不支持索引

InnoDB数据更新流程

1、客户端传入SQL 2、server层修改数据后发送到储存引擎 3、储存引擎将修改结果更新到内存 4、储存引擎记录redo log，并将这条记录状态设置为prepare（准备状态） 5、储存引擎通知server层提交事务 6、server层写入binlog 7、server提交事务并通知储存引擎 8、储存引擎更新redo log中这条记录的状态为commit

日志

redo log

• 物理日志
• 大小固定，前面的内容会被覆盖，不能用于数据回滚，用于服务崩溃后数据恢复
• InnoDB储存引擎实现，其他引擎没有

binlog

• 逻辑日志
• 用于主从复制和数据回滚
• 大小没有限制，所有储存引擎都可以使用

主从复制

三个线程

• I/O线程：slave连接到master获取binlog，并且解析binlog写入relay log（中继日志）
• log dump：master节点上用来发送binlog给slave
• SQL线程：slave节点中用来读取relay log，把数据写入数据库

其他知识点

• 平衡二叉树左（右）旋实现逻辑：修改旋转节点指针方向
• InnoDB默认每次加载16kb大小的数据（数据页）
• Windows默认每次加载4kb大小的数据
• redis集群的槽位数16384
• 创建索引所消耗的时间是在不断地分裂合并调整BTREE的平衡形态
• hash索引不支持范围查找，因为hash code是离散分布的
• 聚集索引：完整的数据存放的B+树称为聚集索引 1、默认主键是聚集索引； 2、若没有主键则第一个非空的唯一索引为聚集索引； 3、若不满足以上情况，则将隐藏的rowID作为聚集索引；
• 二级索引：除了聚集索引，其他索引称为二级索引
• 回表：通过二级索引查到聚集索引的值，然后在通过聚集索引查到完整数据的过程称为回表
• 覆盖索引：不是索引的类型，是一种使用索引的情况。通过二级索引查到聚集索引的值正好是需要查找的值称为覆盖索引
• 红黑树的最长路径不超过最短路径的二倍，不适合于数据库索引，适合内存的数据结构。

个人学习笔记梳理，如有错误之处请见谅。