MySQL-成本计算

• 一条查询语句的执行成本由下边这两个方面组成
  • I/O成本：把数据或者索引加载到内存
  • CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等操作
• 基于索引统计数据的成本
  • IN语句中的参数个数: eq_range_index_dive_limit
  • show index from table 为表中的每一个索引维护一份统计数据
• 单表查询的成本
  1. 根据搜索条件，找出所有可能使用的索引
  2. 计算全表扫描的代价
  3. 计算使用不同索引执行查询的代价
     1. 范围区间数量
     2. 需要回表的记录数
     3. 是否有可能使用索引合并
  4. 对比各种方案，找出成本最低的那一个
• 连接查询的成本
  • 单次查询驱动表的成本
  • 多次查询被驱动表的成本
    • 具体查询多少次取决于对驱动表查询的结果集中有多少条
    • 对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）
  • Condition filtering 就是这个猜的过程
    • 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条
    • 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条
  • 两表连接的成本分析
  • 多表连接的成本分析
    • optimizer_search_depth 如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量的表进行穷举分析
    • optimizer_prune_level 启发式规则
• 调节成本常数 server_cost engine_cost
• InnoDB中的统计数据
  • 统计数据存储方式 innodb_stats_persistent
    • 永久性的统计数据，这种统计数据存储在磁盘上
      • innodb_table_stats 存储了关于表的统计数据，每条记录对应一个表的统计数据
      • innodb_index_stats 存储了关于索引的统计数据，每条记录对应一个索引的一个统计项
    • 非永久性的统计数据，这种统计数据存储在内存中
  • 更新统计数据
    • innodb_stats_auto_recalc 自动重新计算统计数据，该功能默认是开启的
    • 手动调用ANALYZE TABLE语句来更新统计信息，会立即重新计算统计数据
    • 手动更新innodb_table_stats和innodb_index_stats表
      • FLUSH TABLE table 让MySQL查询优化器重新加载我们更改过的数据