什么是成本

  • I/O成本

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

  • CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

成本常数

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大叔规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2

  • 读取一个页面花费的成本默认是1.0
  • 检测一条记录是否符合搜索条件的成本默认是0.2

    单表查询的成本

    ```sql CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8;

SELECT * FROM single_table WHERE key1 IN (‘a’, ‘b’, ‘c’) AND key2 > 10 AND key2 < 1000 AND key3 > key2 AND key_part1 LIKE ‘%hello%’ AND common_field = ‘123’;

  1. <a name="tkbQ2"></a>
  2. ### 基于成本的优化步骤
  3. <a name="kiI1c"></a>
  4. #### 1. 根据搜索条件，找出所有可能使用的索引
  5. 上边的查询语句可能用到的索引，也就是possible keys只有idx_key1和idx_key2
  6. <a name="RsjT9"></a>
  7. #### 2. 计算全表扫描的代价
  8. <a name="mjiBA"></a>
  9. #### 3. 计算使用不同索引执行查询的代价
  10. <a name="qXzYu"></a>
  11. #### 4. 对比各种执行方案的代价，找出成本最低的那一个
  12. <a name="og5x2"></a>
  13. ### 基于索引统计数据的成本计算
  14. ```sql
  15. SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive

如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算

连接查询的成本

Condition filtering介绍

对于两表连接查询来说，它的查询成本由下边两个部分构成：

  • 单次查询驱动表的成本
  • 多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）

MySQL的把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据

两表连接的成本分析

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：

  • 尽量减少驱动表的扇出
  • 对被驱动表的访问成本尽量低这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

    多表连接的成本分析

    MySQL干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level来控制到底是不是用这些启发式规则

调节成本常数

mysql.server_cost表调节server层

mysql.engine_cost表调节存储引擎层