《MySQL 技术内幕》索引和算法

最新推荐文章于 2023-01-05 11:30:35 发布

D.Chuan

最新推荐文章于 2023-01-05 11:30:35 发布

阅读量330

点赞数 2

分类专栏：数据库 MySQL 技术内幕 InnoDB 存储引擎文章标签： B+树索引 Cardinality 哈希算法全文检索 InnoDB存储引擎

本文链接：https://blog.csdn.net/YangDongChuan1995/article/details/87870717

版权

数据库同时被 2 个专栏收录

27 篇文章 0 订阅

订阅专栏

MySQL 技术内幕 InnoDB 存储引擎

6 篇文章 1 订阅

订阅专栏

文章目录

前言

索引是应用程序设计和开发的一个重要方面
索引过多，应用程序的性能可能会受到影响；索引太少，查询性能又会产生影响
- 如何找到一个合适的平衡点，这对应用程序的性能至关重要

InnoDB 存储引擎索引概述

InnoDB 支持以下常见的索引
- B+ 树索引
- 全文索引
- 哈希索引
B+树索引就是传统意义上的索引，也是关系型数据库系统中最常用最有效的索引
- B+树索引的构造类似于二叉树，根据键值快速找到数据
  - 索引并不能找到一个给定键值的具体行，只能找到具体行所在的页，将其读取到内存后再查找行
- B+树中的 B 不是代表二叉（Binary），而是代表平衡（Balance）

B+ 树

B+ 树是为了磁盘或其他直接存取辅助设备设计的一种平衡查找树
在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各子节点指针进行连接
一棵高度为 2 的 B+ 树

B+ 树的插入操作
- B+ 树的插入必须保证插入后子节点中的记录依然排序
- B+ 树插入的3种情况
  - 不管如何变化，B+ 树总是会保持平衡（为了保持平衡可能需要做大量的拆分页（spit）操作
- B+ 树同样提供了类似于平衡二叉树的旋转（Rotation）功能
  - 时机：旋转发生在需插入的 Leaf Page 已经满了，但其左右兄弟节点还未满时
  - 流程：这时不会急于去做拆分页操作，而是将记录移到所在页的兄弟节点上
    - 转移后，依然需保持有序
    - 通常情况下，左兄弟会被首先检查用来旋转操作
  - 优势：旋转操作使 B+ 树减少了一次页的拆分操作
B+ 树的删除操作
- B+ 树使用填充因子（fill factor）来控制树的删除变化
  - 50% 是填充因子可设的最小值
- B+ 树的删除操作同样也必须保证删除后的叶子节点中的记录依然排序
- B+ 树删除操作的三种情况

B+ 树索引

B+ 树索引的本质就是B+ 树在数据库中的实现
- 高扇出性（B+ 树的高度一般都在 2 ~ 4 层，查找某一键值只需要 2 到 4次 IO
B+ 树索引可以分为聚集索引（聚簇索引）和辅助索引（二级索引）
- 其内部都是B+ 树，即高度平衡，叶子节点存放所有数据
- 不同的是，聚集索引叶子节点存储着整行记录数据，辅助索引存储着聚集索引键值

聚集索引
- 聚集索引就是按照每张表的主键构造一棵B+ 树
  - 叶子节点（也称为数据页）存放着整张表的行记录数据
  - 数据页都是通过一个双向链表来进行链接
- 每张表只能拥有一个聚集索引
  - 因为数据页只能按照一棵 B+ 树进行排序
- B+ 树聚集索引
  - 非叶子节点（也称为数据页）存放着是键值及指向数据页的偏移量
- 聚集索引的存储并不是物理上连续的，而是逻辑上连续的
  - 页通过双向链表链接，页按照主键的顺序排序
  - 每个页的记录也是通过双向链表进行维护，物理存储上可以同样不按照主键存储
- 聚集索引对于主键的排序查找和范围查找速度非常快
辅助索引
- 辅助索引的叶子节点包含着索引键值以及一个书签（bookmark）
  - 书签用来找到与索引对应的行数据
  - InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键
