mysql 8.018 直方图测试_drop database if exists test; create database test-CSDN博客

本文链接：https://blog.csdn.net/BASEDEDATO/article/details/113251291

#测试数据准备
mysql -uroot -proot
drop database if EXISTS test;
create database test;
use test;
CREATE TABLE test(
id int not null primary key,
cr_time time,
diff_value int,
key ind_test_1 (diff_value) using btree
);

DROP PROCEDURE IF EXISTS proc_test;
DELIMITER $$
CREATE PROCEDURE proc_test()
BEGIN
DECLARE i int DEFAULT 1;

LOOP_LABEL:LOOP

   -- 每500000 提交批量提交批量提交批量提交，基于主键update，每次一条循环百万次，然后找dba “我这IO有些高”
   IF (i-1) mod 500000 = 0 THEN
   commit;
   select sleep(20);
   END IF;

IF i <= 6000000 THEN
   INSERT INTO test VALUES(i, current_time(), 1);
SET i = i+1;
   END IF;
   IF i >= 6000001 AND i <=8000000 THEN
INSERT INTO test VALUES(i, current_time(), 2);
SET i = i+1;
END IF;
   IF i >= 8000001 AND i <=9000000 THEN
INSERT INTO test VALUES(i, current_time(), 3);
SET i = i+1;
END IF;
   IF i >= 9000001 AND i <=9999999 THEN
INSERT INTO test VALUES(i, current_time(), 4);
SET i = i+1;
END IF;
   IF i = 10000000 THEN
   INSERT INTO test VALUES(i, current_time(), 5);
   commit;
   LEAVE LOOP_LABEL;
   END IF;

END LOOP;
END $$
DELIMITER ;
exit

mysql -uroot -proot
use test
set session autocommit = off;
CALL proc_test();

-- 查看数据分布
select diff_value, count(1) from test group by diff_value;

-- 查看表及索引统计信息
select * from mysql.innodb_table_stats a where a.table_name = 'test';
select * from mysql.innodb_index_stats a where a.table_name = 'test';
-- 查看表直方图信息
select * from information_schema.column_statistics a where a.table_name = 'test'\G;

-- 在没有直方图时的执行计划取表半数以上的数据，mysql 选择了糟糕的二级索引
explain format=json select count(distinct cr_time) from test a where a.diff_value = 1;

-- 在看一下，走索引和全表扫描的效率，很明显走二级索引是个错误的选择
set profiling = on;
select count(distinct cr_time) from test a where a.diff_value = 1;
select count(distinct cr_time) from test a ignore index(ind_test_1) where a.diff_value = 1;
set profiling = off;

show profiles;
show profile for query 1;
show profile for query 2;

-- 对进行直方图数据采集，因为diff_value只有5个值，因此bucket设为10足以，最大值1024，越大越精确
analyze table test update histogram on diff_value with 10 buckets;
-- 查看表直方图信息
select * from information_schema.column_statistics a where a.table_name = 'test'\G;
-- 删除直方图
-- analyze table test drop histogram on diff_value ;

-- 在看一下执行计划，竟然没有影响，在有直方图，就是所谓数据分布的情况下，mysql依旧走二级索引，并回表。不能如此鸡肋吧。欢迎来喷
mysql> explain format=json select count(distinct cr_time) from test a where a.diff_value = 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "514083.90"
},
"table": {
"table_name": "a",
"access_type": "ref",
"possible_keys": [
"ind_test_1"
],
"key": "ind_test_1",
"used_key_parts": [
"diff_value"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 4990209,
"rows_produced_per_join": 4990209,
"filtered": "100.00",
"cost_info": {
"read_cost": "15063.00",
"eval_cost": "499020.90",
"prefix_cost": "514083.90",
"data_read_per_join": "76M"
},
"used_columns": [
"cr_time",
"diff_value"
]
}
}
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 查看 cbo-trace
set optimizer_trace='enabled=on';
set end_markers_in_json=on;
select count(distinct cr_time) from test a where a.diff_value = 1;

select * from information_schema.optimizer_trace\G;

{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`test` `a`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "ind_test_1",
"rows": 4.99e6,
"cost": 514084,
"chosen": true
},
{
"rows_to_scan": 9980418,
"access_type": "scan",
"resulting_rows": 9.98e6,
"cost": 1e6,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 4.99e6,
"cost_for_plan": 514084,
"chosen": true
}
] /* considered_execution_plans */
}