MySQL单表查询的成本

存储引擎将数据存储在磁盘中，查询数据时会选择合适的执行计划将数据查询，执行计划的选择取决于SQL语句执行的成本。而当我们在讨论查询的成本时，一般有两部分：

• I/O成本：查询时需要将磁盘上的数据加载到内存中。这个过程损耗的时间即I/O成本
• CPU成本：读取记录、检测记录是否满足搜索条件、排序等操作的时间为CPU成本

在操作系统中，I/O和CPU因为效率的不同，计算成本的单位也不一样。MySQL规定读取一个页面花费的成本为1.0，读取记录等操作的成本是0.2。（加：优化器将读取一个扫描区间和执行一次回表的的I/O成本定为一样的，都是1.0）

基于成本的优化步骤

mysql查询前的步骤：

根据搜索条件找出所有可能使用的索引。对于B+树索引来说，只要索引列和常数使用连接符号（=、IN、>、IS NULL、LIKE等）连接起来，就会产生扫描区间。一个查询中可能使用到的索引称之为possible keys。如下图查询SQL语句的执行计划中，possible_keys为NULL

计算全表扫描的代价。执行SHOW TABLE STATUS LIKE ‘表名’;即可查询到当前表的统计信息，其中可以看到rows记录条数（估计值，偏高）和Data_length所占空间字节数。Data_length值可以大概计算出该表在表空间中所占用的页的数量，从而推导出I/O成本，而rows值可以算出CPU成本，相加即可得出全表扫描的总成本。（忽略了内节点成本，因为层级本就不高）

计算使用不同索引执行查询的代价。MySQL优化器会先分析使用唯一二级索引的成本，再分析使用普通索引的成本。既然是二级索引，必然是要回表的。所以计算成本时，主要计算两种：

• • 计算扫描区间的数量

    • 不管某个扫描区间的二级索引占用了多少页面。优化器粗略的定义：读取一个扫描区间的I/O成本与一个页的I/O成本相同。

  • 计算需要回表的记录数量

  • • 即某个扫描区间的记录数量，也就是某个搜索条件在二级索引所包含的记录数。过程如下：

      • 找到条件符合的第一条记录，，为区间最左记录，此过程非常快，性能忽略不记

      • 找到最后一条满足条件的记录，为区间最右记录，此过程非常快，性能忽略不记

      • 最后算出具体的记录数量，也分两种情况：

      • • 如果最左记录与最右记录距离相隔不远（5.7版本为<=10个页面），就可以精确算出记录条数。距离通过数据页中的PAGE_N_RECS属性（记录当前页面用户记录数量）来算出，直接遍历即可。
        • 如果最左与最右距离相隔较远，则需要顺着最左记录向右读10个页面，计算平均每页的记录，然后用平均值与最左最右记录之间的页面数量相乘。最左最右记录之间的页面数量，可以在他们的共同父节点（目录项记录页）中统计两条记录之间相隔几条记录。

  • 拿到扫描区间的二级记录数量时，需要根据这些记录的对应的主键去聚簇索引执行回表。那执行一次回表多大成本呢？这里规定是相当于一次访问页面。即1.0，因此二级索引扫描区间中有几条记录，就需要进行多少次I/O。

  • 回表后得到完整的用户记录，再去匹配其他搜索条件是否成立。扫描每条记录所用的成本是CPU成本，上面已经说过了是2.0。

  • 最后，将所有成本相加。即：I/O成本+CPU成本=（扫描区间的数量+预估的二级索引记录数量）+（读取二级索引记录的成本+读取并检测回表后聚簇索引记录的成本）=总成本。

对比执行方案，取成本最低者执行