MYSQL与TiDB的执行计划

SHOW STATS_META

ANALYZE TABLE nation,region,part,supplier,partsupp,customer,orders,lineitem

select
    sum(l_extendedprice) / 7.0 asavg_yearly
fromlineitem,
    part
wherep_partkey =l_partkey
    and p_brand = 'Brand#23'# 指定品牌。 BRAND＝’Brand#MN’ ，M和N是两个字母，代表两个数值，相互独立，取值在1到5之间
    and p_container = 'MED BOX' # 指定包装类型。在TPC-H标准指定的范围内随机选择
    and l_quantity <(
        select
            0.2 * avg(l_quantity)
        fromlineitem
        wherel_partkey =p_partkey
    );

CREATE TABLE IF NOT EXISTS part  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL,
              PRIMARY KEY(P_PARTKEY));

CREATE TABLE IF NOT EXISTS lineitem ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL,
                 PRIMARY KEY(L_ORDERKEY,L_LINENUMBER),
                 CONSTRAINT FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  referencesorders(O_ORDERKEY),
                 CONSTRAINT FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references partsupp(PS_PARTKEY, PS_SUPPKEY));

type executor interface{
    SetSrcExec(executor)
    GetSrcExec() executor
    ResetCounts()
    Counts() []int64
    Next(ctx context.Context) ([][]byte, error)
    //Cursor returns the key gonna to be scanned by the Next() function.
    Cursor() (key []byte, desc bool)
}

type indexScanExec struct {
    *tipb.IndexScan
    colsLen        intkvRanges       []kv.KeyRange
    startTS        uint64
    isolationLevel kvrpcpb.IsolationLevel
    mvccStore      MVCCStore
    cursor         intseekKey        []bytepkStatus       intstart          intcounts         []int64

    src executor
}

select
    sum(l_extendedprice * l_discount) asrevenue # 潜在的收入增加量
fromlineitem
wherel_shipdate >= '1994-01-01' # DATE是从[1993, 1997]中随机选择的一年的1月1日
    and l_shipdate < date_add('1994-01-01', interval '1' year) # 一年内
    and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    and l_quantity < 24; # QUANTITY在区间[24, 25]中随机选择

select
    100.00 * sum(case
        when p_type like 'PROMO%'# 促销零件
            then l_extendedprice * (1 -l_discount) # 某一特定时间的收入
        else 0
    end) / sum(l_extendedprice * (1 - l_discount)) aspromo_revenue
fromlineitem,
    part
wherel_partkey =p_partkey
    and l_shipdate >= '1995-09-01'# DATE是从1993年到1997年中任一年的任一月的一号
    and l_shipdate < date_add('1995-09-01', interval '1' month);