- 辅助索引与聚集索引的关系
  - 每张表上可以有多个辅助索引
  - 查找流程
    - 先遍历辅助索引找到指向聚集索引的主键
    - 然后通过主键值遍历聚集索引找到一个完整的行记录
- 堆表：行数据的存储按照插入的顺序存放
  - 受用于：SQL Server 数据库与 MySQL 数据库的 MyISAM 存储引擎等
  - 这里的书签是一个行标识符（用于定位实际的行数据
  - 主键与非主键的区别只在于是否唯一且非空
- 两者对比
  - 对应非聚集查找与非聚集索引的离散读取，堆表效率更高
  - 索引组织表的优势
    - 行数据的物理地址变更，对于辅助索引是无感知的
    - 对于排序和范围查找，索引组织表效率更高
    - 对于非聚集索引的离散读取，可以通过实现预读技术来避免多次离散读操作
  - 具体是建堆表还是索引组织表，这取决于应用（It all depends
B+ 树索引的分裂
- B+ 树索引页的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费
- InnoDB 存储引擎的 Page Header 中以下部分来保存插入的顺序信息
  - PAGE_LAST_INSERT
  - PAGE_DIRECTION
  - PAGE_N_DIRECTION
- 通过以上信息，InnoDB 可以决定是向左还是向右进行分裂，及其分裂点记录为哪一个
  - 若插入是随机的，则取页的中间记录作为分裂点的记录
  - 若往同一方向进行插入的数量为 5
    - 并且定位的记录点之后还有3条记录，则分裂点的记录为定位点后第三条记录
      - 定位点：待插入记录的前一条记录
    - 否则，分裂点记录就是待插入的记录（普遍存在于自增插入时
B+ 树索引的管理
- 索引管理
  - 索引的创建和删除可以通过两种方法
    - 一种是 ALTER TABLE
    - 另一种是 CREATE/DROP INDEX
  - 可以只索引一个列的开头部分数据
    - 栗子：列b 为 varchar（8000）/ ADD KEY idx_b( b(100) ) #只索引前100位
  - SHOW INDEX 展现结果中每列的含义
    - TABLE：表名
    - Non_unique：非唯一的索引
      - 主键（primary key）是 0，因为必须是唯一
    - Key_name：索引的名字
    - Seq_in_index：索引中该列的位置（主要针对联合索引
    - Column_name：索引列的名称
    - Collation：列以什么方式存储在索引中
      - B+ 树索引总是 A
      - Heap 存储引擎并且建立了 Hash 索引后，这里显示 NULL （即不排序
    - Cardinality：索引中唯一值的数目的估计值
      - 越接近 1 越好，若较小，建议删除此索引
    - Sub_part：是否是列的部分被索引
      - 如果是索引整列，则此字段为 NULL
      - 若只索引了该列的前 100 位，则此字段为 100
    - Packed：关键字如何被压缩（若未压缩，显示为 NULL
    - Null：是否索引的列含有 NULL 值
    - Index_type：索引的类型
    - Comment：注释
- Fast Index Creation
  - MySQL 5.5 版本之前对于索引的添加或删除等DDL操作的过程
    - 创建一张新的临时表（表结构通过 ALTER TABLE 新定义
    - 将原表数据导入到临时表
    - 删除原表
    - 将临时表重命名为原来的表名
  - 旧版对于一张大表进行索引的添加和删除操作，耗费时间较长且事务服务不可用
  - 从 InnoDB 1.0.x 版本开始支持 Fast Index Creation（快速索引创建）的创建方式
    - 对于辅助索引
      - 创建时，会对表加上一个 S 锁，不需要重建表，速度更快，可用性提高
      - 删除时，将原辅助索引的空间标记为可用，删除内部视图该表的索引定义
    - 对于聚集索引
      - 对于主键的创建和删除同样需要重建一张表
    - 必须保证 tmpdir 有足够的空间可以存放临时表，否则会导致创建索引失败
