mysql直方图_继续Mysql直方图

首先建了一张表bigtable，用存储过程往表里插入100000条记录，其中，测试列为c1，才取值为0-100的随机数。

准备测试数据CREATE TABLE `bigtable` (`id` int NOT NULL,`c1` int DEFAULT NULL,`c2` varchar(20) DEFAULT NULL,`d1` datetime DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;drop procedure imp_data;delimitercreate procedure imp_data()begindeclare i int default 0;while i < 100000 doinsert into bigtable values(i,rand()*100,lpad(i,20,0),now());set i = i+1;end while;enddelimiter ;

对bigtable.c1收集直方图

查看直方图mysql> select json_pretty(histogram) from  information_schema.column_statistics where table_name='bigtable' and column_name='c1';{"buckets": [[0,3,0.03933039330393304,4],[4,8,0.0892108921089211,5],[9,12,0.12882128821288213,4],[13,17,0.17849178491784917,5],[18,21,0.21884218842188422,4],[22,25,0.25865258652586526,4],[26,30,0.3084630846308463,5],[31,34,0.3485534855348553,4],[35,38,0.3894238942389424,4],[39,43,0.43859438594385947,5],[44,47,0.4781647816478165,4],[48,51,0.5172751727517275,4],[52,56,0.5678956789567896,5],[57,60,0.6075360753607536,4],[61,64,0.6481264812648126,4],[65,69,0.6982169821698216,5],[70,73,0.7389573895738958,4],[74,77,0.7793077930779307,4],[78,82,0.8293182931829318,5],[83,86,0.8700187001870019,4],[87,90,0.9104191041910419,4],[91,95,0.959459594595946,5],[96,99,1.0,4]],"data-type": "int","null-values": 0.0,"collation-id": 8,"last-updated": "2020-07-08 15:56:05.832571","sampling-rate": 1.0,"histogram-type": "equi-height","number-of-buckets-specified": 23}

查看表统计信息

查看表bigtable的cardinality。

Cardinality是指索引中不重复记录的预估值。通常我们判断一个索引是否高效，是通过计算选择率(选择率=Cardinality/表的记录数)，选择率越接近1，选择度就越高。

Cardinality只是一个预估值，所以如果这个预估值不准确就会导致mysql优化器生成错误的执行计划。因为更新Cardinality代价比较大，所以并不是实时更新的，通常Cardinality的自动重新统计会发生在insert和update这两个操作中，(Oracle中搜集统计信息，应该是由定时任务触发的)但这也需要满足一定的条件：表中有1/6的数据已经更新

stat_modified_counter>2,000,000,000

当然，也可以手工来触发Cardinality的更新，运行analyze table table，show table status，show index from table命令即可。

Sample

以65-69这个bucket为例.[57,60,0.6075360753607536,4],[61,64,0.6481264812648126,4],[65,69,0.6982169821698216,5]

等高直方图, 每个桶里的数据是一致的, 一共23个bucket, 表的cardinality为99899, 表示有69.8%的数据小于等于69, 桶内所有数据的选择率为：Bucket selectivity=0.6982169821698216-0.6481264812648126= 0.0500905009050090

Sample 1:

where c1 between 65 and 69,查询的刚好是一个bucket的low value 和 high value的区间值，那么这个条件的选择率，应该刚好就是这个bucket的选择率，按照上面计算的，应该是5.01%。

Sample 2:对于bucket中的每一个值作等于运算时, 其选择率均为:

Bucket selectivity * (1/number of value in the bucket)

即：0.0500905009050090/5 = 0.01001810018100180000

Sample 3:

在直方图内的range条件，其选择率为：

Bucket selectivity (number of values/number of value in the bucket)

假如我们查询条件为c1 between 64 and 67, 这个条件一共涉及桶内的4个值, 选择率为:0.01001810018100180000*4= 0.04007240072400720000

Sample 4:如果跨bucket查询, 例如c1 between 62 and 66, 其选择率为:Selectivity=(0.6481264812648126-0.6075360753607536)(3/4)+(0.6982169821698216-0.6481264812648126)(2/5)= 0.05047900479004785000

注意：如果c1列上有索引，将不会遵从以上算法。

END;