MySQL 索引
数据库的三范式, 反模式
零碎知识
索引
索引原理
B Tree 索引
B+Tree 索引
B Tree 与 B+Tree 的比较
聚集索引和辅助索引
聚集索引的注意事项
索引的最左匹配特征
数据库的三范式, 反模式
数据库范式是为了解决关系数据库中数据冗余, 更新异常, 插入异常, 删除异常等问题而引入的. 数据范式可以避免数据冗余, 节省空间, 增加维护便利性
第一范式 (原子性)
强调属性的原子性, 要求属性不可再分解
第二范式 (唯一约束性)
强调记录的唯一约束性, 表中必须有一主键, 且非主键不能只依赖主键的一部分
第三范式 (冗余性约束)
强调属性的冗余性约束, 非主键列必须直接依赖于主键
反模式
范式设计下, 数据库业务设计的表可能增多, 涉及到多表联合查询, 导致性能变差. 因此, 出于性能优先的考量, 在数据库结构中使用反模式的设计, 利用空间换取时间, 采用数据冗余的方式避免多表联合查询. 数据一致性的问题, 可选择尽可能使数据达到用户一致, 保证系统一段时间的自我恢复和修正, 最终达成一致.
零碎知识
数据类型
数值, 日期, 字符串
可变长度类型数据
可变长度类型中 varchar(200) 与 varchar(50) 存储 hello 所占空间相同, 但前者在排序时会占用更多内存
自增 id 删除之后的记录
InnoDB 引擎只将当前自增主键的最大 id 存到内存中, 重启后可能会使最大 id 丢失; 而 MyISAM 会将最大 id 记录到数据文件中
count 语句的统计
没有 where 限定时, InnoDB 的 count(*) 相对 MyISM 可能慢很多, 因为 InnoDB 是实时统计. 而 MyISM 维护了一个计数器
索引
优缺点
优点:
提高检索速度, 降低 IO 成本
事前排序, 降低查询时 CPU 消耗
缺点:
增加存储空间, 降低表更新速度
使用场景
小型表不建议使用, 适用于中大型表; 对特大型表索引的代价更大, 可对数据库表进行分区
索引的六种类型
普通索引, 唯一索引 (唯一性), 主键索引 (不允许为空), 复合索引, 外建索引, 全文索引
索引创建原则
出现在 where 语句后, 而不是 select; 索引基数越大, 效果越好; 有时复合索引提高效率; 过多索引会占用磁盘空间; 主键尽量选取较短的数据类型; 字符串索引应建立一个前缀长度, 节省索引空间.
索引使用注意事项
避免在 where 后面使用逻辑或表达式操作, 使用 OR 连接条件
索引原理
默认使用 B Tree 索引
B Tree 索引
以 M 路 BTree 结构为例 (M>=2, 否则为空树)
排序方式: 所有节点都递增排序
子节点数: 1 < 非叶子节点数 <=M
关键字数: ceil(M/2)-1 < 关键字数 < M-1
叶子节点: 所有子节点均在同一层, 包换关键字及关键字记录的地址 (此外, 也有指向其子节点的指针, 值为 null)
B+Tree 索引
对 B Tree 索引的一种优化, B+Tree 中所有数据记录节点都按照键值大小顺序存放在同一层节点上, 每个非叶子节点都只存储 key 值信息, 这样可大大增加每个节点存储的 key 值数量, 降低树的高度
非叶子节点只记录键信息
所有叶子节点之间都有一个链指针
数据记录都放在叶子节点中
B Tree 与 B+Tree 的比较
B + 树层级更少: B + 树每个非叶子节点存储的关键字更多, 所以层级更少, 查询速度更快
B + 树查询更稳定: B + 树所有关键字地址都存储在叶子节点上, 所以每次查找次数相同, 查询速度更稳定
B + 树具有天然的排序功能: B + 树所有叶子节点数据构成了一个有序链表, 在查询区间数据时更方便, 数据紧密性高, 缓存命中率也高
B + 树全局遍历更快: B + 树只需要遍历所有叶子节点, 而不需要像 B 树一层层进行遍历
B 数对根节点附近的数据访问速度更快: 因为 B 数非叶子节点本身存有关键字其数据地址
聚集索引和辅助索引
聚集索引 (主键索引), 其叶子节点存储的数据是整行的具体数据;
辅助索引 (二级索引), 其叶子节点存的是整行数据对应的主键值, 根据辅助索引查询数据要经过两步, 即回表:
InnoDB 存储引擎会遍历辅助索引找到主键
再通过主键在聚集索引中找到完整的行记录数据
聚集索引的主键
定义主键时, InnoDB 存储引擎会将其当做聚集索引
没有定义主键时, InnoDB 存储引擎会定位到第一个唯一索引, 且改索引的所有列值均为非空, 将其当做聚集索引
没有主键且没有适合的唯一索引, InnoDB 存储引擎产生一个 ID 值 6 字节的聚集索引
聚集索引的注意事项
插入速度严重依赖插入顺序, 按照主键的顺序插入是最快的方式, 否则出现页分裂 会影响性能.(因此, 一般定义一个自增的 Id 作为主键)
更新主键会导致更新的行移动, 因此一般定义主键为不可更新
二级索引访问需要两次查询, 第一次找到主键值, 第二次找到行数据.(但是当查询数据只有 id 和索引时, 可一次查询直接返回数据, 即索引覆盖)
主键 id 建议使用整型. 如此, 主键索引的 B + 树节点可以存储更多主键 id, 辅佐索引的 B + 树节点可以存储更多主键 id
索引的最左匹配特征
当索引种类是复合索引时, B + 树通过从左往右建立搜索树, 即索引的最左匹配特征
来源: https://www.cnblogs.com/CodeMLB/p/13303301.html