- Online Schema Change
  - OSC（在线架构改变）是一种在线执行 DDL 的方式
    - 在线：在事务的创建过程中，可以读写事务对表进行操作
    - 提高了数据库在 DDL 操作时的并发性
  - OSC 只是一个 PHP 脚本，存在一定的局限性（例如表必须要存在主键等
- Online DDL
  - MySQL 5.6 版本开始支持 Online DDL（在线数据定义）操作
    - 允许创建辅助索引的同时，还允许 DML 操作
    - 极大地提高了数据库在生产环境中的可用性
  - 不仅是辅助索引，以下几类 DDL 操作都可以通过 “在线” 的方式进行操作
    - 辅助索引的创建与删除
    - 改变自增长值
    - 添加或删除外键约束
    - 列的重命名
  - 实现原理
    - 在执行创建或者删除操作的同时，将 DML 操作日志写入到一个缓存中
    - 待完成索引创建后再将重做应用到表上，以此达到数据的一致性
  - 参数 innodb_online_alter_log_max_size 配置以上缓存的大小
  - 因为是通过重做日志来保证一致性，导致索引创建过程中，SQL 优化器不会选择正在创建中的索引

Cardinality 值

什么是 Cardinality
- 对应性别字段、地区字段、类型字段，他们可取值的范围很小，称为低选择性
- 如果某个字段的取值范围很广，几乎没有重复，即属于高选择性
- Cardinality 值表示索引中不重复记录数量的预估值
  - Cardinality/n_rows_in_table 应尽可能地接近1（主键即为1
InnoDB 存储引擎的 Cardinality 统计
- Cardinality 的统计是放在存储引擎层进行的
- InnoDB 存储引擎内部对更新 Cardinality 信息的策略
  - 表中 1/16 的数据已发生过变化
  - stat_modified_counter > 2 000 000 000
    - 参数 stat_modified_counter表示发生变化的次数
    - 主要是针对表中部分行数据频繁地更新，但仍不满足第一条更新策略的情况
- 数据库对于 Cardinality 的统计都是通过采样（Sample）方法完成的
  - 默认 InnoDB 存储引擎对8个叶子节点进行采样
  - 采样过程
    - 取得 B+ 树索引中叶子节点的数量，记为 A
    - 随机取得 B+ 树索引中的 8 个叶子节点。统计每个页不同记录的个数，即 P1,P2 …,P8
    - 根据采样信息给出 Cardinality 的预估值：Cardinality = （P1+P2+…+P8）* A /8
  - 因为每次都是随机取8个叶子节点，导致每次得到的 Cardinality 值可能是不同的
  - 参数innodb_stats_sample_pages配置统计 Cardinality 时每次采样页的数量
  - 参数innodb_stats_method配置如何对待索引中出现的 NULL 值记录
  - 以下操作都会导致 InnoDB 存储引擎去重新计算索引的 Cardinality 值
    - ANALYZE TABLE
    - SHOW TABLE STATUS
    - SHOW INDEX
    - 访问 INFORMATION_SCHEMA 架构下的表 TABLES 和 STATISTICS
  - InnoDB 1.2 新增对 Cardinality 统计设置的参数

B+ 树索引的使用

不要盲从任何人给你的经验意见，Think Different !!!

不同应用中 B+ 树索引的使用
- OLTP 应用中，B+ 树索引建立后，对索引的使用应该只是通过该索引获取表中少部分数据
  - 如根据主键值获取用户信息
  - 如根据订单号取得订单的详细信息
- OLAP 应用中，索引的添加根据的应该是宏观的信息，而不是微观
  - 如不需要对姓名字段进行索引，因为很少需要对单个用户进行查询
  - 如通常会需要对时间字段进行索引，因为大多数统计需要根据时间维度进行数据筛查
