mysql 8.018 直方图测试

冷不防 2023-01-12 01:49 111阅读 0赞

\#测试数据准备  
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 \*/  
          \}