mysql8直方图测试
参考：
https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html#analyze-table-histogram-statistics-analysis
https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html
https://cloud.tencent.com/developer/article/1659123
https://www.cnblogs.com/ataoxz/p/16071042.html
1、建立测试数据，sex=1占比非常小
mysql> create table t1(id int primary key auto_increment,name varchar(200),sex int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 select null,repeat('a',20),0; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 32 rows affected (0.00 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 64 rows affected (0.01 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 128 rows affected (0.00 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 256 rows affected (0.01 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 512 rows affected (0.01 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 1024 rows affected (0.01 sec) Records: 1024 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 2048 rows affected (0.01 sec) Records: 2048 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 4096 rows affected (0.03 sec) Records: 4096 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 8192 rows affected (0.04 sec) Records: 8192 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,repeat('a',20),0 from t1; Query OK, 16384 rows affected (0.08 sec) Records: 16384 Duplicates: 0 Warnings: 0 mysql> select max(id) from t1; +---------+ | max(id) | +---------+ | 49137 | +---------+ 1 row in set (0.01 sec) mysql> update t1 set sex=1 where id>=48000; Query OK, 1138 rows affected (0.02 sec) Rows matched: 1138 Changed: 1138 Warnings: 0 mysql> alter table t1 add index idx_sex(sex); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
2、创建直方图
mysql> ANALYZE TABLE t1 UPDATE HISTOGRAM ON sex; +----------+-----------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-----------+----------+------------------------------------------------+ | ceshi.t1 | histogram | status | Histogram statistics created for column 'sex'. | +----------+-----------+----------+------------------------------------------------+ 1 row in set (0.18 sec)
mysql> select * from information_schema.COLUMN_STATISTICS where table_name='t1'\G
*************************** 1. row ***************************
SCHEMA_NAME: ceshi
TABLE_NAME: t1
COLUMN_NAME: sex
HISTOGRAM: {"buckets": [[0, 0.96527099609375], [1, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2024-06-17 06:17:16.275377", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100}
1 row in set (0.00 sec)
3、测试
再补一些数据 mysql> insert into t1 select null,name,0 from t1; Query OK, 32768 rows affected (0.14 sec) Records: 32768 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,name,0 from t1; Query OK, 65536 rows affected (0.24 sec) Records: 65536 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,name,0 from t1; Query OK, 131072 rows affected (0.41 sec) Records: 131072 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,name,0 from t1; Query OK, 262144 rows affected (0.80 sec) Records: 262144 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,name,0 from t1; Query OK, 524288 rows affected (1.46 sec) Records: 524288 Duplicates: 0 Warnings: 0 mysql> insert into t1 select null,name,0 from t1; Query OK, 1048576 rows affected (3.13 sec) Records: 1048576 Duplicates: 0 Warnings: 0 mysql> explain select count(name) from t1 where sex=1 and id>10000; +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------+ | 1 | SIMPLE | t1 | NULL | index_merge | PRIMARY,idx_sex | idx_sex,PRIMARY | 9,4 | NULL | 569 | 100.00 | Using intersect(idx_sex,PRIMARY); Using where | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(name) from t1 where sex=0 and id>10000; +----+-------------+-------+------------+-------+-----------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY,idx_sex | PRIMARY | 4 | NULL | 1045356 | 0.79 | Using where | +----+-------------+-------+------------+-------+-----------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
4、删除后再测试
mysql> ANALYZE TABLE t1 DROP HISTOGRAM ON sex; +----------+-----------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-----------+----------+------------------------------------------------+ | ceshi.t1 | histogram | status | Histogram statistics removed for column 'sex'. | +----------+-----------+----------+------------------------------------------------+ 1 row in set (0.01 sec) mysql> explain select count(name) from t1 where sex=1 and id>10000; +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------+ | 1 | SIMPLE | t1 | NULL | index_merge | PRIMARY,idx_sex | idx_sex,PRIMARY | 9,4 | NULL | 569 | 100.00 | Using intersect(idx_sex,PRIMARY); Using where | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-----------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(name) from t1 where sex=0 and id>10000; +----+-------------+-------+------------+-------+-----------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY,idx_sex | PRIMARY | 4 | NULL | 1045356 | 100.00 | Using where | +----+-------------+-------+------------+-------+-----------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
没有直接方图之后，第2个SQL的 filtered 和之前不一样了