联合索引
- 联合索引是指对表上的多个列进行索引
- 多个键值的 B+ 树
- 联合索引默认排序（升序）处理
  - 栗子：假设联合索引（a,b,c）
    - SELECT … FROM TABLE WHERE a=xx ORDER BY b ;
    - SELECT … FROM TABLE WHERE a=xx and b=xxx ORDER BY c ;
    - SELECT … FROM TABLE WHERE a=xx ORDER BY c ;
  - 以上3条SQL,前两条都可以直接通过联合索引得到结果，第三条，还需要通过一次文件排序（filesort）
  - 详解查看：《高性能 MySQL》创建高性能的索引与《高性能 MySQL》查询性能优化
覆盖索引
- 覆盖索引是指从辅助索引中可以得到查询的记录，而不需要再去查询聚集索引中的记录
  - 辅助索引不包含整行记录的所有信息，减少大量的 IO 操作
- 覆盖索引的另一个好处是对某些统计而言
  - 栗子：SELECT COUNT( * ) FROM TABLE （存在聚集索引和非 NULL 列的辅助索引
  - InnoDB 并不会选择通过查询聚集索引来统计，而是选择辅助索引来进行统计
    - 辅助索引远小于聚集索引，减少 IO 操作
    - EXPLAIN - Extra : Using index 表示使用了覆盖索引操作
  - 对于使用覆盖索引来进行统计时，可以不完全遵守联合索引的最左前缀匹配原则
    - 栗子：联合索引（userid, buy_date）
      - SELECT COUNT( * ) FROM buy_log WHERE buy_date >= ‘XX_XX’ AND buy_date <= ‘XX’
      - SELECT * FROM buy_log WHERE buy_date >= ‘XX_XX’ AND buy_date <= ‘XX’
    - 第一条能使用联合索引，第二条无法使用上联合索引（不符合最左前缀匹配原则
优化器选择不使用索引的情况
- 对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是查找的数据必须是少量的
  - 当访问的数据占整个表中数据蛮大一部分时，优化器会选择聚集索引来查找（全表扫描，顺序读）
    - 一般默认是占比超过 20% 就会选择聚集索引
  - 这是由当前传统机械硬盘的特性所决定的（利用顺序读来替换随机读的查找
  - 若使用的高 IOPS 的固态硬盘（随机读快）并确认使用辅助索引性能更优，可以使用 FORCE INDEX
索引提示
- 个人总结以下两种情况需要使用到 INDEX HINT
  - 优化器错误地选择了某个索引，导致 SQL 语句执行过慢（极少情况
  - 某 SQL 语句可以选择的索引非常多，优化器选择执行计划时间的开销会大于执行该条 SQL 语句本身
- FORCE INDEX 用于强制指定某个索引完成查询（USE INDEX 只是建议优化器可以选择某个索引
Multi-Range Read 优化
- MySQL 5.6 版本开始支持 MRR 优化，用于减少磁盘的随机访问（将随机访问转换为顺序的数据访问
- MRR 优化适用于 range、ref、eq_ref 类型的查询
- MRR 优化的好处
  - 使数据访问变得较为顺序
    - 查询辅助索引时，先根据查询结果按照主键值进行排序，并按照主键排序的顺序进行书签查找
  - 减少缓冲池中也被替换的次数
    - 频繁的离散读操作会导致缓存中的页被替换出缓冲池，然后又不断被读入缓冲池
  - 批量处理对键值的查询操作
    - 将某些范围查询拆分为键值对，以此进行批量数据查询（拆分过程中可直接过滤不符合条件的数据
- EXPLAIN - Extra 列：Using MRR 表示使用了 Multi-Range Read 优化
- 通过参数 optimizer_switch中的标记来控制是否启用 MMR 优化
  - mrr = on ：启用了 MMR 优化
  - mrr_cost_based：是否通过 cost based 的方式来选择是否启用 mrr
  - set @@optimizer_switch=‘mrr=on,mrr_cost_based=off’ 设置 MRR优化总是开启状态
- 参数read_rnd_buffer_size配置控制键值的缓冲区大小
  - 大于该值时，则执行器对已经缓存的数据进行 RowID 进行排序，并通过 RowID 来取得行数据
  - 默认值为 256K
