mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rank1` int(11) DEFAULT NULL,
`rank2` int(11) DEFAULT NULL,
`rank3` int(11) DEFAULT NULL,
`log_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_rank1` (`rank1`),
KEY `idx_log_date` (`log_date`)
) ENGINE=InnoDB AUTO_INCREMENT=49140 DEFAULT CHARSET=utf8mb4 \
COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=1 STATS_AUTO_RECALC=0
1 row in set (0.00 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 30940 |
+----------+
1 row in set (0.00 sec)
同时对t2克隆了一张表t3
mysql> create table t3 like t2;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into t3 select * from t2;
Query OK, 30940 rows affected (1.94 sec)
Records: 30940 Duplicates: 0 Warnings: 0
给表t3列rank1和log_date 添加histogram
mysql> analyze table t3 update histogram on rank1,log_date;+--------+-----------+----------+-----------------------------------------------------+| Table | Op | Msg_type | Msg_text |+--------+-----------+----------+-----------------------------------------------------+| ytt.t3 | histogram | status | Histogram statistics created for column 'log_date'. || ytt.t3 | histogram | status | Histogram statistics created for column 'rank1'. |+--------+-----------+----------+-----------------------------------------------------+2 rows in set (0.19 sec)
我们来看看histogram的分布状况
mysql> select json_pretty(histogram) result from information_schema.column_statistics where table_name = 't3' and column_name = 'log_date'\G*************************** 1. row ***************************result: { "buckets": [ [ "2018-04-17", "2018-04-20", 0.01050420168067227, 4 ], ... , [ "2019-04-14", "2019-04-16", 1.0, 3 ] ], "data-type": "date", "null-values": 0.0, "collation-id": 8, "last-updated": "2019-04-17 03:43:01.910185", "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 100}1 row in set (0.03 sec)
MySQL自动为这个字段分配了等高直方图,默认为100个桶。SQL A:
select count(*) from t2/t3 where (rank1 between 1 and 10) and log_date < '2018-09-01';
SQL A的执行结果:
mysql> select count(*) from t2/t3 where (rank1 between 1 and 10) and log_date < '2018-09-01';+----------+| count(*) |+----------+| 2269 |+----------+1 row in set (0.01 sec)
无histogram的执行计划
mysql> explain format=json select count(*) from t2 where (rank1 between 1 and 10) and log_date < '2018-09-01'\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2796.11" }, "table": { "table_name": "t2", "access_type": "range", "possible_keys": [ "idx_rank1", "idx_log_date" ], "key": "idx_rank1", "used_key_parts": [ "rank1" ], "key_length": "5", "rows_examined_per_scan": 6213, "rows_produced_per_join": 3106, "filtered": "50.00", "index_condition": "(`ytt`.`t2`.`rank1` between 1 and 10)", "cost_info": { "read_cost": "2485.46", "eval_cost": "310.65", "prefix_cost": "2796.11", "data_read_per_join": "72K" }, "used_columns": [ "rank1", "log_date" ], "attached_condition": "(`ytt`.`t2`.`log_date` < '2018-09-01')" } }}
有histogram的执行计划
mysql> explain format=json select count(*) from t3 where (rank1 between 1 and 10) and log_date < '2018-09-01'\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "0.71" }, "table": { "table_name": "t3", "access_type": "range", "possible_keys": [ "idx_rank1", "idx_log_date" ], "key": "idx_log_date", "used_key_parts": [ "log_date" ], "key_length": "4", "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "index_condition": "(`ytt`.`t3`.`log_date` < '2018-09-01')", "cost_info": { "read_cost": "0.61", "eval_cost": "0.10", "prefix_cost": "0.71", "data_read_per_join": "24" }, "used_columns": [ "rank1", "log_date" ], "attached_condition": "(`ytt`.`t3`.`rank1` between 1 and 10)" } }}1 row in set, 1 warning (0.00 sec)
我们看到两个执行计划的对比,有Histogram的执行计划cost比普通的sql快了好多倍。上面文字可以看起来比较晦涩,贴上两张图,看起来就很简单了。无histogram请点击输入图片描述有histogram请点击输入图片描述当然,我这里举得例子相对简单,有兴趣的朋友可以更深入学习其他复杂些的例子。