Mysql基于成本的优化

单表查询

基于成本的优化步骤

1. 根据搜索条件，找出所有可能用到的索引

2. 计算全表扫描的代价

   ​ show table status like

   • 聚簇索引页面数 Data_length=聚簇索引页面数*每个页面大小
   • 该表的记录数 Row MyISAM是准确，InnoDB是估计值

   I/O成本 + CPU 成本 =聚簇索引页面数 *1.0 + Rows * 0.2

3. 计算每个索引成本

   1. 每个索引成本 扫描区间数量 * 1.0 + 二级索引记录5.7.22版本 最左记录相隔最右记录不超过10页面可以准确统计出 * 0.2 + 二级索引回表 *1.0 + 读取完整记录加判断其他条件 * 0.2
   2. 是否可能使用索引合并

4. 选择成本最低的

基于索引统计数据的成本计算

例如 select * from t where key IN (‘aa’,…) 会用到 index dive 5.7.3 之前 in 中个个数为 10 之后为200

统计数据来源

1. show table status from 中的 Rows
2. show index from 中的 Cardinality

统计结果为 IN 中的个数 * (Rows / Cardinality)

Cardinality——基数 表示列不重复的个数

连接查询的成本

两表连接

内连接驱动表和倍驱动表可能交换 成本为单次访问驱动表的成本 + 驱动扇区 * 单次访问倍驱动表的成本

计算成本涉及到的条数，已经统计信息的就用，没有的根据已经的推测(启发式规则)

原则

1. 尽量减少驱动表的扇区

2. 访问被驱动表的成本要尽量低

   • 为被驱动表的连接列建立索引，就可以使用 ref (因为每次拿一条记录到被驱动表匹配) ，或者连接列最好是主键或者唯一二级索引就可以const

多表连接

n!种情况

1. 一个全局变量记录最低成本 一旦超过他就停止分析
2. optimizer_search_depth 连接的表小于这个数量就穷举
3. 启发式规则：根据以前经验指定规则，不满足规则的连接顺序不分析