MySQL 索引拾遗

Updated on with 0 views and 0 comments

一、索引选择

Cardinality 值

411dfa683f1d4a31bd004a05e112a4d9.png

  • 作用
    • Cardinality 值非常关键，优化器会根据这个值来判断是否使用索引
    • MySQL 数据库对于 Cardinality 计数在运行一段时间后，可能出现 Cardinality 为 NULL 的情况，这可能导致建立了索引却没有被使用到
  • 高选择性
    • Cardinality / n_rows_in_table 应尽可能接近 1
  • Cardinality 统计
    • 通过采样（Sample）方式完成
    • 更新策略
      • 表中 1/16 的数据已发生过变化
      • stat_modified_counter > 2,000,000,000
      • 除了以上两种情况，当执行 ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX 以及访问 INFORMATION_SCHEMA 里的 TABLES 和 STATISTICS 时会触发 InnoDB 存储引擎重新计算索引的 Cardinality 值
    • 相关参数
      • innodb_stats_sample_pages 用来设置统计 Cardinality 时每次采样页的数据量，默认值为 8
      • innodb_stats_method 用来判断如何对待索引中出现的 NULL 值记录，有效值为 nulls_equal、nulls_unequal 和 nulls_ignored
      • InnoDB 1.2 新增参数
        da533984846443729c05874831c2b668.png
    • 采样过程
      • 取得 B+ 树索引中叶子节点的数量，记为 N
      • 随机取得 B+ 树索引中的 8 个叶子节点，统计每个页不同记录的个数，记为 P1、P2、... 、P8
      • 根据采样信息算出 Cardinality 的预估值：Cardinality = (P1+P2+...+P8) * N / 8
      • 说明：
        1. Cardinality 值是预估值，不是精确值
        2. 每次对 Cardinality 值的统计都是通过随机取 8 个叶子节点得到

Cost-based Optimizer（CBO，基于成本的优化器）

  • 什么是成本
    • MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询
      • io_cost：cost of I/O operations IO操作消耗
      • cpu_cost：cost of CPU operations CPU操作消耗
      • import_cost：cost of remote operations 远程操作消耗
      • mem_cost：memory used (bytes) 内存消耗
    • MySQL 目前关注 io_cost 和 cpu_cost
    • io_cost
      • 将索引或者数据以页为基本单位，从磁盘加载到内存的过程称为 IO 成本
    • cpu_cost
      • 在页中定位记录，并且判断记录是否满足搜索条件，对结果集排序等操作称为 CPU 成本
  • 成本模型 MySQL :: MySQL 5.7 Reference Manual :: 8.9.5 The Optimizer Cost Model
    • 为了生成执行计划，优化器使用一个成本模型，该模型基于查询执行期间发生的各种操作的成本估计值。优化器有一组编译后的默认“成本常量”可用来决定执行计划
    • 优化器还拥有一个在执行计划构建期间使用的成本估算数据库，这些估算值存储在mysql系统数据库的 server_cost 和 engine_cost 表中，可以随时配置
      • mysql.server_cost（Server层）
      • mysql.engine_cost（存储引擎层）
    • 这些表的目的是为了便于调整优化器在试图获得查询执行计划时使用的成本估计
    • 具体常数项
      1420fcc4b87d43d2bf234eb196122754.png
    • 常数项默认值
      • KEY_COMPARE_COST = 0.1
      • MEMORY_TEMPTABLE_CREATE_COST = 2.0
      • MEMORY_TEMPTABLE_ROW_COST = 0.2
      • DISK_TEMPTABLE_CREATE_COST = 40.0
      • DISK_TEMPTABLE_ROW_COST = 1.0
      • ROW_EVALUATE_COST = 0.2
      • MEMORY_BLOCK_READ_COST = 1.0
      • IO_BLOCK_READ_COST = 1.0
  • 成本模型的一般性操作
    • 服务器在启动时将成本模型表读入内存，并在运行时使用内存中的值
      • 表中指定的任何非 NULL 成本估计优先于相应的默认成本常量
      • 任何 NULL 估计都指示优化器使用编译后的默认值
    • 在运行时，服务器可能会重新读取成本表，这种情况发生在动态加载存储引擎或执行 FLUSH OPTIMIZER_COSTS 语句时
    • 成本表使 DBA 能够通过更改表中的条目轻松地调整成本估算
      • 通过将条目的 cost 设置为 NULL，还可以很容易地恢复到默认值
      • 优化器使用内存中的成本值，因此对表的更改应该在 FLUSH OPTIMIZER_COSTS 之后生效
    • 客户端会话开始时的当前内存成本估算适用于整个会话，直到会话结束
      • 注意，如果服务器重新读取成本表，任何更改的估计仅适用于随后启动的会话，现有的会话不受影响
    • 成本表是特定于给定服务器实例的，服务器不会将成本表更改复制到从库

