手工计算mysql cost_计算select语句的cost

(分析的过程比分析的结果重要的多)

不涉及join的cost计算

==================================================================

1 数据库中可以提供给我们的可供参考的参数：

表：cardinality_table(表有多少条record), npages_table(表有多少页),

列：cardinality_column(列中不同值的数目), HIGH2KEY/LOW2KEY(因为最大值可能是null,最小值可能是0)，frequencies(某个值在此列中出现的比率比如100个数字，有10个0，那么0的频率是0.1)。

索引：nleaf(索引叶子结点的个数)，nlevel(索引的深度)，clusted_ratio(聚集索引的比率？具体含义就是指某一列聚集的程度，在mysql中主键是聚集的，所以主键的clustoratio是1，其他键看和主键的对应程度，因为record是按照主键进行排序的。其他列与主键对应的好，在从磁盘上取数据的时候可以进行prefetch，能将磁盘io降到不prefetch情况的0.2)。

filterfactor(这是一个很重要的概念，代表过滤条件过滤的条数的比率)

好现在开始分析一个最简单的例子select * from table1 where c1=?;

如果不在c1上建立索引，全部扫描一遍。

如果在c1上建立了索引，先要看索引的cost，再看取表的cost，cost主要是io cost，以下全为估算。

2 索引的cost：

nleaf * filterfactor(叶子结点的cost)+ (非叶子结点的cost)

解释：

filterfactor(过滤的比例)有这么几种情况  1 c1=?的时候应该是filterfactor = 1/cardinality_column，如果某种实现中还考虑到frequencies,这个比率可能就该变为(1-frequencies)/ (cardinality_column-1) .  2 c1>?的时候filterfactor = (HIGH2KEY-?)/(HIGH2KEY-LOW2KEY) . 其实估算时filterfactor是可以通过select count(*) from table1看出来的。

非叶子结点的cost  根据层数判断个数，比如叶子结点共有1000个，总共三层，因为第一行只有1个，所以第二行大概有10个结点，再根据filterfactor，就可以算出来 非叶子结点的io cost.

3 表的cost：

clusted_ratio * npages_table * filterfactor * 0.2(能够使用prefetch) + (1-clusted_ratio) * npages_table * filterfactor(不能够使用prefetch)。

解释：

要取的页数为：npages_table * filterfactor，再乘以clusted_ratio*0.2是说这部分可以使用prefetch，提高io效率。

另外的一半是不能够使用prefetch的页。

=============================================================

再举个例子，

select * from table1 where c1=1 and c2>2.应该怎么优化？

(重点的在于分析的过程。分析的过程才是最重要的。而不是结论)

直观的想法是在c1，(c1,c2)，(c2,c1)，c2上建立索引，分别利用上面的方法计算公式，计算出io cost。

假设在(c1,c2)上建立索引。

索引的具体形式：

跟单独在c1上建立索引是一样的，只不过现在比较的是c1c2，连接成了一个数据了。

那么先按照c1比较，找到c1=1是matching的，那么只要扫描一遍找到的matching的leaf entry，就可以将所有的page读进来。

所以index的io cost最小是 npage * filterfactor_c1 * filterfactor_c2.如果 (c1,c2)联合起来的filterfactor最小是filterfactor_c1 * filterfactor_c2。因为c1和c2会有相关性。

这样感觉，该取多少index page，该取多少data page就非常清晰了。

这里相对单独在c1上建立索引的好处是，取的data_page应该会减少很多，因为不c2已经在索引中了，可以直接比较了。

其他的可以类似的得出结论。