概述

mysql中，有个查询优化器，是专门用来估算查询语句的成本的，然后选择一个mysql认为成本最低的方案进行执行。今天我们来了解一下常见查询sql语句的成本是怎么估算的，mysql依据是什么?

估算准备知识点

1mysql估算基于哪些数据

a.mysql既然要估算查询成本，那么必然要进行一些计算，就像我们要去一个地方，想要知道要多久一样。
  计算公式是路程除于速度。不同交通工具速度不一样，不同路线总路程也是不一样的。
  
b.mysql有类似于上面的数据存在。mysql每张表都有统计信息，使用命令SHOW TABLE STATUS就可以看到，
  命令后面接一个参数就是表名称。例如执行show table status like '%user%'\G;命令，我们可以看到
  两个字段Rows和Data_length，Rows表记录数，Data_length表示数据占用字节大小数(Data_length =
  聚簇索引的页面数量 x 每个页面的大小)，这样我们就能计算出页数了。

2mysql选择最低成本策略

a.sql语句执行基于两个成本，一个是从磁盘加载数据到内存的io成本，一个是将内存数据进行筛选过滤计算的
  cpu成本。我之前看过一些文章，会给io成本和cpu成本定一个常量值，分别是1(加载一个页)和0.2（单条记录
  条件过滤）.
  
b.单表查询的时候，提供几种策略，一个是走索引，一个是不走索引。主要就是这两种策略成本的估算，然后
  将计算的结果进行比对，生成执行计划，开始执行语句。
  
C.成本估算我们只要掌握单表估算即可，连接查询，其实计算cpu和io成本是一样的，只是做个叠加计算。  

成本估算分析

1.全表扫描估算

select * from user where name=?  and ...(假设name没有建立索引，只有一个主键id,一共1000条数据)

a.使用show table status命令，查询了表统计数据row=1000,data_length=163840
b.计算io成本，163840/16*1024=10，也就是10页数据，那么io成本=10*1为10
c.计算cpu成本，1000*0.2=200，那么过滤1000条记录的cpu成本就是100
d.计算总成本  100+10=110

2.普通索引估算

select * from user where name=? and ...(假设name建立索引，主键id,一共1000条数据)

a.使用根据索引，直接就能检索到name等于某个值的记录，一般只要加载1页即可（索引值相等的排序在一块），
  特殊情况是索引列存储了识别度不高的值，那页数就很多了，但是mysql认为还是一页的成本来估算，因为
  多个页是连续存储的顺序io(这个类似于范围查询一样情况)
b.计算二级索引io成本，那么io成本=1*1为1
c.计算cpu成本，假设name相同的记录有10条，那么10条记录需要参与过滤，那么cpu成本就是 10*0.2=2
d.计算回表io成本，10条需要回表，当作10次随机io，那么io成本 1*10=10
e.计算回表cpu成本，10条记录过滤其它条件判断，那么cpu成本 0.2*10=2
f.计算总成本 1+2+10+2=15

3.普通索引估算(单索引多条件情况)

select * from user where name in (?,?,?) and ...(假设name建立索引，主键id,一共1000条数据)
a.这种一个索引多区间，也就是多点的情况，其实比上一种情况多了几次io,不像单值和范围查询情况
  只考虑一次io
b.计算二级索引io成本，，假设有三个值，那么io成本=3*1为1
C.其它和上述一样

4.多表连接查询

1.多表连接成本估算，其实就是多次单表查询的估算的叠加。比如你有两个表连接，先计算驱动表的成本，
  然后计算被驱动表的成本，其实就是等于 驱动表+与被驱动表连接的io+cpu成本
2.连接查询和单表查询成本估算有点不一样，比如外连接，需要计算两个表作为驱动表的情况，如果三张，
  四张表连接，那么穷举的量更多了，总之不像单表一样，一次计算就够了

成本估算配置

1.当单表in太多情况in(....)里面有好多值，那么按照上面计算成本方式，比如2000个值，那么io成本
  2000*1=2000，光计算成本就很高了，所以一般不会这么去算，会用系统统计数据去估算。每个索引
  有个统计值，使用命令show index from table_name,看Cardinality,可以估算数据重复率。
  
2.查看加载页io成本常量和过滤记录成本常量SHOW TABLES FROM mysql LIKE '%cost%'，可以看到
  两张表，一般我们关系存储引擎的参数表mysql.engine_cost，里面两个参数，一个是磁盘读取页
  的成本常量io_block_read_cost，一个是从内存页读取一条记录的成本常量memory_block_read_cost