Mysql--查询的成本如何计算

具体细节 请去掘金购买《MySQL 是怎样运行的：从根儿上理解 MySQL》

查询成本组成有哪些。

• 1.I/O成本
• 2.CPU成本
• 3.Mysql规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数
• 4.需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

单表查询的成本

基于成本的优化步骤

• 1.在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案--这个成本最低的方案就是所谓的执行计划
• 2.具体流程如下：
• 3.根据搜索条件，找出所有可能使用的索引
• 4.计算全表扫描的代价
• 5.计算使用不同索引执行查询的代价
• 6.对比各种执行方案的代价，找出成本最低的那一个

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

• 1.一个查询中可能使用到的索引称之为possible keys。

计算全表扫描的代价

• 1.聚簇索引占用的页面数
• 2.该表中的记录数
• 3.MySQL为每个表维护了一系列的统计信息,上述1和2的信息就在这里面。
• 4.可以通过SHOW TABLE STATUS LIKE tableName 来查询
• 5.Rows代表记录--在innodb下只是一个概数。
• 6.Data_length--表示表占用的存储空间字节数。
• 7.Data_length = 聚簇索引的页面数量 x 每个页面的大小（16KB）
• 8.I/O成本==页面数量*1.0+1.1
• 9.CPU成本=ROWS*0.2+1.0--0.2指的是访问一条记录所需的成本常数
• 10.总成本=I/O成本+CPU成本。
• 11.我们前边说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍
  。也就是说全表扫描这个过程其实有的B+树内节点是不需要访问的.上述计算全表扫描是个比较粗略的计算。

计算使用不同索引执行查询的代价

• 1.要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并
• 2.MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本---当然这个前提是无法直接走主键索引。
• 3.对于使用二级索引 + 回表方式的查询：主要考虑范围区间数量和需要回表的记录数。
• 4.范围区间数量：不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的
• 5.需要回表的记录数:首先通过左右区间可以在常数范围找到临界点记录。然后只要从区间最左记录沿着链表方向走向区间最右记录。如果两者相隔不超过10个页面
  是可以得到回表的准确数据。如果超过则统计10个页面情况下平均每个页面包含的记录数，然后再乘以页面个数。
• 6.寻找页面数，只要去记录也得父层寻找即可。
• 7.CPU成本主要是读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本

index dive

• 1.Mysql把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive
• 2.当我们使用二级索引in的时候，由于不是唯一的，所以在in中的每一个参数都需要index dive
• 3.mysql通过eq_range_index_dive_limit参数，如果是in的话 参数数量大于这个参数 则采用索引统计数据来进行估算记录数量。
• 4.主要使用ROWS 和Cardinality，两者可以计算出一个值得重复次数==一个值的重复次数 ≈ Rows ÷ Cardinality
• 5.所以一个in中一个参数代表10个记录

mysql为索引的统计数据

• 1.MySQL也会为表中的每一个索引维护一份统计数据---- SHOW INDEX FROM TABLENAME
• 2.关键的属性：Cardinality，Sub_part
• 3.Cardinality：表示索引列中不重复值的个数，这是一个估数。 值越大代表该列重复值越少。值越大重复越小，则可区分度就很大，进而建立索引的意义不大。
• 4.Sub_part：对于存储字符串或者字节串的列来说，有时候我们只想对这些串的前n个字符或字节建立索引，这个属性表示的就是那个n值。如果对完整的列建立索引的话，该属性的值就是NULL。

连接查询的成本

• 1.MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次
• 2.查询成主要是 ：单次查询驱动表的成本和多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）
• 3.驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）

Condition filtering

• 1.当使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条。
• 2.如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

两表连接的成本分析

• 1.连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
• 2.对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要：分别为驱动表和被驱动表选择成本最低的访问方法。
• 3.对于内连接来说，驱动表和被驱动表的位置是可以互换的,因此算出最优的方式执行。

多表连接的成本分析

• 1.有n个表进行连接，MySQL查询优化器要每一种连接顺序的成本都计算一遍么？
• 2.提前维护一个最小成本值，每个连接顺序查询成本计算时只要大于这个的直接跳过。
• 3.系统变量optimizer_search_depth，可以规定最多穷举多少种查询方式。
• 4.根据某些规则压根儿就不考虑某些连接顺序--这些规则叫启发规则，可以通过系统变量optimizer_prune_level来控制到底是不是用这些启发式规则。

调节成本常数

• 1.读取一个页面花费的成本默认是1.0
• 2.测一条记录是否符合搜索条件的成本默认是0.2
• 3.其实除了这两个成本常数，MySQL还支持好多呢，它们被存储到了mysql数据库的两个表中：engine_cost 和server_cost 。
• 4.一条语句的执行其实是分为两层的：server层，存储引擎层
• 5.通过update 修改常数，然后通过FLUSH OPTIMIZER_COSTS;生效