Index Condition Pushdown（ICP）优化
- ICP 优化也是 MySQL 5.6 开始支持的一种根据索引进行查询的优化方式
- 原理：当进行索引查询时，会在取出索引的同时，判断是否进行 WHERE 条件的过滤（即存储引擎层过滤
  - 极大减少了 SQL 层对记录的索取（fetch），从而提高数据库的整体性能
- ICP 优化适用于 range、ref、eq_ref、ref_or_null 类型的查询
- EXPLAIN - Extra 列：Using index condition 表示使用了 Index Condition Pushdown 优化
- NDB Cluster 存储引擎支持 Engine Condition Pushdown 优化
  - 不仅支持索引条件下推，还支持非索引的条件下推
- MySQL 5.5 和 MySQL 5.6 中是否启用 ICP 与 MMR 的执行时间对比
  - 在不对缓冲池的预热操作下，ICP 优化可以提升查询效率 23%，若再加上 MRR 优化后，提升 400%

哈希算法

哈希算法是一种非常常见算法，时间复杂度为 O（1）

哈希表
- 哈希表也称为散列表，由直接寻址表改进而来
  - 在直接寻址表的基础上增加哈希函数（数据库一般采用除法散列
    - h (k) = k mod m
- 通过链表法解决碰撞的哈希表
InnoDB 存储引擎中的哈希算法
- InnoDB 使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方法
- InnoDB 中 m 的取值为略大于 2 倍的缓冲池页数量的质数
  - 栗子：缓冲池的大小为 10M ，则共有 640个页，则 640 X 2 = 1280 ，则 m = 1399
自适应哈希索引
- 自适应哈希索引经哈希函数映射到一个哈希表中
  - 只能等值匹配，不支持范围查找等
- 参数 innodb_adaptive_hash_index配置是否启用自适应哈希索引特性（默认开启

全文检索

概述
- 全文检索（Full-Text Serach）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术
  - 根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析
- MyISAM 存储引擎一直支持全文检索技术，InnoDB 从 1.2.x 版本开始也能支持全文检索
倒排索引
- 全文检索通常使用倒排索引（inverted index）来实现（倒排索引也是一种索引结构
  - 它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射
  - 通常利用关联数组实现，其拥有两种表现形式
    - inverted file index ,其表现形式为 { 单词，单词所在文档的 ID}
    - full inverted index ,其表现形式为 { 单词，（单词所在文档的 ID，在具体文档中的位置）}
- 栗子
  - 全文检索表 t
  - inverted file index 的关联数组
  - full inverted index 的关联数组
InnoDB 全文检索
- InnoDB 存储引擎从 1.2.x 版本开始支持全文检索的技术，其采用 full inverted index 的方式
  - InnoDB 将（DocumentId，Position）视为一个 “ilist”
  - 全文检索的表中，有两个列，一个是 word 字段，另一个是 ilist 字段
    - 对 word 字段建立索引，并且因 ilist 字段中存放了 Position 字段，故也支持 Proximity Search
- Auxiliary Table（辅助表）用于倒排索引可以将 word 存放到此表上
  - 为了提高全文检索的并行性能，共有 6 张 Auxiliary Table（根据 word 的 Latin 编码进行分区
- FTS Index Cache（全文检索索引缓存）用来提高全文检索的性能
  - FTS Index Cache 是一个红黑树结构，其根据（word，ilist）进行排序
  - 提高了 InnoDB 存储引擎的性能，且根据红黑树顺序后进行批量插入，其产生 Auxiliary Table 相对较小

D.Chuan

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
《MySQL 技术内幕》索引和算法

文章目录前言InnoDB 存储引擎索引概述B+ 树B+ 树索引Cardinality 值B+ 树索引的使用哈希算法全文检索前言索引是应用程序设计和开发的一个重要方面索引过多，应用程序的性能可能会受到影响；索引太少，查询性能又会产生影响如何找到一个合适的平衡点，这对应用程序的性能至关重要InnoDB 存储引擎索引概述InnoDB 支持以下常见的索引 B+ 树索引全文索...
复制链接

扫一扫