索引提示（INDEX HINT）

  • 需要使用 Index Hint 的情况
    • 优化器没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据
      • 多发生于范围查找、JOIN 链接操作等情况下
      • 因为在传统的机械硬盘上顺序读要远远快于离散随机读
    • SQL 语句可以选择的索引非常多，优化器选择执行计划的时间开销可能会大于 SQL 语句本身
      • 例如，优化器分析 Range 查询本身就是比较耗时的操作
  • USE INDEX 和 FORCE INDEX
    • SELECT * FROM t USE INDEX(a) WHERE a=1 AND b = 2;
    • SELECT * FROM t FORCE INDEX(a) WHERE a=1 AND b = 2;

二、索引优化

Multi-Range Read（MRR）优化

  • MRR 实现逻辑
    1. 将查询得到的辅助索引键值存放于一个缓存中，这时缓存的数据是根据辅助索引键值排序的
    2. 将缓存中的键值根据 RowID 进行排序
    3. 根据 RowID 的排序顺序来访问实际的数据文件
    4. 补充一点：MRR 还可以将某些范围查询拆分为键值对，以此来进行批量的数据查询
      • select * from t where key_part1 >= 1 and key_part1 < 10 and key_part2 = 100
      • 表 t 有 (key_part1, key_part2) 联合索引
      • 若启用 MRR 优化，优化器会先将查询条件拆分，然后再进行查询
      • 拆分结果：(1, 100)、(2, 100)、... 、(9, 100)
  • MRR 优化的好处
    • MRR 使数据访问变得较为顺序
    • 减少缓冲池中页被替换的次数
    • 批量处理对键值的查询操作
  • MRR 优化适用的查询类型
    • range
    • ref
    • eq_ref
  • MRR 支持的存储引擎
    • MyISAM
    • InnoDB
  • MRR 相关参数
    • 通过参数 optimizer_switch 中的 flag 来控制是否开启 MRR，当优化器选择 MRR 优化时，可在执行计划的 Extra 列看到 Using MRR 提示
      0d891476dd0d4c58b64c2e4e7f3da2da.png
      • mrr
      • mrr_cost_based：是否通过 cost based 的方式来选择是否启用 mrr
    • read_rnd_buffer_size
      • 用来控制键值的缓冲区大小，默认值为 256k
      • 当大于该值时，则执行器对已经缓存的数据根据 RowID 进行排序，并通过 RowID 来取得行数据

Index Condition Pushdown（ICP）优化

  • ICP 实现逻辑
    • 在支持 ICP 后，MySQL 会在取出索引的同时，判断是否可以进行 WHERE 条件过滤，也就是将 WHERE 的部分过滤操作放在了存储引擎层
    • 当优化器选择 ICP 优化时，可在执行计划的 Extra 列看到 Using index condition 提示
  • ICP 优化适用的查询类型
    • range
    • ref
    • eq_ref
    • ref_or_null
  • ICP 支持的存储引擎
    • MyISAM
    • InnoDB
    • 说明：NDB Cluster 存储引擎支持 Engine Condition Pushdown 优化，不仅可以进行 Index 的 Condition Pushdown，也可以支持非索引的 Condition Pushdown

附录

a9bb0abf5d2440edb7f154dc403aaa93.png
87ee2ceddbb84087815374d1c954feda.png

标题：MySQL 索引拾遗
作者：yanghao
地址：http://solo.fancydigital.com.cn/articles/2021/09/28/1632817612939.html

↑ ↓