摘要:

mysql列存储引擎-一铺-上下文记录

DDL:

表结构:

create table tunit(
  id int not null,
  unitsn varchar(20) not null
);

create table tlog(
  id int not null,
  unitid int not null,
  vname varchar(20) not null,
  vval int not null
);

create table tresult(
  unitid int not null,
  v1 int,v2 int,v3 int,v4 int,v5 int,v6 int,v7 int,v8 int,v9 int,v10 int,
  v11 int,v12 int,v13 int,v14 int,v15 int,v16 int,v17 int,v18 int,v19 int,v20 int,
  v21 int,v22 int,v23 int,v24 int,v25 int,v26 int,v27 int,v28 int,v29 int,v30 int,
  v31 int,v32 int,v33 int,v34 int,v35 int,v36 int,v37 int,v38 int,v39 int,v40 int,
  v41 int,v42 int,v43 int,v44 int,v45 int,v46 int,v47 int,v48 int,v49 int,v50 int,
  v51 int,v52 int,v53 int,v54 int,v55 int,v56 int,v57 int,v58 int,v59 int,v60 int,
  v61 int,v62 int,v63 int,v64 int,v65 int,v66 int,v67 int,v68 int,v69 int,v70 int,
  v71 int,v72 int,v73 int,v74 int,v75 int,v76 int,v77 int,v78 int,v79 int,v80 int,
  v81 int,v82 int,v83 int,v84 int,v85 int,v86 int,v87 int,v88 int,v89 int,v90 int,
  v91 int,v92 int,v93 int,v94 int,v95 int,v96 int,v97 int,v98 int,v99 int,v100 int
);

插入数据:

查询SQL:

select unitid,
  max(if(vname='V1',vval,null)) v1,
  max(if(vname='V2',vval,null)) v2,
  max(if(vname='V3',vval,null)) v3,
  max(if(vname='V4',vval,null)) v4,
  max(if(vname='V5',vval,null)) v5,
  max(if(vname='V6',vval,null)) v6,
  max(if(vname='V7',vval,null)) v7,
  max(if(vname='V8',vval,null)) v8,
  max(if(vname='V9',vval,null)) v9,
  max(if(vname='V10',vval,null)) v10,
  max(if(vname='V11',vval,null)) v11,
  max(if(vname='V12',vval,null)) v12,
  max(if(vname='V13',vval,null)) v13,
  max(if(vname='V14',vval,null)) v14,
  max(if(vname='V15',vval,null)) v15,
  max(if(vname='V16',vval,null)) v16,
  max(if(vname='V17',vval,null)) v17,
  max(if(vname='V18',vval,null)) v18,
  max(if(vname='V19',vval,null)) v19,
  max(if(vname='V20',vval,null)) v20,
  max(if(vname='V21',vval,null)) v21,
  max(if(vname='V22',vval,null)) v22,
  max(if(vname='V23',vval,null)) v23,
  max(if(vname='V24',vval,null)) v24,
  max(if(vname='V25',vval,null)) v25,
  max(if(vname='V26',vval,null)) v26,
  max(if(vname='V27',vval,null)) v27,
  max(if(vname='V28',vval,null)) v28,
  max(if(vname='V29',vval,null)) v29,
  max(if(vname='V30',vval,null)) v30,
  max(if(vname='V31',vval,null)) v31,
  max(if(vname='V32',vval,null)) v32,
  max(if(vname='V33',vval,null)) v33,
  max(if(vname='V34',vval,null)) v34,
  max(if(vname='V35',vval,null)) v35,
  max(if(vname='V36',vval,null)) v36,
  max(if(vname='V37',vval,null)) v37,
  max(if(vname='V38',vval,null)) v38,
  max(if(vname='V39',vval,null)) v39,
  max(if(vname='V40',vval,null)) v40,
  max(if(vname='V41',vval,null)) v41,
  max(if(vname='V42',vval,null)) v42,
  max(if(vname='V43',vval,null)) v43,
  max(if(vname='V44',vval,null)) v44,
  max(if(vname='V45',vval,null)) v45,
  max(if(vname='V46',vval,null)) v46,
  max(if(vname='V47',vval,null)) v47,
  max(if(vname='V48',vval,null)) v48,
  max(if(vname='V49',vval,null)) v49,
  max(if(vname='V50',vval,null)) v50,
  max(if(vname='V51',vval,null)) v51,
  max(if(vname='V52',vval,null)) v52,
  max(if(vname='V53',vval,null)) v53,
  max(if(vname='V54',vval,null)) v54,
  max(if(vname='V55',vval,null)) v55,
  max(if(vname='V56',vval,null)) v56,
  max(if(vname='V57',vval,null)) v57,
  max(if(vname='V58',vval,null)) v58,
  max(if(vname='V59',vval,null)) v59,
  max(if(vname='V60',vval,null)) v60,
  max(if(vname='V61',vval,null)) v61,
  max(if(vname='V62',vval,null)) v62,
  max(if(vname='V63',vval,null)) v63,
  max(if(vname='V64',vval,null)) v64,
  max(if(vname='V65',vval,null)) v65,
  max(if(vname='V66',vval,null)) v66,
  max(if(vname='V67',vval,null)) v67,
  max(if(vname='V68',vval,null)) v68,
  max(if(vname='V69',vval,null)) v69,
  max(if(vname='V70',vval,null)) v70,
  max(if(vname='V71',vval,null)) v71,
  max(if(vname='V72',vval,null)) v72,
  max(if(vname='V73',vval,null)) v73,
  max(if(vname='V74',vval,null)) v74,
  max(if(vname='V75',vval,null)) v75,
  max(if(vname='V76',vval,null)) v76,
  max(if(vname='V77',vval,null)) v77,
  max(if(vname='V78',vval,null)) v78,
  max(if(vname='V79',vval,null)) v79,
  max(if(vname='V80',vval,null)) v80,
  max(if(vname='V81',vval,null)) v81,
  max(if(vname='V82',vval,null)) v82,
  max(if(vname='V83',vval,null)) v83,
  max(if(vname='V84',vval,null)) v84,
  max(if(vname='V85',vval,null)) v85,
  max(if(vname='V86',vval,null)) v86,
  max(if(vname='V87',vval,null)) v87,
  max(if(vname='V88',vval,null)) v88,
  max(if(vname='V89',vval,null)) v89,
  max(if(vname='V90',vval,null)) v90,
  max(if(vname='V91',vval,null)) v91,
  max(if(vname='V92',vval,null)) v92,
  max(if(vname='V93',vval,null)) v93,
  max(if(vname='V94',vval,null)) v94,
  max(if(vname='V95',vval,null)) v95,
  max(if(vname='V96',vval,null)) v96,
  max(if(vname='V97',vval,null)) v97,
  max(if(vname='V98',vval,null)) v98,
  max(if(vname='V99',vval,null)) v99,
  max(if(vname='V100',vval,null)) v100 
from tlog
group by unitid;

分析:

explain分析:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tlog
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10000000
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)